OushuDB入门（七）——OLAP篇

一、OLAP简介

1. 概念

        OLAP是英文是On-Line Analytical Processing的缩写，意为联机分析处理。此概念最早由关系数据库之父E.F.Codd于1993年提出。OLAP允许以一种称为多维数据集的结构，访问业务数据源经过聚合和组织整理后的数据。以此为标准，OLAP作为单独的一类技术同联机事务处理（On-Line Transaction Processing，OLTP）得以明显区分。

        在计算领域，OLAP是一种快速应答多维分析查询的方法，也是商业智能的一个组成部分，与之相关的概念还包括数据仓库、报表系统、数据挖掘等。数据仓库用于数据的存储和组织，OLAP集中于数据的分析，数据挖掘则致力于知识的自动发现，报表系统则侧重于数据的展现。OLAP系统从数据仓库中的集成数据出发，构建面向分析的多维数据模型，再使用多维分析方法从多个不同的视角对多维数据集合进行分析比较，分析活动以数据驱动。通过使用OLAP工具，用户可以从多个视角交互式地查询多维数据。

        OLAP由三个基本的分析操作构成：合并（上卷）、下钻和切片。合并是指数据的聚合，即数据可以在一个或多个维度上进行累积和计算。例如，所有的营业部数据被上卷到销售部门以分析销售趋势。下钻是一种由汇总数据向下浏览细节数据的技术。比如用户可以从产品分类的销售数据下钻查看单个产品的销售情况。切片则是这样一种特性，通过它用户可以获取OLAP立方体中的特定数据集合，并从不同的视角观察这些数据。这些观察数据的视角就是我们所说的维度。例如通过经销商、日期、客户、产品或区域等等，查看同一销售事实。

        OLAP系统的核心是OLAP立方体，或称为多维立方体或超立方体。它由被称为度量的数值事实组成，这些度量被维度划分归类。一个OLAP立方体的例子如图1所示，数据单元位于立方体的交叉点上，每个数据单元跨越产品、时间、地区等多个维度。通常使用一个矩阵接口操作OLAP立方体，例如电子表格程序的数据透视表，可以按维度分组执行聚合或求平均值等操作。立方体的元数据一般由关系数据库中的星型模式或雪花模式生成，度量来自事实表的记录，维度来自维度表。

图1

2. 分类

        通常可以将联机分析处理系统分为MOLAP、ROLAP、HOLAP三种类型。
（1）MOLAP
        MOLAP（multi-dimensional online analytical processing）是一种典型的OLAP形式，甚至有时就被用来表示OLAP。MOLAP将数据存储在一个经过优化的多维数组中，而不是存储在关系数据库中。某些MOLAP工具要求预先计算并存储计算后的结果数据，这种操作方式被称为预处理。MOLAP工具一般将预计算后的数据集合作为一个数据立方体使用。对于给定范围的问题，立方体中的数据包含所有可能的答案。预处理的好处是可以对问题作出非常快速地响应。然而另一方面，依赖于预计算的聚合程度，装载新数据可能会花费很长的时间。另外还有些MOLAP工具，尤其是那些实现了某些数据库功能的MOLAP工具，并不预先计算原始数据，而是在需要时才进行计算。

        MOLAP的优点：

• 优化的数据存储、多维数据索引和缓存带来的快速查询性能。
• 相对于关系数据库，可以通过压缩技术，使数据存储需要更小的磁盘空间。
• MOLAP工具一般能够自动进行高级别的数据聚合。
• 对于低基数维度的数据集合是紧凑的。
• 数组模型提供了原生的索引功能。

        MOLAP的缺点：

• 某些MOLAP解决方案中的处理步骤可能需要很长的时间，尤其是当数据量很大时。要解决这个问题，通常只能增量处理变化的数据，而不是预处理整个数据集合。
• 可能引入较多的数据冗余。

        MOLAP产品：
        商业的MOLAP产品主要有Cognos Powerplay、Oracle Database OLAP Option、MicroStrategy、Microsoft Analysis Services、Essbase等。

