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

  1. 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

  1. SHOW TABLES;

SHOW VIEWS

Description

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

Syntax

  1. 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

  1. SHOW PARTITIONS table_name [ partition_spec ];
  2. partition_spec:
  3.   : (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

  1. -- list all partitions
  2. SHOW PARTITIONS t1;
  4. -- specific a full partition partition spec to list specific partition
  5. SHOW PARTITIONS t1 PARTITION (year = 2022, mohth = 12);
  7. -- specific a partial partition spec to list all the specifc partitions
  8. SHOW PARTITIONS t1 PARTITION (year = 2022);

SHOW FUNCTIONS

Description

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

Syntax

  1. SHOW FUNCTIONS;