Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id 2BA6A200B25 for ; Wed, 8 Jun 2016 17:25:10 +0200 (CEST) Received: by cust-asf.ponee.io (Postfix) id 2A8AB160A2E; Wed, 8 Jun 2016 15:25:10 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id F38A0160A0E for ; Wed, 8 Jun 2016 17:25:08 +0200 (CEST) Received: (qmail 23130 invoked by uid 500); 8 Jun 2016 15:25:07 -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 23120 invoked by uid 99); 8 Jun 2016 15:25:07 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd4-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 08 Jun 2016 15:25:07 +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 3CE5DC0E1A for ; Wed, 8 Jun 2016 15:25:07 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd4-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: -0.248 X-Spam-Level: X-Spam-Status: No, score=-0.248 tagged_above=-999 required=6.31 tests=[DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, HTML_MESSAGE=2, RCVD_IN_DNSWL_LOW=-0.7, RCVD_IN_MSPIKE_H3=-0.01, RCVD_IN_MSPIKE_WL=-0.01, RP_MATCHES_RCVD=-1.426, SPF_HELO_PASS=-0.001, SPF_PASS=-0.001] autolearn=disabled Authentication-Results: spamd4-us-west.apache.org (amavisd-new); dkim=pass (2048-bit key) header.d=citi.com Received: from mx2-lw-us.apache.org ([10.40.0.8]) by localhost (spamd4-us-west.apache.org [10.40.0.11]) (amavisd-new, port 10024) with ESMTP id 1pc1aVXRcYYL for ; Wed, 8 Jun 2016 15:25:05 +0000 (UTC) Received: from mx-b.mail.citi.com (mx-b.mail.citi.com [67.231.153.94]) by mx2-lw-us.apache.org (ASF Mail Server at mx2-lw-us.apache.org) with ESMTPS id CFDE25F232 for ; Wed, 8 Jun 2016 15:25:04 +0000 (UTC) Received: from pps.filterd (m0030158.ppops.net [127.0.0.1]) by mx0b-00123c02.pphosted.com (8.16.0.11/8.16.0.11) with SMTP id u58FNEOn013384; Wed, 8 Jun 2016 15:25:02 GMT DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=citi.com; h=from : to : cc : subject : date : message-id : references : in-reply-to : content-type : mime-version; s=maila; bh=sUMPju2b14zFCSj2jcc9CY2hCwELbXZqCTQNSUG4FdE=; b=lFUKc7InIHPkYfh6GWKBXG010QoprtRkaTO2xTshxEuzLl8MOR+PhzbYh5TzVwhgYCWN HoEHpUdLqx4VFWBIS+Aq3D8rAFpV6hTZqd91fKjk1zNZ0U6OU6G/sasPl6lnbQhL6QfL RNLhHt5Yb4j7pe6879TsML2YHgw1Vs8wkhAlB/oeK4z6LezNolKVL90Fea+bkccwhkSW JUkZjaimzS02F6JKHPyyANN4tsnV8Y3SBuvZ+jPge7vUEVZjPFKevItypf9bycKHEFcB XNLcX880J1XmGBKuCh2YcjJHs61yoQUSqLIRoGkIL2N8L2aC7Gstdk5x64R6znGlDCgR ZQ== Received: from mail.citigroup.com (smtpoutbound.citigroup.com [192.193.158.16]) by mx0b-00123c02.pphosted.com with ESMTP id 23e3c7xtm5-1 (version=TLSv1.2 cipher=AES256-SHA bits=256 verify=NOT); Wed, 08 Jun 2016 15:25:02 +0000 Received: from imbhub-sw35.nam.nsroot.net (namdlpdimpgt06.nam.nsroot.net [169.193.226.15]) by smtpinbound.citigroup.com (Sentrion-MTA-4.3.1/Sentrion-MTA-4.2.2) with ESMTP id u58FOhGa020165; Wed, 8 Jun 2016 15:25:02 GMT Received: from EXGTIHT04.nam.nsroot.net (EXGTIHT04.nam.nsroot.net [169.171.127.31]) by imbhub-sw35.nam.nsroot.net (Sentrion-MTA-4.3.1/Sentrion-MTA-4.2.2) with ESMTP id u58FOsTI012495; Wed, 8 Jun 2016 15:25:00 GMT Received: from EXGTRHT06.nam.nsroot.net (169.171.82.51) by EXGTIHT04.nam.nsroot.net (169.171.127.31) with Microsoft SMTP Server (TLS) id 14.3.248.2; Wed, 8 Jun 2016 10:24:50 -0500 Received: from EXGTMB24.nam.nsroot.net ([169.171.127.39]) by EXGTRHT06.nam.nsroot.net ([169.171.82.51]) with mapi id 14.03.0248.002; Wed, 8 Jun 2016 10:24:49 -0500 From: "Lunagariya, Dhaval " To: "'user@hive.apache.org'" CC: "'er.dcpatel@gmail.com'" Subject: RE: Need Your Inputs For Below Scenario Thread-Topic: Need Your Inputs For Below Scenario Thread-Index: AdHBgGKKclqRmTxdSkij6fR1NyfK7gAGVD7Q Date: Wed, 8 Jun 2016 15:24:49 +0000 Message-ID: <07C0E546FCCBBA49BA208B3CB4BA7C6D36602726@EXGTMB24.nam.nsroot.net> References: <07C0E546FCCBBA49BA208B3CB4BA7C6D3660265D@EXGTMB24.nam.nsroot.net> In-Reply-To: <07C0E546FCCBBA49BA208B3CB4BA7C6D3660265D@EXGTMB24.nam.nsroot.net> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [169.171.82.19] x-wiganss: 01000000010018EXGTRHT06.nam.nsroot.net ID0042<07C0E546FCCBBA49BA208B3CB4BA7C6D36602726@EXGTMB24.nam.nsroot.net> Content-Type: multipart/alternative; boundary="_000_07C0E546FCCBBA49BA208B3CB4BA7C6D36602726EXGTMB24namnsro_" MIME-Version: 1.0 X-CFilter-Loop: Reflected X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10432:,, definitions=2016-06-08_05:,, signatures=0 archived-at: Wed, 08 Jun 2016 15:25:10 -0000 --_000_07C0E546FCCBBA49BA208B3CB4BA7C6D36602726EXGTMB24namnsro_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Here Table2 is very large table and contains lakhs of rows. From: Lunagariya, Dhaval [CCC-OT] Sent: Wednesday, June 08, 2016 5:52 PM To: user@hive.apache.org Subject: Need Your Inputs For Below Scenario Hey folks, Need your help. Input Table1: Column1 Column2 Column3 Column4 Column5 Column6 Column7 A B1 B2 B3(NULL) B4 B5 B6 Input Table2: Column1 Column2 B1 D1 B2 D2 B3 D3 B4 D4 B5 D5 B6 D6 Output: Column1 Column2 Column3 A B1 D1 A B2 D2 A B4 D4 A B5 D5 A B6 D6 Here B3 is skipped because B3 is NULL. What is the efficient way to get above result using Hive? Regards, Dhaval --_000_07C0E546FCCBBA49BA208B3CB4BA7C6D36602726EXGTMB24namnsro_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Here Table2 is very la= rge table and contains lakhs of rows.

 

From: Lunagari= ya, Dhaval [CCC-OT]
Sent: Wednesday, June 08, 2016 5:52 PM
To: user@hive.apache.org
Subject: Need Your Inputs For Below Scenario

 

Hey folks,

 

Need your help.

 

Input Table1:

 

Column1

Column2

Column3

Column4

Column5

Column6

Column7

A

B1

B2

B3(NULL)

B4

B5

B6

 

 

Input Table2:

Column1

Column2

B1

D1

B2

D2

B3

D3

B4

D4

B5

D5

B6

D6

 

 

Output:

Column1

Column2

Column3

A

B1

D1

A

B2

D2

A

B4

D4

A

B5

D5

A

B6

D6

 

Here B3 is skipped because B3 is NULL.

 

What is the efficient way to get above result using = Hive?

 

 

 

Regards,

Dhaval=

 

--_000_07C0E546FCCBBA49BA208B3CB4BA7C6D36602726EXGTMB24namnsro_--