通过

DESCRIBE TABLE

  • 项目

适用于：勾选“是” Databricks SQL 勾选“是” Databricks Runtime

返回表的基本元数据信息。 元数据信息包括列名、列类型和列注释。 可根据需要指定分区规范或列名称，以分别返回与分区或列有关的元数据。 使用 Delta 表时，不会返回所有字段。

语法

{ DESC | DESCRIBE } [ TABLE ] [ EXTENDED | FORMATTED ] table_name { [ PARTITION clause ] | [ column_name ] }

参数

  • EXTENDED 或 FORMATTED

    如果指定了该参数，则显示有关指定列的详细信息，包括命令收集的列统计信息和其他元数据（例如架构限定符、所有者和访问时间）。

  • table_name

    标识要描述的表。 名称不得使用时态规范或选项规范。 如果找不到表，Azure Databricks 会引发 TABLE_OR_VIEW_NOT_FOUND 错误。

  • PARTITION 子句

    一个可选参数，指示 Databricks SQL 为命名分区返回附加元数据。

  • column_name

    一个可选参数，具有需说明的列名。 目前不允许指定嵌套列。

参数partition_spec和column_name互斥，不能同时指定。

示例

-- Creates a table `customer`. Assumes current schema 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 schema, 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.
> 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

DESCRIBE DETAIL [schema_name.]table_name

返回架构、分区、表大小等方面的信息。 例如，对于 Delta 表，你可以查看表的当前读取器和编写器版本。 请参阅使用 describe detail 查看 Delta Lake 表详细信息，了解详细信息架构。