hive常用的一些高級(jí)函數(shù)匯總

hive處理json字符串的函數(shù)
Hive是基于Hadoop的數(shù)據(jù)倉(cāng)庫(kù)工具，支持使用HiveQL（一種類(lèi)SQL的查詢(xún)語(yǔ)言）查詢(xún)和處理數(shù)據(jù)。Hive提供了許多用于處理JSON數(shù)據(jù)的內(nèi)置函數(shù)，以下是一些常用的JSON處理函數(shù)和示例：

get_json_object(json_string, path)：從JSON字符串中提取指定的字段值，根據(jù)提供的路徑返回一個(gè)JSON子字符串或一個(gè)原始值。例如：

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字符串中提取指定的多個(gè)字段值，返回一個(gè)元組。例如：

SELECT json_tuple('{"name":"John","age":30}', 'name', 'age');
-- 輸出：("John", "30")
json_array_contains(json_array, value)：檢查一個(gè)JSON數(shù)組是否包含指定的值，返回一個(gè)布爾值。例如：

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數(shù)組中獲取指定索引位置的元素。例如：

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數(shù)組的長(zhǎng)度。例如：

SELECT json_array_length('[1, 2, 3]');
-- 輸出：3
SELECT json_array_length('[{"name":"John","age":30}, {"name":"Bob","age":25}]');
-- 輸出：2
這些是Hive中常用的JSON處理函數(shù)和示例，還有其他一些類(lèi)似的函數(shù)可以用于處理JSON數(shù)據(jù)。

Hive  lateral view 函數(shù)
Lateral View 是 Hive 中用于展開(kāi)嵌套數(shù)據(jù)結(jié)構(gòu)的關(guān)鍵字，可以將數(shù)組、Map 和結(jié)構(gòu)體等復(fù)雜數(shù)據(jù)類(lèi)型展開(kāi)成多個(gè)行，從而使這些數(shù)據(jù)能夠被查詢(xún)和分析。

在 Hive 中，Lateral View 語(yǔ)句通常用于與 explode 或 inline 函數(shù)結(jié)合使用，以將數(shù)組或 Map 中的元素分解成單獨(dú)的行。Lateral View 可以用于 SELECT 和 FROM 語(yǔ)句中。

下面是一個(gè)使用 Lateral View 和 explode 函數(shù)將數(shù)組展開(kāi)的示例：

SELECT id, name, score
FROM students
LATERAL VIEW explode(scores) exploded_scores AS score;
上述示例中，students 表包含了學(xué)生的信息，其中 scores 是一個(gè)數(shù)組類(lèi)型的字段，記錄了學(xué)生的考試成績(jī)。使用 Lateral View 和 explode 函數(shù)，可以將每個(gè)學(xué)生的成績(jī)展開(kāi)成多個(gè)行，每行包含學(xué)生的 ID、姓名和一個(gè)單獨(dú)的成績(jī)。

Lateral View 還可以與 UDTF（用戶(hù)定義的表生成函數(shù)）結(jié)合使用，以實(shí)現(xiàn)更復(fù)雜的數(shù)據(jù)展開(kāi)和處理操作。例如，可以編寫(xiě)一個(gè)自定義函數(shù)來(lái)將結(jié)構(gòu)體中的字段展開(kāi)成多個(gè)行，并使用 Lateral View 將其應(yīng)用到 Hive 表中。

需要注意的是，Lateral View 語(yǔ)句只能用于  Hive 中的查詢(xún)操作，而不能用于更新、刪除或插入數(shù)據(jù)等操作。

hive 常用的一些高級(jí)函數(shù)
Hive 中有許多高級(jí)函數(shù)可用于數(shù)據(jù)處理和分析。以下是一些常用的高級(jí)函數(shù)及其用途：

Window functions：支持在結(jié)果集上執(zhí)行窗口聚合操作，如窗口函數(shù)、排序等。例如，row_number()、rank()、dense_rank() 等。

Aggregate functions：支持對(duì)數(shù)據(jù)集進(jìn)行聚合計(jì)算，如求和、計(jì)數(shù)、平均值等。例如，SUM()、COUNT()、AVG() 等。

