===分区表=================================================
分区表实例:按照时间来生成和管理日志文件
20160830
** 2016083001.log
** 2016083002.log
** 2016083003.log
...
20160831
** 2016083101.log
** 2016083102.log
...
20160901
20160902
...
--创建分区表
create table emp_part(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)
partitioned by(date string)
row format delimited fields terminated by '\t';
--向分区表加载数据
load data local inpath 'emp.txt' into table emp_part partition(date='20160830');
load data local inpath 'emp.txt' into table emp_part partition(date='20160831');
load data local inpath 'emp.txt' into table emp_part partition(date='20160901');
--分区表查询
hive (mydb)> select * from emp_part;
hive (mydb)> select * from emp_part where date='20160830';
----二级分区表------------------
create table if not exists track_log(
id string,
url string,
referer string,
keyword string,
type string,
guid string,
pageId string,
moduleId string,
linkId string,
attachedInfo string,
sessionId string,
trackerU string,
trackerType string,
ip string,
trackerSrc string,
cookie string,
orderCode string,
trackTime string,
endUserId string,
firstLink string,
sessionViewNo string,
productId string,
curMerchantId string,
provinceId string,
cityId string,
fee string,
edmActivity string,
edmEmail string,
edmJobId string,
ieVersion string,
platform string,
internalKeyword string,
resultSum string,
currentPage string,
linkPosition string,
buttonPosition string
)
partitioned by (date string,hour string)
row format delimited fields terminated by '\t';
--加载数据
load data local inpath '/home/tom/2015082818' into table mydb.track_log partition(date='20150828',hour='18');
load data local inpath '/home/tom/2015082819' into table mydb.track_log partition(date='20150828',hour='19');
--查询
select * from mydb.track_log where date='20150828';
select * from mydb.track_log where date='20150828' and hour='18';
select * from mydb.track_log where date='20150828' and hour='18' limit 10;
-----------
查看一个分区表有几个分区
hive (mydb)> show partitions track_log;
删除一个分区表的分区
hive (mydb)> alter table track_log drop partition(date='20150828',hour='19'); --同时删除HDFS上的对应分区目录
----分区表 + 外部表-----------
create external table track_log_ext(
id string,
url string,
referer string,
keyword string,
type string,
guid string,
pageId string,
moduleId string,
linkId string,
attachedInfo string,
s
分区表实例:按照时间来生成和管理日志文件
20160830
** 2016083001.log
** 2016083002.log
** 2016083003.log
...
20160831
** 2016083101.log
** 2016083102.log
...
20160901
20160902
...
--创建分区表
create table emp_part(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)
partitioned by(date string)
row format delimited fields terminated by '\t';
--向分区表加载数据
load data local inpath 'emp.txt' into table emp_part partition(date='20160830');
load data local inpath 'emp.txt' into table emp_part partition(date='20160831');
load data local inpath 'emp.txt' into table emp_part partition(date='20160901');
--分区表查询
hive (mydb)> select * from emp_part;
hive (mydb)> select * from emp_part where date='20160830';
----二级分区表------------------
create table if not exists track_log(
id string,
url string,
referer string,
keyword string,
type string,
guid string,
pageId string,
moduleId string,
linkId string,
attachedInfo string,
sessionId string,
trackerU string,
trackerType string,
ip string,
trackerSrc string,
cookie string,
orderCode string,
trackTime string,
endUserId string,
firstLink string,
sessionViewNo string,
productId string,
curMerchantId string,
provinceId string,
cityId string,
fee string,
edmActivity string,
edmEmail string,
edmJobId string,
ieVersion string,
platform string,
internalKeyword string,
resultSum string,
currentPage string,
linkPosition string,
buttonPosition string
)
partitioned by (date string,hour string)
row format delimited fields terminated by '\t';
--加载数据
load data local inpath '/home/tom/2015082818' into table mydb.track_log partition(date='20150828',hour='18');
load data local inpath '/home/tom/2015082819' into table mydb.track_log partition(date='20150828',hour='19');
--查询
select * from mydb.track_log where date='20150828';
select * from mydb.track_log where date='20150828' and hour='18';
select * from mydb.track_log where date='20150828' and hour='18' limit 10;
-----------
查看一个分区表有几个分区
hive (mydb)> show partitions track_log;
删除一个分区表的分区
hive (mydb)> alter table track_log drop partition(date='20150828',hour='19'); --同时删除HDFS上的对应分区目录
----分区表 + 外部表-----------
create external table track_log_ext(
id string,
url string,
referer string,
keyword string,
type string,
guid string,
pageId string,
moduleId string,
linkId string,
attachedInfo string,
s