数仓可视化2--数仓分层

 1、dwd层--用户主题明细层

1.1、码表说明

action行为种类：
INSTALL("01", "install","安装"),
LAUNCH("02", "launch","启动"),
LOGIN("03", "login","登录"),
REGISTER("04", "register","注册"),
INTERACTIVE("05", "interactive","交互行为"),
EXIT("06", "exit","退出"),
PAGE_ENTER_H5("07", "page_enter_h5","网页页面进入"),
PAGE_ENTER_NATIVE("08", "page_enter_native","app页面进入")

eventtype事件类型：
VIEW("01", "view","浏览"),
CLICK("02", "click","点击"),
INPUT("03", "input","输入"),
SLIDE("04", "slide","滑动")
 

 1.2、用户启动日志表

ps：下面的数据导入是分区要对应上自己的分区， action='02'

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、6-12、12-18、18-24）每个阶段用 1 2 3 4表示
  ct string comment '产生时间'
) partitioned by (bdp_day string)
stored as parquet
location '/data/nshop/dwd/user/dwd_nshop_actlog_launch/';

insert overwrite table dwd_nshop.dwd_nshop_actlog_launch partition(bdp_day='20231227')
  select
      customer_id ,
      device_num ,
      device_type ,
      os  ,
      os_version ,
      manufacturer,
      carrier,
      network_type,
      area_code,
          case when from_unixtime(cast(ct/1000+8*3600 as int),'HH') between 0 and 6 then 1
               when from_unixtime(cast(ct/1000+8*3600 as int),'HH') between 7 and 12 then 2
                when     from_unixtime(cast(ct/1000+8*3600 as int),'HH') between 13 and 18 then 3
                when      from_unixtime(cast(ct/1000+8*3600 as int),'HH') between 19 and 23 then 4 end launch_time_segment,
      ct
  from ods_nshop.ods_nshop_01_useractlog where action='02' and bdp_day='20231227';

1.3、用户产品浏览表 

action 是 ('07','08') 并且 event_type ='01';

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',
  duration int comment '停留时长',
  ct bigint comment '产生时间'
) partitioned by (bdp_day string)
stored as parquet
location '/data/nshop/dwd/user/dwd_nshop_actlog_pdtview/';

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

1.4、用户产品查询表

action ='05' 并且 event_type in ('01','04')

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/dwd/user/dwd_nshop_actlog_pdtsearch/';

insert overwrite table dwd_nshop.dwd_nshop_actlog_pdtsearch partition(bdp_day='20231227')
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='20231227'
and action='05'
and event_type in('01','04');

1.5、用户关注店铺表

target_action='01' 并且 target_type = 3

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/dwd/user/dwd_actlog_product_comment/';

with t as (
select 
    customer_id,
    device_num ,
  device_type ,
  os ,
  os_version ,
  manufacturer ,
  carrier ,
  network_type,
  area_code ,
  get_json_object(extinfo,'$.target_type') as target_type,
  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='20231227' and action='05' and event_type='02'
)
insert overwrite table dwd_nshop.dwd_actlog_product_comment partition(bdp_day='20231227')
select 
customer_id,
    device_num ,
  device_type ,
  os ,
  os_version ,
  manufacturer ,
  carrier ,
  network_type,
  area_code ,
  target_id,
  ct
from t where  target_type = '3'  and target_action='01';

2、交易主题 

ps:20191102有数据，所以添加了一个日期条件

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 '/data/nshop/dwd/order/dwd_nshop_orders_details/';

with r as (
   select order_id,count(1) zfcs from ods_nshop.ods_02_orders_pay_records
   where from_unixtime(cast(pay_ctime/1000 as int),'yyyyMMdd') ='20191102'
   group by order_id
)
insert overwrite table dwd_nshop.dwd_nshop_orders_details partition(bdp_day='20231227')
select
   o.order_id,
   o.order_status,
   p.supplier_code,
   p.product_code,
   o.customer_id,
o.consignee_zipcode,
o.pay_type,
o.pay_nettype,
  r.zfcs,
  t.product_price,
t.product_cnt,
t.weighing_cost,
o.district_money,
o.shipping_money,
o.payment_money,
t.is_activity,
o.order_ctime

 from
 ods_nshop.ods_02_orders  o
  join
 ods_nshop.ods_02_order_detail  t
 on o.order_id = t.order_id  and   from_unixtime(cast(o.order_ctime/1000+3600*8 as int),'yyyyMMdd') ='20191102'
  join
 dim_nshop.dim_pub_product  p
 on  t.product_id = p.product_code
  join
 r  on r.order_id = o.order_id ;

 3、营销主题明细表

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 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://127.0.0.1:8888/path?",a.release_params),'QUERY','ip') as release_ip,
a.release_session,
a.release_sources,
parse_url(concat("http://127.0.0.1:8888/path?",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 a.bdp_day='20231227'
)
insert overwrite table dwd_nshop.dwd_nshop_releasedatas partition(bdp_day='20231227')
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 dim_nshop.dim_pub_page b
on t1.release_product_page = b.page_code and b.page_type = '4'
join  dim_nshop.dim_pub_product c
on b.page_target = c.product_code;