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

最新推荐文章于 2024-04-29 16:06:53 发布

x我有辣条跟我走。

最新推荐文章于 2024-04-29 16:06:53 发布

阅读量1.7k

点赞数 2

文章标签：数据仓库

本文链接：https://blog.csdn.net/XIAOMO__/article/details/109476749

版权

数据仓库专栏收录该内容

7 篇文章 2 订阅

订阅专栏

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

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

7.DATE_FORMAT(date,format)函数

1.dwd中用到了三张表，分别是ods_order_info，ods_order_status_log，ods_activity_order；

2.ods_order_status_log需要做一些处理，将他的order_status ,operate_time进行更细致的划分，取出其中的数据，方便动态分区等sql语句调用；

3.根据创建时间分组，其他支付时间，退款时间等之后会变化，所以使用动态分区；

4.full outer join 全连接，new表有的用new表，old表有的用old表，两个表都有的用new表更新的；

5.关键sql语句分析: 将ods_order_status_log 表中的 order_status 和operate_time里的创建时间，支付时间，取消时间，完成时间，退款时间，退款完成时间对应的1001等数字与所对应的时间弄成一个按创建id分组的大表的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 有 --》保留

思路

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.csdn.net/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 external table ods_order_info (
    `id` string COMMENT '订单号',
    `final_total_amount` decimal(10,2) COMMENT '订单金额',
    `order_status` string COMMENT '订单状态',
    `user_id` string COMMENT '用户id',
    `out_trade_no` string COMMENT '支付流水号',
    `create_time` string COMMENT '创建时间',
    `operate_time` string COMMENT '操作时间',
    `province_id` string COMMENT '省份ID',
    `benefit_reduce_amount` decimal(10,2) COMMENT '优惠金额',
    `original_total_amount` decimal(10,2)  COMMENT '原价金额',
    `feight_fee` decimal(10,2)  COMMENT '运费'
) COMMENT '订单表'
PARTITIONED BY (`dt` string)
row format delimited fields terminated by '\t'
STORED AS
  INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
  OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
location '/warehouse/gmall/ods/ods_order_info/';

订单状态表（增量）

hive (gmall)>
drop table if exists ods_order_status_log;
create external table ods_order_status_log (
    `id`   bigint COMMENT '编号',
    `order_id` string COMMENT '订单ID',
    `order_status` string COMMENT '订单状态',
    `operate_time` string COMMENT '修改时间'
)  COMMENT '订单状态表'
PARTITIONED BY (`dt` string)
row format delimited fields terminated by '\t'
STORED AS
  INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
  OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
location '/warehouse/gmall/ods/ods_order_status_log/';

活动订单关联表（增量）

hive (gmall)>
drop table if exists ods_activity_order;
create external table ods_activity_order(
    `id` string COMMENT '编号',
    `activity_id` string  COMMENT '优惠券ID',
    `order_id` string  COMMENT 'skuid',
    `create_time` string  COMMENT '领取时间'
) COMMENT '活动订单关联表'
PARTITIONED BY (`dt` string)
row format delimited fields terminated by '\t'
STORED AS
  INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
  OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
location '/warehouse/gmall/ods/ods_activity_order/';

DWD

订单事实表

hive (gmall)>
drop table if exists dwd_fact_order_info;
create external table dwd_fact_order_info (
    `id` string COMMENT '订单编号',
    `order_status` string COMMENT '订单状态',
    `user_id` string COMMENT '用户id',
    `out_trade_no` string COMMENT '支付流水号',
    `create_time` string COMMENT '创建时间(未支付状态)',
    
    `payment_time` string COMMENT '支付时间(已支付状态)',
    `cancel_time` string COMMENT '取消时间(已取消状态)',
    `finish_time` string COMMENT '完成时间(已完成状态)',
    `refund_time` string COMMENT '退款时间(退款中状态)',
    `refund_finish_time` string COMMENT '退款完成时间(退款完成状态)',
   
    `province_id` string COMMENT '省份ID',
    `activity_id` string COMMENT '活动ID',
    `original_total_amount` string COMMENT '原价金额',
    `benefit_reduce_amount` string COMMENT '优惠金额',
    `feight_fee` string COMMENT '运费',
    `final_total_amount` decimal(10,2) COMMENT '订单金额'
) 
PARTITIONED BY (`dt` string)
stored as parquet
location '/warehouse/gmall/dwd/dwd_fact_order_info/'
tblproperties ("parquet.compression"="lzo");

分析：

1.dwd中用到了三张表，分别是ods_order_info，ods_order_status_log，ods_activity_order；

2.ods_order_status_log需要做一些处理，将他的order_status ,operate_time进行更细致的划分，取出其中的数据，方便动态分区等sql语句调用；

3.根据创建时间分组，其他支付时间，退款时间等之后会变化，所以使用动态分区；

4.full outer join 全连接，new表有的用new表，old表有的用old表，两个表都有的用new表更新的；

5.关键sql语句分析: 将ods_order_status_log 表中的 order_status 和operate_time里的创建时间，支付时间，取消时间，完成时间，退款时间，退款完成时间对应的1001等数字与所对应的时间弄成一个按创建id分组的大表的sql语句，方便分区时调用

order_status:
create_time 1001
`payment_time` 1002
`cancel_time` 1003
`finish_time` 1004
`refund_time` 1005
`refund_finish_time` 1006

select order_id, str_to_map(concat_ws(',',collect_set(concat(order_status,'=',operate_time))), ',' , '=') from ods_order_status_log where dt='2020-10-30' group by order_id;
将取出的日志文件，取名为tms

ods_order_status_log --->order_status ,operate_time

1.将多个字符串用“=”拼接，列出所有字符串； 3210 1001=2020-10-30 00:00:00.0
2.按照id分组，把一个id的放一个集合 3210 ["1001=2020-10-30 00:00:00.0","1002=2020-10-30 00:00:00.0","1005=2020-10-30 00:00:00.0"]
3.将每个集合按"="拼接，","分割 3210 1001=2020-10-30 00:00:00.0,1002=2020-10-30 00:00:00.0,1005=2020-10-30 00:00:00.0
4.将集合分割成map形式，用","分割一个map，":"分割kv 3210 {"1001":"2020-10-30 00:00:00.0","1002":"2020-10-30 00:00:00.0","1005":"2020-10-30 00:00:00.0"}

6.为什么要加on?

简单理解就是A表和B表连接的必要条件

7.为什么用where?

where:后面跟上你bai的查询要求，where后面总要有语句，加上了1=1后就可以保证语法不会出错! 1=1永真条件，一般用于构造动态SQL语句，"SELECT ... FROM ... WHERE 1=1 "+动态构造条件子句。

sql实现

hive (gmall)>
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_fact_order_info partition(dt)
select
    if(new.id is null,old.id,new.id),
    if(new.order_status is null,old.order_status,new.order_status),
    if(new.user_id is null,old.user_id,new.user_id),
    if(new.out_trade_no is null,old.out_trade_no,new.out_trade_no),
    if(new.tms['1001'] is null,old.create_time,new.tms['1001']),--1001对应未支付状态
    if(new.tms['1002'] is null,old.payment_time,new.tms['1002']),
    if(new.tms['1003'] is null,old.cancel_time,new.tms['1003']),
    if(new.tms['1004'] is null,old.finish_time,new.tms['1004']),
    if(new.tms['1005'] is null,old.refund_time,new.tms['1005']),
    if(new.tms['1006'] is null,old.refund_finish_time,new.tms['1006']),
    if(new.province_id is null,old.province_id,new.province_id),
    if(new.activity_id is null,old.activity_id,new.activity_id),
    if(new.original_total_amount is null,old.original_total_amount,new.original_total_amount),
    if(new.benefit_reduce_amount is null,old.benefit_reduce_amount,new.benefit_reduce_amount),
    if(new.feight_fee is null,old.feight_fee,new.feight_fee),
    if(new.final_total_amount is null,old.final_total_amount,new.final_total_amount),
    date_format(if(new.tms['1001'] is null,old.create_time,new.tms['1001']),'yyyy-MM-dd')
