Impala Shell Command – Types of Impala Commands

In this Impala Shell tutorial, we will learn the Impala Shell Command. Moreover, we will study different types of Shell Commands in Impala: Table and Database specific options, General Commands, and Query specific options.

In addition we will see the various Impala Commands from this Impala Shell Commands. 

So, let’s start Impala Shell Commands.

What is Impala Shell Command?

Basically, to set up databases and tables, insert data, and issue queries, we can use the Impala shell tool (impala-shell). Moreover, we can submit SQL statements in an interactive session for ad hoc queries and exploration.

Also, to process a single statement or a script file or to process a single statement or a script file we can specify command-line options.

In addition, it supports all the same SQL statements listed in Impala SQL Statements along with some shell-only commands. Hence,  that we can use for tuning performance and diagnosing problems.

Starting Impala Shell

In order to start the Impala shell. Thus, follow several steps;

• At first, Open the Cloudera terminal.
• Afterward, sign in as superuser.
• Then, type Cloudera as password

as shown below.
[cloudera@quickstart ~]$ su
Password: cloudera
[root@quickstart cloudera]#
Start Impala shell by typing the following command −

[root@quickstart cloudera] # impala-shell
Starting Impala Shell without Kerberos authentication
Connected to quickstart.cloudera:21000
Server version: impalad version 2.3.0-cdh5.5.0 RELEASE
(build 0c891d79aa38f297d244855a32f1e17280e2129b)
*********************************************************************

Welcome to the Impala shell. Copyright (c) 2015 Cloudera, Inc. All rights reserved.
(Impala Shell v2.3.0-cdh5.5.0 (0c891d7) built on Mon Nov 9 12:18:12 PST 2015)

Want to know what version of Impala you’re connected to? Run the VERSION command to
find out!
*********************************************************************
[quickstart.cloudera:21000] >

Impala Command Reference

However, we can categorize the Impala Shell Commands in 3 ways. Such as general commands, query specific options, and table and database specific options. So, let’s discuss all three Impala commands in detail

a. General Commands

Impala Shell Command – Types of Impala Commands

i. Help Command
By using the help Impala Shell Command, we can access the complete list of the commands available −

[quickstart.cloudera:21000] > help;

Documented commands (type help <topic>):
========================================================
compute  describe insert  set unset with  version
connect  explain quit    show values use
exit     history profile select  shell tip
 
Undocumented commands:
=========================================
alter create desc drop help load summary

ii. Version Command
Basically, when we need to use the current version of Impala, apply the below version command.

[quickstart.cloudera:21000] > version;
Shell version: Impala Shell v2.3.0-cdh5.5.0 (0c891d7) built on Mon Nov 9
12:18:12 PST 2015

Server version: impalad version 2.3.0-cdh5.5.0 RELEASE (build
0c891d79aa38f297d244855a32f1e17280e2129b)

iii. History Command
The history Imapala Command displays the last 10 commands executed in the shell. Following is the example of the history command. So, here we have executed 5 commands, namely, version, help, show, use, and history.

[quickstart.cloudera:21000] > history;
[1]:version;
[2]:help;
[3]:show databases;
[4]:use my_db;
[5]:history;

iv. Quit/exit Command
Moreover, we use below quit or exit command in order to come out of the Impala shell.

[quickstart.cloudera:21000] > exit;
Goodbye cloudera

v. Connect Command
While we want to connect to a given instance of Impala we use the below Impala shell command. However, we can connect to the default port 21000 if in case we do not specify any instance.

[quickstart.cloudera:21000] > connect;
Connected to quickstart.cloudera:21000
Server version: impalad version 2.3.0-cdh5.5.0 RELEASE (build
0c891d79aa38f297d244855a32f1e17280e2129b)

b. Query Specific Options

Impala shell commands

i. Explain
While we need execution plan, we use Explain command. Basically, This Impala returns the execution plan for the given query.
[quickstart.cloudera:21000] > explain select * from sample;
Query: explain select * from sample
+————————————————————————————+
| Explain String                                                                     |
+————————————————————————————+
| Estimated Per-Host Requirements: Memory = 48.00MB VCores = 1                       |
| WARNING: The following tables are missing relevant table and/or column statistics. |
| my_db.customers                                                                    |
| 01:EXCHANGE [UNPARTITIONED]                                                        |
| 00:SCAN HDFS [my_db.customers]                                                     |
| partitions = 1/1 files = 6 size = 148B                                             |
+————————————————————————————+
Fetched 7 row(s) in 0.17s

ii. Profile
By the help of the profile command, we can see the low-level information about the recent query. In addition, for diagnosis and performance tuning of a query we use this Impala shell command. Let’s understand this with an example. Here, the profile command returns the low-level information explaining query.

[quickstart.cloudera:21000] > profile;

Query Runtime Profile:
Query (id=164b1294a1049189:a67598a6699e3ab6):

  Summary:
     Session ID: e74927207cd752b5:65ca61e630ad3ad
     Session Type: BEESWAX
     Start Time: 2016-04-17 23:49:26.08148000 End Time: 2016-04-17 23:49:26.2404000
     Query Type: EXPLAIN
     Query State: FINISHED
     Query Status: OK
     Impala Version: impalad version 2.3.0-cdh5.5.0 RELEASE (build 0c891d77280e2129b)
     User: cloudera
     Connected User: cloudera
     Delegated User:
     Network Address:10.0.2.15:43870
     Default Db: my_db
     Sql Statement: explain select * from sample
     Coordinator: quickstart.cloudera:22000
     : 0ns
     Query Timeline: 167.304ms
        – Start execution: 41.292us (41.292us) – Planning finished: 56.42ms (56.386ms)
        – Rows available: 58.247ms (1.819ms)
        – First row fetched: 160.72ms (101.824ms)
        – Unregister query: 166.325ms (6.253ms)
        
  ImpalaServer:
     – ClientFetchWaitTimer: 107.969ms
     – RowMaterializationTimer: 0ns

c. Table and Database Specific Options

Impala Shell Command – Types of Impala Commands

i. Alter
While we want to change the structure and name of a table we use the alter command in Impala.

ii. Describe
Moreover, when we need metadata of a table we use the describe Impala Shell Command. The Describe command carries the information like columns and their data types. Also, it has a shortcut as a desc.

iii. Drop
In order to remove a construct from Impala, we use the drop Impala shell command. Here, a construct refers to a table, a view, or a database function.

iv. Insert
However, there are several uses of the insert Impala Shell Command. Such as

1. Append data (columns) into a table.
2. Override the data of an existing table.
3. Override the data of an existing table.

v. Select
Moreover, to perform the desired operation on a particular dataset we use the select statement. Also. we can specify the dataset on which to complete some action, through this command. In addition, it is possible to print or store (in a file) the result of the select statement.

vi. Show
Also, to display the Metastore of various constructs, we use the show statement of Impala. For Example tables, databases, etc.

vii. Use
In order to change the current context to the desired database, we select the use statement of Impala.

So, this was all about Impala Shell Command. Hope you like our explanation.

Conclusion – Impala Shell Command

Hence, we have seen the whole concept of the Impala Shell Command. However, if any query occurs, please ask through the comment section.