分享

大数据项目之电商数仓(总结)(六):系统业务数据仓库

问题导读:
1、如何统计新付费用户数?
2、如何使用付费用户数以及新增用户表作为数据源?
3、如何统计每个用户最近一次购买时间?
4、如何设计用户行为宽表作为DWS层数据?


上一篇:大数据项目之电商数仓(总结)(五):系统业务数据仓库

第8章 需求五:新付费用户数

8.1 DWS层
8.1.1 建表语句
  1. drop table if exists dws_pay_user_detail;
  2. create external table dws_pay_user_detail(   
  3.     `user_id` string comment '付费用户id',
  4.     `name` string comment '付费用户姓名',
  5.     `birthday` string COMMENT '',
  6.     `gender` string COMMENT '',
  7.     `email` string COMMENT '',
  8.     `user_level` string COMMENT ''
  9. ) COMMENT '付费用户表'
  10. PARTITIONED BY (`dt` string)
  11. stored as parquet
  12. location '/warehouse/gmall/dws/dws_pay_user_detail/';
复制代码


8.1.2 导入数据
  1. insert overwrite table dws_pay_user_detail partition(dt='2019-10-03')
  2. select
  3.    ua.user_id,
  4.    ui.name,
  5.    ui.birthday,
  6.    ui.gender,
  7.    ui.email,
  8.    ui.user_level
  9. from (
  10.   select user_id from dws_user_action where dt='2019-10-03'
  11. ) ua join(
  12.   select * from dwd_user_info where dt='2019-10-03'
  13. ) ui on ua.user_id=ui.id
  14. left join dws_pay_user_detail ud on ua.user_id=ud.user_id
  15. where ud.user_id is null
复制代码


8.1.3 导入数据脚本
1)在/home/kgg/bin目录下创建脚本dws_pay_user_detail.sh
  1. [kgg@hadoop102 bin]$ vim dws_pay_user_detail.sh
  2. 在脚本中填写如下内容
  3. #!/bin/bash
  4. db=gmall
  5. hive=/opt/module/hive-1.2.1/bin/hive
  6. hadoop=/opt/module/hadoop-2.7.2/bin/hadoop
  7. if [[ -n $1 ]]; then
  8.     do_date=$1
  9. else
  10.     do_date=`date -d '-1 day' +%F`
  11. fi
  12. sql="
  13. use gmall;
  14. insert overwrite table dws_pay_user_detail partition(dt='$do_date')
  15. select
  16.    ua.user_id,
  17.    ui.name,
  18.    ui.birthday,
  19.    ui.gender,
  20.    ui.email,
  21.    ui.user_level
  22. from (
  23.   select user_id from dws_user_action where dt='$do_date'
  24. ) ua join(
  25.   select * from dwd_user_info where dt='$do_date'
  26. ) ui on ua.user_id=ui.id
  27. left join dws_pay_user_detail ud on ua.user_id=ud.user_id
  28. where ud.user_id is null;
  29. "
  30. $hive -e "$sql"
复制代码

2)增加脚本执行权限
  1. [kgg@hadoop102 bin]$ chmod 777 dws_pay_user_detail.sh
复制代码

3)脚本使用
  1. [kgg@hadoop102 module]$ dws_pay_user_detail.sh 2019-02-20
复制代码

4)查询结果
  1. hive (gmall)> select * from dws_pay_user_detail;
复制代码

5)脚本执行时间
  1. 企业开发中一般在每天凌晨30分~1点
复制代码


8.2 ADS层
8.2.1 建表语句
  1. drop table if exists ads_pay_user_count;
  2. create external table ads_pay_user_count(   
  3.     dt string COMMENT '统计日期',
  4.     pay_count   bigint  COMMENT '付费用户数'
  5. ) COMMENT '付费用户表'
  6. stored as parquet
  7. location '/warehouse/gmall/dws/ads_pay_user_count/';
复制代码


8.2.2 导入数据
  1. insert into table ads_pay_user_count
  2. select
  3.     '2019-02-10',
  4.     count(*) pay_count
  5. from
  6.     dws_pay_user_detail
  7. where
  8.     dt='2019-02-10';
复制代码


