oracle动态分区静态分区,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界面验证