【离线数仓】Day03-系统业务数据仓库:数仓表概念、搭建、数据导入、数据可视化、Azkaban全调度、拉链表的使用
一、电商业务与数据结构简介
1、业务流程
2、常识:SKU/SPU
SKU=Stock Keeping Unit(库存量基本单位)。现在已经被引申为产品统一编号的简称,每种产品均对应有唯一的SKU号。
SPU(Standard Product Unit):是商品信息聚合的最小单位,是一组可复用、易检索的标准化信息集合。
3、电商表结构
二、数仓理论
1、表的分类
实体表
维度表:对应一些业务状态,编号的解释表。也可以称之为码表,比如地区表,订单状态,支付方式,审批状态,商品分类等等
事务型事实表:一旦发生不会再变化,如,交易流水,操作日志,出库入库记录等等
周期型事实表,一般指随着业务发生不断产生变化(更新, 新增)的数据。比如订单,其中订单状态会周期性变化。再比如,请假、贷款申请,随着批复状态在周期性变化。
2、同步策略
全量表:存储完整的数据。
增量表:存储新增加的数据。
新增及变化表:存储新增加的数据和变化的数据。
拉链表:对新增及变化表做定期合并。
---------------------
实体表:每日全量
维度表:每日全量
事务型事实表:增量表
周期型事实表:拉链表
name姓名 |
start新名字创建时间 |
end名字更改时间 |
张三 |
1990/1/1 |
2018/12/31 |
张小三 |
2019/1/1 |
2019/4/30 |
张大三 |
2019/5/1 |
9999-99-99 |
。。。 |
。。。 |
。。。 |
select * from user where start =<’2019-1-2’ and end>=’2019-1-2’
3、范式
4、关系建模与维度建模
关系模型:OLTP,多表关联降低执行效率
维度模型:OLAP,所有维度表围绕着事实表进行解释
5、雪花模型、星型模型和星座模型
三、数仓搭建
1、配置Hadoop支持Snappy压缩
解压缩上传至/opt/module/hadoop-2.7.2/lib/native
检查压缩方式:hadoop checknative
2、业务数据生成
MySQL建库gmall,建表
执行生成业务数据函数:init_data ( do_date_string VARCHAR(20) , order_incr_num INT, user_incr_num INT , sku_num INT , if_truncate BOOLEAN ):
需求:生成日期2019年2月10日数据、订单1000个、用户200个、商品sku300个、删除原始数据。
CALL init_data('2019-02-10',1000,200,300,TRUE);
2、数据导入数仓
sqoop导入命令
/opt/module/sqoop/bin/sqoop import \ --connect \ --username \ --password \ --target-dir \ --delete-target-dir \ --num-mappers \ --fields-terminated-by \ --query "$2" ' and $CONDITIONS;'
各表的同步策略
Sqoop定时导入脚本:vim sqoop_import.sh【MySQL-hdfs】
生成某日数据:CALL init_data('2019-02-11',1000,200,300,TRUE);
3、ODS层
仿照业务数据库中的表字段,创建ODS层的表
数据导入脚本编写【hdfs-MySQL】
sql=" load data inpath '/origin_data/$APP/db/order_info/$do_date' OVERWRITE into table "$APP".ods_order_info partition(dt='$do_date');
4、DWD层
对ODS层数据进行判空过滤。对商品分类表进行维度退化(降维)
insert overwrite table "$APP".dwd_order_info partition(dt)
select * from "$APP".ods_order_info
where dt='$do_date' and id is not null;
insert overwrite table "$APP".dwd_sku_info partition(dt) select sku.id, sku.spu_id, sku.price, sku.sku_name, sku.sku_desc, sku.weight, sku.tm_id, sku.category3_id, c2.id category2_id, c1.id category1_id, c3.name category3_name, c2.name category2_name, c1.name category1_name, sku.create_time, sku.dt from "$APP".ods_sku_info sku join "$APP".ods_base_category3 c3 on sku.category3_id=c3.id join "$APP".ods_base_category2 c2 on c3.category2_id=c2.id join "$APP".ods_base_category1 c1 on c2.category1_id=c1.id where sku.dt='$do_date' and c2.dt='$do_date' and c3.dt='$do_date' and c1.dt='$do_date' and sku.id is not null; " $hive -e "$sql"
5、用户行为宽表
用户单日的行为聚合起来组成一张多列宽表,以便之后关联用户维度信息后进行
create external table dws_user_action ( user_id string comment '用户 id', order_count bigint comment '下单次数 ', order_amount decimal(16,2) comment '下单金额 ', payment_count bigint comment '支付次数', payment_amount decimal(16,2) comment '支付金额 ', comment_count bigint comment '评论次数' ) COMMENT '每日用户行为宽表' PARTITIONED BY (`dt` string) stored as parquet location '/warehouse/gmall/dws/dws_user_action/';
导入数据
insert overwrite table dws_user_action partition(dt='2019-02-10') select user_actions.user_id, sum(user_actions.order_count), sum(user_actions.order_amount), sum(user_actions.payment_count), sum(user_actions.payment_amount), sum(user_actions.comment_count) from ( select
四、GMV成交总额
1、ADS层
五、转化率之用户新鲜度及漏斗分析
1、转化率
2、ADS层之新增用户占日活跃用户比率(用户新鲜度)
十三、数据可视化
1、MySQL建表并插入数据
2、web查看
运行spring-boot-echarts-master程序
在web页面上查看显示结果
3、sqoop导出脚本
export_data() { /opt/module/sqoop/bin/sqoop export \ --connect "jdbc:mysql://hadoop102:3306/${db_name}?useUnicode=true&characterEncoding=utf-8" \ --username root \ --password 000000 \ --table $1 \ --num-mappers 1 \ --export-dir /warehouse/$db_name/ads/$1 \ --input-fields-terminated-by "\t" \ --update-mode allowinsert \ --update-key $2 \ --input-null-string '\\N' \ --input-null-non-string '\\N' } case $1 in "ads_uv_count") export_data "ads_uv_count" "dt" ;; "ads_user_action_convert_day") export_data "ads_user_action_convert_day" "dt" ;; "ads_gmv_sum_day") export_data "ads_gmv_sum_day" "dt" ;; "all") export_data "ads_uv_count" "dt" export_data "ads_user_action_convert_day" "dt" export_data "ads_gmv_sum_day" "dt" ;; esac
十四、Azkaban调度器
1、GMV指标获取的全调度流程
编写Azkaban程序运行job
(1)import.job文件
type=command
do_date=${dt}
command=/home/atguigu/bin/sqoop_import.sh all ${do_date}
(2)ods.job文件
type=command
do_date=${dt}
dependencies=import
command=/home/atguigu/bin/ods_db.sh ${do_date}
(3)dwd.job文件
type=command
do_date=${dt}
dependencies=ods
command=/home/atguigu/bin/dwd_db.sh ${do_date}
(4)dws.job文件
type=command
do_date=${dt}
dependencies=dwd
command=/home/atguigu/bin/dws_db_wide.sh ${do_date}
(5)ads.job文件
type=command
do_date=${dt}
dependencies=dws
command=/home/atguigu/bin/ads_db_gmv.sh ${do_date}
(6)export.job文件
type=command
dependencies=ads
command=/home/atguigu/bin/sqoop_export.sh ads_gmv_sum_day
(7)将以上6个文件压缩成gmv-job.zip文件
创建Azkaban工程,并上传gmv-job.zip文件。
页面上创建工程执行gmv-job.zip任务,查看结果
十五、拉链表
1、使用
2、形成过程
3、制作过程
制作当日变动数据(包括新增,修改)每日执行
2019-02-14日新增2条订单数据
CALL init_data('2019-02-14',2,5,10,TRUE);
步骤2:先合并变动信息,再追加新增信息,插入到临时表中
步骤3:把临时表覆盖给拉链表
hive (gmall)> insert overwrite table dwd_order_info_his select * from dwd_order_info_his_tmp;
十六、项目总结
1、熟悉8张表的业务字段
2、数仓理论
3、Sqoop参数
4、sqoop导出时的存储一致性、运行机制等问题
5、拉链表
1)通过关系型数据库的create time和operation time获取数据的新增和变化。
2)用临时拉链表解决Hive了中数据不能更新的问题。
6、Azkaban
1)每天集群运行多少job?
2)多个指标(200)*6=1200(1000-2000个job)
3)每天集群运行多少个task? 1000*(5-8)=5000多个
7、表关系
本文来自博客园,作者:哥们要飞,转载请注明原文链接:https://www.cnblogs.com/liujinhui/p/15647340.html
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列:如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列(二):开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· Manus重磅发布:全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后,我竟然真的恢复了删除的微信聊天记录!
· 没有Manus邀请码?试试免邀请码的MGX或者开源的OpenManus吧
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· 【自荐】一款简洁、开源的在线白板工具 Drawnix