SHOW TABLE EXTENDED (SQL Analytics)

Shows information for all tables matching the given regular expression. Output 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.

If 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

    Specifies database name. If not provided, uses the current database.

  • regex_pattern

    The regular expression pattern used to filter out unwanted tables.

    • Except for * and | character, the pattern works like a regular expression.
    • * 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_spec

    A comma-separated list of key-value pairs for partitions. You cannot use a table regex with a partition specification.

    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)