Hive Describe

The DESCRIBE command in Hive is used to get information about a database, table, view, or columns. It is a quick way to:

  • See the structure of a table (columns, data types).
  • View storage information like file format and location.
  • Understand the properties of a database or table.

Why Do We Need to Use DESCRIBE?

There are many situations where using DESCRIBE makes your life easier, especially when:

  • We forget what columns exist in a table.
  • We want to check the data types before writing a query.
  • We need to know where the data is stored in HDFS.
  • We want to double-check if the table has the right structure after creation.

Describe Database -

DESCRIBE DATABASE displays the name of the database, its comment, and the root location on the file system. DESCRIBE SCHEMA was introduced in Hive 0.15.

Syntax -
DESCRIBE DATABASE [EXTENDED] db_name;

DESCRIBE SCHEMA [EXTENDED] db_name;

If we want more detailed information about a table (like storage location, file format, SerDe properties), we can use DESCRIBE EXTENDED.

Examples -

Scenario: If we want detailed output, use:

DESCRIBE DATABASE EXTENDED sales_db;

This is very useful when managing multiple databases and we want to quickly find out where each one is stored.

Describe Table | View | Column -

The DESCRIBE command displays the list of columns, including partition columns, for a given table. The keywords EXTENDED and FORMATTED are optional. If EXTENDED is specified, it displays all the metadata for the specified table. If FORMATTED is specified, it presents the metadata in a tabular format.

Syntax –

This is the most commonly used command to see the columns and their data types of a table.

DESCRIBE table_name;

Sometimes we need to check the details of a single column in a table. 

DESCRIBE [EXTENDED|FORMATTED] table_name column_name;

Examples -

Scenario: To check only the email column:

DESCRIBE customer_data email;

Describe Partition -

Describe metadata for partition lists related to a specific partition.

Syntax –
DESCRIBE [EXTENDED|FORMATTED] table_name[.column_name]
 PARTITION partition_spec;