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.

Impala - Drop a Database

The DROP DATABASE Statement of Impala is used to remove a database from Impala. Before deleting the database, it is recommended to remove all the tables from it.

Syntax

Following is the syntax of DROP DATABASE Statement.

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT | 
CASCADE] [LOCATION hdfs_path];

Here, IF EXISTS is an optional clause. If we use this clause when a database with the given name exists, then it will be deleted. And if there is no existing database with the given name, then no operation is performed.

Example

Following is an example of DROP DATABASE statement. Assume you have a database in Impala with the name sample_database.

And, if you verify the pst of databases using the SHOW DATABASES statement, you will observe the name in it.

[quickstart.cloudera:21000] > SHOW DATABASES;

Query: show DATABASES
+-----------------------+ 
| name                  | 
+-----------------------+ 
| _impala_builtins      | 
| default               | 
| my_db                 | 
| sample_database       | 
+-----------------------+ 
Fetched 4 row(s) in 0.11s

Now, you can delete this database using the DROP DATABASE Statement as shown below.

 < DROP DATABASE IF EXISTS sample_database;

This will delete the specified database and gives you the following output.

Query: drop DATABASE IF EXISTS sample_database;

Verification

You can verify whether the given database is deleted, using the SHOW DATABASES statement. Here you can observe that the database named sample_database is removed from the pst of databases.

[quickstart.cloudera:21000] > SHOW DATABASES;

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

Cascade

In general, to delete a database, you need to remove all the tables in it manually. If you use cascade, Impala removes the tables within the specified database before deleting it.

Example

Suppose there is a database in Impala named sample, and it contains two tables, namely, student and test. If you try to remove this database directly, you will get an error as shown below.

[quickstart.cloudera:21000] > DROP database sample;
Query: drop database sample 
ERROR: 
ImpalaRuntimeException: Error making  dropDatabase  RPC to Hive Metastore: 
CAUSED BY: InvapdOperationException: Database sample is not empty. One or more 
tables exist.

Using cascade, you can delete this database directly (without deleting its contents manually) as shown below.

[quickstart.cloudera:21000] > DROP database sample cascade; 
Query: drop database sample cascade

Note − You cannot delete the “current database” in Impala. Therefore, before deleting a database, you need to make sure that the current context is set to the database other than the one which you are going to delete.

Deleting a Database using Hue Browser

Open Impala Query editor and type the DELETE DATABASE statement in it and cpck on the execute button as shown below. Suppose there are three databases, namely, my_db, my_database, and sample_database along with the default database. Here we are deleting the database named my_database.

After executing the query, gently move the cursor to the top of the dropdown menu. Then, you will find a refresh symbol as shown in the screenshot given below. If you cpck on the refresh symbol, the pst of databases will be refreshed and the recent changes done will be appped to it.

Verification

Cpck on the drop down 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.

Impala - Select a Database

Once you get connected to Impala, it is required to select one among the available databases. The USE DATABASE Statement of Impala is used to switch the current session to another database.

Syntax

Following is the syntax of USE Statement.

USE db_name;

Example

Following is an example of USE statement. First of all, let us create a database with the name sample_database as shown below.

> CREATE DATABASE IF NOT EXISTS sample_database;

This will create a new database and give you the following output.

Query: create DATABASE IF NOT EXISTS my_db2

Fetched 0 row(s) in 2.73s

If you verify the pst of databases using the SHOW DATABASES statement, you can observe the name of newly created database in it.

> SHOW DATABASES;

Query: show DATABASES 
+-----------------------+ 
| name                  | 
+-----------------------+ 
| _impala_builtins      | 
| default               | 
| my_db                 | 
| sample_database       | 
+-----------------------+ 
Fetched 4 row(s) in 0.11s

Now, let’s switch the session to the newly created database (sample_database) using the USE Statement as shown below.

> USE sample_database;

This will change the current context to sample_database and display a message as shown below.

Query: use sample_database

Selecting a Database using Hue Browser

On the left-hand side of the Query Editor of Impala, you will find a dropdown menu as shown in the following screenshot.

If you cpck on the dropdown menu, you will find the pst of all the databases in Impala as shown below.

Simply select the database to which you need to change the current context.

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 pst 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 pst 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 cpck 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 cpck on the refresh symbol, the pst of databases will be refreshed and the recent changes done are appped to it.

Verification

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

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

Impala - Insert Statement

The INSERT Statement of Impala has two clauses − into and overwrite. Insert statement with into clause is used to add new records into an existing table in a database.

Syntax

There are two basic syntaxes of INSERT statement as follows −

insert into table_name (column1, column2, column3,...columnN)

values (value1, value2, value3,...valueN);

Here, column1, column2,...columnN are the names of the columns in the table into which you want to insert data.

You can also add values without specifying the column names but, for that you need to make sure the order of the values is in the same order as the columns in the table as shown below.

Insert into table_name values (value1, value2, value2);

CREATE TABLE is the keyword telpng 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 the table_name.

Example

Suppose we have created a table named student in Impala as shown below.

create table employee (Id INT, name STRING, age INT,address STRING, salary BIGINT);

Following is an example of creating a record in the table named employee.

[quickstart.cloudera:21000] > insert into employee 
(ID,NAME,AGE,ADDRESS,SALARY)VALUES (1,  Ramesh , 32,  Ahmedabad , 20000 );

On executing the above statement, a record is inserted into the table named employee displaying the following message.

Query: insert into employee (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1,  Ramesh ,
   32,  Ahmedabad , 20000 ) 
Inserted 1 row(s) in 1.32s

You can insert another record without specifying the column names as shown below.

[quickstart.cloudera:21000] > insert into employee values (2,  Khilan , 25, 
    Delhi , 15000 );

On executing the above statement, a record is inserted into the table named employee displaying the following message.

Query: insert into employee values (2,  Khilan , 25,  Delhi , 15000 ) 
Inserted 1 row(s) in 0.31s

You can insert a few more records in the employee table as shown below.

Insert into employee values (3,  kaushik , 23,  Kota , 30000 );

Insert into employee values (4,  Chaitap , 25,  Mumbai , 35000 );

