2. 第二层DW主题层
2.1 DWD明细层
2.1.1 用户主题
DWD明细粒度事实层Hive数据库建库建表：
创建Hive库并进入：
create database if not exists dwd_nshop;
use dwd_nshop;
在DWD层开始入库之前，我们有一张通用字典表，在mysql已经导入，为后面的数据筛选做条件过滤：
2.1.1.1 用户启动日志表【DWD】
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 '启动时间段',
ct BIGINT COMMENT '产生时间'
) partitioned BY (bdp_day string) stored AS parquet location '/data/nshop/dw/user/dwd_nshop_actlog_launch/';
从ods层事件表里选择action=02（启动）的事件数据导入即可：
insert overwrite table dwd_nshop.dwd_nshop_actlog_launch partition(bdp_day="20200618")
select
customer_id,
device_num,
device_type,
os,
os_version,
manufacturer,
carrier,
network_type,
area_code,
from_unixtime(cast(ct/1000 as int),'HH') as launch_time_segment,
ct
from
ods_nshop.ods_nshop_01_useractlog
where
bdp_day = "20200618"
and
action = "02"
2.1.1.2 用户产品浏览表【DWD】
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',
ct BIGINT COMMENT '产生时间'
) partitioned BY (bdp_day string) stored AS parquet location '/data/nshop/dw/user/dwd_nshop_actlog_pdtview/'
从ods层的事件表里筛选出action为07或08的数据，即为用户浏览数据：
insert overwrite table dwd_nshop.dwd_nshop_actlog_pdtview partition(bdp_day="20200618")
select
customer_id,
device_num,
device_type,
os,
os_version,
manufacturer,
carrier,
network_type,
area_code,
get_json_object(extinfo,'$.target_id')as target_id,
ct
from
ods_nshop.ods_nshop_01_useractlog
where
bdp_day = "20200618"
and
action in('07','08')
2.1.1.3 用户产品查询表【DWD】
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 '/data/nshop/dw/user/dwd_nshop_actlog_pdtsearch/';
查看公共字典表：
我们知道，选择action为05(交互)和event_type为01(浏览)或04(滑动)的行为是用户查询行为,因此写出下列sql语句
insert overwrite table dwd_nshop.dwd_nshop_actlog_pdtsearch partition(bdp_day="20200618")
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,
get_json_object(extinfo,'$.target_id')as 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 = "20200618"
and
action = '05'
and
event_type in('01','04')
2.1.1.4 用户产品关注表【DWD】
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 '/data/nshop/dw/user/dwd_actlog_product_comment/';
选择action ='05'(交互)，event_type='02'(点击)，并且target_action='01'(店铺关注)的事件即可：
with log_attend 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,
extinfo,
ct,
bdp_day
from
ods_nshop.ods_nshop_01_useractlog
where
bdp_day = "20200618"
and
action ='05'
and
event_type='02'
)
insert overwrite table dwd_nshop.dwd_actlog_product_comment partition(bdp_day="20200618")
select
customer_id,
device_num,
device_type,
os,
os_version,
manufacturer,
carrier,
network_type,
area_code,
target_id,
ct
from
log_attend where target_action='01'
2.1.2 交易主题
2.1.2.1 交易订单明细流水表【DWD】
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 string COMMENT '店铺ID',
product_code string COMMENT '商品ID',
customer_id string COMMENT '用户id',
device_type string COMMENT '设备类型',
os string COMMENT '手机系统',
os_version string COMMENT '手机系统版本',
manufacturer string COMMENT '手机制造商',
carrier string COMMENT '电信运营商',
user_areacode string COMMENT '用户所在地区',
consignee_zipcode string COMMENT '收货人地址',
pay_type string COMMENT '支付类型：线上支付 10 网上银行 11 微信 12 支付宝 | 线下支 付(货到付款) 20 ',
pay_count INT COMMENT '支付次数',
product_price DECIMAL (5, 1) COMMENT '购买商品单价',
weighing_cost DECIMAL (2, 1) COMMENT '商品加权价格',
district_money DECIMAL (4, 1) COMMENT '优惠金额',
is_activity int COMMENT '1:参加活动|0：没有参加活动',
order_ctime BIGINT COMMENT '创建时间'
) partitioned BY (bdp_day string) stored AS parquet location '/data/nshop/dwd/order/dwd_nshop_orders_details/';
订单明细流水表的数据主要是从ods层的订单详细表里抽取，结合公共产品维度表查出产品相关信息，另外还结合订单支付记录表的数据，查出支付相关信息，sql语句如下：
with tborder as(
select
order_id,
order_status,
customer_id,
consignee_zipcode,
pay_type,
order_ctime,
from_unixtime(cast(order_ctime/1000 as bigint),'yyyyMMdd') as bdp_day
from ods_nshop.ods_02_orders
where
from_unixtime(cast(order_ctime/1000 as bigint),'yyyyMMdd') = '20200618'
),
tbdetail as(
select
a.order_id,
a.product_id,
a.product_cnt,
a.weighing_cost,
a.district_money,
a.is_activity,
b.supplier_code,
b.product_price,
from_unixtime(cast(a.order_detail_ctime/1000 as bigint),'yyyyMMdd') as bdp_day
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 bigint),'yyyyMMdd')='20200618'
),
pays as (
select
count(*) as pay_count,
order_id,
from_unixtime(cast(pay_ctime/1000 as bigint),'yyyyMMdd') as bdp_day
from ods_nshop.ods_02_orders_pay_records
where
from_unixtime(cast(pay_ctime/1000 as bigint),'yyyyMMdd')='20200618'
group by
order_id,
from_unixtime(cast(pay_ctime/1000 as bigint),'yyyyMMdd')
)
insert overwrite table dwd_nshop.dwd_nshop_orders_details partition (bdp_day)
select
tborder.order_id,
tborder.order_status,
tbdetail.supplier_code,
tbdetail.product_id,
tborder.customer_id,
tborder.consignee_zipcode,
tborder.pay_type,
pays.pay_count,
tbdetail.product_price,
tbdetail.product_cnt,
tbdetail.weighing_cost,
tbdetail.district_money,
tbdetail.is_activity,
tborder.order_ctime,
tborder.bdp_day
from tborder join tbdetail
on tborder.order_id=tbdetail.order_id
join pays
on tbdetail.order_id=pays.order_id
2.2.1 订单支付表【DWD】
CREATE external TABLE
IF NOT EXISTS dwd_nshop.dwd_order_pay (
pay_id string COMMENT '订单支付记录ID',
order_id string COMMENT '订单ID',
customer_id string COMMENT '用户ID',
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 '地区编码',
longitude string COMMENT '经度',
latitude string COMMENT '纬度',
page_id string COMMENT '行为所在页',
target_action string COMMENT '参考点击目标类型说明',
target_id string COMMENT '订单id',
target_price DOUBLE COMMENT '订单价格',
target_status string COMMENT '支付状态:0 未支付| 1 支付中 | 2 已支付',
target_type string COMMENT '支付类型：线上支付 10 网上银行 11 微信 12 支付宝 | 线 下支付(货到付款) 20 ',
ct BIGINT COMMENT '产生时间'
) partitioned BY (bdp_day string) stored AS parquet location '/data/nshop/dwd/order/dwd_order_pay/'
2.1.3 营销活动主题
广告投放数据表【DWD】
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 '/data/nshop/dwd/release/dwd_nshop_releasedatas/'
这里需要关联两张中间表，一个是产品表，查出产品分类，一个是页面布局表，选择产品页，两张表结构如下：
页面布局表
插入数据：
with tbrelease as(
select
c.customer_id,
r.device_num ,
r.device_type ,
r.os ,
r.os_version ,
r.manufacturer ,
r.area_code ,
r.release_sid ,
parse_url(concat("http://127.0.0.1:8088/release?",'',r.release_params),'QUERY','ip')as release_ip,
r.release_session,
r.release_sources,
parse_url(concat("http://127.0.0.1:8088/release?",'',r.release_params),'QUERY','productPage')as release_product_page,
r.ct
from ods_nshop.ods_01_releasedatas r
join ods_nshop.ods_02_customer c
on r.customer_id=c.customer_id
where
bdp_day='20200618'
)
insert overwrite table dwd_nshop.dwd_nshop_releasedatas partition(bdp_day='20200618')
select
a.customer_id,
a.device_num ,
a.device_type ,
a.os ,
a.os_version ,
a.manufacturer,
a.area_code ,
a.release_sid ,
a.release_ip,
a.release_session,
a.release_sources,
f.category_code release_category,
p.page_target release_product,
a.release_product_page,
a.ct
from tbrelease a
join dim_nshop.dim_pub_page p
on a.release_product_page=p.page_code and p.page_type='4'
join dim_nshop.dim_pub_product f
on p.page_code=f.product_code;
