Hive专项

Hive专项：数组函数

在Hive中，Array是一种用于存储多个相同类型元素的复杂数据结构。
Hive中的Array是一种非常实用的复杂数据类型，它能够有效地存储和处理多个相同类型的数据元素。通过使用Array及相关的HiveQL操作函数，人们可以简化数据处理任务，提高查询效率，并在数据分析中实现更多样化的操作。同时，结合其他复杂类型如Map和Struct，Array能提供多层次、多维度的数据组织方式，满足不同业务场景的需求。
灵活性：数组允许存储和处理多个值，适合复杂的数据结构。
高效查询：减少了关联操作，通过数组直接访问和处理数据，提高查询性能。
简化数据模型：使得数据存储更为简洁，适合多值属性和记录。

如何构造array


xxxxxxxxxx
3
1
SELECT  array(1, 3, 5) c1,
2
        split('a,c,b', ',') c2,
3
        split('a b d', ' ') c3;

array(1, 3, 5) c1：这一列的值是一个数组，包含三个元素1, 3, 5。
split('a,c,b', ',') c2：这一列的值也是一个数组，通过对字符串'a,c,b'进行逗号分割得到，包含三个元素'a', 'c', 'b'。
split('a b d', ' ') c3：这一列的值同样是一个数组，通过对字符串'a b d'进行空格分割得到，包含三个元素'a', 'b', 'd'。

获取数组元素


xxxxxxxxxx
2
1
SELECT  array(1, 3, 5) [0] c1,
2
        array('a', 'b', 'c') [3] c2;

array(1, 3, 5) [0] c1：这部分代码创建了一个数组，数组的元素是1，3，5，然后通过[0]取出数组的第一个元素（在这里是1），并将这个元素作为新表的c1列的值。
array('a', 'b', 'c') [3] c2：这部分代码创建了一个数组，数组的元素是'a'，'b'，'c'，然后通过[3]试图取出数组的第四个元素。但是，因为数组的索引是从0开始的，所以这个数组只有三个元素，没有第四个元素，所以这部分代码会返回NULL，即c2列的值为NULL。

返回数组中的最大（小）值


xxxxxxxxxx
2
1
SELECT  array_max(array(1, 3, 5)) c1,
2
        array_max(array('a', 'b', 'c')) c2;

array_max(array(1, 3, 5)) c1：创建了一个包含三个元素（1，3，5）的数组，并使用array_max函数找出这个数组中的最大值，即5。然后将这个最大值命名为c1。
array_max(array('a', 'b', 'c')) c2：创建了一个包含三个元素（'a'，'b'，'c'）的数组，并使用array_max函数找出这个数组中的最大值，即'c'。然后将这个最大值命名为c2。


xxxxxxxxxx
2
1
SELECT  array_min(array(1, 3, 5)) c1,
2
        array_min(array('a', 'b', 'c')) c2;

将数组中的元素连接成一个字符串


xxxxxxxxxx
3
1
SELECT  array_join(array('1', '3', '5'), '_') c1,
2
        concat_ws('#', array('a', 'b', 'c')) c2,
3
        concat_ws(' ', 'first name', 'last_name') c3;

array_join(array('1', '3', '5'), '_') c1：array_join函数将数组中的元素用指定的分隔符连接起来。这里的数组是['1', '3', '5']，分隔符是'_'，所以结果是'1_3_5'，并将这个结果命名为c1。
concat_ws('#', array('a', 'b', 'c')) c2：concat_ws函数也是将多个字符串用指定的分隔符连接起来。这里的字符串是'a', 'b', 'c'，分隔符是'#'，所以结果是'a#b#c'，并将这个结果命名为c2。
concat_ws(' ', 'first name', 'last_name') c3：这里的concat_ws函数将'first name'和'last_name'两个字符串用空格' '连接起来，结果是'first name last_name'，并将这个结果命名为c3。

`collect_list/collect set`：分组中某列转为数组返回


xxxxxxxxxx
15
1
WITH cte AS (
2
  SELECT 'A' AS user_id, '1001' AS date UNION ALL
3
  SELECT 'B', '1004' UNION ALL
4
  SELECT 'A', '1002' UNION ALL
5
  SELECT 'B', '1002' UNION ALL
6
  SELECT 'A', '1003' UNION ALL
7
  SELECT 'B', '1043'
8
)
9
SELECT  user_id,
10
        concat_ws(',', sort_array(collect_list(cast(date AS STRING)))) AS date_concat
11
FROM    cte
12
GROUP BY
13
        user_id;
14
        
15
-- set则是去重

concat_ws(',', sort_array(collect_list(cast(date AS STRING)))) AS date_concat：这部分是对date字段的处理。首先，cast(date AS STRING)将date字段转换为字符串类型。然后，collect_list()函数将同一个user_id下的所有date字段值聚合成一个列表。sort_array()函数将这个列表按照日期的顺序进行排序。最后，concat_ws(',', ...)函数将排序后的日期列表用逗号,连接成一个字符串。

数组包含指定的值`array_contains(array, value)`


xxxxxxxxxx
8
1
SELECT  array_contains(array(1, 2, 3), 2) c1,
2
        array_contains(array('a', 'b', 'c'), 'd') c2;
3

4
+-------+--------+--+
5
|  c1   |   c2   |
6
+-------+--------+--+
7
| true  | false  |
8
+-------+--------+--+

返回value在数组中的位置 `array_position(array, value)`


xxxxxxxxxx
8
1
SELECT  array_position(array(1, 2, 3), 4) c1,
2
        array_position(array('a', 'b', 'c'), 'a') c2;
3
        
4
+-----+-----+--+
5
| c1  | c2  |
6
+-----+-----+--+
7
| 0   | 1   |
8
+-----+-----+--+

array_position(array(1, 2, 3), 4) c1：这个函数试图在数组[1, 2, 3]中找到值4，但是数组中并没有4，所以返回值为0，然后将这个结果命名为c1。
array_position(array('a', 'b', 'c'), 'a') c2：这个函数在数组['a', 'b', 'c']中找到了值'a'，'a'在数组中的位置是1，所以返回值为1，然后将这个结果命名为c2。

去除数组中的所有value元素 `array_remove(array, value)`


xxxxxxxxxx
8
1
SELECT  array_remove(array(1, 2, 3, 2), 2) c1,
2
        array_remove(array('a', 'b', 'c'), 'd') c2;
3

4
+--------+----------------+--+
5
|   c1   |       c2       |
6
+--------+----------------+--+
7
| [1,3]  | ["a","b","c"]  |
8
+--------+----------------+--+

array_remove(array(1, 2, 3, 2), 2) c1：这个子句创建了一个数组[1, 2, 3, 2]，然后使用array_remove函数删除所有值为2的元素。所以，返回的结果将是[1, 3]，并将这个结果命名为c1。
array_remove(array('a', 'b', 'c'), 'd') c2：这个子句创建了一个数组['a', 'b', 'c']，然后试图删除所有值为'd'的元素。但是，因为原数组中没有'd'，所以返回的结果仍然是['a', 'b', 'c']，并将这个结果命名为c2。

在数组array1中去除掉所有数组array2中的元素


xxxxxxxxxx
8
1
SELECT  array_except(array(1, 2, 3, 2), array(2, 3)) c1,
2
        array_except(array('a', 'b', 'c'), array('d')) c2;
3
        
4
+------+----------------+--+
5
|  c1  |       c2       |
6
+------+----------------+--+
7
| [1]  | ["a","b","c"]  |
8
+------+----------------+--+

