Connecting to Snowflake
Last Modified:
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 API
Start by adding the JAR files to the /lib of your project. You can drag them into the folder via the UI.
You can verify information has been added by opening the .classpath file in the Navigator.
Using the Custom API
Download and unzip the Snowflake Connection API in the zip file below.
Drag the SnowflakeConnection.java file into the src > customapis folder in the navigator.
Select the Copy Files option and click OK.
To use the custom API, find the new functionality on the Test Palette under the “My Cust API’s” 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. Add variables to Test Settings > Variables for authorization values. This 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 API 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
After you finish designing your test, run the test case. To view your result file, right click on the project level in the Navigator and refresh.
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.
For a video walkthrough, check out the video below: