需求:数据量亿级、日增量千万级
1、 如何快速的查询任意一天的 日累积、月累积、季累积、年累积的交易额
2、 如何快速的查询任意一天的 日累积、月累积、季累积、年累积的交易用户数
ODS:增量抽取数据
DROP TABLE IF EXISTS ods_order_info_di_james;
CREATE TABLE IF NOT EXISTS ods_order_info_di_james (
userid STRING COMMENT '用户ID',
order_id STRING COMMENT '订单ID',
order_date STRING COMMENT '订单日期',
pay_amt DOUBLE COMMENT '支付金额'
) COMMENT '订单表'
PARTITIONED by (ds STRING)
LOCATION '/user/hive/warehouse/datacenter/ods_order_info_di_james'
--ods dynamic partition init data
insert overwrite table ods_order_info_di_james partition (ds)
SELECT 'james' as userid
,'1' as order_id
,'20211026' as order_date
,100 as pay_amt
,'20211026' as ds
union all
SELECT 'james' as userid
,'2' as order_id
,'20211026' as order_date
,200 as pay_amt
,'20211026' as ds
union all
SELECT 'james' as userid
,'3' as order_id
,'20211027' as order_date
,100 as pay_amt
,'20211027' as ds
union all
SELECT 'james' as userid
,'4' as order_id
,'20211027' as order_date
,200 as pay_amt
,'20211027' as ds
union all
SELECT 'james' as userid
,'5' as order_id
,'20210909' as order_date
,100 as pay_amt
,'20210909' as ds
union all
SELECT 'james' as userid
,'6' as order_id
,'20210909' as order_date
,200 as pay_amt
,'20210909' as ds;
DWD:增量、标准化、退维
DROP TABLE IF EXISTS dwd_order_info_di_james;
CREATE TABLE dwd_order_info_di_james (
userid STRING COMMENT '用户ID',
order_id STRING COMMENT '订单ID',
order_date STRING COMMENT '订单日期',
pay_amt DOUBLE COMMENT '支付金额'
) COMMENT 'dwd层订单表'
PARTITIONED by (ds STRING)
LOCATION '/user/hive/warehouse/datacenter/dwd_order_info_di_james'
--dwd dynamic partition init data
insert overwrite table dwd_order_info_di_james partition(ds)
select
userid
,order_id
,order_date
,pay_amt
,ds
from
ods_order_info_di_james
where ds > 0;
DWS: 轻度汇总
粒度:userid date_id (用户+天)
指标:amt_dtd (用户当天的支付金额)
DROP TABLE IF EXISTS dws_user_day_summary_james;
CREATE TABLE dws_user_day_summary_james (
userid STRING COMMENT '用户ID',
date_id STRING COMMENT '日期ID',
amt_dtd DOUBLE COMMENT '用户当天的支付金额'
) COMMENT 'dws层订单表'
PARTITIONED by (ds STRING)
LOCATION '/user/hive/wareh