You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Aditya Singh30 <Ad...@infosys.com> on 2011/11/22 06:55:12 UTC
Mysql metastore configuration error.
Hi Everybody,
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 have 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 :
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost:3306/metastore?createDatabaseIfNotExist=true</value>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>hiveuser</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>hiveuser</value>
</property>
<property>
<name>datanucleus.autoCreateSchema</name>
<value>false</value>
</property>
<property>
<name>datanucleus.fixedDatastore</name>
<value>true</value>
</property>
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 terminated 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='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(Utilities.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.MySQLSyntaxErrorException: 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 metastore db.
So how come on Hive CLI, when I do "select * from abcd" it shows the data in the table. And "show tables" shows abcd there. It means Hive CLI is not using 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 configuration mistake I think. Please help me out.
Regards,
Aditya Singh
Infosys, India.
**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
for the use of 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 should carry out 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 this 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***
Re: FW: Mysql metastore configuration error.
Posted by Jasper Knulst <ja...@incentro.com>.
Hi Aditya,
Do you have a mysql-connector jar in your classpath for the Hadoop
jobtracker/tasktrackers ?
Gr
Jasper
2011/11/22 Aditya Singh30 <Ad...@infosys.com>
> Hi,****
>
> I tried some more things :****
>
> ** **
>
> I created a new table with same structure as table abcd as follows:****
>
> hive> create table abcdfinal(ab int, cd string) row format delimited
> fields terminated by '#' stored as rcfile;****
>
> ** **
>
> and then loaded the data into this new table by selecting * from old table
> abcd:****
>
> hive> insert overwrite table abcdfinal select * from abcd; ****
>
> ** **
>
> This also results in the same error as the “select ab from abcd” and
> “select * from abcd where cd=’aa’”.****
>
> The command “select * from abcd” was working file alone. But with the
> insert command it also gives error. The error on the jobtracker UI is also
> same,”NoSuchElementFound”. ****
>
> ** **
>
> What I got from all this is whatever be the query, if hive creates
> map-reduce job for it, it results in exception. If it is not being executed
> as map reduce jobs, for e.g. select * from abcd, show tables, describe
> abcd, then it works fine. ****
>
> ** **
>
> What am I doing wrong.****
>
> ** **
>
> Thanks,****
>
> Aditya****
>
> *From:* Aditya Singh30
> *Sent:* Tuesday, November 22, 2011 2:03 PM
> *To:* 'user@hive.apache.org'
> *Cc:* 'Sriram Krishnan'
>
> *Subject:* RE: Mysql metastore configuration error.****
>
> ** **
>
> I checked the TBLS table. It contains an entry for abcd. ****
>
> About Hadoop only jobs. Yes I ran examples provided with Hadoop which
> don’t use hive at all. They ran fine, I checked their output also. ****
>
> ** **
>
> I tried executing “select ab from abcd” on hive CLI and checked the job
> tracker UI. There were 2 failed map jobs giving the following error:****
>
> java.lang.RuntimeException: java.util.NoSuchElementException****
>
> at
> org.apache.hadoop.hive.ql.exec.Utilities.getMapRedWork(Utilities.java:192)
> ****
>
> at
> org.apache.hadoop.hive.ql.io.HiveInputFormat.init(HiveInputFormat.java:249)
> ****
>
> at
> org.apache.hadoop.hive.ql.io.HiveInputFormat.getRecordReader(HiveInputFormat.java:222)
> ****
>
> at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:338)
> ****
>
> at org.apache.hadoop.mapred.MapTask.run(MapTask.java:307)****
>
> at org.apache.hadoop.mapred.Child.main(Child.java:170)****
>
> 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(Utilities.java:462)
> ****
>
> at
> org.apache.hadoop.hive.ql.exec.Utilities.getMapRedWork(Utilities.java:184)
> ****
>
> ... 5 more****
>
> ** **
>
> And there were no reduce jobs executed. This is the same error I got when
> I was using hive derby for metastore. I have checked for any
> Lower-Uppercase typos or spelling mistakes in the column name or table
> name. They are all ok.****
>
> One more thing I need to ask. Is the connection string (jdbc:mysql://master:3306/metastore","hiveuser","hiveuser")
> correct. I mean you said that the metastore db doesn’t contain the tables
> directly. And here we are specifying to use the metastore DB. What should
> be the connection string if I want to access the table abcd via a java
> program.****
>
> I tried TBLS.abcd instead of abcd in the query I was executing in the java
> program. It gave the following error:****
>
> Exception in thread "main"
> com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: SELECT command
> denied to user 'hiveuser'@'slave' for table 'abcd'****
>
> ** **
>
> I checked the permissions for hiveuser on metastore DB, re-run the command
> “GRANT ALL ON metastore.* TO 'hiveuser'@'%';”, but still when I run the
> java program it gives the same error.****
>
> Does this points to anything.****
>
> ** **
>
> ** **
>
> Regards,****
>
> Aditya****
>
> ** **
>
> *From:* Sriram Krishnan [mailto:skrishnan@netflix.com]
> *Sent:* Tuesday, November 22, 2011 1:19 PM
>
> *To:* user@hive.apache.org
> *Subject:* Re: Mysql metastore configuration error.****
>
> ** **
>
> Hive tables do not have a 1-1 mapping to tables in MySQL. In other words,
> your hive table "abcd" will NOT be a table within the MySQL "metastore"
> database.****
>
> ** **
>
> If you want to see what is going on in the MySQL metastore, you can do the
> following:****
>
> mysql> use metastore;****
>
> mysql> show tables;****
>
> ** **
>
> You should see a TBLS table, which should contain an entry for the Hive
> table that you just created (I.e. abcd):****
>
> ** **
>
> mysql> select * from TBLS;****
>
> ** **
>
> As for your execution error, go to your job tracker UI to see if there are
> any obvious errors. Looks like people have seen errors like this before (
> http://mail-archives.apache.org/mod_mbox/hive-user/201012.mbox/%3C4D102AF4.6060403@orkash.com%3E). Are
> you able to run regular Hadoop jobs (I.e. non-Hive)? If you can't run
> regular Hadoop jobs, you might want to start debugging that first.****
>
> ** **
>
> Sriram****
>
> ** **
>
> ** **
>
> *From: *Aditya Singh30 <Ad...@infosys.com>
> *Reply-To: *<us...@hive.apache.org>
> *Date: *Tue, 22 Nov 2011 12:56:16 +0530
> *To: *"user@hive.apache.org" <us...@hive.apache.org>
> *Subject: *RE: Mysql metastore configuration error.****
>
> ** **
>
> 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 <ja...@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 *s*ata local inpath ‘path/to/abcd.txt’ into table abcd;****
>
> ****
>
> (load sata not load data)****
>
> 2011/11/21 Aditya Singh30 <Ad...@infosys.com>****
>
> Hi Everybody,****
>
> 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 have 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 :****
>
> ****
>
> <property> ****
>
> <name>javax.jdo.option.ConnectionURL</name> ****
>
> <value>jdbc:mysql://localhost:3306/metastore?createDatabaseIfNotExist=true</value>
> ****
>
> </property> ****
>
> ****
>
> <property> ****
>
> <name>javax.jdo.option.ConnectionDriverName</name> ****
>
> <value>com.mysql.jdbc.Driver</value> ****
>
> </property> ****
>
> ****
>
> <property> ****
>
> <name>javax.jdo.option.ConnectionUserName</name> ****
>
> <value>hiveuser</value> ****
>
> </property> ****
>
> ****
>
> <property> ****
>
> <name>javax.jdo.option.ConnectionPassword</name> ****
>
> <value>hiveuser</value> ****
>
> </property> ****
>
> ****
>
> <property> ****
>
> <name>datanucleus.autoCreateSchema</name> ****
>
> <value>false</value> ****
>
> </property> ****
>
> ****
>
> <property> ****
>
> <name>datanucleus.fixedDatastore</name> ****
>
> <value>true</value> ****
>
> </property>****
>
> ****
>
> ****
>
> 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
> terminated 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=’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(Utilities.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.MySQLSyntaxErrorException: 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
> metastore db. ****
>
> So how come on Hive CLI, when I do “select * from abcd” it shows the data
> in the table. And “show tables” shows abcd there. It means Hive CLI is not
> using 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
> configuration mistake I think. Please help me out.****
>
> ****
>
> Regards,****
>
> Aditya Singh ****
>
> Infosys, India.****
>
> ****
>
> **************** CAUTION - Disclaimer *****************
> This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
> for the use of 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 should carry out 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 this 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***
>
>
--
*Jasper Knulst*
Consultant *|* Incentro Den Haag
Gildeweg 5B
2632 BD Nootdorp
The Netherlands
*E:* jasper.knulst@incentro.com
*T:* +31157640750
*M: *+31619667511
*W:* www.incentro.com
[image: Logo Incentro]
FW: Mysql metastore configuration error.
Posted by Aditya Singh30 <Ad...@infosys.com>.
Hi,
I tried some more things :
I created a new table with same structure as table abcd as follows:
hive> create table abcdfinal(ab int, cd string) row format delimited fields terminated by '#' stored as rcfile;
and then loaded the data into this new table by selecting * from old table abcd:
hive> insert overwrite table abcdfinal select * from abcd;
This also results in the same error as the "select ab from abcd" and "select * from abcd where cd='aa'".
The command "select * from abcd" was working file alone. But with the insert command it also gives error. The error on the jobtracker UI is also same,"NoSuchElementFound".
What I got from all this is whatever be the query, if hive creates map-reduce job for it, it results in exception. If it is not being executed as map reduce jobs, for e.g. select * from abcd, show tables, describe abcd, then it works fine.
What am I doing wrong.
Thanks,
Aditya
From: Aditya Singh30
Sent: Tuesday, November 22, 2011 2:03 PM
To: 'user@hive.apache.org'
Cc: 'Sriram Krishnan'
Subject: RE: Mysql metastore configuration error.
I checked the TBLS table. It contains an entry for abcd.
About Hadoop only jobs. Yes I ran examples provided with Hadoop which don't use hive at all. They ran fine, I checked their output also.
I tried executing "select ab from abcd" on hive CLI and checked the job tracker UI. There were 2 failed map jobs giving the following error:
java.lang.RuntimeException: java.util.NoSuchElementException
at org.apache.hadoop.hive.ql.exec.Utilities.getMapRedWork(Utilities.java:192)
at org.apache.hadoop.hive.ql.io.HiveInputFormat.init(HiveInputFormat.java:249)
at org.apache.hadoop.hive.ql.io.HiveInputFormat.getRecordReader(HiveInputFormat.java:222)
at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:338)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:307)
at org.apache.hadoop.mapred.Child.main(Child.java:170)
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(Utilities.java:462)
at org.apache.hadoop.hive.ql.exec.Utilities.getMapRedWork(Utilities.java:184)
... 5 more
And there were no reduce jobs executed. This is the same error I got when I was using hive derby for metastore. I have checked for any Lower-Uppercase typos or spelling mistakes in the column name or table name. They are all ok.
One more thing I need to ask. Is the connection string (jdbc:mysql://master:3306/metastore","hiveuser","hiveuser") correct. I mean you said that the metastore db doesn't contain the tables directly. And here we are specifying to use the metastore DB. What should be the connection string if I want to access the table abcd via a java program.
I tried TBLS.abcd instead of abcd in the query I was executing in the java program. It gave the following error:
Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: SELECT command denied to user 'hiveuser'@'slave' for table 'abcd'
I checked the permissions for hiveuser on metastore DB, re-run the command "GRANT ALL ON metastore.* TO 'hiveuser'@'%';", but still when I run the java program it gives the same error.
Does this points to anything.
Regards,
Aditya
From: Sriram Krishnan [mailto:skrishnan@netflix.com]
Sent: Tuesday, November 22, 2011 1:19 PM
To: user@hive.apache.org
Subject: Re: Mysql metastore configuration error.
Hive tables do not have a 1-1 mapping to tables in MySQL. In other words, your hive table "abcd" will NOT be a table within the MySQL "metastore" database.
If you want to see what is going on in the MySQL metastore, you can do the following:
mysql> use metastore;
mysql> show tables;
You should see a TBLS table, which should contain an entry for the Hive table that you just created (I.e. abcd):
mysql> select * from TBLS;
As for your execution error, go to your job tracker UI to see if there are any obvious errors. Looks like people have seen errors like this before (http://mail-archives.apache.org/mod_mbox/hive-user/201012.mbox/%3C4D102AF4.6060403@orkash.com%3E). Are you able to run regular Hadoop jobs (I.e. non-Hive)? If you can't run regular Hadoop jobs, you might want to start debugging that first.
Sriram
From: Aditya Singh30 <Ad...@infosys.com>>
Reply-To: <us...@hive.apache.org>>
Date: Tue, 22 Nov 2011 12:56:16 +0530
To: "user@hive.apache.org<ma...@hive.apache.org>" <us...@hive.apache.org>>
Subject: RE: Mysql metastore configuration error.
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<ma...@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 <Ad...@infosys.com>>
Hi Everybody,
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 have 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 :
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost:3306/metastore?createDatabaseIfNotExist=true</value>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>hiveuser</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>hiveuser</value>
</property>
<property>
<name>datanucleus.autoCreateSchema</name>
<value>false</value>
</property>
<property>
<name>datanucleus.fixedDatastore</name>
<value>true</value>
</property>
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 terminated 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='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(Utilities.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.MySQLSyntaxErrorException: 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 metastore db.
So how come on Hive CLI, when I do "select * from abcd" it shows the data in the table. And "show tables" shows abcd there. It means Hive CLI is not using 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 configuration mistake I think. Please help me out.
Regards,
Aditya Singh
Infosys, India.
**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
for the use of 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 should carry out 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 this 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***
Re: Mysql metastore configuration error.
Posted by Florin Diaconeasa <fl...@gmail.com>.
Ah... I apologise! I just saw that it was generated by hand.
@Aditya
Remove the database and try with a new one, like Alexander said.
Again, I apologise!
Florin
On 23 November 2011 16:21, Florin Diaconeasa <fl...@gmail.com>wrote:
> @Alexander
>
> From what i'm seeing inside the original email,
> the createDatabaseIfNotExist property is set on TRUE inside
> the javax.jdo.option.ConnectionURL property
> So the metadata database should have been created by hive.
>
> @Aditya
> Did you create the metadata DB by hand or was it generated by Hive?
>
> On 23 November 2011 10:45, Alexander C.H. Lorenz <wget.null@googlemail.com
> > wrote:
>
>> Hi Aditya,
>>
>> I could reproduce the issue. Please create a new database @mysql without
>> schema-updates. Hive will create the necessary tables if the database has
>> no tables (?createDatabaseIfNotExist=true). The schema-upgrades are written
>> for upgrades only, not for new datasets. To create the tables start hive a
>> fist time and create a table inside. That will took a while, after you can
>> check your metastore-db:
>> mysql> show tables;
>> +-------------------------+
>> | Tables_in_hive_test_new |
>> +-------------------------+
>> | BUCKETING_COLS |
>> | COLUMNS |
>> | DATABASE_PARAMS |
>> | DBS |
>> | DB_PRIVS |
>> | GLOBAL_PRIVS |
>> | IDXS |
>> | INDEX_PARAMS |
>> | PARTITIONS |
>> | PARTITION_KEYS |
>> | PARTITION_KEY_VALS |
>> | PARTITION_PARAMS |
>> | PART_COL_PRIVS |
>> | PART_PRIVS |
>> | ROLES |
>> | ROLE_MAP |
>> | SDS |
>> | SD_PARAMS |
>> | SEQUENCE_TABLE |
>> | SERDES |
>> | SERDE_PARAMS |
>> | SORT_COLS |
>> | TABLE_PARAMS |
>> | TBLS |
>> | TBL_COL_PRIVS |
>> | TBL_PRIVS |
>> | TYPES |
>> | TYPE_FIELDS |
>> +-------------------------+
>> 28 rows in set (0.00 sec)
>>
>> regards
>> - alex
>>
>>
>> On Wed, Nov 23, 2011 at 9:24 AM, Aditya Singh30 <
>> Aditya_Singh30@infosys.com> wrote:
>>
>>> Hi,****
>>>
>>> ** **
>>>
>>> @Jasper: ****
>>>
>>> yes I have placed the MySQL connector in hive/lib and Hadoop/lib on both
>>> the nodes.****
>>>
>>> ** **
>>>
>>> @Sriram: ****
>>>
>>> I already tried running Hadoop in pseudo distributed mode on a single
>>> node before moving on to 2 node cluster. I started hive Thrift server on it
>>> and accessed Hive DB from my windows machine using java code. It worked
>>> fine, all the queries, Including the ones having map reduce jobs.****
>>>
>>> Regarding accessing hive thru jdbc, I figured I was doing the wrong
>>> thing, that MySQL connection URL was for hive to use MySQL for metastore
>>> not for hiveClient to access the hive DB J. I changed the code back to
>>> as described in the hiveClient tutorial (which I had already tested for
>>> pseudo distributed mode), didn’t get any ‘access denied‘ or ‘no such table’
>>> errors, but the result was same, as it was on hive CLI or HWI. It starts
>>> the map-reduce jobs, “No such element found exception” occurs and execution
>>> is terminated with return code 2.****
>>>
>>> I again checked my Hadoop cluster by running some Hadoop (non-hive)
>>> examples, performing some operations on HDFS, they worked fine.****
>>>
>>> I wanted to ask one more thing. Am I right if I think that when we
>>> execute a query on say Hive CLI which runs mapreduce jobs on the Hadoop
>>> cluster, Then after the map reduce job are created, the job is like any
>>> other Hadoop job and does not requires Hive anymore.(except for the
>>> libraries which it will include in the mapreduce job program/code)??****
>>>
>>> ** **
>>>
>>> I don’t know wht to do. The only thing I can think of doing right now is
>>> getting another linux machine in place of windows machine n try all linux
>>> nodes in the cluster rather than windows-linux combination. Hive didn’t
>>> worked on my windows even in the local mode. L****
>>>
>>> ** **
>>>
>>> ** **
>>>
>>> Thanks, ****
>>>
>>> Aditya****
>>>
>>> ** **
>>>
>>> ** **
>>>
>>> *From:* Sriram Krishnan [mailto:skrishnan@netflix.com]
>>> *Sent:* Tuesday, November 22, 2011 10:07 PM
>>> *To:* Aditya Singh30; user@hive.apache.org
>>>
>>> *Subject:* Re: Mysql metastore configuration error.****
>>>
>>> ** **
>>>
>>> As far as I can tell, there are two things going on here:****
>>>
>>> 1. You are trying to access Hive via JDBC – if so, you are not going
>>> to be able to connect to the Hive DB the way you are trying to. If you do
>>> want to access Hive via JDBC, follow instructions here:
>>> https://cwiki.apache.org/confluence/display/Hive/HiveClient. You are
>>> going to need to run a standalone Hive server for JDBC access.****
>>> 2. You are correct – it seems like you are unable to run any Hive
>>> jobs that require MapReduce. If you are getting the same error even with
>>> Derby, then it may be safe to assume that MySQL is not the culprit. I am
>>> not certain what the problem is here – however, you might want to run your
>>> query in local mode (
>>> https://cwiki.apache.org/confluence/display/Hive/GettingStarted#GettingStarted-Hive%2CMapReduceandLocalMode)
>>> to ensure that it works. Next, I would start with a single node Hadoop
>>> installation (to rule out any configuration mismatch between your Ubuntu
>>> and Cygwin nodes).****
>>>
>>> Hope that helps.****
>>>
>>> Sriram****
>>>
>>> ** **
>>>
>>> *From: *Aditya Singh30 <Ad...@infosys.com>
>>> *Date: *Tue, 22 Nov 2011 14:02:56 +0530
>>> *To: *"user@hive.apache.org" <us...@hive.apache.org>
>>> *Cc: *Sriram Krishnan <sk...@netflix.com>
>>> *Subject: *RE: Mysql metastore configuration error.****
>>>
>>> ** **
>>>
>>> I checked the TBLS table. It contains an entry for abcd. ****
>>>
>>> About Hadoop only jobs. Yes I ran examples provided with Hadoop which
>>> don’t use hive at all. They ran fine, I checked their output also. ****
>>>
>>> ****
>>>
>>> I tried executing “select ab from abcd” on hive CLI and checked the job
>>> tracker UI. There were 2 failed map jobs giving the following error:****
>>>
>>> java.lang.RuntimeException: java.util.NoSuchElementException****
>>>
>>> at
>>> org.apache.hadoop.hive.ql.exec.Utilities.getMapRedWork(Utilities.java:192)
>>> ****
>>>
>>> at
>>> org.apache.hadoop.hive.ql.io.HiveInputFormat.init(HiveInputFormat.java:249)
>>> ****
>>>
>>> at
>>> org.apache.hadoop.hive.ql.io.HiveInputFormat.getRecordReader(HiveInputFormat.java:222)
>>> ****
>>>
>>> at
>>> org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:338)****
>>>
>>> at org.apache.hadoop.mapred.MapTask.run(MapTask.java:307)****
>>>
>>> at org.apache.hadoop.mapred.Child.main(Child.java:170)****
>>>
>>> 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(Utilities.java:462)
>>> ****
>>>
>>> at
>>> org.apache.hadoop.hive.ql.exec.Utilities.getMapRedWork(Utilities.java:184)
>>> ****
>>>
>>> ... 5 more****
>>>
>>> ****
>>>
>>> And there were no reduce jobs executed. This is the same error I got
>>> when I was using hive derby for metastore. I have checked for any
>>> Lower-Uppercase typos or spelling mistakes in the column name or table
>>> name. They are all ok.****
>>>
>>> One more thing I need to ask. Is the connection string (jdbc:mysql://master:3306/metastore","hiveuser","hiveuser")
>>> correct. I mean you said that the metastore db doesn’t contain the
>>> tables directly. And here we are specifying to use the metastore DB. What
>>> should be the connection string if I want to access the table abcd via a
>>> java program.****
>>>
>>> I tried TBLS.abcd instead of abcd in the query I was executing in the
>>> java program. It gave the following error:****
>>>
>>> Exception in thread "main"
>>> com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: SELECT command
>>> denied to user 'hiveuser'@'slave' for table 'abcd'****
>>>
>>> ****
>>>
>>> I checked the permissions for hiveuser on metastore DB, re-run the
>>> command “GRANT ALL ON metastore.* TO 'hiveuser'@'%';”, but still when I
>>> run the java program it gives the same error.****
>>>
>>> Does this points to anything.****
>>>
>>> ****
>>>
>>> ****
>>>
>>> Regards,****
>>>
>>> Aditya****
>>>
>>> ****
>>>
>>> *From:* Sriram Krishnan [mailto:skrishnan@netflix.com<sk...@netflix.com>]
>>>
>>> *Sent:* Tuesday, November 22, 2011 1:19 PM
>>> *To:* user@hive.apache.org
>>> *Subject:* Re: Mysql metastore configuration error.****
>>>
>>> ****
>>>
>>> Hive tables do not have a 1-1 mapping to tables in MySQL. In other
>>> words, your hive table "abcd" will NOT be a table within the MySQL
>>> "metastore" database.****
>>>
>>> ****
>>>
>>> If you want to see what is going on in the MySQL metastore, you can do
>>> the following:****
>>>
>>> mysql> use metastore;****
>>>
>>> mysql> show tables;****
>>>
>>> ****
>>>
>>> You should see a TBLS table, which should contain an entry for the Hive
>>> table that you just created (I.e. abcd):****
>>>
>>> ****
>>>
>>> mysql> select * from TBLS;****
>>>
>>> ****
>>>
>>> As for your execution error, go to your job tracker UI to see if there
>>> are any obvious errors. Looks like people have seen errors like this before
>>> (
>>> http://mail-archives.apache.org/mod_mbox/hive-user/201012.mbox/%3C4D102AF4.6060403@orkash.com%3E). Are
>>> you able to run regular Hadoop jobs (I.e. non-Hive)? If you can't run
>>> regular Hadoop jobs, you might want to start debugging that first.****
>>>
>>> ****
>>>
>>> Sriram****
>>>
>>> ****
>>>
>>> ****
>>>
>>> *From: *Aditya Singh30 <Ad...@infosys.com>
>>> *Reply-To: *<us...@hive.apache.org>
>>> *Date: *Tue, 22 Nov 2011 12:56:16 +0530
>>> *To: *"user@hive.apache.org" <us...@hive.apache.org>
>>> *Subject: *RE: Mysql metastore configuration error.****
>>>
>>> ****
>>>
>>> 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 <ja...@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 *s*ata local inpath ‘path/to/abcd.txt’ into table abcd;****
>>>
>>> ****
>>>
>>> (load sata not load data)****
>>>
>>> 2011/11/21 Aditya Singh30 <Ad...@infosys.com>****
>>>
>>> Hi Everybody,****
>>>
>>> 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 have 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 :****
>>>
>>> ****
>>>
>>> <property> ****
>>>
>>> <name>javax.jdo.option.ConnectionURL</name> ****
>>>
>>> <value>jdbc:mysql://localhost:3306/metastore?createDatabaseIfNotExist=true</value>
>>> ****
>>>
>>> </property> ****
>>>
>>> ****
>>>
>>> <property> ****
>>>
>>> <name>javax.jdo.option.ConnectionDriverName</name> ****
>>>
>>> <value>com.mysql.jdbc.Driver</value> ****
>>>
>>> </property> ****
>>>
>>> ****
>>>
>>> <property> ****
>>>
>>> <name>javax.jdo.option.ConnectionUserName</name> ****
>>>
>>> <value>hiveuser</value> ****
>>>
>>> </property> ****
>>>
>>> ****
>>>
>>> <property> ****
>>>
>>> <name>javax.jdo.option.ConnectionPassword</name> ****
>>>
>>> <value>hiveuser</value> ****
>>>
>>> </property> ****
>>>
>>> ****
>>>
>>> <property> ****
>>>
>>> <name>datanucleus.autoCreateSchema</name> ****
>>>
>>> <value>false</value> ****
>>>
>>> </property> ****
>>>
>>> ****
>>>
>>> <property> ****
>>>
>>> <name>datanucleus.fixedDatastore</name> ****
>>>
>>> <value>true</value> ****
>>>
>>> </property>****
>>>
>>> ****
>>>
>>> ****
>>>
>>> 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
>>> terminated 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=’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(Utilities.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.MySQLSyntaxErrorException: 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
>>> metastore db. ****
>>>
>>> So how come on Hive CLI, when I do “select * from abcd” it shows the
>>> data in the table. And “show tables” shows abcd there. It means Hive CLI is
>>> not using 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 configuration mistake I think. Please help me out.****
>>>
>>> ****
>>>
>>> ****
>>>
>>> ****
>>>
>>> Regards,****
>>>
>>> Aditya Singh ****
>>>
>>> Infosys, India.****
>>>
>>> ****
>>>
>>> **************** CAUTION - Disclaimer *********************
>>>
>>> This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely ****
>>>
>>> for the use of 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 should carry out 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 this 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*******
>>>
>>> ****
>>>
>>
>>
>>
>> --
>> Alexander Lorenz
>> http://mapredit.blogspot.com
>>
>> *P **Think of the environment: please don't print this email unless you
>> really need to.*
>>
>>
>>
>
>
> --
>
>
> Florin
>
--
Florin
Re: Mysql metastore configuration error.
Posted by Florin Diaconeasa <fl...@gmail.com>.
@Alexander
>From what i'm seeing inside the original email,
the createDatabaseIfNotExist property is set on TRUE inside
the javax.jdo.option.ConnectionURL property
So the metadata database should have been created by hive.
@Aditya
Did you create the metadata DB by hand or was it generated by Hive?
On 23 November 2011 10:45, Alexander C.H. Lorenz
<wg...@googlemail.com>wrote:
> Hi Aditya,
>
> I could reproduce the issue. Please create a new database @mysql without
> schema-updates. Hive will create the necessary tables if the database has
> no tables (?createDatabaseIfNotExist=true). The schema-upgrades are written
> for upgrades only, not for new datasets. To create the tables start hive a
> fist time and create a table inside. That will took a while, after you can
> check your metastore-db:
> mysql> show tables;
> +-------------------------+
> | Tables_in_hive_test_new |
> +-------------------------+
> | BUCKETING_COLS |
> | COLUMNS |
> | DATABASE_PARAMS |
> | DBS |
> | DB_PRIVS |
> | GLOBAL_PRIVS |
> | IDXS |
> | INDEX_PARAMS |
> | PARTITIONS |
> | PARTITION_KEYS |
> | PARTITION_KEY_VALS |
> | PARTITION_PARAMS |
> | PART_COL_PRIVS |
> | PART_PRIVS |
> | ROLES |
> | ROLE_MAP |
> | SDS |
> | SD_PARAMS |
> | SEQUENCE_TABLE |
> | SERDES |
> | SERDE_PARAMS |
> | SORT_COLS |
> | TABLE_PARAMS |
> | TBLS |
> | TBL_COL_PRIVS |
> | TBL_PRIVS |
> | TYPES |
> | TYPE_FIELDS |
> +-------------------------+
> 28 rows in set (0.00 sec)
>
> regards
> - alex
>
>
> On Wed, Nov 23, 2011 at 9:24 AM, Aditya Singh30 <
> Aditya_Singh30@infosys.com> wrote:
>
>> Hi,****
>>
>> ** **
>>
>> @Jasper: ****
>>
>> yes I have placed the MySQL connector in hive/lib and Hadoop/lib on both
>> the nodes.****
>>
>> ** **
>>
>> @Sriram: ****
>>
>> I already tried running Hadoop in pseudo distributed mode on a single
>> node before moving on to 2 node cluster. I started hive Thrift server on it
>> and accessed Hive DB from my windows machine using java code. It worked
>> fine, all the queries, Including the ones having map reduce jobs.****
>>
>> Regarding accessing hive thru jdbc, I figured I was doing the wrong
>> thing, that MySQL connection URL was for hive to use MySQL for metastore
>> not for hiveClient to access the hive DB J. I changed the code back to
>> as described in the hiveClient tutorial (which I had already tested for
>> pseudo distributed mode), didn’t get any ‘access denied‘ or ‘no such table’
>> errors, but the result was same, as it was on hive CLI or HWI. It starts
>> the map-reduce jobs, “No such element found exception” occurs and execution
>> is terminated with return code 2.****
>>
>> I again checked my Hadoop cluster by running some Hadoop (non-hive)
>> examples, performing some operations on HDFS, they worked fine.****
>>
>> I wanted to ask one more thing. Am I right if I think that when we
>> execute a query on say Hive CLI which runs mapreduce jobs on the Hadoop
>> cluster, Then after the map reduce job are created, the job is like any
>> other Hadoop job and does not requires Hive anymore.(except for the
>> libraries which it will include in the mapreduce job program/code)??****
>>
>> ** **
>>
>> I don’t know wht to do. The only thing I can think of doing right now is
>> getting another linux machine in place of windows machine n try all linux
>> nodes in the cluster rather than windows-linux combination. Hive didn’t
>> worked on my windows even in the local mode. L****
>>
>> ** **
>>
>> ** **
>>
>> Thanks, ****
>>
>> Aditya****
>>
>> ** **
>>
>> ** **
>>
>> *From:* Sriram Krishnan [mailto:skrishnan@netflix.com]
>> *Sent:* Tuesday, November 22, 2011 10:07 PM
>> *To:* Aditya Singh30; user@hive.apache.org
>>
>> *Subject:* Re: Mysql metastore configuration error.****
>>
>> ** **
>>
>> As far as I can tell, there are two things going on here:****
>>
>> 1. You are trying to access Hive via JDBC – if so, you are not going
>> to be able to connect to the Hive DB the way you are trying to. If you do
>> want to access Hive via JDBC, follow instructions here:
>> https://cwiki.apache.org/confluence/display/Hive/HiveClient. You are
>> going to need to run a standalone Hive server for JDBC access.****
>> 2. You are correct – it seems like you are unable to run any Hive
>> jobs that require MapReduce. If you are getting the same error even with
>> Derby, then it may be safe to assume that MySQL is not the culprit. I am
>> not certain what the problem is here – however, you might want to run your
>> query in local mode (
>> https://cwiki.apache.org/confluence/display/Hive/GettingStarted#GettingStarted-Hive%2CMapReduceandLocalMode)
>> to ensure that it works. Next, I would start with a single node Hadoop
>> installation (to rule out any configuration mismatch between your Ubuntu
>> and Cygwin nodes).****
>>
>> Hope that helps.****
>>
>> Sriram****
>>
>> ** **
>>
>> *From: *Aditya Singh30 <Ad...@infosys.com>
>> *Date: *Tue, 22 Nov 2011 14:02:56 +0530
>> *To: *"user@hive.apache.org" <us...@hive.apache.org>
>> *Cc: *Sriram Krishnan <sk...@netflix.com>
>> *Subject: *RE: Mysql metastore configuration error.****
>>
>> ** **
>>
>> I checked the TBLS table. It contains an entry for abcd. ****
>>
>> About Hadoop only jobs. Yes I ran examples provided with Hadoop which
>> don’t use hive at all. They ran fine, I checked their output also. ****
>>
>> ****
>>
>> I tried executing “select ab from abcd” on hive CLI and checked the job
>> tracker UI. There were 2 failed map jobs giving the following error:****
>>
>> java.lang.RuntimeException: java.util.NoSuchElementException****
>>
>> at
>> org.apache.hadoop.hive.ql.exec.Utilities.getMapRedWork(Utilities.java:192)
>> ****
>>
>> at
>> org.apache.hadoop.hive.ql.io.HiveInputFormat.init(HiveInputFormat.java:249)
>> ****
>>
>> at
>> org.apache.hadoop.hive.ql.io.HiveInputFormat.getRecordReader(HiveInputFormat.java:222)
>> ****
>>
>> at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:338)
>> ****
>>
>> at org.apache.hadoop.mapred.MapTask.run(MapTask.java:307)****
>>
>> at org.apache.hadoop.mapred.Child.main(Child.java:170)****
>>
>> 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(Utilities.java:462)
>> ****
>>
>> at
>> org.apache.hadoop.hive.ql.exec.Utilities.getMapRedWork(Utilities.java:184)
>> ****
>>
>> ... 5 more****
>>
>> ****
>>
>> And there were no reduce jobs executed. This is the same error I got when
>> I was using hive derby for metastore. I have checked for any
>> Lower-Uppercase typos or spelling mistakes in the column name or table
>> name. They are all ok.****
>>
>> One more thing I need to ask. Is the connection string (jdbc:mysql://master:3306/metastore","hiveuser","hiveuser")
>> correct. I mean you said that the metastore db doesn’t contain the tables
>> directly. And here we are specifying to use the metastore DB. What should
>> be the connection string if I want to access the table abcd via a java
>> program.****
>>
>> I tried TBLS.abcd instead of abcd in the query I was executing in the
>> java program. It gave the following error:****
>>
>> Exception in thread "main"
>> com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: SELECT command
>> denied to user 'hiveuser'@'slave' for table 'abcd'****
>>
>> ****
>>
>> I checked the permissions for hiveuser on metastore DB, re-run the
>> command “GRANT ALL ON metastore.* TO 'hiveuser'@'%';”, but still when I
>> run the java program it gives the same error.****
>>
>> Does this points to anything.****
>>
>> ****
>>
>> ****
>>
>> Regards,****
>>
>> Aditya****
>>
>> ****
>>
>> *From:* Sriram Krishnan [mailto:skrishnan@netflix.com<sk...@netflix.com>]
>>
>> *Sent:* Tuesday, November 22, 2011 1:19 PM
>> *To:* user@hive.apache.org
>> *Subject:* Re: Mysql metastore configuration error.****
>>
>> ****
>>
>> Hive tables do not have a 1-1 mapping to tables in MySQL. In other words,
>> your hive table "abcd" will NOT be a table within the MySQL "metastore"
>> database.****
>>
>> ****
>>
>> If you want to see what is going on in the MySQL metastore, you can do
>> the following:****
>>
>> mysql> use metastore;****
>>
>> mysql> show tables;****
>>
>> ****
>>
>> You should see a TBLS table, which should contain an entry for the Hive
>> table that you just created (I.e. abcd):****
>>
>> ****
>>
>> mysql> select * from TBLS;****
>>
>> ****
>>
>> As for your execution error, go to your job tracker UI to see if there
>> are any obvious errors. Looks like people have seen errors like this before
>> (
>> http://mail-archives.apache.org/mod_mbox/hive-user/201012.mbox/%3C4D102AF4.6060403@orkash.com%3E). Are
>> you able to run regular Hadoop jobs (I.e. non-Hive)? If you can't run
>> regular Hadoop jobs, you might want to start debugging that first.****
>>
>> ****
>>
>> Sriram****
>>
>> ****
>>
>> ****
>>
>> *From: *Aditya Singh30 <Ad...@infosys.com>
>> *Reply-To: *<us...@hive.apache.org>
>> *Date: *Tue, 22 Nov 2011 12:56:16 +0530
>> *To: *"user@hive.apache.org" <us...@hive.apache.org>
>> *Subject: *RE: Mysql metastore configuration error.****
>>
>> ****
>>
>> 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 <ja...@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 *s*ata local inpath ‘path/to/abcd.txt’ into table abcd;****
>>
>> ****
>>
>> (load sata not load data)****
>>
>> 2011/11/21 Aditya Singh30 <Ad...@infosys.com>****
>>
>> Hi Everybody,****
>>
>> 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 have 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 :****
>>
>> ****
>>
>> <property> ****
>>
>> <name>javax.jdo.option.ConnectionURL</name> ****
>>
>> <value>jdbc:mysql://localhost:3306/metastore?createDatabaseIfNotExist=true</value>
>> ****
>>
>> </property> ****
>>
>> ****
>>
>> <property> ****
>>
>> <name>javax.jdo.option.ConnectionDriverName</name> ****
>>
>> <value>com.mysql.jdbc.Driver</value> ****
>>
>> </property> ****
>>
>> ****
>>
>> <property> ****
>>
>> <name>javax.jdo.option.ConnectionUserName</name> ****
>>
>> <value>hiveuser</value> ****
>>
>> </property> ****
>>
>> ****
>>
>> <property> ****
>>
>> <name>javax.jdo.option.ConnectionPassword</name> ****
>>
>> <value>hiveuser</value> ****
>>
>> </property> ****
>>
>> ****
>>
>> <property> ****
>>
>> <name>datanucleus.autoCreateSchema</name> ****
>>
>> <value>false</value> ****
>>
>> </property> ****
>>
>> ****
>>
>> <property> ****
>>
>> <name>datanucleus.fixedDatastore</name> ****
>>
>> <value>true</value> ****
>>
>> </property>****
>>
>> ****
>>
>> ****
>>
>> 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
>> terminated 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=’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(Utilities.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.MySQLSyntaxErrorException: 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
>> metastore db. ****
>>
>> So how come on Hive CLI, when I do “select * from abcd” it shows the data
>> in the table. And “show tables” shows abcd there. It means Hive CLI is not
>> using 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
>> configuration mistake I think. Please help me out.****
>>
>> ****
>>
>> ****
>>
>> ****
>>
>> Regards,****
>>
>> Aditya Singh ****
>>
>> Infosys, India.****
>>
>> ****
>>
>> **************** CAUTION - Disclaimer *********************
>>
>> This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely ****
>>
>> for the use of 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 should carry out 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 this 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*******
>>
>> ****
>>
>
>
>
> --
> Alexander Lorenz
> http://mapredit.blogspot.com
>
> *P **Think of the environment: please don't print this email unless you
> really need to.*
>
>
>
--
Florin
Re: Mysql metastore configuration error.
Posted by "Alexander C.H. Lorenz" <wg...@googlemail.com>.
Hi Aditya,
I could reproduce the issue. Please create a new database @mysql without
schema-updates. Hive will create the necessary tables if the database has
no tables (?createDatabaseIfNotExist=true). The schema-upgrades are written
for upgrades only, not for new datasets. To create the tables start hive a
fist time and create a table inside. That will took a while, after you can
check your metastore-db:
mysql> show tables;
+-------------------------+
| Tables_in_hive_test_new |
+-------------------------+
| BUCKETING_COLS |
| COLUMNS |
| DATABASE_PARAMS |
| DBS |
| DB_PRIVS |
| GLOBAL_PRIVS |
| IDXS |
| INDEX_PARAMS |
| PARTITIONS |
| PARTITION_KEYS |
| PARTITION_KEY_VALS |
| PARTITION_PARAMS |
| PART_COL_PRIVS |
| PART_PRIVS |
| ROLES |
| ROLE_MAP |
| SDS |
| SD_PARAMS |
| SEQUENCE_TABLE |
| SERDES |
| SERDE_PARAMS |
| SORT_COLS |
| TABLE_PARAMS |
| TBLS |
| TBL_COL_PRIVS |
| TBL_PRIVS |
| TYPES |
| TYPE_FIELDS |
+-------------------------+
28 rows in set (0.00 sec)
regards
- alex
On Wed, Nov 23, 2011 at 9:24 AM, Aditya Singh30
<Ad...@infosys.com>wrote:
> Hi,****
>
> ** **
>
> @Jasper: ****
>
> yes I have placed the MySQL connector in hive/lib and Hadoop/lib on both
> the nodes.****
>
> ** **
>
> @Sriram: ****
>
> I already tried running Hadoop in pseudo distributed mode on a single node
> before moving on to 2 node cluster. I started hive Thrift server on it and
> accessed Hive DB from my windows machine using java code. It worked fine,
> all the queries, Including the ones having map reduce jobs.****
>
> Regarding accessing hive thru jdbc, I figured I was doing the wrong thing,
> that MySQL connection URL was for hive to use MySQL for metastore not for
> hiveClient to access the hive DB J. I changed the code back to as
> described in the hiveClient tutorial (which I had already tested for pseudo
> distributed mode), didn’t get any ‘access denied‘ or ‘no such table’
> errors, but the result was same, as it was on hive CLI or HWI. It starts
> the map-reduce jobs, “No such element found exception” occurs and execution
> is terminated with return code 2.****
>
> I again checked my Hadoop cluster by running some Hadoop (non-hive)
> examples, performing some operations on HDFS, they worked fine.****
>
> I wanted to ask one more thing. Am I right if I think that when we execute
> a query on say Hive CLI which runs mapreduce jobs on the Hadoop cluster,
> Then after the map reduce job are created, the job is like any other Hadoop
> job and does not requires Hive anymore.(except for the libraries which it
> will include in the mapreduce job program/code)??****
>
> ** **
>
> I don’t know wht to do. The only thing I can think of doing right now is
> getting another linux machine in place of windows machine n try all linux
> nodes in the cluster rather than windows-linux combination. Hive didn’t
> worked on my windows even in the local mode. L****
>
> ** **
>
> ** **
>
> Thanks, ****
>
> Aditya****
>
> ** **
>
> ** **
>
> *From:* Sriram Krishnan [mailto:skrishnan@netflix.com]
> *Sent:* Tuesday, November 22, 2011 10:07 PM
> *To:* Aditya Singh30; user@hive.apache.org
>
> *Subject:* Re: Mysql metastore configuration error.****
>
> ** **
>
> As far as I can tell, there are two things going on here:****
>
> 1. You are trying to access Hive via JDBC – if so, you are not going
> to be able to connect to the Hive DB the way you are trying to. If you do
> want to access Hive via JDBC, follow instructions here:
> https://cwiki.apache.org/confluence/display/Hive/HiveClient. You are
> going to need to run a standalone Hive server for JDBC access.****
> 2. You are correct – it seems like you are unable to run any Hive jobs
> that require MapReduce. If you are getting the same error even with Derby,
> then it may be safe to assume that MySQL is not the culprit. I am not
> certain what the problem is here – however, you might want to run your
> query in local mode (
> https://cwiki.apache.org/confluence/display/Hive/GettingStarted#GettingStarted-Hive%2CMapReduceandLocalMode)
> to ensure that it works. Next, I would start with a single node Hadoop
> installation (to rule out any configuration mismatch between your Ubuntu
> and Cygwin nodes).****
>
> Hope that helps.****
>
> Sriram****
>
> ** **
>
> *From: *Aditya Singh30 <Ad...@infosys.com>
> *Date: *Tue, 22 Nov 2011 14:02:56 +0530
> *To: *"user@hive.apache.org" <us...@hive.apache.org>
> *Cc: *Sriram Krishnan <sk...@netflix.com>
> *Subject: *RE: Mysql metastore configuration error.****
>
> ** **
>
> I checked the TBLS table. It contains an entry for abcd. ****
>
> About Hadoop only jobs. Yes I ran examples provided with Hadoop which
> don’t use hive at all. They ran fine, I checked their output also. ****
>
> ****
>
> I tried executing “select ab from abcd” on hive CLI and checked the job
> tracker UI. There were 2 failed map jobs giving the following error:****
>
> java.lang.RuntimeException: java.util.NoSuchElementException****
>
> at
> org.apache.hadoop.hive.ql.exec.Utilities.getMapRedWork(Utilities.java:192)
> ****
>
> at
> org.apache.hadoop.hive.ql.io.HiveInputFormat.init(HiveInputFormat.java:249)
> ****
>
> at
> org.apache.hadoop.hive.ql.io.HiveInputFormat.getRecordReader(HiveInputFormat.java:222)
> ****
>
> at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:338)
> ****
>
> at org.apache.hadoop.mapred.MapTask.run(MapTask.java:307)****
>
> at org.apache.hadoop.mapred.Child.main(Child.java:170)****
>
> 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(Utilities.java:462)
> ****
>
> at
> org.apache.hadoop.hive.ql.exec.Utilities.getMapRedWork(Utilities.java:184)
> ****
>
> ... 5 more****
>
> ****
>
> And there were no reduce jobs executed. This is the same error I got when
> I was using hive derby for metastore. I have checked for any
> Lower-Uppercase typos or spelling mistakes in the column name or table
> name. They are all ok.****
>
> One more thing I need to ask. Is the connection string (jdbc:mysql://master:3306/metastore","hiveuser","hiveuser")
> correct. I mean you said that the metastore db doesn’t contain the tables
> directly. And here we are specifying to use the metastore DB. What should
> be the connection string if I want to access the table abcd via a java
> program.****
>
> I tried TBLS.abcd instead of abcd in the query I was executing in the java
> program. It gave the following error:****
>
> Exception in thread "main"
> com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: SELECT command
> denied to user 'hiveuser'@'slave' for table 'abcd'****
>
> ****
>
> I checked the permissions for hiveuser on metastore DB, re-run the command
> “GRANT ALL ON metastore.* TO 'hiveuser'@'%';”, but still when I run the
> java program it gives the same error.****
>
> Does this points to anything.****
>
> ****
>
> ****
>
> Regards,****
>
> Aditya****
>
> ****
>
> *From:* Sriram Krishnan [mailto:skrishnan@netflix.com<sk...@netflix.com>]
>
> *Sent:* Tuesday, November 22, 2011 1:19 PM
> *To:* user@hive.apache.org
> *Subject:* Re: Mysql metastore configuration error.****
>
> ****
>
> Hive tables do not have a 1-1 mapping to tables in MySQL. In other words,
> your hive table "abcd" will NOT be a table within the MySQL "metastore"
> database.****
>
> ****
>
> If you want to see what is going on in the MySQL metastore, you can do the
> following:****
>
> mysql> use metastore;****
>
> mysql> show tables;****
>
> ****
>
> You should see a TBLS table, which should contain an entry for the Hive
> table that you just created (I.e. abcd):****
>
> ****
>
> mysql> select * from TBLS;****
>
> ****
>
> As for your execution error, go to your job tracker UI to see if there are
> any obvious errors. Looks like people have seen errors like this before (
> http://mail-archives.apache.org/mod_mbox/hive-user/201012.mbox/%3C4D102AF4.6060403@orkash.com%3E). Are
> you able to run regular Hadoop jobs (I.e. non-Hive)? If you can't run
> regular Hadoop jobs, you might want to start debugging that first.****
>
> ****
>
> Sriram****
>
> ****
>
> ****
>
> *From: *Aditya Singh30 <Ad...@infosys.com>
> *Reply-To: *<us...@hive.apache.org>
> *Date: *Tue, 22 Nov 2011 12:56:16 +0530
> *To: *"user@hive.apache.org" <us...@hive.apache.org>
> *Subject: *RE: Mysql metastore configuration error.****
>
> ****
>
> 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 <ja...@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 *s*ata local inpath ‘path/to/abcd.txt’ into table abcd;****
>
> ****
>
> (load sata not load data)****
>
> 2011/11/21 Aditya Singh30 <Ad...@infosys.com>****
>
> Hi Everybody,****
>
> 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 have 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 :****
>
> ****
>
> <property> ****
>
> <name>javax.jdo.option.ConnectionURL</name> ****
>
> <value>jdbc:mysql://localhost:3306/metastore?createDatabaseIfNotExist=true</value>
> ****
>
> </property> ****
>
> ****
>
> <property> ****
>
> <name>javax.jdo.option.ConnectionDriverName</name> ****
>
> <value>com.mysql.jdbc.Driver</value> ****
>
> </property> ****
>
> ****
>
> <property> ****
>
> <name>javax.jdo.option.ConnectionUserName</name> ****
>
> <value>hiveuser</value> ****
>
> </property> ****
>
> ****
>
> <property> ****
>
> <name>javax.jdo.option.ConnectionPassword</name> ****
>
> <value>hiveuser</value> ****
>
> </property> ****
>
> ****
>
> <property> ****
>
> <name>datanucleus.autoCreateSchema</name> ****
>
> <value>false</value> ****
>
> </property> ****
>
> ****
>
> <property> ****
>
> <name>datanucleus.fixedDatastore</name> ****
>
> <value>true</value> ****
>
> </property>****
>
> ****
>
> ****
>
> 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
> terminated 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=’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(Utilities.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.MySQLSyntaxErrorException: 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
> metastore db. ****
>
> So how come on Hive CLI, when I do “select * from abcd” it shows the data
> in the table. And “show tables” shows abcd there. It means Hive CLI is not
> using 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
> configuration mistake I think. Please help me out.****
>
> ****
>
> ****
>
> ****
>
> Regards,****
>
> Aditya Singh ****
>
> Infosys, India.****
>
> ****
>
> **************** CAUTION - Disclaimer *********************
>
> This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely ****
>
> for the use of 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 should carry out 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 this 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*******
>
> ****
>
--
Alexander Lorenz
http://mapredit.blogspot.com
*P **Think of the environment: please don't print this email unless you
really need to.*
RE: Mysql metastore configuration error.
Posted by Aditya Singh30 <Ad...@infosys.com>.
Hi,
@Jasper:
yes I have placed the MySQL connector in hive/lib and Hadoop/lib on both the nodes.
@Sriram:
I already tried running Hadoop in pseudo distributed mode on a single node before moving on to 2 node cluster. I started hive Thrift server on it and accessed Hive DB from my windows machine using java code. It worked fine, all the queries, Including the ones having map reduce jobs.
Regarding accessing hive thru jdbc, I figured I was doing the wrong thing, that MySQL connection URL was for hive to use MySQL for metastore not for hiveClient to access the hive DB :). I changed the code back to as described in the hiveClient tutorial (which I had already tested for pseudo distributed mode), didn't get any 'access denied' or 'no such table' errors, but the result was same, as it was on hive CLI or HWI. It starts the map-reduce jobs, "No such element found exception" occurs and execution is terminated with return code 2.
I again checked my Hadoop cluster by running some Hadoop (non-hive) examples, performing some operations on HDFS, they worked fine.
I wanted to ask one more thing. Am I right if I think that when we execute a query on say Hive CLI which runs mapreduce jobs on the Hadoop cluster, Then after the map reduce job are created, the job is like any other Hadoop job and does not requires Hive anymore.(except for the libraries which it will include in the mapreduce job program/code)??
I don't know wht to do. The only thing I can think of doing right now is getting another linux machine in place of windows machine n try all linux nodes in the cluster rather than windows-linux combination. Hive didn't worked on my windows even in the local mode. :(
Thanks,
Aditya
From: Sriram Krishnan [mailto:skrishnan@netflix.com]
Sent: Tuesday, November 22, 2011 10:07 PM
To: Aditya Singh30; user@hive.apache.org
Subject: Re: Mysql metastore configuration error.
As far as I can tell, there are two things going on here:
1. You are trying to access Hive via JDBC - if so, you are not going to be able to connect to the Hive DB the way you are trying to. If you do want to access Hive via JDBC, follow instructions here: https://cwiki.apache.org/confluence/display/Hive/HiveClient. You are going to need to run a standalone Hive server for JDBC access.
2. You are correct - it seems like you are unable to run any Hive jobs that require MapReduce. If you are getting the same error even with Derby, then it may be safe to assume that MySQL is not the culprit. I am not certain what the problem is here - however, you might want to run your query in local mode (https://cwiki.apache.org/confluence/display/Hive/GettingStarted#GettingStarted-Hive%2CMapReduceandLocalMode) to ensure that it works. Next, I would start with a single node Hadoop installation (to rule out any configuration mismatch between your Ubuntu and Cygwin nodes).
Hope that helps.
Sriram
From: Aditya Singh30 <Ad...@infosys.com>>
Date: Tue, 22 Nov 2011 14:02:56 +0530
To: "user@hive.apache.org<ma...@hive.apache.org>" <us...@hive.apache.org>>
Cc: Sriram Krishnan <sk...@netflix.com>>
Subject: RE: Mysql metastore configuration error.
I checked the TBLS table. It contains an entry for abcd.
About Hadoop only jobs. Yes I ran examples provided with Hadoop which don't use hive at all. They ran fine, I checked their output also.
I tried executing "select ab from abcd" on hive CLI and checked the job tracker UI. There were 2 failed map jobs giving the following error:
java.lang.RuntimeException: java.util.NoSuchElementException
at org.apache.hadoop.hive.ql.exec.Utilities.getMapRedWork(Utilities.java:192)
at org.apache.hadoop.hive.ql.io.HiveInputFormat.init(HiveInputFormat.java:249)
at org.apache.hadoop.hive.ql.io.HiveInputFormat.getRecordReader(HiveInputFormat.java:222)
at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:338)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:307)
at org.apache.hadoop.mapred.Child.main(Child.java:170)
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(Utilities.java:462)
at org.apache.hadoop.hive.ql.exec.Utilities.getMapRedWork(Utilities.java:184)
... 5 more
And there were no reduce jobs executed. This is the same error I got when I was using hive derby for metastore. I have checked for any Lower-Uppercase typos or spelling mistakes in the column name or table name. They are all ok.
One more thing I need to ask. Is the connection string (jdbc:mysql://master:3306/metastore","hiveuser","hiveuser") correct. I mean you said that the metastore db doesn't contain the tables directly. And here we are specifying to use the metastore DB. What should be the connection string if I want to access the table abcd via a java program.
I tried TBLS.abcd instead of abcd in the query I was executing in the java program. It gave the following error:
Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: SELECT command denied to user 'hiveuser'@'slave' for table 'abcd'
I checked the permissions for hiveuser on metastore DB, re-run the command "GRANT ALL ON metastore.* TO 'hiveuser'@'%';", but still when I run the java program it gives the same error.
Does this points to anything.
Regards,
Aditya
From: Sriram Krishnan [mailto:skrishnan@netflix.com]
Sent: Tuesday, November 22, 2011 1:19 PM
To: user@hive.apache.org<ma...@hive.apache.org>
Subject: Re: Mysql metastore configuration error.
Hive tables do not have a 1-1 mapping to tables in MySQL. In other words, your hive table "abcd" will NOT be a table within the MySQL "metastore" database.
If you want to see what is going on in the MySQL metastore, you can do the following:
mysql> use metastore;
mysql> show tables;
You should see a TBLS table, which should contain an entry for the Hive table that you just created (I.e. abcd):
mysql> select * from TBLS;
As for your execution error, go to your job tracker UI to see if there are any obvious errors. Looks like people have seen errors like this before (http://mail-archives.apache.org/mod_mbox/hive-user/201012.mbox/%3C4D102AF4.6060403@orkash.com%3E). Are you able to run regular Hadoop jobs (I.e. non-Hive)? If you can't run regular Hadoop jobs, you might want to start debugging that first.
Sriram
From: Aditya Singh30 <Ad...@infosys.com>>
Reply-To: <us...@hive.apache.org>>
Date: Tue, 22 Nov 2011 12:56:16 +0530
To: "user@hive.apache.org<ma...@hive.apache.org>" <us...@hive.apache.org>>
Subject: RE: Mysql metastore configuration error.
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<ma...@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 <Ad...@infosys.com>>
Hi Everybody,
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 have 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 :
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost:3306/metastore?createDatabaseIfNotExist=true</value>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>hiveuser</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>hiveuser</value>
</property>
<property>
<name>datanucleus.autoCreateSchema</name>
<value>false</value>
</property>
<property>
<name>datanucleus.fixedDatastore</name>
<value>true</value>
</property>
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 terminated 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='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(Utilities.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.MySQLSyntaxErrorException: 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 metastore db.
So how come on Hive CLI, when I do "select * from abcd" it shows the data in the table. And "show tables" shows abcd there. It means Hive CLI is not using 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 configuration mistake I think. Please help me out.
Regards,
Aditya Singh
Infosys, India.
**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
for the use of 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 should carry out 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 this 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***
Re: Mysql metastore configuration error.
Posted by "Alexander C.H. Lorenz" <wg...@googlemail.com>.
check your /etc/my.cnf for "old-passwords = 1". If "1" set to "0" and
restart mysql. After you have to set the password for your user again.
best,
Alex
On Tue, Nov 22, 2011 at 9:32 AM, Aditya Singh30
<Ad...@infosys.com>wrote:
> I checked the TBLS table. It contains an entry for abcd. ****
>
> About Hadoop only jobs. Yes I ran examples provided with Hadoop which
> don’t use hive at all. They ran fine, I checked their output also. ****
>
> ** **
>
> I tried executing “select ab from abcd” on hive CLI and checked the job
> tracker UI. There were 2 failed map jobs giving the following error:****
>
> java.lang.RuntimeException: java.util.NoSuchElementException****
>
> at
> org.apache.hadoop.hive.ql.exec.Utilities.getMapRedWork(Utilities.java:192)
> ****
>
> at
> org.apache.hadoop.hive.ql.io.HiveInputFormat.init(HiveInputFormat.java:249)
> ****
>
> at
> org.apache.hadoop.hive.ql.io.HiveInputFormat.getRecordReader(HiveInputFormat.java:222)
> ****
>
> at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:338)
> ****
>
> at org.apache.hadoop.mapred.MapTask.run(MapTask.java:307)****
>
> at org.apache.hadoop.mapred.Child.main(Child.java:170)****
>
> 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(Utilities.java:462)
> ****
>
> at
> org.apache.hadoop.hive.ql.exec.Utilities.getMapRedWork(Utilities.java:184)
> ****
>
> ... 5 more****
>
> ** **
>
> And there were no reduce jobs executed. This is the same error I got when
> I was using hive derby for metastore. I have checked for any
> Lower-Uppercase typos or spelling mistakes in the column name or table
> name. They are all ok.****
>
> One more thing I need to ask. Is the connection string (jdbc:mysql://master:3306/metastore","hiveuser","hiveuser")
> correct. I mean you said that the metastore db doesn’t contain the tables
> directly. And here we are specifying to use the metastore DB. What should
> be the connection string if I want to access the table abcd via a java
> program.****
>
> I tried TBLS.abcd instead of abcd in the query I was executing in the java
> program. It gave the following error:****
>
> Exception in thread "main"
> com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: SELECT command
> denied to user 'hiveuser'@'slave' for table 'abcd'****
>
> ** **
>
> I checked the permissions for hiveuser on metastore DB, re-run the command
> “GRANT ALL ON metastore.* TO 'hiveuser'@'%';”, but still when I run the
> java program it gives the same error.****
>
> Does this points to anything.****
>
> ** **
>
> ** **
>
> Regards,****
>
> Aditya****
>
> ** **
>
> *From:* Sriram Krishnan [mailto:skrishnan@netflix.com]
> *Sent:* Tuesday, November 22, 2011 1:19 PM
>
> *To:* user@hive.apache.org
> *Subject:* Re: Mysql metastore configuration error.****
>
> ** **
>
> Hive tables do not have a 1-1 mapping to tables in MySQL. In other words,
> your hive table "abcd" will NOT be a table within the MySQL "metastore"
> database.****
>
> ** **
>
> If you want to see what is going on in the MySQL metastore, you can do the
> following:****
>
> mysql> use metastore;****
>
> mysql> show tables;****
>
> ** **
>
> You should see a TBLS table, which should contain an entry for the Hive
> table that you just created (I.e. abcd):****
>
> ** **
>
> mysql> select * from TBLS;****
>
> ** **
>
> As for your execution error, go to your job tracker UI to see if there are
> any obvious errors. Looks like people have seen errors like this before (
> http://mail-archives.apache.org/mod_mbox/hive-user/201012.mbox/%3C4D102AF4.6060403@orkash.com%3E). Are
> you able to run regular Hadoop jobs (I.e. non-Hive)? If you can't run
> regular Hadoop jobs, you might want to start debugging that first.****
>
> ** **
>
> Sriram****
>
> ** **
>
> ** **
>
> *From: *Aditya Singh30 <Ad...@infosys.com>
> *Reply-To: *<us...@hive.apache.org>
> *Date: *Tue, 22 Nov 2011 12:56:16 +0530
> *To: *"user@hive.apache.org" <us...@hive.apache.org>
> *Subject: *RE: Mysql metastore configuration error.****
>
> ** **
>
> 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 <ja...@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 *s*ata local inpath ‘path/to/abcd.txt’ into table abcd;****
>
> ****
>
> (load sata not load data)****
>
> 2011/11/21 Aditya Singh30 <Ad...@infosys.com>****
>
> Hi Everybody,****
>
> 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 have 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 :****
>
> ****
>
> <property> ****
>
> <name>javax.jdo.option.ConnectionURL</name> ****
>
> <value>jdbc:mysql://localhost:3306/metastore?createDatabaseIfNotExist=true</value>
> ****
>
> </property> ****
>
> ****
>
> <property> ****
>
> <name>javax.jdo.option.ConnectionDriverName</name> ****
>
> <value>com.mysql.jdbc.Driver</value> ****
>
> </property> ****
>
> ****
>
> <property> ****
>
> <name>javax.jdo.option.ConnectionUserName</name> ****
>
> <value>hiveuser</value> ****
>
> </property> ****
>
> ****
>
> <property> ****
>
> <name>javax.jdo.option.ConnectionPassword</name> ****
>
> <value>hiveuser</value> ****
>
> </property> ****
>
> ****
>
> <property> ****
>
> <name>datanucleus.autoCreateSchema</name> ****
>
> <value>false</value> ****
>
> </property> ****
>
> ****
>
> <property> ****
>
> <name>datanucleus.fixedDatastore</name> ****
>
> <value>true</value> ****
>
> </property>****
>
> ****
>
> ****
>
> 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
> terminated 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=’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(Utilities.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.MySQLSyntaxErrorException: 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
> metastore db. ****
>
> So how come on Hive CLI, when I do “select * from abcd” it shows the data
> in the table. And “show tables” shows abcd there. It means Hive CLI is not
> using 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
> configuration mistake I think. Please help me out.****
>
> ****
>
> ****
>
> ****
>
> Regards,****
>
> Aditya Singh ****
>
> Infosys, India.****
>
> ****
>
> **************** CAUTION - Disclaimer *********************
>
> This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely ****
>
> for the use of 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 should carry out 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 this 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*******
>
> ****
>
--
Alexander Lorenz
http://mapredit.blogspot.com
*P **Think of the environment: please don't print this email unless you
really need to.*
Re: Mysql metastore configuration error.
Posted by Sriram Krishnan <sk...@netflix.com>.
As far as I can tell, there are two things going on here:
1. You are trying to access Hive via JDBC – if so, you are not going to be able to connect to the Hive DB the way you are trying to. If you do want to access Hive via JDBC, follow instructions here: https://cwiki.apache.org/confluence/display/Hive/HiveClient. You are going to need to run a standalone Hive server for JDBC access.
2. You are correct – it seems like you are unable to run any Hive jobs that require MapReduce. If you are getting the same error even with Derby, then it may be safe to assume that MySQL is not the culprit. I am not certain what the problem is here – however, you might want to run your query in local mode (https://cwiki.apache.org/confluence/display/Hive/GettingStarted#GettingStarted-Hive%2CMapReduceandLocalMode) to ensure that it works. Next, I would start with a single node Hadoop installation (to rule out any configuration mismatch between your Ubuntu and Cygwin nodes).
Hope that helps.
Sriram
From: Aditya Singh30 <Ad...@infosys.com>>
Date: Tue, 22 Nov 2011 14:02:56 +0530
To: "user@hive.apache.org<ma...@hive.apache.org>" <us...@hive.apache.org>>
Cc: Sriram Krishnan <sk...@netflix.com>>
Subject: RE: Mysql metastore configuration error.
I checked the TBLS table. It contains an entry for abcd.
About Hadoop only jobs. Yes I ran examples provided with Hadoop which don’t use hive at all. They ran fine, I checked their output also.
I tried executing “select ab from abcd” on hive CLI and checked the job tracker UI. There were 2 failed map jobs giving the following error:
java.lang.RuntimeException: java.util.NoSuchElementException
at org.apache.hadoop.hive.ql.exec.Utilities.getMapRedWork(Utilities.java:192)
at org.apache.hadoop.hive.ql.io.HiveInputFormat.init(HiveInputFormat.java:249)
at org.apache.hadoop.hive.ql.io.HiveInputFormat.getRecordReader(HiveInputFormat.java:222)
at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:338)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:307)
at org.apache.hadoop.mapred.Child.main(Child.java:170)
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(Utilities.java:462)
at org.apache.hadoop.hive.ql.exec.Utilities.getMapRedWork(Utilities.java:184)
... 5 more
And there were no reduce jobs executed. This is the same error I got when I was using hive derby for metastore. I have checked for any Lower-Uppercase typos or spelling mistakes in the column name or table name. They are all ok.
One more thing I need to ask. Is the connection string (jdbc:mysql://master:3306/metastore","hiveuser","hiveuser") correct. I mean you said that the metastore db doesn’t contain the tables directly. And here we are specifying to use the metastore DB. What should be the connection string if I want to access the table abcd via a java program.
I tried TBLS.abcd instead of abcd in the query I was executing in the java program. It gave the following error:
Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: SELECT command denied to user 'hiveuser'@'slave' for table 'abcd'
I checked the permissions for hiveuser on metastore DB, re-run the command “GRANT ALL ON metastore.* TO 'hiveuser'@'%';”, but still when I run the java program it gives the same error.
Does this points to anything.
Regards,
Aditya
From: Sriram Krishnan [mailto:skrishnan@netflix.com]
Sent: Tuesday, November 22, 2011 1:19 PM
To: user@hive.apache.org<ma...@hive.apache.org>
Subject: Re: Mysql metastore configuration error.
Hive tables do not have a 1-1 mapping to tables in MySQL. In other words, your hive table "abcd" will NOT be a table within the MySQL "metastore" database.
If you want to see what is going on in the MySQL metastore, you can do the following:
mysql> use metastore;
mysql> show tables;
You should see a TBLS table, which should contain an entry for the Hive table that you just created (I.e. abcd):
mysql> select * from TBLS;
As for your execution error, go to your job tracker UI to see if there are any obvious errors. Looks like people have seen errors like this before (http://mail-archives.apache.org/mod_mbox/hive-user/201012.mbox/%3C4D102AF4.6060403@orkash.com%3E). Are you able to run regular Hadoop jobs (I.e. non-Hive)? If you can't run regular Hadoop jobs, you might want to start debugging that first.
Sriram
From: Aditya Singh30 <Ad...@infosys.com>>
Reply-To: <us...@hive.apache.org>>
Date: Tue, 22 Nov 2011 12:56:16 +0530
To: "user@hive.apache.org<ma...@hive.apache.org>" <us...@hive.apache.org>>
Subject: RE: Mysql metastore configuration error.
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<ma...@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 <Ad...@infosys.com>>
Hi Everybody,
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 have 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 :
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost:3306/metastore?createDatabaseIfNotExist=true</value>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>hiveuser</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>hiveuser</value>
</property>
<property>
<name>datanucleus.autoCreateSchema</name>
<value>false</value>
</property>
<property>
<name>datanucleus.fixedDatastore</name>
<value>true</value>
</property>
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 terminated 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=’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(Utilities.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.MySQLSyntaxErrorException: 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 metastore db.
So how come on Hive CLI, when I do “select * from abcd” it shows the data in the table. And “show tables” shows abcd there. It means Hive CLI is not using 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 configuration mistake I think. Please help me out.
Regards,
Aditya Singh
Infosys, India.
**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
for the use of 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 should carry out 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 this 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***
RE: Mysql metastore configuration error.
Posted by Aditya Singh30 <Ad...@infosys.com>.
I checked the TBLS table. It contains an entry for abcd.
About Hadoop only jobs. Yes I ran examples provided with Hadoop which don't use hive at all. They ran fine, I checked their output also.
I tried executing "select ab from abcd" on hive CLI and checked the job tracker UI. There were 2 failed map jobs giving the following error:
java.lang.RuntimeException: java.util.NoSuchElementException
at org.apache.hadoop.hive.ql.exec.Utilities.getMapRedWork(Utilities.java:192)
at org.apache.hadoop.hive.ql.io.HiveInputFormat.init(HiveInputFormat.java:249)
at org.apache.hadoop.hive.ql.io.HiveInputFormat.getRecordReader(HiveInputFormat.java:222)
at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:338)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:307)
at org.apache.hadoop.mapred.Child.main(Child.java:170)
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(Utilities.java:462)
at org.apache.hadoop.hive.ql.exec.Utilities.getMapRedWork(Utilities.java:184)
... 5 more
And there were no reduce jobs executed. This is the same error I got when I was using hive derby for metastore. I have checked for any Lower-Uppercase typos or spelling mistakes in the column name or table name. They are all ok.
One more thing I need to ask. Is the connection string (jdbc:mysql://master:3306/metastore","hiveuser","hiveuser") correct. I mean you said that the metastore db doesn't contain the tables directly. And here we are specifying to use the metastore DB. What should be the connection string if I want to access the table abcd via a java program.
I tried TBLS.abcd instead of abcd in the query I was executing in the java program. It gave the following error:
Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: SELECT command denied to user 'hiveuser'@'slave' for table 'abcd'
I checked the permissions for hiveuser on metastore DB, re-run the command "GRANT ALL ON metastore.* TO 'hiveuser'@'%';", but still when I run the java program it gives the same error.
Does this points to anything.
Regards,
Aditya
From: Sriram Krishnan [mailto:skrishnan@netflix.com]
Sent: Tuesday, November 22, 2011 1:19 PM
To: user@hive.apache.org
Subject: Re: Mysql metastore configuration error.
Hive tables do not have a 1-1 mapping to tables in MySQL. In other words, your hive table "abcd" will NOT be a table within the MySQL "metastore" database.
If you want to see what is going on in the MySQL metastore, you can do the following:
mysql> use metastore;
mysql> show tables;
You should see a TBLS table, which should contain an entry for the Hive table that you just created (I.e. abcd):
mysql> select * from TBLS;
As for your execution error, go to your job tracker UI to see if there are any obvious errors. Looks like people have seen errors like this before (http://mail-archives.apache.org/mod_mbox/hive-user/201012.mbox/%3C4D102AF4.6060403@orkash.com%3E). Are you able to run regular Hadoop jobs (I.e. non-Hive)? If you can't run regular Hadoop jobs, you might want to start debugging that first.
Sriram
From: Aditya Singh30 <Ad...@infosys.com>>
Reply-To: <us...@hive.apache.org>>
Date: Tue, 22 Nov 2011 12:56:16 +0530
To: "user@hive.apache.org<ma...@hive.apache.org>" <us...@hive.apache.org>>
Subject: RE: Mysql metastore configuration error.
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<ma...@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 <Ad...@infosys.com>>
Hi Everybody,
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 have 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 :
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost:3306/metastore?createDatabaseIfNotExist=true</value>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>hiveuser</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>hiveuser</value>
</property>
<property>
<name>datanucleus.autoCreateSchema</name>
<value>false</value>
</property>
<property>
<name>datanucleus.fixedDatastore</name>
<value>true</value>
</property>
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 terminated 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='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(Utilities.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.MySQLSyntaxErrorException: 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 metastore db.
So how come on Hive CLI, when I do "select * from abcd" it shows the data in the table. And "show tables" shows abcd there. It means Hive CLI is not using 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 configuration mistake I think. Please help me out.
Regards,
Aditya Singh
Infosys, India.
**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
for the use of 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 should carry out 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 this 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***
Re: Mysql metastore configuration error.
Posted by Sriram Krishnan <sk...@netflix.com>.
Hive tables do not have a 1-1 mapping to tables in MySQL. In other words, your hive table "abcd" will NOT be a table within the MySQL "metastore" database.
If you want to see what is going on in the MySQL metastore, you can do the following:
mysql> use metastore;
mysql> show tables;
You should see a TBLS table, which should contain an entry for the Hive table that you just created (I.e. abcd):
mysql> select * from TBLS;
As for your execution error, go to your job tracker UI to see if there are any obvious errors. Looks like people have seen errors like this before (http://mail-archives.apache.org/mod_mbox/hive-user/201012.mbox/%3C4D102AF4.6060403@orkash.com%3E). Are you able to run regular Hadoop jobs (I.e. non-Hive)? If you can't run regular Hadoop jobs, you might want to start debugging that first.
Sriram
From: Aditya Singh30 <Ad...@infosys.com>>
Reply-To: <us...@hive.apache.org>>
Date: Tue, 22 Nov 2011 12:56:16 +0530
To: "user@hive.apache.org<ma...@hive.apache.org>" <us...@hive.apache.org>>
Subject: RE: Mysql metastore configuration error.
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<ma...@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 <Ad...@infosys.com>>
Hi Everybody,
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 have 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 :
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost:3306/metastore?createDatabaseIfNotExist=true</value>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>hiveuser</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>hiveuser</value>
</property>
<property>
<name>datanucleus.autoCreateSchema</name>
<value>false</value>
</property>
<property>
<name>datanucleus.fixedDatastore</name>
<value>true</value>
</property>
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 terminated 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=’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(Utilities.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.MySQLSyntaxErrorException: 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 metastore db.
So how come on Hive CLI, when I do “select * from abcd” it shows the data in the table. And “show tables” shows abcd there. It means Hive CLI is not using 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 configuration mistake I think. Please help me out.
Regards,
Aditya Singh
Infosys, India.
**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
for the use of 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 should carry out 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 this 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***
RE: Mysql metastore configuration error.
Posted by Aditya Singh30 <Ad...@infosys.com>.
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 <Ad...@infosys.com>>
Hi Everybody,
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 have 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 :
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost:3306/metastore?createDatabaseIfNotExist=true</value>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>hiveuser</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>hiveuser</value>
</property>
<property>
<name>datanucleus.autoCreateSchema</name>
<value>false</value>
</property>
<property>
<name>datanucleus.fixedDatastore</name>
<value>true</value>
</property>
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 terminated 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='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(Utilities.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.MySQLSyntaxErrorException: 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 metastore db.
So how come on Hive CLI, when I do "select * from abcd" it shows the data in the table. And "show tables" shows abcd there. It means Hive CLI is not using 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 configuration mistake I think. Please help me out.
Regards,
Aditya Singh
Infosys, India.
**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
for the use of 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 should carry out 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 this 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***
Re: Mysql metastore configuration error.
Posted by Stephen Boesch <ja...@gmail.com>.
Was that code above *verbatim? *
because there is a typo
Hive> Load *s*ata local inpath ‘path/to/abcd.txt’ into table abcd;
(load sata not load data)
2011/11/21 Aditya Singh30 <Ad...@infosys.com>
> Hi Everybody,****
>
> 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 have 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 :****
>
> ** **
>
> <property> ****
>
> <name>javax.jdo.option.ConnectionURL</name> ****
>
> <value>jdbc:mysql://localhost:3306/metastore?createDatabaseIfNotExist=true</value>
> ****
>
> </property> ****
>
> ** **
>
> <property> ****
>
> <name>javax.jdo.option.ConnectionDriverName</name> ****
>
> <value>com.mysql.jdbc.Driver</value> ****
>
> </property> ****
>
> ** **
>
> <property> ****
>
> <name>javax.jdo.option.ConnectionUserName</name> ****
>
> <value>hiveuser</value> ****
>
> </property> ****
>
> ** **
>
> <property> ****
>
> <name>javax.jdo.option.ConnectionPassword</name> ****
>
> <value>hiveuser</value> ****
>
> </property> ****
>
> ** **
>
> <property> ****
>
> <name>datanucleus.autoCreateSchema</name> ****
>
> <value>false</value> ****
>
> </property> ****
>
> ****
>
> <property> ****
>
> <name>datanucleus.fixedDatastore</name> ****
>
> <value>true</value> ****
>
> </property>****
>
> ** **
>
> ** **
>
> 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
> terminated 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=’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(Utilities.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.MySQLSyntaxErrorException: 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
> metastore db. ****
>
> So how come on Hive CLI, when I do “select * from abcd” it shows the data
> in the table. And “show tables” shows abcd there. It means Hive CLI is not
> using 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
> configuration mistake I think. Please help me out.****
>
> ** **
>
> ** **
>
> ** **
>
> Regards,****
>
> Aditya Singh ****
>
> Infosys, India.****
>
> ** **
>
> **************** CAUTION - Disclaimer *****************
> This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
> for the use of 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 should carry out 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 this 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***
>
>