Overview
Configuration is provided for establishing connections with the Snowflake service. The configuration is used in the REST Connection Manager.
Setup
Press icon to get more information about the connection parameters.
Obtain data
Use REST Source component to read data from Snowflake.
Insert data
Use REST Destination component to insert/update/delete rows in a Snowflake table.
Quick Start
In order to obtain an access token for the Snowflake REST API, you would first need to configure a custom client with OAuth access in Snowflake.
DISCLAIMER: The example below is just an example how to get started! For the official Snowflake documentation click here.
Step 1. For this demo's purposes we will be using the SYSADMIN role but the steps work for any user created role.
With ACCOUNTADMIN as your current role, execute GRANT CREATE INTEGRATION ON ACCOUNT TO ROLE SYSADMIN
Step 3. Open a Snowflake worksheet and create an integration, example integration below. Official Snowflake documentation for the integration can be found here.
CREATE SECURITY INTEGRATION [Your integration name]
TYPE = oauth
ENABLED = true
OAUTH_CLIENT = custom
OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
OAUTH_REDIRECT_URI = 'https://www.cozyroc.com/oauth_callback'
OAUTH_ISSUE_REFRESH_TOKENS = TRUE
OAUTH_REFRESH_TOKEN_VALIDITY = 86400
Step 4. You're almost ready, now to check the Id and Secret of your Integration, just execute SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS( [Your integration name] ) and you can input them in the connection manager.
In this task, we will show you step-by-step how to create a connection to the Snowflake REST API using COZYROC's REST Connection Manager.
Step 2. From the drop-down menu Configuration select Snowflake. Enter a valid Account URL for the Server parameter, you can copy it from the Snowflake UI. The Account URL should look something like this "https://fo32815.switzerland-north.azure.snowflakecomputing.com". Add "/api/v2" at the back of your server URL. Alternatively you can replace the [account identifier] placeholder with your account id separated with a dash e.g. ORGID1-ACCID1.
Step 3. In the Token file row click "New" and input the integration Secret and Id, if you do not know what that is refer to the Snowflake documentation or the part about Snowflake in this documentation. Next copy and paste the integration Redirect_uri and if you have a Schema in which you will be working, specify it and the database it's located in.
Step 4. Press the Test Connection button. When you see Test Connection succeeded, click OK.
Congratulations! You have successfully created a connection to the Snowflake API using COZYROC's REST Connection Manager.
Step 1. Generate as a minimum, a 2048-bit RSA key pair. After navigating to a proper folder, open a terminal or command prompt and execute:
openssl genpkey -algorithm RSA -out private.key -aes256
Note: The generated private.key file contains the encrypted RSA private key. Keep this file secure and never share it with unauthorized individuals.
Step 2. Generate a certificate signing request (CSR) with the command:
openssl req -new -key private.key -out certificate.csr
Step 3. Generate a self-signed certificate:
openssl x509 -req -days 365 -in certificate.csr -signkey private.key -out certificate.crt
Step 4. Combine the private key and the certificate into a PFX/P12 file:
openssl pkcs12 -export -out certificate.pfx -inkey private.key -in certificate.crt
Step 5. Extract the public key from the private key since we need the public key for Snowflake:
openssl rsa -in private.key -outform PEM -pubout -out public_key.pem
Step 6. Assign the public key to a Snowflake user:
ALTER USER jsmith SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';
Note: Only users with the SECURITYADMIN role or higher can alter a user.
In this task, we will show you step-by-step how to create a connection to the Snowflake REST API using COZYROC's REST Connection Manager.
Step 2. From the drop-down menu Configuration select Snowflake. Enter a valid Account URL for the Server parameter, you can copy it from the Snowflake UI. The Account URL should look something like this "https://fo32815.switzerland-north.azure.snowflakecomputing.com" replace the default value and add "/api/v2". Your server field should look like the one in the image. Alternatively you can replace the [account identifier] placeholder with your account id separated with a dash e.g. ORGID1-ACCID1.
Step 4. The account identifier and username
For the account identifier, follow the image and copy it from the website.
NOTE You have to delete the "." symbol separating the two values and replace it with a "-" symbol, so it would look like this - NVVA4TE-UD16253
For the username, just copy the username you login into your account.
Step 6. Press the Test Connection button. When you see Test Connection succeeded, click OK.
Congratulations! You have successfully created a connection to the Snowflake API using COZYROC's REST Connection Manager.
In this guide, we will show you how to read data from the Snowflake API resource using the COZYROC REST Source component.
Step 3. Double click on the "REST Source" component and select the Snowflake connection from the list. Select from the available resources in the second list. Enter the name of the warehouse you created earlier and if you want to have a statement that is different from the default SELECT * FROM [Your table name], specify it in the Statement field.
Bravo! You have now learned how to read data from the Snowflake API service.
In situations where you need to process hierarchical, parent-child data, you might prefer using the "Composite records" SSIS+ pattern instead of a normal tabular join. Below, we'll showcase how to construct a query that produces such a response and how it is consumed.
For the purpose of this demo, we will be using a query which utilizes the database SNOWFLAKE_SAMPLE_DATA, allowing anyone to execute it.
Step 1. Using the Custom Query resource, paste your statement. An example statement (you may need to remove the line breaks, a tool such as this can be very helpful):
SELECT O.O_ORDERKEY, array_agg( OBJECT_CONSTRUCT( 'O_COMMENT', O_COMMENT, 'O_ORDERDATE', O_ORDERDATE, 'O_TOTALPRICE', O.O_TOTALPRICE ) ) AS order_details, array_agg( OBJECT_CONSTRUCT( 'L_SUPPKEY', L.L_SUPPKEY, 'L_COMMITDATE', L.L_COMMITDATE, 'L_COMMENT', L.L_COMMENT, 'L_PARTKEY', L.L_PARTKEY, 'L_ORDERKEY', L.L_ORDERKEY, 'SOMEOBJECT', OBJECT_CONSTRUCT( 'COMMENT', L.L_COMMENT, 'COMMITDATE', L.L_COMMITDATE, 'SOMEARR', ARRAY_CONSTRUCT( OBJECT_CONSTRUCT('L_COMMENT', L_COMMENT), OBJECT_CONSTRUCT('L_DISCOUNT', L_DISCOUNT) ) ) ) ) AS lineitem_details FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS O JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.LINEITEM L ON O.O_ORDERKEY = L.L_ORDERKEY WHERE O.O_ORDERKEY < 50 GROUP BY O.O_ORDERKEY
In this guide, we will show you how to write data to the Snowflake API service resource using the COZYROC REST Destination component.
Done. You have now learned how to write data into the Snowflake API resource.
Configuration
Base URL address: https://[account identifier].snowflakecomputing.com/api/v2
.
- OAuth 2
-
The authentication uses an authorized token. The token will be refreshed with the following expression:
{{=Date.now() + (response.expires_in - 300) * 1000}}
.The authentication has the following user-defined parameters:
- ClientId: Required. Specify client_id for OAuth integration.
- ClientSecret: Required. Specify client_secret for OAuth integration.
- Database: Specify default database. Optional.
- Schema: Specify default schema. Optional.
- RedirectUri: Required. Specify redirect_uri for OAuth integration.
- Scope: https://docs.snowflake.com/en/user-guide/oauth-custom#scope
The following request parameters will be automatically processed during the authentication process:
-
Authorization:
Bearer {{=token.Access}}
-
User-Agent:
COZYROC_SSISPlus
Documentation: https://docs.snowflake.com/en/developer-guide/sql-api/authenticating.
- Key-Pair Auth
-
The authentication uses a parameters-based authentication.
The authentication has the following user-defined parameters:
- PrivateKey: Required. Select private key file (p12/pfx).
- PrivateKeyPassword: Required. The password for the PFX file.
- AccountIdDashSeperated: Required. This is your Snowflake Account Identifier, separated by a dash e.g. HG55RP1-WK8RCYL
- Username: Required. The username you login with.
- PublicKeyFingerprint: Required. Execute the SQL DESCRIBE USER command to get the value from the RSA_PUBLIC_KEY_FP property.
The following request parameters will be automatically processed during the authentication process:
-
X-Snowflake-Authorization-Token-Type:
KEYPAIR_JWT
-
Authorization:
Bearer {{=Snowflake.KeyAuth.generateJWT()}}
Documentation: https://docs.snowflake.com/en/developer-guide/sql-api/authenticating#using-key-pair-authentication.
Based on resource template Base.
- [Read] action
-
The result is extracted from:{{=Snowflake.getData(parameters, resource)}}
.The action has the following user-defined parameters:
- Statement: Required. Snowflake standard SQL statement
- Database: Snowflake database. If not specified, the user default database will be used.
- Schema: Snowflake schema. If not specified, the user default schema will be used.
Documentation: https://docs.snowflake.com/en/developer-guide/sql-api/handling-responses.
- [External]
-
The external fields list is extracted from:
{{=Snowflake.getQueryFields(parameters)}}
.
- [Read] action
-
The result is extracted from:{{=Snowflake.getData(_.extend( parameters, { database: resource.external.database_name, schema: resource.external.schema_name, Statement: 'SELECT ' + parameters.fields.join(',') + ' FROM ' + resource.external.name + (parameters.get('Where') ? ' WHERE ' + parameters.Where : '') } ), resource)}}
.The action has the following user-defined parameters:
- Where: Specify filter. Optional.
The following request parameters will be automatically processed:
-
offset:
{{=parameters.iterator}}
-
limit:
{{=parameters.batchSize}}
-
_includeUserParameters:
{{=parameters}}
- [Create] action
-
Endpoint URL address:
/statements
.
The result is extracted from:{{=utils.ensureArray(result)}}
.The following request parameters will be automatically processed:
-
application/json:
{{=Snowflake.create(resource, parameters, batch)}}
Documentation: https://docs.snowflake.com/en/sql-reference/sql/insert-multi-table.
-
application/json:
- [Update] action
-
Endpoint URL address:
/statements
.
The action uses POST method.The following request parameters will be automatically processed:
-
application/json:
{{=Snowflake.update(resource, parameters, item)}}
Documentation: https://docs.snowflake.com/en/sql-reference/sql/update.
-
application/json:
- [Upsert] action
-
Endpoint URL address:
/statements
.
The action uses POST method.The following request parameters will be automatically processed:
-
application/json:
{{=Snowflake.upsert(resource, parameters, item)}}
Documentation: https://docs.snowflake.com/en/sql-reference/sql/merge.
-
application/json:
- [Delete] action
-
Endpoint URL address:
/statements
.
The action uses POST method.The following request parameters will be automatically processed:
-
application/json:
{{=Snowflake.delete(resource, parameters, item)}}
Documentation: https://docs.snowflake.com/en/sql-reference/sql/delete.
-
application/json:
- [External]
-
The external fields list is extracted from:
{{=Snowflake.getTableFields(resource)}}
.
The external resource list is extracted from: {{=Snowflake.getTables()}}
.
Based on resource template Base.
- [Read] action
-
The result is paginated.The action has the following user-defined parameters:
- Warehouse: Required. Snowflake warehouse
- Timeout: Timeout duration for the response
The following request parameters will be automatically processed:
-
_includeUserParameters:
{{=parameters}}
- [Create] action
-
The action uses POST method.The action has the following user-defined parameters:
- Warehouse: Required. Snowflake warehouse
- Timeout: Timeout duration for the response
The following request parameters will be automatically processed:
-
_includeUserParameters:
{{=parameters}}
-
application/json:
{{=item}}
- [Update] action
-
The action uses PUT method.The action has the following user-defined parameters:
- Warehouse: Required. Snowflake warehouse
- Timeout: Timeout duration for the response
- Where: Attaches a WHERE on the statement for additional filtering.
The following request parameters will be automatically processed:
-
_includeUserParameters:
{{=parameters}}
-
application/json:
{{=item}}
- [Upsert] action
-
The action uses POST method.The action has the following user-defined parameters:
- Warehouse: Required. Snowflake warehouse
- Timeout: Timeout duration for the response
- [Delete] action
-
The action uses DELETE method.The action has the following user-defined parameters:
- Warehouse: Required. Snowflake warehouse
- Timeout: Timeout duration for the response
- Where: Attaches a WHERE on the statement for additional filtering.
- ShortText
Data type: DT_WSTR ( length 255 )
- LongText
Data type: DT_WSTR ( length 1000 )
- DateTime
Data type: DT_DBTIMESTAMP
- Date
Data type: DT_DBDATE
- Number
Data type: DT_R8
- Bool
Data type: DT_BOOL
- Integer
Data type: DT_I8
Knowledge Base
What's New
- New: Introduced connection.
COZYROC SSIS+ Components Suite is free for testing in your development environment.
A licensed version can be deployed on-premises, on Azure-SSIS IR and on COZYROC Cloud.