图文精华

About云»专题 › 技术学习(版主发帖区) › 大数据学习 › Hive|数据仓库 › 连载型› 大数据项目之电商数仓（总结）（六）：系统业务数据仓库
分享

大数据项目之电商数仓（总结）（六）：系统业务数据仓库

levycui 2021-1-20 20:23:03 发表于 连载型 [显示全部楼层] 回帖奖励 阅读模式 关闭右栏 0 2596
问题导读：
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. 

  8. if [[ -n $1 ]]; then

  9.     do_date=$1

  10. else

  11.     do_date=`date -d '-1 day' +%F`

  12. fi

  13. 

  14. sql="

  15. use gmall;

  16. insert overwrite table dws_pay_user_detail partition(dt='$do_date')

  17. select

  18.    ua.user_id,

  19.    ui.name,

  20.    ui.birthday,

  21.    ui.gender,

  22.    ui.email,

  23.    ui.user_level

  24. from (

  25.   select user_id from dws_user_action where dt='$do_date'

  26. ) ua join(

  27.   select * from dwd_user_info where dt='$do_date'

  28. ) ui on ua.user_id=ui.id

  29. left join dws_pay_user_detail ud on ua.user_id=ud.user_id

  30. where ud.user_id is null;

  31. "

  32. 

  33. $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. # 定义变量方便修改

  6. APP=gmall

  7. hive=/opt/module/hive/bin/hive

  8. hadoop=/opt/module/hadoop-2.7.2/bin/hadoop

  9. 

  10. # 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天

  11. if [ -n "$1" ] ;then

  12.    do_date=$1

  13. else 

  14.    do_date=`date -d "-1 day" +%F`

  15. fi

  16. 

  17. echo "===日志日期为 $do_date==="

  18. sql="

  19. insert into table "$APP".ads_pay_user_count

  20. select

  21.     '$do_date',

  22.     count(*) pay_count

  23. from

  24.     "$APP".dws_pay_user_detail

  25. where

  26.     dt='$do_date';

  27. "

  28. 

  29. $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. # 定义变量方便修改

  6. APP=gmall

  7. hive=/opt/module/hive/bin/hive

  8. hadoop=/opt/module/hadoop-2.7.2/bin/hadoop

  9. 

  10. # 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天

  11. if [ -n "$1" ] ;then

  12.    do_date=$1

  13. else 

  14.    do_date=`date -d "-1 day" +%F`

  15. fi

  16. 

  17. echo "===日志日期为 $do_date==="

  18. sql="

  19. insert into table "$APP".ads_pay_user_ratio

  20. select

  21.     '$do_date' dt,

  22.     pay_count,

  23.     new_mid_count,

  24.     pay_count/new_mid_count*100 pay_count_ratio

  25. from

  26.     (select

  27.         '$do_date' dt,

  28.         pay_count

  29.     from

  30.         "$APP".ads_pay_user_count

  31.     )pay_user

  32. join

  33.     (select

  34.         '$do_date' dt, 

  35.         sum(new_mid_count) new_mid_count

  36.     from

  37.         "$APP".ads_new_mid_count

  38.     where

  39.         create_date<='$do_date'

  40.     )user_total_count

  41. on

  42.     pay_user.dt=user_total_count.dt;

  43. "

  44. 

  45. $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. # 定义变量方便修改

  7. APP=gmall

  8. hive=/opt/module/hive/bin/hive

  9. hadoop=/opt/module/hadoop-2.7.2/bin/hadoop

  10. 

  11. # 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天

  12. if [ -n "$1" ] ;then

  13.    do_date=$1

  14. else 

  15.    do_date=`date -d "-1 day" +%F`

  16. fi

  17. 

  18. echo "===日志日期为 $do_date==="

  19. sql="

  20. insert overwrite table "$APP".ads_user_last_pay

  21. select

  22.     if(du.user_id is null, au.user_id, du.user_id),  

  23.     if(du.user_id is null, au.pay_date,'$do_date')

  24. from

  25.     "$APP".ads_user_last_pay  au

  26. full join

  27.     (select

  28.         user_id

  29.     from

  30.         "$APP".dws_user_action

  31.     where

  32.         dt='$do_date'

  33.         and

  34.         payment_amount>0) du

  35. on

  36.     au.user_id=du.user_id;

  37. "

  38. 

  39. $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点
复制代码

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



回复

使用道具 举报

自定义广告语

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

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

发表新帖
levycui

超级版主

关注

  • 654

    主题

  • 1166

    帖子

  • 97

    粉丝

TA的主题
关闭

推荐上一条 /2 下一条

关于我们· 联系我们· 加入我们· 小黑屋· 合作伙伴   

Copyright © 2001-2025 About云 Powered by Discuz! X3.4 Licensed Discuz Team.

简书 / Smrz 京ICP备2020039040号 Wxb 简书网举报电话：021-34700000