Hive-基础知识-二-

Hive 基础知识（二）

原文：Apache Hive Essentials

协议：CC BY-NC-SA 4.0

六、数据汇总和采样

This chapter is about how to aggregate and sample data in HQL. It first covers the use of several aggregate functions, enhanced aggregate functions, and window functions working with a GROUP BY*,* PARTITION BY statement. Then, it introduces the different ways of sampling data. In this chapter, we will cover the following topics:

• 基本聚合
• 增强型聚合
• 聚集条件
• 窗口函数
• 采样 / 取样 / 抽样 / 样品

基本聚合

数据聚合是根据特定条件收集并以摘要形式表示数据以获得有关特定群体的更多信息的过程。 HQL 提供了几个内置聚合函数，如max(...)、min(...)和avg(...)。 它还支持使用诸如GROUPING SETS、ROLLUP和CUBE等关键字以及不同类型的窗口函数的高级聚合。

基本的内置聚合函数通常与GROUP BY子句一起使用。 如果没有指定GROUP BY子句，则默认情况下，它将聚合整个行(所有列)。 除了聚合函数外，GROUP BY子句中还必须包括所有选定的列。 以下是涉及内置聚合函数的几个示例：

1. 不带GROUP BY列的数据聚合：

 > SELECT 
 > count(*) as rowcnt1, 
 > count(1) as rowcnt2 -- same to count(*)
 > FROM employee;
 +---------+---------+
 | rowcnt1 | rowcnt2 |
 +---------+---------+
 | 4       | 4       |
 +---------+---------+
 1 row selected (0.184 seconds)

Sometimes, the basic aggregate function call returns the result immediately, such as in the previous example, where it took less than 0.2 seconds. The reason is that Hive fetches such aggregation results directly from the statistics collected (introduced in Chapter 8, Extensibility Considerations). To get the aggregation by actually running a job, you may need to add a limit or where clause in the query.

2. 包含GROUP BY列的聚合：

 > SELECT 
 > gender_age.gender, count(*) as row_cnt
 > FROM employee
 > GROUP BY gender_age.gender;
 +--------------------+----------+
 | gender_age.gender  | row_cnt  |
 +--------------------+----------+
 | Female             | 2        |
 | Male               | 3        |
 +--------------------+----------+
 2 rows selected (100.565 seconds)

-- The column name selected is not a group by columns causes error
 > SELECT 
 > name, gender_age.gender, count(*) as row_cnt
 > FROM employee GROUP BY gender_age.gender;
 Error: Error while compiling statement: FAILED: SemanticException 
      [Error 10025]: Line 2:1 Expression 
      not in GROUP BY key 'name' (state=42000,code=10025) 

If we have to select columns that are not GROUP BY columns, one way is to use window functions, which are introduced later.

聚合函数可以与同一SELECT语句中的其他聚合函数一起使用。 它还可以嵌套的方式与其他函数(如条件函数)一起使用。 但是，不支持嵌套聚合函数。 有关更多详细信息，请参阅以下示例：

1. 同一SELECT语句中有多个聚合函数：

 > SELECT 
 > gender_age.gender, avg(gender_age.age) as avg_age,
 > count(*) as row_cnt
 > FROM employee GROUP BY gender_age.gender; 
 +--------------------+---------------------+----------+
 | gender_age.gender  |       avg_age       | row_cnt  |
 +--------------------+---------------------+----------+
 | Female             | 42.0                | 2        |
 | Male               | 31.666666666666668  | 3        |
 +--------------------+---------------------+----------+
 2 rows selected (98.857 seconds)

2. 聚合函数还可以与CASE WHEN THEN ELSE END、coalesce(...)或if(...)一起使用：

 > SELECT 
 > sum(CASE WHEN gender_age.gender = 'Male'
 > THEN gender_age.age ELSE 0 END)/
 > count(CASE WHEN gender_age.gender = 'Male' THEN 1
 > ELSE NULL END) as male_age_avg 
 > FROM employee;
 +---------------------+
 |    male_age_avg     |
 +---------------------+
 | 31.666666666666668  |
 +---------------------+
 1 row selected (38.415 seconds)

 > SELECT
 > sum(coalesce(gender_age.age,0)) as age_sum,
 > sum(if(gender_age.gender = 'Female',gender_age.age,0)) as 
      female_age_sum
 > FROM employee;
 +----------+----------------+
 | age_sum  | female_age_sum |
 +----------+----------------+
 | 179      | 84             |
 +----------+----------------+
 1 row selected (42.137 seconds)

3. GROUP BY也可以应用于表达式：

 > SELECT
 > if(name = 'Will', 1, 0) as name_group, 
 > count(name) as name_cnt 
 > FROM employee 
 > GROUP BY if(name = 'Will', 1, 0);
 +------------+----------+
 | name_group | name_cnt |
 +------------+----------+
 | 0          | 3        |
 | 1          | 1        |
 +------------+----------+
 2 rows selected (23.749 seconds)

4. 验证是否不允许嵌套聚合函数：

 > SELECT avg(count(*)) as row_cnt FROM employee;
 Error: Error while compiling statement: FAILED: SemanticException 
      [Error 10128]: Line 1:11 Not yet 
      supported place for UDAF 'count' (state=42000,code=10128)

5. 诸如max(...)或min(...)之类的聚合函数应用于NULL，并返回NULL。 但是，像sum()和avg(...)这样的函数不能适用于NULL。 count(null)返回 0。

 > SELECT max(null), min(null), count(null);
 +------+------+-----+
 | _c0  | _c1  | _c2 |
 +------+------+-----+
 | NULL | NULL |  0  |
 +------+------+-----+
 1 row selected (23.54 seconds)

 > SELECT sum(null), avg(null);
 Error: Error while compiling statement: FAILED: 
      UDFArgumentTypeException Only numeric or string type 
      arguments are accepted but void is passed. 
      (state=42000,code=40000)

此外，在处理值为 1NULL的列之间的聚合时，我们可能会遇到非常特殊的行为。 将忽略整行(如果有一列将NULL作为该行中的值)。 为了避免这种情况，我们可以在列值为NULL时使用coalesce(...)命令分配一个默认值。请参见以下示例：

      -- Create a table t for testing
 > CREATE TABLE t (val1 int, val2 int);
 > INSERT INTO TABLE t VALUES (1, 2),(null,2),(2,3);
 No rows affected (0.138 seconds) 

      -- Check the rows in the table created
 > SELECT * FROM t;
 +---------+---------+
 | t.val1  | t.val2  |
 +---------+---------+
 | 1       | 2       |
 | NULL    | 2       |
 | 2       | 3       |
 +---------+---------+
 3 rows selected (0.069 seconds)

      -- The 2nd row (NULL, 2) is ignored when doing sum(val1 + val2)
 > SELECT sum(val1), sum(val1 + val2) FROM t; 
 +------+------+
 | _c0  | _c1  |
 +------+------+
 | 3    | 8    |
 +------+------+
 1 row selected (57.775 seconds)

 > SELECT 
 > sum(coalesce(val1,0)),
 > sum(coalesce(val1,0) + val2) 
 > FROM t;
 +------+------+
 | _c0  | _c1  |
 +------+------+
 | 3    | 10   |
 +------+------+
 1 row selected (69.967 seconds)

6. 聚合函数还可以与DISTINCT关键字配合使用，以聚合唯一值：

 > SELECT 
 > count(DISTINCT gender_age.gender) as gender_uni_cnt,
 > count(DISTINCT name) as name_uni_cnt
 > FROM employee; 
 +-----------------+---------------+
 | gender_uni_cnt  | name_uni_cnt  |
 +-----------------+---------------+
 | 2               | 5             |
 +-----------------+---------------+
 1 row selected (35.935 seconds)

当我们同时使用COUNT和DISTINCT时，它总是忽略所使用的减速器数量的设置(例如mapred.reduce.tasks = 20)，并且可能只使用一个减速器。 在这种情况下，单个减速器成为处理大量数据的瓶颈。 解决方法是使用子查询，如下所示：

-- May trigger single reducer during the whole processing
> SELECT count(distinct gender_age.gender) as gender_uni_cnt FROM employee;

-- Use subquery to select unique value before aggregations
> SELECT 
> count(*) as gender_uni_cnt 
> FROM (
> SELECT DISTINCT gender_age.gender FROM employee
) a;

在这种情况下，实现DISTINCT的查询的第一阶段可以使用多个减法器。 在第二个阶段中，映射器仅用于COUNT目的的输出将较少，因为在实现DISTINCT之后数据已经是唯一的。 因此，减速器不会超载。

有时，我们可能需要找到最大值。 或最小。 特定列以及其他列的值，例如，要回答以下问题：员工表中年龄最大的男性和女性是谁？和要实现这一点，我们还可以对结构使用 max/min，如下所示，而不是使用所有子查询/窗口函数：

> SELECT gender_age.gender, 
> max(struct(gender_age.age, name)).col1 as age,
> max(struct(gender_age.age, name)).col2 as name
> FROM employee
> GROUP BY gender_age.gender;
+-------------------+-----+------+
| gender_age.gender | age | name |
+-------------------+-----+------+
| Female            | 57  | Lucy |
| Male              | 35  | Will |
+-------------------+-----+------+
2 rows selected (26.896 seconds)

虽然它仍然需要使用GROUP BY子句，但该作业比常规的GROUP BY查询或子查询效率更高，因为它只触发一个作业。

The hive.map.aggr property controls aggregations in the map task. The default value for this setting is true, so Hive will do the first-level aggregation directly in the map task for better performance, but consume more memory. Turn it off if you run out of memory in the map phase.

增强型聚合

配置单元通过使用**GROUPING SETS、CUBE和、ROLLUP**关键字提供增强的聚合。

对集合进行分组

GROUPING SETS针对同一组数据实现多个高级GROUP BY操作。 实际上，GROUPING SETS是将多个GROUP BY结果集与UNION ALL连接起来的一种快捷方式。 关键字GROUPING SETS在作业的单个阶段完成所有流程，效率更高。 GROUPING SETS子句中的空白集()计算总体聚合。 下面是几个例子来说明GROUPING SETS的等价性。 为了更好地理解，我们可以说GROUPING SETS的外层(大括号)定义了要实现的数据UNION ALL。 内部级别(大括号)定义要在每个UNION ALL中实施哪些GROUP BY数据。

1. 具有一个列对元素的分组集：

 SELECT 
 name, start_date, count(sin_number) as sin_cnt 
 FROM employee_hr
 GROUP BY name, start_date 
 GROUPING SETS((name, start_date));
      --||-- equals to
 SELECT
 name, start_date, count(sin_number) as sin_cnt 
 FROM employee_hr
 GROUP BY name, start_date;
 +---------+------------+---------+
 | name    | start_date | sin_cnt |
 +---------+------------+---------+
 | Lucy    | 2010-01-03 | 1       |
 | Michael | 2014-01-29 | 1       |
 | Steven  | 2012-11-03 | 1       |
 | Will    | 2013-10-02 | 1       |
 +---------+------------+---------+
 4 rows selected (26.3 seconds)

2. 包含两个元素的分组集：

 SELECT 
 name, start_date, count(sin_number) as sin_cnt 
 FROM employee_hr
 GROUP BY name, start_date 
 GROUPING SETS(name, start_date);
      --||-- equals to SELECT 
 name, null as start_date, count(sin_number) as sin_cnt 
 FROM employee_hr
 GROUP BY name
 UNION ALL
 SELECT 
 null as name, start_date, count(sin_number) as sin_cnt 
 FROM employee_hr
 GROUP BY start_date;
 ----------+------------+---------+
 | name    | start_date | sin_cnt |
 +---------+------------+---------+
 | NULL    | 2010-01-03 | 1       |
 | NULL    | 2012-11-03 | 1       |
 | NULL    | 2013-10-02 | 1       |
 | NULL    | 2014-01-29 | 1       |
 | Lucy    | NULL       | 1       |
 | Michael | NULL       | 1       |
 | Steven  | NULL       | 1       |
 | Will    | NULL       | 1       |
 +---------+------------+---------+
 8 rows selected (22.658 seconds)

3. 包含两个元素、列对和列的分组集：

 SELECT 
 name, start_date, count(sin_number) as sin_cnt 
 FROM employee_hr
 GROUP BY name, start_date 
 GROUPING SETS((name, start_date), name);
      --||-- equals to SELECT 
 name, start_date, count(sin_number) as sin_cnt 
 FROM employee_hr
 GROUP BY name, start_date
 UNION ALL
 SELECT 
 name, null as start_date, count(sin_number) as sin_cnt 
 FROM employee_hr
 GROUP BY name;
 +---------+------------+---------+
 | name    | start_date | sin_cnt |
 +---------+------------+---------+
 | Lucy    | NULL       | 1       |
 | Lucy    | 2010-01-03 | 1       |
 | Michael | NULL       | 1       |
 | Michael | 2014-01-29 | 1       |
 | Steven  | NULL       | 1       |
 | Steven  | 2012-11-03 | 1       |
 | Will    | NULL       | 1       |
 | Will    | 2013-10-02 | 1       |
 +---------+------------+---------+
 8 rows selected (22.503 seconds)

4. 包含四个元素的分组集，包括所有列的组合：

 SELECT 
 name, start_date, count(sin_number) as sin_cnt 
 FROM employee_hr
 GROUP BY name, start_date 
 GROUPING SETS((name, start_date), name, start_date, ());
      --||-- equals to SELECT 
 name, start_date, count(sin_number) as sin_cnt 
 FROM employee_hr
 GROUP BY name, start_date
 UNION ALL
 SELECT 
 name, null as start_date, count(sin_number) as sin_cnt 
 FROM employee_hr
 GROUP BY name
 UNION ALL
 SELECT 
 null as name, start_date, count(sin_number) as sin_cnt 
 FROM employee_hr
 GROUP BY start_date
 UNION ALL
 SELECT 
 null as name, null as start_date, count(sin_number) as sin_cnt 
 FROM employee_hr
 +---------+------------+---------+
 | name    | start_date | sin_cnt |
 +---------+------------+---------+
 | NULL    | NULL       | 4       |
 | NULL    | 2010-01-03 | 1       |
 | NULL    | 2012-11-03 | 1       |
 | NULL    | 2013-10-02 | 1       |
 | NULL    | 2014-01-29 | 1       |
 | Lucy    | NULL       | 1       |
 | Lucy    | 2010-01-03 | 1       |
 | Michael | NULL       | 1       |
 | Michael | 2014-01-29 | 1       |
 | Steven  | NULL       | 1       |
 | Steven  | 2012-11-03 | 1       |
 | Will    | NULL       | 1       |
 | Will    | 2013-10-02 | 1       |
 +---------+------------+---------+
 13 rows selected (24.916 seconds)

汇总和多维数据集

ROLLUP语句使SELECT语句能够计算指定维度组中的多个级别的聚合。 ROLLUP语句是GROUP BY子句的简单扩展，具有很高的效率和最小的查询开销。 与创建指定级别的聚合的GROUPING SETS相比，ROLLUP创建了n+1级别，其中n是分组列数。 首先，它计算在GROUP BY子句中指定的标准聚合值。 然后，它创建更高级别的小计，在分组列的组合列表中从右向左移动。 例如，GROUP BY a,b,c WITH ROLLUP和等同于GROUP BY a,b,c GROUPING SETS ((a,b,c),(a,b),(a),())。

CUBE语句接受一组指定的分组列，并为它们的所有可能组合创建聚合。 如果为多维数据集指定了n列，则将返回 2 个ⁿ聚合组合。 例如，GROUP BY a,b,c WITH CUBE和等同于GROUP BY a,b,c GROUPING SETS ((a,b,c),(a,b),(b,c),(a,c),(a),(b),(c),())。

函数GROUPING__ID和可以作为一个扩展来区分整行。 它返回在GROUP BY之后指定的每一列的位向量的十进制等效值。 返回的十进制数是从 1 和 0 的二进制转换而来的，它表示列是否在行中聚合(0)。另一方面，函数还通过直接返回二进制 1 或 0 来指示是否聚合GROUP BY子句中的列。 在下面的示例中，列的顺序从从GROUP BY开始计算最近的列(如name)开始。 结果集中的第一行表示GROUP BY中没有使用任何列。

