（38）DWD层事实表创建

最新推荐文章于 2024-06-15 06:34:42 发布

大数据开发工程师-宋权

最新推荐文章于 2024-06-15 06:34:42 发布

阅读量730

点赞数

分类专栏：离线数仓文章标签：大数据

本文链接：https://blog.csdn.net/song_quan_/article/details/118699115

版权

离线数仓专栏收录该内容

59 篇文章 8 订阅

订阅专栏

1.支付事实表（事务型事实表）

1）建表语句

hive (gmall)>

drop table if exists dwd_fact_payment_info;

create external table dwd_fact_payment_info (

`id` string COMMENT 'id',

`out_trade_no` string COMMENT ' 对外业务编号 ',

`order_id` string COMMENT ' 订单编号 ',

`user_id` string COMMENT ' 用户编号 ',

`alipay_trade_no` string COMMENT ' 支付宝交易流水编号 ',

`payment_amount` decimal(16,2) COMMENT ' 支付金额 ',

`subject` string COMMENT ' 交易内容 ',

`payment_type` string COMMENT ' 支付类型 ',

`payment_time` string COMMENT ' 支付时间 ',

`province_id` string COMMENT ' 省份 ID'

) COMMENT ' 支付事实表表 '

PARTITIONED BY (`dt` string)

stored as parquet

location '/warehouse/gmall/dwd/dwd_fact_payment_info/'

tblproperties ("parquet.compression"="lzo");

2 ）数据装载

hive (gmall)>

SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;

insert overwrite table dwd_fact_payment_info partition(dt='2020-06-14')

select

pi.id,

pi.out_trade_no,

pi.order_id,

pi.user_id,

pi.alipay_trade_no,

pi.total_amount,

pi.subject,

pi.payment_type,

pi.payment_time,

oi.province_id

from

(

select * from ods_payment_info where dt='2020-06-14'

)pi

join

(

select id, province_id from ods_order_info where dt='2020-06-14'

)oi

on pi.order_id = oi.id;

3 ）查询加载结果

hive (gmall)> select * from dwd_fact_payment_info where dt='2020-06-14' limit 2;

2. 退款事实表（事务型事实表）

把 ODS 层 ods_order_refund_info 表数据导入到 DWD 层退款事实表，在导入过程中可

以做适当的清洗。

1 ）建表语句

hive (gmall)>

drop table if exists dwd_fact_order_refund_info;

create external table dwd_fact_order_refund_info(

`id` string COMMENT ' 编号 ',

`user_id` string COMMENT ' 用户 ID',

`order_id` string COMMENT ' 订单 ID',

`sku_id` string COMMENT ' 商品 ID',

`refund_type` string COMMENT ' 退款类型 ',

`refund_num` bigint COMMENT ' 退款件数 ',

`refund_amount` decimal(16,2) COMMENT ' 退款金额 ',

`refund_reason_type` string COMMENT ' 退款原因类型 ',

`create_time` string COMMENT ' 退款时间 '

) COMMENT ' 退款事实表 '

PARTITIONED BY (`dt` string)

stored as parquet

location '/warehouse/gmall/dwd/dwd_fact_order_refund_info/'

tblproperties ("parquet.compression"="lzo");

2)数据装载

hive (gmall)>

SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;

insert overwrite table dwd_fact_order_refund_info partition(dt='2020-06-14')

select

id,

user_id,

order_id,

sku_id,

refund_type,

refund_num,

refund_amount,

refund_reason_type,

create_time

from ods_order_refund_info

where dt='2020-06-14';

3 ）查询加载结果

hive (gmall)> select * from dwd_fact_order_refund_info where dt='2020-06-14' limit 2;

3. 评价事实表（事务型事实表）

把 ODS 层 ods_comment_info 表数据导入到 DWD 层评价事实表，在导入过程中可以做

适当的清洗。

1 ）建表语句

hive (gmall)>

drop table if exists dwd_fact_comment_info;

