|NO.Z.00039|——————————|BigDataEnd|——|Hadoop&PB级数仓.V06|——|PB数仓.v06|广告分析|脚本调用顺序|

一、广告效果分析

### --- 需求分析

~~~     活动曝光效果评估：
~~~     行为(曝光、点击、购买)、时间段、广告位、商品，统计对应的次数
~~~     时间段、广告位、商品，曝光次数最多的前100个

### --- 实验说明

~~~     小结：分析简单，没有DWS层
~~~     Flume、json解析在会员分析讲解

二、创建ADS层表

### --- 创建ADS层表
~~~     # 语法：创建ADS层表

drop table if exists ads.ads_ad_show_place;

create table ads.ads_ad_show_place(
ad_action tinyint,
hour string,
place string,
product_id int,
cnt bigint
)PARTITIONED BY (`dt` string)
row format delimited fields terminated by ',';

~~~     # 语法：创建ADS层表

drop table if exists ads.ads_ad_show_place_window;

create table ads.ads_ad_show_place_window(
hour string,
place string,
product_id int,
cnt bigint,
rank int
)PARTITIONED BY (`dt` string)
row format delimited fields terminated by ',';

### --- 创建ADS层表：操作实例
~~~     # 操作实例：创建ADS层表

hive (default)> drop table if exists ads.ads_ad_show_place;

hive (default)> 
              > create table ads.ads_ad_show_place(
              > ad_action tinyint,
              > hour string,
              > place string,
              > product_id int,
              > cnt bigint
              > )PARTITIONED BY (`dt` string)
              > row format delimited fields terminated by ',';

~~~     # 操作实例：创建ADS层表

hive (default)> drop table if exists ads.ads_ad_show_place_window;
OK
Time taken: 0.035 seconds
hive (default)> 
              > create table ads.ads_ad_show_place_window(
              > hour string,
              > place string,
              > product_id int,
              > cnt bigint,
              > rank int
              > )PARTITIONED BY (`dt` string)
              > row format delimited fields terminated by ',';

三、加载ADS层数据

### --- 创建ADS层数据脚本文件一

[root@hadoop02 ~]# vim /data/yanqidw/script/advertisement/ads_load_ad_show_page.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_ad_show_place
partition (dt='$do_date')
select ad_action,
        hour,
        place,
        product_id,
count(1)
from dwd.dwd_ad
where dt='$do_date'
group by ad_action, hour, place, product_id;
"

hive -e "$sql"

### --- 创建ADS层数据脚本文件二

[root@hadoop02 ~]# vim /data/yanqidw/script/advertisement/ads_load_ad_show_page_window.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_ad_show_place_window
partition (dt='$do_date')
select *
from (
        select hour,
        place,
        product_id,
        cnt,
        row_number() over (partition by hour, place,
product_id order by cnt desc) rank
from ads.ads_ad_show_place
where dt='$do_date' and ad_action='0'
) t where rank <= 100
"

hive -e "$sql"

四、加载ADS层数据

### --- 执行

[root@hadoop02 ~]# sh /data/yanqidw/script/advertisement/ads_load_ad_show_page.sh 2020-07-21
hive (default)> show partitions ads.ads_ad_show_place;
partition
dt=2020-07-21

hive (default)> select * from ads.ads_ad_show_place where dt='2020-07-21' limit 3;
ads_ad_show_place.ad_action ads_ad_show_place.hour  ads_ad_show_place.place ads_ad_show_place.product_id    ads_ad_show_place.cnt   ads_ad_show_place.dt
0   00  placecampaign1_index    13  1   2020-07-21
0   00  placecampaign1_index    23  1   2020-07-21
0   00  placecampaign1_left 12  1   2020-07-21

hive (default)> select count(*) from ads.ads_ad_show_place where dt='2020-07-21';
1414

### --- 执行

[root@hadoop02 ~]# sh /data/yanqidw/script/advertisement/ads_load_ad_show_page_window.sh 2020-07-21
hive (default)> show partitions ads.ads_ad_show_place_window;
partition
dt=2020-07-21

hive (default)> select * from ads.ads_ad_show_place_window where dt='2020-07-21' limit 3;
OK
ads_ad_show_place_window.hour   ads_ad_show_place_window.place  ads_ad_show_place_window.product_id ads_ad_show_place_window.cnt    ads_ad_show_place_window.rank   ads_ad_show_place_window.dt
00  placecampaign1_index    13  1   1   2020-07-21
00  placecampaign1_index    23  1   1   2020-07-21
00  placecampaign1_left 12  1   1   2020-07-21

hive (default)> select count(*) from ads.ads_ad_show_place_window where dt='2020-07-21';
1001

---

五、广告分析小结

### --- 脚本调用次序:**
    
[root@hadoop02 ~]# cd  /data/yanqidw/script/advertisement/
./ods_load_event_log.sh
./dwd_load_event_log.sh
./dwd_load_ad_log.sh
./ads_load_ad_show.sh
./ads_load_ad_show_rate.sh
./ads_load_ad_show_page.sh
./ads_load_ad_show_page_window.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

---