vlambda博客
学习文章列表

导言

物化视图作为一种预计算的优化方式，广泛应用于传统数据库中，如Oracle，MSSQL Server等。随着大数据技术的普及，各类数仓及查询引擎在业务中扮演着越来越重要的数据分析角色，而物化视图作为数据查询的加速器，将极大增强用户在数据分析工作中的使用体验。本文将基于 SparkSQL（2.4.4） + Hive （2.3.6）， 介绍物化视图在SparkSQL中的实现及应用。

什么是物化视图

物化视图主要用于预先计算并保存表连接或聚合等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，从而快速的得到结果。物化视图使用查询重写（query rewrite）机制，不需要修改原有的查询语句，引擎自动选择合适的物化视图进行查询重写，完全对应用透明。它和视图的区别在于，物化视图将存储实际的数据，而视图只是存储SQL语句。使用物化视图的基本流程为：

1. 创建物化视图

2. Query查询

3. 基于物化视图，对Query进行查询重写，生成新的Query

4. 基于新的Query进行查询

如下图，user，item，ui是3张表，先创建物化视图mv，使用Query查询时，将基于mv对Query进行重写，生成新的基于物化视图的Query，再进行查询。这个例子中可以看到，在最终生成的Query里，消除了所有的join操作，将3表join查询转换成了单表查询。对于大数据查询引擎来说，大表join将会产生shuffle过程，是造成查询缓慢的瓶颈之一，这种转换将极大的提升查询效率。

物化视图现状及实践目标

传统数据库，如Oracle，MSSQL Server等都已经支持物化视图，但是在大数据领域里，支持这类预计算优化的有Hive，Kylin，ClickHouse，其中只有Hive是基于物化视图，而Kylin由于并非将数据存储在原有数仓中，而是存储在自定义的介质中（HBase，Parquet等），所以和物化视图还是有很大的差别。基于使用方式，ClickHouse在使用时需要输入物化视图的表名，不能做到查询时对用户透明。

SparkSQL目前有哪些预计算相关的优化呢？

• 已有实现：

• Cache Table

• Reuse Exchange （该优化重用同一个Query中相同的表扫描操作）

• 各大公司的自有实现：

• Relational cache (阿里巴巴)

• eBay, 字节跳动等内部数据平台，在各种场合的分享中，也提到了对SparkSQL进行了物化视图的优化

• Spark社区的相关JIRA（未实现）：

• SPARK-29038: SPIP: Support Spark Materialized View

• SPARK-26764: [SPIP] Spark Relational Cache

• SPARK-29059: [SPIP] Support for Hive Materialized Views in Spark SQL

基于目前现状，我们希望能将物化视图的优化功能加入到SparkSQL中，并使用Hive对相关元数据进行管理。从实现角度看，Hive的实现是基于Calcite，将Hive的plan转换成Calcite的结构，使用Calcite的AbstractMaterializedViewRule进行优化后，再转换回Hive的plan，最终提交给计算引擎进行计算。我们目标是借鉴Calcite的实现方式（基于参考文献【1】），将物化视图的优化整合进Spark Catalyst框架。不引入Calcite的优势是，避免核心功能强依赖于第三方库，便于后续改动及功能增强。

物化视图设计详解

物化视图的功能将拆分为2部分，分别是SparkSQL + Hive整合，Plan Rewrite，其中Plan Rewrite是作为整个功能的核心模块，接下来将分别对这2部分进行描述。

SparkSQL + Hive 整合

为什么选择Hive2.3

因为Hive是在2.3将物化视图功能引入（官网里显示是 3.0.0版本才被引入），虽然这个版本对于物化视图支持还不够完善，但是我们所需要的相关元数据管理已经具备。目前SparkSQL对于Hive的支持只实现到Hive2.3（参考HiveClientImpl），当然，内部Hive3.0还未开始大规模使用也是原因之一。这里需要注意的是，2.3和3.0版本最主要的区别是存储Materialized View的字段不同，在2.3中是存储在View Original Text，而在3.0是存储在View Expanded Text。

基于Hive的物化视图DDL命令，在SparkSQL中新增DDL命令，用来管理物化视图，新增命令如下:

• create materialized view

