Using Impala shell

This topic describes how to download and install the Impala shell to query Impala Virtual Warehouses in the Cloudera Data Warehouse (CDW) service.

Required role: DWUser

You can install the Impala shell on a local computer and use it as a client to connect with an Impala Virtual Warehouse instance. If you are connecting from a node that is already a part of a CDH or CDP cluster, you already have Impala shell and do not need to install it.

Make sure that you have the latest stable version of Python 2.7 and a pip installer associated with that build of Python installed on the computer where you want to run the Impala shell.

Open a terminal window on the computer where you want to install the Impala shell, and run the following pip installer command to install the shell on your local computer:

pip install impala-shell

After you run this command, if your installation was successful, you receive success messages that are similar to the following messages:

Successfully built impala-shell bitarray prettytable sasl sqlparse thrift thrift-sasl
Installing collected packages: bitarray, prettytable, six, sasl, sqlparse, thrift, thrift-sasl, impala-shell
Successfully installed bitarray-1.0.1 impala-shell-3.3.0.dev20190730101121
prettytable-0.7.1 sasl-0.2.1 six-1.11.0 sqlparse-0.1.19 thrift-0.11.0 thrift-sasl-0.2.1

To confirm that the Impala shell has installed correctly, run the following command which displays the help for the tool:
```
impala-shell --help
```
If the tool help displays, the Impala shell is installed properly on your computer.
To connect to your Impala Virtual Warehouse instance using this installation of Impala shell:
1. Log in to the CDP web interface and navigate to the Data Warehouse service.
2. Go to the Virtual Warehouses tab, locate the Impala Virtual Warehouse you want to connect to, and select > Copy Impala shell command.
  
  This copies the shell command to your computer's clipboard. This command enables you to connect to the Virtual Warehouse instance in Cloudera Data Warehouse service using the Impala shell that is installed on your local computer.
In the terminal window on your local computer, at the command prompt, paste the command you just copied from your clipboard. The command might look something like this:
```
impala-shell --protocol='hs2-http' --ssl -i "tpcds-impala.your_company.com:443"
```

Press return and you are connected to the Impala Virtual Warehouse instance. A "Starting Impala Shell..." message similar to the following displays:

Starting Impala Shell without Kerberos authentication
SSL is enabled. Impala server certificates will NOT be verified (set --ca_cert to change)
Warning: --connect_timeout_ms is currently ignored with HTTP transport.
Opened TCP connection to
tpcds-impala.your_company.com:443
Connected to tpcds-impala.your_company.com:443
Server version: impalad version 3.4.0-SNAPSHOT RELEASE (build
d133a7140a3b97508ec77b1c73bb4f55f5dcb928)
*********************************************************************************
Welcome to the Impala shell.
(Impala Shell v3.3.0-SNAPSHOT (a509cff) built on Mon Jul 29 18:37:09 PDT 2019)
Every command must be terminated by a ';'.
*********************************************************************************

Run the following SQL command to confirm that you are connected properly to the Impala Virtual Warehouse instance:

SHOW DATABASES;

If you are connected properly, this SQL command should return the following type of information:

Query: show databases
+------------------------+----------------------------------------------+
| name                   | comment                                      |
+------------------------+----------------------------------------------+
| _impala_builtins       | System database for Impala builtin functions |                                                                                       
| default                | Default Hive database                        |                   |                        |                                              |
+------------------------+----------------------------------------------+
Fetched 2 row(s) in 0.30s

If you see a listing of databases similar to the above example, your installation is successful and you can use the shell to query the Impala Virtual Warehouse instance from your local computer.