Hive的Lateral View

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

转自：http://ju.outofmemory.cn/entry/30251