（2）ROLAP
        ROLAP直接使用关系数据库存储数据，不需要执行预计算。基础的事实数据及其维度表作为关系表被存储，而聚合信息存储在新创建的附加表中。ROLAP以数据库模式设计为基础，操作存储在关系数据库中的数据，实现传统的OLAP数据切片和分块功能。本质上讲，每种数据切片或分块行为都等同于在SQL语句中增加一个“WHERE”子句的过滤条件。ROLAP不使用预计算的数据立方体，取而代之的是查询标准的关系数据库表，返回回答问题所需的数据。与预计算的MOLAP不同，ROLAP工具有能力回答任意相关的数据分析问题，因为该技术不受立方体内容的限制。通过ROLAP还能够下钻到数据库中存储的最细节的数据。

        由于ROLAP使用关系数据库，通常数据库模式必须经过仔细设计。为OLTP应用设计的数据库不能直接作为ROLAP数据库使用，这种投机取巧的做法并不能使ROLAP良好工作。因此ROLAP仍然需要创建额外的数据拷贝。但不管怎样，ROLAP毕竟用的是数据库，各种各样的数据库设计与优化技术都可以被有效利用。

        ROLAP的优点：

• 在处理大量数据时，ROLAP更具可伸缩性，尤其是当模型中包含的维度具有很高的基数，例如，维度表中有上百万的成员时。
• 有很多可选用的数据装载工具，并且能够针对特定的数据模型精细调整ETL代码，数据装载所需时间通常比自动化的MOLAP装载少的多。
• 因为数据存储于标准关系数据库中，可以使用SQL报表工具访问数据，而不必是专有的OLAP工具。
• ROLAP更适合处理非聚合的事实，例如文本型描述。在MOLAP工具中查询文本型元素时性能会相对较差。
• 通过将数据存储从多维模型中解耦出来，相对于用使用严格的维度模型，这种更普通的关系模型增加了成功建模的可能性。
• ROLAP方法可以利用数据库的权限控制，例如通过行级安全性设置，可以用事先设定的条件过滤查询结果。例如Oracle的VPD技术，能够根据连接的用户自动在查询的SQL语句中拼接WHERE谓词条件。

        ROLAP的缺点：

• 业界普遍认为ROLAP工具比MOLAP查询速度慢。
• 聚合表的数据装载必须由用户自己定制的ETL代码控制。ROLAP工具不能自动完成这个任务，这意味着额外的开发工作量。
• 如果跳过创建聚合表的步骤，查询性能会大打折扣，因为不得不查询大量的细节数据表。虽然可以通过适当建立聚合表缓解性能问题，但对所有维度表及其属性的组合创建聚合表是不切实际的。
• ROLAP依赖于针对通用查询或缓存目标的数据库，因此并没有提供某些MOLAP工具所具有的特殊技术，如透视表等。但是现代ROLAP工具可以利用SQL语言中的CUBE、ROLLUP操作或其它SQL OLAP扩展。随着这些SQL扩展的逐步完善，MOLAP工具的优势也不那么明显了。
• 因为ROLAP工具的所有计算都依赖于SQL，对于某些不易转化为SQL的计算密集型模型，ROLAP不再适用。例如包含预算、拨款等条目的复杂财务报表或地理位置计算的场景。

        ROLAP产品：
        使用ROLAP的商业产品包括Microsoft Analysis Services、MicroStrategy、SAP Business Objects、Oracle Business Intelligence Suite Enterprise Edition、 Tableau Software等等。也有开源的ROLAP服务器，如Mondrian。

