Spark/Hive 行列转换

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);
1
2
3

• lateral view explode(map)

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

• explode(array)

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

---

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;
1
2
3

• 统计id出现次数

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

• 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;
1
2
3
4
5
6

• 转换

SELECT
    a.*,
    b.host,
    b.path
FROM urls a
LATERAL VIEW parse_url_tuple(url, 'HOST',"PATH") b as host,path;
1
2
3
4
5
6

---

列转行

• 原表

SELECT
    name,
    concat_ws(',',collect_set(course)) AS course,
    concat_ws(',',collect_set(cast(score AS string))) AS score
FROM posexplode
GROUP BY name;
1
2
3
4
5
6

单列转多行

• 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
1
2
3
4
5
6
7
8
9
10
11
12
13

多列转多行

• 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

• split

select
    concat_ws(',',
        concat(c1,':c:',c2),
        concat(c1,':d:',c3),
        concat(c1,':e:',c4)
    ) as value
from  col2row;
1
2
3
4
5
6
7

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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

行转列

单行转多列

• split

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

多行转多列

• 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
1
2
3
4
5
6

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;
1
2
3
4
5
6
7

• 字符型

insert into row2col2 (c1, c2, c3) values
("a","c","cc"),
("a","d","dd"),
("a","e","ee"),
("b","c","cc"),
("b","d","dd"),
("b","e","ee");
1
2
3
4
5
6
7

# 转换
SELECT
 c1,
 max(CASE WHEN c2='c' THEN c3  END) AS c ,
 max(CASE WHEN c2='d' THEN c3  END) AS d,
 max(CASE WHEN c2='e' THEN c3  END) AS e
FROM row2col2
GROUP BY c1;
1
2
3
4
5
6
7
8

注意此处为什么要加max()函数，是因为我们case when后 没有匹配到的地方会以null值呈现，
就像下面这种情况（没用group by是方便大家理解）

SELECT
 c1,
 CASE WHEN c2='c' THEN c3  END ,
 CASE WHEN c2='d' THEN c3  END,
 CASE WHEN c2='e' THEN c3  END
FROM row2col2;
1
2
3
4
5
6