Snowflake With C#.Net Core

Introduction

 
In this article, we are going to learn the concept of connecting Snowflake with .Net connector.
 
As we know, Snowflake is a cloud based data warehouse where we do a lot of ETL operations and visualize the data with the BI tools. Snowflake Supports semi-structured data such as Json, XML etc. If you are not aware the purpose of Snowflake, please go through the link here.
 
Not only loading and visualizing the data, we also have some real-time scenarios where we treat Snowflake as an API and write all the logic via stored procedure /function/ View.
 
As part of this article, I came up with the new problem statement mentioned below. Using .Net connector,
  1. Pull the list of employees from the Snowflake view which returns Json data
  2. Insert the Json data to the Snowflake table through Snowflake Stored procedure
Let us start to work on Connector. Before going to the .Net code side, I would suggest you go through the below article as a reference because I will be using the View and Stored procedure here.
 
View in Snowflake: link here.
 
In this article, I have created the View which will return the Json data. I will be using the employee_skill_json_view to pull the list of employees in C# application.
 
Stored Procedure in Snowflake: link here.
 
Here, I have created stored procedure employee_insert_json which accepts the Json data. It validates the data internally and inserts into the table if validation passes.
 

Snowflake Connector for .NET

 
Snowflake provides different connectors for Python, Spark, Kafka, .Net etc. In our scenario, we are focusing on Snowflake connector for .Net. To connect the Snowflake via .Net we use Snowflake.Data library. This library we can get from Nuget package. The library target is under .Net Framework 4.6 and .Net Core 2.1 and higher. Please use VS 2017 or VS2019 to work with this connector.
 
Now I believe you have enough information to start work on the below scenario
 

Scenario

  1. Pull the list of employees from the Snowflake view which returns Json data
  2. Insert the Json data to the Snowflake table through Snowflake Stored procedure

Solution

 
We are covering both problem statements in the same solution. To achieve that scenario, I have taken the .Net Core Console Application with Snowflake Connector. The framework is .Net Core 3.1. You can use .Net Core Web API or MVC anything to achieve this solution. But I am sure if you know in console Application, then you can implement in any .Net application. To start the implementation, perform the following steps.
 
Step 1
 
Open Visual Studio 2019
 
Step 2
 
Create the .Net Core Console Application in C#. You will end up with the below screen.

Snowflake With C#.Net Core
 
Step 3
 
Go to the solution Explorer and right click on the project file.
 
Snowflake With C#.Net Core
 
Step 4
 
Click on Manage Nuget Packages.
 
Step 5
 
Search for Snowflake Data as below.
 
Snowflake With C#.Net Core
 
Step 6
 
Click on Install button (refer to the above screen).
 
Snowflake With C#.Net Core
 
Step 7
 
Click on Ok
 
Step 8
 
Click on Accept button as per the below image.
 
Snowflake With C#.Net Core
 
Once the library got installed you can verify in the Solution explorer (refer to the below screen).
 
Snowflake With C#.Net Core
 
Step 9
 
Write the code and logic in Program.cs file.
 
You can copy the Main block code and Name space from the below code snippet. The very important thing is connection string. You must pass the following information inside the connection string.
 
a. Scheme=https
b. ACCOUNT= bea78282 (it is a snowflake account name which you will get after registering into the snowflake). if the Snowflake URL is “bea78282.us-east-1.snowflakecomputing.com”, then account name will be bea78282. Please change the connection string based on your account.
c. PORT=443
d. ROLE=sysadmin (Under which role you want run your SQL query). It is a Snowflake User role.
e. WAREHOUSE= compute_wh (Under which virtual warehouse you want to compute your SQL query). It is a warehouse name created under Snowflake account.
f. USER=Nitesh (give your Snowflake user name)
g. PASSWORD=XXXXX (give your Snowflake password)
h. DB=employeemanagement (the database name where you have table, views and stored procedure.
i. SCEHEMA=EM (schema name created under database in snowflake)
 
Code snippet below:
  1. using Snowflake.Data.Client;  
  2. using System;  
  3. namespace SnowflakeWithConsoleApp {  
  4.     class Program {  
  5.         static void Main(string[] args) {  
  6.             string connectionString = "scheme=https;ACCOUNT=bea78282;HOST=bea78282.us-east-1.snowflakecomputing.com;port=443; ROLE=sysadmin;WAREHOUSE=compute_wh; USER=nitesh; PASSWORD=XXXXXXX;DB=employeemanagement;SCHEMA=EM";  
  7.             //Scenario 1. Get the list of employee from Snowflake View which returns Json data  
  8.             using(var conn = new SnowflakeDbConnection()) {  
  9.                 conn.ConnectionString = connectionString;  
  10.                 conn.Open();  
  11.                 var cmd = conn.CreateCommand();  
  12.                 cmd.CommandText = "select * from employee_skill_json_view;";  
  13.                 var reader = cmd.ExecuteReader();  
  14.                 dynamic employeeList;  
  15.                 while (reader.Read()) {  
  16.                     Console.WriteLine(reader.GetString(0));  
  17.                     employeeList = reader.GetString(0);  
  18.                 }  
  19.                 conn.Close();  
  20.             }  
  21.             //Scenario 2. Call the stored procedure employee_insert_json to insert to json data.  
  22.             string inputJsonData = @ "{"  
  23.             "employee_name"  
  24.             ": "  
  25.             "San"  
  26.             ","  
  27.             "employee_address"  
  28.             ": "  
  29.             "Hyderabad"  
  30.             "}";  
  31.             using(var conn = new SnowflakeDbConnection()) {  
  32.                 conn.ConnectionString = connectionString;  
  33.                 conn.Open();  
  34.                 var cmd = conn.CreateCommand();  
  35.                 cmd.CommandText = "call employee_insert_json('" + inputJsonData + "'); ";  
  36.                 var reader = cmd.ExecuteReader();  
  37.                 dynamic resultData;  
  38.                 while (reader.Read()) {  
  39.                     Console.WriteLine(reader.GetString(0));  
  40.                     resultData = reader.GetString(0);  
  41.                 }  
  42.                 conn.Close();  
  43.             }  
  44.         }  
  45.     }  
  46. }  
Step 10
 
Run your application. You will be getting the below result for problem statement 
 
Screen for problem statement 1
 
Snowflake With C#.Net Core
 
Screen for problem statement 2 
 
Snowflake With C#.Net Core
 
 
Step 11 
 
To view your SQL query sending by .Net connector to the Snowflake, tou can go the history and verify the call. I have highlighted the Client Name in the below screen. If you are using .Net client then It will show .Net. If you are using Snowflake Web Interface, then it will show Snowflake UI.
 
Snowflake With C#.Net Core
 

Conclusion

 
You can write all the business logic into your stored procedure, View and Function and connect through Snowflake connector to perform any operation. Now in our case, we can bind the output data directly to the UI screen.
 
Hope you understand the concept of Snowflake Connector for .Net
 
Happy Learning!