Return-Path: Delivered-To: apmail-hive-user-archive@www.apache.org Received: (qmail 55793 invoked from network); 19 Feb 2011 10:58:54 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3) by minotaur.apache.org with SMTP; 19 Feb 2011 10:58:54 -0000 Received: (qmail 80439 invoked by uid 500); 19 Feb 2011 10:58:54 -0000 Delivered-To: apmail-hive-user-archive@hive.apache.org Received: (qmail 80157 invoked by uid 500); 19 Feb 2011 10:58:51 -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 80146 invoked by uid 99); 19 Feb 2011 10:58:50 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 19 Feb 2011 10:58:50 +0000 X-ASF-Spam-Status: No, hits=3.7 required=5.0 tests=FREEMAIL_ENVFROM_END_DIGIT,FREEMAIL_FROM,HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS,T_TO_NO_BRKTS_FREEMAIL X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of zhao6014@gmail.com designates 209.85.213.48 as permitted sender) Received: from [209.85.213.48] (HELO mail-yw0-f48.google.com) (209.85.213.48) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 19 Feb 2011 10:58:42 +0000 Received: by ywc21 with SMTP id 21so2118636ywc.35 for ; Sat, 19 Feb 2011 02:58:21 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:in-reply-to:references:date :message-id:subject:from:to:content-type; bh=jf3MmEUXglHMvcKZyPwDmlbJpHPi1Yrxx9ANA78bnsw=; b=VejP3ofZ7q2NmEcCIFDcSomQrZuT1y6diRY4ffuIrrgHj2bMlaEG5eWVAMEIthALEt a1kjtILApJIGpxH9RKXQwfzwDhvR+AyS0QrazFaw+sXVsqnJF64mCdT/C1+/GUS4nHp6 m8UtYmEb+cgIK3BGdPYSmlPai+CpztlmfQ/do= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; b=hCeYJGLBdwL6SwEouwUjZpBYGrAzgfQpjs2c65xY7eCiTKcuEUBq3HqaFIFz2TPf9p aqpy0jxU03vMtoismDOXL7ASLWQhg0htOu30PbYi27aqvOzluc2/Jc8zoWZt74jePxec ecJfgb2svCZaYejkRtLJ+iFlHFa9KnMxgxLeM= MIME-Version: 1.0 Received: by 10.150.54.20 with SMTP id c20mr2324067yba.55.1298113101047; Sat, 19 Feb 2011 02:58:21 -0800 (PST) Received: by 10.147.168.8 with HTTP; Sat, 19 Feb 2011 02:58:20 -0800 (PST) Received: by 10.147.168.8 with HTTP; Sat, 19 Feb 2011 02:58:20 -0800 (PST) In-Reply-To: References: Date: Sat, 19 Feb 2011 18:58:20 +0800 Message-ID: Subject: Re: problem while performing union on twotables From: Jov To: user@hive.apache.org Content-Type: multipart/alternative; boundary=000e0cd3060c442914049ca08257 X-Virus-Checked: Checked by ClamAV on apache.org --000e0cd3060c442914049ca08257 Content-Type: text/plain; charset=GB2312 Content-Transfer-Encoding: quoted-printable if you want union,you should do it as this : select distinct ... from subquery1 union all subquery2 so,union =3D union distinct =D4=DA 2011-2-19 =C9=CF=CE=E712:11=A3=AC"sangeetha s" =D0=B4=B5=C0=A3=BA > Hi, > > Thanks Jov and Ajo > > Changing from Union to Union all solved the issue. But do we need to specify > all the fields in the sub query? > Actually I had used the following query. > > INSERT OVERWRITE TABLE tab3 SELECT t3.col1,t3col2 FROM (SELECT id AS > col1,name AS col2 FROM tab1 UNION ALL SELECT id AS col1,name as col2 FROM > tab2)t3; > > The above query overwrite the result in the table tab3. If I am doing suc= h > an operation in a table that has many fields,Is it necessary to specify all > the column names as a part of the sub query? If not, Will the query *SELECT > t3.* FROM (SELECT * FROM tab1 UNION ALL SELECT * FROM tab2)t3;* work?As I > am using this to update my table which has many columns,I need this. Well,I > will check it first and get back to you. > > Thank you, > > > > 2011/2/18 Jov > >> hive0.4.1 do not support union,only support union all >> =D4=DA 2011-2-18 =CF=C2=CE=E73:12=A3=AC"sangeetha s" =D0=B4=B5=C0=A3=BA >> >> > >> > Hi, >> > >> > I am trying to perform union of two tables which are having identical >> > schemas and distinct data.There are two tables 'oldtable' and 'newtable'. >> > The old table contains the information of old users and the new table >> will >> > conatin the information of new user. I am trying to update the new entry >> in >> > the old table using the following query. >> > >> > INSERT OVERWRITE TABLE oldtable SELECT * FROM (SELECT * FROM oldtable >> UNION >> > select * from newtable); >> > >> > executing the above query results, >> > FAILED:Parse Error:line 1.68 mismatched input 'select' expecting ALL i= n >> > query operator. >> > >> > I am currently using Hadoop verso 0.20.2 and Hive 0.4.1 from apache >> > disctribution. Will Hive 0.4.1 support UNION and UNION ALL queries? Is >> the >> > above problem is dueto the Hive version or due to a wrong query? Also = I >> am >> > not using any partitions in these tables and the table format is >> TextFile. >> > >> > Any pointers in this regard would be highly helpful. >> > >> > -- >> > >> > >> > >> > Regards, >> > Sangita >> > >> > >> > -- >> > >> > > > > -- > > > > Regards, > Sangita --000e0cd3060c442914049ca08257 Content-Type: text/html; charset=GB2312 Content-Transfer-Encoding: quoted-printable

