It is working perfectly after changing from union to union all. Thanks to all.
2011/2/19 Jov <zhao6...@gmail.com> > 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....@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 <zhao6...@gmail.com> > > > >> hive0.4.1 do not support union,only support union all > >> 在 2011-2-18 下午3:12，"sangeetha s" <sangee....@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 > -- Regards, Sangita
