Connect Pigment with Snowflake

  • 26 May 2022
  • 0 replies
  • 2376 views
Connect Pigment with Snowflake
Userlevel 5
Badge +8

This article explains how to connect Snowflake with Pigment to retrieve any query result into a Pigment’s block.
 

Table of contents

As with all other native Pigment integrations, you’ll first need to configure a Connection in the Integration library page. Once configured, this connection can be used by any Application that is authorized as a data source. These authorized Apps are defined during the ‘Import Data’ configuration step. Now, let’s see how to configure and use this native integration!

 

Preliminary steps on Snowflake side

 

First, you need to collect some information about your Snowflake account.  To do this, click on the bottom left of the screen when using the new UI Snowsights . 

 

From here you’ll need the following two details:

  • Organization
  • Account


To authenticate to your snowflake cluster Pigment is using Key-Pair Authentication (Snowflake Doc). You can either set up this Key-Pair Authentication on a new dedicated user (recommended) or use an already existing user. You’ll need to gather the following keys, make sure to stores these keys in a safe place.

 

  1. Follow Snowflake documentation to generate an unencrypted private key 

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt

  1. Continue to follow the documentation to generate a public key

openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

  1. Associate the public key to the user you want Pigment to use for the integration (content of the file rsa_key.pub with the command given previously). You can refer to Snowflake documentation.

alter user pierrevanroy set rsa_public_key='MIIBIjANBgkqh...';

  1. Set a Default Role and Default Warehouse to the user . The role and warehouse will be the ones used by Pigment.

 

Recommended

Be careful to set the correct permission level.  We recommend Read-only permission and limited access to tables they should access. 
This role will be accessible by all users using the snowflake integration in Pigment. As Pigment will submit Query written manually by the end user, make sure that commands such as ALTER, CREATE, INSERT or DROP will be refused. 

Network Policies

If your Snowflake cluster is protect with some Network Policies, you will need to Allow Pigment’s IP to reach your cluster. The IPs to add to the allow list:

 35.242.251.111 and 34.145.54.113

 

Setting up the Snowflake Connector in Pigment

 

Within Pigment, navigate to the Integration page from your Workspace homepage, and click on + Add on the Snowflake integration.

 

Important Note 

You need to be a Pigment Workspace Admin to be allowed to configure the connector.

 

Click on Setup a Connection and then fill in the following 4 fields: 

  • Name: give a name to your connection (ex: “Snowflake connection”)
  • Application Access: List the application which will be allowed to use this connection
  • Organization: input your Snowflake Organization Id (ex: YYTIGHZ)
  • Account: input the Snowflake account Id you want to query (ex: TW37351)
  • User Name: input the Service account user name you generated during previous step (ex: pierrevanroyx)
  • Private Key: input the private key you generated during previous step (generated during the preliminary steps, content of the file rsa_key.p8)

Note

Once created you won’t be able to edit the connection details (except “Name” and “Application Access”)  

 

Click on Setup to finish the setup! 

 

Load Snowflake data into Pigment

 

Once the Snowflake Connection is configured, open an Application that the connection is available on.  From here click “Import Data” for the object you want to import data into.  For example, if you were importing data into a Transactions, you would open up list and then select Import and Download and then Import .

Instead of “Upload file”, select the Integration option.  After this, select the Snowflake Connection you just configured.

 

Note

If you do not see the Snowflake connection you have configured, then make sure the current application is part of the authorized applications for this connection. If you do not see the connection, navigate back to Integrations page, and verify that the Application is on the Application Access list.

 

Now, you just need to input a Query and click on Start Import.

 

The result of the query should load in Pigment within a few seconds! 

 

Debugging a connection

If you’re having trouble with your Snowflake connection, execute the following query using the connection you want to debug: 

SELECT CURRENT_REGION(), CURRENT_ROLE(), CURRENT_ORGANIZATION_NAME(), CURRENT_ACCOUNT(), CURRENT_ACCOUNT_NAME(), CURRENT_USER(), CURRENT_WAREHOUSE();

Query Returns an Error

If your query returns an error, you need to do the following: 

  • Check if Pigment IP was correctly added to the Snowfake Network Policies
  • Check that you’ve correctly generated your private key. 
    This is described in detail at the start of this topic.

  • Confirm that you’re using the correct values for Account, User, and Organization.

Query Returns Values

If your query returns values, ensure that they match your configuration: 

  • Region: This is the Snowflake instance targeted by Pigment. If it’s incorrect, contact our Support team for assistance.  

  • Role or Warehouse: If these are incorrect, check that the user’s default Role and default Warehouse are correctly configured in the Snowflake UI. These are the Role and Warehouse used by Pigment.

  • Account, Organization, or User: If these are incorrect, you need to reconfigure your connection within Pigment. 

If the above query works but you’re still experiencing problems accessing the data you need, check the permissions associated to the Role used by the Pigment connection. This returns the privileges and roles granted on the database and schema where you’re targeting your query. To do this, run the following query from the Snowflake UI: 

SHOW GRANTS TO role_used_by_pigment; 

 

 


This topic has been closed for comments