Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER

How to get started with Snowflake in Qlik Application Automation

100% helpful (1/1)
cancel
Showing results for 
Search instead for 
Did you mean: 
Ionut_Dumitrascu

How to get started with Snowflake in Qlik Application Automation

Last Update:

Dec 4, 2024 8:31:52 AM

Updated By:

Ionut_Dumitrascu

Created date:

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

    URL with dashes: https://acme-marketing-test-account.snowflakecomputing.com

    More 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

  1. Add the Insert Record block from the Snowflake connector to your automation.
  2. 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.

    Insert Record.png

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

  1. Create a new automation
  2. 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.

    select do query.png

    Do Query.png

  3. 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);

    query.png

  4. Run the automation. This will create a new table with the specified structure within the selected database.

 

Upsert multiple records into a table

  1. Add the Upsert Bulk block from the Snowflake connector to your automation.
  2. 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.
  3. 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.
  4. 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.

    Upsert block.png

 

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.

 

Tags (1)
Labels (2)
Comments
estherl
Partner - Contributor
Partner - Contributor

Does the Snowflake connector in App Automation support other forms of authentication other than username and password? For example OAuth mechanism like we do for a data connection for Qlik app development? Thanks

Sonja_Bauernfeind
Digital Support
Digital Support

Hello @estherl I believe only username and password are currently supported, but let me check in with our experts!

All the best,
Sonja 

Sonja_Bauernfeind
Digital Support
Digital Support

Hello @estherl 

I can now confirm that only username and password are supported as of right now. However, expanding this is on our team's radar, though we do not currently have any guarantee on when that would be added.

All the best,
Sonja 

estherl
Partner - Contributor
Partner - Contributor

Thanks @Sonja_Bauernfeind 

Dalton_Ruer
Support
Support

Just in case this helps anyone else ... If your Snowflake URL happens to contain a location name along with your company name ... be sure to use that whole thing as your Account Name

{Your Name}.us-east-1

If your Snowflake URL is just {Your Name}.snowflakecomputing.com then you are good to go with just your Account name. 

 

Version history
Last update:
yesterday
Updated by: