DESCRIBE TABLE (SQL Analytics)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

显示有关指定列的详细信息,包括命令 ANALYZE TABLE table_name COMPUTE STATISTICS FOR COLUMNS column_name [column_name, ...] 收集的列统计信息。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

  • formatformat

    描述输出的可选格式。The optional format of describe output. 如果指定了 EXTENDED,则返回其他元数据信息(例如父数据库、所有者和访问时间)。If EXTENDED is specified then additional metadata information (such as parent database, owner, and access time) is returned.

  • table_identifiertable_identifier

    • [database_name.] table_name:表名,可选择使用数据库名称进行限定。[database_name.] table_name: A table name, optionally qualified with a database name.
    • delta.`<path-to-table>`:现有 Delta 表的位置。delta.`<path-to-table>`: The location of an existing Delta table.
  • partition_specpartition_spec

    一个可选参数,用于指定分区键值对的逗号分隔列表。An optional parameter that specifies a comma-separated list of key-value pairs for partitions. 指定后,将返回其他分区元数据。When specified, additional partition metadata is returned.

    语法: PARTITION ( partition_col_name = partition_col_val [ , ... ] )Syntax: PARTITION ( partition_col_name = partition_col_val [ , ... ] )

  • col_namecol_name

    一个可选参数,用于指定需说明的列名。An optional parameter that The column name that needs to be described. 提供的列名可选择性进行限定。The supplied column name may be optionally qualified. 参数 partition_speccol_name 互斥,不能同时指定。Parameters partition_spec and col_name are mutually exclusive and cannot be specified together. 目前不允许指定嵌套列。Currently nested columns are not allowed to be specified.

    语法: [database_name.] [ table_name. ] column_nameSyntax: [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(Azure Databricks 上的 Delta Lake) DESCRIBE DETAIL (Delta Lake on Azure 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.