HAWQ取代传统数仓实践(十八)——层次维度

原创 2017年06月12日 17:34:36

一、层次维度简介

        大多数维度都具有一个或多个层次。例如,示例数据仓库中的日期维度就有一个四级层次:年、季度、月和日。这些级别用date_dim表里的列表示。日期维度是一个单路径层次,因为除了年-季度-月-日这条路径外,它没有任何其它层次。为了识别数据仓库里一个维度的层次,首先要理解维度中列的含义,然后识别两个或多个列是否具有相同的主题。例如,年、季度、月和日具有相同的主题,因为它们都是关于日期的。具有相同主题的列形成一个组,组中的一列必须包含至少一个组内的其它成员(除了最低级别的列),如在前面提到的组中,月包含日。这些列的链条形成了一个层次,例如,年-季度-月-日这个链条是一个日期维度的层次。除了日期维度,邮编维度中的地理位置信息,产品维度的产品与产品分类,也都构成层次关系。表1显示了三个维度的层次。

zip_code_dim

product_dim

date_dim

zip_code

product_name

date

city

product_category

month

state

 

quarter

 

 

year

表1

        本篇描述处理层次关系的方法,包括在固定深度的层次上进行分组和钻取查询,多路径层次和参差不齐层次的处理等,从最基本的情况开始讨论。

二、固定深度的层次

        固定深度层次是一种一对多关系,例如,一年中有四个季度,一个季度包含三个月等等。当固定深度层次定义完成后,层次就具有固定的名称,层次级别作为维度表中的不同属性出现。只要满足上述条件,固定深度层次就是最容易理解和查询的层次关系,固定层次也能够提供可预测的、快速的查询性能。可以在固定深度层次上进行分组和钻取查询。
        分组查询是把度量按照一个维度的一个或多个级别进行分组聚合。下面的脚本是一个分组查询的例子。该查询按产品(product_category列)和日期维度的三个层次级别(year、quarter和month列)分组返回销售金额。
select product_category,year,quarter,month,sum(order_amount) s_amount  
  from 	v_sales_order_fact a,product_dim b,date_dim c    
 where 	a.product_sk = b.product_sk    
   and 	a.year_month = c.year * 100 + c.month    
 group by product_category, year, quarter, month    
 order by product_category, year, quarter, month; 
        这是一个非常简单的分组查询,结果输出的每一行度量(销售订单金额)都沿着年-季度-月的层次分组。
        与分组查询类似,钻取查询也把度量按照一个维度的一个或多个级别进行分组。但与分组查询不同的是,分组查询只显示分组后最低级别、即本例中月级别上的度量,而钻取查询显示分组后维度每一个级别的度量。下面使用两种方法进行钻取查询,结果显示了每个日期维度级别,即年、季度和月各级别的订单汇总金额。
-- 使用union all   
select product_category, time, order_amount  
  from (select product_category,   
               case when sequence = 1 then 'year: '||time
                    when sequence = 2 then 'quarter: '||time 
                    else 'month: '||time  
               end time,  
               order_amount, 
               sequence, 
               date 
          from (select product_category, min(date) date, year time, 1 sequence, sum(order_amount) order_amount  
                  from v_sales_order_fact a, product_dim b, date_dim c    
                 where a.product_sk = b.product_sk    
                   and a.year_month = c.year * 100 + c.month    
                 group by product_category , year  
                 union all   
                select product_category, min(date) date, quarter time, 2 sequence, sum(order_amount) order_amount  
                  from v_sales_order_fact a, product_dim b, date_dim c    
                 where a.product_sk = b.product_sk    
                   and a.year_month = c.year * 100 + c.month    
                 group by product_category , year , quarter   
                 union all   
                select product_category, min(date) date, month time, 3 sequence, sum(order_amount) order_amount  
                  from v_sales_order_fact a, product_dim b, date_dim c    
                 where a.product_sk = b.product_sk    
                   and a.year_month = c.year * 100 + c.month    
                 group by product_category , year , quarter , month) x) y
 order by product_category , date , sequence , time;

