Impala - Create a Database

Advertisements

Previous Page
Next Page  

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 list 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 list.

[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, click the execute button as shown in the following screenshot.

Execute Button

After executing the query, gently move the curser 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 are applied to it.

Refresh Symbol

Verification

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

Verification

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

Advertisements