关于优化Hive查询速度的一些实践

写在前面

由于工作需要，笔者在最近的日常工作中大量使用了Hive。由于其查询速度较为缓慢，笔者花了大量的时间去学习如何优化Hive的查询速度。所谓久病成良医，笔者在这过程中也积累了一些经验，在此做一个分享与总结，也希望能帮助到需要的人。另外，许多数据分析的JD中都要求有Hive经验。因此，有志于转行数据分析的朋友们也可以借此了解一下Hive。

本文基于Hue运行Hive。

PS：笔者对Hive及其相关理论的认识比较粗浅，存在错误之处还请大家及时指正。本文仅记录笔者亲身实践过的并行之有效的一些方法。

Why Hive

说到Hive就不能不提Hadoop。Hadoop作为大数据必备框架，近年来大红大紫。为了不误导读者，在此引用专业名词解释：

关于Hadoop的更多知识，可以查看如下链接：

之前笔者苦于查询速度过慢，曾经向资深同事问过一个问题：为啥不用MySQL？

这个其实和公司的业务有关。在数据开发工程师设计数据库架构时，就需要考虑到公司业务的需求和将来可能的数据量大小。从数据量和查询速度上看，传统的数据库可以做到“小而美”，即在数据量不大时可以提供较快的查询。而Hive基于MapReduce框架进行运行，本身就具有高延迟性，在数据量较小时非常明显。当数据量较大时，Hive的并行运算优势就会体现出来。

此处再引用一个比较好的解释：

以上看不懂没关系，因为笔者也不是太懂。总而言之，笔者由于架构的限制，必须使用Hive在线查询数据量并不大的数据仓库（约十几万条），因此被迫寻找了一些优化措施。

Impala

由于在实际编写SQL的过程需要不断调整与优化（自身水平的局限性），而Hive的高延迟极大地降低了SQL试错的速度，因此引入Impala作为一种快速的查询手段用于SQL语句的调试。

但是，由于底层算法不同，impala在提供低延迟的同时，也有以下缺点：

它的几个缺点会在下文的实践中坑到我们。

实践过的优化方式

1.使用With as

拖累Hive运行速度的关键是子查询，当子查询中使用了join、count(distinct)+group by时会进一步减慢运行速度，增加数据倾斜。另外，虽然Hive对union all语句提供了优化，但该优化对于带有子查询的union all无效。

因此，优化的重点就是消灭子查询。在此推荐with as语句。with as是将语句中用到的子查询事先提取出来（类似临时表），使整个查询当中的所有模块都可以调用该查询结果。使用with as可以避免Hive对不同部分的相同子查询进行重复计算。

在此列举一个优化实例：

优化前的语句节选如下（第一次看到时笔者是崩溃的），查询用时1h21m：

该语句用于统计某类订单的数量。从上述SQL中发现，其中的每个子查询模块都使用到了几个相同的条件，如amt=12345、tr=xx、to_date(time)>='20xx-xx-xx'，因此可以使用with as将这些条件先提取出来，以供所有子模块使用。优化后的查询语句如下：

该查询的思路是：使用cr1部分提取公共条件，然后在使用cr2、3、4对cr1进行进一步细分。最后直接从cr2、3、4中筛选数据。该语句查询用时42分，查询速度提升较多。

2.慎用select *、order by

在使用with as提取公共条件时，只查询需要使用到的字段即可。如果原始表中的字段较多，提取无用字段会增加Hive的工作量（尤其是多表join时）。另外，order by对资源的占用也比较多，Hive会对全表进行扫描再进行排序。如果没有排序筛选的需求（如limit），可以先不用order by查询出结果，然后利用其它工具对查询结果进行排序。

3.巧妙利用impala

impala的查询速度比hive快了几个数量级，用好impala会使查询工作事半功倍。但在使用impala过程中会面临较多的限制，这是由于其特性所导致的。

首先，impala对语法的要求比较严格。例如我们需要在筛选条件中设置“字段A”=“字段B”，其中字段A=12345，字段B=“12345”，那么在Hive中，Hive会自动将B转化为数值型，然后完成对比。但impala没有这一功能。直接对比两者会报错。在实际的工作当中，由于字段格式不规范所带来的这一问题比较常见。因此将Hive语句放到impala中执行时，需要注意对字段格式进行转换。对于string、int、float型，可以使用cast(column_name as type)的方式进行转换；对于日期类型，可以使用to_date(date)将timestamp数据转换为string再进行对比。

其次，impala不支持同时执行多个count(distinct)。即每次只能执行一个（严格来说是每次只能对同一字段执行distinct）。这点非常之坑爹。不完全解决办法有以下几点：

最后，impala对表的要求比较严格。笔者在实践中遇到过如下错误：

而同样的语句在Hive中可以运行。其原因就是impala不支持该表的类型。解决方法是先用Hive复制该表并存储为parquet格式：

创建完成该表的副本后，在impala中并无法看到该表，还需要进行一下操作：

运行后即可看到新创建的表。然后再重新运行查询语句即可。

将第1节优化后的案例代码进行调整以适应impala，然后在impala上运行，用时仅1.6s。

写在最后

本文记录了笔者最近工作中使用到的优化方法，利用这些方法大大提高了工作效率，同时也认识到了自身的很多有待提高之处。关于更高阶的优化理论和方法，推荐一篇文章如下：

http://www.cnblogs.com/smartloli/p/4356660.html