Impala - Shell

In the earper chapters, we have seen the installation of Impala using cloudera and its architecture.

Impala shell (command prompt)

Hue (User Interface)

ODBC and JDBC (Third party pbraries)

This chapter explains how to start Impala Shell and the various options of the shell.

Impala Shell Command Reference

The commands of Impala shell are classified as general commands, query specific options, and table and database specific options, as explained below.

General Commands

help

version

history

shell (or) !

connect

exit | quit

Query specific options

Set/unset

Profile

Explain

Table and Database specific options

Alter

describe

drop

insert

select

show

use

Starting Impala Shell

Open the cloudera terminal, sign in as superuser, and 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 – General Purpose Commands

The general purpose commands of Impala are explained below −

help command

The help command of Impala shell gives you a pst of the commands available in Impala −

[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

version command

The version command gives you the current version of Impala, as shown below.

[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)

history command

The history command of Impala displays the last 10 commands executed in the shell. Following is the example of the history command. 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;

quit/exit command

You can come out of the Impala shell using the quit or exit command, as shown below.

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

connect command

The connect command is used to connect to a given instance of Impala. In case you do not specify any instance, then it connects to the default port 21000 as shown below.

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

Impala Query Specific Options

The query specific commands of Impala accept a query. They are explained below −

Explain

The explain command 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

Profile

The profile command displays the low-level information about the recent query. This command is used for diagnosis and performance tuning of a query. Following is the example of a profile command. In this scenario, the profile command returns the low-level information of explain 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 Timepne: 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: 
      - CpentFetchWaitTimer: 107.969ms 
      - RowMateriapzationTimer: 0ns

Table and Database Specific Options

The following table psts out the table and data specific options in Impala.