Impala CREATE TABLE statement

Create TABLE statements are used to create new tables in the desired database in Impala. C reating a base table involves naming the table and defining its columns and the data types for each column.

Grammar

The following is the syntax of the CREATE TABLE statement. H ere, IF NOT EXISTS is an optional clause. I f you use this clause, a table with a given name is created only if there are no existing tables with the same name in the specified database.

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. T he unique name or identifier of the table is after the CREATE TABLE statement. Optionally, you can specify database_name and table_name.

Cases

The following is an example of a table statement. I n this example, we my_db table called student in the database database database.

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

When you execute the above statement, a table with the specified name is created and the following output is displayed.

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

Fetched 0 row(s) in 0.48s

Verify

The show Tables query provides a list of tables in the current database in Impala. Therefore, you can use the Show Tables statement to verify that the table was created.
First, you need to switch the context to the database where the desired table is located, as shown below.

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

Then, if you use the show tables query to get a list of tables, 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 the HDFS file system, you need to specify where you want to create the database, as shown below.

CREATE DATABASE IF NOT EXISTS database_name LOCATION hdfs_path;

Create a database using The Hue browser

Open the impala query editor and type CREATE Table Statement in it. T hen click the execute button, as shown in the screenshot below.

After executing the query, gently move the cursor to the top of the drop-down menu and you'll notice a refresh symbol. I f you click refresh symbol, the database list is refreshed and recent changes are applied to it.

Verify

Click the drop-down menu under the title DATABASE on the left side of the editor. T here you can see a list of databases. Select the database my_db, as shown below.

When you select a my_db, you can see a list of tables in it, as shown below. H ere you can find the newly created table students, as shown below.