OLTP数据结构(重要)
关系图
订单模块
订单表:订单核心信息
订单详情表:订单表的副表,id和订单表一致
订单分组表:订单组-壳子
订单退款表:订单退款详情
订单配送表:订单配送详情
订单评价表:订单评价信息
订单商品评价表:订单商品评价信息
订单结算表:各方的收益结算
订单商品快照表:下单后定型的商品信息,和SKU脱离
支付模块
订单组支付表:订单组支付成功以后插入数据。
交易记录表:交易的详细记录信息。
店铺商圈模块
店铺表
商圈表
地址表:百度地图提供的详细地址信息
区域表:省市县区等国家规定的区域编码及名称
商品模块
商品表:商品价格、活动等信息
商品分类表:大中小分类,自关联
商品品牌表
用户模块
用户登录日志表
商品收藏表
店铺收藏表
购物车表:用户、商品、店铺
ODS
首次执行和循环执行
首次执行时,采用的是▁▁▁全量模式,处理的是▁▁▁所有的量,数据量较▁▁▁大;
后续的循环执行大多采用的是▁▁▁T+1模式,每次数据是▁▁▁一天 的量。
抽取方式
全量覆盖
抽取方式:每次抽取▁▁▁全量 数据,▁▁▁覆盖 插入到ODS层。ODS▁▁▁不要 分区。
应用场景:数据量少,数据不会或很少新增或改变。比如:地区、时间、性别等维度。
仅新增抽取(重要)
抽取方式:每次抽取▁▁▁新增 数据,▁▁▁追加 插入到ODS层。ODS▁▁▁需要 分区。
应用场景:数据量较大,数据会有新增,不会有更新。比如:登录日志表、访问日志表。
新增和更新抽取(重要)
抽取方式:每次抽取▁▁▁新增和更新 数据,▁▁▁追加 插入到ODS层。ODS▁▁▁需要 分区。
应用场景:数据量较大,数据会有新增和更新操作。比如:订单表。
全量同步
抽取方式:每次抽取▁▁▁全量 数据,▁▁▁追加 插入到ODS层。ODS▁▁▁需要 分区。
应用场景:数据会有新增和更新,但是数据量较少,且历史快照不用保存很久的情况。
Hive参数优化
HDFS基本配置(要改)
HDFS副本数
dfs.replication
文件副本数,通常设为▁▁▁,如果测试环境只有二台虚拟机(2个datanode节点),此值要修改为2。
Yarn基础配置
NodeManager
yarn.nodemanager.resource.memory-mb NM的总内存,要▁▁▁把linux剩下的都给他
yarn.scheduler.maximum-allocation-mb 要▁▁▁和上面的保持一致
yarn.app.mapreduce.am.command-opts 要▁▁▁比上面的小一点
MR
mapreduce.map.java.opts ▁▁▁ < mapreduce.map.memory.mb
mapreduce.reduce.java.opts ▁▁▁< mapreduce.reduce.memory.mb,格式-Xmx4096m
此部分所有配置均要 ▁▁▁< Yarn的NodeManager内存配置
Hive基础配置
HiveServer2 的 Java 堆栈(要改)
HiveServer2 的 Java 堆栈
Hive并行编译(要改)
hive.driver.parallel.compilation
是否开启并行编译
hive.driver.parallel.compilation.global.limit
同时编译数量限制
压缩配置
Map压缩
mapreduce.map.output.compress ▁▁▁ true
mapreduce.map.output.compress.codec ▁▁SNAPPY
Reduce压缩
mapreduce.output.fileoutputformat.compress ▁▁▁true
mapreduce.output.fileoutputformat.compress.codec ▁▁▁SNAPPY
mapreduce.output.fileoutputformat.compress.type ▁▁▁BLOCK
Hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
建表相关(重要)
数据结构和压缩算法
ORC+SNAPPY
为什么要选择Orc格式?▁▁▁▁▁▁▁▁▁
快——按行分块按列存储——行式存储*更快,列式存储指定字段更快
Hive分区
为什么要分区?
▁▁▁▁▁▁
静态和动态区别
▁▁▁▁▁▁
插入数据时,静态分区要指定值,动态分区不用指定;
创建表时,没有区别;
创建分区表
--分区字段主要是时间,按年分区 CREATE TABLE device_open ( deviceid varchar(50), ... ) ▁▁▁ ▁▁ (year varchar(50)) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
写入分区数据
静态分区
-- 插入到2020年的分区 insert overwrite table device_open partition(▁▁▁year='2020') select ..., original_device_open.month as month, original_device_open.day as day, original_device_open.hour as hour FROM original_device_open;
动态分区
-- 开启动态分区支持,并开启非严格模式 set hive.exec.dynamic.partition=▁▁▁true; set hive.exec.dynamic.partition.mode=▁▁▁nonstrict; -- 按照查询出的年月日时,进行动态分区插入 insert overwrite table device_open partition(▁▁▁year) select ..., original_device_open.year as year, original_device_open.month as month, original_device_open.day as day, original_device_open.hour as hour FROM original_device_open;
静动混合
动态分区要在静态分区▁▁▁之后
-- 静动混用 insert overwrite table device_open partition(▁▁▁year='2020', month) select ..., original_device_open.day as day, original_device_open.hour as hour FROM original_device_open where original_device_open.year='2017' and original_device_open.month='05';
Sqoop脚本
全量覆盖
没有分区
select * from t_district where 1=1 and \$CONDITIONS
仅新增同步
有分区
select *, '${TD_DATE}' as dt from t_goods_evaluation where 1=1 and (create_time between '${TD_DATE} 00:00:00' and '${TD_DATE} 23:59:59') and \$CONDITIONS
新增和更新同步
有分区
select *, '${TD_DATE}' as dt from t_trade_area where 1=1 and ( (create_time between '${TD_DATE} 00:00:00' and '${TD_DATE} 23:59:59') or (update_time between '${TD_DATE} 00:00:00' and '${TD_DATE} 23:59:59') ) and \$CONDITIONS
DWD
建模流程
1、确认业务需求,识别感兴趣的事实表
2、确认数据粒度,DWD最细粒度,DWS天数据、店铺统计数据
3、识别维度
4、确定事实,事实表中的指标字段:金额、订单量
5、冗余维度
命名规范(重要)
库名:业务简拼_分层:yp_dwd
事实表名:▁▁▁
维度表名:▁▁▁
区分事实表与维度表(重要)
1、数据特性:事实表一般都是▁▁▁,数据量较▁▁▁,更新较▁▁▁;维度表数据量相对较▁▁▁,更新频率▁▁▁;
2、图表展现:在图表展现中,事实数据体现为▁▁▁轴,维度数据体现为▁▁▁轴;
3、统计实现:在统计sql中,维度字段体现在▁▁▁中,行为指标字段体现在▁▁▁中。
拉链表(重要)
对于==会更新==的数据,如何更好的存储和查询呢?▁▁▁
查询拉链表数据
查询当前正在生效的数据
select * from table where ▁▁▁▁▁▁▁▁▁end_date='9999-99-99';
查询出2021-08-08日生效的数据:
select * from table where ▁▁▁▁▁▁▁▁▁'2021-08-08' between start_date and end_date;
拉链表插入数据
-
ODS抽取新增/变更数据;
-
重建临时表tmp;
-
合并昨日增量数据(ods表)与历史数据(拉链表)到临时表
(1) 新数据(员工)end_date设为▁▁▁,也就是当前有效;
(2) 如果增量数据有重复id的旧数据(未离职老员工),将旧数据end_date(退休日期)更新为▁▁▁,也就是从昨天开始不再生效;
(3) 合并后的数据写入tmp表;
-
将临时表的数据,覆盖到拉链表中;
-
第二天再次循环。
Left Join的作用
合并SQL
合并核心sql
insert overwrite table yp_dwd.fact_shop_order_tmp partition (start_date) -- 一、ods表的新数据 select ……, '9999-99-99' end_date, '${TD_DATE}' as start_date from yp_ods.t_shop_order where dt='${TD_DATE}' union all -- 二、历史拉链表数据,并根据up_id判断更新end_time有效期 select ……, -- 3、更新end_time:如果没有匹配到变更数据,或者当前已经是无效的历史数据,则保留原始end_time过期时间; 否则变更end_time时间为前天(昨天之前有效) if(up.id is null or fso.end_date<'9999-99-99', fso.end_date, date_add(up.dt,-1)) as end_date, fso.start_date from yp_dwd.fact_shop_order fso left join (select *from yp_ods.t_shop_order where dt='${TD_DATE}') up on fso.id=up.id -- 4、时间限制:如果订单的变更周期是30天则可加上此条件,结果会按照所属分区进行覆盖插入 -- where fso.start_date >= date_add(up.dt,-30) ;
完整合并Sql
insert overwrite table yp_dwd.fact_shop_order_tmp partition (start_date) select * from ( -- 一、ods表的新数据 select id, order_num, buyer_id, store_id, -- 转换 case order_from when 1 then 'android' when 2 then 'ios' when 3 then 'miniapp' when 4 then 'pcweb' else 'other' end as order_from, order_state, create_date, finnshed_time, is_settlement, is_delete, evaluation_state, way, is_stock_up, create_user, create_time, update_user, update_time, is_valid, '9999-99-99' end_date, '${TD_DATE}' as start_date from yp_ods.t_shop_order -- 过滤 where dt = '${TD_DATE}' and id is not null and buyer_id is not null and store_id is not null and create_date is not null union all -- 二、历史拉链表数据,并根据up_id判断更新end_time有效期 select fso.id, fso.order_num, fso.buyer_id, fso.store_id, fso.order_from, fso.order_state, fso.create_date, fso.finnshed_time, fso.is_settlement, fso.is_delete, fso.evaluation_state, fso.way, fso.is_stock_up, fso.create_user, fso.create_time, fso.update_user, fso.update_time, fso.is_valid, -- 3、更新end_time:如果没有匹配到变更数据,或者当前已经是无效的历史数据,则保留原始end_time过期时间; 否则变更end_time时间为前天(昨天之前有效) if(up.id is null or fso.end_date < '9999-99-99', fso.end_date, date_add(up.dt, -1)) end_date, fso.start_date from yp_dwd.fact_shop_order fso left join (select * from yp_ods.t_shop_order where dt = '${TD_DATE}') up on fso.id = up.id -- 4、时间限制:如果订单的变更周期是30天则可加上此条件,结果会按照所属分区进行覆盖插入 -- where fso.start_date >= date_add(up.dt, -30) ) his order by his.id, start_date;
完整SQL流程示例
-- =====拉链表示例===== -- 1、建表 create database yp_dwd; DROP TABLE if EXISTS yp_dwd.fact_shop_order; CREATE TABLE yp_dwd.fact_shop_order ( id string COMMENT '根据一定规则生成的订单编号', order_num string COMMENT '订单序号', buyer_id string COMMENT '买家的userId', store_id string COMMENT '店铺的id', order_from string COMMENT '此字段可以转换 1.安卓\; 2.ios\; 3.小程序H5 \; 4.PC', order_state int COMMENT '订单状态:1.已下单\; 2.已付款, 3. 已确认 \;4.配送\; 5.已完成\; 6.退款\;7.已 取消', create_date string COMMENT '下单时间', finnshed_time timestamp COMMENT '订单完成时间,当配送员点击确认送达时,进行更新订单完成时间,后期需要根据 订单完成时间,进行自动收货以及自动评价', is_settlement tinyint COMMENT '是否结算\;0.待结算订单\; 1.已结算订单\;', is_delete tinyint COMMENT '订单评价的状态:0.未删除\; 1.已删除\;(默认0)', evaluation_state tinyint COMMENT '订单评价的状态:0.未评价\; 1.已评价\;(默认0)', way string COMMENT '取货方式:SELF自提\;SHOP店铺负责配送', is_stock_up int COMMENT '是否需要备货 0:不需要 1:需要 2:平台确认备货 3:已完成备货 4平台已经将货 物送至店铺 ', create_user string, create_time string, update_user string, update_time string, is_valid tinyint COMMENT '是否有效 0: false\; 1: true\; 订单是否有效的标志', end_date string COMMENT '拉链结束日期' ) COMMENT '订单表' partitioned by (start_date string) row format delimited fields terminated by '\t' stored as orc tblproperties ('orc.compress' = 'SNAPPY'); SELECT * FROM yp_dwd.fact_shop_order WHERE order_from!='miniapp'; -- 2、DWD首次 清洗转换 SET hive.exec.dynamic.partition=true; SET hive.exec.dynamic.partition.mode=nonstrict; INSERT INTO yp_dwd.fact_shop_order PARTITION (start_date) SELECT id ,order_num ,buyer_id ,store_id -- 转换:1.安卓; 2.ios; 3.小程序H5 ; 4.PC ,CASE order_from WHEN 1 THEN 'android' WHEN 2 THEN 'ios' WHEN 3 THEN 'miniapp' WHEN 4 THEN 'pcweb' ELSE NULL END as order_from ,order_state ,create_date ,finnshed_time ,is_settlement ,is_delete ,evaluation_state ,way ,is_stock_up ,create_user ,create_time ,update_user ,update_time ,is_valid ,'9999-99-99' end_date ,SUBSTRING(create_time, 1, 10) start_date FROM yp_ods.t_shop_order -- 清洗数据 WHERE id is NOT NULL AND buyer_id is NOT NULL AND store_id is NOT NULL; -- 拉链表循环执行 -- 1、重建临时表 DROP TABLE if EXISTS yp_dwd.fact_shop_order_tmp; CREATE TABLE yp_dwd.fact_shop_order_tmp ( id string COMMENT '根据一定规则生成的订单编号', order_num string COMMENT '订单序号', buyer_id string COMMENT '买家的userId', store_id string COMMENT '店铺的id', order_from string COMMENT '此字段可以转换 1.安卓\; 2.ios\; 3.小程序H5 \; 4.PC', order_state int COMMENT '订单状态:1.已下单\; 2.已付款, 3. 已确认 \;4.配送\; 5.已完成\; 6.退款\;7.已 取消', create_date string COMMENT '下单时间', finnshed_time timestamp COMMENT '订单完成时间,当配送员点击确认送达时,进行更新订单完成时间,后期需要根据 订单完成时间,进行自动收货以及自动评价', is_settlement tinyint COMMENT '是否结算\;0.待结算订单\; 1.已结算订单\;', is_delete tinyint COMMENT '订单评价的状态:0.未删除\; 1.已删除\;(默认0)', evaluation_state tinyint COMMENT '订单评价的状态:0.未评价\; 1.已评价\;(默认0)', way string COMMENT '取货方式:SELF自提\;SHOP店铺负责配送', is_stock_up int COMMENT '是否需要备货 0:不需要 1:需要 2:平台确认备货 3:已完成备货 4平台已经将货 物送至店铺 ', create_user string, create_time string, update_user string, update_time string, is_valid tinyint COMMENT '是否有效 0: false\; 1: true\; 订单是否有效的标志', end_date string COMMENT '拉链结束日期' ) COMMENT '订单表' partitioned by (start_date string) row format delimited fields terminated by '\t' stored as orc tblproperties ('orc.compress' = 'SNAPPY'); -- 合并新旧数据 INSERT overwrite TABLE yp_dwd.fact_shop_order_tmp PARTITION (start_date) -- 新数据,昨天是2021-10-22 SELECT id ,order_num ,buyer_id ,store_id -- 转换:1.安卓; 2.ios; 3.小程序H5 ; 4.PC ,CASE order_from WHEN 1 THEN 'android' WHEN 2 THEN 'ios' WHEN 3 THEN 'miniapp' WHEN 4 THEN 'pcweb' ELSE NULL END as order_from ,order_state ,create_date ,finnshed_time ,is_settlement ,is_delete ,evaluation_state ,way ,is_stock_up ,create_user ,create_time ,update_user ,update_time ,is_valid ,'9999-99-99' as end_date, '2021-10-22' as start_date FROM yp_ods.t_shop_order ods WHERE ods.dt='2021-10-22' -- 清洗不要忘记 and id is NOT NULL AND buyer_id is NOT NULL AND store_id is NOT NULL UNION ALL -- 旧数据 SELECT dwd.id ,dwd.order_num ,dwd.buyer_id ,dwd.store_id ,dwd.order_from ,dwd.order_state ,dwd.create_date ,dwd.finnshed_time ,dwd.is_settlement ,dwd.is_delete ,dwd.evaluation_state ,dwd.way ,dwd.is_stock_up ,dwd.create_user ,dwd.create_time ,dwd.update_user ,dwd.update_time ,dwd.is_valid -- 更新end_date:1、老员工的岗位有新员工顶替;2、老员工在职(还未退休) ,if(up.id is NOT NULL and end_date='9999-99-99', '2021-10-21', end_date ) as end_date, start_date FROM yp_dwd.fact_shop_order dwd LEFT JOIN yp_ods.t_shop_order up ON dwd.id=up.id and up.dt='2021-10-22' and up.id is NOT NULL AND up.buyer_id is NOT NULL AND up.store_id is NOT NULL -- 30天之前的数据不会更新 -- WHERE dwd.create_date >= date_add('2021-10-22', -30) and start_date >= date_add('2021-10-22', -30) ; -- 覆盖插入到拉链表 insert overwrite table yp_dwd.fact_shop_order PARTITION (start_date) SELECT * FROM yp_dwd.fact_shop_order_tmp;
Hive分桶采样(重要)
分桶和分区的区别
-
分桶对数据的处理比分区更加细粒度化:分区针对的是数据的▁▁▁;分桶针对的是▁▁▁;
-
分桶是按照列的哈希函数进行分割的,相对==比较平均==;而分区是按照列的值来进行分割的,容易造成==数据倾斜==;
-
分桶和分区两者不干扰,可以把分区表进一步分桶。
创建分桶表
create table test_buck(id int, name string) ▁▁▁ ▁(id) ▁▁ ▁ (id asc) ▁▁ 6 ▁▁▁ row format delimited fields terminated by '\t';
插入分桶数据
--启用桶表 set hive.enforce.bucketing=true; insert into table test_buck select id, name from temp_buck;
数据采样
select * from test_buck ▁▁▁(▁▁▁ 1 ▁ ▁ 10 ▁ id);
注意:sqoop不支持分桶表,如果需要从sqoop导入数据到分桶表,可以通过中间临时表进行过度。ODS也可以不做分桶,从DWD明细层开始分桶。
Hive执行计划(重要)
执行计划可以告诉我们查询过程的关键信息,用来帮助我们判定优化措施是否已经生效。
语法:
▁▁▁ select * form table;
案例
1、重新ODS分桶表和临时表(临时表不能分桶)
DROP TABLE if exists yp_ods.t_shop_order; CREATE TABLE yp_ods.t_shop_order ( `id` string COMMENT '根据一定规则生成的订单编号', `order_num` string COMMENT '订单序号', `buyer_id` string COMMENT '买家的userId', `store_id` string COMMENT '店铺的id', `order_from` TINYINT COMMENT '是来自于app还是小程序,或者pc 1.安卓; 2.ios; 3.小程序H5 ; 4.PC', `order_state` INT COMMENT '订单状态:1.已下单; 2.已付款, 3. 已确认 ;4.配送; 5.已完成; 6.退款;7. 已取消', `create_date` string COMMENT '下单时间', `finnshed_time` timestamp COMMENT '订单完成时间,当配送员点击确认送达时,进行更新订单完成时间,后期需 要根据订单完成时间,进行自动收货以及自动评价', `is_settlement` TINYINT COMMENT '是否结算;0.待结算订单; 1.已结算订单;', `is_delete` TINYINT COMMENT '订单评价的状态:0.未删除; 1.已删除;(默认0)', `evaluation_state` TINYINT COMMENT '订单评价的状态:0.未评价; 1.已评价;(默认0)', `way` string COMMENT '取货方式:SELF自提;SHOP店铺负责配送', `is_stock_up` INT COMMENT '是否需要备货 0:不需要 1:需要 2:平台确认备货 3:已完成备货 4平 台已经将货物送至店铺 ', `create_user` string, `create_time` string, `update_user` string, `update_time` string, `is_valid` TINYINT COMMENT '是否有效 0: false; 1: true; 订单是否有效的标志' ) comment '订单表' partitioned by (dt string) clustered by (id) sorted by (id) into 10 buckets row format delimited fields terminated by '\t' stored as orc tblproperties ('orc.compress' = 'ZLIB'); DROP TABLE if exists yp_ods.t_shop_order_tmp; CREATE TABLE yp_ods.t_shop_order_tmp ( `id` string COMMENT '根据一定规则生成的订单编号', `order_num` string COMMENT '订单序号', `buyer_id` string COMMENT '买家的userId', `store_id` string COMMENT '店铺的id', `order_from` TINYINT COMMENT '是来自于app还是小程序,或者pc 1.安卓; 2.ios; 3.小程序H5 ; 4.PC', `order_state` INT COMMENT '订单状态:1.已下单; 2.已付款, 3. 已确认 ;4.配送; 5.已完成; 6.退款;7. 已取消', `create_date` string COMMENT '下单时间', `finnshed_time` timestamp COMMENT '订单完成时间,当配送员点击确认送达时,进行更新订单完成时间,后期需 要根据订单完成时间,进行自动收货以及自动评价', `is_settlement` TINYINT COMMENT '是否结算;0.待结算订单; 1.已结算订单;', `is_delete` TINYINT COMMENT '订单评价的状态:0.未删除; 1.已删除;(默认0)', `evaluation_state` TINYINT COMMENT '订单评价的状态:0.未评价; 1.已评价;(默认0)', `way` string COMMENT '取货方式:SELF自提;SHOP店铺负责配送', `is_stock_up` INT COMMENT '是否需要备货 0:不需要 1:需要 2:平台确认备货 3:已完成备货 4平 台已经将货物送至店铺 ', `create_user` string, `create_time` string, `update_user` string, `update_time` string, `is_valid` TINYINT COMMENT '是否有效 0: false; 1: true; 订单是否有效的标志' ) comment '订单表' partitioned by (dt string) row format delimited fields terminated by '\t' stored as orc tblproperties ('orc.compress' = 'ZLIB');
2、抽取数据到tmp临时表
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \ --connect 'jdbc:mysql://192.168.88.80:3306/yipin?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true' \ --username root \ --password 123456 \ --query "select *, '2021-10-22' as dt from t_shop_order where 1=1 and \$CONDITIONS" \ --hcatalog-database yp_ods \ --hcatalog-table t_shop_order_tmp \ -m 1
3、tmp插入到ODS分桶表
SET hive.exec.dynamic.partition=true; SET hive.exec.dynamic.partition.mode=nonstrict; insert into yp_ods.t_shop_order partition (dt) select * from yp_ods.t_shop_order_tmp;
4、查看执行计划对比
-- INSERT INTO yp_dwd.fact_shop_order PARTITION (start_date) explain SELECT id ,order_num ,buyer_id ,store_id -- 转换:1.安卓; 2.ios; 3.小程序H5 ; 4.PC ,CASE order_from WHEN 1 THEN 'android' WHEN 2 THEN 'ios' WHEN 3 THEN 'miniapp' WHEN 4 THEN 'pcweb' ELSE NULL END as order_from ,order_state ,create_date ,finnshed_time ,is_settlement ,is_delete ,evaluation_state ,way ,is_stock_up ,create_user ,create_time ,update_user ,update_time ,is_valid ,'9999-99-99' end_date ,SUBSTRING(create_time, 1, 10) start_date FROM yp_ods.t_shop_order tablesample (bucket 1 out of 10 on id) -- 清洗数据 WHERE id is NOT NULL AND buyer_id is NOT NULL AND store_id is NOT NULL;