Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 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
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB