CODEWITHSUNDEEP
sqlhiveimpala
Sergio Vivas Pleite
Sergio Vivas Pleite

Reputation: 147

Create new rows depending on values in Hive/Impala

I am trying to do an operation on Hive/Impala and I don't know how to continue. First, I explain what I want to do. I have the following table:

enter image description here

Well, I want to create a new row for each missing position, and assign it a zero value. The table would look like this:

enter image description here

I do not know if it is possible to create this functionality in Hive or Impala, either one would suit me.

Thanks a lot!

Upvotes: 0

Views: 475

Answers (2)

Sergio Vivas Pleite
Sergio Vivas Pleite

Reputation: 147

Based on @GordonLinoff's answer, i get what I want, but i made some changes. Basically, it is what he says, but splitting his answer in two different queries. This is because in Hive you can not do LATERAL VIEW and JOIN in the same query. The solution would be:

create table t1 as 
select i, x 
from (select max(position) as max_position from t) p 
lateral view posexplode(split(space(p.max_position), ' ')) pe as i, x

select a.i, coalesce(b.value, 0) as value
from t1 a LEFT JOIN t b
on a.i = b.position
where a.i != 0

Thanks Gordon!

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can use a trick in Hive where you generate a string of spaces and then split the string into an array and turn the array into a table:

select pe.i, coalesce(t.value, 0) as value
from (select i, x
      from (select max(position) as max_position
            from t
           ) p lateral view 
           posexplode(split(space(p.max_position), ' ')) pe as i, x
     ) pe left join
     t
     on pe.i = t.position;

Upvotes: 1

Related Questions