SELECT myCol1, myCol2 FROM baseTable
LATERAL VIEW explode(col1) myTable1 AS myCol1
LATERAL VIEW explode(col2) myTable2 AS myCol2;
总结
Lateral View通常和UDTF一起出现，为了解决UDTF不允许在select字段的问题
Multiple Lateral View可以实现类似笛卡尔乘积。
Outer关键字可以把不输出的UDTF的空结果，输出成NULL，防止丢失数据。
explode
注意事项
No other expressions are allowed in SELECT ： SELECT pageid, explode(adid_list) AS myCol… is not supported（不能udtf和其他非udtf列混用）
UDTF’s can’t be nested ： SELECT explode(explode(adid_list)) AS myCol… is not supported（不能嵌套）
GROUP BY / CLUSTER BY / DISTRIBUTE BY / SORT BY is not supported
SELECT explode(adid_list) AS myCol … GROUP BY myCol is not supported（select中的udtf一定要有别名，否则报错）
query
SELECT explode(myCol) AS myNewCol FROM myTable;
SELECT explode(myMap) AS (myMapKey, myMapValue) FROM myMapTable;
SELECT posexplode(myCol) AS pos, myNewCol FROM myTable;
parse_url_tuple
create table
create external table if not exists t_url(f1 string, f2 string) row format delimited fields TERMINATED BY ' ' location '/test/url';
query
SELECT f1, b.* FROM t_url LATERAL VIEW parse_url_tuple(f2, 'HOST', 'PATH', 'QUERY', 'QUERY:k1') b as host, path, query, query_id
array_contains
create table
create EXTERNAL table IF NOT EXISTS userInfo (id int,sex string, age int, name string, email string,sd string, ed string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' location '/hive/dw';
query
select * from userinfo where sex='male' and (id!=1 and id !=2 and id!=3 and id!=4 and id!=5) and age < 30;
select * from (select * from userinfo where sex='male' and !array_contains(split('1,2,3,4,5',','),cast(id as string))) tb1 where tb1.age < 30;
776
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
