Return-Path: 
 <user-return-14656-apmail-hive-user-archive=hive.apache.org@hive.apache.org>
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 0B70010276
	for <apmail-hive-user-archive@www.apache.org>;
 Thu, 17 Oct 2013 21:48:13 +0000 (UTC)
Received: (qmail 36517 invoked by uid 500); 17 Oct 2013 21:46:15 -0000
Delivered-To: apmail-hive-user-archive@hive.apache.org
Received: (qmail 36348 invoked by uid 500); 17 Oct 2013 21:46:05 -0000
Mailing-List: contact user-help@hive.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:user-help@hive.apache.org>
List-Unsubscribe: <mailto:user-unsubscribe@hive.apache.org>
List-Post: <mailto:user@hive.apache.org>
List-Id: <user.hive.apache.org>
Reply-To: user@hive.apache.org
Delivered-To: mailing list user@hive.apache.org
Received: (qmail 36307 invoked by uid 99); 17 Oct 2013 21:45:59 -0000
Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136)
    by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 17 Oct 2013 21:45:59 +0000
X-ASF-Spam-Status: No, hits=2.2 required=5.0
	tests=HTML_MESSAGE,SPF_PASS
X-Spam-Check-By: apache.org
Received-SPF: pass (athena.apache.org: domain of
 prvs=600281c5f7=venkatesh.narla@cerner.com designates 159.140.194.49 as
 permitted sender)
Received: from [159.140.194.49] (HELO xmail02.cernercloud.com)
 (159.140.194.49)
    by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 17 Oct 2013 21:45:52 +0000
Received: from CERNMSGLS5HUB2.cerner.net ([170.71.96.37])
	by xmail02.cernercloud.com (8.14.5/8.14.5) with ESMTP id r9HLjSCs020068
	for <user@hive.apache.org>; Thu, 17 Oct 2013 16:45:31 -0500
Received: from CERNMSGLS5MB5B.cerner.net ([169.254.3.224]) by
 CERNMSGLS5HUB2.cerner.net ([170.71.96.37]) with mapi id 14.02.0318.004; Thu,
 17 Oct 2013 16:44:24 -0500
From: "Narla,Venkatesh" <Venkatesh.Narla@Cerner.com>
To: "user@hive.apache.org" <user@hive.apache.org>
Subject: Performing MULTIPLE DISTINCTS on Columns using Hive
Thread-Topic: Performing MULTIPLE DISTINCTS on Columns using Hive
Thread-Index: AQHOy4IKAr1/UT9qVE6Ve5ikHCOyVQ==
Date: Thu, 17 Oct 2013 21:44:23 +0000
Message-ID: <2609B31FB167234BAA88F490D6FE68EE288A08@CERNMSGLS5MB5B.cerner.net>
Accept-Language: en-US
Content-Language: en-US
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
x-originating-ip: [39.250.140.26]
Content-Type: multipart/alternative;
	boundary="_000_2609B31FB167234BAA88F490D6FE68EE288A08CERNMSGLS5MB5Bcer_"
MIME-Version: 1.0
X-Proofpoint-Virus-Version: vendor=fsecure
 engine=2.50.10432:5.10.8794,1.0.431,0.0.0000
 definitions=2013-10-17_06:2013-10-17,2013-10-17,1970-01-01 signatures=0
X-Virus-Checked: Checked by ClamAV on apache.org

--_000_2609B31FB167234BAA88F490D6FE68EE288A08CERNMSGLS5MB5Bcer_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hello,

I  am using Hive to analyze some data. As part of my hive queries I am tryi=
ng to do Distinct  select on different columns and I am not able to get tha=
t well.

I am performing explode on data as its type is array<struct>. I am not able=
 to


Select DISTINCT (lateral_test.id, lateral_test.person.datetime) from hive_t=
est LATERAL VIEW EXPLODE(key.data) explodedtable as lateral_test  where key=
.id=3D1 and key.prson=3Dtest
And lateral_test.person.datetime=3D12345669


Exception:

FAILED: ParseException line 1:37 missing ) at ',' near '<EOF>'
line 1:84 extraneous input ')' expecting FROM near '<EOF>'


I have also tried this way by modifying my query.


Select DISTINCT (lateral_test.id), DISTINCT(lateral_test.person.datetime) f=
rom hive_test LATERAL VIEW EXPLODE(key.data) explodedtable as lateral_test =
 where key.id=3D1 and key.prson=3Dtest
