Return-Path: X-Original-To: apmail-hive-user-archive@www.apache.org Delivered-To: apmail-hive-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id E8A5D9596 for ; Tue, 22 Nov 2011 07:27:17 +0000 (UTC) Received: (qmail 29964 invoked by uid 500); 22 Nov 2011 07:27:17 -0000 Delivered-To: apmail-hive-user-archive@hive.apache.org Received: (qmail 29914 invoked by uid 500); 22 Nov 2011 07:27:15 -0000 Mailing-List: contact user-help@hive.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@hive.apache.org Delivered-To: mailing list user@hive.apache.org Received: (qmail 29897 invoked by uid 99); 22 Nov 2011 07:27:15 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 22 Nov 2011 07:27:15 +0000 X-ASF-Spam-Status: No, hits=-0.1 required=5.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_MED,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: local policy) Received: from [122.98.14.32] (HELO kecgate02.infosys.com) (122.98.14.32) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 22 Nov 2011 07:27:07 +0000 X-TM-IMSS-Message-ID: Received: from blrkechub01.ad.infosys.com ([10.66.236.41]) by infosys.com ([122.98.14.32]) with ESMTP (TREND IMSS SMTP Service 7.1) id a38dfe710013f1df ; Tue, 22 Nov 2011 12:55:50 +0530 Received: from BLRKECHUB05.ad.infosys.com (10.66.236.45) by blrkechub01.ad.infosys.com (10.66.236.41) with Microsoft SMTP Server (TLS) id 8.2.176.0; Tue, 22 Nov 2011 12:56:19 +0530 Received: from BLRKECMBX01.ad.infosys.com ([10.66.236.21]) by BLRKECHUB05.ad.infosys.com ([10.66.236.45]) with mapi; Tue, 22 Nov 2011 12:56:17 +0530 From: Aditya Singh30 To: "user@hive.apache.org" Date: Tue, 22 Nov 2011 12:56:16 +0530 Subject: RE: Mysql metastore configuration error. Thread-Topic: Mysql metastore configuration error. Thread-Index: Acyo5JgyAlYTLW06TPWtZuSrWcfclgAAx1FA Message-ID: <3328D456F567BB42856F60C58D32F0C3215E2D2075@BLRKECMBX01.ad.infosys.com> References: <3328D456F567BB42856F60C58D32F0C3215E2D19C8@BLRKECMBX01.ad.infosys.com> In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: acceptlanguage: en-US Content-Type: multipart/alternative; boundary="_000_3328D456F567BB42856F60C58D32F0C3215E2D2075BLRKECMBX01ad_" MIME-Version: 1.0 X-Virus-Checked: Checked by ClamAV on apache.org --_000_3328D456F567BB42856F60C58D32F0C3215E2D2075BLRKECMBX01ad_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Sorry It was a typo.. I used "Load data local inpath 'path/to/abcd.txt' into table abcd;" only Thanks for pointing it out Stephen. Regards, Aditya From: Stephen Boesch [mailto:javadba@gmail.com] Sent: Tuesday, November 22, 2011 12:32 PM To: user@hive.apache.org Subject: Re: Mysql metastore configuration error. Was that code above verbatim? because there is a typo Hive> Load sata local inpath 'path/to/abcd.txt' into table abcd; (load sata not load data) 2011/11/21 Aditya Singh30 > Hi Everybody, I am using Apache's Hadoop-0.20.2 and Apach= e's Hive-0.7.0. I have a 2 node cluster. One Redhat Linux 6.0(Hadoop Server= ) and other Windows 7 using Cygwin. The Hadoop cluster is working fine. I h= ave checked by executing various examples provided with Hadoop. Map reduce = jobs are being executed fine. For Hive I am using MySQL for metastore with = following configuration is hive-site.xml : javax.jdo.option.ConnectionURL jdbc:mysql://localhost:3306/metastore?createDatabaseIfNotExist=3Dtru= e javax.jdo.option.ConnectionDriverName com.mysql.jdbc.Driver javax.jdo.option.ConnectionUserName hiveuser javax.jdo.option.ConnectionPassword hiveuser datanucleus.autoCreateSchema false datanucleus.fixedDatastore true I created the DB and hiveuser in mysql using following commands: mysql> CREATE DATABASE metastore; mysql> USE metastore; mysql> SOURCE /usr/local/hive/scripts/metastore/upgrade/mysql/hive-schema-0= .7.0.mysql.sql; mysql> CREATE USER 'hiveuser'@'%' IDENTIFIED BY 'hiveuser'; mysql> GRANT ALL ON metastore.* TO 'hiveuser'@'%'; I created a table using the following command on hive: hive> Create table abcd(ab int, cd string) row format delimited fields term= inated by '#' stored as textfile; Then I created a file abcddata.txt containing the following data 11#aa 22#bb 33#cc Then I loaded this data into table abcd using : Hive> Load sata local inpath 'path/to/abcd.txt' into table abcd; Now when I execute "select * from abcd" it runs successfully and shows the = data in abcd. But if I run "select ab from abcd" or " select * from abcd where cd=3D'aa'"= it returns error: FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.= MapRedTask In the logs I found: Caused by: java.util.NoSuchElementException at java.util.Vector.lastElement(Vector.java:456) at com.sun.beans.ObjectHandler.lastExp(ObjectHandler.java:134) at com.sun.beans.ObjectHandler.dequeueResult(ObjectHandler.java:138= ) at java.beans.XMLDecoder.readObject(XMLDecoder.java:201) at org.apache.hadoop.hive.ql.exec.Utilities.deserializeMapRedWork(U= tilities.java:462) at org.apache.hadoop.hive.ql.exec.Utilities.getMapRedWork(Utilities= .java:184) And when I tried to access Hive from a java program using connection string= : (jdbc:mysql://master:3306/metastore","hiveuser","hiveuser") Running command "describe abcd" it returns: Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxError= Exception: Table 'metastore.abcd' doesn't exist Then on the mysql server I ran: mysql> use metastore; mysql> show tables; The table abcd is not there. The table is not being stored in the mysql met= astore db. So how come on Hive CLI, when I do "select * from abcd" it shows the data i= n the table. And "show tables" shows abcd there. It means Hive CLI is not u= sing the mysql metastore for storing and "select *" statement but whenever = it's a statement that requires map reduce jobs or while accessing via java = program using connection string it uses mysql metastore. It must be some co= nfiguration mistake I think. Please help me out. Regards, Aditya Singh Infosys, India. **************** CAUTION - Disclaimer ***************** This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solel= y for the use of the addressee(s). If you are not the intended recipient, ple= ase notify the sender by e-mail and delete the original message. Further, you a= re not to copy, disclose, or distribute this e-mail or its contents to any other p= erson and any such actions are unlawful. This e-mail may contain viruses. Infosys has= taken every reasonable precaution to minimize this risk, but is not liable for an= y damage you may sustain as a result of any virus in this e-mail. You should carry o= ut your own virus checks before opening the e-mail or attachment. Infosys reserves = the right to monitor and review the content of all messages sent to or from thi= s e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system. ***INFOSYS******** End of Disclaimer ********INFOSYS*** --_000_3328D456F567BB42856F60C58D32F0C3215E2D2075BLRKECMBX01ad_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Sorry It was a typo..

 

