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