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;
单分区:
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;