Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

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

avatar
New Contributor

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!

1 ACCEPTED SOLUTION

avatar
New Contributor

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"

View solution in original post

1 REPLY 1

avatar
New Contributor

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"