Most Efficient Way to Auto-Sync Canvas Data With MySQL Database?

[brett_sheeran]

Hi,

I'm looking for the most efficient way to auto-sync Canvas Data with a MySQL database

Any suggestions?

Details as follows.

MySQL is on a Linux instance. We don't require all data, I gather request table is huge. At the moment we need maybe a dozen tables.

Currently, I've installed this library:

Canvas Data Cli

https://github.com/instructure/canvas-data-cli

And am manually running these Bash & SQL commands

canvasDataCli fetch -c config.js -t enrollment_term_dim

canvasDataCli unpack -c config.js -f enrollment_term_dim

delete from enrollment_term_dim;

LOAD DATA LOCAL INFILE '/opt/canvas-data/unpackedFiles/enrollment_term_dim.txt' INTO TABLE enrollment_term_dim FIELDS TERMINATED BY '\t' ESCAPED BY '\b';

However, I'm concerned that delete all is rather a brute force approach. I have searching for various alternatives. However, while I see plenty of alternatives, I'm not sure which might best meet our needs:

Canvas Data Loader

https://github.com/instructure/canvas-data-loader

Canvancement

https://github.com/jamesjonesmath/canvancement

Py Canvas Data

https://github.com/kajigga/py_canvas_data

Canvas Data SDK

https://pypi.python.org/pypi/canvas-data-sdk/

Any suggestions?

Regards

Brett S

[millerjm]

Hi Brett,

We are doing something similar but with more than 3 tables (ignoring requests).  I use canvasdatacli to fetch the day's tables and I have a bash script that I use to bring in the non-requests tables that we are currently using.  

Since the daily files are complete replacements of the previous day's files, at the moment I'm truncating each of the MySQL tables prior to import using MYSQL LOAD DATA LOCAL INFILE to load the data into the table.  In the future I might do REPLACE INTO vs. truncating but it's not imperative right now that I have the tables populated while the data loads. 

You could simply add the word replace into your load data query and see how your data looks instead of truncating. 

LOAD DATA LOCAL INFILE '/opt/canvas-data/unpackedFiles/enrollment_term_dim.txt' REPLACE INTO TABLE enrollment_term_dim FIELDS TERMINATED BY '\t' ESCAPED BY '\b';

[brett_sheeran]

Hi Joni,

Thanks for your reply.

I tried a "REPLACE INTO" on assignment_dim and it updated successfully 🙂

However, it did raise another question: how often to records get deleted from the canvas_data database?

I ask because I presume that just because a record is no longer in the import file that doesn't mean it will get deleted from the destination database. However, that may not be a problem for Canvas.

Thanks.

Cheers

Brett