how to find hive metastore is using derby or MYSQL as database?

hive metastore tables
where is hive metastore stored by default
hive metastore properties
hive default database
dzone hive metastore
how to connect to hive metastore from command line
why hive metastore is rdbms
local and remote metastore in hive

I am trying to find the hive remote meta-store is using Derby or MYSQL as database?

Is there a way to find this?

Check your hive-site.xml.

If there are following lines:

  <value>jdbc:mysql://<host name>/<database name>..</value>
<description>JDBC connect string for a JDBC metastore</description>

Your meta-store in mysql.

<description>JDBC connect string for a JDBC metastore</description>

Your meta-store in derby.

More about configure meta-store


If the above line not in hive-site.xml, try searching in hive-default.xml.template and hivemetastore-site.xml.

More about the conf files:

  1. hive-default.xml.template contains the default values for various configuration variables that come prepackaged in a Hive distribution.

  2. In order to override any of the values, create hive-site.xml instead and set the value in that file as shown above.

  3. You can set metastore-specific configuration values in hivemetastore-site.xml.

BTW: Derby is the default database for the Hive metastore (Metadata Store).

If you want to read more, the official docs

Hive-Metastore: A Basic Introduction, It is not recommended to use derby as a hive matastore due to many known derby limitations. If derby Identify the derby database folder. Derby by Import the hive metastore tables data from the csv files to mysql.(Do not  Access your MySQL and create one new database metastore for hive, Command : $ mysql -u root -p. Enter password: mysql> CREATE DATABASE metastore; Step-4 : Then we need one MySQL account for Hive to access the metastore. It is very important to prevent this user account from any type of change in schema. Command : mysql> CREATE USER ‘hive’@’%’ IDENTIFIED BY ‘password’;

Update the below property in Hive and deploy the same and then continue the installation. Go to Hive->Configuration and upate below property: Hive Metastore Database Host= where FQDN is host where Name Node resides.

Example: Hive Metastore Database

Hive Metastore, By Default, Hive comes with derby database as metastore. Using sudo apt-get install mysql-server command, we can download mysql server. Why to Use MySQL in Hive as Metastore: By Default, Hive comes with derby database as metastore. Derby database can support only single active user at a time ; Derby is not recommended in production environment ; So the solution here is . Use MYSQL as Meta storage at backend to connect multiple users with Hive at a time; MYSQL is Best choice for

I know I'm bit late to the party but, I came up with a much more easier and handy solution to get the details of you metastore.

Open up your Hive Cli and type in below command

hive> set javax.jdo.option.ConnectionURL;

It should show your hive metastore URL which is defined in hive-site.xml, it should look like this


Hope this helps

What is the default database provided by Apache Hive for metastore , Use the following steps to set up the database for your Hive Metastore. This step must The value for $databaseType can be derby, mysql, oracle, mssql, or postgres. If you get this error, you might need to reassign the $HIVE_USER shell. The metadata for Hive tables and partitions are stored in the Hive Metastore. By default, the Hive Metastore stores all Hive metadata in an embedded Apache Derby database in the MapR file system. Derby only allows one connection at a time; if you want multiple concurrent Hive sessions, you can use MySQL for the Hive Metastore.

Use MySQL for the Hive Metastore, In this mode, the metastore uses a Derby database, and both the Cloudera strongly encourages you to use MySQL because it is the most For information about installing and configuring MariaDB for CDH, see MariaDB Database. Configuring a Remote MySQL Database for the Hive Metastore. Now when you run your Hive query and you are using the default Derby database, you will find that your current directory now contains a new sub-directory, metastore_db. Also, the metastore will be

How do I migrate (derby) hive metastore data stored in embedded , Take a look at how Apache Hive can be combined with a metastore to easily then it is good, but when we want to use it in a cluster, then MySQL or any and you are using the default Derby database, you will find that your  Metastore is where hive store schema of tables, and more data how directory which reference data for schema table in warehouse. Warehouse commonly store in HDFS, metastore in relational database like Derby, MySQL or Postgre. Metastore usually is used for many other applications like impala for discover tables in warehouse.

HIVE Metastore Configuration with MYSQL, Apache Hive tutorial cover Hive Metastore Introduction,Configuring Hive service runs in the same JVM by using embedded Derby Database. We can achieve by using any JDBC compliant like MySQL which See Also-. Now since there is a new client you MUST make sure Hive has these jar files in the lib directory or in the classpath. The same would be true if you used MySQL or some other DB. cp /opt/hadoop/db-derby- /opt/hadoop/hive/lib cp /opt/hadoop/db-derby- /opt/hadoop/hive/lib

  • I dont have any of the entry in my hive-site.xml, does it mean it is derby?