SHOW TABLE EXTENDED
Description
SHOW TABLE EXTENDED will show 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. Note that a table regex
cannot be used with a partition specification.
Syntax
SHOW TABLE EXTENDED [ { IN | FROM } database_name ] LIKE regex_pattern
[ partition_spec ]
Parameters
-
{ IN
|FROM } database_nameSpecifies database name. If not provided, will use the current database.
-
regex_pattern
Specifies the regular expression pattern that is 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.
- Except for
-
partition_spec
An optional parameter that specifies a comma separated list of key and value pairs for partitions. Note that a table regex cannot be used with a partition specification.
Syntax:
PARTITION ( partition_col_name = partition_col_val [ , ... ] )
Examples
-- Assumes `employee` table created with 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-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 (nullable = true)
|-- grade: integer (nullable = true)
+--------+---------+-----------+--------------------------------------------------------------+
-- showing the 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-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 (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-SNAPSHOT
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 fails as shown below
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)