-- 使用grouping sets
select product_category,   
       case when gid = 3 then 'year: '||year  
            when gid = 1 then 'quarter: '||quarter  
            else 'month: '||month  
        end time,  
       order_amount
  from (select product_category, year, quarter, month, min(date) date, sum(order_amount) order_amount,grouping(product_category,year,quarter,month) gid
          from v_sales_order_fact a, product_dim b, date_dim c    
         where a.product_sk = b.product_sk 
           and a.year_month = c.year * 100 + c.month   
         group by grouping sets ((product_category,year,quarter,month),(product_category,year,quarter),(product_category,year))) x
 order by product_category , date , gid desc, time;
        以上两种不同写法的查询语句结果相同,如图1所示。
图1

        第一条语句的子查询中使用union all集合操作将年、季度、月三个级别的汇总数据联合成一个结果集。注意union all的每个查询必须包含相同个数和类型的字段。附加的min(date)和sequence导出列用于对输出结果排序显示。这种写法使用标准的SQL语法,具有通用性。
        第二条语句使用HAWQ提供的grouping函数和group by grouping sets子句。Grouping set就是对列出的每一个字段组进行group by操作,如果字段组为空,则不进行分组处理。因此该语句会生成按产品类型、年、季度、月;类型、年、季度;类型、年分组的聚合数据行。
        grouping(<column> [, …])函数用于区分查询结果中的null值是属于列本身的还是聚合的结果行。该函数为每个参数产生一位0或1,1代表结果行是聚合行,0表示结果行是正常分组数据行。函数值使用了位图策略(bitvector,位向量),即它的二进制形式中的每一位表示对应列是否参与分组,如果某一列参与了分组,对应位就被置为1,否则为0。最后将二进制数转换为十进制数返回。通过这种方式可以区分出数据本身中的null值。

三、多路径层次

        多路径层次是对单路径层次的扩展。现在数据仓库的月维度只有一条层次路径,即年-季度-月这条路径。现在增加一个新的“促销期”级别,并且加一个新的年-促销期-月的层次路径。这时月维度将有两条层次路径,因此是多路径层次维度。下面的脚本给month_dim表添加一个叫做campaign_session的新列,并建立rds.campaign_session过渡表。
alter table tds.month_dim add column campaign_session varchar(30) default null;
comment on column tds.month_dim.campaign_session is '促销期';  

create table rds.campaign_session 
(campaign_session varchar(30),month smallint,year smallint);

        假设所有促销期都不跨年,并且一个促销期可以包含一个或多个月份,但一个月份只能属于一个促销期。为了理解促销期如何工作,表2给出了一个促销期定义的示例。

促销期

月份

2017 年第一促销期

1月—4

2017 年第二促销期

5月—7

2017 年第三促销期

8

2017 年第四促销期

9月—12


表2

        每个促销期有一个或多个月。一个促销期也许并不是正好一个季度,也就是说,促销期级别不能上卷到季度,但是促销期可以上卷至年级别。假设2017年促销期的数据如下,并保存在/home/gpadmin/campaign_session.csv文件中。
2016 First Campaign,1,2017  
2017 First Campaign,2,2017  
2017 First Campaign,3,2017  
2017 First Campaign,4,2017  
2017 Second Campaign,5,2017  
2017 Second Campaign,6,2017  
2017 Second Campaign,7,2017  
2017 Third Campaign,8,2017  
2017 Last Campaign,9,2017  
2017 Last Campaign,10,2017  
2017 Last Campaign,11,2017  
2017 Last Campaign,12,2017  
        现在可以执行下面的脚本把2017年的促销期数据装载进月维度。
copy rds.campaign_session from '/home/gpadmin/campaign_session.csv' with delimiter ',';  

set search_path = tds;
 
create table tmp as   
select t1.month_sk month_sk,
       	t1.month month1,  
       	t1.month_name month_name, 
	    t1.quarter quarter,  
       	t1.year year1,		
       	t2.campaign_session campaign_session    
  from 	month_dim t1 
  left join rds.campaign_session t2 on t1.year = t2.year 
   and 	t1.month = t2.month;  
