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

 

ods:新增及变化 --》 每天分区里面存放的是新增的与变化的数据 

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 有  --》 保留 
 思路 :
新表有的，用新表的，新表没有的用旧表的 ，，更新过去了

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.csdn.net/qq_16590169/article/details/103464349
2.Where语句
    1．使用WHERE子句，将不满足条件的行过滤掉

    2．WHERE子句紧随FROM子句

    3．案例实操

            查询出薪水大于1000的所有员工

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

    注意：where子句中不能使用字段别名。
3.dt是一个ods层设置的分区
4.老表没有的数据用新表的，新表没有的用老表的，老表新表都有的用新表的。
5.where in

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