（3）HOLAP
        在额外的ETL开发成本与缓慢的查询性能之间难以选择，正是因为这种情况，现在大部分商业OLAP工具都使用一种混合型（Hybrid）方法，它允许模型设计者决定哪些数据存储在MOLAP中，哪些数据存储在ROLAP中。除了把数据划分成传统关系型存储和专有存储，业界对混合型OLAP并没有清晰的定义。例如，某些厂商的HOLAP数据库使用关系表存储大量的细节数据，而是用专用表保存少量的聚合数据。HOLAP结合了MOLAP和ROLAP两种方法的优点，可以同时利用预计算的多维立方体和关系数据源。HOLAP有以下两种划分数据的策略。

• 垂直分区。这种模式的HOLAP将聚合数据存储在MOLAP中，以支持良好的查询性能，而把细节数据存储在ROLAP中以减少立方体处理所需时间。
• 水平分区。这种模式的HOLAP按数据热度划分，将某些最近使用的数据分片存储在MOLAP中，而将老的数据存储在ROLAP。

3. 性能

        OLAP分析所需的原始数据量是非常庞大的。一个分析模型，往往会涉及数千万或数亿条甚至更多的数据，而且分析模型中包含多个维度数据，这些维度又可以由用户作任意的组合。这样的结果就是大量的实时运算导致过长的响应时间。想象一个1000万条记录的分析模型，如果一次提取4个维度进行组合分析，每个维度有10个不同的取值，理论上的运算次数将达到10的12次方。这样的运算量将导致数十分钟乃至更长的等待时间。如果用户对维组合次序进行调整，或增加、或减少某些维度的话，又将是一个重新的计算过程。

        从上面的分析中可以得出结论，如果不能解决OLAP运算效率问题的话，OLAP将只会是一个没有实用价值的概念。在OLAP的发展历史中，常见的解决方案是用多维数据库代替关系数据库设计，将数据根据维度进行最大限度的聚合运算，运算中会考虑到各种维度组合情况，运算结果将生成一个数据立方体，并保存在磁盘上，用这种预运算方式提高OLAP的速度。例如Kylin就是使用这种以空间换时间的方式来提高查询速度，而OushuDB在性能上的优势，也使它较为适合OLAP应用。OushuDB不同文件格式的性能对比，参见“ OushuDB入门（二）——性能篇”。

二、交互查询与图形化显示

1. Zeppelin简介

        Zeppelin是一个基于Web的软件，用于交互式地数据分析。它一开始是Apache软件基金会的孵化项目，2016年5月正式成为顶级项目。Zeppelin描述自己是一个可以进行数据摄取、数据发现、数据分析、数据可视化的笔记本，用以帮助开发者、数据科学家以及相关用户更有效地处理数据，而不必使用复杂的命令行，也不必关心集群的实现细节。Zeppelin的架构如图2所示。

图2

        从上图中可以看到，Zeppelin具有客户端/服务器架构，客户端一般就是指浏览器。服务器接收客户端的请求，并将请求通过Thrift协议发送给翻译器组。翻译器组物理表现为JVM进程，负责实际处理客户端的请求并与服务器进行通信。

        翻译器是一个插件式的体系结构，允许任何语言或后端数据处理程序以插件的形式添加到Zeppelin中。特别需要指出的是，Zeppelin内建Spark翻译器，因此不需要构建单独的模块、插件或库。翻译器的架构如图3所示。

图3

        当前的Zeppelin已经支持很多翻译器，如Zeppelin 0.6.0版本自带的翻译器有alluxio、cassandra、file、hbase、ignite、kylin、md、phoenix、sh、tajo、angular、elasticsearch、flink、hive、jdbc、lens、psql、spark等18种之多。插件式架构允许用户在Zeppelin中使用自己熟悉的特定程序语言或数据处理方式。例如，通过使用%spark翻译器，可以在Zeppelin中使用Scala语言代码。

        在数据可视化方面，Zeppelin已经包含一些基本的图表，如柱状图、饼图、线形图、散点图等，任何支持的后端语言输出都可以被图形化表示。

        在Zeppelin中，用户建立的每一个查询叫做一个note，note的URL在多用户间共享，Zeppelin将向所有用户实时广播note的变化。Zeppelin还提供一个只显示查询结果的URL，该页不包括任何菜单和按钮。用这种方式可以方便地将结果页作为一帧嵌入到自己的web站点中。

