HAWQ取代传统数仓实践(九)——维度表技术之退化维度

原创 2017年05月26日 13:29:04
        退化维度技术减少维度的数量,简化维度数据仓库模式。简单的模式比复杂的更容易理解,也有更好的查询性能。
        有时,维度表中除了业务主键外没有其它内容。例如,在本销售订单示例中,订单维度表除了订单号,没有任何其它属性,而订单号是事务表的主键,这种维度就是退化维度。业务系统中的主键通常是不允许修改的。销售订单只能新增,不能修改已经存在的订单号,也不会删除订单记录。因此订单维度表也不会有历史数据版本问题。退化维度常见于事务和累计快照事实表中。
        销售订单事实表中的每行记录都包括作为退化维度的订单号代理键。在操作型系统中,销售订单表是最细节事务表,订单号是订单表的主键,每条订单都可以通过订单号定位,订单中的其它属性,如客户、产品等,都依赖于订单号。也就是说,订单号把与订单属性有关的表联系起来。但是,在维度模型中,事实表中的订单号代理键通常与订单属性的其它表没有关联。可以将订单事实表所有关心的属性分类到不同的维度中,例如,订单日期关联到日期维度,客户关联到客户维度等。在事实表中保留订单号最主要的原因是用于连接数据仓库与操作型系统,它也可以起到事实表主键的作用。某些情况下,可能会有一个或两个属性仍然属于订单而不属于其它维度。当然,此时订单维度就不再是退化维度了。
        退化维度通常被保留作为操作型事务的标识符。实际上可以将订单号作为一个属性加入到事实表中。这样订单维度就没有数据仓库需要的任何数据,此时就可以退化订单维度。需要把退化维度的相关数据迁移到事实表中,然后删除退化的维度。
        注意,操作型事务中的控制号码,例如,订单号码、发票号码、提货单号码等通常产生空的维度并且表示为事务事实表中的退化维度。

一、退化订单维度

        使用维度退化技术时先要识别数据,分析从来不用的数据列。例如,订单维度的order_number列就可能是这样的一列。但如果用户想看事务的细节,还需要订单号。因此,在退化订单维度前,要把订单号迁移到sales_order_fact事实表。图1显示了修改后的模式。
图1

        按顺序执行下面的四步退化order_dim维度表:
  • 给sales_order_fact表添加order_number列
  • 把order_dim表里的订单号迁移到sales_order_fact表
  • 删除sales_order_fact表里的order_sk列
  • 删除order_dim表
        下面的脚本完成所有退化订单维度所需的步骤。
set search_path=tds;

alter table sales_order_fact rename to sales_order_fact_old; 
create table sales_order_fact as
select t2.order_number,
       t1.customer_sk,
       t1.product_sk,
       t1.order_date_sk,
       t1.year_month,
       t1.order_amount,
       t1.order_quantity,
       t1.request_delivery_date_sk  
  from sales_order_fact_old t1 inner join order_dim t2 on t1.order_sk = t2.order_sk;

comment on table sales_order_fact is '销售订单事实表';  
comment on column sales_order_fact.order_number is '订单号';  
comment on column sales_order_fact.customer_sk is '客户维度代理键';  
comment on column sales_order_fact.product_sk is '产品维度代理键';  
comment on column sales_order_fact.order_date_sk is '日期维度代理键';  
comment on column sales_order_fact.year_month is '年月分区键';  
comment on column sales_order_fact.order_amount is '销售金额';  
comment on column sales_order_fact.order_quantity is '数量';  
comment on column sales_order_fact.request_delivery_date_sk is '请求交付日期代理键';  

drop table sales_order_fact_old;  
drop table order_dim;
        HAWQ没有提供UPDATE功能,因此要更新已有数据的订单号,只能重新装载所有数据。在本例中,订单号维度表中代理键和订单号业务主键的值相同,其实可以简单地将事实表的order_sk字段改名为order_number。但这只是一种特殊情况,通常代理键和业务主键的值是不同的,因此这里依然使用标准的方式重新生成数据。

二、修改定期数据装载函数

        退化一个维度后需要做的另一件事就是修改定期数据装载函数。需要把订单号加入到销售订单事实表,而不再需要导入订单维度。修改后的函数如下。
create or replace function fn_regular_load ()          
returns void as          
$$          
declare          
    -- 设置scd的生效时间        
    v_cur_date date := current_date;            
    v_pre_date date := current_date - 1;        
    v_last_load date;        
