hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Arpita SINGH <arpita.si...@steria.co.in>
Subject RE: Top N query
Date Wed, 03 Jun 2015 11:22:37 GMT
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:rravimr@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.singh@steria.co.in<mailto:arpita.singh@steria.co.in>>
wrote:
Is Inner Join supported by the Hive version you are using ?

Regards
Arpita Singh

From: Ravisankar Mani [mailto:rravimr@gmail.com<mailto:rravimr@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.
Mime
View raw message