Hive调优之设计篇

从设计上来考虑HQL的优化，会涉及到表设计、数据格式以及Job任务优化等。

分区表设计
Hive 分区是最有效的优化查询性能的方法之一。使用分区过滤的查询只加载特定分区（子目录）的数据，因此查询性能肯定比不适用分组的查询更快。分区键值选择的优劣通常是影响性能的重要因素。分区键值的选择应当是非重复数据相对较少，以避免使用过多的子目录。以下为分区键值选择的通常做法：

按照日期和时间分区：当数据和日期/时间列比较相关，使用日期和时间，如年、月、日（甚至小时）等作为分区键列，这些相关分区键值列为日期或者时间数据类型，如load_date, business_date, run_date等。
按照地域分区：当数据和地理位置等信息相关，使用国家、区域、州/省和城市作为分区键值。
按照业务逻辑分区：当数据可以按照业务逻辑来进行区分，可以使用部门、销售区域、应用、客户等业务逻辑相关的数据列做为分区键列。

Bucket表设计
与分区表类似，Bucket表把数据放在HDFS中不同的单独的文件中。Bucket表可以加速数据取样。同时当关联列同时是Bucket列时，Bucket表可以改善关联性能。由于Bucket列可以确保关联列只出现在某个特定的Bucket里，所以Bucket列同时是关联列时，关联性能会大大改善。较好选择的Bucket列意味着关联查询的关联列更大可能和Bucket列一致，因此通常把最有可能用于关联或者过滤的列作为Bucket列。
Index设计
使用Index用于改善性能是关系型数据库最常用的调优手段。Hive自0.7.0之后，也支持在表或者分区上创建索引。Hive中的索引提供一个基于Key的数据视图，以及支持WHERE、GROUP BY 和JOIN等操作更好地进行数据访问。创建Index的示例为，

> CREATE INDEX idx_id_employee_id
> ON TABLE employee_id (employee_id)
> AS 'COMPACT'
> WITH DEFERRED REBUILD;
No rows affected (1.149 seconds)

COMPACT索引保存索引列的值和它的Block ID。
BITMAP索引（自0.8.0后支持）适用于列值为较少变量值的情况。以下为BITMAP索引的示例，

> CREATE INDEX idx_gender_employee_id
> ON TABLE employee_id (gender_age)
> AS 'BITMAP'
> WITH DEFERRED REBUILD;
No rows affected (0.251 seconds)

WITH DEFERRED REBUILD选项，指定索引不会立即重建。为了重建索引，我们可以执行 ALTER INDEX ...REBUILD命令。

ALTER INDEX idx_id_employee_id ON employee_id REBUILD;
No rows affected (111.413 seconds)
ALTER INDEX idx_gender_employee_id ON employee_id REBUILD;
No rows affected (82.23 seconds)
一旦索引创建成功，每个索引会对应创建一个索引表，该索引表的命名格式为 <database_name><table_name><index_name>_:

> SHOW TABLES '*idx*';
+-----------+---------------------------------------------+-----------+
|TABLE_SCHEM|                 TABLE_NAME                  | TABLE_TYPE|
+-----------+---------------------------------------------+-----------+
|default    |default__employee_id_idx_id_employee_id__    |INDEX_TABLE|
|default    |default__employee_id_idx_gender_employee_id__|INDEX_TABLE|
+-----------+---------------------------------------------+-----------+

索引表包含索引列、_bucketname和_off_sets，共三列。如下例所示，

> DESC default__employee_id_idx_id_employee_id__;
+--------------+----------------+----------+
|   col_name   |   data_type    | comment  |
+--------------+----------------+----------+
| employee_id  | int            |          |
| _bucketname  | string         |          |
| _offsets     | array<bigint>  |          |
+--------------+----------------+----------+
3 rows selected (0.135 seconds)

> SELECT * FROM default__employee_id_idx_id_employee_id__;
+--------------+------------------------------------------------------+
| employee_id  | _bucketname                               | _offsets |
+--------------+------------------------------------------------------+
| 100          | .../warehouse/employee_id/employee_id.txt | [0]      |
| 101          | .../warehouse/employee_id/employee_id.txt | [66]     |
| 102          | .../warehouse/employee_id/employee_id.txt | [123]    |
| ...          |                  ...             ...      | ...      |
+--------------+-------------------------------------------+----------+
25 rows selected (0.219 seconds)

删除索引的命令如下所示，删表不会删相关索引。

DROP INDEX index_name ON table_name

skewed/temporary 表的使用
自Hive 0.10.0之后，HQL开始支持创建skewed表。通过把skewed数据区分开来自动放入专门的文件和文件夹内，查询就只访问或者不访问这些数据，这样在查询中用到的文件和分区文件夹数量变小，从而改善性能。以下为创建skewed表示例，

> CREATE TABLE sample_skewed_table (
> dept_no int, 
> dept_name string
> ) 
> SKEWED BY (dept_no) ON (1000, 2000); -- Specify value skewed
No rows affected (3.122 seconds)

> DESC FORMATTED sample_skewed_table;
+-----------------+------------------+---------+
| col_name        | data_type        | comment |
+-----------------+------------------+---------+
| ...             | ...              |         |
| Skewed Columns: | [dept_no]        | NULL    |
| Skewed Values:  | [[1000], [2000]] | NULL    |
| ...             | ...              |         |
+-----------------+------------------+---------+
33 rows selected (0.247 seconds)

临时表（temporary table）可以把多次使用的中间结果单独存放，以节省重建这些共享共通数据集所消耗的资源。另外也可以把它存放在SSD或者内存中以改善性能。