How to get started with Snowflake in Qlik Application Automation

[Ionut_Dumitrascu]

This article gives an overview of the available blocks in the Snowflake connector in Qlik Application Automation. It will also go over some basic examples of retrieving data from a Snowflake database and creating a record in a database.

Connector overview

This 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, 'ABC' if you 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

  • Account identifiers 
  • Connecting to Snowflake account 
    
    
• username -> username for the user that has remote access to the Snowflake database.
• password -> password used to authenticate the above username
• dbname -> the name of the database you want to use for this connection. You'll need to create multiple connections if you want to connect to multiple databases in the same automation.
• warehouse -> the name of the warehouse you want to use for this connection.

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, feel free to use the do lookup function for this.
   
   
   
   
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. See the below example on creating a new table.

1. Create a new automation
2. Search for the Snowflake connector in the left-hand side menu 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 right-hand side menu.
   
   
   
   
   
   
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);
   
   
   
   
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, feel free to 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 Object 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.

[estherl]

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]

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]

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]

Thanks @Sonja_Bauernfeind 

[Dalton_Ruer]

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.