Hive Metastore Derby DB

If you are using Hive in default mode, you may see the following behavior – you get to hive client from different directories and see different results when you run a query like “show tables”.  For example, you have hive installed in /usr/local/hive and your are currently in your home directory and run

~> /usr/local/hive/bin/hive    #– get to hive
hive> create table new_table (c1 string);
hive> show tables;

Now you will see “new_table” in the list.

~> cd /tmp
/tmp> /usr/local/hive/bin/hive   #– get to hive
hive> show tables;

Now you don’t see “new_table” in your list of tables.  Those who come from typical SQL background may find it little weird in the beginning due to fact that results seem different depending on from where you started the hive client.  The reason  is because hive uses “embedded Derby” database to store meta data and one of the default configuration property is to use the current directory to store metastore_db.

On starting the hive from two different directories like above, one would see two “metastore_db” directories  created in home (~) and /tmp directories.  You can change this and use one metastore_db by updating “/usr/local/hive/conf/hive-default.xml” file’s “javax.jdo.option.ConnectionURL” as below.

Default setting:
   
      javax.jdo.option.ConnectionURL
      jdbc:derby:;databaseName=metastore_db;create=true
      JDBC connect string for a JDBC metastore
   

Update it to:
   
      javax.jdo.option.ConnectionURL
      jdbc:derby:;databaseName=/home/hadoop/metastore_db;create=true
      JDBC connect string for a JDBC metastore
   

“/home/hadoop” is an example and one can appropriately change it to suitable host and directory.  Say,
jdbc:derby:;databaseName=//localhost:1234/metastore_db;create=true

Cheers,
Shiva

10 thoughts on “Hive Metastore Derby DB

  1. Hi,
    I am a newbie to hive. I have installed hadoop-0.20.2 using cygwin on my windows 7 machine. It's working fine. Now I have tried to install the Hive-0.10.0 and when I go to
    hive> show tables;
    It doesn't give any output.
    Is I am missing anything?

  2. Hi ,

    I'm getting below error , while doing show tables or create table on hive , please help on this to resolve

    hive> show tables;
    14/01/20 11:58:43 INFO parse.ParseDriver: Parsing command: show tables
    14/01/20 11:58:43 INFO parse.ParseDriver: Parse Completed
    14/01/20 11:58:43 INFO ql.Driver: Semantic Analysis Completed
    14/01/20 11:58:44 INFO ql.Driver: Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:tab_name, type:string, comment:from deserializer)], properties:null)
    14/01/20 11:58:44 INFO ql.Driver: Starting command: show tables
    FAILED: Error in metadata: java.lang.IllegalArgumentException: URI: does not have a scheme
    14/01/20 11:58:44 ERROR exec.FetchTask: 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.getDatabase(Hive.java:1028)
    at org.apache.hadoop.hive.ql.metadata.Hive.databaseExists(Hive.java:1013)
    at org.apache.hadoop.hive.ql.exec.DDLTask.showTables(DDLTask.java:1712)
    at org.apache.hadoop.hive.ql.exec.DDLTask.execute(DDLTask.java:289)
    at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:131)
    at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:57)
    at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:1063)
    at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:900)
    at org.apache.hadoop.hive.ql.Driver.run(Driver.java:748)
    at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:209)
    at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:286)
    at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:516)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.apache.hadoop.util.RunJar.main(RunJar.java:197)
    Caused by: java.lang.IllegalArgumentException: URI: does not have a scheme
    at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.(HiveMetaStoreClient.java:127)
    at org.apache.hadoop.hive.ql.metadata.Hive.createMetaStoreClient(Hive.java:1868)
    at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:1878)
    at org.apache.hadoop.hive.ql.metadata.Hive.getDatabase(Hive.java:1024)
    … 16 more

    FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask
    14/01/20 11:58:44 ERROR ql.Driver: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask

  3. Hi Shiva,

    I was wondering how would you connect remotely to the above derby metastore since we haven't really configured a specific port in the above case. Thanks again!

  4. Hi Shiva,

    Could you explain how to connect to mysql instead of Derby database from spark?
    Hive is configured to connect to mysql: jdbc:mysql://localhost/metastore_db;create=true.

    But Spark does not connect to derby. Could you help

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s