Snowflake

The following steps enable users to connect Single Origin to Snowflake.

Service Account

To begin, log in as an ACCOUNTADMIN in Snowflake and create a user account for Single Origin with limited access. This connector will not be used by anyone else besides the Single Origin service that performs the metadata collection.

The Snowflake SQL examples below will help in creating an account, but any existing account can be used as long as there are sufficient privileges to reference table schemas.

📘

Snowflake SQL Examples

The difference between the two examples below are:

  • no dataset profiling: creates an account with minimal privileges
  • dataset profiling: in addition to the above, enable the account to
    • read privilege on INFORMATION_SCHEMA and other metadata tables
    • a compute WAREHOUSE so can SELECT data related to table profiling e.g. what is number of rows in table, what is the storage size of the table, when was table created, when was table last updated
-- Create an account for Single Origin to collect only schema metadata.
-- No warehouse is needed to ensure that no data will be read besides schema.
-- Password (so_password) and Target database (target_db) must be provided.

USE ROLE ACCOUNTADMIN;

SET so_username= 'SINGLE_ORIGIN_SERVICE';
-- TODO: Fill password
SET so_password= <PASSWORD HERE>;
SET so_role= 'SINGLE_ORIGIN_ROLE';

CREATE ROLE IF NOT EXISTS identifier($so_role);

-- Grant permission to normal databases, replace target_db with all databases Single Origin can index for metadata.
-- TODO: Fill target database to grant permissions
SET target_db = <DATABASE NAME HERE>;
GRANT USAGE ON DATABASE identifier($target_db) TO ROLE identifier($so_role);
GRANT REFERENCES ON ALL TABLES IN DATABASE identifier($target_db) to ROLE identifier($so_role);
GRANT REFERENCES ON FUTURE TABLES IN DATABASE identifier($target_db) to ROLE identifier($so_role);
GRANT REFERENCES ON ALL EXTERNAL TABLES IN DATABASE identifier($target_db) to ROLE identifier($so_role);
GRANT REFERENCES ON FUTURE EXTERNAL TABLES IN DATABASE identifier($target_db) to ROLE identifier($so_role);
GRANT REFERENCES ON ALL VIEWS IN DATABASE identifier($target_db) to ROLE identifier($so_role);
GRANT REFERENCES ON FUTURE VIEWS IN DATABASE identifier($target_db) to ROLE identifier($so_role);
GRANT USAGE ON ALL SCHEMAS in DATABASE identifier($target_db) TO ROLE identifier($so_role);
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE identifier($target_db) to ROLE identifier($so_role);

-- Create Single Origin user and grant only Single Origin role with minimal requirements
CREATE USER identifier($so_username) PASSWORD=$so_password DEFAULT_ROLE=$so_role;
GRANT ROLE identifier($so_role) TO USER identifier($so_username);

-- Create an account for Single Origin to collect schema metadata and prolfile datasets.
-- Password (so_password) and Target database (target_db) must be provided.

USE ROLE ACCOUNTADMIN;

SET so_username= 'SINGLE_ORIGIN_SERVICE';
-- TODO: Fill password
SET so_password= <PASSWORD HERE>;
SET so_role= 'SINGLE_ORIGIN_ROLE';

CREATE ROLE IF NOT EXISTS identifier($so_role);

-- Grant permission to normal databases, replace target_db with all databases Single Origin can index for metadata.
-- TODO: Fill target database to grant permissions
SET target_db = <DATABASE NAME HERE>;
GRANT USAGE ON DATABASE identifier($target_db) TO ROLE identifier($so_role);
GRANT REFERENCES ON ALL TABLES IN DATABASE identifier($target_db) to ROLE identifier($so_role);
GRANT REFERENCES ON FUTURE TABLES IN DATABASE identifier($target_db) to ROLE identifier($so_role);
GRANT REFERENCES ON ALL EXTERNAL TABLES IN DATABASE identifier($target_db) to ROLE identifier($so_role);
GRANT REFERENCES ON FUTURE EXTERNAL TABLES IN DATABASE identifier($target_db) to ROLE identifier($so_role);
GRANT REFERENCES ON ALL VIEWS IN DATABASE identifier($target_db) to ROLE identifier($so_role);
GRANT REFERENCES ON FUTURE VIEWS IN DATABASE identifier($target_db) to ROLE identifier($so_role);
GRANT USAGE ON ALL SCHEMAS in DATABASE identifier($target_db) TO ROLE identifier($so_role);
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE identifier($target_db) to ROLE identifier($so_role);

-- Create Single Origin user and grant only Single Origin role with minimal requirements
CREATE USER identifier($so_username) PASSWORD=$so_password DEFAULT_ROLE=$so_role;
GRANT ROLE identifier($so_role) TO USER identifier($so_username);

