SHOW TABLE EXTENDEDSHOW TABLE EXTENDED

显示与给定正则表达式匹配的所有表的信息。Shows information for all tables matching the given regular expression. 输出包括基本表信息和文件系统信息,如 Last AccessCreated ByTypeProviderTable PropertiesLocationSerde LibraryInputFormatOutputFormatStorage PropertiesPartition ProviderPartition ColumnsSchemaOutput includes basic table information and file system information like Last Access, Created By, Type, Provider, Table Properties, Location, Serde Library, InputFormat, OutputFormat, Storage Properties, Partition Provider, Partition Columns, and Schema.

如果存在分区规范,则输出给定分区的特定于文件系统的信息,如 Partition ParametersPartition StatisticsIf a partition specification is present, it outputs the given partition’s file-system-specific information such as Partition Parameters and Partition Statistics. 无法对分区规范使用表正则表达式。You cannot use a table regex with a partition specification.

语法Syntax

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

参数Parameters

  • { IN | FROM } database_name{ IN | FROM } database_name

    指定数据库名。Specifies database name. 如果未提供,则使用当前数据库。If not provided, uses the current database.

  • regex_patternregex_pattern

    用于筛除不需要的表的正则表达式模式。The regular expression pattern used to filter out unwanted tables.

    • *| 字符外,该模式的工作方式类似于正则表达式。Except for * and | character, the pattern works like a regular expression.
    • 只有 * 则匹配 0 个或多个字符,| 用于分隔多个不同的正则表达式,其中任何一个表达式都可以匹配。* alone matches 0 or more characters and | is used to separate multiple different regular expressions, any of which can match.
    • 在处理前,在输入模式中删除前导空格和尾随空格。The leading and trailing blanks are trimmed in the input pattern before processing. 模式匹配不区分大小写。The pattern match is case-insensitive.
  • partition_specpartition_spec

    分区键值对的逗号分隔列表。A comma-separated list of key-value pairs for partitions. 无法对分区规范使用表正则表达式。You cannot use a table regex with a partition specification.

    语法: PARTITION ( partition_col_name = partition_col_val [ , ... ] )Syntax: PARTITION ( partition_col_name = partition_col_val [ , ... ] )

示例Examples

-- 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: Error running query: org.apache.spark.sql.catalyst.analysis.NoSuchTableException:
 Table or view 'emplo*' not found in database 'default'; (state=,code=0)