Since we deal with tables most of the time, there are a few useful table-information display commands, as follows:
- Show tables with regular expression filters:
> SHOW TABLES; -- Show all tables in database
> SHOW TABLES '*sam*'; -- Show tables name contains "sam"
> SHOW TABLES '*sam|lily*'; -- Show tables name contains "sam" or
"lily"
- List detailed table information for all tables matching the given regular expression:
>SHOW TABLE EXTENDED LIKE 'employee_int*';
OK
tableName:employee_internal
owner:dayongd
location:hdfs://localhost/user/hive/warehouse/employee_internal
inputformat:org.apache.hadoop.mapred.TextInputFormat
outputformat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyText
OutputFormatcolumns:struct columns { i32 num}
partitioned:false
partitionColumns:
totalNumberFiles:0
totalFileSize:0
maxFileSize:0
minFileSize:0
lastAccessTime:0
lastUpdateTime:1274517075221
- Show table-column information in two ways:
> SHOW COLUMNS IN employee_internal;
+---------------+
| field |
+---------------+
| name |
| work_place |
| gender_age |
| skills_score |
| depart_title |
+---------------+
5 rows selected (0.101 seconds)
> DESC employee_internal;
+--------------+-------------------------------+---------+
| col_name | data_type | comment |
+--------------+-------------------------------+---------+
| name | string | |
| work_place | array<string> | |
| gender_age | struct<gender:string,age:int> | |
| skills_score | map<string,int> | |
| depart_title | map<string,array<string>> | |
+---------------+------------------------------+---------+
5 rows selected (0.127 seconds)
- Show create-table DDL statements for the specified table:
> SHOW CREATE TABLE employee_internal;
+--------------------------------------------------------------+
| createtab_stmt |
+--------------------------------------------------------------+
| CREATE TABLE `employee_internal`( |
| `name` string, |
| `work_place` array<string>, |
| `gender_age` struct<gender:string,age:int>, |
| `skills_score` map<string,int>, |
| `depart_title` map<string,array<string>>) |
| COMMENT 'this is an internal table' |
| ROW FORMAT SERDE |
| 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' |
| WITH SERDEPROPERTIES ( |
| 'colelction.delim'=',', |
| 'field.delim'='|', |
| 'mapkey.delim'=':', |
| 'serialization.format'='|') |
| STORED as INPUTFORMAT |
| 'org.apache.hadoop.mapred.TextInputFormat' |
| OUTPUTFORMAT |
| 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' |
| LOCATION |
| 'hdfs://localhost:9000/user/hive/warehouse/employee_internal'|
| TBLPROPERTIES ( |
| 'transient_lastDdlTime'='1523108773') |
+--------------------------------------------------------------+
22 rows selected (0.191 seconds)
- Show table properties for the specified table:
> SHOW TBLPROPERTIES employee_internal;
+-----------------------+---------------------------+
| prpt_name | prpt_value |
+-----------------------+---------------------------+
| comment | this is an internal table |
| numFiles | 1 |
| numRows | 0 |
| rawDataSize | 0 |
| totalSize | 227 |
| transient_lastDdlTime | 1523108773 |
+-----------------------+---------------------------+
6 rows selected (0.095 seconds)