hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Florin Diaconeasa <florin.diacone...@gmail.com>
Subject Re: Mysql metastore configuration error.
Date Wed, 23 Nov 2011 14:21:53 GMT
@Alexander

>From what i'm seeing inside the original email,
the createDatabaseIfNotExist property is set on TRUE inside
the javax.jdo.option.ConnectionURL property
So the metadata database should have been created by hive.

@Aditya
Did you create the metadata DB by hand or was it generated by Hive?

On 23 November 2011 10:45, Alexander C.H. Lorenz
<wget.null@googlemail.com>wrote:

> Hi Aditya,
>
> I could reproduce the issue. Please create a new database @mysql without
> schema-updates. Hive will create the necessary tables if the database has
> no tables (?createDatabaseIfNotExist=true). The schema-upgrades are written
> for upgrades only, not for new datasets. To create the tables start hive a
> fist time and create a table inside. That will took a while, after you can
> check your metastore-db:
> mysql> show tables;
> +-------------------------+
> | Tables_in_hive_test_new |
> +-------------------------+
> | BUCKETING_COLS          |
> | COLUMNS                 |
> | DATABASE_PARAMS         |
> | DBS                     |
> | DB_PRIVS                |
> | GLOBAL_PRIVS            |
> | IDXS                    |
> | INDEX_PARAMS            |
> | PARTITIONS              |
> | PARTITION_KEYS          |
> | PARTITION_KEY_VALS      |
> | PARTITION_PARAMS        |
> | PART_COL_PRIVS          |
> | PART_PRIVS              |
> | ROLES                   |
> | ROLE_MAP                |
> | SDS                     |
> | SD_PARAMS               |
> | SEQUENCE_TABLE          |
> | SERDES                  |
> | SERDE_PARAMS            |
> | SORT_COLS               |
> | TABLE_PARAMS            |
> | TBLS                    |
> | TBL_COL_PRIVS           |
> | TBL_PRIVS               |
> | TYPES                   |
> | TYPE_FIELDS             |
> +-------------------------+
> 28 rows in set (0.00 sec)
>
> regards
> - alex
>
>
> On Wed, Nov 23, 2011 at 9:24 AM, Aditya Singh30 <
> Aditya_Singh30@infosys.com> wrote:
>
>> Hi,****
>>
>> ** **
>>
>> @Jasper: ****
>>
>> yes I have placed the MySQL connector in hive/lib and Hadoop/lib on both
>> the nodes.****
>>
>> ** **
>>
>> @Sriram: ****
>>
>> I already tried running Hadoop in pseudo distributed mode on a single
>> node before moving on to 2 node cluster. I started hive Thrift server on it
>> and accessed Hive DB from my windows machine using java code. It worked
>> fine, all the queries, Including the ones having map reduce jobs.****
>>
>> Regarding accessing hive thru jdbc, I figured I was doing the wrong
>> thing, that MySQL connection URL was for hive to use MySQL for metastore
>> not for hiveClient to access the hive DB J. I changed the code back to
>> as described in the hiveClient tutorial (which I had already tested for
>> pseudo distributed mode), didn’t get any ‘access denied‘ or ‘no such table’
>> errors, but the result was same, as it was on hive CLI or HWI. It starts
>> the map-reduce jobs, “No such element found exception” occurs and execution
>> is terminated with return code 2.****
>>
>> I again checked my Hadoop cluster by running some Hadoop (non-hive)
>> examples, performing some operations on HDFS, they worked fine.****
>>
>> I wanted to ask one more thing. Am I right if I think that when we
>> execute a query on say Hive CLI which runs mapreduce jobs on the Hadoop
>> cluster, Then after the map reduce job are created, the job is like any
>> other Hadoop job and does not requires Hive anymore.(except for the
>> libraries which it will include in the mapreduce job program/code)??****
>>
>> ** **
>>
>> I don’t know wht to do. The only thing I can think of doing right now is
>> getting another linux machine in place of windows machine n try all linux
>> nodes in the cluster rather than windows-linux combination. Hive didn’t
>> worked on my windows even in the local mode. L****
>>
>> ** **
>>
>> ** **
>>
>> Thanks, ****
>>
>> Aditya****
>>
>> ** **
>>
>> ** **
>>
>> *From:* Sriram Krishnan [mailto:skrishnan@netflix.com]
>> *Sent:* Tuesday, November 22, 2011 10:07 PM
>> *To:* Aditya Singh30; user@hive.apache.org
>>
>> *Subject:* Re: Mysql metastore configuration error.****
>>
>> ** **
>>
>> 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_Singh30@infosys.com>
>> *Date: *Tue, 22 Nov 2011 14:02:56 +0530
>> *To: *"user@hive.apache.org" <user@hive.apache.org>
>> *Cc: *Sriram Krishnan <skrishnan@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:skrishnan@netflix.com<skrishnan@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.6060403@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_Singh30@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:javadba@gmail.com <javadba@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_Singh30@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*******
>>
>>  ****
>>
>
>
>
> --
> Alexander Lorenz
> http://mapredit.blogspot.com
>
> *P **Think of the environment: please don't print this email unless you
> really need to.*
>
>
>


-- 


Florin

Mime
View raw message