6.2 动态分区

开启支持动态分区

xxxxxxxxxx
set hive.exec.dynamic.partition=true;

默认：true #默认支持动态分区

xxxxxxxxxx
hive> set hive.exec.dynamic.partition.mode;
hive.exec.dynamic.partition.mode=strict
#修改为非严格模式
hive>set hive.exec.dynamic.partition.mode=nostrict;

默认：strict严格模式(比如订单表以秒为单位创建分区，将会导致特别多的分区，严格模式一般不允许，但是非严格模式允许）。

nostrict:非严格模式

案例演示：

创建原始数据表

xxxxxxxxxx
create table person21(
id int,
name string,
age int,
gender string,
likes array<string>,
address map<string,string>
)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':';

将软件/data/person21.txt上传到node4上的/root/data目录下

加载数据

xxxxxxxxxx
hive> load data local inpath '/root/data/person21.txt' into table person21;
Loading data to table default.person21
OK
Time taken: 0.786 seconds
hive> select * from person21;
OK
person21.id person21.name   person21.age    person21.gender person21.likes  person21.address
1   tuhao1  32  man ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}
2   tuhao2  32  man ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}
3   tuhao3  12  boy ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}
4   tuhao4  32  man ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}
5   tuhao5  12  boy ["lol","movie"] {"beijing":"xisanqi","shanghai":"pudong"}
6   tuhao6  32  man ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}
7   tuhao7  32  man ["lol","book"]  {"beijing":"xisanqi","shanghai":"pudong"}
8   tuhao8  12  boy ["lol","book"]  {"beijing":"xisanqi","shanghai":"pudong"}
9   tuhao9  32  man ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}

创建分区表：

xxxxxxxxxx
create table person22(
id int,
name string,
likes array<string>,
address map<string,string>
)
partitioned by(age int,gender string)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':';

将person21中的数据加载到person22表中：

xxxxxxxxxx
from person21
insert overwrite table person22 partition(age,gender)
select id,name,likes,address,age,gender distribute by age,gender

还可以简写为：

xxxxxxxxxx
from person21
insert overwrite table person22 partition(age,gender)
select id,name,likes,address,age,gender;

注意：分区字段一定要写到最后面，否则容易出错。

xxxxxxxxxx
show partitions person22;#查询某表上的 分区

查看表的分区：

xxxxxxxxxx
hive> show partitions person22;
OK
partition
age=12/gender=boy
age=32/gender=man
Time taken: 0.331 seconds, Fetched: 2 row(s)

相关参数

xxxxxxxxxx
set hive.exec.max.dynamic.partitions.pernode;
每一个执行mr节点上，允许创建的动态分区的最大数量(100)

set hive.exec.max.dynamic.partitions;
所有执行mr节点上，允许创建的所有动态分区的最大数量(1000)

set hive.exec.max.created.files;
所有的mr job允许创建的文件的最大数量(100000)