Thank you for replying. You are right, I just have to set these guys in: set hive.strict.checks.cartesian.product=false;
set hive.mapred.mode=nonstrict; *From: *Furcy Pin <pin.fu...@gmail.com> *Date: *Tuesday, August 7, 2018 at 2:25 AM *To: *Sowjanya Kakarala *Subject: *[EXT] Re: Subqueries two tables to one in Hive Hi, this looks like a simple generic SQL question, you should probably look at a SQL tutorial before asking them here... BTW, what you're looking for is probably this: https://www.w3schools.com/sql/sql_join.asp <https://urldefense.proofpoint.com/v2/url?u=https-3A__www.w3schools.com_sql_sql-5Fjoin.asp&d=DwMFaQ&c=zgHsTqBQkGAdNuoPRxUzSQ&r=orisX-Ua3ov0KPzZaKyP6RYptE2HyOsdKqRZuToKenU&m=wtmyUe-ity4jXduO6gbAgLwu2WOBD7VQ-VLOFe54MOM&s=vGJ_vhZvDB9rI7rr3mLiQ4EqJEIz5INmhJN1nqLE-xE&e=> Regards, Furcy On Mon, 6 Aug 2018 at 18:18, Sowjanya Kakarala <sowja...@agrible.com> wrote: On Mon, Aug 6, 2018 at 11:18 AM, Sowjanya Kakarala <sowja...@agrible.com> wrote: > Hi Everyone, > > > > I am trying to insert data from 2tables to one table as separate columns. > > Example: > > Table1 as A: > > Id > > Data > > time_stamp > > 1 > > 0.1 > > 2018-01-01 > > 2 > > 0.2 > > 2018-01-01 > > 3 > > 0.3 > > 2018-01-02 > > Table2 as B > > Id > > Data > > time_stamp > > 1 > > 1.1 > > 2018-01-01 > > 2 > > 2.2 > > 2018-01-01 > > 3 > > 1.3 > > 2018-01-02 > > Now I am looking to merge these two tables into one > > Table 3 as one: > > id > > A > > B > > time_stamp > > 1 > > 0.1 > > 1.1 > > 2018-01-01 > > 2 > > 0.2 > > 2.2 > > 2018-01-01 > > 3 > > 0.3 > > 1.3 > > 2018-01-02 > > > > Is this possible in Hive? > > > > I tried: > > select * from (select * from (select a.id,a.data,a.time_stamp from > db.tbl1 a left join db.one b on a.id = b.id limit 1) A, select * from > (select a.id,a.data,a.time_stamp from db.tbl2 a left join db.one b on a.id > = b.id limit 1) B)a ; > > > ERROR: `FAILED: ParseException line 1:15 cannot recognize input near '(' > 'select' '*' in joinSource` > > > > but when I use union/union all it works as a single column > > > > like > > 1 > > 0.1 > > 2018-01-01 > > 2 > > 0.2 > > 2018-01-01 > > 3 > > 0.3 > > 2018-01-02 > > 1 > > 1.1 > > 2018-01-01 > > 2 > > 2.2 > > 2018-01-01 > > 3 > > 1.3 > > 2018-01-02 > > also tried: > > insert into table mono.all_in_one select (select a.cell_id,a.data as > tmax,a.time_stamp from mono.daily_tmax_half_deg a left join mono.all_in_one > b on a.cell_id = b.cell_id union all select c.cell_id,c.data,c.time_stamp > from mono.daily_tmin_half_deg c left join mono.all_in_one b on c.cell_id = > b.cell_id)a; > > > ERROR: FAILED: CalciteSubquerySemanticException [Error 10249]: > Unsupported SubQuery Expression Currently SubQuery expressions are only > allowed as Where and Having Clause predicates > > So is there a way that I can achieve the way I am looking for? > > Any suggestions? > > > > Thanks > > Sowjanya >
