Impala - Create a Database

In Impala, a database is a construct which holds related tables, views, and functions within their namespaces. It is represented as a directory tree in HDFS; it contains tables partitions, and data files. This chapter explains how to create a database in Impala.

CREATE DATABASE Statement

The CREATE DATABASE Statement is used to create a new database in Impala.

Syntax

Following is the syntax of the CREATE DATABASE Statement.

CREATE DATABASE IF NOT EXISTS database_name;

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

Example

Following is an example of the create database statement. In this example, we have created a database with the name my_database.

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

On executing the above query in cloudera impala-shell, you will get the following output.

Query: create DATABASE my_database 

Fetched 0 row(s) in 0.21s

Verification

The SHOW DATABASES query gives the pst of the databases in Impala, therefore you can verify whether the database is created, using the SHOW DATABASES statement. Here you can observe the newly created database my_db in the pst.

[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 HDFS file system, you need to specify the location where the database is to be created.

CREATE DATABASE IF NOT EXISTS database_name LOCATION hdfs_path;

Creating a Database using Hue Browser

Open Impala Query editor and type the CREATE DATABASE statement in it. Thereafter, cpck the execute button as shown in the following screenshot.

After executing the query, gently move the curser to the top of the dropdown menu and you will find a refresh symbol. If you cpck on the refresh symbol, the pst of databases will be refreshed and the recent changes are appped to it.

Verification

Cpck the drop-down box under the heading DATABASE on the left-hand side of the editor. There you can see a pst of databases in the system. Here you can observe the newly created database my_db as shown below.

If you observe carefully, you can see only one database, i.e., my_db in the pst along with the default database.