USE CASE:: Hierarchical Structure in Hive and Java
Hello All, We are having a use case where we need to create the hierarchical structure using Hive and Java For example Lets say in an organisation we need to create Org chart i.e. Senior director -> director -> associate director -> senior manager -> manager -> senior associate -> associate -> Developer means parent child then sub child and so on. Input Source: summarized table which is getting populated after running the joins which will run business logic and fetch the data from base table. Output: Table which store the parent child relationship in a hierarchical manner If anyone come across this kind of requirement/scenario kindly suggest the approach to proceed. Thanks in Advance Thanks & Regards Yogesh
HIVE::START WITH and CONNECT BY implementation in Hive
Hello All How can we achive *start with .. connect by* clause can be used to select data that has a hierarchical relationship (usually some sort of parent->child (boss->employee or thing->parts). into Hive, or what would be the other work around to implement this. Please suggest Thanks in advance Yogesh
Re: PLEASE HELP :: HOW TO DO INNER JOIN IN HIVE
Thanks Devopam , I did not get your point, could you pls show me a dummy query in my case Regards On Wed, Oct 15, 2014 at 1:42 PM, Devopam Mittra wrote: > hi Yogesh, > Please try to leverage common table expression (WITH ...) to achieve your > desired outcome > > regards > Dev > > > On Oct 15, 2014, at 1:18 PM, yogesh dhari wrote: > > > > Hello all, > > > > I have a use case where I need to do inner join.. > > > > Like > > > > select A.iteam , B.Decsription, > > from iteam_table A INNER JOIN iteam_desc B > > on A.id = B.id > > > > > > As hive does not support Inner Join, > > > > Please suggest how to do it > > > > > > Thanks in Advance > > >
PLEASE HELP :: HOW TO DO INNER JOIN IN HIVE
Hello all, I have a use case where I need to do inner join.. Like select A.iteam , B.Decsription, from iteam_table A INNER JOIN iteam_desc B on A.id = B.id As hive does not support Inner Join, Please suggest how to do it Thanks in Advance
HIVE QUERY HELP:: HOW TO IMPLEMENT THIS CASE
Hello All, I have a use case in RDBMS query which I have implemented in HIVE as.. *1.1) Update statement* *in RDBMS* update TABLE1 set Age= case when isnull(age,'') ='' then 'A= Current' else '240+ Days' end, Prev_Age=case when isnull(prev_age,'') ='' then 'A= Current' else '240+ Days' end; *1.2) Update statement* *in HIVE* create table TABLE2 as select a.* , case when coalesce(a.age,'')='' then 'A=Current' else '240+ Days' end as Age, case when coalesce(a.prev_age,'')='' then 'A=Current' else '240+ Days' end as Prev_age from TABLE1 a ; *Now I have a case statement in which I have a join condition*. *2) Join in RDBMS* update TABLE1 set a.Age = c.sys_buscd_item_desc1 from TABLE1 a join TABLE2 c on c.sys_type_cd='AGE' where isnull(a.age,'00')=c.sys_item; commit; How can I implement this query into Hive, Pls help and suggest. Thanks In Advance Yogesh Kumar
Re: Hive Query :: Implementing case statement
Hello Stephen , Yes, actully I have used Left Outer Join instead of Join, there were left outer joins in RDBMS Query instead of join. Thanks again :) On Thu, Feb 20, 2014 at 10:45 AM, Stephen Sprague wrote: > Hi Yogesh, > > i overlooked one thing and for completeness we should make note of it here. > > change: > > > -- non-intersected rows >select a.* from TABLE_SQL a join NEW_BALS b on (a.key=b.key) where > b.NEW_BALANCE is null > > to > > -- non-intersected rows >select a.* from TABLE_SQL a *LEFT OUTER* join NEW_BALS b on > (a.key=b.key) where b.NEW_BALANCE is null > > > This takes care of the rows where code != 1 and code != 2. > > > But you knew that! > > > > > On Wed, Feb 19, 2014 at 8:33 PM, yogesh dhari wrote: > >> Thanks a lot Stephen Sprague :) :) >> >> It worked.. , just to remove the " ; " from here, bcoz it was throuig >> sub query systax error... >> >> >> >> create table NEW_BALS as >> select * from ( >> select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join TABLE_SQL_2 b >> on (a.key=b.key) where a.code='1'; >> UNION ALL >> select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join TABLE_SQL_3 b >> on (a.key=b.key) where a.code='2'; >> ) z >> ; >> >> >> >> to >> >> create table NEW_BALS as >> select * from ( >> select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join TABLE_SQL_2 b >> on (a.key=b.key) where a.code='1' >> UNION ALL >> select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join TABLE_SQL_3 b >> on (a.key=b.key) where a.code='2' >> ) z >> ; >> >> Thanks & Regards >> Yogesh Kumar >> >> >> On Tue, Feb 18, 2014 at 9:18 PM, Stephen Sprague wrote: >> >>> maybe consider something along these lines. nb. not tested. >>> >>> -- temp table holding new balances + key >>> >>> create table NEW_BALS as >>> select * from ( >>> select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join >>> TABLE_SQL_2 b on (a.key=b.key) where a.code='1'; >>> UNION ALL >>> select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join >>> TABLE_SQL_3 b on (a.key=b.key) where a.code='2'; >>> ) z >>> ; >>> >>> -- i gave the table a new name but you could use TABLE_SQL instead of >>> NEW_TABLE_SQL for the true update. >>> >>> insert overwrite table NEW_TABLE_SQL >>> >>>select * from ( >>> >>> -- intersected rows - you'll have to line-up the columns correctly >>> substituting b.NEW_BALANCE into the right position >>>select a.col1, b.NEW_BALANCE, a.col2, ... from TABLE_SQL a join >>> NEW_BALS b on (a.key=b.key) >>> >>>UNION ALL >>> >>> -- non-intersected rows >>>select a.* from TABLE_SQL a join NEW_BALS b on (a.key=b.key) where >>> b.NEW_BALANCE is null >>> >>>) z >>> ; >>> >>> there's probably some typos in there but hopefully you get the idea and >>> can take it from here. >>> >>> >>> >>> >>> >>> On Tue, Feb 18, 2014 at 4:39 AM, yogesh dhari wrote: >>> >>>> Hello All, >>>> >>>> I have a use case where a table say TABLE_SQL is geting updated like. >>>> >>>> >>>> 1st Update Command >>>> >>>> update TABLE_SQL a >>>> set BALANCE = b.prev >>>> from TABLE_SQL_2 b >>>> where a.key = b.key and a.code = "1" >>>> >>>> >>>> 2nd Update Command >>>> >>>> update TABLE_SQL a >>>> set BALANCE = b.prev >>>> from TABLE_SQL_3 b >>>> where a.key = b.key and a.code = "2" >>>> >>>> >>>> same column is getting update twice in sql table, >>>> >>>> I have a Table in Hive say TABLE_HIVE. >>>> >>>> How to perform this kind operatation in HIVE, >>>> >>>> Pls Help, >>>> >>>> Thanks in Advance >>>> Yogesh Kumar >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>> >>> >> >
Re: Hive Query :: Implementing case statement
Thanks a lot Stephen Sprague :) :) It worked.. , just to remove the " ; " from here, bcoz it was throuig sub query systax error... create table NEW_BALS as select * from ( select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join TABLE_SQL_2 b on (a.key=b.key) where a.code='1'; UNION ALL select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join TABLE_SQL_3 b on (a.key=b.key) where a.code='2'; ) z ; to create table NEW_BALS as select * from ( select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join TABLE_SQL_2 b on (a.key=b.key) where a.code='1' UNION ALL select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join TABLE_SQL_3 b on (a.key=b.key) where a.code='2' ) z ; Thanks & Regards Yogesh Kumar On Tue, Feb 18, 2014 at 9:18 PM, Stephen Sprague wrote: > maybe consider something along these lines. nb. not tested. > > -- temp table holding new balances + key > > create table NEW_BALS as > select * from ( > select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join > TABLE_SQL_2 b on (a.key=b.key) where a.code='1'; > UNION ALL > select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join > TABLE_SQL_3 b on (a.key=b.key) where a.code='2'; > ) z > ; > > -- i gave the table a new name but you could use TABLE_SQL instead of > NEW_TABLE_SQL for the true update. > > insert overwrite table NEW_TABLE_SQL > >select * from ( > > -- intersected rows - you'll have to line-up the columns correctly > substituting b.NEW_BALANCE into the right position >select a.col1, b.NEW_BALANCE, a.col2, ... from TABLE_SQL a join > NEW_BALS b on (a.key=b.key) > >UNION ALL > > -- non-intersected rows >select a.* from TABLE_SQL a join NEW_BALS b on (a.key=b.key) where > b.NEW_BALANCE is null > >) z > ; > > there's probably some typos in there but hopefully you get the idea and > can take it from here. > > > > > > On Tue, Feb 18, 2014 at 4:39 AM, yogesh dhari wrote: > >> Hello All, >> >> I have a use case where a table say TABLE_SQL is geting updated like. >> >> >> 1st Update Command >> >> update TABLE_SQL a >> set BALANCE = b.prev >> from TABLE_SQL_2 b >> where a.key = b.key and a.code = "1" >> >> >> 2nd Update Command >> >> update TABLE_SQL a >> set BALANCE = b.prev >> from TABLE_SQL_3 b >> where a.key = b.key and a.code = "2" >> >> >> same column is getting update twice in sql table, >> >> I have a Table in Hive say TABLE_HIVE. >> >> How to perform this kind operatation in HIVE, >> >> Pls Help, >> >> Thanks in Advance >> Yogesh Kumar >> >> >> >> >> >> >> >> >> >> >> > >
Re: Hive Query :: Implementing case statement
Yes, Hive does not provide update statement, I am just looking for the work arround it, how to implement it On Tue, Feb 18, 2014 at 6:27 PM, Peter Marron < peter.mar...@trilliumsoftware.com> wrote: > From https://cwiki.apache.org/confluence/display/Hive/Home > > > > "Hive is not designed for OLTP workloads and does not offer real-time > queries or row-level updates." > > > > As far as I am aware "UPDATE" isn't even in the Hive DML. > > > > Z > > *Peter Marron* > Senior Developer > Trillium Software, A Harte Hanks Company > > Theale Court, 1st Floor, 11-13 High Street > Theale > RG7 5AH > > +44 (0) 118 940 7609 office > +44 (0) 118 940 7699 fax > > [image: > https://4b2685446389bc779b46-5f66fbb59518cc4fcae8900db28267f5.ssl.cf2.rackcdn.com/trillium.png]<http://www.trilliumsoftware.com/> > > trilliumsoftware.com <http://www.trilliumsoftware.com/> / > linkedin<http://www.linkedin.com/company/17710> > / twitter <https://twitter.com/trilliumsw> / > facebook<http://www.facebook.com/HarteHanks> > > > > *From:* yogesh dhari [mailto:yogeshh...@gmail.com] > *Sent:* 18 February 2014 12:39 > *To:* user@hive.apache.org > *Subject:* Hive Query :: Implementing case statement > > > > Hello All, > > > > I have a use case where a table say TABLE_SQL is geting updated like. > > > > > > 1st Update Command > > > > update TABLE_SQL a > > set BALANCE = b.prev > > from TABLE_SQL_2 b > > where a.key = b.key and a.code = "1" > > > > > > 2nd Update Command > > > > update TABLE_SQL a > > set BALANCE = b.prev > > from TABLE_SQL_3 b > > where a.key = b.key and a.code = "2" > > > > > > same column is getting update twice in sql table, > > > > I have a Table in Hive say TABLE_HIVE. > > > > How to perform this kind operatation in HIVE, > > > > Pls Help, > > > > Thanks in Advance > > Yogesh Kumar > > > > > > > > > > > > > > > > > > > > > <>
Hive Query :: Implementing case statement
Hello All, I have a use case where a table say TABLE_SQL is geting updated like. 1st Update Command update TABLE_SQL a set BALANCE = b.prev from TABLE_SQL_2 b where a.key = b.key and a.code = "1" 2nd Update Command update TABLE_SQL a set BALANCE = b.prev from TABLE_SQL_3 b where a.key = b.key and a.code = "2" same column is getting update twice in sql table, I have a Table in Hive say TABLE_HIVE. How to perform this kind operatation in HIVE, Pls Help, Thanks in Advance Yogesh Kumar
HIVE SUB QUERY:: How to implement this case
Hello all, I have a case statement where I need to work like this logic. select as_of_dt as as_of_dt, max_feed_key as max_feed_key, min_feed_key as min_feed_key from table feed_key_temp where max_fed_key > ( select max(feed_key) from summ_table ) group by as_of_dt ; Here, max_feed_key and min_feed_key are the fields in table feed_key_temp. As Hive does not provide (0.9 version) sub query in where clause, Pls suggest the work around it, and how to implement it Thanks & Regards Yogesh Kumar
Re: working with HIVE VARIALBE: Pls suggest
Thanks all for your help.. I think was not so much clear about what I was trying to do... I was just trying to create a variable in Hive like in RDBMS, and want to store the result of a query into that variable. lets say, I have declared a variable MY_VAR I want to store the result of hive query "select max(salary) from Hive_employe_table" ; into MY_VAR Is it possible in Hive ? if yes then how to achieve.. Thanks a lot in advance. Pls suggest or to work around it Thanks & Regards Yogesh On Mon, Jan 6, 2014 at 12:36 PM, lxw wrote: > > maybe you can see: > > > https://cwiki.apache.org/confluence/plugins/viewsource/viewpagesrc.action?pageId=30754722 > > > -- Original -- > *From: * "yogesh dhari";; > *Date: * Fri, Jan 3, 2014 01:13 AM > *To: * "user"; > *Subject: * working with HIVE VARIALBE: Pls suggest > > Hello Hive Champs, > I have a case statement, where I need to check the date passed through > parameter, > If date is 1st date of the month then keep it as it as > else > set the parameter date to 1st date of the month. > and then later opretation are being performed on that date into hive > quries, > I have wrote this Hive QL > *select case when as_of_dt = ${hivevar:as_of_dt} then > ${hivevar:as_of_dt} else date_sub(${hivevar:as_of_dt} , > (day(${hivevar:as_of_dt} )) -1 ) end as as_of_dt from TABLE group by > as_of_dt ;* > O/P of this query is, lets say = 2012-08-01 > > I want to store the value of this Query into a variable. > like > > MY_VARIABLE = (*select case when as_of_dt = ${hivevar:as_of_dt} then > ${hivevar:as_of_dt} else date_sub(${hivevar:as_of_dt} , > (day(${hivevar:as_of_dt} )) -1 ) end as as_of_dt from TABLE group by > as_of_dt; )* > > > > How to achieve that. > Pls suggest, > Thanks in advance >
working with HIVE VARIALBE: Pls suggest
Hello Hive Champs, I have a case statement, where I need to check the date passed through parameter, If date is 1st date of the month then keep it as it as else set the parameter date to 1st date of the month. and then later opretation are being performed on that date into hive quries, I have wrote this Hive QL *select case when as_of_dt = ${hivevar:as_of_dt} then ${hivevar:as_of_dt} else date_sub(${hivevar:as_of_dt} , (day(${hivevar:as_of_dt} )) -1 ) end as as_of_dt from TABLE group by as_of_dt ;* O/P of this query is, lets say = 2012-08-01 I want to store the value of this Query into a variable. like MY_VARIABLE = (*select case when as_of_dt = ${hivevar:as_of_dt} then ${hivevar:as_of_dt} else date_sub(${hivevar:as_of_dt} , (day(${hivevar:as_of_dt} )) -1 ) end as as_of_dt from TABLE group by as_of_dt; )* How to achieve that. Pls suggest, Thanks in advance
Setting value into hive varialble
Hello All, I have a case statement, where I need to check the date passed through parameter, If date is 1st date of the month then keep it as it as else set the parameter date to 1st date of the month. and then later opretation are being performed on that date into hive quries, I have wrote this Hive QL *select case when as_of_dt = ${hivevar:as_of_dt} then ${hivevar:as_of_dt} else date_sub(${hivevar:as_of_dt} , (day(${hivevar:as_of_dt} )) -1 ) end as as_of_dt from TABLE group by as_of_dt ;* I want to store the value of this Query int a variable. like MY_VARIABLE = output of this query; How to achive that. Pls suggest, Thanks in advance some
CASE Statement not working in hive
Hello all, I have wrote this query . *" select* *case when as_date rlike '2013-05-01' then as_date else '2013-07-04' end as as_date from table AA ; " * as the value of as_date i.e 2013-05-01 exists in table AA it *should return the value 2013-05-01 *(Which is true in case statement) but it always *results value in else condition i.e '2013-07-04'* *"select as_date from AA ;"* 2013-05-01 why is it so, pls suggest and help Regards Yogesh Kumar
Re: to find the 1st day of the month in hive
Hi, Can i use this. select date_sub('2013-12-08', (day('2013-12-08')) -1) from table. just want to cross chk On Tue, Dec 24, 2013 at 3:48 PM, yogesh dhari wrote: > Hello, > > I have a use case where I need to find the 1st day of the month of > entered date. > > > > like if the date is 2013-12-05 i need to get 2013-12-01. > > how to do it in Hive. (wont preffer to use UDF, like by doing some > date_sub kind of or other function) > > > > Pls suggest > > > > Thanks > Yogesh >
to find the 1st day of the month in hive
Hello, I have a use case where I need to find the 1st day of the month of entered date. like if the date is 2013-12-05 i need to get 2013-12-01. how to do it in Hive. (wont preffer to use UDF, like by doing some date_sub kind of or other function) Pls suggest Thanks Yogesh
Re: Date format in Hive
Java nahi aati, to udf kaha se likhu.. koi hive ka function hi bata ya work around it.. ya fir shell script me kaisey kerney ka h ... On Tue, Dec 24, 2013 at 12:47 PM, wrote: > Hello Dhari, > > Write a hive udf, > 1.which ll take date as argument. > 2.extract yr, mon,date from string, create a new string combining them > 3. convert it to numeric and multiply 100.. > 4. return this value from udf. > > Thanks, > Ashok > ____ > From: yogesh dhari [yogeshh...@gmail.com] > Sent: Tuesday, December 24, 2013 12:24 PM > To: user@hive.apache.org > Subject: Date format in Hive > > Hello All, > > I have a hive table in which dates are stored in string format. > > like > > 2013-01-01 > 2013-02-01 > 2013-03-01 > > > I have a use case where I need this date like > 20130101 > 20130201 > 20130301 > > and want to multiply each with 100 > like > > 2013010100 > 2013020100 > 2013030100 > > > How can I do it into Hive > > pls help. > > Thanks & Regards > Yogesh > > The information contained in this electronic message and any attachments > to this message are intended for the exclusive use of the addressee(s) and > may contain proprietary, confidential or privileged information. If you are > not the intended recipient, you should not disseminate, distribute or copy > this e-mail. Please notify the sender immediately and destroy all copies of > this message and any attachments. > > WARNING: Computer viruses can be transmitted via email. The recipient > should check this email and any attachments for the presence of viruses. > The company accepts no liability for any damage caused by any virus > transmitted by this email. > > www.wipro.com >
Date format in Hive
Hello All, I have a hive table in which dates are stored in string format. like 2013-01-01 2013-02-01 2013-03-01 I have a use case where I need this date like 20130101 20130201 20130301 and want to multiply each with 100 like 2013010100 2013020100 2013030100 How can I do it into Hive pls help. Thanks & Regards Yogesh
JOIN comparasion PIG V/S HIVE
Hi All, Is it true that Pig's JOIN operation is not so efficient as of HIVE. I have just tried over and found differences over JOIN query. Hive resulted the same as My Sql but Pig resulted some counts lesser then Hive Join. Please put some light over JOINS in Pig and Hive. Regards Yogesh Kumar Dhari
PIG vs HIVE
Hi All, I want to understand about the exceptional cases where Hive takes over Pig and Pig takes over Hive. leaving the Fact Pig is best as an ETL Tool and Hive is best Data Warehouse. Please suggest me me the real use cases for both. Thanks &Regards Yogesh Kumar
RE: NEED HELP in Hive Query
Thanks John :-), I got it now in Pig also :-). A = load '/File/00_0' using PigStorage('\u0001') as as (name, date, url, hit:INT); B = group A by (id, name, date, url); C = foreach B generate flatten(A.id), flatten(A.name), flatten(A.url), SUM(A.hit) ; D = distinct C; Dump D; Thanks & Regards Yogesh Kumar Dhari From: j...@omernik.com Date: Sun, 14 Oct 2012 12:29:23 -0500 Subject: Re: NEED HELP in Hive Query To: user@hive.apache.org select NAME, DATE, URL, SUM(HITCOUNT) as HITCOUNT from yourtable group by NAME, DATE, URL That's the HIVE answer. Not sure the PIG answer. On Sun, Oct 14, 2012 at 9:54 AM, yogesh dhari wrote: Hi all, I have this file. I want this operation to perform in HIVE & PIG NAME DATE URL HITCOUNT timesascent.in2008-08-27 http://timesascent.in/index.aspx?page=tparchives15 timesascent.in2008-08-27 http://timesascent.in/index.aspx?page=article§id=1&contentid=200812182008121814134447219270b26 20 timesascent.in2008-08-27http://timesascent.in/37 timesascent.in2008-08-27http://timesascent.in/section/39/Job%20Wise 14 timesascent.in2008-08-27 http://timesascent.in/article/7/2011062120110621171709769aacc537/Work-environment--Employee-productivity.html 20 timesascent.in2008-08-27http://timesascent.in/17 timesascent.in2008-08-27http://timesascent.in/section/2/Interviews 15 timesascent.in2008-08-27http://timesascent.in/17 timesascent.in2008-08-27http://timesascent.in/27 timesascent.in2008-08-27http://timesascent.in/37 timesascent.in2008-08-27http://timesascent.in/27 timesascent.in2008-08-27http://www.timesascent.in/16 timesascent.in2008-08-27http://timesascent.in/section/2/Interviews 14 timesascent.in2008-08-27http://timesascent.in/14 timesascent.in2008-08-27http://timesascent.in/22 I want to add all HITCOUNT for the same NAME, DATE & URL like timesascent.in2008-08-27http://timesascent.in/(addition of all hitcount under same name, date, url (37+17+17+27+)) Please suggest me is there any method to perform this query. Thanks & Regards Yogesh Kumar
NEED HELP in Hive Query
Hi all, I have this file. I want this operation to perform in HIVE & PIG NAME DATE URL HITCOUNT timesascent.in2008-08-27 http://timesascent.in/index.aspx?page=tparchives15 timesascent.in2008-08-27 http://timesascent.in/index.aspx?page=article§id=1&contentid=200812182008121814134447219270b26 20 timesascent.in2008-08-27http://timesascent.in/37 timesascent.in2008-08-27http://timesascent.in/section/39/Job%20Wise 14 timesascent.in2008-08-27 http://timesascent.in/article/7/2011062120110621171709769aacc537/Work-environment--Employee-productivity.html 20 timesascent.in2008-08-27http://timesascent.in/17 timesascent.in2008-08-27http://timesascent.in/section/2/Interviews 15 timesascent.in2008-08-27http://timesascent.in/17 timesascent.in2008-08-27http://timesascent.in/27 timesascent.in2008-08-27http://timesascent.in/37 timesascent.in2008-08-27http://timesascent.in/27 timesascent.in2008-08-27http://www.timesascent.in/16 timesascent.in2008-08-27http://timesascent.in/section/2/Interviews 14 timesascent.in2008-08-27http://timesascent.in/14 timesascent.in2008-08-27http://timesascent.in/22 I want to add all HITCOUNT for the same NAME, DATE & URL like timesascent.in2008-08-27http://timesascent.in/(addition of all hitcount under same name, date, url (37+17+17+27+)) Please suggest me is there any method to perform this query. Thanks & Regards Yogesh Kumar
RE: Need Help in Hive storage format
Thanks All :-) Its working now :-) Regards Yogesh Kumar > CC: user@hive.apache.org > From: abhishek.dod...@gmail.com > Subject: Re: Need Help in Hive storage format > Date: Fri, 12 Oct 2012 08:47:41 -0400 > To: user@hive.apache.org > > Hi Yogesh, > > Try this PigStorage(' \u0001'); > > Thanks > Abhi > > > Sent from my iPhone > > On Oct 12, 2012, at 5:50 AM, MiaoMiao wrote: > > > Hi Yogesh: > > > > I think this may help. > > > > https://pig.apache.org/docs/r0.10.0/api/org/apache/pig/builtin/PigStorage.html > > > > Miao > > > > On Fri, Oct 12, 2012 at 5:27 PM, yogesh dhari wrote: > >> Thanks Bejoy, > >> > >> Now I want to store theses rows in Pig. > >> > >> like > >> > >> A = load '/Pig/00_0' using PigStorage() > >> as > >> (id:INT, name:chararray, ddate, prim, ignore, ignorecase, activat); > >> > >> What should be in the delimiter into PigStorage( )? > >> I have tried PigStorage('/001') but its showing errors. > >> What delimiter should we use. > >> > >> Please help and Suggest. > >> > >> > >> Thanks & Regards > >> Yogesh Kumar > >> > >> > >> > >> > >> > >> > >> Subject: Re: Need Help in Hive storage format > >> To: user@hive.apache.org > >> From: bejoy...@yahoo.com > >> Date: Thu, 11 Oct 2012 17:53:12 + > >> > >> > >> Hi Yogesh. > >> > >> It should be a simple delimited file with ^A character as the field > >> delimiter. > >> Regards > >> Bejoy KS > >> > >> Sent from handheld, please excuse typos. > >> > >> From: yogesh dhari > >> Date: Thu, 11 Oct 2012 23:18:35 +0530 > >> To: hive request > >> ReplyTo: user@hive.apache.org > >> Subject: Need Help in Hive storage format > >> > >> Hi all, > >> > >> If we run this query > >> > >> insert overwrite local directory '/home/yogesh/Downloads/demoyy' select * > >> from NYSE_LOCAL; > >> > >> { > >> ( describe NYSE_LOCAL ; > >> > >> exchangestring > >> symbolstring > >> ddatestring > >> openfloat > >> highfloat > >> lowfloat > >> ) } > >> > >> ls /home/yogesh/Downloads/demoyy/ > >> > >> it shows the file name 00_0 > >> > >> my Question is: > >> > >> 1) In which format does file 00_0 is? > >> 2) what is the delimiter between columns? > >> > >> Please help > >> > >> Thanks & Regards > >> Yogesh Kumar > >> > >>
RE: Need Help in Hive storage format
Thanks Bejoy, Now I want to store theses rows in Pig. like A = load '/Pig/00_0' using PigStorage() as (id:INT, name:chararray, ddate, prim, ignore, ignorecase, activat); What should be in the delimiter into PigStorage( )? I have tried PigStorage('/001') but its showing errors. What delimiter should we use. Please help and Suggest. Thanks & Regards Yogesh Kumar Subject: Re: Need Help in Hive storage format To: user@hive.apache.org From: bejoy...@yahoo.com Date: Thu, 11 Oct 2012 17:53:12 + Hi Yogesh. It should be a simple delimited file with ^A character as the field delimiter. Regards Bejoy KS Sent from handheld, please excuse typos.From: yogesh dhari Date: Thu, 11 Oct 2012 23:18:35 +0530To: hive requestReplyTo: user@hive.apache.org Subject: Need Help in Hive storage format Hi all, If we run this query insert overwrite local directory '/home/yogesh/Downloads/demoyy' select * from NYSE_LOCAL; { ( describe NYSE_LOCAL ; exchangestring symbolstring ddatestring openfloat highfloat lowfloat ) } ls /home/yogesh/Downloads/demoyy/ it shows the file name 00_0 my Question is: 1) In which format does file 00_0 is? 2) what is the delimiter between columns? Please help Thanks & Regards Yogesh Kumar
Need Help in Hive storage format
Hi all, If we run this query insert overwrite local directory '/home/yogesh/Downloads/demoyy' select * from NYSE_LOCAL; { ( describe NYSE_LOCAL ; exchangestring symbolstring ddatestring openfloat highfloat lowfloat ) } ls /home/yogesh/Downloads/demoyy/ it shows the file name 00_0 my Question is: 1) In which format does file 00_0 is? 2) what is the delimiter between columns? Please help Thanks & Regards Yogesh Kumar
RE: hive query fails
Hi Ajit, Select * command doesn't invoke reducer, its just dump the data. check out your network proxy settings. Regards Yogesh Kumar Dhari From: ajit.shreevast...@hcl.com To: user@hive.apache.org Date: Mon, 1 Oct 2012 16:29:27 +0530 Subject: hive query fails Dear all, I am running following query and I m not getting any output. But select * from pokes is working fine. [hadoop@NHCLT-PC44-2 bin]$ hiveLogging initialized using configuration in file:/home/hadoop/Hive/conf/hive-log4j.propertiesHive history file=/home/hadoop/tmp/hadoop/hive_job_log_hadoop_201210011620_669979453.txthive> select count(1) from pokes;Total MapReduce jobs = 1Launching Job 1 out of 1Number of reduce tasks determined at compile time: 1In order to change the average load for a reducer (in bytes): set hive.exec.reducers.bytes.per.reducer=In order to limit the maximum number of reducers: set hive.exec.reducers.max=In order to set a constant number of reducers: set mapred.reduce.tasks=Starting Job = job_201210011527_0004, Tracking URL = http://NHCLT-PC44-2:50030/jobdetails.jsp?jobid=job_201210011527_0004Kill Command = /home/hadoop/hadoop-1.0.3/bin/hadoop job -kill job_201210011527_0004 Thanks and RegardsAjit Kumar ShreevastavaADCOE (App Development Center Of Excellence )Mobile: 9717775634 ::DISCLAIMER:: The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. E-mail transmission is not guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or may contain viruses in transmission. The e mail and its contents (with or without referred errors) shall therefore not attach any liability on the originator or HCL or its affiliates. Views or opinions, if any, presented in this email are solely those of the author and may not necessarily reflect the views or opinions of HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of this message without the prior written consent of authorized representative of HCL is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. Before opening any email and/or attachments, please check them for viruses and other defects.
Report tool ISSUE.
Hi all, I am trying to install iReport on Ubuntu. I am not able to install it. Its doesn't have start.sh file in /iReport-4.7.1/bin/ it has /iReport-4.7.1/bin/ireport.exe Please suggest me how to get it install over ubuntu. Please suggest me for the linux version Thanks & Regards Yogesh Kumar
RE: how to perform GROUP BY:: in pig for this
Thanks Bejoy :-) Regards Yogesh Kumar From: bejo...@outlook.com To: user@hive.apache.org Subject: RE: how to perform GROUP BY:: in pig for this Date: Sun, 30 Sep 2012 18:57:55 +0530 Hi Yogesh If you are looking for the solution in hive, then the following query will get you the required result Select month(Date), max(rate) from date_sample Group BY month(Date); Regards Bejoy KS > From: yogesh.kuma...@wipro.com > To: user@hive.apache.org > CC: yogeshdh...@live.com > Subject: FW: how to perform GROUP BY:: in pig for this > Date: Sat, 29 Sep 2012 11:22:29 + > > > > Hi all, > > I have this data, having fields (Date, symbol, rate) > > and I want it to be group by Months, and to find out the maximum rate value > for each month. > > like: for month (08, 36.3), (09, 36.4), (10, 36.8), (11, 37.5) .. > > > (2009-08-21,CLI,33.38) > (2009-08-24,CLI,33.03) > (2009-08-25,CLI,33.16) > (2009-08-26,CLI,32.78) > (2009-08-27,CLI,32.79) > (2009-08-28,CLI,33.37) > (2009-08-31,CLI,32.51) > (2009-09-11,CLI,34.08) > (2009-09-14,CLI,35.19) > (2009-09-15,CLI,35.82) > (2009-09-16,CLI,36.58) > (2009-09-17,CLI,37.63) > (2009-09-18,CLI,37.26) > (2009-09-21,CLI,36.31) > (2009-09-22,CLI,35.88) > (2009-09-23,CLI,35.84) > (2009-09-24,CLI,33.98) > (2009-09-25,CLI,32.44) > (2009-09-28,CLI,33.34) > (2009-09-29,CLI,33.6) > (2009-09-30,CLI,33.24) > (2009-10-01,CLI,31.98) > (2009-10-02,CLI,31.21) > (2009-10-05,CLI,31.31) > (2009-10-21,CLI,32.86) > (2009-10-26,CLI,33.15) > (2009-10-27,CLI,32.71) > (2009-10-28,CLI,32.03) > (2009-10-29,CLI,32.05) > (2009-10-30,CLI,31.88) > (2009-11-02,CLI,31.88) > (2009-11-03,CLI,31.16) > (2009-11-04,CLI,31.47) > (2009-11-09,CLI,31.59) > (2009-11-25,CLI,30.58) > (2009-11-27,CLI,30.19) > (2009-11-30,CLI,30.86) > (2009-12-01,CLI,31.74) > (2009-12-02,CLI,32.62) > (2009-12-03,CLI,33.43) > (2009-12-04,CLI,34.12) > (2009-12-07,CLI,33.77) > (2009-12-08,CLI,33.8) > (2009-12-09,CLI,33.71) > > Please help and suggest . > > Thanks & Regards > Yogesh Kumar > > The information contained in this electronic message and any attachments to > this message are intended for the exclusive use of the addressee(s) and may > contain proprietary, confidential or privileged information. If you are not > the intended recipient, you should not disseminate, distribute or copy this > e-mail. Please notify the sender immediately and destroy all copies of this > message and any attachments. > > WARNING: Computer viruses can be transmitted via email. The recipient should > check this email and any attachments for the presence of viruses. The company > accepts no liability for any damage caused by any virus transmitted by this > email. > > www.wipro.com
RE: ERROR: Hive subquery showing
Hi Bejoy, I tried this one also but here it throws horrible error: i.e: hive: select name from ABD where grp=MAX(grp); FAILED: Hive Internal Error: java.lang.NullPointerException(null) java.lang.NullPointerException at org.apache.hadoop.hive.ql.plan.ExprNodeGenericFuncDesc.newInstance(ExprNodeGenericFuncDesc.java:214) at org.apache.hadoop.hive.ql.parse.TypeCheckProcFactory$DefaultExprProcessor.getXpathOrFuncExprNodeDesc(TypeCheckProcFactory.java:684) at org.apache.hadoop.hive.ql.parse.TypeCheckProcFactory$DefaultExprProcessor.process(TypeCheckProcFactory.java:805) at org.apache.hadoop.hive.ql.lib.DefaultRuleDispatcher.dispatch(DefaultRuleDispatcher.java:89) at org.apache.hadoop.hive.ql.lib.DefaultGraphWalker.dispatch(DefaultGraphWalker.java:88) at org.apache.hadoop.hive.ql.lib.DefaultGraphWalker.walk(DefaultGraphWalker.java:125) at org.apache.hadoop.hive.ql.lib.DefaultGraphWalker.startWalking(DefaultGraphWalker.java:102) at org.apache.hadoop.hive.ql.parse.TypeCheckProcFactory.genExprNode(TypeCheckProcFactory.java:161) at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genExprNodeDesc(SemanticAnalyzer.java:7506) at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genExprNodeDesc(SemanticAnalyzer.java:7464) at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genFilterPlan(SemanticAnalyzer.java:1513) at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genFilterPlan(SemanticAnalyzer.java:1494) at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genBodyPlan(SemanticAnalyzer.java:5886) at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:6524) at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:7282) at org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:243) at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:430) at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:337) at org.apache.hadoop.hive.ql.Driver.run(Driver.java:889) at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:255) at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:212) at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:403) at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:671) at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:554) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:601) at org.apache.hadoop.util.RunJar.main(RunJar.java:156) Regards Yogesh Kumar Subject: Re: ERROR: Hive subquery showing To: user@hive.apache.org From: bejoy...@yahoo.com Date: Thu, 27 Sep 2012 19:48:25 + Hi yogesh What about a query like this select name from ABC WHERE grp=MAX(grp); Regards Bejoy KS Sent from handheld, please excuse typos.From: Chen Song Date: Thu, 27 Sep 2012 15:33:11 -0400To: ReplyTo: user@hive.apache.org Subject: Re: ERROR: Hive subquery showing Can you try this? select name from ( select MAX(grp) as name from ABC ) gy ; On Thu, Sep 27, 2012 at 3:29 PM, yogesh dhari wrote: Hi all, I have a table called ABC, like namegrp A 1 B 2 C 4 D 8 I want the output like the name having greatest grp i.e D; I wrote a query: select name from ( select MAX(grp) from ABC ) gy ; but it gives error FAILED: Error in semantic analysis: Line 1:7 Invalid table alias or column reference 'name': (possible column names are: _col0) Please help and suggest why it is so, and what would be the query; Thanks & regards Yogesh Kumar -- Chen Song
RE: ERROR: Hive subquery showing
thanks Chen, I want output like ( the name and grp having highest grp) D 8 for the table. name grp A 1 B 2 C 4 D 8 Query : select name from ( select MAX(grp) as name from ABC ) gy ; showing output: 8 which can be obtained by simple : select MAX(grp) from ABC ( I think here outer query is not performing) Please Suggest Regards Yogesh Kumar Date: Thu, 27 Sep 2012 15:33:11 -0400 Subject: Re: ERROR: Hive subquery showing From: chen.song...@gmail.com To: user@hive.apache.org Can you try this? select name from ( select MAX(grp) as name from ABC ) gy ; On Thu, Sep 27, 2012 at 3:29 PM, yogesh dhari wrote: Hi all, I have a table called ABC, like name 牋 grp A牋牋 1 B牋牋 2 C牋牋 4 D牋牋 8 I want the output like the name having greatest grp i.e D; I wrote a query: select name from ( select MAX(grp) from ABC ) gy ; but it gives error FAILED: Error in semantic analysis: Line 1:7 Invalid table alias or column reference 'name': (possible column names are: _col0) Please help and suggest why it is so, and what would be the query; Thanks & regards Yogesh Kumar -- Chen Song
ERROR: Hive subquery showing
Hi all, I have a table called ABC, like namegrp A 1 B 2 C 4 D 8 I want the output like the name having greatest grp i.e D; I wrote a query: select name from ( select MAX(grp) from ABC ) gy ; but it gives error FAILED: Error in semantic analysis: Line 1:7 Invalid table alias or column reference 'name': (possible column names are: _col0) Please help and suggest why it is so, and what would be the query; Thanks & regards Yogesh Kumar
RE: how to load TAB_SEPRATED file in hive table
Thanks Ashok :-), I am not so much aware about regarding storage formats in hive(I am new to hive). Could you please list some of them except those. 1) space seprated --> FIELDS TERMINATED BY " "; 2) Control-A seprated > FIELDS TERMINATED BY '\001' 3) Tab septared --> FIELDS TERMINATED BY '\t' Please list some more. Thanks & Regards Yogesh Kumar > From: ashok.sa...@wipro.com > To: user@hive.apache.org > Subject: RE: how to load TAB_SEPRATED file in hive table > Date: Thu, 27 Sep 2012 05:20:11 + > > '\t' > > From: yogesh dhari [yogeshdh...@live.com] > Sent: Thursday, September 27, 2012 10:42 AM > To: hive request > Subject: how to load TAB_SEPRATED file in hive table > > Hi all, > > I have a file in which records are Tab-Seprated, > Please suggest me how to upload such file in Hive table. > > Like how to specify > > Create table XYZ (name STRING, roll INT) > ROW FORMAT DELIMITED > FIELDS TERMINATED BY > > Please suggest for "" over here. > > > Thanks & Regards > Yogesh Kumar > > Please do not print this email unless it is absolutely necessary. > > The information contained in this electronic message and any attachments to > this message are intended for the exclusive use of the addressee(s) and may > contain proprietary, confidential or privileged information. If you are not > the intended recipient, you should not disseminate, distribute or copy this > e-mail. Please notify the sender immediately and destroy all copies of this > message and any attachments. > > WARNING: Computer viruses can be transmitted via email. The recipient should > check this email and any attachments for the presence of viruses. The company > accepts no liability for any damage caused by any virus transmitted by this > email. > > www.wipro.com
how to load TAB_SEPRATED file in hive table
Hi all, I have a file in which records are Tab-Seprated, Please suggest me how to upload such file in Hive table. Like how to specify Create table XYZ (name STRING, roll INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY Please suggest for "" over here. Thanks & Regards Yogesh Kumar
RE: ERROR :regarding Hive WI, hwi service is not running
Hi Bertrand and Swarnim, Its done now :-) The issue was that the error message is not actually printing the path it is looking in. the path is relative to hive home. I tried /lib/hive-hwi-0.8.1.war instead of /opt/hive-0.8.1/lib/hive-hwi-0.5.0.war and its running now :-) Regards Yogesh Kumar Date: Thu, 20 Sep 2012 00:05:45 +0200 Subject: Re: ERROR :regarding Hive WI, hwi service is not running From: decho...@gmail.com To: user@hive.apache.org Check your configuration (and the default value) and change it.I know there was issues about the check and/or the error message for previous versions of hive.I had the same issue but can't remember the exact details (maybe it was due to relative path vs absolute). Try1) rename the war2) put the absolute path in your conf3) run it Regards Bertrand On Thu, Sep 20, 2012 at 12:02 AM, yogesh dhari wrote: Swarnim, "opt/hive-0.8.1/lib" already exists, its the path of hive dir & hive-hwi-0.8.1.war already exists there. /opt/hive-0.8.1/lib ls -l -rw-rw-r-- 1 root root 55876 Jan 26 2012 hive-common-0.8.1.jar -rw-rw-r-- 1 root root 112440 Jan 26 2012 hive-contrib-0.8.1.jar -rw-rw-r-- 1 root root 112440 Jan 26 2012 hive_contrib.jar -rw-rw-r-- 1 root root 3461228 Jan 26 2012 hive-exec-0.8.1.jar -rw-rw-r-- 1 root root 48829 Jan 26 2012 hive-hbase-handler-0.8.1.jar -rw-rw-r-- 1 root root 23529 Jan 26 2012 hive-hwi-0.8.1.jar -rwxrwxrwx 1 root root 28413 Jan 26 2012 hive-hwi-0.8.1.war -rw-rw-r-- 1 root root 58914 Jan 26 2012 hive-jdbc-0.8.1.jar -rw-rw-r-- 1 root root 1765743 Jan 26 2012 hive-metastore-0.8.1.jar Thats what its very surprising :-( Thanks & Regards Yogesh Kumar From: kulkarni.swar...@gmail.com Date: Wed, 19 Sep 2012 16:58:01 -0500 Subject: Re: ERROR :regarding Hive WI, hwi service is not running To: user@hive.apache.org No. I meant create "/opt/hive-0.8.1/lib/" in HDFS and place the "hive-hwi-0.8.1.war" there. On Wed, Sep 19, 2012 at 4:55 PM, yogesh dhari wrote: Hello Swarnim, Are you saying to put hive-hwi-0.8.1.war into hadoop/lib ? I have put it over there and still the same issue.. Thanks & Regards Yogesh Kumar From: kulkarni.swar...@gmail.com Date: Wed, 19 Sep 2012 16:48:37 -0500 Subject: Re: ERROR :regarding Hive WI, hwi service is not running To: user@hive.apache.org It's probably looking for that file on HDFS. Try placing it there under the given location and see if you get the same error. On Wed, Sep 19, 2012 at 4:45 PM, yogesh dhari wrote: Hi all, I am trying to run hive wi but its showing FATAL, I have used this command hive --service hwi but it shows.. yogesh@yogesh-Aspire-5738:/opt/hive-0.8.1/lib$ hive --service hwi 12/09/20 03:12:03 INFO hwi.HWIServer: HWI is starting up 12/09/20 03:12:04 FATAL hwi.HWIServer: HWI WAR file not found at /opt/hive-0.8.1/lib/hive-hwi-0.8.1.war although lies there. yogesh@yogesh-Aspire-5738:/opt/hive-0.8.1/lib$ pwd /opt/hive-0.8.1/lib ls -l -rw-rw-r-- 1 root root 55876 Jan 26 2012 hive-common-0.8.1.jar -rw-rw-r-- 1 root root 112440 Jan 26 2012 hive-contrib-0.8.1.jar -rw-rw-r-- 1 root root 112440 Jan 26 2012 hive_contrib.jar -rw-rw-r-- 1 root root 3461228 Jan 26 2012 hive-exec-0.8.1.jar -rw-rw-r-- 1 root root 48829 Jan 26 2012 hive-hbase-handler-0.8.1.jar -rw-rw-r-- 1 root root 23529 Jan 26 2012 hive-hwi-0.8.1.jar -rwxrwxrwx 1 root root 28413 Jan 26 2012 hive-hwi-0.8.1.war -rw-rw-r-- 1 root root 58914 Jan 26 2012 hive-jdbc-0.8.1.jar -rw-rw-r-- 1 root root 1765743 Jan 26 2012 hive-metastore-0.8.1.jar -rw-rw-r-- 1 root root 14081 Jan 26 2012 hive-pdk-0.8.1.jar -rw-rw-r-- 1 root root 509488 Jan 26 2012 hive-serde-0.8.1.jar -rw-rw-r-- 1 root root 174445 Jan 26 2012 hive-service-0.8.1.jar -rw-rw-r-- 1 root root 110154 Jan 26 2012 hive-shims-0.8.1.jar -rw-rw-r-- 1 root root 15260 Jan 24 2012 javaewah-0.3.jar -rw-rw-r-- 1 root root 198552 Dec 24 2009 jdo2-api-2.3-ec.jar Please suggest regarding Thanks & regards Yogesh Kumar -- Swarnim -- Swarnim -- Bertrand Dechoux
RE: ERROR :regarding Hive WI, hwi service is not running
Swarnim, "opt/hive-0.8.1/lib" already exists, its the path of hive dir & hive-hwi-0.8.1.war already exists there. /opt/hive-0.8.1/lib ls -l -rw-rw-r-- 1 root root 55876 Jan 26 2012 hive-common-0.8.1.jar -rw-rw-r-- 1 root root 112440 Jan 26 2012 hive-contrib-0.8.1.jar -rw-rw-r-- 1 root root 112440 Jan 26 2012 hive_contrib.jar -rw-rw-r-- 1 root root 3461228 Jan 26 2012 hive-exec-0.8.1.jar -rw-rw-r-- 1 root root 48829 Jan 26 2012 hive-hbase-handler-0.8.1.jar -rw-rw-r-- 1 root root 23529 Jan 26 2012 hive-hwi-0.8.1.jar -rwxrwxrwx 1 root root 28413 Jan 26 2012 hive-hwi-0.8.1.war -rw-rw-r-- 1 root root 58914 Jan 26 2012 hive-jdbc-0.8.1.jar -rw-rw-r-- 1 root root 1765743 Jan 26 2012 hive-metastore-0.8.1.jar Thats what its very surprising :-( Thanks & Regards Yogesh Kumar From: kulkarni.swar...@gmail.com Date: Wed, 19 Sep 2012 16:58:01 -0500 Subject: Re: ERROR :regarding Hive WI, hwi service is not running To: user@hive.apache.org No. I meant create "/opt/hive-0.8.1/lib/" in HDFS and place the "hive-hwi-0.8.1.war" there. On Wed, Sep 19, 2012 at 4:55 PM, yogesh dhari wrote: Hello Swarnim, Are you saying to put hive-hwi-0.8.1.war into hadoop/lib ? I have put it over there and still the same issue.. Thanks & Regards Yogesh Kumar From: kulkarni.swar...@gmail.com Date: Wed, 19 Sep 2012 16:48:37 -0500 Subject: Re: ERROR :regarding Hive WI, hwi service is not running To: user@hive.apache.org It's probably looking for that file on HDFS. Try placing it there under the given location and see if you get the same error. On Wed, Sep 19, 2012 at 4:45 PM, yogesh dhari wrote: Hi all, I am trying to run hive wi but its showing FATAL, I have used this command hive --service hwi but it shows.. yogesh@yogesh-Aspire-5738:/opt/hive-0.8.1/lib$ hive --service hwi 12/09/20 03:12:03 INFO hwi.HWIServer: HWI is starting up 12/09/20 03:12:04 FATAL hwi.HWIServer: HWI WAR file not found at /opt/hive-0.8.1/lib/hive-hwi-0.8.1.war although lies there. yogesh@yogesh-Aspire-5738:/opt/hive-0.8.1/lib$ pwd /opt/hive-0.8.1/lib ls -l -rw-rw-r-- 1 root root 55876 Jan 26 2012 hive-common-0.8.1.jar -rw-rw-r-- 1 root root 112440 Jan 26 2012 hive-contrib-0.8.1.jar -rw-rw-r-- 1 root root 112440 Jan 26 2012 hive_contrib.jar -rw-rw-r-- 1 root root 3461228 Jan 26 2012 hive-exec-0.8.1.jar -rw-rw-r-- 1 root root 48829 Jan 26 2012 hive-hbase-handler-0.8.1.jar -rw-rw-r-- 1 root root 23529 Jan 26 2012 hive-hwi-0.8.1.jar -rwxrwxrwx 1 root root 28413 Jan 26 2012 hive-hwi-0.8.1.war -rw-rw-r-- 1 root root 58914 Jan 26 2012 hive-jdbc-0.8.1.jar -rw-rw-r-- 1 root root 1765743 Jan 26 2012 hive-metastore-0.8.1.jar -rw-rw-r-- 1 root root 14081 Jan 26 2012 hive-pdk-0.8.1.jar -rw-rw-r-- 1 root root 509488 Jan 26 2012 hive-serde-0.8.1.jar -rw-rw-r-- 1 root root 174445 Jan 26 2012 hive-service-0.8.1.jar -rw-rw-r-- 1 root root 110154 Jan 26 2012 hive-shims-0.8.1.jar -rw-rw-r-- 1 root root 15260 Jan 24 2012 javaewah-0.3.jar -rw-rw-r-- 1 root root 198552 Dec 24 2009 jdo2-api-2.3-ec.jar Please suggest regarding Thanks & regards Yogesh Kumar -- Swarnim -- Swarnim
RE: ERROR :regarding Hive WI, hwi service is not running
Hello Swarnim, Are you saying to put hive-hwi-0.8.1.war into hadoop/lib ? I have put it over there and still the same issue.. Thanks & Regards Yogesh Kumar From: kulkarni.swar...@gmail.com Date: Wed, 19 Sep 2012 16:48:37 -0500 Subject: Re: ERROR :regarding Hive WI, hwi service is not running To: user@hive.apache.org It's probably looking for that file on HDFS. Try placing it there under the given location and see if you get the same error. On Wed, Sep 19, 2012 at 4:45 PM, yogesh dhari wrote: Hi all, I am trying to run hive wi but its showing FATAL, I have used this command hive --service hwi but it shows.. yogesh@yogesh-Aspire-5738:/opt/hive-0.8.1/lib$ hive --service hwi 12/09/20 03:12:03 INFO hwi.HWIServer: HWI is starting up 12/09/20 03:12:04 FATAL hwi.HWIServer: HWI WAR file not found at /opt/hive-0.8.1/lib/hive-hwi-0.8.1.war although lies there. yogesh@yogesh-Aspire-5738:/opt/hive-0.8.1/lib$ pwd /opt/hive-0.8.1/lib ls -l -rw-rw-r-- 1 root root 55876 Jan 26 2012 hive-common-0.8.1.jar -rw-rw-r-- 1 root root 112440 Jan 26 2012 hive-contrib-0.8.1.jar -rw-rw-r-- 1 root root 112440 Jan 26 2012 hive_contrib.jar -rw-rw-r-- 1 root root 3461228 Jan 26 2012 hive-exec-0.8.1.jar -rw-rw-r-- 1 root root 48829 Jan 26 2012 hive-hbase-handler-0.8.1.jar -rw-rw-r-- 1 root root 23529 Jan 26 2012 hive-hwi-0.8.1.jar -rwxrwxrwx 1 root root 28413 Jan 26 2012 hive-hwi-0.8.1.war -rw-rw-r-- 1 root root 58914 Jan 26 2012 hive-jdbc-0.8.1.jar -rw-rw-r-- 1 root root 1765743 Jan 26 2012 hive-metastore-0.8.1.jar -rw-rw-r-- 1 root root 14081 Jan 26 2012 hive-pdk-0.8.1.jar -rw-rw-r-- 1 root root 509488 Jan 26 2012 hive-serde-0.8.1.jar -rw-rw-r-- 1 root root 174445 Jan 26 2012 hive-service-0.8.1.jar -rw-rw-r-- 1 root root 110154 Jan 26 2012 hive-shims-0.8.1.jar -rw-rw-r-- 1 root root 15260 Jan 24 2012 javaewah-0.3.jar -rw-rw-r-- 1 root root 198552 Dec 24 2009 jdo2-api-2.3-ec.jar Please suggest regarding Thanks & regards Yogesh Kumar -- Swarnim
ERROR :regarding Hive WI, hwi service is not running
Hi all, I am trying to run hive wi but its showing FATAL, I have used this command hive --service hwi but it shows.. yogesh@yogesh-Aspire-5738:/opt/hive-0.8.1/lib$ hive --service hwi 12/09/20 03:12:03 INFO hwi.HWIServer: HWI is starting up 12/09/20 03:12:04 FATAL hwi.HWIServer: HWI WAR file not found at /opt/hive-0.8.1/lib/hive-hwi-0.8.1.war although lies there. yogesh@yogesh-Aspire-5738:/opt/hive-0.8.1/lib$ pwd /opt/hive-0.8.1/lib ls -l -rw-rw-r-- 1 root root 55876 Jan 26 2012 hive-common-0.8.1.jar -rw-rw-r-- 1 root root 112440 Jan 26 2012 hive-contrib-0.8.1.jar -rw-rw-r-- 1 root root 112440 Jan 26 2012 hive_contrib.jar -rw-rw-r-- 1 root root 3461228 Jan 26 2012 hive-exec-0.8.1.jar -rw-rw-r-- 1 root root 48829 Jan 26 2012 hive-hbase-handler-0.8.1.jar -rw-rw-r-- 1 root root 23529 Jan 26 2012 hive-hwi-0.8.1.jar -rwxrwxrwx 1 root root 28413 Jan 26 2012 hive-hwi-0.8.1.war -rw-rw-r-- 1 root root 58914 Jan 26 2012 hive-jdbc-0.8.1.jar -rw-rw-r-- 1 root root 1765743 Jan 26 2012 hive-metastore-0.8.1.jar -rw-rw-r-- 1 root root 14081 Jan 26 2012 hive-pdk-0.8.1.jar -rw-rw-r-- 1 root root 509488 Jan 26 2012 hive-serde-0.8.1.jar -rw-rw-r-- 1 root root 174445 Jan 26 2012 hive-service-0.8.1.jar -rw-rw-r-- 1 root root 110154 Jan 26 2012 hive-shims-0.8.1.jar -rw-rw-r-- 1 root root 15260 Jan 24 2012 javaewah-0.3.jar -rw-rw-r-- 1 root root 198552 Dec 24 2009 jdo2-api-2.3-ec.jar Please suggest regarding Thanks & regards Yogesh Kumar
RE: FAILED: Error in metadata
thanks all for your guidance :-) its done now Regards Yogesh kumar From: rohithsharm...@huawei.com To: user@hive.apache.org Subject: RE: FAILED: Error in metadata Date: Tue, 11 Sep 2012 14:35:18 +0530 Hi Exception trace clearly indicates that hive is pointing to default configurations.Make sure your editing hive-site.xml is loaded by hive server. For verifying whether correct configurations are loaded, you enable DEBUG logs.There you can see the loaded configurations. Verify your configurations with loaded configuration (In hive.log). Regardsrohith Sharma k s From: Ravindra [mailto:ravindra.baj...@gmail.com] Sent: Monday, September 10, 2012 11:49 AM To: user@hive.apache.org Subject: Re: FAILED: Error in metadata I am new to Hive, Still 2 cents - 1. Do you have metastore_db already created, I don't see this name in your hive-site (you have "try"). 2. Hope you have your database client driver jar copied in the hive classpath. -- Ravi. ''We do not inherit the earth from our ancestors, we borrow it from our children.'' PROTECT IT ! On Mon, Sep 10, 2012 at 10:40 AM, Hongjie Guo wrote:check your mysql first, if you can see some tables like "TBLS","COLUMNS", the mysql should work fine. otherwise you should check your configuration about the hive-site.xml 2012/9/10 yogesh dhari Helli Bejoy, I have restarted the hive and all cluster again but still the same issue. Please help me out Thanks & regards Yogesh KumarDate: Sun, 9 Sep 2012 02:21:47 -0700 From: bejoy...@yahoo.com Subject: Re: FAILED: Error in metadata To: user@hive.apache.org Hi Yogesh It looks like hive is still on the derby db . Can you restart your hive instances after updating the hive-site.xml. Also please make sure that you are modifying the right copy of the file. Regards,Bejoy KS From: yogesh dhari To: hive request Sent: Sunday, September 9, 2012 2:21 PM Subject: FAILED: Error in metadata Hi all, my hive-site.xml is javax.jdo.option.ConnectionURL jdbc:mysql://127.0.0.1:3306/try?createDatabaseIfNotExist=true javax.jdo.option.ConnectionDriverName com.mysql.jdbc.Driver javax.jdo.option.ConnectionUserName hadoop javax.jdo.option.ConnectionPassword hadoop and created a user in my sql. CREATE USER 'hadoop'@'localhost' IDENTIFIED BY 'hadoop'; GRANT ALL PRIVILEGES ON *.* TO 'hadoop' WITH GRANT OPTION; Schema name is try, and connection name is Demo When I run hive in single terminal then it runs fine but when i try to run hive parallel on new terminal window it shows the error: FAILED: Error in metadata: javax.jdo.JDOFatalDataStoreException: Failed to start database 'metastore_db', see the next exception for details. NestedThrowables: java.sql.SQLException: Failed to start database 'metastore_db', see the next exception for details. FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask Please help and suggest Yogesh Kumar
RE: FAILED: Error in metadata
Helli Bejoy, I have restarted the hive and all cluster again but still the same issue. Please help me out Thanks & regards Yogesh Kumar Date: Sun, 9 Sep 2012 02:21:47 -0700 From: bejoy...@yahoo.com Subject: Re: FAILED: Error in metadata To: user@hive.apache.org Hi Yogesh It looks like hive is still on the derby db . Can you restart your hive instances after updating the hive-site.xml. Also please make sure that you are modifying the right copy of the file. Regards,Bejoy KS From: yogesh dhari To: hive request Sent: Sunday, September 9, 2012 2:21 PM Subject: FAILED: Error in metadata Hi all, my hive-site.xml is javax.jdo.option.ConnectionURL jdbc:mysql://127.0.0.1:3306/try?createDatabaseIfNotExist=true javax.jdo.option.ConnectionDriverName com.mysql.jdbc.Driver javax.jdo.option.ConnectionUserName hadoop javax.jdo.option.ConnectionPassword hadoop and created a user in my sql. CREATE USER 'hadoop'@'localhost' IDENTIFIED BY 'hadoop'; GRANT ALL PRIVILEGES ON *.* TO 'hadoop' WITH GRANT OPTION; Schema name is try, and connection name is Demo When I run hive in single terminal then it runs fine but when i try to run hive parallel on new terminal window it shows the error: FAILED: Error in metadata: javax.jdo.JDOFatalDataStoreException: Failed to start database 'metastore_db', see the next exception for details. NestedThrowables: java.sql.SQLException: Failed to start database 'metastore_db', see the next exception for details. FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask Please help and suggest Yogesh Kumar
FAILED: Error in metadata
Hi all, my hive-site.xml is javax.jdo.option.ConnectionURL jdbc:mysql://127.0.0.1:3306/try?createDatabaseIfNotExist=true javax.jdo.option.ConnectionDriverName com.mysql.jdbc.Driver javax.jdo.option.ConnectionUserName hadoop javax.jdo.option.ConnectionPassword hadoop and created a user in my sql. CREATE USER 'hadoop'@'localhost' IDENTIFIED BY 'hadoop'; GRANT ALL PRIVILEGES ON *.* TO 'hadoop' WITH GRANT OPTION; Schema name is try, and connection name is Demo When I run hive in single terminal then it runs fine but when i try to run hive parallel on new terminal window it shows the error: FAILED: Error in metadata: javax.jdo.JDOFatalDataStoreException: Failed to start database 'metastore_db', see the next exception for details. NestedThrowables: java.sql.SQLException: Failed to start database 'metastore_db', see the next exception for details. FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask Please help and suggest Yogesh Kumar
issue regarding importing hive tables from one cluster to another.
Hi all, I have switched to new hdfs cluster from old cluster ( all machines from old cluster are not connected to new cluster in any manner) I brought edit and fsimage including ( dfs.name.dir and dfs.data.dir )from old cluster and put it over new cluster and every files and data are showing over new cluster. is there any way out by which I can bring all tables created and there structure in hive from old cluster to new to new cluster.. Thanks & regards Yogesh Kumar
RE: namenode starting error
Hi, have you formated your name node by using hadoop namenode -format Regards Yogesh Kumar > From: are9...@nyp.org > To: user@hive.apache.org; common-u...@hadoop.apache.org > Date: Sat, 7 Jul 2012 21:37:16 -0400 > Subject: Re: namenode starting error > > Check your firewall settings, specifically if port 54310 is open. The > other ports to look at are: > > 50010 > 50020 > 50030 > 50060 > 50070 > 54311 > > On 6/20/12 5:22 AM, "soham sardar" wrote: > > >the thing is i have updated my JAVA_HOME in both .bashrc and hadoop-env.sh > > > > > >now the problem is when i try > >jps is the output is > > > >6113 NodeManager > >6799 DataNode > >7562 Jps > >7104 SecondaryNameNode > >5728 ResourceManager > > > >and now except namenode ,all other nodes are working and when i try to > >give > > > >hadoop fs -ls > > > >hduser@XPS-L501X:/home/soham/cloudera/hadoop-2.0.0-cdh4.0.0/sbin$ hadoop > >fs -ls > >12/06/20 14:45:14 INFO ipc.Client: Retrying connect to server: > >localhost/127.0.0.1:54310. Already tried 0 time(s). > >12/06/20 14:45:15 INFO ipc.Client: Retrying connect to server: > >localhost/127.0.0.1:54310. Already tried 1 time(s). > >12/06/20 14:45:16 INFO ipc.Client: Retrying connect to server: > >localhost/127.0.0.1:54310. Already tried 2 time(s). > >12/06/20 14:45:17 INFO ipc.Client: Retrying connect to server: > >localhost/127.0.0.1:54310. Already tried 3 time(s). > >12/06/20 14:45:18 INFO ipc.Client: Retrying connect to server: > >localhost/127.0.0.1:54310. Already tried 4 time(s). > >12/06/20 14:45:19 INFO ipc.Client: Retrying connect to server: > >localhost/127.0.0.1:54310. Already tried 5 time(s). > >12/06/20 14:45:20 INFO ipc.Client: Retrying connect to server: > >localhost/127.0.0.1:54310. Already tried 6 time(s). > >12/06/20 14:45:21 INFO ipc.Client: Retrying connect to server: > >localhost/127.0.0.1:54310. Already tried 7 time(s). > >12/06/20 14:45:22 INFO ipc.Client: Retrying connect to server: > >localhost/127.0.0.1:54310. Already tried 8 time(s). > >12/06/20 14:45:23 INFO ipc.Client: Retrying connect to server: > >localhost/127.0.0.1:54310. Already tried 9 time(s). > >ls: Call From XPS-L501X/127.0.1.1 to localhost:54310 failed on > >connection exception: java.net.ConnectException: Connection refused; > >For more details see: http://wiki.apache.org/hadoop/ConnectionRefused > > > >could u temme why this error is coming ? > > > This electronic message is intended to be for the use only of the named > recipient, and may contain information that is confidential or privileged. If > you are not the intended recipient, you are hereby notified that any > disclosure, copying, distribution or use of the contents of this message is > strictly prohibited. If you have received this message in error or are not > the named recipient, please notify us immediately by contacting the sender at > the electronic mail address noted above, and delete and destroy all copies of > this message. Thank you. > > > > > This electronic message is intended to be for the use only of the named > recipient, and may contain information that is confidential or privileged. > If you are not the intended recipient, you are hereby notified that any > disclosure, copying, distribution or use of the contents of this message is > strictly prohibited. If you have received this message in error or are not > the named recipient, please notify us immediately by contacting the sender at > the electronic mail address noted above, and delete and destroy all copies of > this message. Thank you. > > > > > > > This electronic message is intended to be for the use only of the named > recipient, and may contain information that is confidential or privileged. > If you are not the intended recipient, you are hereby notified that any > disclosure, copying, distribution or use of the contents of this message is > strictly prohibited. If you have received this message in error or are not > the named recipient, please notify us immediately by contacting the sender at > the electronic mail address noted above, and delete and destroy all copies of > this message. Thank you. > > >
RE: Hive upload
Hi Bejoy, Thank you very much for your response, 1) A) When I run command show tables it doesn't show newhive table.B) Yes the the newhive directory is present into /user/hive/warehouse and also containing the values imported from RDBMS Please suggest and give me an example for the sqoop import command according to you for this case. 2) A) Here is the command describe formatted letstry; OK # col_namedata_type comment rollno int None namestring None numbr int None sno int None # Detailed Table Information Database: default Owner: mediaadmin CreateTime: Tue Jul 03 17:06:27 GMT+05:30 2012 LastAccessTime: UNKNOWN Protect Mode: None Retention: 0 Location: hdfs://localhost:9000/user/hive/warehouse/letstry Table Type: MANAGED_TABLE Table Parameters: transient_lastDdlTime1341315550 # Storage Information SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe InputFormat:org.apache.hadoop.mapred.TextInputFormat OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat Compressed: No Num Buckets:-1 Bucket Columns: [] Sort Columns: [] Storage Desc Params: serialization.format1 Time taken: 0.101 seconds B) hadoop dfs -ls /user/hive/warehouse/letstry/ Found 1 items -rw-r--r-- 1 mediaadmin supergroup 17 2012-07-02 12:05 /user/hive/warehouse/letstry/part-m-0 hadoop dfs -cat /user/hive/warehouse/letstry/part-m-0 1,John,123,abc,2 Here data is present but when I upload it to Hive it gets deleted from HDFS and in Hive value appers NULL instead of ( 1,John,123,abc,2). and I didn't understad your point regarding correct data format? ( this data was imported from Mysql table)And what kind of confugration neede in sqoop Please suggest and help GreetingsYogesh Kumar Subject: Re: Hive upload To: user@hive.apache.org From: bejoy...@yahoo.com Date: Wed, 4 Jul 2012 05:58:41 + Hi Yogesh The first issue (sqoop one). 1) Is the table newhive coming when you list tables using 'show table'? 2) Are you seeing a directory 'newhive' in your hive warte house dir(usually /usr/hive/warehouse)? If not sqoop is failing to create hive tables /load data into them. Only sqoop import to hdfs is getting successful the hive part is failing. If hive in stand alone mode works as desired you need to check the sqoop configurations. Regarding the second issue, can you check the storage location of NewTable and check whether there are files within. If so then do a 'cat' of those files and see whether it has the correct data format. You can get the location of your table from the following command describe formatted NewTable; Regards Bejoy KS Sent from handheld, please excuse typos.From: yogesh dhari Date: Wed, 4 Jul 2012 11:09:02 +0530To: hive requestReplyTo: user@hive.apache.org Subject: Hive upload Hi all, I am trying to upload the tables from RDBMS to hive through sqoop, hive imports successfully. but i didn't find any table in hive that imported table gets uploaded into hdfs idr /user/hive/warehouseI want it to be present into hive, I used this command sqoop import --connect jdbc:mysql://localhost:3306/Demo --username sqoop1 --password SQOOP1 -table newone --hive-table newhive --create-hive-table --hive-import --target-dir /user/hive/warehouse/new And another thing is,If I upload any file or table from HDFS or from Local then its uploads but data doesn't show in Hive table, If I run command Select * from NewTable;it reflects Null Null NullNull although the real data is Yogesh4Bangalore 1234 Please Suggest and help RegardsYogesh Kumar
Hive upload
Hi all, I am trying to upload the tables from RDBMS to hive through sqoop, hive imports successfully. but i didn't find any table in hive that imported table gets uploaded into hdfs idr /user/hive/warehouseI want it to be present into hive, I used this command sqoop import --connect jdbc:mysql://localhost:3306/Demo --username sqoop1 --password SQOOP1 -table newone --hive-table newhive --create-hive-table --hive-import --target-dir /user/hive/warehouse/new And another thing is,If I upload any file or table from HDFS or from Local then its uploads but data doesn't show in Hive table, If I run command Select * from NewTable;it reflects Null Null NullNull although the real data is Yogesh4Bangalore 1234 Please Suggest and help RegardsYogesh Kumar