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

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

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

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

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

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

一、退化订单维度

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

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏java工会

MVC设计模式

MVC模式(Model-View-Controller)是软件工程中的一种软件架构模式,把软件系统分为三个基本部分:模型(Model)、视图(View)和控制器...

820
来自专栏杨建荣的学习笔记

MySQL删除数据的简单尝试 (r7笔记第98天)

在Oracle里面对于数据清理,如果是非分区表,目前我经常的处理思路是下面三个。 第一种是中规中矩,做好备份,然后开始清理,当然这种情况只是说明数据清理的部分,...

2765
来自专栏禁心尽力

数据库设计

杨鑫奇数据库设计经验之谈 一个成功的管理系统,是由:[50% 的业务 + 50% 的软件] 所组成,而 50% 的成功软件又有 [25% 的数据库 + 25% ...

1798
来自专栏铭毅天下

Elasticsearch聚合后分页深入详解

1、Elasticsearch支持聚合后分页吗,为什么? 不支持,看看Elasticsearch员工如何解读。 ? ? 这个问题,2014年在gith...

59111
来自专栏CSDN技术头条

漫谈千亿级数据优化实践:一次数据优化实录

即使没有数据倾斜,千亿级的数据查询对于系统也是一种巨大负担,对于数据开发来说,如何来优化它,既是挑战,也是机遇!

30410
来自专栏架构师之路

1对多业务,数据库水平切分架构一次搞定 | 架构师之路

本文将以“帖子中心”为例,介绍“1对多”类业务,随着数据量的逐步增大,数据库性能显著降低,数据库水平切分相关的架构实践: 如何来实施水平切分 水平切分后常见的问...

32410
来自专栏北京马哥教育

一网打尽当下NoSQL类型、适用场景及使用公司

对比传统关系型数据库,NoSQL有着更为复杂的分类——键值、面向文档、列存储以及图数据库。这里就带你一览NoSQL各种类型的适用场景及一些知名公司的方案选择。 ...

3255
来自专栏IMWeb前端团队

使用Mocha测试node应用

[TOC] 前言 在使用node开发iconfont平台时,由于没有产品与设计的主导,我遇到了协同开发的一大难题——合并代码。开发过程中每次合并代码时基本上都有...

20010
来自专栏CDA数据分析师

分享 | 8条数据清洗经验,收藏备用!

文 | Philip Guo 来自Chaoslog 平时习惯了在某些特定的数据集合上做实验,简单的tokenization、预处理等步骤就足够了。但是在数据...

1945
来自专栏喔家ArchiSelf

NoSQL 之于大数据

关系型数据库几乎是许多开发者和DBA对于传统三层架构应用的唯一选择。使用这一场景有很多原因,数据建模方法,查询语言与数据交互,保证数据的一致性部署,并能够为复杂...

553

扫码关注云+社区