1、创建分区表
create table dept_partition(
deptno int , dname string, loc string
)
partitioned by ( month string)
row format delimited fields terminated by '\t' ;
2、加载数据到分区表中
load data local inpath '/opt/module/datas/dept.txt' into table dept_partition partition ( month = '201709' ) ;
3、查询分区表中数据
select * from dept_partition where month = '201709' ;
4、多表联合查询
select * from dept_partition where month = '201709'
union
select * from dept_partition where month = '201708'
union
select * from dept_partition where month = '201707' ;
5、增加分区
alter table dept_partition add partition ( month = '201706' ) ;
6、同时增加多个分区
alter table dept_partition add partition ( month = '201705' ) partition ( month = '201704' ) ;
7、删除分区
alter table dept_partition drop partition ( month = '201704' ) ;
8、同时删除多个分区
alter table dept_partition drop partition ( month = '201705' ) , partition ( month = '201706' ) ;
9、查看分区表有多少分区
show partitions dept_partition;
10、创建二级分区表
create table dept_partition2(
deptno int , dname string, loc string
)
partitioned by ( month string, day string)
row format delimited fields terminated by '\t' ;
11、加载数据到二级分区
load data local inpath '/opt/module/data/text' into table dept_partition2 partition ( month = '201709' , day = '13' ) ;
12、查询分区数据
select * from dept_partition2 where month = '201709' and day = '13' ;
13、分区表和数据管关联
如果首先用hadoop fs - mkdri 创建分区文件夹,然后- put文件,不会查询到,原因是因为mysql上没有元数据信息,alter 添加分区和load data 会自动创建元数据
不一定是分区表,普通的表也同理
hadoop fs - mkdir / user / hive/ warehouse/ stu3
hadoop fs - put / opt/ data / test / user / hive/ warehouse/ stu3
这样查询表不会查询到数据,而且表也没有,因为在Mysql上没有元数据信息
只需要重新建立这张表就好
create table if not exists (
id int ) ;
这就会将元数据与现在的文件夹与数据相关联
14、上传数据后修复
msck repair table dept_pattition2;
15、上传数据后添加分区
alter table dept_pattition2 add partition ( month = '201709' , day = '11' ) ;
16、创建文件夹后load数据到分区
load data local inpath '/opt/module/data/test' into table dept_partition2 partition ( month = '201709' , day = '10' ) ;