ParseException - mismatched input in subquery source - error in Hive

Question

I am running the next query in Hive:

SELECT COUNT(*) 
FROM 
  (
   SELECT * 
   FROM 
   (SELECT id, COUNT(*) AS count_p_id FROM palladion GROUP BY id) a, 
   (SELECT cid, COUNT(*) AS count_q_cid FROM operations GROUP BY cid) b 
   WHERE a.id=b.cid
  ) 
WHERE count_p_id < count_q_cid;

I keep getting the error like

 ParseException line 1:103 mismatched input ',' expecting ) near 'a' in subquery source

What is the problem with the code? I can't see any.

vijay kumar · Accepted Answer · 2014-07-31 15:53:55Z

1

Implicit join notation is supported starting with Hive 0.13.0. This allows the FROM clause to join a comma-separated list of tables, omitting the JOIN keyword. For example:

SELECT *
FROM table1 t1, table2 t2
WHERE t1.id = t2.id

I hope you are using < 0.13.0 version . If your hive version is < 0.13.0

Try this : you have to use JOIN - ON , not Comma - WHERE

SELECT COUNT(*) 
FROM 
  (
   SELECT * 
   FROM 
   (SELECT id, COUNT(*) AS count_p_id FROM palladion GROUP BY id) a JOIN 
   (SELECT cid, COUNT(*) AS count_q_cid FROM operations GROUP BY cid) b 
   ON  a.id=b.cid
  ) 
WHERE count_p_id < count_q_cid;

edited Jul 31, 2014 at 15:53

answered Jul 31, 2014 at 15:48

vijay kumar

2,0491 gold badge16 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Konstantin Popov Over a year ago

Yes, it turns out we are using 0.10.0. The explicit join syntax works. Thank you!

Collectives™ on Stack Overflow

ParseException - mismatched input in subquery source - error in Hive

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related