truncate table month_dim;
insert into month_dim select * from tmp;
drop table tmp;
        此时查询月份维度表,可以看到2017年的促销期已经有数据,其它年份的campaign_session字段值为null,如图2所示。
图2

        注意:
  • 本地文件必须在HAWQ master主机上的本地目录中。
  • copy命令需要使用gpadmin用户执行。

四、参差不齐的层次

        在一个或多个级别上没有数据的层次称为不完全层次。例如在特定月份没有促销期,那么月维度就具有不完全促销期层次。下面是一个不完全促销期的例子,数据存储在ragged_campaign.csv文件中。2017年1月、4月、6月、9月、10月、11月和12月没有促销期。
,1,2017  
2017 Early Spring Campaign,2,2017  
2017 Early Spring Campaign,3,2017  
,4,2017  
2017 Spring Campaign,5,2017  
,6,2017  
2017 Last Campaign,7,2017  
2017 Last Campaign,8,2017  
,9,2017  
,10,2017  
,11,2017  
,12,2017 
        重新向month_dim表装载促销期数据。
truncate table rds.campaign_session;
copy rds.campaign_session from '/home/gpadmin/ragged_campaign.csv' with delimiter ','; 

set search_path = tds;

create table tmp as   
select t1.month_sk month_sk,
       	t1.month month1,  
       	t1.month_name month_name, 
	    t1.quarter quarter,  
       	t1.year year1,		
       	null campaign_session   
  from 	month_dim t1; 

truncate table month_dim; 

insert into month_dim 
select t1.month_sk,  
       	t1.month1,  
       	t1.month_name,         	  
       	t1.quarter,  
       	t1.year1,
        case when t2.campaign_session != '' then t2.campaign_session 
            	  else t1.month_name 
       	      end campaign_session
  from tmp t1 left join rds.campaign_session t2 
on t1.year1 = t2.year and t1.month1 = t2.month;  

drop table tmp; 
        在有促销期的月份,campaign_session列填写促销期名称,而对于没有促销期的月份,该列填写月份名称。轻微参差不齐层次没有固定的层次深度,但层次深度有限。如地理层次深度通常包含3到6层。与其使用复杂的机制构建难以预测的可变深度层次,不如将其变换为固定深度位置设计,针对不同的维度属性确立最大深度,然后基于业务规则放置属性值。

        下面的语句查询年-促销期-月层次,查询结果如图3所示。

select product_category,       
       case when gid = 3 then cast(year as varchar(10))      
        when gid = 1 then campaign_session     
        else month_name
     end time,   
     order_amount    
  from (select product_category, 
                year, 
                campaign_session, 
                month, 
                month_name, 
                sum(month_order_amount) order_amount, 
                sum(month_order_quantity) order_quantity,
                grouping(product_category,year,campaign_session,month) gid,
                min(month) min_month			   
       from v_month_end_sales_order_fact a, product_dim b, month_dim c        
          where a.product_sk = b.product_sk     
            and a.year_month = c.year * 100 + c.month  
            and c.year = 2017                
          group by grouping sets ((product_category,year,campaign_session,month,month_name),(product_category,year,campaign_session),(product_category,year))) x    
 order by product_category, min_month, gid desc, month;   

图3

        min_month列用于排序。在有促销期月份的路径,月级别行的汇总与促销期级别的行相同。而对于没有促销期的月份,其促销期级别的行与月级别的行相同。也就是说,在没有促销期级别的月份,月上卷了它们自己。例如,2017年6月没有促销期,所以在输出看到,每种产品分类有两个相同的6月的行,其中后一行是月份级别的行,前一行表示是没有促销期的行。对于没有促销期的月份,促销期行的销售订单金额(输出里的order_amount列)与月分行的相同。

五、递归

        HAWQ不支持递归查询,但支持递归函数,具体可参考“HAWQ技术解析(十) —— 过程语言”中的“UDF实例——递归树形遍历”。
版权声明:本文为博主原创文章,未经博主允许不得转载。

HAWQ取代传统数仓实践(八)——维度表技术之角色扮演维度

