Transpose?pivot rows and columns in hive
Hi , How to Transpose?pivot rows and columns in hive Your help is appreciated. cheers
Hive nested correlated sub queries
Hi Team I need to convert below sql query to hive query. Is nested sub-queries possible in hive? SELECT * FROM( SELECT NVL((SELECT orgname FROM organisations WHERE orgid IN(SELECT parentid FROM organisations WHERE orgid IN(SELECT parentid FROM organisations WHERE orgid IN(SELECT parentid FROM organisations WHERE orgid=7228 AND STATUS=1 AND entityid IN (1,2))AND STATUS=1 AND entityid IN (1,2))AND STATUS=1 AND entityid IN (1,2)) AND STATUS=1 AND entityid IN (1,2),'NA')) AS ReportingOrg3, NVL((SELECT orgname FROM organisations WHERE orgid IN(SELECT parentid FROM organisations WHERE orgid IN(SELECT parentid FROM organisations WHERE orgid= 7229 AND STATUS=1 AND entityid IN (1,2))AND STATUS=1 AND entityid IN (1,2)) AND STATUS=1 AND entityid IN (1,2)),'NA') AS ReportingOrg2, NVL((SELECT orgname FROM organisations WHERE orgid IN(SELECT parentid FROM organisations WHERE orgid = 7228 AND STATUS=1 AND entityid = 2) AND STATUS=1 AND entityid = 2),'NA') AS ReportingOrg1, (SELECT NVL(orgcode,'NA') FROM organisations WHERE orgid =r.rootbranch) AS OrgCode, (SELECT orgname FROM organisations WHERE orgid =r.rootbranch) AS OrgName, NVL((SELECT orgname FROM organisations WHERE orgid=(SELECT parentid FROM organisations WHERE orgid=(SELECT parentid FROM organisations WHERE orgid=(SELECT parentid FROM organisations WHERE orgid=r.parentid AND STATUS=1 AND entityid = 3)AND STATUS=1 AND entityid = 3) AND STATUS=1 AND entityid = 3) AND STATUS=1 AND entityid = 3),'NA') AS ReportingDept4, NVL((SELECT orgname FROM organisations WHERE orgid=(SELECT parentid FROM organisations WHERE orgid=(SELECT parentid FROM organisations WHERE orgid=r.parentid AND STATUS=1 AND entityid = 3)AND STATUS=1 AND entityid = 3) AND STATUS=1 AND entityid = 3),'NA') AS ReportingDept3, NVL((SELECT orgname FROM organisations WHERE orgid=(SELECT parentid FROM organisations WHERE orgid =r.parentid AND STATUS=1 AND entityid = 3) AND STATUS=1 AND entityid = 3),'NA') AS ReportingDept2, NVL((SELECT orgname FROM organisations WHERE orgid=r.parentid AND STATUS=1 AND entityid = 3),'NA') AS ReportingDept1, Thanks in Advance Cheers
hive benchmark
Hi Folks, I need to insert 1 billion records into hive and here is my cluster details. 1. 6-node Hadoop cluster cluster. 2. 16GB RAM on each node. 3. 2TB Hard-disk on each node. Is this configuration suitable for storing 1 billion records? If not, what is that all we need to store and read 1 billion records? Thanks and regards, siva
Re: Perl-Hive connection
Hi David , I have tried the link you have posted. But im stuck with this error message below Can't locate object method "new" via package "Thrift::API::HiveClient2" could you please help me out ? Thanks and regards, siva On Thu, Jul 30, 2015 at 3:20 PM, David Morel wrote: > On 29 Jul 2015, at 9:42, siva kumar wrote: > > Hi folks, >>I need to set up a connection between perl and hive using >> thrift. Can anyone sugggest me the steps involved in making this happen?. >> >> Thanka and regrads, >> siva. >> > > Hi, > > check out > http://search.cpan.org/~dmor/Thrift-API-HiveClient2/lib/Thrift/API/HiveClient2.pm > > David >
Perl-Hive connection
Hi folks, I need to set up a connection between perl and hive using thrift. Can anyone sugggest me the steps involved in making this happen?. Thanka and regrads, siva.
Re: Hive Data into a Html Page
Hi Lohith, We use http server. Lemme explain you what exactly is the requirement.We are using a Perl Script to read hive data and make some modifications on that data.Now, this modified data should get displayed on the html page instead of storing it back to database.So, i need to know the solution for this scenario. Can we achieve this(displayimg onto html page) using the same perl script after reading and modifying the data from hive?. Any suggestions? Thanks and regards, siva. On Mon, Jul 27, 2015 at 8:09 PM, Lohith Samaga M wrote: > Hi Siva > What web/application server do you have? > > You could use Hive or Drill odbc drivers... > > Sent from my Sony Xperia™ smartphone > > > siva kumar wrote > > > Hi , > There is some data loaded into hive and i want to display hive data > into a html webpage based on the parameters i pass. So, how can i do this? > > Any Help? > Thanks in Advance > siva. > > > Information transmitted by this e-mail is proprietary to Mphasis, its > associated companies and/ or its customers and is intended > for use only by the individual or entity to which it is addressed, and may > contain information that is privileged, confidential or > exempt from disclosure under applicable law. If you are not the intended > recipient or it appears that this mail has been forwarded > to you without proper authority, you are notified that any use or > dissemination of this information in any manner is strictly > prohibited. In such cases, please notify us immediately at > mailmas...@mphasis.com and delete this mail from your records. >
Hive Data into a Html Page
Hi , There is some data loaded into hive and i want to display hive data into a html webpage based on the parameters i pass. So, how can i do this? Any Help? Thanks in Advance siva.
skewed tables
hi folks,
Im working on skewed tables but facing some problems while
it has to create seperate files. Here is my query.
create table t(key string,value int) row format delimited stored as
textfile;
load data local inpath 'skew.txt' into table t;
select * from t;
OK
siva1
nivi2
hari3
ravi4
hive> set hive.mapred.supports.subdirectories=true;
hive> set mapred.input.dir.recursive=true;
hive> create table t1(key string,value int) skewed by (value) on ('2')
stored as directories;
OK
insert overwrite table t1 select * from t;
select * from t1;
OK
siva1
nivi2
hari3
ravi4
But only one file is created instead of seperate files. .
hadoop fs -ls /user/hive/warehouse/t1
Found 1 items
-rw-rw-rw- 3 23529 supergroup 28 2014-10-09 15:26*
/user/hive/warehouse/t1/00_0*
Im using hive 0.10 version. Any suggestions as soon as possible?
Thanks and regards,
sivakumar.c
hue not writing data to sql tables
Hi folks, I need to populate hive query history in sql table.Im quering those using hue. My hue default database is sqlite3. When i say '.tables' it displayed me list of tables where i can find auth_user,beeswax_queryhistory tables and many other tables as well. But,when i say 'select username from auth_user' , no values r displayed. Even, i tried configuring sql as my hue database and here is the configuartion properties. engine=mysql host=localhost port=3306 user=root password= name=hue. But,i can only find the tables mentioned above and no data is populated in the respective tables when i query or create users. Im working with CM VMware. Is there any other configuartions to populate the data. I really find tough time . Any quick response is greatly appreaciated. Thanks and regards, sivakumar.c
Re: audit hive query
Hi Dev, When i run the hive query in hive shell, the query is stored in hive history file at default location.Since my metastore is sql for hive whenever i create a table with some columns in hive shell,it gets populated in sql metastore database.Simillarly,all the queries should be popoulated in a sql table. I want to view the hive queries over a period of time populated in a sql table, but not through the web browser. Can u suggest me some idea on dis scenario. Thanks and regards, siva On Mon, Sep 22, 2014 at 9:18 AM, Devopam Mittra wrote: > Please check .hivehistory settings and it might be of easy / simple good > help . > If you are on Ambari based installation then simply looking into Resource > Manager UI should suffice the need. > If you are on native hadoop, then you can access the Yarn log through web > browser as well. > > Hope this helps > > regards > Dev > > > On Fri, Sep 19, 2014 at 4:01 PM, siva kumar wrote: > >> Hi All, >> I have a requirement to view the recently executed Hive queries >> in sql. I have configured sql as metastore to hive and im able to view hive >> tables,columns,partitions in sql . Simillarly,i need to view the hive >> queries executed on hive data. >> >> >> >> Any help is appreciated, >> sivakumar.c >> > > > > -- > Devopam Mittra > Life and Relations are not binary >
audit hive query
Hi All, I have a requirement to view the recently executed Hive queries in sql. I have configured sql as metastore to hive and im able to view hive tables,columns,partitions in sql . Simillarly,i need to view the hive queries executed on hive data. Any help is appreciated, sivakumar.c
Fwd: Inline query in select clause
-- Forwarded message -- From: siva kumar Date: Mon, Jul 21, 2014 at 3:52 PM Subject: Inline query in select clause To: user@hive.apache.org Hi, I have below 2 tables� 1) create table dim (rank string,grade string) row format delimited fields terminated by ',' stored as textile; Data: 1,1 2,1 3,1 4,2 5,2 6,2 7,3 2) create table fact (rollno string,name string,sub1 string,rank1 string,sub2 string,rank2 string,sub3 string,rank3 string) row format delimited fields terminated by ',' stored as textile; Data : 1,name1,english,1,maths,2,science,3 2,name2,english,5,maths,6,science,7 3,name1,english,7,maths,4,science,1 Below is the oracle query for which an equivalent query in HIVE is needed. Is it possible to do without using UDF? The requirement display the grade instead of rank in the fact table�. select a.rollno,a.name,a.sub1,(select grade from dim where rank = a.rank1), a.sub2,(select grade from dim where rank = a.rank2), a.sub3,(select grade from dim where rank = a.rank3) from fact; Note : For simplicity have kept limited range/rows, actual requirement has a very big range which cannot be hardcoded. regards, siva
Inline query in select clause
Hi, I have below 2 tables� 1) create table dim (rank string,grade string) row format delimited fields terminated by ',' stored as textile; Data: 1,1 2,1 3,1 4,2 5,2 6,2 7,3 2) create table fact (rollno string,name string,sub1 string,rank1 string,sub2 string,rank2 string,sub3 string,rank3 string) row format delimited fields terminated by ',' stored as textile; Data : 1,name1,english,1,maths,2,science,3 2,name2,english,5,maths,6,science,7 3,name1,english,7,maths,4,science,1 Below is the oracle query for which an equivalent query in HIVE is needed. Is it possible to do without using UDF? The requirement display the grade instead of rank in the fact table�. select a.rollno,a.name,a.sub1,(select grade from dim where rank = a.rank1), a.sub2,(select grade from dim where rank = a.rank2), a.sub3,(select grade from dim where rank = a.rank3) from fact; Note : For simplicity have kept limited range/rows, actual requirement has a very big range which cannot be hardcoded. regards, siva
