基于hadoop生态圈的数据仓库实践 —— 进阶技术(十六)

释放双眼,带上耳机,听听看~!

十六、累积的度量
本篇说明如何实现累积月底金额,并对数据仓库模式和初始装载、定期装载脚本做相应地修改。累积度量是半可加的,而且它的初始装载比前面做的要复杂的多。
可加、半可加、不可加事实
事实表中的数字度量可划分为三类。最灵活、最有用的度量是完全可加的,可加性度量可以按照与事实表关联的任意维度汇总。半可加度量可以对某些维度汇总,但不能对所有维度汇总。差额是常见的半可加度量,除了时间维度外,它们可以跨其它所有维度进行加法操作。另外,一些度量是完全不可加的,例如比率。
1. 修改模式
建立一个新叫做month_end_balance_fact的事实表,用来存储销售订单金额的月底累积值。month_end_balance_fact表在模式中构成了另一个星型模式。新的星型模式除了包括这个新的事实表,还包括两个其它星型模式中已有的维度表,即product_dim和month_dim。下图显示了新的模式。注意这里只显示了相关的表。
下面的脚本用于创建month_end_balance_fact表。


1
2
3
4
5
6
7
8
1use dw;  
2create table month_end_balance_fact (  
3    month_sk int,  
4    product_sk int,  
5    month_end_amount_balance decimal(10,2),  
6    month_end_quantity_balance int  
7);  
8

1
2
1        因为对此事实表只有insert操作,没有update、delete操作,所以这里没有用orc文件格式,而是采用了缺省的文本格式。  
2

2. 初始装载
现在要把month_end_sales_order_fact表里的数据导入month_end_balance_fact表,下面显示了初始装载month_end_balance_fact表的脚本。此脚本装载累月的月底销售订单,每年的年初都要重置累积金额。


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
1use dw;  
2insert overwrite table month_end_balance_fact  
3select a.month_sk,  
4       b.product_sk,  
5       sum(b.month_order_amount) month_order_amount,  
6       sum(b.month_order_quantity) month_order_quantity  
7  from month_dim a,  
8       (select a.*,
9                    b.year,
10                    b.month,
11                    max(a.order_month_sk) over () max_month_sk
12               from month_end_sales_order_fact a, month_dim b
13              where a.order_month_sk = b.month_sk) b
14 where a.month_sk <= b.max_month_sk and a.year = b.year and b.month <= a.month
15 group by a.month_sk , b.product_sk;
16

1
2
1        为了确认初始装载是否正确,先查询month_end_sales_order_fact表,然后在执行完初始装载后查询month_end_balance_fact表。  
2

        使用下面的语句查询month_end_sales_order_fact表。


1
2
3
4
5
6
7
8
9
10
11
1use dw;
2select b.year year,
3       b.month month,
4       a.product_sk psk,
5       a.month_order_amount amt,
6       a.month_order_quantity qty
7  from month_end_sales_order_fact a,
8       month_dim b
9 where a.order_month_sk = b.month_sk
10cluster by year, month, psk;
11

1
2
1        查询结果如下图所示。  
2

        使用下面的语句查询month_end_balance_fact表。


1
2
3
4
5
6
7
8
9
10
11
1use dw;
2select b.year year,
3       b.month month,
4       a.product_sk psk,
5       a.month_end_amount_balance amt,
6       a.month_end_quantity_balance qty
7  from month_end_balance_fact a,
8       month_dim b
9 where a.month_sk = b.month_sk
10cluster by year, month, psk;
11

1
2
1        查询结果如下图所示。  
2

        从图中可也看到,2016年6月的商品销售金额和数量被累积到了了2016年7月。商品1和2累加了6月和7月的销售,商品3在7月没有销售,所以6月的销售顺延到7月,商品4和5只有7月的销售。
3. 定期装载
下面所示的month_balance_sum.sql脚本用于定期装载销售订单金额月底累积事实表,该脚本在每个月执行一次,装载上个月的数据。


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
1-- 设置变量以支持事务    
2set hive.support.concurrency=true;    
3set hive.exec.dynamic.partition.mode=nonstrict;    
4set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.dbtxnmanager;    
5set hive.compactor.initiator.on=true;    
6set hive.compactor.worker.threads=1;
7
8use dw;  
9
10set hivevar:pre_month_date = add_months(current_date,-1);
11set hivevar:year = year(${hivevar:pre_month_date});
12set hivevar:month = month(${hivevar:pre_month_date});
13
14insert into month_end_balance_fact  
15select order_month_sk,  
16       product_sk,  
17       sum(month_order_amount),  
18       sum(month_order_quantity)  
19  from (select a.*  
20          from month_end_sales_order_fact a,
21               month_dim b  
22         where a.order_month_sk = b.month_sk  
23           and b.year = ${hivevar:year}  
24           and b.month = ${hivevar:month}
25       union all  
26       select month_sk + 1 order_month_sk,
27               product_sk product_sk,
28               month_end_amount_balance month_order_amount,
29               month_end_quantity_balance month_order_quantity
30          from month_end_balance_fact a  
31         where a.month_sk in (select max(case when ${hivevar:month} = 1 then 0 else month_sk end)  
32                                from month_end_balance_fact)) t
33 group by order_month_sk, product_sk;
34