create external table dwd_fact_comment_info(

`id` string COMMENT ' 编号 ',

`user_id` string COMMENT ' 用户 ID',

`sku_id` string COMMENT ' 商品 sku',

`spu_id` string COMMENT ' 商品 spu',

`order_id` string COMMENT ' 订单 ID',

`appraise` string COMMENT ' 评价 ',

`create_time` string COMMENT ' 评价时间 '

) COMMENT ' 评价事实表 '

PARTITIONED BY (`dt` string)

stored as parquet

location '/warehouse/gmall/dwd/dwd_fact_comment_info/'

tblproperties ("parquet.compression"="lzo");

2 ）数据装载

hive (gmall)>

SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;

insert overwrite table dwd_fact_comment_info partition(dt='2020-06-14')

select

id,

user_id,

sku_id,

spu_id,

order_id,

appraise,

create_time

from ods_comment_info

where dt='2020-06-14';

3 ）查询加载结果

hive (gmall)> select * from dwd_fact_comment_info where dt='2020-06-14' limit 2;

4. 订单明细事实表（事务型事实表）

1 ）建表语句

hive (gmall)>

drop table if exists dwd_fact_order_detail;

create external table dwd_fact_order_detail (

`id` string COMMENT ' 订单编号 ',

`order_id` string COMMENT ' 订单号 ',

`user_id` string COMMENT ' 用户 id',

`sku_id` string COMMENT 'sku 商品 id',

`sku_name` string COMMENT ' 商品名称 ',

`order_price` decimal(16,2) COMMENT ' 商品价格 ',

`sku_num` bigint COMMENT ' 商品数量 ',

`create_time` string COMMENT ' 创建时间 ',

`province_id` string COMMENT ' 省份 ID',

`source_type` string COMMENT ' 来源类型 ',

`source_id` string COMMENT ' 来源编号 ',

`original_amount_d` decimal(20,2) COMMENT ' 原始价格分摊 ',

`final_amount_d` decimal(20,2) COMMENT ' 购买价格分摊 ',

`feight_fee_d` decimal(20,2) COMMENT ' 分摊运费 ',

`benefit_reduce_amount_d` decimal(20,2) COMMENT ' 分摊优惠 '

) COMMENT ' 订单明细事实表表 '

PARTITIONED BY (`dt` string)

stored as parquet

location '/warehouse/gmall/dwd/dwd_fact_order_detail/'

tblproperties ("parquet.compression"="lzo");

2 ）数据装载

hive (gmall)>

SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;

insert overwrite table dwd_fact_order_detail partition(dt='2020-06-14')

select

id,

order_id,

user_id,

sku_id,

sku_name,

order_price,

sku_num,

create_time,

province_id,

source_type,

source_id,

original_amount_d,

if( rn=1 , final_total_amount -( sum_div_final_amount - final_amount_d ), final_amount_d ),

if( rn=1 , feight_fee - ( sum_div_feight_fee - feight_fee_d ), feight_fee_d ),

if( rn=1 , benefit_reduce_amount

( sum_div_benefit_reduce_amount

benefit_reduce_amount_d ), benefit_reduce_amount_d )

from

(

select

od.id,

od.order_id,

od.user_id,

od.sku_id,

od.sku_name,

od.order_price,

od.sku_num,

od.create_time,

oi.province_id,

od.source_type,

od.source_id,

round(od.order_price*od.sku_num,2) original_amount_d,

round(od.order_price*od.sku_num/oi.original_total_amount*oi.final_total_amount,2)

final_amount_d ,

round(od.order_price*od.sku_num/oi.original_total_amount*oi.feight_fee,2)

feight_fee_d ,

round(od.order_price*od.sku_num/oi.original_total_amount*oi.benefit_reduce_amount,2)

benefit_reduce_amount_d ,

row_number() over(partition by od.order_id order by od.id desc) rn ,

oi. final_total_amount ,

oi. feight_fee ,

oi. benefit_reduce_amount ,

sum(round(od.order_price*od.sku_num/oi.original_total_amount*oi.final_total_amount,2))

over(partition by od.order_id) sum_div_final_amount ,

sum(round(od.order_price*od.sku_num/oi.original_total_amount*oi.feight_fee,2))

over(partition by od.order_id) sum_div_feight_fee ,

sum(round(od.order_price*od.sku_num/oi.original_total_amount*oi.benefit_reduce_amount,

2)) over(partition by od.order_id) sum_div_benefit_reduce_amount

(

select * from ods_order_detail where dt='2020-06-14'

) od

join

(

select * from ods_order_info where dt='2020-06-14'

) oi

on od.order_id=oi.id

)t1;

