6

If I have a table mytable and a list

set vals = (1,2,3,4);

and I want to cross-join the table with the list (getting a new table which has 4 time as many rows as the original table and an extra val column), do I have a better option than creating an explicit temp table?

What I can do is:

select a.*, b.val
from mytable a cross join
(select stack(4,1,2,3,4) as (val) from 
 (select * from mytable limit 1) z) b;

EDIT: My main use case would be passing -hiveconf vals='4,1,2,3,4' to hive and replacing stack(4,1,2,3,4) with stack(${hiveconf:vals}) in the above code.

Community
  • 1
  • 1
sds
  • 58,617
  • 29
  • 161
  • 278
  • Can't you use Union? like (select 1 as stg union select 2 union select 3 union selec 4)? – PeterRing Jan 29 '14 at 16:25
  • @PeterRing: I think `union` would be even worse than what I have, but please do post an answer using it! – sds Jan 29 '14 at 16:29

3 Answers3

5

I dont know this will help.

SELECT *
from mytable cross join
(select 1 as p
union 
select 2 
union 
select 3
union 
select 4) as x
PeterRing
  • 1,767
  • 12
  • 20
  • +1, but what if I have 20 instead of 4 values? I think your code explodes in size. – sds Jan 29 '14 at 16:35
2
select a.*, b.val
from a lateral view explode(array(1,2,3,4)) b as val;
Joe K
  • 18,204
  • 2
  • 36
  • 58
1
select a.*, b.val
from mytable a cross join
(values (1), (2), (3), (4), (5))b(val);
Khashayar Nourian
  • 43
  • 1
  • 4