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/05/15 14:37:37 UTC
Combining hive tables as one query
Hi all,
Is there a way in hive that different tables data, can be read as in a
single query?
example:
(something like)
select a,b from (select col1 from tbl1)a , (select col1 from tb2)b);
output as :
a b
0.1 0.2
Any help is appreciated.
Thanks
Sowjanya
RE: Combining hive tables as one query
Posted by "Grim, Paul" <pg...@soleocommunications.com>.
Something like this perhaps? (forgive my shorthand)
with
a as (select c1 from tb1),
b as (select c2 from tb2),
c as (select c3 from tb3)
select a.c1, b.c2, c.c3
from a
JOIN b ON b.c2b = a.c1b
JOIN c ON c.c3c = a.c1c;
Paul
From: Sowjanya Kakarala <so...@agrible.com>
Sent: Tuesday, May 15, 2018 12:34 PM
To: user@hive.apache.org
Subject: Re: Combining hive tables as one query
ok. but in my usecase join's/union's wont help.
Here is an example of my usecase from postgres, which I have to do it in similar way for hive.
with recursive
a as (select col from tb1),
b as (select col from tb2),
c as (select col from tb3)
select a,b,c from a,b,c;
which output's me in a dataframe which i am writing to a csv and it looks like
a b c
0.1 0.2 0.3
where hive is not supporting `with rec` in same functionality as in postgres and views are also not helping here.
On Tue, May 15, 2018 at 11:19 AM, Alan Gates <al...@gmail.com>> wrote:
In general this is done using joins, as in all SQL engines. A google search on "intro to SQL joins" will suggest a number of resources, for example https://www.essentialsql.com/get-ready-to-learn-sql-12-introduction-to-database-joins/
Alan.
On Tue, May 15, 2018 at 7:37 AM, Sowjanya Kakarala <so...@agrible.com>> wrote:
Hi all,
Is there a way in hive that different tables data, can be read as in a single query?
example:
(something like)
select a,b from (select col1 from tbl1)a , (select col1 from tb2)b);
output as :
a b
0.1 0.2
Any help is appreciated.
Thanks
Sowjanya
--
Sowjanya Kakarala
Infrastructure Software Engineer
Agrible, Inc. | sowjanya@agrible.com <ma...@agrible.com> | 217-848-1128
2021 S. First Street, Suite 201, Champaign, IL 61820<https://maps.google.com/?q=2021+S.+First+Street,+Suite+201,+Champaign,+IL+61820&entry=gmail&source=g>
Agrible.com<http://agrible.com/> | facebook<https://www.facebook.com/Agrible> | youtube<https://www.youtube.com/c/AgribleInc_TheInsightToDecide> | twitter<https://twitter.com/Agribleinc>
[Image removed by sender. Agrible_Logo_Email_Signature.jpg]
Re: Combining hive tables as one query
Posted by Sowjanya Kakarala <so...@agrible.com>.
Thank you all for putting light here.
The problem is I was trying to copy the same way which I was doing with
postgres(like with recursive kind thing) and hive doesnt support that.
1. As you all said, I tried using UNION/UNIONALL which is appending the
rows but not creating it as a new column.
for ex:
select * from (select data from tbl1 limit 1) as a UNION select * from
(select data from tb2 limit 1)as b;
which Appended the output as
1.1
2.1
2. I do not have common keys to use JOINS and it is not what I want.
As I am looking for fetching all records from one column from all tables.
3. Rownumber method did not work for my scenario.
On Tue, May 15, 2018 at 1:19 PM, balajee venkatesh <
balajee.venkatesh1667@gmail.com> wrote:
> It's way too easy to perform file write through hive itself then
> unnecessary usage of Excel and power operations. Please refer the provided
> link for your reference.
>
> https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML
>
> Thanks,
> Balajee
>
> On Tue 15 May, 2018, 11:38 PM Grim, Paul, <pg...@soleocommunications.com>
> wrote:
>
>> This probably isn’t what you want to do since it requires an extra step
>> outside of Hive, but have you tried using UNION ALL to append all the data
>> together (in Hive), then unpivoting it in Excel using Power Query? So your
>> Hive query would output the data like this:
>>
>>
>>
>> tb1 1.1 1.2
>>
>> tb1 1.3 1.4
>>
>> tb2 2.1 2.2
>>
>> tb2 2.3 2.4
>>
>> tb3 3.1 3.2
>>
>> tb3 3.3 3.4
>>
>> etc.
>>
>>
>>
>> Then you could unpivot it in Excel Power Query to become like this:
>>
>>
>>
>> tb1 tb2 tb3
>>
>> 1.1 2.1 3.1
>>
>> 1.2 2.2 3.2
>>
>> 1.3 2.3 3.3
>>
>> 1.4 2.4 3.4
>>
>>
>>
>> You could of course then resave it in .csv format. But if the entire
>> process needs to be automated within Hive, then my Hive query skills are
>> not quite that advanced yet (I’ve only been using Hive for about a month).
>>
>>
>>
>> Paul
>>
>>
>>
>>
>>
>> *From:* Sowjanya Kakarala <so...@agrible.com>
>> *Sent:* Tuesday, May 15, 2018 1:33 PM
>> *To:* user@hive.apache.org
>> *Subject:* Re: Combining hive tables as one query
>>
>>
>>
>> I am trying to fetch data from hive for 8tables at a time and save it in
>> a csv, so that our pipeline would read that csv which had 8tables data like:
>>
>>
>>
>> tb1 tb2 tb3 tb4 tb5 tb6 tb7 tb8
>>
>> 0.1 1.1 2.1 -1.1 -0.1 0.1 0.2 3.2
>>
>> 1.2 0.4 4.1 -2.1 -0.5 0.2 0.3 6.2
>>
>> and so on....
>>
>>
>>
>> If I fetch one table at a time it will be hard to append all the 8 tables
>> data in one csv(not sure if we can do that and its time consuming).
>>
>>
>>
>> On Tue, May 15, 2018 at 12:20 PM, Alan Gates <al...@gmail.com>
>> wrote:
>>
>> You are correct that Hive does not support "with recursive". A few more
>> details of what you are trying to do would be helpful, since it's not clear
>> why you need the iteration provided by "with recursive". If you really
>> need the iteration I don't think Hive can do what you want.
>>
>>
>>
>> Alan.
>>
>>
>>
>> On Tue, May 15, 2018 at 9:34 AM, Sowjanya Kakarala <so...@agrible.com>
>> wrote:
>>
>> ok. but in my usecase join's/union's wont help.
>>
>> Here is an example of my usecase from postgres, which I have to do it in
>> similar way for hive.
>>
>> with recursive
>>
>> a as (select col from tb1),
>>
>> b as (select col from tb2),
>>
>> c as (select col from tb3)
>>
>>
>>
>> select a,b,c from a,b,c;
>>
>>
>>
>> which output's me in a dataframe which i am writing to a csv and it looks
>> like
>>
>> a b c
>>
>> 0.1 0.2 0.3
>>
>>
>>
>> where hive is not supporting `with rec` in same functionality as in
>> postgres and views are also not helping here.
>>
>>
>>
>> On Tue, May 15, 2018 at 11:19 AM, Alan Gates <al...@gmail.com>
>> wrote:
>>
>> In general this is done using joins, as in all SQL engines. A google
>> search on "intro to SQL joins" will suggest a number of resources, for
>> example https://www.essentialsql.com/get-ready-to-learn-sql-
>> 12-introduction-to-database-joins/
>>
>>
>>
>> Alan.
>>
>>
>>
>> On Tue, May 15, 2018 at 7:37 AM, Sowjanya Kakarala <so...@agrible.com>
>> wrote:
>>
>> Hi all,
>>
>>
>>
>> Is there a way in hive that different tables data, can be read as in a
>> single query?
>>
>>
>>
>> example:
>>
>> (something like)
>>
>> select a,b from (select col1 from tbl1)a , (select col1 from tb2)b);
>>
>>
>>
>> output as :
>>
>> a b
>>
>> 0.1 0.2
>>
>>
>>
>> Any help is appreciated.
>>
>>
>>
>> Thanks
>>
>> Sowjanya
>>
>>
>>
>>
>>
>>
>>
>> --
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> --
>>
>
--
Sowjanya Kakarala
Infrastructure Software Engineer
Agrible, Inc. | sowjanya@agrible.com | 217-848-1128
2021 S. First Street, Suite 201, Champaign, IL 61820
<https://maps.google.com/?q=2021+S.+First+Street,+Suite+201,+Champaign,+IL+61820&entry=gmail&source=g>
Agrible.com <http://agrible.com/> | facebook
<https://www.facebook.com/Agrible> | youtube
<https://www.youtube.com/c/AgribleInc_TheInsightToDecide> | twitter
<https://twitter.com/Agribleinc>
[image: Agrible_Logo_Email_Signature.jpg]
Re: Combining hive tables as one query
Posted by balajee venkatesh <ba...@gmail.com>.
It's way too easy to perform file write through hive itself then
unnecessary usage of Excel and power operations. Please refer the provided
link for your reference.
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML
Thanks,
Balajee
On Tue 15 May, 2018, 11:38 PM Grim, Paul, <pg...@soleocommunications.com>
wrote:
> This probably isn’t what you want to do since it requires an extra step
> outside of Hive, but have you tried using UNION ALL to append all the data
> together (in Hive), then unpivoting it in Excel using Power Query? So your
> Hive query would output the data like this:
>
>
>
> tb1 1.1 1.2
>
> tb1 1.3 1.4
>
> tb2 2.1 2.2
>
> tb2 2.3 2.4
>
> tb3 3.1 3.2
>
> tb3 3.3 3.4
>
> etc.
>
>
>
> Then you could unpivot it in Excel Power Query to become like this:
>
>
>
> tb1 tb2 tb3
>
> 1.1 2.1 3.1
>
> 1.2 2.2 3.2
>
> 1.3 2.3 3.3
>
> 1.4 2.4 3.4
>
>
>
> You could of course then resave it in .csv format. But if the entire
> process needs to be automated within Hive, then my Hive query skills are
> not quite that advanced yet (I’ve only been using Hive for about a month).
>
>
>
> Paul
>
>
>
>
>
> *From:* Sowjanya Kakarala <so...@agrible.com>
> *Sent:* Tuesday, May 15, 2018 1:33 PM
> *To:* user@hive.apache.org
> *Subject:* Re: Combining hive tables as one query
>
>
>
> I am trying to fetch data from hive for 8tables at a time and save it in a
> csv, so that our pipeline would read that csv which had 8tables data like:
>
>
>
> tb1 tb2 tb3 tb4 tb5 tb6 tb7 tb8
>
> 0.1 1.1 2.1 -1.1 -0.1 0.1 0.2 3.2
>
> 1.2 0.4 4.1 -2.1 -0.5 0.2 0.3 6.2
>
> and so on....
>
>
>
> If I fetch one table at a time it will be hard to append all the 8 tables
> data in one csv(not sure if we can do that and its time consuming).
>
>
>
> On Tue, May 15, 2018 at 12:20 PM, Alan Gates <al...@gmail.com> wrote:
>
> You are correct that Hive does not support "with recursive". A few more
> details of what you are trying to do would be helpful, since it's not clear
> why you need the iteration provided by "with recursive". If you really
> need the iteration I don't think Hive can do what you want.
>
>
>
> Alan.
>
>
>
> On Tue, May 15, 2018 at 9:34 AM, Sowjanya Kakarala <so...@agrible.com>
> wrote:
>
> ok. but in my usecase join's/union's wont help.
>
> Here is an example of my usecase from postgres, which I have to do it in
> similar way for hive.
>
> with recursive
>
> a as (select col from tb1),
>
> b as (select col from tb2),
>
> c as (select col from tb3)
>
>
>
> select a,b,c from a,b,c;
>
>
>
> which output's me in a dataframe which i am writing to a csv and it looks
> like
>
> a b c
>
> 0.1 0.2 0.3
>
>
>
> where hive is not supporting `with rec` in same functionality as in
> postgres and views are also not helping here.
>
>
>
> On Tue, May 15, 2018 at 11:19 AM, Alan Gates <al...@gmail.com> wrote:
>
> In general this is done using joins, as in all SQL engines. A google
> search on "intro to SQL joins" will suggest a number of resources, for
> example
> https://www.essentialsql.com/get-ready-to-learn-sql-12-introduction-to-database-joins/
>
>
>
> Alan.
>
>
>
> On Tue, May 15, 2018 at 7:37 AM, Sowjanya Kakarala <so...@agrible.com>
> wrote:
>
> Hi all,
>
>
>
> Is there a way in hive that different tables data, can be read as in a
> single query?
>
>
>
> example:
>
> (something like)
>
> select a,b from (select col1 from tbl1)a , (select col1 from tb2)b);
>
>
>
> output as :
>
> a b
>
> 0.1 0.2
>
>
>
> Any help is appreciated.
>
>
>
> Thanks
>
> Sowjanya
>
>
>
>
>
>
>
> --
>
>
>
>
>
>
>
>
>
> --
>
RE: Combining hive tables as one query
Posted by "Grim, Paul" <pg...@soleocommunications.com>.
This probably isn’t what you want to do since it requires an extra step outside of Hive, but have you tried using UNION ALL to append all the data together (in Hive), then unpivoting it in Excel using Power Query? So your Hive query would output the data like this:
tb1 1.1 1.2
tb1 1.3 1.4
tb2 2.1 2.2
tb2 2.3 2.4
tb3 3.1 3.2
tb3 3.3 3.4
etc.
Then you could unpivot it in Excel Power Query to become like this:
tb1 tb2 tb3
1.1 2.1 3.1
1.2 2.2 3.2
1.3 2.3 3.3
1.4 2.4 3.4
You could of course then resave it in .csv format. But if the entire process needs to be automated within Hive, then my Hive query skills are not quite that advanced yet (I’ve only been using Hive for about a month).
Paul
From: Sowjanya Kakarala <so...@agrible.com>
Sent: Tuesday, May 15, 2018 1:33 PM
To: user@hive.apache.org
Subject: Re: Combining hive tables as one query
I am trying to fetch data from hive for 8tables at a time and save it in a csv, so that our pipeline would read that csv which had 8tables data like:
tb1 tb2 tb3 tb4 tb5 tb6 tb7 tb8
0.1 1.1 2.1 -1.1 -0.1 0.1 0.2 3.2
1.2 0.4 4.1 -2.1 -0.5 0.2 0.3 6.2
and so on....
If I fetch one table at a time it will be hard to append all the 8 tables data in one csv(not sure if we can do that and its time consuming).
On Tue, May 15, 2018 at 12:20 PM, Alan Gates <al...@gmail.com>> wrote:
You are correct that Hive does not support "with recursive". A few more details of what you are trying to do would be helpful, since it's not clear why you need the iteration provided by "with recursive". If you really need the iteration I don't think Hive can do what you want.
Alan.
On Tue, May 15, 2018 at 9:34 AM, Sowjanya Kakarala <so...@agrible.com>> wrote:
ok. but in my usecase join's/union's wont help.
Here is an example of my usecase from postgres, which I have to do it in similar way for hive.
with recursive
a as (select col from tb1),
b as (select col from tb2),
c as (select col from tb3)
select a,b,c from a,b,c;
which output's me in a dataframe which i am writing to a csv and it looks like
a b c
0.1 0.2 0.3
where hive is not supporting `with rec` in same functionality as in postgres and views are also not helping here.
On Tue, May 15, 2018 at 11:19 AM, Alan Gates <al...@gmail.com>> wrote:
In general this is done using joins, as in all SQL engines. A google search on "intro to SQL joins" will suggest a number of resources, for example https://www.essentialsql.com/get-ready-to-learn-sql-12-introduction-to-database-joins/
Alan.
On Tue, May 15, 2018 at 7:37 AM, Sowjanya Kakarala <so...@agrible.com>> wrote:
Hi all,
Is there a way in hive that different tables data, can be read as in a single query?
example:
(something like)
select a,b from (select col1 from tbl1)a , (select col1 from tb2)b);
output as :
a b
0.1 0.2
Any help is appreciated.
Thanks
Sowjanya
--
--
Re: Combining hive tables as one query
Posted by balajee venkatesh <ba...@gmail.com>.
Ok. There are 8 tables. Do you see any conditional match based on some
keys? May be a well designed 'Left/Right' join provisioning would help you
tackling your use case. You can also look at merge or union options. Once
you are able to correlate those tables and design a query which would give
you what you want, you can go ahead with file write feature (Insert
overwrite directory) to save the data in your desired format.
On Tue 15 May, 2018, 11:03 PM Sowjanya Kakarala, <so...@agrible.com>
wrote:
> I am trying to fetch data from hive for 8tables at a time and save it in a
> csv, so that our pipeline would read that csv which had 8tables data like:
>
> tb1 tb2 tb3 tb4 tb5 tb6 tb7 tb8
> 0.1 1.1 2.1 -1.1 -0.1 0.1 0.2 3.2
> 1.2 0.4 4.1 -2.1 -0.5 0.2 0.3 6.2
> and so on....
>
> If I fetch one table at a time it will be hard to append all the 8 tables
> data in one csv(not sure if we can do that and its time consuming).
>
> On Tue, May 15, 2018 at 12:20 PM, Alan Gates <al...@gmail.com> wrote:
>
>> You are correct that Hive does not support "with recursive". A few more
>> details of what you are trying to do would be helpful, since it's not clear
>> why you need the iteration provided by "with recursive". If you really
>> need the iteration I don't think Hive can do what you want.
>>
>> Alan.
>>
>> On Tue, May 15, 2018 at 9:34 AM, Sowjanya Kakarala <so...@agrible.com>
>> wrote:
>>
>>> ok. but in my usecase join's/union's wont help.
>>> Here is an example of my usecase from postgres, which I have to do it in
>>> similar way for hive.
>>> with recursive
>>> a as (select col from tb1),
>>> b as (select col from tb2),
>>> c as (select col from tb3)
>>>
>>> select a,b,c from a,b,c;
>>>
>>> which output's me in a dataframe which i am writing to a csv and it
>>> looks like
>>> a b c
>>> 0.1 0.2 0.3
>>>
>>> where hive is not supporting `with rec` in same functionality as in
>>> postgres and views are also not helping here.
>>>
>>> On Tue, May 15, 2018 at 11:19 AM, Alan Gates <al...@gmail.com>
>>> wrote:
>>>
>>>> In general this is done using joins, as in all SQL engines. A google
>>>> search on "intro to SQL joins" will suggest a number of resources, for
>>>> example
>>>> https://www.essentialsql.com/get-ready-to-learn-sql-12-introduction-to-database-joins/
>>>>
>>>> Alan.
>>>>
>>>> On Tue, May 15, 2018 at 7:37 AM, Sowjanya Kakarala <
>>>> sowjanya@agrible.com> wrote:
>>>>
>>>>> Hi all,
>>>>>
>>>>> Is there a way in hive that different tables data, can be read as in a
>>>>> single query?
>>>>>
>>>>> example:
>>>>> (something like)
>>>>> select a,b from (select col1 from tbl1)a , (select col1 from tb2)b);
>>>>>
>>>>> output as :
>>>>> a b
>>>>> 0.1 0.2
>>>>>
>>>>> Any help is appreciated.
>>>>>
>>>>> Thanks
>>>>> Sowjanya
>>>>>
>>>>
>>>>
>>>
>>>
>>> --
>>>
>>>
>>
>
>
> --
>
Re: Combining hive tables as one query
Posted by Uli Bethke <ul...@sonra.io>.
Maybe use ROW_NUMBER() OVER () AS row_num for each table to create a
sequence number and then (full outer) join all tables on row_num
Probably best done outside SQL though
On 15/05/2018 18:32, Sowjanya Kakarala wrote:
> I am trying to fetch data from hive for 8tables at a time and save it
> in a csv, so that our pipeline would read that csv which had 8tables
> data like:
>
> tb1 tb2 tb3 tb4 tb5 tb6 tb7 tb8
> 0.1 1.1 2.1 -1.1 -0.1 0.1 0.2 3.2
> 1.2 0.4 4.1 -2.1 -0.5 0.2 0.3 6.2
> and so on....
>
> If I fetch one table at a time it will be hard to append all the 8
> tables data in one csv(not sure if we can do that and its time consuming).
>
> On Tue, May 15, 2018 at 12:20 PM, Alan Gates <alanfgates@gmail.com
> <ma...@gmail.com>> wrote:
>
> You are correct that Hive does not support "with recursive". A
> few more details of what you are trying to do would be helpful,
> since it's not clear why you need the iteration provided by "with
> recursive". If you really need the iteration I don't think Hive
> can do what you want.
>
> Alan.
>
> On Tue, May 15, 2018 at 9:34 AM, Sowjanya Kakarala
> <sowjanya@agrible.com <ma...@agrible.com>> wrote:
>
> ok. but in my usecase join's/union's wont help.
> Here is an example of my usecase from postgres, which I have
> to do it in similar way for hive.
> with recursive
> a as (select col from tb1),
> b as (select col from tb2),
> c as (select col from tb3)
>
> select a,b,c from a,b,c;
>
> which output's me in a dataframe which i am writing to a csv
> and it looks like
> a b c
> 0.1 0.2 0.3
>
> where hive is not supporting `with rec` in same functionality
> as in postgres and views are also not helping here.
>
> On Tue, May 15, 2018 at 11:19 AM, Alan Gates
> <alanfgates@gmail.com <ma...@gmail.com>> wrote:
>
> In general this is done using joins, as in all SQL
> engines. A google search on "intro to SQL joins" will
> suggest a number of resources, for example
> https://www.essentialsql.com/get-ready-to-learn-sql-12-introduction-to-database-joins/
> <https://www.essentialsql.com/get-ready-to-learn-sql-12-introduction-to-database-joins/>
>
> Alan.
>
> On Tue, May 15, 2018 at 7:37 AM, Sowjanya Kakarala
> <sowjanya@agrible.com <ma...@agrible.com>> wrote:
>
> Hi all,
>
> Is there a way in hive that different tables data, can
> be read as in a single query?
>
> example:
> (something like)
> select a,b from (select col1 from tbl1)a , (select
> col1 from tb2)b);
>
> output as :
> a b
> 0.1 0.2
>
> Any help is appreciated.
>
> Thanks
> Sowjanya
>
>
>
>
>
> --
>
>
>
>
>
> --
--
Uli Bethke
CEO Sonra
p: +353 86 32 83 040
Connect on LinkedIn <https://linkedin.com/in/ulibethke>
Convert complex XML to text, a database, or Hadoop
<https://sonra.io/flexter-for-xml/>
[Training] Big Data for Data Warehouse Professionals
<https://sonra.io/big-data-data-warehouse-business-intelligence-professionals/>
Re: Combining hive tables as one query
Posted by Sowjanya Kakarala <so...@agrible.com>.
I am trying to fetch data from hive for 8tables at a time and save it in a
csv, so that our pipeline would read that csv which had 8tables data like:
tb1 tb2 tb3 tb4 tb5 tb6 tb7 tb8
0.1 1.1 2.1 -1.1 -0.1 0.1 0.2 3.2
1.2 0.4 4.1 -2.1 -0.5 0.2 0.3 6.2
and so on....
If I fetch one table at a time it will be hard to append all the 8 tables
data in one csv(not sure if we can do that and its time consuming).
On Tue, May 15, 2018 at 12:20 PM, Alan Gates <al...@gmail.com> wrote:
> You are correct that Hive does not support "with recursive". A few more
> details of what you are trying to do would be helpful, since it's not clear
> why you need the iteration provided by "with recursive". If you really
> need the iteration I don't think Hive can do what you want.
>
> Alan.
>
> On Tue, May 15, 2018 at 9:34 AM, Sowjanya Kakarala <so...@agrible.com>
> wrote:
>
>> ok. but in my usecase join's/union's wont help.
>> Here is an example of my usecase from postgres, which I have to do it in
>> similar way for hive.
>> with recursive
>> a as (select col from tb1),
>> b as (select col from tb2),
>> c as (select col from tb3)
>>
>> select a,b,c from a,b,c;
>>
>> which output's me in a dataframe which i am writing to a csv and it looks
>> like
>> a b c
>> 0.1 0.2 0.3
>>
>> where hive is not supporting `with rec` in same functionality as in
>> postgres and views are also not helping here.
>>
>> On Tue, May 15, 2018 at 11:19 AM, Alan Gates <al...@gmail.com>
>> wrote:
>>
>>> In general this is done using joins, as in all SQL engines. A google
>>> search on "intro to SQL joins" will suggest a number of resources, for
>>> example https://www.essentialsql.com/get-ready-to-learn-sql-
>>> 12-introduction-to-database-joins/
>>>
>>> Alan.
>>>
>>> On Tue, May 15, 2018 at 7:37 AM, Sowjanya Kakarala <sowjanya@agrible.com
>>> > wrote:
>>>
>>>> Hi all,
>>>>
>>>> Is there a way in hive that different tables data, can be read as in a
>>>> single query?
>>>>
>>>> example:
>>>> (something like)
>>>> select a,b from (select col1 from tbl1)a , (select col1 from tb2)b);
>>>>
>>>> output as :
>>>> a b
>>>> 0.1 0.2
>>>>
>>>> Any help is appreciated.
>>>>
>>>> Thanks
>>>> Sowjanya
>>>>
>>>
>>>
>>
>>
>> --
>>
>>
>
--
Re: Combining hive tables as one query
Posted by Alan Gates <al...@gmail.com>.
You are correct that Hive does not support "with recursive". A few more
details of what you are trying to do would be helpful, since it's not clear
why you need the iteration provided by "with recursive". If you really
need the iteration I don't think Hive can do what you want.
Alan.
On Tue, May 15, 2018 at 9:34 AM, Sowjanya Kakarala <so...@agrible.com>
wrote:
> ok. but in my usecase join's/union's wont help.
> Here is an example of my usecase from postgres, which I have to do it in
> similar way for hive.
> with recursive
> a as (select col from tb1),
> b as (select col from tb2),
> c as (select col from tb3)
>
> select a,b,c from a,b,c;
>
> which output's me in a dataframe which i am writing to a csv and it looks
> like
> a b c
> 0.1 0.2 0.3
>
> where hive is not supporting `with rec` in same functionality as in
> postgres and views are also not helping here.
>
> On Tue, May 15, 2018 at 11:19 AM, Alan Gates <al...@gmail.com> wrote:
>
>> In general this is done using joins, as in all SQL engines. A google
>> search on "intro to SQL joins" will suggest a number of resources, for
>> example https://www.essentialsql.com/get-ready-to-learn-sql-
>> 12-introduction-to-database-joins/
>>
>> Alan.
>>
>> On Tue, May 15, 2018 at 7:37 AM, Sowjanya Kakarala <so...@agrible.com>
>> wrote:
>>
>>> Hi all,
>>>
>>> Is there a way in hive that different tables data, can be read as in a
>>> single query?
>>>
>>> example:
>>> (something like)
>>> select a,b from (select col1 from tbl1)a , (select col1 from tb2)b);
>>>
>>> output as :
>>> a b
>>> 0.1 0.2
>>>
>>> Any help is appreciated.
>>>
>>> Thanks
>>> Sowjanya
>>>
>>
>>
>
>
> --
>
> Sowjanya Kakarala
>
> Infrastructure Software Engineer
>
>
>
> Agrible, Inc. | sowjanya@agrible.com | 217-848-1128
>
> 2021 S. First Street, Suite 201, Champaign, IL 61820
> <https://maps.google.com/?q=2021+S.+First+Street,+Suite+201,+Champaign,+IL+61820&entry=gmail&source=g>
>
>
>
> Agrible.com <http://agrible.com/> | facebook
> <https://www.facebook.com/Agrible> | youtube
> <https://www.youtube.com/c/AgribleInc_TheInsightToDecide> | twitter
> <https://twitter.com/Agribleinc>
>
> [image: Agrible_Logo_Email_Signature.jpg]
>
Re: Combining hive tables as one query
Posted by Sowjanya Kakarala <so...@agrible.com>.
ok. but in my usecase join's/union's wont help.
Here is an example of my usecase from postgres, which I have to do it in
similar way for hive.
with recursive
a as (select col from tb1),
b as (select col from tb2),
c as (select col from tb3)
select a,b,c from a,b,c;
which output's me in a dataframe which i am writing to a csv and it looks
like
a b c
0.1 0.2 0.3
where hive is not supporting `with rec` in same functionality as in
postgres and views are also not helping here.
On Tue, May 15, 2018 at 11:19 AM, Alan Gates <al...@gmail.com> wrote:
> In general this is done using joins, as in all SQL engines. A google
> search on "intro to SQL joins" will suggest a number of resources, for
> example https://www.essentialsql.com/get-ready-to-
> learn-sql-12-introduction-to-database-joins/
>
> Alan.
>
> On Tue, May 15, 2018 at 7:37 AM, Sowjanya Kakarala <so...@agrible.com>
> wrote:
>
>> Hi all,
>>
>> Is there a way in hive that different tables data, can be read as in a
>> single query?
>>
>> example:
>> (something like)
>> select a,b from (select col1 from tbl1)a , (select col1 from tb2)b);
>>
>> output as :
>> a b
>> 0.1 0.2
>>
>> Any help is appreciated.
>>
>> Thanks
>> Sowjanya
>>
>
>
--
Sowjanya Kakarala
Infrastructure Software Engineer
Agrible, Inc. | sowjanya@agrible.com | 217-848-1128
2021 S. First Street, Suite 201, Champaign, IL 61820
<https://maps.google.com/?q=2021+S.+First+Street,+Suite+201,+Champaign,+IL+61820&entry=gmail&source=g>
Agrible.com <http://agrible.com/> | facebook
<https://www.facebook.com/Agrible> | youtube
<https://www.youtube.com/c/AgribleInc_TheInsightToDecide> | twitter
<https://twitter.com/Agribleinc>
[image: Agrible_Logo_Email_Signature.jpg]
Re: Combining hive tables as one query
Posted by Alan Gates <al...@gmail.com>.
In general this is done using joins, as in all SQL engines. A google
search on "intro to SQL joins" will suggest a number of resources, for
example
https://www.essentialsql.com/get-ready-to-learn-sql-12-introduction-to-database-joins/
Alan.
On Tue, May 15, 2018 at 7:37 AM, Sowjanya Kakarala <so...@agrible.com>
wrote:
> Hi all,
>
> Is there a way in hive that different tables data, can be read as in a
> single query?
>
> example:
> (something like)
> select a,b from (select col1 from tbl1)a , (select col1 from tb2)b);
>
> output as :
> a b
> 0.1 0.2
>
> Any help is appreciated.
>
> Thanks
> Sowjanya
>