DESCRIBE TABLE
說明
DESCRIBE TABLE 陳述式會傳回資料表的基礎元資料資訊。元資料資訊包括欄位名稱、欄位類型和欄位註解。也可以選擇指定分割規格或欄位名稱，分別傳回分割或欄位的相關元資料。
語法
{ DESC | DESCRIBE } [ TABLE ] [ format ] table_identifier [ partition_spec ] [ col_name ]
參數
-
格式
指定 DESCRIBE 輸出的選用格式。如果指定
EXTENDED，則會傳回其他元資料資訊（例如父資料庫、擁有者和存取時間）。 -
資料表識別碼
指定資料表名稱，也可以選擇用資料庫名稱限定。
語法：
[ database_name. ] table_name -
分割規格
選用參數，指定分割的鍵值對清單。指定後，會傳回其他分割元資料。
語法：
PARTITION ( partition_col_name = partition_col_val [ , ... ] ) -
欄位名稱
選用參數，指定需要描述的欄位名稱。提供的欄位名稱也可以選擇限定。參數
partition_spec和col_name互斥，不能一起指定。目前不允許指定巢狀欄位。語法：
[ database_name. ] [ table_name. ] column_name
範例
-- Creates a table `customer`. Assumes current database is `salesdb`.
CREATE TABLE customer(
cust_id INT,
state VARCHAR(20),
name STRING COMMENT 'Short name'
)
USING parquet
PARTITIONED BY (state);
INSERT INTO customer PARTITION (state = 'AR') VALUES (100, 'Mike');
-- Returns basic metadata information for unqualified table `customer`
DESCRIBE TABLE customer;
+-----------------------+---------+----------+
| col_name|data_type| comment|
+-----------------------+---------+----------+
| cust_id| int| null|
| name| string|Short name|
| state| string| null|
|# Partition Information| | |
| # col_name|data_type| comment|
| state| string| null|
+-----------------------+---------+----------+
-- Returns basic metadata information for qualified table `customer`
DESCRIBE TABLE salesdb.customer;
+-----------------------+---------+----------+
| col_name|data_type| comment|
+-----------------------+---------+----------+
| cust_id| int| null|
| name| string|Short name|
| state| string| null|
|# Partition Information| | |
| # col_name|data_type| comment|
| state| string| null|
+-----------------------+---------+----------+
-- Returns additional metadata such as parent database, owner, access time etc.
DESCRIBE TABLE EXTENDED customer;
+----------------------------+------------------------------+----------+
| col_name| data_type| comment|
+----------------------------+------------------------------+----------+
| cust_id| int| null|
| name| string|Short name|
| state| string| null|
| # Partition Information| | |
| # col_name| data_type| comment|
| state| string| null|
| | | |
|# Detailed Table Information| | |
| Database| default| |
| Table| customer| |
| Owner| <TABLE OWNER>| |
| Created Time| Tue Apr 07 22:56:34 JST 2020| |
| Last Access| UNKNOWN| |
| Created By| <SPARK VERSION>| |
| Type| MANAGED| |
| Provider| parquet| |
| Location|file:/tmp/salesdb.db/custom...| |
| Serde Library|org.apache.hadoop.hive.ql.i...| |
| InputFormat|org.apache.hadoop.hive.ql.i...| |
| OutputFormat|org.apache.hadoop.hive.ql.i...| |
| Partition Provider| Catalog| |
+----------------------------+------------------------------+----------+
-- Returns partition metadata such as partitioning column name, column type and comment.
DESCRIBE TABLE EXTENDED customer PARTITION (state = 'AR');
+------------------------------+------------------------------+----------+
| col_name| data_type| comment|
+------------------------------+------------------------------+----------+
| cust_id| int| null|
| name| string|Short name|
| state| string| null|
| # Partition Information| | |
| # col_name| data_type| comment|
| state| string| null|
| | | |
|# Detailed Partition Inform...| | |
| Database| default| |
| Table| customer| |
| Partition Values| [state=AR]| |
| Location|file:/tmp/salesdb.db/custom...| |
| Serde Library|org.apache.hadoop.hive.ql.i...| |
| InputFormat|org.apache.hadoop.hive.ql.i...| |
| OutputFormat|org.apache.hadoop.hive.ql.i...| |
| Storage Properties|[serialization.format=1, pa...| |
| Partition Parameters|{transient_lastDdlTime=1586...| |
| Created Time| Tue Apr 07 23:05:43 JST 2020| |
| Last Access| UNKNOWN| |
| Partition Statistics| 659 bytes| |
| | | |
| # Storage Information| | |
| Location|file:/tmp/salesdb.db/custom...| |
| Serde Library|org.apache.hadoop.hive.ql.i...| |
| InputFormat|org.apache.hadoop.hive.ql.i...| |
| OutputFormat|org.apache.hadoop.hive.ql.i...| |
+------------------------------+------------------------------+----------+
-- Returns the metadata for `name` column.
-- Optional `TABLE` clause is omitted and column is fully qualified.
DESCRIBE customer salesdb.customer.name;
+---------+----------+
|info_name|info_value|
+---------+----------+
| col_name| name|
|data_type| string|
| comment|Short name|
+---------+----------+