就这样被SQL on Hadoop三驾马车华丽丽滴碾过

“五一”节前的最后一天，我正在微信上左支右挡、狼狈不堪地同时应对几位小伙伴的约稿和催稿，突然半路上一位程咬金非常不合时宜地乱入了。

好险，差点把讨价还价的信息发错了人。

仔细一看，原来是一位北邮的好基友。

说起北邮，不只是中国IT圈里的小伙伴，估计全中国数以亿计的资深网民也一定都耳熟能详，包括许多友邦人士对这个名字也是如雷贯耳。

原因其实很简单，谁让人家是打造大中华局域网的最大功臣呢。

好基友发来了一篇文章的链接：“这个是我们公众号的原创内容，帮忙用你的牛号给转转呗。”

其实一开始听到这种含情脉脉的要求，我是拒绝的。因为就在本自媒体公众号作者日益逗比化，作品日益呆萌化的大好形势之下，突然出现了一篇这样高端大气上档次的好文，大家多半会感觉“Duang”的一下，以为我这是要改邪归正了呢。

不过后来经过好基友巧妙滴暗示，我的脑海中终于回忆起了之前在北邮参观人家机房时的画面。对了，好基友当时就是这样提醒我的：“你在上网时的所有行为和所有记录，我这里都可以通过系统看得到哦！”

此时此刻此景，作为一枚阳光帅气并且有着强烈正义感的狮子座，向来都很有节操的我也顿时忍不住爆发了。

我拍案而起，义正辞严、色厉内荏地表示：“不就是一篇文章嘛！发就发！Who怕Who？”

===偶素好久没出现滴华丽丽滴分割线===

SQL on hadoop性能对比－Hive、Spark SQL、Impala1三种语言、三套工具、三个架构

不了解SQL on Hadoop三驾马车－Hive、Spark SQL、Impala吗？听小编慢慢道来

1

Hive

Apache Hive数据仓库软件提供对存储在分布式中的大型数据集的查询和管理，它本身是建立在Apache Hadoop之上。Hive SQL代表的是以传统基于Mapreduce为核心的SQL语言。

2

Spark SQL

Spark SQL则是基于内存计算Spark框架。Spark SQL抛弃原有Shark的代码，汲取了Shark的一些优点，如内存列存储（In-Memory Columnar Storage）、Hive兼容性等，重新开发了Spark SQL代码。由于摆脱了对Hive的依赖性，SparkSQL无论在数据兼容、性能优化、组件扩展方面都得到了极大的方便。

3

Impala

Impala则是Cloudera公司主导开发的查询系统，最近刚刚完全开源。主要参考的是Google的Dremel，实现了多层查询树，使得任务可以分布在所有节点上并行执行和聚合结果。

2对本文中测试的说明

本文将从压缩对查询速度的影响、文件格式对CPU资源消耗的影响、文件格式对内存消耗的影响三个部分进行性能的比较。

测试数据：

本次测试采用的是某省2014年6月某天的网络流量话单数据，数据量为772.57G。

测试环境：
共有15台机器，物理内存为16G。HiveSQL与Spark SQL都是基于YARN资源分配。

内存资源设置：

yarn.nodemanager.resource.memory-mb为8G,yarn.scheduler.minimum-allocation-mb为512MB,yarn.scheduler.maximum-allocation-mb为8G.

CPU资源设置：

yarn.nodemanager.resource.cpu-vcores为16个,yarn.scheduler.minimum-allocation-vcores为1个,yarn.scheduler.maximum-allocation-vcores为16个

特别注意：

本次测试会将结果保存到本地，查询时间是包括了IO传输到本地的时间。

本文档中涉及的查询语句：
查询一：select * from table where phnum = '139******** '

查询二：select phnum, ByteUp, ByteDn from table where phnum = '139********'

查询三：select phnum, ByteUp, ByteDn from table where phnum like '139*****%'

查询四：select GGSN, count(*) from table group by GGSN

查询五：select count(*) from table

查询六：selectGGSN,count(*), sum(cast(ByteUp as float) + cast(ByteDn as float)), sum(cast(PktUp as float) + cast(PktDn as float)) from table group by GGSN

查询七：select m.T_2G_3G, count() group by m.T_2G_3G from (selectT_2G_3G,phnum,count() from table group by T_2G_3G, phnum) as m

