topic Re: Cloud Snowflake DB Connect Integration in All Apps and Add-ons

Cloud Snowflake DB Connect Integration

ekcsoc — Wed, 30 Sep 2020 02:05:37 GMT

Hi,

Could you please help here I tried to integrate the Snowflake with db connect.

I am using following version of JRE: /usr/java/jdk1.8.0_131/jre

I have placed snowflake at 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

I have put below stanza in /opt/splunk/etc/apps/splunk_app_db_connect/local/ db_connection_types.conf

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

Now when I create a connection and try to save I get following error:

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

Re: Cloud Snowflake DB Connect Integration

ptang_splunk — Wed, 30 Sep 2020 02:46:32 GMT

Hi there,

Please try the following steps:

Make sure you download the latest Snowflake JDBC Driver jar version and NOT javadoc.jar : https://docs.snowflake.net/manuals/user-guide/jdbc-download.html#downloading-the-driver OR https://search.maven.org/classic/#search%7Cga%7C1%7Csnowflake-jdbc
Drop the .jar file (driver) under $SPLUNK_HOME/etc/apps/splunk_app_db_connect/drivers
Create or update db_connection_types.conf under $SPLUNK_HOME/etc/apps/splunk_app_db_connect/local with the following: [snowflake] displayName = Snowflake serviceClass = com.splunk.dbx2.DefaultDBX2JDBC jdbcDriverClass = net.snowflake.client.jdbc.SnowflakeDriver jdbcUrlFormat = jdbc:snowflake://<host>:<port>/?db=<database> ui_default_catalog = $database$ port = 443
Restart Splunk
Now, the Snowflake Driver should be available via the UI
Create a new Identity with your Snowflake credentials
Create a new Database Connection for Snowflake with the following:
Snowflake Identity
Snowflake Connection Type
Select the Timezone set/used by your Snowflake Database/Environment
Check the "Edit JDBC URL" checkbox, this will have to be manually provided as per https://docs.snowflake.net/manuals/user-guide/jdbc-configure.html or something similar to jdbc:snowflake://<account_name>.snowflakecomputing.com/?<connection_params> OR jdbc:snowflake://<account_name>.snowflakecomputing.com/?db=<database>&warehouse=<warehouse>&schema=<schema> Replace all the <parameters> to reflect your environment.
Make sure that Read Only is unchecked as this parameter is not available on Snowflake
Fetch size can be left alone

Your Database Connection should look as follows: Screenshot

The JDBC URL can be adapted as per your environment as long as it matches their JDBC Driver Connection String: https://docs.snowflake.net/manuals/user-guide/jdbc-configure.html#jdbc-driver-connection-string!

Re: Cloud Snowflake DB Connect Integration

bwu_hcf — Wed, 30 Sep 2020 05:33:06 GMT

Hi @ptang_splunk ,

Thanks for providing detailed steps above! I had configured our DB Connect inputs for Snowflake as described by your post as well as following the doc below from Snowflake:

https://community.snowflake.com/s/article/Integrating-Snowflake-and-Splunk-with-DBConnect

I am able to telnet to port 443 on the host as specified in the "JDBC URL" from the Splunk HF that the DB connection is initiated. However, I still encountered the below errors from internal logs (splunk_app_db_connect_server.log) after restarting splunkd service:

2020-05-30 00:03:19.220 +1000 [QuartzScheduler_Worker-10] WARN com.zaxxer.hikari.pool.PoolBase - unnamed_pool_1112188830_jdbc_snowflake//XXX.XX-XXXXXXXXX-X.snowflakecomputing.com443/?warehouseSPLUNK_WH&dbSNOWFLAKE - Failed to register management beans.
javax.management.RuntimeOperationsException: null
at com.sun.jmx.mbeanserver.Repository.addMBean(Unknown Source)
at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.registerWithRepository(Unknown Source)
at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.registerDynamicMBean(Unknown Source)
at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.registerObject(Unknown Source)
at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.registerMBean(Unknown Source)
at com.sun.jmx.mbeanserver.JmxMBeanServer.registerMBean(Unknown Source)
at com.zaxxer.hikari.pool.PoolBase.registerMBeans(PoolBase.java:258)
at com.zaxxer.hikari.pool.HikariPool.(HikariPool.java:116)
at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:94)
at com.splunk.dbx.connector.ConnectorFactory.getConnectionFromPool(ConnectorFactory.java:201)
at com.splunk.dbx.connector.ConnectorFactory.getConnection(ConnectorFactory.java:189)
at com.splunk.dbx.connector.ConnectorFactory.create(ConnectorFactory.java:174)
at com.splunk.dbx.server.dbinput.recordreader.DbInputRecordReader.executeQuery(DbInputRecordReader.java:60)
at com.splunk.dbx.server.dbinput.recordreader.DbInputRecordReader.open(DbInputRecordReader.java:52)
at org.easybatch.core.job.BatchJob.openReader(BatchJob.java:117)
at org.easybatch.core.job.BatchJob.call(BatchJob.java:74)
at org.easybatch.extensions.quartz.Job.execute(Job.java:59)
at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)
Caused by: java.lang.IllegalArgumentException: Repository: cannot add mbean for pattern name com.zaxxer.hikari:type=PoolConfig (unnamed_pool_1112188830_jdbcsnowflake//XXX.XX-XXXXXXXXX-X.snowflakecomputing.com443/?warehouseSPLUNK_WH&db_SNOWFLAKE)
... 19 common frames omitted

2020-05-30 00:03:21.109 +1000 [QuartzScheduler_Worker-10] ERROR org.easybatch.core.job.BatchJob - Unable to open record reader
java.sql.SQLFeatureNotSupportedException: null
at net.snowflake.client.jdbc.SnowflakeConnectionV1.setReadOnly(SnowflakeConnectionV1.java:518)
at com.zaxxer.hikari.pool.ProxyConnection.setReadOnly(ProxyConnection.java:388)
at com.zaxxer.hikari.pool.HikariProxyConnection.setReadOnly(HikariProxyConnection.java)
at com.splunk.dbx.connector.ConnectorFactory.getConnection(ConnectorFactory.java:190)
at com.splunk.dbx.connector.ConnectorFactory.create(ConnectorFactory.java:174)
at com.splunk.dbx.server.dbinput.recordreader.DbInputRecordReader.executeQuery(DbInputRecordReader.java:60)
at com.splunk.dbx.server.dbinput.recordreader.DbInputRecordReader.open(DbInputRecordReader.java:52)
at org.easybatch.core.job.BatchJob.openReader(BatchJob.java:117)
at org.easybatch.core.job.BatchJob.call(BatchJob.java:74)
at org.easybatch.extensions.quartz.Job.execute(Job.java:59)
at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)

2020-05-30 00:03:21.109 +1000 [QuartzScheduler_Worker-10] INFO org.easybatch.core.job.BatchJob - Job 'SNOWFLAKE_ACCOUNT_QUERY_TEST' finished with status: FAILED

As the above error trace did not give out much information on why the connection failed apart from the 2 messages below:

"Failed to register management beans"
"Unable to open record reader"

Re: Cloud Snowflake DB Connect Integration

dmaislin_splunk — Fri, 29 May 2020 20:07:01 GMT

Are you using telnet to test from your client PC or from where DBX is installed? Just curious?

Re: Cloud Snowflake DB Connect Integration

bwu_hcf — Sat, 30 May 2020 10:34:14 GMT

Telnet testing is from the Splunk HF where DBX is installed.

Re: Cloud Snowflake DB Connect Integration

dmaislin_splunk — Sat, 30 May 2020 10:40:52 GMT

What is the version of the JDBC driver? Please provide the link to whichever one you downloaded. Thank you.

Re: Cloud Snowflake DB Connect Integration

bwu_hcf — Sat, 30 May 2020 10:45:00 GMT

I am currently using snowflake-jdbc-3.12.4.jar driver downloaded from the site below:

https://repo1.maven.org/maven2/net/snowflake/snowflake-jdbc/

Re: Cloud Snowflake DB Connect Integration

dmaislin_splunk — Sat, 30 May 2020 10:57:32 GMT

I see you have a support case open on this correct? Did you follow the guidance they suggested regarding the link and the timeout behavior?

