hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jasper Knulst <jasper.knu...@incentro.com>
Subject Re: FW: Mysql metastore configuration error.
Date Tue, 22 Nov 2011 12:59:45 GMT
Hi Aditya,

Do you have a mysql-connector jar in your classpath for the Hadoop
jobtracker/tasktrackers ?

Gr
Jasper

2011/11/22 Aditya Singh30 <Aditya_Singh30@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 <Aditya_Singh30@infosys.com>
> *Reply-To: *<user@hive.apache.org>
> *Date: *Tue, 22 Nov 2011 12:56:16 +0530
> *To: *"user@hive.apache.org" <user@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 <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 *s*ata local inpath ‘path/to/abcd.txt’ into table abcd;****
>
>  ****
>
> (load sata not load data)****
>
> 2011/11/21 Aditya Singh30 <Aditya_Singh30@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]

Mime
View raw message