大数据篇--SQL on Hadoop

一、业界常用框架

有几个概念需要先区分一下：

  Hive on Spark：这个是Hive社区的，它底层支持MR、Spark和Tez。它的意思是说可以直接运行Spark之上。
  Spark SQL：Spark社区里的。而Spark on Hive的说法是错误的。

它们两支持的力度现在也不一样，目前Hive on Spark支持的肯定会多一些，因为Hive这个东西发展到现在已经很多年了，所以说它的技术积累是很多的，而Spark SQL是一个新兴的框架，它里面所支持的语法等要稍微差一些。其实现在在Spark 2.2和2.3以后他们基本上都是类似的，能兼容和覆盖的面基本上都已经差不多了。对于以前的版本，可能Hive on Spark能运行，但是Spark SQL不一定能运行得通，因为它很多语法并没有做一个很好的支持。

前言：

  SQL on Hadoop中的Hadoop指的是广义上面的Hadoop，而不是针对于Hadoop一个框架，指的是大数据生态圈里面广义Hadoop的概念。意思就是在大数据框架中我们使用SQL语言就可以搞定，这就是所谓的一个SQL on Hadoop。

  下面框架它们底层MetaStore都是相通的，MetaStore指的是存储元数据信息的，比如表的名字叫什么，表里面有哪些字段，它的数据类型是什么等，因为只有把元数据信息有了以后你才可以再把这个元数据和文件系统上面的文件作用上你才可以提供一个SQL的查询，否则文件系统上面的文件就是一个普通的文本文件而已，它根本就不知道你的一个元数据信息，所以有了这个东西以后这些框架之间是共享元数据信息的，意思就是你在hive里面创建的一张表你在Impala、Presto等里也可以做相应的一个查询，同理impala里创建的表在hive里也可以查询，所以说MetaStore这个共享元数据是非常重要的。否则hive里面的东西只能在hive里面用，这种局限性就太强了，不方便以后框架的一个迁移。

1.Hive SQL：

  我们最常用的肯定是Hive，它是SQL on Hadoop最经典的一个解决方案。它的功能就是把我们的SQL转换成对应执行引擎的作业，SQL层面你是不用感知到底运行在哪个执行框架上面的，因为它底层是通过一个参数就可以直接设置执行框架的。执行引擎有好几种：对于Hive 1.x来说默认的是MapReduce；Hive 2.x以后默认的就是基于Spark了，把MapReduce标识为一种过时的；还有基于Tez的；Hive的语法和SQL是类似的，但是它们之间并没有什么联系，只不过说它们的语法长的比较像而已。

2.Spark SQL：

  它提供的并不仅仅是SQL的功能，提供的功能要比SQL功能多很多。通过这个名字很多人都会以为它仅仅是使用SQL，但是这个理解很定是不对的。可以去Spark官网上看一下Spark SQL是如何定义的，这个定义是非常重要的，就决定了你对它的理解是否正确以及影响到你以后的技术选型是否正确。

  Spark Sql不仅仅具有访问或者操作sql的功能，还提供了其他的非常丰富的操作：外部数据源（能够访问hive、json、parquet等文件的数据）、优化。

注意：Spark SQL的前身是Shark，但又因为Shark对于Hive的太多依赖（如采用Hive的语法解析器、查询优化器等等)，2014年spark团队停止对Shark的开发，将所有资源放SparkSQL项目上。shark终止以后，产生了两个分支：hive on spark（hive 社区，源码在hive中。是一个Hive的发展计划，该计划将Spark作为Hive的底层引擎之一，也就是说，Hive将不再受限于一个引擎）；spark sql（spark 社区，源码在spark中，作为Spark生态的一员继续发展，而不再受限于Hive，只是兼容Hive，支持多种数据源，多种优化技术，扩展性好很多）；

3.Impala：

  整体的性能是要比hive快的，但是有一点要注意那就是需要提供足够的内存，这个东西简直就是一个吃内存的老虎，如果没有足够的内存肯定是没法使用的。

