In a business world increasingly moving towards cloud-based solutions, the Snowflake Data Cloud is one of the largest and fastest-growing vendors in the market. With high-speed database connections and elastic warehouse sizing, Snowflake offers clients an excellent ecosystem for storing and utilizing their data.
Using a cloud database offers many advantages, including scalability, data governance, and security. However, to use Snowflake effectively, you must be versed in SQL (Structured Query Language).
While learning SQL is a highly valuable skill for any data analyst, what options are there for those who either do not know SQL or don’t have direct access to Snowflake?
The answer is KNIME Analytics Platform, a free and open-source data preparation and data science tool.
KNIME uses a simple drag-and-drop interface that allows users to connect to data quickly and perform necessary data manipulations. KNIME now offers a built-in Snowflake connector node, which allows KNIME developers to connect to Snowflake tables and work with them—all without the need to know SQL.
In this blog, we will show you how to use KNIME to connect to a Snowflake database.
How to Connect to Snowflake with KNIME
We assume you have already downloaded KNIME and have a basic understanding of its user interface. If you aren’t there yet, don’t worry! KNIME has published several blogs to help you get started:
Installing the Snowflake Extension
KNIME does not automatically include certain extensions by default to keep the installation size to a minimum. To connect to Snowflake, you must first add the KNIME Snowflake Integration extension. This extension (which is completely free) can be added by following the steps below:
- Navigate to File → Install KNIME Extensions...
- In the menu that appears, type snowflake in the search bar along the top.
- You may need to expand the folder labeled KNIME & Extensions. In there, you will see two extensions. Select the one labeled KNIME Snowflake Integration.
- Press the Next button to navigate to the installation details page.
- Press the Next button to navigate to the licenses screen. Accept the terms of the license agreement.
- Press Finish.
At this point, KNIME will begin installing the extension. You can see the progress along the bottom-right corner of the window. Once the extension is installed, you will be prompted to restart KNIME.
Congratulations, you have added the Snowflake integration extension!Â
Connecting to Snowflake
With KNIME restarted, search for Snowflake in the Node Repository. Depending on which extensions you have or have not installed, you may see a different set of nodes.
Find the Snowflake Connector node and drag it onto the workflow editor. Double-click on the node to open its configuration menu.
The configuration menu can look daunting; there are seven tabs along the top and the first tab, Connection Settings, has thirteen required and optional fields. Below, we will go through them briefly:
- Database Dialect: This is set to Snowflake and cannot be changed.
- Driver Name: Currently, this is set to Snowflake 3.13.4 and cannot be changed.
- Full Account Name: The account name that identifies a Snowflake account within an organization. If you need help finding this, try reading this blog or contacting the Snowflake administrator in your organization.
- Virtual Warehouse: This can be found in the Snowflake user interface under the Warehouses menu.
- Default Access Control Role (optional): Access control privileges determine who can access and perform operations in Snowflake. This value can be found in the upper-right corner under your username.
- Default Database (optional): Identify which database the Snowflake connector should automatically connect to.
- Default Schema (optional): Identify which schema within the above database the connector should automatically connect to.
- Authentication
- None: Select this if you have no authentication credentials when connecting to Snowflake, such as if you use SSO.
- Credentials: Select this if you provide username and password credentials in the workflow itself.
- Username: Provide the username that you use to log in to Snowflake. Select this option if you use key pair authentication to sign in.
- Username & Password: Provide the username and password that you use to log in to Snowflake.
If you select the Username option above, you may need to perform one additional step before finalizing the connection.Â
- Navigate to the JDBC Parameters tab along the top of the configuration menu.
- At the bottom of the window, press the Add button.
- Type “authenticator” in the left box and “externalbrowser” in the right box in the new row that appears.
If you entered all connection criteria correctly, you can now connect to Snowflake! Press OK to close the configuration menu.
Once back on the workflow editor, execute the workflow by pressing F7 or the green execute button. If you use SSO, an external browser window will open, and you will select the account associated with your Snowflake login.
If you did everything correctly, the Snowflake Connector node will display a green light, indicating a successful execution
3 Tips for Connecting to Snowflake in KNIME
- If you are not familiar with connecting to Snowflake or what credentials to provide, contact your Snowflake administrator and they should be able to provide you with the necessary information.
- If you need further support, read KNIME’s description of the Snowflake Connector node.
- Once you have successfully connected, use the DB Table Selector node to select a table and the dozens of DB nodes (such as DB Row Filter or DB GroupBy) to create the data set you need.
Closing
Not everyone has the means or ability to write complex SQL queries. Using low-code, drag-and-drop tools like KNIME can make complex data wrangling much easier, faster, and more accessible to colleagues in your organization.
If your data is stored in Snowflake, using KNIME to connect to it is effective and simple; KNIME’s Snowflake Connector lets users access their Snowflake data in mere minutes. Further, once you have established a successful connection, you can perform highly complex data manipulations without SQL.
Looking for more help with Snowflake or KNIME? Our team of experts can help!
