CODEWITHSUNDEEP
hadoophive
Brisi
Brisi

Reputation: 1811

How to write subquery in select statement in hive

I am trying to do an select with sub-queries using hive.

In foos table have the following columns:

foo1,
foo2,
foo3_input

Is what I want

select foo1, foo2, foo3 from foos;

Is what I will execute

select foo1, foo2, foo3_input from foos;

for each foo3 in a row I would like to execute the following query

foo3 = select bar1 from bars where (foo3_input) between val1 and val2;

Is there any possible way to construct this query?

Upvotes: 10

Views: 23661

Answers (3)

Brisi
Brisi

Reputation: 1811

Hive version 0.13.0

select
    a.foo1, 
    a.foo2,
    b.bar1
from foos a, bars b
where a.foo3_input between b.val1, b.val2;

Upvotes: 0

Jimmy Zhang
Jimmy Zhang

Reputation: 967

select
    a.foo1, 
    a.foo2,
    b.bar1
from
(
    (select foo1, foo2, foo3_input from foos) a
    left outer join
    (select bar1, foo3_input from bars ) b
    on a.foo3_input = b.foo3_input
 )tmp
 where b.foo3_input between a.foo1, a.foo2
 ;

[edit]

select
    a.foo1, 
    a.foo2,
    b.bar1
from
(
    (select foo1, foo2, foo3_input from foos) a
    full outer join
    (select bar1, val1, var2 from bars ) b

 )tmp
 where a.foo3_input between b.val1, b.val2
 ;

Upvotes: 5

Galet
Galet

Reputation: 6269

"Hive does not support IN, EXISTS or subqueries in the WHERE clause." See this https://issues.apache.org/jira/browse/HIVE-1799

See this for where clause in hive https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SubQueries#LanguageManualSubQueries-SubqueriesintheWHEREClause

Upvotes: 1

Related Questions