Hive Show命令

Show databases

语法

SHOW (DATABASES | SCHEMAS) [LIKE 'identifier'];

举例

hive> show databases;
OK
default
tpcds_parquet
Time taken: 1.7 seconds, Fetched: 3 row(s)
hive> show schemas;
OK
default
tpcds_parquet
Time taken: 0.02 seconds, Fetched: 3 row(s)
hive> show databases like 'tpc*';
OK
tpcds_parquet
Time taken: 0.017 seconds, Fetched: 1 row(s)

Show Tables

语法

SHOW TABLES [IN database_name] ['identifier'];

举例

hive> show tables;
OK
aa
load_null
orc_store
original
test1
test111
test2
test3
test4
test_ext
Time taken: 0.029 seconds, Fetched: 18 row(s)
hive> show tables in default;
OK
aa
load_null
orc_store
original
test1
test111
test2
test3
test4
test_ext
Time taken: 0.021 seconds, Fetched: 18 row(s)
hive> show tables 'test*';
OK
test1
test111
test2
test3
test4
test_ext
Time taken: 0.021 seconds, Fetched: 9 row(s)

Show Views

与Show Tables类似

Show Partitions

语法

SHOW PARTITIONS table_name [PARTITION(parttion_spec)];

举例

hive> show partitions test_partition;
OK
dt=201412
dt=201413
dt=201601
dt=201602
dt=201603
Time taken: 0.171 seconds, Fetched: 5 row(s)

Show Table/Partition Extended

语法

SHOW TABLE EXTENDED [IN/FROM database_name] LIKE 'identifier' [PARTITION(partition_spec)];

举例

hive> show table extended like 'test_partition';
OK
tableName:test_partition
owner:root
location:hdfs://nameservice1/user/hive/warehouse/test_partition
inputformat:org.apache.hadoop.mapred.TextInputFormat
outputformat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
columns:struct columns { i32 a, varchar(10) b}
partitioned:true
partitionColumns:struct partition_columns { varchar(10) dt}
totalNumberFiles:unknown
totalFileSize:unknown
maxFileSize:unknown
minFileSize:unknown
lastAccessTime:unknown
lastUpdateTime:unknown

Show Create Table

语法

SHOW CREATE TABLE ([db_name.]table_name|view_name);

举例

hive> show create table default.test_partition;
OK
CREATE TABLE `default.test_partition`(
  `a` int,
  `b` varchar(10))
PARTITIONED BY (
  `dt` varchar(10))
ROW FORMAT SERDE
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://nameservice1/user/hive/warehouse/test_partition'
TBLPROPERTIES (
  'transient_lastDdlTime'='1489646826')
Time taken: 0.248 seconds, Fetched: 15 row(s)

以上是常用的show命令的语法及举例，完整的show命令，请参考官网地址：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-Show