阜宁网站制作服务商,seo实战密码读后感,小程序的功能,网站音乐播放器源码离线数仓-9-数据仓库开发DWS层设计要点-DWS层汇总表以及数据装载离线数仓-9-数据仓库开发DWS层设计要点-DWS层汇总表以及数据装载一、交易域用户商品粒度订单最近1日/N日汇总表1.交易域用户商品粒度订单最近1日汇总表2.交易域用户商品粒度订单最近N日汇总表二、交易域优惠券粒度…
离线数仓-9-数据仓库开发DWS层设计要点-DWS层汇总表以及数据装载离线数仓-9-数据仓库开发DWS层设计要点-DWS层汇总表以及数据装载一、交易域用户商品粒度订单最近1日/N日汇总表1.交易域用户商品粒度订单最近1日汇总表2.交易域用户商品粒度订单最近N日汇总表二、交易域优惠券粒度订单最近N日汇总表1.最近30天发布的优惠券的补贴率三、用户域用户粒度登录历史至今汇总表四、DWS层数据装载脚本1.dws层所有建表语句脚本2.1d表格装载脚本1.首日装载脚本2.每日装载脚本3.nd表格装载脚本4.td表格装载脚本1.首日装载脚本2.每日装载脚本离线数仓-9-数据仓库开发DWS层设计要点-DWS层汇总表以及数据装载
一、交易域用户商品粒度订单最近1日/N日汇总表
1.交易域用户商品粒度订单最近1日汇总表
建表DDL语句
DROP TABLE IF EXISTS dws_trade_user_sku_order_1d;
CREATE EXTERNAL TABLE dws_trade_user_sku_order_1d
(user_id STRING COMMENT 用户id,sku_id STRING COMMENT sku_id,sku_name STRING COMMENT sku名称,category1_id STRING COMMENT 一级分类id,category1_name STRING COMMENT 一级分类名称,category2_id STRING COMMENT 一级分类id,category2_name STRING COMMENT 一级分类名称,category3_id STRING COMMENT 一级分类id,category3_name STRING COMMENT 一级分类名称,tm_id STRING COMMENT 品牌id,tm_name STRING COMMENT 品牌名称,order_count_1d BIGINT COMMENT 最近1日下单次数,order_num_1d BIGINT COMMENT 最近1日下单件数,order_original_amount_1d DECIMAL(16, 2) COMMENT 最近1日下单原始金额,activity_reduce_amount_1d DECIMAL(16, 2) COMMENT 最近1日活动优惠金额,coupon_reduce_amount_1d DECIMAL(16, 2) COMMENT 最近1日优惠券优惠金额,order_total_amount_1d DECIMAL(16, 2) COMMENT 最近1日下单最终金额
) COMMENT 交易域用户商品粒度订单最近1日汇总事实表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/gmall/dws/dws_trade_user_sku_order_1dTBLPROPERTIES (orc.compress snappy);首日装载数据
set hive.exec.dynamic.partition.modenonstrict;
insert overwrite table dws_trade_user_sku_order_1d partition(dt)
selectuser_id,id,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_name,order_count_1d,order_num_1d,order_original_amount_1d,activity_reduce_amount_1d,coupon_reduce_amount_1d,order_total_amount_1d,dt
from
(selectdt,user_id,sku_id,count(*) order_count_1d,sum(sku_num) order_num_1d,sum(split_original_amount) order_original_amount_1d,sum(nvl(split_activity_amount,0.0)) activity_reduce_amount_1d,sum(nvl(split_coupon_amount,0.0)) coupon_reduce_amount_1d,sum(split_total_amount) order_total_amount_1dfrom dwd_trade_order_detail_incgroup by dt,user_id,sku_id
)od
left join
(selectid,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_namefrom dim_sku_fullwhere dt2020-06-14
)sku
on od.sku_idsku.id;每日装载
insert overwrite table dws_trade_user_sku_order_1d partition(dt2020-06-15)
selectuser_id,id,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_name,order_count,order_num,order_original_amount,activity_reduce_amount,coupon_reduce_amount,order_total_amount
from
(selectuser_id,sku_id,count(*) order_count,sum(sku_num) order_num,sum(split_original_amount) order_original_amount,sum(nvl(split_activity_amount,0)) activity_reduce_amount,sum(nvl(split_coupon_amount,0)) coupon_reduce_amount,sum(split_total_amount) order_total_amountfrom dwd_trade_order_detail_incwhere dt2020-06-15group by user_id,sku_id
)od
left join
(selectid,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_namefrom dim_sku_fullwhere dt2020-06-15
)sku
on od.sku_idsku.id;2.交易域用户商品粒度订单最近N日汇总表
建表DDL语句
DROP TABLE IF EXISTS dws_trade_user_sku_order_nd;
CREATE EXTERNAL TABLE dws_trade_user_sku_order_nd
(user_id STRING COMMENT 用户id,sku_id STRING COMMENT sku_id,sku_name STRING COMMENT sku名称,category1_id STRING COMMENT 一级分类id,category1_name STRING COMMENT 一级分类名称,category2_id STRING COMMENT 一级分类id,category2_name STRING COMMENT 一级分类名称,category3_id STRING COMMENT 一级分类id,category3_name STRING COMMENT 一级分类名称,tm_id STRING COMMENT 品牌id,tm_name STRING COMMENT 品牌名称,order_count_7d STRING COMMENT 最近7日下单次数,order_num_7d BIGINT COMMENT 最近7日下单件数,order_original_amount_7d DECIMAL(16, 2) COMMENT 最近7日下单原始金额,activity_reduce_amount_7d DECIMAL(16, 2) COMMENT 最近7日活动优惠金额,coupon_reduce_amount_7d DECIMAL(16, 2) COMMENT 最近7日优惠券优惠金额,order_total_amount_7d DECIMAL(16, 2) COMMENT 最近7日下单最终金额,order_count_30d BIGINT COMMENT 最近30日下单次数,order_num_30d BIGINT COMMENT 最近30日下单件数,order_original_amount_30d DECIMAL(16, 2) COMMENT 最近30日下单原始金额,activity_reduce_amount_30d DECIMAL(16, 2) COMMENT 最近30日活动优惠金额,coupon_reduce_amount_30d DECIMAL(16, 2) COMMENT 最近30日优惠券优惠金额,order_total_amount_30d DECIMAL(16, 2) COMMENT 最近30日下单最终金额
) COMMENT 交易域用户商品粒度订单最近n日汇总事实表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/gmall/dws/dws_trade_user_sku_order_ndTBLPROPERTIES (orc.compress snappy);每日装载数据 从业务需求方面考虑没有业务需求需要获取截止到历史某天的最近N天的相关信息。所以不需要进首日全量装载
insert overwrite table dws_trade_user_sku_order_nd partition(dt2020-06-14)
selectuser_id,sku_id,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_name,sum(if(dtdate_add(2020-06-14,-6),order_count_1d,0)),sum(if(dtdate_add(2020-06-14,-6),order_num_1d,0)),sum(if(dtdate_add(2020-06-14,-6),order_original_amount_1d,0)),sum(if(dtdate_add(2020-06-14,-6),activity_reduce_amount_1d,0)),sum(if(dtdate_add(2020-06-14,-6),coupon_reduce_amount_1d,0)),sum(if(dtdate_add(2020-06-14,-6),order_total_amount_1d,0)),sum(order_count_1d),sum(order_num_1d),sum(order_original_amount_1d),sum(activity_reduce_amount_1d),sum(coupon_reduce_amount_1d),sum(order_total_amount_1d)
from dws_trade_user_sku_order_1d
where dtdate_add(2020-06-14,-29)
group by user_id,sku_id,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_name;二、交易域优惠券粒度订单最近N日汇总表
此汇总表只有最近N日的汇总表是由于业务需求层面这样设计的只需要最近N日的优惠券信息所以此表格数据数据来源于DWD层明细数据而没有1d维度的数据。
1.最近30天发布的优惠券的补贴率
补贴率最近30天发布的优惠券相对的订单明细优惠券减免金额之和/最近30天发布的优惠券相对的订单明细的原价金额之和 补贴率是一个衍生指标基于此指标进行拆分指标形成思维导图 使用了最近30天发布的优惠券购买商品下单此订单必然位于最近30天内。 基于思维导图的业务矩阵上面的两个派生指标 指标的业务过程、统计周期、统计粒度一致可以将其划分到一个汇总表中汇总表数据落地以后优惠券补贴率指标直接使用此表中数据进行比对就可以了。 建表语句DDL
DROP TABLE IF EXISTS dws_trade_coupon_order_nd;
CREATE EXTERNAL TABLE dws_trade_coupon_order_nd
(coupon_id STRING COMMENT 优惠券id,coupon_name STRING COMMENT 优惠券名称,coupon_type_code STRING COMMENT 优惠券类型id,coupon_type_name STRING COMMENT 优惠券类型名称,coupon_rule STRING COMMENT 优惠券规则,start_date STRING COMMENT 发布日期,original_amount_30d DECIMAL(16, 2) COMMENT 使用下单原始金额,coupon_reduce_amount_30d DECIMAL(16, 2) COMMENT 使用下单优惠金额
) COMMENT 交易域优惠券粒度订单最近n日汇总事实表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/gmall/dws/dws_trade_coupon_order_ndTBLPROPERTIES (orc.compress snappy);数据装载每日增量装载nd不需要使用全量装载
insert overwrite table dws_trade_coupon_order_nd partition(dt2020-06-14)
selectid,coupon_name,coupon_type_code,coupon_type_name,benefit_rule,start_date,sum(split_original_amount),sum(split_coupon_amount)
from
(selectid,coupon_name,coupon_type_code,coupon_type_name,benefit_rule,date_format(start_time,yyyy-MM-dd) start_datefrom dim_coupon_fullwhere dt2020-06-14and date_format(start_time,yyyy-MM-dd)date_add(2020-06-14,-29)
)cou
left join
(selectcoupon_id,order_id,split_original_amount,split_coupon_amountfrom dwd_trade_order_detail_incwhere dtdate_add(2020-06-14,-29)and dt2020-06-14and coupon_id is not null
)od
on cou.idod.coupon_id
group by id,coupon_name,coupon_type_code,coupon_type_name,benefit_rule,start_date;三、用户域用户粒度登录历史至今汇总表
建表语句DDL
DROP TABLE IF EXISTS dws_user_user_login_td;
CREATE EXTERNAL TABLE dws_user_user_login_td
(user_id STRING COMMENT 用户id,login_date_last STRING COMMENT 末次登录日期,login_count_td BIGINT COMMENT 累计登录次数
) COMMENT 用户域用户粒度登录历史至今汇总事实表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/gmall/dws/dws_user_user_login_tdTBLPROPERTIES (orc.compress snappy);数据装载-首日 装载数据的时候默认使用1d表的数据此处没有1d表数据就使用dwd层dwd_user_login_inc表但是这个表数据来自于用户行为日志数据此数据没有历史数据依据此表格进行历史相关维度数据计算得到的结果不准确需要关联一下业务维度表格关联完毕以后依然拿不到相关维度数据就直接给其默认值数仓刚刚上线的时候数据不准确之前历史数据没有只能保证数仓上线以后的数据准确。
insert overwrite table dws_user_user_login_td partition(dt2020-06-14)
selectu.id,nvl(login_date_last,date_format(create_time,yyyy-MM-dd)),nvl(login_count_td,1)
from
(selectid,create_timefrom dim_user_zipwhere dt9999-12-31
)u
left join
(selectuser_id,max(dt) login_date_last,count(*) login_count_tdfrom dwd_user_login_incgroup by user_id
)l
on u.idl.user_id;数据装载-每日
insert overwrite table dws_user_user_login_td partition(dt2020-06-15)
selectnvl(old.user_id,new.user_id),if(new.user_id is null,old.login_date_last,2020-06-15),nvl(old.login_count_td,0)nvl(new.login_count_1d,0)
from
(selectuser_id,login_date_last,login_count_tdfrom dws_user_user_login_tdwhere dtdate_add(2020-06-15,-1)
)old
full outer join
(selectuser_id,count(*) login_count_1dfrom dwd_user_login_incwhere dt2020-06-15group by user_id
)new
on old.user_idnew.user_id;四、DWS层数据装载脚本
DWS层表格分为三部分1d表nd表td表。1d表被nd表和td表依赖而nd表和td表之间没有依赖关系需要先装载1d表1d表装载完毕后然后再装载td和nd表。
1.dws层所有建表语句脚本
DROP TABLE IF EXISTS dws_trade_user_sku_order_1d;
CREATE EXTERNAL TABLE dws_trade_user_sku_order_1d
(user_id STRING COMMENT 用户id,sku_id STRING COMMENT sku_id,sku_name STRING COMMENT sku名称,category1_id STRING COMMENT 一级分类id,category1_name STRING COMMENT 一级分类名称,category2_id STRING COMMENT 一级分类id,category2_name STRING COMMENT 一级分类名称,category3_id STRING COMMENT 一级分类id,category3_name STRING COMMENT 一级分类名称,tm_id STRING COMMENT 品牌id,tm_name STRING COMMENT 品牌名称,order_count_1d BIGINT COMMENT 最近1日下单次数,order_num_1d BIGINT COMMENT 最近1日下单件数,order_original_amount_1d DECIMAL(16, 2) COMMENT 最近1日下单原始金额,activity_reduce_amount_1d DECIMAL(16, 2) COMMENT 最近1日活动优惠金额,coupon_reduce_amount_1d DECIMAL(16, 2) COMMENT 最近1日优惠券优惠金额,order_total_amount_1d DECIMAL(16, 2) COMMENT 最近1日下单最终金额
) COMMENT 交易域用户商品粒度订单最近1日汇总事实表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/gmall/dws/dws_trade_user_sku_order_1dTBLPROPERTIES (orc.compress snappy);DROP TABLE IF EXISTS dws_trade_user_sku_order_refund_1d;
CREATE EXTERNAL TABLE dws_trade_user_sku_order_refund_1d
(user_id STRING COMMENT 用户id,sku_id STRING COMMENT sku_id,sku_name STRING COMMENT sku名称,category1_id STRING COMMENT 一级分类id,category1_name STRING COMMENT 一级分类名称,category2_id STRING COMMENT 一级分类id,category2_name STRING COMMENT 一级分类名称,category3_id STRING COMMENT 一级分类id,category3_name STRING COMMENT 一级分类名称,tm_id STRING COMMENT 品牌id,tm_name STRING COMMENT 品牌名称,order_refund_count_1d BIGINT COMMENT 最近1日退单次数,order_refund_num_1d BIGINT COMMENT 最近1日退单件数,order_refund_amount_1d DECIMAL(16, 2) COMMENT 最近1日退单金额
) COMMENT 交易域用户商品粒度退单最近1日汇总事实表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/gmall/dws/dws_trade_user_sku_order_refund_1dTBLPROPERTIES (orc.compress snappy);DROP TABLE IF EXISTS dws_trade_user_order_1d;
CREATE EXTERNAL TABLE dws_trade_user_order_1d
(user_id STRING COMMENT 用户id,order_count_1d BIGINT COMMENT 最近1日下单次数,order_num_1d BIGINT COMMENT 最近1日下单商品件数,order_original_amount_1d DECIMAL(16, 2) COMMENT 最近1日最近1日下单原始金额,activity_reduce_amount_1d DECIMAL(16, 2) COMMENT 最近1日下单活动优惠金额,coupon_reduce_amount_1d DECIMAL(16, 2) COMMENT 下单优惠券优惠金额,order_total_amount_1d DECIMAL(16, 2) COMMENT 最近1日下单最终金额
) COMMENT 交易域用户粒度订单最近1日汇总事实表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/gmall/dws/dws_trade_user_order_1dTBLPROPERTIES (orc.compress snappy);DROP TABLE IF EXISTS dws_trade_user_cart_add_1d;
CREATE EXTERNAL TABLE dws_trade_user_cart_add_1d
(user_id STRING COMMENT 用户id,cart_add_count_1d BIGINT COMMENT 最近1日加购次数,cart_add_num_1d BIGINT COMMENT 最近1日加购商品件数
) COMMENT 交易域用户粒度加购最近1日汇总事实表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/gmall/dws/dws_trade_user_cart_add_1dTBLPROPERTIES (orc.compress snappy);DROP TABLE IF EXISTS dws_trade_user_payment_1d;
CREATE EXTERNAL TABLE dws_trade_user_payment_1d
(user_id STRING COMMENT 用户id,payment_count_1d BIGINT COMMENT 最近1日支付次数,payment_num_1d BIGINT COMMENT 最近1日支付商品件数,payment_amount_1d DECIMAL(16, 2) COMMENT 最近1日支付金额
) COMMENT 交易域用户粒度支付最近1日汇总事实表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/gmall/dws/dws_trade_user_payment_1dTBLPROPERTIES (orc.compress snappy);DROP TABLE IF EXISTS dws_trade_province_order_1d;
CREATE EXTERNAL TABLE dws_trade_province_order_1d
(province_id STRING COMMENT 用户id,province_name STRING COMMENT 省份名称,area_code STRING COMMENT 地区编码,iso_code STRING COMMENT 旧版ISO-3166-2编码,iso_3166_2 STRING COMMENT 新版版ISO-3166-2编码,order_count_1d BIGINT COMMENT 最近1日下单次数,order_original_amount_1d DECIMAL(16, 2) COMMENT 最近1日下单原始金额,activity_reduce_amount_1d DECIMAL(16, 2) COMMENT 最近1日下单活动优惠金额,coupon_reduce_amount_1d DECIMAL(16, 2) COMMENT 最近1日下单优惠券优惠金额,order_total_amount_1d DECIMAL(16, 2) COMMENT 最近1日下单最终金额
) COMMENT 交易域省份粒度订单最近1日汇总事实表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/gmall/dws/dws_trade_province_order_1dTBLPROPERTIES (orc.compress snappy);DROP TABLE IF EXISTS dws_trade_user_order_refund_1d;
CREATE EXTERNAL TABLE dws_trade_user_order_refund_1d
(user_id STRING COMMENT 用户id,order_refund_count_1d BIGINT COMMENT 最近1日退单次数,order_refund_num_1d BIGINT COMMENT 最近1日退单商品件数,order_refund_amount_1d DECIMAL(16, 2) COMMENT 最近1日退单金额
) COMMENT 交易域用户粒度退单最近1日汇总事实表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/gmall/dws/dws_trade_user_order_refund_1dTBLPROPERTIES (orc.compress snappy);DROP TABLE IF EXISTS dws_traffic_session_page_view_1d;
CREATE EXTERNAL TABLE dws_traffic_session_page_view_1d
(session_id STRING COMMENT 会话id,mid_id string comment 设备id,brand string comment 手机品牌,model string comment 手机型号,operate_system string comment 操作系统,version_code string comment app版本号,channel string comment 渠道,during_time_1d BIGINT COMMENT 最近1日访问时长,page_count_1d BIGINT COMMENT 最近1日访问页面数
) COMMENT 流量域会话粒度页面浏览最近1日汇总表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/gmall/dws/dws_traffic_session_page_view_1dTBLPROPERTIES (orc.compress snappy);DROP TABLE IF EXISTS dws_traffic_page_visitor_page_view_1d;
CREATE EXTERNAL TABLE dws_traffic_page_visitor_page_view_1d
(mid_id STRING COMMENT 访客id,brand string comment 手机品牌,model string comment 手机型号,operate_system string comment 操作系统,page_id STRING COMMENT 页面id,during_time_1d BIGINT COMMENT 最近1日浏览时长,view_count_1d BIGINT COMMENT 最近1日访问次数
) COMMENT 流量域访客页面粒度页面浏览最近1日汇总事实表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/gmall/dws/dws_traffic_page_visitor_page_view_1dTBLPROPERTIES (orc.compress snappy); DROP TABLE IF EXISTS dws_trade_user_sku_order_nd;
CREATE EXTERNAL TABLE dws_trade_user_sku_order_nd
(user_id STRING COMMENT 用户id,sku_id STRING COMMENT sku_id,sku_name STRING COMMENT sku名称,category1_id STRING COMMENT 一级分类id,category1_name STRING COMMENT 一级分类名称,category2_id STRING COMMENT 一级分类id,category2_name STRING COMMENT 一级分类名称,category3_id STRING COMMENT 一级分类id,category3_name STRING COMMENT 一级分类名称,tm_id STRING COMMENT 品牌id,tm_name STRING COMMENT 品牌名称,order_count_7d STRING COMMENT 最近7日下单次数,order_num_7d BIGINT COMMENT 最近7日下单件数,order_original_amount_7d DECIMAL(16, 2) COMMENT 最近7日下单原始金额,activity_reduce_amount_7d DECIMAL(16, 2) COMMENT 最近7日活动优惠金额,coupon_reduce_amount_7d DECIMAL(16, 2) COMMENT 最近7日优惠券优惠金额,order_total_amount_7d DECIMAL(16, 2) COMMENT 最近7日下单最终金额,order_count_30d BIGINT COMMENT 最近30日下单次数,order_num_30d BIGINT COMMENT 最近30日下单件数,order_original_amount_30d DECIMAL(16, 2) COMMENT 最近30日下单原始金额,activity_reduce_amount_30d DECIMAL(16, 2) COMMENT 最近30日活动优惠金额,coupon_reduce_amount_30d DECIMAL(16, 2) COMMENT 最近30日优惠券优惠金额,order_total_amount_30d DECIMAL(16, 2) COMMENT 最近30日下单最终金额
) COMMENT 交易域用户商品粒度订单最近n日汇总事实表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/gmall/dws/dws_trade_user_sku_order_ndTBLPROPERTIES (orc.compress snappy);DROP TABLE IF EXISTS dws_trade_user_sku_order_refund_nd;
CREATE EXTERNAL TABLE dws_trade_user_sku_order_refund_nd
(user_id STRING COMMENT 用户id,sku_id STRING COMMENT sku_id,sku_name STRING COMMENT sku名称,category1_id STRING COMMENT 一级分类id,category1_name STRING COMMENT 一级分类名称,category2_id STRING COMMENT 一级分类id,category2_name STRING COMMENT 一级分类名称,category3_id STRING COMMENT 一级分类id,category3_name STRING COMMENT 一级分类名称,tm_id STRING COMMENT 品牌id,tm_name STRING COMMENT 品牌名称,order_refund_count_7d BIGINT COMMENT 最近7日退单次数,order_refund_num_7d BIGINT COMMENT 最近7日退单件数,order_refund_amount_7d DECIMAL(16, 2) COMMENT 最近7日退单金额,order_refund_count_30d BIGINT COMMENT 最近30日退单次数,order_refund_num_30d BIGINT COMMENT 最近30日退单件数,order_refund_amount_30d DECIMAL(16, 2) COMMENT 最近30日退单金额
) COMMENT 交易域用户商品粒度退单最近n日汇总事实表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/gmall/dws/dws_trade_user_sku_order_refund_ndTBLPROPERTIES (orc.compress snappy);DROP TABLE IF EXISTS dws_trade_user_order_nd;
CREATE EXTERNAL TABLE dws_trade_user_order_nd
(user_id STRING COMMENT 用户id,order_count_7d BIGINT COMMENT 最近7日下单次数,order_num_7d BIGINT COMMENT 最近7日下单商品件数,order_original_amount_7d DECIMAL(16, 2) COMMENT 最近7日下单原始金额,activity_reduce_amount_7d DECIMAL(16, 2) COMMENT 最近7日下单活动优惠金额,coupon_reduce_amount_7d DECIMAL(16, 2) COMMENT 最近7日下单优惠券优惠金额,order_total_amount_7d DECIMAL(16, 2) COMMENT 最近7日下单最终金额,order_count_30d BIGINT COMMENT 最近30日下单次数,order_num_30d BIGINT COMMENT 最近30日下单商品件数,order_original_amount_30d DECIMAL(16, 2) COMMENT 最近30日下单原始金额,activity_reduce_amount_30d DECIMAL(16, 2) COMMENT 最近30日下单活动优惠金额,coupon_reduce_amount_30d DECIMAL(16, 2) COMMENT 最近30日下单优惠券优惠金额,order_total_amount_30d DECIMAL(16, 2) COMMENT 最近30日下单最终金额
) COMMENT 交易域用户粒度订单最近n日汇总事实表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/gmall/dws/dws_trade_user_order_ndTBLPROPERTIES (orc.compress snappy);DROP TABLE IF EXISTS dws_trade_user_cart_add_nd;
CREATE EXTERNAL TABLE dws_trade_user_cart_add_nd
(user_id STRING COMMENT 用户id,cart_add_count_7d BIGINT COMMENT 最近7日加购次数,cart_add_num_7d BIGINT COMMENT 最近7日加购商品件数,cart_add_count_30d BIGINT COMMENT 最近30日加购次数,cart_add_num_30d BIGINT COMMENT 最近30日加购商品件数
) COMMENT 交易域用户粒度加购最近n日汇总事实表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/gmall/dws/dws_trade_user_cart_add_ndTBLPROPERTIES (orc.compress snappy);DROP TABLE IF EXISTS dws_trade_user_payment_nd;
CREATE EXTERNAL TABLE dws_trade_user_payment_nd
(user_id STRING COMMENT 用户id,payment_count_7d BIGINT COMMENT 最近7日支付次数,payment_num_7d BIGINT COMMENT 最近7日支付商品件数,payment_amount_7d DECIMAL(16, 2) COMMENT 最近7日支付金额,payment_count_30d BIGINT COMMENT 最近30日支付次数,payment_num_30d BIGINT COMMENT 最近30日支付商品件数,payment_amount_30d DECIMAL(16, 2) COMMENT 最近30日支付金额
) COMMENT 交易域用户粒度支付最近n日汇总事实表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/gmall/dws/dws_trade_user_payment_nd
TBLPROPERTIES (orc.compress snappy);DROP TABLE IF EXISTS dws_trade_province_order_nd;
CREATE EXTERNAL TABLE dws_trade_province_order_nd
(province_id STRING COMMENT 用户id,province_name STRING COMMENT 省份名称,area_code STRING COMMENT 地区编码,iso_code STRING COMMENT 旧版ISO-3166-2编码,iso_3166_2 STRING COMMENT 新版版ISO-3166-2编码,order_count_7d BIGINT COMMENT 最近7日下单次数,order_original_amount_7d DECIMAL(16, 2) COMMENT 最近7日下单原始金额,activity_reduce_amount_7d DECIMAL(16, 2) COMMENT 最近7日下单活动优惠金额,coupon_reduce_amount_7d DECIMAL(16, 2) COMMENT 最近7日下单优惠券优惠金额,order_total_amount_7d DECIMAL(16, 2) COMMENT 最近7日下单最终金额,order_count_30d BIGINT COMMENT 最近30日下单次数,order_original_amount_30d DECIMAL(16, 2) COMMENT 最近30日下单原始金额,activity_reduce_amount_30d DECIMAL(16, 2) COMMENT 最近30日下单活动优惠金额,coupon_reduce_amount_30d DECIMAL(16, 2) COMMENT 最近30日下单优惠券优惠金额,order_total_amount_30d DECIMAL(16, 2) COMMENT 最近30日下单最终金额
) COMMENT 交易域省份粒度订单最近n日汇总事实表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/gmall/dws/dws_trade_province_order_ndTBLPROPERTIES (orc.compress snappy);DROP TABLE IF EXISTS dws_trade_coupon_order_nd;
CREATE EXTERNAL TABLE dws_trade_coupon_order_nd
(coupon_id STRING COMMENT 优惠券id,coupon_name STRING COMMENT 优惠券名称,coupon_type_code STRING COMMENT 优惠券类型id,coupon_type_name STRING COMMENT 优惠券类型名称,coupon_rule STRING COMMENT 优惠券规则,start_date STRING COMMENT 发布日期,original_amount_30d DECIMAL(16, 2) COMMENT 使用下单原始金额,coupon_reduce_amount_30d DECIMAL(16, 2) COMMENT 使用下单优惠金额
) COMMENT 交易域优惠券粒度订单最近n日汇总事实表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/gmall/dws/dws_trade_coupon_order_ndTBLPROPERTIES (orc.compress snappy);DROP TABLE IF EXISTS dws_trade_activity_order_nd;
CREATE EXTERNAL TABLE dws_trade_activity_order_nd
(activity_id STRING COMMENT 活动id,activity_name STRING COMMENT 活动名称,activity_type_code STRING COMMENT 活动类型编码,activity_type_name STRING COMMENT 活动类型名称,start_date STRING COMMENT 发布日期,original_amount_30d DECIMAL(16, 2) COMMENT 参与活动订单原始金额,activity_reduce_amount_30d DECIMAL(16, 2) COMMENT 参与活动订单优惠金额
) COMMENT 交易域活动粒度订单最近n日汇总事实表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/gmall/dws/dws_trade_activity_order_ndTBLPROPERTIES (orc.compress snappy);DROP TABLE IF EXISTS dws_trade_user_order_refund_nd;
CREATE EXTERNAL TABLE dws_trade_user_order_refund_nd
(user_id STRING COMMENT 用户id,order_refund_count_7d BIGINT COMMENT 最近7日退单次数,order_refund_num_7d BIGINT COMMENT 最近7日退单商品件数,order_refund_amount_7d DECIMAL(16, 2) COMMENT 最近7日退单金额,order_refund_count_30d BIGINT COMMENT 最近30日退单次数,order_refund_num_30d BIGINT COMMENT 最近30日退单商品件数,order_refund_amount_30d DECIMAL(16, 2) COMMENT 最近30日退单金额
) COMMENT 交易域用户粒度退单最近n日汇总事实表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/gmall/dws/dws_trade_user_order_refund_ndTBLPROPERTIES (orc.compress snappy);DROP TABLE IF EXISTS dws_traffic_page_visitor_page_view_nd;
CREATE EXTERNAL TABLE dws_traffic_page_visitor_page_view_nd
(mid_id STRING COMMENT 访客id,brand string comment 手机品牌,model string comment 手机型号,operate_system string comment 操作系统,page_id STRING COMMENT 页面id,during_time_7d BIGINT COMMENT 最近7日浏览时长,view_count_7d BIGINT COMMENT 最近7日访问次数,during_time_30d BIGINT COMMENT 最近30日浏览时长,view_count_30d BIGINT COMMENT 最近30日访问次数
) COMMENT 流量域访客页面粒度页面浏览最近n日汇总事实表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/gmall/dws/dws_traffic_page_visitor_page_view_ndTBLPROPERTIES (orc.compress snappy);DROP TABLE IF EXISTS dws_trade_user_order_td;
CREATE EXTERNAL TABLE dws_trade_user_order_td
(user_id STRING COMMENT 用户id,order_date_first STRING COMMENT 首次下单日期,order_date_last STRING COMMENT 末次下单日期,order_count_td BIGINT COMMENT 下单次数,order_num_td BIGINT COMMENT 购买商品件数,original_amount_td DECIMAL(16, 2) COMMENT 原始金额,activity_reduce_amount_td DECIMAL(16, 2) COMMENT 活动优惠金额,coupon_reduce_amount_td DECIMAL(16, 2) COMMENT 优惠券优惠金额,total_amount_td DECIMAL(16, 2) COMMENT 最终金额
) COMMENT 交易域用户粒度订单历史至今汇总事实表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/gmall/dws/dws_trade_user_order_tdTBLPROPERTIES (orc.compress snappy);DROP TABLE IF EXISTS dws_trade_user_payment_td;
CREATE EXTERNAL TABLE dws_trade_user_payment_td
(user_id STRING COMMENT 用户id,payment_date_first STRING COMMENT 首次支付日期,payment_date_last STRING COMMENT 末次支付日期,payment_count_td BIGINT COMMENT 最近7日支付次数,payment_num_td BIGINT COMMENT 最近7日支付商品件数,payment_amount_td DECIMAL(16, 2) COMMENT 最近7日支付金额
) COMMENT 交易域用户粒度支付历史至今汇总事实表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/gmall/dws/dws_trade_user_payment_tdTBLPROPERTIES (orc.compress snappy);DROP TABLE IF EXISTS dws_user_user_login_td;
CREATE EXTERNAL TABLE dws_user_user_login_td
(user_id STRING COMMENT 用户id,login_date_last STRING COMMENT 末次登录日期,login_count_td BIGINT COMMENT 累计登录次数
) COMMENT 用户域用户粒度登录历史至今汇总事实表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/gmall/dws/dws_user_user_login_tdTBLPROPERTIES (orc.compress snappy);2.1d表格装载脚本
1.首日装载脚本
#!/bin/bash
APPgmallif [ -n $2 ] ;thendo_date$2
else echo 请传入日期参数exit
fidws_trade_province_order_1d
insert overwrite table ${APP}.dws_trade_province_order_1d partition(dt)
selectprovince_id,province_name,area_code,iso_code,iso_3166_2,order_count_1d,order_original_amount_1d,activity_reduce_amount_1d,coupon_reduce_amount_1d,order_total_amount_1d,dt
from
(selectprovince_id,count(distinct(order_id)) order_count_1d,sum(split_original_amount) order_original_amount_1d,sum(nvl(split_activity_amount,0)) activity_reduce_amount_1d,sum(nvl(split_coupon_amount,0)) coupon_reduce_amount_1d,sum(split_total_amount) order_total_amount_1d,dtfrom ${APP}.dwd_trade_order_detail_incgroup by province_id,dt
)o
left join
(selectid,province_name,area_code,iso_code,iso_3166_2from ${APP}.dim_province_fullwhere dt$do_date
)p
on o.province_idp.id;dws_trade_user_cart_add_1d
insert overwrite table ${APP}.dws_trade_user_cart_add_1d partition(dt)
selectuser_id,count(*),sum(sku_num),dt
from ${APP}.dwd_trade_cart_add_inc
group by user_id,dt;dws_trade_user_order_1d
insert overwrite table ${APP}.dws_trade_user_order_1d partition(dt)
selectuser_id,count(distinct(order_id)),sum(sku_num),sum(split_original_amount),sum(nvl(split_activity_amount,0)),sum(nvl(split_coupon_amount,0)),sum(split_total_amount),dt
from ${APP}.dwd_trade_order_detail_inc
group by user_id,dt;dws_trade_user_order_refund_1d
insert overwrite table ${APP}.dws_trade_user_order_refund_1d partition(dt)
selectuser_id,count(*) order_refund_count,sum(refund_num) order_refund_num,sum(refund_amount) order_refund_amount,dt
from ${APP}.dwd_trade_order_refund_inc
group by user_id,dt;dws_trade_user_payment_1d
insert overwrite table ${APP}.dws_trade_user_payment_1d partition(dt)
selectuser_id,count(distinct(order_id)),sum(sku_num),sum(split_payment_amount),dt
from ${APP}.dwd_trade_pay_detail_suc_inc
group by user_id,dt;dws_trade_user_sku_order_1d
insert overwrite table ${APP}.dws_trade_user_sku_order_1d partition(dt)
selectuser_id,id,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_name,order_count_1d,order_num_1d,order_original_amount_1d,activity_reduce_amount_1d,coupon_reduce_amount_1d,order_total_amount_1d,dt
from
(selectdt,user_id,sku_id,count(*) order_count_1d,sum(sku_num) order_num_1d,sum(split_original_amount) order_original_amount_1d,sum(nvl(split_activity_amount,0.0)) activity_reduce_amount_1d,sum(nvl(split_coupon_amount,0.0)) coupon_reduce_amount_1d,sum(split_total_amount) order_total_amount_1dfrom ${APP}.dwd_trade_order_detail_incgroup by dt,user_id,sku_id
)od
left join
(selectid,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_namefrom ${APP}.dim_sku_fullwhere dt$do_date
)sku
on od.sku_idsku.id;dws_trade_user_sku_order_refund_1d
insert overwrite table ${APP}.dws_trade_user_sku_order_refund_1d partition(dt)
selectuser_id,sku_id,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_name,order_refund_count,order_refund_num,order_refund_amount,dt
from
(selectdt,user_id,sku_id,count(*) order_refund_count,sum(refund_num) order_refund_num,sum(refund_amount) order_refund_amountfrom ${APP}.dwd_trade_order_refund_incgroup by dt,user_id,sku_id
)od
left join
(selectid,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_namefrom ${APP}.dim_sku_fullwhere dt$do_date
)sku
on od.sku_idsku.id;dws_traffic_page_visitor_page_view_1d
insert overwrite table ${APP}.dws_traffic_page_visitor_page_view_1d partition(dt$do_date)
selectmid_id,brand,model,operate_system,page_id,sum(during_time),count(*)
from ${APP}.dwd_traffic_page_view_inc
where dt$do_date
group by mid_id,brand,model,operate_system,page_id;dws_traffic_session_page_view_1d
insert overwrite table ${APP}.dws_traffic_session_page_view_1d partition(dt$do_date)
selectsession_id,mid_id,brand,model,operate_system,version_code,channel,sum(during_time),count(*)
from ${APP}.dwd_traffic_page_view_inc
where dt$do_date
group by session_id,mid_id,brand,model,operate_system,version_code,channel;
case $1 indws_trade_province_order_1d )hive -e $dws_trade_province_order_1d;;dws_trade_user_cart_add_1d )hive -e $dws_trade_user_cart_add_1d;;dws_trade_user_order_1d )hive -e $dws_trade_user_order_1d;;dws_trade_user_order_refund_1d )hive -e $dws_trade_user_order_refund_1d;;dws_trade_user_payment_1d )hive -e $dws_trade_user_payment_1d;;dws_trade_user_sku_order_1d )hive -e $dws_trade_user_sku_order_1d;;dws_trade_user_sku_order_refund_1d )hive -e $dws_trade_user_sku_order_refund_1d;;dws_traffic_page_visitor_page_view_1d )hive -e $dws_traffic_page_visitor_page_view_1d;;dws_traffic_session_page_view_1d )hive -e $dws_traffic_session_page_view_1d;;all )hive -e $dws_trade_province_order_1d$dws_trade_user_cart_add_1d$dws_trade_user_order_1d$dws_trade_user_order_refund_1d$dws_trade_user_payment_1d$dws_trade_user_sku_order_1d$dws_trade_user_sku_order_refund_1d$dws_traffic_page_visitor_page_view_1d$dws_traffic_session_page_view_1d;;
esac2.每日装载脚本
#!/bin/bash
APPgmall# 如果输入的日期按照取输入日期如果没输入日期取当前时间的前一天
if [ -n $2 ] ;thendo_date$2
else do_datedate -d -1 day %F
fidws_trade_province_order_1d
insert overwrite table ${APP}.dws_trade_province_order_1d partition(dt$do_date)
selectprovince_id,province_name,area_code,iso_code,iso_3166_2,order_count_1d,order_original_amount_1d,activity_reduce_amount_1d,coupon_reduce_amount_1d,order_total_amount_1d
from
(selectprovince_id,count(distinct(order_id)) order_count_1d,sum(split_original_amount) order_original_amount_1d,sum(nvl(split_activity_amount,0)) activity_reduce_amount_1d,sum(nvl(split_coupon_amount,0)) coupon_reduce_amount_1d,sum(split_total_amount) order_total_amount_1dfrom ${APP}.dwd_trade_order_detail_incwhere dt$do_dategroup by province_id
)o
left join
(selectid,province_name,area_code,iso_code,iso_3166_2from ${APP}.dim_province_fullwhere dt$do_date
)p
on o.province_idp.id;dws_trade_user_cart_add_1d
insert overwrite table ${APP}.dws_trade_user_cart_add_1d partition(dt$do_date)
selectuser_id,count(*),sum(sku_num)
from ${APP}.dwd_trade_cart_add_inc
where dt$do_date
group by user_id;dws_trade_user_order_1d
insert overwrite table ${APP}.dws_trade_user_order_1d partition(dt$do_date)
selectuser_id,count(distinct(order_id)),sum(sku_num),sum(split_original_amount),sum(nvl(split_activity_amount,0)),sum(nvl(split_coupon_amount,0)),sum(split_total_amount)
from ${APP}.dwd_trade_order_detail_inc
where dt$do_date
group by user_id;dws_trade_user_order_refund_1d
insert overwrite table ${APP}.dws_trade_user_order_refund_1d partition(dt$do_date)
selectuser_id,count(*),sum(refund_num),sum(refund_amount)
from ${APP}.dwd_trade_order_refund_inc
where dt$do_date
group by user_id;dws_trade_user_payment_1d
insert overwrite table ${APP}.dws_trade_user_payment_1d partition(dt$do_date)
selectuser_id,count(distinct(order_id)),sum(sku_num),sum(split_payment_amount)
from ${APP}.dwd_trade_pay_detail_suc_inc
where dt$do_date
group by user_id;dws_trade_user_sku_order_1d
insert overwrite table ${APP}.dws_trade_user_sku_order_1d partition(dt$do_date)
selectuser_id,id,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_name,order_count,order_num,order_original_amount,activity_reduce_amount,coupon_reduce_amount,order_total_amount
from
(selectuser_id,sku_id,count(*) order_count,sum(sku_num) order_num,sum(split_original_amount) order_original_amount,sum(nvl(split_activity_amount,0)) activity_reduce_amount,sum(nvl(split_coupon_amount,0)) coupon_reduce_amount,sum(split_total_amount) order_total_amountfrom ${APP}.dwd_trade_order_detail_incwhere dt$do_dategroup by user_id,sku_id
)od
left join
(selectid,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_namefrom ${APP}.dim_sku_fullwhere dt$do_date
)sku
on od.sku_idsku.id;dws_trade_user_sku_order_refund_1d
insert overwrite table ${APP}.dws_trade_user_sku_order_refund_1d partition(dt$do_date)
selectuser_id,sku_id,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_name,order_refund_count,order_refund_num,order_refund_amount
from
(selectuser_id,sku_id,count(*) order_refund_count,sum(refund_num) order_refund_num,sum(refund_amount) order_refund_amountfrom ${APP}.dwd_trade_order_refund_incwhere dt$do_dategroup by user_id,sku_id
)od
left join
(selectid,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_namefrom ${APP}.dim_sku_fullwhere dt$do_date
)sku
on od.sku_idsku.id;dws_traffic_page_visitor_page_view_1d
insert overwrite table ${APP}.dws_traffic_page_visitor_page_view_1d partition(dt$do_date)
selectmid_id,brand,model,operate_system,page_id,sum(during_time),count(*)
from ${APP}.dwd_traffic_page_view_inc
where dt$do_date
group by mid_id,brand,model,operate_system,page_id;dws_traffic_session_page_view_1d
insert overwrite table ${APP}.dws_traffic_session_page_view_1d partition(dt$do_date)
selectsession_id,mid_id,brand,model,operate_system,version_code,channel,sum(during_time),count(*)
from ${APP}.dwd_traffic_page_view_inc
where dt$do_date
group by session_id,mid_id,brand,model,operate_system,version_code,channel;
case $1 indws_trade_province_order_1d )hive -e $dws_trade_province_order_1d;;dws_trade_user_cart_add_1d )hive -e $dws_trade_user_cart_add_1d;;dws_trade_user_order_1d )hive -e $dws_trade_user_order_1d;;dws_trade_user_order_refund_1d )hive -e $dws_trade_user_order_refund_1d;;dws_trade_user_payment_1d )hive -e $dws_trade_user_payment_1d;;dws_trade_user_sku_order_1d )hive -e $dws_trade_user_sku_order_1d;;dws_trade_user_sku_order_refund_1d )hive -e $dws_trade_user_sku_order_refund_1d;;dws_traffic_page_visitor_page_view_1d )hive -e $dws_traffic_page_visitor_page_view_1d;;dws_traffic_session_page_view_1d )hive -e $dws_traffic_session_page_view_1d;;all )hive -e $dws_trade_province_order_1d$dws_trade_user_cart_add_1d$dws_trade_user_order_1d$dws_trade_user_order_refund_1d$dws_trade_user_payment_1d$dws_trade_user_sku_order_1d$dws_trade_user_sku_order_refund_1d$dws_traffic_page_visitor_page_view_1d$dws_traffic_session_page_view_1d;;
esac3.nd表格装载脚本
#!/bin/bash
APPgmall# 如果是输入的日期按照取输入日期如果没输入日期取当前时间的前一天
if [ -n $2 ] ;thendo_date$2
else do_datedate -d -1 day %F
fidws_trade_activity_order_nd
insert overwrite table ${APP}.dws_trade_activity_order_nd partition(dt$do_date)
selectact.activity_id,activity_name,activity_type_code,activity_type_name,date_format(start_time,yyyy-MM-dd),sum(split_original_amount),sum(split_activity_amount)
from
(selectactivity_id,activity_name,activity_type_code,activity_type_name,start_timefrom ${APP}.dim_activity_fullwhere dt$do_dateand date_format(start_time,yyyy-MM-dd)date_add($do_date,-29)group by activity_id, activity_name, activity_type_code, activity_type_name,start_time
)act
left join
(selectactivity_id,order_id,split_original_amount,split_activity_amountfrom ${APP}.dwd_trade_order_detail_incwhere dtdate_add($do_date,-29)and dt$do_dateand activity_id is not null
)od
on act.activity_idod.activity_id
group by act.activity_id,activity_name,activity_type_code,activity_type_name,start_time;dws_trade_coupon_order_nd
insert overwrite table ${APP}.dws_trade_coupon_order_nd partition(dt$do_date)
selectid,coupon_name,coupon_type_code,coupon_type_name,benefit_rule,start_date,sum(split_original_amount),sum(split_coupon_amount)
from
(selectid,coupon_name,coupon_type_code,coupon_type_name,benefit_rule,date_format(start_time,yyyy-MM-dd) start_datefrom ${APP}.dim_coupon_fullwhere dt$do_dateand date_format(start_time,yyyy-MM-dd)date_add($do_date,-29)
)cou
left join
(selectcoupon_id,order_id,split_original_amount,split_coupon_amountfrom ${APP}.dwd_trade_order_detail_incwhere dtdate_add($do_date,-29)and dt$do_dateand coupon_id is not null
)od
on cou.idod.coupon_id
group by id,coupon_name,coupon_type_code,coupon_type_name,benefit_rule,start_date;dws_trade_province_order_nd
insert overwrite table ${APP}.dws_trade_province_order_nd partition(dt$do_date)
selectprovince_id,province_name,area_code,iso_code,iso_3166_2,sum(if(dtdate_add($do_date,-6),order_count_1d,0)),sum(if(dtdate_add($do_date,-6),order_original_amount_1d,0)),sum(if(dtdate_add($do_date,-6),activity_reduce_amount_1d,0)),sum(if(dtdate_add($do_date,-6),coupon_reduce_amount_1d,0)),sum(if(dtdate_add($do_date,-6),order_total_amount_1d,0)),sum(order_count_1d),sum(order_original_amount_1d),sum(activity_reduce_amount_1d),sum(coupon_reduce_amount_1d),sum(order_total_amount_1d)
from ${APP}.dws_trade_province_order_1d
where dtdate_add($do_date,-29)
and dt$do_date
group by province_id,province_name,area_code,iso_code,iso_3166_2;dws_trade_user_cart_add_nd
insert overwrite table ${APP}.dws_trade_user_cart_add_nd partition(dt$do_date)
selectuser_id,sum(if(dtdate_add($do_date,-6),cart_add_count_1d,0)),sum(if(dtdate_add($do_date,-6),cart_add_num_1d,0)),sum(cart_add_count_1d),sum(cart_add_num_1d)
from ${APP}.dws_trade_user_cart_add_1d
where dtdate_add($do_date,-29)
and dt$do_date
group by user_id;dws_trade_user_order_nd
insert overwrite table ${APP}.dws_trade_user_order_nd partition(dt$do_date)
selectuser_id,sum(if(dtdate_add($do_date,-6),order_count_1d,0)),sum(if(dtdate_add($do_date,-6),order_num_1d,0)),sum(if(dtdate_add($do_date,-6),order_original_amount_1d,0)),sum(if(dtdate_add($do_date,-6),activity_reduce_amount_1d,0)),sum(if(dtdate_add($do_date,-6),coupon_reduce_amount_1d,0)),sum(if(dtdate_add($do_date,-6),order_total_amount_1d,0)),sum(order_count_1d),sum(order_num_1d),sum(order_original_amount_1d),sum(activity_reduce_amount_1d),sum(coupon_reduce_amount_1d),sum(order_total_amount_1d)
from ${APP}.dws_trade_user_order_1d
where dtdate_add($do_date,-29)
and dt$do_date
group by user_id;dws_trade_user_order_refund_nd
insert overwrite table ${APP}.dws_trade_user_order_refund_nd partition(dt$do_date)
selectuser_id,sum(if(dtdate_add($do_date,-6),order_refund_count_1d,0)),sum(if(dtdate_add($do_date,-6),order_refund_num_1d,0)),sum(if(dtdate_add($do_date,-6),order_refund_amount_1d,0)),sum(order_refund_count_1d),sum(order_refund_num_1d),sum(order_refund_amount_1d)
from ${APP}.dws_trade_user_order_refund_1d
where dtdate_add($do_date,-29)
and dt$do_date
group by user_id;dws_trade_user_payment_nd
insert overwrite table ${APP}.dws_trade_user_payment_nd partition (dt $do_date)
select user_id,sum(if(dt date_add($do_date, -6), payment_count_1d, 0)),sum(if(dt date_add($do_date, -6), payment_num_1d, 0)),sum(if(dt date_add($do_date, -6), payment_amount_1d, 0)),sum(payment_count_1d),sum(payment_num_1d),sum(payment_amount_1d)
from ${APP}.dws_trade_user_payment_1d
where dt date_add($do_date, -29)and dt $do_date
group by user_id;dws_trade_user_sku_order_nd
insert overwrite table ${APP}.dws_trade_user_sku_order_nd partition(dt$do_date)
selectuser_id,sku_id,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_name,sum(if(dtdate_add($do_date,-6),order_count_1d,0)),sum(if(dtdate_add($do_date,-6),order_num_1d,0)),sum(if(dtdate_add($do_date,-6),order_original_amount_1d,0)),sum(if(dtdate_add($do_date,-6),activity_reduce_amount_1d,0)),sum(if(dtdate_add($do_date,-6),coupon_reduce_amount_1d,0)),sum(if(dtdate_add($do_date,-6),order_total_amount_1d,0)),sum(order_count_1d),sum(order_num_1d),sum(order_original_amount_1d),sum(activity_reduce_amount_1d),sum(coupon_reduce_amount_1d),sum(order_total_amount_1d)
from ${APP}.dws_trade_user_sku_order_1d
where dtdate_add($do_date,-30)
group by user_id,sku_id,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_name;dws_trade_user_sku_order_refund_nd
insert overwrite table ${APP}.dws_trade_user_sku_order_refund_nd partition(dt$do_date)
selectuser_id,sku_id,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_name,sum(if(dtdate_add($do_date,-6),order_refund_count_1d,0)),sum(if(dtdate_add($do_date,-6),order_refund_num_1d,0)),sum(if(dtdate_add($do_date,-6),order_refund_amount_1d,0)),sum(order_refund_count_1d),sum(order_refund_num_1d),sum(order_refund_amount_1d)
from ${APP}.dws_trade_user_sku_order_refund_1d
where dtdate_add($do_date,-29)
and dt$do_date
group by user_id,sku_id,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_name;dws_traffic_page_visitor_page_view_nd
insert overwrite table ${APP}.dws_traffic_page_visitor_page_view_nd partition(dt$do_date)
selectmid_id,brand,model,operate_system,page_id,sum(if(dtdate_add($do_date,-6),during_time_1d,0)),sum(if(dtdate_add($do_date,-6),view_count_1d,0)),sum(during_time_1d),sum(view_count_1d)
from ${APP}.dws_traffic_page_visitor_page_view_1d
where dtdate_add($do_date,-29)
and dt$do_date
group by mid_id,brand,model,operate_system,page_id;
case $1 indws_trade_activity_order_nd )hive -e $dws_trade_activity_order_nd;;dws_trade_coupon_order_nd )hive -e $dws_trade_coupon_order_nd;;dws_trade_province_order_nd )hive -e $dws_trade_province_order_nd;;dws_trade_user_cart_add_nd )hive -e $dws_trade_user_cart_add_nd;;dws_trade_user_order_nd )hive -e $dws_trade_user_order_nd;;dws_trade_user_order_refund_nd )hive -e $dws_trade_user_order_refund_nd;;dws_trade_user_payment_nd )hive -e $dws_trade_user_payment_nd;;dws_trade_user_sku_order_nd )hive -e $dws_trade_user_sku_order_nd;;dws_trade_user_sku_order_refund_nd )hive -e $dws_trade_user_sku_order_refund_nd;;dws_traffic_page_visitor_page_view_nd )hive -e $dws_traffic_page_visitor_page_view_nd;;all )hive -e $dws_trade_activity_order_nd$dws_trade_coupon_order_nd$dws_trade_province_order_nd$dws_trade_user_cart_add_nd$dws_trade_user_order_nd$dws_trade_user_order_refund_nd$dws_trade_user_payment_nd$dws_trade_user_sku_order_nd$dws_trade_user_sku_order_refund_nd$dws_traffic_page_visitor_page_view_nd;;
esac4.td表格装载脚本
1.首日装载脚本
#!/bin/bash
APPgmallif [ -n $2 ] ;thendo_date$2
else echo 请传入日期参数exit
fidws_trade_user_order_td
insert overwrite table ${APP}.dws_trade_user_order_td partition(dt$do_date)
selectuser_id,min(dt) login_date_first,max(dt) login_date_last,sum(order_count_1d) order_count,sum(order_num_1d) order_num,sum(order_original_amount_1d) original_amount,sum(activity_reduce_amount_1d) activity_reduce_amount,sum(coupon_reduce_amount_1d) coupon_reduce_amount,sum(order_total_amount_1d) total_amount
from ${APP}.dws_trade_user_order_1d
group by user_id;
dws_trade_user_payment_td
insert overwrite table ${APP}.dws_trade_user_payment_td partition(dt$do_date)
selectuser_id,min(dt) payment_date_first,max(dt) payment_date_last,sum(payment_count_1d) payment_count,sum(payment_num_1d) payment_num,sum(payment_amount_1d) payment_amount
from ${APP}.dws_trade_user_payment_1d
group by user_id;
dws_user_user_login_td
insert overwrite table ${APP}.dws_user_user_login_td partition(dt$do_date)
selectu.id,nvl(login_date_last,date_format(create_time,yyyy-MM-dd)),nvl(login_count_td,1)
from
(selectid,create_timefrom ${APP}.dim_user_zipwhere dt9999-12-31
)u
left join
(selectuser_id,max(dt) login_date_last,count(*) login_count_tdfrom ${APP}.dwd_user_login_incgroup by user_id
)l
on u.idl.user_id;
case $1 indws_trade_user_order_td )hive -e $dws_trade_user_order_td;;dws_trade_user_payment_td )hive -e $dws_trade_user_payment_td;;dws_user_user_login_td )hive -e $dws_user_user_login_td;;all )hive -e $dws_trade_user_order_td$dws_trade_user_payment_td$dws_user_user_login_td;;
esac2.每日装载脚本
#!/bin/bash
APPgmall# 如果输入的日期按照取输入日期如果没输入日期取当前时间的前一天
if [ -n $2 ] ;thendo_date$2
else do_datedate -d -1 day %F
fidws_trade_user_order_td
insert overwrite table ${APP}.dws_trade_user_order_td partition(dt$do_date)
selectnvl(old.user_id,new.user_id),if(new.user_id is not null and old.user_id is null,$do_date,old.order_date_first),if(new.user_id is not null,$do_date,old.order_date_last),nvl(old.order_count_td,0)nvl(new.order_count_1d,0),nvl(old.order_num_td,0)nvl(new.order_num_1d,0),nvl(old.original_amount_td,0)nvl(new.order_original_amount_1d,0),nvl(old.activity_reduce_amount_td,0)nvl(new.activity_reduce_amount_1d,0),nvl(old.coupon_reduce_amount_td,0)nvl(new.coupon_reduce_amount_1d,0),nvl(old.total_amount_td,0)nvl(new.order_total_amount_1d,0)
from
(selectuser_id,order_date_first,order_date_last,order_count_td,order_num_td,original_amount_td,activity_reduce_amount_td,coupon_reduce_amount_td,total_amount_tdfrom ${APP}.dws_trade_user_order_tdwhere dtdate_add($do_date,-1)
)old
full outer join
(selectuser_id,order_count_1d,order_num_1d,order_original_amount_1d,activity_reduce_amount_1d,coupon_reduce_amount_1d,order_total_amount_1dfrom ${APP}.dws_trade_user_order_1dwhere dt$do_date
)new
on old.user_idnew.user_id;
dws_trade_user_payment_td
insert overwrite table ${APP}.dws_trade_user_payment_td partition(dt$do_date)
selectnvl(old.user_id,new.user_id),if(old.user_id is null and new.user_id is not null,$do_date,old.payment_date_first),if(new.user_id is not null,$do_date,old.payment_date_last),nvl(old.payment_count_td,0)nvl(new.payment_count_1d,0),nvl(old.payment_num_td,0)nvl(new.payment_num_1d,0),nvl(old.payment_amount_td,0)nvl(new.payment_amount_1d,0)
from
(selectuser_id,payment_date_first,payment_date_last,payment_count_td,payment_num_td,payment_amount_tdfrom ${APP}.dws_trade_user_payment_tdwhere dtdate_add($do_date,-1)
)old
full outer join
(selectuser_id,payment_count_1d,payment_num_1d,payment_amount_1dfrom ${APP}.dws_trade_user_payment_1dwhere dt$do_date
)new
on old.user_idnew.user_id;
dws_user_user_login_td
insert overwrite table ${APP}.dws_user_user_login_td partition(dt$do_date)
selectnvl(old.user_id,new.user_id),if(new.user_id is null,old.login_date_last,$do_date),nvl(old.login_count_td,0)nvl(new.login_count_1d,0)
from
(selectuser_id,login_date_last,login_count_tdfrom ${APP}.dws_user_user_login_tdwhere dtdate_add($do_date,-1)
)old
full outer join
(selectuser_id,count(*) login_count_1dfrom ${APP}.dwd_user_login_incwhere dt$do_dategroup by user_id
)new
on old.user_idnew.user_id;
case $1 indws_trade_user_order_td )hive -e $dws_trade_user_order_td;;dws_trade_user_payment_td )hive -e $dws_trade_user_payment_td;;dws_user_user_login_td )hive -e $dws_user_user_login_td;;all )hive -e $dws_trade_user_order_td$dws_trade_user_payment_td$dws_user_user_login_td;;
esac
