Hive describe command to check the meta data of the Hive table

Posted on by

Contents

We can see the Hive tables structures using the Describe commands. This command shows meta data about the hive table which includes list of columns,data types and location of the table.There are three ways to describe a table in Hive.

Describe table_name:

If you want to see the primary information of the Hive table such as only the list of columns and its data types,the describe command will help you on this.

Syntax:

describe table_name;

Example:

hive> describe Customer_Txn;
OK
account_id              varchar(40)
txn_amount              decimal(16,4)
txn_type                varchar(40)
balance                 decimal(8,4)
interest_rate           decimal(8,4)
Time taken: 0.048 seconds, Fetched: 5 row(s)

Describe extended table_name:

The describe extended command will show the detailed information of the table such as list of columns , data type of the columns,table type,location of the table,table size and so on.

Syntax:

describe extended table_name;

Example:

hive> describe extended customer_Txn;
OK
account_id              varchar(40)
txn_amount              decimal(16,4)
txn_type                varchar(40)
balance                 decimal(8,4)
interest_rate           decimal(8,4)

Detailed Table Information      Table(tableName:customer_txn, dbName:bank_src,
owner:revisit_user, createTime:1553185725, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:account_id, type:varchar(40), 
comment:null), FieldSchema(name:txn_amount, type:decimal(16,4), comment:null), 
FieldSchema(name:txn_type, type:varchar(40), comment:null), 
FieldSchema(name:balance, type:decimal(8,4), comment:null), 
FieldSchema(name:interest_rate, type:decimal(8,4), comment:null)], 
location:hdfs://user/hive/wrehouse/bank_src.db/customer_txn, inputFormat:org.apache.hadoop.mapred.TextInputFormat, 
outputFormat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, parameters:{serialization.format=1}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{}), storedAsSubDirectories:false), partitionKeys:[], parameters:{totalSize=0, numRows=0, rawDataSize=0, COLUMN_STATS_ACCURATE={"BASIC_STATS":"true"}, numFiles=0, transient_lastDdlTime=1553185725}, viewOriginalText:null, viewExpandedText:null, tableType:MANAGED_TABLE)

Describe formatted table_name:

The describe formatted command returns the detailed table information in a clean manner. The results are easy to understand.The complete table information is displayed in a clean manner by describe formatted command

Syntax:

describe formatted table_name;

Example:

hive> describe formatted Customer_Txn;
OK
# col_name              data_type               comment

account_id              varchar(40)
txn_amount              decimal(16,4)
txn_type                varchar(40)
balance                 decimal(8,4)
interest_rate           decimal(8,4)

# Detailed Table Information
Database:               bank_src
Owner:                  revisit_user
CreateTime:             Thu Mar 21 09:28:45 PDT 2019
LastAccessTime:         UNKNOWN
Protect Mode:           None
Retention:              0
Location:               hdfs://user/hive/wrehouse/bank_src.db/customer_txn
Table Type:             MANAGED_TABLE
Table Parameters:
        COLUMN_STATS_ACCURATE   {\"BASIC_STATS\":\"true\"}
        numFiles                0
        numRows                 0
        rawDataSize             0
        totalSize               0
        transient_lastDdlTime   1553185725

# Storage Information
SerDe Library:          org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat:            org.apache.hadoop.mapred.TextInputFormat
OutputFormat:           org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed:             No
Num Buckets:            -1
Bucket Columns:         []
Sort Columns:           []
Storage Desc Params:
        serialization.format    1
Time taken: 0.04 seconds, Fetched: 35 row(s)

Recommended Articles