Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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

Solved Go to solution

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

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

Accepted Solutions

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

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"

1 REPLY 1

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

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"