经典sql题（十二）UDTF之Explode炸裂函数

1. EXPLODE: UDTF 函数

1.1 功能说明

EXPLODE 函数 是Hive 中的一种用户定义的表函数（UDTF），用于将数组或映射结构中的复杂的数据结构每个元素拆分为单独的行。这在处理复杂数据时非常有用，尤其是在需要将嵌套数据“打散”以便更好地分析时。

1.2 使用示例

假设我们有一个存储用户偏好的 Hive 表，结构如下：

user_id	preferences
1	[“music”, “sports”]
2	[“reading”]
3	[“coding”, “travel”]

我们希望将 preferences 列中的数组拆分为多行。

SELECT user_id, preference
FROM users
LATERAL VIEW explode(preferences) exploded_table AS preference;

1.3 输出结果

user_id	preference
1	music
1	sports
2	reading
3	coding
3	travel

2. LATERAL VIEW

2.1 功能说明

LATERAL VIEW 允许我们将 UDTF 的输出与原始表中每一行的数据连接在一起，这样就可以在查询中灵活地处理嵌套数据。

2.2 使用示例

结合 EXPLODE 和 LATERAL VIEW 的例子，假设我们有一个包含订单信息的表：

order_id	items
1001	[“itemA”, “itemB”]
1002	[“itemC”]
1003	[“itemA”, “itemD”, “itemE”]

我们想要拆分 items 列。

SELECT order_id, item
FROM orders
LATERAL VIEW explode(items) exploded_items AS item;

2.3 输出结果

order_id	item
1001	itemA
1001	itemB
1002	itemC
1003	itemA
1003	itemD
1003	itemE

3. SPLIT 函数

3.1 功能说明

SPLIT 函数用于将字符串分割成字符串数组。它常与 EXPLODE 一起使用，以便对字符串数据进行更细粒度的分析。

3.2 使用示例

假设我们有一个存储用户评论的表：

user_id	comment
1	“great product, love it”
2	“not bad, could be better”
3	“will buy again, highly recommend”

我们希望将评论中的每个单词拆分出来。

SELECT user_id, word
FROM comments
LATERAL VIEW explode(split(comment, ' ')) exploded_words AS word;

3.3 输出结果

user_id	word
1	great
1	product,
1	love
2	not
2	bad,
2	could
2	be
2	better
3	will
3	buy
3	again,
3	highly
3	recommend

4. 结合使用 EXPLODE 和 LATERAL VIEW

4.1 使用场景

将 EXPLODE 和 LATERAL VIEW 结合使用，可以轻松实现词频统计等任务。

4.2 示例

假设我们要统计每个单词的出现频率：

SELECT word, COUNT(*) AS frequency
FROM comments
LATERAL VIEW explode(split(comment, ' ')) exploded_words AS word
GROUP BY word;

4.3 输出结果

word	frequency
great	1
product,	1
love	1
not	1
bad,	1
could	1
be	1
better	1
will	1
buy	1
again,	1
highly	1
recommend	1

理解这几个概念的关系

1. SPLIT 函数:

   • 用于将字符串按照指定的分隔符拆分成数组。
   • 适用于文本数据的分割，比如将一句话拆分成单词。

2. LATERAL VIEW 和 EXPLODE:

   • LATERAL VIEW 是用于将 UDTF (用户定义的表函数) 的结果与原始表数据结合使用的。EXPLODE 是一种 UDTF，它可以将数组中的每个元素展开为多行。
   • 当我们有一个数组时，可以直接使用 EXPLODE 来将它拆分为多行。

使用区别

• 单纯数组: 如果数据已经是数组（例如，JSON 数组），只需使用 EXPLODE 和 LATERAL VIEW。
• 字符串: 如果数据是字符串（例如，评论文本），需要使用 SPLIT 先将其拆分为数组，然后使用 EXPLODE。

Hive 提供了一种强大的方式来处理和分析复杂的嵌套数据结构，在实际应用中，合理地结合使用 EXPLODE、SPLIT 和 LATERAL VIEW，可以帮助我们更高效地进行数据清洗、转换和分析，为决策提供有力支持，掌握这些函数和技术，将极大地提高我们在数据分析中的灵活性和效率。