Date functions：支持對(duì)日期和時(shí)間類(lèi)型的數(shù)據(jù)進(jìn)行操作，如日期格式化、日期比較等。例如，to_date()、date_add()、date_sub() 等。

String functions：支持對(duì)字符串類(lèi)型的數(shù)據(jù)進(jìn)行操作，如字符串拼接、字符串替換等。例如，concat()、substr()、replace() 等。

Math functions：支持對(duì)數(shù)值類(lèi)型的數(shù)據(jù)進(jìn)行操作，如絕對(duì)值、指數(shù)、對(duì)數(shù)等。例如，abs()、exp()、log() 等。

Array functions：支持對(duì)數(shù)組類(lèi)型的數(shù)據(jù)進(jìn)行操作，如數(shù)組展開(kāi)、數(shù)組聚合等。例如，explode()、collect_set()、、collect_array()、array_contains() 等。

Map functions：支持對(duì) Map 類(lèi)型的數(shù)據(jù)進(jìn)行操作，如 Map 展開(kāi)、Map 聚合等。例如，map()、explode()、aggregate() 等。

Struct functions：支持對(duì)結(jié)構(gòu)體類(lèi)型的數(shù)據(jù)進(jìn)行操作，如結(jié)構(gòu)體字段訪(fǎng)問(wèn)、結(jié)構(gòu)體字段拼接等。例如，get_json_object()、concat_ws() 等。

Conditional functions：支持對(duì)數(shù)據(jù)進(jìn)行條件判斷，如 IF 語(yǔ)句、CASE 語(yǔ)句等。例如，IF()、CASE WHEN THEN ELSE END 等。

這些高級(jí)函數(shù)提供了更加靈活和強(qiáng)大的數(shù)據(jù)處理和分析能力，使得 Hive 在數(shù)據(jù)倉(cāng)庫(kù)和大數(shù)據(jù)分析領(lǐng)域具有很高的應(yīng)用價(jià)值。以下是對(duì) Hive 中一些常用的高級(jí)函數(shù)的詳細(xì)解釋以及示例：

Window functions：支持在結(jié)果集上執(zhí)行窗口聚合操作，如窗口函數(shù)、排序等。常用的窗口函數(shù)包括：
ROW_NUMBER()：返回分區(qū)內(nèi)行的序號(hào)，從 1 開(kāi)始遞增。

RANK()：返回分區(qū)內(nèi)行的排名，排名相同的行會(huì)得到相同的排名，下一行的排名會(huì)跳過(guò)相同的排名數(shù)。

DENSE_RANK()：返回分區(qū)內(nèi)行的稠密排名，排名相同的行會(huì)得到相同的排名，下一行的排名不會(huì)跳過(guò)相同的排名數(shù)。

NTILE()：將數(shù)據(jù)劃分為指定數(shù)量的桶，然后為每一行數(shù)據(jù)分配所在的桶號(hào)。

LAG()：獲取當(dāng)前行指定列的上一行數(shù)據(jù)。

LEAD()：獲取當(dāng)前行指定列的下一行數(shù)據(jù)。

下面是一個(gè)使用 ROW_NUMBER() 函數(shù)實(shí)現(xiàn)每個(gè)部門(mén)工資排名的示例：

SELECT dept_id, emp_id, salary, ROW_NUMBER() OVER(PARTITION BY dept_id ORDER BY salary DESC) as rank
FROM employee;
上述示例中，employee 表包含了員工的信息，其中 dept_id 是員工所屬部門(mén)的 ID，salary 是員工的薪水。使用 ROW_NUMBER() 函數(shù)，可以為每個(gè)部門(mén)的員工根據(jù)薪水進(jìn)行排序，并為每個(gè)員工分配一個(gè)排名。






Aggregate functions：支持對(duì)數(shù)據(jù)集進(jìn)行聚合計(jì)算，如求和、計(jì)數(shù)、平均值等。常用的聚合函數(shù)包括：
SUM()：計(jì)算數(shù)據(jù)集中數(shù)值型字段的總和。

COUNT()：計(jì)算數(shù)據(jù)集中記錄的數(shù)量。

AVG()：計(jì)算數(shù)據(jù)集中數(shù)值型字段的平均值。

下面是一個(gè)使用 SUM() 函數(shù)計(jì)算每個(gè)部門(mén)的總薪水的示例：