Insert into employee values (5,  Hardik , 27,  Bhopal , 40000 );

Insert into employee values (6,  Komal , 22,  MP , 32000 );

After inserting the values, the employee table in Impala will be as shown below.

+----+----------+-----+-----------+--------+
| id | name     | age | address   | salary |
+----+----------+-----+-----------+--------+
| 1  | Ramesh   | 32  | Ahmedabad | 20000  |
| 2  | Khilan   | 25  | Delhi     | 15000  |
| 5  | Hardik   | 27  | Bhopal    | 40000  |
| 4  | Chaitap | 25  | Mumbai    | 35000  |
| 3  | kaushik  | 23  | Kota      | 30000  |
| 6  | Komal    | 22  | MP        | 32000  |
+----+----------+-----+-----------+--------+

Overwriting the Data in a Table

We can overwrite the records of a table using overwrite clause. The overwritten records will be permanently deleted from the table. Following is the syntax of using the overwrite clause.

Insert overwrite table_name values (value1, value2, value2);

Example

Following is an example of using the clause overwrite.

[quickstart.cloudera:21000] > Insert overwrite employee values (1,  Ram , 26, 
    Vishakhapatnam , 37000 );

On executing the above query, this will overwrite the table data with the specified record displaying the following message.

Query: insert overwrite employee values (1,  Ram , 26,  Vishakhapatnam , 37000 ) 
Inserted 1 row(s) in 0.31s

On verifying the table, you can observe that all the records of the table employee are overwritten by new records as shown below.

+----+------+-----+---------------+--------+
| id | name | age | address       | salary |
+----+------+-----+---------------+--------+
| 1  | Ram  | 26  | Vishakhapatnam| 37000  |
+----+------+-----+---------------+--------+

Inserting Data using Hue Browser

Open Impala Query editor and type the insert Statement in it. And cpck on the execute button as shown in the following screenshot.

After executing the query/statement, this record is added to the table.

Impala - Select Statement

Impala SELECT statement is used to fetch the data from one or more tables in a database. This query returns data in the form of tables.

Syntax

Following is the syntax of the Impala select statement.

SELECT column1, column2, columnN from table_name;

Here, column1, column2...are the fields of a table whose values you want to fetch. If you want to fetch all the fields available in the field, then you can use the following syntax −

SELECT * FROM table_name;

Example

Assume we have a table named customers in Impala, with the following data −

ID    NAME       AGE    ADDRESS      SALARY
---   -------    ---    ----------   -------
1     Ramesh     32     Ahmedabad    20000
2     Khilan     25     Delhi        15000
3     Hardik     27     Bhopal       40000
4     Chaitap   25     Mumbai       35000
5     kaushik    23     Kota         30000
6     Komal      22     Mp           32000

You can fetch the id, name, and age of all the records of the customers table using select statement as shown below −

[quickstart.cloudera:21000] > select id, name, age from customers;

On executing the above query, Impala fetches id, name, age of all the records from the specified table and displays them as shown below.

Query: select id,name,age from customers

+----+----------+-----+
| id | name     | age |
| 1  | Ramesh   | 32  |
| 2  | Khilan   | 25  |
| 3  | Hardik   | 27  |
| 4  | Chaitap | 25  |
| 5  | kaushik  | 23  |
| 6  | Komal    | 22  |
+----+----------+-----+

Fetched 6 row(s) in 0.66s

You can also fetch all the records from the customers table using the select query as shown below.

[quickstart.cloudera:21000] > select name, age from customers; 
Query: select * from customers

On executing the above query, Impala fetches and displays all the records from the specified table as shown below.

+----+----------+-----+-----------+--------+
| id | name     | age | address   | salary |
+----+----------+-----+-----------+--------+
| 1  | Ramesh   | 32  | Ahmedabad | 20000  |
| 2  | Khilan   | 25  | Delhi     | 15000  |
| 3  | Hardik   | 27  | Bhopal    | 40000  |
| 4  | Chaitap | 25  | Mumbai    | 35000  |
| 5  | kaushik  | 23  | Kota      | 30000  |
| 6  | Komal    | 22  | MP        | 32000  |
+----+----------+-----+-----------+--------+

Fetched 6 row(s) in 0.66s

Fetching the Records using Hue

Open Impala Query editor and type the select Statement in it. And cpck on the execute button as shown in the following screenshot.

After executing the query, if you scroll down and select the Results tab, you can see the pst of the records of the specified table as shown below.

Impala - Describe Statement

The describe statement in Impala is used to give the description of the table. The result of this statement contains the information about a table such as the column names and their data types.

Syntax

Following is the syntax of the Impala describe statement.

Describe table_name;

Example

For example, assume we have a table named customer in Impala, with the following data −

ID    NAME     AGE    ADDRESS     SALARY
--- --------- ----- ----------- -----------
1   Ramesh     32    Ahmedabad    20000
2   Khilan     25    Delhi        15000
3   Hardik     27    Bhopal       40000
4   Chaitap   25    Mumbai       35000
5   kaushik    23    Kota         30000
6   Komal      22    Mp           32000

You can get the description of the customer table using the describe statement as shown below −

[quickstart.cloudera:21000] > describe customer;

On executing the above query, Impala fetches the metadata of the specified table and displays it as shown below.

Query: describe customer
 
+---------+--------+---------+ 
| name    | type   | comment | 
+---------+--------+---------+ 
| id      | int    |         |                    
| name    | string |         | 
| age     | int    |         | 
| address | string |         | 
| salary  | bigint |         |
+---------+--------+---------+ 

Fetched 5 row(s) in 0.51s

Describing the Records using Hue

Open Impala Query editor and type the describe statement in it and cpck on the execute button as shown in the following screenshot.

After executing the query, if you scroll down and select the Results tab, you can see the metadata of the table as shown below.

Impala - Alter Table

The Alter table statement in Impala is used to perform changes on a given table. Using this statement, we can add, delete, or modify columns in an existing table and we can also rename it.

This Chapter explains various types of alter statements with syntax and examples. First of all assume that we have a table named customers in the my_db database in Impala, with the following data

ID   NAME     AGE   ADDRESS    SALARY
--- --------- ----- ----------- --------
1   Ramesh    32    Ahmedabad   20000
2   Khilan    25    Delhi       15000
3   Hardik    27    Bhopal      40000
4   Chaitap  25    Mumbai      35000
5   kaushik   23    Kota        30000
6   Komal     22    Mp          32000

And, if you get the pst of tables in the database my_db, you can find the customers table in it as shown below.

[quickstart.cloudera:21000] > show tables;

Query: show tables 
+-----------+ 
| name      | 
+-----------+ 
| customers | 
| employee  | 
| student   | 
| student1  | 
+-----------+

Altering the name of a table

Syntax

The basic syntax of ALTER TABLE to rename an existing table is as follows −

ALTER TABLE [old_db_name.]old_table_name RENAME TO [new_db_name.]new_table_name

Example

Following is an example of changing the name of the table using the alter statement. Here we are changing the name of the table customers to users.

[quickstart.cloudera:21000] > ALTER TABLE my_db.customers RENAME TO my_db.users;

After executing the above query, Impala changes the name of the table as required, displaying the following message.

Query: alter TABLE my_db.customers RENAME TO my_db.users

You can verify the pst of tables in the current database using the show tables statement. You can find the table named users instead of customers.

Query: show tables 
+----------+ 
| name     | 
+----------+ 
| employee | 
| student  | 
| student1 | 
| users    | 
+----------+ 
Fetched 4 row(s) in 0.10s

Adding columns to a table

Syntax

The basic syntax of ALTER TABLE to add columns to an existing table is as follows −

ALTER TABLE name ADD COLUMNS (col_spec[, col_spec ...])

Example

The following query is an example demonstrating how to add columns to an existing table. Here we are adding two columns account_no and phone_number (both are of bigint data type) to the users table.

[quickstart.cloudera:21000] > ALTER TABLE users ADD COLUMNS (account_no BIGINT, 
phone_no BIGINT);

On executing the above query, it will add the specified columns to the table named student, displaying the following message.

Query: alter TABLE users ADD COLUMNS (account_no BIGINT, phone_no BIGINT)

If you verify the schema of the table users, you can find the newly added columns in it as shown below.

quickstart.cloudera:21000] > describe users;
 
Query: describe users 
+------------+--------+---------+ 
| name       | type   | comment | 
+------------+--------+---------+ 
| id         | int    |         | 
| name       | string |         | 
| age        | int    |         |
| address    | string |         | 
| salary     | bigint |         | 
| account_no | bigint |         | 
| phone_no   | bigint |         | 
+------------+--------+---------+ 
Fetched 7 row(s) in 0.20s

Dropping columns from a table

Syntax

The basic syntax of ALTER TABLE to DROP COLUMN in an existing table is as follows −

ALTER TABLE name DROP [COLUMN] column_name

Example

The following query is an example of deleting columns from an existing table. Here we are deleting the column named account_no.

[quickstart.cloudera:21000] > ALTER TABLE users DROP account_no;

On executing the above query, Impala deletes the column named account_no displaying the following message.

Query: alter TABLE users DROP account_no

If you verify the schema of the table users, you cannot find the column named account_no since it was deleted.

[quickstart.cloudera:21000] > describe users; 

Query: describe users 
+----------+--------+---------+ 
| name     | type   | comment | 
+----------+--------+---------+ 
| id       | int    |         | 
| name     | string |         | 
| age      | int    |         | 
| address  | string |         | 
| salary   | bigint |         | 
| phone_no | bigint |         |
+----------+--------+---------+ 
Fetched 6 row(s) in 0.11s

Changing the name and type of a column

Syntax

The basic syntax of ALTER TABLE to change the name and datatype of a column in an existing table is as follows −

ALTER TABLE name CHANGE column_name new_name new_type

Example

Following is an example of changing the name and datatype of a column using the alter statement. Here we are changing the name of the column phone_no to email and its data type to string.

[quickstart.cloudera:21000] > ALTER TABLE users CHANGE phone_no e_mail string;

On executing the above query, Impala does the specified changes, displaying the following message.

Query: alter TABLE users CHANGE phone_no e_mail string

You can verify the metadata of the table users using the describe statement. You can observe that Impala has done the required changes to the specified column.

[quickstart.cloudera:21000] > describe users; 
Query: describe users 
+----------+--------+---------+ 
| name     | type   | comment | 
+----------+--------+---------+ 
| id       | int    |         | 
| name     | string |         | 
| age      | int    |         | 
| address  | string |         | 
| salary   | bigint |         | 
| phone_no | bigint |         |
+----------+--------+---------+ 
Fetched 6 row(s) in 0.11s

Altering a Table using Hue

Open the Impala Query editor and type the alter statement in it and cpck on the execute button as shown in the following screenshot.

On executing the above query, it will change the name of the table customers to users. In the same way, we can execute all the alter queries.

Impala - Drop a Table

The Impala drop table statement is used to delete an existing table in Impala. This statement also deletes the underlying HDFS files for internal tables

NOTE − You have to be careful while using this command because once a table is deleted, then all the information available in the table would also be lost forever.

Syntax

Following is the syntax of the DROP TABLE Statement. Here, IF EXISTS is an optional clause. If we use this clause, a table with the given name is deleted, only if it exists. Otherwise, no operation will be carried out.

DROP table database_name.table_name;

If you try to delete a table that doesn’t exist without the IF EXISTS clause, an error will be generated. Optionally you can specify database_name along with table_name.

Example

Let us first verify the pst of tables in the database my_db as shown below.

[quickstart.cloudera:21000] > show tables;

Query: show tables 
+------------+ 
| name       | 
+------------+ 
| customers  | 
| employee   | 
| student    | 
+------------+ 
Fetched 3 row(s) in 0.11s

From the above result, you can observe that the database my_db contains 3 tables

Following is an example of the drop table statement. In this example, we are deleting the table named student from the database my_db.

[quickstart.cloudera:21000] > drop table if exists my_db.student;

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

Query: drop table if exists student

Verification

The show Tables query gives a pst of the tables in the current database in Impala. Therefore, you can verify whether a table is deleted, 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 pst of tables using the show tables query, you can observe the table named student is not in the pst.

[quickstart.cloudera:21000] > show tables; 

Query: show tables 
+-----------+ 
| name      | 
+-----------+ 
| customers | 
| employee  | 
| student   | 
+-----------+ 
Fetched 3 row(s) in 0.11s

Creating a Database using Hue Browser

Open Impala Query editor and type the drop Table Statement in it. And cpck 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 cpck on the refresh symbol, the pst of databases will be refreshed and the recent changes done are appped to it.

Verification

Cpck on the drop down under the heading DATABASE on the left-hand side of the editor. There you can see a pst of databases; select the database my_db as shown below.

On selecting the database my_db, you can see a pst of tables in it as shown below. Here you cannot find the deleted table student in the pst as shown below.

Impala - Truncate a Table

The Truncate Table Statement of Impala is used to remove all the records from an existing table.

You can also use DROP TABLE command to delete a complete table, but it would remove the complete table structure from the database and you would need to re-create this table once again if you wish you store some data.

Syntax

Following is the syntax of the truncate table statement.

truncate table_name;

Example

Suppose, we have a table named customers in Impala, and if you verify its contents, you are getting the following result. This means that the customers table contains 6 records.

[quickstart.cloudera:21000] > select * from customers; 

Query: select * from customers 
+----+----------+-----+-----------+--------+--------+ 
| id | name     | age | address   | salary | e_mail | 
+----+----------+-----+-----------+--------+--------+
| 1  | Ramesh   | 32  | Ahmedabad | 20000  | NULL   | 
| 2  | Khilan   | 25  | Delhi     | 15000  | NULL   | 
| 3  | kaushik  | 23  | Kota      | 30000  | NULL   |
| 4  | Chaitap | 25  | Mumbai    | 35000  | NULL   | 
| 5  | Hardik   | 27  | Bhopal    | 40000  | NULL   | 
| 6  | Komal    | 22  | MP        | 32000  | NULL   | 
+----+----------+-----+-----------+--------+--------+

Following is an example of truncating a table in Impala using truncate statement. Here we are removing all the records of the table named customers.

[quickstart.cloudera:21000] > truncate customers;

On executing the above statement, Impala deletes all the records of the specified table, displaying the following message.

Query: truncate customers 

Fetched 0 row(s) in 0.37s

Verification

If you verify the contents of the customers table, after the delete operation, using select statement, you will get an empty row as shown below.

[quickstart.cloudera:21000] > select * from customers;
Query: select * from customers 

Fetched 0 row(s) in 0.12s

Truncating a Table using Hue Browser

Open Impala Query editor and type the truncate Statement in it. And cpck on the execute button as shown in the following screenshot.

After executing the query/statement, all the records from the table are deleted.

Impala - Show Tables

The show tables statement in Impala is used to get the pst of all the existing tables in the current database.

Example

Following is an example of the show tables statement. If you want to get the pst of tables in a particular database, first of all, change the context to the required database and get the pst of tables in it using show tables statement as shown below.

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

On executing the above query, Impala fetches the pst of all the tables in the specified database and displays it as shown below.

Query: show tables 
+-----------+ 
| name      | 
+-----------+ 
| customers | 
| employee  | 
+-----------+ 
Fetched 2 row(s) in 0.10s

Listing the Tables using Hue

Open impala Query editor, select the context as my_db and type the show tables statement in it and cpck on the execute button as shown in the following screenshot.

After executing the query, if you scroll down and select the Results tab, you can see the pst of the tables as shown below.

Impala - Create View

A view is nothing more than a statement of Impala query language that is stored in the database with an associated name. It is a composition of a table in the form of a predefined SQL query.

A view can contain all the rows of a table or selected ones. A view can be created from one or many tables. Views allow users to −

Structure data in a way that users or classes of users find natural or intuitive.

Restrict access to the data such that a user can see and (sometimes) modify exactly what they need and no more.

Summarize data from various tables which can be used to generate reports.

You can create a view using the Create View statement of Impala.

Syntax

Following is the syntax of the create view statement. 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 View IF NOT EXISTS view_name as Select statement

Example

For example, assume we have a table named customers in the my_db database in Impala, with the following data.

ID  NAME      AGE   ADDRESS     SALARY
--- --------- ----- ----------- --------
1   Ramesh    32    Ahmedabad   20000
2   Khilan    25    Delhi       15000
3   Hardik    27    Bhopal      40000
4   Chaitap  25    Mumbai      35000
5   kaushik   23    Kota        30000
6   Komal     22    MP          32000

Following is an example of Create View Statement. In this example, we are creating a view as customers table which contains the columns, name, and age.

[quickstart.cloudera:21000] > CREATE VIEW IF NOT EXISTS customers_view AS 
select name, age from customers;

On executing the above query, a view with the desired columns is created, displaying the following message.

Query: create VIEW IF NOT EXISTS sample AS select * from customers 
Fetched 0 row(s) in 0.33s

Verification

You can verify the contents of the view just created, using the select statement as shown below.

[quickstart.cloudera:21000] > select * from customers_view;

This will produce the following result.

Query: select * from customers_view 
+----------+-----+ 
| name     | age | 
+----------+-----+ 
| Komal    | 22  | 
| Khilan   | 25  | 
| Ramesh   | 32  | 
| Hardik   | 27  | 
| Chaitap | 25  | 
| kaushik  | 23  | 
+----------+-----+ 
Fetched 6 row(s) in 4.80s

Creating a View using Hue

Open Impala Query editor, select the context as my_db, and type the Create View statement in it and cpck on the execute button as shown in the following screenshot.

After executing the query, if you scroll down, you can see the view named sample created in the pst of tables as shown below.

Impala - Alter View

The Alter View statement of Impala is used to change a view. Using this statement, you can change the name of a view, change the database, and the query associated with it.

Since a view is a logical construct, no physical data will be affected by the alter view query.

Syntax

Following is the syntax of the Alter View statement

ALTER VIEW database_name.view_name as Select statement

Example

For example, assume we have a view named customers_view in the my_db database in Impala with the following contents.