-- To run profiling, the role needs a warehouse and select privileges.
GRANT SELECT ON ALL TABLES IN DATABASE identifier($target_db) to ROLE identifier($so_role);
GRANT SELECT ON FUTURE TABLES IN DATABASE identifier($target_db) to ROLE identifier($so_role);
GRANT SELECT ON ALL EXTERNAL TABLES IN DATABASE identifier($target_db) to ROLE identifier($so_role);
GRANT SELECT ON FUTURE EXTERNAL TABLES IN DATABASE identifier($target_db) to ROLE identifier($so_role);
GRANT SELECT ON ALL VIEWS IN DATABASE identifier($target_db) to ROLE identifier($so_role);
GRANT SELECT ON FUTURE VIEWS IN DATABASE identifier($target_db) to ROLE identifier($so_role);

SET so_warehouse_size='XSMALL';
SET so_warehouse_name= 'SINGLE_ORIGIN_WH';

-- Create warehouse for Single Origin workload
CREATE WAREHOUSE IF NOT EXISTS identifier($so_warehouse_name) WAREHOUSE_SIZE=$so_warehouse_size INITIALLY_SUSPENDED=TRUE
AUTO_SUSPEND = 5 AUTO_RESUME = TRUE;

-- Grant permissions to use the new warehouse
GRANT OPERATE, USAGE, MONITOR ON WAREHOUSE identifier($so_warehouse_name) TO ROLE identifier($so_role);

Once the account has been created, a Single Origin user with the Admin role can input the account credentials in the Single Origin portal:

  1. Navigate to "Mission Control > Single Origin Connectors" and add a new Single Origin connector of type Snowflake user pass.
  2. Input a name for the connector (what you would like to call it).
  3. Input the Username and Password from above. In the example above, the Username is SINGLE_ORIGIN_SERVICE.
  4. Input your server name ending with .snowflakecomputing.com
  5. Input the Warehouse (target_db) and Role (so_role) from above. For more on warehouses, see here.
  6. Click "Test" to test the connector, and if successful then "Save".

Personal Connector

📘

Note

  • To create a Data Entity, the logged in user must provide an account with sufficient privileges to reference the table schemas.
  • To query a Data Entity, the logged in user must provide an account with sufficient SELECT privileges to query the underlying tables in Snowflake.

Once a Single Origin connector is setup, users can setup (and modify) personal connectors using Username & Password as well. Setting up a personal connector is similar to setting up a Single Origin connector above:

  1. Navigate to "Mission Control > Personal Connectors."
  2. Add a new connector, and specify a type of Snowflake user pass.
  3. Input a name for the connector (what you would like to call it).
  4. Input your Username and Password.
  5. Input your server name ending with .snowflakecomputing.com. This should be the same as the Single Origin connector.
  6. Input the warehouse and role you are using to operate on your data in Snowflake - these likely match those setup in Service Account above.
  7. Click "Test" to test the connector, and if successful then "Save".

OAuth Integration

If you are using SSO to log into Snowflake or if you would like to use OAuth to connect to Snowflake, then you can setup OAuth in Single Origin.

📘

Note

With OAuth delegation, Single Origin will require users to re-authenticate in order to refresh the tokens when they expire.

To start, your Snowflake account must have OAuth enabled: see Snowflake OAuth Documentation. Once OAuth is enabled, then to integrate Snowflake OAuth with Single Origin, a Snowflake user with ACCOUNTADMIN role must perform the following two steps:

  1. In Snowflake, create a security integration with Single Origin. Run the following SQL, but replace {tenant} with your Single Origin instance.
CREATE OR REPLACE SECURITY INTEGRATION SINGLE_ORIGIN
  TYPE = OAUTH
  ENABLED = TRUE
  OAUTH_CLIENT = CUSTOM
  OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
  OAUTH_REDIRECT_URI = 'https://{tenant}.singleorigin.tech/api/connectors/oauth/snowflake'
  OAUTH_ISSUE_REFRESH_TOKENS = TRUE
  OAUTH_REFRESH_TOKEN_VALIDITY = 7776000;
  
WITH oauth_client AS (
  SELECT parse_json(system$show_oauth_client_secrets('SINGLE_ORIGIN')) AS client_secrets
)
SELECT
  client_secrets:"OAUTH_CLIENT_ID"::string     AS client_id,
  client_secrets:"OAUTH_CLIENT_SECRET"::string AS client_secret
FROM oauth_client;
  1. In Single Origin, navigate to "Mission Control > Single Origin Connectors > Connector Settings." Copy and paste the client_id and client_secret from the previous step into the setting form.

Once this is complete, Admins have configured the required OAuth information in Snowflake to:

  • setup a Single Origin connector, as well as
  • allow users to setup personal connectors with OAuth.

An admin can setup a Single Origin connector of type Snowflake OAuth with the connector settings created above. After this, any user that would like to setup a personal connector simply needs to:

  1. Navigate to "Mission Control > Personal Connectors"
  2. Click "New"
  3. Select type of Snowflake OAuth, and select the connector settings setup by admin above.
  4. Input a Name, Warehouse, and Role.
  5. Click "Authenticate with Snowflake"

Updated 9 months ago