CC00025.pbpositions——|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/bash

source /etc/profile

if [ -n "$1" ]
then
    do_date=$1
else
    do_date=`date -d "-1 day" +%F`
fi

sql="
insert into table dws.dws_member_add_day
select 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 join
dws.dws_member_add_day t2
on t1.device_id=t2.device_id
where 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.dt
1FB872-9A10010000   2F10092A10000   1.1.13  0.5.2   chinese HV  桂林  xiaomi-3    2021-09-29
1FB872-9A10010001   2F10092A10001   1.1.18  3.1 chinese KJ  常德  iphone-5    2021-09-29
1FB872-9A10010002   2F10092A10002   1.1.10  9.6.0   chinese YB  石家庄 iphone-7    2021-09-29
1FB872-9A10010003   2F10092A10003   1.1.3   0.96    chinese XF  莱芜  Huawei-5    2021-09-29
1FB872-9A10010004   2F10092A10004   1.1.15  7.60    chinese AG  乳山  iphone-0    2021-09-29
Time 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/bash

source /etc/profile

if [ -n "$1" ] ;
then
    do_date=$1
else
    do_date=`date -d "-1 day" +%F`
fi

sql="
insert overwrite table ads.ads_new_member_cnt
partition (dt='$do_date')
select count(1)
from dws.dws_member_add_day
where 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;
partition
dt=2021-09-27
dt=2021-09-28
dt=2021-09-29

hive (ads)> select * from ads.ads_new_member_cnt;
ads_new_member_cnt.cnt  ads_new_member_cnt.dt
0   2021-09-27
0   2021-09-28
11000   2021-09-29

三、小结

### --- 调用脚本次序：

[root@hadoop02 ~]# cd /data/yanqidw/script/member_active/
./dws_load_member_add_day.sh
./ads_load_member_add.sh