begin        
    -- 分析外部表        
    analyze ext.customer;        
    analyze ext.product;        
    analyze ext.sales_order;        
        
    -- 将外部表数据装载到原始数据表        
    truncate table rds.customer;          
    truncate table rds.product;         
        
    insert into rds.customer select * from ext.customer;         
    insert into rds.product select * from ext.product;        
    insert into rds.sales_order   
    select order_number,  
           customer_number,  
           product_code,  
           order_date,  
           entry_date,  
           order_amount,  
           order_quantity,  
           request_delivery_date   
      from ext.sales_order;        
            
    -- 分析rds模式的表        
    analyze rds.customer;        
    analyze rds.product;        
    analyze rds.sales_order;        
        
    -- 设置cdc的上限时间        
    select last_load into v_last_load from rds.cdc_time;        
    truncate table rds.cdc_time;        
    insert into rds.cdc_time select v_last_load, v_cur_date;        
        
    -- 装载客户维度        
    insert into tds.customer_dim        
    (customer_number,        
     customer_name,        
     customer_street_address,        
     customer_zip_code,        
     customer_city,        
     customer_state,      
     shipping_address,       
     shipping_zip_code,       
     shipping_city,       
     shipping_state,        
     isdelete,        
     version,        
     effective_date)        
    select case flag         
                when 'D' then a_customer_number        
                else b_customer_number        
            end customer_number,        
           case flag         
                when 'D' then a_customer_name        
                else b_customer_name        
            end customer_name,        
           case flag         
                when 'D' then a_customer_street_address        
                else b_customer_street_address        
            end customer_street_address,        
           case flag         
                when 'D' then a_customer_zip_code        
                else b_customer_zip_code        
            end customer_zip_code,        
           case flag         
                when 'D' then a_customer_city        
                else b_customer_city        
            end customer_city,        
           case flag         
                when 'D' then a_customer_state        
                else b_customer_state        
            end customer_state,        
           case flag         
                when 'D' then a_shipping_address        
                else b_shipping_address        
            end shipping_address,      
           case flag         
                when 'D' then a_shipping_zip_code        
                else b_shipping_zip_code        
            end shipping_zip_code,        
           case flag         
                when 'D' then a_shipping_city        
                else b_shipping_city        
            end shipping_city,        
           case flag         
                when 'D' then a_shipping_state        
                else b_shipping_state        
            end shipping_state,      
           case flag         
                when 'D' then true        
                else false        
            end isdelete,        
           case flag         
                when 'D' then a_version        
                when 'I' then 1        
                else a_version + 1        
            end v,        
           v_pre_date        
      from (select a.customer_number a_customer_number,        
                   a.customer_name a_customer_name,        
                   a.customer_street_address a_customer_street_address,        
                   a.customer_zip_code a_customer_zip_code,        
                   a.customer_city a_customer_city,        
                   a.customer_state a_customer_state,       
                   a.shipping_address a_shipping_address,        
                   a.shipping_zip_code a_shipping_zip_code,        
                   a.shipping_city a_shipping_city,        
                   a.shipping_state a_shipping_state,       
                   a.version a_version,        
                   b.customer_number b_customer_number,        
                   b.customer_name b_customer_name,        
                   b.customer_street_address b_customer_street_address,        
                   b.customer_zip_code b_customer_zip_code,        
                   b.customer_city b_customer_city,        
                   b.customer_state b_customer_state,       
                   b.shipping_address b_shipping_address,        
                   b.shipping_zip_code b_shipping_zip_code,        
                   b.shipping_city b_shipping_city,        
                   b.shipping_state b_shipping_state,       
                   case when a.customer_number is null then 'I'        
                        when b.customer_number is null then 'D'        
                        else 'U'         
                    end flag        
              from v_customer_dim_latest a         
              full join rds.customer b on a.customer_number = b.customer_number         
             where a.customer_number is null -- 新增        
                or b.customer_number is null -- 删除        
                or (a.customer_number = b.customer_number         
                    and not         
                           (coalesce(a.customer_name,'') = coalesce(b.customer_name,'')         
                        and coalesce(a.customer_street_address,'') = coalesce(b.customer_street_address,'')         
                        and coalesce(a.customer_zip_code,0) = coalesce(b.customer_zip_code,0)        
                        and coalesce(a.customer_city,'') = coalesce(b.customer_city,'')         
                        and coalesce(a.customer_state,'') = coalesce(b.customer_state,'')      
                        and coalesce(a.shipping_address,'') = coalesce(b.shipping_address,'')         
                        and coalesce(a.shipping_zip_code,0) = coalesce(b.shipping_zip_code,0)        
                        and coalesce(a.shipping_city,'') = coalesce(b.shipping_city,'')         
                        and coalesce(a.shipping_state,'') = coalesce(b.shipping_state,'')      
                        ))) t        
             order by coalesce(a_customer_number, 999999999999), b_customer_number limit 999999999999;        
     
    -- 重载PA客户维度      
    truncate table pa_customer_dim;        
    insert into pa_customer_dim        
    select customer_sk,       
           customer_number,          
           customer_name,          
           customer_street_address,          
           customer_zip_code,          
           customer_city,          
           customer_state,        
           isdelete,       
           version,        
           effective_date,        
           shipping_address,     
           shipping_zip_code,    
           shipping_city,    
           shipping_state       
      from customer_dim        
     where customer_state = 'pa';       
    
    -- 装载产品维度        
    insert into tds.product_dim        
    (product_code,        
     product_name,        
     product_category,             
     isdelete,        
     version,        
     effective_date)        
    select case flag         
                when 'D' then a_product_code        
                else b_product_code        
            end product_code,        
           case flag         
                when 'D' then a_product_name        
                else b_product_name        
            end product_name,        
           case flag         
                when 'D' then a_product_category        
                else b_product_category        
            end product_category,        
           case flag         
                when 'D' then true        
                else false        
            end isdelete,        
           case flag         
                when 'D' then a_version        
                when 'I' then 1        
                else a_version + 1        
            end v,        
           v_pre_date        
      from (select a.product_code a_product_code,        
                   a.product_name a_product_name,        
                   a.product_category a_product_category,        
                   a.version a_version,        
                   b.product_code b_product_code,        
                   b.product_name b_product_name,        
                   b.product_category b_product_category,                       
                   case when a.product_code is null then 'I'        
                        when b.product_code is null then 'D'        
                        else 'U'         
                    end flag        
              from v_product_dim_latest a         
              full join rds.product b on a.product_code = b.product_code         
             where a.product_code is null -- 新增        
                or b.product_code is null -- 删除        
                or (a.product_code = b.product_code         
                    and not         
                           (a.product_name = b.product_name         
                        and a.product_category = b.product_category))) t        
             order by coalesce(a_product_code, 999999999999), b_product_code limit 999999999999;        

    -- 装载销售订单事实表          
    insert into sales_order_fact          
    select a.order_number,          
           customer_sk,          
           product_sk,          
           e.date_sk,        
           e.year * 100 + e.month,             
           order_amount,      
           order_quantity,  
           f.date_sk             
      from rds.sales_order a,         
           v_customer_dim_his c,          
           v_product_dim_his d,          
           date_dim e,   
           date_dim f,           
           rds.cdc_time g          
     where a.customer_number = c.customer_number          
       and a.order_date >= c.effective_date        
       and a.order_date < c.expiry_date           
       and a.product_code = d.product_code          
       and a.order_date >= d.effective_date        
       and a.order_date < d.expiry_date           
       and date(a.order_date) = e.date    
       and date(a.request_delivery_date) = f.date    
       and a.entry_date >= g.last_load and a.entry_date < g.current_load;                      
        
    -- 分析tds模式的表        
    analyze customer_dim;        
    analyze product_dim;        
    analyze sales_order_fact;        
        
    -- 更新时间戳表的last_load字段          
    truncate table rds.cdc_time;        
    insert into rds.cdc_time select v_cur_date, v_cur_date;        
        
