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 个或多个字符,|
用于分隔多个不同的正则表达式,其中任何一个表达式都可以匹配。 - 在处理前,在输入模式中删除前导空格和尾随空格。 模式匹配不区分大小写。
- 除
-
(可选)指定分区。 不能将表正则表达式模式与
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