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