2. 使用Zeppelin执行OushuDB查询

（1）安装Zeppelin
        HDP 2.5.3安装包中已经集成了Zeppelin 0.6.0，因此不需要单独进行复杂的安装配置，只要启动Zeppelin服务就可以了。

（2）配置Zeppelin支持OushuDB
        Zeppelin 0.6.0通过JDBC翻译器解析OushuDB查询，只需进行简单的配置即可，步骤如下。

• 在Ambari控制台主页面中，点击Services -> Zeppelin Notebook -> Quick Links -> Zeppelin UI，打开Zeppelin UI主页面。
• 在Zeppelin UI主页面中，点击anonymous -> interpreter，进入翻译器页面。
• 点击edit编辑jdbc翻译器，配置default.driver、default.password、default.url、default.user四个属性的值，我的配置如图4所示。

图4

        配置好后点击Save保存配置，然后点击restart重启jdbc翻译器，至此配置完成。

（3）在Zeppelin中执行OushuDB查询
        点击Notebook -> Create new note，新建一个note，在其中输入查询语句，如：

%jdbc  
select * from sales_order order by order_number;

        运行结果的表格、柱状图、饼图、堆叠图、线形图分别如图5-图9所示。

图5 

图6 

图7 

图8 

图9 

        一个note中可以独立执行多个查询语句。图形显示可以根据不同的“settings”联机分析不同的指标。报表有default、simple、report三种可选样式。例如，报表样式的饼图表示如图10所示。

图10

        可以点击如图11红框中所示的链接单独引用此报表。

图11

        单独的页面能根据查询或设置的修改而实时变化，比如将Values由customer_number列改为product_code列，饼图表变为图12的样子。

图12

        单独链接的页面也随之自动发生变化，如图13所示。

图13

        Zeppelin支持联机输入变量值，例如，要查询某一年的销售情况，查询语句改为：

%jdbc  
select * 
  from sales_order 
 where to_char(entry_date,'yyyy') = '${year}'
 order by order_number;

        在运行查询时会在页面中出现一个输入框，填入适当的变量值运行查询，如图14所示。

图14

        甚至可以动态定义查询的列，例如查询语句改为：

%jdbc  
select ${checkbox:fields=customer_number, customer_number|product_code},*
  from sales_order 
 where to_char(entry_date,'yyyy') = '${year}'
 order by order_number;

        查询运行时出现字段复选框，如图15所示。

图15

        

三、OLAP实例

        要做好OLAP类的应用，需要对业务数据有深入的理解。只有了解了业务，才能知道需要分析哪些指标，从而有的放矢地剖析相关数据，得出可信的结论来辅助决策。下面就以销售订单数据仓库为例，提出若干问题，然后使用OushuDB查询数据以回答这些问题：

1. 每种商品类型以及单个商品的累积销售额是多少？
2. 每种商品类型销售额同比如何？
3. 客户年消费金额的平均数和中位数是多少？
4. 客户年消费金额分布处于25%、50%、75%位置的消费金额是多少？
5. 每类商品中销售金额排在前三位的商品是什么？
6. 所有商品的销售百分比排名？

1. 每种商品类型以及单个商品的累积销售额是多少？

        使用OushuDB的group by rollup求小计和总计。

%jdbc
select t2.product_category, t2.product_name, sum(order_amount)  
  from sales_order_fact t1, product_dim t2   
 where t1.product_sk = t2.product_sk  
 group by rollup (t2.product_category, t2.product_name)  
 order by t2.product_category, t2.product_name;  

        结果如图16所示。

图16

 

2. 每种商品类型销售额同比如何？