将以下示例与GROUPING SETS部分中的最后一个示例进行比较，以更好地理解GROUPING_ID和grouping(...)：

SELECT 
name, start_date, count(employee_id) as emp_id_cnt,
GROUPING__ID,
grouping(name) as gp_name, 
grouping(start_date) as gp_sd
FROM employee_hr 
GROUP BY name, start_date 
WITH CUBE ORDER BY name, start_date;
+---------+------------+------------+-----+---------+-------+
| name    | start_date | emp_id_cnt | gid | gp_name | gp_sd |
+---------+------------+------------+-----+---------+-------+
| NULL    | NULL       | 4          | 3   | 1       | 1     |
| NULL    | 2010-01-03 | 1          | 2   | 1       | 0     |
| NULL    | 2012-11-03 | 1          | 2   | 1       | 0     |
| NULL    | 2013-10-02 | 1          | 2   | 1       | 0     |
| NULL    | 2014-01-29 | 1          | 2   | 1       | 0     |
| Lucy    | NULL       | 1          | 1   | 0       | 1     |
| Lucy    | 2010-01-03 | 1          | 0   | 0       | 0     |
| Michael | NULL       | 1          | 1   | 0       | 1     |
| Michael | 2014-01-29 | 1          | 0   | 0       | 0     |
| Steven  | NULL       | 1          | 1   | 0       | 1     |
| Steven  | 2012-11-03 | 1          | 0   | 0       | 0     |
| Will    | NULL       | 1          | 1   | 0       | 1     |
| Will    | 2013-10-02 | 1          | 0   | 0       | 0     |
+---------+------------+------------+-----+---------+-------+
13 rows selected (55.507 seconds)

聚集条件

从 v0.7.0 开始，增加了HAVING，直接支持聚合结果的条件过滤。 通过使用HAVING，我们可以避免在GROUP BY语句之后使用子查询。 请参见以下示例：

> SELECT 
> gender_age.age 
> FROM employee
> GROUP BY gender_age.age 
> HAVING count(*)=1;
+----------------+
| gender_age.age |
+----------------+
| 27             |
| 30             |
| 35             |
| 57             |
+----------------+
4 rows selected (25.829 seconds)

> SELECT 
> gender_age.age, 
> count(*) as cnt -- Support use column alias in HAVING, like ORDER BY
> FROM employee 
> GROUP BY gender_age.age HAVING cnt=1; 
+----------------+-----+
| gender_age.age | cnt |
+----------------+-----+
| 27             | 1   |
| 30             | 1   |
| 35             | 1   |
| 57             | 1   |
+----------------+-----+ 
4 rows selected (25.804 seconds)

HAVING supports filtering on regular columns too. However, it is recommended to use such a filter type after a WHERE clause rather than HAVING for better performance.

如果不使用HAVING，则可以改用子查询，如下所示：

> SELECT 
> a.age
> FROM (
> SELECT count(*) as cnt, gender_age.age 
> FROM employee GROUP BY gender_age.age
> ) a WHERE a.cnt <= 1;
+--------+
| a.age  |
+--------+
| 57     |
| 27     |
| 35     |
+--------+
3 rows selected (87.298 seconds)

窗口函数

窗口函数从配置单元 v0.11.0 开始提供，是扫描多个输入行以计算每个输出值的一组特殊函数。 窗口函数通常与OVER、PARTITION BY、ORDER BY和窗口规范一起使用。 与GROUP BY子句使用的常规聚合函数不同，并且限制为每组一个结果值，窗口函数在窗口上操作，其中输入行使用通过OVER和PARTITION子句表示的灵活条件进行排序和分组。 窗口函数提供聚合结果，但不对结果集进行分组。 它们为每条记录多次返回组值。 与常规的GROUP BY子句相比，窗口函数提供了极大的灵活性和功能，并使 HQL 进行的特殊聚合更容易、更强大。 窗口函数的语法如下：

Function (arg1,..., argn) OVER ([PARTITION BY <...>] [ORDER BY <....>] [<window_expression>])

Function (arg1,..., argn)可以是以下四个类别中的任何函数：

• 聚合函数：常规聚合函数，如sum(...)和max(...)
• 排序函数：用于对数据进行排序的函数，如rank(...)和row_number(...)
• 分析函数：用于统计和比较的函数，如lead(...)、lag(...)和first_value(...)

OVER ``[PARTITION BY <...>]和子句类似于GROUP BY子句。 它按列将行划分为在一个或多个分区中包含相同值的组。 这些逻辑组称为分区，这与用于分区表的术语不同。 省略PARTITION BY语句会将操作应用于表中的所有行。

[ORDER BY <....>]子句与常规ORDER BY子句相同。 它确保由PARTITION BY子句生成的行按规范排序，例如升序或降序。

接下来，我们将通过示例了解每类窗口函数的更多细节。

窗口聚合函数

在窗口函数中使用常规聚合函数比GROUP BY更灵活，后者需要选择列表中的所有分组列。 从配置单元 v2.2.0 开始，支持将DISTINCT与窗口函数中的聚合函数一起使用：

1. 准备用于演示的表格和数据：

 > CREATE TABLE IF NOT EXISTS employee_contract (
 > name string,
 > dept_num int,
 > employee_id int,
 > salary int,
 > type string,
 > start_date date
 > )
 > ROW FORMAT DELIMITED
 > FIELDS TERMINATED BY '|'
 > STORED as TEXTFILE;
 No rows affected (0.282 seconds)

 > LOAD DATD INPATH '/tmp/hivedemo/data/employee_contract.txt'
 > OVERWRITE INTO TABLE employee_contract;
 No rows affected (0.48 seconds)

2. 常规聚合用作窗口函数：

 > SELECT 
 > name, 
 > dept_num as deptno, 
 > salary,
 > count(*) OVER (PARTITION BY dept_num) as cnt,
 > count(distinct dept_num) OVER (PARTITION BY dept_num) as dcnt,
 > sum(salary) OVER(PARTITION BY dept_num ORDER BY dept_num) as 
      sum1,
 > sum(salary) OVER(ORDER BY dept_num) as sum2,
 > sum(salary) OVER(ORDER BY dept_num, name) as sum3
 > FROM employee_contract
 > ORDER BY deptno, name;
 +---------+--------+--------+-----+-----+-------+-------+-------+
 | name    | deptno | salary | cnt | dcnt| sum1  | sum2  | sum3  |
 +---------+--------+--------+-----+-----+-------+-------+-------+
 | Lucy    | 1000   | 5500   | 5   | 1   | 24900 | 24900 | 5500  |
 | Michael | 1000   | 5000   | 5   | 1   | 24900 | 24900 | 10500 |
 | Steven  | 1000   | 6400   | 5   | 1   | 24900 | 24900 | 16900 |
 | Wendy   | 1000   | 4000   | 5   | 1   | 24900 | 24900 | 20900 |
 | Will    | 1000   | 4000   | 5   | 1   | 24900 | 24900 | 24900 |
 | Jess    | 1001   | 6000   | 3   | 1   | 17400 | 42300 | 30900 |
 | Lily    | 1001   | 5000   | 3   | 1   | 17400 | 42300 | 35900 |
 | Mike    | 1001   | 6400   | 3   | 1   | 17400 | 42300 | 42300 |
 | Richard | 1002   | 8000   | 3   | 1   | 20500 | 62800 | 50300 |
 | Wei     | 1002   | 7000   | 3   | 1   | 20500 | 62800 | 57300 |
 | Yun     | 1002   | 5500   | 3   | 1   | 20500 | 62800 | 62800 |
 +---------+--------+--------+-----+-----+-------+-------+-------+
 11 rows selected (111.856 seconds)

窗口排序函数

窗口排序函数将特定组内的排序数据信息(如行号和排名)作为返回数据的一部分提供。 最常用的排序函数如下：

• row_number：根据分区和顺序规范，为每行分配一个从 1 开始的唯一序列号。
• rank：对组中的项目进行排名，例如查找满足特定条件的前三行N。
• dense_rank：类似于rank，但在有平局时不会在排名顺序中留下空白。 例如，如果我们使用dense_rank来对一场比赛进行排名，并且有两名球员并列第二名，我们会看到这两名球员都排在第二位，下一个人排在第三位。 然而，第二个rank函数将把两个人排在第二位，但下一个人将排在第四位。
• percent_rank：使用排名值而不是其分子中的行数作为(当前排名-1)/(总行数-1)。 因此，它返回值相对于一组值的百分比排名。
• ntile：将有序数据集数据集划分为多个存储桶，并为每行分配适当的存储桶编号。 它可用于将行划分为相等的集合，并为每行分配一个数字。

以下是在 HQL 中使用窗口排序函数的一些示例：

> SELECT 
> name, 
> dept_num as deptno, 
> salary,
> row_number() OVER () as rnum, *-- sequence in orginal table*
> rank() OVER (PARTITION BY dept_num ORDER BY salary) as rk, 
> dense_rank() OVER (PARTITION BY dept_num ORDER BY salary) as drk,
> percent_rank() OVER(PARTITION BY dept_num ORDER BY salary) as prk,
> ntile(4) OVER(PARTITION BY dept_num ORDER BY salary) as ntile
> FROM employee_contract
> ORDER BY deptno, name;
+---------+--------+--------+------+----+-----+------+-------+
| name    | deptno | salary | rnum | rk | drk | prk  | ntile |
+---------+--------+--------+------+----+-----+------+-------+
| Lucy    | 1000   | 5500   | 7    | 4  | 3   | 0.75 | 3     |
| Michael | 1000   | 5000   | 11   | 3  | 2   | 0.5  | 2     |
| Steven  | 1000   | 6400   | 8    | 5  | 4   | 1.0  | 4     |
| Wendy   | 1000   | 4000   | 9    | 1  | 1   | 0.0  | 1     |
| Will    | 1000   | 4000   | 10   | 1  | 1   | 0.0  | 1     |
| Jess    | 1001   | 6000   | 5    | 2  | 2   | 0.5  | 2     |
| Lily    | 1001   | 5000   | 6    | 1  | 1   | 0.0  | 1     |
| Mike    | 1001   | 6400   | 4    | 3  | 3   | 1.0  | 3     |
| Richard | 1002   | 8000   | 1    | 3  | 3   | 1.0  | 3     |
| Wei     | 1002   | 7000   | 3    | 2  | 2   | 0.5  | 2     |
| Yun     | 1002   | 5500   | 2    | 1  | 1   | 0.0  | 1     |
+---------+--------+--------+------+----+-----+------+-------+
11 rows selected (80.052 seconds)

从配置单元 v2.1.0 开始，我们可以在OVER语句中使用聚合函数，如下所示：

> SELECT
> dept_num,
> rank() OVER (PARTITION BY dept_num ORDER BY sum(salary)) as rk
> FROM employee_contract
> GROUP BY dept_num;
+----------+----+
| dept_num | rk |
+----------+----+
| 1000     | 1  |
| 1001     | 1  |
| 1002     | 1  | 
+----------+----+
3 rows selected (54.43 seconds)

窗口分析功能

窗口分析函数提供扩展的数据分析，例如获取有序集合中的滞后、领先、最后或第一行。 最常用的分析函数如下：

• cume_dist：计算其值小于或等于总行数除以当前行的值的行数，例如*(当前行的行数)/(总行数)*。
• lead：此函数lead(value_expr[,offset[,default]])用于返回下一行的数据。 可以选择指定行数(偏移量)，默认情况下为 1 行。 当未指定默认值时，该函数返回[,default]或NULL。 此外，当前行的引线延伸到窗口末尾之外。
• lag：此函数lag(value_expr[,offset[,default]])用于访问前一行中的数据。 可以选择指定要滞后的行数(偏移量)，默认情况下为 1。 当未指定默认值时，该函数返回[,default]或NULL。 此外，当前行的滞后时间会延伸到窗口末尾之外。
• first_value：它返回有序集的第一个结果。
• last_value：它返回有序集的最后一个结果。

以下是在 HQL 中使用窗口分析函数的一些示例：