SELECT dept_id, SUM(salary) as total_salary
FROM employee
GROUP BY dept_id;
上述示例中，employee 表包含了員工的信息，其中 dept_id 是員工所屬部門(mén)的 ID，salary 是員工的薪水。使用 SUM() 函數(shù)和 GROUP BY 子句，可以對(duì)每個(gè)部門(mén)的員工薪水進(jìn)行匯總計(jì)算。

Date functions：支持對(duì)日期和時(shí)間類(lèi)型的數(shù)據(jù)進(jìn)行操作，如日期格式化、日期比較等。常用的日期函數(shù)包括：
TO_DATE()：將字符串類(lèi)型的日期轉(zhuǎn)換成日期類(lèi)型。

DATE_ADD()：對(duì)日期進(jìn)行加法運(yùn)算。

DATE_SUB()：對(duì)日期進(jìn)行減法運(yùn)算。

下面是一個(gè)使用 DATE_ADD() 函數(shù)計(jì)算每個(gè)員工下個(gè)月的生日的示例：

SELECT emp_id, name, birthday, DATE_ADD(birthday, INTERVAL 1 MONTH) as next_month_birthday
FROM employee;
上述示例中，employee 表包含了員工的信息，其中 birthday 是員工的生日。使用 DATE_ADD() 函數(shù)和 INTERVAL 關(guān)鍵字，可以對(duì)員工的生日進(jìn)行計(jì)算，得到下個(gè)月的生日日期。

String functions：支持對(duì)字符串類(lèi)型的數(shù)據(jù)進(jìn)行操作，如字符串拼接、字符串截取、字符串替換等。常用的字符串函數(shù)包括：
CONCAT()：將多個(gè)字符串拼接在一起。

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

REPLACE()：將字符串中的指定子串替換成另一個(gè)字符串。

下面是一個(gè)使用 CONCAT() 函數(shù)將員工姓名和工號(hào)拼接在一起的示例：

SELECT CONCAT(name, '-', emp_id) as emp_info
FROM employee;
上述示例中，employee 表包含了員工的信息，其中 name 是員工的姓名，emp_id 是員工的工號(hào)。使用 CONCAT() 函數(shù)，可以將員工的姓名和工號(hào)拼接在一起，形成一個(gè)字符串。

Conditional functions：支持在 SQL 語(yǔ)句中添加條件判斷，根據(jù)不同的條件返回不同的結(jié)果。常用的條件函數(shù)包括：
CASE WHEN：根據(jù)指定的條件判斷返回不同的結(jié)果。

IF()：根據(jù)指定的條件判斷返回不同的結(jié)果。

COALESCE()：返回參數(shù)列表中第一個(gè)非空值。

下面是一個(gè)使用 CASE WHEN 函數(shù)根據(jù)員工的薪水等級(jí)計(jì)算每個(gè)部門(mén)的平均薪水的示例：

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 是員工所屬部門(mén)的 ID，salary 是員工的薪水。使用 CASE WHEN 函數(shù)，可以根據(jù)員工的薪水等級(jí)計(jì)算每個(gè)部門(mén)的平均薪水，并為不同薪水等級(jí)分配不同的標(biāo)簽。

Math functions：支持對(duì)數(shù)值型數(shù)據(jù)進(jìn)行數(shù)學(xué)運(yùn)算，如求絕對(duì)值、取整、開(kāi)方等。常用的數(shù)學(xué)函數(shù)包括：
ABS()：返回?cái)?shù)值的絕對(duì)值。

CEIL()：向上取整。

FLOOR()：向下取整。

下面是一個(gè)使用 ABS() 函數(shù)計(jì)算員工薪水增長(zhǎng)率的示例：

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() 函數(shù)，可以獲取上一次的薪水，并使用 ABS() 函數(shù)計(jì)算員工薪水的增長(zhǎng)率。

Date functions：支持對(duì)日期類(lèi)型的數(shù)據(jù)進(jìn)行操作，如日期格式化、日期計(jì)算等。常用的日期函數(shù)包括：
TO_DATE()：將字符串轉(zhuǎn)換為日期類(lèi)型。

DATE_FORMAT()：將日期類(lèi)型轉(zhuǎn)換為指定格式的字符串。