And lateral_test.person.datetime=3D12345669




Exception:


FAILED: ParseException line 1:40 cannot recognize input near 'DISTINCT' '('=
 'result_data' in select expression

I would appreciate if any body could guide me where I am doing wrong or how=
 I should be able to proceed with that. Thank you for your time.


Regards,
VN

CONFIDENTIALITY NOTICE This message and any included attachments are from C=
erner Corporation and are intended only for the addressee. The information =
contained in this message is confidential and may constitute inside or non-=
public information under international, federal, or state securities laws. =
Unauthorized forwarding, printing, copying, distribution, or use of such in=
formation is strictly prohibited and may be unlawful. If you are not the ad=
dressee, please promptly delete this message and notify the sender of the d=
elivery error by e-mail or you may call Cerner's corporate offices in Kansa=
s City, Missouri, U.S.A at (+1) (816)221-1024.

--_000_2609B31FB167234BAA88F490D6FE68EE288A08CERNMSGLS5MB5Bcer_
Content-Type: text/html; charset="us-ascii"
Content-ID: <3215105CD2BC8447925A3DFE0D7CFF92@cerner.com>
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii">
</head>
<body style=3D"word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-lin=
e-break: after-white-space; color: rgb(0, 0, 0); font-size: 14px; font-fami=
ly: Calibri, sans-serif; ">
<div>Hello,</div>
<div><br>
</div>
<div>I &nbsp;am using Hive to analyze some data. As part of my hive queries=
 I am trying to do Distinct &nbsp;select on different columns and I am not =
able to get that well.&nbsp;</div>
<div><br>
</div>
<div>I am performing explode on data as its type is array&lt;struct&gt;. I =
am not able to&nbsp;</div>
<div><br>
</div>
<div><br>
</div>
<div>Select DISTINCT (lateral_test.id, lateral_test.person.datetime) from h=
ive_test LATERAL VIEW EXPLODE(key.data) exploded<span style=3D"font-style: =
italic; ">table as
</span>lateral_test &nbsp;where key.id=3D1 and key.prson=3Dtest</div>
<div>And lateral_test.person.datetime=3D12345669</div>
<div><br>
</div>
<div><br>
</div>
<div>Exception:</div>
<div><br>
</div>
<div>
<div>FAILED: ParseException line 1:37 missing ) at ',' near '&lt;EOF&gt;'</=
div>
<div>line 1:84 extraneous input ')' expecting FROM near '&lt;EOF&gt;'</div>
</div>
<div><br>
</div>
<div><br>
</div>
<div>I have also tried this way by modifying my query.</div>
<div><br>
</div>
<div><br>
</div>
<div>
<div>Select DISTINCT (lateral_test.id), DISTINCT(lateral_test.person.dateti=
me) from hive_test LATERAL VIEW EXPLODE(key.data) exploded<span style=3D"fo=
nt-style: italic; ">table as&nbsp;</span>lateral_test &nbsp;where key.id=3D=
1 and key.prson=3Dtest</div>
<div>And lateral_test.person.datetime=3D12345669</div>
</div>
<div><br>
</div>
<div><br>
</div>
<div><br>
</div>
<div><br>
</div>
<div>Exception:</div>
<div><br>
</div>
<div><br>
</div>
<div>FAILED: ParseException line 1:40 cannot recognize input near 'DISTINCT=
' '(' 'result_data' in select expression</div>
<div><br>
</div>
<div>I would appreciate if any body could guide me where I am doing wrong o=
r how I should be able to proceed with that. Thank you for your time.</div>
<div><br>
</div>
<div><br>
</div>
<div>Regards,</div>
<div>VN</div>
<div><br>
</div>

<DIV>
CONFIDENTIALITY NOTICE This message and any included attachments are from C=
erner Corporation and are intended only for the addressee. The information =
contained in this message is confidential and may constitute inside or non-=
public information under international, federal, or state securities laws. =
Unauthorized forwarding, printing, copying, distribution, or use of such in=
formation is strictly prohibited and may be unlawful. If you are not the ad=
dressee, please promptly delete this message and notify the sender of the d=
elivery error by e-mail or you may call Cerner&#39;s corporate offices in K=
ansas City, Missouri, U.S.A at (+1) (816)221-1024.<BR>
</DIV></body>
</html>

--_000_2609B31FB167234BAA88F490D6FE68EE288A08CERNMSGLS5MB5Bcer_--