> SELECT 
> name,
> dept_num as deptno,
> salary,
> cume_dist() OVER (PARTITION BY dept_num ORDER BY salary) as cume,
> lead(salary, 2) OVER (PARTITION BY dept_num ORDER BY salary) as lead,
> lag(salary, 2, 0) OVER (PARTITION BY dept_num ORDER BY salary) as lag,
> first_value(salary) OVER (PARTITION BY dept_num ORDER BY salary) as fval,
> last_value(salary) OVER (PARTITION BY dept_num ORDER BY salary) as lval,
> last_value(salary) OVER (PARTITION BY dept_num ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as lval2
> FROM employee_contract 
> ORDER BY deptno, salary;
+--------+------+--------+------+------+-----+------+------+-------+
| name   |deptno| salary | cume | lead | lag | fval |lvalue|lvalue2|
+--------+------+--------+------+------+-----+------+------+-------+
| Will   | 1000 | 4000   | 0.4  | 5500 | 0   | 4000 | 4000 | 6400  |
| Wendy  | 1000 | 4000   | 0.4  | 5000 | 0   | 4000 | 4000 | 6400  |
| Michael| 1000 | 5000   | 0.6  | 6400 | 4000| 4000 | 5000 | 6400  |
| Lucy   | 1000 | 5500   | 0.8  | NULL | 4000| 4000 | 5500 | 6400  |
| Steven | 1000 | 6400   | 1.0  | NULL | 5000| 4000 | 6400 | 6400  |
| Lily   | 1001 | 5000   | 0.33 | 6400 | 0   | 5000 | 5000 | 6400  |
| Jess   | 1001 | 6000   | 0.67 | NULL | 0   | 5000 | 6000 | 6400  |
| Mike   | 1001 | 6400   | 1.0  | NULL | 5000| 5000 | 6400 | 6400  |
| Yun    | 1002 | 5500   | 0.33 | 8000 | 0   | 5500 | 5500 | 8000  |
| Wei    | 1002 | 7000   | 0.67 | NULL | 0   | 5500 | 7000 | 8000  |
| Richard| 1002 | 8000   | 1.0  | NULL | 5500| 5500 | 8000 | 8000  |
+--------+------+--------+------+------+-----+------+------+-------+
11 rows selected (55.203 seconds)

对于last_value，结果(lval列)有点出乎意料。 这是因为使用的默认 WINDOW 子句(将在下一节中介绍)是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，在本例中，这意味着当前行将始终是最后一个值。 将窗口子句更改为RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING会给出预期的结果(请参阅lval2列)。

窗口表达式

[<window_expression>]用于进一步细分结果并应用窗函数。 有两种类型的窗口：行类型窗口和范围类型窗口。

根据issues.apache.org/jira/browse…处的 JIRA，rank(...)、ntile(...)、dense_rank(...)、cume_dist(...)、percent_rank(...)、lead(...)、lag(...)和row_number(...)函数还不支持与窗口表达式一起使用。

对于行类型窗口，定义是根据当前行之前或之后的行号。 ROW WINDOW 子句的常规语法如下：

ROWS BETWEEN <start_expr> AND <end_expr>

<start_expr>可以是以下任一项：

• UNBOUNDED PRECEDING
• CURRENT ROW
• N PRECEDING or FOLLOWING

<end_expr>可以是以下任一项：

• UNBOUNDED FOLLOWING
• CURRENT ROW
• N PRECEDING or FOLLOWING

下面介绍有关使用窗口表达式及其组合的更多详细信息：

• BETWEEN...AND：使用它指定窗的起点和终点。 第一个表达式(在AND之前)定义起点，第二个表达式(在AND之后)定义终点。 如果我们省略了BETWEEN...AND(例如ROWS N PRECEDING或ROWS UNBOUNDED PRECEDING)，则配置单元会将其视为起点，而终结点则默认为当前行(请参阅以下示例中的win6和win7列)。
• N PRECEDING or FOLLOWING：这表示当前行之前或之后的N行。
• UNBOUNDED PRECEDING：这表示窗口从分区的第一行开始。 这是起点规范，不能用作终点规范。
• UNBOUNDED FOLLOWING：这表示窗口在分区的最后一行结束。 这是终结点规范，不能用作起点规范。
• UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING：这表示每行的第一行和最后一行，表示表中的所有行(请参见下面示例中的win14列)。
• CURRENT ROW：作为起点，CURRENT ROW指定窗口从当前行或值开始，具体取决于我们指定的是ROW还是RANGE(范围将在本章后面介绍)。 在这种情况下，端点不能为M PRECEDING。 作为端点，CURRENT ROW指定窗口在当前行或当前值结束，具体取决于我们指定的是ROW还是RANGE。 在这种情况下，起点不能是N FOLLOWING。

下面的图表可以帮助我们更清楚地理解前面的定义：

Window expression definitions

以下示例实现 ROW 类型的窗口表达式：

-- Preceding and Following
> SELECT 
> name, dept_num as dno, salary as sal,
> max(salary) OVER (PARTITION BY dept_num ORDER BY name
> ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) win1,
> max(salary) OVER (PARTITION BY dept_num ORDER BY name 
> ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING) win2,
> max(salary) OVER (PARTITION BY dept_num ORDER BY name 
> ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) win3,
> max(salary) OVER (PARTITION BY dept_num ORDER BY name 
> ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) win4,
> max(salary) OVER (PARTITION BY dept_num ORDER BY name 
> ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING) win5,
> max(salary) OVER (PARTITION BY dept_num ORDER BY name 
> ROWS 2 PRECEDING) win6, -- FOLLOWING does not work in this way> max(salary) OVER (PARTITION BY dept_num ORDER BY name
> ROWS UNBOUNDED PRECEDING) win7
> FROM employee_contract
> ORDER BY dno, name;
+---------+------+------+------+------+------+------+------+------+------+
| name    | dno  | sal  | win1 | win2 | win3 | win4 | win5 | win6 | win7 |
+---------+------+------+------+------+------+------+------+------+------+
| Lucy    | 1000 | 5500 | 5500 | 6400 | 6400 | NULL | 6400 | 5500 | 5500 |
| Michael | 1000 | 5000 | 5500 | 6400 | 6400 | 5500 | 6400 | 5500 | 5500 |
| Steven  | 1000 | 6400 | 6400 | 6400 | 6400 | 5500 | 4000 | 6400 | 6400 |
| Wendy   | 1000 | 4000 | 6400 | 6400 | 6400 | 6400 | 4000 | 6400 | 6400 |
| Will    | 1000 | 4000 | 6400 | 6400 | 4000 | 6400 | NULL | 6400 | 6400 |
| Jess    | 1001 | 6000 | 6000 | 6400 | 6400 | NULL | 6400 | 6000 | 6000 |
| Lily    | 1001 | 5000 | 6000 | 6400 | 6400 | 6000 | 6400 | 6000 | 6000 |
| Mike    | 1001 | 6400 | 6400 | 6400 | 6400 | 6000 | NULL | 6400 | 6400 |
| Richard | 1002 | 8000 | 8000 | 8000 | 8000 | NULL | 7000 | 8000 | 8000 |
| Wei     | 1002 | 7000 | 8000 | 8000 | 8000 | 8000 | 5500 | 8000 | 8000 |
| Yun     | 1002 | 5500 | 8000 | 8000 | 7000 | 8000 | NULL | 8000 | 8000 |
+---------+------+------+------+------+------+------+------+------+------+
11 rows selected (55.885 seconds)

-- Current and Unbounded
> SELECT 
> name, dept_num as dno, salary as sal,
> max(salary) OVER (PARTITION BY dept_num ORDER BY name 
> ROWS BETWEEN CURRENT ROW AND CURRENT ROW) win8,
> max(salary) OVER (PARTITION BY dept_num ORDER BY name 
> ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) win9,
> max(salary) OVER (PARTITION BY dept_num ORDER BY name 
> ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) win10,
> max(salary) OVER (PARTITION BY dept_num ORDER BY name 
> ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) win11,
> max(salary) OVER (PARTITION BY dept_num ORDER BY name 
> ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) win12,
> max(salary) OVER (PARTITION BY dept_num ORDER BY name 
> ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) win13,
> max(salary) OVER (PARTITION BY dept_num ORDER BY name 
> ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) win14
> FROM employee_contract
> ORDER BY dno, name;
+-------+----+------+------+------+-------+-------+-------+-------+-------+
|name   |dno | sal  | win8 | win9 | win10 | win11 | win12 | win13 | win14 |
+-------+----+------+------+------+-------+-------+-------+-------+-------+
|Lucy   |1000| 5500 | 5500 | 5500 | 6400  | NULL  | 5500  | 5500  | 6400  |
|Michael|1000| 5000 | 5000 | 6400 | 6400  | 5500  | 5500  | 6400  | 6400  |
|Steven |1000| 6400 | 6400 | 6400 | 6400  | 5500  | 6400  | 6400  | 6400  |
|Wendy  |1000| 4000 | 4000 | 4000 | 4000  | 6400  | 6400  | 6400  | 6400  |
|Will   |1000| 4000 | 4000 | 4000 | 4000  | 6400  | 6400  | 6400  | 6400  |
|Jess   |1001| 6000 | 6000 | 6000 | 6400  | NULL  | 6000  | 6000  | 6400  |
|Lily   |1001| 5000 | 5000 | 6400 | 6400  | 6000  | 6000  | 6400  | 6400  |
|Mike   |1001| 6400 | 6400 | 6400 | 6400  | 6000  | 6400  | 6400  | 6400  |
|Richard|1002| 8000 | 8000 | 8000 | 8000  | NULL  | 8000  | 8000  | 8000  |
|Wei    |1002| 7000 | 7000 | 7000 | 7000  | 8000  | 8000  | 8000  | 8000  |
|Yun    |1002| 5500 | 5500 | 5500 | 5500  | 8000  | 8000  | 8000  | 8000  |
+-------+----+------+------+------+-------+-------+-------+-------+-------+
11 rows selected (53.754 seconds)

此外，窗口可以在单独的 WINDOW 子句中定义，也可以由其他窗口引用，如下所示：

> SELECT 
> name, dept_num, salary,
> max(salary) OVER w1 as win1,
> max(salary) OVER w2 as win2,
> max(salary) OVER w3 as win3
> FROM employee_contract
> WINDOW w1 as (
> PARTITION BY dept_num ORDER BY name
> ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
> ),
> w2 as w3,
> w3 as (
> PARTITION BY dept_num ORDER BY name
> ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING
> );
+---------+----------+--------+------+------+------+
| name    | dept_num | salary | win1 | win2 | win3 |
+---------+----------+--------+------+------+------+
| Lucy    | 1000     | 5500   | 5500 | 6400 | 6400 |
| Michael | 1000     | 5000   | 5500 | 6400 | 6400 |
| Steven  | 1000     | 6400   | 6400 | 6400 | 6400 |
| Wendy   | 1000     | 4000   | 6400 | 6400 | 6400 |
| Will    | 1000     | 4000   | 6400 | 4000 | 4000 |
| Jess    | 1001     | 6000   | 6000 | 6400 | 6400 |
| Lily    | 1001     | 5000   | 6000 | 6400 | 6400 |
| Mike    | 1001     | 6400   | 6400 | 6400 | 6400 |
| Richard | 1002     | 8000   | 8000 | 8000 | 8000 |
| Wei     | 1002     | 7000   | 8000 | 8000 | 8000 |
| Yun     | 1002     | 5500   | 8000 | 7000 | 7000 |
+---------+----------+--------+------+------+------+
11 rows selected (57.204 seconds)

与行类型窗口(以行为单位)相比，范围类型窗口以窗口表达式指定范围内的值为单位。 例如，max(salary) RANGE BETWEEN 500 PRECEDING AND 1000 FOLLOWING语句将不会根据当前行值-500 到+1000 之间的距离计算分区范围内的max(salary)。 如果当前行的工资为 4,000 美元，则此max(salary)将在每个dept_num指定的分区中包括其工资范围从 3,500 美元到 5,000 美元的行：

> SELECT
> dept_num, start_date, name, salary,
> max(salary) OVER (PARTITION BY dept_num ORDER BY salary
> RANGE BETWEEN 500 PRECEDING AND 1000 FOLLOWING) win1,
> max(salary) OVER (PARTITION BY dept_num ORDER BY salary
> RANGE BETWEEN 500 PRECEDING AND CURRENT ROW) win2
> FROM employee_contract
> order by dept_num, start_date;
+----------+------------+---------+--------+------+------+
| dept_num | start_date | name    | salary | win1 | win2 |
+----------+------------+---------+--------+------+------+
| 1000     | 2010-01-03 | Lucy    | 5500   | 6400 | 5500 |
| 1000     | 2012-11-03 | Steven  | 6400   | 6400 | 6400 |
| 1000     | 2013-10-02 | Will    | 4000   | 5000 | 4000 |
| 1000     | 2014-01-29 | Michael | 5000   | 5500 | 5000 |
| 1000     | 2014-10-02 | Wendy   | 4000   | 5000 | 4000 |
| 1001     | 2013-11-03 | Mike    | 6400   | 6400 | 6400 |
| 1001     | 2014-11-29 | Lily    | 5000   | 6000 | 5000 |
| 1001     | 2014-12-02 | Jess    | 6000   | 6400 | 6000 |
| 1002     | 2010-04-03 | Wei     | 7000   | 8000 | 7000 |
| 1002     | 2013-09-01 | Richard | 8000   | 8000 | 8000 |
| 1002     | 2014-01-29 | Yun     | 5500   | 5500 | 5500 |
+----------+------------+---------+--------+------+------+
11 rows selected (60.784 seconds)

如果完全省略Window表达式子句，则缺省窗口规范为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。如果同时缺少ORDER BY和WINDOW表达式子句，则窗口规范缺省为ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

采样 / 取样 / 抽样 / 样品

当数据量特别大时，我们可能需要找到数据子集来加快数据分析。 这就是抽样，这是一种用于识别和分析数据子集以发现整个数据集中的模式和趋势的技术。 在 HQL 中，数据抽样有三种方式：随机抽样、桶表抽样和分块抽样。

随机抽样

随机采样使用rand()函数和LIMIT关键字来获取数据采样，如下例所示。 这里使用DISTRIBUTE和SORT关键字，以确保数据也在映射器和减法器之间有效地随机分布。 ORDER BY rand()语句也可以达到同样的目的，但性能不好：

> SELECT name FROM employee_hr 
> DISTRIBUTE BY rand() SORT BY rand() LIMIT 2;
+--------+
| name   |
+--------+
| Will   |
| Steven |
+--------+
2 rows selected (52.399 seconds)

桶表抽样

这是一种特殊的采样方法，针对存储桶表进行了优化，如下例所示。 SELECT子句指定要从中采样数据的列。 在对整行进行采样时，也可以使用rand()函数。 如果样本柱也是CLUSTERED BY柱，则样本柱的效率会更高：

-- Sampling based on the whole row
> SELECT name FROM employee_trans
> TABLESAMPLE(BUCKET 1 OUT OF 2 ON rand()) a;
+--------+
| name   |
+--------+
| Steven |
+--------+
1 row selected (0.129 seconds)

-- Sampling based on the bucket column, which is efficient
> SELECT name FROM employee_trans 
> TABLESAMPLE(BUCKET 1 OUT OF 2 ON emp_id) a;
+---------+
| name    |
+---------+
| Lucy    |
| Steven  |
| Michael |
+---------+
3 rows selected (0.136 seconds)

整组抽样

这种类型的采样允许查询随机选取n行数据、n百分比的数据大小或n字节的数据。 采样粒度为 HDFS 块大小。 请参考以下示例：

-- Sample by number of rows
> SELECT name
> FROM employee TABLESAMPLE(1 ROWS) a;
+----------+
|   name   |
+----------+
| Michael  |
+----------+
1 rows selected (0.075 seconds)

-- Sample by percentage of data size
> SELECT name
> FROM employee TABLESAMPLE(50 PERCENT) a;
+----------+
|   name   |
+----------+
| Michael  |
| Will     |
+----------+
2 rows selected (0.041 seconds)

-- Sample by data size
-- Support b/B, k/K, m/M, g/G
> SELECT name FROM employee TABLESAMPLE(1B) a;
+----------+
|   name   |
+----------+
| Michael  |
+----------+
1 rows selected (0.075 seconds)

简略的 / 概括的 / 简易判罪的 / 简易的

在本章中，我们介绍了如何使用基本聚合函数聚合数据。 然后，我们介绍了使用GROUPING SETS、ROLLUP和CUBE的高级聚合，以及使用HAVING的聚合条件。 我们还介绍了各种窗口函数。 在本章的最后，我们介绍了数据采集的三种方式。 读完本章之后，您应该能够在 HQL 中进行基本的和高级的聚合和数据采样。 在下一章中，我们将讨论 Hive 中的性能考虑因素。

七、性能注意事项

虽然 Hive 是为处理大数据而建造的，但我们仍然不能忽视性能的重要性。 大多数情况下，更好的查询可以依靠智能查询优化器来查找最佳执行策略以及默认设置和最佳实践。 但是，有经验的用户应该更多地了解性能调优的理论和实践，特别是在处理对性能敏感的项目或环境时。

在本章中，我们将开始使用 HQL 中提供的实用程序来查找导致性能低下的潜在问题。 然后，我们介绍在设计、文件格式、压缩、存储、查询和作业方面的性能注意事项的最佳做法。 在本章中，我们将介绍以下主题：

• 性能实用程序
• 设计优化
• 数据优化
• 作业优化

性能实用程序

HQL 提供EXPLAIN和ANALYZE语句，可用作检查和识别查询性能的实用程序。 此外，Hive 日志应包含足够详细的信息，用于性能调查和故障排除。

解释语句

配置单元提供EXPLAIN语句以返回查询执行计划，而不运行查询。 如果我们担心查询的性能，我们可以使用它来分析查询。 EXPLAIN语句帮助我们查看两个或多个出于相同目的的查询之间的差异。 它的语法如下：

EXPLAIN [FORMATTED|EXTENDED|DEPENDENCY|AUTHORIZATION] hql_query

可以使用以下关键字：

• FORMATTED：这提供了查询计划的格式化 JSON 版本。
• EXTENDED：这为计划中的操作员提供了附加信息，如文件路径名。
• DEPENDENCY：它提供 JSON 格式的输出，其中包含查询所依赖的表和分区的列表。 从配置单元 v0.10.0 开始提供
• AUTHORIZATION：列出需要授权的所有实体，包括运行查询的输入和输出，以及授权失败(如果有)。 它从配置单元 v0.14.0 开始提供。

典型的查询计划包含以下三个部分。 稍后我们还将查看一个示例：

• 抽象语法树(AST)：HIVE 使用名为 ANTLR 的解析器生成器(参见HQL)自动生成 www.antlr.org/的树语法
• 阶段依赖项：列出用于运行查询的所有依赖项和阶段数
• 阶段计划：它包含用于运行作业的重要信息，如操作员和排序顺序

以下是典型查询计划的外观。 从下面的示例中，我们可以看到AST部分显示为 Map/Reduce 运算符树。 在STAGE DEPENDENCIES部分中，Stage-0和Stage-1都是独立的根阶段。 在STAGE PLANS部分中，Stage-1有一个 MAP 和 REDUE，它们分别是Map Operator Tree和Reduce Operator Tree所指的。 在每个Map/Reduce Operator Tree部分中，列出了与查询关键字相对应的所有运算符，以及表达式和聚合。 Stage-0阶段没有映射和还原。 它只是一个Fetch操作：

> EXPLAIN SELECT gender_age.gender, count(*) 
> FROM employee_partitioned WHERE year=2018 
> GROUP BY gender_age.gender LIMIT 2;

+----------------------------------------------------------------------+
| Explain                                                              |
+----------------------------------------------------------------------+
| STAGE DEPENDENCIES:                                                  |
| Stage-1 is a root stage                                              |
| Stage-0 depends on stages: Stage-1                                   |
|                                                                      |
| STAGE PLANS:                                                         |
| Stage: Stage-1                                                       |
| Map Reduce                                                           |
| Map Operator Tree:                                                   |
| TableScan                                                            |
| alias: employee_partitioned                                          |
| Pruned Column Paths: gender_age.gender                               |
| Statistics:                                                          |
| Num rows: 4 Data size: 223 Basic stats: COMPLETE Column stats: NONE  |
| Select Operator                                                      |
| expressions: gender_age.gender (type: string)                        |
| outputColumnNames: _col0                                             |
| Statistics:                                                          |
| Num rows: 4 Data size: 223 Basic stats: COMPLETE Column stats: NONE  |
| Group By Operator                                                    |
| aggregations: count()                                                |
| keys: _col0 (type: string)                                           |
| mode: hash                                                           |
| outputColumnNames: _col0, _col1                                      |
| Statistics:                                                          |
| Num rows: 4 Data size: 223 Basic stats: COMPLETE Column stats: NONE  |
| Reduce Output Operator                                               |
| key expressions: _col0 (type: string)                                |
| sort order: +                                                        |
| Map-reduce partition columns: _col0 (type: string)                   |
| Statistics:                                                          |
| Num rows: 4 Data size: 223 Basic stats: COMPLETE Column stats: NONE  |
| TopN Hash Memory Usage: 0.1                                          |
| value expressions: _col1 (type: bigint)                              |
| Reduce Operator Tree:                                                |
| Group By Operator                                                    |
| aggregations: count(VALUE._col0)                                     |
| keys: KEY._col0 (type: string)                                       |
| mode: mergepartial                                                   |
| outputColumnNames: _col0, _col1                                      |
| Statistics:                                                          |
| Num rows: 2 Data size: 111 Basic stats: COMPLETE Column stats: NONE  |
| Limit                                                                |
| Number of rows: 2                                                    |
| Statistics:                                                          |
| Num rows: 2 Data size: 110 Basic stats: COMPLETE Column stats: NONE  |
| File Output Operator                                                 |
| compressed: false                                                    |
| Statistics:                                                          |
| Num rows: 2 Data size: 110 Basic stats: COMPLETE Column stats: NONE  |
| table:                                                               |
| input format:                                                        |
| org.apache.hadoop.mapred.SequenceFileInputFormat                     |
| output format:                                                       |
| org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat            |
| serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe            |
|                                                                      |
| Stage: Stage-0                                                       |
| Fetch Operator                                                       |
| limit: 2                                                             |
| Processor Tree:                                                      |
| ListSink                                                             |
+----------------------------------------------------------------------+
53 rows selected (0.232 seconds)

Ambari 配置单元视图和色调配置单元编辑器在运行查询时都有内置的可视化查询解释。 Ambari 配置单元视图显示前面的查询，如下所示：

Ambari Hive view visual explaination

分析语句

配置单元统计信息是描述更多详细信息的数据集合，例如数据库中对象的行数、文件数和原始数据大小。 统计数据是数据的元数据，收集并存储在metastore数据库中。 HIVE 支持表、分区和列级别的统计信息。 这些统计数据用作基于配置单元成本优化器(CBO)的输入，该优化器用于根据完成查询所需的系统资源选择成本最低的查询计划。 统计信息在配置单元 v3.2.0 到 JIRA HIVE-11160(issues.apache.org/jira/browse…)中部分自动收集，或通过表、分区和列的ANALYZE语句手动收集，如下例所示：

1. 收集现有表的统计信息。如果指定了NOSCAN选项，该命令将忽略文件扫描而只收集文件数量及其大小，从而运行速度更快：

      > ANALYZE TABLE employee COMPUTE STATISTICS;
      No rows affected (27.979 seconds)

 > ANALYZE TABLE employee COMPUTE STATISTICS NOSCAN;
      No rows affected (25.979 seconds)

2. 收集特定或所有现有分区的统计信息：

      -- Applies for specific partition
      > ANALYZE TABLE employee_partitioned 
      > PARTITION(year=2018, month=12) COMPUTE STATISTICS;
      No rows affected (45.054 seconds)

-- Applies for all partitions
     > ANALYZE TABLE employee_partitioned 
 > PARTITION(year, month) COMPUTE STATISTICS;
      No rows affected (45.054 seconds)

3. 收集现有表的列的统计信息：

      > ANALYZE TABLE employee_id COMPUTE STATISTICS FOR COLUMNS 
 employee_id; 
 No rows affected (41.074 seconds)

We can enable automatic gathering of statistics by specifying SET hive.stats.autogather=true. For new tables or partitions that are populated through the INSERT OVERWRITE/INTO statement (rather than the LOAD statement), statistics are automatically collected in the metastore. 

一旦构建并收集了统计信息，我们就可以使用DESCRIBE EXTENDED/FORMATTED语句检查统计信息。 从表/分区输出中，我们可以找到参数内部的统计信息，例如parameters:{numFiles=1, COLUMN_STATS_ACCURATE=true, transient_lastDdlTime=1417726247, numRows=4, totalSize=227, rawDataSize=223})。 以下是检查表中的统计信息的示例：

