hive中将单行拆分成多行

初始值：

想得到的结果：

创建测试数据

CREATE TABLE fwj.customer
(
id STRING,
name STRING,
mobiles STRING);

INSERT INTO fwj.customer
SELECT '1','jim','139,177,158' FROM system.dual;

1.最笨的办法：

逻辑简单，写法粗暴，可拓展性为0。

SELECT a.id,a.name,substr(a.mobiles,1,3) mobiles FROM fwj.customer a 
UNION ALL
SELECT a.id,a.name,substr(a.mobiles,5,3) mobiles FROM fwj.customer a 
UNION ALL
SELECT a.id,a.name,substr(a.mobiles,9,3) mobiles FROM fwj.customer a 

result:
id    name   mobiles    
1    jim    139        
1    jim    177        
1    jim    158        

2. 第二种方法

仔细看看这个mobiles字段，其实我们只要将这个字段中用逗号间隔的值拆开到每一列，然后再和原表关联就能得到我们想要的答案了。在oracle中有列传行，hive中也有类似的操作。

SELECT b.id,a.mobiles,b.name FROM (
SELECT explode(split(t.mobiles,',')) mobiles FROM fwj.customer t )a ,  fwj.customer b;

result:
id    name   mobiles    
1    jim    139        
1    jim    177        
1    jim    158    

这里会用到两个函数。
split(str,sep):
该函数的作用是拆分指定分隔符分割的字符串，返回一个列表。
如：

SELECT split(a.mobiles,',') FROM  fwj.customer a;

result:
["139","177","158"]

explode(arr):
该函数是一个表生成函数。输入一个列表参数，将列表中的每个值都转换为一行。
如：

SELECT explode(plit(a.mobiles,',')) mobiles FROM  fwj.customer a;

result:

mobiles
139
177
158

3.第三种方法

问题虽然解决了，但还不够完美，表被读了两次。能不能一次就能取出我们想要的结果呢？

再来优化一下:

SELECT t.id,t.name,explode(split(t.mobiles,',')) mobiles FROM fwj.customer t 

可惜不行，explode 这类UDTF函数不支持和其他字段一块被select。

不过，hive提供了 lateral view 侧视图 这个功能。

SELECT a.id,a.name,mob.mobile  FROM fwj.customer a lateral view explode(split(a.mobiles,',')) mob AS mobile;

result:
id    name   mobile    
1    jim    139        
1    jim    177        
1    jim    158    

可以理解成在一次查询中 先生成了一个视图 mob 包含了行转列后的数据，之后从mob 中取出转换后的数据，其他字段仍旧从原表中取。