一、新增会员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
三、小结
![](https://i-blog.csdnimg.cn/blog_migrate/e95368f70b743d27f6dc37e699e6beb2.png)
### --- 调用脚本次序:
[root@hadoop02 ~]# cd /data/yanqidw/script/member_active/
./dws_load_member_add_day.sh
./ads_load_member_add.sh