impala creates the database

In Impala, a database is a construct that holds related tables, views, and functions in its namespace. I t is represented as a tree in HDFS; I t contains table partitions and data files. T his chapter describes how to create a database in Impala.

CREATE DATABASE statement

Create DATABASE statements are used to create a new database in Impala.

Grammar

The following is the syntax of the CREATE DATABASE statement.

CREATE DATABASE IF NOT EXISTS database_name;

Here, IF NOT EXISTS is an optional clause. I f we use this clause, a database with a given name is created only if there is no existing database with the same name.

Cases

The following is an example of a creative database statement. In this example, we created a database called my_database database.

[quickstart.cloudera:21000] > CREATE DATABASE IF NOT EXISTS my_database;

When you perform the above query in cloudera impala-shell, you get the following output.

Query: create DATABASE my_database 

Fetched 0 row(s) in 0.21s

Verify

The SHOW DATABASES query gives a list of databases in Impala, so you can use the SHOW DATABASES statement to verify that the database was created. Here, you can observe the newly created database and my_db.

[quickstart.cloudera:21000] > show databases; 

Query: show databases
+-----------------------------------------------+
| name                                          | 
+-----------------------------------------------+ 
| _impala_builtins                              |
| default                                       | 
|  my_db                                        | 
+-----------------------------------------------+
Fetched 3 row(s) in 0.20s 
[quickstart.cloudera:21000] >

Hdfs path

In order to create a database in the HDFS file system, you need to specify where to create the database.

CREATE DATABASE IF NOT EXISTS database_name LOCATION hdfs_path;

Create a database using The Hue browser

Open the Impala query editor and type the CREATE DATABASE statement in it. A fter that, 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 pull-down box under the title DATABASE on the left side of the editor. T here you can see a list of databases in the system. H ere you can observe the newly created database my_db, as shown below.

If you look closely, you can only see one database, the list of my_db the default database.