数仓ads层表脚本

#!/bin/bash
source /etc/profile

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

# 创建人:  xiaotao
# 创建日期: Created on  2022-04-24
# 数据开发功能描述: 兑换码兑换商品打卡数据（习惯熊打卡数据）
ads_ks_redeem_code_df="
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
set mapred.job.queue.name=hive2;
set hive.exec.max.dynamic.partitions=2000;
set hive.execution.engine=spark;
SET hive.merge.mapredfiles=true;
SET hive.merge.mapfiles=true;
set hive.merge.size.per.task=1000000;
SET hive.merge.smallfiles.avgsize=1000000;
with duihuan as (select user_id
                      ,a.product_id
                      ,c.product_name
                      ,batch_number -- 批次号
                      ,substr(a.exchange_time,1,10) as exchange_time --兑换时间
                      ,datediff(current_date(),to_date(a.exchange_time)) as diffday --兑换了第几天
                 from new_mid.mid_rights_exchange_di a -- 兑换码明细表
                          join new_dim.dim_produts_product_df c on a.product_id=c.product_id and c.dt = '$do_date'
                 WHERE to_date(a.exchange_time) >= '2021-10-01'
                   and a.product_id in ('4115','4104','4094','1666','4148','4213','4248','4249','4259','4260','4286','4283')
                  )

--故事--商品表
   ,story_prod as (select product_id
                        ,story_id
                   from new_dim.dim_story_story_album_product_df
                   where product_id in ('4115','4104','4094','1666','4148','4213','4248','4249','4259','4260','4286','4283')
                     and dt = '$do_date'
                   group by product_id,story_id
                   )

   --伴读主表
   ,banduzhu as (select  bandu_id,
                         story_id
                 from new_dim.dim_story_ks_bandu_info_df where dt = '$do_date'
                 )

--伴读记录信息
   ,bandu_records as (select distinct substr(createtime,1,10) as createtime,
                                      bandu_id,
                                      user_id
                      from (select distinct from_unixtime(unix_timestamp(create_time)+28800,'yyyy-MM-dd') as createtime,
                                           bandu_id,
                                           user_id
                           from new_dim.dim_story_ks_bandu_records_df
                           where create_time >='2021-10-23 00:00:00' and create_time<= '2021-11-11 10:55:00' and dt = '$do_date'
                           union all
                           select distinct create_time as createtime,
                                           bandu_id,
                                           user_id
                           from new_dim.dim_story_ks_bandu_records_df
                           where create_time> '2021-11-11 10:55:00' and dt = '$do_date'
                          ) t
                        )

insert overwrite table new_ads.ads_ks_redeem_code_df
select duihuan.user_id                           as user_id --用户ID
     ,duihuan.exchange_time                    as exchange_time  --兑换时间
     ,duihuan.batch_number                     as batch_number  --兑换码批次号
     ,min(bandu_records.createtime)            as fst_my_production_time  --第一次我的作品时间
     ,count(distinct bandu_records.createtime) as punch_card_days  --打卡几天
     ,count(distinct banduzhu.story_id)        as story_cts  --不同诗词有多少首
     ,duihuan.product_id                       as product_id  --商品ID
     ,duihuan.product_name                     as product_name  --商品名称
     ,duihuan.diffday                          as diffday --兑换了第几天
from duihuan
         join story_prod on duihuan.product_id =story_prod.product_id
         join banduzhu on story_prod.story_id =banduzhu.story_id
         join bandu_records on banduzhu.bandu_id =bandu_records.bandu_id and duihuan.user_id =bandu_records.user_id and duihuan.exchange_time <=bandu_records.createtime
group by duihuan.user_id, duihuan.exchange_time, duihuan.batch_number, duihuan.product_id, duihuan.product_name, duihuan.diffday
;
"
# drop table new_ads.ads_ks_redeem_code_df;
# CREATE TABLE new_ads.ads_ks_redeem_code_df(
# user_id      bigint COMMENT '用户ID',
# exchange_time   string COMMENT '兑换时间',
# batch_number   string COMMENT '兑换码批次号',
# fst_my_production_time   string COMMENT '第一次我的作品时间',
# punch_card_days   bigint COMMENT '打卡几天',
# story_cts   bigint COMMENT '不同诗词有多少首',
# product_id   bigint COMMENT '商品ID',
# product_name   string COMMENT '商品名称',
# diffday   bigint COMMENT '兑换了第几天'
# )
#     COMMENT '兑换码兑换商品打卡数据（习惯熊打卡数据）'
#     stored as parquet
#     LOCATION 'hdfs://emr-cluster/user/hive/warehouse/new_ads/ads_ks_redeem_code_df';

case $1 in

"ads_ks_redeem_code_df"){
    hive -e "$ads_ks_redeem_code_df"
};;
"all"){
    hive -e "$ads_ks_redeem_code_df"
};;
esac