Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive, mail # user - Mysql metastore configuration error.


Copy link to this message
-
Re: Mysql metastore configuration error.
Sriram Krishnan 2011-11-22, 07:48
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/%[EMAIL PROTECTED]%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 <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
Reply-To: <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
Date: Tue, 22 Nov 2011 12:56:16 +0530
To: "[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>" <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
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:[EMAIL PROTECTED]]
Sent: Tuesday, November 22, 2011 12:32 PM
To: [EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>
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 <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
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 mess