HIVE

1、hive语句

1）lateral view explode

-- LATERAL VIEW udtf(expression) tableAlias AS columnAlias (’,’ columnAlias)
tableAlias:表别名
columnAlias:表中的列别名

-- explode() takes in an array (or a map) as an input and outputs the elements of the array (map) as separate rows. 
SELECT explode(myCol) AS myNewCol FROM myTable;

-- Lateral view is used in conjunction with user-defined table generating functions such as explode(). As mentioned in Built-in Table-Generating Functions, a UDTF generates zero or more output rows for each input row. A lateral view first applies the UDTF to each row of base table and then joins resulting output rows to the input rows to form a virtual table having the supplied table alias.
SELECT pageid, adid
FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid;

-- 例如：增加计算所有类型的数据
LATERAL VIEW explode(array(platform, 'ALL')) col AS platform_all

在正常解析一个有值的字符串时，用lateral view explode是完全ok的，但是，当遇到该字符串为空时，如果在使用该函数，就会导致该条记录消失。具体场景如下：

这样的结果就会导致我们漏掉一部分用户的浏览信息，在统计浏览量时数据缺失，结果与实际偏小。
这时，就要用到lateral view outer explode