DESCRIBE - Amazon Athena
概要參數

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

DESCRIBE

顯示指定資料表的一個或多個資料欄，包括分割區欄位。此指令有助於檢查複雜資料欄的屬性。

概要

DESCRIBE [EXTENDED | FORMATTED] [db_name.]table_name [PARTITION partition_spec] [col_name ( [.field_name] | [.'$elem$'] | [.'$key$'] | [.'$value$'] )]
重要

此陳述式的語法是 DESCRIBE table_name，而不是 DESCRIBE TABLE table_name。使用後一種語法會導致錯誤訊息失敗： SemanticException [錯誤 10001]：找不到資料表。

參數

[EXTENDED | FORMATTED]

判斷輸出的格式。省略這些參數會以表格格式顯示資料欄名稱及其對應的資料類型，包括分割區欄位。指定 FORMATTED 不僅以表格格式顯示資料欄名稱和資料類型，而且還顯示詳細的資料表和儲存資訊。EXTENDED 會以表格格式顯示資料欄和資料類型資訊，並以 Thrift 序列化格式顯示資料表的詳細中繼資料。此格式不易讀取，並且主要用於偵錯。

[PARTITION partition_spec]

如果包含，則會列出由 partition_spec 指定之分割區中繼資料，其中 partition_spec 的格式為 (partition_column = partition_col_value, partition_column = partition_col_value, ...)。

[col_name ( [.field_name] | [.'$elem$'] | [.'$key$'] | [.'$value$'] )* ]

指定要檢查的資料欄和屬性。您可以為結構的元素指定 .field_name，為陣列元素指定 '$elem$'，為對應金鑰指定 '$key$'，以及為對應值指定 '$value$'。您可以以遞迴的方式指定，以進一步探索複雜的資料欄。

範例

DESCRIBE orders
DESCRIBE FORMATTED mydatabase.mytable PARTITION (part_col = 100) columnA;

以下查詢和輸出顯示了基於 Amazon EMR 範例資料的 impressions 資料表的資料欄和資料類型。

DESCRIBE impressions
requestbegintime          string                                         from deserializer   
adid                      string                                         from deserializer   
impressionid              string                                         from deserializer   
referrer                  string                                         from deserializer   
useragent                 string                                         from deserializer   
usercookie                string                                         from deserializer   
ip                        string                                         from deserializer   
number                    string                                         from deserializer   
processid                 string                                         from deserializer   
browsercokie              string                                         from deserializer   
requestendtime            string                                         from deserializer   
timers                    struct<modellookup:string,requesttime:string>  from deserializer   
threadid                  string                                         from deserializer   
hostname                  string                                         from deserializer   
sessionid                 string                                         from deserializer   
dt                        string

# Partition Information
# col_name                data_type                 comment             

dt                        string

以下範例查詢和輸出顯示了使用 FORMATTED 選項時相同資料表的結果。

DESCRIBE FORMATTED impressions
requestbegintime          string                                         from deserializer
adid                      string                                         from deserializer
impressionid              string                                         from deserializer
referrer                  string                                         from deserializer
useragent                 string                                         from deserializer
usercookie                string                                         from deserializer
ip                        string                                         from deserializer
number                    string                                         from deserializer
processid                 string                                         from deserializer
browsercokie              string                                         from deserializer
requestendtime            string                                         from deserializer
timers                    struct<modellookup:string,requesttime:string>  from deserializer
threadid                  string                                         from deserializer
hostname                  string                                         from deserializer
sessionid                 string                                         from deserializer
dt                        string

# Partition Information
# col_name                data_type                 comment

dt                        string

# Detailed Table Information
Database:                 sampledb
Owner:                    hadoop
CreateTime:               Thu Apr 23 02:55:21 UTC 2020
LastAccessTime:           UNKNOWN
Protect Mode:             None
Retention:                0
Location:                 s3://us-east-1.elasticmapreduce/samples/hive-ads/tables/impressions
Table Type:               EXTERNAL_TABLE
Table Parameters:
        EXTERNAL                  TRUE
        transient_lastDdlTime     1587610521

# Storage Information
SerDe Library:                         org.openx.data.jsonserde.JsonSerDe
InputFormat:                           org.apache.hadoop.mapred.TextInputFormat
OutputFormat:                          org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat
Compressed:                            No
Num Buckets:                           -1
Bucket Columns:                        []
Sort Columns:                          []
Storage Desc Params:
        paths                                  requestbegintime, adid, impressionid, referrer, useragent, usercookie, ip
        serialization.format                   1

以下範例查詢和輸出顯示了使用 EXTENDED 選項時相同資料表的結果。資料表詳細資訊會在單一行上輸出，但在此處進行格式化以增加可讀性。

DESCRIBE EXTENDED impressions
requestbegintime          string                                         from deserializer
adid                      string                                         from deserializer
impressionid              string                                         from deserializer
referrer                  string                                         from deserializer
useragent                 string                                         from deserializer
usercookie                string                                         from deserializer
ip                        string                                         from deserializer
number                    string                                         from deserializer
processid                 string                                         from deserializer
browsercokie              string                                         from deserializer
requestendtime            string                                         from deserializer
timers                    struct<modellookup:string,requesttime:string>  from deserializer
threadid                  string                                         from deserializer
hostname                  string                                         from deserializer
sessionid                 string                                         from deserializer
dt                        string

# Partition Information
# col_name                data_type                 comment

dt                        string

Detailed Table Information       Table(tableName:impressions, dbName:sampledb, owner:hadoop, createTime:1587610521, 
lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:requestbegintime, type:string, comment:null), 
FieldSchema(name:adid, type:string, comment:null), FieldSchema(name:impressionid, type:string, comment:null), 
FieldSchema(name:referrer, type:string, comment:null), FieldSchema(name:useragent, type:string, comment:null), 
FieldSchema(name:usercookie, type:string, comment:null), FieldSchema(name:ip, type:string, comment:null), 
FieldSchema(name:number, type:string, comment:null), FieldSchema(name:processid, type:string, comment:null), 
FieldSchema(name:browsercokie, type:string, comment:null), FieldSchema(name:requestendtime, type:string, comment:null), 
FieldSchema(name:timers, type:struct<modellookup:string,requesttime:string>, comment:null), FieldSchema(name:threadid, 
type:string, comment:null), FieldSchema(name:hostname, type:string, comment:null), FieldSchema(name:sessionid, 
type:string, comment:null)], location:s3://us-east-1.elasticmapreduce/samples/hive-ads/tables/impressions, 
inputFormat:org.apache.hadoop.mapred.TextInputFormat, 
outputFormat:org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat, compressed:false, numBuckets:-1, 
serdeInfo:SerDeInfo(name:null, serializationLib:org.openx.data.jsonserde.JsonSerDe, parameters:{serialization.format=1, 
paths=requestbegintime, adid, impressionid, referrer, useragent, usercookie, ip}), bucketCols:[], sortCols:[], parameters:{}, 
skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{}), 
storedAsSubDirectories:false), partitionKeys:[FieldSchema(name:dt, type:string, comment:null)], 
parameters:{EXTERNAL=TRUE, transient_lastDdlTime=1587610521}, viewOriginalText:null, viewExpandedText:null, 
tableType:EXTERNAL_TABLE)