How to configure Database Connection to Snowflake?

Question

I have a Snowflake database that I need to connect QIE to. How can I set up this database connection.

Answer 1

To connect to Snowflake from QIE first download the JDBC driver and other required dependencies as needed. For versions prior to 3.11.0 you should just need this jar:

snowflake-jdbc-#.#.#.jar

See details to download here:

https://docs.snowflake.com/en/developer-guide/jdbc/jdbc-download

or

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

Version 3.11.0 or later will also require these Apache Arrow dependencies:

arrow-vector-x.x.x.jar

arrow-memory-netty-x.x.x.jar

See details to download Java Packages here:

https://arrow.apache.org/install/

Jars are also available here:

https://repo.maven.apache.org/maven2/org/apache/arrow/arrow-vector/

and

https://repo.maven.apache.org/maven2/org/apache/arrow/arrow-memory-netty/

It will also need the following added to the "Startup" tabs "Arguments:" in Qie Service Manager for version 3.11.0 or later.

--add-opens=java.base/java.nio=org.apache.arrow.memory.core,ALL-UNNAMED

Alternatively you can configure SnowFlake to return data using the JSON format instead of using Apache Arrow. See details in these links for that option:

https://community.snowflake.com/s/article/Getting-java-lang-NoClassDefFoundError-for-class-RootAllocator

https://community.snowflake.com/s/article/JDBC-Driver-Compatibility-Issue-With-JDK-16-and-Later

https://community.snowflake.com/s/article/SAP-BW-Java-lang-NoClassDefFoundError-for-Apache-arrow

After downloading jar files, follow these instructions to make them available in QIE:

How do you install a .jar for ms sql on QIE?

What are External Libraries or JAR files

You can now configure a Database Connection in QIE.

Driver Class:

net.snowflake.client.jdbc.SnowflakeDriver

Connection URL:

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

Connection URL example:

jdbc:snowflake://YourAccountIdentifier.snowflakecomputing.com/?user=YourUser&password=YourPassword&warehouse=YourWarehouse&db=YourDatabase&schema=YourSchema&role=PUBLIC

See this for more details on the Snowflake connection URL:

https://docs.snowflake.com/en/developer-guide/jdbc/jdbc-configure

Also see:

https://arrow.apache.org/

https://arrow.apache.org/install/

https://arrow.apache.org/docs/java/install.html#id6