Lateral View用于把UDTF的行转列结果集合在一起提供服务。Lateral View可以返回多列数据,前提是UDTF注册的输出个数。 UDTF代码参考:hive/src/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDTFExplode.java

准备数据

create table test_array (a array<int>, b array<string>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '`'
COLLECTION ITEMS TERMINATED BY ',';

测试数据,保存文件为/tmp/test

10,11`tom,mary
20,21`kate,tim

导入数据

load data local inpath '/tmp/test' overwrite into table test_array;

测试

hive> select explode(b) as name, a from test_array;
FAILED: SemanticException 1:27 Only a single expression in the SELECT clause is supported with UDTF's. Error encountered near token 'a'

UDTF只能select单列 使用LATERAL VIEW

hive> SELECT a, name FROM test_array LATERAL VIEW explode(b) r1 AS name;
[10,11]	tom
[10,11]	mary
[20,21]	kate
[20,21]	tim

可以有多个lateral view

hive> SELECT id, name FROM test_array LATERAL VIEW explode(b) r1 AS name LATERAL VIEW explode(A) r2 AS id;
10	tom
11	tom
10	mary
11	mary
20	kate
21	kate
20	tim
21	tim

参考

hive中的Lateral View