0001 ---
0002 layout: global
0003 title: DESCRIBE TABLE
0004 displayTitle: DESCRIBE TABLE
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 `DESCRIBE TABLE` statement returns the basic metadata information of a
0025 table. The metadata information includes column name, column type
0026 and column comment. Optionally a partition spec or column name may be specified
0027 to return the metadata pertaining to a partition or column respectively.
0028
0029 ### Syntax
0030
0031 ```sql
0032 { DESC | DESCRIBE } [ TABLE ] [ format ] table_identifier [ partition_spec ] [ col_name ]
0033 ```
0034
0035 ### Parameters
0036
0037 * **format**
0038
0039 Specifies the optional format of describe output. If `EXTENDED` is specified
0040 then additional metadata information (such as parent database, owner, and access time)
0041 is returned.
0042
0043 * **table_identifier**
0044
0045 Specifies a table name, which may be optionally qualified with a database name.
0046
0047 **Syntax:** `[ database_name. ] table_name`
0048
0049 * **partition_spec**
0050
0051 An optional parameter that specifies a comma separated list of key and value pairs
0052 for partitions. When specified, additional partition metadata is returned.
0053
0054 **Syntax:** `PARTITION ( partition_col_name = partition_col_val [ , ... ] )`
0055
0056 * **col_name**
0057
0058 An optional parameter that specifies the column name that needs to be described.
0059 The supplied column name may be optionally qualified. Parameters `partition_spec`
0060 and `col_name` are mutually exclusive and can not be specified together. Currently
0061 nested columns are not allowed to be specified.
0062
0063 **Syntax:** `[ database_name. ] [ table_name. ] column_name`
0064
0065 ### Examples
0066
0067 ```sql
0068 -- Creates a table `customer`. Assumes current database is `salesdb`.
0069 CREATE TABLE customer(
0070 cust_id INT,
0071 state VARCHAR(20),
0072 name STRING COMMENT 'Short name'
0073 )
0074 USING parquet
0075 PARTITIONED BY (state);
0076
0077 INSERT INTO customer PARTITION (state = 'AR') VALUES (100, 'Mike');
0078
0079 -- Returns basic metadata information for unqualified table `customer`
0080 DESCRIBE TABLE customer;
0081 +-----------------------+---------+----------+
0082 | col_name|data_type| comment|
0083 +-----------------------+---------+----------+
0084 | cust_id| int| null|
0085 | name| string|Short name|
0086 | state| string| null|
0087 |# Partition Information| | |
0088 | # col_name|data_type| comment|
0089 | state| string| null|
0090 +-----------------------+---------+----------+
0091
0092 -- Returns basic metadata information for qualified table `customer`
0093 DESCRIBE TABLE salesdb.customer;
0094 +-----------------------+---------+----------+
0095 | col_name|data_type| comment|
0096 +-----------------------+---------+----------+
0097 | cust_id| int| null|
0098 | name| string|Short name|
0099 | state| string| null|
0100 |# Partition Information| | |
0101 | # col_name|data_type| comment|
0102 | state| string| null|
0103 +-----------------------+---------+----------+
0104
0105 -- Returns additional metadata such as parent database, owner, access time etc.
0106 DESCRIBE TABLE EXTENDED customer;
0107 +----------------------------+------------------------------+----------+
0108 | col_name| data_type| comment|
0109 +----------------------------+------------------------------+----------+
0110 | cust_id| int| null|
0111 | name| string|Short name|
0112 | state| string| null|
0113 | # Partition Information| | |
0114 | # col_name| data_type| comment|
0115 | state| string| null|
0116 | | | |
0117 |# Detailed Table Information| | |
0118 | Database| default| |
0119 | Table| customer| |
0120 | Owner| <TABLE OWNER>| |
0121 | Created Time| Tue Apr 07 22:56:34 JST 2020| |
0122 | Last Access| UNKNOWN| |
0123 | Created By| <SPARK VERSION>| |
0124 | Type| MANAGED| |
0125 | Provider| parquet| |
0126 | Location|file:/tmp/salesdb.db/custom...| |
0127 | Serde Library|org.apache.hadoop.hive.ql.i...| |
0128 | InputFormat|org.apache.hadoop.hive.ql.i...| |
0129 | OutputFormat|org.apache.hadoop.hive.ql.i...| |
0130 | Partition Provider| Catalog| |
0131 +----------------------------+------------------------------+----------+
0132
0133 -- Returns partition metadata such as partitioning column name, column type and comment.
0134 DESCRIBE TABLE EXTENDED customer PARTITION (state = 'AR');
0135 +------------------------------+------------------------------+----------+
0136 | col_name| data_type| comment|
0137 +------------------------------+------------------------------+----------+
0138 | cust_id| int| null|
0139 | name| string|Short name|
0140 | state| string| null|
0141 | # Partition Information| | |
0142 | # col_name| data_type| comment|
0143 | state| string| null|
0144 | | | |
0145 |# Detailed Partition Inform...| | |
0146 | Database| default| |
0147 | Table| customer| |
0148 | Partition Values| [state=AR]| |
0149 | Location|file:/tmp/salesdb.db/custom...| |
0150 | Serde Library|org.apache.hadoop.hive.ql.i...| |
0151 | InputFormat|org.apache.hadoop.hive.ql.i...| |
0152 | OutputFormat|org.apache.hadoop.hive.ql.i...| |
0153 | Storage Properties|[serialization.format=1, pa...| |
0154 | Partition Parameters|{transient_lastDdlTime=1586...| |
0155 | Created Time| Tue Apr 07 23:05:43 JST 2020| |
0156 | Last Access| UNKNOWN| |
0157 | Partition Statistics| 659 bytes| |
0158 | | | |
0159 | # Storage Information| | |
0160 | Location|file:/tmp/salesdb.db/custom...| |
0161 | Serde Library|org.apache.hadoop.hive.ql.i...| |
0162 | InputFormat|org.apache.hadoop.hive.ql.i...| |
0163 | OutputFormat|org.apache.hadoop.hive.ql.i...| |
0164 +------------------------------+------------------------------+----------+
0165
0166 -- Returns the metadata for `name` column.
0167 -- Optional `TABLE` clause is omitted and column is fully qualified.
0168 DESCRIBE customer salesdb.customer.name;
0169 +---------+----------+
0170 |info_name|info_value|
0171 +---------+----------+
0172 | col_name| name|
0173 |data_type| string|
0174 | comment|Short name|
0175 +---------+----------+
0176 ```
0177
0178 ### Related Statements
0179
0180 * [DESCRIBE DATABASE](sql-ref-syntax-aux-describe-database.html)
0181 * [DESCRIBE QUERY](sql-ref-syntax-aux-describe-query.html)
0182 * [DESCRIBE FUNCTION](sql-ref-syntax-aux-describe-function.html)
