How to get hive table delimiter or schema
When you have a hive table, you may want to check its delimiter or detailed information such as Schema. There are two solutions:
|
1 2 3 4 5 |
1. show create table <table_name> 2. describe extended <table_name>; 3. describe formatted <table_name>; |
Get the delimiter of a Hive Table
To get the field delimiter of a hive table, we can use the following command:
|
1 |
show create table <table_name> |
Here is an example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
hive> show create table student; OK CREATE TABLE `student`( `id` int, `name` string, `age` int, `score` float) COMMENT 'Description of the table' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 'hdfs://.../tmp/xx/hivedb/student' TBLPROPERTIES ( 'COLUMN_STATS_ACCURATE'='false', 'hcat.msgbus.topic.name'='hcat.xxxx.students_db.student', 'numFiles'='0', 'numRows'='-1', 'rawDataSize'='-1', 'totalSize'='0', 'transient_lastDdlTime'='1466792036') Time taken: 0.149 seconds, Fetched: 23 row(s) |
Get the schema of Hive Table
Another solution is to use:
|
1 |
describe extended <table_name>; |
This will generate a competed information about the table.
|
1 2 3 4 5 6 7 8 9 10 |
hive> describe extended student; OK id int name string age int score float Detailed Table Information Table(tableName:student, dbName:students_db, owner:xxx, createTime:1466792036, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:id, type:int, comment:null), FieldSchema(name:name, type:string, comment:null), FieldSchema(name:age, type:int, comment:null), FieldSchema(name:score, type:float, comment:null)], location:hdfs://xxx-nn1.blue.grid.xxx.com:8020/tmp/xxx/hivedb/student, 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:{line.delim= , field.delim= , serialization.format= }), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{}), storedAsSubDirectories:false), partitionKeys:[], parameters:{hcat.msgbus.topic.name=hcat.jet-blue.students_db.student, totalSize=0, numRows=-1, rawDataSize=-1, COLUMN_STATS_ACCURATE=false, numFiles=0, transient_lastDdlTime=1466792036, comment=Description of the table}, viewOriginalText:null, viewExpandedText:null, tableType:MANAGED_TABLE) Time taken: 0.048 seconds, Fetched: 7 row(s) |
