Hive Show Statement

SHOW statements allow querying the Hive metastore for existing data. Below is a list of SHOW options available to trigger on the metastore -

  • Show Databases/Schemas
  • Show Tables | Partitions | Indexes
    • Show Tables
    • Show Partitions
    • Show Table | Partition Extended
    • Show Table Properties
    • Show Create Table
    • Show Indexes
  • Show Columns
  • Show Functions

Show Database | Schemas -

`SHOW DATABASES` or `SHOW SCHEMAS` displays all the databases defined in the megastore.

The `LIKE` clause is optional in the `SHOW` command. When used, it allows you to filter the list of databases using a regular expression created with wildcards. The only wildcards you can use are '*' (which represents any character(s)) and '|' (which represents a choice).

Syntax -
SHOW (DATABASES|SCHEMAS) [LIKE 
 'identifier_with_wildcards'];
Examples -

Scenario1: Displaying all databases

SHOW DATABASES;

Scenario2: Using a pattern to filter results:

SHOW DATABASES LIKE 'sal*';

This shows databases whose names start with "sal".

Show Tables -

SHOW TABLES is used to display all the base tables and views in the current database. The optional keyword IN can be included to specify a different database if needed.

SHOW TABLES allows the use of an optional regular expression to filter table names. The wildcard characters that can be used are '*' to represent any character(s) and '|' for indicating a choice. The matching tables will be listed in alphabetical order.

If no tables match the provided criteria, it is not considered an error.

Syntax -
SHOW TABLES [IN database_name]
 ['identifier_with_wildcards'];
Examples -

Scenario1: Displaying all tables

SHOW TABLES;

Scenario2: To filter tables by name:

SHOW TABLES LIKE 'cust*';

Show Partitions:

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

Syntax -
SHOW PARTITIONS table_name;
Examples -

Scenario1: Display all partitions for table

SHOW PARTITIONS sales_partition;

Show Create Table | View (Version: Hive 0.10.0):

This command shows the table definition—how the table was originally created, including columns, data types, storage format, and partitions.

Syntax -
SHOW CREATE TABLE 
([db_name.]table_name|view_name);
Examples -

Scenario1: Displays the full CREATE TABLE command used to define the orders table.

SHOW CREATE TABLE orders;

Show Indexes (Version: Hive 0.7.):

SHOW INDEXES displays all the indexes related to the specified column. It provides detailed information, including the index name, table name, the names of the columns used as keys, index table name, index type, and any comments associated with the index. The keyword FORMATTED is optional; when used, it will include column titles for each piece of information displayed.

Syntax -
SHOW [FORMATTED] (INDEX|INDEXES) ON 
 table_with_index [(FROM|IN) db_name];
Examples -

Scenario1: Displays any indexes you have on the orders table.

SHOW INDEX ON orders;

Show Columns (Version: Hive 0.10.0):

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

Syntax -
SHOW COLUMNS (FROM|IN) table_name
 [(FROM|IN) db_name];
Examples -

Scenario1: Displays columns.

SHOW COLUMNS IN orders;

Show Functions:

Hive has many built-in functions (like SUM(), COUNT(), UPPER()). This command lists all available functions.

Syntax -
SHOW FUNCTIONS ".*";
Examples -

Scenario1: Displays avaiable functions.

SHOW FUNCTIONS;