hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jov <zhao6...@gmail.com>
Subject Re: problem while performing union on twotables
Date Sat, 19 Feb 2011 10:58:20 GMT
if you want union,you should do it as this :

select distinct ... from
subquery1
union all
subquery2

so,union = union distinct
在 2011-2-19 上午12:11,"sangeetha s" <sangee.sha@gmail.com>写道:
> 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 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
*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 <zhao6014@gmail.com>
>
>> hive0.4.1 do not support union,only support union all
>> 在 2011-2-18 下午3:12,"sangeetha s" <sangee.sha@gmail.com>写道:
>>
>> >
>> > 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 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 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

Mime
View raw message