单个物理维度可以被事实表多次引用,每个引用连接逻辑上存在差异的角色维度。例如,事实表可以有多个日期,每个日期通过外键引用不同的日期维度,原则上每个外键表示不同的日期维度视图,这样引用具有不同的含义。这...
  • wzy0623
  • wzy0623
  • 2017年05月25日 15:47
  • 3500

HAWQ取代传统数仓实践(二)——搭建示例模型(MySQL、HAWQ)

一、业务场景        本系列实验将应用HAWQ数据库,为一个销售订单系统建立数据仓库。本篇说明示例的业务场景、数据仓库架构、实验环境、源和目标库的建立过程、测试数据和日期维度的生成。后面陆续进行...
  • wzy0623
  • wzy0623
  • 2017年05月11日 15:31
  • 5698

HAWQ取代传统数仓实践(三)——初始ETL(Sqoop、HAWQ)

一、用sqoop用户建立初始抽取脚本        本示例要用Sqoop将MySQL的数据抽取到HDFS上的指定目录,然后利用HAWQ外部表功能将HDFS数据文件装载到内部表中。表1汇总了示例中维度表...
  • wzy0623
  • wzy0623
  • 2017年05月12日 11:00
  • 3179

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

退化维度技术减少维度的数量,简化维度数据仓库模式。简单的模式比复杂的更容易理解,也有更好的查询性能。        有时,维度表中除了业务主键外没有其它内容。例如,在本销售订单示例中,订单维度表除了订...
  • wzy0623
  • wzy0623
  • 2017年05月26日 13:29
  • 2505

HAWQ取代传统数仓实践(五)——自动调度工作流(Oozie、Falcon)

一旦数据仓库开始使用,就需要不断从源系统给数据仓库提供新数据。为了确保数据流的稳定,需要使用所在平台上可用的任务调度器来调度ETL定期执行。调度模块是ETL系统必不可少的组成部分,它不但是数据仓库的基...
  • wzy0623
  • wzy0623
  • 2017年05月18日 17:35
  • 4618

HAWQ取代传统数仓实践(六)——增加列

业务的扩展或变化是不可避免的,尤其像互联网行业,需求变更已经成为常态,唯一不变的就是变化本身,其中最常碰到的扩展是给一个已经存在的表曾加列。        以销售订单为例,假设因为业务需要,在操作型源...
  • wzy0623
  • wzy0623
  • 2017年05月23日 17:18
  • 3269

HAWQ取代传统数仓实践(十二)——维度表技术之分段维度

一、分段维度简介        在客户维度中,最具有分析价值的属性就是各种分类,这些属性的变化范围比较大。对某个个体客户来说,可能的分类属性包括:性别、年龄、民族、职业、收入和状态,例如,新客户、活跃...
  • wzy0623
  • wzy0623
  • 2017年06月01日 11:42
  • 3693

HAWQ取代传统数仓实践(十九)——OLAP

一、OLAP简介1. 概念        OLAP是英文是On-Line Analytical Processing的缩写,意为联机分析处理。此概念最早由关系数据库之父E.F.Codd于1993年提出...
  • wzy0623
  • wzy0623
  • 2017年06月14日 13:37
  • 3492

HAWQ取代传统数仓实践(一)——为什么选择HAWQ

为了跟上所谓“大数据”技术的脚步,从两年前开始着手实践各种SQL-on-Hadoop技术,从最初的Hive,到SparkSQL,再到Impala,进行了一系列ETL、CDC、多维数据仓库、OLAP的实...
  • wzy0623
  • wzy0623
  • 2017年05月10日 16:24
  • 8833

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

有些需求不需要最细节的数据。例如更想要某个月的销售汇总,而不是某天的数据。再比如相对于全部的销售数据,可能对某些特定状态的数据更感兴趣等。此时事实数据需要关联到特定的维度,这些特定维度包含在从细节维度...
  • wzy0623
  • wzy0623
  • 2017年05月24日 14:21
  • 3523
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:HAWQ取代传统数仓实践(十八)——层次维度
举报原因:
原因补充:

(最多只允许输入30个字)