DESCRIBE TABLE
適用於：
Databricks SQL Databricks Runtime
傳回數據表的基本元數據資訊。 元數據資訊包括資料行名稱、數據行類型和數據行批注。 您可以選擇性地指定分割區規格或數據行名稱，以分別傳回與分割區或數據行相關的元數據。 使用 Delta 數據表時，不會傳回所有欄位。
元數據會以報表表窗體或 JSON 檔的形式傳回。
重要
使用 DESCRIBE AS JSON 以程式設計方式剖析描述輸出。 非 JSON 報表格式可能會變更。
語法
{ DESC | DESCRIBE } [ TABLE ] [ EXTENDED ] table_name { [ PARTITION clause ] | [ column_name ] } [ AS JSON ]
為了相容性 FORMATTED 可以指定為 EXTENDED的同義字。
參數
EXTENDED如果指定顯示所指定數據行的詳細資訊，包括命令所收集的數據行統計數據，以及其他元數據資訊（例如架構限定符、擁有者和存取時間）。
-
識別要描述的數據表。 名稱不得使用 時態規格或選項規格。 如果無法找到資料表，Azure Databricks 會觸發 TABLE_OR_VIEW_NOT_FOUND 錯誤。
-
選擇性參數，指示 Databricks SQL 傳回具名數據分割的加法元數據。
-
具選擇性的參數，帶有需描述的欄名稱。 目前不允許指定巢狀數據行。
支援個別欄位的 JSON 格式。
AS JSON適用於：
Databricks Runtime 16.2 及更高版本選擇性地以 JSON 字串形式傳回數據表元數據，而不是人類可讀取的報表。 使用程式剖析結果時，請使用這個格式。
只有在指定
EXTENDED格式時才支援。
參數 partition_spec 和 column_name 互斥，不能一起指定。
JSON 格式化的輸出
指定 AS JSON 時，輸出會以 JSON 字串的形式傳回。 支援下列架構：
{
"table_name": "<table_name>",
"catalog_name": "<catalog_name>",
"schema_name": "<schema_name>",
"namespace": ["<schema_name>"],
"type": "<table_type>",
"provider": "<provider>",
"columns": [
{
"name": "<name>",
"type": <type_json>,
"comment": "<comment>",
"nullable": <boolean>,
"default": "<default_val>"
}
],
"partition_values": {
"<col_name>": "<val>"
},
"location": "<path>",
"view_text": "<view_text>",
"view_original_text": "<view_original_text>",
"view_schema_mode": "<view_schema_mode>",
"view_catalog_and_namespace": "<view_catalog_and_namespace>",
"view_query_output_columns": ["<col_name>"],
"comment": "<comment>",
"table_properties": {
"property1": "<property1>",
"property2": "<property2>"
},
"statistics": {
"num_rows": <count>,
"size_in_bytes": <bytes>,
"table_change_stats": {
"inserted": <count>,
"deleted": <count>,
"updated": <count>,
"change_percent": <percent_changed_float>
}
},
"storage_properties": {
"property1": "<property1>",
"property2": "<property2>"
},
"serde_library": "<serde_library>",
"input_format": "<input_format>",
"output_format": "<output_format>",
"num_buckets": <num_buckets>,
"bucket_columns": ["<col_name>"],
"sort_columns": ["<col_name>"],
"created_time": "<timestamp_ISO-8601>",
"created_by": "<created_by>",
"last_access": "<timestamp_ISO-8601>",
"partition_provider": "<partition_provider>"
}
以下是 <type_json>的架構定義：
| SQL 類型 | JSON 表示法 |
|---|---|
| TINYINT | { "name" : "tinyint" } |
| SMALLINT | { "name" : "smallint" } |
| INT | { "name" : "int" } |
| BIGINT | { "name" : "bigint" } |
| FLOAT | { "name" : "float" } |
| DOUBLE | { "name" : "double" } |
| DECIMAL（p， s） | { "name" : "decimal", "precision": p, "scale": s } |
| 字串 | { "name" : "string" } |
| VARCHAR（n） | { "name" : "varchar", "length": n } |
| CHAR（n） | { "name" : "char", "length": n } |
| 二元的 | { "name" : "binary" } |
| 布爾 | { "name" : "boolean" } |
| DATE | { "name" : "date" } |
| TIMESTAMP | { "name" : "timestamp_ltz" } |
| TIMESTAMP_NTZ | { "name" : "timestamp_ntz" } |
| 間隔 start_unit 至 end_unit | { "name" : "interval", "start_unit": "<start_unit>", "end_unit": "<end_unit>" } |
| 數組<元素類型> | { "name" : "array", "element_type": <type_json>, "element_nullable": <boolean_val> } |
| MAP<key_type，value_type> | { "name" : "map", "key_type": <type_json>, "value_type": <type_json>, "element_nullable": <boolean_val> } |
| 結構<field_name ...， ...> | { "name" : "struct", "fields": [ {"name" : "<field_name>", "type" : <type_json>, “nullable”: <boolean_val>, "comment": “<field_comment>”, "default": “<default_val>”}] } |
| 變體 | { "name" : "variant" } |
範例
-- 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
- Returns the table metadata in JSON format.
DESCRIBE EXTENDED customer AS JSON;
{
"table_name":"customer",
"catalog_name":"spark_catalog",
"schema_name":"default",
"namespace":["default"],
"columns":[
{"name":"cust_id","type":{"name":"integer"},"nullable":true},
{"name":"name","type":{"name":"string"},"comment":"Short name","nullable":true},
{"name":"state","type":{"name":"varchar","length":20},"nullable":true}],
"location": "file:/tmp/salesdb.db/custom...",
"created_time":"2020-04-07T14:05:43Z",
"last_access":"UNKNOWN",
"created_by":"None",
"type":"MANAGED",
"provider":"parquet",
"partition_provider":"Catalog",
"partition_columns":["state"]}
描述詳細數據
DESCRIBE DETAIL [schema_name.]table_name
傳回架構、數據分割、數據表大小等的相關信息。 例如，針對 Delta 數據表，您可以看到數據表的目前 讀取器和寫入器版本。 如需瞭解詳細資料的結構，請參閱 檢閱 Delta Lake 表格詳細資訊。