4.Presto：

  在国内京东用的非常多。

  和 Impala 类似，都是基于内存，速度很快，容易出现 OOM。不同点是 Presto 支持的数据源比较多，Impala 支持的数据源相对少，Impala 性能要比 Presto 要快一丢丢但不是很明显。在生产环境中，如果是 CDH 框架选择 Impala（默认集成）；如果 Apache 选择 Presto（集成安装比较费劲，步骤繁琐有很多坑）。

5.Drill：

  能做到跨数据源的一个查询，比如你的文本文件在HDFS上，它有元数据以后可以直接关联关系型数据库。

6.Phoenix：

  HBASE查询性能如何取决于你Rowkey的一个设计，所以说你的Rowkey肯定要基于查询条件来进行设计的。Hbase官方提供api和命令行的操作而并没有提供一个SQL的查询。所以社区上面就出来一个Phoenix，它可以直接使用SQL查询HBASE中的东西，而且它还可以提供二级索引的功能。

二、SQL on Hadoop调优策略

先介绍两个概念：行式存储和列式存储

  很多人刚开始学习数据库的时候可能接触到的都是关系型数据库RDBMS，它是基于行式存储的思想来进行数据存储的；但是数据库存储领域还有一个不容小觑的力量，非关系型数据库NoSQL，其中一部分NoSQL数据库是采用了列式存储的思想。
  一般认为原因是Column-Store在存储格式有优势，分析类查询往往只查询一个表里面很少的几个字段，Column-Store只需要从磁盘读取用户查询的Column，而Row-Store读取每一条记录的时候你会把所有Column的数据读出来，在IO上Column-Store比Row-Store效率高很多，因此性能更好。如果想深入研究原因可参考：Column-Stores vs. Row-Stores
  对此，行式数据库给出的优化方案是加“索引”，在OLTP类型的应用中通过索引机制或给表分区等手段可以简化查询操作步骤，并提升查询效率但针对海量数据背景的OLAP应用(例如分布式数据库、数据仓库等等)行式存储的数据库就有些“力不从心”了。当然，跟行数据库一样列式存储也有不太适用的场景：不适合扫描小量数据；不适合随机的更新；不适合做含有删除和更新的实时操作。随着列式数据库的发展，传统的行式数据库加入了列式存储的支持，形成具有两种存储方式的数据库系统。例如，随着Oracle 12c推出了in memory组件，使得Oracle数据库具有了双模式数据存放方式，从而能够实现对混合类型应用的支持，当然列式数据库也有对行式存储的支持比如HP Vertica。参考：https://stor.51cto.com/art/201809/583648.htm

调优：在资源不变的前提下，让作业的执行性能有提升。

1.架构层面调优：

（1）分表：

  将一张大数据表分成多个小表。

（2）分区表：

  将同一张表分别储存到不同的分区

个人感觉：抛开分区表的一些限制和缺陷来说，可以做分区表的情况下一般不拆成实体小表，因为通常需要改程序，而分区表不用。而且分区切换速度非常快，我个人偏向于使用分区表。好像在分区表出现之前，当数据量很大时，一种方法就是将大表分成几个小表；后来，数据库自己实现这种功能，就是分区表。整体来看分区表比较好，除了一些特殊情况不能使用分区表外，最好还是使用分区表。

（3）压缩（compressed）：

a.为什么要使用压缩：

  压缩分为无损压缩(Lossless Compression)和有损压缩（Lossy Compression）。无损压缩一般适用于用户行为数据这类不允许数据丢失的业务场景；有损压缩一般适用于大文件的压缩，例如图片、视频的处理，优点是压缩率和压缩比都比较高，可以节省更多的空间。

  随着数据量越来越大，对数据如何处理使得我们能够提高数据处理效率，如何选择和使用压缩就显得尤为重要。缺点是由于使用数据时需要先解压，就会加重CPU的负荷。但压缩的优点：

• 减少文件大小（reduce file size）
• 节省磁盘空间（svae disk space）
• 增加网络传输速度及效率（Increase tansfer speed at a given data rate）

  使用压缩的准则：Hadoop怎么查看压缩文件 hadoop中都有哪些压缩格式

