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