-- Check statistics in a table
> DESCRIBE EXTENDED employee_partitioned PARTITION(year=2018, month=12);

-- Check statistics in a partition
> DESCRIBE EXTENDED employee;
...
parameters:{numFiles=1, COLUMN_STATS_ACCURATE=true, transient_lastDdlTime=1417726247, numRows=4, totalSize=227, rawDataSize=223}). 
-- Check statistics in a column
> DESCRIBE FORMATTED employee.name;
+--------+---------+---+---+---------+--------------+
|col_name|data_type|min|max|num_nulls|distinct_count| ...
+--------+---------+---+---+---------+--------------+
| name   | string  |   |   | 0       | 5            | ...
+--------+---------+---+---+---------+--------------+
+-----------+-----------+
|avg_col_len|max_col_len| ...
+-----------+-----------+
| 5.6       | 7         | ...
+-----------+-----------+
3 rows selected (0.116 seconds)

记录 / 砍伐 / 达到 / 伐木

日志提供了解查询/作业如何运行的详细信息。 通过检查日志详细信息，我们可以识别运行时问题和可能导致性能不佳的问题。 有两种类型的日志可用，系统日志和作业日志。

系统日志包含配置单元运行状态和问题。 它在{HIVE_HOME}/conf/hive-log4j.properties中配置。 在该文件中可以找到以下三行日志属性：

hive.root.logger=WARN,DRFA     *## set logger level*
hive.log.dir=/tmp/${user.name} *## set log file path*
hive.log.file=hive.log         *## set log file name*

要修改记录器级别，我们可以修改前面应用于所有用户的属性文件，也可以设置仅应用于当前用户会话的配置单元命令行配置，如$hive --hiveconf hive.root.logger=DEBUG,console。

作业日志包含作业信息，通常由 Yarn 管理。 要检查作业日志，请使用yarn logs -applicationId <application_id>。

设计优化

设计优化包括几种设计、数据格式和作业优化策略，以提高性能。 以下各节将详细介绍这一点。

分区表设计

配置单元分区是提高大型表查询性能的最有效方法之一。 带有分区筛选的查询将只从指定的分区(子目录)加载数据，因此它的执行速度比按非分区字段过滤的普通查询快得多。 分区键的选择始终是影响性能的重要因素。 它应该始终是一个低基数属性，以避免太多子目录开销。 以下是一些通常用作分区键的属性：

• 按日期和时间分区：当数据与日期/时间列(如load_date、business_date、run_date等)相关联时，使用日期和时间(如年、月和日(偶数小时))作为分区键
• 按位置分区：当数据与位置相关时，使用国家/地区、地区、州和城市作为分区键
• 按业务逻辑分区：当业务逻辑可以均匀地分隔数据时，使用部门、销售区域、应用、客户等作为分区键

斗台设计

与分区类似，存储桶表在 HDFS 中将数据组织到单独的文件中。 分组可以加快存储桶上的数据采样速度。 如果联接键也是存储桶列，则存储块还可以提高连接性能，因为存储块可以确保密钥出现在某个存储桶中。 选择更好的存储桶列可以使存储桶表连接执行得更好。 选择存储桶列的最佳实践是根据数据集背后的业务逻辑标识最有可能在筛选器或联接条件中使用的列。 有关更多详细信息，请参阅本章后面的作业优化部分。

指标设计

使用索引是在关系数据库中进行性能调优的一种非常常见的最佳实践。 从 HIVE v0.7.0 开始，HIVE 支持在表/分区上创建索引。 配置单元中的索引为某些操作(如WHERE、GROUP BY和JOIN)提供了基于键的数据视图和更好的数据访问。 使用索引总是比全表扫描更便宜的替代方案。 在 HQL 中创建索引的命令非常简单，如下所示：

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

除了这个用于存储索引列值及其块 ID 对的COMPACT索引之外，从 v0.8.0 开始，HQL 还支持对方差较小的列值使用两个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...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(HDFS 上的典型文件 URI)和_offsets(每行的偏移量)。 然后，当我们查询索引表中的索引列时，可以引用该索引表，如下所示：

> 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语句。 我们不能使用DROP TABLE语句删除索引：

> DROP INDEX idx_gender_employee_id ON employee_id;
No rows affected (0.247 seconds)

使用倾斜/临时表

除了常规的内部/外部或分区表之外，我们还应该考虑使用倾斜或临时表，以获得更好的设计和性能。

从配置单元 v0.10.0 开始，HQL 支持创建一个特殊的表来组织偏斜数据。-偏斜表可以通过自动将这些偏斜值拆分到单独的文件或目录中来提高性能。 因此，减少了文件或分区文件夹的总数。 此外，查询可以快速高效地包含或忽略此数据。 以下是用于创建倾斜表格的示例：

> 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)

另一方面，在数据递归处理期间使用 HQL 中的临时表保留中间数据将省去重新构建公共或共享结果集的工作。 此外，临时表还可以利用存储策略设置来使用 SSD 或内存进行数据存储，这也会带来更好的性能。

数据优化

数据文件优化涵盖了数据文件在文件格式、压缩和存储方面的性能改进。

档案格式

配置单元支持TEXTFILE、SEQUENCEFILE、AVRO、RCFILE、ORC和PARQUET文件格式。 有两个 HQL 语句用于指定文件格式，如下所示：

