hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Grim, Paul" <>
Subject RE: Combining hive tables as one query
Date Tue, 15 May 2018 18:07:55 GMT
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

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).


From: Sowjanya Kakarala <>
Sent: Tuesday, May 15, 2018 1:33 PM
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 <<>>
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


On Tue, May 15, 2018 at 9:34 AM, Sowjanya Kakarala <<>>
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 <<>>
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


On Tue, May 15, 2018 at 7:37 AM, Sowjanya Kakarala <<>>
Hi all,

Is there a way in hive that different tables data, can be read as in a single query?

(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.



View raw message