电商项目_dwd主题域

最新推荐文章于 2024-01-26 14:34:31 发布

a-tao必须奥利给

最新推荐文章于 2024-01-26 14:34:31 发布

阅读量886

点赞数 1

文章标签：大数据数据仓库

本文链接：https://blog.csdn.net/weixin_48370579/article/details/125802158

版权

主题域的划分

主题划分介绍

在不同的业务中，数仓建设都会涉及到几种类型的数据：通用行为数据、业务强相关的数据、通用行为衍生出的行为数据以及其它不易划分的数据：
# 1 通用行为数据主题：
  即各业务场景都容易出现行为，比如用户行为日志中的曝光、点击和下载等。
	
# 业务行为数据主题：
  主要是指和业务强相关的行为，比如说电商下单业务，视频业务中有播放行为。
	
# 衍生行为数据主题：
  主要是指非用户原始的行为，比如说留存，本身是由用户活跃来描述的。

# 其它行为数据主题：
  有一些多主题交叉的数据，比如跨业务和行为的阅读带来的购买行为数据主题

实现概述

#  1 DWD与DWS
公共汇总粒度事实层(DWS)和明细粒度事实层(DWD)的事实表作为数据仓库维度建模的核心，需紧绕业务过程来设计。通过获取描述业务过程的度量来表达业务过程，包括引用的维度和与业务过程有关的度量。度量通常为数值型数据，作为事实逻辑表的事实。事实属性则作为事实逻辑表的描述信息，关联维度则将事实属性中的外键字段关联对应维度

示例：
用户产品浏览主题 
create external table if not exists dwd_nshop.dwd_nshop_actlog_product_view(
  user_id string comment '用户id',
  os string comment '手机系统',
  os_version string comment '手机系统版本',
  manufacturer string comment '手机制造商',
  carrier string comment '电信运营商',
  network_type string comment '网络类型',
  area_code string comment '地区编码',
  target_id string comment '产品ID',
  ct bigint comment '产生时间'
) 

# 1 数据依赖：
	ODS层的用户行为日志(ods_nshop.ods_nshop_01_useractlog)
	
# 2 哪些维度(包括关联其他主题维度)、度量？
	维度：
		1 以用户相关的维度【user_id、os、os_version、manufacturer等等】
		2 关联相关主题维度【target_id】
		3 度量值 聚合操作(sum,count等等)
	维表列：
		时间、地区、产品维度等这类数据往往应用在dm层进行统计报表操作中处理或进行多维计算时作为多维组合方案使用，如grouping_sets(area_code地区、bdp_day时间维度、os操作系统等等)

二、DWD层建设---域的划分以及表的设计

# 1 DWD是什么？
	明细粒度事实层以业务过程作为建模驱动，基于每个具体的业务过程特点，构建最细粒度的明细层事实表。可以结合企业的数据使用特点，将明细事实表的某些重要维度属性字段做适当冗余，即宽表化处理.
#	明细粒度事实层(DWD)通常分为三种：事务事实表、周期快照事实表和累积快照事实表。
	
# 2 DWD中的信息有什么？
事实表中一条记录所表达的业务细节程度被称为粒度。通常粒度可以通过两种方式来表述：一种是维度属性组合所表示的细节程度，一种是所表示的具体业务含义。

作为度量业务过程的事实，通常为整型或浮点型的十进制数值，有可加性、半可加性和不可加性三种类型：
(1) 可加性事实是指可以按照与事实表关联的任意维度进行汇总。
示例：交易订单记录表中的【用户ID】可以进行count|distinct count汇总

(2) 半可加性事实只能按照特定维度汇总，不能对所有维度汇总。
示例：库存可以按照地点和商品进行汇总，而按时 间维度把一年中每个月的库存累加则毫无意义。
交易订单记录表中的【时间维度、地区维度】按照这类“自然属性”的维度进行统计，在主题层没有实际意义，而偏统计报表类计算更多在DM层进行汇总，或者在DWS层往往是以某个主题数据做核心，与其产生关系的其他主题数据作为度量值来进行统计汇总的。


(3) 完全不可加性。
示例：转化率、比率型等事实。

事务事实表

事务事实表用来描述业务过程，跟踪空间或时间上某点的度量事件，保存的是最原子的数据，也称为原子事实表。
示例：
	交易订单记录表、广告投放数据表，这类数据本身是一个业务过程。

周期快照事实表

周期快照事实表中的每行汇总了发生在某一标准周期， 如一天、 一周或一月的多个度量。 其粒度是周期性的时间段， 而不是单个事务。周期快照事实表通常包含许多数据的总计， 因为任何与事实表时间范围一致的记录都会被包含在内。

示例：
	一个月|一周的销售订单周期快照， 用于按产品统计每个月总的销售订单金额和产品销售数量

