项目---累积型快照事实表sql

drop table if exists ods_coupon_use;
create external table ods_coupon_use(
    `id` string COMMENT '编号',
    `coupon_id` string  COMMENT '优惠券ID',
    `user_id` string  COMMENT 'skuid',
    `order_id` string  COMMENT 'spuid',
    `coupon_status` string  COMMENT '优惠券状态',
    `get_time` string  COMMENT '领取时间',
    `using_time` string  COMMENT '使用时间(下单)',
    `used_time` string  COMMENT '使用时间(支付)'
) COMMENT '优惠券领用表'
PARTITIONED BY (`dt` string)
row format delimited fields terminated by '\t'

DWD

累积型的事实表

drop table if exists dwd_fact_coupon_use;
create external table dwd_fact_coupon_use(
    `id` string COMMENT '编号',
    `coupon_id` string  COMMENT '优惠券ID',
    `user_id` string  COMMENT 'userid',
    `order_id` string  COMMENT '订单id',
    `coupon_status` string  COMMENT '优惠券状态',
    `get_time` string  COMMENT '领取时间',
    `using_time` string  COMMENT '使用时间(下单)',
    `used_time` string  COMMENT '使用时间(支付)'
) COMMENT '优惠券领用事实表'
PARTITIONED BY (`dt` string)
row format delimited fields terminated by '\t'         //行格式分隔符“/t”
location '/warehouse/gmall/dwd/dwd_fact_coupon_use/';

两个表进行join
new（新增与变化）和 old（这个事实表）
new有 old 有 ---》需要更新
new 有 old 没有 --》插入到当天的分区
new 没有 old 有 --》保留

思路

1.get_time:之前时间发生，以他为分区，因为这两个useing_time和used_time发生时间不确定，所以使用动态分区。

useing_time:使用优惠券的时间不确定。

used_time:使用优惠券支付的时间不确定。

2.新表有的，用新表的，新表没有的用旧表的 ,旧表有的，用旧表的，旧表没有的用新表的，新表旧表都有的用新表的；

3.new（新增与变化）和 old（事实表）

SQL

set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_fact_coupon_use partition(dt)
select
    if(new.id is null,old.id,new.id),
    if(new.coupon_id is null,old.coupon_id,new.coupon_id),
    if(new.user_id is null,old.user_id,new.user_id),
    if(new.order_id is null,old.order_id,new.order_id),
    if(new.coupon_status is null,old.coupon_status,new.coupon_status),
    if(new.get_time is null,old.get_time,new.get_time),
    if(new.using_time is null,old.using_time,new.using_time),
    if(new.used_time is null,old.used_time,new.used_time),	
    date_format(if(new.get_time is null,old.get_time,new.get_time),'yyyy-MM-dd')  // 获取到动态分区
from
(
    select
        id,
        coupon_id,
        user_id,
        order_id,
        coupon_status,
        get_time,
        using_time,
        used_time
    from dwd_fact_coupon_use
    where dt in
    (
        select
            date_format(get_time,'yyyy-MM-dd')
        from ods_coupon_use
        where dt='2020-10-30'
    )
)old
full outer join
(
    select
        id,
        coupon_id,
        user_id,
        order_id,
        coupon_status,
        get_time,
        using_time,
        used_time
    from ods_coupon_use
    where dt='2020-10-30'
)new
on old.id=new.id;

用到的知识点

1.动态分区参数：

https://blog.youkuaiyun.com/qq_16590169/article/details/103464349

2.Where语句

使用WHERE子句，将不满足条件的行过滤掉
WHERE子句紧随FROM子句
案例实操查询出薪水大于1000的所有员工

hive (default)> select * from emp where sal >1000;

注意：where子句中不能使用字段别名。

3.dt是一个ods层设置的分区

4.老表没有的数据用新表的，新表没有的用老表的，老表新表都有的用新表的。

6.full outer join ...on 全外连接

FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行.

FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

7.DATE_FORMAT(date,format)函数

date 参数是合法的日期,format 规定日期/时间的输出格式。
DATE_FORMAT(NOW(),'%m-%d-%Y') --mysql

例二：订单事实表（累积型快照事实表）

ODS

订单表（增量及更新）

hive (gmall)>
drop table if exists ods_order_info;
create extern

最低0.47元/天解锁文章

200万优质内容无限畅学