数据分析课程笔记 - 19 - HiveSQL 常用优化技巧

大家好呀，这节课学习 HiveSQL 的常用优化技巧。由于 Hive 主要用来处理非常大的数据，运行过程由于通常要经过 MapReduce 的过程，因此不像 MySQL 一样很快出结果。而使用不同方法写出来的 HiveSQL 语句执行效率也是不一样的，因此为了减少等待的时间，提高服务器的运行效率，我们需要在 HiveSQL 的语句上进行一些优化。

本节课的主要内容 ：

引言

1、技巧一：列裁剪和分区裁剪

（1）列裁剪

（2）分区裁剪

2、技巧二：排序技巧——sort by代替order by

3、技巧三：去重技巧——用group by来替换distinct

4、技巧四：聚合技巧——grouping sets、cube、rollup

（1）grouping sets

（2）cube

（3）rollup

5、技巧五：换个思路解题

6、技巧六：union all时可以开启并发执行

7、技巧七：表连接优化

8、技巧八：遵循严格模式

Hive 作为大数据领域常用的数据仓库组件，在平时设计和查询时要特别注意效率。影响Hive效率的几乎从不是数据量过大，而是数据倾斜、数据冗余、job 或 I/O 过多、MapReduce 分配不合理等等。对 Hive 的调优既包含对HiveSQL 语句本身的优化，也包含 Hive 配置项和 MR 方面的调整。

列裁剪就是在查询时只读取需要的列。当列很多或者数据量很大时，如果select 所有的列或者不指定分区，导致的全表扫描和全分区扫描效率都很低。Hive中与列裁剪优化相关的配置项是 hiveoptimizecp ，默认是 true 。

分区裁剪就是在查询时只读需要的分区。Hive中与分区裁剪优化相关的则是 hiveoptimizepruner ，默认是 true 。

HiveSQL中的 order by 与其他 SQL 语言中的功能一样，就是将结果按某个字段全局排序，这会导致所有map端数据都进入一个 reduce 中，在数据量大时可能会长时间计算不完。

如果使用 sort by ，那么就会视情况启动多个 reducer 进行排序，并且保证每个 reducer 内局部有序。为了控制 map 端数据分配到 reduce 的 key，往往还要配合 distribute by 一同使用。如果不加 distribute by 的话，map 端数据就会随机分配给 reducer。

这里需要解释一下， distribute by 和 sort by 结合使用是如何相较于 order by 提升运行效率的。

假如我们要对一张很大的用户信息表按照年龄进行分组，优化前的写法是直接 order by age 。使用 distribute by 和 sort by 结合进行优化的时候， sort by 后面还是 age 这个排序字段， distribute by 后面选择一个没有重复值的均匀字段，比如 user_id 。

这样做的原因是，通常用户的年龄分布是不均匀的，比如20岁以下和50岁以上的人非常少，中间几个年龄段的人又非常多，在 Map 阶段就会造成有些任务很大，有些任务很小。那通过 distribute by 一个均匀字段，就可以让系统均匀地进行“分桶”，对每个桶进行排序，最后再组合，这样就能从整体上提升 MapReduce 的效率。

取出 user_trade 表中全部支付用户：

原有写法的执行时长：

优化写法的执行时长：

考虑对之前的案例进行优化：

注意： 在极大的数据量（且很多重复值）时，可以先 group by 去重，再 count() 计数，效率高于直接 count(distinct ) 。

如果我们想知道用户的性别分布、城市分布、等级分布，你会怎么写？

通常写法：

缺点 ：要分别写三次SQL，需要执行三次，重复工作，且费时。

那该怎么优化呢？

注意 ：这个聚合结果相当于纵向地堆在一起了（Union all），分类字段用不同列来进行区分，也就是每一行数据都包含 4 列，前三列是分类字段，最后一列是聚合计算的结果。

GROUPING SETS() ：在 group by 查询中，根据不同的维度组合进行聚合，等价于将不同维度的 group by 结果集进行 union all。聚合规则在括号中进行指定。

如果我们想知道用户的性别分布以及每个性别的城市分布，你会怎么写？

那该怎么优化呢？

注意： 第二列为NULL的，就是性别的用户分布，其余有城市的均为每个性别的城市分布。

cube：根据 group by 维度的所有组合进行聚合

注意 ：跑完数据后，整理很关键！！！

rollup：以最左侧的维度为主，进行层级聚合，是cube的子集。

如果我想同时计算出，每个月的支付金额，以及每年的总支付金额，该怎么办？

那应该如何优化呢？

条条大路通罗马，写SQL亦是如此，能达到同样效果的SQL有很多种，要学会思路转换，灵活应用。

来看一个我们之前做过的案例：

有没有别的写法呢？

Hive 中互相没有依赖关系的 job 间是可以并行执行的，最典型的就是

多个子查询union all。在集群资源相对充足的情况下，可以开启并

行执行。参数设置： set hiveexecparallel=true;

时间对比：

所谓严格模式，就是强制不允许用户执行3种有风险的 HiveSQL 语句，一旦执行会直接报错。

要开启严格模式，需要将参数 hivemapredmode 设为 strict 。

好啦，这节课的内容就是这些。以上优化技巧需要大家在平时的练习和使用中有意识地去注意自己的语句，不断改进，就能掌握最优的写法。

