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)