Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 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.
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
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB