数据建模案例

需求：数据量亿级、日增量千万级
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