You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Ravisankar Mani <rr...@gmail.com> on 2015/06/03 12:28:43 UTC
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
Re: Top N query
Posted by Gopal Vijayaraghavan <go...@apache.org>.
> I does't understand about your solution. Could you please provide exact
>query?
...
GROUP BY recommend_ratings2.movieid ORDER BY Measure_0 DESC limit 2 ) T_0
ON ( recommend_ratings2.movieid <=> T_0.movieid_0)
Follow the docs on the mysql impl of the space-ship operator -
<https://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator
_equal-to>
The hive impl follows the same rules which is (a.x = b.x) OR (a.x is NULL
and b.x is NULL).
AFAIK, most of the complex optimizations are turned off in the presence of
null-safe operators.
Cheers,
Gopal
Re: Top N query
Posted by Ravisankar Mani <rr...@gmail.com>.
Hi gopal,
Thanks for your response.
I does't understand about your solution. Could you please provide exact
query?
Regards,
Ravi
On Wed, Jun 3, 2015 at 2:23 PM, Gopal Vijayaraghavan <go...@apache.org>
wrote:
> Hi,
>
> This particular case can be handled by a special case of the inner
> equijoin.
>
> > ( recommend_ratings2.movieid = T_0.movieid_0 or
> >((recommend_ratings2.movieid IS NULL) AND
> (T_0.movieid_0 IS NULL)))
>
> Can be rewritten as a null-safe equi-join (i.e where movieid <=>
> movieid_0, which is not SQL semantics).
>
> That however is a corner-case to workaround folks who want OR joins to
> just handle NULL == NULL as a match.
>
> Cheers,
> Gopal
>
>
>
Re: Top N query
Posted by Gopal Vijayaraghavan <go...@apache.org>.
Hi,
This particular case can be handled by a special case of the inner
equijoin.
> ( recommend_ratings2.movieid = T_0.movieid_0 or
>((recommend_ratings2.movieid IS NULL) AND
(T_0.movieid_0 IS NULL)))
Can be rewritten as a null-safe equi-join (i.e where movieid <=>
movieid_0, which is not SQL semantics).
That however is a corner-case to workaround folks who want OR joins to
just handle NULL == NULL as a match.
Cheers,
Gopal
RE: Top N query
Posted by Arpita SINGH <ar...@steria.co.in>.
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 <ar...@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<ma...@gmail.com>]
Sent: Wednesday, June 03, 2015 11:29 AM
To: user@hive.apache.org<ma...@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.
Re: Top N query
Posted by Ravisankar Mani <rr...@gmail.com>.
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 <ar...@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]
> *Sent:* Wednesday, June 03, 2015 11:29 AM
> *To:* 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.
>
RE: Top N query
Posted by Arpita SINGH <ar...@steria.co.in>.
Is Inner Join supported by the Hive version you are using ?
Regards
Arpita Singh
From: Ravisankar Mani [mailto:rravimr@gmail.com]
Sent: Wednesday, June 03, 2015 11:29 AM
To: 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.