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;