炸裂函数

1. Explode

将hive某列一行中复杂的 array 或 map 结构拆分成多行（只能输入array或map）;
通常，explode函数会与lateral view一起结合使用;

语法 :  explode(col)
           select explode(arraycol) as newcol from tablename;

解释 : 
          explode()：函数中的参数传入的是arrary/map数据类型的列名。
          newcol：是给转换成的列命名一个新的名字，用于代表转换之后的列名。
          tablename：原表名
 
示例 :
	explode(array) 使得结果中将array列表里的每个元素生成一行;
		select explode(array(1,2,3,4));
      +------+
      | col  |
      +------+
      | 1    |
      | 2    |
      | 3    |
      | 4    |
      +------+
     
  explode(map)使得结果中将map里的每一对元素作为一行，key为一列，value为一列;
		select explode(map('a',1,'b',2));
      +------+--------+
      | key  | value  |
      +------+--------+
      | a    | 1      |
      | b    | 2      |
      +------+--------+
        
      给  key 和 value 列取别名为 aa 和 bb
     	select explode(map('a',1,'b',2)) as (aa,bb);
      +-----+-----+
      | aa  | bb  |
      +-----+-----+
      | a   | 1   |
      | b   | 2   |
      +-----+-----+

2. posexplode()函数

对一列进行炸裂可以使用 explode()函数，但是如果想实现对两列都进行多行转换，
那么用explode()函数就不能实现了，可以用posexplode()函数，因为该函数可以将index和数据都取出来，
使用两次posexplode并令两次取到的index相等就行了。

示例 ;
  select posexplode(array('a','b','c','d'));
    +------+------+
    | pos  | val  |
    +------+------+
    | 0    | a    |
    | 1    | b    |
    | 2    | c    |
    | 3    | d    |
    +------+------+

Lateral View

官方文档 : https://cwiki.apache.org/confluence/display/Hive/LanguageManual+LateralView#LanguageManualLateralView-Description

		Lateral View配合 split, explode 等UDTF函数一起使用，它能够将一列数据拆成多行数据，
并且对拆分后结果进行聚合，即将多行结果组合成一个支持别名的虚拟表;
		Lateral View主要解决在select使用UDTF做查询的过程中查询只能包含单个UDTF，
不能包含其它字段以及多个UDTF的情况（不能添加额外的select列的问题）;
		一个 FROM 子句可以有多个 LATERAL VIEW 子句。后续的 LATERAL VIEWS 可以引用出现在 LATERAL VIEW 左侧的任何表格中的列。

语法 :
		格式一 : lateral view udtf(expression) tableAlias as columnAlias (,columnAlias)*

解释 :
		lateral view在UDTF前使用，表示连接UDTF所分裂的字段。
    UDTF(expression)：使用的UDTF函数，例如explode()。
    tableAlias：表示UDTF函数转换的虚拟表的名称。
    columnAlias：
          表示虚拟表的虚拟字段名称，如果分裂之后有一个列，则写一个即可；
          如果分裂之后有多个列，按照列的顺序在括号中声明所有虚拟列名，以逗号隔开;
          从 Hive 0.12.0 开始，可以省略列别名;
    
格式二 :  lateral view outer udtf(expression) tableAlias as columnAlias (,columnAlias)*
  
解释 : 
			outer : udtf 函数中指定的列值为 null 时,添加 outer 结果会显示包含 null 的这一行数据,否则会过滤掉此行数据;
    	为了避免 当udtf 没有得到任何结果时最终虚拟结果表里丢失原数据行的问题。具体来将，
	由于later view 的工作原理是将原表与 udtf 产生的虚拟表做 inner join 操作，所以如果 udtf 不产生任何结果时，
  那么对应原表的那一行也会在 inner join 操作后消失。outer关键字就是来解决这个问题的，加上这个关键字之后执行的就是 outer join 操作了，因此原表数据会被完全保留下来。


注：
    1）lateral view的位置是from后where条件前
    2）生成的虚拟表的表名不可省略
    3）from后可带多个lateral view
    3）如果要拆分的字段有null值，需要使用lateral view outer 替代，避免数据缺失
    
示例 :
	hive 建表 :
      create table  lateral_tal(
        km string,
        rq string
        );
	插入测试数据 :
			insert into lateral_tal values("hive	spark	flink	line	null	so	easy","date	todate	firstday	day	day	no	bug");
	
	单列炸裂-posexplode : t_1 虚表有索引列字段;
				select km, c_1 ,c_2   from lateral_tal lateral view posexplode(split(km,'\t')) t_1 as c_1,c_2;
+-------------------------------------+------+--------+
|                 km                 															 | c_1  |  c_2   |
+-------------------------------------+------+--------+
| hive  spark   flink   line    line    so      easy  | 0    | hive   |
| hive  spark   flink   line    line    so      easy  | 1    | spark  |
| hive  spark   flink   line    line    so      easy  | 2    | flink  |
| hive  spark   flink   line    line    so      easy  | 3    | line   |
| hive  spark   flink   line    line    so      easy  | 4    | line   |
| hive  spark   flink   line    line    so      easy  | 5    | so     |
| hive  spark   flink   line    line    so      easy  | 6    | easy   |
| hive  spark   flink   line    null    so      easy  | 0    | hive   |
| hive  spark   flink   line    null    so      easy  | 1    | spark  |
| hive  spark   flink   line    null    so      easy  | 2    | flink  |
| hive  spark   flink   line    null    so      easy  | 3    | line   |
| hive  spark   flink   line    null    so      easy  | 4    | null   |
| hive  spark   flink   line    null    so      easy  | 5    | so     |
| hive  spark   flink   line    null    so      easy  | 6    | easy   |
+-------------------------------------+------+--------+

	单列炸裂-explode :  t_1 虚表没有索引列字段;
				select km, c_1   from lateral_tal lateral view posexplode(split(km,'\t')) t_1 as c_1;
+-------------------------------------+--------+
|                 km                  															|  c_1   |
+-------------------------------------+--------+
| hive  spark   flink   line    line    so      easy  | hive   |
| hive  spark   flink   line    line    so      easy  | spark  |
| hive  spark   flink   line    line    so      easy  | flink  |
| hive  spark   flink   line    line    so      easy  | line   |
| hive  spark   flink   line    line    so      easy  | line   |
| hive  spark   flink   line    line    so      easy  | so     |
| hive  spark   flink   line    line    so      easy  | easy   |
| hive  spark   flink   line    null    so      easy  | hive   |
| hive  spark   flink   line    null    so      easy  | spark  |
| hive  spark   flink   line    null    so      easy  | flink  |
| hive  spark   flink   line    null    so      easy  | line   |
| hive  spark   flink   line    null    so      easy  | null   |
| hive  spark   flink   line    null    so      easy  | so     |
| hive  spark   flink   line    null    so      easy  | easy   |
+-------------------------------------+--------+

	双列炸裂 : 通过索引列(c_1和c_3)关联 t_1 和 t_2 两张虚标;
			select  km ,rq,c_1 ,c_2,c_3,c_4   from lateral_tal lateral view posexplode(split(km,'\t')) t_1 as c_1,c_2 
 lateral view  posexplode(split(rq,'\t')) t_2 as c_3,c_4 where c_1=c_3;
+-------------------------------------+--------------------------------------+------+--------+------+-----------+
|                 km                  															|                  rq                                                               | c_1  |  c_2   | c_3  |    c_4    |
+-------------------------------------+--------------------------------------+------+--------+------+-----------+
| hive  spark   flink   line    line    so      easy  | date    todate  firstday        day     day     no      bug  | 0    | hive   | 0    | date      |
| hive  spark   flink   line    line    so      easy  | date    todate  firstday        day     day     no      bug  | 1    | spark  | 1    | todate    |
| hive  spark   flink   line    line    so      easy  | date    todate  firstday        day     day     no      bug  | 2    | flink  | 2    | firstday  |
| hive  spark   flink   line    line    so      easy  | date    todate  firstday        day     day     no      bug  | 3    | line   | 3    | day       |
| hive  spark   flink   line    line    so      easy  | date    todate  firstday        day     day     no      bug  | 4    | line   | 4    | day       |
| hive  spark   flink   line    line    so      easy  | date    todate  firstday        day     day     no      bug  | 5    | so     | 5    | no        |
| hive  spark   flink   line    line    so      easy  | date    todate  firstday        day     day     no      bug  | 6    | easy   | 6    | bug       |
| hive  spark   flink   line    null    so      easy  | date    todate  firstday        day     day     no      bug  | 0    | hive   | 0    | date      |
| hive  spark   flink   line    null    so      easy  | date    todate  firstday        day     day     no      bug  | 1    | spark  | 1    | todate    |
| hive  spark   flink   line    null    so      easy  | date    todate  firstday        day     day     no      bug  | 2    | flink  | 2    | firstday  |
| hive  spark   flink   line    null    so      easy  | date    todate  firstday        day     day     no      bug  | 3    | line   | 3    | day       |
| hive  spark   flink   line    null    so      easy  | date    todate  firstday        day     day     no      bug  | 4    | null   | 4    | day       |
| hive  spark   flink   line    null    so      easy  | date    todate  firstday        day     day     no      bug  | 5    | so     | 5    | no        |
| hive  spark   flink   line    null    so      easy  | date    todate  firstday        day     day     no      bug  | 6    | easy   | 6    | bug       |
+-------------------------------------+--------------------------------------+------+--------+------+-----------+

