hive explode 行拆列

创建一张表test_explode，表结构如下

 

表数据如下：

 

1.使用explode函数

select explode(friends) as friend from test_explode;

但是只使用explode函数很难满足实际需求，原因如下：

　　1.1 No other expressions are allowed in SELECT

0: jdbc:hive2://master01.hadoop.mobile.cn:1> select name,explode(friends) as friend from test_explode;
Error: Error while compiling statement: FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions (state=42000,code=10081)

 　　1.2 UDTF's can't be nested

　　 1.3 GROUP BY / CLUSTER BY / DISTRIBUTE BY / SORT BY is not supported

 

2 配合LATERAL VIEW 解除限制

select name,sex,phone,friend from test_explode LATERAL VIEW explode(friends) myTable1 AS friend ;

结果如下：