1
2
1**4. 测试定期装载**  
2

        使用下面步骤测试非1月的装载:
(1)使用下面的命令向month_end_sales_order_fact表添加两条记录


1
2
1insert into dw.month_end_sales_order_fact values (200,1,1000,10),(200,6,1000,10);
2

1
2
1(2)设置时间  
2

将set hivevar:pre_month_date = add_months(current_date,-1); 行改为set hivevar:pre_month_date = current_date;,装载2016年8月的数据。 
(3)执行定期装载


1
2
1beeline -u jdbc:hive2://cdh2:10000/dw -f month_balance_sum.sql
2

1
2
1(4)查询month_end_balance_fact表  
2

1
2
3
4
1select *
2  from dw.month_end_balance_fact a
3cluster by a.month_sk, a.product_sk;
4

1
2
1        查询结果如下图所示。  
2

        从图中可以看到,product_sk为6的产品是本年前面月份没有销售而8月份有销售的,product_sk为1的产品是本年前面月份和8月份都有销售的,而product_sk为2、3、4、5的产品是本年前面月份有销售而8月份没有销售的。
使用下面步骤测试1月的装载:
(1)使用下面的命令向month_end_sales_order_fact表添加两条记录,month_sk的值是205,指的是2017年1月


1
2
3
1insert into dw.month_end_sales_order_fact values (205,1,1000,10);
2insert into dw.month_end_sales_order_fact values (205,6,1000,10);
3

1
2
1(2)使用下面的命令向month_end_balance_fact表添加三条记录   
2

1
2
3
4
1insert into dw.month_end_balance_fact values (204,1,1000,10);
2insert into dw.month_end_balance_fact values (204,6,1000,10);
3insert into dw.month_end_balance_fact values (204,3,1000,10);
4

1
2
1(3)将set hivevar:pre_month_date = add_months(current_date,-1); 行改为set hivevar:pre_month_date = add_months('2017-02-01',-1);,装载2017年1月的数据。   
2

(4)执行定期装载


1
2
1beeline -u jdbc:hive2://cdh2:10000/dw -f month_balance_sum.sql
2

1
2
1(5)查询month_end_balance_fact表  
2

1
2
3
4
1select *
2  from dw.month_end_balance_fact a
3cluster by a.month_sk, a.product_sk;
4

1
2
1        查询结果如下图所示。  
2

        从图中可以看到,2017年1月只装载了新增的两条销售记录。
(6)删除测试数据


1
2
3
4
5
1delete from dw.month_end_sales_order_fact where order_month_sk >=200;
2create table t1 as select * from month_end_balance_fact where month_sk < 200;
3insert overwrite table month_end_balance_fact select * from t1;
4drop table t1;
5

1
2
1**5. 查询**  
2

        本小节使用两个查询展示月底累积金额度量(也就是累积度量)必须要小心使用,因为它不是“全可加”的。一个非全可加度量在某些维度(通常是时间维度)上是不可加的。

通过产品可加,可以通过产品正确地累加月底累积金额。


1
2
3
4
5
6
7
8
1use dw;
2select year, month, sum(month_end_amount_balance) s
3  from month_end_balance_fact a,
4       month_dim b
5 where a.month_sk = b.month_sk
6 group by year, month
7cluster by year, month;
8

1
2
1        查询结果如下图所示。  
2

        通过月份累加月底金额。


1
2
3
4
5
6
7
1use dw;
2select product_name, sum(month_end_amount_balance) s
3  from month_end_balance_fact a,
4       product_dim b
5 where a.product_sk = b.product_sk
6 group by product_name;
7

1
2
1        查询结果如下图所示。  
2

        查询结果是错误的。正确的结果应该和下面的在month_end_sales_order_fact表上进行的查询结果相同。


1
2
3
4
5
6
7
1use dw;
2select product_name, sum(month_order_amount) s
3  from month_end_sales_order_fact a,
4       product_dim b
5 where a.product_sk = b.product_sk
6 group by product_name;
7

        查询结果如下图所示。

给TA打赏
共{{data.count}}人
人已打赏
安全运维

MySQL和MongoDB数据相互迁移

2021-12-11 11:36:11

安全运维

Ubuntu上NFS的安装配置

2021-12-19 17:36:11

个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索