Hive（二）

1、创建分区表，注意关键字 partitioned by 
单分区：
create table day_table (id int, content string) 
partitioned by (dt string)
row format delimited
fields terminated by '\t'
location '/in/daytable';
双分区：
create table day_hour_table (id int, content string) 
partitioned by (dt string,hour string)
row format delimited
fields terminated by '\t'
location '/in/dayhourtable';


2、向分区表加载数据
语法;
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] 
INTO TABLE tablename 
[PARTITION (partcol1=val1, partcol2=val2 ...)]
示例：
单分区表加载数据
load data local inpath '/home/zkpk/daytable' 
overwrite into table day_table
partition (dt='20160829');


双分区表加载数据
load data local inpath '/home/zkpk/daytable' 
overwrite into table day_hour_table
partition (dt='20160829',hour="12");


load data local inpath '/home/zkpk/daytable' 
overwrite into table day_hour_table
partition (dt='20160829',hour="11");


load data local inpath '/home/zkpk/daytable' 
overwrite into table day_hour_table
partition (dt='20160830',hour="11");


基于分区进行查询：
SELECT day_table.* FROM day_table 
WHERE day_table.dt>= '20160829';


SELECT d.* FROM day_hour_table d
WHERE d.dt>= '20160829';


SELECT d.* FROM day_hour_table d
WHERE d.hour> '11';


3、删除分区表中分区的数据：
语法：
ALTER TABLE table_name DROP PARTITION
partition_spec, partition_spec,...
示例：
alter table day_table drop partition(dt='20160829');


查看分区语句：
show partitions 表名; 
show partitions day_hour_table;
========================================
视图
创建视图
create view sogou_view
as
select * from sogou limit 50;
查看视图
desc sogou_view;
使用视图
select count(distinct uid) from sogou_view where rank <=3;
删除视图
drop view sogou_view;