• 平衡压缩和解压缩数据所需的能力、读写数据所需的磁盘 IO，以及在网络中发送数据所需的网络带宽。
• 如果数据已压缩（例如 JPEG 格式的图像），则不建议进行压缩。事实上，结果文件实际上可能大于原文件。
• GZIP 压缩使用的 CPU 资源比 Snappy 或 LZO 更多，但可提供更高的压缩比。GZIP 通常是不常访问的冷数据的不错选择。而 Snappy 或 LZO 则更加适合经常访问的热数据。
• BZip2 还可以为某些文件类型生成比 GZip 更多的压缩，但是压缩和解压缩时会在一定程度上影响速度。HBase 不支持 BZip2 压缩。

b.MapReduce处理过程中哪些地方可以使用到压缩（Spark同理）：

  1描述的是使用压缩过的数据作为Map的输入；2描述的是压缩的中间数据，因为Map的输出到Reduce的输入中间是有一个过程；3描述的是Reduce处理完的结果可以进行压缩。

  对于这一份数据来说假设没有经过压缩体积肯定是要大一些的，按照默认的 Input Split 大小是 block size 大小，不压缩 Input Split 会给Map处理的数量肯定要多一些。如果使用压缩的话这会使数据体积减小，经过 Input Split 后的数量也会少一些。当 Maps 读的时候会有一个解压缩，这块分布式的框架比如 MapReduce、Spark 等引擎默认内置的，所以对数据到底压缩不压缩就不用在你的编码层面指定，通过配置就可以搞定。Maps 进来以后按照业务逻辑比如统计 WordCount，他会把每一行数据按照分隔符进行拆分，拆分出来以后把每个单词赋上一个1。Map 输出会先把数据写到环形缓存区中，当填满的时候会刷到磁盘上面，所以刷到磁盘上面的数据又可以进行压缩。然后 Reduce 会根据一定规则去 Map 的输出里面拉取数据，并再次解压，这就是一个中间的过程。到 Reduce 之后会有一个真正作业结果的输出，这个输出以后也可以进行压缩，这个压缩以后可以节省空间。

  不同的场景选择不同的压缩方式，肯定没有一个一劳永逸的方法，如果选择高压缩比，那么对于cpu的性能要求要高，同时压缩、解压时间耗费也多；选择压缩比低的，对于磁盘io、网络io的时间要多，空间占据要多；对于支持分割的，可以实现并行处理。

  那么这几种压缩该如何做一个选择呢？需要根据压缩比、压缩/解压速度这两个角度来考虑。根据上面这个图我们可以想到中间2这个过程应该选择速度非常快的，因为中间这个过程无所谓压缩比大还是小。后面3输出的话肯定优先选择压缩比高的，因为这样可以节省磁盘空间。对于一开始的1就应该考虑压缩能否支持分片，如果压缩不支持分片，即使你使用了压缩，Map处理的时候也只有一个Task进行处理就肯定会比较慢的。所以对于这个3个场景要使用不同的压缩技术。

结论：
  Map的输入：压缩后要保证能进行切片，支持切片的压缩方法（LZO，bzip2）
  Map的输出：压缩速度要尽可能的快，速度快的压缩方法（Snappy）
  Reduce输出：
    如果输出想要永久保存，选择压缩率最高的，但是压缩速度会慢一些，比如（Gzip）
    如果输出要作为下一个MapReduce的输入，要考虑支持切片的压缩方法（LZO，bzip2）

c.大数据里常用的压缩：

压缩格式	压缩工具	算法	文件扩展名	Hadoop类	hadoop自带	是否支持分片
gzip	gzip	deffault	.gz	org.apache.hadoop.io.compress.GzipCodec	是	No
bzip2	bzip2	bzip2	.bz2	org.apache.hadoop.io.compress.BZip2Codec	是	Yes
LZO	LZO	LZO	.lzo	com.hadoop.compression.lzo.LzoCodec	是	Yes(建索引)
Snappy	N/A	Snappy	.snappy	org.apache.hadoop.io.compress.SnappyCodec	否	No