另外周期快照在库存管理和人力资源系统中有比较广泛的应用
1 零售商希望通过产品和商店分析每天保有商品的库存水平，分析的业务过程是零售商店库存的每日周期快照。 
2 在人力资源管理系统中， 分析包括员工数量、支付的工资、假期天数、新增员工数量、离职员工数量，晋升人员数量等。 这时需要建立一个每月员工统计周期快照。

累积快照事实表

累积快照事实表用来表述过程开始和结束之间的关键步骤事件，覆盖过程的整个生命周期，通常具有多个日期字段来记录关键时间点。当累积快照事实表随着生命周期不断变化时，记录也会随着过程的变化而被修改。

具体实现方式：拉链表形式表达事实数据的变化过程或称历史轨迹

1、用户主题域

DWD层建设----用户启动日志表

创建dwd层数据库

create database dwd_nshop;
use dwd_nshop;

(ods_nshop.ods_nshop_01_useractlog)

create external table if not exists dwd_nshop.dwd_nshop_actlog_launch(
  user_id string comment '用户id',
  device_num string comment '设备号',
  device_type string comment '设备类型',
  os string comment '手机系统',
  os_version string comment '手机系统版本',
  manufacturer string comment '手机制造商',
  carrier string comment '电信运营商',
  network_type string comment '网络类型',
  area_code string comment '地区编码',
  launch_time_segment string comment '启动时间段',-- 启动时间段分为四个阶段(0-6、7-12、13-18、19-24)每个阶段用 1 2 3 4表示
  ct bigint comment '产生时间'
) partitioned by (bdp_day string)
stored as parquet
location '/shujia/bigdata17/data/nshop/dwd/user/dwd_nshop_actlog_launch/'

DWD层建设----用户产品浏览表

(ods_nshop.ods_nshop_01_useractlog)

create external table if not exists dwd_nshop.dwd_nshop_actlog_pdtview(
  user_id string comment '用户id',
  device_num string comment '设备号',
  device_type string comment '设备类型',
  os string comment '手机系统',
  os_version string comment '手机系统版本',
  manufacturer string comment '手机制造商',
  carrier string comment '电信运营商',
  network_type string comment '网络类型',
  area_code string comment '地区编码',
  target_id string comment '产品ID',
  target_ids array<string> comment '产品IDS',
  duration string comment '停留时长',
  ct bigint comment '产生时间'
) partitioned by (bdp_day string)
stored as parquet
location '/shujia/bigdata17/data/nshop/dwd/user/dwd_nshop_actlog_pdtview/'

DWD层建设----用户产品查询表

(ods_nshop.ods_nshop_01_useractlog)

create external table if not exists dwd_nshop.dwd_nshop_actlog_pdtsearch(
  user_id string comment '用户id',
  device_num string comment '设备号',
  device_type string comment '设备类型',
  os string comment '手机系统',
  os_version string comment '手机系统版本',
  manufacturer string comment '手机制造商',
  carrier string comment '电信运营商',
  network_type string comment '网络类型',
  area_code string comment '地区编码',
  target_order string comment '查询排序方式',
  target_keys string comment '查询内容',
  target_id string comment '查询商品ID',
  ct bigint comment '产生时间'
) partitioned by (bdp_day string)
stored as parquet
location '/shujia/bigdata17/data/nshop/dwd/user/dwd_nshop_actlog_pdtsearch/'

DWD层建设----用户店铺关注表

(ods_nshop.ods_nshop_01_useractlog)

create external table if not exists dwd_nshop.dwd_actlog_product_comment(
  user_id string comment '用户id',
  device_num string comment '设备号',
  device_type string comment '设备类型',
  os string comment '手机系统',
  os_version string comment '手机系统版本',
  manufacturer string comment '手机制造商',
  carrier string comment '电信运营商',
  network_type string comment '网络类型',
  area_code string comment '地区编码',
  target_id string comment '产品ID',
  ct bigint comment '产生时间'
) partitioned by (bdp_day string)
stored as parquet
location '/shujia/bigdata17/data/nshop/dwd/user/dwd_actlog_product_comment/'

2、交易主题域

DWD层建设----交易订单明细流水表

(ods_nshop.ods_02_orders，ods_nshop.ods_02_order_detail，dim_nshop.dim_pub_product，ods_nshop.ods_02_orders_pay_records)

