That is the point , actually each Hive query is transformed into a MapReduce Program which can easily evaluate an EQUIJOIN ( which permits only equality condition ) . Till recently as far as I know Hive supported only EquiJoins , other joins were not supported decently because transforming them into a MR program is not easy .
If it helps , you can refer : https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Joins which says , : Only equality joins, outer joins, and left semi joins are supported in Hive. Hive does not support join conditions that are not equality conditions as it is very difficult to express such conditions as a map/reduce job. Also, more than two tables can be joined in Hive. Regards Arpita Singh From: Ravisankar Mani [mailto:rrav...@gmail.com] Sent: Wednesday, June 03, 2015 12:17 PM To: user@hive.apache.org Subject: Re: Top N query Hi Arpita, Inner join working perfectly. But the 'or' condition not supported in inner join. Regards Ravi On Wed, Jun 3, 2015 at 6:39 AM, Arpita SINGH <arpita.si...@steria.co.in<mailto:arpita.si...@steria.co.in>> wrote: Is Inner Join supported by the Hive version you are using ? Regards Arpita Singh From: Ravisankar Mani [mailto:rrav...@gmail.com<mailto:rrav...@gmail.com>] Sent: Wednesday, June 03, 2015 11:29 AM To: user@hive.apache.org<mailto:user@hive.apache.org> Subject: Top N query Hi everyone, "FAILED: SemanticException [Error 10019]: Line 9:5 OR not supported in JOIN currently 'movieid_0' " Exception occur while executing the following query. But i need the add the conditions. Kindly help other way to resolve the query. Please refer the following query SELECT IF(ISNOTNULL(SUM(recommend_ratings2.rating)),SUM(recommend_ratings2.rating), 0 ) AS rating ,recommend_ratings2.movieid AS movieid FROM default.recommend_ratings2 INNER JOIN (SELECT recommend_ratings2.movieid AS movieid_0 ,IF(ISNOTNULL(SUM(recommend_ratings2.rating)),SUM(recommend_ratings2.rating), 0 ) AS Measure_0 FROM default.recommend_ratings2 GROUP BY recommend_ratings2.movieid ORDER BY Measure_0 DESC limit 2 ) T_0 ON ( recommend_ratings2.movieid = T_0.movieid_0 or ((recommend_ratings2.movieid IS NULL) AND (T_0.movieid_0 IS NULL))) Regards Ravi This email and any attachments may contain confidential information and intellectual property (including copyright material). It is only for the use of the addressee(s) in accordance with any instructions contained within it. If you are not the addressee, you are prohibited from copying, forwarding, disclosing, saving or otherwise using it in any way. If you receive this email in error, please immediately advise the sender and delete it. Steria may monitor the content of emails within its network to ensure compliance with its policies and procedures. Emails are susceptible to alteration and their integrity (including origin) cannot be assured. Steria shall not be liable for any modification to a message, or for messages falsely sent. This email and any attachments may contain confidential information and intellectual property (including copyright material). It is only for the use of the addressee(s) in accordance with any instructions contained within it. If you are not the addressee, you are prohibited from copying, forwarding, disclosing, saving or otherwise using it in any way. If you receive this email in error, please immediately advise the sender and delete it. Steria may monitor the content of emails within its network to ensure compliance with its policies and procedures. Emails are susceptible to alteration and their integrity (including origin) cannot be assured. Steria shall not be liable for any modification to a message, or for messages falsely sent.
