DESCRIBE TABLE (SQL Analytics)
Returns the basic metadata information of a table. The metadata information includes column name, column type and column comment. Optionally you can specify a partition spec or column name to return the metadata pertaining to a partition or column respectively.
Syntax
{ DESC | DESCRIBE } TABLE [EXTENDED] [ format ] table_identifier [ partition_spec ] [ col_name ]
EXTENDED
Display detailed information about the specified columns, including the column statistics collected by
the command ANALYZE TABLE table_name COMPUTE STATISTICS FOR COLUMNS column_name [column_name, ...].
Parameters
format
The optional format of describe output. If
EXTENDEDis specified then additional metadata information (such as parent database, owner, and access time) is returned.table_identifier
[database_name.] table_name: A table name, optionally qualified with a database name.delta.`<path-to-table>`: The location of an existing Delta table.
partition_spec
An optional parameter that specifies a comma-separated list of key-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 The column name that needs to be described. The supplied column name may be optionally qualified. Parameters
partition_specandcol_nameare mutually exclusive and cannot 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
PARTITIONED BY (state);
INSERT INTO customer PARTITION (state = 'AR') VALUES (100, 'Mike');
-- 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| default| |
| Table| customer| |
| Owner| <TABLE OWNER>| |
| Created Time| Tue Apr 07 22:56:34 JST 2020| |
| Last Access| UNKNOWN| |
| Created By| <SPARK VERSION>| |
| Type| MANAGED| |
| Provider| parquet| |
| Location|file:/tmp/salesdb.db/custom...| |
| Serde Library|org.apache.hadoop.hive.ql.i...| |
| InputFormat|org.apache.hadoop.hive.ql.i...| |
| OutputFormat|org.apache.hadoop.hive.ql.i...| |
| Partition Provider| Catalog| |
+----------------------------+------------------------------+----------+
-- Returns partition metadata such as partitioning column name, column type and comment.
```sql
DESCRIBE TABLE EXTENDED 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 Inform...| | |
| Database| default| |
| Table| customer| |
| Partition Values| [state=AR]| |
| Location|file:/tmp/salesdb.db/custom...| |
| Serde Library|org.apache.hadoop.hive.ql.i...| |
| InputFormat|org.apache.hadoop.hive.ql.i...| |
| OutputFormat|org.apache.hadoop.hive.ql.i...| |
| Storage Properties|[serialization.format=1, pa...| |
| Partition Parameters|{transient_lastDdlTime=1586...| |
| Created Time| Tue Apr 07 23:05:43 JST 2020| |
| Last Access| UNKNOWN| |
| Partition Statistics| 659 bytes| |
| | | |
| # Storage Information| | |
| Location|file:/tmp/salesdb.db/custom...| |
| Serde Library|org.apache.hadoop.hive.ql.i...| |
| InputFormat|org.apache.hadoop.hive.ql.i...| |
| OutputFormat|org.apache.hadoop.hive.ql.i...| |
+------------------------------+------------------------------+----------+
-- 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|
+---------+----------+
DESCRIBE DETAIL (Delta Lake on Databricks)
DESCRIBE DETAIL [db_name.]table_name
DESCRIBE DETAIL delta.`<path-to-table>`
Return information about schema, partitioning, table size, and so on. For example, you can see the current reader and writer versions of a table.