end;          
$$          
language plpgsql;
        函数做了以下两点修改:
  • 去掉装载和分析order_dim维度表的语句。
  • 事实表中的order_number字段字节从rds.sales_order表获得。

三、测试


1. 准备测试数据

        执行下面的SQL脚本在源库中增加两条销售订单记录。
use source;   

set @start_date := unix_timestamp('2017-05-25');    
set @end_date := unix_timestamp('2017-05-25 12:00:00');    
set @order_date := from_unixtime(@start_date + rand() * (@end_date - @start_date)); 
set @request_delivery_date := from_unixtime(unix_timestamp(date_add(current_date, interval 5 day)) + rand() * 86400);      
set @amount := floor(1000 + rand() * 9000);   
set @quantity := floor(10 + rand() * 90);      

insert into sales_order values (null,1,1,@order_date,@request_delivery_date,@order_date,@amount,@quantity);          
 
set @start_date := unix_timestamp('2017-05-25 12:00:01');    
set @end_date := unix_timestamp('2017-05-26');    
set @order_date := from_unixtime(@start_date + rand() * (@end_date - @start_date)); 
set @request_delivery_date := from_unixtime(unix_timestamp(date_add(current_date, interval 5 day)) + rand() * 86400);   
set @amount := floor(1000 + rand() * 9000);   
set @quantity := floor(10 + rand() * 90);      

