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 <aditya_sing...@infosys.com<mailto:aditya_sing...@infosys.com>> Date: Tue, 22 Nov 2011 14:02:56 +0530 To: "user@hive.apache.org<mailto:user@hive.apache.org>" <user@hive.apache.org<mailto:user@hive.apache.org>> Cc: Sriram Krishnan <skrish...@netflix.com<mailto:skrish...@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:skrish...@netflix.com] Sent: Tuesday, November 22, 2011 1:19 PM To: user@hive.apache.org<mailto: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<mailto:aditya_sing...@infosys.com>> Reply-To: <user@hive.apache.org<mailto:user@hive.apache.org>> Date: Tue, 22 Nov 2011 12:56:16 +0530 To: "user@hive.apache.org<mailto:user@hive.apache.org>" <user@hive.apache.org<mailto: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] Sent: Tuesday, November 22, 2011 12:32 PM To: user@hive.apache.org<mailto: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 <aditya_sing...@infosys.com<mailto: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***