+----------+-----+ 
| name     | age | 
+----------+-----+ 
| Komal    | 22  | 
| Khilan   | 25  | 
| Ramesh   | 32  | 
| Hardik   | 27  | 
| Chaitap | 25  | 
| kaushik  | 23  | 
+----------+-----+

Following is an example of Alter View Statement. In this example, we are including the columns id, name, and salary instead of name and age to the customers_view.

[quickstart.cloudera:21000] > Alter view customers_view as select id, name, 
salary from customers;

On executing the above query, Impala does the specified changes to the customers_view, displaying the following message.

Query: alter view customers_view as select id, name, salary from customers

Verification

You can verify the contents of the view named customers_view, using the select statement as shown below.

[quickstart.cloudera:21000] > select * from customers_view;
Query: select * from customers_view

This will produce the following result.

+----+----------+--------+ 
| id | name     | salary | 
+----+----------+--------+
| 3  | kaushik  | 30000  | 
| 2  | Khilan   | 15000  | 
| 5  | Hardik   | 40000  | 
| 6  | Komal    | 32000  | 
| 1  | Ramesh   | 20000  | 
| 4  | Chaitap | 35000  | 
+----+----------+--------+ 
Fetched 6 row(s) in 0.69s

Altering a View using Hue

Open Impala Query editor, select the context as my_db, and type the Alter View statement in it and cpck on the execute button as shown in the following screenshot.

After executing the query, the view named sample will be altered accordingly.

Impala - Drop a View

The Drop View query of Impala is used to delete an existing view. Since a view is a logical construct, no physical data will be affected by the drop view query.

Syntax

Following is the syntax of the drop view statement.

DROP VIEW database_name.view_name;

Example

For example, assume we have a view named customers_view in the my_db database in Impala with the following contents.

+----------+-----+ 
| name     | age | 
+----------+-----+ 
| Komal    | 22  | 
| Khilan   | 25  | 
| Ramesh   | 32  | 
| Hardik   | 27  | 
| Chaitap | 25  | 
| kaushik  | 23  | 
+----------+-----+

Following is an example of Drop View Statement. In this example, we are trying to delete the view named customers_view using the drop view query.

[quickstart.cloudera:21000] > Drop view customers_view;

On executing the above query, Impala deletes the specified view, displaying the following message.

Query: drop view customers_view

Verification

If you verify the pst of tables using show tables statement, you can observe that the view named customers_view is deleted.

[quickstart.cloudera:21000] > show tables;

This will produce the following result.

Query: show tables 
+-----------+ 
| name      | 
+-----------+ 
| customers | 
| employee  | 
| sample    | 
+-----------+ 
Fetched 3 row(s) in 0.10s

Dropping a View using Hue

Open Impala Query editor, select the context as my_db, and type the Drop view statement in it and cpck on the execute button as shown in the following screenshot.

After executing the query, if you scroll down, you can see a pst named TABLES. This pst contains all the tables and views in the current database. From this pst, you can find that the specified view was deleted.

Impala - Order By Clause

The Impala ORDER BY clause is used to sort the data in an ascending or descending order, based on one or more columns. Some databases sort the query results in ascending order by default.

Syntax

Following is the syntax of the ORDER BY clause.

select * from table_name ORDER BY col_name [ASC|DESC] [NULLS FIRST|NULLS LAST]

You can arrange the data in the table in ascending or descending order using the keywords ASC or DESC respectively.

In the same way, if we use NULLS FIRST, all the null values in the table are arranged in the top rows; and if we use NULLS LAST, the rows containing null values will be arranged last.

Example

Assume we have a table named customers in the database my_db and its contents are as follows −

[quickstart.cloudera:21000] > select * from customers;
Query: select * from customers 
+----+----------+-----+-----------+--------+ 
| id | name     | age | address   | salary | 
+----+----------+-----+-----------+--------+ 
| 3  | kaushik  | 23  | Kota      | 30000  | 
| 1  | Ramesh   |  32 | Ahmedabad | 20000  | 
| 2  | Khilan   | 25  | Delhi     | 15000  | 
| 6  | Komal    | 22  | MP        | 32000  | 
| 4  | Chaitap | 25  | Mumbai    | 35000  | 
| 5  | Hardik   | 27  | Bhopal    | 40000  | 
+----+----------+-----+-----------+--------+ 
Fetched 6 row(s) in 0.51s

Following is an example of arranging the data in the customers table, in ascending order of their id’s using the order by clause.

[quickstart.cloudera:21000] > Select * from customers ORDER BY id asc;

On executing, the above query produces the following output.

Query: select * from customers ORDER BY id asc 
+----+----------+-----+-----------+--------+ 
| id | name     | age | address   | salary | 
+----+----------+-----+-----------+--------+ 
| 1  | Ramesh   | 32  | Ahmedabad | 20000  | 
| 2  | Khilan   | 25  | Delhi     | 15000  | 
| 3  | kaushik  | 23  | Kota      | 30000  | 
| 4  | Chaitap | 25  | Mumbai    | 35000  | 
| 5  | Hardik   | 27  | Bhopal    | 40000  | 
| 6  | Komal    | 22  | MP        | 32000  | 
+----+----------+-----+-----------+--------+ 
Fetched 6 row(s) in 0.56s

In the same way, you can arrange the data of customers table in descending order using the order by clause as shown below.

[quickstart.cloudera:21000] > Select * from customers ORDER BY id desc;

On executing, the above query produces the following output.

Query: select * from customers ORDER BY id desc 
+----+----------+-----+-----------+--------+ 
| id | name     | age | address   | salary | 
+----+----------+-----+-----------+--------+ 
| 6  | Komal    | 22  | MP        | 32000  | 
| 5  | Hardik   | 27  | Bhopal    | 40000  | 
| 4  | Chaitap | 25  | Mumbai    | 35000  | 
| 3  | kaushik  | 23  | Kota      | 30000  | 
| 2  | Khilan   | 25  | Delhi     | 15000  |
| 1  | Ramesh   | 32  | Ahmedabad | 20000  | 
+----+----------+-----+-----------+--------+ 
Fetched 6 row(s) in 0.54s

