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 < [email protected]> 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, <[email protected]> > 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 <[email protected]> >> *Sent:* Tuesday, May 15, 2018 1:33 PM >> *To:* [email protected] >> *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 <[email protected]> >> 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 <[email protected]> >> 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 <[email protected]> >> 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 <[email protected]> >> 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. | [email protected] | 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]
