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

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

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/wzy0623/article/details/52174832

十六、累积的度量 本篇说明如何实现累积月底金额,并对数据仓库模式和初始装载、定期装载脚本做相应地修改。累积度量是半可加的,而且它的初始装载比前面做的要复杂的多。 可加、半可加、不可加事实 事实表中的数字度量可划分为三类。最灵活、最有用的度量是完全可加的,可加性度量可以按照与事实表关联的任意维度汇总。半可加度量可以对某些维度汇总,但不能对所有维度汇总。差额是常见的半可加度量,除了时间维度外,它们可以跨其它所有维度进行加法操作。另外,一些度量是完全不可加的,例如比率。 1. 修改模式 建立一个新叫做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文件格式,而是采用了缺省的文本格式。 2. 初始装载 现在要把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月的销售。 3. 定期装载 下面所示的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;

4. 测试定期装载 使用下面步骤测试非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;

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

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;

查询结果如下图所示。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • HAWQ取代传统数仓实践(十七)——事实表技术之累积度量

            累积度量指的是聚合从序列内第一个元素到当前元素的数据,例如统计从每年的一月到当前月份的累积销售额。本篇说明如何在销售订单示例中实现累积月销售数量...

    用户1148526
  • 基于Hadoop生态圈的数据仓库实践 —— 进阶技术(八)

    八、多路径和参差不齐的层次 本节讨论多路径层次,它是对单路径层次的扩展。上一节里数据仓库的月维度只有一条层次路径,即年-季度-月这条路径。在本...

    用户1148526
  • HAWQ技术解析(八) —— 大表分区

    一、HAWQ中的分区表         与大多数关系数据库一样,HAWQ也支持分区表。这里所说的分区表是指HAWQ的内部分区表,外部分区表在后面“外部数据”篇讨...

    用户1148526
  • Python 当前时间增加或减少一个月

    今天在之前的代码中发现了一个bug,有个计算当前时间减少一个月的函数,其报出下面的异常信息:

    py3study
  • 监控一哥Prometheus你可认识?

    先大致认识认识普罗米修斯 —— Prometheus。依据官方文档https://prometheus.io/docs/introduction/overvie...

    一猿小讲
  • 以色列理工暑期学习-机器学习中Loss函数的小结

    机器学习作为一种优化方法,最重要的一点是找到优化的目标函数——损失函数和正则项的组合;有了目标函数的“正确的打开方式”,才能通过合适的机器学习算法求解优化。 通...

    智能算法
  • 群辉docker的简单使用

    各种折腾后,感觉还是win,centos好用 但是,平时一些小功能,只有简单存储,和一些小折腾 发现群辉好一点的机器,都支持docker了 docker很...

    dodo_lihao
  • What‘s docker?How to study?

    看图说话:“一只可爱的大鲸鱼上面摆放着许多小箱子,而这些小箱子呢--就是应用程序,鲸鱼就是操作系统”。 大家应该都知道集装箱吧,不过你是什么货物,通过什么运输方...

    benny
  • 【原创】Java并发编程系列13 | LookSupport

    java.util.concurrent 中源码频繁使用的 LockSupport 来阻塞线程和唤醒线程,如 AQS 的底层实现用到 LockSupport.p...

    java进阶架构师
  • 关保笔记(三):安全防护

    安全防护是关保标准中的第二个环节,也是重点环节,基于等保开展定级、备案、测评、建设、整改和自查工作。此外,在以下8个领域提出要求:

    FB客服

扫码关注云+社区

领取腾讯云代金券