• CREATE TABLE ... STORE AS <file_format>：创建表时指定文件格式
• ALTER TABLE ... [PARTITION partition_spec] SET FILEFORMAT <file_format>：修改现有表格中的文件格式(仅限定义

一旦创建了以文本格式存储的表，我们就可以直接将文本数据加载到其中。 要将文本数据加载到具有其他文件格式的表格中，我们可以首先将数据加载到以文本形式存储的表格中，在该表格中，我们使用INSERT OVERWRITE/INTO TABLE ... SELECT按钮从表格中选择数据，然后将数据插入到具有其他文件格式的表格中。

To change the default file format for table creation, we can set the hive.default.fileformat = <file_format> property for all tables or hive.default.fileformat.managed = <file_format> only for internal/managed tables.

作为面向行的文件存储格式的TEXT、SEQUENCE和AVRO文件不是最佳解决方案，因为即使只请求一列，查询也必须读取整行。 另一方面，针对这一问题，采用行列混合存储文件格式，如RCFILE、ORC、PARQUET等，HQL 支持的文件格式详情如下：

• TEXTFILE：这是创建表的默认文件格式。 对于此格式，数据以明文形式存储。 文本文件自然是可拆分的，并且能够并行处理。 还可以使用 GZip、LZO 和 Snappy 等算法对其进行压缩。 但是，大多数压缩文件不能拆分以进行并行处理。 因此，他们只使用一个具有单个映射器的作业来缓慢地处理数据。 使用压缩文本文件的最佳实践是确保文件不太大，并且接近几个 HDFS 块大小。
• SEQUENCEFILE：这是键/值对的二进制存储格式。 序列文件的好处是它比文本文件更紧凑，并且非常适合 MapReduce 输出格式。 序列文件可以压缩到记录级或块级，其中块级具有较好的压缩比。 要启用块级压缩，我们需要使用以下设置：set hive.exec.compress.output=true;和set io.seqfile.compression.type=BLOCK;。
• AVRO：这也是二进制格式。 不仅如此，它还是一个序列化和反序列化框架。 Avro提供了一个数据模式，该模式描述数据结构并处理模式更改，如添加、重命名和删除列。 该模式与数据一起存储，以供进一步处理。 考虑到AVRO在处理模式演变方面的优势，建议在映射源数据时使用它，源数据可能会随时间发生模式更改。
• RCFILE：这是记录列文件的缩写。 它是一个平面文件，由二进制密钥/值对组成，与序列文件有许多相似之处。 RCFile将数据水平拆分成行组。 一个或多个组存储在 HDFS 文件中。 然后，RCFile以列格式保存行组数据，方法是先保存所有行的第一列，然后保存所有行的第二列，依此类推。 这种格式是可拆分的，允许配置单元跳过数据中不相关的部分，从而更快、更便宜地获得结果。
• ORC：这是优化行列的缩写。 它从配置单元 v0.11.0 开始提供。 可以将ORC格式视为RCFILE的改进版本。 默认情况下，它提供 256 MB 的较大块大小(RCFILE为 4 MB，SEQUENCEFILE为 1 MB)，针对 HDFS 上的大型顺序读取进行了优化，以获得更高的吞吐量和更少的文件，以减少 NameNode 中的过载。 与依赖metastore了解数据类型的RCFILE不同，ORC文件通过使用特定的编码器来了解数据类型，因此它可以根据不同的类型优化压缩。 它还存储有关列的基本统计信息，如MIN、MAX、SUM和COUNT，以及可用于跳过无关紧要的行块的轻量级索引。
• PARQUET：这是另一种设计与ORC类似的行列式文件格式。 更重要的是，与主要由 Hive、PIG 和星火支持的ORC相比，Parquet 对生态系统中的大多数项目都有更广泛的支持。 PARQUET利用 Google 的 DREMEL 设计中的最佳实践(参见research.google.com/pubs/pub366…)来支持数据的嵌套结构。 PARQUET从配置单元 v0.10.0 开始受插件支持，在 v0.13.0 之后获得原生支持。

根据使用的技术堆栈，如果配置单元是定义或处理数据的主要工具，则建议使用ORC格式。 如果您在生态系统中使用多种工具，PARQUET在适应性方面是更好的选择。

Hadoop Archive File (HAR) is another type of file format to pack HDFS files into archives. This is an option (not a good option) for storing a large number of small-sized files in HDFS, as storing a large number of small-sized files directly in HDFS is not very efficient. However, HAR has other limitations, such as an immutable archive process, not being splittable, and compatibility issues. For more information about HAR and archiving, please refer to the Hive Wiki at cwiki.apache.org/confluence/….

压缩 / 压紧 / 压挤 / 压榨，压迫

通过适当压缩中间和最终输出数据，配置单元中的压缩技术可以显著减少映射器和减少器之间传输的数据量。 因此，查询将具有更好的性能。 要压缩在多个 MapReduce 作业之间生成的中间文件，我们需要在命令行会话或hive-site.xml文件中设置以下属性(默认情况下为false)：

> SET hive.exec.compress.intermediate=true

然后，我们需要决定配置哪个压缩编解码器。 下表列出了通常支持的各种编解码器：

| 压缩 | 编解码器 | 扩展 | 可拆分 | | 显示 | org.apache.hadoop.io.compress.DefaultCodec | .deflate | （化学元素）氮 | | Gzip | org.apache.hadoop.io.compress.GzipCodec | .gz | （化学元素）氮 | | Bzip2 | org.apache.hadoop.io.compress.BZip2Codec | .gz | 英语字母表中第二十五个字母 / Y 字形 / Y 项 | | LZO | com.apache.compression.lzo.LzopCodec | .lzo | （化学元素）氮 | | LZ4 | org.apache.hadoop.io.compress.Lz4Codec | .lz4 | （化学元素）氮 | | 厉声说话的 / 恶声恶气的 / 短小精悍的 / 时髦的 | org.apache.hadoop.io.compress.SnappyCodec | .snappy | （化学元素）氮 |

DEVATE(.deflate)是一种默认编解码器，具有均衡的压缩比和 CPU 成本。 Gzip 的压缩比非常高，其 CPU 成本也很高。 Bzip2 是可拆分的，但考虑到其巨大的 CPU 成本，就像 Gzip 一样，它对于压缩来说太慢了。 LZO 文件本身不能拆分，但我们可以对其进行预处理(使用com.hadoop.compression.lzo.LzoIndexer)以创建确定文件拆分的索引。 当谈到 CPU 成本和压缩比的平衡时，LZ4 或 Snappy 比 Deflate 做得更好，但 Snappy 更受欢迎。 由于大多数压缩文件是不可拆分的，因此不建议压缩单个大文件。 最佳实践是生成几个 HDFS 块大小的压缩文件，以便每个文件花费更少的处理时间。 压缩编解码器可以在mapred-site.xml、hive-site.xml或命令行会话中指定，如下所示：

> SET hive.intermediate.compression.codec=
org.apache.hadoop.io.compress.SnappyCodec

中间压缩只会为需要多个 MapReduce 作业的特定作业节省磁盘空间。 为了进一步节省磁盘空间，可以压缩实际的配置单元输出文件。 当hive.exec.compress.output属性设置为true时，配置单元将使用由mapreduce.output.fileoutputformat.compress.codec属性配置的编解码器来压缩 HDFS 中的数据，如下所示。 这些属性可以在hive-site.xml或命令行会话中设置：

> SET hive.exec.compress.output=true
> SET mapreduce.output.fileoutputformat.compress.codec=
org.apache.hadoop.io.compress.SnappyCodec

存储优化

频繁使用或扫描的数据可以识别为热门数据。 通常，热数据上的查询性能对整体性能至关重要。 为热数据增加 HDFS 中的数据复制系数(请参见以下示例)可能会增加作业在本地命中数据的可能性，并提高整体性能。 但是，这是对存储的权衡：

$ hdfs dfs -setrep -R -w 4 /user/hive/warehouse/employee
Replication 4 set: /user/hive/warehouse/employee/000000_0

另一方面，过多的文件或冗余可能会使 NameNode 的内存耗尽，特别是大量小于 HDFS 块大小的小文件。 Hadoop 本身已经有一些解决方案，可以通过以下方式处理许多小文件问题：

• Hadoop Archive/HAR：这些是前面介绍的打包小文件的工具包。
• SEQUENCEFILE格式：这是一种可用于将小文件压缩成大文件的格式。
• CombineFileInputFormat：一种InputFormat类型，用于在映射和缩减处理之前合并小文件。 它是配置单元的默认InputFormat(参见issues.apache.org/jira/browse…)。
• HDFS 联合：支持多个名称节点管理更多文件。

如果我们安装了 Hadoop 生态系统中的其他工具，我们还可以利用它们，例如：

• HBase 具有更小的数据块大小和更好的文件格式，可处理较小的文件存储和访问问题
• Flume NG 可用作将小文件合并为大文件的管道
• 开发并计划了一个文件合并程序，用于在 HDFS 中或在将文件加载到 HDFS 之前合并小文件

对于配置单元，我们可以使用以下配置来合并查询结果文件，避免重新创建小文件：

• hive.merge.mapfiles：这会在仅地图作业结束时合并小文件。 默认情况下，它是true。
• hive.merge.mapredfiles：这会在 MapReduce 作业结束时合并小文件。 将其设置为TRUE，，因为默认值为false。
• hive.merge.size.per.task：此选项定义作业结束时合并文件的大小。 默认值为 256,000,000。
• hive.merge.smallfiles.avgsize：这是触发文件合并的阈值。 默认值为 16,000,000。

当作业的平均输出文件大小小于hive.merge.smallfiles.avgsize和属性指定的值，并且hive.merge.mapfiles(对于纯地图作业)和hive.merge.mapredfiles(对于 MapReduce 作业)都设置为TRUE时，配置单元将启动附加的 MapReduce 作业以将输出文件合并到大文件中。

作业优化

作业优化包括在作业运行模式、JVM 重用、作业并行运行和查询连接优化等方面提高性能的经验和技能。

本地模式

Hadoop 可以在独立、伪分布式和完全分布式模式下运行。 大多数情况下，我们需要将其配置为在完全分布式模式下运行。 当要处理的数据较小时，由于完全分布式模式的启动时间比作业处理时间更长，因此启动分布式数据处理是一种开销。 从 v0.7.0 开始，配置单元支持使用以下设置自动转换作业以在本地模式下运行：

> SET hive.exec.mode.local.auto=true; -- default false > SET hive.exec.mode.local.auto.inputbytes.max=50000000;
> SET hive.exec.mode.local.auto.input.files.max=5; -- default 4

作业必须满足以下条件才能在本地模式下运行：

• 作业的总输入大小小于hive.exec.mode.local.auto.inputbytes.max设置的值
• 地图任务总数小于设置的值hive.exec.mode.local.auto.input.files.max
• 所需的 Reduce 任务总数为 1 或 0

JVM 重用

默认情况下，Hadoop 为每个 map 或 Reduce 作业启动一个新的 JVM，并并行运行 map 或 Reduce 任务。 当 map 或 Reduce 作业是只运行几秒钟的轻量级作业时，JVM 启动过程可能会带来很大的开销。 Hadoop 可以通过共享 JVM 来选择重用 JVM，以串行而不是并行地运行映射器/减少器。 JVM 重用适用于映射或减少同一作业中的任务。 来自不同作业的任务将始终在单独的 JVM 中运行。 要启用重用，我们可以使用以下属性为 JVM 重用设置单个作业的最大任务数。 默认值为 1。如果设置为-1，则没有限制：

> SET mapreduce.job.jvm.numtasks=5;

并行执行

Hive 查询通常被转换成由默认序列执行的多个阶段。 这些阶段并不总是相互依赖的。 相反，它们可以并行运行，以减少总体作业运行时间。 我们可以使用以下设置启用此功能，并设置并行运行的预期作业数：

> SET hive.exec.parallel=true; -- default false > SET hive.exec.parallel.thread.number=16; -- default 8

并行执行将提高集群利用率。 如果集群的利用率已经很高，并行执行在总体性能方面不会有太大帮助。

连接优化

我们已经在章，的数据关联和范围中讨论了不同类型的配置单元联接的优化。 在这里，我们将简要回顾联接改进的关键设置。

公共连接

公共联接也称为 Reduce 侧联接。 它是 HQL 中的一个基本连接，并且在大多数情况下都有效。 对于普通连接，我们需要确保大表位于最右侧或由 HIT 指定，如下所示：

/*+ STREAMTABLE(stream_table_name) */

地图连接

当其中一个连接表足够小，可以放入内存时，就会使用映射连接，因此它速度很快，但受到表大小的限制。 从配置单元 v0.7.0 开始，它可以使用以下设置自动转换地图连接：

> SET hive.auto.convert.join=true; -- default true after v0.11.0 > SET hive.mapjoin.smalltable.filesize=600000000; -- default 25m
> SET hive.auto.convert.join.noconditionaltask=true; -- default value above is true so map join hint is not needed
> SET hive.auto.convert.join.noconditionaltask.size=10000000; -- default value above controls the size of table to fit in memory

一旦启用联接和自动转换，配置单元将自动检查较小的表文件大小是否大于hive.mapjoin.smalltable.filesize指定的值，然后将联接转换为普通联接。 如果文件大小小于此阈值，它将尝试将公共连接转换为映射连接。 一旦启用了自动转换联接，就不需要在查询中提供映射联接提示。

桶图连接

存储桶映射连接是应用于存储桶表的一种特殊类型的映射连接。 要启用存储桶图加入，我们需要启用以下设置：

> SET hive.auto.convert.join=true; 
> SET hive.optimize.bucketmapjoin=true; -- default false

在存储桶映射连接中，所有连接表必须是存储桶表并连接到存储桶列。 此外，较大表格中的存储桶编号必须是较小表格中的存储桶编号的倍数。

排序合并存储桶(SMB)联接

SMB 是对具有相同的排序、存储桶和联接条件列的存储桶表执行的联接。 它从两个桶表中读取数据，并在桶表上执行公共连接(映射和 Reduce 触发)。 我们需要启用以下属性才能使用 SMB：

> SET hive.input.format=
> org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;
> SET hive.auto.convert.sortmerge.join=true;
> SET hive.optimize.bucketmapjoin=true;
> SET hive.optimize.bucketmapjoin.sortedmerge=true;
> SET hive.auto.convert.sortmerge.join.noconditionaltask=true;

排序合并桶图(SMBM)联接

SMBM 联接是一种特殊的桶联接，但仅触发映射端联接。 它可以避免像映射连接那样缓存内存中的所有行。 要执行 SMBM 联接，联接表必须具有相同的存储桶列、排序列和联接条件列。 要启用此类联接，我们需要启用以下设置：

> SET hive.auto.convert.join=true;
> SET hive.auto.convert.sortmerge.join=true
> SET hive.optimize.bucketmapjoin=true;
> SET hive.optimize.bucketmapjoin.sortedmerge=true;
> SET hive.auto.convert.sortmerge.join.noconditionaltask=true;
> SET hive.auto.convert.sortmerge.join.bigtable.selection.policy=
org.apache.hadoop.hive.ql.optimizer.TableSizeBasedBigTableSelectorForAutoSMJ;

斜连接

当处理分布高度不均匀的数据时，可能会发生数据歪斜，导致少数计算节点必须处理大量计算。 如果发生数据倾斜，以下设置将通知配置单元正确优化：

> SET hive.optimize.skewjoin=true; --If there is data skew in join, set it to true. Default is false.

> SET hive.skewjoin.key=100000; 
 --This is the default value. If the number of key is bigger than 
 --this, the new keys will send to the other unused reducers.

Skewed data could occur with the GROUP BY data too. To optimize it, we need set hive.groupby.skewindata=true to use the preceding settings to enable skew data optimization in the GROUP BY result. Once configured, Hive will first trigger an additional MapReduce job whose map output will randomly distribute to the reducer to avoid data skew.

有关联接优化的更多信息，请参考位于cwiki.apache.org/confluence/…和cwiki.apache.org/confluence/…的配置单元维基。

作业引擎

配置单元支持在不同引擎上运行作业。 发动机的选择也会影响整体性能。 然而，与其他设置相比，这是一个更大的变化。 此外，此更改需要重新启动服务，而不是临时使其在命令行会话中生效。 以下是设置引擎的语法以及每个引擎的详细信息：

SET hive.execution.engine=<engine>; -- <engine> = mr|tez|spark 

• mr：这是默认引擎 MapReduce。 在配置单元 v2.0.0 之后，它已弃用。
• tez：TEZ(tez.apache.org/)是一个构建在 Yarn 上的应用框架，它可以为一般的数据处理任务执行复杂的有向无环图和(DAGs)。 TEZ 进一步将 MAP 和 Reduce 作业拆分成更小的任务，并以灵活高效的方式将它们组合在一起执行。 TEZ 被认为是 MapReduce 框架灵活而强大的继任者。 TEZ 已经为生产做好了准备，并且大部分时间都被用来取代 MR 引擎。
• spark：Spark 是另一个通用大数据框架。 它的组件 Spark SQL 支持 HQL 的一个子集，并提供类似于 HQL 的语法。 通过使用 Spark 上的配置单元，Spark 可以利用 Spark 的内存计算模型以及 Spark 成熟的基于成本的优化器。 然而，星火之上的 Hive 需要手动配置，在实际生产中仍然缺乏坚实的使用案例。 有关“星火上的 Hive”的更多细节，请参考(cwiki.apache.org/confluence/…)的维基页面。
• mr3：mr3 是另一个实验引擎(mr3.postech.ac.kr/)。 它类似于 TEZ，但增强了设计、更好的性能和更多的功能。 MR3 记录为可投入生产使用，并支持 TEZ 的所有主要功能，例如基于 Kerberos 的安全性、身份验证和授权、容错和恢复。 但是，它缺乏可靠的生产使用案例和生产部署的最佳实践，也缺乏对 CDH 或 HDP 分发的支持。

Live Long And Process (LLAP) functionality was added in Hive v2.0.0. It combines a live long running query service and intelligent in-memory caching to deliver fast queries. Together with a job engine, LLAP provides a hybrid execution model to improve overall Hive performance. LLAP needs to work through Apache Slider (slider.incubator.apache.org/) and only works with Tez for now. In the future, it will support other engines. The recent HDP has provided LLAP supported thought Tez.

优化器

与关系数据库类似，配置单元在提交最终执行之前生成并优化每个查询的逻辑和物理执行计划。 目前在配置单元中有两个主要的优化器来进一步优化总体上的查询性能，向量化和基于成本的优化(CBO)。

矢量化优化

矢量化优化可以同时处理更大批量的数据，而不是一次处理一行，从而显著降低计算开销。 每个批处理由一个列向量组成，该列向量通常是一个基元类型数组。 操作是在整个列向量上执行的，这改进了指令流水线和高速缓存的使用。 文件必须以ORC格式存储，才能使用矢量化。 有关矢量化的更多详细信息，请参考 Hive 维基(cwiki.apache.org/confluence/…)。 要启用矢量化，我们需要使用以下设置：

> SET hive.vectorized.execution.enabled=true; -- default false

基于成本的优化

配置单元中的 CBO 由 Apache 方解石(calcite.apache.org/)提供支持，这是一个开源的、基于成本的企业级逻辑优化器和查询执行框架。 HIVE CBO 通过检查由ANALYZE语句或metastore本身收集的查询成本来生成高效的执行计划，最终缩短查询执行时间并降低资源使用率。 要使用 CBO，请设置以下属性：

> SET hive.cbo.enable=true; -- default true after v0.14.0
> SET hive.compute.query.using.stats=true; -- default false
> SET hive.stats.fetch.column.stats=true; -- default false
> SET hive.stats.fetch.partition.stats=true; -- default true

简略的 / 概括的 / 简易判罪的 / 简易的

在本章中，我们首先介绍了如何使用EXPLAIN和ANALYZE语句识别性能瓶颈。 然后，我们谈到了使用表、分区和索引时的性能优化设计。 我们还介绍了数据文件优化，包括文件格式、压缩和存储。 在本章的最后，我们讨论了作业优化、作业引擎和优化器。 读完本章后，您应该能够在配置单元中进行性能故障排除和调优。 在下一章中，我们将讨论配置单元的功能扩展。

八、可扩展性注意事项

尽管 Hive 提供了许多内置功能，但在特殊情况下，用户可能需要超出所提供的功能。 在这种情况下，我们可以在三个主要方面扩展 Hive 的功能：

• 用户定义函数(UDF)：这提供了一种使用外部函数(主要是用 Java 编写的)扩展功能的方法，该函数可以在 HQL 中求值
• HPL/SQL：这为 HQL 提供过程语言编程支持
• 流：这将用户自己的定制程序插入到数据流中
• serDe：这代表序列化和反序列化，并提供了一种使用定制文件格式序列化或反序列化数据的方法

在本章中，我们将更详细地讨论它们中的每一个。

用户定义函数

用户定义函数提供了一种在 HQL 查询期间使用用户自己的应用/业务逻辑处理列值的方法。 例如，用户定义的函数可以使用外部机器学习库执行特征清理、验证来自其他服务的用户访问、将多个值合并为一个或多个、执行特殊的数据编码或加密以及常规 HQL 运算符和函数范围之外的其他操作。 配置单元定义了以下三种类型的用户定义函数，它们是可扩展的：

• UDF：代表用户自定义函数，按行操作，一行输出一个结果，比如大多数内置的数学函数和字符串函数。

• UDAF：代表用户定义的聚合函数，按行或按组操作，结果输出整个表一行或每组一行，如：max(...)和count(...)内置函数。

• UDTF：它代表用户定义的表格生成函数，该函数也是按行操作的，但是会产生多个行/表，比如explode(...)函数。 UDTF可以在SELECT或LATERAL VIEW语句之后使用。

Although all In functions in HQL are implemented in Java, UDF can also be implemented in any JVM-compatible language, such as Scala. In this book, we only focus on writing user-defined functions in Java.

在接下来的几节中，我们将开始更详细地查看每种用户定义函数的 Java 代码模板。

自定义项代码模板

规则UDF的代码模板如下：

package com.packtpub.hive.essentials.hiveudf;

import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.hive.ql.exec.Description;
import org.apache.hadoop.hive.ql.udf.UDFType; 
import org.apache.hadoop.io.Text;
*// Other libraries my needed*

*// These information is show by "desc function <function_name>"*
@Description(
 name = "udf_name",
 value = "_FUNC_(arg1, ... argN) - description for the function.",
 extended = "decription with more details, such as syntax, examples."
)
@UDFType(deterministic = true, stateful = false)

public class udf_name extends UDF { 
     // *evaluate() is the only necessary function to overwrite*
     public Text evaluate(){
         /*
          * *H****ere** to impelement core function logic*
          */
          return "return the udf result"; 
     } 
     // *override is supported*
     public String evaluate(<Type_arg1> arg1,..., <Type_argN> argN){
          /*
           * *Do something here*
           */
          return "return the udf result"; 
     } 
}

在前面的模板中，包定义和导入应该是不言而喻的。 除了前三个必需库之外，我们还可以导入任何需要的库。 @Description注释是特定于配置单元的有用注释，可提供函数和用法。 在value属性中定义的信息将显示在DESC FUNCTION语句中。 在extended属性中定义的信息将显示在DESCRIBE FUNCTION EXTENDED语句中。 @UDFType注释指定函数的预期行为。 确定性UDF(deterministic = true)是在传递相同参数时始终给出相同结果的函数，例如length(...)和max(...)。 另一方面，非确定性(deterministic = false)UDF可以为同一组参数返回不同的结果，例如，unix_timestamp()，它返回默认时区的当前时间戳。 Stateful(stateful = true)属性允许函数跨行保留一些静态变量，例如为表行分配序列号的row_number()。

所有UDF都应该从org.apache.hadoop.hive.ql.exec.UDF类扩展，因此UDF子类必须实现evaluate()方法，该方法也可以重写用于不同的目的。 在此方法中，我们可以使用 Java、Hadoop 和配置单元库和数据类型实现预期的功能逻辑和异常处理。

UDAF 代码模板

在本节中，我们将介绍UDAF代码模板，它是从org.apache.hadoop.hive.ql.exec.UDAF和类扩展而来的。 代码模板如下：

package com.packtpub.hive.essentials.hiveudaf;

import org.apache.hadoop.hive.ql.exec.UDAF;
import org.apache.hadoop.hive.ql.exec.UDAFEvaluator;
import org.apache.hadoop.hive.ql.exec.Description;
import org.apache.hadoop.hive.ql.udf.UDFType;

@Description(
 name = "udaf_name",
 value = "_FUNC_(arg1, arg2, ... argN) - description for the function",
 extended = "description with more details, such as syntax, examples."
)
@UDFType(deterministic = false, stateful = true)

public final class udaf_name extends UDAF {
  /**
   * *The internal state of an aggregation function.* *
   * *Note that this is only needed if the internal state* * *cannot be represented by a primitive type.* *
   * *The internal state can contain fields with types like* * *ArrayList<String> and HashMap<String,Double> if needed.*
   */
  public static class UDAFState {
    private <Type_state1> state1;
    private <Type_stateN> stateN;
  }

  /**
   * *The actual class for doing the aggregation. Hive will* * *automatically look for all internal classes of the UDAF* * *that implements UDAFEvaluator.*
   */
  public static class UDAFExampleAvgEvaluator implements UDAFEvaluator {

    UDAFState state;

    public UDAFExampleAvgEvaluator() {
      super();
      state = new UDAFState();
      init();
    }

    /**
     * *Reset the state of the aggregation.*
     */
    public void init() {
      /*
       * *Examples for initializing state.*
       */
      state.state1 = 0;
      state.stateN = 0;
    }

    /**
     * *Iterate through one row of original data.* *
     * *The number and type of arguments need to be the same as we* * *call this UDAF from the Hive command line.* ** *This function should always return true.*
     */
    public boolean iterate(<Type_arg1> arg1,..., <Type_argN> argN){
      /*
       * *Add logic here for how to do aggregation if there is* * *a new value to be aggregated.*
       */
      return true;
    }

    /**
     * *Called on the mapper side on different data nodes.* * *Terminate a partial aggregation and return the state.* * *If the state is a primitive, just return primitive Java* * *classes like Integer or String.*
     */
    public UDAFState terminatePartial() {
      /*
       * *Check and return a partial result in expectations.*
       */
      return state;
    }

    /**
     * *Merge with a partial aggregation.* *
     * *This function should always have a single argument,* * *which has the same type as the return value of* * *terminatePartial().*
     */
    public boolean merge(UDAFState o) {
      /*
       * *Define operations how to merge the result calculated* * *from all data nodes.*
       */
      return true;
    }

    /**
     * *Terminates the aggregation and returns the final result.*
     */
    public long terminate() {
      /*
       * *Check and return final result in expectations.*
       */
      return state.stateN;
    }
  }
}

UDAF 必须是包含一个或多个实现org.apache.hadoop.hive.ql.exec.UDAFEvaluator的嵌套静态类的org.apache.hadoop.hive.ql.exec.UDAF的子类。 确保实现UDAFEvaluator的内部类定义为 public。 否则，配置单元将无法使用反射并确定UDAFEvaluator实现。 我们还应该实现前面已经描述过的五个必需函数init()、iterate()、terminatePartial()、merge()和terminate()。

还可以通过扩展GenericUDF和GenericUDAFEvaluator类来实现UDF和UDAF，以避免使用 Java 反射以获得更好的性能。 此外，泛型函数支持复杂数据类型(如MAP、ARRAY和STRUCT)作为参数，而UDF和UDAF函数不支持。 有关GenericUDAF的更多信息，请参考cwiki.apache.org/confluence/…上的 Hive 维基。

UDTF 代码模板

要实现UDTF，只有一个方法从org.apache.hadoop.hive.ql.exec.GenericUDTF扩展而来。 没有普通的UDTF类。 我们需要实现三个方法：initialize()、process()和close()。 UDTF将调用initialize()方法，该方法返回函数输出的信息，如数据类型和输出数量。 然后，调用process()方法来执行带参数的核心函数逻辑并转发结果。 最后，如果需要，close()方法将进行适当的清理。 UDTF的代码模板如下：

package com.packtpub.hive.essentials.hiveudtf;

import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
import org.apache.hadoop.hive.ql.exec.Description;
import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.PrimitiveObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;

@Description(
 name = "udtf_name",
 value = "_FUNC_(arg1, arg2, ... argN) - description for the function",
 extended = "description with more detail, such as syntax, examples."
)
public class udtf_name extends GenericUDTF {
  private PrimitiveObjectInspector stringOI = null;
  /**
   * *This method will be called exactly once per instance.* * *It performs any custom initialization logic we need.* * *It is also responsible for verifying the input types and* * *specifying the output types.*
   */
  @Override
  public StructObjectInspector initialize(ObjectInspector[] args) 
  throws UDFArgumentException {

    // *Check number of arguments.*
    if (args.length != 1) {
      throw new UDFArgumentException(
      "The UDTF should take exactly one argument");
    }
    /*
     * *Check that the input ObjectInspector[] array contains a* * *single PrimitiveObjectInspector of the Primitive type,* * *such as String.*
     */
    if (args[0].getCategory() != ObjectInspector.Category.PRIMITIVE
        && 
       ((PrimitiveObjectInspector) args[0]).getPrimitiveCategory() 
        != 
        PrimitiveObjectInspector.PrimitiveCategory.STRING) {
        throw new UDFArgumentException(
        "The UDTF should take a string as a parameter");
    }

    stringOI = (PrimitiveObjectInspector) args[0];
    /*
     * *Define the expected output for this function, including*
     * *each alias and types for the aliases.*
     */
    List<String> fieldNames = new ArrayList<String>(2);
    List<ObjectInspector> fieldOIs = new ArrayList<ObjectInspector>(2);
    fieldNames.add("alias1");
    fieldNames.add("alias2");
    fieldOIs.add(PrimitiveObjectInspectorFactory.
                 javaStringObjectInspector);
    fieldOIs.add(PrimitiveObjectInspectorFactory.
                 javaIntObjectInspector);
    //*Set up the output schema.*
    return ObjectInspectorFactory.
    getStandardStructObjectInspector(fieldNames, fieldOIs);
  }

  /**
   * *This method is called once per input row and generates* * *output. The "forward" method is used (instead of* * *"return") in order to specify the output from the function.*
   */
  @Override
  public void process(Object[] record) throws HiveException {
   /*
    * *We may need to convert the object to a primitive type*
    * *before implementing customized logic.*
    */
    final String recStr = (String) stringOI.
    getPrimitiveJavaObject(record[0]);

    //*Emit newly created structs after applying customized logic.*
    forward(new Object[] {recStr, Integer.valueOf(1)});
  }

  /**
   * *This method is for any cleanup that is necessary before* * *returning from the UDTF. Since the output stream has* * *already been closed at this point, this method cannot* * *emit more rows.*
   */
  @Override
  public void close() throws HiveException {
    //*Do nothing*.
  }
}

开发和部署

我们将通过一个示例完成整个开发和部署步骤。 让我们创建一个名为toUpper的简单函数，通过以下开发和部署步骤将字符串转换为大写：

1. 下载并安装 Java IDE，如 Eclipse 或 IntelliJ IDEA。
2. 启动 IDE 并创建一个 Java 项目
3. 右键单击项目以选择构建路径|配置构建路径|添加外部 Jars 选项。 它将打开一个新窗口。 导航到包含配置单元和 Hadoop 库的目录。 然后，选择并添加我们需要导入的所有 JAR 文件。 我们还可以使用 maven(maven.apache.org/)自动解析库依赖关系；本书的示例代码中给出了适当的pom.xml文件，以便作为 Maven 项目导入。
4. 在 IDE 中，根据前面提到的 UDF 模板创建以下ToUpper.java文件：

      package hive.essentials.hiveudf;

      import org.apache.hadoop.hive.ql.exec.UDF;
      import org.apache.hadoop.io.Text;

      class ToUpper extends UDF {
        public Text evaluate(Text input) {
          if(input == null) return null;
          return new Text(input.toString().toUpperCase());
        }
      }

5. 使用hiveudf-1.0.jar编译并构建项目 JAR 文件。
6. 使用hdfs dfs -put hiveudf-1.0.jar /app/hive/function/命令将 JAR 文件上传到 HDFS。
7. 将该函数创建为仅在当前会话中有效的临时函数。 从配置单元 v0.13.0 开始，我们还可以创建永久函数，该函数永久注册到元存储区，并且可以在所有查询和会话中引用：

 > CREATE TEMPORARY FUNCTION tmptoUpper 
 > as 'com.packtpub.hive.essentials.hiveudf.toupper';
 > USING JAR 'hdfs:///app/hive/function/hiveudf-1.0.jar';

 > CREATE FUNCTION toUpper -- Create permanent function
 > as 'hive.essentials.hiveudf.ToUpper' 
 > USING JAR 'hdfs:///app/hive/function/hiveudf-1.0.jar';

8. 验证并检查功能：

 > SHOW FUNCTIONS ToUpper;
 > DESCRIBE FUNCTION ToUpper;
 > DESCRIBE FUNCTION EXTENDED ToUpper;
 +----------------------------------------------------+
 | tab_name                                           |
 +----------------------------------------------------+
 | toUpper(value) - Returns upper case of value.      |
 | Synonyms: default.toupper                          |
 | Example:                                           |
 | > SELECT toUpper('will');                          |
 | WILL                                               |
 | Function class:hive.essentials.hiveudf.ToUpper     |
 | Function type:PERSISTENT                           |
 | Resource:hdfs:///app/hive/function/hiveudf-1.0.jar |
 +----------------------------------------------------+

9. 重新加载并使用 HQL 中的函数：

 > RELOAD FUNCTION; -- Reload all invisible functions if needed

 > SELECT 
 > name, toUpper(name) as cap_name, tmptoUpper(name) as cname 
 > FROM employee;
 +---------+----------+----------+
 | name    | cap_name | c_name   |
 +---------+----------+----------+
 | Michael | MICHAEL  | MICHAEL  |
 | Will    | WILL     | WILL     |
 | Shelley | SHELLEY  | SHELLEY  |
 | Lucy    | LUCY     | LUCY     |
 +---------+----------+----------+
 4 rows selected (0.363 seconds)

10. 在需要时删除该函数：

      > DROP TEMPORARY FUNCTION IF EXISTS tmptoUpper;
 > DROP FUNCTION IF EXISTS toUpper;

HPL/SQL

从 HifeV2.0.0 开始，Hadoop 过程语言 SQL(HPL/SQL)(www.hplsql.org/))可用于在 Hieve 中提供存储过程编程。 HPL/SQL 支持配置单元、Spark SQL 和 Impala，并与 Oracle、DB2、MySQL 和 TSQL 标准兼容。 它的好处之一是使现有的数据库存储过程迁移到配置单元变得简单而高效。 使用 HPL/SQL 不需要 Java 技能就可以实现前面提到的通过 UDF 实现的功能。 与 UDF 相比，HPL/SQL 的性能稍慢一些，在生产应用中仍然是新事物。

