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.
Note:The name of your Snowflake database must be all uppercase in order to be able to access the schemas through LabKey, i.e. use "MY_SNOWFLAKE_DB" instead of "my_snowflake_db". Using a lower or mixed case name will create the external data source connection, but it will not appear to have any schemas available in it.
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 for your chosen authentication method.
Note: Snowflake is deprecating username and password authentication and moving toward key-based authentication. Private key authentication is the recommended approach. See
Snowflake's MFA rollout documentation for details.
There are additional properties you can set, as shown in the
template for the main "labkeyDataSource" in the application.properties file.
Authenticate with a Private Key (Recommended)
To authenticate using a private key, pass the key as a `private_key_base64` parameter in the
url property. Set the
password property to any non-empty string — its value is not used when key-based authentication is active.
Provide your own values for SNOWFLAKE_URL, SNOWFLAKE_DATABASE_NAME, SNOWFLAKE_WAREHOUSE_NAME, SNOWFLAKE_ROLE, SNOWFLAKE_USERNAME, and SNOWFLAKE_PRIVATE_KEY_BASE64:
context.resources.jdbc.externalSnowflakeDataSource.type=javax.sql.DataSource
context.resources.jdbc.externalSnowflakeDataSource.driverClassName=net.snowflake.client.jdbc.SnowflakeDriver
context.resources.jdbc.externalSnowflakeDataSource.url=jdbc:snowflake://SNOWFLAKE_URL/?db=SNOWFLAKE_DATABASE_NAME&warehouse=SNOWFLAKE_WAREHOUSE_NAME&role=SNOWFLAKE_ROLE&private_key_base64=SNOWFLAKE_PRIVATE_KEY_BASE64
context.resources.jdbc.externalSnowflakeDataSource.username=SNOWFLAKE_USERNAME
context.resources.jdbc.externalSnowflakeDataSource.password=SNOWFLAKE_USERNAME
context.resources.jdbc.externalSnowflakeDataSource.accessToUnderlyingConnectionAllowed=true
context.resources.jdbc.externalSnowflakeDataSource.validationQuery=SELECT 1
The private_key_base64 value is a large alphanumeric string, typically over 2000 characters. Refer to your Snowflake account settings or administrator for the correct value. See the
Snowflake JDBC driver documentation for additional parameters.
Authenticate with a Username and Password
If your Snowflake account still permits password-based authentication, you can use the simpler configuration below. Provide your own values for ACCOUNT_IDENTIFIER, DB_NAME, DB_USERNAME, and DB_PASSWORD:
context.resources.jdbc.externalSnowflakeDataSource.driverClassName=net.snowflake.client.jdbc.SnowflakeDriver
context.resources.jdbc.externalSnowflakeDataSource.url=jdbc:snowflake://ACCOUNT_IDENTIFIER.snowflakecomputing.com/?db=DB_NAME&role=ACCOUNTADMIN
context.resources.jdbc.externalSnowflakeDataSource.username=DB_USERNAME
context.resources.jdbc.externalSnowflakeDataSource.password=DB_PASSWORD
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
Database Name in Upper Case
The name of your Snowflake database should be all upper case, and specified with an all uppercase string in the application.properties configuration and URL. If you use a lowercase or mixed case name, you will be able to see an external connection to the data source, but it will not appear to contain any schemas.
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
