hive动态分区与静态分区

测试目的：
1.分区表的动态分区与静态分区
2.每层数据，数据流向，数据是否在每层都保留一份
测试结果：
1.动态分区/静态分区略
2.每层表的数据都会保留，因此在生产上odm层的数据是可以删除的（不管是内表还是外表）

数据源：
1,jack,shanghai,20190129
2,kevin,beijing,20190130
3,lucas,hangzhou,20190129
4,lily,hangzhou,20190130
1. 创建数据库
create database TestFenQu;

2. 创建源数据表（外表）
create external table TestFenQu.dept(
id int,
name string,
address string,
day string
)
row format delimited fields terminated by ',';
加载数据：
load data local inpath '/home/kong/test.dat' into table TestFenQu.dept;
3. 创建分区表1（外表）
create external table TestFenQu.dept_part(
id int,
name string,
address string
)partitioned by(day string) row format delimited fields terminated by ',';

静态分区加载数据：
//静态分区缺点：每次写入都要明确指定分区日期。
insert overwrite table TestFenQu.dept_part partition(day='20190129') select id,name,address from TestFenQu.dept where day='20190129';
//并且在查询处不能包含分区字段day，否则会报如下错误

动态分区加载数据：
// 自动识别分区，不需要明确指定
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
insert into table TestFenQu.dept_part partition(day) select id,name,address,day from TestFenQu.dept;

查询验证：
select * from TestFenQu.dept_part;
select * from TestFenQu.dept_part where day='20190129';
select * from TestFenQu.dept_part where day='20190130';
HDFS web界面验证
4. 创建分区表2（内表）
create table TestFenQu.dept_part_noExternal(
id int,
name string,
address string
)partitioned by(day string) row format delimited fields terminated by ',';

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
insert into table TestFenQu.dept_part_noExternal partition(day) select id,name,address,day from TestFenQu.dept;
查询验证：
select * from TestFenQu.dept_part_noExternal;
select * from TestFenQu.dept_part_noExternal where day='20190129';
select * from TestFenQu.dept_part_noExternal where day='20190130';
HDFS web界面验证

转载于:https://www.cnblogs.com/dtmobile-ksw/p/11160619.html