Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dec 4, 2024 8:31:52 AM
Nov 8, 2021 4:11:22 AM
This article overviews the available blocks in the Snowflake connector in Qlik Application Automation. It will also cover some basic examples of retrieving data from a Snowflake database and creating a record in a database.
Connector overview
The Snowflake connector has the following blocks:
- List Tables: returns a list of the tables from the connected database
- List Records: returns a list of records from the specified table
- Insert Record: create one record in the specified table
- Upsert Record: create or update one record in the specified table
- Insert Bulk: create multiple records in the specified table
- Upsert Bulk: create or update multiple records in the specified table
- Do Query: do a generic SQL query against the connected Snowflake database
- Update Record by One Field: update a single record in the specified table
- Delete Record: delete one record in the specified table
- List Schemas: returns a list of schemas from the connected database
Authentication
To create a new connection to Snowflake, the following parameters are required:
- account_name -> your snowflake account/tenant name. Type 'ABC' if your Snowflake URL is abc.snowflakecomputing.com.
Warning
Account names that include underscores can sometimes cause issues for certain features. For this reason, Snowflake also supports a version of the account name that substitutes the hyphen character (-) in place of the underscore character. For example, both of the following URLs are supported:
URL with underscores:
https://acme-marketing_test_account.snowflakecomputing.comURL with dashes:
https://acme-marketing-test-account.snowflakecomputing.comMore details about the account name can be found in the below Snowflake documentation
- username -> username for the user with remote access to the Snowflake database.
- password -> password used to authenticate the above username. If the key pair authentication method is being used, use this field to provide the decryption passphrase.
- dbname -> the name of the database you want to use for this connection. You'll need to create multiple connections to connect to multiple databases in the same automation.
- warehouse -> the name of the warehouse you want to use for this connection.
- keyfile -> this is an optional parameter that is used for the key pair authentication method. Only the encrypted version of the private key is supported as this method offers enhanced authentication security. You must generate a Privacy Enhanced Mail (.pem) file and copy the contents into this field. Use the password field to provide the private key passphrase for decryption. For more information about the key pair authentication method, see this article.
Examples
Insert a new record into a table
- Add the Insert Record block from the Snowflake connector to your automation.
- Configure the block to point to a table in the database you're currently connected to. You can use the do lookup function for this.
- Run the automation. This will insert a new record in your Snowflake table.
Use the Do Query block to create a new table
The Do Query block can be used to perform actions in Snowflake that aren't supported by the other blocks.
- Create a new automation
- Search for the Snowflake connector in the Block library menu on the left side of the canvas, and find the Do Query block. Drag this block inside the automation. Highlight the Do Query block by clicking it and configure it in the Block configuration menu on the right side of the canvas.
- Add your query to create a new table in the Query input field. Here is a query example that creates a table:
CREATE TABLE "MY_DATABASE"."PUBLIC"."TEST" (ID INT, "Description" varchar (100), "Serial" NUMBER, COST FLOAT, "Create_date" DATE, "Details" VARIANT); - Run the automation. This will create a new table with the specified structure within the selected database.
Upsert multiple records into a table
- Add the Upsert Bulk block from the Snowflake connector to your automation.
- Configure the block to point to a table in the database you're currently connected to. You can use the do lookup function for this.
- The value of the Where key input parameter should be a unique identifier that is used to check the existing records and update them if needed.
- The value of the Data input parameter should be Json List to pass multiple records as Json Objects, along with a unique identifier added as a key-value pair to the Json Object.
The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.
