Did you add the mysql jar properly in the classpath? Before that are you
able to create and populate tables using Hive CLI?
If the HiveCLI is working correctly,then try to modify your hive-site.xml as
follows.
//hive-site.xml
<property>
<name>hive.metastore.local</name>
<value>true</value>
</property>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost:3306/hive?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>username</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>password</value>
</property>
Before doing these steps you need to do few tricks in mysql.
Start the mysql server.Login as root.
You need to create a new user as specified in your hive username in
hive-site.xml
mysql> CREATE USER 'username'@'localhost' IDENTIFIED BY 'username';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT
OPTION;
mysql> exit
Then check whether you are able to login mysql as the new user.
Pls Note: The mysql-connector-java*.jar should in the library(either hadoop
or hive). The above procedure helps you to run Hive queries in CLI with
mysql as metastore.Once you succeed,try to use drivers to access the
metastore from an application.
Hope it helps!
On Wed, Feb 23, 2011 at 5:58 PM, <hi...@gmx.de> wrote:
> Hello,
>
> I am running hive with a hadoop mini cluster and want to switch to mysql as
> a metastore because I need multiple connections at same time (not sure if
> this will work at all). I tried the local metastore config. The DB and two
> tables in mysql are created but I receive an error. If I comment out the
> metastore settings in hive-site.xml everthing goes well with derby.
>
> Maybe somebody has a clue.
> Malte
>
> Java code
> =========
> Connection con = DriverManager.getConnection("jdbc:hive://", "", "");
> Statement stmt = con.createStatement();
> String tableName = "testHiveDriverTable";
> ResultSet res = null;
> // show tables
> String sql = "show tables";
> System.out.println("Running: " + sql);
> res = stmt.executeQuery(sql);
> while (res.next()) {
> System.out.println(res.getString(1));
> }
>
> Exception
> =========
> FAILED: Error in metadata: java.lang.IllegalArgumentException: URI: does
> not have a scheme
> 13:16:47,158 ERROR (SessionState.java:277) - FAILED: Error in metadata:
> java.lang.IllegalArgumentException: URI: does not have a scheme
> org.apache.hadoop.hive.ql.metadata.HiveException:
> java.lang.IllegalArgumentException: URI: does not have a scheme
> at
> org.apache.hadoop.hive.ql.metadata.Hive.getTablesByPattern(Hive.java:605)
> at
> org.apache.hadoop.hive.ql.metadata.Hive.getAllTables(Hive.java:577)
> at
> org.apache.hadoop.hive.ql.metadata.Hive.getAllTables(Hive.java:567)
> at
> org.apache.hadoop.hive.ql.exec.DDLTask.showTables(DDLTask.java:1090)
> at org.apache.hadoop.hive.ql.exec.DDLTask.execute(DDLTask.java:227)
> at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:107)
> at
> org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:55)
> at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:633)
> at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:506)
> at org.apache.hadoop.hive.ql.Driver.run(Driver.java:384)
> at
> org.apache.hadoop.hive.service.HiveServer$HiveServerHandler.execute(HiveServer.java:114)
> at
> org.apache.hadoop.hive.jdbc.HiveStatement.executeQuery(HiveStatement.java:195)
> at de.codiacs.hive.service.HiveService2.main(HiveService2.java:38)
> Caused by: java.lang.IllegalArgumentException: URI: does not have a scheme
> at
> org.apache.hadoop.hive.metastore.HiveMetaStoreClient.<init>(HiveMetaStoreClient.java:105)
> at
> org.apache.hadoop.hive.ql.metadata.Hive.createMetaStoreClient(Hive.java:1269)
> at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:1279)
> at
> org.apache.hadoop.hive.ql.metadata.Hive.getTablesByPattern(Hive.java:603)
> ... 12 more
>
> FAILED: Execution Error, return code 1 from
> org.apache.hadoop.hive.ql.exec.DDLTask
> 13:16:47,159 ERROR (SessionState.java:277) - FAILED: Execution Error,
> return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask
> Exception in thread "main" java.sql.SQLException: Query returned non-zero
> code: 9, cause: FAILED: Execution Error, return code 1 from
> org.apache.hadoop.hive.ql.exec.DDLTask
> at
> org.apache.hadoop.hive.jdbc.HiveStatement.executeQuery(HiveStatement.java:197)
> at de.codiacs.hive.service.HiveService2.main(HiveService2.java:38)
>
>
> part of hive-site.xml
> =====================
> <property>
> <name>javax.jdo.option.ConnectionURL</name>
>
>
> <value>jdbc:mysql://localhost/hive_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>root</value>
> </property>
> <property>
> <name>javax.jdo.option.ConnectionPassword</name>
> <value>test</value>
> </property>
> <property>
> <name>hive.metastore.local </name>
> <value>local</value>
> </property>
>
> <property>
> <name>hive.metastore.warehouse.dir</name>
> <value>hdfs://192.168.56.101:54310/hive</value>
> <description>location of default database for the
> warehouse</description>
> </property>
> --
> GMX DSL Doppel-Flat ab 19,99 Euro/mtl.! Jetzt mit
> gratis Handy-Flat! http://portal.gmx.net/de/go/dsl
>
--
Regards,
Sangita.
