[ARCHIVED] Questions about getting up and running with Canvas Data

[awilliams]

I have been working with one of our Analyst in Institutional Research to try and get our data into database software we can work with to start evaluating what all is there and what we can do with it. For background, we are on the free tier. With that said, hopefully, someone can help me provide her with some information she is seeking. Here's her latest list of things she thinks we need to get up and running. (I am wondering if she found and followed instructions for the paid tier with data hosted in Amazon Redshift.)

Adam,

Finally got some time to dig into this!

I have located and downloaded the necessary ODBC drivers for Amazon Redshift.

To actually get this going we will need:

1.       Server – this is the cluster configuration connection string

2.       Database – name of the redshift database

3.       User – login name

4.       Password

5.       SSL Authentication mode.  Probably either

a.       Verify-ca

b.      Verify-full

6.       Data Type Options (these are the defaults)

a.       Unicode – enabled

b.      Show Boolean Column as String – enabled

c.       Text as LongVarChar – enabled

d.      Max Varchar – 255

e.      Max LongVarChar – 8190

f.        Max Bytea - 255

They may also have advice about which one of these options is preferred:

·         Single Row Mode

·         Use Declare/Fetch

·         Use Multiple Statements

·         Retrieve Entire Result into Memory

[James · Accepted solution]

awilliams​,

It sure sounds like she found RedShift stuff because none of that rings a bell with what I've been doing with the free (no-extra-cost) version. Also, the mention to RedShift is a give-away

Anyway, I just wrote a blog post Canvas Data API Authentication that explains how to create the HMAC signature for the API calls so that you can start to automate the download process and not have to sit there and click on 50 files at a time. There are links at the bottom to working PHP and PERL code from my Canvancement site that do all the API work for you.

I also have, used locally but not ready for publication, PHP code that will:

• create a MySQL database from the schema found in the API
• generate wget statements that can be executed on a server to download all of the files for a single dump
• scan a folder for files matching the correct pattern, gunzip them, and then import them into the MySQL database
• generate a tab-delimited header row that could be added to the existing data files in case they're small enough and someone wanted to use Excel

I don't have experience with OLAP and right now, we're more interested in drilling down to generate reports rather than looking at aggregate data, but those are areas to pursue in the future.