ods层数据抽取请参考我的另一篇文章
sqoop自定义日期采集数据脚本
假设在已经导入ODS层的情况下,我现在需要生成一张对应的DWD层表,即不重复的最新数据的表,因为ODS层默认导入的是昨天一天内更新的数据,而我们的分区是create_time转换的日期,所以我们需要更新DWD层里面对应的老数据以及添加昨天的新数据。
下面的表名字段名等都是例子,不规范,请按自己公司的标准命名。
set hive.default.fileformat=Orc;
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode = 1000;
set hive.exec.max.dynamic.partitions=1000;
create table if not exists trade_dwd.trade (
id string,
unit string,
code int,
create_time timestamp,
update_time timestamp
) partitioned by (create_dt string);
create temporary table trade_dwd.change_date as select to_date(create_time) as create_dt from trade_ods.trade where create_dt='2020-09-10' group by to_date(update_time);
insert overwrite table trade_dwd.trade partition(create_dt)
select
coalesce(a.id,c.id) id,
coalesce(a.unit,c.unit) unit,
coalesce(a.code,c.code) code,
coalesce(a.create_time,c.create_time) create_time,
coalesce(a.update_time,c.update_time) update_time,
to_date(coalesce(a.create_time,c.create_time)) create_dt
from trade_ods.trade a
full outer join
(select a.* from trade_dwd.change_date b join trade_dwd.trade a on a.create_dt=b.create_dt) c
on a.create_dt='2020-09-10' and a.id=c.id;
上面的步骤是:
- 判断是有存在表,不存在就建表
- 将ODS层昨天更新的数据里的create_time转换为create_dt并去重,可以得到需要跟新的分区
- 将需要更新的分区的数据从DWD层取出来和昨天ODS层的数据做full outer join,在用coalesce函数取第一个不为空的,a表是ODS层新抽的数据,里面要么是新增数据,要么是更新的数据,所以优先用ODS层的新数据,之后用动态分区再将数据覆盖到对应的老分区去,同时也完成了新增数据。