前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >HAWQ取代传统数仓实践(九)——维度表技术之退化维度

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

作者头像
用户1148526
发布2018-01-03 16:48:36
2.2K0
发布2018-01-03 16:48:36
举报
文章被收录于专栏:Hadoop数据仓库

        退化维度技术减少维度的数量,简化维度数据仓库模式。简单的模式比复杂的更容易理解,也有更好的查询性能。

        有时,维度表中除了业务主键外没有其它内容。例如,在本销售订单示例中,订单维度表除了订单号,没有任何其它属性,而订单号是事务表的主键,这种维度就是退化维度。业务系统中的主键通常是不允许修改的。销售订单只能新增,不能修改已经存在的订单号,也不会删除订单记录。因此订单维度表也不会有历史数据版本问题。退化维度常见于事务和累计快照事实表中。

        销售订单事实表中的每行记录都包括作为退化维度的订单号代理键。在操作型系统中,销售订单表是最细节事务表,订单号是订单表的主键,每条订单都可以通过订单号定位,订单中的其它属性,如客户、产品等,都依赖于订单号。也就是说,订单号把与订单属性有关的表联系起来。但是,在维度模型中,事实表中的订单号代理键通常与订单属性的其它表没有关联。可以将订单事实表所有关心的属性分类到不同的维度中,例如,订单日期关联到日期维度,客户关联到客户维度等。在事实表中保留订单号最主要的原因是用于连接数据仓库与操作型系统,它也可以起到事实表主键的作用。某些情况下,可能会有一个或两个属性仍然属于订单而不属于其它维度。当然,此时订单维度就不再是退化维度了。

        退化维度通常被保留作为操作型事务的标识符。实际上可以将订单号作为一个属性加入到事实表中。这样订单维度就没有数据仓库需要的任何数据,此时就可以退化订单维度。需要把退化维度的相关数据迁移到事实表中,然后删除退化的维度。

        注意,操作型事务中的控制号码,例如,订单号码、发票号码、提货单号码等通常产生空的维度并且表示为事务事实表中的退化维度。

一、退化订单维度

        使用维度退化技术时先要识别数据,分析从来不用的数据列。例如,订单维度的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表

        下面的脚本完成所有退化订单维度所需的步骤。

代码语言:javascript
复制
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。但这只是一种特殊情况,通常代理键和业务主键的值是不同的,因此这里依然使用标准的方式重新生成数据。

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

        退化一个维度后需要做的另一件事就是修改定期数据装载函数。需要把订单号加入到销售订单事实表,而不再需要导入订单维度。修改后的函数如下。

代码语言:javascript
复制
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脚本在源库中增加两条销售订单记录。

代码语言:javascript
复制
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. 执行定期装载函数并查看结果

代码语言:javascript
复制
~/regular_etl.sh

        脚本执行成功后,查询sales_order_fact表,验证新增的两条订单是否正确装载。

代码语言:javascript
复制
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

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2017年05月26日,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 一、退化订单维度
  • 二、修改定期数据装载函数
  • 三、测试
    • 1. 准备测试数据
      • 2. 执行定期装载函数并查看结果
      领券
      问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档