订单模块
订单表：订单核心信息
订单详情表：订单表的副表，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表；
-
将临时表的数据，覆盖到拉链表中；
-
第二天再次循环。
合并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;
