Summary -

In this topic, we described about the below sections -

The Describe can be used on the below three to get/displays information for the specified type.

  • Describe Database
  • Describe Table/View/Column
  • Describe Partition

Describe Database (Version: Hive 0.7)

DESCRIBE DATABASE shows/displays the name of the database, its comment and the root location on the file system. The uses of SCHEMA and DATABASE are interchangeable. DESCRIBE SCHEMA is added in Hive 0.15. EXTENDED also shows the database properties.

Syntax -

DESCRIBE DATABASE [EXTENDED] db_name;
DESCRIBE SCHEMA [EXTENDED] db_name;

Describe Table/View/Column

DESCRIBE shows/displays the list of columns (including partition columns) for the given table. EXTENDED, FORMATTED keyword is optional. If the EXTENDED is specified, it will show/displays all the metadata for the specified table.

If the FORMATTED is specified, it will show/displays the metadata in a tabular format. DESCRIBE of View and Column is also same to get respective details specified. For a view, DESCRIBE EXTENDED or FORMATTED can be used to retrieve the view's definition.

Two formats are available to describe table/view/column syntax depending the database is specified or not.

Syntax – without DB specified:

DESCRIBE [EXTENDED|FORMATTED]  
  table_name[.col_name ( [.field_name] |
   [.'$elem$'] | [.'$key$'] | [.'$value$'] )* ];

Syntax – with DB specified:

DESCRIBE [EXTENDED|FORMATTED]  
  [db_name.]table_name[ col_name ( [.field_name] |
   [.'$elem$'] | [.'$key$'] | [.'$value$'] )* ];

Syntax – Column names:

DESCRIBE FORMATTED [db_name.]table_name column_name;
DESCRIBE FORMATTED [db_name.]table_name
 column_name PARTITION (partition_spec);

Describe Partition

Describe partition lists/describes metadata for a given partition. The output is very similar to that of DESCRIBE table_name. Currently, the column information associated with a particular partition is not used while preparing plans. Two formats for the describe partition syntax depending the database is specified or not.

Syntax – without DB Specified:

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

Syntax – with DB Specified:

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