SHOW Statements #

With Hive dialect, the following SHOW statements are supported for now:

• SHOW DATABASES
• SHOW TABLES
• SHOW VIEWS
• SHOW PARTITIONS
• SHOW FUNCTIONS

SHOW DATABASES #

Description #

SHOW DATABASES statement is used to list all the databases defined in the metastore.

Syntax #

SHOW (DATABASES|SCHEMAS);

The use of SCHEMA and DATABASE are interchangeable - they mean the same thing.

SHOW TABLES #

Description #

SHOW TABLES statement lists all the base tables and views in the current database.

Syntax #

SHOW TABLES;

SHOW VIEWS #

Description #

SHOW VIEWS statement lists all the views in the current database.

Syntax #

SHOW VIEWS;

SHOW PARTITIONS #

Description #

SHOW PARTITIONS lists all the existing partitions or the partitions matching the specified partition spec for a given base table.

Syntax #

SHOW PARTITIONS table_name [ partition_spec ];
partition_spec:
  : (partition_column = partition_col_value, partition_column = partition_col_value, ...)

Parameter #

• partition_spec

  The optional partition_spec is used to what kind of partition should be returned. When specified, the partitions that match the partition_spec specification are returned. The partition_spec can be partial which means you can specific only part of partition columns for listing the partitions.

Examples #

-- list all partitions
SHOW PARTITIONS t1;

-- specific a full partition partition spec to list specific partition
SHOW PARTITIONS t1 PARTITION (year = 2022, month = 12);

-- specific a partial partition spec to list all the specifc partitions
SHOW PARTITIONS t1 PARTITION (year = 2022);

SHOW FUNCTIONS #

Description #

SHOW FUNCTIONS statement is used to list all the user defined and builtin functions.

Syntax #

SHOW FUNCTIONS;