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<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***
