Description
DESCRIBE TABLE statement returns the basic metadata information of a table. The metadata information includes column name, column type and column comment. Optionally a partition spec or column name may be specified to return the metadata pertaining to a partition or column respectively.
Syntax
{ DESC | DESCRIBE } [ TABLE ] [ format ] table_identifier [ partition_spec ] [ col_name ]
Parameters
-
format - Specifies the optional format of describe output. If `EXTENDED` is specified then additional metadata information (such as parent database, owner, and access time) is returned.
-
table_identifier -
Specifies a table name, which may be optionally qualified with a database name.
Syntax:[ database_name. ] table_name -
partition_spec -
An optional parameter that specifies a comma separated list of key and value pairs for partitions. When specified, additional partition metadata is returned.
Syntax:PARTITION ( partition_col_name = partition_col_val [ , ... ] ) -
col_name -
An optional parameter that specifies the column name that needs to be described. The supplied column name may be optionally qualified. Parameters `partition_spec` and `col_name` are mutually exclusive and can not be specified together. Currently nested columns are not allowed to be specified.
Syntax:[ database_name. ] [ table_name. ] column_name
Examples
-- Creates a table customer. Assumes current database is salesdb. CREATE TABLE customer( cust_id INT, state VARCHAR(20), name STRING COMMENT 'Short name' ) USING parquet PARTITION BY state; ;
-- Returns basic metadata information for unqualified table customer DESCRIBE TABLE customer; +-----------------------+---------+----------+ |col_name |data_type|comment | +-----------------------+---------+----------+ |cust_id |int |null | |name |string |Short name| |state |string |null | |# Partition Information| | | |# col_name |data_type|comment | |state |string |null | +-----------------------+---------+----------+
-- Returns basic metadata information for qualified table customer DESCRIBE TABLE salesdb.customer; +-----------------------+---------+----------+ |col_name |data_type|comment | +-----------------------+---------+----------+ |cust_id |int |null | |name |string |Short name| |state |string |null | |# Partition Information| | | |# col_name |data_type|comment | |state |string |null | +-----------------------+---------+----------+
-- Returns additional metadata such as parent database, owner, access time etc. DESCRIBE TABLE EXTENDED customer; +----------------------------+------------------------------+----------+ |col_name |data_type |comment | +----------------------------+------------------------------+----------+ |cust_id |int |null | |name |string |Short name| |state |string |null | |# Partition Information | | | |# col_name |data_type |comment | |state |string |null | | | | | |# Detailed Table Information| | | |Database |salesdb | | |Table |customer | | |Owner |
-- Returns partition metadata such as partitioning column name, column type and comment. DESCRIBE TABLE customer PARTITION (state = 'AR');
+--------------------------------+-----------------------------------------+----------+ |col_name |data_type |comment | +--------------------------------+-----------------------------------------+----------+ |cust_id |int |null | |name |string |Short name| |state |string |null | |# Partition Information | | | |# col_name |data_type |comment | |state |string |null | | | | | |# Detailed Partition Information| | | |Database |salesdb | | |Table |customer | | |Partition Values |[state=AR] | | |Location |file:.../salesdb.db/customer/state=AR | | |Serde Library |...serde.ParquetHiveSerDe | | |InputFormat |...parquet.MapredParquetInputFormat | | |OutputFormat |...parquet.MapredParquetOutputFormat | | |Storage Properties |[path=file:.../salesdb.db/customer, | | | | serialization.format=1] | | |Partition Parameters |{rawDataSize=-1, numFiles=1l, | | | | transient_lastDdlTime=1567185245, | | | | totalSize=688, | | | | COLUMN_STATS_ACCURATE=false, numRows=-1}| | |Created Time |Fri Aug 30 10:14:05 PDT 2019 | | |Last Access |Wed Dec 31 16:00:00 PST 1969 | | |Partition Statistics |688 bytes | | +--------------------------------+-----------------------------------------+----------+
-- Returns the metadata for name column. -- Optional TABLE clause is omitted and column is fully qualified. DESCRIBE customer salesdb.customer.name; +---------+----------+ |info_name|info_value| +---------+----------+ |col_name |name | |data_type|string | |comment |Short name| +---------+----------+