DESCRIBE

Mode fokus

DESCRIBE - Amazon Athena

Menampilkan satu atau lebih kolom, termasuk kolom partisi, untuk tabel yang ditentukan. Perintah ini berguna untuk memeriksa atribut kolom kompleks.

Sinopsis


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

penting

Sintaks untuk pernyataan ini adalahDESCRIBE table_name, tidakDESCRIBE TABLE table_name. Menggunakan sintaks yang terakhir menghasilkan pesan kesalahan GAGAL: SemanticException [Kesalahan 10001]: Tabel tidak ditemukan tabel.

Parameter

[DIPERPANJANG | DIFORMAT]: Menentukan format output. Menghilangkan parameter ini menunjukkan nama kolom dan tipe data yang sesuai, termasuk kolom partisi, dalam format tabel. Menentukan FORMATTED tidak hanya menampilkan nama kolom dan tipe data dalam format tabel, tetapi juga informasi tabel dan penyimpanan terperinci. EXTENDEDmenampilkan informasi kolom dan tipe data dalam format tabel, dan metadata rinci untuk tabel dalam bentuk serial Thrift. Format ini kurang mudah dibaca dan berguna terutama untuk debugging.
[Partisi partition_spec]: Jika disertakan, daftar metadata untuk partisi yang ditentukan olehpartition_spec, tempatpartition_specdalam format(partition_column = partition_col_value, partition_column = partition_col_value, ...).
[col_name ([.field_name] | [. ' $ elem$ '] | [.' $ kunci$ '] | [.' $ nilai$ ']) *]: Menentukan kolom dan atribut untuk memeriksa. Anda dapat menentukan.field_nameuntuk elemen struct,'$elem$'untuk elemen larik,'$key$'untuk kunci peta, dan'$value$'untuk nilai peta. Anda dapat menentukan ini rekursif untuk lebih mengeksplorasi kolom kompleks.

Contoh


DESCRIBE orders


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

Kueri dan output berikut menunjukkan kolom dan informasi tipe data dari impressions tabel berdasarkan data sampel 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

Contoh query berikut dan output menunjukkan hasil untuk tabel yang sama ketika FORMATTED opsi digunakan.


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

Contoh query berikut dan output menunjukkan hasil untuk tabel yang sama ketika EXTENDED opsi digunakan. Informasi tabel rinci adalah output pada satu baris, tetapi diformat di sini untuk keterbacaan.


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)