以下是创建存储存储过程的示例。 HPL/SQL 支持创建Function和Procedure：

$ cat getEmpCnt.pl
CREATE PROCEDURE getCount()
BEGIN
DECLARE cnt INT = 0;
SELECT COUNT(*) INTO cnt FROM employee;
PRINT 'Users cnt: ' || cnt;
END;

call getCount(); -- Call a procedure

为了运行过程，我们需要通过提供hiveserver2连接 URL 在hplsql-site.xml中设置数据库连接，如下所示。 之后，HPL/SQL 可以使用默认连接提交过程语句或文件：

SQLhplsql命令与带-f选项的hive命令位于同一文件夹中，如下所示：

$ cat /opt/hive2/conf/hplsql-site.xml

 <configuration>
 <property>
 <name>hplsql.conn.default</name>
 <value>hive2conn</value>
 </property>
 <property>
 <name>hplsql.conn.hive2conn</name>
 <value>org.apache.hive.jdbc.HiveDriver;jdbc:hive2://localhost:10500</value>
 </property>
 </configuration>

然后，我们可以呼叫 HPL：

$cd /opt/hive2/bin
$ ./hplsql -f getEmpCnt.pl
SLF4J: Class path contains multiple SLF4J bindings.
...
Open connection: jdbc:hive2://localhost:10500 (1.02 sec)
Starting query
Query executed successfully (569 ms)
Users cnt: 4

流

HIVE 还可以利用 Hadoop 中的流功能以另一种方式转换数据。 流 API 打开通往外部进程(如脚本)的 I/O 管道。 然后，该过程从标准输入读取数据，并通过标准输出将结果写出。 在 HQL 中，我们可以直接使用TRANSFORM子句来嵌入用命令、shell 脚本、Java 或其他编程语言编写的映射器和削减器脚本。 尽管流在进程之间使用序列化/反序列化带来了开销，但它为非 Java 开发人员提供了一种简单的编码模式。 TRANSFORM子句的语法如下：

