hive常用的一些高级函数汇总

hive处理json字符串的函数
Hive是基于Hadoop的数据仓库工具，支持使用HiveQL（一种类SQL的查询语言）查询和处理数据。Hive提供了许多用于处理JSON数据的内置函数，以下是一些常用的JSON处理函数和示例：

get_json_object(json_string, path)：从JSON字符串中提取指定的字段值，根据提供的路径返回一个JSON子字符串或一个原始值。例如：

SELECT get_json_object('{"name":"John","age":30}', '$.name');
-- 输出：John
SELECT get_json_object('{"name":"John","age":30}', '$.age');
-- 输出：30
json_tuple(json_string, field1, field2, ...)：从JSON字符串中提取指定的多个字段值，返回一个元组。例如：

SELECT json_tuple('{"name":"John","age":30}', 'name', 'age');
-- 输出：("John", "30")
json_array_contains(json_array, value)：检查一个JSON数组是否包含指定的值，返回一个布尔值。例如：

SELECT json_array_contains('[1, 2, 3]', 2);
-- 输出：true
SELECT json_array_contains('[{"name":"John","age":30}, {"name":"Bob","age":25}]', '{"name":"John","age":30}');
-- 输出：true
get_json_object_index(json_string, index)：从JSON数组中获取指定索引位置的元素。例如：

SELECT get_json_object_index('[1, 2, 3]', 1);
-- 输出：2
SELECT get_json_object_index('[{"name":"John","age":30}, {"name":"Bob","age":25}]', 0);
-- 输出：{"name":"John","age":30}
json_array_length(json_array)：获取JSON数组的长度。例如：

SELECT json_array_length('[1, 2, 3]');
-- 输出：3
SELECT json_array_length('[{"name":"John","age":30}, {"name":"Bob","age":25}]');
-- 输出：2
这些是Hive中常用的JSON处理函数和示例，还有其他一些类似的函数可以用于处理JSON数据。

Hive  lateral view 函数
Lateral View 是 Hive 中用于展开嵌套数据结构的关键字，可以将数组、Map 和结构体等复杂数据类型展开成多个行，从而使这些数据能够被查询和分析。

在 Hive 中，Lateral View 语句通常用于与 explode 或 inline 函数结合使用，以将数组或 Map 中的元素分解成单独的行。Lateral View 可以用于 SELECT 和 FROM 语句中。

下面是一个使用 Lateral View 和 explode 函数将数组展开的示例：

SELECT id, name, score
FROM students
LATERAL VIEW explode(scores) exploded_scores AS score;
上述示例中，students 表包含了学生的信息，其中 scores 是一个数组类型的字段，记录了学生的考试成绩。使用 Lateral View 和 explode 函数，可以将每个学生的成绩展开成多个行，每行包含学生的 ID、姓名和一个单独的成绩。

Lateral View 还可以与 UDTF（用户定义的表生成函数）结合使用，以实现更复杂的数据展开和处理操作。例如，可以编写一个自定义函数来将结构体中的字段展开成多个行，并使用 Lateral View 将其应用到 Hive 表中。

需要注意的是，Lateral View 语句只能用于  Hive 中的查询操作，而不能用于更新、删除或插入数据等操作。

hive 常用的一些高级函数
Hive 中有许多高级函数可用于数据处理和分析。以下是一些常用的高级函数及其用途：

Window functions：支持在结果集上执行窗口聚合操作，如窗口函数、排序等。例如，row_number()、rank()、dense_rank() 等。

Aggregate functions：支持对数据集进行聚合计算，如求和、计数、平均值等。例如，SUM()、COUNT()、AVG() 等。

Date functions：支持对日期和时间类型的数据进行操作，如日期格式化、日期比较等。例如，to_date()、date_add()、date_sub() 等。

String functions：支持对字符串类型的数据进行操作，如字符串拼接、字符串替换等。例如，concat()、substr()、replace() 等。

Math functions：支持对数值类型的数据进行操作，如绝对值、指数、对数等。例如，abs()、exp()、log() 等。

Array functions：支持对数组类型的数据进行操作，如数组展开、数组聚合等。例如，explode()、collect_set()、、collect_array()、array_contains() 等。

Map functions：支持对 Map 类型的数据进行操作，如 Map 展开、Map 聚合等。例如，map()、explode()、aggregate() 等。

Struct functions：支持对结构体类型的数据进行操作，如结构体字段访问、结构体字段拼接等。例如，get_json_object()、concat_ws() 等。