• drop materialized view

• alter materialized view enable/disable rewrite

• alter materialized view rebuild

由于物化视图本质是一种类型的表，所以desc命令同样适用，无需新增。

在Spark中，HiveShim中并未实现新的物化视图的元数据接口，需要进行实现，实现后的整合如下图：

来看一个实际的例子，使用如下Query创建物化视图后，

 
   
   
 

  
    
    
  
create materialized view mv_q1
  
    
    
  
STORED AS ORC
  
    
    
  
AS
  
    
    
  
SELECT
  
    
    
  
 d_year,
  
    
    
  
 sr_customer_sk AS ctr_customer_sk,
  
    
    
  
 sr_store_sk AS ctr_store_sk,
  
    
    
  
 sum(sr_return_amt) AS ctr_total_return
  
    
    
  
FROM tpcds_bin_partitioned_orc_2.store_returns,
  
    
    
  
 tpcds_bin_partitioned_orc_2.date_dim
  
    
    
  
WHERE sr_returned_date_sk = d_date_sk
  
    
    
  
GROUP BY d_year, sr_customer_sk, sr_store_sk
 
   
   
 

使用desc命令展示物化视图的元数据，结果如下图：

需要关注的是，Table Type和View Original Text，和普通表相比存储了物化视图相关的信息。在整合完成后，SparkSQL和Hive对于物化视图的操作就完全打通，创建等操作互相可见。

Plan Rewrite 设计

设计概览

上图展示了Plan Rewrite功能实现涉及的基本流程，其中，Materialized Optimizer作为单独节点接入到整个SparkSQL流程中，为什么不和其它优化规则一起并入Optimizer？最主要的原因是，单独列出来可以使用explain命令对优化后的LogicalPlan进行检查。物化视图的优化涉及到大量的代码，这样做对于后续的debug也是很有帮助。再回到基本流程，图的下半部分是关于物化视图优化的具体步骤，而SessionCatalog那条路径则是用来从Hive侧获取物化视图的Query。

优化步骤简介

关于优化步骤，简单来说就是提取实际Query和物化视图的相关信息，进行信息相互的匹配，匹配成功后生成新的Query，再将新的Query转换成LogicalPlan并返回，如下图所示：

由于涉及的细节比较多，上图并未全部展开，对于图中列出的信息，其中：

• table: (x, t1), (t1, t1), (t2, t2)：对于每个表，记录（表名，表名）的键值对，如果有别名，则增加（别名，表名）键值对

• output: (t2.id, t2.id), (c, count(1))：对于每个输出列，保存信息方式如表名

• equalPreds: (t1.id, (t2.id)), (t2.id,(t1.id))：（col1，（col2，col3））说明col1，col2，col3是相等的列

• otherPreds: (x.id > 10)：除了列相等以外的查询条件

• groupby: t1.id：groupBy字段

本文由于不会涉及到实现的细节，所以上述的数据结构仅用来让大家了解在实现过程所用到的部分辅助数据结构。在获取到相关信息后，将进行匹配及替换过程，最终生成新的Query并返回LogicalPlan。

优化过程中的问题

基于参考文献【1】，物化视图在优化过程中需要考虑到一系列问题，接下来将例举其中的部分：

列相等问题

查询：

 
   
   
 

  
    
    
  
select qed.deptno as ed
  
    
    
  
from db2.depts qdd join db1.emps qed
  
    
    
  
where qed.deptno = qdd.deptno
  
    
    
  
group by qed.deptno
 
   
   
 

物化视图：

 
   
   
 

  
    
    
  
select dd.deptno as md
  
    
    
  
from db2.depts dd join db1.emps de
  
    
    
  
where de.deptno = dd.deptno
  
    
    
  
group by dd.deptno
 
   
   
 

优化结果：

 
   
   
 

  
    
    
  
select mv_db.testmv.`md` AS `ed`
  
    
    
  
from mv_db.testmv
 
   
   
 

在上面例子中，查询的输出是db1.emps.deptno，物化视图的输出是db2.depts.deptno，但是由于都存在列相等条件db1.emps.deptno = db2.depts.deptno，所以这类场景是可以被优化的。

条件匹配问题

