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

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

Parameters

  • { IN|FROM } database_name

    Specifies 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.

  • 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

  1. -- Assumes `employee` table created with partitioned by column `grade`
  2. CREATE TABLE employee(name STRING, grade INT) PARTITIONED BY (grade);
  3. INSERT INTO employee PARTITION (grade = 1) VALUES ('sam');
  4. INSERT INTO employee PARTITION (grade = 2) VALUES ('suj');
  6.  -- Show the details of the table
  7. SHOW TABLE EXTENDED LIKE 'employee';
  8. +--------+---------+-----------+--------------------------------------------------------------+
  9. |database|tableName|isTemporary|                         information                          |
  10. +--------+---------+-----------+--------------------------------------------------------------+
  11. |default |employee |false      |Database: default
  12.                                 Table: employee
  13.                                 Owner: root
  14.                                 Created Time: Fri Aug 30 15:10:21 IST 2019
  15.                                 Last Access: Thu Jan 01 05:30:00 IST 1970
  16.                                 Created By: Spark 3.0.0-SNAPSHOT
  17.                                 Type: MANAGED
  18.                                 Provider: hive
  19.                                 Table Properties: [transient_lastDdlTime=1567158021]
  20.                                 Location: file:/opt/spark1/spark/spark-warehouse/employee
  21.                                 Serde Library: org.apache.hadoop.hive.serde2.lazy
  22.                                 .LazySimpleSerDe
  23.                                 InputFormat: org.apache.hadoop.mapred.TextInputFormat
  24.                                 OutputFormat: org.apache.hadoop.hive.ql.io
  25.                                 .HiveIgnoreKeyTextOutputFormat
  26.                                 Storage Properties: [serialization.format=1]
  27.                                 Partition Provider: Catalog
  28.                                 Partition Columns: [`grade`]
  29.                                 Schema: root
  30.                                  |-- name: string (nullable = true)
  31.                                  |-- grade: integer (nullable = true)
  33. +--------+---------+-----------+--------------------------------------------------------------+
  35. -- showing the multiple table details with pattern matching
  36. SHOW TABLE EXTENDED  LIKE 'employe*';
  37. +--------+---------+-----------+--------------------------------------------------------------+
  38. |database|tableName|isTemporary|                         information                          |
  39. +--------+---------+-----------+--------------------------------------------------------------+
  40. |default |employee |false      |Database: default
  41.                                 Table: employee
  42.                                 Owner: root
  43.                                 Created Time: Fri Aug 30 15:10:21 IST 2019
  44.                                 Last Access: Thu Jan 01 05:30:00 IST 1970
  45.                                 Created By: Spark 3.0.0-SNAPSHOT
  46.                                 Type: MANAGED
  47.                                 Provider: hive
  48.                                 Table Properties: [transient_lastDdlTime=1567158021]
  49.                                 Location: file:/opt/spark1/spark/spark-warehouse/employee
  50.                                 Serde Library: org.apache.hadoop.hive.serde2.lazy
  51.                                 .LazySimpleSerDe
  52.                                 InputFormat: org.apache.hadoop.mapred.TextInputFormat
  53.                                 OutputFormat: org.apache.hadoop.hive.ql.io
  54.                                 .HiveIgnoreKeyTextOutputFormat
  55.                                 Storage Properties: [serialization.format=1]
  56.                                 Partition Provider: Catalog
  57.                                 Partition Columns: [`grade`]
  58.                                 Schema: root
  59.                                  |-- name: string (nullable = true)
  60.                                  |-- grade: integer (nullable = true)
  62. |default |employee1|false      |Database: default
  63.                                 Table: employee1
  64.                                 Owner: root
  65.                                 Created Time: Fri Aug 30 15:22:33 IST 2019
  66.                                 Last Access: Thu Jan 01 05:30:00 IST 1970
  67.                                 Created By: Spark 3.0.0-SNAPSHOT
  68.                                 Type: MANAGED
  69.                                 Provider: hive
  70.                                 Table Properties: [transient_lastDdlTime=1567158753]
  71.                                 Location: file:/opt/spark1/spark/spark-warehouse/employee1
  72.                                 Serde Library: org.apache.hadoop.hive.serde2.lazy
  73.                                 .LazySimpleSerDe
  74.                                 InputFormat: org.apache.hadoop.mapred.TextInputFormat
  75.                                 OutputFormat: org.apache.hadoop.hive.ql.io
  76.                                 .HiveIgnoreKeyTextOutputFormat
  77.                                 Storage Properties: [serialization.format=1]
  78.                                 Partition Provider: Catalog
  79.                                 Schema: root
  80.                                  |-- name: string (nullable = true)
  82. +--------+---------+----------+---------------------------------------------------------------+
  84. -- show partition file system details
  85. SHOW TABLE EXTENDED  IN default LIKE 'employee' PARTITION (grade=1);
  86. +--------+---------+-----------+--------------------------------------------------------------+
  87. |database|tableName|isTemporary|                         information                          |
  88. +--------+---------+-----------+--------------------------------------------------------------+
  89. |default |employee |false      |Partition Values: [grade=1]
  90.                                 Location: file:/opt/spark1/spark/spark-warehouse/employee
  91.                                 /grade=1
  92.                                 Serde Library: org.apache.hadoop.hive.serde2.lazy
  93.                                 .LazySimpleSerDe
  94.                                 InputFormat: org.apache.hadoop.mapred.TextInputFormat
  95.                                 OutputFormat: org.apache.hadoop.hive.ql.io
  96.                                 .HiveIgnoreKeyTextOutputFormat
  97.                                 Storage Properties: [serialization.format=1]
  98.                                 Partition Parameters: {rawDataSize=-1, numFiles=1,
  99.                                 transient_lastDdlTime=1567158221, totalSize=4,
  100.                                 COLUMN_STATS_ACCURATE=false, numRows=-1}
  101.                                 Created Time: Fri Aug 30 15:13:41 IST 2019
  102.                                 Last Access: Thu Jan 01 05:30:00 IST 1970
  103.                                 Partition Statistics: 4 bytes
  104.                                                                                                                                                                           |
  105. +--------+---------+-----------+--------------------------------------------------------------+
  107. -- show partition file system details with regex fails as shown below
  108. SHOW TABLE EXTENDED  IN default LIKE 'empl*' PARTITION (grade=1);
  109. Error: Error running query: org.apache.spark.sql.catalyst.analysis.NoSuchTableException:
  110.  Table or view 'emplo*' not found in database 'default'; (state=,code=0)