hive中将单行拆分成多行

初始值：

id	name	mobiles
1	jim	139,177,158

想得到的结果：

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

创建测试数据

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 中取出转换后的数据，其他字段仍旧从原表中取。