目录
一、交易域用户商品粒度订单最近1日汇总表
二、交易域用户粒度订单最近1日汇总表
三、交易域用户粒度加购最近1日汇总表
四、交易域用户粒度支付最近1日汇总表
五、交易域省份粒度订单最近1日汇总表
六、工具域用户优惠券粒度优惠券使用(支付)最近1日汇总表
七、互动域商品粒度收藏商品最近1日汇总表
八、流量域会话粒度页面浏览最近1日汇总表
九、流量域访客页面粒度页面浏览最近1日汇总表
一、交易域用户商品粒度订单最近1日汇总表
建表语句
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 ORC LOCATION '/warehouse/gmall/dws/dws_trade_user_sku_order_1d' TBLPROPERTIES ('orc.compress' = 'snappy');首日数据装载
set hive.exec.dynamic.partition.mode=nonstrict; -- Hive的bug：对某些类型数据的处理可能会导致报错，关闭矢量化查询优化解决 set hive.vectorized.execution.enabled = false; insert overwrite table dws_trade_user_sku_order_1d partition(dt) select user_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 ( select dt, 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_1d from dwd_trade_order_detail_inc group by dt,user_id,sku_id )od left join ( select id, sku_name, category1_id, category1_name, category2_id, category2_name, category3_id, category3_name, tm_id, tm_name from dim_sku_full where dt='2022-06-08' )sku on od.sku_id=sku.id; -- 矢量化查询优化可以一定程度上提升执行效率，不会触发前述Bug时，应打开 set hive.vectorized.execution.enabled = true;每日数据装载
set hive.vectorized.execution.enabled = false; insert overwrite table dws_trade_user_sku_order_1d partition(dt='2022-06-09') select user_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 ( select user_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_amount from dwd_trade_order_detail_inc where dt='2022-06-09' group by user_id,sku_id )od left join ( select id, sku_name, category1_id, category1_name, category2_id, category2_name, category3_id, category3_name, tm_id, tm_name from dim_sku_full where dt='2022-06-09' )sku on od.sku_id=sku.id; set hive.vectorized.execution.enabled = true;二、交易域用户粒度订单最近1日汇总表
建表语句
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日下单原始金额', `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 ORC LOCATION '/warehouse/gmall/dws/dws_trade_user_order_1d' TBLPROPERTIES ('orc.compress' = 'snappy');首日数据装载
set hive.exec.dynamic.partition.mode=nonstrict; insert overwrite table dws_trade_user_order_1d partition(dt) select user_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 dwd_trade_order_detail_inc group by user_id,dt;每日数据装载
insert overwrite table dws_trade_user_order_1d partition(dt='2022-06-09') select user_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 dwd_trade_order_detail_inc where dt='2022-06-09' group by user_id;三、交易域用户粒度加购最近1日汇总表
建表语句
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 ORC LOCATION '/warehouse/gmall/dws/dws_trade_user_cart_add_1d' TBLPROPERTIES ('orc.compress' = 'snappy');首日数据装载
set hive.exec.dynamic.partition.mode=nonstrict; insert overwrite table dws_trade_user_cart_add_1d partition(dt) select user_id, count(*), sum(sku_num), dt from dwd_trade_cart_add_inc group by user_id,dt;每日数据装载
insert overwrite table dws_trade_user_cart_add_1d partition(dt='2022-06-09') select user_id, count(*), sum(sku_num) from dwd_trade_cart_add_inc where dt='2022-06-09' group by user_id;四、交易域用户粒度支付最近1日汇总表
建表语句
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 ORC LOCATION '/warehouse/gmall/dws/dws_trade_user_payment_1d' TBLPROPERTIES ('orc.compress' = 'snappy');首日数据装载
set hive.exec.dynamic.partition.mode=nonstrict; insert overwrite table dws_trade_user_payment_1d partition(dt) select user_id, count(distinct(order_id)), sum(sku_num), sum(split_payment_amount), dt from dwd_trade_pay_detail_suc_inc group by user_id,dt;每日数据装载
insert overwrite table dws_trade_user_payment_1d partition(dt='2022-06-09') select user_id, count(distinct(order_id)), sum(sku_num), sum(split_payment_amount) from dwd_trade_pay_detail_suc_inc where dt='2022-06-09' group by user_id;五、交易域省份粒度订单最近1日汇总表
建表语句
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` STRING COMMENT '新版国际标准地区编码', `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 ORC LOCATION '/warehouse/gmall/dws/dws_trade_province_order_1d' TBLPROPERTIES ('orc.compress' = 'snappy');首日数据装载
set hive.exec.dynamic.partition.mode=nonstrict; insert overwrite table dws_trade_province_order_1d partition(dt) select province_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 ( select province_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, dt from dwd_trade_order_detail_inc group by province_id,dt )o left join ( select id, province_name, area_code, iso_code, iso_3166_2 from dim_province_full where dt='2022-06-08' )p on o.province_id=p.id;每日数据装载
insert overwrite table dws_trade_province_order_1d partition(dt='2022-06-09') select province_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 ( select province_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 from dwd_trade_order_detail_inc where dt='2022-06-09' group by province_id )o left join ( select id, province_name, area_code, iso_code, iso_3166_2 from dim_province_full where dt='2022-06-09' )p on o.province_id=p.id;六、工具域用户优惠券粒度优惠券使用(支付)最近1日汇总表
建表语句
DROP TABLE IF EXISTS dws_tool_user_coupon_coupon_used_1d; CREATE EXTERNAL TABLE dws_tool_user_coupon_coupon_used_1d ( `user_id` STRING COMMENT '用户ID', `coupon_id` STRING COMMENT '优惠券ID', `coupon_name` STRING COMMENT '优惠券名称', `coupon_type_code` STRING COMMENT '优惠券类型编码', `coupon_type_name` STRING COMMENT '优惠券类型名称', `benefit_rule` STRING COMMENT '优惠规则', `used_count_1d` STRING COMMENT '使用(支付)次数' ) COMMENT '工具域用户优惠券粒度优惠券使用(支付)最近1日汇总表' PARTITIONED BY (`dt` STRING) STORED AS ORC LOCATION '/warehouse/gmall/dws/dws_tool_user_coupon_coupon_used_1d' TBLPROPERTIES ('orc.compress' = 'snappy');首日数据装载
set hive.exec.dynamic.partition.mode=nonstrict; insert overwrite table dws_tool_user_coupon_coupon_used_1d partition(dt) select user_id, coupon_id, coupon_name, coupon_type_code, coupon_type_name, benefit_rule, used_count, dt from ( select dt, user_id, coupon_id, count(*) used_count from dwd_tool_coupon_used_inc group by dt,user_id,coupon_id )t1 left join ( select id, coupon_name, coupon_type_code, coupon_type_name, benefit_rule from dim_coupon_full where dt='2022-06-08' )t2 on t1.coupon_id=t2.id;每日数据装载
insert overwrite table dws_tool_user_coupon_coupon_used_1d partition(dt='2022-06-09') select user_id, coupon_id, coupon_name, coupon_type_code, coupon_type_name, benefit_rule, used_count from ( select user_id, coupon_id, count(*) used_count from dwd_tool_coupon_used_inc where dt='2022-06-09' group by user_id,coupon_id )t1 left join ( select id, coupon_name, coupon_type_code, coupon_type_name, benefit_rule from dim_coupon_full where dt='2022-06-09' )t2 on t1.coupon_id=t2.id;七、互动域商品粒度收藏商品最近1日汇总表
建表语句
DROP TABLE IF EXISTS dws_interaction_sku_favor_add_1d; CREATE EXTERNAL TABLE dws_interaction_sku_favor_add_1d ( `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 '品牌名称', `favor_add_count_1d` BIGINT COMMENT '商品被收藏次数' ) COMMENT '互动域商品粒度收藏商品最近1日汇总表' PARTITIONED BY (`dt` STRING) STORED AS ORC LOCATION '/warehouse/gmall/dws/dws_interaction_sku_favor_add_1d' TBLPROPERTIES ('orc.compress' = 'snappy');首日数据装载
set hive.exec.dynamic.partition.mode=nonstrict; insert overwrite table dws_interaction_sku_favor_add_1d partition(dt) select sku_id, sku_name, category1_id, category1_name, category2_id, category2_name, category3_id, category3_name, tm_id, tm_name, favor_add_count, dt from ( select dt, sku_id, count(*) favor_add_count from dwd_interaction_favor_add_inc group by dt,sku_id )favor left join ( select id, sku_name, category1_id, category1_name, category2_id, category2_name, category3_id, category3_name, tm_id, tm_name from dim_sku_full where dt='2022-06-08' )sku on favor.sku_id=sku.id;每日数据装载
insert overwrite table dws_interaction_sku_favor_add_1d partition(dt='2022-06-09') select sku_id, sku_name, category1_id, category1_name, category2_id, category2_name, category3_id, category3_name, tm_id, tm_name, favor_add_count from ( select sku_id, count(*) favor_add_count from dwd_interaction_favor_add_inc where dt='2022-06-09' group by sku_id )favor left join ( select id, sku_name, category1_id, category1_name, category2_id, category2_name, category3_id, category3_name, tm_id, tm_name from dim_sku_full where dt='2022-06-09' )sku on favor.sku_id=sku.id;八、流量域会话粒度页面浏览最近1日汇总表
建表语句
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 ORC LOCATION '/warehouse/gmall/dws/dws_traffic_session_page_view_1d' TBLPROPERTIES ('orc.compress' = 'snappy');数据装载
insert overwrite table dws_traffic_session_page_view_1d partition(dt='2022-06-08') select session_id, mid_id, brand, model, operate_system, version_code, channel, sum(during_time), count(*) from dwd_traffic_page_view_inc where dt='2022-06-08' group by session_id,mid_id,brand,model,operate_system,version_code,channel;九、流量域访客页面粒度页面浏览最近1日汇总表
建表语句
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 ORC LOCATION '/warehouse/gmall/dws/dws_traffic_page_visitor_page_view_1d' TBLPROPERTIES ('orc.compress' = 'snappy');数据装载
insert overwrite table dws_traffic_page_visitor_page_view_1d partition(dt='2022-06-08') select mid_id, brand, model, operate_system, page_id, sum(during_time), count(*) from dwd_traffic_page_view_inc where dt='2022-06-08' group by mid_id,brand,model,operate_system,page_id;
