Snowflake With C#.Net Core

Introduction

In this article, we are going to learn the concept of connecting Snowflake with a .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 of 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 into the Snowflake table through the Snowflake Stored procedure

Let us start to work on the 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 the C# application.

Stored Procedure in Snowflake: link here.

Here, I have created the stored procedure employee_insert_json which accepts the JSON data. It validates the data internally and inserts it 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 a Snowflake connector for .Net. To connect the Snowflake via .Net we use Snowflake. Data library. This library we can get this from the 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 into the Snowflake table through the 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 about console Application, then you can implement it 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 the Install button (refer to the above screen).

Step 7. Click on Ok

Step 8. Click on the Accept button as per the below image.

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

Step 9. Write the code and logic in the Program.cs file.

You can copy the Main block code and Namespace from the below code snippet. The very important thing is the connection string. You must pass the following information inside the connection string.

• a. Scheme=https
• b. ACCOUNT= bea78282 (it is a snowflake account name that 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 to 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 the 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)

The code snippet is 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 the problem statement

Screen for problem statement 1

Screen for problem statement 2

Step 11. To view your SQL query sent by the .Net connector to the Snowflake, you can go to the history and verify the call. I have highlighted the Client Name in the below screen. If you are using a .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!