查询：

 
   
   
 

  
    
    
  
select deptno
  
    
    
  
from db1.emps
  
    
    
  
where (deptno >= 0 and deptno < 10)
  
    
    
  
 or (deptno > 10 and deptno <= 20)
  
    
    
  
 or (deptno >= 1 and deptno < 9)
 
   
   
 

物化视图：

 
   
   
 

  
    
    
  
select deptno
  
    
    
  
from db1.emps
  
    
    
  
where (deptno >= 0 and deptno < 10)
  
    
    
  
 or (deptno > 10 and deptno <= 20)
 
   
   
 

优化结果：

 
   
   
 

  
    
    
  
select mv_db.testmv.`deptno` AS `deptno`
  
    
    
  
from mv_db.testmv
  
    
    
  
where (((mv_db.testmv.`deptno` >= 0 AND mv_db.testmv.`deptno` < 10)
  
    
    
  
 OR (mv_db.testmv.`deptno` > 10 AND mv_db.testmv.`deptno` <= 20))
  
    
    
  
 OR (mv_db.testmv.`deptno` >= 1) AND (mv_db.testmv.`deptno` < 9))
 
   
   
 

条件匹配问题的核心其实是物化视图包含的数据是否包含所有查询所需的数据，如果没有，则优化失败。

表达式匹配问题

查询：

 
   
   
 

  
    
    
  
select empid + 1 as empAdd1,
  
    
    
  
 empid as newEmpId
  
    
    
  
from db1.emps
  
    
    
  
where deptno = 10
 
   
   
 

物化视图：

 
   
   
 

  
    
    
  
select empid from db1.emps where deptno = 10
 
   
   
 

优化结果：

 
   
   
 

  
    
    
  
select (mv_db.testmv.`empid` + 1) AS `empAdd1`,
  
    
    
  
 mv_db.testmv.`empid` AS `newEmpId`
  
    
    
  
from mv_db.testmv
 
   
   
 

条件匹配问题用来判断查询所需要的表达式，是否可以通过物化视图的输出列进行计算，查询的表达式不仅包含输出列，还有where语句中存在的表达式。

多表查询问题

我们将通过2个例子来了解下这个问题：查询：

 
   
   
 

  
    
    
  
select depts.deptno
  
    
    
  
from db1.emps join db2.depts on emps.deptno = depts.deptno
 
   
   
 

物化视图：

 
   
   
 

  
    
    
  
select deptno from db1.emps
 
   
   
 

优化结果：

 
   
   
 

  
    
    
  
select db2.depts.`deptno` AS `deptno`
  
    
    
  
from db2.depts, mv_db.testmv
  
    
    
  
where (mv_db.testmv.`deptno` = db2.depts.`deptno`)
 
   
   
 

上述例子中，查询的表的数量大于物化视图，在优化后需要额外join不在物化视图中的表。

查询：

 
   
   
 

  
    
    
  
select depts.deptno
  
    
    
  
from db2.depts, db2.dependents, db2.locations
  
    
    
  
where depts.name = dependents.name
  
    
    
  
 and locations.name = dependents.name
 
   
   
 

物化视图：

 
   
   
 

  
    
    
  
select depts.deptno
  
    
    
  
from db2.depts, db2.dependents, db2.locations, db1.emps
  
    
    
  
where depts.name = dependents.name
  
    
    
  
 and locations.name = dependents.name
  
    
    
  
 and emps.deptno = depts.deptno
 
   
   
 

优化结果：?

在这个例子中，查询的表的数量小于物化视图，这个时候能优化吗？由于SparkSQL不支持主外键模型，所以这个问题系统是无法判断的，需要用户进行判断物化视图的数据是否包含了查询所需的所有数据。我们为这种情况添加了相关参数，默认不支持，但是用户可以根据需要自行开启。

聚合函数问题

我们依然通过2个例子来了解下这个问题：

查询：

 
   
   
 

  
    
    
  
select deptno, count(*) as c1
  
    
    
  
from db1.emps
  
    
    
  
group by deptno
 
   
   
 

物化视图：

 
   
   
 

  
    
    
  
select deptno, count(*) as c
  
    
    
  
from db1.emps
  
    
    
  
group by deptno
 
   
   
 

