Hive_5. Hive QL -- Hive 查询优化

• HiveQL 查询 _1 (翻译于 《Programing Hive》)：http://flyingdutchman.iteye.com/blog/1869472
• HiveQL 查询 _2 (翻译于 《Programing Hive》)：http://flyingdutchman.iteye.com/blog/1869621
• HiveQL 查询 _3 (翻译于 《Programing Hive》)：http://flyingdutchman.iteye.com/blog/1869687

关于 Hive 的基本查询这边我也就不再多说了，大家可以参考上面提供的链接。这里主要跟大家讨论一下 Hive 的查询优化。

Hive 提供了简单的语法以及可伸缩性让我们能够方便的进行数据处理，以至于我们忽略了在 Hive 中的优化操作。通过对表的精确设计和查询能够大大提高查询效率以及降数据低处理的成本。在这里将跟大家讨论一下在做 ETL 的时候，我们可以通过哪些途径来优化 Hive 的查询。
我们可以通过以下三种方式进行优化：

• 数据格式 -- 分区表 & 桶表 
• 数据抽样 -- 桶表抽样 & 块抽样
• 数据处理 -- 桶表的 Map Join 和 并行处理

首先介绍一下我们的测试数据：我们有三张表的数据，第一张 机票预订 表含有2.76亿条完整记录从出发地到目的地的航空旅行线路信息，其中行程标识作为主键；第二张 机票语句来源 包含了旅行中的第一站信息；最后一张表是美国人口每个州的普查信息。

接下来让我们看看如何利用这些示例数据进行优化 Hive 查询。

1. Hive 分区表

默认情况下，Hive 的查询(select) 和 过滤(过滤) 会遍历整张表。当遇到大表是，查询性能会变得非常缓慢。

在 Hive 中，每个分区表都关联一个预分区的列来存储这张表在 HDFS 路径下的子目录。当查询分区表的时候，只会查询所需要的分区路径下的数据，这样使得 I/O 和 查询时间变得更加高效。 需要注意的是，分区不会自动 enabled。  我们需要使用 ALTER TABLE ADD PARTITION 语句为一张表添加分区。 ADD PARTITION 会更改表的元数据，但是不会加载数据。如果分区位置不存在数据，查询就不会返回任何结果。如果你希望擅长分区表的数据和元数据，可以使用：ALTER TABLE DROP PARTITION 语句。具体详解可以参考：Hive_3.DDL -- 分区表 & 桶表 & 视图：http://blog.csdn.net/mike_h/article/details/50121135

并不是说分区越多就越好，我们也需要考虑可能分区列的基数，避免出现太多的数据碎片。对行程 ID 分区显然是一个非常笨拙的方案，因为会导致 HDFS 下生产大量的小文件，因为就算每个文件中只有几个字节的数据量，HDFS通常也会使用一个大于等于64MB的块(文件)来进行存储。

2. 桶表

桶表对应着HDFS中文件的Block(64/128/256..)，它可以让集群数据更容易管理和查询优化。 

例如：表是根据年和月进行的top-level分区。如果有进一步需求要根据employee_id来做第三次分区,这样会导致许多小的分区和目录。例如，我们使用employee_id做为桶表employee_partitioned 的桶列。桶列会通过用户定义的数字被 Hash 到桶中，具有相同 employee_id 会存储在相同的桶(分割的文件)中。

在该例中，我们利用两个表的行程 ID 作为哈希的字段，然后利用桶表中的对应哈希值来对两张表进行 JOIN。

在使用桶表进行查询及数据操作的时候，我们需要注意以下几点：

• Hive 需要我们提供桶表创建时的聚集列和子桶的个数。桶表跟底层的数据加载密切相关。
• 为了正确加载数据到桶表中，我们可以设置最大的 reducer 数量跟创建表时指定的桶数相同(例如：2)，或者强制允许使用桶表：set hive.enforce.bucketing = true;
• 在查询的时候，我们希望在Join 中利用桶表的特性（可以设置 set hive.optimize.bucketmapjoin=true），之后Select 语句就可以包含 MAPJOIN 语句了，既可以确保不会因为数据倾斜导致某个 reduce 上落太多数据而failed，同时 mapjoin 还有一个好处是能够进行不等连接的join操作，如果将不等条件写在where中，那么mapreduce过程中会进行笛卡尔积，运行效率特别低，如果使用mapjoin操作，在map的过程中就完成了不等值的join操作，效率会高很多。
• 在将数据加载到桶表中，我们不能使用LOAD 关键字，因为 LOAD 不会验证加载数据的元数据。我们可以使用 INSERT 语句来为桶表加载数据。

