基于 Hadoop 生态圈的数据仓库实践 —— 累积的度量

wzy0623

已于 2025-05-12 13:38:18 修改

阅读量3.8k

点赞数

CC 4.0 BY-SA版权

分类专栏：基于 Hadoop 生态圈的数据仓库实践文章标签： hadoop 数据仓库

于 2016-08-10 18:09:45 首次发布

本文链接：https://blog.youkuaiyun.com/wzy0623/article/details/52174832

基于 Hadoop 生态圈的数据仓库实践专栏收录该内容

43 篇文章

订阅专栏

本文介绍如何在数据仓库中实现累积月底金额度量，并详细解释了对应的模式修改、初始装载及定期装载过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

本篇说明如何实现累积月底金额，并对数据仓库模式和初始装载、定期装载脚本做相应地修改。累积度量是半可加的，而且它的初始装载比前面做的要复杂的多。

事实表中的数字度量可划分为可加、半可加、不可加三类。最灵活、最有用的度量是完全可加的，可加性度量可以按照与事实表关联的任意维度汇总。半可加度量可以对某些维度汇总，但不能对所有维度汇总。差额是常见的半可加度量，除了时间维度外，它们可以跨其它所有维度进行加法操作。另外，一些度量是完全不可加的，例如比率。

一、修改模式

建立一个新叫做 month_end_balance_fact 的事实表，用来存储销售订单金额的月底累积值。month_end_balance_fact 表在模式中构成了另一个星型模式，新的星型模式除了包括这个新的事实表，还包括两个其它星型模式中已有的维度表，即 product_dim 和 month_dim。下图显示了新的模式，这里只显示了相关的表。

下面的脚本用于创建 month_end_balance_fact 表。

use dw;  
create table month_end_balance_fact (  
    month_sk int,  
    product_sk int,  
    month_end_amount_balance decimal(10,2),  
    month_end_quantity_balance int  
);

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

二、初始装载

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

use dw;  
insert overwrite table month_end_balance_fact  
select a.month_sk,  
       b.product_sk,  
       sum(b.month_order_amount) month_order_amount,  
       sum(b.month_order_quantity) month_order_quantity  
  from month_dim a,  
       (select a.*, 
                    b.year, 
                    b.month, 
                    max(a.order_month_sk) over () max_month_sk
               from month_end_sales_order_fact a, month_dim b 
              where a.order_month_sk = b.month_sk) b
 where a.month_sk <= b.max_month_sk and a.year = b.year and b.month <= a.month
 group by a.month_sk , b.product_sk;

为了确认初始装载是否正确，先查询 month_end_sales_order_fact 表，然后在执行完初始装载后查询 month_end_balance_fact 表。使用下面的语句查询 month_end_sales_order_fact 表。

use dw;
select b.year year,
       b.month month,
       a.product_sk psk,
       a.month_order_amount amt,
       a.month_order_quantity qty
  from month_end_sales_order_fact a,
       month_dim b
 where a.order_month_sk = b.month_sk
cluster by year, month, psk;

查询结果如下图所示。

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

use dw;
select b.year year,
       b.month month,
       a.product_sk psk,
       a.month_end_amount_balance amt,
       a.month_end_quantity_balance qty
  from month_end_balance_fact a,
       month_dim b
 where a.month_sk = b.month_sk
cluster by year, month, psk;

查询结果如下图所示。

从图中可也看到，2016 年 6 月的商品销售金额和数量被累积到了 2016 年 7 月。商品 1 和 2 累加了 6 月和 7 月的销售，商品 3 在 7 月没有销售，所以 6 月的销售顺延到 7 月，商品 4 和 5 只有 7 月的销售。

三、定期装载

下面所示的 month_balance_sum.sql 脚本用于定期装载销售订单金额月底累积事实表，该脚本在每个月执行一次，装载上个月的数据。

-- 设置变量以支持事务    
set hive.support.concurrency=true;    
set hive.exec.dynamic.partition.mode=nonstrict;    
set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.dbtxnmanager;    
set hive.compactor.initiator.on=true;    
set hive.compactor.worker.threads=1;

use dw;  

set hivevar:pre_month_date = add_months(current_date,-1); 
set hivevar:year = year(${hivevar:pre_month_date});
set hivevar:month = month(${hivevar:pre_month_date});
 
insert into month_end_balance_fact  
select order_month_sk,  
       product_sk,  
       sum(month_order_amount),  
       sum(month_order_quantity)  
  from (select a.*  
          from month_end_sales_order_fact a, 
               month_dim b  
         where a.order_month_sk = b.month_sk  
           and b.year = ${hivevar:year}  
           and b.month = ${hivevar:month}
       union all  
       select month_sk + 1 order_month_sk,
               product_sk product_sk,
               month_end_amount_balance month_order_amount,
               month_end_quantity_balance month_order_quantity 
          from month_end_balance_fact a  
         where a.month_sk in (select max(case when ${hivevar:month} = 1 then 0 else month_sk end)  
                                from month_end_balance_fact)) t
 group by order_month_sk, product_sk;

四、测试定期装载

使用下面步骤测试非 1 月的装载。

1. 使用下面的命令向 month_end_sales_order_fact 表添加两条记录

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

2. 设置时间

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

3. 执行定期装载

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

4. 查询 month_end_balance_fact 表

select * 
  from dw.month_end_balance_fact a
cluster by a.month_sk, a.product_sk;

查询结果如下图所示。

从图中可以看到，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 月。

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

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

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

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

4. 执行定期装载

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

5. 查询 month_end_balance_fact 表

select * 
  from dw.month_end_balance_fact a
cluster by a.month_sk, a.product_sk;

查询结果如下图所示。

从图中可以看到，2017 年 1 月只装载了新增的两条销售记录。

6. 删除测试数据

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

五、查询

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

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

use dw;
select year, month, sum(month_end_amount_balance) s
  from month_end_balance_fact a,
       month_dim b
 where a.month_sk = b.month_sk
 group by year, month
cluster by year, month;

查询结果如下图所示。

通过月份累加月底金额。

use dw;
select product_name, sum(month_end_amount_balance) s
  from month_end_balance_fact a,
       product_dim b
 where a.product_sk = b.product_sk
 group by product_name;

查询结果如下图所示。

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

use dw;
select product_name, sum(month_order_amount) s
  from month_end_sales_order_fact a,
       product_dim b
 where a.product_sk = b.product_sk
 group by product_name;

查询结果如下图所示。