3 ）查询加载结果

hive (gmall)> select * from dwd_fact_order_detail where dt='2020-06-14' limit 2;

5.加购事实表（周期型快照事实表，每日快照）

由于购物车的数量是会发生变化，所以导增量不合适。

每天做一次快照，导入的数据是全量，区别于事务型事实表是每天导入新增。

周期型快照事实表劣势：存储的数据量会比较大。

解决方案：周期型快照事实表存储的数据比较讲究时效性，时间太久了的意义不大，可

以删除以前的数据。

1 ）建表语句

hive (gmall)>

drop table if exists dwd_fact_cart_info;

create external table dwd_fact_cart_info(

`id` string COMMENT ' 编号 ',

`user_id` string COMMENT ' 用户 id',

`sku_id` string COMMENT 'skuid',

`cart_price` string COMMENT ' 放入购物车时价格 ',

`sku_num` string COMMENT ' 数量 ',

`sku_name` string COMMENT 'sku 名称 ( 冗余 )',

`create_time` string COMMENT ' 创建时间 ',

`operate_time` string COMMENT ' 修改时间 ',

`is_ordered` string COMMENT ' 是否已经下单。 1 为已下单 ;0 为未下单 ',

`order_time` string COMMENT ' 下单时间 ',

`source_type` string COMMENT ' 来源类型 ',

`srouce_id` string COMMENT ' 来源编号 '

) COMMENT ' 加购事实表 '

PARTITIONED BY (`dt` string)

stored as parquet

location '/warehouse/gmall/dwd/dwd_fact_cart_info/'

tblproperties ("parquet.compression"="lzo");

2 ）数据装载

hive (gmall)>

SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;

insert overwrite table dwd_fact_cart_info partition(dt='2020-06-14')

select

id,

user_id,

sku_id,

cart_price,

sku_num,

sku_name,

create_time,

operate_time,

is_ordered,

order_time,

source_type,

source_id

from ods_cart_info

where dt='2020-06-14';

3 ）查询加载结果

hive (gmall)> select * from dwd_fact_cart_info where dt='2020-06-14' limit 2;

6.收藏事实表（周期型快照事实表，每日快照）

收藏的标记，是否取消，会发生变化，做增量不合适。

每天做一次快照，导入的数据是全量，区别于事务型事实表是每天导入新增。

1 ）建表语句

hive (gmall)>

drop table if exists dwd_fact_favor_info;

create external table dwd_fact_favor_info(

`id` string COMMENT ' 编号 ',

`user_id` string COMMENT ' 用户 id',

`sku_id` string COMMENT 'skuid',

`spu_id` string COMMENT 'spuid',

`is_cancel` string COMMENT ' 是否取消 ',

`create_time` string COMMENT ' 收藏时间 ',

`cancel_time` string COMMENT ' 取消时间 '

) COMMENT ' 收藏事实表 '

PARTITIONED BY (`dt` string)

stored as parquet

location '/warehouse/gmall/dwd/dwd_fact_favor_info/'

tblproperties ("parquet.compression"="lzo");

2 ）数据装载

hive (gmall)>

SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;

insert overwrite table dwd_fact_favor_info partition(dt='2020-06-14')

select

id,

user_id,

