MS SQL Canvas Data Database Generation and Load Scripts

[FelixHaeuptle]

I was recently tasked by the college leadership to figure out a way to get more data out of Canvas. How hard could that be right? After poking at the request for a few weeks like it was a dead rat, I decided to give Canvas Data a try. The result was the creation of two SQL scripts. Impressive right? The first file creates the database based on the JSON file that is part of the Canvas Data download. The second file creates the stored procedure that loads the unpacked Canvas Data files into the tables. The hardest part of the setup is getting the Canvas Data files using Canvas Data CLI but there are good instructions on that. Once the data has been downloaded onto a local server, the database creation script can be updated to point to the JSON file location and executed. It creates all the tables and columns and primary key indexes based on the provided schema. The second script creates the stored procedure that loads the tables. The code is documented (mostly) and explains how all this happens (mostly). This should shorten the development cycle for MS SQL shops significantly and the code could probably be adapted to other SQL environments. Hopefully this helps get people over the hump of creating the initial database. 

The CanvasDatabaseGenerationPublish.txt and CanvasDatabaseLoadPublish.txt files should have a .sql extension. They are SQL script files.  

If you see areas that I can improve please let me know. I like to learn new things and improve my skills as well as the code. Also, I know my spelling is horrible. I became a programmer because my spelling is so bad that it makes perfectly legible and functional code.

Cheers,

Felix

[Doug9]

Thank you @FelixHaeuptle appreciate you sharing your well written script.  My team would like to leverage the Canvas Data CLI to integrate both current course and also assignment grades from Canvas with our in-house SIS, and if we could bulk download our data and import into a SQL Server db that might work for us instead of querying various Canvas APIs.  We were able to adapt the first script to create the database and schema (CanvasDatabaseGenerationPublish.txt), but could not get the 2nd script for loading data to work in our Microsoft Azure environment.  The problem is how to connect our Canvas Data unzipped files from SQL Server Managed Instance database to where the files are in an Azure Storage Blob rather than a local drive.  We tried using a Blob Storage SAS key URL and also tried creating External Data Source with a Scoped Credential, but no go so far.  We're getting an access permission error but see no reason why.  We'll keep trying, but if anyone has advice they can share for this situation it is appreciated.  Thank you,

[FelixHaeuptle]

Hi Doug,

Just a heads up that Canvas Data 1 is end of life and from what I've been reading will be discontinued at the end of this year. You may want to look into Canvas Data 2 and invest your time there. I'm waiting for Canvas to grant me access on our tenant so I can start developing the next set of scripts. 😊

[tshea3]

I recently learned that Microsoft has a community sourced Azure pipeline for Canvas Data 1 at https://github.com/microsoft/OpenEduAnalytics. I plan on watching this space for a Canvas Data 2 pipeline or reaching out to help with it's development once I get some other projects wrapped up. But this should be better than starting from scratch if you need something for Azure like I do.

Thanks,

Travis Shea
University of Oregon

[nhumphries]

@Edina_Tipter do you have the current .json schema file or know where i can download it from?

[nhumphries]

@FelixHaeuptle Hi felix, thank you for sharing these scripts. I am having an issue with the create database file.

DECLARE @JSON VARCHAR(MAX)
SELECT @JSON = BulkColumn
FROM OPENROWSET
(BULK 'C:\Users\NHumphries\Documents\canvasdata2schema.json' , SINGLE_CLOB)
AS j

Msg 46548, Level 16, State 6, Line 134
'C:\Users\NHumphries\Documents\canvasdata2schema.json' contains an unsupported prefix. Retry with supported connector prefix.

Any ideas?

[FelixHaeuptle]

@nhumphries , I believe the error indicates that the json schema is malformed. Are you using the schema file that was downloaded from Canvas Data? If not, get the file downloads to work first and then try again with the schema that is part of the download set. If that does not work I'll have to scratch my head for a bit. Are you by any chance trying to set this up in Azure? 

[nhumphries]

@FelixHaeuptle Hi Felix, yes it is an azure based database. I downloaded the schema from here https://api-gateway.instructure.com/doc/?_gl=1*lx1szq*_ga*ODgzOTQwMzQzLjE2NjQ1NDU2NjQ.*_ga_75H5134F9...

Thanks

[FelixHaeuptle]

@nhumphries , I believe that may be the problem. You may have to create a blob storage in Azure and load the file from there. Azure cannot read directly from your local hard drive (that I am aware of). It's the issue Doug9 was working on above. Unfortunately I cannot help much with the process as I haven't tried running the scripts in Azure yet. 

[Doug9]

@nhumphries ,  we used Canvas Data 1 (as noted now being replaced by CD2), and we downloaded and unzipped our files with the Canvas Data CLI tool. Schema.json is included with the downloaded files. We got @FelixHaeuptle's scripts running in our Azure cloud environment yesterday. We used a combo of loading some things in temp tables (instead of reading file from storage) and connecting to an Azure Blob storage (for the unzipped data files) to replace the local drive/network storage locations in the scripts, and otherwise we used it as is. Checking with the db team to see what I can share with any sensitive info and access keys removed.

[nhumphries]

@Doug9 Hi Doug, that would be great. Many thanks

Nigel

[Doug9]

Thank you Felix, the scripts are helping us onboard with Canvas, @FelixHaeuptle 

 @nhumphries  ,  Here are our modified versions of Felix's scripts with changes to replace local/network drive with Azure cloud environment storage. 

CanvasDatabaseGenerationPublish-Azure.sql and CanvasDatabaseLoadPublish-Azure.sql

Our database team worked with the scripts interactively and with some help from Dr Google did the following to get them running for Azure:

0) xp_DirTree not available in Azure SQL Server - replaced with list of files.
1) Create a storage account and blob container - load unzipped CD1 files
2) Get the URL and Shared accesses signature to the location
3) Create a new empty database in Azure
4) CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential ( in the starting for create table script)
5) CREATE EXTERNAL DATA SOURCE RAJStorage( in the starting for create table script)
6) Use the attached SQL to create tables in the empty database
7) Create the stored procedure by add the URL and Shared accesses signature inside the procedure script
😎 Run the procedure to load the data