Impala - Group By Clause

The Impala GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups.

Syntax

Following is the syntax of the GROUP BY clause.

select data from table_name Group BY col_name;

Example

Assume we have a table named customers in the database my_db and its contents are as follows −

[quickstart.cloudera:21000] > select * from customers; 
Query: select * from customers 
+----+----------+-----+-----------+--------+ 
| id | name     | age | address   | salary | 
+----+----------+-----+-----------+--------+ 
| 1  | Ramesh   | 32  | Ahmedabad | 20000  | 
| 2  | Khilan   | 25  | Delhi     | 15000  | 
| 3  | kaushik  | 23  | Kota      | 30000  | 
| 4  | Chaitap | 25  | Mumbai    | 35000  | 
| 5  | Hardik   | 27  | Bhopal    | 40000  | 
| 6  | Komal    | 22  | MP        | 32000  | 
+----+----------+-----+-----------+--------+ 
Fetched 6 row(s) in 0.51s

You can get the total amount of salary of each customer using GROUP BY query as shown below.

[quickstart.cloudera:21000] > Select name, sum(salary) from customers Group BY name;

On executing, the above query gives the following output.

Query: select name, sum(salary) from customers Group BY name 
+----------+-------------+ 
| name     | sum(salary) | 
+----------+-------------+ 
| Ramesh   | 20000       | 
| Komal    | 32000       | 
| Hardik   | 40000       | 
| Khilan   | 15000       | 
| Chaitap | 35000       | 
| kaushik  | 30000       |
+----------+-------------+ 
Fetched 6 row(s) in 1.75s

Assume that this table has multiple records as shown below.

+----+----------+-----+-----------+--------+ 
| id | name     | age | address   | salary | 
+----+----------+-----+-----------+--------+ 
| 1  | Ramesh   | 32  | Ahmedabad | 20000  |
| 2  | Ramesh   | 32  | Ahmedabad | 1000|  | 
| 3  | Khilan   | 25  | Delhi     | 15000  | 
| 4  | kaushik  | 23  | Kota      | 30000  | 
| 5  | Chaitap | 25  | Mumbai    | 35000  |
| 6  | Chaitap | 25  | Mumbai    | 2000   |
| 7  | Hardik   | 27  | Bhopal    | 40000  | 
| 8  | Komal    | 22  | MP        | 32000  | 
+----+----------+-----+-----------+--------+

Now again, you can get the total amount of salaries of the employees, considering the repeated entries of records, using the Group By clause as shown below.

Select name, sum(salary) from customers Group BY name;

On executing, the above query gives the following output.

Query: select name, sum(salary) from customers Group BY name 
+----------+-------------+ 
| name     | sum(salary) | 
+----------+-------------+ 
| Ramesh   | 21000       | 
| Komal    | 32000       | 
| Hardik   | 40000       | 
| Khilan   | 15000       | 
| Chaitap | 37000       | 
| kaushik  | 30000       | 
+----------+-------------+
Fetched 6 row(s) in 1.75s

Impala - Having Clause

The Having clause in Impala enables you to specify conditions that filter which group results appear in the final results.

In general, the Having clause is used along with group by clause; it places conditions on groups created by the GROUP BY clause.

Syntax

Following is the syntax of the Havingclause.

select * from table_name ORDER BY col_name [ASC|DESC] [NULLS FIRST|NULLS LAST]

Example

Assume we have a table named customers in the database my_db and its contents are as follows −

[quickstart.cloudera:21000] > select * from customers; 
Query: select * from customers 
+----+----------+-----+-------------+--------+ 
| id | name     | age | address     | salary | 
+----+----------+-----+-------------+--------+ 
| 1  | Ramesh   | 32  | Ahmedabad   | 20000  |
| 2  | Khilan   | 25  | Delhi       | 15000  | 
| 3  | kaushik  | 23  | Kota        | 30000  | 
| 4  | Chaitap | 25  | Mumbai      | 35000  | 
| 5  | Hardik   | 27  | Bhopal      | 40000  | 
| 6  | Komal    | 22  | MP          | 32000  | 
| 7  | ram      | 25  | chennai     | 23000  | 
| 8  | rahim    | 22  | vizag       | 31000  | 
| 9  | robert   | 23  | banglore    | 28000  | 
+----+----------+-----+-----------+--------+ 
Fetched 9 row(s) in 0.51s

Following is an example of using Having clause in Impala −

[quickstart.cloudera:21000] > select max(salary) from customers group by age having max(salary) > 20000;

This query initially groups the table by age and selects the maximum salaries of each group and displays those salaries, which are greater than 20000 as shown below.

20000 
+-------------+ 
| max(salary) |
+-------------+ 
| 30000       |
| 35000       | 
| 40000       | 
| 32000       | 
+-------------+ 
Fetched 4 row(s) in 1.30s

Impala - Limit Clause

The pmit clause in Impala is used to restrict the number of rows of a resultset to a desired number, i.e., the resultset of the query does not hold the records beyond the specified pmit.

Syntax

Following is the syntax of the Limit clause in Impala.

select * from table_name order by id pmit numerical_expression;

Example

Assume we have a table named customers in the database my_db and its contents are as follows −

[quickstart.cloudera:21000] > select * from customers; 
Query: select * from customers 
+----+----------+-----+-----------+--------+ 
| id | name     | age | address   | salary | 
+----+----------+-----+-----------+--------+ 
| 3  | kaushik  | 23  | Kota      | 30000  | 
| 6  | Komal    | 22  | MP        | 32000  | 
| 1  | Ramesh   | 32  | Ahmedabad | 20000  | 
| 5  | Hardik   | 27  | Bhopal    | 40000  | 
| 2  | Khilan   | 25  | Delhi     | 15000  | 
| 8  | ram      | 22  | vizag     | 31000  | 
| 9  | robert   | 23  | banglore  | 28000  | 
| 7  | ram      | 25  | chennai   | 23000  | 
| 4  | Chaitap | 25  | Mumbai    | 35000  | 
+----+----------+-----+-----------+--------+ 
Fetched 9 row(s) in 0.51s

