Hive - nested query

Question

I've totally new to hive. I've able to run non-nested SQL queries without a problem. However, I'm unable to execute the below query.

SELECT E_DATE,
       CLIENT,
       INS,
       (SELECT SUM(time)
        FROM   mytable b
        WHERE  a.E_DATE = b.E_DATE
               AND a.INS = b.INS
               AND b.CLIENT = 'comA') TOTAL_TIME
FROM   mytable a
GROUP  BY E_DATE,CLIENT,INS

I get the error FAILED: ParseException line 4:8 cannot recognize input near 'SELECT' 'SUM' '(' in expression specification

Hive doesn't support sub-queries in a SELECT statement. cwiki.apache.org/confluence/display/Hive/… — gobrewers14
Hello - Thank you for pointing me there. Any suggestions on how I could convert the SQLquery into hive query please? — User12345
An example of what your data looks like and what you want to output to look like would be helpful. — gobrewers14
Could I please ask you to refer to stackoverflow.com/questions/31049397/…? THe table structure in there is what am after — User12345
There is a typo in that example (where did a4 go?), scientific notation, and there's words for output ... I have no idea what the output should be. — gobrewers14

Ankit Agrahari Ankit Agrahari · Accepted Answer · 2015-06-26T13:38:36

select E_DATE,CLIENT,INS,SUM(time) 
from mytable 
where CLIENT = 'comA' 
group by E_DATE,CLIENT,INS;

OR

select a.E_DATE,a.CLIENT,a.INS,SUM(b.time)
from mytable a JOIN mytable b ON (a.E_DATE = b.E_DATE AND a.INS = b.INS)
where b.CLIENT = 'comA'
group by a.E_DATE,a.CLIENT,a.INS;

If you have a nested queries in sql, than in hive you have to go for joins to get the result.

Hive - nested query

1 Answers