HIVE:如何在侧视图中包含空行爆炸 [英] HIVE: How to include null rows in lateral view explode

问题描述

我有一张桌子，如下:

user_id email
u1      e1, e2
u2      null

我的目标是将其转换为以下格式:

My goal is to convert this into the following format:

user_id email
u1      e1
u1      e2
u2      null

为此，我在Hive中使用了侧面视图explode()函数，如下所示:

So for this I am using the lateral view explode() function in Hive, as follows:

select *  FROM  table
       LATERAL VIEW explode (split(email ,',')) email AS email_id 

但是，这样做会跳过u2行，因为它在电子邮件中具有空值.我们如何在输出中也包含空值?

But doing this the u2 row is getting skipped as it has null value in email. How can we include the nulls too in the output?

我正在使用一种变通方法，可以将此表与基本表进行合并而不会爆炸，但是由于这个原因，我认为将再次扫描数据.我想知道是否有更好的方法.

I am using a workaround doing an union of this table with the base table without explode, but I think the data will be scanned one more time because of this. I wanted to know if there is a better way to do it.

推荐答案

在查询中包括OUTER以获取具有NULL值的行

include OUTER in the query to get rows with NULL values

类似

select *  FROM table LATERAL VIEW OUTER explode (  split (  email  ,','  ) ) email AS email_id;

检查此链接-> https://cwiki.apache .org/confluence/display/Hive/LanguageManual + LateralView#LanguageManualLateralView-OuterLateralViews

这篇关于HIVE:如何在侧视图中包含空行爆炸的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！