3不同文件格式和压缩方式条件下的查询时间对比1文件格式

测试所用的文件格式有如下几种：SequenceFile（Hadoop生态圈常用文件格式）、RCFile（结合了行式和列式存储格式的优点）、Parquet（列式存储格式）

2测试结果

3结果说明

- 从压缩的角度来讲，三种文件格式均有下述结论：压缩可以减少输入数据量，从而减少查询时间。原因在于这些查询当中IO的耗时占据查询时间的大部分时间。并且压缩后的数据量和查询时间成正比，压缩后的查询平均耗时是压缩前的一半左右。

- 从文件格式的角度来讲：Hive适配最好的是RCfile文件格式，spark SQL是Parquet，Impala适配最好的是Parquet。在纵向上来看，Impala在采用Parquet文件格式的时候，查询速度最快，而且相比于RCFile而言，查询速度可提升3.5倍左右，相比于Spark SQL在相同条件下可以提升2倍。对于加载个别列并进行查询操作的话，Impala采用Parquet格式是最优选择。

- 综合结论：当需要加载所有列的时候，无论哪种查询方式，RCFile都是最好的选择。因为采用RCFile这种格式保证了同一行的数据位于同一个节点上，因此元组的重构的开销成本就会很低。然后对每行进行垂直划分，以便于单独进行列式存储。

4不同文件格式和压缩方式条件下的CPU资源消耗对比
1测试说明

- CPU累积时间一方面反映的是查询时间，即查询时间越长，CPU的累积时间就会越多。另一方面反映的是查询中重组数据的难度，重组数据的难度越大，CPU的累积时间就会越多。因为Spark SQL无法监测到具体的CPU使用情况，故没有比较。

- 这里（Hive/Impala）各种文件格式消耗CPU值，是指在整个查询过程中CPU累积时间。

2测试结果

3测试结果说明

- RCFile采用Snappy与Parquet压缩方式的对比：从Hive来看两者消耗的CPU时间相当，而且两者的数据量大小是差不多的（Hive的RCFile经过Snappy压缩后为286G，Parquet数据量大小为265G），对于Hive来讲数据量的多少直接影响了CPU累积时间。但是要注意的是，在查询一，因为查询一要求加载所有的列，对于以列式存储为特征的Parquet而言，数据重组的难度会急剧增大，消耗了很多的CPU资源，所以在Hive的查询一中，Parquet消耗的CPU累计时间是最大的，同时查询时间也是最大的。所以综合来看，对于Hive而言采用RCFile文件格式经过Snappy压缩后的方式是最合适的。

- Impala的说明：对于Impala而言，情况则有些不同。在查询一中因为加载所有列，造成了内存不足，导致无法查询。

5不同文件格式和压缩方式条件下的内存消耗对比1测试说明

- 因为无法检测具体每种查询所消耗的内存资源，所以本次执行Spark SQL和Hive基本可以假定是在充分使用了8G内存资源下测试的。

- 对于三种类型的查询方式在内存上的使用情况在纵向比较是存在困难的，一是没有监测到具体查询中Hive和SparkSQL的内存使用情况，二是三者并非都是以内存计算为特点，纵向比较意义不大。但是可以通过设置yarn.nodemanager.resource.memory-mb的大小横向对Hive和SparkSQL在不同内存条件下进行比较。

2测试结果

3结果分析

- 在查询一中，因为对于未压缩的Sequence消耗内存很大，单节点峰值超过了7.8G。Parquet消耗内存更大，单节点峰值超过了12.6G，并且因为无法再申请内存而报错。所以在加载全部列的时候，仍然是不推荐使用Parquet格式。

- 比较除查询一之外的其余查询所消耗的平均内存，可以比较所有文件的平均消耗内存排名为：Sequence未压缩（1650MB）> Sequence 压缩（798MB）> Parquet（652MB）> RCFile未压缩（560MB）> RCFile压缩（500MB）> 文本（478MB）。Parquet格式所消耗的内存与RCFile、文本相比，内存消耗相差不大。所以选择Parquet格式对于Impala而言，仍然是不错的选择。