优化结果：

 
   
   
 

  
    
    
  
select mv_db.testmv.`c` AS `c1`, mv_db.testmv.`deptno` AS `deptno`
  
    
    
  
from mv_db.testmv
 
   
   
 

这个例子中，查询和物化视图的groupBy语句里包含相同的字段，所以优化结果可以直接使用mv_db.testmv.c替换查询里的c1。

查询：

 
   
   
 

  
    
    
  
select name, count(*) as c
  
    
    
  
from db1.emps
  
    
    
  
group by name
 
   
   
 

物化视图：

 
   
   
 

  
    
    
  
select deptno, name, count(*) as c
  
    
    
  
from db1.emps
  
    
    
  
group by deptno, name
 
   
   
 

优化结果：

 
   
   
 

  
    
    
  
select count(mv_db.testmv.`c`) AS `c`,
  
    
    
  
 mv_db.testmv.`name` AS `name`
  
    
    
  
from mv_db.testmv
  
    
    
  
group by mv_db.testmv.`name`
 
   
   
 

这个例子中，查询和物化视图的groupBy语句里包含的字段，所以优化结果对于聚合函数需要做额外的处理。

多个物化视图匹配问题

查询：

 
   
   
 

  
    
    
  
select emps.deptno
  
    
    
  
from db2.depts join db1.emps
  
    
    
  
where emps.deptno = depts.deptno
  
    
    
  
 and emps.deptno > 0
  
    
    
  
group by emps.deptno
 
   
   
 

物化视图1：

 
   
   
 

  
    
    
  
select depts.deptno
  
    
    
  
from db2.depts join db1.emps
  
    
    
  
where emps.deptno = depts.deptno
  
    
    
  
group by depts.deptno
 
   
   
 

物化视图2：

 
   
   
 

  
    
    
  
select deptno from db1.emps
 
   
   
 

物化视图3：

 
   
   
 

  
    
    
  
select depts.deptno
  
    
    
  
from db2.depts join db1.emps
  
    
    
  
where emps.deptno = depts.deptno
 
   
   
 

物化视图4（被选中）：

 
   
   
 

  
    
    
  
select depts.deptno
  
    
    
  
from db2.depts join db1.emps
  
    
    
  
where emps.deptno = depts.deptno
  
    
    
  
 and emps.deptno > 0
  
    
    
  
group by depts.deptno
 
   
   
 

优化结果：

 
   
   
 

  
    
    
  
select mv_db.tmv4.`deptno` AS `deptno`
  
    
    
  
from mv_db.tmv4
 
   
   
 

这个例子展示了当多个物化视图匹配时，会选择较优的物化视图进行优化，如何判断较优目前仅比较优化结果里join和groupby的数量，相同时再比较filter的数量。由于篇幅有限，这里不再一一列出更为细节的问题了。

物化视图实战

本节将基于TPC-DS（100G），Query17，对物化视图的实战能力进行一次测试。

测试用的查询

由于物化视图和查询中同一个Table在from语句里不能出现多次，所以我们对测试用的查询做了一些调整，具体如下：

 
   
   
 

  
    
    
  
调整前：
  
    
    
  
SELECT
  
    
    
  
 i_item_id,
  
    
    
  
 i_item_desc,
  
    
    
  
 s_state,
  
    
    
  
 count(ss_quantity) AS store_sales_quantitycount,
  
    
    
  
 avg(ss_quantity) AS store_sales_quantityave,
  
    
    
  
 stddev_samp(ss_quantity) AS store_sales_quantitystdev,
  
    
    
  
 stddev_samp(ss_quantity) / avg(ss_quantity) AS store_sales_quantitycov,
  
    
    
  
 count(sr_return_quantity) as_store_returns_quantitycount,
  
    
    
  
 avg(sr_return_quantity) as_store_returns_quantityave,
  
    
    
  
 stddev_samp(sr_return_quantity) as_store_returns_quantitystdev,
  
    
    
  
 stddev_samp(sr_return_quantity) / avg(sr_return_quantity) AS store_returns_quantitycov,
  
    
    
  
 count(cs_quantity) AS catalog_sales_quantitycount,
  
    
    
  
 avg(cs_quantity) AS catalog_sales_quantityave,
  
    
    
  
 stddev_samp(cs_quantity) / avg(cs_quantity) AS catalog_sales_quantitystdev,
  
    
    
  
 stddev_samp(cs_quantity) / avg(cs_quantity) AS catalog_sales_quantitycov
  
    
    
  
