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

MaxU — Fri, 16 Sep 2022 12:31:33 GMT

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!

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

MaxU — Wed, 22 Nov 2017 10:39:51 GMT

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"

question Re: How to use JDBC connection string (Oracle TNSNAMES) for connecting to Hive Metastore DB (Oracle) in Archives of Support Questions (Read Only)

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

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