写在前面
由于工作需要，笔者在最近的日常工作中大量使用了Hive。由于其查询速度较为缓慢，笔者花了大量的时间去学习如何优化Hive的查询速度。所谓久病成良医，笔者在这过程中也积累了一些经验，在此做一个分享与总结，也希望能帮助到需要的人。另外，许多数据分析的JD中都要求有Hive经验。因此，有志于转行数据分析的朋友们也可以借此了解一下Hive。
本文基于Hue运行Hive。
PS：笔者对Hive及其相关理论的认识比较粗浅，存在错误之处还请大家及时指正。本文仅记录笔者亲身实践过的并行之有效的一些方法。
Why Hive
说到Hive就不能不提Hadoop。Hadoop作为大数据必备框架，近年来大红大紫。为了不误导读者，在此引用专业名词解释：一句话产品介绍:
Apache Hadoop: 是Apache开源组织的一个分布式计算开源框架，提供了一个分布式文件系统子项目(HDFS)和支持MapReduce分布式计算的软件架构。
Apache Hive: 是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，通过类SQL语句快速实现简单的MapReduce统计，不必开发专门的MapReduce应用，十分适合数据仓库的统计分析。
关于Hadoop的更多知识，可以查看如下链接：Hadoop家族学习路线图 | 粉丝日志blog.fens.me
之前笔者苦于查询速度过慢，曾经向资深同事问过一个问题：为啥不用MySQL？
这个其实和公司的业务有关。在数据开发工程师设计数据库架构时，就需要考虑到公司业务的需求和将来可能的数据量大小。从数据量和查询速度上看，传统的数据库可以做到“小而美”，即在数据量不大时可以提供较快的查询。而Hive基于MapReduce框架进行运行，本身就具有高延迟性，在数据量较小时非常明显。当数据量较大时，Hive的并行运算优势就会体现出来。
此处再引用一个比较好的解释：Hive是为了简化编写MapReduce程序而生的。Hive本身不存储和计算数据，它完全依赖于HDFS和MapReduce，Hive中的表纯逻辑，就是些表的定义等，也就是表的元数据。使用SQL实现Hive是因为SQL大家都熟悉，转换成本低，类似作用的Pig就不是SQL。
以上看不懂没关系，因为笔者也不是太懂。总而言之，笔者由于架构的限制，必须使用Hive在线查询数据量并不大的数据仓库(约十几万条)，因此被迫寻找了一些优化措施。
Impala
由于在实际编写SQL的过程需要不断调整与优化(自身水平的局限性)，而Hive的高延迟极大地降低了SQL试错的速度，因此引入Impala作为一种快速的查询手段用于SQL语句的调试。什么是Impala？
Impala是用于处理存储在Hadoop集群中的大量数据的MPP(大规模并行处理)SQL查询引擎。 它是一个用C ++和Java编写的开源软件。 与其他Hadoop的SQL引擎相比，它提供了高性能和低延迟。
换句话说，Impala是性能最高的SQL引擎(提供类似RDBMS的体验)，它提供了访问存储在Hadoop分布式文件系统中的数据的最快方法。
但是，由于底层算法不同，impala在提供低延迟的同时，也有以下缺点：1.Impala不提供任何对序列化和反序列化的支持。
2.Impala只能读取文本文件，而不能读取自定义二进制文件。
3.每当新的记录/文件被添加到HDFS中的数据目录时，该表需要被刷新。
它的几个缺点会在下文的实践中坑到我们。
实践过的优化方式
1.使用With as
拖累Hive运行速度的关键是子查询，当子查询中使用了join、count(distinct)+group by时会进一步减慢运行速度，增加数据倾斜。另外，虽然Hive对union all语句提供了优化，但该优化对于带有子查询的union all无效。
因此，优化的重点就是消灭子查询。在此推荐with as语句。with as是将语句中用到的子查询事先提取出来(类似临时表)，使整个查询当中的所有模块都可以调用该查询结果。使用with as可以避免Hive对不同部分的相同子查询进行重复计算。
在此列举一个优化实例：
优化前的语句节选如下(第一次看到时笔者是崩溃的)，查询用时1h21m：
select aa.ap_date,aa.ap,bb.cl,cc.l,0 m_1,dd.m0,ee.m1,ee.m2,ee.m3,
ee.m4,ee.m4_PLUS,ff.m1 as u_m1,ff.m2 as u_m2,ff.m3 as u_m3,
ff.m4 as u_m4,ff.m4_PLUS as u_m4_PLUS from
(
(select month(ap_time) ap_date,count(distinct unique_id) ap from aps
where amt = 12345 and pe = xx
and to_date(ap_time) > '20xx-xx-xx'
group by month(ap_time)
) aa
left join
(
select to_date(a.ph_time) ph_date,count(distinct a.unique_id) cl from uwt a
left join aps b on a.unique_id = b.unique_id
where b.amt = 12345 and b.pe = xx
and to_date(phone_audit_time) > '20xx-xx-xx'
group by to_date(a.ph_time)
) bb on aa.ap_date = bb.ph_date
left join
(
select to_date(l_time) l_date, count(distinct unique_id) l from la
where amt = 12345 and tr = xx
and to_date(l_time) >= '20xx-xx-xx'
group by to_date(l_time)
) cc on aa.ap_date = cc.l_date
left join
(
select to_date(l_time) l_date,count(distinct unique_id) m0
from rpm
where sta = 'A'
and amt = 12345 and tr = xx
and to_date(l_time) >= '20xx-xx-xx'
group by to_date(l_time)
) dd on aa.ap_date = dd.l_date
left join
(
select to_date(l_time) l_date,
COUNT(DISTINCT CASE WHEN (oas > x1 and oas <= x2) THEN unique_id END) m1,
COUNT(DISTINCT CASE WHEN (oas > x2 and oas <= x3) THEN unique_id END) m2,
COUNT(DISTINCT CASE WHEN (oas > x3 and oas <= x4) THEN unique_id END) m3,
COUNT(DISTINCT CASE WHEN (oas > x4 and oas <= x5) THEN unique_id END) m4,
COUNT(DISTINCT CASE WHEN (oas > x5) THEN unique_id END) m4_PLUS
from rpm
where sta = 'B'
and amt = 12345 and tr = xx
and to_date(l_time) >= '20xx-xx-xx'
group by to_date(l_time)
) ee on aa.ap_date = ee.l_date
left join
(
select to_date(l_time) l_date,
COUNT(DISTINCT CASE WHEN (oas > x1 and oas <= x2) THEN unique_id END) m1,
COUNT(DISTINCT CASE WHEN (oas > x2 and oas <= x3) THEN unique_id END) m2,
COUNT(DISTINCT CASE WHEN (oas > x3 and oas <= x4) THEN unique_id END) m3,
COUNT(DISTINCT CASE WHEN (oas > x4 and oas <= x5) THEN unique_id END) m4,
COUNT(DISTINCT CASE WHEN (oas > x5) THEN unique_id END) m4_PLUS
from rpm
where sta = 'C'
and amt = 12345 and tr = xx
and to_date(l_time) >= '20xx-xx-xx'
group by to_date(l_time)
) ff on aa.ap_date = ff.l_date
该语句用于统计某类订单的数量。从上述SQL中发现，其中的每个子查询模块都使用到了几个相同的条件，如amt=12345、tr=xx、to_date(time)>='20xx-xx-xx'，因此可以使用with as将这些条件先提取出来，以供所有子模块使用。优化后的查询语句如下：
with
cr1 as
(select a.ap_time,a.unique_id,a.amt,a.pe,b.ph_time,c.l_time,d.sta,d.oas
from aps a left join uwt b on a.unique_id=b.unique_id
left join l c on a.unique_id=c.unique_id left join rpm d
on a.unique_id=d.unique_id
where to_date(a.ap_time) >= '20xx-xx-xx'),
cr2 as
(select to_date(cr1.ap_time) ap_date,count(distinct cr1.unique_id) ap
from cr1 where amt=12345 and pe=x group by to_date(ap_time)),
cr3 as
(select to_date(cr1.ph_time) ph_time,count(distinct cr1.ph_time) cl
from cr1 where amt=12345 and pe=x group by to_date(ph_time)),
cr4 as
(select to_date(cr1.l_time) l_time2,count(cr1.l_time) l,
count(case when sta='A' then sta END) m0,
count(CASE WHEN (oas > x1 and oas <= x2) and sta='B' THEN sta END) m1,
count(CASE WHEN (oas > x2 and oas <= x3) and sta='B' THEN sta END) m2,
count(CASE WHEN (oas > x3 and oas <= x4) and sta='B' THEN sta END) m3,
count(CASE WHEN (oas > x4 and oas <= x5) and sta='B' THEN sta END) m4,
count(CASE WHEN (oas > x5) and sta='B' THEN sta END) m4_PLUS,
count(CASE WHEN (oas > x1 and oas <= x2) and sta='C' THEN sta END) u_m1,
count(CASE WHEN (oas > x2 and oas <= x3) and sta='C' THEN sta END) u_m2,
count(CASE WHEN (oas > x3 and oas <= x4) and sta='C' THEN sta END) u_m3,
count(CASE WHEN (oas > x4 and oas <= x5) and sta='C' THEN sta END) u_m4,
count(CASE WHEN (oas > x5) and sta='C' THEN sta END) u_m4_PLUS,
from cr1 where amt=12345 and pe=x group by to_date(l_time))
select cr2.ap_date,cr2.ap,cr3.cl,cr4.l,0 m_1,cr4.m0,cr4.m1,cr4.m2,cr4.m3,
cr4.m4,cr4.m4_PLUS,cr4.u_m1,cr4.u_m2,cr4.u_m3,cr4.u_m4,cr4.u_m4_PLUS
from cr2 left join cr3 on cr2.ap_date=cr3.ph_time left join cr4
on cr2.ap_date=cr4.l_time2该查询的思路是：使用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的NDV函数。它返回一个count(distinct)的估计值，和精确结果有一些偏差
利用子查询或with as事先去重
分次执行代码，每次只执行一个count(distinct)
最后，impala对表的要求比较严格。笔者在实践中遇到过如下错误：impala does not support tables of this type. reason: serde library 'org.apache.hadoop.hive.serde2.opencsvserde' is not supported.
而同样的语句在Hive中可以运行。其原因就是impala不支持该表的类型。解决方法是先用Hive复制该表并存储为parquet格式：
CREATE TABLE copy_of_table
STORED AS PARQUET AS
SELECT * FROM your_original_table创建完成该表的副本后，在impala中并无法看到该表，还需要进行一下操作：
INVALIDATE METADATA
运行后即可看到新创建的表。然后再重新运行查询语句即可。
将第1节优化后的案例代码进行调整以适应impala，然后在impala上运行，用时仅1.6s。