array_except(array(1, 2, 3, 2), array(2, 3)) c1：这个子句返回的是第一个数组array(1, 2, 3, 2)中存在但第二个数组array(2, 3)中不存在的元素，结果是一个新的数组，这个新的数组被命名为c1。
array_except(array('a', 'b', 'c'), array('d')) c2：这个子句返回的是第一个数组array('a', 'b', 'c')中存在但第二个数组array('d')中不存在的元素，结果是一个新的数组，这个新的数组被命名为c2。

数组array1和数组array2的交集元素


xxxxxxxxxx
8
1
SELECT  array_intersect(array(1, 2, 3, 2), array(2, 3)) c1,
2
        array_intersect(array('a', 'b', 'c'), array('d')) c2;
3
        
4
+--------+-----+--+
5
|   c1   | c2  |
6
+--------+-----+--+
7
| [2,3]  | []  |
8
+--------+-----+--+

array_intersect(array(1, 2, 3, 2), array(2, 3)) c1：这一部分是在找数组array(1, 2, 3, 2)和数组array(2, 3)的交集，结果将被命名为c1。交集的结果应该是array(2, 3)。
array_intersect(array('a', 'b', 'c'), array('d')) c2：这一部分是在找数组array('a', 'b', 'c')和数组array('d')的交集，结果将被命名为c2。由于这两个数组没有共同的元素，所以交集的结果应该是一个空数组。

判断数组为空 `size(column) = 0`


xxxxxxxxxx
37
1
WITH user_data AS (
2
  SELECT
3
    1 AS user_id,
4
    'Alice' AS name,
5
    array('reading', 'traveling', 'cooking') AS interests
6
  UNION ALL
7
  SELECT
8
    2 AS user_id,
9
    'Bob' AS name,
10
    array('sports', 'movies') AS interests
11
  UNION ALL
12
  SELECT
13
    3 AS user_id,
14
    'Charlie' AS name,
15
    array('gaming', 'music', 'art') AS interests
16
  UNION ALL
17
  SELECT
18
    4 AS user_id,
19
    'Diana' AS name,
20
    array('writing', 'dancing') AS interests
21
  UNION ALL
22
  SELECT
23
    5 AS user_id,
24
    'Eve' AS name,
25
    array() AS interests
26
)
27

28
SELECT
29
  user_id,
30
  name,
31
  interests,
32
  CASE
33
    WHEN size(interests) = 0 THEN 'No interests'
34
    ELSE 'Has interests'
35
  END AS interest_status
36
FROM
37
  user_data;

user_id	name	interests	interest_status
5	Eve	[]	No interests
4	Diana	["writing","dancing"]	Has interests
3	Charlie	["gaming","music","art"]	Has interests
1	Alice	["reading","traveling","cooking"]	Has interests
2	Bob	["sports","movies"]	Has interests

行转列

`lateral view explode`


xxxxxxxxxx
36
1
WITH user_data AS (
2
  SELECT
3
    1 AS user_id,
4
    'Alice' AS name,
5
    array('reading', 'traveling', 'cooking') AS interests
6
  UNION ALL
7
  SELECT
8
    2 AS user_id,
9
    'Bob' AS name,
10
    array('sports', 'movies') AS interests
11
  UNION ALL
12
  SELECT
13
    3 AS user_id,
14
    'Charlie' AS name,
15
    array('gaming', 'music', 'art') AS interests
16
  UNION ALL
17
  SELECT
18
    4 AS user_id,
19
    'Diana' AS name,
20
    array('writing', 'dancing') AS interests
21
  UNION ALL
22
  SELECT
23
    5 AS user_id,
24
    'Eve' AS name,
25
    array() AS interests
26
)
27

28

29
-- 使用 explode 展开 interests 数组
30
SELECT
31
  user_id,
32
  name,
33
  interest
34
FROM
35
  user_data
36
LATERAL VIEW explode(interests) exploded_table AS interest;

user_data:

user_id	name	interests
5	Eve	[]
4	Diana	["writing","dancing"]
3	Charlie	["gaming","music","art"]
1	Alice	["reading","traveling","cooking"]
2	Bob	["sports","movies"]

转换后

user_id	name	interest
3	Charlie	gaming
3	Charlie	music
3	Charlie	art
4	Diana	writing
4	Diana	dancing
1	Alice	reading
1	Alice	traveling
1	Alice	cooking
2	Bob	sports
2	Bob	movies

`LATERAL VIEW posexplode`

posexplode 是 Hive 中的一个函数，用于将数组或 map 展开为多行，同时保留数组的索引位置。


xxxxxxxxxx
37
1
-- 使用 CTE 构造示例数据
2
WITH product_data AS (
3
  SELECT
4
    101 AS product_id,
5
    'Laptop' AS product_name,
6
    array('16GB RAM', '512GB SSD', 'Intel i7') AS features
7
  UNION ALL
8
  SELECT
9
    102 AS product_id,
10
    'Smartphone' AS product_name,
11
    array('128GB Storage', '6GB RAM', 'Dual Camera') AS features
12
  UNION ALL
13
  SELECT
14
    103 AS product_id,
15
    'Tablet' AS product_name,
16
    array('64GB Storage', '4GB RAM', '8-inch Display') AS features
17
  UNION ALL
18
  SELECT
19
    104 AS product_id,
20
    'Smartwatch' AS product_name,
21
    array('Heart Rate Monitor', 'GPS', 'Water Resistant') AS features
22
  UNION ALL
23
  SELECT
24
    105 AS product_id,
25
    'Headphones' AS product_name,
26
    array('Noise Cancelling', 'Bluetooth 5.0') AS features
27
)
28

29
-- 使用 posexplode 展开 features 数组并保留索引位置
30
SELECT
31
  product_id,
32
  product_name,
33
  pos AS feature_position,
34
  feature
35
FROM
36
  product_data
37
LATERAL VIEW posexplode(features) exploded_table AS pos, feature;

使用 LATERAL VIEW posexplode(features) exploded_table AS pos, feature 将 features 数组展开为多行，同时保留每个特性的索引位置，并将每个特性的索引位置命名为 pos，特性命名为 feature。


xxxxxxxxxx
18
1
+------------+--------------+----------------+----------------------+
2
| product_id | product_name | feature_position | feature             |
3
+------------+--------------+----------------+----------------------+
4
| 101        | Laptop       | 0              | 16GB RAM             |
5
| 101        | Laptop       | 1              | 512GB SSD            |
6
| 101        | Laptop       | 2              | Intel i7             |
7
| 102        | Smartphone   | 0              | 128GB Storage        |
8
| 102        | Smartphone   | 1              | 6GB RAM              |
9
| 102        | Smartphone   | 2              | Dual Camera          |
10
| 103        | Tablet       | 0              | 64GB Storage         |
11
| 103        | Tablet       | 1              | 4GB RAM              |
12
| 103        | Tablet       | 2              | 8-inch Display       |
13
| 104        | Smartwatch   | 0              | Heart Rate Monitor   |
14
| 104        | Smartwatch   | 1              | GPS                  |
15
| 104        | Smartwatch   | 2              | Water Resistant      |
16
| 105        | Headphones   | 0              | Noise Cancelling     |
17
| 105        | Headphones   | 1              | Bluetooth 5.0        |
18
+------------+--------------+----------------+----------------------+

进阶用法


xxxxxxxxxx
25
1
-- 创建示例数据
2
WITH A AS (
3
    SELECT  1 AS uid,
4
            'login,view,logout' AS action,
5
            '2024-08-01 10:00,2024-08-01 10:30,2024-08-01 11:00' AS TIMESTAMP
6
    UNION ALL
7
    SELECT  2 AS uid,
8
            'login,purchase,logout' AS action,
9
            '2024-08-02 09:00,2024-08-02 09:15,2024-08-02 09:45' AS TIMESTAMP
10
    UNION ALL
11
    SELECT  3 AS uid,
12
            'view,comment,like,logout' AS action,
13
            '2024-08-03 08:00,2024-08-03 08:10,2024-08-03 08:20,2024-08-03 08:30' AS TIMESTAMP
14
)
15
-- 使用 posexplode 同时拆解 action 和 timestamp
16
SELECT 
17
  A.uid, 
18
  s2.action, 
19
  s3.timestamp
20
FROM 
21
  A
22
LATERAL VIEW posexplode(split(A.action, ',')) s2 AS idx2, action
23
LATERAL VIEW posexplode(split(A.timestamp, ',')) s3 AS idx3, timestamp
24
WHERE 
25
  s2.idx2 = s3.idx3;

假设我们的表 A 如下：

uid	action	timestamp
1	login,view,logout	2024-08-01 10:00,2024-08-01 10:30,2024-08-01 11:00
2	login,purchase,logout	2024-08-02 09:00,2024-08-02 09:15,2024-08-02 09:45
3	view,comment,like,logout	2024-08-03 08:00,2024-08-03 08:10,2024-08-03 08:20,2024-08-03 08:30

需要转换为


xxxxxxxxxx
14
1
+-----+----------+---------------------+
2
| uid | action   | timestamp           |
3
+-----+----------+---------------------+
4
| 1   | login    | 2024-08-01 10:00    |
5
| 1   | view     | 2024-08-01 10:30    |
6
| 1   | logout   | 2024-08-01 11:00    |
7
| 2   | login    | 2024-08-02 09:00    |
8
| 2   | purchase | 2024-08-02 09:15    |
9
| 2   | logout   | 2024-08-02 09:45    |
10
| 3   | view     | 2024-08-03 08:00    |
11
| 3   | comment  | 2024-08-03 08:10    |
12
| 3   | like     | 2024-08-03 08:20    |
13
| 3   | logout   | 2024-08-03 08:30    |
14
+-----+----------+---------------------+

将用户的行为和时间戳从字符串形式转换为单独的行，每一行对应一个用户的一个行为和对应的时间戳。

首先，使用WITH语句创建了一个名为A的临时表，包含三个字段：uid（用户ID），action（用户行为，以逗号分隔的字符串），timestamp（行为发生的时间，以逗号分隔的字符串）。
然后，使用posexplode函数和split函数将action和timestamp字段中的字符串拆分为多行。posexplode函数会返回一个新的行集，每一行包含一个索引和一个值，索引表示原始字符串中的位置，值表示拆分后的单个元素。split函数用于将字符串按照指定的分隔符（这里是逗号）拆分为多个部分。
LATERAL VIEW是Hive中的一个关键字，用于与posexplode函数一起使用，生成一个虚拟表，这个虚拟表包含了拆分后的索引和值。
FROM A LATERAL VIEW posexplode(split(A.action, ',')) s2 AS idx2, action LATERAL VIEW posexplode(split(A.timestamp, ',')) s3 AS idx3, TIMESTAMP这一行的意思是，对A表中的action和timestamp字段进行拆分，生成两个虚拟表s2和s3，s2表包含了action字段拆分后的索引和值，s3表包含了timestamp字段拆分后的索引和值。
WHERE s2.idx2=s3.idx3这一行的意思是，只选择那些action和timestamp字段拆分后索引相同的行，也就是说，只选择那些行为和时间戳一一对应的行。

最后，这段SQL将返回一个新的表，每一行包含一个用户ID，一个行为和一个时间戳。

Hive专项：CONCAT相关函数

CONCAT

语法: CONCAT(string A, string B…)
返回值: string
说明：返回输入字符串连接后的结果，支持任意个输入字符串

比如：select concat('鲸鲸','说','数据')

return 鲸鲸说数据

CONCAT_WS