%jdbc
select t1.product_category,    
       t1.year*100+t1.month year_month,   
       sum(t1.order_amount) amount_cur, 
	   sum(t2.order_amount) amount_pre,
       round(((sum(t1.order_amount) - sum(t2.order_amount)) / sum(t2.order_amount))::numeric,2) pct_amount 	   
  from 
(select d.year,d.month,p.product_category,p.product_sk,sum(s.order_amount) order_amount 
   from sales_order_fact s, date_dim d, product_dim p 
  where s.order_date_sk = d.date_sk and s.product_sk = p.product_sk 
  group by d.year,d.month,p.product_category,p.product_sk) t1,
(select d.year,d.month,p.product_category,p.product_sk,sum(s.order_amount) order_amount 
   from sales_order_fact s, date_dim d, product_dim p 
  where s.order_date_sk = d.date_sk and s.product_sk = p.product_sk 
  group by d.year,d.month,p.product_category,p.product_sk) t2
 where t1.product_sk = t2.product_sk
   and t1.year = t2.year+1
   and t1.month = t2.month
 group by t1.product_category, t1.year*100+t1.month
 order by t1.product_category, t1.year*100+t1.month; 

        结果如图17所示。

图17

 

3. 客户年消费金额的平均数和中位数是多少？

        分别使用两种方法求得平均数和中位数。OushuDB为分析型应用提供了丰富的聚合函数。

%jdbc
select round(avg(sum_order_amount)::numeric,2) avg_amount,   
       round((sum(sum_order_amount)/count(customer_sk))::numeric,2) avg_amount1,   
       percentile_cont(0.5) within group (order by sum_order_amount) median_amount,  
       median(sum_order_amount) median_amount1  
  from (select customer_sk,sum(order_amount) sum_order_amount  
          from sales_order_fact  
         group by customer_sk) t1;

        结果如图18所示。

图18 

 

4. 客户年消费金额分布处于25%、50%、75%位置的消费金额是多少？

%jdbc
select percentile_cont(0.25) within group (order by sum_order_amount desc) max_amount_25,  
       percentile_cont(0.50) within group (order by sum_order_amount desc) max_amount_50,  
       percentile_cont(0.75) within group (order by sum_order_amount desc) max_amount_75  
  from (select customer_sk,sum(order_amount) sum_order_amount  
          from sales_order_fact 		  
         group by customer_sk) t1; 

        结果如图19所示。

图19 

 

5. 每类商品中销售金额排在前三位的商品是什么？

        使用OushuDB提供的窗口函数row_number()，按商品类型分区，按销售额倒序，取得销售排名。

%jdbc
select case when t1.rn =1 then t1.product_category end product_category,   
       t1.product_name,   
       t1.sum_order_amount,   
       t1.rn    
  from (select product_category,   
               product_name,    
               sum_order_amount,    
               row_number() over (partition by product_category order by sum_order_amount desc) rn   
          from (select t2.product_category,    
                       t2.product_sk, 
                       t2.product_name,					   
                       sum(order_amount) sum_order_amount    
                  from sales_order_fact t1, product_dim t2  
                 where t1.product_sk = t2.product_sk   
                 group by t2.product_category, t2.product_sk, t2.product_name) t) t1      
 where t1.rn <= 3    
 order by t1.product_category, t1.rn; 

        结果如图20所示。

图20 

 

6. 所有商品的销售百分比排名？

%jdbc
select product_name,   
       sum_order_amount,  
       round(percent_rank() over (order by sum_order_amount desc)::numeric,2) rank  
  from (select product_sk,sum(order_amount) sum_order_amount  
          from sales_order_fact   
         group by product_sk) t1, product_dim t2  
 where t1.product_sk = t2.product_sk  
 order by rank; 

        结果如图21所示。

图21 

参考：

• HAWQ取代传统数仓实践（十九）——OLAP
• https://zeppelin.apache.org/docs/latest/manual/dynamicform.html