You can arrange the records in the table in the ascending order of their id’s using the order by clause as shown below.

[quickstart.cloudera:21000] > select * from customers order by id; 
Query: select * from customers order by id 
+----+----------+-----+-----------+--------+ 
| id | name     | age | address   | salary | 
+----+----------+-----+-----------+--------+ 
| 1  | Ramesh   | 32  | Ahmedabad | 20000  | 
| 2  | Khilan   | 25  | Delhi     | 15000  | 
| 3  | kaushik  | 23  | Kota      | 30000  | 
| 4  | Chaitap | 25  | Mumbai    | 35000  | 
| 5  | Hardik   | 27  | Bhopal    | 40000  | 
| 6  | Komal    | 22  | MP        | 32000  | 
| 7  | ram      | 25  | chennai   | 23000  | 
| 8  | ram      | 22  | vizag     | 31000  |
| 9  | robert   | 23  | banglore  | 28000  | 
+----+----------+-----+-----------+--------+ 
Fetched 9 row(s) in 0.54s

Now, using the pmit clause, you can restrict the number of records of the output to 4, using the pmit clause as shown below.

[quickstart.cloudera:21000] > select * from customers order by id pmit 4;

On executing, the above query gives the following output.

Query: select * from customers order by id pmit 4 
+----+----------+-----+-----------+--------+ 
| id | name     | age | address   | salary | 
+----+----------+-----+-----------+--------+ 
| 1  | Ramesh   | 32  | Ahmedabad | 20000  | 
| 2  | Khilan   | 25  | Delhi     | 15000  |
| 3  | kaushik  | 23  | Kota      | 30000  | 
| 4  | Chaitap | 25  | Mumbai    | 35000  | 
+----+----------+-----+-----------+--------+ 
Fetched 4 row(s) in 0.64s

Impala - Offset Clause

In general, the rows in the resultset of a select query starts from 0. Using the offset clause, we can decide from where the output should be considered. For example, if we choose the offset as 0, the result will be as usual and if we choose the offset as 5, the result starts from the fifth row.

Syntax

Following is the syntax of the offsetclause in Impala.

select data from table_name Group BY col_name;

Example

Assume we have a table named customers in the database my_db and its contents are as follows −

[quickstart.cloudera:21000] > select * from customers; 
Query: select * from customers 
+----+----------+-----+-----------+--------+ 
| id | name     | age | address   | salary | 
+----+----------+-----+-----------+--------+ 
| 3  | kaushik  | 23  | Kota      | 30000  | 
| 6  | Komal    | 22  | MP        | 32000  | 
| 1  | Ramesh   | 32  | Ahmedabad | 20000  | 
| 5  | Hardik   | 27  | Bhopal    | 40000  | 
| 2  | Khilan   | 25  | Delhi     | 15000  | 
| 8  | ram      | 22  | vizag     | 31000  | 
| 9  | robert   | 23  | banglore  | 28000  |
| 7  | ram      | 25  | chennai   | 23000  | 
| 4  | Chaitap | 25  | Mumbai    | 35000  | 
+----+----------+-----+-----------+--------+ 
Fetched 9 row(s) in 0.51s

You can arrange the records in the table in the ascending order of their id’s and pmit the number of records to 4, using pmit and order by clauses as shown below.

Query: select * from customers order by id pmit 4 
+----+----------+-----+-----------+--------+ 
| id | name     | age | address   | salary | 
+----+----------+-----+-----------+--------+ 
| 1  | Ramesh   | 32  | Ahmedabad | 20000  | 
| 2  | Khilan   | 25  | Delhi     | 15000  | 
| 3  | kaushik  | 23  | Kota      | 30000  | 
| 4  | Chaitap | 25  | Mumbai    | 35000  | 
+----+----------+-----+-----------+--------+ 
Fetched 4 row(s) in 0.64s

Following is an example of the offset clause. Here, we are getting the records in the customers table in the order of their id’s and printing the first four rows starting from the 0^th row.

[quickstart.cloudera:21000] > select * from customers order by id pmit 4 offset 0;

On executing, the above query gives the following result.

Query: select * from customers order by id pmit 4 offset 0 
+----+----------+-----+-----------+--------+
| id | name     | age | address   | salary | 
+----+----------+-----+-----------+--------+ 
| 1  | Ramesh   | 32  | Ahmedabad | 20000  | 
| 2  | Khilan   | 25  | Delhi     | 15000  | 
| 3  | kaushik  | 23  | Kota      | 30000  | 
| 4  | Chaitap | 25  | Mumbai    | 35000  | 
+----+----------+-----+-----------+--------+ 
Fetched 4 row(s) in 0.62s

In the same way, you can get four records from the customers table starting from the row having offset 5 as shown below.

[quickstart.cloudera:21000] > select * from customers order by id pmit 4 offset 5; 
Query: select * from customers order by id pmit 4 offset 5 
+----+--------+-----+----------+--------+ 
| id | name   | age | address  | salary | 
+----+--------+-----+----------+--------+ 
| 6  | Komal  | 22  | MP       | 32000  | 
| 7  | ram    | 25  | chennai  | 23000  | 
| 8  | ram    | 22  | vizag    | 31000  |
| 9  | robert | 23  | banglore | 28000  | 
+----+--------+-----+----------+--------+ 
Fetched 4 row(s) in 0.52s

Impala - Union Clause

You can combine the results of two queries using the Union clause of Impala.

Syntax

Following is the syntax of the Union clause in Impala.

query1 union query2;

Example

Assume we have a table named customers in the database my_db and its contents are as follows −

[quickstart.cloudera:21000] > select * from customers; 
Query: select * from customers 
+----+----------+-----+-----------+--------+ 
| id | name     | age | address   | salary | 
+----+----------+-----+-----------+--------+ 
| 1  | Ramesh   | 32  | Ahmedabad | 20000  | 
| 9  | robert   | 23  | banglore  | 28000  | 
| 2  | Khilan   | 25  | Delhi     | 15000  | 
| 4  | Chaitap | 25  | Mumbai    | 35000  | 
| 7  | ram      | 25  | chennai   | 23000  | 
| 6  | Komal    | 22  | MP        | 32000  | 
| 8  | ram      | 22  | vizag     | 31000  | 
| 5  | Hardik   | 27  | Bhopal    | 40000  | 
| 3  | kaushik  | 23  | Kota      | 30000  | 
+----+----------+-----+-----------+--------+ 
Fetched 9 row(s) in 0.59s

