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 条评论
登录 后参与评论

相关文章

来自专栏WindCoder

网易MySQL微专业学习笔记(十一)-MySQL业务优化与设计

这个系列属于个人学习网易云课堂MySQL数据库工程师微专业的相关课程过程中的笔记,本篇为其“MySQL业务优化与设计”中的MySQL数据类型相关笔记。

431
来自专栏性能与架构

建立索引后的代价到底有多大?

前几天写的文章“MySQL 性能优化案例:覆盖索引”,介绍了使用覆盖索引优化查询的方式,受到了一个网友的批评 批评的内容为: “直接从索引放回数据很快是个常...

3287
来自专栏逸鹏说道

维护索引(4)——通过重组索引提高性能

前言: 如果碎片程度小于30%,建议使用重组而不是重建。因为重组不会锁住数据页或者数据表,并且降低CPU的资源。 总得来说,重组会清空当前的B-TREE,特别是...

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

一则备库CPU报警的思考(r7笔记第69天)

今天收到一封报警邮件,这引起了我的注意。当然过了一会,有收到了CPU使用率恢复的邮件。 报警邮件内容如下: ZABBIX-监控系统: ---------...

3063
来自专栏Albert陈凯

Hive性能优化统计每日IP CREATE TABLE ip_2014_12_29 AS SELECT COUNT(DISTINCT ip) AS IP FROM logdfs WHERE logda

Hive性能优化 1.概述   继续《那些年使用Hive踩过的坑》一文中的剩余部分,本篇博客赘述了在工作中总结Hive的常用优化手段和在工作中使用Hive出现...

3395
来自专栏desperate633

第一课 了解sql准备样例表sql初探

首先阅读这本书,必须先了解这本书里sql语句所操作的数据库表。 也就是书附录中的样例表,深入理解这个样例表不仅可以帮助我们设计好的数据库结构,也能打下基础,更...

892
来自专栏BeJavaGod

网站平台架构演变史(四) - 水平拆分的查询

之前在讲表拆分的时候氛围垂直拆分和水平拆分 垂直拆分的查询其实不难,就是从单表变为了多表,而大部分情况下只是对主表的查询多,从表的查询会很少用到,这样的情况下关...

2637
来自专栏数据和云

【新书连载】诊断Cache buffers chains案例一则

题记:这是某移动运营商在SQL线下审核项目中,协助开发商完善数据库性能的过程。以往开发商遇到此问题总是怀疑是数据库的Bug,试图尝试重启Tuxedo、Weblo...

3338
来自专栏美团技术团队

数据库运维必读的10个问题

本文中的问题精选自上期【你问我答】——数据库专题中读者的提问。【你问我答】是由美团点评技术团队推出的线上问答服务,你在工作学习中遇到的各种技术问题,都可以通过我...

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

Oracle表中含有255列以上时需要注意的(r12笔记第77天)

今天看JL(Jonathan Lewis)的一篇文章,真是费了不少的脑细胞,玩Oracle几十年的老司机,看问题的角度和深度果然不一样,当时看他的大作《O...

35810

扫码关注云+社区