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




fatkun

13条评论


You can leave the first : )



发表评论

电子邮件地址不会被公开。