I need to change default database derby to mysql for my cluster, because of this derby I am not able to open second session in hive to connect derby (After installation I saw that derby has this limit).
How can I change this default database, I tried below link. I worked for sometime, once I restart hive everything is gone back to derby.
In Cloudera Manager open Hive Configuration, next search for hive-site. The first row that appear is Hive Service Advance Configuration Snippet, copy there your xml file and changes should be permanent.
Cloudera Manager should be able to re-configure the underlying database for the Hive Metastore service. Go to Hive-->Configuration & search for something like database. You will need to set the following properties to point to the new database
Hive Metastore Database Type
Hive Metastore Database Name
Hive Metastore Database Host
Hive Metastore Database Port
Hive Metastore Database User
Hive Metastore Database Password
If the hive schema has not been created yet, you can enable the following property
Auto Create and Upgrade Hive Metastore Database Schema:
Hope this helps.