含义：CONCAT With Separator
语法: CONCAT_WS(separator, str1, str2,...)
返回值: string
说明：一般我们会把一列的多行合并为一个array，然后再用这个函数合并成一个长字符串（对某一组内的字符串进行聚合）

比如：select concat_ws(',',array('鲸鲸','说数据'))

return 鲸鲸,说数据

CONCAT_MAP

MAP是啥？
在 Hive 中，MAP 是一种数据结构，用于存储键值对（key-value pairs），类似于 Python 中的字典或 JSON 对象。在 MAP 结构中，每个键都唯一，并且与一个特定的值相关联。
语法：MAP(key1, value1, key2, value2, ...)
例子：SELECT MAP('name', 'Alice', 'age', '30');
使用 MAP 的场景
存储属性信息: 例如，一个 MAP 可以存储一位用户的多个属性信息（如名字、年龄、兴趣爱好等）。
合并信息: 当你有多个信息来源，并且每个来源都有不同的键值对时，可以使用 MAP 来合并这些信息，并保存在一个字段中。

含义: CONCAT Maps (合并多个 MAP 数据结构)
语法: CONCAT_MAP(map1, map2, ...)
返回值: MAP
说明: CONCAT_MAP 函数用于将多个 MAP 数据结构合并为一个 MAP。如果多个 MAP 中包含相同的键，则后面的键值对会覆盖前面的键值对。通常用于将不同来源或类型的信息合并为一个完整的结构。

思考


xxxxxxxxxx
1
1
select concat("abc",null,"123");
2

3
select concat_ws(',',array('鲸鲸','说数据',null)) 
4

5
SELECT CONCAT_MAP(MAP('name', '鲸鲸', 'age', '25'), 
6
                  MAP('hobby', '说数据', 'age', '26'));

答案

SELECT CONCAT("abc", NULL, "123");

查询解释:

CONCAT 函数将输入的字符串拼接在一起。

如果任何输入为 NULL，则 CONCAT 函数将结果视为 NULL。

答案是NULL

SELECT CONCAT_WS(',', array('鲸鲸', '说数据', NULL));

查询解释:

CONCAT_WS 是 CONCAT With Separator 的简写，用于将数组中的字符串拼接在一起，并使用指定的分隔符。
NULL 值在 CONCAT_WS 中会被忽略，不会包含在结果中。

答案是鲸鲸,说数据

SELECT CONCAT_MAP(MAP('name', '鲸鲸', 'age', '25'), MAP('hobby', '说数据', 'age', '26'));

查询解释:

CONCAT_MAP 函数用于将两个 MAP 合并成一个。
如果两个 MAP 中有相同的键（如 age），后面的 MAP 会覆盖前面的值。

答案是{"name": "鲸鲸", "age": "26", "hobby": "说数据"}

MAP 中的 age 键值对被第二个 MAP 中的 age 覆盖，最终的 MAP 包含了所有键值对，age 的值为 26。

Hive专项

Hive专项：数组函数

如何构造array

获取数组元素

返回数组中的最大（小）值

将数组中的元素连接成一个字符串

collect_list/collect set：分组中某列转为数组返回

数组包含指定的值array_contains(array, value)

返回value在数组中的位置 array_position(array, value)

去除数组中的所有value元素 array_remove(array, value)

在数组array1中去除掉所有数组array2中的元素

数组array1和数组array2的交集元素

判断数组为空 size(column) = 0

行转列

lateral view explode

LATERAL VIEW posexplode

Hive专项：CONCAT相关函数

CONCAT

CONCAT_WS

CONCAT_MAP

思考

答案

`collect_list/collect set`：分组中某列转为数组返回

数组包含指定的值`array_contains(array, value)`

返回value在数组中的位置 `array_position(array, value)`

去除数组中的所有value元素 `array_remove(array, value)`

判断数组为空 `size(column) = 0`

`lateral view explode`

`LATERAL VIEW posexplode`