Integrating Snowflake Data Warehouse with Splunk DB Connect

Applicability

Description

Snowflake has been placed at the following location:
 

/opt/splunk/etc/apps/splunk_app_db_connect/drivers/snowflake-jdbc-3.9.1-javadoc.jar
/opt/splunk/etc/apps/splunk_app_db_connect/drivers/snowflake-libs/snowflake-jdbc-3.9.1-javadoc.jar
/opt/splunk/etc/apps/splunk_app_db_connect/jars/snowflake-jdbc-3.9.1-javadoc.jar

The following version of JRE is in use: 

 /usr/java/jdk1.8.0_131/jre

The stanza in /opt/splunk/etc/apps/splunk_app_db_connect/local/ db_connection_types.conf is:
 

[snowflake]
displayName = Snowflake
serviceClass = com.splunk.dbx2.DefaultDBX2JDBC
jdbcUrlFormat = jdbc:snowflake://:/
jdbcDriverClass = com.snowflake.client.jdbc.SnowflakeDriver

A connection is created and when an attempt to save it is made the following error appears:
 

Database connection snowflake is invalid
No suitable driver found for jdbc:snowflake://"":443/

​​​​​​​

Investigation

If Snowflake is the actual database then it is not something which Splunk supports, however any database that has a JDBC driver should work with Splunk DB Connect.

The correct notation for enabling Snowflake should be followed in order for it to work.


Splunk Documentation
Install database drivers
Supported_databases_matrix


Install drivers for other databases

It is possible to add custom support to Splunk DB Connect for any database that has a JDBC driver, even if it doesn't appear in the supported database matrix.

Connecting to a database that is not listed in the supported database matrix is not supported by Splunk Support.

At a minimum, Splunk DB Connect supports querying custom database connections. For some custom database connections, certain query-related features may not work."

It was noted that the file db_connection_types.conf had been modified.
 

[snowflake]
displayName = Snowflake
serviceClass = com.splunk.dbx2.DefaultDBX2JDBC
jdbcUrlFormat = jdbc:snowflake://:/
jdbcDriverClass = com.snowflake.client.jdbc.SnowflakeDriver

The standard definitions of jdbcUrlFormat show fields such as 'host', 'port', and 'database'  missing from the jdbcUrlFormat parameter above.
 

etc/apps/splunk_app_db_connect/default/db_connection_types.conf [oracle]
etc/apps/splunk_app_db_connect/default/db_connection_types.conf jdbcDriverClass = oracle.jdbc.OracleDriver
etc/apps/splunk_app_db_connect/default/db_connection_types.conf jdbcUrlFormat = jdbc:oracle:thin:@<host>:<port>:<database>

etc/apps/splunk_app_db_connect/default/db_connection_types.conf [mssql]
etc/apps/splunk_app_db_connect/default/db_connection_types.conf jdbcDriverClass = net.sourceforge.jtds.jdbc.Driver
etc/apps/splunk_app_db_connect/default/db_connection_types.conf jdbcUrlFormat = jdbc:jtds:sqlserver://<host>:<port>/<database>;useCursors=true

Rather than:

jdbcUrlFormat = jdbc:snowflake://:/

Review the URL format:

e.g. it could be something like:

jdbc:showflake://<host>.snowflakecomputing.com/
jdbc:showflake://<account>.snowflakecomputing.com:<port>/

with connection parameters such as 'user', 'role', or 'schema' as specified on the vendors' website.


Snowflake documentation

Manuals: user-guide jdbc-configure.html jdbc-driver-connection-string

jdbc:snowflake://<account_name>.snowflakecomputing.com/?<connection_params>
"Use net.snowflake.client.jdbc.SnowflakeDriver as the driver class in the JDBC application.

The previous driver class, com.snowflake.client.jdbc.SnowflakeDriver, is still supported but
is deprecated (i.e. it will be removed in a future release, TBD)."


 

Resolution

The correct format (jdbcUrlFormat) to specify for the Snowflake stanza in db_connection_types.conf is:

jdbc:snowflake://<account_name>.snowflakecomputing.com/?<connection_params>