DevoteamInsightsInput & output methods from Snowflake to Alteryx – An overview

Input & output methods from Snowflake to Alteryx – An overview

Share

Carlene Østergaard is a data wiz and certified SnowPro at Inviso by Devoteam

In my recent webinar, I discuss how using Snowflake and Alteryx together is like using the best of both worlds. 

Luckily, using them together has never been simpler, you use Snowflake as your database and Alteryx as you would normally. 

However, there are many ways you can connect input and output to snowflake (just as with every other database). In this blog post we will compare different methods. There is no best way, so you should test based on your data and use case. There is one worst way, so I will tell you which to avoid 😉 

First we will go over the options, then I will show some comparisons of the options being used on real data and lastly walk through how to get these set up in your Alteryx.

Input Options

There are two methods to input data from Snowflake to Alteryx:

Output Options

There are 5 (yes 5!!) ways to output data from Alteryx to Snowflake: 

There are 2 methods of outputting through the in-db tools. These tools work the best when using in-db tools for your entire flow. There are also 2 methods of outputting with the normal output tool. Lastly there is a custom output tool. Not only is it faster than the normal output tool, you have more options like Okta authentication, and updating records. 

Real tests of the different options

My conclusion from these tests

Use in-db tools if possible. Whether you use the in-db bulk uploader or the ODBC uploader, does not make too much of a difference. If you need to use normal tools, go for the custom snowflake output tool. 

You may notice in these examples that I am never testing the standard output with ODBC connection – this is because it is HORRENDOUSLY SLOW. What it does is write one line of data at a time to Snowflake – yawwwwnnn.  

How to set up the 2 inputs and 4 output connectors

(we exclude standard output with odbc, because zzz) 

Set up ODBC connection

Before diving into each of the options, use this guide to set up the ODBC connection on your PC. It is also possible to use a connection string, but find this to be easier. 

  1. Download ODBC driver
  2. Create new ODBC connector
    1. Click the windows button in the lower left corner of your computer
    2. Search for ODBC Data Sources (64 bit) and open 
    3. Click on the drivers tab, ensure you have one for snowflake
    4. Click on User DSN
    5. Click add
    6. Select the snowflake driver
    7. Complete configuration as below. All fields are case sensitive. The server should be the url for your snowflake account without https:// For example: xvdgsh.west-europe.azure.snowflakecomputing.com

In most instances, I like to create a Snowflake warehouse that I use for all of my Alteryx flows, therefore I am able to write the warehouse directly in the odbc connection settings. If this is not the case for you, you can leave it blank, and in your pre-sql in your input tool, you will need to write USE WAREHOUSE <WAREHOUSENAME>;

Input: In-DB ODBC

Use the In-DB tool as you normally would, with this connection string: 

odbc:DRIVER={SnowflakeDSIIDriver};UID=user;PWD=password;WAREHOUSE=name;DATABASE=name;SERVER=xxxxx.west-europe.azure.snowflakecomputing.com

Note: your url may be different based on what cloud your snowflake is hosted on. Look at your url for your snowflake and enter it here, without https://

Input: Standard ODBC

The input tool is very easy to use after the odbc is set up. Bring the input tool to your canvas and click on the little arrow to find your data source. Select databases -> Snowflake ODBC -> select the name of your odbc connection and enter credentials. From there it is the same as a normal SQL server when inputting.

Output: In-DB ODBC

You can use the same odbc connection string as input. When you are creating a new table, you need to write SCHEMANAME.TABLENAME.

**Note this configuration happens in the In-DB Input Tool**

Output: In-DB Bulk Uploader

You can use local bulk loading on versions 2020.3 and above. 

In the input indb tool, make the write connector say: 

snowbl:DSN=invisosnowflake;UID=DBA_USER;PWD=__EncPwd1__;WAREHOUSE=XSALTERYX_WH;Staging=local;Method=user

When writing a table name, make sure to write DATABASENAME.SCHEMANAME.TABLENAME.

**Note this configuration happens in the In-DB Input Tool**

Output: Standard Bulk Uploader

It is very easy to use the bulk uploader. Use a normal output tool and select databases -> Snowflake -> Bulk. Then you will be prompted to enter information like below. Choose your odbc connection, write your username and password. Select Local and User Stage. 

Output: Custom Snowflake Tool

Install the tool (one time only):

  1. Download the tool from Bob’s GIT: https://github.com/bobpeers/Alteryx_SDK_Snowflake_Output/
  2. Unzip it 
  3. Click on snowflake.yxi, Alteryx will open and click run
  4. It will take a few minutes to install
  5. Close and re-open Alteryx
  6. Now you will have a new Snowflake tool in your toolbar under connectors
  7. Drag this new snowflake tool to your canvas as you normally would an output tool

Configure the tool according to the instructions on the git page. The “Account” is part of your snowflake url. If my url is https://xvdgsh.west-europe.azure.snowflakecomputing.com, under account I would write xvdgsh.west-europe.azure. Account URLs are created differently based on which cloud platform your snowflake account sits on, but just follow this guideline and it will work! 

Example configuration: 

Now you know how easy it is to work with Snowflake and Alteryx! If you have any further questions, please feel free to reach out to info@inviso.dk.