In the same way, suppose we have another table named employee and its contents are as follows −

[quickstart.cloudera:21000] > select * from employee; 
Query: select * from employee 
+----+---------+-----+---------+--------+ 
| id | name    | age | address | salary | 
+----+---------+-----+---------+--------+ 
| 3  | mahesh  | 54  | Chennai | 55000  | 
| 2  | ramesh  | 44  | Chennai | 50000  | 
| 4  | Rupesh  | 64  | Delhi   | 60000  | 
| 1  | subhash | 34  | Delhi   | 40000  | 
+----+---------+-----+---------+--------+ 
Fetched 4 row(s) in 0.59s

Following is an example of the union clause in Impala. In this example, we arrange the records in both tables in the order of their id’s and pmit their number by 3 using two separate queries and joining these queries using the UNION clause.

[quickstart.cloudera:21000] > select * from customers order by id pmit 3
 union select * from employee order by id pmit 3;

On executing, the above query gives the following output.

Query: select * from customers order by id pmit 3 union select 
   * from employee order by id pmit 3 
+----+---------+-----+-----------+--------+ 
| id | name    | age | address   | salary | 
+----+---------+-----+-----------+--------+ 
| 2  | Khilan  | 25  | Delhi     | 15000  |
| 3  | mahesh  | 54  | Chennai   | 55000  | 
| 1  | subhash | 34  | Delhi     | 40000  | 
| 2  | ramesh  | 44  | Chennai   | 50000  | 
| 3  | kaushik | 23  | Kota      | 30000  | 
| 1  | Ramesh  | 32  | Ahmedabad | 20000  | 
+----+---------+-----+-----------+--------+ 
Fetched 6 row(s) in 3.11s

Impala - With Clause

In case a query is way too complex, we can define apases to complex parts and include them in the query using the with clause of Impala.

Syntax

Following is the syntax of the with clause in Impala.

with x as (select 1), y as (select 2) (select * from x union y);

Example

Assume we have a table named customers in the database my_db and its contents are as follows −

[quickstart.cloudera:21000] > select * from customers;
Query: select * from customers 
+----+----------+-----+-----------+--------+ 
| id | name     | age | address   | salary | 
+----+----------+-----+-----------+--------+ 
| 1  | Ramesh   | 32  | Ahmedabad | 20000  | 
| 9  | robert   | 23  | banglore  | 28000  | 
| 2  | Khilan   | 25  | Delhi     | 15000  | 
| 4  | Chaitap | 25  | Mumbai    | 35000  | 
| 7  | ram      | 25  | chennai   | 23000  | 
| 6  | Komal    | 22  | MP        | 32000  | 
| 8  | ram      | 22  | vizag     | 31000  | 
| 5  | Hardik   | 27  | Bhopal    | 40000  | 
| 3  | kaushik  | 23  | Kota      | 30000  | 
+----+----------+-----+-----------+--------+ 
Fetched 9 row(s) in 0.59s

In the same way, suppose we have another table named employee and its contents are as follows −

[quickstart.cloudera:21000] > select * from employee; 
Query: select * from employee 
+----+---------+-----+---------+--------+ 
| id | name    | age | address | salary | 
+----+---------+-----+---------+--------+ 
| 3  | mahesh  | 54  | Chennai | 55000  | 
| 2  | ramesh  | 44  | Chennai | 50000  | 
| 4  | Rupesh  | 64  | Delhi   | 60000  | 
| 1  | subhash | 34  | Delhi   | 40000  | 
+----+---------+-----+---------+--------+ 
Fetched 4 row(s) in 0.59s

Following is an example of the with clause in Impala. In this example, we are displaying the records from both employee and customers whose age is greater than 25 using with clause.

[quickstart.cloudera:21000] > 
   with t1 as (select * from customers where age>25), 
   t2 as (select * from employee where age>25) 
   (select * from t1 union select * from t2);

On executing, the above query gives the following output.

Query: with t1 as (select * from customers where age>25), t2 as (select * from employee where age>25) 
   (select * from t1 union select * from t2)
+----+---------+-----+-----------+--------+ 
| id | name    | age | address   | salary | 
+----+---------+-----+-----------+--------+ 
| 3  | mahesh  | 54  | Chennai   | 55000  | 
| 1  | subhash | 34  | Delhi     | 40000  | 
| 2  | ramesh  | 44  | Chennai   | 50000  | 
| 5  | Hardik  | 27  | Bhopal    | 40000  | 
| 4  | Rupesh  | 64  | Delhi     | 60000  | 
| 1  | Ramesh  | 32  | Ahmedabad | 20000  | 
+----+---------+-----+-----------+--------+ 
Fetched 6 row(s) in 1.73s

Impala - Distinct Operator

The distinct operator in Impala is used to get the unique values by removing duppcates.

Syntax

Following is the syntax of the distinct operator.

select distinct columns… from table_name;

Example

Assume that we have a table named customers in Impala and its contents are as follows −

[quickstart.cloudera:21000] > select distinct id, name, age, salary from customers; 
Query: select distinct id, name, age, salary from customers

Here you can observe the salary of the customers Ramesh and Chaitap entered twice and using the distinct operator, we can select the unique values as shown below.

[quickstart.cloudera:21000] > select distinct name, age, address from customers;

On executing, the above query gives the following output.

Query: select distinct id, name from customers
+----------+-----+-----------+ 
| name     | age | address   | 
+----------+-----+-----------+ 
| Ramesh   | 32  | Ahmedabad |
| Khilan   | 25  | Delhi     | 
| kaushik  | 23  | Kota      | 
| Chaitap | 25  | Mumbai    |
| Hardik   | 27  | Bhopal    |
| Komal    | 22  | MP        | 
+----------+-----+-----------+
Fetched 9 row(s) in 1.46s