Community

sam_mcknight · ‎09-09-2022

You nailed it! I can't believe I overlooked the weekend refreshes as the cause. We've been doing automated regression testing of our data integrations over the weekends on the beta site.

sam_mcknight · ‎09-07-2022

With OAuth2 authentication, you receive an access token and a refresh token. I know the access token is valid for an hour and after it expires you use the refresh token to obtain a new access token. How long is a refresh token valid for before it is no longer valid? I ask because per this article (https://community.canvaslms.com/t5/Canvas-Developers-Group/Working-with-Refresh-Tokens/m-p/84667#M1383) refresh tokens are permanent unless you go through the OAuth2 process again. However, I am running in to an error that seems to indicate that isn't the case. I haven't nailed down an exact timeframe but it seems to be about 24 hours after running through the initial OAuth2 process. Error message: {"error":"invalid_grant","error_description":"refresh_token not found"}

sam_mcknight · ‎08-15-2022

Are there any APIs we can use to check system health and service availability like what is shown on the status.instructure.com page? We are hoping to have our internal system monitoring tools monitor the health and notify us when an Instructure service is unavailable.

sam_mcknight · ‎04-02-2020

The past two mornings, a subset of files in the data export contain data from the wrong tables. Later in the day, we notice that the exports have been corrected. We use the CLI tool to download and unpack the data exports. Is this something others are seeing? Examples Wednesday April 1 at 3 AM - the unpacked submission_dim.txt file was an exact copy of submission_fact.txt. It contained all the data and headers that should have been in the submission_fact.txt. Pseudonym_dim.txt was an exact copy of the pseudonym_fact.txt file. The submission_fact.txt file contained the data from the submission_file_fact table. By 9:45 AM when I reran the fetch through the CLI, the data files had been corrected. Thursday April 2 at 3 AM - The unpacked account_dim.txt file, usually 57 KB, was 1.4 GB and contained all the data from the wiki_page_dim table. By 10:45 AM when I reran the fetch through the CLI, the data files had been corrected. We are submitting a support ticket too but I wanted to throw it out to the group here and see if others are having the same trouble.

sam_mcknight · ‎07-22-2019

Once the Canvas CLI has downloaded the gz files for the requests table, there is an alphanumeric identifier appended to each file name. I'm working on a way to shorten our data load time on the requests table into our local db. Can I rely on the alphanumeric value appended to the filename to be universally unique and not ever repeated for a file containing different data? If so, I will use the filename as a primary key/unique constraint to tell me which files I have already successfully loaded. For example with a file named requests-00000-9b157498.gz, is 9b157498 ever going to be repeated for the requests gz file for a completely different set of data? I know I'll see the same file for a few weeks during the download window but will it be used for a different gz file say in a year or two?

sam_mcknight · ‎12-22-2018

When we implemented Canvas Data in Oracle, I came across a DDL script online for MySql. It required some tweaking to deal with Oracle-specific limitations (table name and column name lengths) as well as some data type changes (longtext-->clob) but that script was a lifesaver. I wish I still had that link. But check out canvancement/canvas-data/mysql at master · jamesjonesmath/canvancement · GitHub for some php code that will generate the MySql DDL from schema.json. I haven't checked it to see if it's using the current schema.json but it should get you started in the right direction.

sam_mcknight · ‎06-15-2018

While I can't speak authoritatively, I do not believe there is logic in the naming structure, at least nothing that a person can interpret; it looks like a GUID or a hash value. Instead of finding the specific file packed file, I would recommend using the CLI tool to download and unpack the request files. That will create one consolidated txt file which you can load into any database and query the data from there. I say that fully realizing that the process is easier to write about than to accomplish. The CLI tool though is really easy to use. The final unpacked requests.txt file will be huge. And if the enrollment happened more than a few weeks ago, you'll have to wait for the monthly full data dump rather than the incremental dump that happens daily with the requests table.

sam_mcknight · ‎04-30-2018

When was the user added to the course? I ask because Canvas Data is 24-36 hours older than the production system via the API. If you are querying a current semester, it's possible the user was recently added and hasn't made it to the Canvas Data export yet.

sam_mcknight · ‎03-21-2018

Same here. About 70% of our account_dim table is missing. I haven't checked the others.

sam_mcknight · ‎01-26-2018

That data is not included natively in the Canvas Data product. There's a report in Canvas called Student Analysis. We use the REST API to download the quiz report and put the data into our own table. It has worked well for us.

sam_mcknight · ‎12-01-2017

In the v1.17.2 schema docs, the descriptions for submission_comment_participant_dim and submission_comment_participant_fact both say [Deprecated] This table is based on a table in the canvas application that no longer exists. It will be deprecated in the next major release of Canvas Data. However, I see many new records in both tables each day. If the canvas application functionality no longer exists, how are records getting into those two tables?

sam_mcknight · ‎12-01-2017

Thanks for the detailed reply. I hadn't considered filtering out record we'll never look at, such as the null user_id. We do use the api URLs like you said, for determining if it's a mobile app or not. I went with NVARCHAR for the string data type to deal with other character sets but lost half the storage capacity because it's 4 byte instead of 2 byte, so I don't know if I really gained anything with it or not. I ditched the CLOB; we use Tableau and Tableau does not work with CLOBs at all. I don't know if this will help you out but we went with SQL Loader. I originally had a script that would take 12-18 hours. SQL Loader takes about 20 minutes. It goes into a staging table where we do a bit of transformation. Then compare the staging table to the live table to only insert records that are missing. ETL has cut down to 45 minutes total.

sam_mcknight · ‎12-01-2017

What database platform do you use Stuart? I anticipate being at 400 million records within a year or two and I want to plan ahead for the capacity and performance needs.

sam_mcknight · ‎12-01-2017

Stuart - It looks like it was a correcting dump. Apparently our files for the past few months had not been including all the current records so now we're getting them. Starting around August we were only getting a small percentage of the daily records. This was actually good because despite a tripling of courses and enrollments this semester, we noticed a 75% drop in online usage when looking at the requests table. We were stuck trying to explain the usage decline. Now that we have the missing requests records, our usage trends match the enrollment trends. I did have to spend a bunch of time rewriting the ETL process into Oracle though. Getting that many records in every day meant doing newer optimizations I hadn't anticipated needing.

sam_mcknight · ‎11-10-2017

What is the date range you are covering? I'm sure you've already done this but I just wanted to confirm that you adjusted your time field to PDT and didn't simply make the column header say PDT. For future question visitors, all times in Canvas Data are GMT.

sam_mcknight · ‎11-10-2017

We've had Canvas for a few years and have been working with Canvas Data for a while now. At the start of November, our requests table had 44 million rows and great at a rate of about 250K rows per week. Sometime around November 3, the size of the table exploded to 87 million rows. On November 2, there were about 44 million rows. What could cause the requests table to grow by 43 million rows in just one day? Our upload process has come to a screeching halt because of the shear size of the table now. Has anyone else experienced this before?

sam_mcknight · ‎11-08-2017

You probably already know this but for other people coming across this question...when comparing the Canvas Data to live data in Canvas, you need to remember that Canvas Data is a delayed snapshot. If you compare scores, make sure you are comparing students whose grades haven't changed in the past few days.

sam_mcknight · ‎10-27-2017

Let's start with your school's definition of eligibility. Should be easy to translate that definition into a query from whichever tables hold the data needed.

sam_mcknight · ‎10-27-2017

Not exactly broken. The API is marked as being in beta. I got around this by writing a script that will download the csv quiz item analytics for me. I then combine those csv files and do my analytics. The URL to use is /api/v1/courses/{CourseId}/quizzes/{QuizId}/reports. Add a form parameter to the header : quiz_report[report_type]=student_analysis. You will get back a Json object and two of its members are a filename and report id. Use the report id to go to /api/v1/courses/{CourseId}/quizzes/{QuizId}/reports/{ReportId}. This will then return the downloaded csv file. You can do this in any language that allows http calls. In Python, it's really easy. You'll need to append your access_token as a URL parameter for each of these calls.

sam_mcknight · ‎05-15-2017

Andrew or Stuart - Any suggestions on how to convert it to the user's timezone that is in their user_dim record? The timezone string stored in Canvas doesn't seem to match up with what Oracle expects in the conversion function, but maybe I'm missing something. What we want to look at is not the UTC time but the time the user interacted at their local time. We have users across the US and knowing their local time will help analyze usage patterns.

Post Details	Date Published	Views	Likes
How long is a refresh token valid? With OAuth2 authentication, you receive an access token and a refresh token. I know the access token is valid for an hour and after it expires you use the refresh token to obtain a new access token. ...	09-07-2022	632	0
API for system health status Are there any APIs we can use to check system health and service availability like what is shown on the status.instructure.com page? We are hoping to have our internal system monitoring tools monitor...	08-15-2022	273	0
Data export files have data from wrong tables. The past two mornings, a subset of files in the data export contain data from the wrong tables. Later in the day, we notice that the exports have been corrected. We use the CLI tool to download and u...	04-02-2020	665	0
Are unpacked request table filenames unique Once the Canvas CLI has downloaded the gz files for the requests table, there is an alphanumeric identifier appended to each file name. I'm working on a way to shorten our data load time on the reque...	07-22-2019	896	0
submission_comment_participant dim and fact In the v1.17.2 schema docs, the descriptions for submission_comment_participant_dim and submission_comment_participant_fact both say [Deprecated] This table is based on a table in t...	12-01-2017	482	0
How did our requests table get so big? We've had Canvas for a few years and have been working with Canvas Data for a while now. At the start of November, our requests table had 44 million rows and great at a rate of about 250K rows per we...	11-10-2017	1223	0
Count assignments using Turnitin external tool Most of our external tools are activated at the root account level. The question I have been asked is how many assignments use a particular external tool (in this case, Turnitin). So there is an assi...	03-27-2017	2290	4

Posts	24
Solutions	2
Likes given	18
Likes received	21
Member Since	‎03-02-2017

Community

Have a Canvas Question?

Have a Mastery Question?

Have an Elevate Question?

Have an Impact Question?

Re: How long is a refresh token valid?

How long is a refresh token valid?

API for system health status

Data export files have data from wrong tables.

Are unpacked request table filenames unique

Re: Schema JSON to Oracle DB DDL

Re: Logic of Request filenames i Canvas Data?

Re: Get Same "Current Term" as API Using SQL To Ca...

Re: Missing data

Re: Is there a way to query the score for each qui...

submission_comment_participant dim and fact

Re: How did our requests table get so big?

Re: How did our requests table get so big?

Re: How did our requests table get so big?

Re: Peculiar drop in requests

How did our requests table get so big?

Re: Canvas Data: Missing Data in score_fact and su...

Re: Canvas Data - Tableau student eligibility repo...

Re: Get student question answers submitted via API

Re: How can i automatically change the time zone i...

Canvas Developers Group