8.2.3 导入数据脚本
1)在/home/kgg/bin目录下创建脚本ads_pay_user_count.sh
  1. [kgg@hadoop102 bin]$ vim ads_pay_user_count.sh
  2. 在脚本中填写如下内容
  3. #!/bin/bash
  4. # 定义变量方便修改
  5. APP=gmall
  6. hive=/opt/module/hive/bin/hive
  7. hadoop=/opt/module/hadoop-2.7.2/bin/hadoop
  8. # 如果是输入的日期按照取输入日期;如果没输入日期取当前时间的前一天
  9. if [ -n "$1" ] ;then
  10.    do_date=$1
  11. else
  12.    do_date=`date -d "-1 day" +%F`
  13. fi
  14. echo "===日志日期为 $do_date==="
  15. sql="
  16. insert into table "$APP".ads_pay_user_count
  17. select
  18.     '$do_date',
  19.     count(*) pay_count
  20. from
  21.     "$APP".dws_pay_user_detail
  22. where
  23.     dt='$do_date';
  24. "
  25. $hive -e "$sql"
复制代码

2)增加脚本执行权限
  1. [kgg@hadoop102 bin]$ chmod 777 ads_pay_user_count.sh
复制代码

3)脚本使用
  1. [kgg@hadoop102 module]$ ads_pay_user_count.sh 2019-02-20
复制代码

4)查询结果
  1. hive (gmall)> select * from ads_pay_user_count;
复制代码

5)脚本执行时间
企业开发中一般在每天凌晨30分~1点

第9章 需求六:付费用户率
9.1 DWS层
使用付费用户数以及新增用户表作为数据源

9.2 ADS层
9.2.1 建表语句
  1. drop table if exists ads_pay_user_ratio;
  2. create external table ads_pay_user_ratio (   
  3.     dt string comment '统计日期',
  4.     pay_count   bigint  comment '总付费用户数',
  5.     user_count bigint comment '总用户数',
  6.     pay_count_ratio decimal(10,2) COMMENT '付费用户比率'
  7. ) COMMENT '付费用户率表'
  8. stored as parquet
  9. location '/warehouse/gmall/dws/ads_pay_user_ratio';
复制代码


9.2.2 导入数据
  1. insert into table ads_pay_user_ratio
  2. select
  3.     '2019-02-10' dt,
  4.     pay_count,
  5.     new_mid_count,
  6.     pay_count/new_mid_count*100 pay_count_ratio
  7. from
  8.     (select
  9.         '2019-02-10' dt,
  10.         pay_count
  11.     from
  12.         ads_pay_user_count
  13.     )pay_user
  14. join
  15.     (select
  16.         '2019-02-10' dt,
  17.         sum(new_mid_count) new_mid_count
  18.     from
  19.         ads_new_mid_count
  20.     where
  21.         create_date<='2019-02-10'
  22.     )user_total_count
  23. on
  24.     pay_user.dt=user_total_count.dt;
复制代码



9.2.3 导入数据脚本
1)在/home/kgg/bin目录下创建脚本ads_pay_user_ratio.sh
  1. [kgg@hadoop102 bin]$ vim ads_pay_user_ratio.sh
  2. 在脚本中填写如下内容
  3. #!/bin/bash
  4. # 定义变量方便修改
  5. APP=gmall
  6. hive=/opt/module/hive/bin/hive
  7. hadoop=/opt/module/hadoop-2.7.2/bin/hadoop
  8. # 如果是输入的日期按照取输入日期;如果没输入日期取当前时间的前一天
  9. if [ -n "$1" ] ;then
  10.    do_date=$1
  11. else
  12.    do_date=`date -d "-1 day" +%F`
  13. fi
  14. echo "===日志日期为 $do_date==="
  15. sql="
  16. insert into table "$APP".ads_pay_user_ratio
  17. select
  18.     '$do_date' dt,
  19.     pay_count,
  20.     new_mid_count,
  21.     pay_count/new_mid_count*100 pay_count_ratio
  22. from
  23.     (select
  24.         '$do_date' dt,
  25.         pay_count
  26.     from
  27.         "$APP".ads_pay_user_count
  28.     )pay_user
  29. join
  30.     (select
  31.         '$do_date' dt,
  32.         sum(new_mid_count) new_mid_count
  33.     from
  34.         "$APP".ads_new_mid_count
  35.     where
  36.         create_date<='$do_date'
  37.     )user_total_count
  38. on
  39.     pay_user.dt=user_total_count.dt;
  40. "
  41. $hive -e "$sql"
