Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive >> mail # user >> enforcing query with partition column


Copy link to this message
-
RE: enforcing query with partition column
What does explain <query> show? An easy work around for now is to push the partition predicate into a subquery on the table.

Ashish
________________________________________
From: Abhijit Pol [[EMAIL PROTECTED]]
Sent: Thursday, September 10, 2009 10:40 PM
To: [EMAIL PROTECTED]
Subject: Re: enforcing query with partition column

Looks like MAPJOIN hint is causing a problem.

Here is real the query which gives problem:

INSERT OVERWRITE TABLE nikeusers1
SELECT u.uid, u.type FROM
(
 SELECT /*+ MAPJOIN(a)*/ b.user_id AS uid, a.action_type AS type FROM actions a join beacons b on (b.conversion_action_version_id = a.version_id) WHERE a.campaign_id=443 and b.data_date >= '20090901' and (a.action_type=0 or a.action_type=2)
UNION ALL
SELECT /*+ MAPJOIN(a)*/ c.user_id AS uid, a.action_type AS type FROM actions a join conversions c on (c.conversion_action_version_id = a.version_id) WHERE a.campaign_id=443 and c.data_date >= '20090901' and (a.action_type=0 or a.action_type=2)
) u

Hive history file=/tmp/apol/hive_job_log_apol_200909110535_1230112822.txt
FAILED: Error in semantic analysis: The query does not reference any valid partition. To run this query, set hive.mapred.mode=nonstrict

Same query without MAPJOIN hint works fine....

On Thu, Sep 10, 2009 at 10:30 PM, Abhijit Pol <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>> wrote:
I am using ~15 days old version from 0.4 branch......
On Thu, Sep 10, 2009 at 5:55 PM, Namit Jain <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>> wrote:

That should work – which revision of hive are you using ?

From: Abhijit Pol [mailto:[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>]
Sent: Thursday, September 10, 2009 11:51 AM

To: [EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>
Subject: Re: enforcing query with partition column

Thanks Namit. Setting this parameter in general works. However following query is now not allowed:

SELECT a.bar FROM
(
SELECT b.bar FROM table1 b WHERE b.partition_key = 1
UNION ALL
SELECT c.bar FROM table2 c WHERE c.partition_key = 1
) a

Individual sub queries are allowed here...any suggestions?
On Tue, Sep 8, 2009 at 10:22 AM, Namit Jain <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>> wrote:

If you run in strict mode, that is enforced.

set hive.mapred.mode=strict

-namit
-----Original Message-----
From: Abhijit Pol [mailto:[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>]
Sent: Tuesday, September 08, 2009 10:07 AM
To: [EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>
Subject: enforcing query with partition column

Is there a way in hive to enforce use of partition column before
accepting the query for execution?
Often user make mistake and don't specify them and then end-up waiting
a lot more than expected....

Thanks,
Abhi