專案---累積型快照事實表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