CREATE TABLE page_view(viewTime INT, userid BIGINT,p_date timestamp,
page_url STRING, referrer_url varchar(200),
ip STRING COMMENT 'IP Address of the User')
COMMENT 'This is the page view table'
PARTITIONED BY(dt STRING, country STRING)
STORED AS SEQUENCEFILE
TBLPROPERTIES ('creator'='wx','date'='2015-07-18');
CREATE TABLE students (name VARCHAR(64), age INT, gpa DECIMAL(3, 2))
CLUSTERED BY (age) INTO 2 BUCKETS STORED AS rcfile;
CREATE TABLE user_info_bucketed(user_id BIGINT, firstname STRING, lastname STRING)
COMMENT 'A bucketed copy of user_info'
PARTITIONED BY(ds STRING)
CLUSTERED BY(user_id) SORTED BY(firstname) INTO 3 BUCKETS;
Show Databases
hive> show databases;
default
test
hive> SHOW DATABASES LIKE 'te*';
test
hive> SHOW DATABASES LIKE 'te*|de*';
default
test
Show Tables
hive> SHOW TABLES;
students
user_info_bucketed
hive> SHOW TABLES IN test;
students
user_info_bucketed
hive> use test;
hive> SHOW TABLES 'user*';
user_info_bucketed
hive> SHOW TABLES 'user*|stu*';
students
user_info_bucketed
Show Partitions
hive> SHOW PARTITIONS user_info_bucketed;
ds=2015-07-20
ds=2015-07-25
ds=2015-07-30
hive> SHOW PARTITIONS user_info_bucketed PARTITION(ds='2015-07-25');
ds=2015-07-25
hive> SHOW PARTITIONS test.user_info_bucketed PARTITION(ds='2015-07-25');
ds=2015-07-25
hive> SHOW TABLE EXTENDED in test LIKE 'user_info_bucketed' PARTITION(ds='2015-07-20');
tableName:user_info_bucketed
owner:wx
location:hdfs://ns1/user/hive/warehouse/test.db/user_info_bucketed/ds=2015-07-20
inputformat:org.apache.hadoop.mapred.TextInputFormat
outputformat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
columns:struct columns { i64 user_id, string firstname, string lastname}
partitioned:true
partitionColumns:struct partition_columns { string ds}
totalNumberFiles:3
totalFileSize:72
maxFileSize:36
minFileSize:14
lastAccessTime:1437793073583
lastUpdateTime:1437823862864
Show Table Properties
hive> SHOW TBLPROPERTIES page_view;
comment This is the page view table
creator wx
date 2015-07-18
transient_lastDdlTime 1437825559
hive> SHOW TBLPROPERTIES page_view("creator");
wx
Show Create Table
hive> SHOW CREATE TABLE page_view;
CREATE TABLE `page_view`(
`viewtime` int,
`userid` bigint,
`p_date` timestamp,
`page_url` string,
`referrer_url` varchar(200),
`ip` string COMMENT 'IP Address of the User')
COMMENT 'This is the page view table'
PARTITIONED BY (
`dt` string,
`country` string)
ROW FORMAT SERDE
'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.SequenceFileInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat'
LOCATION
'hdfs://ns1/user/hive/warehouse/test.db/page_view'
TBLPROPERTIES (
'creator'='wx',
'date'='2015-07-18',
'transient_lastDdlTime'='1437825559')
hive> SHOW CREATE TABLE user_info_bucketed;
CREATE TABLE `user_info_bucketed`(
`user_id` bigint,
`firstname` string,
`lastname` string)
COMMENT 'A bucketed copy of user_info'
PARTITIONED BY (
`ds` string)
CLUSTERED BY (
user_id)
SORTED BY (
firstname ASC)
INTO 3 BUCKETS
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://ns1/user/hive/warehouse/test.db/user_info_bucketed'
TBLPROPERTIES (
'transient_lastDdlTime'='1437793010')
hive> SHOW CREATE TABLE test.students;
CREATE TABLE `test.students`(
`name` varchar(64),
`age` int,
`gpa` decimal(3,2))
CLUSTERED BY (
age)
INTO 2 BUCKETS
ROW FORMAT SERDE
'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.RCFileInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.RCFileOutputFormat'
LOCATION
'hdfs://ns1/user/hive/warehouse/test.db/students'
TBLPROPERTIES (
'transient_lastDdlTime'='1437793374')
Show Indexes
hive> SHOW INDEXES ON user_info_bucketed;
inx_1 user_info_bucketed user_id test__user_info_bucketed_inx_1__ compact index-my!!!!
hive> SHOW FORMATTED INDEXES ON user_info_bucketed;
idx_name tab_name col_names idx_tab_name idx_type comment
inx_1 user_info_bucketed user_id test__user_info_bucketed_inx_1__ compact index-my!!!!
hive> SHOW FORMATTED INDEXES ON user_info_bucketed IN test;
inx_1 user_info_bucketed user_id test__user_info_bucketed_inx_1__ compact index-my!!!!
Show Columns
hive> SHOW COLUMNS IN page_view;
viewtime
userid
p_date
page_url
referrer_url
ip
dt
country
hive> SHOW COLUMNS IN page_view in test;
viewtime
userid
p_date
page_url
referrer_url
ip
dt
country
Show Functions
hive> SHOW FUNCTIONS;
hive> SHOW FUNCTIONS "a.*";
SHOW FUNCTIONS is deprecated, please use SHOW FUNCTIONS LIKE instead.
abs
acos
add_months
and
array
array_contains
ascii
asin
assert_true
atan
avg
hive> SHOW FUNCTIONS LIKE "a*";
abs
acos
add_months
and
array
array_contains
ascii
asin
assert_true
atan
avg
hive> SHOW FUNCTIONS LIKE "*a*";
Show Conf
Note that SHOW CONF does not show the current value of a configuration property.
hive> SHOW CONF 'hive.exec.parallel';
false BOOLEAN Whether to execute jobs in parallel