Conditional functions：支持对数据进行条件判断，如 IF 语句、CASE 语句等。例如，IF()、CASE WHEN THEN ELSE END 等。

这些高级函数提供了更加灵活和强大的数据处理和分析能力，使得 Hive 在数据仓库和大数据分析领域具有很高的应用价值。以下是对 Hive 中一些常用的高级函数的详细解释以及示例：

Window functions：支持在结果集上执行窗口聚合操作，如窗口函数、排序等。常用的窗口函数包括：
ROW_NUMBER()：返回分区内行的序号，从 1 开始递增。

RANK()：返回分区内行的排名，排名相同的行会得到相同的排名，下一行的排名会跳过相同的排名数。

DENSE_RANK()：返回分区内行的稠密排名，排名相同的行会得到相同的排名，下一行的排名不会跳过相同的排名数。

NTILE()：将数据划分为指定数量的桶，然后为每一行数据分配所在的桶号。

LAG()：获取当前行指定列的上一行数据。

LEAD()：获取当前行指定列的下一行数据。

下面是一个使用 ROW_NUMBER() 函数实现每个部门工资排名的示例：

SELECT dept_id, emp_id, salary, ROW_NUMBER() OVER(PARTITION BY dept_id ORDER BY salary DESC) as rank
FROM employee;
上述示例中，employee 表包含了员工的信息，其中 dept_id 是员工所属部门的 ID，salary 是员工的薪水。使用 ROW_NUMBER() 函数，可以为每个部门的员工根据薪水进行排序，并为每个员工分配一个排名。

Aggregate functions：支持对数据集进行聚合计算，如求和、计数、平均值等。常用的聚合函数包括：
SUM()：计算数据集中数值型字段的总和。

COUNT()：计算数据集中记录的数量。

AVG()：计算数据集中数值型字段的平均值。

下面是一个使用 SUM() 函数计算每个部门的总薪水的示例：

SELECT dept_id, SUM(salary) as total_salary
FROM employee
GROUP BY dept_id;
上述示例中，employee 表包含了员工的信息，其中 dept_id 是员工所属部门的 ID，salary 是员工的薪水。使用 SUM() 函数和 GROUP BY 子句，可以对每个部门的员工薪水进行汇总计算。

Date functions：支持对日期和时间类型的数据进行操作，如日期格式化、日期比较等。常用的日期函数包括：
TO_DATE()：将字符串类型的日期转换成日期类型。

DATE_ADD()：对日期进行加法运算。

DATE_SUB()：对日期进行减法运算。

下面是一个使用 DATE_ADD() 函数计算每个员工下个月的生日的示例：

SELECT emp_id, name, birthday, DATE_ADD(birthday, INTERVAL 1 MONTH) as next_month_birthday
FROM employee;
上述示例中，employee 表包含了员工的信息，其中 birthday 是员工的生日。使用 DATE_ADD() 函数和 INTERVAL 关键字，可以对员工的生日进行计算，得到下个月的生日日期。

String functions：支持对字符串类型的数据进行操作，如字符串拼接、字符串截取、字符串替换等。常用的字符串函数包括：
CONCAT()：将多个字符串拼接在一起。

SUBSTR()：截取字符串的一部分。

REPLACE()：将字符串中的指定子串替换成另一个字符串。

下面是一个使用 CONCAT() 函数将员工姓名和工号拼接在一起的示例：

SELECT CONCAT(name, '-', emp_id) as emp_info
FROM employee;
上述示例中，employee 表包含了员工的信息，其中 name 是员工的姓名，emp_id 是员工的工号。使用 CONCAT() 函数，可以将员工的姓名和工号拼接在一起，形成一个字符串。

Conditional functions：支持在 SQL 语句中添加条件判断，根据不同的条件返回不同的结果。常用的条件函数包括：
CASE WHEN：根据指定的条件判断返回不同的结果。

IF()：根据指定的条件判断返回不同的结果。

COALESCE()：返回参数列表中第一个非空值。

下面是一个使用 CASE WHEN 函数根据员工的薪水等级计算每个部门的平均薪水的示例：

SELECT dept_id,
       CASE WHEN AVG(salary) >= 10000 THEN '高薪'
            WHEN AVG(salary) >= 8000 THEN '中等薪'
            ELSE '低薪' END AS salary_level,
       AVG(salary) as avg_salary
