Connect to Snowflake

Connection Modes: Direct

Connection

To connect to Snowflake, select Add Datasource on the datasources overview page to open the Create Datasource dialog and select Snowflake as the database type.

The Name is a required human-friendly identifier or description of the datasource in Cluvio. Datasource names need not be unique but we recommend to give each datasource a unique and meaningful name for ease of identification, especially if your organization uses multiple datasources.

The Use field allows you to choose between specifying the Snowflake Account Identifier (recommended) or the Account Locator & Region.

Account Locator

Account Locators are only supported for backward-compatibility. If you are using an Account Locator, the Cloud Region field must be filled with the Cloud Region ID of your Snowflake account, which depending on the region may need to be followed by a platform identifier: .aws, .gcp or .azure. See Account Locator Formats by Cloud Platform and Region. For example, a valid value for the Cloud Region in Cluvio is us-central1.gcp.

The Warehouse and Database Name must be set to the name of the Snowflake Warehouse and Snowflake Database within that warehouse that you want to connect to. Cluvio will fetch schema information for this database in order to populate the almanach in the report editor.

The Username must identify a Snowflake service user that has access to the specified warehouse and database. The user must be configured with the Public Key shown by Cluvio. Cluvio keeps the corresponding private key stored securely and only uses it to connect to Snowflake to execute queries. See Assign User Public Key on how to assign the public key to the database user.

When you have entered all the required information, select Test Connection to check that Cluvio can connect to your database. The connection test will report errors if the connection fails. See Troubleshooting for common problems.

Assign User Public Key

When you create a new Snowflake datasource or switch an existing Snowflake datasource to key-pair authentication, Cluvio shows a Public Key. Copy the public key and configure it on the Snowflake user used to connect to Cluvio. The Snowflake user must be of type SERVICE. For example:

CREATE USER cluvio
TYPE = SERVICE,
RSA_PUBLIC_KEY = '<public-key>'

Replace the <public-key> with the unmodified value copied from Cluvio, (including newlines).

Configuration

The Configuration tab of the datasource dialog shows settings that affect the datasource's behavior.

The Data Time Zone defaults to UTC and is the time zone that Cluvio assumes for any timestamps returned from queries that do not contain time zone information. See Data Time Zone for details.

The Maximum number of concurrent query executions control the maximum concurrency that Cluvio allows for the datasource. This setting can be used to control the maximum load on your database. The default is 20.

The toggle Update schema nightly controls whether Cluvio queries your database schema nightly to ensure that the almanach in the report editor has up-to-date information on your database schema. Together with fetching schema information, Cluvio also tries to retrieve approximate row counts in each table. If you disable nightly schema updates, the almanach is only updated when you manually trigger a schema refresh on the datasource from the Cluvio datasources overview. Nightly schema updates are enabled by default.

The toggle Update exact row counts is only available when Update schema nightly is enabled. This setting controls whether Cluvio will determine exact row counts for every table in your database schema. Row counts are shown in the report editor almanach. Determining exact row counts usually involves issuing a COUNT(*) query on each table, which may cause undesirable load on your database. You can disable this setting to avoid these nightly queries. When disabled, the tables in the report editor almanach may not show row count information if the database does not provide approximate row counts.

Troubleshooting

Authorization Errors

If the connection test succeeds but Cluvio was unable to fetch the Almanach (schema and table information) and you are also unable to query any of your database tables with errors that look like

Object <table-name> does not exist or not authorized.

the reason is a lack of permissions on the database user configured on your Snowflake datasource. The database user must have a DEFAULT_ROLE assigned that grants full read access to your Snowflake warehouse. If your database user has no default role assigned, the Cluvio connection will use the PUBLIC role, which usually does not have the required authorization. See Snowflake Access Control for details.

Similarly, the error

No active warehouse selected

usually results from a lack of permissions of the database user to access the warehouse configured on your Cluvio datasource.