注：上图的这个压缩比待考证，我在 Linux 中使用 gzip 命令的压缩比大概在 9% 左右，bzip2 的压缩比大概在 4% 左右。

  从上图可以看出，压缩比越高，压缩速率越慢，压缩时间越长，压缩比：Snappy<LZ4<LZO<GZIP<BZIP2（注意，压缩比的大小不是通过数字的大小来看的，是数字越小，压缩比越大，所以snappy的压缩比是22.2%，数据最大，但是压缩比是最小的）。

gzip：优点：压缩比在四种压缩方式中较高；hadoop本身支持，在应用中处理gzip格式的文件就和直接处理文本一样；有hadoop native库；大部分linux系统都自带gzip命令，使用方便。缺点：不支持split。

lzo：优点：压缩/解压速度也比较快，合理的压缩率；支持split，是hadoop中最流行的压缩格式；支持hadoop native库；需要在linux系统下自行安装lzop命令，使用方便。缺点：压缩率比gzip要低；hadoop本身不支持，需要安装；lzo虽然支持split，但需要对lzo文件建索引，否则hadoop也是会把lzo文件看成一个普通文件（为了支持split需要建索引，需要指定inputformat为lzo格式）。

snappy：优点：压缩速度快；支持hadoop native库；缺点：不支持split；压缩比低；hadoop本身不支持，需要安装；linux系统下没有对应的命令。

bzip2：优点：支持split；具有很高的压缩率，比gzip压缩率都高；hadoop本身支持，但不支持native；在linux系统下自带bzip2命令，使用方便。缺点：压缩/解压速度慢；不支持native。

d.整合Hadoop的使用：

  检查 Hadoop 的压缩格式是否可用，你可能都是false，这就需要你编译一下，把压缩的东西整进来。

  CDH-6.3.2-1 我看默认都支持了已经：

  压缩配置在 core-site.xml：

<property>
<name>io.compression.codecs</name>
	<value>
		org.apache.hadoop.io.compress.GzipCodec,
		org.apache.hadoop.io.compress.DefaultCodec,
		org.apache.hadoop.io.compress.BZip2Codec
	</value>
</property>

这里在mapred-site.xml里配置一下reduce和map输出，中间输出并没有配：

<property>
	<name>mapreduce.output.fileoutputformat.compress</name>
	<value>true</value>
	<description>Reduce是否启用输出压缩</description>
</property>
<property>
	<name>mapreduce.output.fileoutputformat.compress.codec</name>
	<value>org.apache.hadoop.io.compress.GzipCodec</value>
	<description>Reduce输出压缩算法:Gzip</description>
</property>
<property>
	<name>mapreduce.map.output.compress</name> 
	<value>true</value>
	<description>Map是否开启输出压缩</description>
</property>	
<property>
	<name>mapreduce.map.output.compress.codec</name>
	<value>org.apache.hadoop.io.compress.SnappyCodec</value>
	<description>Map输出压缩算法：Snappy</description>
</property>

e.整合Hive的使用：

测试BZIp2压缩：

