Configure metastore database properties

In CDP Data Center, you configure Hive and Hive metastore by modifying hive-site.xml indirectly using the Cloudera Manager Safety Valve feature. Using hive set key=value on the command line 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: jdbc:postgresql://myhost/metastore

  • 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)
  1. 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 Hive Metastore Server.
  2. Navigate to the metastore host configuration in Clusters > HIVE-1 > Configuration, and search for hive-site.xml.
    HIVE-1 is the Hive metastore service.
  3. In Hive Metastore Service Advanced Configuration Snippet (Safety Valve) for hive-site.xml, click +, and add the name of the javax.jdo.option.ConnectionURL property.
  4. 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>
  5. Optionally, repeat the previous steps on all hosts in the cluster.
  6. 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>