FROM store_sales, store_returns, catalog_sales, date_dim d1, date_dim d2, date_dim d3, store, item
  
    
    
  
WHERE d1.d_quarter_name = '2001Q1'
  
    
    
  
 AND d1.d_date_sk = ss_sold_date_sk
  
    
    
  
 AND i_item_sk = ss_item_sk
  
    
    
  
 AND s_store_sk = ss_store_sk
  
    
    
  
 AND ss_customer_sk = sr_customer_sk
  
    
    
  
 AND ss_item_sk = sr_item_sk
  
    
    
  
 AND ss_ticket_number = sr_ticket_number
  
    
    
  
 AND sr_returned_date_sk = d2.d_date_sk
  
    
    
  
 AND d2.d_quarter_name IN ('2001Q1', '2001Q2', '2001Q3')
  
    
    
  
 AND sr_customer_sk = cs_bill_customer_sk
  
    
    
  
 AND sr_item_sk = cs_item_sk
  
    
    
  
 AND cs_sold_date_sk = d3.d_date_sk
  
    
    
  
 AND d3.d_quarter_name IN ('2001Q1', '2001Q2', '2001Q3')
  
    
    
  
GROUP BY i_item_id, i_item_desc, s_state
  
    
    
  
ORDER BY i_item_id, i_item_desc, s_state
  
    
    
  
LIMIT 100
  
    
    
  
调整后：
  
    
    
  
SELECT
  
    
    
  
 i_item_id,
  
    
    
  
 i_item_desc,
  
    
    
  
 s_state,
  
    
    
  
 count(ss_quantity) AS store_sales_quantitycount,
  
    
    
  
 avg(ss_quantity) AS store_sales_quantityave,
  
    
    
  
 stddev_samp(ss_quantity) AS store_sales_quantitystdev,
  
    
    
  
 stddev_samp(ss_quantity) / avg(ss_quantity) AS store_sales_quantitycov,
  
    
    
  
 count(sr_return_quantity) as_store_returns_quantitycount,
  
    
    
  
 avg(sr_return_quantity) as_store_returns_quantityave,
  
    
    
  
 stddev_samp(sr_return_quantity) as_store_returns_quantitystdev,
  
    
    
  
 stddev_samp(sr_return_quantity) / avg(sr_return_quantity) AS store_returns_quantitycov,
  
    
    
  
 count(cs_quantity) AS catalog_sales_quantitycount,
  
    
    
  
 avg(cs_quantity) AS catalog_sales_quantityave,
  
    
    
  
 stddev_samp(cs_quantity) / avg(cs_quantity) AS catalog_sales_quantitystdev,
  
    
    
  
 stddev_samp(cs_quantity) / avg(cs_quantity) AS catalog_sales_quantitycov
  
    
    
  
FROM tpcds_bin_partitioned_orc_100.store_sales,
  
    
    
  
 tpcds_bin_partitioned_orc_100.store_returns,
  
    
    
  
 tpcds_bin_partitioned_orc_100.catalog_sales,
  
    
    
  
 tpcds_bin_partitioned_orc_100.date_dim d1,
  
    
    
  
 tpcds_bin_partitioned_orc_100.store,
  
    
    
  
 tpcds_bin_partitioned_orc_100.item
  
    
    
  
WHERE d1.d_date_sk = ss_sold_date_sk
  
    
    
  
AND i_item_sk = ss_item_sk
  
    
    
  
AND s_store_sk = ss_store_sk
  
    
    
  
AND ss_customer_sk = sr_customer_sk
  
    
    
  
AND ss_item_sk = sr_item_sk
  
    
    
  
AND ss_ticket_number = sr_ticket_number
  
    
    
  
AND sr_customer_sk = cs_bill_customer_sk
  
    
    
  
AND sr_item_sk = cs_item_sk
  
    
    
  
AND d_quarter_name = '2001Q1'
  
    
    
  
GROUP BY i_item_id, i_item_desc, s_state;
 
   
   
 

