[ARCHIVED] [ARCHIVED] To Redshift or Not to Redshift... that is my question

[rtcliffo]

Greetings Canvas Data Community,

Our online HS is exploring its options for setting up a data warehouse for the purpose of housing data from the Canvas Data Portal in addition to some other sources (survey results, student performance data from 3rd party tools). We currently have a small community of canvas users (~100 students and staff) but are anticipating significant future growth.

I'm curious to hear thoughts from those who are ahead of us in this process...

What do you see as the pros, cons and considerations when deciding to opt in to use Redshift via Instructure vs. choosing a different path (MS Azure SQL, Cloud SQL, MySQL, on-prem solutions, etc.)?

I am generally more experienced in report building using an existing RDBMS, as opposed to setting up the warehouse itself.

All advice from the data trailblazers before me is appreciated!

Rob Clifford

[brenden_goetz]

Hi rtcliffo‌ -

We use Redshift at the University of Colorado Denver, and I am happy to share some of my thoughts on the topic:

Pros

• Reduced Workload / Outsourcing DW management - someone else takes care of all the warehousing and hosting, so you can focus on reporting and analytics. It can be more efficient to outsource the modeling, updating data, dropping and recreating tables, server tuning and management, etc depending on your situation.
• Reliability - Amazon's services are usually available. Haven't had any downtime in my experience.

Cons

• Limited Redshift Capacity - my understanding is that we have a DC1 Large single node for our Redshift instance, which limits the amount of data that can be pulled in one query to 16GB. In most cases that is fine, but the Requests table - which houses lots of interesting information about user engagement - is huge in most cases (for us, going back to 2015, the table is about 1.7 billion rows). Having a smaller cluster/node may not be an issue in most cases, but it's worth being aware of. 

Considerations

•  How will you pull the data into your DW?  @wjones20 ‌ and his team at Eastern Michigan University created an application that would download all the Canvas Data flat files and insert them into tables in SQL Server. Here's the EMU GitHub repo for that. And here's another good post in the community about scripts for pulling data into MySQL.   @millerjm ‌ and  @James ‌ are two folks whom I have seen being fairly active in this realm.
• Do you need to integrate Canvas Data with other data sources? Some tools (e.g. Tableau) allow you to blend sources so it is easier to explore data across databases. Others might not. For example, having some data in MS SQL Server and some data in Redshift might mean that your queries in SQL Server won't be able to access the Redshift data.  
• Not really a consideration, but there is a nice GitHub repository with Hosted Data Query Examples.

Those are a few things that come to mind initially. I'll add more as it comes up, and hope to hear more from others!

[jdelcampo]

Hi Brenden,

We used CanvasDataViewer to import the data into our SQL SERVER DW, as you suggested, and worked wonders so far. Thank you very much for sharing it!

The only problem we have is that it doesn't import wellformed data and takes some garbage characters. We understand that this is because we are not using the correct encoding.

Reviewing the Stored Procedure "CanvasData_TableBuild_02_LoadImportTables", that is created with CanvasDataViewer, we saw that the BULK INSERT command is used to insert the data that comes in the txt files. We tried changing the parameters CODEPAGE and DATAFILETYPE but we still don’t solve it. Our database has this configuration: Collation = SQL_Latin1_General_CP1_CI_AS and Compatibility Level=SQL Server 2008 (100).

Do you have any advice for us so we can remove the garbage characters when inserting the data into our DW? Or how can we get in touch with William Jones and his team at Eastern Michigan University to give us some support?

Thank you. Regards!

Jimena del Campo

[brenden_goetz]

Hi Jimena -

Thanks for providing all the details! I haven't actually used the CanvasDataViewer myself and am not 100% positive of all its inner workings, so I can't speak from experience. Here are a few troubleshooting ideas though:

• First, I noted that your compatibility level is set to SQL Server 2008, but the CanvasDataViewer recommendations are 2012 or higher. Any options to upgrade or set a higher compatibility level?
• Are there any intermediary steps that the flat files are taking? Are they somehow getting corrupted/using a different character encoding than what is given in the source from Canvas?
• Might it be something with the character set being used in the SQL Server table or database? In the scripts community post James suggests using utf8 instead of latin1, and another post about errors importing data suggests the same thing.
• As for contact info, the EMU directory has entries for the two folks involved with the tool (William Jones and Andrew Anders). 
• As a last ditch effort, there are some options for removing or replacing characters after the fact - not ideal, but it's an option. I did a quick Google search and came up with plenty of hits, so you'll have to find the one that fits your situation. 

Let me know if you've made any progress on this!

[James]

Since writing that, I've since discovered utf8 is not sufficient for MySQL, which was the original context of my comment. We had a couple of tables that were failing import because certain emojis required 3 bytes rather than 2. For MySQL, I had to switch to multi-byte utf8 -- in MySQL parlance the utf8mb4 charset. Even if you're not using MySQL, you might be aware of the issue in case it works most of the time, but there are a couple of tables that have issues. I believe I found it in conversations and submission comments but a teacher could put an emoji in a quiz, so I just went ahead and switched everything for MySQL over to utf8mb4.

Along the same vein, the documentation for collation for MS SQL 2008 says that you want to be sure to use the correct data types. If you're used to latin1 and only having ASCII data, that page might help in eliminating the garbage (by making it recognizable). The section on Unicode says:

If you store character data that reflects multiple languages, always use Unicode data types (nchar, nvarchar, and ntext) instead of the non-Unicode data types (char, varchar, and text).