create external table if not exists dwd_nshop.dwd_nshop_orders_details(
  order_id string comment '订单ID',
  order_status int comment '订单状态：5已收货(完成)|6投诉 7退货',
  supplier_code VARCHAR(20)  COMMENT '店铺ID',
  product_code VARCHAR(20)  COMMENT '商品ID',
  customer_id string comment '用户id',
  consignee_zipcode string comment '收货人地址',
  pay_type string comment '支付类型：线上支付 10 网上银行 11 微信 12 支付宝 | 线下支付(货到付款) 20 ',
  pay_nettype varchar(1)  COMMENT '支付网络方式：0 wifi | 1 4g | 2 3g |3 线下支付',
  pay_count int comment '支付次数',
  product_price DECIMAL(5,1)  COMMENT '购买商品单价',
  product_cnt INT  COMMENT '购买商品数量',
  weighing_cost DECIMAL(2,1) COMMENT '商品加权价格',
  district_money DECIMAL(4,1)  COMMENT '优惠金额',
  shipping_money DECIMAL(8,1)  COMMENT '运费金额',
  payment_money DECIMAL(10,1)  COMMENT '支付金额',
  is_activity  int COMMENT '1:参加活动|0：没有参加活动',
  order_ctime bigint comment '创建时间'
) partitioned by (bdp_day string)
stored as parquet
location '/shujia/bigdata17/data/nshop/dwd/order/dwd_nshop_orders_details/'

3、营销活动主题

DWD层建设----广告投放数据表

(ods_nshop.ods_nshop_02_customer，ods_nshop.ods_nshop_01_releasedatas，dim_nshop.dim_pub_page，dim_nshop.dim_pub_product)

create external table if not exists dwd_nshop.dwd_nshop_releasedatas(
  customer_id string comment '用户id',
  device_num string comment '设备号',
  device_type string comment '设备类型',
  os string comment '手机系统',
  os_version string comment '手机系统版本',
  manufacturer string comment '手机制造商',
  area_code string comment '地区编码',
  release_sid string comment '投放请求id',
  release_ip string comment '投放方ip',
  release_session string comment '投放会话id',
  release_sources string comment '投放渠道',
  release_category string comment '投放浏览产品分类',
  release_product string comment '投放浏览产品',
  release_product_page string comment '投放浏览产品页',
  ct bigint comment '创建时间'
) partitioned by (bdp_day string)
stored as parquet
location '/shujia/bigdata17/data/nshop/dwd/release/dwd_nshop_releasedatas/'

DWD层Hql语句实现

用户启动日志表

把之前的lib路径参数修改回去

--开启本地模式
set hive.exec.mode.local.auto=true;
--开启动态分区
set hive.exec.dynamic.partition=true;  #开启动态分区，默认是false
set hive.exec.dynamic.partition.mode=nonstrict; #开启允许所有分区都是动态的，否则必须要有静态分区才能使用。

insert overwrite table dwd_nshop.dwd_nshop_actlog_launch partition (bdp_day)
select
customer_id,
device_num ,
device_type,
os  ,
os_version ,
manufacturer,
carrier ,
network_type,
area_code ,
from_unixtime(cast(ct/1000 as int),'HH'),
ct,
bdp_day
from ods_nshop.ods_nshop_01_useractlog
where action ='02'
and bdp_day='20220630'

用户浏览日志表

{"target_type":"4","target_keys":"20402","target_order":"31","target_ids":"[\"4320402595801\",\"4320402133801\",\"4320402919201\",\"4320402238501\"]"}


insert overwrite table dwd_nshop.dwd_nshop_actlog_pdtview partition(bdp_day='20220630')
select
customer_id,
device_num ,
device_type,
os,
os_version ,
manufacturer,
carrier ,
network_type,
area_code ,
get_json_object(extinfo,'$.target_ids') as target_ids,
get_json_object(extinfo,'$.target_id') as target_id,
duration,
ct
from ods_nshop.ods_nshop_01_useractlog
where bdp_day='20220630'
and action in('07','08')
and event_type ='01'

用户产品查询表

{"target_type":"4","target_keys":"20402","target_order":"31","target_ids":"[\"4320402595801\",\"4320402133801\",\"4320402919201\",\"4320402238501\"]"}

insert overwrite table dwd_nshop.dwd_nshop_actlog_pdtsearch partition(bdp_day='20220630')
select
customer_id,
device_num,
device_type,
os,
os_version ,
manufacturer,
carrier,
network_type,
area_code,
get_json_object(extinfo,'$.target_order') as target_order,
get_json_object(extinfo,'$.target_keys') as target_keys,
target_id,
ct
from ods_nshop.ods_nshop_01_useractlog
lateral view explode(split(regexp_replace(get_json_object(extinfo,'$.target_ids'),'[\\[\\]\\"]',''),',')) t as target_id
where bdp_day='20220630'
and action='05'
and event_type in('01','04');

用户店铺关注表

