|NO.Z.00025|——————————|BigDataEnd|——|Hadoop&PB级数仓.V09|——|PB数仓.v09|会员活跃度分析|新增会员&DWS层计算&ADS层计算|

|NO.Z.00025|——————————|BigDataEnd|——|Hadoop&PB级数仓.V09|——|PB数仓.v09|会员活跃度分析|新增会员&DWS层计算&ADS层计算|

一、新增会员DWS层计算

### --- 创建DWS层表~~~ # 语法use dws;drop table if exists dws.dws_member_add_day;create table dws.dws_member_add_day(`device_id` string,`uid` string,`app_v` string,`os_type` string,`language` string,`channel` string,`area` string,`brand` string,`dt` string) COMMENT '每日新增会员明细'stored as parquet;

~~~ # 操作实例hive (default)> use dws;hive (dws)> drop table if exists dws.dws_member_add_day;hive (dws)> create table dws.dws_member_add_day > ( > `device_id` string, > `uid` string, > `app_v` string, > `os_type` string, > `language` string, > `channel` string, > `area` string, > `brand` string, > `dt` string > ) COMMENT '每日新增会员明细' > stored as parquet;

### --- 加载DWS层数据[root@hadoop02 ~]# vim /data/yanqidw/script/member_active/dws_load_member_add_day.sh#!/bin/bashsource /etc/profileif [ -n "$1" ]then do_date=$1else do_date=`date -d "-1 day" +%F`fisql="insert into table dws.dws_member_add_dayselect t1.device_id,t1.uid,t1.app_v,t1.os_type,t1.language,t1.channel,t1.area,t1.brand,'$do_date'from dws.dws_member_start_day t1 left joindws.dws_member_add_day t2on t1.device_id=t2.device_idwhere t1.dt='$do_date'and t2.device_id is null;"hive -e "$sql"

### --- 授予可执行权限，并加载数据~~~ # 授予可执行权限[root@hadoop02 ~]# chmod +x /data/yanqidw/script/member_active/dws_load_member_add_day.sh

~~~ # 加载数据[root@hadoop02 ~]# /data/yanqidw/script/member_active/dws_load_member_add_day.sh

~~~ # 查看数据hive (dws)> select * from dws.dws_member_add_day limit 5;dws_member_add_day.device_id dws_member_add_day.uid dws_member_add_day.app_v dws_member_add_day.os_type dws_member_add_day.language dws_member_add_day.channel dws_member_add_day.area dws_member_add_day.brand dws_member_add_day.dt1FB872-9A10010000 2F10092A10000 1.1.13 0.5.2 chinese HV 桂林 xiaomi-3 2021-09-291FB872-9A10010001 2F10092A10001 1.1.18 3.1 chinese KJ 常德 iphone-5 2021-09-291FB872-9A10010002 2F10092A10002 1.1.10 9.6.0 chinese YB 石家庄 iphone-7 2021-09-291FB872-9A10010003 2F10092A10003 1.1.3 0.96 chinese XF 莱芜 Huawei-5 2021-09-291FB872-9A10010004 2F10092A10004 1.1.15 7.60 chinese AG 乳山 iphone-0 2021-09-29Time taken: 0.321 seconds, Fetched: 5 row(s)

二、新增会员ADS层计算

### --- 创建ADS层表~~~ # 语法use ads;drop table if exists ads.ads_new_member_cnt;create table ads.ads_new_member_cnt(`cnt` string)partitioned by(dt string)row format delimited fields terminated by ',';

~~~ # 操作实例hive (dws)> use ads;hive (ads)> drop table if exists ads.ads_new_member_cnt;hive (ads)> create table ads.ads_new_member_cnt > ( > `cnt` string > ) > partitioned by(dt string) > row format delimited fields terminated by ',';

### --- 加载ADS层数据[root@hadoop02 ~]# vim /data/yanqidw/script/member_active/ads_load_member_add.sh#!/bin/bashsource /etc/profileif [ -n "$1" ] ;then do_date=$1else do_date=`date -d "-1 day" +%F`fisql="insert overwrite table ads.ads_new_member_cntpartition (dt='$do_date')select count(1)from dws.dws_member_add_daywhere dt = '$do_date'"hive -e "$sql"

### --- 授予可执行权限并加载数据~~~ # 授予可执行权限[root@hadoop02 ~]# chmod +x /data/yanqidw/script/member_active/ads_load_member_add.sh

~~~ # 加载数据[root@hadoop02 ~]# /data/yanqidw/script/member_active/ads_load_member_add.sh

~~~ # 查看加载的数据hive (ads)> show partitions ads.ads_new_member_cnt;partitiondt=2021-09-27dt=2021-09-28dt=2021-09-29hive (ads)> select * from ads.ads_new_member_cnt;ads_new_member_cnt.cnt ads_new_member_cnt.dt0 2021-09-270 2021-09-2811000 2021-09-29

三、小结

### --- 调用脚本次序：[root@hadoop02 ~]# cd /data/yanqidw/script/member_active/./dws_load_member_add_day.sh./ads_load_member_add.sh

Walter Savage Landor:strove with none,for none was worth my strife.Nature I loved and, next to Nature, Art:I warm'd both hands before the fire of life.It sinks, and I am ready to depart

——W.S.Landor

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。