创建一张表：
hive> create table page_views(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

hive> load data local inpath '/home/hadoop/data/page_views.dat' overwrite into table page_views;

查大小：
[hadoop@hadoop000 data]$ hadoop fs -du -s hdfs://hadoop000:8020/user/hive/warehouse/interview.db/page_views
18.1 M   18.1 M hdfs://hadoop000:8020/user/hive/warehouse/interview.db/page_views

hive设置压缩：
hive> set hive.exec.compress.output=true;
hive> set hive.exec.compress.output;
hive.exec.compress.output=true
hive> set mapreduce.output.fileoutputformat.compress.codec;
mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.DefaultCodec; -- 默认是DefaultCodec
hive> set mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.BZIp2Codec; -- 我们改为BZIp2Codec

创建另一张表：会运行一个MR作业
hive> create table page_views_bzip2    
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
as select * from page_views;

再查这张表的大小：
[hadoop@hadoop000 data]$ hadoop fs -du -s hdfs://hadoop000:8020/user/hive/warehouse/interview.db/page_views_bzip2
3.6 M   3.6 M hdfs://hadoop000:8020/user/hive/warehouse/interview.db/page_views_bzip2

也可以在建表的时候进行设置，一般选择orcfile/parquet + snappy 的方式比较好：

create table tablename (
 xxx,string
 xxx, bigint
)
ROW FORMAT DELTMITED FIELDS TERMINATED BY '\t'
STORED AS orc tblproperties("orc.compress" = "SNAPPY")

f.整合Spark的使用：

参考spark2.4.1版本来讲解：spark 压缩分为3块，rdd压缩、broadcast压缩和spark sql的压缩

rdd 压缩：spark.rdd.compress。
用来压缩rdd的分区，默认值为false。用来一想也是，rdd的分区缓存在内存中，我们本身就是想使用内存来加速，压缩和相应的解压缩反而浪费了时间。默认的压缩格式是用的spark.io.compression.codec配置的值。

RDD输出压缩文件：

import org.apache.hadoop.io.compress.BZip2Codec
// bzip2 压缩率最高，压缩解压速度较慢，支持split。
rdd.saveAsTextFile("codec/bzip2",classOf[BZip2Codec])

import org.apache.hadoop.io.compress.SnappyCodec
//snappy json文本压缩率 38.2%，压缩和解压缩时间短。
rdd.saveAsTextFile("codec/snappy",classOf[SnappyCodec])

import org.apache.hadoop.io.compress.GzipCodec
//gzip 压缩率高，压缩和解压速度较快，不支持split，如果不对文件大小进行控制，下次分析可能会造成效率低下的问题。
// rdd..saveAsTextFile(path,GzipCodec.class); 
rdd.saveAsTextFile("codec/gzip",classOf[GzipCodec])

broadcast压缩：spark.broadcast.compress
压缩发送的广播变量，默认压缩。压缩格式使用spark.io.compression.codec 配置的值。想想也合理，压缩广播变量，可以大大的节省带宽和IO，节省效率。

spark sql中的压缩：spark.sql.parquet.compression.codec和spark.sql.orc.compression.codec snappy
上面两个配置说的就是写parquet 和orc 文件默认都是使用snappy格式压缩的。
spark sql中的压缩指的比如spark sql建表 spark sql写文件等操作。这块的配置在官方文档没有，这边顺带说声spark sql 想查看相关配置应该怎么做：打开spark shell 使用spark.sql(“SET -v”).write.saveAsTable(“demo”); 把这些spark sql的配置直接存成一张表也方便查看，不然80多个有点多。

// spark sql 输出压缩文件：parquet文件压缩
// parquet为文件提供了列式存储，查询时只会取出需要的字段和分区，对IO性能的提升非常大，同时占用空间较小，即使是parquet的uncompressed存储方式也比普通的文本要小的多。
// 默认值snappy.Acceptable values include: none, uncompressed, snappy, gzip, lzo, brotli, lz4, zstd.

sparkConf.set("spark.sql.parquet.compression.codec","gzip")
dataset.write().parquet("path");

val PARQUET_COMPRESSION = buildConf("spark.sql.parquet.compression.codec")
    .doc("Sets the compression codec used when writing Parquet files. If either `compression` or " +
      "`parquet.compression` is specified in the table-specific options/properties, the " +
      "precedence would be `compression`, `parquet.compression`, " +
      "`spark.sql.parquet.compression.codec`. Acceptable values include: none, uncompressed, " +
      "snappy, gzip, lzo, brotli, lz4, zstd.")
    .stringConf
    .transform(_.toLowerCase(Locale.ROOT))
    .checkValues(Set("none", "uncompressed", "snappy", "gzip", "lzo", "lz4", "brotli", "zstd"))
    .createWithDefault("snappy");

// orc：默认值snappy.Acceptable values include: none, uncompressed, snappy, zlib, lzo

val ORC_COMPRESSION = buildConf("spark.sql.orc.compression.codec")
    .doc("Sets the compression codec used when writing ORC files. If either `compression` or " +
      "`orc.compress` is specified in the table-specific options/properties, the precedence " +
      "would be `compression`, `orc.compress`, `spark.sql.orc.compression.codec`." +
      "Acceptable values include: none, uncompressed, snappy, zlib, lzo.")
    .stringConf
    .transform(_.toLowerCase(Locale.ROOT))
    .checkValues(Set("none", "uncompressed", "snappy", "zlib", "lzo"))
    .createWithDefault("snappy");

参考：
SparkSQL的几种输出格式及压缩方式
https://www.cnblogs.com/yyy-blog/p/12747133.html
spark的压缩使用和简单介绍
在Spark程序中使用压缩

2.执行层面调优：

（1）推测执行：

参考官网：https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties

  在分布式集群环境下，因为程序Bug（包括Hadoop本身的bug），负载不均衡（比如作业在一个负载比较高的机器上运行会比较慢一些）或者资源分布不均（比如作业在一个低配的机器上运行就会比较慢一些）等原因，会造成同一个作业的多个任务之间运行速度不一致，有些任务的运行速度可能明显慢于其他任务（比如一个作业的某个任务进度只有50%，而其他所有任务已经运行完毕），则这些任务会拖慢作业的整体执行进度。
  为了避免这种情况发生，Hadoop采用了推测执行（Speculative Execution）机制，它根据一定的法则推测出“拖后腿”的任务，并为这样的任务启动一个备份任务，让该任务与原始任务同时处理同一份数据，并最终选用最先成功运行完成任务的计算结果作为最终结果，原有任务和新任务哪个先执行完就把另外一个kill掉。

设置开启推测执行参数：Hadoop的mapred-site.xml文件中进行配置，默认是true：

<property>
    <name>mapreduce.map.speculative</name>
    <value>true</value>
    <description>If true, then multiple instances of some map tasks 
    may be executed in parallel.
    </description>
</property>

<property>
    <name>mapreduce.reduce.speculative</name>
    <value>true</value>
    <description>If true, then multiple instances of some reduce tasks 
    may be executed in parallel.
    </description>
</property>

hive本身也提供了配置项来控制reduce-side的推测执行，默认是true：

<property>
    <name>hive.mapred.reduce.tasks.speculative.execution</name>
    <value>true</value>
    <description>Whether speculative execution for reducers 
    should be turned on.     
    </description>
</property>

  在测试环境下我们都把应用程序测试OK了，如果还加上推测执行，如果有一个数据分片本来就会发生数据倾斜，执行时间就是比其他的时间长，那么hive就会把这个执行时间长的job当作运行失败，继而又产生一个相同的job去运行，后果可想而知，可通过如下设置关闭推测执行：

set mapreduce.map.speculative=false
set mapreduce.reduce.speculative=false
set hive.mapred.reduce.tasks.speculative.execution=false

（2）并行执行：

  当一个sql中有多个job时候，且这多个job之间没有依赖，则可以让顺序执行变为并行执行（一般为用到union all ）；

// 开启任务并行执行
set hive.exec.parallel=true;
// 同一个sql允许并行任务的最大线程数 
set hive.exec.parallel.thread.number=8;

（3）JVM重用：

  JVM重用是Hadoop调优参数的内容，其对Hive的性能具有非常大的影响，特别是对于很难避免小文件的场景或task特别多的场景，这类场景大多数执行时间都很短。
  Hadoop的默认配置通常是使用派生JVM来执行map和Reduce任务的。这时JVM的启动过程可能会造成相当大的开销，尤其是执行的job包含有成百上千task任务的情况。JVM重用可以使得JVM实例在同一个job中重新使用N次。N的值可以在Hadoop的mapred-site.xml文件中进行配置。通常在10-20之间，具体多少需要根据具体业务场景测试得出。

<property>
  <name>mapreduce.job.jvm.numtasks</name>
  <value>10</value>
  <description>How many tasks to run per jvm. If set to -1, there is
  no limit. 
  </description>
</property>

  我们也可以在hive当中通过：set mapred.job.reuse.jvm.num.tasks=10;设置我们的jvm重用。
  当然，这个功能也是有它的缺点的。开启JVM重用将一直占用使用到的task插槽，以便进行重用，直到任务完成后才能释放。如果某个“不平衡的”job中有某几个reduce task执行的时间要比其他Reduce task消耗的时间多的多的话，那么保留的插槽就会一直空闲着却无法被其他的job使用，直到所有的task都结束了才会释放。