拡張テーブルの表示 (SQL Analytics)SHOW TABLE EXTENDED (SQL Analytics)

指定された正規表現に一致するすべてのテーブルの情報を表示します。Shows information for all tables matching the given regular expression. 出力には、基本的なテーブル情報と、、、、、、、、、、、、、およびなどのファイルシステム情報が含まれ Last Access Created By Type Provider Table Properties Location Serde Library InputFormat OutputFormat Storage Properties Partition Provider Partition Columns Schema ます。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.

パーティション指定が存在する場合は、指定されたパーティションのファイルシステム固有の情報 (やなど) が出力され Partition Parameters Partition Statistics ます。If a partition specification is present, it outputs the given partition’s file-system-specific information such as Partition Parameters and Partition Statistics. テーブル regex をパーティション指定と共に使用することはできません。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 |} から 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. テーブル regex をパーティション指定と共に使用することはできません。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)