if you want union,you should do it as this :

select distinct ... from
subquery1
union all
subquery2

so,union =3D union distinct

=D4=DA 2011-2-19 =C9=CF=CE=E712:11=A3=AC"sa= ngeetha s" <sangee.sha@gmai= l.com>=D0=B4=B5=C0=A3=BA
> Hi,
> > Thanks Jov and Ajo
>
> Changing from Union to Union all solved the issue. But do we need to s= pecify
> all the fields in the sub query?
> Actually I had use= d the following query.
>
> INSERT OVERWRITE TABLE tab3 SELECT = t3.col1,t3col2 FROM (SELECT id AS
> col1,name AS col2 FROM tab1 UNION ALL SELECT id AS col1,name as col2 F= ROM
> tab2)t3;
>
> The above query overwrite the result = in the table tab3. If I am doing such
> an operation in a table that = has many fields,Is it necessary to specify all
> the column names as a part of the sub query? If not, Will the query *S= ELECT
> t3.* FROM (SELECT * FROM tab1 UNION ALL SELECT * FROM tab2)t3= ;* work?As I
> am using this to update my table which has many colum= ns,I need this. Well,I
> will check it first and get back to you.
>
> Thank you,>
>
>
> 2011/2/18 Jov <zhao6014@gmail.com>
>
>> hive0.4.1 do= not support union,only support union all
>> =D4=DA 2011-2-18 =CF=C2=CE=E73:12=A3=AC"sangeetha s" <= ;sangee.sha@gmail.com>=D0=B4= =B5=C0=A3=BA
>>
>> >
>> > Hi,
>> = >
>> > I am trying to perform union of two tables which are = having identical
>> > schemas and distinct data.There are two tables 'oldtable&= #39; and 'newtable'.
>> > The old table contains the in= formation of old users and the new table
>> will
>> > = conatin the information of new user. I am trying to update the new entry >> in
>> > the old table using the following query.
&g= t;> >
>> > INSERT OVERWRITE TABLE oldtable SELECT * FROM = (SELECT * FROM oldtable
>> UNION
>> > select * from ne= wtable);
>> >
>> > executing the above query results,
>&g= t; > FAILED:Parse Error:line 1.68 mismatched input 'select' expe= cting ALL in
>> > query operator.
>> >
>> = > I am currently using Hadoop verso 0.20.2 and Hive 0.4.1 from apache >> > disctribution. Will Hive 0.4.1 support UNION and UNION ALL qu= eries? Is
>> the
>> > above problem is dueto the Hive = version or due to a wrong query? Also I
>> am
>> > not= using any partitions in these tables and the table format is
>> TextFile.
>> >
>> > Any pointers in this r= egard would be highly helpful.
>> >
>> > --
>= > >
>> >
>> >
>> > Regards,
>> > Sangita
>> >
>> >
>> > --=
>> >
>>
>
>
>
> --
>= ;
>
>
> Regards,
> Sangita
--000e0cd3060c442914049ca08257--