Skip to main content
Sign in

Connecting to Snowflake

  • Updated

This article covers how to connect Openprise to Snowflake when setting up a data source or data target  using a Standard Connector.

This connector uses OAuth authentication and requires set up of a security integration by your Snowflake administrator before you configure the connector in Openprise. See the section on setting up a security integration at the end of this document for details on the commands needed.

You will need the Snowflake credentials for Openprise, with permission to query and update the table or view configurations. You can learn more about Data Sources HERE.

 

Prerequisites

To authenticate to Snowflake in Openprise you will need the following credentials. Please refer to the Snowflake Configuration section below for instructions on how to configure Snowflake and obtain the needed credentials.

  1. accountUrl - this is unique to your account and will be in the format xxxx.snowflakecomputing.com
  2. clientID
  3. client Secret
  4. Username and password

The table you wish to import should contain the following fields:

  1. Primary key (unique attribute)
  2. Date value for the created date (primary timestamp)
  3. Date value for the last modified date (modified timestamp) 

Note: Snowflake has several time data types. Openprise imports the following type as text:

  • Time
  • TIMESTAMP_LTZ (LocalTimeZone)
  • TIMESTAMP_TZ (TimeZone)

Note: Snowflake has several date data types. Openprise imports the following type as a date:

  • Date
  • Datetime
  • Timestamp
  • Timestamp_ntz

 

Configuring the Data Source

When creating a new data source, select Snowflake from the source technology and data format picklist.

Snowflake1.png

Snowflake2.png4.png

  1. Select the Add Account Information button to link your Snowflake account to Openprise
  2. Click on the Add Account button to sign into your Snowflake account using your credentials
  3. Select from an existing account if you've already linked your Snowflake account to Openprise

If you selected the Add Account button, you will need to supply the following: 

snowflake authentication.png

You will then be asked to sign into your Snowflake account. 

Note: Snowflake’s OAuth requires you to re-enter your credentials every 3 to 12 months. Click HERE for more details.

Snowflake5.png

Supply the following information to import data from a specific table: 

Snowflake6.png

  1. Directory or entity: select the warehouse from Snowflake
  2. Database: Select the name of the database 
  3. Schema: Select the schema
  4. Table or View: Select the table or view 

 

Advanced Usage

Openprise supports importing records by supplying a custom query. To use this optional feature, check the “Import by Custom Query” box, and supply the query to be used for import.

1.png

  1. Enter your query.
  2. Check the syntax by selecting the VALIDATE button.
    1. If the query syntax is accepted by Snowflake, Openprise will return the number of records that match the query and will be imported.
    2. If the query syntax is rejected, an error message will appear.
  3. When you have a successful query, click next to view the Parse screen. Review the sample records retrieved. Then click continue.
  4. Continue with the Data Source setup

Example Queries

Simple query selecting all fields from one table.

2.png

Simple query with error message resulting from the semicolon at the end of the query

snowflake simple query with error.png

More complex query involving two tables. Note: the LIMIT command is optional.

3.png

 

Snowflake Configuration

To authenticate to Snowflake in Openprise we need to first configure Snowflake to establish a clientID and client Secret.

Please use the queries below to create a security integration for Openprise and then copy the client id and secret. These commands require the ACCOUNTADMIN or SECURITYADMIN role to execute.

Step1

In this example OPPROD is the name of the security integration. You can choose a different name if desired.

create or replace security integration OPPROD
type = oauth
enabled =true
oauth_client=custom
oauth_client_type='CONFIDENTIAL'
oauth_redirect_uri='https://www.openprisecloud.com/daass/oauthclient/snowflake/callback'
oauth_issue_refresh_tokens=true
oauth_allow_non_tls_redirect_uri=false

Step 2

Execute the following query to check security integration details.

  • DESC SECURITY INTEGRATION OPPROD

To get the clientId and secret from your security integration, execute the following command and then copy the client Id and clientSecret from the json.

  • Select SYSTEM$SHOW_OAUTH_CLIENT_SECRETS( 'OPPROD')

Example response:
{
"OAUTH_CLIENT_SECRET_2": "gHsOYZzB862cHBPWgI9Zju1vkx7pKLwNlppkif72wSs=",
"OAUTH_CLIENT_SECRET": "hIUPETv5F0c/MC7DjndJJTuZI3ZeQj5X7UtexXKZEWI=",
"OAUTH_CLIENT_ID": "Wor1fP+U5e3SKojTnO3B3neqHq0="
}

https://docs.snowflake.com/en/sql-reference/functions/system_show_oauth_client_secrets

 

Openprise can't log in to Snowflake with the ACCOUNTADMIN or SECURITYADMIN role using an OAuth flow because Snowflake imposes security restrictions to prevent these highly privileged roles from being used in OAuth integrations. Please see the document Overview of Access Control | Snowflake Documentation for details.

With an OAuth Security integration, we use the sysadmin role to login and manage data. Sometimes the sysadmin role has not been granted to warehouse, databases, schemas or tables. Please verify if the sysadmin role has proper access to the tables you want to connect with Openprise.

 

Troubleshooting the Standard Connector to Snowflake

If you cannot see entities in the dropdown in Openprise after authenticating to Snowflake, use the following query to grant access to sysadmin. In the command below, please replace {warehouse name} with your warehouse name

>GRANT USAGE ON WAREHOUSE {warehouse name} TO ROLE SYSADMIN;

Related articles