Premium Feature — Available with the Professional and Enterprise Editions of LabKey Server.
Learn more or
contact LabKey.
This topic explains how to configure LabKey Server to retrieve and display data from a Snowflake database as an
external data source. If a Snowflake external schema is configured as "Editable", LabKey can also write to the database (e.g., insert, update, delete, import, ETL, etc.). This topic assumes you have
reviewed the general guidance here and provides specific parameters and details for this database type.
Configure the Snowflake Data Source
The new external data source must have a
unique JNDI name that you will use in naming the properties you will define. In the example on this page, we use "externalSnowflakeDataSource", which will appear to users defining external schemas as "externalSnowflake". If you have more than one external data source, give each a unique name with the DataSource suffix ("firstExternalSnowflakeDataSource", "secondExternalSnowflakeDataSource", etc.). Learn more
here.
In the <LABKEY_HOME>/config/application.properties file, add a new section with the name of the datasource and the parameters you want to define. Provide your own <SERVER:PORT>, <DB_NAME>, <DB_USERNAME>, and <DB_PASSWORD> where indicated:
context.resources.jdbc.externalSnowflakeDataSource.driverClassName=net.snowflake.client.jdbc.SnowflakeDriver
context.resources.jdbc.externalSnowflakeDataSource.url=jdbc:snowflake://<ACCOUNT_IDENTIFIER>.snowflakecomputing.com/?db=SNOWFLAKE_SAMPLE_DATA&role=ACCOUNTADMIN
context.resources.jdbc.externalSnowflakeDataSource.username=<DB_USERNAME>
context.resources.jdbc.externalSnowflakeDataSource.password=<DB_PASSWORD>
There are additional properties you can set, as shown in the
template for the main "labkeyDataSource" in the application.properties file.
driverClassName
Use this as the
driverClassName:
net.snowflake.client.jdbc.SnowflakeDriver
url
The
url property for Snowflake takes the form documented on the
Snowflake Configuring the JDBC Driver page. Below is a simple example template that should be suitable for testing; substitute the correct details:
jdbc:snowflake://<ACCOUNT_IDENTIFIER>.snowflakecomputing.com/?db=SNOWFLAKE_SAMPLE_DATA&role=ACCOUNTADMIN
The db parameter shown here, SNOWFLAKE_SAMPLE_DATA, is one of the sample databases that comes with
Snowflake trial accounts. The role value shown here, ACCOUNTADMIN, gives broad permissions. Specifying a role is important to ensure LabKey Server has appropriate permissions. Review the documentation page to determine the appropriate parameters and values for your integration.
validationQuery
Use "SELECT 1" as the validation query for Snowflake. This is the default so does not need to be provided separately for this type of external data source:
Define a New External Schema
To define a new schema from this data source see
Set Up an External Schema.
Troubleshooting
Include "--add-opens.." Flags
Note that the flags including the following must be specified at JVM startup:
--add-opens=java.base/java.nio=ALL-UNNAMED...
If these flags are missing, an exception like this will appear in the log when trying to connect to
a Snowflake database:
java.lang.RuntimeException: Failed to initialize MemoryUtil. Was Java
started with `--add-opens=java.base/java.nio=ALL-UNNAMED`? (See
https://arrow.apache.org/docs/java/install.html)
at
net.snowflake.client.jdbc.internal.apache.arrow.memory.util.MemoryUtil.<clinit>(MemoryUtil.java:146)
at
net.snowflake.client.jdbc.internal.apache.arrow.memory.ArrowBuf.getDirectBuffer(ArrowBuf.java:234)
at
net.snowflake.client.jdbc.internal.apache.arrow.memory.ArrowBuf.nioBuffer(ArrowBuf.java:229)
at
net.snowflake.client.jdbc.internal.apache.arrow.vector.ipc.ReadChannel.readFully(ReadChannel.java:87)
at
net.snowflake.client.jdbc.internal.apache.arrow.vector.ipc.message.MessageSerializer.readMessageBody(MessageSerializer.java:728)
VARCHAR(16777216)
LabKey queries and fills in the "values" panel of the filter dialog for string columns that are less than 300 characters. VARCHAR(16777216) is a frequent column type in Snowflake example data; columns of that type will not result in a values panel.
Related Topics