复制代码

2)增加脚本执行权限
  1. [kgg@hadoop102 bin]$ chmod 777 ads_pay_user_ratio.sh
复制代码

3)脚本使用
  1. [kgg@hadoop102 module]$ ads_pay_user_ratio.sh 2019-02-20
复制代码

4)查询结果
  1. hive (gmall)> select * from ads_pay_user_ratio;
复制代码

5)脚本执行时间
  1. <div>企业开发中一般在每天凌晨30分~1点</div>
复制代码



第10章 需求七:每个用户最近一次购买时间

10.1 DWS层
使用用户行为宽表作为DWS层数据

10.2 ADS层
10.2.1 建表语句
  1. drop table if exists ads_user_last_pay;
  2. create external table ads_user_last_pay(
  3.     user_id   string  comment '用户id',
  4.     pay_date string comment '最近一次购买时间'
  5. ) COMMENT '用户最近一次购买时间表'
  6. stored as parquet
  7. location '/warehouse/gmall/dws/ads_user_last_pay/';
复制代码


10.2.2 导入数据
初始化数据
  1. insert into table ads_user_last_pay
  2. select
  3.     user_id,
  4.     '2019-02-10'
  5. from
  6.     dws_user_action
  7. where
  8.     dt='2019-02-10'
  9.     and
  10.     payment_amount>0;
复制代码

导入其他日期数据
  1. insert overwrite table ads_user_last_pay
  2. select
  3.     if(du.user_id is null, au.user_id, du.user_id),  
  4.     if(du.user_id is null, au.pay_date,'2019-02-11')
  5. from
  6.     ads_user_last_pay  au
  7. full join
  8.     (select
  9.         user_id
  10.     from
  11.         dws_user_action
  12.     where
  13.         dt='2019-02-11'
  14.         and
  15.         payment_amount>0) du
  16. on
  17.     au.user_id=du.user_id;
复制代码


10.2.3 导入数据脚本
  1. 1)在/home/kgg/bin目录下创建脚本ads_user_last_pay.sh
  2. [kgg@hadoop102 bin]$ vim ads_user_last_pay.sh
  3. 在脚本中填写如下内容
  4. #!/bin/bash
  5. # 定义变量方便修改
  6. APP=gmall
  7. hive=/opt/module/hive/bin/hive
  8. hadoop=/opt/module/hadoop-2.7.2/bin/hadoop
  9. # 如果是输入的日期按照取输入日期;如果没输入日期取当前时间的前一天
  10. if [ -n "$1" ] ;then
  11.    do_date=$1
  12. else
  13.    do_date=`date -d "-1 day" +%F`
  14. fi
  15. echo "===日志日期为 $do_date==="
  16. sql="
  17. insert overwrite table "$APP".ads_user_last_pay
  18. select
  19.     if(du.user_id is null, au.user_id, du.user_id),  
  20.     if(du.user_id is null, au.pay_date,'$do_date')
  21. from
  22.     "$APP".ads_user_last_pay  au
  23. full join
  24.     (select
  25.         user_id
  26.     from
  27.         "$APP".dws_user_action
  28.     where
  29.         dt='$do_date'
  30.         and
  31.         payment_amount>0) du
  32. on
  33.     au.user_id=du.user_id;
  34. "
  35. $hive -e "$sql"
复制代码

2)增加脚本执行权限
  1. [kgg@hadoop102 bin]$ chmod 777 ads_user_last_pay.sh
复制代码

3)脚本使用
  1. [kgg@hadoop102 module]$ ads_user_last_pay.sh 2019-02-12
复制代码

4)查询结果
  1. hive (gmall)> select * from ads_user_last_pay;
复制代码

5)脚本执行时间
  1. 企业开发中一般在每天凌晨30分~1点
复制代码

最新经典文章,欢迎关注公众号



没找到任何评论,期待你打破沉寂

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

关闭

推荐上一条 /5 下一条