前面已经有篇文章介绍如何编译包含hive的spark-assemblyjar了,不清楚的可以翻看一下前面的文章。clouderamanager装好的spark,直接执行spark-shell进入命令行后，写入如下语句：valhiveContext=neworgapachesparksqlhiveHiveContext(sc)你会发现没法执行通过，因为cm装的原生的spark是不支持sparkhql的，我们需要手动进行一些调整：第一步，将编译好的包含hive的JAR包上传到hdfs上配置的默认的spark的sharelib目录：/user/spark/share/lib第二步：在你要运行spark-shell脚本的节点上的/opt/cloudera/parcels/CDH-530-1cdh530p030/lib/spark/lib/目录下面，下载这个jar到这个目录：hadoopfs-gethdfs://n1:8020/user/spark/share/lib/spark-assembly-with-hive-mavenjar（具体路径替换成你自己的）。然后这个目录下面原来会有个软链接spark-assemblyjar指向的是spark-assembly-120-cdh530-hadoop250-cdh530jar，我们把这个软链接删除掉重新创建一个同名的软链接：ln-sspark-assembly-with-hive-mavenjarspark-assemblyjar，指向我们刚下载下来的那个JAR包，这个JAR包会在启动spark-shell脚本时装载到driverprogram的classpath中去的，sparkContext也是在driver中创建出来的，所以需要将我们编译的JAR包替换掉原来的spark-assemblyjar包，这样在启动spark-shell的时候，包含hive的spark-assembly就被装载到classpath中去了。第三步：在/opt/cloudera/parcels/CDH/lib/spark/conf/目录下面创建一个hive-sitexml。/opt/cloudera/parcels/CDH/lib/spark/conf目录是默认的spark的配置目录，当然你可以修改默认配置目录的位置。hive-sitexml内容如下：hivemetastorelocalfalsehivemetastoreuristhrift://n1:9083hivemetastoreclientsockettimeout300hivemetastorewarehousedir/user/hive/warehouse这个应该大家都懂的，总要让spark找到hive的元数据在哪吧，于是就有了上面一些配置。第四步：修改/opt/cloudera/parcels/CDH/lib/spark/conf/spark-defaultsconf，添加一个属性：sparkyarnjar=hdfs://n1:8020/user/spark/share/lib/spark-assembly-with-hive-mavenjar。这个是让每个executor下载到本地然后装载到自己的classpath下面去的，主要是用在yarn-cluster模式。local模式由于driver和executor是同一个进程所以没关系。以上完事之后，运行spark-shell,再输入:valhiveContext=neworgapachesparksqlhiveHiveContext(sc)应该就没问题了。我们再执行一个语句验证一下是不是连接的我们指定的hive元数据库：hiveContextsql("showtables")take(10)//取前十个表看看最后要重点说明一下这里的第二步第三步和第四步，如果是yarn-cluster模式的话，应该替换掉集群所有节点的spark-assemblyjar集群所有节点的sparkconf目录都需要添加hive-sitexml，每个节点spark-defaultsconf都需要添加sparkyarnjar=hdfs://n1:8020/user/spark/share/lib/spark-assembly-with-hive-mavenjar。可以写个shell脚本来替换，不然手动一个一个节点去替换也是蛮累的。

为什么使用Hive？ 为什么使用Hive？那么，在哪里使用Hive呢？在载入了60亿行（经度、维度、时间、数据值、高度）数据集到MySQL后，系统崩溃了，并经历过数据丢失。这可能部分是因为我们最初的策略是将所有的数据都存储到单一的一张表中了

1、hive 命令行模式，直接输入/hive/bin/hive的执行程序，或者输入 hive --service cli

用于linux平台命令行查询，查询语句基本跟mysql查询语句类似

2、 hive web界面的 (端口号9999) 启动方式

hive –service hwi &

用于通过浏览器来访问hive，感觉没多大用途

3、 hive 远程服务 (端口号10000) 启动方式

hive --service hiveserver &

或者

hive --service hiveserver 10000>/dev/null 2>/dev/null &

备注：

连接Hive JDBC URL：jdbc:hive://1921686116:10000/default （Hive默认端口：10000 默认数据库名：default）

这是由于 hive 找不到存放元数据库的数据库而导致的，修改 conf/ hive-defaultxml 配置文件中的 hivemetastorelocal 为 true 即可。由于 hive 把结构化数据的元数据信息放在第三方数据库，此处设置为 true ， hive 将在本地创建 derby 数据库用于存放元数据。当然如果有需要也可以采用 mysql 等第三方数据库存放元数据，不过这时 hivemetastorelocal 的配置值应为 false 。

settingpy:都要配置下USER, PASSWORD, HOST

MySQL插件 ：MySQL-python version 121p2 or higher

ENGINE ：djangodbbackendsmysql

NAME:你的数据库名字

PostgreSQL插件：postgresql_psycopg2 地址>

以上就是关于数据分析课程笔记 - 19 - HiveSQL 常用优化技巧全部的内容，包括:数据分析课程笔记 - 19 - HiveSQL 常用优化技巧、hue/oozie 调度shell执行hive脚本、怎么在hive中建database等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！