You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Sowjanya Kakarala <so...@agrible.com> on 2018/08/06 16:18:26 UTC
Subqueries two tables to one in Hive
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
Re: Subqueries two tables to one in Hive
Posted by Sowjanya Kakarala <so...@agrible.com>.
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 <pi...@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 <so...@agrible.com> wrote:
On Mon, Aug 6, 2018 at 11:18 AM, Sowjanya Kakarala <so...@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
>