This article covers:
To make a connection to Snowflake, navigate to the Connections page, select New Connection, then select Snowflake from the New Connection modal. You'll be able to provide the following properties:
.snowflakecomputing.com suffix). This should have the format eg. TO12345.eu-west-1 or TO12345.us-east-2.aws on AWS or TO12345.west-europe.azure on Azure. See the Snowflake documentation for the full list.The Snowflake connector supports 2 authentication modes:
-----BEGIN ENCRYPTED PRIVATE KEY-----
base64 key material
-----END ENCRYPTED PRIVATE KEY-----
Please refer to the examples in our developer documentation to find out how to create a connection to Snowflake using our API.
Notes: If you use Network Policies within Snowflake, you should whitelist Luzmo's range of IP addresses as described here You'll find more information how to do this in the Snowflake user manual: Modifying a Network Policy.
You can create a read-only technical user in Snowflake by running the following query (while replacing references):
-- ACCOUNTADMIN role is necessary for initial user creation setup
USE role ACCOUNTADMIN;
-- Create Luzmo role
CREATE role IF NOT EXISTS <ROLE NAME>;
-- Grant Luzmo role to SYSADMIN role (to modify Luzmo role)
GRANT role <ROLE NAME> TO role SYSADMIN;
-- Create a user for Luzmo
CREATE user IF NOT EXISTS "<USERNAME>" -- <USERNAME> used to create Snowflake connection in Luzmo
-- See https://docs.snowflake.com/en/user-guide/key-pair-auth
rsa_public_key = 'MIIBIjANBgkqh...'; -- [✔ Recommended key-pair authentication] Assign public key to user <USERNAME>
-- password = '<PASSWORD>'; -- [⚠ Legacy, see above] <PASSWORD> could be used to create Snowflake connection in Luzmo
-- Grant Luzmo user acces to Luzmo role
GRANT role <ROLE NAME> TO user "<USERNAME>";
-- Change Luzmo user to set default role and warehouse
ALTER user "<USERNAME>"
SET default_role = <ROLE NAME>
default_warehouse = <WAREHOUSE>;
USE role SYSADMIN;
-- Create warehouse if not exists
-- (you can also skip this and create the warehouse in the Snowflake UI, or use your current warehouse)
CREATE warehouse IF NOT EXISTS <WAREHOUSE>
-- Alter these according to your needs
warehouse_size = medium
warehouse_type = standard
auto_suspend = 1800
auto_resume = true
initially_suspended = true;
-- Make sure to use the role that has granting permissions for the warehouse, databases, schemas and tables
-- USE role ACCOUNTADMIN;
-- Grant usage and resuming of warehouse to your Luzmo role
GRANT USAGE, OPERATE ON warehouse <WAREHOUSE> TO role <ROLE NAME>;
-- Grant read-only database access on your database
-- (do this for all databases and schemas that you'd like to expose to Luzmo)
GRANT USAGE ON database <DATABASE> TO role <ROLE NAME>;
-- or, grant read-only database access on shared or imported databases
-- GRANT IMPORTED PRIVILEGES ON database <DATABASE> TO role <ROLE NAME>;
-- Grant read-only access on a schema in your database
GRANT USAGE ON schema <DATABASE>.<SCHEMA> TO role <ROLE NAME>;
-- Grant read-only access to all tables in schemas
GRANT SELECT ON ALL tables IN schema <DATABASE>.<SCHEMA> to role <ROLE NAME>;
You can further customize this query (e.g. only granting access to certain tables), Refer to the SQL Command Reference of Snowflake itself for more information.
Once you have connected your Snowflake you can add datasets as explained here.
{{metadata.< parameter name >|< default value >}}. Find out more about parameterized SQL Datasets.Also make sure to check out this article on Preparing your data for analytics.
To find out how to add datasets using our API, please refer to the examples in our developer documentation.
When generating an Authorization token to grant a user acces to your embedded dashboards it is possible to override the data source properties in the authorization request to dynamically use different properties. Find out more about connection overrides.
The fields available for overriding a Snowflake connection are as follows:
<new_schema_name>.<table_name>.Our developer documentation has more info about connection overrides and an examples of Snowflake connection overrides.
Example code demonstrating how to override a Snowflake connection:
POST https://api.luzmo.com/0.1.0/authorization
{
"action":"create",
"version":"0.1.0",
"key": "< your API key >",
"token": "< your API token >",
"properties":{
"type": "embed",
...,
"account_overrides": {
"<your connection_id>": {
"properties": {
"host": "<New account name of your Snowflake account to connect to>",
"key": "<New username of Snowflake user>",
"private_key": "<New private key, in case key-pair auth is used>",
"token": "<Passphrase used to protect private key, in case key-pair auth is used, or new password of Snowflake user>",
"warehouse": "<New Snowflake Warehouse to use to query data>",
"role": "<New Snowflake user Role to use to query data>"
}
}
}
}
}
Example code demonstrating override the table for a single Snowflake dataset:
POST https://api.luzmo.com/0.1.0/authorization
{
"action":"create",
"version":"0.1.0",
"key": "< your API key >",
"token": "< your API token >",
"properties":{
"type": "embed",
...,
"account_overrides": {
"<your connection_id>": {
"datasets": {
"<dataset_id>": {
"table": "<new_schema_name>.<table_name>"
}
}
}
}
}
}
| Data Type in Snowflake | Data Type in Luzmo |
|---|---|
| number | numeric |
| decimal | numeric |
| numeric | numeric |
| int | numeric |
| integer | numeric |
| bigint | numeric |
| smallint | numeric |
| float | numeric |
| float4 | numeric |
| float8 | numeric |
| double | numeric |
| double precision | numeric |
| real | numeric |
| date | date |
| datetime | datetime |
| time | datetime |
| timestamp | datetime |
| timestamp_ltz | datetime |
| timestamp_ntz | datetime |
| timestamp_tz | datetime |
| All Other Data Types | hierarchy |