Hive3分区表增删改查

创建分区表

create table student_par(id int,name string)
partitioned by (class string) 
row format delimited fields terminated by '\t';

加载数据

load data local inpath '/opt/module/hive/datas/student.txt' into table student_par partition(class='class01');

insert into table student_par partition(class="class02") select * from student; 

insert overwrite table student_par partition(class="class02") select * from student where id=1001;

查询指定分区数据

select * from student_par where class='class01';

增加分区

alter table student_par add partition(class="class03");

alter table student_par add partition(class="class04") partition(class="class05");

查看表所有分区

show partitions student_par;

删除分区

alter table student_par drop partition(class="class03");

alter table student_par drop partition(class="class04"),partition(class="class05");

创建二级分区

create table student_par2(id int,name string)
partitioned by (class string,grade string)
row format delimited fields terminated by '\t';

加载数据

load data local inpath '/opt/module/hive/datas/student.txt' into table student_par2 partition(class='class01',grade="1");

修复分区数据不显示

-- 方式1 上传数据后修复
msck repair table student_par2;
-- 方式2 上传数据后添加对应分区
alter table student_par2 add partition(class='class01',grade="2");
-- 方式3 上传数据同时指定分区
load data local inpath '/opt/module/hive/datas/student.txt' into table student_par2 partition(class='class01',grade='3');