This is the documentation for Cloudera Impala 2.0.0.
Documentation for other versions is available at Cloudera.com.

SHOW Statement

The SHOW statement is a flexible way to get information about different types of Impala objects. You can issue a SHOW object_type statement to see the appropriate objects in the current database, or SHOW object_type IN database_name to see objects in a specific database.

Syntax:

To display a list of available objects of a particular kind, issue these statements:

SHOW DATABASES [[LIKE] 'pattern']
SHOW SCHEMAS [[LIKE] 'pattern'] - an alias for SHOW DATABASES
SHOW TABLES [IN database_name] [[LIKE] 'pattern']
SHOW [AGGREGATE | ANALYTIC] FUNCTIONS [IN database_name] [[LIKE] 'pattern']
SHOW CREATE TABLE [database_name].table_name
SHOW TABLE STATS [database_name.]table_name
SHOW COLUMN STATS [database_name.]table_name
SHOW PARTITIONS [database_name.]table_name



SHOW ROLES
SHOW CURRENT ROLES

SHOW ROLE GRANT GROUP group_name
SHOW GRANT ROLE role_name

The optional pattern argument is a quoted string literal, using Unix-style * wildcards and allowing | for alternation. The preceding LIKE keyword is also optional. All object names are stored in lowercase, so use all lowercase letters in the pattern string. For example:

show databases 'a*';
show databases like 'a*';
show tables in some_db like '*fact*';
use some_db;
show tables '*dim*|*fact*';

Usage notes:

When authorization is enabled, the output of the SHOW statement is limited to those objects for which you have some privilege. There might be other database, tables, and so on, but their names are concealed. If you believe an object exists but you cannot see it in the SHOW output, check with the system administrator if you need to be granted a new privilege for that object. See Enabling Sentry Authorization for Impala for how to set up authorization and add privileges for specific kinds of objects.

SHOW ROLES:

The SHOW ROLES statement displays roles. This syntax is available in CDH 5.2 and later only, when you are using the Sentry authorization framework along with the Sentry service, as described in Using Impala with the Sentry Service (CDH 5.1 or later only). It does not apply when you use the Sentry framework with privileges defined in a policy file.

Depending on the roles set up within your organization by the CREATE ROLE statement, the output might look something like this:

show roles;
+-----------+
| role_name |
+-----------+
| analyst   |
| role1     |
| sales     |
| superuser |
| test_role |
+-----------+

SHOW CURRENT ROLE:

The SHOW CURRENT ROLE statement displays roles assigned to the current user. This syntax is available in CDH 5.2 and later only, when you are using the Sentry authorization framework along with the Sentry service, as described in Using Impala with the Sentry Service (CDH 5.1 or later only). It does not apply when you use the Sentry framework with privileges defined in a policy file.

Depending on the roles set up within your organization by the CREATE ROLE statement, the output might look something like this:

show current roles;
+-----------+
| role_name |
+-----------+
| role1     |
| superuser |
+-----------+

SHOW ROLE GRANT:

The SHOW ROLE GRANT statement lists all the roles assigned to the specified group. This statement is only allowed for Sentry administrative users and others users that are part of the specified group. This syntax is available in CDH 5.2 and later only, when you are using the Sentry authorization framework along with the Sentry service, as described in Using Impala with the Sentry Service (CDH 5.1 or later only). It does not apply when you use the Sentry framework with privileges defined in a policy file.

SHOW GRANT ROLE:

The SHOW GRANT ROLE statement list all the grants for the given role name. This statement is only allowed for Sentry administrative users and other users that have been granted the specified role. This syntax is available in CDH 5.2 and later only, when you are using the Sentry authorization framework along with the Sentry service, as described in Using Impala with the Sentry Service (CDH 5.1 or later only). It does not apply when you use the Sentry framework with privileges defined in a policy file.

SHOW DATABASES:

The SHOW DATABASES statement is often the first one you issue when connecting to an instance for the first time. You typically issue SHOW DATABASES to see the names you can specify in a USE db_name statement, then after switching to a database you issue SHOW TABLES to see the names you can specify in SELECT and INSERT statements.

The output of SHOW DATABASES includes the special _impala_builtins database, which lets you view definitions of built-in functions, as described under SHOW FUNCTIONS.

SHOW CREATE TABLE:

As a schema changes over time, you might run a CREATE TABLE statement followed by several ALTER TABLE statements. To capture the cumulative effect of all those statements, SHOW CREATE TABLE displays a CREATE TABLE statement that would reproduce the current structure of a table. You can use this output in scripts that set up or clone a group of tables, rather than trying to reproduce the original sequence of CREATE TABLE and ALTER TABLE statements. When creating variations on the original table, or cloning the original table on a different system, you might need to edit the SHOW CREATE TABLE output to change things such as the database name, LOCATION field, and so on that might be different on the destination system.

