Configuring metastore database properties

In Cloudera Base on premises, you configure Hive and Hive metastore by modifying hive-site.xml indirectly using the Cloudera ManagerSafety Valve feature. A step-by-step procedure shows you how to set a number of property names and values in lieu of using hive set key=value on the command line, which is not supported.

This task assumes the database is running on myhost, the user account is hiveuser, and the password is mypassword. Substitute the following connection URLs and driver names depending on the your database type.

MySQL connection URL: jdbc:mysql://myhost/metastore
MySQL driver name: com.mysql.jdbc.Driver
Postgres connection URL: jdbc:postgresql://myhost/metastore
Postgres driver name: org.postgresql.Driver
Oracle connection URL: jdbc:oracle:thin:@//myhost/xe
Oracle driver name: oracle.jdbc.OracleDriver

The following components are running:
- HiveServer
- Hive Metastore
- A database for the metastore, such as the default MySQL Server
- Hive clients
Minimum Required Role: Configurator (also provided by Cluster Administrator, Full Administrator)

Find the fully qualified domain name or IP address of Hive metastore by navigating to Cloudera Manager > Hosts > Role(s) and looking through the list of roles to find Hive Metastore Server.
Navigate to the metastore host configuration in Clusters > Hive Metastore > Configuration, and search forjavax.jdo.option.ConnectionURL.
In Value, specify the database connection string using the following syntax:<connection protocol>://<metastore host>/<metastore database>?createDatabaseIfNotExist=true
For example:
In View as XML, the XML configuration snippet appears.
```
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://10.65.13.98/mydb?createDatabaseIfNotExist=true</value>
<description>Database connection string</description>
</property>
```
Optionally, repeat the previous steps on all hosts in the cluster.

In the same manner, specify other required connection properties on the metastore host (required), or on all hosts (optional) as shown in the following example.

<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>(your driver name)</value>
</property>

<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>hive</value>
</property>
                        
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>mypassword</value>
</property>
                        
<property>
<name>datanucleus.autoCreateSchema</name>
<value>false</value>
</property>
                        
<property>
<name>datanucleus.fixedDatastore</name>
<value>true</value>
</property>
                        
<property>
<name>datanucleus.autoStartMechanism</name> 
<value>SchemaTable</value>
</property> 
                                              
<property>
<name>hive.metastore.schema.verification</name>
<value>true</value>
</property>