explode

分類：UDTF
功能：函數可以將一個array或者map展開
1.explode(array)：將array列表里的每個元素生成一行
2.explode(map)：每一對元素作為一行，key為一列，value為一列

demo

• explode(map)

//todo explode(map)

select explode(map('A',10,'B',20,'C',30)) as (key,value);

• lateral view explode(map)

select
    tf.*
from
(select 0) t
lateral view explode(map('A',10,'B',20,'C',30)) tf as key,value;

• explode(array)

//todo explode(array)
SELECT explode(split(name,"")) FROM hobbies

---

lateral view

分類：視圖
功能：配合UDTF來使用,把某一行數據拆分成多行數據
很多的UDTF不能將結果與源表進行關聯，使用lateral view ，可以將UDTF拆分的單個字段數據與原始表數據關聯上

demo

• lateral view explode

• 原表
  

• 轉換

SELECT name,ids
from hobbies
LATERAL VIEW explode(id_list) tmp_view AS ids;

• 統計id出現次數

SELECT ids,count(1) as ids_cnt
from hobbies
LATERAL VIEW explode(id_list) tmp_view AS ids
GROUP BY ids;

• lateral view parse_url_tuple

• 原表

insert into urls (id, url) values
    ("1","http://facebook.com/path/p1.php?query=1"),
    ("2","http://www.baidu.com/news/index.jsp?uuid=frank"),
    ("3","http://www.jd.com/index?source=baidu");

SELECT * from urls;

• 轉換

SELECT
    a.*,
    b.host,
    b.path
FROM urls a
LATERAL VIEW parse_url_tuple(url, 'HOST',"PATH") b as host,path;

---

列轉行

• 原表

SELECT
    name,
    concat_ws(',',collect_set(course)) AS course,
    concat_ws(',',collect_set(cast(score AS string))) AS score
FROM posexplode
GROUP BY name;

單列轉多行

• LATERAL VIEW explode

// todo 單列轉多行
WITH t1 AS (
    SELECT
        name,
        concat_ws(',',collect_set(course)) AS course,
        concat_ws(',',collect_set(cast(score AS string))) AS score
    FROM posexplode
    GROUP BY name
 )
SELECT
    name,pose_course,SCORE
FROM t1
LATERAL VIEW explode(split(t1.COURSE,',')) tmp_view AS pose_course

多列轉多行

• LATERAL VIEW posexplode

// todo 多列轉多行
WITH t1 AS (
    SELECT
        name,
        concat_ws(',',collect_set(course)) AS course,
        concat_ws(',',collect_set(cast(score AS string))) AS score
    FROM posexplode
    GROUP BY name
 )
SELECT
    name,pos_course,a,pos_score,b
FROM t1
LATERAL VIEW posexplode(split(t1.COURSE,',')) cr AS a, pos_course
LATERAL VIEW posexplode(split(t1.SCORE,',')) cr AS b, pos_score
WHERE a=b

• split

select
    concat_ws(',',
        concat(c1,':c:',c2),
        concat(c1,':d:',c3),
        concat(c1,':e:',c4)
    ) as value
from  col2row;

SELECT
     split(value_list,":")[0] c1,
     split(value_list,":")[1] c2,
     split(value_list,":")[2] c3
FROM (
    select
        concat_ws(',',
            concat(c1,':c:',c2),
            concat(c1,':d:',c3),
            concat(c1,':e:',c4)
        ) as value
    from  col2row
)t1
LATERAL VIEW explode(split(t1.VALUE,",")) tmp_view AS value_list;

行轉列

單行轉多列

• split

SELECT
   split(c1,',')[0] as id,
   split(c1,',')[1] as name
FROM test

多行轉多列

• max case when

SELECT
    c1,
 CASE WHEN c2='c' THEN c3 ELSE 0 END,
 CASE WHEN c2='d' THEN c3 ELSE 0 END,
 CASE WHEN c2='e' THEN c3 ELSE 0 END
from row2col

SELECT
    c1,
 max(CASE WHEN c2='c' THEN c3 ELSE 0 END) c,
 max(CASE WHEN c2='d' THEN c3 ELSE 0 END) d,
 max(CASE WHEN c2='e' THEN c3 ELSE 0 END) e
from row2col
GROUP BY c1;