I used “Load data local inpath ‘path/to/abcd.txt’ into table abcd;= ” only

 

=

Thanks for pointing it out Stephen.

 

Regards,

Aditya

 <= /o:p>

 

 

<= b>From:<= /span> Stephen Boesch [mailto:javadba@gmail.com]
Sent: Tuesday, Nove= mber 22, 2011 12:32 PM
To: user@hive.apache.org
Subject: Re: Mysql metastore configuration error.

 

Was that code above  verbatim? <= /p>

because there is a typ= o

 

Hive= > Load sata local inpath ‘path/to/abcd.txt’ into table= abcd;

&nb= sp;

(load sata not load data)=

2011/11/21 Aditya Singh30 <Aditya_Singh30@infosys.com>

Hi Everybody,

   =             &nb= sp;            =     I am using Apache’s Hadoop-0.20.2 and Apache’= ;s Hive-0.7.0. I have a 2 node cluster. One Redhat Linux 6.0(Hadoop Server)= and other Windows 7 using Cygwin. The Hadoop cluster is working fine. I ha= ve checked by executing various examples provided with Hadoop. Map reduce j= obs are being executed fine. For Hive I am using MySQL for metastore with f= ollowing configuration is hive-site.xml :

 <= /o:p>

<property>  

<name>javax.jdo.option.ConnectionURL</name>  

<value>jdbc:mysql://localhost:3306/meta= store?createDatabaseIfNotExist=3Dtrue</value>

<= p class=3DMsoNormal style=3D'mso-margin-top-alt:auto;mso-margin-bottom-alt:= auto;text-autospace:none'></property>  

&nbs= p;

<property>   =

<name>javax.jdo.option.ConnectionDriverName</n= ame>  

<value>com.mysql.= jdbc.Driver</value>

</property>= ;  

 

