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 thepartition_spec
specification are returned. Thepartition_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, mohth = 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;