实现 :

hive 建表 :
    create table  lateral_tal_2(
        column1 string,
        column2 string,
        column3 string,
        x1 string,
        x2 string,
        x3 string,
        x4 string
    );
插入数据 :
	insert into lateral_tal_2 values('A1','A2','A3',5,6,1,4);
	insert into lateral_tal_2 values('B1','B2','B3',5,6,1,4);

SQL 语句 :
		select column1,column2,column3,key,value from (
select  column1,column2,column3,map('x1',x1,'x2',x2,'x3',x3,'x4',x4) m from lateral_tal_2) a lateral view explode(m) t1 as key,value;

+----------+----------+----------+------+--------+
| column1  | column2  | column3  | key  | value  |
+----------+----------+----------+------+--------+
| A1       | A2       | A3       | x1   | 5      |
| A1       | A2       | A3       | x2   | 6      |
| A1       | A2       | A3       | x3   | 1      |
| A1       | A2       | A3       | x4   | 4      |
| B1       | B2       | B3       | x1   | 5      |
| B1       | B2       | B3       | x2   | 6      |
| B1       | B2       | B3       | x3   | 1      |
| B1       | B2       | B3       | x4   | 4      |
+----------+----------+----------+------+--------+

如何产生1-100的连续的数字？

结合space函数与split函数，posexplode函数，lateral view函数获得
方法一 :
      select
      id_start+pos as id
      from(
          select
          1 as id_start,
          100 as id_end
      ) m  lateral view posexplode(split(space(id_end-id_start), '')) t as pos, val;

方法二 :
      select
        row_number() over() as id
      from  
        (select split(space(99), ' ') as x) t
      lateral view
      explode(x) ex;

方法三 :
      select
        pos+1
      from  
        (select split(space(99), ' ') as x) t
      lateral view
      posexplode(x) ex;

如何产生开始日期到结束日期的连续的日期？

SELECT
	DATE_ADD(START_DATE, pos)
FROM
	(
	SELECT
		"2023-03-13" AS START_DATE,
		"2023-03-22" AS END_DATE
) s1 lateral VIEW posexplode(split(SPACE(DATEDIFF(END_DATE, START_DATE)),
	" ")) s2 AS pos,null_ele;

lateral view json_tuple(转成多列)

lateral view json_tuple 函数解析非结构化的json数据类型

hive 建表 :
    create table  lateral_tal_3(
    id int,
    col1 string,
    col2 string
    );

插入数据 :
		insert into lateral_tal_3 values(1234,'{"part1" : "61", "total" : "623", "part2" : "560", "part3" : "1", "part4" : "1"}','	{"to_part2" : "0", "to_part4" : "0", "to_up" : "0", "to_part3" : "0", "to_part34" : "0"}'),
(4567,'{"part1" : "451", "total" : "89928", "part2" : "88653", "part3" : "789", "part4" : "35"}','{"to_part2" : "54", "to_part4" : "6", "to_up" : "65", "to_part3" : "2", "to_part34" : "3"}'),
(7890,'{"part1" : "142", "total" : "351808", "part2" : "346778", "part3" : "4321", "part4" : "567"}','{"to_part2" : "76", "to_part4" : "23", "to_up" : "65", "to_part3" : "14", "to_part34" : "53"}');


SQL 语句 : json_tuple : 第一个参数是json 字符串所在的列名,其它参数是获取 json 字符串中的哪些key值;
使用lateral view json_tuple函数 从两列中分别选出part3,part4, to_part3,to_part4的key对应的数据值：
      SELECT
          id,part3,part4 ,part1,
          to_part3,
          to_part4,
          IF(part3=0,0.0, to_part3/part3) as ratio3,
          IF(part4=0,0.0, to_part4/part4) as ratio4
      FROM
      lateral_tal_3
      lateral VIEW json_tuple(col1, 'part3', 'part4', 'part1') json1 AS part3,part4,part1
      lateral VIEW json_tuple(col2, 'to_part3', 'to_part4') json2 AS to_part3,to_part4
      ;

lateral view json_tuple VS lateral view explode

key 固定如果key 多但是许多key对应的值不需要,使用 lateral view json_tuple,否则使用 lateral view explode;