FROM (
    FROM src
    SELECT TRANSFORM '(' expression (',' expression)* ')'
    (inRowFormat)?
    USING 'map_user_script'
    (AS colName (',' colName)*)?
    (outRowFormat)? (outRecordReader)?
    (CLUSTER BY?|DISTRIBUTE BY? SORT BY?) src_alias
 )
 SELECT TRANSFORM '(' expression (',' expression)* ')'
 (inRowFormat)?
 USING 'reduce_user_script'
 (AS colName (',' colName)*)?
 (outRowFormat)? (outRecordReader)? 

默认情况下，用户脚本的INPUT值如下：

• 转换为STRING值的列
• 由制表符分隔
• NULL转换为文本字符串的N值(区分NULL值和空字符串)

默认情况下，用户脚本的OUTPUT值如下：

• 视为制表符分隔的STRING列
• N将重新解释为NULL
• 生成的STRING列将转换为 TABLE 声明中指定的数据类型

可以用ROW FORMAT覆盖这些默认值。 使用 Python 脚本upper.py进行流式传输的示例如下：

$cat upper.py
#!/usr/bin/env python
'''
This is a script to upper all cases
'''
import sys

def main():
    try:
        for line in sys.stdin:
          n = line.strip()
          print n.upper()
    except:
        return None
if __name__ == "__main__":main()

通过以正常方式运行脚本来测试该脚本，如下所示：

$ echo "Will" | python upper.py
$ WILL

使用 HQL 调用脚本：

 > ADD FILE /tmp/upper.py;
 > SELECT 
 > TRANSFORM (name,work_place[0])
 > USING 'python upper.py' as (CAP_NAME,CAP_PLACE)
 > FROM employee;
 +-----------+------------+
 | cap_name  | cap_place  |
 +-----------+------------+
 | MICHAEL   | MONTREAL   |
 | WILL      | MONTREAL   |
 | SHELLEY   | NEW YORK   |
 | LUCY      | VANCOUVER  |
 | STEVEN    | NULL       |
 +-----------+------------+
 5 rows selected (30.101 seconds)

从配置单元 v0.13.0 开始配置基于 SQL 标准的授权时，不允许使用TRANSFORM命令。

谢尔德先生

SerDe 代表序列化和反序列化。 它是用于处理记录并将其映射到配置单元表格中的列数据类型的技术。 要解释使用 SerDe 的场景，我们需要首先了解配置单元是如何读写数据的。

读取数据的过程如下。

1. 数据从 HDFS 读取。
2. 数据由INPUTFORMAT实现处理，该实现定义输入数据拆分和键/值记录。 在配置单元中，我们可以使用CREATE TABLE ... STORED AS <FILE_FORMAT>(参见第 9 章，性能注意事项)来指定它从哪个INPUTFORMAT读取。
3. 调用 SerDe 中定义的 JavaDeserializer类将数据格式化为映射到表中的列和数据类型的记录。

作为读取数据的示例，我们可以使用 JSON SerDe 从 HDFS 读取TEXTFILE格式的数据，并将 JSON 属性和值的每一行转换为配置单元表中具有正确模式的行。

写入数据的流程如下：

1. 要写入的数据(如使用INSERT语句)由 SerDe 中定义的Serializer类转换为OUTPUTFORMAT类可以读取的格式。

2. 数据由OUTPUTFORMAT实现处理，该实现创建RecordWriter对象。 与INPUTFORMAT实现类似，OUTPUTFORMAT实现的指定方式与它写入数据的表的方式相同。

3. 数据被写入到表中(保存在 HDFS 中的数据)。

以写入数据为例，我们可以使用 JSON SerDe 将数据行-列写入配置单元表，JSON SerDe 将数据转换为保存到 HDFS 的 JSON 文本字符串。

支持的常用 SerDe(org.apache.hadoop.hive.serde2)列表如下：

• LazySimpleSerDe：与TEXTFILE格式一起使用的默认内置 SerDe(org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe)。 其实现方式如下：

 > CREATE TABLE test_serde_lz 
 > STORED as TEXTFILE as
 > SELECT name from employee;
 No rows affected (32.665 seconds)

• ColumnarSerDe：这是与RCFILE和ORC格式一起使用的内置 SerDe。 它的使用方法如下：

 > CREATE TABLE test_serde_rc
 > STORED as RCFILE as
 > SELECT name from employee;
 No rows affected (27.187 seconds)

 > CREATE TABLE test_serde_orc
 > STORED as ORC as
 > SELECT name from employee;
 No rows affected (24.087 seconds)

• RegexSerDe：这是 SerDe 中用于解析文本文件的内置 Java 正则表达式。 它的使用方法如下：

 > CREATE TABLE test_serde_rex(
 > name string,
 > gender string,
 > age string.
 > )
 > ROW FORMAT SERDE
 > 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
 > WITH SERDEPROPERTIES(
 >   'input.regex' = '([^,]*),([^,]*),([^,]*)',
 >   'output.format.string' = '%1$s %2$s %3$s'
 > )
 > STORED AS TEXTFILE;
 No rows affected (0.266 seconds)

• HBaseSerDe：这是内置的 SerDe，使配置单元能够与 HBase 集成。 通过利用该 SerDe 查询和插入数据，我们可以将配置单元表映射到现有的 HBase 表。 在运行以下查询之前，请确保 HBase 守护进程正在运行。 有关更多详细信息，请参阅第 1o 章和使用其他工具：

 > CREATE TABLE test_serde_hb(
 > id string,
 > name string,
 > gender string,
 > age string
 > )
 > ROW FORMAT SERDE
 > 'org.apache.hadoop.hive.hbase.HBaseSerDe'
 > STORED BY
 > 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
 > WITH SERDEPROPERTIES (
 > "hbase.columns.mapping"=
 > ":key,info:name,info:gender,info:age"
 > )
 > TBLPROPERTIES("hbase.table.name" = "test_serde");
 No rows affected (0.387 seconds)

• AvroSerDe：这是内置的 SerDe，可以读取和写入 Hive 表中的 AVRO(参见avro.apache.org/)数据。 AVRO 是一个远程过程调用和数据序列化框架。 从配置单元 v0.14.0 开始，只需通过三种方式将文件格式指定为AVRO，即可创建 Avro 支持的表格：

 > CREATE TABLE test_serde_avro( -- Specify schema directly
 > name string,
 > gender string,
 > age string
 > )
 > STORED as AVRO;
 No rows affected (0.31 seconds)

 > CREATE TABLE test_serde_avro2 -- Specify schema from properties
 > STORED as AVRO
 > TBLPROPERTIES (
 >   'avro.schema.literal'='{
 >    "type":"record",
 >    "name":"user",
 >    "fields":[ 
 >    {"name":"name", "type":"string"}, 
 >    {"name":"gender", "type":"string", "aliases":["gender"]}, 
 >    {"name":"age", "type":"string", "default":"null"}
 >    ]
 >   }'
 > );
 No rows affected (0.41 seconds)

      -- Using schema file directly as follows is a more flexiable way
 > CREATE TABLE test_serde_avro3 -- Specify schema from schema 
      file
 > STORED as AVRO
 > TBLPROPERTIES (
 > 'avro.schema.url'='/tmp/schema/test_avro_schema.avsc'
 > );
 No rows affected (0.21 seconds)

      -- Check the schema file
 $ cat /tmp/schema/test_avro_schema.avsc
 {
 "type" : "record",
 "name" : "test",
 "fields" : [
 {"name":"name", "type":"string"}, 
 {"name":"gender", "type":"string", "aliases":["gender"]}, 
 {"name":"age", "type":"string", "default":"null"}
 ]
 }

• ParquetHiveSerDe：这是内置 SerDe(parquet.hive.serde.ParquetHiveSerDe)，支持从配置单元 v0.13.0 开始读取和写入拼花数据格式。 它的使用方法如下：

 CREATE TABLE test_serde_parquet
 > STORED as PARQUET as 
 > SELECT name from employee;
 No rows affected (34.079 seconds)

• OpenCSVSerDe：这是读写 CSV 数据的串口。 从配置单元 v0.14.0 开始，它是内置的 SerDe。 OpenCSVSerDe比支持转义和引号规范等支持的内置行分隔符更强大。 它的使用方法如下：

 > CREATE TABLE test_serde_csv(
 > name string,
 > gender string,
 > age string
 >)
 > ROW FORMAT SERDE
 > 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
 > WITH SERDEPROPERTIES (
 >   "separatorChar" = "\t",
 >   "quoteChar" = "'",
 >   "escapeChar" = "\\"
 > ) 
 > STORED AS TEXTFILE;

• JSONSerDe：从配置单元 v0.12.0 开始，JSON SerDe 可用于使用配置单元读取和写入 JSON 数据记录：

 > CREATE TABLE test_serde_js(
 > name string,
 > gender string,
 > age string
 > )
 > ROW FORMAT SERDE 
 > 'org.apache.hive.hcatalog.data.JsonSerDe'
 > STORED AS TEXTFILE;
 No rows affected (0.245 seconds)

HIVE 还允许用户定义自定义 SerDe(如果所有这些都不适用于他们的数据格式)。 有关自定义 SerDe 的更多信息，请参阅位于cwiki.apache.org/confluence/…的配置单元维基。

简略的 / 概括的 / 简易判罪的 / 简易的

在本章中，我们介绍了扩展 Hive 功能的四个主要方面。 我们还介绍了三种用户定义函数，以及它们的编码模板和部署步骤，以指导编码和部署过程。 然后介绍了 HPL/SQL，它在 HQL 中加入了过程性语言编程。 此外，我们还讨论了流以插入您自己的代码，这些代码不必是 Java 代码。 在本章的最后，我们讨论了在读写数据时可用于解析不同格式的数据文件的 SerDe。 读完本章后，您应该能够编写基本的 UDF 和 HPL/SQL，将代码插入到流中，并在配置单元中使用可用的 SerDe。

在下一章中，我们将讨论安全注意事项。

九、安全考虑

对于大多数开源软件来说，安全是生产发布前需要解决的一个关键领域。 作为 Hadoop 数据的领先类 SQL 界面，配置单元必须确保数据得到安全保护和访问。 因此，Hive 的安全一直被认为是生态系统不可或缺的重要组成部分。 早期版本的配置单元主要依靠 HDFS 来保证安全性。 Hive 的安全性在hiveserver2发布后逐渐成熟。

本章将讨论以下方面的配置单元安全：

• 验证
• （被）授权 / （被）批准
• 掩码和加密

验证

身份验证是通过获取用户凭据来验证用户身份的过程。 配置单元从hiveserver2开始提供身份验证。 在旧版本的配置单元中，hiveserver1不支持储蓄客户端的 Kerberos 身份验证。 因此，如果我们可以通过网络访问主机/端口，我们就可以访问服务器。 相反，我们可以利用支持 Kerberos 的metastore服务器进行身份验证。 在本节中，我们将简要介绍metastore服务器和hiveserver2服务器中的身份验证配置。

Kerberos is a network authentication protocol developed by MIT as part of Project Athena. It uses time-sensitive tickets that are generated using symmetric key cryptography to securely authenticate a user in an unsecured network environment. Kerberos, in Greek mythology, was the three-headed dog that guarded the gates of Hades. The three-headed part refers to the three parties involved in the Kerberos authentication process: client, server, and Key Distribution Center (KDC). All clients and servers registered to KDC are known as a realm, which is typically the domain's DNS name in all caps. For more information, please refer to the MIT Kerberos website: web.mit.edu/kerberos/.

元存储区身份验证

要强制客户端使用 Kerberos 向metastore服务器进行身份验证，我们可以在hive-site.xml文件中设置以下三个属性，然后重新启动metastore服务器以使其正常工作：

1. 启用简单身份验证和安全层(SASL)框架以实施客户端 Kerberos 身份验证，如下所示：

      <property>
      <name>hive.metastore.sasl.enabled</name>
      <value>true</value>
      <description>If true, the metastore thrift interface will be 
      secured with SASL framework. Clients must authenticate with 
      Kerberos.</description>
      </property>

2. 指定生成的 Kerberos 密钥表。 如果要将文件保存在其他位置，请覆盖以下示例。 确保密钥表文件权限掩码设置为只读权限(600)，以避免意外更改或删除。 它还应该属于用于运行metastore服务器的同一帐户(默认情况下为配置单元)：

      <property>
      <name>hive.metastore.kerberos.keytab.file</name>
      <value>/etc/hive/conf/hive.keytab</value>
      <description>The sample path to the Kerberos Keytab file 
      containing the metastore thrift server's service principal.
      </description>
      </property>

3. 指定 Kerberos 主体模式字符串。 特殊字符串将自动替换为正确的主机名。 YOUR-REALM.COM值应替换为实际的域名：

      <property>
      <name>hive.metastore.kerberos.principal</name>
      <value>hive/_HOST@YOUR-REALM.COM</value>
      <description>The service principal for metastore server.
      </description>
      </property>

Hiveserver2 身份验证

hiveserver2支持多种身份验证模式，如 Kerberos、LDAP、PAM 和自定义代码。 要将hiveserver2配置为使用这些身份验证模式之一，我们可以在hive_site.xml中设置正确的属性，如下所示，然后重新启动hiveserver2服务以使其工作：

• NONE：默认设置中为无身份验证。 None这里的意思是允许使用以下设置进行匿名访问：

      <property>
      <name>hive.server2.authentication</name>
      <value>NONE</value>
      </property>

• KERBEROS：如果使用 Kerberos 身份验证，则用于在储蓄客户端与hiveserver2和hiveserver2之间进行身份验证，并保护 HDFS。 要为hiveserver2启用 Kerberos 身份验证，我们可以通过指定keytab路径并在YOUR-REALM.COM中指定实际域名来设置以下属性：

      <property>
      <name>hive.server2.authentication</name>
      <value>KERBEROS</value>
      </property>

      <property>
      <name>hive.server2.authentication.kerberos.keytab</name>
      <value>/etc/hive/conf/hive.keytab</value>
      </property>

      <property>
      <name>hive.server2.authentication.kerberos.principal</name>
      <value>hive/_HOST@YOUR-REALM.COM</value>
      </property>

Once Kerberos is enabled, the JDBC client (such as Beeline) must include the principal parameter in the JDBC connection string, such as jdbc:hive2://hiveserver2host:10000/default;principal=hive/_HOST@REALM. For more examples of the supported connection string syntax, refer to community.hortonworks.com/articles/41….

• LDAP：要将hiveserver2配置为使用LDAP支持的用户和密码验证(请参阅en.wikipedia.org/wiki/Lightw…)，我们可以设置以下属性：

      <property>
      <name>hive.server2.authentication</name>
      <value>LDAP</value>
      </property>

      <property>
      <name>hive.server2.authentication.ldap.url</name>
      <value>LDAP_URL, such as ldap://ldaphost@company.com</value>
      </property>

      <property>
      <name>hive.server2.authentication.ldap.Domain</name>
      <value>Domain Name</value>
      </property>

要使用OpenLDAP(en.wikipedia.org/wiki/OpenLD…)对其进行配置，我们可以添加baseDN设置，而不是前面的Domain属性，如下所示：

<property>
<name>hive.server2.authentication.ldap.baseDN</name>
<value>LDAP_BaseDN, such as ou=people,dc=packtpub,dc=com</value>
</property>

• CUSTOM：这表示hiveserver2的自定义身份验证提供程序。 要启用它，请按如下方式配置设置：

      <property>
      <name>hive.server2.authentication</name>
      <value>CUSTOM</value>
      </property>

      <property>
      <name>hive.server2.custom.authentication.class</name>
      <value>pluggable-auth-class-name</value>
      <description>Customized authentication class name, such as 
      com.packtpub.hive.essentials.hiveudf.customAuthenticator
      </description>
      </property>

Pluggable authentication with a customized class did not work until the bug (see issues.apache.org/jira/browse…) was fixed in Hive v0.13.0.

以下是实现org.apache.hive.service.auth.PasswdAuthenticationProvider接口的自定义类的示例。 被覆盖的Authenticate(...)方法具有如何验证用户名和密码的核心逻辑。 确保将编译的JAR文件复制到$HIVE_HOME/lib/，以便前面的设置可以工作：

*// customAuthenticator.java*
package com.packtpub.hive.essentials.hiveudf;