from
(
    select
        id,
        order_status,
        user_id,
        out_trade_no,
        create_time,
        payment_time,
        cancel_time,
        finish_time,
        refund_time,
        refund_finish_time,
        province_id,
        activity_id,
        original_total_amount,
        benefit_reduce_amount,
        feight_fee,
        final_total_amount
    from dwd_fact_order_info
    where dt
    in
    (
        select
          date_format(create_time,'yyyy-MM-dd')
        from ods_order_info
        where dt='2020-10-30'
    )
)old
full outer join
(
    select
        info.id,
        info.order_status,
        info.user_id,
        info.out_trade_no,
        info.province_id,
        act.activity_id,
        log.tms,
        info.original_total_amount,
        info.benefit_reduce_amount,
        info.feight_fee,
        info.final_total_amount
    from
    (
        select
            order_id,
            str_to_map(concat_ws(',',collect_set(concat(order_status,'=',operate_time))),',','=') tms
        from ods_order_status_log
        where dt='2020-10-30'
        group by order_id
    )log
    join
    (
        select * from ods_order_info where dt='2020-10-30'
    )info
    on log.order_id=info.id
    left join
    (
        select * from ods_activity_order where dt='2020-10-30'
    )act
    on log.order_id=act.order_id
)new
on old.id=new.id;

用到的hivesql函数:

1.concat函数
   CONCAT（）函数用于将多个字符串连接成一个字符串。
   返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。可以有一个或多个参数。

hive> select concat('a','b');
ab

   hive> select concat('a','b',null);
   NULL
2.concat_ws函数
   是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。
   CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。
   concat_ws函数在连接字符串的时候，只要有一个字符串不是NULL，就不会返回NULL。

hive> select concat_ws('-','a','b');
a-b

hive> select concat_ws('-','a','b',null);
a-b

hive> select concat_ws('','a','b',null);
ab

3.group_concat函数
group_concat()会计算哪些行属于同一组，将属于同一组的列显示出来。要返回哪些列，由数参数(就是字段名)决定。分组必须有个标准，就是根据group by指定的列进行分组。

+----------+----+--------------------------+
| locus | id | day |
+----------+----+--------------------------+
| 27 | 1 | Unpublished |
| 27 | 2 | Submitted (20-11-2020) |
| 64 | 23 | Unpublished |
| 64 | 24 | Submitted (31-10-2020) |
+----------+----+--------------------------+
SELECT locus,GROUP_CONCAT(distinct id ORDER BY id DESC SEPARATOR '_') FROM info WHERE locus IN('27','64') GROUP BY locus;
+----------+----------------------------------------------------------+
| locus | GROUP_CONCAT(distinct id ORDER BY id DESC SEPARATOR '_') |
+----------+----------------------------------------------------------+
| 27 | 2_1 |
| 64 | 24_23 |
+----------+----------------------------------------------------------+

4.str_to_map函数
STR_TO_MAP(VARCHAR text, VARCHAR listDelimiter, VARCHAR keyValueDelimiter)
text是由map集合组成的，有listDelimiter分割map，keyValueDelimiter连接key,value.

str_to_map('1001=2020-10-30,1002=2020-10-30', ',' , '=')
输出
{"1001":"2020-10-30","1002":"2020-10-30"}

x我有辣条跟我走。

关注

2
点赞
踩
14

收藏

觉得还不错? 一键收藏
0
评论
项目---累积型快照事实表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 ...
复制链接

扫一扫