=

<property>  

<= name>javax.jdo.option.ConnectionUserName</name>  

<value>hiveuser</value> <= /o:p>

</property>  

 

<property>  

<name>javax.jdo.option.ConnectionPasswo= rd</name>  

<value>hi= veuser</value>

</property> = ; 

 

<property> <= /p>

 &= nbsp;<name>datanucleus.autoCreateSchema</name>

&nbs= p; <value>false</value>

</property>

  

<property>

  &l= t;name>datanucleus.fixedDatastore</name>

  &= lt;value>true</value>

</property>

 

 

I cre= ated the DB and hiveuser in mysql using following commands:

<= p class=3DMsoNormal style=3D'mso-margin-top-alt:auto;mso-margin-bottom-alt:= auto'>mysql> C= REATE DATABASE metastore;

mysql> USE metastore;

mysql> SOURCE /usr/local/hive/scripts/metastore/upgrade/mysql/hive-sche= ma-0.7.0.mysql.sql;

 

mysql> CREATE USER 'hi= veuser'@'%' IDENTIFIED BY 'hiveuser';

mysql> GRANT ALL ON me= tastore.* TO 'hiveuser'@'%';

 =

I created a table using the following command on hive:

hive&= gt; Create table abcd(ab int, cd string) row format delimited fields termin= ated by ‘#’ stored as textfile;

&= nbsp;

Then I created a file abcddata.txt containing th= e following data

11#aa

22#bb=

33#cc

 

T= hen I loaded this data into table abcd using :

Hive> Load sata local= inpath ‘path/to/abcd.txt’ into table abcd;

 

Now when I execute “select * from abcd= 221; it runs successfully and shows the data in abcd.

But if I run “select ab from abcd” or “ select * from abcd where cd=3D’aaR= 17;” it returns error:

 =

FAILED= : Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.MapRed= Task

 

In the logs I found:

  &nbs= p;     at java.util.Vector.lastElement(Vector.java:456)=

        at com.sun.beans.Ob= jectHandler.lastExp(ObjectHandler.java:134)

<= span style=3D'font-size:10.0pt;font-family:"Courier New"'>   = ;     at com.sun.beans.ObjectHandler.dequeueResult(Obje= ctHandler.java:138)

        = at java.beans.XMLDecoder.readObject(XMLDecoder.java:201)<= /p>

 =        at org.apache.hadoop.hive.ql.exec.Util= ities.deserializeMapRedWork(Utilities.java:462)

  &nb= sp;     at org.apache.hadoop.hive.ql.exec.Utilities.get= MapRedWork(Utilities.java:184)

 

 =

And when I tried to access Hive from a java program using connection = string:

(jdbc:mysql://master:3306/metastore","hiveuse= r","hiveuser")

Running command “describe a= bcd” it returns:

Exception in thread "main" com= .mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'metastore.ab= cd' doesn't exist

 

Then on the mysql server I= ran:

mysql> use metastore;

mysql> show table= s;

 

The table abcd is not there. The table is= not being stored in the mysql metastore db.

<= span style=3D'font-size:10.0pt;font-family:"Courier New"'>So how come on Hi= ve CLI, when I do “select * from abcd” it shows the data in the= table. And “show tables” shows abcd there. It means Hive CLI i= s not using the mysql metastore for storing and “select *” stat= ement but whenever it’s a statement that requires map reduce jobs or = while accessing via java program using connection string it uses mysql meta= store. It must be some configuration mistake I think. Please help me out.

 

 

&= nbsp;

Regards,

Aditya Singh <= o:p>

Infosys, India.

 

**************** CAUTION - Disclai=
mer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended so=
lely 
for the use o=
f the addressee(s). If you are not the intended recipient, please 
notify the sender by e-mail=
 and delete the original message. Further, you are not 
to copy, disclose, or distribute this =
e-mail or its contents to any other person and 
any such actions are unlawful. This e-mail may=
 contain viruses. Infosys has taken 
every reasonable precaution to minimize this risk, but is=
 not liable for any damage 
you may sustain as a result of any virus in this e-mail. You shoul=
d carry out your 
o=
wn virus checks before opening the e-mail or attachment. Infosys reserves t=
he 
right to monito=
r and review the content of all messages sent to or from this e-mail <=
/o:p>
address. Messages sent t=
o or from this e-mail address may be stored on the
= 
Infosys e-mail system.
***INFOSYS******** End of Disclaimer *=
*******INFOSYS***

 

= --_000_3328D456F567BB42856F60C58D32F0C3215E2D2075BLRKECMBX01ad_--