Re: Cloud Snowflake DB Connect Integration

bwu_hcf — Sat, 30 May 2020 11:18:12 GMT

Yes, I had captured tcpdump from Wireshark and will attach the output to the case for further analysis. I have also increased the timeout value to "maxWaitMillis=1800000" to test the connection again, but still no luck. Same error messages as the above internal logs. I am upgrading the jdbc driver to the latest to test again.

Re: Cloud Snowflake DB Connect Integration

dmaislin_splunk — Sat, 30 May 2020 11:21:39 GMT

I will assume you followed Snowflake’s post on the setup? Have you also reached out to their side for assistance?

https://support.snowflake.net/s/article/Integrating-Snowflake-and-Splunk-with-DBConnect

Re: Cloud Snowflake DB Connect Integration

bwu_hcf — Sat, 30 May 2020 11:24:45 GMT

Thanks for reminding me! That is actually my next step as I am suspecting they could be blocking this type of connection from their end by default.

Re: Cloud Snowflake DB Connect Integration

phoran — Mon, 01 Jun 2020 13:35:59 GMT

Can you post your connection string? (Leave out the sensitive parts, obviously...)
What is your Snowflake account? Remember that the Snowflake "account" is everything preceding the ".snowflakecomputing.com" in your account URL. There should be both an account name and a region identifier - like "mySnowflakeAccount.us-east-1." for AWS East. Only Snowflake accounts based in the AWS US-West region will not have a region identifier included.

-Paul Horan-
Snowflake

Re: Cloud Snowflake DB Connect Integration

phoran — Mon, 01 Jun 2020 13:37:43 GMT

Snowflake will only "block" connection attempts with invalid credentials, or coming from network IP addresses outside your "whitelisted" network policy (should one have been created).
This is more likely an invalid connection string.

-Paul-

Re: Cloud Snowflake DB Connect Integration

bwu_hcf — Mon, 01 Jun 2020 13:45:43 GMT

Thanks, @phoran ! When I tried to create such connection via Splunk DB Connect GUI, it did show "This is an invalid connection" when I was saving the settings. If I ask our Snowflake counterpart to add our IP to their whitelist, this should resolve the issue. Am I correct?

Re: Cloud Snowflake DB Connect Integration

phoran — Mon, 01 Jun 2020 14:21:31 GMT

If you're trying to connect from outside the whitelisted IP range, the error message from Snowflake is "IP XX.XX.XX.XX is not allowed to access Snowflake. Contact your local security administrator."

The error you're seeing sounds more like a malformed connectionstring.

-Paul-

Re: Cloud Snowflake DB Connect Integration

bwu_hcf — Mon, 01 Jun 2020 21:31:49 GMT

The JDBC URL is below:

jdbc:snowflake://xxx.ap-southeast-2.snowflakecomputing.com:443/?warehouse=XXXXX_WH&db=XXXXXXXXX

Re: Cloud Snowflake DB Connect Integration

thambisetty — Fri, 26 Apr 2024 18:17:10 GMT

I am aware there is an accepted answer that helped me, but the connection was not established with the message 'need further details.' However, I need to do some extra configuration to establish the connection.

I followed accepted answer for jdbc driver (downloaded snowflake-jdbc-3.15.1.jar ) and to add db_connection_types.conf (don't copy and paste it from the accepted answer. see below db_connection_types.conf)
However, I ran into a problem with error message : could not initialize class net.snowflake.client.jdbc.internal.apache.arrow.memory.util.MemoryUtil

Followed below link to fix the issue :
https://community.snowflake.com/s/article/JDBC-OutOfDirectMemoryError

I added the first two options from the above solution (you can see below in case the above link is broken or removed) to the JVM Options in connection settings with a space delimiter. It didn't work. However, the connection got established with the 3rd solution. (See db_connections.conf also to understand how that parameter is used.)

1.) Increase the maximum heap size, which in return will increase the maximum direct memory size. You will need to refer to the application's documentation for instructions on how to configure this value because it is application-specific. If you were starting the application using the java command then any of the following JVM arguments will set the maximum heap size to 1 GB: -Xmx1048576k -Xmx1024m -Xmx1g 2.) Explicitly increase the maximum direct memory size. E.g., the following JVM sets the value to 1 GB: -XX:MaxDirectMemorySize=1g 3.) If for any reason you do not have any control over the amount of memory you can allocate to your JVM (e.g., you are limited by the size of the container you're running in and it cannot be configured) then change the query result set from ARROW to JSON. You can pass this setting as a connection parameter using your JDBC driver: JDBC_QUERY_RESULT_FORMAT=JSON

