Page History

These statements provide a way to query the Hive metastore for existing data and metadata accessible to this Hive system.

Show Databases

SHOW (DATABASES|SCHEMAS) [LIKE identifier_with_wildcards];

SHOW DATABASES lists all of the databases defined in the metastore. The optional LIKE clause allows the list of databases to be filtered using a regular expression. Wildcards in the regular expression can only be '' for any character(s) or '|' for a choice. Examples are 'employees', 'emp', 'emp*|*ees', all of which will match the database named 'employees'.

Show Tables/Partitions/Indexes

Show Tables

SHOW TABLES [IN database_name] [identifier_with_wildcards];

SHOW TABLES lists all the base tables and views in the current database (or the one explicitly named using the IN clause) with names matching the optional regular expression. Wildcards in the regular expression can only be '' for any character(s) or '|' for a choice. Examples are 'page_view', 'page_v', 'view|page', all which will match the 'page_view' table. Matching tables are listed in alphabetical order. It is not an error if there are no matching tables found in metastore. If no regular expression is given then all tables in the selected database are listed.

Show Partitions

SHOW PARTITIONS table_name

SHOW PARTITIONS lists all the existing partitions for a given base table. Partitions are listed in alphabetical order.

It is also possible to specify parts of a partition specification to filter the resulting list. For example:

SHOW PARTITIONS table_name PARTITION(ds='2010-03-03');            (Hive 0.6 and later)
SHOW PARTITIONS table_name PARTITION(hr='12');                    (Hive 0.6 and later)
SHOW PARTITIONS table_name PARTITION(ds='2010-03-03', hr='12');   (Hive 0.6 and later)

SHOW PARTITIONS [db_name.]table_name [PARTITION(partition_desc)]            (Hive 0.13.0 and later)

Example:

SHOW PARTITIONS databaseFoo.tableBar PARTITION(ds='2010-03-03', hr='12');   (Hive 0.13.0 and later)

Show Tables/Partitions Extended

SHOW TABLE EXTENDED [IN|FROM database_name] LIKE identifier_with_wildcards [PARTITION(partition_desc)]

SHOW TABLE EXTENDED will list information for all tables matching the given regular expression. Users cannot use regular expression for table name if a partition specification is present. This command's output includes basic table information and file system information like totalNumberFiles, totalFileSize, maxFileSize, minFileSize,lastAccessTime, and lastUpdateTime. If partition is present, it will output the given partition's file system information instead of table's file system information.

Show Table Properties

SHOW TBLPROPERTIES tblname;
SHOW TBLPROPERTIES tblname("foo");

The first form lists all of the table properties for the table in question one per row separated by tabs. The second form of the command prints only the value for the property that's being asked for.

Show Create Table

SHOW CREATE TABLE ([db_name.]table_name|view_name)

SHOW CREATE TABLE shows the CREATE TABLE statement that creates a given table, or the CREATE VIEW statement that creates a given view.

Show Indexes

SHOW [FORMATTED] (INDEX|INDEXES) ON table_with_index [(FROM|IN) db_name]

SHOW INDEXES shows all of the indexes on a certain column, as well as information about them: index name, table name, names of the columns used as keys, index table name, index type, and comment. If the FORMATTED keyword is used, then column titles are printed for each column.

Show Columns

SHOW COLUMNS (FROM|IN) table_name [(FROM|IN) db_name]

SHOW COLUMNS shows all the columns in a table including partition columns.

Show Functions

SHOW FUNCTIONS "a.*"

SHOW FUNCTIONS lists all the user defined and builtin functions matching the regular expression. To get all functions use ".*"

Show Granted Roles and Privileges

The Authorization document has information about these SHOW statements:

• SHOW ROLE GRANT
• SHOW GRANT