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 48B04109D8 for ; Wed, 31 Jul 2013 04:35:31 +0000 (UTC) Received: (qmail 77757 invoked by uid 500); 31 Jul 2013 04:35:29 -0000 Delivered-To: apmail-hive-user-archive@hive.apache.org Received: (qmail 77414 invoked by uid 500); 31 Jul 2013 04:35: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 77406 invoked by uid 99); 31 Jul 2013 04:35:23 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 31 Jul 2013 04:35:23 +0000 X-ASF-Spam-Status: No, hits=2.2 required=5.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_NONE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of liusand@hotmail.com designates 65.54.190.139 as permitted sender) Received: from [65.54.190.139] (HELO bay0-omc3-s1.bay0.hotmail.com) (65.54.190.139) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 31 Jul 2013 04:35:19 +0000 Received: from BAY177-W5 ([65.54.190.189]) by bay0-omc3-s1.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.4675); Tue, 30 Jul 2013 21:34:58 -0700 X-TMN: [T/jgc71Ohoneq3l1ZoLWchyPZubp2VAB] X-Originating-Email: [liusand@hotmail.com] Message-ID: Content-Type: multipart/alternative; boundary="_7b8613be-2569-4887-a26d-a506e439a6b5_" From: Sha Liu To: "user@hive.apache.org" Subject: RE: Multiple Insert with Where Clauses Date: Tue, 30 Jul 2013 21:34:58 -0700 Importance: Normal In-Reply-To: References: ,,, MIME-Version: 1.0 X-OriginalArrivalTime: 31 Jul 2013 04:34:58.0516 (UTC) FILETIME=[50370140:01CE8DA7] X-Virus-Checked: Checked by ClamAV on apache.org --_7b8613be-2569-4887-a26d-a506e439a6b5_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Doesn't INSERT INTO do what you said? I'm not sure I understand "inserting = a few records into a table". Anyway here the problem seems different to me. For my cases these where cla= uses for multiple inserts seem not effective=2C while Hive doesn't complain= about that. -Sha Date: Tue=2C 30 Jul 2013 21:06:22 -0700 Subject: Re: Multiple Insert with Where Clauses From: bruderman@radiumone.com To: user@hive.apache.org Hive doesn't support inserting a few records into a table. You will need to= write a query to union your select and then insert. IF you can partition= =2C then you can insert a whole partition at a time instead of the table.= =0A= Thanks=2CBrad On Tue=2C Jul 30=2C 2013 at 9:04 PM=2C Sha Liu wrote: =0A= =0A= =0A= =0A= Yes for the example you gave=2C it works. It even works when there is a sin= gle insert under the from clause=2C but there there are multiple inserts=2C= the where clauses seem no longer effective. =0A= Date: Tue=2C 30 Jul 2013 20:29:19 -0700 Subject: Re: Multiple Insert with Where Clauses From: bruderman@radiumone.com To: user@hive.apache.org=0A= Have you simply tried INSERT OVERWRITE TABLE destination SELECT col1=2C col2=2C col3FROM sourceWH= ERE col4 =3D 'abc' =0A= Thanks!=0A= On Tue=2C Jul 30=2C 2013 at 8:25 PM=2C Sha Liu wrote: =0A= =0A= =0A= =0A= Hi Hive Gurus=2C When using the Hive extension of multiple inserts=2C can we add Where claus= es for each Select statement=2C like the following? FROM ...=0A= =0A= INSERT OVERWRITE TABLE ... SELECT col1=2C col2=2C col3 WHERE col4=3D'= abc'INSERT OVERWRITE TABLE ... SELECT col1=2C col4=2C col2 WHERE col3= =3D'xyz'=0A= =0A= The underlined parts didn't cause any errors=2C but they didn't seem to be = effective either (I'm using Hive 0.9). Note that the columns used in the Wh= ere clauses are not among the selected ones=2C but I'm not sure if that is = important. Is this kind of operations supported?=0A= =0A= Thanks=2CSha Liu =0A= =0A= = --_7b8613be-2569-4887-a26d-a506e439a6b5_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Doesn't INSERT INTO do what you = said? I'm not sure I understand "inserting a few records into a table".
Anyway here the problem seems different to me. For my cases= these where clauses for multiple inserts seem not effective=2C while Hive = doesn't complain about that.

-Sha

Date: Tue=2C 30 Jul 2013 21:06:22 -0700
Subject: Re:= Multiple Insert with Where Clauses
From: bruderman@radiumone.com
To:= user@hive.apache.org

Hive doesn't support insertin= g a few records into a table. You will need to write a query to union your = select and then insert. IF you can partition=2C then you can insert a whole= partition at a time instead of the table.
=0A=
Thanks=2C
Brad


On Tue=2C Jul 30=2C 2013 at 9:0= 4 PM=2C Sha Liu <=3Bliusand@hotmail.com>=3B wrote:
=0A=
=0A= =0A= =0A=
Yes for the example you gave=2C it works. It even wor= ks when there is a single insert under the from clause=2C but there there a= re multiple inserts=2C the where clauses seem no longer effective.

<= div>=0A=
Date: Tue=2C 30 Jul 2013 20:29:19 -0700
Subject: Re: Multiple Insert= with Where Clauses
From: bruderman@radiumone.com
To: user@hive.apache.org
=0A=


Have you simply tried

INSERT OVERWRITE TABLE destination =3B
SELECT col1= =2C col2=2C col3
FROM source
WHERE col4 =3D 'abc'
=

=0A=
Thanks!
=0A=



On Tue=2C Jul 30=2C 2013 at 8:25 PM= =2C Sha Liu <=3Bliusand@hotmail.com>=3B wrote:
=0A=
=0A= =0A= =0A=
Hi Hive Gurus=2C

When using the Hi= ve extension of multiple inserts=2C can we add Where clauses for each Selec= t statement=2C like the following?

FROM ...
<= div>=0A= =0A= INSERT OVERWRITE TABLE ...
 =3B  =3B SELECT col1=2C col2= =2C col3
 =3B  =3B WHERE col4=3D'abc'
=
INSERT OVERWRITE TABLE ...
 =3B  =3B SELECT col1=2C = col4=2C col2
 =3B  =3B WHERE col3=3D'xyz'=0A= =0A=

The underlined parts didn't cause any err= ors=2C but they didn't seem to be effective either (I'm using Hive 0.9).&nb= sp=3BNote that the columns used in the Wh= ere clauses are not among the selected ones=2C but I'm not sure if that is = important. =3BIs this kind of = operations supported?
=0A= =0A=

Thanks=2C
Sha Liu
=0A=

=0A=

= --_7b8613be-2569-4887-a26d-a506e439a6b5_--