Snowflake
Snowflake (opens in a new tab) is a popular cloud-based data platform.
Prerequisites
In order to connect Cube to Snowflake, you need to grant certain permissions to the Snowflake role
used by Cube. Cube requires the role to have USAGE on databases and schemas
and SELECT on tables. An example configuration:
GRANT USAGE ON DATABASE ABC TO ROLE XYZ;
GRANT USAGE ON ALL SCHEMAS IN DATABASE ABC TO ROLE XYZ;
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE ABC TO ROLE XYZ;
GRANT SELECT ON ALL TABLES IN DATABASE ABC TO ROLE XYZ;
GRANT SELECT ON FUTURE TABLES IN DATABASE ABC TO ROLE XYZ;- Account/Server URL (opens in a new tab) for Snowflake.
- User name and password or an RSA private key for the Snowflake account.
- Optionally, the warehouse name, the user role, and the database name.
Setup
If you're having Network error and Snowflake can't be reached please make sure you tried format 2 for an account id (opens in a new tab).
Manual
Add the following to a .env file in your Cube project:
CUBEJS_DB_TYPE=snowflake
CUBEJS_DB_SNOWFLAKE_ACCOUNT=XXXXXXXXX.us-east-1
CUBEJS_DB_SNOWFLAKE_WAREHOUSE=MY_SNOWFLAKE_WAREHOUSE
CUBEJS_DB_NAME=my_snowflake_database
CUBEJS_DB_USER=snowflake_user
CUBEJS_DB_PASS=**********Cube Cloud
In some cases you'll need to allow connections from your Cube Cloud deployment IP address to your database. You can copy the IP address from either the Database Setup step in deployment creation, or from Settings → Configuration in your deployment.
The following fields are required when creating a Snowflake connection:
Cube Cloud also supports connecting to data sources within private VPCs if dedicated infrastructure is used. Check out the VPC connectivity guide for details.
Environment Variables
| Environment Variable | Description | Possible Values | Required |
|---|---|---|---|
CUBEJS_DB_SNOWFLAKE_ACCOUNT | The Snowflake account identifier to use when connecting to the database | A valid Snowflake account ID (opens in a new tab) | ✅ |
CUBEJS_DB_SNOWFLAKE_REGION | The Snowflake region to use when connecting to the database | A valid Snowflake region (opens in a new tab) | ❌ |
CUBEJS_DB_SNOWFLAKE_WAREHOUSE | The Snowflake warehouse to use when connecting to the database | A valid Snowflake warehouse (opens in a new tab) in the account | ✅ |
CUBEJS_DB_SNOWFLAKE_ROLE | The Snowflake role to use when connecting to the database | A valid Snowflake role (opens in a new tab) in the account | ❌ |
CUBEJS_DB_SNOWFLAKE_CLIENT_SESSION_KEEP_ALIVE | If true, keep the Snowflake connection alive indefinitely (opens in a new tab) | true, false | ❌ |
CUBEJS_DB_NAME | The name of the database to connect to | A valid database name | ✅ |
CUBEJS_DB_USER | The username used to connect to the database | A valid database username | ✅ |
CUBEJS_DB_PASS | The password used to connect to the database | A valid database password | ✅ |
CUBEJS_DB_SNOWFLAKE_AUTHENTICATOR | The type of authenticator to use with Snowflake. Use SNOWFLAKE with username/password, or SNOWFLAKE_JWT with key pairs. Defaults to SNOWFLAKE | SNOWFLAKE, SNOWFLAKE_JWT, OAUTH | ❌ |
CUBEJS_DB_SNOWFLAKE_PRIVATE_KEY | The content of the private RSA key | Content of the private RSA key (encrypted or not) | ❌ |
CUBEJS_DB_SNOWFLAKE_PRIVATE_KEY_PATH | The path to the private RSA key | A valid path to the private RSA key | ❌ |
CUBEJS_DB_SNOWFLAKE_PRIVATE_KEY_PASS | The password for the private RSA key. Only required for encrypted keys | A valid password for the encrypted private RSA key | ❌ |
CUBEJS_DB_SNOWFLAKE_OAUTH_TOKEN | The OAuth token | A valid OAuth token (string) | ❌ |
CUBEJS_DB_SNOWFLAKE_OAUTH_TOKEN_PATH | The path to the valid oauth toket file | A valid path for the oauth token file | ❌ |
CUBEJS_DB_SNOWFLAKE_HOST | Host address to which the driver should connect | A valid hostname | ❌ |
CUBEJS_DB_SNOWFLAKE_QUOTED_IDENTIFIERS_IGNORE_CASE | Whether or not quoted identifiers should be case insensitive (opens in a new tab). Default is false | true, false | ❌ |
CUBEJS_DB_MAX_POOL | The maximum number of concurrent database connections to pool. Default is 20 | A valid number | ❌ |
CUBEJS_CONCURRENCY | The number of concurrent queries to the data source | A valid number | ❌ |
Pre-Aggregation Feature Support
count_distinct_approx
Measures of type
count_distinct_approx can
be used in pre-aggregations when using Snowflake as a source database. To learn
more about Snowflake's support for approximate aggregate functions, click
here (opens in a new tab).
Pre-Aggregation Build Strategies
To learn more about pre-aggregation build strategies, head here.
| Feature | Works with read-only mode? | Is default? |
|---|---|---|
| Batching | ❌ | ✅ |
| Export Bucket | ❌ | ❌ |
By default, Snowflake uses batching to build pre-aggregations.
Batching
No extra configuration is required to configure batching for Snowflake.
Export Bucket
Snowflake supports using both AWS S3 (opens in a new tab) and Google Cloud Storage (opens in a new tab) for export bucket functionality.
AWS S3
Ensure the AWS credentials are correctly configured in IAM to allow reads and writes to the export bucket in S3 if you are not using storage integration. If you are using storage integration then you still need to configure access keys for Cube Store to be able to read from the export bucket. It's possible to authenticate with IAM roles instead of access keys for Cube Store.
Using IAM user credentials:
CUBEJS_DB_EXPORT_BUCKET_TYPE=s3
CUBEJS_DB_EXPORT_BUCKET=my.bucket.on.s3
CUBEJS_DB_EXPORT_BUCKET_AWS_KEY=<AWS_KEY>
CUBEJS_DB_EXPORT_BUCKET_AWS_SECRET=<AWS_SECRET>
CUBEJS_DB_EXPORT_BUCKET_AWS_REGION=<AWS_REGION>Using Storage Integration (opens in a new tab) to write to Export Bucket and then Access Keys to read from Cube Store:
CUBEJS_DB_EXPORT_BUCKET_TYPE=s3
CUBEJS_DB_EXPORT_BUCKET=my.bucket.on.s3
CUBEJS_DB_EXPORT_INTEGRATION=aws_int
CUBEJS_DB_EXPORT_BUCKET_AWS_KEY=<AWS_KEY>
CUBEJS_DB_EXPORT_BUCKET_AWS_SECRET=<AWS_SECRET>
CUBEJS_DB_EXPORT_BUCKET_AWS_REGION=<AWS_REGION>Using Storage Integration to write to export bocket and IAM role to read from Cube Store:
CUBEJS_DB_EXPORT_BUCKET_TYPE=s3
CUBEJS_DB_EXPORT_BUCKET=my.bucket.on.s3
CUBEJS_DB_EXPORT_INTEGRATION=aws_int
CUBEJS_DB_EXPORT_BUCKET_AWS_REGION=<AWS_REGION>Google Cloud Storage
When using an export bucket, remember to assign the Storage Object Admin
role to your Google Cloud credentials (CUBEJS_DB_EXPORT_GCS_CREDENTIALS).
Before configuring Cube, an integration must be created and configured in
Snowflake (opens in a new tab). Take note of the integration name
(gcs_int from the example link) as you'll need it to configure Cube.
Once the Snowflake integration is set up, configure Cube using the following:
CUBEJS_DB_EXPORT_BUCKET=snowflake-export-bucket
CUBEJS_DB_EXPORT_BUCKET_TYPE=gcs
CUBEJS_DB_EXPORT_GCS_CREDENTIALS=<BASE64_ENCODED_SERVICE_CREDENTIALS_JSON>
CUBEJS_DB_EXPORT_INTEGRATION=gcs_intAzure
To use Azure Blob Storage as an export bucket, follow the guide on
using a Snowflake storage integration (Option 1) (opens in a new tab).
Take note of the integration name (azure_int from the example link)
as you'll need it to configure Cube.
Retrieve the storage account access key (opens in a new tab) from your Azure account.
Once the Snowflake integration is set up, configure Cube using the following:
CUBEJS_DB_EXPORT_BUCKET_TYPE=azure
CUBEJS_DB_EXPORT_BUCKET=wasbs://my-bucket@my-account.blob.core.windows.net
CUBEJS_DB_EXPORT_BUCKET_AZURE_KEY=<AZURE_STORAGE_ACCOUNT_ACCESS_KEY>
CUBEJS_DB_EXPORT_INTEGRATION=azure_intSSL
Cube does not require any additional configuration to enable SSL as Snowflake connections are made over HTTPS.