Final confs are as below:

local/db_connection_types.conf [snowflake] displayName = Snowflake serviceClass = com.splunk.dbx2.DefaultDBX2JDBC jdbcDriverClass = net.snowflake.client.jdbc.SnowflakeDriver jdbcUrlFormat = jdbc‌‌//:/?db= ui_default_catalog = $database$ port = 443 local/db_connections.conf [Snowflake_DB] connection_properties = {"JDBC_QUERY_RESULT_FORMAT":"JSON"} connection_type = snowflake customizedJdbcUrl = jdbc‌‌//.snowflakecomputing.com:443/?user=&db=snowflake&warehouse=&schema=public database = snowflake disabled = 0 host = .snowflakecomputing.com identity = SnowflakeUser jdbcUseSSL = false localTimezoneConversionEnabled = false port = 443 readonly = false timezone = Etc/UTC

Re: Cloud Snowflake DB Connect Integration

thambisetty — Fri, 26 Apr 2024 18:32:21 GMT

I am aware there is an accepted answer that helped me, but the connection was not established with the message 'need further details.' However, I need to do some extra configuration to establish the connection.

I followed accepted answer for jdbc driver(downloaded snowflake-jdbc-3.15.1.jar ) and to configure db_connection_types.conf ( don't copy and paste it from accpeted answer. see below db_connection_types.conf

Followed below link to fix the issue: could not initialize class net.snowflake.client.jdbc.internal.apache.arrow.memory.util.MemoryUtil
https://community.snowflake.com/s/article/JDBC-OutOfDirectMemoryError

I added the first two options from the above solution (you can see below in case the above link is broken or removed) to the JVM Options in connection settings with a space delimiter. It didn't work. However, the connection got established with the third solution. (See db_connections.conf also to understand how that parameter is used.)

Final db_connections.conf and db_connection_types.conf

local/db_connection_types.conf [snowflake] displayName = Snowflake serviceClass = com.splunk.dbx2.DefaultDBX2JDBC jdbcDriverClass = net.snowflake.client.jdbc.SnowflakeDriver jdbcUrlFormat = jdbc:snowflake://<host>:<port>/?db=<database> ui_default_catalog = $database$ port = 443 local/db_connections.conf [Snowflake_DB] connection_properties = {"JDBC_QUERY_RESULT_FORMAT":"JSON"} connection_type = snowflake customizedJdbcUrl = jdbc:snowflake://<host>.snowflakecomputing.com:443/?user=<user_name>&db=snowflake&warehouse=<warehouse_value>&schema=public database = snowflake disabled = 0 host = <host>.snowflakecomputing.com identity = SnowflakeUser jdbcUseSSL = false localTimezoneConversionEnabled = false port = 443 readonly = false timezone = Etc/UTC

Re: Cloud Snowflake DB Connect Integration

thambisetty — Fri, 26 Apr 2024 18:34:09 GMT

I am aware there is an accepted answer that helped me, but the connection was not established with the message 'need further details.' However, I need to do some extra configuration to establish the connection.

I followed accepted answer for jdbc driver(downloaded snowflake-jdbc-3.15.1.jar ) and to configure db_connection_types.conf ( don't copy and paste it from accpeted answer. see below db_connection_types.conf

Followed below link to fix the issue: could not initialize class net.snowflake.client.jdbc.internal.apache.arrow.memory.util.MemoryUtil
https://community.snowflake.com/s/article/JDBC-OutOfDirectMemoryError

I added the first two options from the above solution (you can see below in case the above link is broken or removed) to the JVM Options in connection settings with a space delimiter. It didn't work. However, the connection got established with the third solution. (See db_connections.conf also to understand how that parameter is used.)

Final db_connections.conf and db_connection_types.conf