0001 ---
0002 layout: global
0003 title: SHOW TABLE EXTENDED
0004 displayTitle: SHOW TABLE EXTENDED
0005 license: |
0006 Licensed to the Apache Software Foundation (ASF) under one or more
0007 contributor license agreements. See the NOTICE file distributed with
0008 this work for additional information regarding copyright ownership.
0009 The ASF licenses this file to You under the Apache License, Version 2.0
0010 (the "License"); you may not use this file except in compliance with
0011 the License. You may obtain a copy of the License at
0012
0013 http://www.apache.org/licenses/LICENSE-2.0
0014
0015 Unless required by applicable law or agreed to in writing, software
0016 distributed under the License is distributed on an "AS IS" BASIS,
0017 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0018 See the License for the specific language governing permissions and
0019 limitations under the License.
0020 ---
0021
0022 ### Description
0023
0024 `SHOW TABLE EXTENDED` will show information for all tables matching the given regular expression.
0025 Output includes basic table information and file system information like `Last Access`,
0026 `Created By`, `Type`, `Provider`, `Table Properties`, `Location`, `Serde Library`, `InputFormat`,
0027 `OutputFormat`, `Storage Properties`, `Partition Provider`, `Partition Columns` and `Schema`.
0028
0029 If a partition specification is present, it outputs the given partition's file-system-specific
0030 information such as `Partition Parameters` and `Partition Statistics`. Note that a table regex
0031 cannot be used with a partition specification.
0032
0033 ### Syntax
0034
0035 ```sql
0036 SHOW TABLE EXTENDED [ { IN | FROM } database_name ] LIKE regex_pattern
0037 [ partition_spec ]
0038 ```
0039
0040 ### Parameters
0041
0042 * **{ IN`|`FROM } database_name**
0043
0044 Specifies database name. If not provided, will use the current database.
0045
0046 * **regex_pattern**
0047
0048 Specifies the regular expression pattern that is used to filter out unwanted tables.
0049
0050 * Except for `*` and `|` character, the pattern works like a regular expression.
0051 * `*` alone matches 0 or more characters and `|` is used to separate multiple different regular expressions,
0052 any of which can match.
0053 * The leading and trailing blanks are trimmed in the input pattern before processing. The pattern match is case-insensitive.
0054
0055 * **partition_spec**
0056
0057 An optional parameter that specifies a comma separated list of key and value pairs
0058 for partitions. Note that a table regex cannot be used with a partition specification.
0059
0060 **Syntax:** `PARTITION ( partition_col_name = partition_col_val [ , ... ] )`
0061
0062 ### Examples
0063
0064 ```sql
0065 -- Assumes `employee` table created with partitioned by column `grade`
0066 CREATE TABLE employee(name STRING, grade INT) PARTITIONED BY (grade);
0067 INSERT INTO employee PARTITION (grade = 1) VALUES ('sam');
0068 INSERT INTO employee PARTITION (grade = 2) VALUES ('suj');
0069
0070 -- Show the details of the table
0071 SHOW TABLE EXTENDED LIKE 'employee';
0072 +--------+---------+-----------+--------------------------------------------------------------+
0073 |database|tableName|isTemporary| information |
0074 +--------+---------+-----------+--------------------------------------------------------------+
0075 |default |employee |false |Database: default
0076 Table: employee
0077 Owner: root
0078 Created Time: Fri Aug 30 15:10:21 IST 2019
0079 Last Access: Thu Jan 01 05:30:00 IST 1970
0080 Created By: Spark 3.0.0-SNAPSHOT
0081 Type: MANAGED
0082 Provider: hive
0083 Table Properties: [transient_lastDdlTime=1567158021]
0084 Location: file:/opt/spark1/spark/spark-warehouse/employee
0085 Serde Library: org.apache.hadoop.hive.serde2.lazy
0086 .LazySimpleSerDe
0087 InputFormat: org.apache.hadoop.mapred.TextInputFormat
0088 OutputFormat: org.apache.hadoop.hive.ql.io
0089 .HiveIgnoreKeyTextOutputFormat
0090 Storage Properties: [serialization.format=1]
0091 Partition Provider: Catalog
0092 Partition Columns: [`grade`]
0093 Schema: root
0094 |-- name: string (nullable = true)
0095 |-- grade: integer (nullable = true)
0096
0097 +--------+---------+-----------+--------------------------------------------------------------+
0098
0099 -- showing the multiple table details with pattern matching
0100 SHOW TABLE EXTENDED LIKE `employe*`;
0101 +--------+---------+-----------+--------------------------------------------------------------+
0102 |database|tableName|isTemporary| information |
0103 +--------+---------+-----------+--------------------------------------------------------------+
0104 |default |employee |false |Database: default
0105 Table: employee
0106 Owner: root
0107 Created Time: Fri Aug 30 15:10:21 IST 2019
0108 Last Access: Thu Jan 01 05:30:00 IST 1970
0109 Created By: Spark 3.0.0-SNAPSHOT
0110 Type: MANAGED
0111 Provider: hive
0112 Table Properties: [transient_lastDdlTime=1567158021]
0113 Location: file:/opt/spark1/spark/spark-warehouse/employee
0114 Serde Library: org.apache.hadoop.hive.serde2.lazy
0115 .LazySimpleSerDe
0116 InputFormat: org.apache.hadoop.mapred.TextInputFormat
0117 OutputFormat: org.apache.hadoop.hive.ql.io
0118 .HiveIgnoreKeyTextOutputFormat
0119 Storage Properties: [serialization.format=1]
0120 Partition Provider: Catalog
0121 Partition Columns: [`grade`]
0122 Schema: root
0123 |-- name: string (nullable = true)
0124 |-- grade: integer (nullable = true)
0125
0126 |default |employee1|false |Database: default
0127 Table: employee1
0128 Owner: root
0129 Created Time: Fri Aug 30 15:22:33 IST 2019
0130 Last Access: Thu Jan 01 05:30:00 IST 1970
0131 Created By: Spark 3.0.0-SNAPSHOT
0132 Type: MANAGED
0133 Provider: hive
0134 Table Properties: [transient_lastDdlTime=1567158753]
0135 Location: file:/opt/spark1/spark/spark-warehouse/employee1
0136 Serde Library: org.apache.hadoop.hive.serde2.lazy
0137 .LazySimpleSerDe
0138 InputFormat: org.apache.hadoop.mapred.TextInputFormat
0139 OutputFormat: org.apache.hadoop.hive.ql.io
0140 .HiveIgnoreKeyTextOutputFormat
0141 Storage Properties: [serialization.format=1]
0142 Partition Provider: Catalog
0143 Schema: root
0144 |-- name: string (nullable = true)
0145
0146 +--------+---------+----------+---------------------------------------------------------------+
0147
0148 -- show partition file system details
0149 SHOW TABLE EXTENDED IN default LIKE `employee` PARTITION (`grade=1`);
0150 +--------+---------+-----------+--------------------------------------------------------------+
0151 |database|tableName|isTemporary| information |
0152 +--------+---------+-----------+--------------------------------------------------------------+
0153 |default |employee |false |Partition Values: [grade=1]
0154 Location: file:/opt/spark1/spark/spark-warehouse/employee
0155 /grade=1
0156 Serde Library: org.apache.hadoop.hive.serde2.lazy
0157 .LazySimpleSerDe
0158 InputFormat: org.apache.hadoop.mapred.TextInputFormat
0159 OutputFormat: org.apache.hadoop.hive.ql.io
0160 .HiveIgnoreKeyTextOutputFormat
0161 Storage Properties: [serialization.format=1]
0162 Partition Parameters: {rawDataSize=-1, numFiles=1,
0163 transient_lastDdlTime=1567158221, totalSize=4,
0164 COLUMN_STATS_ACCURATE=false, numRows=-1}
0165 Created Time: Fri Aug 30 15:13:41 IST 2019
0166 Last Access: Thu Jan 01 05:30:00 IST 1970
0167 Partition Statistics: 4 bytes
0168 |
0169 +--------+---------+-----------+--------------------------------------------------------------+
0170
0171 -- show partition file system details with regex fails as shown below
0172 SHOW TABLE EXTENDED IN default LIKE `empl*` PARTITION (`grade=1`);
0173 Error: Error running query: org.apache.spark.sql.catalyst.analysis.NoSuchTableException:
0174 Table or view 'emplo*' not found in database 'default'; (state=,code=0)
0175 ```
0176
0177 ### Related Statements
0178
0179 * [CREATE TABLE](sql-ref-syntax-ddl-create-table.html)
0180 * [DESCRIBE TABLE](sql-ref-syntax-aux-describe-table.html)
