spark.sql中的Array[Struct]类型查询

背景

我们要查询类似以下结构的数据，但是要筛选出指定key值的数据。

解决方案

一般方法将数组covers字段进行explode()操作展开，生成一个包含covers中struct类型元素的临时表，然后再将临时表中的struct类型字段的各个字段展开。最终生成一个id，key，type的表。得到这个表时便可以指定key进行查询了。但是这个方法需要进行三步，非常麻烦。

使用LATERAL VIEW explode(covers) adTable AS cover可以一步到位。

LATERAL VIEW介绍

语法格式：

LATERAL VIEW [ OUTER ] generator_function ( expression [ , ... ] ) [ table_alias ] AS column_alias [ , ... ]

假设我们已经有如下表：

pageid	col1	col2
front_page	[1, 2, 3]	[“a”, “b”, “c”]
contact_page	[3, 4, 5]	[“d”, “e”, “f”]

• 单个Lateral View语句

select pageid, col1_new, col2 from pageAds lateral view explode(col1) adTable as col1_new;

+--------------+------------+---------------+
| pageid       | col1_new   | col2          |
+--------------+------------+---------------+
| front_page   | 1          | ["a","b","c"] |
| front_page   | 2          | ["a","b","c"] |
| front_page   | 3          | ["a","b","c"] |
| contact_page | 3          | ["d","e","f"] |
| contact_page | 4          | ["d","e","f"] |
| contact_page | 5          | ["d","e","f"] |
+--------------+------------+---------------+

• 拆分col1并执行聚合统计。

select col1_new, count(1) as count from pageAds lateral view explode(col1) adTable as col1_new group by col1_new;

+------------+------------+
| col1_new   | count      |
+------------+------------+
| 1          | 1          |
| 2          | 1          |
| 3          | 2          |
| 4          | 1          |
| 5          | 1          |
+------------+------------+

• 多个Lateral View语句

select pageid,mycol1, mycol2 from pageAds 
    lateral view explode(col1) myTable1 as mycol1 
    lateral view explode(col2) myTable2 as mycol2;
    
+--------------+----------+----------+
| pageid       | mycol1   | mycol2   |
+--------------+----------+----------+
| front_page   | 1        | a        |
| front_page   | 1        | b        |
| front_page   | 1        | c        |
| front_page   | 2        | a        |
| front_page   | 2        | b        |
| front_page   | 2        | c        |
| front_page   | 3        | a        |
| front_page   | 3        | b        |
| front_page   | 3        | c        |
| contact_page | 3        | d        |
| contact_page | 3        | e        |
| contact_page | 3        | f        |
| contact_page | 4        | d        |
| contact_page | 4        | e        |
| contact_page | 4        | f        |
| contact_page | 5        | d        |
| contact_page | 5        | e        |
| contact_page | 5        | f        |
+--------------+----------+----------+

• 对于struct类型可以使用 "."直接取数

select id,cover.key as k, cover.type as t from tablename lateral view explode(covers) myTable1 as cover where cover.key = 'special'