with t1 as(select
customer_id,
device_num ,
device_type,
os,
os_version ,
manufacturer,
carrier ,
network_type,
area_code ,
get_json_object(extinfo,'$.target_action') as target_action,
get_json_object(extinfo,'$.target_id') as target_id,
ct
from ods_nshop.ods_nshop_01_useractlog
where bdp_day='20220630'
and action='05'
and event_type='02'
)
insert overwrite table dwd_nshop.dwd_actlog_product_comment partition(bdp_day='20220630')
select
customer_id,
device_num ,
device_type,
os,
os_version ,
manufacturer,
carrier ,
network_type,
area_code ,
target_id,
ct
from t1 
where target_action ='01'

语句 with as (新知识)

with talbeName as(
),
tableName2 as(
),
tableName3 as(
)...
tableNameN as(
)
insert ....
select
*
from tableNameN

交易订单明细流水表

--统计支付次数
with t1 as(
    select
    count(*) as pay_count,
    order_id
    from ods_nshop.ods_02_orders_pay_records
    where from_unixtime(cast(pay_ctime/1000 as int),'yyyyMMdd')='20191102'
    group by order_id
),
--获取order表字段
t2 as(
    select
    order_id,
    order_status,
    customer_id,
    consignee_zipcode,
    pay_type,
    pay_nettype,
    district_money,
    shipping_money,
    payment_money,
    order_ctime
    from ods_nshop.ods_02_orders
    where from_unixtime(cast(order_ctime/1000 as int),'yyyyMMdd')='20191102'
),
--获取商品和订单字段
t3 as(
    select 
    a.order_id,
    a.product_id,
    a.product_cnt,
    a.product_price,
    a.weighing_cost,
    a.is_activity,
    b.supplier_code
    from ods_nshop.ods_02_order_detail a 
    join ods_nshop.dim_pub_product b 
    on a.product_id =b.product_code
    where from_unixtime(cast(a.order_detail_ctime/1000 as int),'yyyyMMdd')='20191102'
)
--汇总
insert overwrite table dwd_nshop.dwd_nshop_orders_details partition(bdp_day='20220630')
select
t2.order_id,
t2.order_status,
t3.supplier_code,
t3.product_id,
t2.customer_id,
t2.consignee_zipcode,
t2.pay_type,
t2.pay_nettype,
t1.pay_count,
t3.product_price,
t3.product_cnt,
t3.weighing_cost,
t2.district_money,
t2.shipping_money,
t2.payment_money,
t3.is_activity,
t2.order_ctime
from t1
join t2 
on t1.order_id=t2.order_id
join t3
on t2.order_id=t3.order_id

广告投放数据表(产品页的广告投放)

数据样式：
http://shujia:6666/shujia_release?ip=3.61.2.222&deviceNum=723748&lon=null&lat=null&aid=null&ctime=1572654905000&sources=tencent&session=1572654785000723748416198&productPage=4320308033801
-- 关联用户表获取用户ID，解析投放参数
with t1 as(
    select
    b.customer_id,
    a.device_num,
    a.device_type ,
    a.os ,
    a.os_version ,
    a.manufacturer ,
    a.area_code ,
    a.release_sid ,
    parse_url(concat("http://shujia:6666/shujia_release?",a.release_params),'QUERY','ip') as release_ip,
    a.release_session,
    a.release_sources,
    parse_url(concat("http://shujia:6666/shujia_release?",a.release_params),'QUERY','productPage') as release_product_page,
    a.ct
    from ods_nshop.ods_nshop_01_releasedatas a
    join ods_nshop.ods_02_customer b
    on a.device_num=b.customer_device_num
    where bdp_day='20220630'
)
insert overwrite table dwd_nshop.dwd_nshop_releasedatas partition(bdp_day='20220630')
select
t1.customer_id,
t1.device_num,
t1.device_type ,
t1.os ,
t1.os_version ,
t1.manufacturer ,
t1.area_code ,
t1.release_sid,
t1.release_ip,
t1.release_session,
t1.release_sources,
c.category_code,
b.page_target,
t1.release_product_page,
t1.ct
from t1 
join ods_nshop.dim_pub_page b
on t1.release_product_page = b.page_code and b.page_type='4'
join ods_nshop.dim_pub_product c
on b.page_target=c.product_code

a-tao必须奥利给

关注

1
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
电商项目_dwd主题域

主题域的划分主题划分介绍在不同的业务中，数仓建设都会涉及到几种类型的数据：通用行为数据、业务强相关的数据、通用行为衍生出的行为数据以及其它不易划分的数据：# 1 通用行为数据主题：即各业务场景都容易出现行为，比如用户行为日志中的曝光、点击和下载等。 # 业务行为数据主题：主要是指和业务强相关的行为，比如说电商下单业务，视频业务中有播放行为。 # 衍生行为数据主题： ...
复制链接

扫一扫