FAQ
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

Search Discussions

  • Namit Jain at Sep 8, 2009 at 5:23 pm
    If you run in strict mode, that is enforced.

    set hive.mapred.mode=strict



    -namit


    -----Original Message-----
    From: Abhijit Pol
    Sent: Tuesday, September 08, 2009 10:07 AM
    To: hive-user@hadoop.apache.org
    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
  • Abhijit Pol at Sep 10, 2009 at 6:51 pm
    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 wrote:

    If you run in strict mode, that is enforced.

    set hive.mapred.mode=strict



    -namit


    -----Original Message-----
    From: Abhijit Pol
    Sent: Tuesday, September 08, 2009 10:07 AM
    To: hive-user@hadoop.apache.org
    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
  • Namit Jain at Sep 11, 2009 at 12:56 am
    That should work - which revision of hive are you using ?


    From: Abhijit Pol
    Sent: Thursday, September 10, 2009 11:51 AM
    To: hive-user@hadoop.apache.org
    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 wrote:
    If you run in strict mode, that is enforced.

    set hive.mapred.mode=strict



    -namit


    -----Original Message-----
    From: Abhijit Pol
    Sent: Tuesday, September 08, 2009 10:07 AM
    To: hive-user@hadoop.apache.org
    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
  • Abhijit Pol at Sep 11, 2009 at 5:30 am
    I am using ~15 days old version from 0.4 branch......


    On Thu, Sep 10, 2009 at 5:55 PM, Namit Jain wrote:

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





    *From:* Abhijit Pol
    *Sent:* Thursday, September 10, 2009 11:51 AM
    *To:* hive-user@hadoop.apache.org
    *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 wrote:

    If you run in strict mode, that is enforced.

    set hive.mapred.mode=strict



    -namit



    -----Original Message-----
    From: Abhijit Pol
    Sent: Tuesday, September 08, 2009 10:07 AM
    To: hive-user@hadoop.apache.org
    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

  • Abhijit Pol at Sep 11, 2009 at 5:40 am
    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 wrote:

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



    On Thu, Sep 10, 2009 at 5:55 PM, Namit Jain wrote:

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





    *From:* Abhijit Pol
    *Sent:* Thursday, September 10, 2009 11:51 AM
    *To:* hive-user@hadoop.apache.org
    *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 wrote:

    If you run in strict mode, that is enforced.

    set hive.mapred.mode=strict



    -namit



    -----Original Message-----
    From: Abhijit Pol
    Sent: Tuesday, September 08, 2009 10:07 AM
    To: hive-user@hadoop.apache.org
    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

  • Ashish Thusoo at Sep 11, 2009 at 7:44 am
    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 [apol@rocketfuelinc.com]
    Sent: Thursday, September 10, 2009 10:40 PM
    To: hive-user@hadoop.apache.org
    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 wrote:
    I am using ~15 days old version from 0.4 branch......




    On Thu, Sep 10, 2009 at 5:55 PM, Namit Jain wrote:

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





    From: Abhijit Pol
    Sent: Thursday, September 10, 2009 11:51 AM

    To: hive-user@hadoop.apache.org
    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 wrote:

    If you run in strict mode, that is enforced.

    set hive.mapred.mode=strict



    -namit


    -----Original Message-----
    From: Abhijit Pol
    Sent: Tuesday, September 08, 2009 10:07 AM
    To: hive-user@hadoop.apache.org
    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
  • Abhijit Pol at Sep 11, 2009 at 4:55 pm
    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 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 [apol@rocketfuelinc.com]
    Sent: Thursday, September 10, 2009 10:40 PM
    To: hive-user@hadoop.apache.org
    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 wrote:
    I am using ~15 days old version from 0.4 branch......




    On Thu, Sep 10, 2009 at 5:55 PM, Namit Jain <njain@facebook.com<mailto:
    njain@facebook.com>> wrote:

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





    From: Abhijit Pol [mailto:apol@rocketfuelinc.com<mailto:
    apol@rocketfuelinc.com>]
    Sent: Thursday, September 10, 2009 11:51 AM

    To: hive-user@hadoop.apache.org 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 <njain@facebook.com<mailto:
    njain@facebook.com>> wrote:

    If you run in strict mode, that is enforced.

    set hive.mapred.mode=strict



    -namit


    -----Original Message-----
    From: Abhijit Pol [mailto:apol@rocketfuelinc.com<mailto:
    apol@rocketfuelinc.com>]
    Sent: Tuesday, September 08, 2009 10:07 AM
    To: hive-user@hadoop.apache.org 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



Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupuser @
categorieshive, hadoop
postedSep 8, '09 at 5:07p
activeSep 11, '09 at 4:55p
posts8
users3
websitehive.apache.org

People

Translate

site design / logo © 2022 Grokbase