Connecting to Snowflake

Snowflake is an elastically scalable cloud data warehouse known for its data analysis and simultaneous access of data sets with minimal latency. 

Snowflake is one of the many database platforms that connect seamlessly with Provar Automation; however, unlike other built-in database connections, connecting with Snowflake takes additional steps to set up. 

Setting Up External Libraries

To ensure Provar can work smoothly with Snowflake, you will need to add the necessary external libraries that contain operations related to Snowflake. We also use Excel in the custom API.

Download the pre-bundled external libraries here:

If you’d like to get more up-to-date versions, you can find them on their respective websites.

• The Snowpark Library can be downloaded from the Snowflake Developer Resources.
  • Click on the Java button to download the latest version as a bundle zip file.
• Additional libraries used in the Custom Test Step
  • Apache Poi
  • Apache Commons

Start by Importing the JAR files to the /lib folder of your project.

Using the Custom Test Step

Download and unzip the Snowflake Connection custom test step in the zip file below.

Add the java file to the Custom Test Step folder. Start by navigating to the folder – you can right click on the Custom Test Step folder in Automation and visit the properties to jump to the right location.

To use the Custom Test Step, find the new functionality on the Test Palette under the “My Custom Test Steps” section. Like any other test step, simply drag it to your test case to use it.

The Snowflake Connection API is created using basic authentication. Values can be added directly in the test step or as variables. Adding authorization values to variables allows for overrides by environment and encryption.

Under the Authorization Details section, you will need the following to connect with your data:

• Snowflake Account : In your connection URL, this is the code before the “.snowflakecomputing.com”
• User Name : your login name
• Password : the login password
• Connection URL : change the “https://” in your connection URL to “jbdc:snowflake://” it should look something like this -> jdbc:snowflake://accountName.snowflakecomputing.com

Next, under the Database Details section, enter the information for the data you’d like to pull.

You’ll need the following information to query the database:

• Database Name : A named grouping of schemas
• Schema Name : A named grouping of database objects (tables, views, etc)
• Table Name : A specific collection of data
• SoQL Query : A SQL query to pull information from the specified table. Be careful of pulling too much information that may cause your connection to time out!

You can see from the Snowflake screenshot on the left that these values are nested.

Last, define where you want the query results to go. The Snowflake Connection custom test step is created to query the data and write the results to an Excel file so that information can be looped through or called in later test or test steps.

In the Result section, you’ll have to define the following:

• Filename to Store Results : the name of the result file
• Result Name : the variable name of storing data before writing it to the file
• Result Scope : defines the lifespan of the results

Viewing the Result File

After you finish designing your test, run the test case. To view your result file, navigate to the Files tab. Result files will show in the Templates folder of the Navigator. Click the link in the content window to open the file with the system editor to view the file in Excel.

Result files will show in the Templates folder of the Navigator. Right click and open with the system editor to view the file in Excel.

---

Feedback

Was this article helpful for you?

We really appreciate it!

Thank you for your feedback.

Tell us how we can improve this article.

No Yes