sku_id,

spu_id,

is_cancel,

create_time,

cancel_time

from ods_favor_info

where dt='2020-06-14';

3 ）查询加载结果

hive (gmall)> select * from dwd_fact_favor_info where dt='2020-06-14' limit 2;

7. 优惠券领用事实表（累积型快照事实表）

优惠卷的生命周期：领取优惠卷 - 》用优惠卷下单 - 》优惠卷参与支付

累积型快照事实表使用：统计优惠卷领取次数、优惠卷下单次数、优惠卷参与支付次数

1 ）建表语句

hive (gmall)>

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)

stored as parquet

location '/warehouse/gmall/dwd/dwd_fact_coupon_use/'

tblproperties ("parquet.compression"="lzo");

注意： dt 是按照优惠卷领用时间 get_time 做为分区。

8. 数据装载

hive (gmall)>

set hive.exec.dynamic.partition.mode=nonstrict;

set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;

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-06-14'

)

)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-06-14'

)new

on old.id=new.id;

3 ）查询加载结果

hive (gmall)> select * from dwd_fact_coupon_use where dt='2020-06-14' limit 2;

8.系统函数（concat、concat_ws、collect_set、STR_TO_MAP）

1 ） concat 函数

concat 函数在连接字符串的时候，只要其中一个是 NULL ，那么将返回 NULL

2 ） concat_ws 函数

concat_ws 函数在连接字符串的时候，只要有一个字符串不是 NULL ，就不会返回 NULL 。

concat_ws 函数需要指定分隔符。

3 ） collect_set 函数

（ 1 ）创建原数据表

（2）向原数据表中插入数据

（3）查询表中数据

（4）把同一分组的不同行的数据聚合成一个集合

（5）用下标可以取某一个

3 ） STR_TO_MAP 函数

（ 1 ）语法描述

STR_TO_MAP(VARCHAR text , VARCHAR listDelimiter , VARCHAR keyValueDelimiter )

（ 2 ）功能描述

使用 listDelimiter 将 text 分隔成 K-V 对，然后使用 keyValueDelimiter 分隔每个 K-V 对，

组装成 MAP 返回。默认 listDelimiter 为（，）， keyValueDelimiter 为（ = ）。

（ 3 ）案例

9. 订单事实表（累积型快照事实表）

订单生命周期：创建时间 = 》支付时间 = 》取消时间 = 》完成时间 = 》退款时间 = 》退款完

成时间。

由于 ODS 层订单表只有创建时间和操作时间两个状态，不能表达所有时间含义，所以

需要关联订单状态表。订单事实表里面增加了活动 id ，所以需要关联活动订单表。

1 ）建表语句

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` decimal(16,2) COMMENT ' 原价金额 ',

`benefit_reduce_amount` decimal(16,2) COMMENT ' 优惠金额 ',

`feight_fee` decimal(16,2) COMMENT ' 运费 ',

`final_total_amount` decimal(16,2) COMMENT ' 订单金额 '

) COMMENT ' 订单事实表 '

PARTITIONED BY (`dt` string)

stored as parquet

location '/warehouse/gmall/dwd/dwd_fact_order_info/'

tblproperties ("parquet.compression"="lzo");

2 ）常用函数

3）数据装载

5 ）查询加载结果

hive (gmall)> select * from dwd_fact_order_info where dt='2020-06-14' limit 2;

（注：主要是试试表数据的加载，建表，以及常见sql函数的应用，会显得sql语句比较长，主要是理解思路，sql语句不做过多阐述）

大数据开发工程师-宋权

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
0
评论
（38）DWD层事实表创建

1.支付事实表（事务型事实表）1）建表语句 hive (gmall)> drop table if exists dwd_fact_payment_info; create external table dwd_fact_payment_info ( `id` string COMMENT 'id', `out_trade_no` string COMMENT '对外业务编号', `order_id` string COMMENT '订单编号', `user_id.
复制链接

扫一扫