Connect Pigment with Snowflake

  • 26 May 2022
  • 2 replies
  • 229 views
Connect Pigment with Snowflake
Userlevel 4
Badge +2

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 following 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. 

 

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”)
  • 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)
  • Application Access: List the application which will be allowed to use this connection

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! 


2 replies

Badge

@Pierre Is there an article on what kind of function/language pigment supports in the query itself? For example would “case when”, “NVL”, “count distinct”, etc work?

Userlevel 4
Badge +4

@Pierre Is there an article on what kind of function/language pigment supports in the query itself? For example would “case when”, “NVL”, “count distinct”, etc work?

the query is executed by Snowflake directly, not Pigment, so no limit on our side.

Reply