Going Off-Piste: ODI 12c Standalone Agent Connection to Snowflake
Recently, a Snowflake customer was having troubles using their Oracle Data Integrator (ODI) 12c standalone agent to connect to Snowflake. Strangely enough, the connection worked just fine using the ODI Studio local agent. I spent a good part of my career working with ODI, so I was interested to dive in and find out what was happening here.
If you’re not familiar with Oracle Data Integrator, it’s Oracle’s data integration and ETL tool that has been around for ~15 years. Connections to data sources and targets are made via JDBC drivers. Once the connection is setup and working, ODI can communicate with the database in order to reverse engineer tables into metadata, develop mappings, and execute mappings. These ETL jobs, comprised of mappings, are executed via an ODI agent. The standalone agent, which we’re working with in this post, is installed on the local server as a lightweight Java application. ODI Studio also has the ability to execute jobs, but simply to test connectivity and individual mappings rather than full production pipelines.
To begin setting up a connection from ODI to Snowflake (after ODI is installed and configured), download the Snowflake JDBC driver. Following the documentation for ODI, place the driver in the ~/.odi/oracledi/userlib directory for ODI Studio and the $ODI_HOME/odi/agent/lib directory for use by the agent. Once placed, shutdown and restart the components with which you plan to use the drivers.
Creating a connection to Snowflake in Oracle Data Integrator is quite simple from there. One of the great features of ODI is that you can customize and create nearly any object beyond what currently exists — including the Technology object. The Technology defines the type of data source that the connection will be created under. In the case of Snowflake, I simply duplicate the Oracle Technology and rename it Snowflake. Bam! Snowflake Technology. The reason I can do this with confidence is because the SQL syntax used by Snowflake is very similar to that of Oracle. Note: I haven’t fully tested all of the settings in this technology with the Snowflake database. Under the Snowflake Technology, I create what’s called a Data Server — the object in ODI that stores the connection information for a given data source.
Give it a name and enter the username/password on the Definition tab. Under the JDBC tab, I need to add the specific JDBC Driver for Snowflake — net.snowflake.client.jdbc.SnowflakeDriver — and the JDBC URL for the Snowflake account and database to which I’m connecting:
jdbc:snowflake://aws_cas2.snowflakecomputing.com/?warehouse=MRAINEYWH&db=MRAINEY&schema=DONORSCHOOSEI’ve added the virtual warehouse, database, and schema names to the connection string. Now, to test the connection simply click the “Test Connection” button on the top left side of the Data Server window.
The dialog that pops up allows us to choose which agent to test the connection against. When I chose the Local (No Agent) — basically using ODI Studio — the connection is successful! However, if I choose the standalone agent, named OracleDIAgent1, the connection fails.
In the logs for the agent we can see the error message:
javax.net.ssl.SSLHandshakeException: No appropriate protocol (protocol is disabled or cipher suites are inappropriate)This is a bit strange, because the agent is not setup for SSL. I went searching for reasons this might be occurring and started down the path of enabling SSL for the agent, when all of a sudden the customer resolved the issue on their own (always give credit where credit is due!). What they found is that in the file instance.sh that is deep within the agent directories at $DOMAIN_HOME/config/fmwconfig/components/ODI/OracleDIAgent1/bin, is a Java option that is being set specifically for SSL/TLS communications. What is not apparent is why this property is being set even if SSL is not in use. So, the customer ended up “unsetting” the variable, restarting the agent, and viola — the connection worked!
Now we can continue on with reverse engineering tables from Snowflake into ODI metadata, building ETL mappings, etc. For completeness, here’s a look at the properties in question from within the instance.sh file:
if [ ! -z $IS_AGENT_SCRIPT ] ; then
ODI_SSL_PROPERTIES="-Djavax.net.ssl.trustStore=${WL_HOME}/server/lib/DemoTrust.jks -Djavax.net.ssl.keyStore=${DOMAIN_HOME}/security/DemoIdentity.jks"
else
ODI_SSL_PROPERTIES="-Djavax.net.ssl.trustStore=${WL_HOME}/server/lib/DemoTrust.jks -Djavax.net.ssl.trustStorePassword=DemoTrustKeyStorePassPhrase"
fi# Added by the customer to unset the property
ODI_SSL_PROPERTIES=""ODI_INSTANCE_JAVA_OPTIONS="$ODI_ADDITIONAL_JAVA_OPTIONS $ODI_SSL_PROPERTIES"
I’m definitely happy it works now — but even more curious as to the why. Why did the ODI Studio connection work just fine? Maybe because it doesn’t have any SSL settings? Why does ODI connect just fine to other databases without SSL, such as Oracle, and not have these issues? I’ll keep digging for answers and post an update if I learn more. But for now, if your agent is not connecting to Snowflake, this workaround should help get you moving.
If you are having troubles connecting 3rd party tools to Snowflake, or simply have a question about the data warehouse built for the cloud, join the Snowflake Lodge Community and get your questions answered by customers and Snowflake experts alike.
Going Off-Piste is a series of articles intended to take Snowflake users a bit off the beaten path, helping work through challenging questions and providing tips for how to best use Snowflake.