- 由于快速检索这种交互式查询需要支持多用户并发操作，因此每一个查询使用的资源越少越好。从上述内存使用状况来看，使用文本格式占用的资源是最稳定的，保持在较低水平，使用Parquet格式占用的内存有时高于1GB（查询1、2、3、7），不太稳定，当有20个并发查询时当前集群的节点的物理内存是不够的（16GB，实际可用12.6GB）。因此，除非物理内存充足，不然使用Parquet格式可能无法支持15个以上的并发查询。

6不同查询工具生成Parquet格式对Impala查询的影响1测试说明

- 从前面三个部分可以看出Impala与Parquet适配性最好，而且Impala在使用Parquet格式进行查询时的查询速度最快。但是我们从上面的测试也可以看出三种查询工具生成的Parquet格式文件是不一样的。这会影响到Impala查询的各个方面。下面测试的就是不同查询工具生成的Parquet格式对Impala查询的影响。

2查询时间的测试结果

从上图可以看出以下几点：

1. 对于查询三至查询六，所有Parquet格式的查询时间相当；对于查询一与查询二，Spark-Parquet的查询时间接近Hive-Parquet的1/2；对于查询七，Hive-Parquet和Spark-Parquet查询时间相当，均少于Impala-parquet。

2. 结论：单从查询速度上考虑，Spark-parquet是适配于Impala的最佳Parquet格式。

3CPU时间的测试结果

其中，对于Impala生成的Parquet文件来说查询一因内存占用过大而无法执行，图中的CPU时间标记为-1。

从上图可以看出以下几点：

1. 对于查询二至六，所有Parquet格式CPU时间相当；对于查询一与七，Spark-Parquet的CPU时间最少。

2. 结论：单从CPU时间上考虑，Spark-parquet占用的CPU资源最少。

4内存占用的测试结果

其中，对于Impala生成的Parquet文件来说查询一因内存占用过大而无法执行，图中的内存占用标记为-1。

从上图可以看出以下几点：

1. 对于所有查询，Impala-Parquet格式占用的内存最多；对于查询二至查询七，Hive-Parquet和Spark-Parquet占用的内存相当；对于查询一，Spark-Parquet占用内存约为Hive-Parquet的1.5倍。

2. 结论：单从内存占用上考虑，Hive-Parquet占用的内存资源最少，其次是Spark-Parquet。

5读取数据量测试结果

其中，对于Impala生成的Parquet文件来说查询一因内存占用过大而无法执行，图中的读取数据量标记为-1。

从上图可以看出以下几点：

1. 对于查询二至查询七，读取数据量大小的排序大致为 Impala-Parquet > Hive-Parquet > Spark-Parquet；对于查询一至查询三，Spark-Parquet读取的数据量接近Hive-Parquet的一半。

2. 结论：单从读取数据量大小上考虑，Spark-Parquet读取的数据量最少，在以IO时间为主要时间开销的查询（如查询一）中，读取数据量与查询时间成正比，即Spark-Parquet的查询时间最少。

6综合结论

- 综合上述几点，可以得出的结论是：在执行除查询一（扫描所有列）以外的查询时，使用Spark-Parquet的查询速度最快，占用CPU与内存资源最少。

7结论

• 纵向上来比较，在节点可用物理内存充足的情况下，Impala采用SparkSQL生成的Parquet格式的查询速度是最快的，并且在CPU和内存上同时具有优势。如果需要构建大数据情况下交互式查询，本条结论具有重要的参考价值。

• 输入数据量的大小是影响查询速度、CPU消耗与内存消耗的关键。

• 尽管在文本格式下进行格式转换会消耗时间，但是这种时间的消耗是值得的，因为可以极大提升查询速度，尤其是适合一次写入，多次查询的情况。

• Sequence File是Hadoop生态系统中普遍支持的文件格式，所以适用性是很普遍的，相比之下，RCFile和Parquet文件格式的适用性要比Sequence File低。但是其在查询速度、资源消耗上是不占有任何优势的。

• 对指定格式进行Snappy压缩也是合适的，因为可以减少近一半的数据量，可以减少IO压力，将IO的压力分担给CPU。

• 对于Hive而言，采用RCFile格式并对其进行Snappy压缩是最合适的。

• 对于SparkSQL而言，采用Parquet格式是最合适的。

• 对于加载全部列的查询方式，采用RCFile格式是最合适的。

• 对于加载部分列，优先选择Impala进行查询。而且对于文件格式来说，推荐使用Spark SQL进行压缩生成的Parquet格式。

转自“北邮数据科学中心”