collect、explode、concat、lateral view_collect函数

一、collect_set 和 collect_list 函数

collect_set(col) 、 collect_list(col)函数只接受基本数据类型，它的主要作用是将某字段的值汇总，产生Array类型字段，注意该函数只能接受一列参数！我们一般都会配合group by 函数，直接汇总分组数据！

 

collect函数也可以实现一个功能：获取group by后面没有的字段！

 

collect_set 和 collect_list 函数的区别很直观，set会进行去重，而list不会！

 

我们可以通过下标去获取数组中的数据取值时如果下标超出范围，会返回NULL

二、concat / concat_ws 函数

concat

 

select concat('a','b')

 

直接用concat函数，如果其中某一列为NULL，那么合并结果也为NULL

concat_ws 

 

select concat_ws(',','a','b')
select concat_ws(',',collect_list(cookieid))

 

第一个参数为指定合并分隔符

NULL字段合并对于concat_ws来说无影响！

三、Explode

 

explode()接收一个数组(或一个map)作为输入并将数组(map)的元素作为单独的行输出。

对数组字段进行explode

 

select explode(id) e from test3  //ID是数组

 

对map字段进行explode

select explode(content) as (mykey,myvalue) from test3 
//map中包含 key、value需要指定两个别名，而且别名要用上面的这种括号括起来的形式，不然会报错

 

对map字段进行explode

 

select 
get_json_object(explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{')),'$.monthSales') as  sale_info 
from explode_lateral_view;
这条sql会报错，因为explode不能包含在其他的函数里面


select 
         get_json_object(concat('{',sale_info_1,'}'),'$.source') as source,
     get_json_object(concat('{',sale_info_1,'}'),'$.monthSales') as monthSales,
     get_json_object(concat('{',sale_info_1,'}'),'$.userCount') as monthSales,
     get_json_object(concat('{',sale_info_1,'}'),'$.score') as monthSales
  from explode_lateral_view 
LATERAL VIEW explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{'))sale_info as sale_info_1;

 

explode函数作为UDTF类型函数的基本特性

1、直接select中使用时

不可以添加其他字段使用

 

select a, explode_map(properties) as (col1,col2) from src

错误信息如下：

Error while compiling statement: FAILED: SemanticException [Error 10081]:UDTF's are not supported outside the SELECT clause, nor nested in expressions

 

不可以嵌套调用

 

select explode_map(explode_map(properties)) from src

 

不可以和group by/cluster by/distribute by/sort by一起使用

 

select explode_map(properties) as (col1,col2) from src group by col1, col2

2、和lateral view一起使用

 

select src.id, mytable.col1, mytable.col2 
from src lateral view explode_map(properties) mytable as col1, col2;

四、lateral view 详解

 

Lateral view与用户定义的表生成函数(如explode())一起使用。正如在内置的表生成函数中提到的，UDTF为每个输入行生成零个或多个输出行。Lateral view首先将UDTF应用于基表的每一行，然后将产生的输出行连接到输入行，形成一个具有提供的表别名的虚拟表。

 

注：Lateral View通常和UDTF一起出现，为了解决UDTF不允许在select中包含其他字段的问题

 

 

多个Lateral View 连用  可以实现类似笛卡尔乘积

SELECT * FROM exampleTable
LATERAL VIEW explode(col1) myTable1 AS myCol1
LATERAL VIEW explode(myCol1) myTable2 AS myCol2;

Outer Lateral Views

即使横向视图通常不会生成一行，用户也可以指定可选的OUTER关键字来生成行。当使用的UDTF不生成任何行时，就会发生这种情况。在这种情况下，源行永远不会出现在结果中。可以使用OUTER来防止这种情况，并在来自UDTF的列中使用空值生成行。

 

Outer关键字可以把不输出的UDTF的空结果，输出成NULL，防止丢失数据。