HAWQ取代传统数仓实践（七）——维度表技术之维度子集

用户1148526

发布于 2018-01-03 15:49:42

1.3K0

发布于 2018-01-03 15:49:42

有些需求不需要最细节的数据。例如更想要某个月的销售汇总，而不是某天的数据。再比如相对于全部的销售数据，可能对某些特定状态的数据更感兴趣等。此时事实数据需要关联到特定的维度，这些特定维度包含在从细节维度选择的行中，所以叫维度子集。维度子集比细节维度的数据少，因此更易使用，查询也更快。

有时称细节维度为基本维度，维度子集为子维度，基本维度表与子维度表具有相同的属性或内容，称这样的维度表具有一致性。一致的维度具有一致的维度关键字、一致的属性列名字、一致的属性定义以及一致的属性值。如果属性的含义不同或者包含不同的值，维度表就不是一致的。

子维度是一种一致性维度，由基本维度的列与行的子集构成。当构建聚合事实表，或者需要获取粒度级别较高的数据时，通常用到子维度。对基本维度和子维度表来说，属性是公共的，其标识和定义相同，两个表中的值相同，然而，基本维度和子维度表的主键是不同的。还有另外一种情况，就是当两个维度具有同样粒度级别的细节数据，但其中一个仅表示行的部分子集时，也需要一致性维度子集。

ETL数据流应当根据基本维度建立一致性子维度，而不是独立于基本维度，以确保一致性。本篇中将准备两个特定子维度，月份维度与Pennsylvania州客户维度。它们均取自现有的维度，月份维度是日期维度的子集，Pennsylvania州客户维度是客户维度的子集。

一、建立包含属性子集的子维度

1. 建立月份维度表

-- 设置模式查找路径  
set search_path to tds;  

-- 建立月份维度表  
create table month_dim (      
    month_sk bigserial,       
    month smallint,    
    month_name varchar(9),    
    quarter smallint,    
    year smallint   
);   
  
comment on table month_dim is '月份维度表';  
comment on column month_dim.month_sk is '月份维度代理键';  
comment on column month_dim.month is '月份';  
comment on column month_dim.month_name is '月份名称';  
comment on column month_dim.quarter is '季度';  
comment on column month_dim.year is '年份';

2. 初始装载月份维度数据

本示例中，以下语句将生成252条月份数据。

insert into month_dim (month, month_name, quarter, year)
select distinct month, month_name, quarter, year 
  from date_dim
 order by year, month
 limit 99999999999999; 

analyze month_dim;

3. 建立追加日期数据的函数

该函数用于向日期维度表和月份维度表追加数据。如果日期所在的月份没在月份维度中，那么该月份会被装载到月份维度中。

create or replace function fn_append_date (end_dt date)    
returns void as    
$$    
declare    
    v_date date;   
    v_datediff int;  
begin    
    select max(date) + 1 into v_date from date_dim;
	v_datediff := end_dt - v_date;
	
    for i in 0 .. v_datediff  
    loop      
        insert into date_dim(date, month, month_name, quarter, year)    
        values(v_date, extract(month from v_date), to_char(v_date,'mon'), extract(quarter from v_date), extract(year from v_date));   
        v_date := v_date + 1;  
    end loop;  
    analyze date_dim;  
	
    insert into month_dim (month, month_name, quarter, year)
    select * from 
    (select distinct month, month_name, quarter, year 
       from date_dim
     except all 
     select month, month_name, quarter, year
       from month_dim) t
     order by year, month
     limit 99999999999999; 
    analyze month_dim;
	
end;    
$$    
language plpgsql;

4. 测试追加日期数据的函数

执行以下语句追加生成一年的日期数据。

select fn_append_date(date '2021-12-31');

执行下面的查询可以看到，日期维度表新增2021年的365条记录。

select * from date_dim where date > date '2020-12-31' order by date;

执行下面的查询可以看到，月份维度表新增2021年的12条记录，如图1所示。

select * from month_dim where year > 2020 order by year,month;

图1

二、建立包含行子集的子维度

当两个维度处于同一细节粒度，但是其中一个仅仅是行的子集时，会产生另外一种一致性维度构造子集。例如，销售订单示例中，客户维度表包含多个州的客户信息。对于不同州的销售分析可能需要浏览客户维度的子集，需要分析的维度仅包含部分客户数据。通过使用行的子集，不会破坏整个客户集合。当然，与该子集连接的事实表必须被限制在同样的客户子集中。

月份维度是一个上卷维度，包含基本维度的上层数据。而特定维度子集是选择基本维度的行子集。执行下面的脚本建立特定维度表，并导入Pennsylvania (PA)客户维度子集数据。

1. 建立PA客户维度表

create table pa_customer_dim
(customer_sk bigserial,   
 customer_number int,      
 customer_name varchar(50),      
 customer_street_address varchar(50),      
 customer_zip_code int,      
 customer_city varchar(30),      
 customer_state varchar(2),    
 isdelete boolean default false,   
 version int,    
 effective_date date,    
 shipping_address varchar(50), 
 shipping_zip_code int,
 shipping_city varchar(30),
 shipping_state varchar(2));

comment on table pa_customer_dim is 'PA客户维度表';  
comment on column pa_customer_dim.customer_sk is '客户维度代理键';  
comment on column pa_customer_dim.customer_number is '客户编号';  
comment on column pa_customer_dim.customer_name is '客户姓名';  
comment on column pa_customer_dim.customer_street_address is '客户地址';  
comment on column pa_customer_dim.customer_zip_code is '客户邮编';  
comment on column pa_customer_dim.customer_city is '客户所在城市';  
comment on column pa_customer_dim.customer_state is '客户所在省份';  
comment on column pa_customer_dim.isdelete is '是否删除';  
comment on column pa_customer_dim.version is '版本';  
comment on column pa_customer_dim.effective_date is '生效日期';
comment on column pa_customer_dim.shipping_address is '送货地址';  
comment on column pa_customer_dim.shipping_zip_code is '送货邮编';  
comment on column pa_customer_dim.shipping_city is '送货城市';  
comment on column pa_customer_dim.shipping_state is '送货省份';

注意，PA客户维度子集与月份维度子集有两点区别：

pa_customer_dim表和customer_dim表有完全相同的列，而month_dim不包含date_dim表的日期列。
pa_customer_dim表的代理键就是客户维度的代理键，而month_dim表里的月份维度代理键并不来自日期维度，而是独立生成的。

2. 修改定期装载函数

通常在基本维度表装载数据后，进行包含其行子集的子维度表的数据装载。因此修改定期装载函数fn_regular_load，增加对PA客户维度的处理，修改后的fn_regular_load函数如下：

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 * 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;    
    
    -- 装载order维度      
    insert into order_dim (order_number, version, effective_date)     
    select t.order_number, t.v, t.effective_date      
      from (select order_number, 1 v, order_date effective_date       
              from rds.sales_order, rds.cdc_time       
             where entry_date >= last_load and entry_date < current_load) t;    
    
    -- 装载销售订单事实表      
    insert into sales_order_fact      
    select order_sk,      
           customer_sk,      
           product_sk,      
           date_sk,    
           year * 100 + month,         
           order_amount,  
           order_quantity            
      from rds.sales_order a,      
           order_dim b,      
           v_customer_dim_his c,      
           v_product_dim_his d,      
           date_dim e,      
           rds.cdc_time f      
     where a.order_number = b.order_number      
       and 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 a.entry_date >= f.last_load and a.entry_date < f.current_load;                  
    
    -- 分析tds模式的表    
    analyze customer_dim;    
    analyze product_dim;    
    analyze order_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;

上面的函数在处理完客户维度表后，装载PA客户维度。每次重新覆盖pa_customer_dim表中的所有数据。先用truncate table语句清空表，然后用insert into ... select语句，从客户维度表中选取Pennsylvania州的数据，并插入到pa_customer_dim表中。

3. 测试定期数据装载函数

（1）执行下面的SQL脚本往客户源数据里添加一个PA的客户和四个OH的客户。

use source;  
insert into customer  
(customer_name, customer_street_address, customer_zip_code,   
 customer_city, customer_state, shipping_address,  
 shipping_zip_code, shipping_city, shipping_state)  
