Geek asks and answers

Many Hive subqueries

I am using Hive 0.9.0 and I am trying to execute a request ie

`SELECT a.id, b.user FROM (SELECT...FROM a_table) a, (SELECT...FROM b_table) b WHERE a.date   = b.date;`

but it returns the error "loop (...) + does not match the input ...". Does Hive support multiple subqueries in FROM in the same way as Oracle DB?

+5
source share
3 answers

Several subqueries are allowed in the hive.

I tested the code below, it works.

    select * from (select id from test where id>10) a 
join (select id from test where id>20) b on a.id=b.id;

Please post your exact code so that I can give an appropriate solution.

+6
source

joins subqueries. Absolutely.

I think the key problem is what u is using SELECT...FROM.

The correct syntax SELECT * FROM

SELECT a.id, b.user 
FROM 
(SELECT * FROM a_table) a 
JOIN (SELECT * FROM b_table) b ON a.date = b.date;
+1
source

If you want to get the full Cartesian product before applying WHERE instead:

SELECT a.id, b.user FROM (SELECT...FROM a_table) a, (SELECT...FROM b_table) b WHERE a.date   = b.date;

you should use "join" in the middle, i.e.

SELECT a.id, b.user FROM (SELECT...FROM a_table) a join (SELECT...FROM b_table) b WHERE a.date   = b.date;

not allowed in strict mode.

0
source

More articles:


All Articles