Create table Bucket_Table(userid INT, pages String, views INT)
Partitioned by(DT String)
Cloustered by(userid) Stored by(dt) INTO 64 BUCKETS

桶数:
在定义合适的桶数时，我们需要避免每个桶拥有太多/少的数据。最好的选择是使用 2 个左右的桶来存储数据。例如，我们计划往每个桶放512MB 数据，如果 Hadoop Blcok 的大小是256MB，如果有可能的话，使用 2N 作为桶数。

3: 桶表抽样

我们通常希望能够对一个大数据集的步伐数据进行迭代操作，并不想对整个数据集进行处理，但是这样在两张表JOIN的时候导致只有一部分数据进行重叠。在实际业务中，这肯定是无法接受的。因此我们就利用桶表抽样来对两张表相同的 IDs 进行哈希操作，之后根据该哈希值进行 JOIN.
桶表抽样是一种使用桶表进行优化的抽样。
其中的 colname 值指定抽样数据的列， 在对整行抽样的时候可以同样可以使用RAND()函数。如果抽样字段跟 CLUSTERED BY 字段相同，TABLESAMPLE 语句会更加高效。它的语法如下所示：

SELECT * FROM <Table_Name> 
TABLESAMPLE(BUCKET <指定抽样的桶号> OUT OF <桶的总数> ON [colname|RAND()]) table_alias;  

4. 块抽样[Block Sampling]:

它允许 Hive随机抽选 N 行数据 & (N%)百分比的数据量 & N 字节的数据。抽样的粒度是 HDFS Block 的大小。
我们经常需要对一张表进行数据抽样以便于进一步探索查询性能和数据。在这种情况下,我们可能不希望通过桶表来操作或我们需要更多的随机样本数据(依据桶表列哈希的值)，或者降低粒度。

块采样提供了一个强大的TABLESAMPLE语句来定义了对数据表进行抽样的多种方法。我们可以根据百分比、字节大小、数据行来进行抽样。我们可以利用抽样粗略估计信息，比如说 出发地和目的地之间的平均距离。如果我们只想使用一个大数据集1%的数据，可以通过  

TABLESAMPLE(1 PERCENT) 来实现。块抽样提供的丰富抽样方法能够让我们能以更少的时间时间来处理 PB 级别的数据。
块抽样的语法结构如下所示：

SELECT * 
FROM <Table_Name> TABLESAMPLE(N PERCENT|ByteLengthLiteral|N ROWS) s;
-- ByteLengthLiteral
-- (Digit)+ ('b' | 'B' | 'k' | 'K' | 'm' | 'M' | 'g' | 'G') 

--按行抽样 

jdbc:hive2://> SELECT name 
. . . . . . .> FROM employee_id_buckets TABLESAMPLE(4 ROWS) a;
+----------+
| name |
+----------+
| Lucy |
| Shelley |
| Lucy |
| Shelley |
+----------+
4 rows selected (0.055 seconds)

--根据数据比例大小来抽样 

jdbc:hive2://> SELECT name 
. . . . . . .> FROM employee_id_buckets TABLESAMPLE(10 PERCENT) a;
+----------+
| name |
+----------+
| Lucy |
| Shelley |
| Lucy |
+----------+
3 rows selected (0.061 seconds)

--根据数据大小进行抽样 

jdbc:hive2://> SELECT name 
. . . . . . .> FROM employee_id_buckets TABLESAMPLE(3M) a;
+----------+
| name |
+----------+
| Lucy |
| Shelley |
| Lucy |
| Shelley |
| Lucy |
| Shelley |
| Lucy |
| Shelley |
| Lucy |
| Will |
| Shelley |
| Lucy |
| Will |
| Shelley |
| Michael |
| Will |
| Shelley |
| Lucy |
| Will |
| Will |
| Will |
| Will |
| Will |
| Lucy |
| Shelley |
+----------+
25 rows selected (0.07 seconds)

5. 并行执行：

Hive 可以利用 Hadoop能够并行执行MapReduce Job 的机制来同时执行多个查询。复杂的 Hive 查询语句将会被转换成若干个 map reduce job 顺序执行。通常情况下，一个查询的 map 和 reduce 阶段是不相互依赖的并且可以并行执行。因此我们可以利用集群的并行能力来提高效率，减少查询整体消耗的世界。我们可以通过设置参数  hive.exce.parallel = true 来使 Hive 无依赖语句并行化。

从上图可以发现，这两个子查询是独立的，因此我们可以通过并行进行处理，在实际操作中的执行时间减少了50%。有兴趣的朋友可以自己尝试一下。