SHOW TABLE STATS, SHOW COLUMN STATS:

The SHOW TABLE STATS and SHOW COLUMN STATS variants are important for tuning performance and diagnosing performance issues, especially with the largest tables and the most complex join queries. See How Impala Uses Statistics for Query Optimization for usage information and examples.

SHOW PARTITIONS:

SHOW PARTITIONS displays information about each partition for a partitioned table. (The output is the same as the SHOW TABLE STATS statement, but SHOW PARTITIONS only works on a partitioned table.) Because it displays table statistics for all partitions, the output is more informative if you have run the COMPUTE STATS statement after creating all the partitions. See COMPUTE STATS Statement for details. For example, on a CENSUS table partitioned on the YEAR column:

[localhost:21000] > show partitions census;
+-------+-------+--------+------+---------+
| year  | #Rows | #Files | Size | Format  |
+-------+-------+--------+------+---------+
| 2000  | -1    | 0      | 0B   | TEXT    |
| 2004  | -1    | 0      | 0B   | TEXT    |
| 2008  | -1    | 0      | 0B   | TEXT    |
| 2010  | -1    | 0      | 0B   | TEXT    |
| 2011  | 4     | 1      | 22B  | TEXT    |
| 2012  | 4     | 1      | 22B  | TEXT    |
| 2013  | 1     | 1      | 231B | PARQUET |
| Total | 9     | 3      | 275B |         |
+-------+-------+--------+------+---------+

SHOW FUNCTIONS:

By default, SHOW FUNCTIONS displays user-defined functions (UDFs) and SHOW AGGREGATE FUNCTIONS displays user-defined aggregate functions (UDAFs) associated with a particular database. The output from SHOW FUNCTIONS includes the argument signature of each function. You specify this argument signature as part of the DROP FUNCTION statement. You might have several UDFs with the same name, each accepting different argument data types.

To display Impala built-in functions, specify the special database name _impala_builtins:

show functions in _impala_builtins;
+----------------+----------------------------------------+
| return type    | signature                              |
+----------------+----------------------------------------+
| BOOLEAN        | ifnull(BOOLEAN, BOOLEAN)               |
| TINYINT        | ifnull(TINYINT, TINYINT)               |
| SMALLINT       | ifnull(SMALLINT, SMALLINT)             |
| INT            | ifnull(INT, INT)                       |
...

show functions in _impala_builtins like '*week*';
+-------------+------------------------------+
| return type | signature                    |
+-------------+------------------------------+
| INT         | weekofyear(TIMESTAMP)        |
| TIMESTAMP   | weeks_add(TIMESTAMP, INT)    |
| TIMESTAMP   | weeks_add(TIMESTAMP, BIGINT) |
| TIMESTAMP   | weeks_sub(TIMESTAMP, INT)    |
| TIMESTAMP   | weeks_sub(TIMESTAMP, BIGINT) |
| INT         | dayofweek(TIMESTAMP)         |
+-------------+------------------------------+

To search for functions that use a particular data type, specify a case-sensitive data type name in all capitals:

show functions in _impala_builtins like '*BIGINT*';
+----------------------------------------+
| name                                   |
+----------------------------------------+
| adddate(TIMESTAMP, BIGINT)             |
| bin(BIGINT)                            |
| coalesce(BIGINT...)                    |
...

Examples:

This example shows how you might locate a particular table on an unfamiliar system. The DEFAULT database is the one you initially connect to; a database with that name is present on every system. You can issue SHOW TABLES IN db_name without going into a database, or SHOW TABLES once you are inside a particular database.

[localhost:21000] > show databases;
+--------------------+
| name               |
+--------------------+
| _impala_builtins   |
| analyze_testing    |
| avro               |
| ctas               |
| d1                 |
| d2                 |
| d3                 |
| default            |
| file_formats       |
| hbase              |
| load_data          |
| partitioning       |
| regexp_testing     |
| reports            |
| temporary          |
+--------------------+
Returned 14 row(s) in 0.02s
[localhost:21000] > show tables in file_formats;
+--------------------+
| name               |
+--------------------+
| parquet_table      |
| rcfile_table       |
| sequencefile_table |
| textfile_table     |
+--------------------+
Returned 4 row(s) in 0.01s
[localhost:21000] > use file_formats;
[localhost:21000] > show tables like '*parq*';
+--------------------+
| name               |
+--------------------+
| parquet_table      |
+--------------------+
Returned 1 row(s) in 0.01s

Cancellation: Cannot be cancelled.