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。使用后一种语法会导致出现错误消息 FAILED: SemanticException [Error 10001]: Table not found table（失败：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;

下面的查询和输出显示了来自 impressions 表的列和数据类型信息，该表基于 Amazon EMR 示例数据。

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)