insert into sales_order values (null,1,1,@order_date,@request_delivery_date,@order_date,@amount,@quantity);          
  
commit ;
        以上语句在源库上生成2017年5月25日的两条销售订单。为了保证自增订单号与订单时间顺序相同,注意一下@order_date变量的赋值。

2. 执行定期装载函数并查看结果

~/regular_etl.sh
        脚本执行成功后,查询sales_order_fact表,验证新增的两条订单是否正确装载。
select a.order_number,   
       customer_name,   
       product_name,   
       e.date,
       f.date,	   
       order_amount amount,
       order_quantity quantity	   
  from sales_order_fact a,
       customer_dim b,  
       product_dim c,   
       date_dim e,
       date_dim f	   
 where a.customer_sk = b.customer_sk 
   and a.product_sk = c.product_sk    
   and a.order_date_sk = e.date_sk 
   and a.request_delivery_date_sk = f.date_sk   
 order by order_number desc
 limit 5;
        查询结果如图2所示,可以看到新增两条记录的订单号被正确装载。
图2
版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

HAWQ取代传统数仓实践(十三)——事实表技术之周期快照

一、周期快照简介        周期快照事实表中的每行汇总了发生在某一标准周期,如一天、一周或一月的多个度量。其粒度是周期性的时间段,而不是单个事务。周期快照事实表通常包含许多数据的总计,因为任何与事...

HAWQ取代传统数仓实践(三)——初始ETL(Sqoop、HAWQ)

一、用sqoop用户建立初始抽取脚本        本示例要用Sqoop将MySQL的数据抽取到HDFS上的指定目录,然后利用HAWQ外部表功能将HDFS数据文件装载到内部表中。表1汇总了示例中维度表...

HAWQ论文笔记

1、背景HAWQ是一个构建在HDFS之上的MPP(massively parallel processing)SQL引擎,不像其他构建在hadoop之上的SQL引擎,HAWQ支持标准SQL,并且完整的...

基于Hadoop生态圈的数据仓库实践 —— 进阶技术(一)

一、增加列         数据仓库最常碰到的扩展是给一个已经存在的维度表和事实表添加列。本节说明如何在客户维度表和销售订单事实表上添加列,并在新列上应用SCD2,以及对定时装载脚本所做的修改。假设需...

HAWQ技术解析(八) —— 大表分区

一、HAWQ中的分区表        与大多数关系数据库一样,HAWQ也支持分区表。这里所说的分区表是指HAWQ的内部分区表,外部分区表在后面“外部数据”篇讨论。在数据仓库应用中,事实表通常有非常多的...

HAWQ取代传统数仓实践(八)——维度表技术之角色扮演维度

单个物理维度可以被事实表多次引用,每个引用连接逻辑上存在差异的角色维度。例如,事实表可以有多个日期,每个日期通过外键引用不同的日期维度,原则上每个外键表示不同的日期维度视图,这样引用具有不同的含义。这...

挑战数据结构与算法面试题——80题全解析(一)

题目来源“数据结构与算法面试题80道”。

HAWQ技术解析(六) —— 定义对象

HAWQ本质上是一个数据库系统,所以这里所说的对象指的是数据库对象。和其它关系数据库类似,HAWQ中有数据库、表空间、表、视图、自定义数据类型、自定义函数、序列等对象。本篇将简述这些对象的创建与管理。...

基于Hadoop生态圈的数据仓库实践 —— 环境搭建(三)

三、建立数据仓库示例模型         Hadoop及其相关服务安装配置好后,下面用一个小而完整的示例说明多维模型及其相关ETL技术在Hadoop上的具体实现。 1. 设计ERD         ...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)