FROM employee
GROUP BY dept_id;
上述示例中，employee 表包含了员工的信息，其中 dept_id 是员工所属部门的 ID，salary 是员工的薪水。使用 CASE WHEN 函数，可以根据员工的薪水等级计算每个部门的平均薪水，并为不同薪水等级分配不同的标签。

Math functions：支持对数值型数据进行数学运算，如求绝对值、取整、开方等。常用的数学函数包括：
ABS()：返回数值的绝对值。

CEIL()：向上取整。

FLOOR()：向下取整。

下面是一个使用 ABS() 函数计算员工薪水增长率的示例：

SELECT emp_id, salary, ABS((salary - last_salary) / last_salary) as salary_increase_rate
FROM (
  SELECT emp_id, salary, LAG(salary) OVER(PARTITION BY dept_id ORDER BY hire_date) as last_salary
  FROM employee
) t;
上述示例中，employee 表包含了员工的信息，其中 emp_id 是员工的 ID，salary 是员工的薪水，hire_date 是员工的入职日期。使用 LAG() 函数，可以获取上一次的薪水，并使用 ABS() 函数计算员工薪水的增长率。

Date functions：支持对日期类型的数据进行操作，如日期格式化、日期计算等。常用的日期函数包括：
TO_DATE()：将字符串转换为日期类型。

DATE_FORMAT()：将日期类型转换为指定格式的字符串。

DATEDIFF()：计算两个日期之间的天数差。

下面是一个使用 DATE_FORMAT() 函数将日期格式化为指定格式的示例：

SELECT emp_id, hire_date, DATE_FORMAT(hire_date, 'yyyy-MM-dd') as formatted_hire_date
FROM employee;
上述示例中，employee 表包含了员工的信息，其中 emp_id 是员工的 ID，hire_date 是员工的入职日期。使用 DATE_FORMAT() 函数，可以将日期格式化为指定的格式，例如将日期格式化为 'yyyy-MM-dd' 的形式。

Collection functions：支持对集合类型的数据进行操作，如数组、Map 等。常用的集合函数包括：
ARRAY()：将多个元素组成一个数组。

MAP()：将多个键值对组成一个 Map。

EXPLODE()：将数组或 Map 拆分成多行。

下面是一个使用 ARRAY() 函数将员工的技能列表转换为数组的示例：

SELECT emp_id, skill_list, ARRAY(skill_list) as skills
FROM employee;
上述示例中，employee 表包含了员工的信息，其中 emp_id 是员工的 ID，skill_list 是员工掌握的技能列表。使用 ARRAY() 函数，可以将技能列表转换为一个数组。

总结
以下是我对前面提到的一些 Hive 函数进行的总结：

1.数学函数：Hive 支持包括 ABS、CEIL、FLOOR、ROUND、TRUNCATE、EXP、LN、LOG2、LOG10、POWER、SQRT、MOD 等常见的数学函数，用于进行常见的数值计算和操作。

2.字符串函数：Hive 支持包括 CONCAT、SUBSTR、TRIM、UPPER、LOWER、REPLACE、REGEXP_EXTRACT、LENGTH 等常见的字符串函数，用于进行字符串操作和处理。

3.日期函数：Hive 支持包括 YEAR、MONTH、DAY、HOUR、MINUTE、SECOND、DATE_FORMAT、FROM_UNIXTIME、TO_DATE、ADD_MONTHS 等常见的日期和时间函数，用于进行日期和时间相关的计算和操作。

4.聚合函数：Hive 支持包括 SUM、AVG、MIN、MAX、COUNT 等常见的聚合函数，用于进行数据聚合和统计计算。

5.分析函数：Hive 支持包括 ROW_NUMBER、RANK、DENSE_RANK、NTILE、LAG、LEAD 等常见的分析函数，用于进行数据分析和统计计算。

6.窗口函数：Hive 支持使用 OVER 子句进行窗口函数的计算，常见的窗口函数包括 ROW_NUMBER、RANK、DENSE_RANK、NTILE、LAG、LEAD 等，用于进行复杂的数据分析和统计计算。

7.高级函数：Hive 还支持包括 ARRAY、MAP、STRUCT、EXPLODE、TRANSFORM、LATERAL VIEW 等高级函数，用于处理复杂的数据类型和结构，实现更加灵活和高效的数据分析和处理。

总的来说，Hive 的函数库非常丰富和强大，可以帮助用户轻松地进行各种数据操作和计算，为数据分析和处理提供了很大的便利和支持。



作者：薛秋艳

欢迎关注微信公众号 ：大数据球球