values  
('pa customer', '1111 louise dr.', '17050', 
'mechanicsburg', 'pa', '1111 louise dr.', 
'17050', 'mechanicsburg', 'pa'),  
('bigger customers', '7777 ridge rd.', '44102', 
'cleveland', 'oh', '7777 ridge rd.', 
'44102', 'cleveland', 'oh'),   
('smaller stores', '8888 jennings fwy.', '44102', 
'cleveland', 'oh', '8888 jennings fwy.', 
'44102', 'cleveland', 'oh'),  
('small-medium retailers', '9999 memphis ave.', '44102', 
'cleveland', 'oh', '9999 memphis ave.', 
'44102', 'cleveland', 'oh'),  
('oh customer', '6666 ridge rd.', '44102', 
'cleveland', 'oh', '6666 ridge rd.', 
'44102','cleveland', 'oh') ;  
commit;

以上代码在一条insert into ... values语句中插入多条数据，这种语法是MySQL对标准SQL语法的扩展。

（2）使用下面的命令执行定期装载。

~/regular_etl.sh

（3）使用下面的查询验证结果，pa_customer_dim表增加了20条记录，如图2所示。

select customer_name, customer_state, version, effective_date
  from tds.pa_customer_dim;

图2

三、使用视图实现维度子集

为了实现维度子集，我们创建了新的子维度表，修改了日期数据预装载和ETL定期装载脚本，并进行了测试。除了需要较大的工作量，这种实现方式还有两个主要问题，一是需要额外的存储空间，因为新创建的子维度是物理表；二是存在数据不一致的潜在风险。本质上，只要相同的数据存储多份，就会有数据不一致的可能。这也就是为什么在数据库设计时要强调规范化以最小化数据冗余的原因之一。为了解决这些问题，还有一种常用的做法是在基本维度上建立视图生成子维度。下面是创建子维度视图的SQL语句。

-- 建立月份维度视图
create view v_month_dim as
select row_number() over (order by t1.year,t1.month) month_sk, t1.*
from (select distinct month, month_name, quarter, year 
from date_dim) t1;

-- 建立PA维度视图
create view v_pa_customer_dim as 
select *    
from customer_dim    
where customer_state = 'pa';

-- 建立PA维度当前视图
create view v_pa_customer_dim_latest as
select * 
  from v_customer_dim_latest 
 where customer_state = 'pa';
 
-- 建立PA维度历史视图
create view v_pa_customer_dim_his as
select * 
  from v_customer_dim_his 
 where customer_state = 'pa';

这种方法的主要优点是：实现简单，只要创建视图，不需要修改原来脚本中的逻辑；不占用存储空间，因为视图不真正存储数据；消除了数据不一致的可能，因为数据只有一份。虽然优点很多，但此方法的缺点也十分明显：当基本维度表和子维度表的数据量相差悬殊时，性能会比物理表差得多；如果定义视图的查询很复杂，并且视图很多的话，可能会对元数据存储系统造成压力，严重影响查询性能。

注意视图是与存储无关的纯粹的逻辑对象，HAWQ不支持物化视图。当查询引用了一个视图，视图的定义被评估后产生一个行集，用作查询后续的处理。这只是一个概念性的描述，实际上，作为查询优化的一部分，HAWQ可能把视图的定义和查询结合起来考虑，而不一定是先生成视图所定义的行集。例如，优化器可能将查询的过滤条件下推到视图中。

一旦视图建立，它的结构就是固定的，之后底层表的结构改变，如添加字段等，不会反映到视图的结构中。如果底层表被删除了，或者表结构改变成一种与视图定义不兼容的形式，视图将变为无效状态，其上的查询将失败。

视图是只读的，不能对视图使用LOAD或INSERT语句装载数据，但可以使用alter view语句修改视图的某些元数据。视图定义中可以包含order by和limit子句，例如，如果一个视图定义中指定了limit 5，而查询语句为select * from v limit 10，那么至多会返回5行记录。

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2017年05月24日，如有侵权请联系 cloudcommunity@tencent.com 删除

数据处理

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

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

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

数据处理

登录后参与评论

0 条评论

热度