Mailing-List: contact user-help@hive.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@hive.apache.org
Subject: Re: Strict mode and joins
To: user@hive.apache.org, edlinuxguru@gmail.com
References: 
 <CAENxBwwfgE42OaMXrKAHeujb8s6OGzE_W4aQAz5f5OOFqy0b2Q@mail.gmail.com>
From: Bennie Schut <bennie.schut@booking.com>
Message-ID: <5624B9E0.2050601@booking.com>
Date: Mon, 19 Oct 2015 11:37:36 +0200
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.10; rv:38.0)
 Gecko/20100101 Thunderbird/38.3.0
MIME-Version: 1.0
In-Reply-To: 
 <CAENxBwwfgE42OaMXrKAHeujb8s6OGzE_W4aQAz5f5OOFqy0b2Q@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------000103010804020307030708"

This is a multi-part message in MIME format.
--------------000103010804020307030708
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit

Hi Edward,

That's possibly due to using unix_timestamp (although the error message 
seems misleading if that proves true) . It's technically correct it 
shouldn't be flagged as deterministic because every time you call it 
you'll get a different answer as time progresses. However reality is I 
just want it called 1 time which is during planning and if I flag is as 
deterministic this is exactly what happens so you can do this:

@UDFType(deterministic = true)
public class UnixTimeStamp extends GenericUDFUnixTimeStamp {
     // Making the udf deterministic which is kind of cheating but makes 
partition pruning work.
}

And then register the udf like you normally would.

If that's not helping do some creative sub-querying might help like
FROM (select * from entry_hourly_v3 where dt=2015101517 ) 
entry_hourly_v3 INNER JOIN article_meta ON

Bennie.

On 15/10/15 23:06, Edward Capriolo wrote:
> So I have strict mode on and I like to keep it that way.
>
> I am trying to do this query.
>
> INSERT OVERWRITE TABLE vertical_stats_recent PARTITION (dt=2015101517)
> SELECT ...
> FROM entry_hourly_v3 INNER JOIN article_meta ON
> entry_hourly_v3.entry_id = article_meta.entry_id
> INNER JOIN channel_meta ON
> channel_meta.section_name = article_meta.channel
>
> WHERE entry_hourly_v3.dt=2015101517
> AND article_meta.dt=2015101517
> AND channel_meta.hitdate=20151015
> AND article_meta.publish_timestamp > ((unix_timestamp() * 1000) - 
> (1000 * 60 * 60 * 24 * 2))
> GROUP
>
> entry_hourly_v3, channel_meta and article_meta are partitioned tables.
>
> *Your query has the following error(s):*
>
> Error while compiling statement: FAILED: SemanticException [Error 
> 10041]: No partition predicate found for Alias "entry_hourly_v3" Table 
> "entry_hourly_v3"
>
> I also tried putting views on the table and I had no luck.
>
> Is there any way I can do this query without turning strict mode off?
>
>


--------------000103010804020307030708
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta content="text/html; charset=utf-8" http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    Hi Edward,<br>
    <br>
    That's possibly due to using unix_timestamp (although the error
    message seems misleading if that proves true) . It's technically
    correct it shouldn't be flagged as deterministic because every time
    you call it you'll get a different answer as time progresses.
    However reality is I just want it called 1 time which is during
    planning and if I flag is as deterministic this is exactly what
    happens so you can do this:<br>
    <br>
    @UDFType(deterministic = true)<br>
    public class UnixTimeStamp extends GenericUDFUnixTimeStamp {<br>
        // Making the udf deterministic which is kind of cheating but
    makes partition pruning work.<br>
    }<br>
    <br>
    And then register the udf like you normally would. <br>
    <br>
    If that's not helping do some creative sub-querying might help like<br>
    FROM (select * from entry_hourly_v3 where dt=2015101517 )
    entry_hourly_v3 INNER JOIN article_meta ON<br>
    <br>
    Bennie.<br>
    <br>
    <div class="moz-cite-prefix">On 15/10/15 23:06, Edward Capriolo
      wrote:<br>
    </div>
    <blockquote
cite="mid:CAENxBwwfgE42OaMXrKAHeujb8s6OGzE_W4aQAz5f5OOFqy0b2Q@mail.gmail.com"
      type="cite">
      <div dir="ltr">
        <div>So I have strict mode on and I like to keep it that way.</div>
        <div><br>
          I am trying to do this query.</div>
        <div><br>
        </div>
        <div>INSERT OVERWRITE TABLE vertical_stats_recent PARTITION
          (dt=2015101517)</div>
        <div>SELECT ...</div>
        <div>  </div>
        <div>FROM entry_hourly_v3 INNER JOIN article_meta ON </div>
        <div>entry_hourly_v3.entry_id = article_meta.entry_id </div>
        <div>INNER JOIN channel_meta ON</div>
        <div>channel_meta.section_name = article_meta.channel</div>
        <div><br>
        </div>
        <div>WHERE entry_hourly_v3.dt=2015101517</div>
        <div>AND article_meta.dt=2015101517</div>
        <div>AND channel_meta.hitdate=20151015 </div>
        <div>AND article_meta.publish_timestamp &gt; ((unix_timestamp()
          * 1000) - (1000 * 60 * 60 * 24 * 2))</div>
        <div>GROUP </div>
        <div><br>
          entry_hourly_v3, channel_meta and article_meta are partitioned
          tables. </div>
        <div><br>
        </div>
        <div>
          <p style="margin:0px 0px
10px;color:rgb(185,74,72);font-family:Arial,sans-serif;font-size:13px;line-height:20px"><strong>Your
              query has the following error(s):</strong></p>
          <div
style="color:rgb(185,74,72);font-family:Arial,sans-serif;font-size:13px;line-height:20px">
            <p class="" style="margin:0px 0px 10px">Error while
              compiling statement: FAILED: SemanticException [Error
              10041]: No partition predicate found for Alias
              "entry_hourly_v3" Table "entry_hourly_v3"</p>
            <p class="" style="margin:0px 0px 10px">I also tried putting
              views on the table and I had no luck.<br>
              <br>
              Is there any way I can do this query without turning
              strict mode off?</p>
            <p class="" style="margin:0px 0px 10px"><br>
            </p>
          </div>
        </div>
      </div>
    </blockquote>
    <br>
  </body>
</html>

--------------000103010804020307030708--