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

If you run in strict mode, that is enforced.

set hive.mapred.mode=strict

-namit
-----Original Message-----
From: Abhijit Pol [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, September 08, 2009 10:07 AM
To: [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

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]> wrote:

> If you run in strict mode, that is enforced.
>
> set hive.mapred.mode=strict
>
>
>
> -namit
>
>
> -----Original Message-----
> From: Abhijit Pol [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, September 08, 2009 10:07 AM
> To: [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
>

That should work - which revision of hive are you using ?
From: Abhijit Pol [mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 10, 2009 11:51 AM
To: [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

I am using ~15 days old version from 0.4 branch......

On Thu, Sep 10, 2009 at 5:55 PM, Namit Jain <[EMAIL PROTECTED]> wrote:

>  That should work – which revision of hive are you using ?
>
>
>
>
>
> *From:* Abhijit Pol [mailto:[EMAIL PROTECTED]]
> *Sent:* Thursday, September 10, 2009 11:51 AM
> *To:* [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]> wrote:
>
> If you run in strict mode, that is enforced.
>
> set hive.mapred.mode=strict
>
>
>
> -namit
>
>
>
> -----Original Message-----
> From: Abhijit Pol [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, September 08, 2009 10:07 AM
> To: [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
>
>
>

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]>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]> wrote:
>
>>  That should work – which revision of hive are you using ?
>>
>>
>>
>>
>>
>> *From:* Abhijit Pol [mailto:[EMAIL PROTECTED]]
>> *Sent:* Thursday, September 10, 2009 11:51 AM
>> *To:* [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]> wrote:
>>
>> If you run in strict mode, that is enforced.
>>
>> set hive.mapred.mode=strict
>>
>>
>>
>> -namit
>>
>>
>>
>> -----Original Message-----
>> From: Abhijit Pol [mailto:[EMAIL PROTECTED]]
>> Sent: Tuesday, September 08, 2009 10:07 AM
>> To: [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
>>
>>
>>
>
>

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

>> What does explain <query> show?
Attached is output of explain extended for both queries (1) with MAPJOIN
hint (2) without hint...

>>An easy work around for now is to push the partition predicate into a
subquery on the table.
Not sure if I get this. I already have partition predicate pushed inside
subquery.
The actions table is dimension table and has no partitions. Beacons and
conversions table are fact tables partitioned on data_date.

On Fri, Sep 11, 2009 at 12:42 AM, Ashish Thusoo <[EMAIL PROTECTED]>wrote:

> 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
>
>
>
>
>