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 CREATE VIEW [database_name].view_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*';
Cancellation: Cannot be cancelled.
Continue reading:
SHOW ROLES Statement
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 higher only). It does not apply when you use the Sentry framework with privileges defined in a policy file.
Security considerations:
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.
Examples:
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 | +-----------+
HDFS permissions: This statement does not touch any HDFS files or directories, therefore no HDFS permissions are required.
Related information:
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 higher only). It does not apply when you use the Sentry framework with privileges defined in a policy file.
Security considerations:
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.
Examples:
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 | +-----------+
HDFS permissions: This statement does not touch any HDFS files or directories, therefore no HDFS permissions are required.
Related information:
SHOW ROLE GRANT Statement
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 higher only). It does not apply when you use the Sentry framework with privileges defined in a policy file.
Security considerations:
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.
HDFS permissions: This statement does not touch any HDFS files or directories, therefore no HDFS permissions are required.
Related information:
SHOW GRANT ROLE Statement
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 higher only). It does not apply when you use the Sentry framework with privileges defined in a policy file.
Security considerations:
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.
HDFS permissions: This statement does not touch any HDFS files or directories, therefore no HDFS permissions are required.
Related information:
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.
Security considerations:
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.
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
HDFS permissions: This statement does not touch any HDFS files or directories, therefore no HDFS permissions are required.
Related information:
SHOW TABLES Statement, SHOW FUNCTIONS Statement, USE Statement
SHOW TABLES Statement
Displays the names of tables. By default, lists tables in the current database, or with the IN clause, in a specified database. By default, lists all tables, or with the LIKE clause, only those whose name match a pattern with * wildcards.
Security considerations:
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.
The user ID that the impalad daemon runs under, typically the impala user, must have read and execute permissions for all directories that are part of the table. (A table could span multiple different HDFS directories if it is partitioned. The directories could be widely scattered because a partition can reside in an arbitrary HDFS directory based on its LOCATION attribute.)
Examples:
The following examples demonstrate the SHOW TABLES statement. If the database contains no tables, the result set is empty. If the database does contain tables, SHOW TABLES IN db_name lists all the table names. SHOW TABLES with no qualifiers lists all the table names in the current database.
create database empty_db; show tables in empty_db; Fetched 0 row(s) in 0.11s create database full_db; create table full_db.t1 (x int); create table full_db.t2 like full_db.t1; show tables in full_db; +------+ | name | +------+ | t1 | | t2 | +------+ use full_db; show tables; +------+ | name | +------+ | t1 | | t2 | +------+
This example demonstrates how SHOW TABLES LIKE 'wildcard_pattern' lists table names that match a pattern, or multiple alternative patterns. The ability to do wildcard matches for table names makes it helpful to establish naming conventions for tables to conveniently locate a group of related tables.
create table fact_tbl (x int); create table dim_tbl_1 (s string); create table dim_tbl_2 (s string); /* Asterisk is the wildcard character. Only 2 out of the 3 just-created tables are returned. */ show tables like 'dim*'; +-----------+ | name | +-----------+ | dim_tbl_1 | | dim_tbl_2 | +-----------+ /* We are already in the FULL_DB database, but just to be sure we can specify the database name also. */ show tables in full_db like 'dim*'; +-----------+ | name | +-----------+ | dim_tbl_1 | | dim_tbl_2 | +-----------+ /* The pipe character separates multiple wildcard patterns. */ show tables like '*dim*|t*'; +-----------+ | name | +-----------+ | dim_tbl_1 | | dim_tbl_2 | | t1 | | t2 | +-----------+
HDFS permissions: This statement does not touch any HDFS files or directories, therefore no HDFS permissions are required.
Related information:
SHOW DATABASES, SHOW FUNCTIONS Statement, DESCRIBE Statement
SHOW CREATE TABLE Statement
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.
If you specify a view name in the SHOW CREATE TABLE, it returns a CREATE VIEW statement with column names and the original SQL statement to reproduce the view. You need the VIEW_METADATA privilege on the view and SELECT privilege on all underlying views and tables to successfully run the SHOW CREATE VIEW statement for a view. The SHOW CREATE VIEW is available as an alias for SHOW CREATE TABLE.
Security considerations:
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.
HDFS permissions: This statement does not touch any HDFS files or directories, therefore no HDFS permissions are required.
Examples:
The following example shows how various clauses from the CREATE TABLE statement are represented in the output of SHOW CREATE TABLE.
create table show_create_table_demo (id int comment "Unique ID", y double, s string)
partitioned by (year smallint)
stored as parquet;
show create table show_create_table_demo;
+----------------------------------------------------------------------------------------+
| result |
+----------------------------------------------------------------------------------------+
| CREATE TABLE scratch.show_create_table_demo ( |
| id INT COMMENT 'Unique ID', |
| y DOUBLE, |
| s STRING |
| ) |
| PARTITIONED BY ( |
| year SMALLINT |
| ) |
| STORED AS PARQUET |
| LOCATION 'hdfs://127.0.0.1:8020/user/hive/warehouse/scratch.db/show_create_table_demo' |
| TBLPROPERTIES ('transient_lastDdlTime'='1418152582') |
+----------------------------------------------------------------------------------------+
Related information:
SHOW CREATE VIEW Statement
The SHOW CREATE VIEW, it returns a CREATE VIEW statement with column names and the original SQL statement to reproduce the view. You need the VIEW_METADATA privilege on the view and SELECT privilege on all underlying views and tables to successfully run the SHOW CREATE VIEW statement for a view.
The SHOW CREATE VIEW is an alias for SHOW CREATE TABLE.
SHOW TABLE STATS Statement
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.
Any values that are not available (because the COMPUTE STATS statement has not been run yet) are displayed as -1.
SHOW TABLE STATS provides some general information about the table, such as the number of files, overall size of the data, whether some or all of the data is in the HDFS cache, and the file format, that is useful whether or not you have run the COMPUTE STATS statement. A -1 in the #Rows output column indicates that the COMPUTE STATS statement has never been run for this table. If the table is partitioned, SHOW TABLE STATS provides this information for each partition. (It produces the same output as the SHOW PARTITIONS statement in this case.)
The output of SHOW COLUMN STATS is primarily only useful after the COMPUTE STATS statement has been run on the table. A -1 in the #Distinct Values output column indicates that the COMPUTE STATS statement has never been run for this table. Currently, Impala always leaves the #Nulls column as -1, even after COMPUTE STATS has been run.
These SHOW statements work on actual tables only, not on views.
Security considerations:
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.
Examples:
The following examples show how the SHOW TABLE STATS statement displays physical information about a table and the associated data files:
show table stats store_sales; +-------+--------+----------+--------------+--------+-------------------+ | #Rows | #Files | Size | Bytes Cached | Format | Incremental stats | +-------+--------+----------+--------------+--------+-------------------+ | -1 | 1 | 370.45MB | NOT CACHED | TEXT | false | +-------+--------+----------+--------------+--------+-------------------+ show table stats customer; +-------+--------+---------+--------------+--------+-------------------+ | #Rows | #Files | Size | Bytes Cached | Format | Incremental stats | +-------+--------+---------+--------------+--------+-------------------+ | -1 | 1 | 12.60MB | NOT CACHED | TEXT | false | +-------+--------+---------+--------------+--------+-------------------+
The following example shows how, after a COMPUTE STATS or COMPUTE INCREMENTAL STATS statement, the #Rows field is now filled in. Because the STORE_SALES table in this example is not partitioned, the COMPUTE INCREMENTAL STATS statement produces regular stats rather than incremental stats, therefore the Incremental stats field remains false.
compute stats customer; +------------------------------------------+ | summary | +------------------------------------------+ | Updated 1 partition(s) and 18 column(s). | +------------------------------------------+ show table stats customer; +--------+--------+---------+--------------+--------+-------------------+ | #Rows | #Files | Size | Bytes Cached | Format | Incremental stats | +--------+--------+---------+--------------+--------+-------------------+ | 100000 | 1 | 12.60MB | NOT CACHED | TEXT | false | +--------+--------+---------+--------------+--------+-------------------+ compute incremental stats store_sales; +------------------------------------------+ | summary | +------------------------------------------+ | Updated 1 partition(s) and 23 column(s). | +------------------------------------------+ show table stats store_sales; +---------+--------+----------+--------------+--------+-------------------+ | #Rows | #Files | Size | Bytes Cached | Format | Incremental stats | +---------+--------+----------+--------------+--------+-------------------+ | 2880404 | 1 | 370.45MB | NOT CACHED | TEXT | false | +---------+--------+----------+--------------+--------+-------------------+
HDFS permissions:
The user ID that the impalad daemon runs under, typically the impala user, must have read and execute permissions for all directories that are part of the table. (A table could span multiple different HDFS directories if it is partitioned. The directories could be widely scattered because a partition can reside in an arbitrary HDFS directory based on its LOCATION attribute.) The Impala user must also have execute permission for the database directory, and any parent directories of the database directory in HDFS.
Related information:
See Table and Column Statistics for usage information and examples.
SHOW COLUMN STATS Statement
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.
Security considerations:
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.
Examples:
The following examples show the output of the SHOW COLUMN STATS statement for some tables, before the COMPUTE STATS statement is run. Impala deduces some information, such as maximum and average size for fixed-length columns, and leaves and unknown values as -1.
show column stats customer; +------------------------+--------+------------------+--------+----------+----------+ | Column | Type | #Distinct Values | #Nulls | Max Size | Avg Size | +------------------------+--------+------------------+--------+----------+----------+ | c_customer_sk | INT | -1 | -1 | 4 | 4 | | c_customer_id | STRING | -1 | -1 | -1 | -1 | | c_current_cdemo_sk | INT | -1 | -1 | 4 | 4 | | c_current_hdemo_sk | INT | -1 | -1 | 4 | 4 | | c_current_addr_sk | INT | -1 | -1 | 4 | 4 | | c_first_shipto_date_sk | INT | -1 | -1 | 4 | 4 | | c_first_sales_date_sk | INT | -1 | -1 | 4 | 4 | | c_salutation | STRING | -1 | -1 | -1 | -1 | | c_first_name | STRING | -1 | -1 | -1 | -1 | | c_last_name | STRING | -1 | -1 | -1 | -1 | | c_preferred_cust_flag | STRING | -1 | -1 | -1 | -1 | | c_birth_day | INT | -1 | -1 | 4 | 4 | | c_birth_month | INT | -1 | -1 | 4 | 4 | | c_birth_year | INT | -1 | -1 | 4 | 4 | | c_birth_country | STRING | -1 | -1 | -1 | -1 | | c_login | STRING | -1 | -1 | -1 | -1 | | c_email_address | STRING | -1 | -1 | -1 | -1 | | c_last_review_date | STRING | -1 | -1 | -1 | -1 | +------------------------+--------+------------------+--------+----------+----------+ show column stats store_sales; +-----------------------+-------+------------------+--------+----------+----------+ | Column | Type | #Distinct Values | #Nulls | Max Size | Avg Size | +-----------------------+-------+------------------+--------+----------+----------+ | ss_sold_date_sk | INT | -1 | -1 | 4 | 4 | | ss_sold_time_sk | INT | -1 | -1 | 4 | 4 | | ss_item_sk | INT | -1 | -1 | 4 | 4 | | ss_customer_sk | INT | -1 | -1 | 4 | 4 | | ss_cdemo_sk | INT | -1 | -1 | 4 | 4 | | ss_hdemo_sk | INT | -1 | -1 | 4 | 4 | | ss_addr_sk | INT | -1 | -1 | 4 | 4 | | ss_store_sk | INT | -1 | -1 | 4 | 4 | | ss_promo_sk | INT | -1 | -1 | 4 | 4 | | ss_ticket_number | INT | -1 | -1 | 4 | 4 | | ss_quantity | INT | -1 | -1 | 4 | 4 | | ss_wholesale_cost | FLOAT | -1 | -1 | 4 | 4 | | ss_list_price | FLOAT | -1 | -1 | 4 | 4 | | ss_sales_price | FLOAT | -1 | -1 | 4 | 4 | | ss_ext_discount_amt | FLOAT | -1 | -1 | 4 | 4 | | ss_ext_sales_price | FLOAT | -1 | -1 | 4 | 4 | | ss_ext_wholesale_cost | FLOAT | -1 | -1 | 4 | 4 | | ss_ext_list_price | FLOAT | -1 | -1 | 4 | 4 | | ss_ext_tax | FLOAT | -1 | -1 | 4 | 4 | | ss_coupon_amt | FLOAT | -1 | -1 | 4 | 4 | | ss_net_paid | FLOAT | -1 | -1 | 4 | 4 | | ss_net_paid_inc_tax | FLOAT | -1 | -1 | 4 | 4 | | ss_net_profit | FLOAT | -1 | -1 | 4 | 4 | +-----------------------+-------+------------------+--------+----------+----------+
The following examples show the output of the SHOW COLUMN STATS statement for some tables, after the COMPUTE STATS statement is run. Now most of the -1 values are changed to reflect the actual table data. The #Nulls column remains -1 because Impala does not use the number of NULL values to influence query planning.
compute stats customer; +------------------------------------------+ | summary | +------------------------------------------+ | Updated 1 partition(s) and 18 column(s). | +------------------------------------------+ compute stats store_sales; +------------------------------------------+ | summary | +------------------------------------------+ | Updated 1 partition(s) and 23 column(s). | +------------------------------------------+ show column stats customer; +------------------------+--------+------------------+--------+----------+--------+ | Column | Type | #Distinct Values | #Nulls | Max Size | Avg Size +------------------------+--------+------------------+--------+----------+--------+ | c_customer_sk | INT | 139017 | -1 | 4 | 4 | | c_customer_id | STRING | 111904 | -1 | 16 | 16 | | c_current_cdemo_sk | INT | 95837 | -1 | 4 | 4 | | c_current_hdemo_sk | INT | 8097 | -1 | 4 | 4 | | c_current_addr_sk | INT | 57334 | -1 | 4 | 4 | | c_first_shipto_date_sk | INT | 4374 | -1 | 4 | 4 | | c_first_sales_date_sk | INT | 4409 | -1 | 4 | 4 | | c_salutation | STRING | 7 | -1 | 4 | 3.1308 | | c_first_name | STRING | 3887 | -1 | 11 | 5.6356 | | c_last_name | STRING | 4739 | -1 | 13 | 5.9106 | | c_preferred_cust_flag | STRING | 3 | -1 | 1 | 0.9656 | | c_birth_day | INT | 31 | -1 | 4 | 4 | | c_birth_month | INT | 12 | -1 | 4 | 4 | | c_birth_year | INT | 71 | -1 | 4 | 4 | | c_birth_country | STRING | 205 | -1 | 20 | 8.4001 | | c_login | STRING | 1 | -1 | 0 | 0 | | c_email_address | STRING | 94492 | -1 | 46 | 26.485 | | c_last_review_date | STRING | 349 | -1 | 7 | 6.7561 | +------------------------+--------+------------------+--------+----------+--------+ show column stats store_sales; +-----------------------+-------+------------------+--------+----------+----------+ | Column | Type | #Distinct Values | #Nulls | Max Size | Avg Size | +-----------------------+-------+------------------+--------+----------+----------+ | ss_sold_date_sk | INT | 4395 | -1 | 4 | 4 | | ss_sold_time_sk | INT | 63617 | -1 | 4 | 4 | | ss_item_sk | INT | 19463 | -1 | 4 | 4 | | ss_customer_sk | INT | 122720 | -1 | 4 | 4 | | ss_cdemo_sk | INT | 242982 | -1 | 4 | 4 | | ss_hdemo_sk | INT | 8097 | -1 | 4 | 4 | | ss_addr_sk | INT | 70770 | -1 | 4 | 4 | | ss_store_sk | INT | 6 | -1 | 4 | 4 | | ss_promo_sk | INT | 355 | -1 | 4 | 4 | | ss_ticket_number | INT | 304098 | -1 | 4 | 4 | | ss_quantity | INT | 105 | -1 | 4 | 4 | | ss_wholesale_cost | FLOAT | 9600 | -1 | 4 | 4 | | ss_list_price | FLOAT | 22191 | -1 | 4 | 4 | | ss_sales_price | FLOAT | 20693 | -1 | 4 | 4 | | ss_ext_discount_amt | FLOAT | 228141 | -1 | 4 | 4 | | ss_ext_sales_price | FLOAT | 433550 | -1 | 4 | 4 | | ss_ext_wholesale_cost | FLOAT | 406291 | -1 | 4 | 4 | | ss_ext_list_price | FLOAT | 574871 | -1 | 4 | 4 | | ss_ext_tax | FLOAT | 91806 | -1 | 4 | 4 | | ss_coupon_amt | FLOAT | 228141 | -1 | 4 | 4 | | ss_net_paid | FLOAT | 493107 | -1 | 4 | 4 | | ss_net_paid_inc_tax | FLOAT | 653523 | -1 | 4 | 4 | | ss_net_profit | FLOAT | 611934 | -1 | 4 | 4 | +-----------------------+-------+------------------+--------+----------+----------+
HDFS permissions:
The user ID that the impalad daemon runs under, typically the impala user, must have read and execute permissions for all directories that are part of the table. (A table could span multiple different HDFS directories if it is partitioned. The directories could be widely scattered because a partition can reside in an arbitrary HDFS directory based on its LOCATION attribute.) The Impala user must also have execute permission for the database directory, and any parent directories of the database directory in HDFS.
Related information:
See Table and Column Statistics for usage information and examples.
SHOW PARTITIONS Statement
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:
Security considerations:
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.
Examples:
[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 | | +-------+-------+--------+------+---------+
HDFS permissions:
The user ID that the impalad daemon runs under, typically the impala user, must have read and execute permissions for all directories that are part of the table. (A table could span multiple different HDFS directories if it is partitioned. The directories could be widely scattered because a partition can reside in an arbitrary HDFS directory based on its LOCATION attribute.) The Impala user must also have execute permission for the database directory, and any parent directories of the database directory in HDFS.
Related information:
See Table and Column Statistics for usage information and examples.
SHOW FUNCTIONS Statement
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.
Security considerations:
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.
HDFS permissions: This statement does not touch any HDFS files or directories, therefore no HDFS permissions are required.
Examples:
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...) | ...
Related information:
Overview of Impala Functions, Impala Built-In Functions, Impala User-Defined Functions (UDFs), SHOW DATABASES, SHOW TABLES Statement