lateral view explode使用

lateral view 侧视图   explode 爆炸               

 

select wdmc,customer_id from(
select 1 as wdmc,'14590170|14587203|21396385|22105752' as qyid
)a lateral view explode(split(qyid,'|')) as customer_id

报错：

Error while compiling statement: FAILED: ParseException line 5:44 extraneous input 'customer_id' expecting EOF near '<EOF>'

侧视图本质是left join，  explode 之后就是 右表了，原表为坐标

而左右两表 都需要  命名

正确的写法： 多了一个b（侧视图命名） 而已

select wdmc,customer_id from(
select 1 as wdmc,"14590170|14587203|21396385|22105752" as qyid
)a lateral view explode(split(qyid,"|"))b as customer_id

但结果有问题，我想用‘|’隔开  但结果是：

	wdmc	customer_id
1	1	1
2	1	4
3	1	5
4	1	9
5	1	0
6	1	1
7	1	7
8	1	0
9	1	|
10	1	1
11	1	4
12	1	5
13	1	8

...

尝试单独使用explode：

select explode(split("14590170|14587203|21396385|22105752","|")) as id

 结果一样：

	id
1	1
2	4
3	5
4	9
5	0
6	1
7	7
8	0
9	|
10	1
11	4
12	5
13	8
14	7
15	2

split有问题 

describe function split

	tab_name
1	split(str, regex) - Splits str around occurances that match regex

后来慢慢分析，原来是转义有问题，单独一个  ‘|’   在 hive中可能是别的含义，我们需要转义‘|’ 让hive知道我要的就是 ‘|’用它，

但是  ‘\|’ 其实这样也是错的，因为 ‘\’ 在hive中也有别的意思，所以，我们要将斜杠  ‘\’  先转义成  真斜杠

select explode(split("14590170|14587203|21396385|22105752","\\|")) as id

 

	id
1	14590170
2	14587203
3	21396385
4	22105752

再搭配 侧视图  lateral view 

select wdmc,customer_id from(
select 1 as wdmc,"14590170|14587203|21396385|22105752" as qyid
)a lateral view explode(split(qyid,"\\|"))b as customer_id

 

结果完美：

	wdmc	customer_id
1	1	14590170
2	1	14587203
3	1	21396385
4	1	22105752