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_sing...@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:skrish...@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.6060...@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_sing...@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:java...@gmail.com <java...@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_sing...@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.knu...@incentro.com *T:* +31157640750 *M: *+31619667511 *W:* www.incentro.com [image: Logo Incentro]
