hive分区操作

hive分区操作

20191017

---

• （1）hive建表时创建分区

create table  `mytable`(
  `column1` string, 
  `column2` string)
partitioned by ( `mydate` string, `mytype` int)
row format delimited fields terminated by ',';

---

• （2）hiveselect--加载数据时动态创建分区

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

use mydatabase;

insert overwrite table mytable partition(
mydate,mytype)
select xxx, xxx, xxx as mydate, xxx as mytype from othertable;

动静混合加载

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

use mydatabase;

insert overwrite table mytable partition(
mydate='2019-01-01',mytype)
select xxx, xxx, col3 as mydate, xxx as mytype from othertable where col3 = '2019-01-01';

---

• （3）hive文件加载数据时创建分区

load data local inpath '/path/file' overwrite into table mytable 
partition (mydate='2018-01-01',mytype=40);

---

• （4）hive只添加分区，不载入数据

alter table mytable add [IF NOT EXISTS] partition (mydate='2018-01-01', mytype=40);

---

• （5）hive 删除已有分区

mytable表有两个分区字段mydate string,mytype int

alter table mytable drop [IF EXISTS] partition (mydate='2018-01-01', mytype=40);

参考

参考1
参考2