Hive-分区的具体操作

分区的操作

创建一个一级分区
 

create table if not exists day_part(
uid int,
uname string
)
partitioned by (year int)
row format delimited
fields terminated by '\t'
;

load入数据：

load data local inpath '/root/day_part.txt' overwrite into table day_part partition (year=2017);
load data local inpath '/root/day_part.txt' into table day_part partition (year=2018);

查看分区
 

show partitions day_part;

select * from day_part where year=2017; 查询时指定分区，不必全表扫描

 

创建二级分区

create table if not exists day_part1(
uid int,
uname string
)
partitioned by (year int ,mouth int)
row format delimited 
fields terminated by '\t'
;

写进数据：

load data local inpath '/root/day_part.txt' overwrite into table day_part1 partition (year=2019,month=04);
load data local inpath '/root/day_part.txt' overwrite into table day_part1 partition (year=2019,month=03);

查询：

select * from day_part1 where year = 2019 and mouth = 04;

 对分区进行操作：

显示分区

show partitions day_part1;

新增分区

alter table day_part1 add partition(year=2017,month=1);

新增多个分区：

alter table day_part1 add partition(year=2017,month=2) partition(year=2017,month=3);

新增分区并加载数据：

alter table day_part1 add partition(year=2017,month=10) location
"/user/hive/warehouse/buc1"

修改分区所对应的路径
 

alter table day_part1 partition(year=2017,month=10) set location
"hdfs://mini1:9000/user/hive/warehouse/log_1"  ##路径必须是绝对路径。从hdfs://mini1:9000开始

删除分区
 

alter table day_part1 drop partition (year=2017,month=1);
show partitions day_part1;