import java.util.Hashtable;
import javax.security.sasl.AuthenticationException;
import org.apache.hive.service.auth.PasswdAuthenticationProvider;

/*
 * *The customized class for hiveserver2 authentication*
 */

public class customAuthenticator implements PasswdAuthenticationProvider {

  Hashtable<String, String> authHashTable = null;

  public customAuthenticator () {
       authHashTable = new Hashtable<String, String>();
       authHashTable.put("user1", "passwd1");
       authHashTable.put("user2", "passwd2");
  }

  @Override
  public void Authenticate(String user, String password)
            throws AuthenticationException {

    String storedPasswd = authHashTable.get(user);

    if (storedPasswd != null && storedPasswd.equals(password))
         return;

    throw new AuthenticationException(
    "customAuthenticator Exception: Invalid user");
  }
}

• PAM：从配置单元 v0.13.0 开始，配置单元支持PAM(可插拔身份验证模块)身份验证，这提供了将现有身份验证机制插入到配置单元的好处。 配置以下设置以启用 PAM 身份验证。 有关如何安装 PAM 的更多信息，请参阅位于cwiki.apache.org/confluence/…的配置单元维基中的设置**hiveserver2 和文章。

      <property>
      <name>hive.server2.authentication</name>
      <value>PAM</value>
      </property>

      <property>
      <name>hive.server2.authentication.pam.services</name>
      <value>pluggable-auth-class-name</value>
      <description> Set this to a list of comma-separated PAM servicesthat 
      will be used. Note that a file with the same name as the PAMservice 
      must exist in /etc/pam.d.</description>
      </property>

（被）授权 / （被）批准

授权用于验证用户是否有权执行特定操作，如创建、读取或写入数据或元数据。 HIVE 提供三种授权模式：传统模式、基于存储的模式和基于 SQL 标准的模式。

传统模式

这是配置单元中的默认授权模式，通过 HQL 语句提供列级和行级授权。 但是，它不是完全安全的授权模式，并且有几个限制。 它主要用于防止好用户意外做坏事，而不是防止恶意用户操作。 为了启用传统授权模式，我们需要在hive-site.xml中设置以下属性：

<property>
<name>hive.security.authorization.enabled</name>
<value>true</value>
<description>enables or disable the hive client authorization
</description>
</property>

<property>
<name>hive.security.authorization.createtable.owner.grants</name>
<value>ALL</value>
<description>the privileges automatically granted to the owner whenever a table gets created. An example like "select, drop" will grant select and drop privilege to the owner of the table.
</description>
</property>

由于这不是一种安全的授权模式，我们在此不再详细讨论。 有关在传统授权模式下对 HQL 的更多支持，请参阅位于cwiki.apache.org/confluence/…的配置单元维基。

基于存储的模式

基于存储的授权模式(从配置单元 v0.10.0 开始)依赖于存储层 HDFS 提供的授权，存储层 HDFS 同时提供 POSIX 和 ACL 权限(从配置单元 v0.14.0 开始提供；请参阅issues.apache.org/jira/browse…)。 在metastore服务器中启用了基于存储的授权；它具有跨生态系统中其他应用的元数据的单一一致视图。 此模式根据 HDFS 中相应文件目录上的 POSIX 权限检查用户权限。 除了 POSIX 权限模型，HDFS 还提供了访问控制列表，这些列表在位于hadoop.apache.org/docs/r2.4.0…的 HDFS 上的 ACL 中描述。

考虑到其实现，基于存储的授权模式只提供数据库、表和分区级别的授权，而不是列和行级别的授权。 由于依赖于 HDFS 权限，它缺乏通过 HQL 语句管理授权的灵活性。 要启用基于存储的授权模式，我们可以在hive-site.xml文件中设置以下属性：

<property>
<name>hive.security.authorization.enabled</name>
<value>true</value>
<description>enable or disable the hive client authorization
</description>
</property>

</property>
<name>hive.metastore.pre.event.listeners</name>
<value>org.apache.hadoop.hive.ql.security.authorization.AuthorizationPreEventListener</value>
<description>This turns on metastore-side security.</description>
</property>

<property> 
<name>hive.security.authorization.manager</name> <value>org.apache.hadoop.hive.ql.security.authorization.StorageBasedAuthorizationProvider</value> 
<description>The class name of the Hive client authorization manager.</description> 
</property> 

<property> 
<name>hive.security.metastore.authorization.manager</name> <value>org.apache.hadoop.hive.ql.security.HadoopDefaultMetastoreAuthenticator
</value> 
<description>authenticator manager class name to be used in the metastore for authentication.</description> 
</property> 

<property>
<name>hive.security.metastore.authorization.auth.reads</name>
<value>true</value>
<description>If this is true, metastore authorizer authorizes read actions on database, table</description>
</property>

With effect from Hive v0.14.0, storage-based authorization also authorizes read privileges on databases and tables by default through the hive.security.metastore.authorization.auth.reads property. For more information, please refer to issues.apache.org/jira/browse….

基于 SQL 标准的模式

对于列和行级别的细粒度访问控制，我们可以使用从 Hiev0.13.0 开始提供的基于 SQL 标准的模式。 它类似于使用GRANT和REVOKE语句通过hiveserver2配置控制访问的关系数据库授权。 但是，诸如配置单元或 HDFS 命令之类的工具不能通过hiveserver2访问数据，因此基于 SQL 标准的模式无法授权它们访问。

因此，建议您结合使用基于存储的模式和基于 SQL 标准的模式，对从各种工具连接的用户进行授权。 要启用基于 SQL 标准的模式授权，我们可以在hive-site.xml文件中设置以下属性：

<property>
<name>hive.security.authorization.enabled</name>
<value>true</value>
<description>enable or disable the hive client authorization </description>
</property>

<property> 
<name>hive.server2.enable.doAs</name> 
<value>false</value> 
<description>Allows Hive queries to be run by the user who submits the query rather than the hive user. Need to turn if off for this SQL standard-base mode</description> 
</property> 

<property> 
<name>hive.users.in.admin.role</name> 
<value>dayongd,administrator</value> 
<description>Comma-separated list of users assigned to the ADMIN role.</description> 
</property> 

<property> 
<name>hive.security.authorization.manager</name>  <value>org.apache.hadoop.hive.ql.security.authorization.plugin.sql</value> </property> 

<property> 
<name>hive.security.authenticator.manager</name> <value>org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdConfOnlyAuthorizerFactory</value> 
</property> 

<property>
<name>hive.security.metastore.authorization.manager</name>
<value>org.apache.hadoop.hive.ql.security.authorization.StorageBasedAuthorizationProvider,org.apache.hadoop.hive.ql.security.authorization.MetaStoreAuthzAPIAuthorizerEmbedOnly</value>
<description>It takes a comma separated list, so we can add MetaStoreAuthzAPIAuthorizerEmbedOnly along with StorageBasedAuthorization parameter,if we want to enable that as well</description>
</property>

此外，在重启hiveserver2之前，我们需要在hiveserver2-site.xml中设置以下配置，以使基于 SQL 标准的授权生效：

<configuration>

<property>
<name>hive.security.authorization.enabled</name>
<value>true</value>
<description></description>
</property>

<property>
<name>hive.security.authorization.manager</name
<value>org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdHiveAuthorizerFactory</value>
</property>

<property>
<name>hive.security.authenticator.manager</name>
<value>org.apache.hadoop.hive.ql.security.SessionStateUserAuthenticator</value>
</property>

<property>
<name>hive.metastore.uris</name>
<value>thrift://localhost:9085</value>
<description>Use 9083 for hive1 and 9085 for hive2</description>
</property> 

</configuration>

Before restarting hiveserver2 to enable the preceding setting, do not forget to grant admin roles to the users defined in hive.users.in.admin.role using GRANT admin TO USER <user_name>.

使用基于 SQL 标准的模式授权，我们可以在两个级别上管理权限：角色或对象。

授予或撤销角色级别的授权的语法如下：

• GRANT <ROLE_NAME> TO <PRINCIPLES> [ WITH ADMIN OPTION]
• REVOKE [ADMIN OPTION FOR] <ROLE_NAME> FROM <PRINCIPLES>

这些参数的用法如下：

• <ROLE_NAME>：这是逗号分隔的角色名称
• <PRINCIPLES>：这是用户或角色
• WITH ADMIN OPTION：这是可选的。 一旦指定，它将确保用户获得将该角色授予其他用户/角色的权限

另一方面，在对象级别授予或撤销授权的语法如下：

• GRANT <PRIVILEGE> ON <OBJECT> TO <PRINCIPLES>
• REVOKE <PRIVILEGE> ON <OBJECT> FROM <PRINCIPLES>

这里使用的参数如下：

• <PRIVILEGE>：可以是INSERT、SELECT、UPDATE、DELETE或ALL
• <PRINCIPLES>：这可以是用户或角色
• <OBJECT>：这是一个表或视图

有关管理基于 sql 标准授权的 hql 语句的更多示例，请参考位于cwiki.apache.org/confluence/…的配置单元维基。

Apache Sentry is a highly modular system for providing centralized, fine-grained, role-based authorization to both data and metadata stored on an Apache Hadoop cluster. It can be integrated with Hive to deliver advanced authorization controls. For more information about Sentry, please refer to sentry.apache.org/. Sentry is usually distributed in the Cloudera CDH package. Another similar project is Apache Ranger (ranger.apache.org/), which is usually distributed in the Hortonworks HDP package.

掩码和加密

对于敏感和受法律保护的数据，如个人身份信息(PII)或个人机密信息(PCI)，需要在文件系统中以加密或屏蔽格式存储数据。 从 Hive v0.13.0 开始，其数据安全功能在数据散列、数据掩蔽、安全和数据加解密功能等领域已经成熟。

数据散列函数

在支持屏蔽数据之前，自 Hieve v1.3.0 以来，内置散列函数一直是一种选择。 散列函数读取输入字符串并生成固定大小的字母数字输出字符串。 由于输出通常是到输入字符串的唯一映射(冲突的可能性很小)，因此散列值经常用于保护列，列是连接或比较数据的唯一标识符。 内置函数，如md5(...)、sha1(...)和sha2(...)，可用于 HQL 中的数据散列：

> SELECT 
> name, 
> md5(name) as md5_name, -- 128 bit
> sha1(name) as sha1_name, -- 160 bit
> sha2(name, 256) as sha2_name -- 256 bit
> FROM employee;
+---------+----------------------------------+
| name    | md5_name                         |
+---------+----------------------------------+
| Michael | 3e06fa3927cbdf4e9d93ba4541acce86 |
| Will    | 2b80f09163f60ce1774b438e605eb1f9 |
| Shelley | e47e592945f28b3c3891ee9d27ec6b61 |
| Lucy    | 80eb0e612760f756547b660c4c71ba7d |
+---------+----------------------------------+
+------------------------------------------+
| sha1_name                                |
+------------------------------------------+
| f8c38b2167c0ab6d7c720e47c2139428d77d8b6a |
| 3e3e5802bd4cad8e29e144b515307d8204a3202a |
| 2d4cab849437156354d24c9564958e6581711d08 |
| c5c8f32bdf9998e0f692231f4f969085c8dc225b |
+------------------------------------------+
+------------------------------------------------------------------+
| sha2_name                                                        |
+------------------------------------------------------------------+
| f089eaef57aba315bc0e1455985c0c8e40c247f073ce1f4c5a1f8ffde8773176 |
| 6cef4ccc1019d6cee6b9cad39d49cabf808ba2e0665d5832b70c44c09c2dfae0 |
| 1e8b342dde7c90cfbc9634c777b6b59388b6a4bd14274adffbfaeed4b329b26e |
| a3fa95a3b95d421c316f1a9b12c88edcc47896705976764d2652425de98f0c4f |
+------------------------------------------------------------------+
4 rows selected (0.344 seconds)

数据屏蔽功能

从配置单元 v2.1.0 开始，数据掩码函数作为内置 UDF 在 SQL 中可用。 对于信用卡号码、银行账号和密码等用户敏感数据，经常需要屏蔽数据。 与散列函数不同的是，SQL 中的 MASK 函数可以指定对部分数据进行屏蔽，这使得您在想要保持部分数据不被屏蔽以便更好地理解时更加灵活。 以下是使用 HQL 中的各种掩码函数的示例：

> SELECT
 -- big letter to U, small letter to l, number to #
> mask("Card-0123-4567-8910", "U", "l", "#") as m0,
 -- mask first n (4) values where X|x for big/small letter, n for number
> mask_first_n("Card-0123-4567-8910", 4) as m1,
 -- mask last n (4) values
> mask_last_n("Card-0123-4567-8910", 4) as m2,
 -- mask everthing except first n(4) values
> mask_show_first_n("Card-0123-4567-8910", 4) as m3,
 -- mask everthing except last n(4) values
> mask_show_last_n("Card-0123-4567-8910", 4) as m4,
 -- return a hash value - sha 256 hex
> mask_hash('Card-0123-4567-8910') as m5
> ;
+-----------------------+-----------------------+------------------------+
| m0                    | m1                    | m2                     |
+-----------------------+-----------------------+------------------------+
| Ulll-####-####-####   | Xxxx-0123-4567-8910   | Card-0123-4567-nnnn    |
+-----------------------+-----------------------+------------------------+
+-------------------+-------------------+--------------------------------+
| m3                | m4                | m5                             |
+-------------------+-------------------+--------------------------------+
|Card-nnnn-nnnn-nnnn|Xxxx-nnnn-nnnn-8910|f0679e470f380ce5183ba403ec0e7e64|
+-------------------+-------------------+--------------------------------+
1 row selected (0.146 seconds)

数据加密功能

从 hive v1.3.0 开始，提供了aes_encrypt(input string/binary, key string/binary)和aes_decrypt(input binary, key string/binary)自定义函数来支持使用 AAES(高级加密标准：Een.wikipedia.org/wiki/Advanc…)算法进行数据加密和解密，该算法是由比利时密码学家琼·达门和文森特·里曼开发的一种对称的 128 位块数据加密技术。

以下是使用这些函数的示例：

-- 1st para. is value to encryped/decryped
-- 2nd para. is 128 bit (16 Byte) keys
> SELECT
> name,
> aes_encrypt(name,'1234567890123456') as encrypted,
> aes_decrypt(
> aes_encrypt(name,'1234567890123456'),
> '1234567890123456') as decrypted
> FROM employee;
+---------+-------------------------+-----------+
| name    | encrypted               | decrypted |
+---------+-------------------------+-----------+
| Michael | ��.b��#����-��I    | Micheal   |
| Will    | "�""��r {cgR�%���    | Will      |
| Shelley | ��W@�Dm�[-�?�        | Shelley   |
| Lucy    | ��/i���x���L�q~     | Lucy      |
+---------+------------------------+------------+
4 rows selected (0.24 seconds)

其他方法

如前所述，我们可以使用 Apache Ranger 或 Sentry 进行列级访问和控制，以实现更精细的安全性。 此外，还有补丁可用于直接在表创建语句上指定列级编码，例如 hive6329c(issues.apache.org/jira/browse…)和 hive7934(issues.apache.org/jira/browse…)。 在存储级别，配置单元还可以利用 HDFS 加密技术(issues.apache.org/jira/browse…)，它提供对 HDFS 上数据的透明加密和解密。 如果我们想在 HDFS 中加密整个数据集，它将满足我们的要求。

简略的 / 概括的 / 简易判罪的 / 简易的

在本章中，我们介绍了配置单元安全领域的身份验证、授权、掩码和加密。 我们介绍了metastore服务器和hiveserver2服务器中的身份验证。 然后，我们讨论了默认模式授权、基于存储的模式授权和基于 SQL 标准的模式授权。 在本章的最后，我们讨论了在配置单元中应用数据掩码和安全性的各种方法。 读完本章后，您应该能够使用不同的身份验证、授权和数据掩码或安全方法来解决安全问题。

在下一章中，我们将讨论如何将 Hive 与大数据生态系统中的其他工具配合使用。