24

Folder metastore_db is created in any directory where I run Hive query. Is there any way to have only one metastore_db in a defined location and stop it from being created all over the places? Does it have anything to do with hive.metastore.local?

darcyy
  • 5,236
  • 5
  • 28
  • 41

2 Answers2

39

The property of interest here is javax.jdo.option.ConnectionURL. The default value of this property is jdbc:derby:;databaseName=metastore_db;create=true. This value specifies that you will be using embedded derby as your Hive metastore and the location of the metastore is metastore_db. Also the metastore will be created if it doesn't already exist.

Note that the location of the metastore (metastore_db) is a relative path. Therefore, it gets created where you launch Hive from. If you update this property (in your hive-site.xml) to be, say an absolute path to a location, the metastore will be used from that location.

I must warn you though that embedded derby metastore can only be accessed by one user at a time. Hive uses embedded derby by default to allow an out of the box experience and for ease of testing. For any practical system, I would recommend moving to standalone "real" database like MySQL or PostgreSQL. Instructions on how to do that are available here.

pmichna
  • 4,800
  • 13
  • 53
  • 90
Mark Grover
  • 4,070
  • 22
  • 21
  • any idea how to set also a default location for the generated derby.log? – Ghashange Mar 09 '15 at 14:26
  • 1
    I change the path to absolute: ``jdbc:derby:;databaseName=/root/metastore_db;create=true`` but I got the error message: ``java.lang.IllegalArgumentException: java.net.URISyntaxException: Relative path in absolute URI: ${system:java.io.tmpdir%7D/$%7Bsystem:user.name%7D`` – xxks-kkk Oct 13 '16 at 06:59
  • @zack the error you have listed might be due to some other property in hive-site.xml file (e.g. hive.exec.local.scratchdir). Am able to get the ConnectionURL working with absolute metastore path. Only issue pending is how to prevent creation of derby.log file in every directory i trigger hive script from. – Saurabh Mishra Mar 22 '17 at 14:53
  • Which file does this need to be changed in ? – AbdealiLoKo Jan 15 '21 at 07:44
  • Thank you sir Mark! – shawn1912 Oct 15 '21 at 16:36
5

Since you use Embedded derby mode. To use single metastore_db location. you need to change following properties.

<property>
  <name>javax.jdo.option.ConnectionURL</name>  
  <value>jdbc:derby:;databaseName=/<file-location>/metastore_db;create=true</value>
  <description>JDBC connect string for a JDBC metastore</description>
</property>

I hope this will help you. For more details click here

ǨÅVËĔŊ RĀǞĴĄŅ
  • 530
  • 6
  • 30