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 D215017594 for ; Tue, 15 Sep 2015 08:53:17 +0000 (UTC) Received: (qmail 10274 invoked by uid 500); 15 Sep 2015 08:53:03 -0000 Delivered-To: apmail-hive-user-archive@hive.apache.org Received: (qmail 10206 invoked by uid 500); 15 Sep 2015 08:53:03 -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 10196 invoked by uid 99); 15 Sep 2015 08:53:03 -0000 Received: from Unknown (HELO spamd4-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 15 Sep 2015 08:53:03 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd4-us-west.apache.org (ASF Mail Server at spamd4-us-west.apache.org) with ESMTP id E5F69C0041 for ; Tue, 15 Sep 2015 08:53:02 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd4-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 2.879 X-Spam-Level: ** X-Spam-Status: No, score=2.879 tagged_above=-999 required=6.31 tests=[DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, HTML_MESSAGE=3, RCVD_IN_MSPIKE_H3=-0.01, RCVD_IN_MSPIKE_WL=-0.01, SPF_PASS=-0.001] autolearn=disabled Authentication-Results: spamd4-us-west.apache.org (amavisd-new); dkim=pass (2048-bit key) header.d=gmail.com Received: from mx1-us-east.apache.org ([10.40.0.8]) by localhost (spamd4-us-west.apache.org [10.40.0.11]) (amavisd-new, port 10024) with ESMTP id aNPIoq88kC7l for ; Tue, 15 Sep 2015 08:53:02 +0000 (UTC) Received: from mail-la0-f42.google.com (mail-la0-f42.google.com [209.85.215.42]) by mx1-us-east.apache.org (ASF Mail Server at mx1-us-east.apache.org) with ESMTPS id 8997642BB4 for ; Tue, 15 Sep 2015 08:53:01 +0000 (UTC) Received: by lamp12 with SMTP id p12so101585380lam.0 for ; Tue, 15 Sep 2015 01:52:53 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :content-type; bh=A07Tcd/oX4fHUfg6bMu4qbm9y+zPkzJaVZ/1zwajrJI=; b=XDLZQ3wORO5rYKxeFxzvy6ZsGrhb7u+QB6KYpQqGb9OUPUL05BQQlbKGJg/Hn0WLT4 XqwT6+aHNe5OdcrDy5RH+zd9iEWZukUjTgqzdrEtfy6x4OoHJAo/2+SxQriZdLeW62Ld JyQU7UwVwAV+3BIecYDD1piBvw/W/XUsjJi7dczYCS6+QoN/BHSBqHvKr5CNHs42b21v Nl13dvud/sAqpmY0MvQs0SempQKro1WmNmQZ0IT0HkNORywHPN4vyKY6Q9VAasHqBV1t 2+3DU92Wnn9aYuzvm4fbKEl2dcPys5wlFT2WR2A3EcZPcNqpSRqGkzRj0zvQzFXgJ5fy FjUA== X-Received: by 10.152.18.193 with SMTP id y1mr6751936lad.45.1442307173567; Tue, 15 Sep 2015 01:52:53 -0700 (PDT) MIME-Version: 1.0 Received: by 10.112.205.133 with HTTP; Tue, 15 Sep 2015 01:52:33 -0700 (PDT) In-Reply-To: References: From: Elliot West Date: Tue, 15 Sep 2015 09:52:33 +0100 Message-ID: Subject: Re: Decomposing nested Hive statements with views To: "user@hive.apache.org" Content-Type: multipart/alternative; boundary=089e0149416cbcd36d051fc54e68 --089e0149416cbcd36d051fc54e68 Content-Type: text/plain; charset=UTF-8 On 15 September 2015 at 00:09, Gopal Vijayaraghavan wrote: > CTE Thank you for the in depth reply Gopal. I've just had a quick try out of CTEs but can't see how they address my original problem of decomposing a query into separate independent units. It seems that the CTE definition (' with' clause) must always be immediately followed by the use of the CTE (' select' for example). Therefore, the CTE queries must still be inlined into the parent queries, creating one monolithic query. My goal is to create separate independent processing units (queries) that can then be composed into larger queries. Effectively something like: WITH a AS (SELECT ... FROM A); // I would now like to be able to test 'a' // This can be in a separate file imported with 'source' WITH b AS (SELECT ... FROM B); // I would now like to be able to test 'b' // This can be in a separate file imported with 'source' // Compose 'a' and 'b' SOURCE a.hql; SOURCE b.hql; WITH c AS ( SELECT ... FROM ( SELECT ... FROM a WHERE ... ) A LEFT JOIN ( SELECT ... FROM b ) B ON (...) ); // I would now like to be able to test the composition 'c' // that uses both 'a' and 'b'. // This can be in a separate file imported with 'source' Thanks - Elliot. --089e0149416cbcd36d051fc54e68 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

= On 15 September 2015 at 00:09, Gopal Vijayaraghavan <<= a href=3D"mailto:gopalv@apache.org" target=3D"_blank">gopalv@apache.org= > wrote:
CTE

Thank you = for the in depth reply Gopal. I've just had a quick try out of CTEs but= can't see how they address my original problem of decomposing a query = into separate independent units. It seems that the CTE definition ('with' clause) must always be im= mediately followed by the use of the CTE ('select' for example). Therefore, the CTE queries must st= ill be inlined into the parent queries, creating one monolithic query.

My goal is= to create separate independent processing units (queries) that can then be= composed into larger queries. Effectively something like:

WITH a AS (SELECT ... FROM A);
// I would now like to be able to = test 'a'
// This can be in a separate file imported with &= #39;source'


= WITH b AS (SELECT ... FROM B);
// I would now like to be able to test 'b'
// This = can be in a separate file imported with 'source'


// Compose 'a'= and 'b'
SOURCE a.hql;
SOUR= CE b.hql;
WITH c AS (
=C2=A0 =C2=A0 SELECT ... FROM (
<= div class=3D"gmail_extra">
=C2=A0 =C2=A0 =C2=A0 =C2=A0 SELECT ... FROM a WHERE ...
=C2=A0 =C2=A0 ) A LEFT JOIN (
=C2=A0 =C2=A0 =C2=A0 =C2=A0 SELECT ... FROM b
<= /div>
=C2=A0 = =C2=A0 ) B ON (...)
);
// I would now like to be able to test= the composition 'c'
// that uses both 'a' and = 'b'.
// This can be in a separate file imported with &#= 39;source'

Thanks - Elliot.
--089e0149416cbcd36d051fc54e68--