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.

 

Step 3

 

Go to the solution Explorer and right click on the project file.

 

 

Step 4

 

Click on Manage Nuget Packages.

 

Step 5

 

Search for Snowflake Data as below.

 

 

Step 6

 

Click on Install button (refer to the above screen).

 

 

Step 7

 

Click on Ok

 

Step 8

 

Click on Accept button as per the below image.

 

 

Once the library got installed you can verify in the Solution explorer (refer to the below screen).

 

 

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:

using Snowflake.Data.Client;
using System;
namespace SnowflakeWithConsoleApp {
    class Program {
        static void Main(string[] args) {
            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";
            //Scenario 1. Get the list of employee from Snowflake View which returns Json data
            using(var conn = new SnowflakeDbConnection()) {
                conn.ConnectionString = connectionString;
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = "select * from employee_skill_json_view;";
                var reader = cmd.ExecuteReader();
                dynamic employeeList;
                while (reader.Read()) {
                    Console.WriteLine(reader.GetString(0));
                    employeeList = reader.GetString(0);
                }
                conn.Close();
            }
            //Scenario 2. Call the stored procedure employee_insert_json to insert to json data.
            string inputJsonData = @ "{"
            "employee_name"
            ": "
            "San"
            ","
            "employee_address"
            ": "
            "Hyderabad"
            "}";
            using(var conn = new SnowflakeDbConnection()) {
                conn.ConnectionString = connectionString;
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = "call employee_insert_json('" + inputJsonData + "'); ";
                var reader = cmd.ExecuteReader();
                dynamic resultData;
                while (reader.Read()) {
                    Console.WriteLine(reader.GetString(0));
                    resultData = reader.GetString(0);
                }
                conn.Close();
            }
        }
    }
}

Step 10

 

Run your application. You will be getting the below result for problem statement 

 

Screen for problem statement 1

 

 

Screen for problem statement 2 

 

 

 

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.

 

 

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!