How to use JDBC connection string (Oracle TNSNAMES) for connecting to Hive Metastore DB (Oracle)

[#1]

Hello Cloudera Community,

We have a primary and a standby Oracle clusters where our Hive Metastore DB is located.

I'd like to be able to specify a FAILOVER JDBC connection string which might access both primary and standby Oracle cluster.

I don't want to change manually values for "Hive Metastore Database Host" and "Hive Metastore Database Name" everytime when a primary and a standby Oracle DB's are switched over (their roles will be swapped).

How can i specify a JDBC connection string for the "Hive Metastore Database"?

Our CDH version: 5.11.0

Thank you!

[#2 · Accepted solution]

I got a very helpful answer from the Cloudera Support and it did the trick:

Look for "Hive Metastore Server Advanced Configuration Snippet (Safety Valve) for hive-site.xml" in the Hive configuration, press (+) [Plus button] and specify the following:

in the "Name" field: javax.jdo.option.ConnectionURL

in the "Value" field: jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=off)(FAILOVER=on)(CONNECT_TIMEOUT=3)(RETRY_COUNT=3)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle-server1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=oracle-server2)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ora_service_name.ora_domain_name)))

NOTE: You will have to change the connection string correspondingly - I just showed an example of the failover-able (primary + standby DBs) Oracle TNS connection string.

The same trick can be used for "Sentry", but the corresponding parameter name is: "sentry.store.jdbc.url"