测试用的物化视图

将创建2个物化视图用来测试，一个是基于Kylin风格，另一个是更灵活的风格。

 
   
   
 

  
    
    
  
物化视图1：
  
    
    
  
create materialized view mv_db.mv_100_t17 AS SELECT
  
    
    
  
 i_item_id,
  
    
    
  
 i_item_desc,
  
    
    
  
 s_state,
  
    
    
  
 d_quarter_name,
  
    
    
  
 count(ss_quantity) AS store_sales_quantitycount,
  
    
    
  
 avg(ss_quantity) AS store_sales_quantityave,
  
    
    
  
 stddev_samp(ss_quantity) AS store_sales_quantitystdev,
  
    
    
  
 stddev_samp(ss_quantity) / avg(ss_quantity) AS store_sales_quantitycov,
  
    
    
  
 count(sr_return_quantity) as_store_returns_quantitycount,
  
    
    
  
 avg(sr_return_quantity) as_store_returns_quantityave,
  
    
    
  
 stddev_samp(sr_return_quantity) as_store_returns_quantitystdev,
  
    
    
  
 stddev_samp(sr_return_quantity) / avg(sr_return_quantity) AS store_returns_quantitycov,
  
    
    
  
 count(cs_quantity) AS catalog_sales_quantitycount,
  
    
    
  
 avg(cs_quantity) AS catalog_sales_quantityave,
  
    
    
  
 stddev_samp(cs_quantity) / avg(cs_quantity) AS catalog_sales_quantitystdev,
  
    
    
  
 stddev_samp(cs_quantity) / avg(cs_quantity) AS catalog_sales_quantitycov
  
    
    
  
FROM tpcds_bin_partitioned_orc_100.store_sales,
  
    
    
  
 tpcds_bin_partitioned_orc_100.store_returns,
  
    
    
  
 tpcds_bin_partitioned_orc_100.catalog_sales,
  
    
    
  
 tpcds_bin_partitioned_orc_100.date_dim d1,
  
    
    
  
 tpcds_bin_partitioned_orc_100.store,
  
    
    
  
 tpcds_bin_partitioned_orc_100.item
  
    
    
  
WHERE d1.d_date_sk = ss_sold_date_sk
  
    
    
  
AND i_item_sk = ss_item_sk
  
    
    
  
AND s_store_sk = ss_store_sk
  
    
    
  
AND ss_customer_sk = sr_customer_sk
  
    
    
  
AND ss_item_sk = sr_item_sk
  
    
    
  
AND ss_ticket_number = sr_ticket_number
  
    
    
  
AND sr_customer_sk = cs_bill_customer_sk
  
    
    
  
AND sr_item_sk = cs_item_sk
  
    
    
  
GROUP BY d_quarter_name, i_item_id, i_item_desc, s_state;
  
    
    
  
物化视图2：
  
    
    
  
create materialized view mv_db.mv_100_t17_2
  
    
    
  
STORED AS ORC
  
    
    
  
AS
  
    
    
  
SELECT
  
    
    
  
 i_item_id,
  
    
    
  
 i_item_desc,
  
    
    
  
 s_state,
  
    
    
  
 d_quarter_name,
  
    
    
  
 ss_quantity,
  
    
    
  
 sr_return_quantity,
  
    
    
  
 cs_quantity
  
    
    
  
FROM tpcds_bin_partitioned_orc_100.store_sales,
  
    
    
  
 tpcds_bin_partitioned_orc_100.store_returns,
  
    
    
  
 tpcds_bin_partitioned_orc_100.catalog_sales,
  
    
    
  
 tpcds_bin_partitioned_orc_100.date_dim d1,
  
    
    
  
 tpcds_bin_partitioned_orc_100.store,
  
    
    
  
 tpcds_bin_partitioned_orc_100.item
  
    
    
  
