Impala - Create Table Statement

The CREATE TABLE Statement is used to create a new table in the required database in Impala. Creating a basic table involves naming the table and defining its columns and each column's data type.

Syntax

Following is the syntax of the CREATE TABLE Statement. Here, IF NOT EXISTS is an optional clause. If we use this clause, a table with the given name is created, only if there is no existing table in the specified database with the same name.

create table IF NOT EXISTS database_name.table_name (
   column1 data_type,
   column2 data_type,
   column3 data_type,
   ………
   columnN data_type
);

CREATE TABLE is the keyword that instructs the database system to create a new table. The unique name or identifier for the table follows the CREATE TABLE statement. Optionally you can specify database_name along with table_name.

Example

Following is an example of the create table statement. In this example, we have created a table named student in the database my_db.

[quickstart.cloudera:21000] > CREATE TABLE IF NOT EXISTS my_db.student
   (name STRING, age INT, contact INT );

On executing the above statement, a table with the specified name will be created, displaying the following output.

Query: create table student (name STRING, age INT, phone INT) 

Fetched 0 row(s) in 0.48s

Verification

The show Tables query gives a list of tables in the current database in Impala. Therefore, you can verify whether the table is created, using the Show Tables statement.

First of all, you need to switch the context to the database in which the required table exists, as shown below.

[quickstart.cloudera:21000] > use my_db; 
Query: use my_db

Then, if you get the list of tables using the show tables query, you can observe the table named student in it as shown below.

[quickstart.cloudera:21000] > show tables;

Query: show tables 
+-----------+ 
| name      | 
+-----------+ 
| student   | 
+-----------+ 
Fetched 1 row(s) in 0.10s 

HDFS path

In order to create a database in HDFS file system, you need to specify the location where the database is to be created as shown below.

CREATE DATABASE IF NOT EXISTS database_name LOCATION hdfs_path;

Creating a Database using Hue Browser

Open impala Query editor and type the CREATE Table Statement in it. And click on the execute button as shown in the following screenshot.

After executing the query, gently move the cursor to the top of the dropdown menu and you will find a refresh symbol. If you click on the refresh symbol, the list of databases will be refreshed and the recent changes done are applied to it.

Verification

Click on the drop down under the heading DATABASE on the left-hand side of the editor. There you can see a list of databases. Select the database my_db as shown below.

On selecting the database my_db you can see a list of tables in it as shown below. Here you can find the newly created table student as shown below.