HIVE: How to include null rows in lateral view explode
<p>I have a table as follows:</p>
<pre><code>user_id email
u1      e1, e2
u2      null
</code></pre>
<p>My goal is to convert this into the following format:</p>
<pre><code>user_id email
u1      e1
u1      e2
u2      null
</code></pre>
<p>So for this I am using the lateral view explode() function in Hive, as follows:</p>
<pre><code>select *  FROM  table
       LATERAL VIEW explode (split(email ,',')) email AS email_id 
</code></pre>
<p>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?</p>
<p>Edit: 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.</p>