Return-Path: X-Original-To: apmail-hive-user-archive@www.apache.org Delivered-To: apmail-hive-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 2B22EC48A for ; Wed, 6 Jun 2012 01:21:26 +0000 (UTC) Received: (qmail 79874 invoked by uid 500); 6 Jun 2012 01:21:25 -0000 Delivered-To: apmail-hive-user-archive@hive.apache.org Received: (qmail 79819 invoked by uid 500); 6 Jun 2012 01:21:25 -0000 Mailing-List: contact user-help@hive.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@hive.apache.org Delivered-To: mailing list user@hive.apache.org Received: (qmail 79804 invoked by uid 99); 6 Jun 2012 01:21:25 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 06 Jun 2012 01:21:24 +0000 X-ASF-Spam-Status: No, hits=1.5 required=5.0 tests=FSL_RCVD_USER,HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of grover.markgrover@gmail.com designates 74.125.82.176 as permitted sender) Received: from [74.125.82.176] (HELO mail-we0-f176.google.com) (74.125.82.176) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 06 Jun 2012 01:21:17 +0000 Received: by weyr3 with SMTP id r3so5212732wey.35 for ; Tue, 05 Jun 2012 18:20:57 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; bh=iRozyJtegjJqmo44i0dCL/HgNv+kOC5oEKwz17HBVIk=; b=RvjkSSs7TLS3btaJe6m6Teno5rDpk/8kIbyU7kDcWA4fvCQfzSY6ydXX+4hkw4obE9 RLZTw63YzguCUkamSS+ngv2uNbAyThERqLzXhNS7yB+Y0KhUpFobOgmTrzR9Ij11HQ2e 7DSzz8qLK0u293M1CQtnpQPve8XtE/ZpGkMjurL2CoaFjHNou3HWp3GcWeKDJN1PZKD8 OsVOCnZVafsYR097+Gh461pzDN05pq26DUXcFMceB8CikEBvmhpY1YPwxCvV1tcMDdDz ABzetlSsU98Z4lkRxADLdh1QlNkVbd3sjZ1GxRGGGJ+RkEudewf11otcU71wLo9Kogk/ qYtQ== MIME-Version: 1.0 Received: by 10.216.213.143 with SMTP id a15mr15691636wep.156.1338945657316; Tue, 05 Jun 2012 18:20:57 -0700 (PDT) Received: by 10.216.168.68 with HTTP; Tue, 5 Jun 2012 18:20:57 -0700 (PDT) In-Reply-To: References: <1979027154.412006.1338830446802.JavaMail.root@sms-zimbra-message-store-03.sms.scalar.ca> Date: Tue, 5 Jun 2012 21:20:57 -0400 Message-ID: Subject: Re: Multi-group-by select always scans entire table From: Mark Grover To: user@hive.apache.org Content-Type: multipart/alternative; boundary=0016e6dbe92147254f04c1c39455 --0016e6dbe92147254f04c1c39455 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Hi Jan, The quick answer is I don't know but may be someone else on the mailing list does:-) Looking at the wiki page for Lateral view( https://cwiki.apache.org/confluence/display/Hive/LanguageManual+LateralView= ), there was a problem related to predicate pushdown on UDTF's ( https://issues.apache.org/jira/browse/HIVE-1056). However, that seemed to have been fixed in Hive 0.6.0 so it shouldn't have any impact on you. The fix for above ticket introduced a unit test (at ql/src/test/results/clientpositive/lateral_view_ppd.q) that tests predicate pushdown on UDTF's. Now, all the subsequent releases should have had that test pass (otherwise they wouldn't have been released, I hope). The test checks for a non-partition column for predicate pushdown. I wonder if it makes a difference with a partition column being used. Can you verify if your query with predicate pushdown enabled works as expected with a non-partition column in the where clause? In that case, the explain/explain extended output should be different from when predicate pushdown is disabled. If predicate pushdown works for non-partition columns but not for partition columns, please create a JIRA stating that predicate pushdown on UDTF's doesn't work with partition columns. If it doesn't work for both partition and non-partition columns, then obviously Hive-1056 is not working for you. We can take it up on the mailing list from there. Thanks for your input, Jan. Mark On Tue, Jun 5, 2012 at 1:19 AM, Jan Dolin=E1r wrote: > > > On Mon, Jun 4, 2012 at 7:20 PM, Mark Grover wrote: > >> Hi Jan, >> Glad you found something workable. >> >> What version of Hive are you using? Could you also please check what the >> value of the property hive.optimize.ppd is for you? >> >> Thanks, >> Mark >> >> > Hi Mark, > > Thanks for reply. I'm using hive 0.7.1 distributed from Cloudera as > cdh3u4. The property hive.optimize.ppd is set true, but I have tried to > turn it off and it doesn't effect the behavior of the problematic query a= t > all. Any other ideas? :-) > > Also could some of you good guys try to check this on hadoop 0.8 or newer= ? > It would be nice to know if it is worth to go through all the hassle of > upgrading or if it won't help. Also, if it is not fixed already, it might > be good idea to report it as a bug. > > Jan > --0016e6dbe92147254f04c1c39455 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Hi Jan,
The quick answer is I don't know but may be someone= else on the mailing list does:-)

Looking = at the wiki page for Lateral view(https://cwiki.apache.org/confluence/display/Hive/Lang= uageManual+LateralView), there was a proble= m related to predicate pushdown on UDTF's (https://issues.apache.org/jira/browse/HIVE-1056). However, that seemed to have been fixed in Hive 0.6.0 so it s= houldn't have any impact on you.

The fix = for above ticket introduced a unit test (at ql/src/test/results/clientposit= ive/lateral_view_ppd.q) that tests predicate pushdown on UDTF's. Now, a= ll the subsequent releases should have had that test pass (otherwise they w= ouldn't have been released, I hope). The test checks for a non-partitio= n column for predicate pushdown. I wonder if it makes a difference with a p= artition column being used.

Can you = verify if your query with predicate pushdown enabled works as expected with= a non-partition column in the where clause? In that case, the explain/expl= ain extended output should be different from when predicate pushdown is dis= abled. If predicate pushdown works for non-partition columns but not for pa= rtition columns, please create a JIRA stating that predicate pushdown on UD= TF's doesn't work with partition columns.=A0

If it do= esn't work for both partition and non-partition columns, then obviously= Hive-1056 is not working for you. We can take it up on the mailing list fr= om there.

Thanks f= or your input, Jan.

Mark=A0

On Tue, Jun 5, 2012 at 1:19 AM, Ja= n Dolin=E1r <dolik.rce@gmail.com> wrote:



--0016e6dbe92147254f04c1c39455--