SHOW TABLE EXTENDED
適用於：
Databricks SQL Databricks Runtime
顯示符合指定正則表示式之所有數據表的資訊。
輸出包含 Last Access、Created By、Type、Provider、Table Properties、Location、Serde Library、InputFormat、OutputFormat、Storage Properties、Partition Provider、Partition Columns和 Schema等基本數據表資訊和文件系統資訊。
如果資料分割規格存在，它會輸出指定的磁碟分區檔案系統特定資訊，例如 Partition Parameters 和 Partition Statistics。 您無法搭配分區規格使用資料表的正則表示式。
注意
為了與 Apache Spark 相容，此命令會使用 Apache Spark 表示法來轉譯數據表數據行類型。 在語言對應中描述了 SQL 的對應。 擷取數據表欄位資訊的其他方式為 DESCRIBE TABLE和 Unity Catalog 的 INFORMATION_SCHEMA。COLUMNS。
語法
SHOW TABLE EXTENDED [ { IN | FROM } schema_name ] LIKE regex_pattern
[ PARTITION clause ]
參數
-
指定架構名稱。 如果未提供，請使用目前的架構。
regex_pattern
用來篩選掉垃圾數據表的正則表達式模式。
-
*除了 和|字元以外，模式的運作方式就像正則表達式。 -
*單獨比對 0 或多個字元，並|用來分隔多個不同的正則表示式，其中任何一個都可以比對。 - 在處理之前，會先去除輸入模式中的前置和尾端空白。 模式比對不區分大小寫。
-
-
選擇性地指定分割區。 您無法將資料表的regex模式與
PARTITION子句一起使用。
範例
-- Assumes `employee` table partitioned by column `grade`
> CREATE TABLE employee(name STRING, grade INT) PARTITIONED BY (grade);
> INSERT INTO employee PARTITION (grade = 1) VALUES ('sam');
> INSERT INTO employee PARTITION (grade = 2) VALUES ('suj');
-- Show the details of the table
> SHOW TABLE EXTENDED LIKE 'employee';
database tableName isTemporary information
-------- --------- ----------- --------------------------------------------------------------
default employee false Database: default
Table: employee
Owner: root
Created Time: Fri Aug 30 15:10:21 IST 2019
Last Access: Thu Jan 01 05:30:00 IST 1970
Created By: Spark 3.0.0
Type: MANAGED
Provider: hive
Table Properties: [transient_lastDdlTime=1567158021]
Location: file:/opt/spark1/spark/spark-warehouse/employee
Serde Library: org.apache.hadoop.hive.serde2.lazy
.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io
.HiveIgnoreKeyTextOutputFormat
Storage Properties: [serialization.format=1]
Partition Provider: Catalog
Partition Columns: [`grade`]
Schema: root
-- name: string (nullable = true)
-- grade: integer (nullable = true)
-- show multiple table details with pattern matching
> SHOW TABLE EXTENDED LIKE 'employe*';
database tableName isTemporary information
-------- --------- ----------- --------------------------------------------------------------
default employee false Database: default
Table: employee
Owner: root
Created Time: Fri Aug 30 15:10:21 IST 2019
Last Access: Thu Jan 01 05:30:00 IST 1970
Created By: Spark 3.0.0
Type: MANAGED
Provider: hive
Table Properties: [transient_lastDdlTime=1567158021]
Location: file:/opt/spark1/spark/spark-warehouse/employee
Serde Library: org.apache.hadoop.hive.serde2.lazy
.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io
.HiveIgnoreKeyTextOutputFormat
Storage Properties: [serialization.format=1]
Partition Provider: Catalog
Partition Columns: [`grade`]
Schema: root
-- name: string (nullable = true)
-- grade: integer (nullable = true)
default employee1 false Database: default
Table: employee1
Owner: root
Created Time: Fri Aug 30 15:22:33 IST 2019
Last Access: Thu Jan 01 05:30:00 IST 1970
Created By: Spark 3.0.0
Type: MANAGED
Provider: hive
Table Properties: [transient_lastDdlTime=1567158753]
Location: file:/opt/spark1/spark/spark-warehouse/employee1
Serde Library: org.apache.hadoop.hive.serde2.lazy
.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io
.HiveIgnoreKeyTextOutputFormat
Storage Properties: [serialization.format=1]
Partition Provider: Catalog
Schema: root
-- name: string (nullable = true)
-- show partition file system details
> SHOW TABLE EXTENDED IN default LIKE 'employee' PARTITION (grade = 1);
database tableName isTemporary information
-------- --------- ----------- --------------------------------------------------------------
default employee false Partition Values: [grade=1]
Location: file:/opt/spark1/spark/spark-warehouse/employee
/grade=1
Serde Library: org.apache.hadoop.hive.serde2.lazy
.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io
.HiveIgnoreKeyTextOutputFormat
Storage Properties: [serialization.format=1]
Partition Parameters: {rawDataSize=-1, numFiles=1,
transient_lastDdlTime=1567158221, totalSize=4,
COLUMN_STATS_ACCURATE=false, numRows=-1}
Created Time: Fri Aug 30 15:13:41 IST 2019
Last Access: Thu Jan 01 05:30:00 IST 1970
Partition Statistics: 4 bytes
-- show partition file system details with regex fail
> SHOW TABLE EXTENDED IN default LIKE 'empl*' PARTITION (grade = 1);
Error: TABLE_OR_VIEW_NOT_FOUND