一、电商业务与数据结构简介
1、业务流程
2、常识&＃xff1a;SKU/SPU
SKU&＃61;Stock Keeping Unit(库存量基本单位)。现在已经被引申为产品统一编号的简称&＃xff0c;每种产品均对应有唯一的SKU号。
SPU(Standard Product Unit)&＃xff1a;是商品信息聚合的最小单位&＃xff0c;是一组可复用、易检索的标准化信息集合。
3、电商表结构
二、数仓理论
1、表的分类
实体表
维度表&＃xff1a;对应一些业务状态&＃xff0c;编号的解释表。也可以称之为码表&＃xff0c;比如地区表&＃xff0c;订单状态&＃xff0c;支付方式&＃xff0c;审批状态&＃xff0c;商品分类等等
事务型事实表&＃xff1a;一旦发生不会再变化&＃xff0c;如&＃xff0c;交易流水&＃xff0c;操作日志&＃xff0c;出库入库记录等等
周期型事实表&＃xff0c;一般指随着业务发生不断产生变化(更新, 新增)的数据。比如订单&＃xff0c;其中订单状态会周期性变化。再比如&＃xff0c;请假、贷款申请&＃xff0c;随着批复状态在周期性变化。
2、同步策略
全量表&＃xff1a;存储完整的数据。
增量表&＃xff1a;存储新增加的数据。
新增及变化表&＃xff1a;存储新增加的数据和变化的数据。
拉链表&＃xff1a;对新增及变化表做定期合并。
---------------------
实体表&＃xff1a;每日全量
维度表&＃xff1a;每日全量
事务型事实表&＃xff1a;增量表
周期型事实表&＃xff1a;拉链表
| 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 &＃61;<’2019-1-2’ and end>&＃61;’2019-1-2’
3、范式
4、关系建模与维度建模
关系模型&＃xff1a;OLTP&＃xff0c;多表关联降低执行效率
维度模型&＃xff1a;OLAP&＃xff0c;所有维度表围绕着事实表进行解释
5、雪花模型、星型模型和星座模型
三、数仓搭建
1、配置Hadoop支持Snappy压缩
解压缩上传至/opt/module/hadoop-2.7.2/lib/native
检查压缩方式&＃xff1a;hadoop checknative
2、业务数据生成
MySQL建库gmall&＃xff0c;建表
执行生成业务数据函数&＃xff1a;init_data ( do_date_string VARCHAR(20) , order_incr_num INT, user_incr_num INT , sku_num INT , if_truncate BOOLEAN )&＃xff1a;
需求&＃xff1a;生成日期2019年2月10日数据、订单1000个、用户200个、商品sku300个、删除原始数据。
CALL init_data(&＃39;2019-02-10&＃39;,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" &＃39; and $CONDITIONS;&＃39;
各表的同步策略
Sqoop定时导入脚本&＃xff1a;vim sqoop_import.sh【MySQL-hdfs】
生成某日数据&＃xff1a;CALL init_data(&＃39;2019-02-11&＃39;,1000,200,300,TRUE);
3、ODS层
仿照业务数据库中的表字段&＃xff0c;创建ODS层的表
数据导入脚本编写【hdfs-MySQL】
sql&＃61;" load data inpath &＃39;/origin_data/$APP/db/order_info/$do_date&＃39; OVERWRITE into table "$APP".ods_order_info partition(dt&＃61;&＃39;$do_date&＃39;);
4、DWD层
对ODS层数据进行判空过滤。对商品分类表进行维度退化(降维)
insert overwrite table "$APP".dwd_order_info partition(dt)
select * from "$APP".ods_order_info
where dt&＃61;&＃39;$do_date&＃39; 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&＃61;c3.id join "$APP".ods_base_category2 c2 on c3.category2_id&＃61;c2.id join "$APP".ods_base_category1 c1 on c2.category1_id&＃61;c1.id
where sku.dt&＃61;&＃39;$do_date&＃39; and c2.dt&＃61;&＃39;$do_date&＃39;
and c3.dt&＃61;&＃39;$do_date&＃39; and c1.dt&＃61;&＃39;$do_date&＃39;
and sku.id is not null;
"$hive -e "$sql"
5、用户行为宽表
用户单日的行为聚合起来组成一张多列宽表&＃xff0c;以便之后关联用户维度信息后进行
create external table dws_user_action
( user_id string comment &＃39;用户 id&＃39;,order_count bigint comment &＃39;下单次数 &＃39;,order_amount decimal(16,2) comment &＃39;下单金额 &＃39;,payment_count bigint comment &＃39;支付次数&＃39;,payment_amount decimal(16,2) comment &＃39;支付金额 &＃39;,comment_count bigint comment &＃39;评论次数&＃39;
) COMMENT &＃39;每日用户行为宽表&＃39;
PARTITIONED BY (&＃96;dt&＃96; string)
stored as parquet
location &＃39;/warehouse/gmall/dws/dws_user_action/&＃39;;
导入数据
insert overwrite table dws_user_action partition(dt&＃61;&＃39;2019-02-10&＃39;)
selectuser_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&＃61;true&characterEncoding&＃61;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 &＃39;\\N&＃39; \
--input-null-non-string &＃39;\\N&＃39;
}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&＃61;command
do_date&＃61;${dt}
command&＃61;/home/atguigu/bin/sqoop_import.sh all ${do_date}
(2)ods.job文件
type&＃61;command
do_date&＃61;${dt}
dependencies&＃61;import
command&＃61;/home/atguigu/bin/ods_db.sh ${do_date}
(3)dwd.job文件
type&＃61;command
do_date&＃61;${dt}
dependencies&＃61;ods
command&＃61;/home/atguigu/bin/dwd_db.sh ${do_date}
(4)dws.job文件
type&＃61;command
do_date&＃61;${dt}
dependencies&＃61;dwd
command&＃61;/home/atguigu/bin/dws_db_wide.sh ${do_date}
(5)ads.job文件
type&＃61;command
do_date&＃61;${dt}
dependencies&＃61;dws
command&＃61;/home/atguigu/bin/ads_db_gmv.sh ${do_date}
(6)export.job文件
type&＃61;command
dependencies&＃61;ads
command&＃61;/home/atguigu/bin/sqoop_export.sh ads_gmv_sum_day
(7)将以上6个文件压缩成gmv-job.zip文件
创建Azkaban工程&＃xff0c;并上传gmv-job.zip文件。
页面上创建工程执行gmv-job.zip任务&＃xff0c;查看结果
十五、拉链表
1、使用
2、形成过程
3、制作过程
制作当日变动数据(包括新增&＃xff0c;修改)每日执行
2019-02-14日新增2条订单数据
CALL init_data(&＃39;2019-02-14&＃39;,2,5,10,TRUE);
步骤2&＃xff1a;先合并变动信息&＃xff0c;再追加新增信息&＃xff0c;插入到临时表中
步骤3&＃xff1a;把临时表覆盖给拉链表
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&＃61;1200(1000-2000个job)
3)每天集群运行多少个task? 1000*(5-8)&＃61;5000多个
7、表关系
京公网安备 11010802041100号