DATEDIFF()：計(jì)算兩個(gè)日期之間的天數(shù)差。

下面是一個(gè)使用 DATE_FORMAT() 函數(shù)將日期格式化為指定格式的示例：

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() 函數(shù)，可以將日期格式化為指定的格式，例如將日期格式化為 'yyyy-MM-dd' 的形式。

Collection functions：支持對(duì)集合類(lèi)型的數(shù)據(jù)進(jìn)行操作，如數(shù)組、Map 等。常用的集合函數(shù)包括：
ARRAY()：將多個(gè)元素組成一個(gè)數(shù)組。

MAP()：將多個(gè)鍵值對(duì)組成一個(gè) Map。

EXPLODE()：將數(shù)組或 Map 拆分成多行。

下面是一個(gè)使用 ARRAY() 函數(shù)將員工的技能列表轉(zhuǎn)換為數(shù)組的示例：

SELECT emp_id, skill_list, ARRAY(skill_list) as skills
FROM employee;
上述示例中，employee 表包含了員工的信息，其中 emp_id 是員工的 ID，skill_list 是員工掌握的技能列表。使用 ARRAY() 函數(shù)，可以將技能列表轉(zhuǎn)換為一個(gè)數(shù)組。

總結(jié)
以下是我對(duì)前面提到的一些 Hive 函數(shù)進(jìn)行的總結(jié)：

1.數(shù)學(xué)函數(shù)：Hive 支持包括 ABS、CEIL、FLOOR、ROUND、TRUNCATE、EXP、LN、LOG2、LOG10、POWER、SQRT、MOD 等常見(jiàn)的數(shù)學(xué)函數(shù)，用于進(jìn)行常見(jiàn)的數(shù)值計(jì)算和操作。

2.字符串函數(shù)：Hive 支持包括 CONCAT、SUBSTR、TRIM、UPPER、LOWER、REPLACE、REGEXP_EXTRACT、LENGTH 等常見(jiàn)的字符串函數(shù)，用于進(jìn)行字符串操作和處理。

3.日期函數(shù)：Hive 支持包括 YEAR、MONTH、DAY、HOUR、MINUTE、SECOND、DATE_FORMAT、FROM_UNIXTIME、TO_DATE、ADD_MONTHS 等常見(jiàn)的日期和時(shí)間函數(shù)，用于進(jìn)行日期和時(shí)間相關(guān)的計(jì)算和操作。

4.聚合函數(shù)：Hive 支持包括 SUM、AVG、MIN、MAX、COUNT 等常見(jiàn)的聚合函數(shù)，用于進(jìn)行數(shù)據(jù)聚合和統(tǒng)計(jì)計(jì)算。

5.分析函數(shù)：Hive 支持包括 ROW_NUMBER、RANK、DENSE_RANK、NTILE、LAG、LEAD 等常見(jiàn)的分析函數(shù)，用于進(jìn)行數(shù)據(jù)分析和統(tǒng)計(jì)計(jì)算。

6.窗口函數(shù)：Hive 支持使用 OVER 子句進(jìn)行窗口函數(shù)的計(jì)算，常見(jiàn)的窗口函數(shù)包括 ROW_NUMBER、RANK、DENSE_RANK、NTILE、LAG、LEAD 等，用于進(jìn)行復(fù)雜的數(shù)據(jù)分析和統(tǒng)計(jì)計(jì)算。

7.高級(jí)函數(shù)：Hive 還支持包括 ARRAY、MAP、STRUCT、EXPLODE、TRANSFORM、LATERAL VIEW 等高級(jí)函數(shù)，用于處理復(fù)雜的數(shù)據(jù)類(lèi)型和結(jié)構(gòu)，實(shí)現(xiàn)更加靈活和高效的數(shù)據(jù)分析和處理。

總的來(lái)說(shuō)，Hive 的函數(shù)庫(kù)非常豐富和強(qiáng)大，可以幫助用戶(hù)輕松地進(jìn)行各種數(shù)據(jù)操作和計(jì)算，為數(shù)據(jù)分析和處理提供了很大的便利和支持。



作者：薛秋艷


歡迎關(guān)注微信公眾號(hào) ：大數(shù)據(jù)球球