WHERE d1.d_date_sk = ss_sold_date_sk
  
    
    
  
AND i_item_sk = ss_item_sk
  
    
    
  
AND s_store_sk = ss_store_sk
  
    
    
  
AND ss_customer_sk = sr_customer_sk
  
    
    
  
AND ss_item_sk = sr_item_sk
  
    
    
  
AND ss_ticket_number = sr_ticket_number
  
    
    
  
AND sr_customer_sk = cs_bill_customer_sk
  
    
    
  
AND sr_item_sk = cs_item_sk;
 
   
   
 

这里需要注意的是物化视图1里的输出列和groupby语句里增加了dquartername字段，由于这个变化，所以针对物化视图1的查询将变更为：

 
   
   
 

  
    
    
  
SELECT i_item_id,
  
    
    
  
 i_item_desc,
  
    
    
  
 s_state,
  
    
    
  
 count(ss_quantity) AS store_sales_quantitycount,
  
    
    
  
 avg(ss_quantity) AS store_sales_quantityave,
  
    
    
  
 stddev_samp(ss_quantity) AS store_sales_quantitystdev,
  
    
    
  
 stddev_samp(ss_quantity) / avg(ss_quantity) AS store_sales_quantitycov,
  
    
    
  
 count(sr_return_quantity) as_store_returns_quantitycount,
  
    
    
  
 avg(sr_return_quantity) as_store_returns_quantityave,
  
    
    
  
 stddev_samp(sr_return_quantity) as_store_returns_quantitystdev,
  
    
    
  
 stddev_samp(sr_return_quantity) / avg(sr_return_quantity) AS store_returns_quantitycov,
  
    
    
  
 count(cs_quantity) AS catalog_sales_quantitycount,
  
    
    
  
 avg(cs_quantity) AS catalog_sales_quantityave,
  
    
    
  
 stddev_samp(cs_quantity) / avg(cs_quantity) AS catalog_sales_quantitystdev,
  
    
    
  
 stddev_samp(cs_quantity) / avg(cs_quantity) AS catalog_sales_quantitycov
  
    
    
  
FROM tpcds_bin_partitioned_orc_100.store_sales,
  
    
    
  
 tpcds_bin_partitioned_orc_100.store_returns,
  
    
    
  
 tpcds_bin_partitioned_orc_100.catalog_sales,
  
    
    
  
 tpcds_bin_partitioned_orc_100.date_dim d1,
  
    
    
  
 tpcds_bin_partitioned_orc_100.store,
  
    
    
  
 tpcds_bin_partitioned_orc_100.item
  
    
    
  
WHERE d1.d_date_sk = ss_sold_date_sk
  
    
    
  
AND i_item_sk = ss_item_sk
  
    
    
  
AND s_store_sk = ss_store_sk
  
    
    
  
AND ss_customer_sk = sr_customer_sk
  
    
    
  
AND ss_item_sk = sr_item_sk
  
    
    
  
AND ss_ticket_number = sr_ticket_number
  
    
    
  
AND sr_customer_sk = cs_bill_customer_sk
  
    
    
  
AND sr_item_sk = cs_item_sk
  
    
    
  
AND d_quarter_name = '2001Q1'
  
    
    
  
GROUP BY d_quarter_name, i_item_id, i_item_desc, s_state;
 
   
   
 

逻辑计划比较

下图展现了3种情况下，最终的逻辑执行计划，可以看到物化视图优化后，从多表join已经变成了单表查询，而物化视图2比物化视图1多了Aggregate运算，这个是由groupBy产生。

Spark UI统计比较

下图展示了3种情况下，各自的执行job/stage，注意，测试时开启了Spark Adaptive Execution特性。

性能数据比较

下图展示了3种情况下，各自的耗时数据，其中Spark统计耗时是来自Spark UI，而这个数据向我们展示了物化视图在查询效率方面的可能性，明明计算只用了0.6s，为什么查询需要6s？在追求更快的响应时间时，这些额外的消耗是否能优化呢？比如资源调度耗时等，这个也是作为一个后续的优化方向。

物化视图 VS Kylin

物化视图属于一种基于预计算方式的查询优化，也是我们常说的空间换时间。在大数据领域，提到查询预计算，Kylin肯定会被提及，该项目在各个领域中有了很多的成功应用案例。那么物化视图和Kylin相比，区别在哪里呢？接下来将分别和Kylin的两种结构，做一些比较。比较内容仅限于离线预计算及查询，毕竟Kylin还支持适时建cube等功能。

Kylin on HBase

	Kylin	物化视图
查询性能	计算量少时，毫秒级；计算量大时，由于有单点计算的瓶颈，毫秒降至秒级甚至分钟	计算量少时，比Kylin慢至少1个数量级；计算量大时，接近甚至超过Kylin效率（计算量越大，由于分布式计算的存在，Spark将越有优势）
查询灵活性	在建Cube时，必须将所有度量，维度信息都进行指定，如果要增加新的维度表，重新建Cube。Cube数据存储在Hbase中，不可和Hive中的数据进行混合查询。	物化视图数据存储在Hive中，支持和其它表进行联合查询。如果新增一张维度表，无需重构物化视图。
运维成本	Cube数据存储在HBase中，需要额外维护HBase。建Cube过程会涉及到Hive + Spark + Hbase，包括建Hive宽表，计算cube，生成字典等多个过程。	数据存储在Hive中，无需额外维护其它组件。创建物化视图使用Hive ddl或SparkSQL ddl，过程类似于CTAS。
预计算管理	具备UI界面进行Cube的管理，且对Cube创建具备剪枝等优化	无物化视图管理UI，该功能也不太适合整合进SparkSQL中，用户需自行维护

基于上述比较，物化视图使用更为灵活，方便，也易于维护，但是Kylin在查询效率，特别是计算量不大的查询（如，没有distinct），有着巨大的优势。对于这两者的应用更多要依赖于实际使用场景，比如：

报表类的场景，维度度量都不会经常变化，前端对于延时要求又比较高，那Kylin是一种很好的选择。在上节例子中，mv_100_t17就是模拟Kylin创建物化视图，在目前的SparkSQL实现中，延迟和Kylin差距还是非常大，但是相信经过适当的优化，性能会有很大的提升。

数据探索类的场景，如果在探索的过程中经常会尝试新的数据维度，那么物化视图就能更好的满足这种灵活性的需求。可以想象下这样的状况，分别通过Kylin和物化视图，对于多张事实表和维度表进行预计算，忽然在数据分析过程中，还想增加维度信息。对于Kylin来说，重新建Cube，而物化视图则支持直接查询。当预计算成本很高时，这样的灵活性还是能给分析工作带来便捷的。在上节的例子中，mv_100_t17_2这类物化视图，就能很好的体现这种灵活性。

Kylin自带Cube管理界面，便于用户对Cube进行管理，而物化视图目前需要用户自行搭建管理平台或使用脚本方式，这个在使用时也应该加以考虑。

Kylin on Parquet

从2020/04开始，Kylin社区开始逐步推进下一代架构，Kylin on Parquet，主要的原因是为了解决HBase运维不便，单点计算等问题。在舍弃HBase后，Cube的存储将使用Parquet，而查询将使用Spark引擎。虽然该架构还在推进演变中，但相比Kylin on HBase，这个架构和SparkSQL物化视图更为接近，物化视图也能将数据以Parquet格式存储，查询引擎使用的是SparkSQL。为了保证低延时，Kylin对Spark进行了优化（比如，从yarn获取资源后，不再释放，消除资源申请耗时），而这类优化对于SparkSQL是通用的，相信物化视图也能从中获取收益。假如在延时上Kylin不能占据绝对的优势，那我认为物化视图带来的灵活性是Kylin目前做不到的，毕竟cube的数据即使存储在parquet中也不能和Hive中的数据进行交互。

总结

本文介绍了数据库常用的优化方式，物化视图，在SparkSQL这个流行的大数据查询引擎上的相关实践，包括了实现的架构，实现的简介，实战中的表现等。同时也和业界大佬Kylin进行了对比，展示了这类优化方式的适用场景。后续有计划将该优化在Spark社区进行开源，和大家一起将SparkSQL打造成更好的大数据计算引擎。

参考资料

【1】Optimizing Queries Using Materialized Views: A Practical, Scalable Solution

腾讯大数据诚招计算，存储，消息中间件，调度，中台等各方向的大数据研发工程师，请私信或联系[email protected]