今日内容:
DWD层作用:
DWD层 和 ODS层保持相同粒度, 从ODS层将数据抽取出来, 对数据进行清洗转换的操作, 将清洗转换后的数据灌入到DWD层中
从DWD层开始, 将整体迁移到云环境来处理
云环境的HUE地址:http://106.75.33.59:8888/hue/用户名: bj_class59密码: bj_class59
- 1- 构建DWD层的库 ```sql create database if not exists bj59_yp_dwd_jiale ;
库名格式要求(务必按照此格式构建): bj59yp_dwd姓名
说明: 关于在DWD层, 一般主要做那些清洗和转换的操作呢?
```properties
关于当前项目的DWD层构建:
1) 由于粒度是一致的, 所以DWD层表数量以及表的结构基本上ODS层是一致的
2) 在DWD层建表的时候, 将压缩方案 从原有zlib 更改为 snappy, 便于后续读取操作
3) 对于同步方式为新增及更新的表, 由于需要在DWD层中对历史数据进行拉链处理操作, 所以在DWD层进行建表的时候, 会新建两个字段: start_date(拉链开始时间) 和 end_date(拉链的结束时间) 其中 会将 start_date作为分区字段
一般在实际生产环境中 一般需要清洗转换那些操作呢?
1- 去除无用空值, 缺少值
2- 去重
3- 过滤掉一些以及标记为删除的数据
4- 发现一个字段中如果涵盖了多个字段信息, 一般需要将其转换为 多个字段来分别处理
比如说: 日期, ODS层中日期值可能为 2022-01-01 14:25:30 包含 年 月 日 小时 分钟 秒
可以将其拆解为 年字段, 月字段, 日字段, 小时字段, 季度字段...
5- 原有数据可能是通过数字来表示一种行为, 可能需要将其转换为具体的内容
比如说: 数据中用 1表示男性 0 表示女性 直接将其转换为 男 和 女
或者 将具体内容, 转换为数字
6- 维度退化操作, 将多个相关的表合并为一个表(此种一般需要JOIN大量的表来处理)
注意: 一般此操作会独立处理, 不会和清洗转换放置在一块, 除非非常简单
7- JSOIN数据的拉平操作:
比如说: 一个字段为 content字段, 字段里面数据格式 "{'name':'张三','address':'北京'}"
此时需要将其拉宽拉平, 形成两个新的字段: content_name , content_address
目前, 在我们项目中, 基本上不去处理任何的转换操作, 主要原因是因为当前这份数据, 本身就是一些测试数据, 里面将大量的敏感数据给脱敏了, 导致一旦进行清洗处理, 可能什么数据都不剩下了
1.1 完成各个表的构建操作
说明:
需要对表划分那些是事实表, 那些是维度表, 以及那些表需要进行拉链
- 事实表:
- 维度表
建表操作:
- 事实表建表语句 ```sql —订单事实表（拉链表） DROP TABLE if EXISTS bj59_yp_dwd_jiale.fact_shop_order; CREATE TABLE bj59_yp_dwd_jiale.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) CLUSTERED BY(id) SORTED BY(id) INTO 5 BUCKETS row format delimited fields terminated by ‘\t’ stored as orc tblproperties (‘orc.compress’ = ‘SNAPPY’);
—订单详情表（拉链表） DROP TABLE if EXISTS bj59_yp_dwd_jiale.fact_shop_order_address_detail; CREATE TABLE bj59_yp_dwd_jiale.fact_shop_order_address_detail( id string COMMENT ‘关联订单的id’, order_amount decimal(36,2) COMMENT ‘订单总金额:购买总金额-优惠金额’, discount_amount decimal(36,2) COMMENT ‘优惠金额’, goods_amount decimal(36,2) COMMENT ‘用户购买的商品的总金额+运费’, is_delivery string COMMENT ‘0.自提；1.配送’, buyer_notes string COMMENT ‘买家备注留言’, pay_time string, receive_time string, delivery_begin_time string, arrive_store_time string, arrive_time string COMMENT ‘订单完成时间,当配送员点击确认送达时,进行更新订单完成时间,后期需要根据订单完成时间,进行自动收货以及自动评价’, 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’);
—订单结算表(拉链表) DROP TABLE if exists bj59_yp_dwd_jiale.fact_order_settle; CREATE TABLE bj59_yp_dwd_jiale.fact_order_settle( id string COMMENT ‘结算单号’, order_id string, settlement_create_date string COMMENT ‘用户申请结算的时间’, settlement_amount decimal(36,2) COMMENT ‘如果发生退款,则结算的金额 = 订单的总金额 - 退款的金额’, dispatcher_user_id string COMMENT ‘配送员id’, dispatcher_money decimal(36,2) COMMENT ‘配送员的配送费(配送员的运费(如果退货方式为1:则买家支付配送费))’, circle_master_user_id string COMMENT ‘圈主id’, circle_master_money decimal(36,2) COMMENT ‘圈主分润的金额’, plat_fee decimal(36,2) COMMENT ‘平台应得的分润’, store_money decimal(36,2) COMMENT ‘商家应得的订单金额’, status tinyint COMMENT ‘0.待结算；1.待审核 \; 2.完成结算；3.拒绝结算’, note string COMMENT ‘原因’, settle_time string COMMENT ‘ 结算时间’, create_user string, create_time string, update_user string, update_time string, is_valid tinyint COMMENT ‘是否有效 0: false\; 1: true\; 订单是否有效的标志’, first_commission_user_id string COMMENT ‘一级分佣用户’, first_commission_money decimal(36,2) COMMENT ‘一级分佣金额’, second_commission_user_id string COMMENT ‘二级分佣用户’, second_commission_money decimal(36,2) COMMENT ‘二级分佣金额’, end_date string COMMENT ‘拉链结束日期’) COMMENT ‘订单结算表’ partitioned by (start_date string) row format delimited fields terminated by ‘\t’ stored as orc tblproperties (‘orc.compress’ = ‘SNAPPY’);
—退款订单表（拉链表） DROP TABLE if exists bj59_yp_dwd_jiale.fact_refund_order; CREATE TABLE bj59_yp_dwd_jiale.fact_refund_order ( id string COMMENT ‘退款单号’, order_id string COMMENT ‘订单的id’, apply_date string COMMENT ‘用户申请退款的时间’, modify_date string COMMENT ‘退款订单更新时间’, refund_reason string COMMENT ‘买家退款原因’, refund_amount DECIMAL(11,2) COMMENT ‘订单退款的金额’, refund_state TINYINT COMMENT ‘1.申请退款;2.拒绝退款; 3.同意退款,配送员配送; 4:商家同意退款,用户亲自送货 ;5.退款完成’, refuse_refund_reason string COMMENT ‘商家拒绝退款原因’, refund_goods_type string COMMENT ‘1.上门取货(买家承担运费); 2.买家送达;’, refund_shipping_fee DECIMAL(11,2) COMMENT ‘配送员的运费(如果退货方式为1:则买家支付配送费)’, 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’);
—订单组表（拉链表） DROP TABLE if EXISTS bj59_yp_dwd_jiale.fact_shop_order_group; CREATE TABLE bj59_yp_dwd_jiale.fact_shop_order_group( id string, order_id string COMMENT ‘订单id’, group_id string COMMENT ‘订单分组id’, is_pay tinyint COMMENT ‘是否已支付,0未支付,1已支付’, create_user string, create_time string, update_user string, update_time string, is_valid tinyint, end_date string COMMENT ‘拉链结束日期’) COMMENT ‘订单组’ partitioned by (start_date string) CLUSTERED BY(order_id ) SORTED BY(order_id ) INTO 5 BUCKETS row format delimited fields terminated by ‘\t’ stored as orc tblproperties (‘orc.compress’ = ‘SNAPPY’);
—订单组支付（增量表） DROP TABLE if EXISTS bj59_yp_dwd_jiale.fact_order_pay; CREATE TABLE bj59_yp_dwd_jiale.fact_order_pay( id string, group_id string COMMENT ‘关联shop_order_group的group_id,一对多订单’, order_pay_amount decimal(36,2) COMMENT ‘订单总金额\;’, create_date string COMMENT ‘订单创建的时间,需要根据订单创建时间进行判断订单是否已经失效’, 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’ = ‘SNAPPY’);
—订单商品快照(拉链表) DROP TABLE if EXISTS bj59_yp_dwd_jiale.fact_shop_order_goods_details; CREATE TABLE bj59_yp_dwd_jiale.fact_shop_order_goods_details( id string COMMENT ‘id主键’, order_id string COMMENT ‘对应订单表的id’, shop_store_id string COMMENT ‘卖家店铺ID’, buyer_id string COMMENT ‘购买用户ID’, goods_id string COMMENT ‘购买商品的id’, buy_num int COMMENT ‘购买商品的数量’, goods_price decimal(36,2) COMMENT ‘购买商品的价格’, total_price decimal(36,2) COMMENT ‘购买商品的价格 = 商品的数量 * 商品的单价 ‘, goods_name string COMMENT ‘商品的名称’, goods_image string COMMENT ‘商品的图片’, goods_specification string COMMENT ‘商品规格’, goods_weight int, goods_unit string COMMENT ‘商品计量单位’, goods_type string COMMENT ‘商品分类 ytgj:进口商品 ytsc:普通商品 hots爆品’, refund_order_id string COMMENT ‘退款订单的id’, goods_brokerage decimal(36,2) COMMENT ‘商家设置的商品分润的金额’, is_refund tinyint COMMENT ‘0.不退款\; 1.退款’, 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’);
—订单评价表（增量表，与ODS一致） DROP TABLE if EXISTS bj59_yp_dwd_jiale.fact_goods_evaluation; CREATE TABLE bj59_yp_dwd_jiale.fact_goods_evaluation( id string, user_id string COMMENT ‘评论人id’, store_id string COMMENT ‘店铺id’, order_id string COMMENT ‘订单id’, geval_scores int COMMENT ‘综合评分’, geval_scores_speed int COMMENT ‘送货速度评分0-5分(配送评分)’, geval_scores_service int COMMENT ‘服务评分0-5分’, geval_isanony tinyint COMMENT ‘0-匿名评价，1-非匿名’, create_user string, create_time string, update_user string, update_time string, is_valid tinyint COMMENT ‘0 ：失效，1 ：开启’) COMMENT ‘订单评价表’ partitioned by (dt string) row format delimited fields terminated by ‘\t’ stored as orc tblproperties (‘orc.compress’ = ‘SNAPPY’);
—商品评价表（拉链表） DROP TABLE if EXISTS bj59_yp_dwd_jiale.fact_goods_evaluation_detail; CREATE TABLE bj59_yp_dwd_jiale.fact_goods_evaluation_detail( id string, user_id string COMMENT ‘评论人id’, store_id string COMMENT ‘店铺id’, goods_id string COMMENT ‘商品id’, order_id string COMMENT ‘订单id’, order_goods_id string COMMENT ‘订单商品表id’, geval_scores_goods int COMMENT ‘商品评分0-10分’, geval_content string, geval_content_superaddition string COMMENT ‘追加评论’, geval_addtime string COMMENT ‘评论时间’, geval_addtime_superaddition string COMMENT ‘追加评论时间’, geval_state tinyint COMMENT ‘评价状态 1-正常 0-禁止显示’, geval_remark string COMMENT ‘管理员对评价的处理备注’, revert_state tinyint COMMENT ‘回复状态0未回复1已回复’, geval_explain string COMMENT ‘管理员回复内容’, geval_explain_superaddition string COMMENT ‘管理员追加回复内容’, geval_explaintime string COMMENT ‘管理员回复时间’, geval_explaintime_superaddition string COMMENT ‘管理员追加回复时间’, create_user string, create_time string, update_user string, update_time string, is_valid tinyint COMMENT ‘0 ：失效，1 ：开启’, end_date string COMMENT ‘拉链结束日期’) COMMENT ‘商品评价明细’ partitioned by (start_date string) row format delimited fields terminated by ‘\t’ stored as orc tblproperties (‘orc.compress’ = ‘SNAPPY’);
—配送表（拉链表） DROP TABLE if EXISTS bj59_yp_dwd_jiale.fact_order_delievery_item; CREATE TABLE bj59_yp_dwd_jiale.fact_order_delievery_item( id string COMMENT ‘主键id’, shop_order_id string COMMENT ‘订单表ID’, refund_order_id string, dispatcher_order_type tinyint COMMENT ‘配送订单类型1.支付单\; 2.退款单’, shop_store_id string COMMENT ‘卖家店铺ID’, buyer_id string COMMENT ‘购买用户ID’, circle_master_user_id string COMMENT ‘圈主ID’, dispatcher_user_id string COMMENT ‘配送员ID’, dispatcher_order_state tinyint COMMENT ‘配送订单状态:0.待接单.1.已接单,2.已到店.3.配送中 4.商家普通提货码完成订单.5.商家万能提货码完成订单。6，买家完成订单’, order_goods_num tinyint COMMENT ‘订单商品的个数’, delivery_fee decimal(36,2) COMMENT ‘配送员的运费’, distance int COMMENT ‘配送距离’, dispatcher_code string COMMENT ‘收货码’, receiver_name string COMMENT ‘收货人姓名’, receiver_phone string COMMENT ‘收货人电话’, sender_name string COMMENT ‘发货人姓名’, sender_phone string COMMENT ‘发货人电话’, 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’);
—登录记录表（增量表，与ODS一致） DROP TABLE if exists bj59_yp_dwd_jiale.fact_user_login; CREATE TABLE bj59_yp_dwd_jiale.fact_user_login( id string, login_user string, login_type string COMMENT ‘登录类型（登陆时使用）’, client_id string COMMENT ‘推送标示id(登录、第三方登录、注册、支付回调、给用户推送消息时使用)’, login_time string, login_ip string, logout_time string ) COMMENT ‘用户登录记录表’ partitioned by (dt string) row format delimited fields terminated by ‘\t’ stored as orc tblproperties (‘orc.compress’ = ‘SNAPPY’);
—购物车（拉链表） DROP TABLE if exists bj59_yp_dwd_jiale.fact_shop_cart; CREATE TABLE bj59_yp_dwd_jiale.fact_shop_cart ( id string COMMENT ‘主键id’, shop_store_id string COMMENT ‘卖家店铺ID’, buyer_id string COMMENT ‘购买用户ID’, goods_id string COMMENT ‘购买商品的id’, buy_num INT COMMENT ‘购买商品的数量’, 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’);
—收藏店铺记录（拉链表） DROP TABLE if exists bj59_yp_dwd_jiale.fact_store_collect; CREATE TABLE bj59_yp_dwd_jiale.fact_store_collect ( id string, user_id string COMMENT ‘收藏人id’, store_id string COMMENT ‘店铺id’, create_user string, create_time string, update_user string, update_time string, is_valid TINYINT COMMENT ‘0 ：失效，1 ：开启’, end_date string COMMENT ‘拉链结束日期’) comment ‘收藏店铺记录表’ partitioned by (start_date string) row format delimited fields terminated by ‘\t’ stored as orc tblproperties (‘orc.compress’ = ‘SNAPPY’);
—商品收藏（拉链表） DROP TABLE if exists bj59_yp_dwd_jiale.fact_goods_collect; CREATE TABLE bj59_yp_dwd_jiale.fact_goods_collect ( id string, user_id string COMMENT ‘收藏人id’, goods_id string COMMENT ‘商品id’, store_id string COMMENT ‘通过哪个店铺收藏的（因主店分店概念存在需要）’, create_user string, create_time string, update_user string, update_time string, is_valid TINYINT COMMENT ‘0 ：失效，1 ：开启’, end_date string COMMENT ‘拉链结束日期’) comment ‘收藏商品记录表’ partitioned by (start_date string) row format delimited fields terminated by ‘\t’ stored as orc tblproperties (‘orc.compress’ = ‘SNAPPY’);
—交易记录（拉链表） DROP TABLE if exists bj59_yp_dwd_jiale.fact_trade_record; CREATE TABLE bj59_yp_dwd_jiale.fact_trade_record ( id string COMMENT ‘交易单号’, external_trade_no string COMMENT ‘(支付,结算.退款)第三方交易单号’, relation_id string COMMENT ‘关联单号’, trade_type TINYINT COMMENT ‘1.支付订单; 2.结算订单; 3.退款订单；4.充值单；5.提现单；6.分销单；7缴纳保证金单8退还保证金单9,冻结通联订单，10通联通账户余额充值，11.扫码单’, status TINYINT COMMENT ‘1.成功;2.失败;3.进行中’, finnshed_time string COMMENT ‘订单完成时间,当配送员点击确认送达时,进行更新订单完成时间,后期需要根据订单完成时间,进行自动收货以及自动评价’, fail_reason string COMMENT ‘交易失败的原因’, payment_type string COMMENT ‘支付方式:小程序,app微信,支付宝,快捷支付,钱包，银行卡,消费券’, trade_before_balance DECIMAL(11,2) COMMENT ‘交易前余额’, trade_true_amount DECIMAL(11,2) COMMENT ‘交易实际支付金额,第三方平台扣除优惠以后实际支付金额’, trade_after_balance DECIMAL(11,2) COMMENT ‘交易后余额’, note string COMMENT ‘业务说明’, user_card string COMMENT ‘第三方平台账户标识/多钱包用户钱包id’, user_id string COMMENT ‘用户id’, aip_user_id string COMMENT ‘钱包id’, 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’);
- 维度表
```sql
--区域字典表（全量覆盖）
DROP TABLE if EXISTS bj59_yp_dwd_jiale.dim_district;
CREATE TABLE bj59_yp_dwd_jiale.dim_district(
id string COMMENT '主键ID',
code string COMMENT '区域编码',
name string COMMENT '区域名称',
pid string COMMENT '父级ID',
alias string COMMENT '别名')
COMMENT '区域字典表'
row format delimited fields terminated by '\t'
stored as orc
tblproperties ('orc.compress' = 'SNAPPY');
--时间维度（全量覆盖）
drop table bj59_yp_dwd_jiale.dim_date;
CREATE TABLE bj59_yp_dwd_jiale.dim_date
(
dim_date_id string COMMENT '日期',
date_code string COMMENT '日期编码',
lunar_calendar string COMMENT '农历',
year_code string COMMENT '年code',
year_name string COMMENT '年名称',
month_code string COMMENT '月份编码',
month_name string COMMENT '月份名称',
quanter_code string COMMENT '季度编码',
quanter_name string COMMENT '季度名称',
year_month string COMMENT '年月',
year_week_code string COMMENT '一年中第几周',
year_week_name string COMMENT '一年中第几周名称',
year_week_code_cn string COMMENT '一年中第几周（中国）',
year_week_name_cn string COMMENT '一年中第几周名称（中国',
week_day_code string COMMENT '周几code',
week_day_name string COMMENT '周几名称',
day_week string COMMENT '周',
day_week_cn string COMMENT '周(中国)',
day_week_num string COMMENT '一周第几天',
day_week_num_cn string COMMENT '一周第几天（中国）',
day_month_num string COMMENT '一月第几天',
day_year_num string COMMENT '一年第几天',
date_id_wow string COMMENT '与本周环比的上周日期',
date_id_mom string COMMENT '与本月环比的上月日期',
date_id_wyw string COMMENT '与本周同比的上年日期',
date_id_mym string COMMENT '与本月同比的上年日期',
first_date_id_month string COMMENT '本月第一天日期',
last_date_id_month string COMMENT '本月最后一天日期',
half_year_code string COMMENT '半年code',
half_year_name string COMMENT '半年名称',
season_code string COMMENT '季节编码',
season_name string COMMENT '季节名称',
is_weekend string COMMENT '是否周末（周六和周日）',
official_holiday_code string COMMENT '法定节假日编码',
official_holiday_name string COMMENT '法定节假日',
festival_code string COMMENT '节日编码',
festival_name string COMMENT '节日',
custom_festival_code string COMMENT '自定义节日编码',
custom_festival_name string COMMENT '自定义节日',
update_time string COMMENT '更新时间'
)
COMMENT '时间维度表'
row format delimited fields terminated by '\t'
stored as orc
tblproperties ('orc.compress' = 'SNAPPY');
--店铺（拉链表）
DROP TABLE if EXISTS bj59_yp_dwd_jiale.dim_store;
CREATE TABLE bj59_yp_dwd_jiale.dim_store(
id string COMMENT '主键',
user_id string,
store_avatar string COMMENT '店铺头像',
address_info string COMMENT '店铺详细地址',
name string COMMENT '店铺名称',
store_phone string COMMENT '联系电话',
province_id int COMMENT '店铺所在省份ID',
city_id int COMMENT '店铺所在城市ID',
area_id int COMMENT '店铺所在县ID',
mb_title_img string COMMENT '手机店铺 页头背景图',
store_description string COMMENT '店铺描述',
notice string COMMENT '店铺公告',
is_pay_bond tinyint COMMENT '是否有交过保证金 1：是0：否',
trade_area_id string COMMENT '归属商圈ID',
delivery_method tinyint COMMENT '配送方式 1 ：自提 ；3 ：自提加配送均可\; 2 : 商家配送',
origin_price decimal(36,2),
free_price decimal(36,2),
store_type int COMMENT '店铺类型 22天街网店 23实体店 24直营店铺 33会员专区店',
store_label string COMMENT '店铺logo',
search_key string COMMENT '店铺搜索关键字',
end_time string COMMENT '营业结束时间',
start_time string COMMENT '营业开始时间',
operating_status tinyint COMMENT '营业状态 0 ：未营业 ；1 ：正在营业',
create_user string,
create_time string,
update_user string,
update_time string,
is_valid tinyint COMMENT '0关闭，1开启，3店铺申请中',
state string COMMENT '可使用的支付类型:MONEY金钱支付\;CASHCOUPON现金券支付',
idcard string COMMENT '身份证',
deposit_amount decimal(36,2) COMMENT '商圈认购费用总额',
delivery_config_id string COMMENT '配送配置表关联ID',
aip_user_id string COMMENT '通联支付标识ID',
search_name string COMMENT '模糊搜索名称字段:名称_+真实名称',
automatic_order tinyint COMMENT '是否开启自动接单功能 1：是 0 ：否',
is_primary tinyint COMMENT '是否是总店 1: 是 2: 不是',
parent_store_id string COMMENT '父级店铺的id，只有当is_primary类型为2时有效',
end_date string COMMENT '拉链结束日期')
COMMENT '店铺表'
partitioned by (start_date string)
row format delimited fields terminated by '\t'
stored as orc
tblproperties ('orc.compress' = 'SNAPPY');
--商圈（拉链表）
DROP TABLE if EXISTS bj59_yp_dwd_jiale.dim_trade_area;
CREATE TABLE bj59_yp_dwd_jiale.dim_trade_area(
id string COMMENT '主键',
user_id string COMMENT '用户ID',
user_allinpay_id string COMMENT '通联用户表id',
trade_avatar string COMMENT '商圈logo',
name string COMMENT '商圈名称',
notice string COMMENT '商圈公告',
distric_province_id int COMMENT '商圈所在省份ID',
distric_city_id int COMMENT '商圈所在城市ID',
distric_area_id int COMMENT '商圈所在县ID',
address string COMMENT '商圈地址',
radius double COMMENT '半径',
mb_title_img string COMMENT '手机商圈 页头背景图',
deposit_amount decimal(36,2) COMMENT '商圈认购费用总额',
hava_deposit int COMMENT '是否有交过保证金 1：是0：否',
state tinyint COMMENT '申请商圈状态 -1 ：未认购 ；0 ：申请中；1 ：已认购；',
search_key string COMMENT '商圈搜索关键字',
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');
--地址信息表（拉链表）
DROP TABLE if EXISTS bj59_yp_dwd_jiale.dim_location;
CREATE TABLE bj59_yp_dwd_jiale.dim_location(
id string COMMENT '主键',
type int COMMENT '类型 1：商圈地址；2：店铺地址；3.用户地址管理\;4.订单买家地址信息\;5.订单卖家地址信息',
correlation_id string COMMENT '关联表id',
address string COMMENT '地图地址详情',
latitude double COMMENT '纬度',
longitude double COMMENT '经度',
street_number string COMMENT '门牌',
street string COMMENT '街道',
district string COMMENT '区县',
city string COMMENT '城市',
province string COMMENT '省份',
business string COMMENT '百度商圈字段，代表此点所属的商圈',
create_user string,
create_time string,
update_user string,
update_time string,
is_valid tinyint COMMENT '是否有效 0: false\; 1: true',
adcode string COMMENT '百度adcode,对应区县code',
end_date string COMMENT '拉链结束日期')
COMMENT '地址信息'
partitioned by (start_date string)
row format delimited fields terminated by '\t'
stored as orc
tblproperties ('orc.compress' = 'SNAPPY');
--商品SKU表（拉链表）
DROP TABLE if EXISTS bj59_yp_dwd_jiale.dim_goods;
CREATE TABLE bj59_yp_dwd_jiale.dim_goods(
id string,
store_id string COMMENT '所属商店ID',
class_id string COMMENT '分类id:只保存最后一层分类id',
store_class_id string COMMENT '店铺分类id',
brand_id string COMMENT '品牌id',
goods_name string COMMENT '商品名称',
goods_specification string COMMENT '商品规格',
search_name string COMMENT '模糊搜索名称字段:名称_+真实名称',
goods_sort int COMMENT '商品排序',
goods_market_price decimal(36,2) COMMENT '商品市场价',
goods_price decimal(36,2) COMMENT '商品销售价格(原价)',
goods_promotion_price decimal(36,2) COMMENT '商品促销价格(售价)',
goods_storage int COMMENT '商品库存',
goods_limit_num int COMMENT '购买限制数量',
goods_unit string COMMENT '计量单位',
goods_state tinyint COMMENT '商品状态 1正常，2下架,3违规（禁售）',
goods_verify tinyint COMMENT '商品审核状态: 1通过，2未通过，3审核中',
activity_type tinyint COMMENT '活动类型:0无活动1促销2秒杀3折扣',
discount int COMMENT '商品折扣(%)',
seckill_begin_time string COMMENT '秒杀开始时间',
seckill_end_time string COMMENT '秒杀结束时间',
seckill_total_pay_num int COMMENT '已秒杀数量',
seckill_total_num int COMMENT '秒杀总数限制',
seckill_price decimal(36,2) COMMENT '秒杀价格',
top_it tinyint COMMENT '商品置顶：1-是，0-否',
create_user string,
create_time string,
update_user string,
update_time string,
is_valid tinyint COMMENT '0 ：失效，1 ：开启',
end_date string COMMENT '拉链结束日期')
COMMENT '商品表_店铺(SKU)'
partitioned by (start_date string)
row format delimited fields terminated by '\t'
stored as orc
tblproperties ('orc.compress' = 'SNAPPY');
--商品分类（拉链表）
DROP TABLE if EXISTS bj59_yp_dwd_jiale.dim_goods_class;
CREATE TABLE bj59_yp_dwd_jiale.dim_goods_class(
id string,
store_id string COMMENT '店铺id',
class_id string COMMENT '对应的平台分类表id',
name string COMMENT '店铺内分类名字',
parent_id string COMMENT '父id',
level tinyint COMMENT '分类层级',
is_parent_node tinyint COMMENT '是否为父节点:1是0否',
background_img string COMMENT '背景图片',
img string COMMENT '分类图片',
keywords string COMMENT '关键词',
title string COMMENT '搜索标题',
sort int COMMENT '排序',
note string COMMENT '类型描述',
url string COMMENT '分类的链接',
is_use tinyint COMMENT '是否使用:0否,1是',
create_user string,
create_time string,
update_user string,
update_time string,
is_valid tinyint COMMENT '0 ：失效，1 ：开启',
end_date string COMMENT '拉链结束日期')
COMMENT '商品分类表'
partitioned by (start_date string)
row format delimited fields terminated by '\t'
stored as orc
tblproperties ('orc.compress' = 'SNAPPY');
--品牌表（拉链表）
DROP TABLE if EXISTS bj59_yp_dwd_jiale.dim_brand;
CREATE TABLE bj59_yp_dwd_jiale.dim_brand(
id string,
store_id string COMMENT '店铺id',
brand_pt_id string COMMENT '平台品牌库品牌Id',
brand_name string COMMENT '品牌名称',
brand_image string COMMENT '品牌图片',
initial string COMMENT '品牌首字母',
sort int COMMENT '排序',
is_use tinyint COMMENT '0禁用1启用',
goods_state tinyint COMMENT '商品品牌审核状态 1 审核中,2 通过,3 拒绝',
create_user string,
create_time string,
update_user string,
update_time string,
is_valid tinyint COMMENT '0 ：失效，1 ：开启',
end_date string COMMENT '拉链结束日期')
COMMENT '品牌（店铺）'
partitioned by (start_date string)
row format delimited fields terminated by '\t'
stored as orc
tblproperties ('orc.compress' = 'SNAPPY');
1.2 完成各个表的数据导入操作
- 1- 全量覆盖的表: 时间表
```sql
— 开启动态分区支持:
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000; set hive.exec.max.created.files=150000;
— hive压缩 set hive.exec.compress.intermediate=true; set hive.exec.compress.output=true;
— 写入时压缩生效 set hive.exec.orc.compression.strategy=COMPRESSION; —分桶 set hive.enforce.bucketing=true; set hive.enforce.sorting=true;
— 思考: insert + select 方式导入到目标表, 内部是根据字段名称导入呢? 还是顺序导入呢? 还是都有? — 只能按照 顺序导入, 要求 select后查询的结果字段顺序 必须和目标表字段顺序保持一致 — 但是一般建议, 将顺序和名称都保证一致 — 首次执行, 和增量执行都是一样的SQL insert overwrite table bj59_yp_dwd_jiale.dim_date select * from yp_ods.t_date;
- 2- 仅新增同步的表: 订单评价表
```sql
-- 仅新增同步表
-- 首次导入的时候, 需要将全部的数据都导入
-- 说明: 在演示中 此处特意加了一个 dt = '2022-04-26' 操作, 主要为了演示后续的增量过程, 实际中首次导入是直接导入全量数据的
insert overwrite table bj59_yp_dwd_jiale.fact_goods_evaluation partition(dt)
select
*
from yp_ods.t_goods_evaluation;
-- 后续的增量导入呢? 每一次导入上一天的新增数据即可
-- 此处使用overwrite, 会不会将历史数据给覆盖掉呢? 对于分区表, 只会覆盖所影响的分区, 不影响的分区不会覆盖, 在进行增量导入的时候, 每一次都是一个新的分区, 所以自然也不会影响原有分区
insert overwrite table bj59_yp_dwd_jiale.fact_goods_evaluation partition(dt)
select
*
from yp_ods.t_goods_evaluation where dt = '2022-04-27';
- 3- 新增及更新同步: t_shop_order 和 t_store
```sql
— 演示 t_shop_order
— 由于DWD层拉链表 和 ODS层目标表字段存在差异, 所以无法使用 * 必须将每一个字段全部都得写出来
— 首次导入: 全量导入
insert overwrite table bj59_yp_dwd_jiale.fact_shop_order partition(start_date)
select
id,
order_num,
buyer_id,
store_id,
case
when order_from = 1 then ‘安卓’ when order_from = 2 then ‘IOS’ when order_from = 3 then ‘小程序H5’ when order_from = 4 then ‘PC’ 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 , dt as start_date from yp_ods.t_shop_order;
— 演示: t_store — 首次导入, 全量导入方案 — 说明: 此处我们只导入了第一次全量数据, 上一天做的增量数据并没有导入, 需要演示后续增量如何实现 insert overwrite table bj59_yp_dwd_jiale.dim_store partition(start_date) select id , user_id , store_avatar, address_info , name , store_phone , province_id , city_id , area_id , mb_title_img , store_description, notice , is_pay_bond , trade_area_id , delivery_method , origin_price , free_price , store_type , store_label , search_key, end_time, start_time , operating_status , create_user , create_time , update_user , update_time , is_valid , state , idcard, deposit_amount , delivery_config_id , aip_user_id, search_name, automatic_order, is_primary, parent_store_id , ‘9999-99-99’ as end_date , dt as start_date from yp_ods.t_store where dt = ‘2022-04-26’;
<a name="Cfhp8"></a>
### 1.3 拉链表实现流程分析
<a name="qlhUq"></a>
### 1.4 拉链表的实现操作
- 店铺表: yp_ods.t_store 拉链表实现操作
```sql
-- 第一步: 创建店铺表的拉链临时表(此表和目标表是完全一致), 便于后续放置拉链后的结果数据
--店铺临时拉链表（拉链表）
DROP TABLE if EXISTS bj59_yp_dwd_jiale.dim_store_scd2_temp;
CREATE TABLE bj59_yp_dwd_jiale.dim_store_scd2_temp(
id string COMMENT '主键',
user_id string,
store_avatar string COMMENT '店铺头像',
address_info string COMMENT '店铺详细地址',
name string COMMENT '店铺名称',
store_phone string COMMENT '联系电话',
province_id int COMMENT '店铺所在省份ID',
city_id int COMMENT '店铺所在城市ID',
area_id int COMMENT '店铺所在县ID',
mb_title_img string COMMENT '手机店铺 页头背景图',
store_description string COMMENT '店铺描述',
notice string COMMENT '店铺公告',
is_pay_bond tinyint COMMENT '是否有交过保证金 1：是0：否',
trade_area_id string COMMENT '归属商圈ID',
delivery_method tinyint COMMENT '配送方式 1 ：自提 ；3 ：自提加配送均可\; 2 : 商家配送',
origin_price decimal(36,2),
free_price decimal(36,2),
store_type int COMMENT '店铺类型 22天街网店 23实体店 24直营店铺 33会员专区店',
store_label string COMMENT '店铺logo',
search_key string COMMENT '店铺搜索关键字',
end_time string COMMENT '营业结束时间',
start_time string COMMENT '营业开始时间',
operating_status tinyint COMMENT '营业状态 0 ：未营业 ；1 ：正在营业',
create_user string,
create_time string,
update_user string,
update_time string,
is_valid tinyint COMMENT '0关闭，1开启，3店铺申请中',
state string COMMENT '可使用的支付类型:MONEY金钱支付\;CASHCOUPON现金券支付',
idcard string COMMENT '身份证',
deposit_amount decimal(36,2) COMMENT '商圈认购费用总额',
delivery_config_id string COMMENT '配送配置表关联ID',
aip_user_id string COMMENT '通联支付标识ID',
search_name string COMMENT '模糊搜索名称字段:名称_+真实名称',
automatic_order tinyint COMMENT '是否开启自动接单功能 1：是 0 ：否',
is_primary tinyint COMMENT '是否是总店 1: 是 2: 不是',
parent_store_id string COMMENT '父级店铺的id，只有当is_primary类型为2时有效',
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 bj59_yp_dwd_jiale.dim_store_scd2_temp partition(start_date)
select
A.id ,
A.user_id ,
A.store_avatar,
A.address_info ,
A.name ,
A.store_phone ,
A.province_id ,
A.city_id ,
A.area_id ,
A.mb_title_img ,
A.store_description,
A.notice ,
A.is_pay_bond ,
A.trade_area_id ,
A.delivery_method ,
A.origin_price ,
A.free_price ,
A.store_type ,
A.store_label ,
A.search_key,
A.end_time,
A.start_time ,
A.operating_status ,
A.create_user ,
A.create_time ,
A.update_user ,
A.update_time ,
A.is_valid ,
A.state ,
A.idcard,
A.deposit_amount ,
A.delivery_config_id ,
A.aip_user_id,
A.search_name,
A.automatic_order,
A.is_primary,
A.parent_store_id ,
if(
B.id is null OR A.end_date != '9999-99-99',
A.end_date,
date_add(B.dt, -1)
) as end_date,
A.start_date
from bj59_yp_dwd_jiale.dim_store A left join (select * from yp_ods.t_store where dt = '2022-04-27') B
on A.id = B.id
union all
select
id ,
user_id ,
store_avatar,
address_info ,
name ,
store_phone ,
province_id ,
city_id ,
area_id ,
mb_title_img ,
store_description,
notice ,
is_pay_bond ,
trade_area_id ,
delivery_method ,
origin_price ,
free_price ,
store_type ,
store_label ,
search_key,
end_time,
start_time ,
operating_status ,
create_user ,
create_time ,
update_user ,
update_time ,
is_valid ,
state ,
idcard,
deposit_amount ,
delivery_config_id ,
aip_user_id,
search_name,
automatic_order,
is_primary,
parent_store_id ,
'9999-99-99' as end_date ,
dt as start_date
from yp_ods.t_store where dt = '2022-04-27';
-- 步骤三: 将临时表的数据覆盖写入到目标表
insert overwrite table bj59_yp_dwd_jiale.dim_store partition(start_date)
select
*
from bj59_yp_dwd_jiale.dim_store_scd2_temp;
-- 步骤四: 将临时表数据清空:
DROP TABLE if EXISTS bj59_yp_dwd_jiale.dim_store_scd2_temp;
CREATE TABLE bj59_yp_dwd_jiale.dim_store_scd2_temp(
id string COMMENT '主键',
user_id string,
store_avatar string COMMENT '店铺头像',
address_info string COMMENT '店铺详细地址',
name string COMMENT '店铺名称',
store_phone string COMMENT '联系电话',
province_id int COMMENT '店铺所在省份ID',
city_id int COMMENT '店铺所在城市ID',
area_id int COMMENT '店铺所在县ID',
mb_title_img string COMMENT '手机店铺 页头背景图',
store_description string COMMENT '店铺描述',
notice string COMMENT '店铺公告',
is_pay_bond tinyint COMMENT '是否有交过保证金 1：是0：否',
trade_area_id string COMMENT '归属商圈ID',
delivery_method tinyint COMMENT '配送方式 1 ：自提 ；3 ：自提加配送均可\; 2 : 商家配送',
origin_price decimal(36,2),
free_price decimal(36,2),
store_type int COMMENT '店铺类型 22天街网店 23实体店 24直营店铺 33会员专区店',
store_label string COMMENT '店铺logo',
search_key string COMMENT '店铺搜索关键字',
end_time string COMMENT '营业结束时间',
start_time string COMMENT '营业开始时间',
operating_status tinyint COMMENT '营业状态 0 ：未营业 ；1 ：正在营业',
create_user string,
create_time string,
update_user string,
update_time string,
is_valid tinyint COMMENT '0关闭，1开启，3店铺申请中',
state string COMMENT '可使用的支付类型:MONEY金钱支付\;CASHCOUPON现金券支付',
idcard string COMMENT '身份证',
deposit_amount decimal(36,2) COMMENT '商圈认购费用总额',
delivery_config_id string COMMENT '配送配置表关联ID',
aip_user_id string COMMENT '通联支付标识ID',
search_name string COMMENT '模糊搜索名称字段:名称_+真实名称',
automatic_order tinyint COMMENT '是否开启自动接单功能 1：是 0 ：否',
is_primary tinyint COMMENT '是否是总店 1: 是 2: 不是',
parent_store_id string COMMENT '父级店铺的id，只有当is_primary类型为2时有效',
end_date string COMMENT '拉链结束日期')
COMMENT '店铺表'
partitioned by (start_date string)
row format delimited fields terminated by '\t'
stored as orc
tblproperties ('orc.compress' = 'SNAPPY');
- 查询临时表| 目标表, 看到的结果: ```sql — 0afb5daf777d11e998ec7cd30ad32e2e
select * from bj59_yp_dwd_jiale.dim_store_scd2_temp where id =’0afb5daf777d11e998ec7cd30ad32e2e’ or start_date = ‘2022-04-27’;
<a name="eyyck"></a>
## 2- 额外其他的表处理
其他表仅需要处理全量操作即可, 增量操作不需要做
- 事实表:
```sql
--增量表，只会新增不会更新
--订单组支付表（增量表，与ODS一致）
INSERT overwrite TABLE bj59_yp_dwd_jiale.fact_order_pay PARTITION (dt)
SELECT
id
,group_id
,order_pay_amount
,create_date
,create_user
,create_time
,update_user
,update_time
,is_valid
,dt
FROM cy_class3_jiale_yp_ods.t_order_pay;
--用户登录记录表（增量表，与ODS一致）
INSERT overwrite TABLE bj59_yp_dwd_jiale.fact_user_login PARTITION(dt)
select
id,
login_user,
login_type,
client_id,
login_time,
login_ip,
logout_time,
SUBSTRING(login_time, 1, 10) as dt
FROM yp_ods.t_user_login;
--交易记录(增量表)
INSERT overwrite TABLE bj59_yp_dwd_jiale.fact_trade_record PARTITION (start_date)
SELECT
id,
external_trade_no,
relation_id,
trade_type,
status,
finnshed_time,
fail_reason,
payment_type,
trade_before_balance,
trade_true_amount,
trade_after_balance,
note,
user_card,
user_id,
aip_user_id,
create_user,
create_time,
update_user,
update_time,
is_valid,
'9999-99-99' as end_date
dt as start_date
FROM yp_ods.t_trade_record;
-- 拉链表
--订单事实表（拉链表）
INSERT overwrite TABLE bj59_yp_dwd_jiale.fact_shop_order PARTITION (start_date)
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,
dt as 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 and create_date is not null;
--订单详情表（拉链表）
INSERT overwrite TABLE bj59_yp_dwd_jiale.fact_shop_order_address_detail PARTITION (start_date)
SELECT
id,
order_amount,
discount_amount,
goods_amount,
is_delivery,
buyer_notes,
pay_time,
receive_time,
delivery_begin_time,
arrive_store_time,
arrive_time,
create_user,
create_time,
update_user,
update_time,
is_valid,
'9999-99-99' end_date,
dt as start_date
FROM yp_ods.t_shop_order_address_detail;
--商品评价表（拉链表，与ODS一致）
INSERT overwrite TABLE bj59_yp_dwd_jiale.fact_goods_evaluation_detail PARTITION(start_date)
select
id,
user_id,
store_id,
goods_id,
order_id,
order_goods_id,
geval_scores_goods,
geval_content,
geval_content_superaddition,
geval_addtime,
geval_addtime_superaddition,
geval_state,
geval_remark,
revert_state,
geval_explain,
geval_explain_superaddition,
geval_explaintime,
geval_explaintime_superaddition,
create_user,
create_time,
update_user,
update_time,
is_valid,
'9999-99-99' end_date,
dt as start_date
from yp_ods.t_goods_evaluation_detail;
--订单结算表
set hive.exec.dynamic.partition.mode=nonstrict;
INSERT overwrite TABLE bj59_yp_dwd_jiale.fact_order_settle PARTITION (start_date)
SELECT
id
,order_id
,settlement_create_date
,settlement_amount
,dispatcher_user_id
,dispatcher_money
,circle_master_user_id
,circle_master_money
,plat_fee
,store_money
,status
,note
,settle_time
,create_user
,create_time
,update_user
,update_time
,is_valid
,first_commission_user_id
,first_commission_money
,second_commission_user_id
,second_commission_money
,'9999-99-99' end_date,
dt as start_date
FROM yp_ods.t_order_settle;
--订单退款表
INSERT overwrite TABLE bj59_yp_dwd_jiale.fact_refund_order PARTITION (start_date)
SELECT
id
,order_id
,apply_date
,modify_date
,refund_reason
,refund_amount
,refund_state
,refuse_refund_reason
,refund_goods_type
,refund_shipping_fee
,create_user
,create_time
,update_user
,update_time
,is_valid
,'9999-99-99' end_date
,dt as start_date
FROM yp_ods.t_refund_order;
--订单组表（拉链表）
INSERT overwrite TABLE bj59_yp_dwd_jiale.fact_shop_order_group PARTITION (start_date)
SELECT
id,
order_id,
group_id,
is_pay,
create_user,
create_time,
update_user,
update_time,
is_valid,
'9999-99-99' end_date,
dt as start_date
FROM yp_ods.t_shop_order_group;
--订单商品快照(拉链表)
INSERT overwrite TABLE bj59_yp_dwd_jiale.fact_shop_order_goods_details PARTITION (start_date)
SELECT
id,
order_id,
shop_store_id,
buyer_id,
goods_id,
buy_num,
goods_price,
total_price,
goods_name,
goods_image,
goods_specification,
goods_weight,
goods_unit,
goods_type,
refund_order_id,
goods_brokerage,
is_refund,
create_user,
create_time,
update_user,
update_time,
is_valid,
'9999-99-99' end_date,
dt as start_date
FROM
yp_ods.t_shop_order_goods_details;
--购物车(拉链表)
INSERT overwrite TABLE bj59_yp_dwd_jiale.fact_shop_cart PARTITION (start_date)
SELECT
id,
shop_store_id,
buyer_id,
goods_id,
buy_num,
create_user,
create_time,
update_user,
update_time,
is_valid,
'9999-99-99' end_date,
dt as start_date
FROM
yp_ods.t_shop_cart;
--店铺收藏(拉链表)
INSERT overwrite TABLE bj59_yp_dwd_jiale.fact_store_collect PARTITION (start_date)
SELECT
id,
user_id,
store_id,
create_user,
create_time,
update_user,
update_time,
is_valid,
'9999-99-99' end_date,
dt as start_date
FROM yp_ods.t_store_collect;
--店铺收藏(拉链表)
INSERT overwrite TABLE bj59_yp_dwd_jiale.fact_goods_collect PARTITION (start_date)
SELECT
id,
user_id,
goods_id,
store_id,
create_user,
create_time,
update_user,
update_time,
is_valid,
'9999-99-99' end_date,
dt as start_date
FROM yp_ods.t_goods_collect;
-- 配送表: 拉链表
INSERT overwrite TABLE bj59_yp_dwd_jiale.fact_order_delievery_item PARTITION(start_date)
select
id,
shop_order_id,
refund_order_id,
dispatcher_order_type,
shop_store_id,
buyer_id,
circle_master_user_id,
dispatcher_user_id,
dispatcher_order_state,
order_goods_num,
delivery_fee,
distance,
dispatcher_code,
receiver_name,
receiver_phone,
sender_name,
sender_phone,
create_user,
create_time,
update_user,
update_time,
is_valid,
'9999-99-99' as end_date,
dt as start_date
FROM yp_ods.t_order_delievery_item;
- 维度表: ```sql —全量覆盖 —区域字典表 INSERT overwrite TABLE bj59_yp_dwd_jiale.dim_district select * from yp_ods.t_district WHERE code IS NOT NULL AND name IS NOT NULL;
—商圈 INSERT overwrite TABLE bj59_yp_dwd_jiale.dim_trade_area PARTITION(start_date) SELECT id, user_id, user_allinpay_id, trade_avatar, name, notice, distric_province_id, distric_city_id, distric_area_id, address, radius, mb_title_img, deposit_amount, hava_deposit, state, search_key, create_user, create_time, update_user, update_time, is_valid, ‘9999-99-99’ end_date, dt as start_date FROM yp_ods.t_trade_area;
—地址信息表（拉链表） INSERT overwrite TABLE bj59_yp_dwd_jiale.dim_location PARTITION(start_date) SELECT id, type, correlation_id, address, latitude, longitude, street_number, street, district, city, province, business, create_user, create_time, update_user, update_time, is_valid, adcode, ‘9999-99-99’ end_date, dt as start_date FROM yp_ods.t_location;
—商品SKU表（拉链表） INSERT overwrite TABLE bj59_yp_dwd_jiale.dim_goods PARTITION(start_date) SELECT id, store_id, class_id, store_class_id, brand_id, goods_name, goods_specification, search_name, goods_sort, goods_market_price, goods_price, goods_promotion_price, goods_storage, goods_limit_num, goods_unit, goods_state, goods_verify, activity_type, discount, seckill_begin_time, seckill_end_time, seckill_total_pay_num, seckill_total_num, seckill_price, top_it, create_user, create_time, update_user, update_time, is_valid, ‘9999-99-99’ end_date, dt as start_date FROM yp_ods.t_goods;
—商品分类（拉链表） INSERT overwrite TABLE bj59_yp_dwd_jiale.dim_goods_class PARTITION(start_date) SELECT id, store_id, class_id, name, parent_id, level, is_parent_node, background_img, img, keywords, title, sort, note, url, is_use, create_user, create_time, update_user, update_time, is_valid, ‘9999-99-99’ end_date, dt as start_date FROM yp_ods.t_goods_class;
—品牌表（拉链表） INSERT overwrite TABLE bj59_yp_dwd_jiale.dim_brand PARTITION(start_date) SELECT id, store_id, brand_pt_id, brand_name, brand_image, initial, sort, is_use, goods_state, create_user, create_time, update_user, update_time, is_valid, ‘9999-99-99’ end_date, dt as start_date FROM yp_ods.t_brand;
<a name="BnWa3"></a>
## 3- Join优化操作
思考: 在执行Join的SQL的时候, SQL会被翻译为MR, 思考, 翻译后MR默认是如何进行JOIN操作的呢?<br />
```properties
思考: 这种reduce端Join操作, 存在那些弊端呢?
1- 可能会存在数据倾斜的问题 (某几个reduce接收数据量远远大于其他的reduce接收数据量)
2- 所有的数据处理的操作, 全部都压在reduce中进行处理, 而reduce数量相比Map来说少的多,导致整个reduce压力比较大
思考: 如何提升Join的效率呢? 思路: 能否不让reduce做这个聚合处理的事情, 将这项工作尝试交给mapTask
3.1 Map Join
Map Join: 每一个mapTask在读取数据的时候, 每读取一条数据, 就会和内存中班级表数据进行匹配, 如果能匹配的上, 将匹配上数据合并在一起, 输出即可
好处: 将原有reduce join 问题全部都可以解决
弊端:
1- 比较消耗内存
2- 要求整个 Join 中, 必须的都有一个小表, 否则无法放入到内存中
仅适用于: 小表 join 大表 | 大表 join 小表
在老版本(1.x以下)中, 需要将小表放置在前面, 大表放置在后面, 在新版本中, 无所谓
建议, 如果明确知道那些表示小表, 可以优先将这些表, 放置在最前面
如何使用呢?
set hive.auto.convert.join=true; -- 开启 map join的支持 默认值为True
set hive.auto.convert.join.noconditionaltask.size=20971520; -- 设置 小表数据量的最大阈值: 默认值为 20971520(20M)
如果不满足条件, HIVE会自动使用 reduce join 操作
3.2 Bucket Map Join
- 中型表 和 大表 join:
- 方案一: 如果中型表能对数据进行提前过滤, 尽量提前过滤, 过滤后, 有可能满足了Map Join 条件 (并不一定可用)
- 方案二: Bucket Map Join
使用条件: 1- Join两个表必须是分桶表 2- 开启 Bucket Map Join 支持: set hive.optimize.bucketmapjoin = true; 3- 一个表的分桶数量是另一个表的分桶数量的整倍数 4- 分桶列 必须 是 join的ON条件的列 5- 必须建立在Map Join场景中
3.3 SMB JOIN
大表 和 大表 join
解决方案: SMB Join ( sort merge bucket map join) ```properties 使用条件: 1- 两个表必须都是分桶表 2- 开启 SMB Join 支持:
set hive.auto.convert.sortmerge.join=true; set hive.optimize.bucketmapjoin.sortedmerge = true; set hive.auto.convert.sortmerge.join.noconditionaltask=true; 3- 两个表的分桶的数量是一致的 4- 分桶列 必须是 join的 on条件的列, 同时必须保证按照分桶列进行排序操作 — 开启强制排序 set hive.enforce.sorting=true; — 在建分桶表使用: 必须使用sorted by()5- 应用在Bucket Map Join 场景中 — 开启 bucket map join set hive.optimize.bucketmapjoin = true;
6- 必须开启HIVE自动尝试使用SMB 方案: set hive.optimize.bucketmapjoin.sortedmerge = true;
最终汇总出来整体配置: set hive.auto.convert.join=true; set hive.auto.convert.join.noconditionaltask.size=20971520; set hive.optimize.bucketmapjoin = true; set hive.auto.convert.sortmerge.join=true; set hive.optimize.bucketmapjoin.sortedmerge = true; set hive.auto.convert.sortmerge.join.noconditionaltask=true; set hive.enforce.sorting=true; set hive.optimize.bucketmapjoin.sortedmerge = true;
建表: create table test_smb_2(mid string,age_id string) CLUSTERED BY(mid) SORTED BY(mid) INTO 500 BUCKETS;
至于分多少个桶: 取决于表的数据大小 和 小表阈值 之间相差了多少倍 ```
若有收获，就点个赞吧
0 人点赞
