HCatalog SHOW

SHOW statements allow access to the HCatalog metastore for existing data. Below is a list of SHOW options available -

  • 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 metastore. The database and schema are interchangeable.

LIKE is an optional. When it is coded, it allows you to filter the list of databases using a regular expression created with wildcards. The wildcards you can use are '*' to represent any character(s) and '|' to indicate a choice.

Syntax -

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

Show Tables -

The `SHOW TABLES` command displays all the base tables and views in the current database. `SHOW TABLES` can filter table names using an optional regular expression. You can use wildcards such as `*`, which represents any character(s), or `|`, which indicates a choice between options. The matching tables will be listed in alphabetical order.

IN is an optional and is used to provide the database if it is not the current.

Syntax -

SHOW TABLES [IN database_name] ['identifier_with_wildcards'];

Example -

The following query displays a list of tables −

./hcat e "Show tables;"

On successful execution of the query, you get to see the following response −

OK
std_details_i
std_details_e
Time taken: 1.2 seconds

Show Partitions -

SHOW PARTITIONS displays all existing partitions for a given base table, listed in alphabetical order.

Syntax -

SHOW PARTITIONS table_name;

Show Table Properties (Version: Hive 0.10.0) -

SHOW TABLE PROPERTIES displays all the properties of the table.

Syntax -

SHOW TBLPROPERTIES tblname;

SHOW TBLPROPERTIES tblname("foo");

Show Create Table/View (Version: Hive 0.10.0) -

SHOW CREATE TABLE displays the CREATE TABLE statement used to create the specified table.

Syntax -

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

Show Indexes (Version: Hive 0.7.) -

SHOW INDEXES displays all the indexes associated with a specified column. It provides information such as the index name, table name, the names of the columns used as keys, the index table name, the index type, and any comments.

The keyword FORMATTED is optional and it prints column titles for each piece of information displayed.

Syntax -

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

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];

Show Functions -

SHOW FUNCTIONS displays all user-defined and built-in functions that match the regular expression. Use ".*" to view details of all functions.

Syntax -

SHOW FUNCTIONS ".*";