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)