显示扩展表

描述

SHOW TABLE EXTENDED 将显示与给定正则表达式匹配的所有表的信息。 输出包括基本的表信息和文件系统信息,如 Last Access Created By Type Provider Table Properties Location Serde Library InputFormat OutputFormat Storage Properties Partition Provider Partition Columns Schema

如果存在分区规范,它将输出给定分区的文件系统特定信息,例如 Partition Parameters Partition Statistics 。 请注意,分区规范不能与表正则表达式一起使用。

语法

SHOW TABLE EXTENDED [ { IN | FROM } database_name ] LIKE regex_pattern
[ partition_spec ]

参数

示例

-- 假设 `employee` 表是通过 `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 ();
-- 显示表的详细信息
SHOW TABLE EXTENDED LIKE ;
+--------+---------+-----------+--------------------------------------------------------------+
|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-SNAPSHOT
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 (可为空 = true)
|-- grade: integer (可为空 = true)
+--------+---------+-----------+--------------------------------------------------------------+
-- 显示多个表的详细信息并进行模式匹配
SHOW TABLE EXTENDED LIKE ;
+--------+---------+-----------+--------------------------------------------------------------+
|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-SNAPSHOT
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
Schema: root
|-- name: string (可为空 = true)
+--------+---------+----------+---------------------------------------------------------------+
-- 显示分区文件系统的详细信息
SHOW TABLE EXTENDED IN default LIKE 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 TABLE EXTENDED IN default LIKE PARTITION (grade=1);
Error: 错误 运行 查询: org.apache.spark.sql.catalyst.analysis.NoSuchTableException:
  视图  找到  数据库 ; (状态=,代码=0)