hive常用的一些高階函式彙總

hive處理json字串的函式

Hive是基於Hadoop的資料倉儲工具，支援使用HiveQL（一種類SQL的查詢語言）查詢和處理資料。Hive提供了許多用於處理JSON資料的內建函式，以下是一些常用的JSON處理函式和示例：

1. 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
   

2. json_tuple(json_string, field1, field2, ...)：從JSON字串中提取指定的多個欄位值，返回一個元組。例如：

   SELECT json_tuple('{"name":"John","age":30}', 'name', 'age');
   -- 輸出：("John", "30")
   

3. 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
   

4. 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}
   

5. 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 中有許多高階函式可用於資料處理和分析。以下是一些常用的高階函式及其用途：

1. Window functions：支援在結果集上執行視窗聚合操作，如視窗函式、排序等。例如，row_number()、rank()、dense_rank() 等。

2. Aggregate functions：支援對資料集進行聚合計算，如求和、計數、平均值等。例如，SUM()、COUNT()、AVG() 等。

3. Date functions：支援對日期和時間型別的資料進行操作，如日期格式化、日期比較等。例如，to_date()、date_add()、date_sub() 等。

4. String functions：支援對字串型別的資料進行操作，如字串拼接、字串替換等。例如，concat()、substr()、replace() 等。

5. Math functions：支援對數值型別的資料進行操作，如絕對值、指數、對數等。例如，abs()、exp()、log() 等。

6. Array functions：支援對陣列型別的資料進行操作，如陣列展開、陣列聚合等。例如，explode()、collect_set()、、collect_array()、array_contains() 等。

7. Map functions：支援對 Map 型別的資料進行操作，如 Map 展開、Map 聚合等。例如，map()、explode()、aggregate() 等。

8. Struct functions：支援對結構體型別的資料進行操作，如結構體欄位訪問、結構體欄位拼接等。例如，get_json_object()、concat_ws() 等。

9. Conditional functions：支援對資料進行條件判斷，如 IF 語句、CASE 語句等。例如，IF()、CASE WHEN THEN ELSE END 等。

這些高階函式提供了更加靈活和強大的資料處理和分析能力，使得 Hive 在資料倉儲和大資料分析領域具有很高的應用價值。以下是對 Hive 中一些常用的高階函式的詳細解釋以及示例：

1. 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() 函式，可以為每個部門的員工根據薪水進行排序，併為每個員工分配一個排名。

1. 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 子句，可以對每個部門的員工薪水進行彙總計算。

1. 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 關鍵字，可以對員工的生日進行計算，得到下個月的生日日期。

1. String functions：支援對字串型別的資料進行操作，如字串拼接、字串擷取、字串替換等。常用的字串函式包括：

• CONCAT()：將多個字串拼接在一起。

• SUBSTR()：擷取字串的一部分。

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

下面是一個使用 CONCAT() 函式將員工姓名和工號拼接在一起的示例：

SELECT CONCAT(name, '-', emp_id) as emp_info
FROM employee;

上述示例中，employee 表包含了員工的資訊，其中 name 是員工的姓名，emp_id 是員工的工號。使用 CONCAT() 函式，可以將員工的姓名和工號拼接在一起，形成一個字串。

1. 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 函式，可以根據員工的薪水等級計算每個部門的平均薪水，併為不同薪水等級分配不同的標籤。

1. 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() 函式計算員工薪水的增長率。

1. 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' 的形式。

1. 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 的函式庫非常豐富和強大，可以幫助使用者輕鬆地進行各種資料操作和計算，為資料分析和處理提供了很大的便利和支援。