Downloading Blackboard Data from Snowflake DB in .NET

Blackboard Data provides access to data about activity on your Blackboard LMS via the Blackboard reporting stack. The data is stored in a Snowflake data warehouse instance. Here you can query the data directly via the Snowflake console or connect a reporting tool such as tableau or Power BI. But what if you want to export the data out of snowflake, for example to an institutional data warehouse. There are number of ways to do this but .NET is my preferred platform so that is what I’ll be using. The Snowflake documentation on this makes the task look quite confusing- but actually it’s very straightforward. Here’s how to connect to and download from Snowflake to an on premise SQL Server instance.

There is Snowflake Connector for .NET available as a nuget package which provides everything we need a connect to Snowflake so start by creating a new Console App project in Visual Studio and adding it in the package manager.

Install-Package Snowflake.Data -Version 1.1.4

 

Then include the following namespaces:

using System;
using System.Data;
using System.Data.SqlClient;
using Snowflake.Data.Client;

First create a Snowflake connection and set the connection string to target your Blackboard Data Warehouse. All the possible properties of the connection string are documented here but here’s what we need in this case:

Property	Example	Description
account	xx99999	The snowflake instance to connect to. This is the first part of the host URL
host	xx99999.eu-central-1.snowflakecomputing.com	The full snowflake host name
user	BBDATA_USER	The snowflake user to connect with
password		The user password
db	BLACKBOARD_DATA_4B0139E...	The database to connect to. You can login to the Snowflake Console and run: select current_database() to get this value
warehouse	BLACKBOARD_DATA_WH	The warehouse to connect to. You can login to the Snowflake Console and run: select current_warehouse() to get this value

Below we create the snowflake connection instance, set the connection string and open it.

SnowflakeDbConnection snowConn = new SnowflakeDbConnection()
snowConn.ConnectionString = @"account=xx99999;host=xx99999.eu-central-1.snowflakecomputing.com;user=BBDATA_USER;password=pass123;db=BLACKBOARD_DATA_4B0139E...;warehouse=BLACKBOARD_DATA_WH";

snowConn.Open();

Next create a query command and execute it against a data reader. Here we are selecting all the terms data.

IDbCommand cmd = snowConn.CreateCommand();
cmd.CommandText = "select ID, NAME, DESCRIPTION from cdm_lms.term";
IDataReader reader = cmd.ExecuteReader();

Finally create a connection to a SQL Server instance and use sql bulk copy to copy the contents of the data reader to a table. The table has to exist on the target database – for the terms data this would be:

CREATE TABLE terms
(
  ID NUMERIC(38,0)
, NAME VARCHAR(MAX)
, DESCRIPTION VARCHAR(MAX)
)

Here we open a connection to a local SQL Server instance and an instance of bulk copy to connect to that server. Then just execute the bulk copy write to server against the data reader and the result set will be streamed to the table: term. 

SqlConnection devConn = new SqlConnection("Server = localhost; Database = dev; Trusted_Connection = True;")
devConn.Open();

SqlBulkCopy bcp = new SqlBulkCopy(devConn)
{
DestinationTableName = "term",
EnableStreaming = true,
BatchSize = 10000,
BulkCopyTimeout = 0
};

bcp.WriteToServer(reader);

And that’s it. The query and download speeds are excellent and since all records include reliable timestamps for inserts, updates and deletes it would be easy to create an incremental extract as well. Below is the full code.

using System;
using System.Data;
using System.Data.SqlClient;
using Snowflake.Data.Client;


namespace Snowflake_Connector
{
    class Program                                                                              
    {
        static void Main(string[] args)
        {

            //Snowflake Source Connection
            using (SnowflakeDbConnection snowConn = new SnowflakeDbConnection())
            {


                // Snowflake BlackBoard Source Connection String
                snowConn.ConnectionString = @"account=xx99999;host=xx99999.eu-central-1.snowflakecomputing.com;user=BBDATA_USER;password=pass123;db=BLACKBOARD_DATA_4B0139E...;warehouse=BLACKBOARD_DATA_WH";
                snowConn.Open();

                IDbCommand cmd = snowConn.CreateCommand();
                cmd.CommandText = "select ID, NAME, DESCRIPTION from cdm_lms.term";
                IDataReader reader = cmd.ExecuteReader();

                // SQL Destination Connection
                using (SqlConnection devConn = new SqlConnection("Server = localhost; Database = dev; Trusted_Connection = True;"))
                {
                    devConn.Open();

                    SqlBulkCopy bcp = new SqlBulkCopy(devConn)
                    {
                        DestinationTableName = "term",
                        EnableStreaming = true,
                        BatchSize = 10000,
                        BulkCopyTimeout = 0
                    };

                    bcp.WriteToServer(reader);

                }

            }
        }
    }
}