Is it possible to get the exact time of the four hour interval our DAP Data Refresh happens at?

We'd like to time our syncdb commands so we know what time index we're pulling down data FROM.

Currently we're doing it once every 24 hours automatically, but we occasionally have need, in a given business day, to sync changes that have been made and it would be very helpful to know if we're going to be getting data from (to use proverbial examples) 5 minutes ago (because the Data Refresh just happened) or from 3 hours and 55 minutes ago (which will likely not reflect the changes we want to see, but WOULD if we waited just a bit longer to sync)

The issue here is that if we know when it's refreshed in the cloud, we can time our syncs down to only specific windows and perform on-demand syncs less frequently and to greater effect.

Find more posts tagged with

Canvas Data 2

dap

Accepted answers

stimme

@IWCC_MTC Another query you might try is "SELECT timestamp FROM instructure_dap.table_sync WHERE source_table = [table_of_interest] AND source_namespace = [relevant_namespace];"

I believe the value there is the maximum meta.ts value of all rows upserted on that table the last time syncdb ran. It's reasonable to suppose that that table's records are up to date through that timestamp.

All comments

LindaWilin1

We posed a similar question to CD2 support when we began using it… this is my postgreSQL version of their answer 'Select * from hd_audit_log
Order BY Started_processing DESC;' The report shows the Canvas and Catalog Sync Processing Start time and end time in HH:MM:SS.

Hope that helps.

IWCC_MTC

Ooh, that could work. I'm assuming I pull that down with an initdb?

What namespace is hd_audit_log in?

canvas_logs ?

LindaWilin1

I'm using PGAdmin4 and that is literally the only 2 lines of the code I use to get that report. I've found that PGAdmin4 makes PostgreSQL easier than straight SQL when I tried to write some reports in SQL that were originally coded in PostgreSQL (and I'm new to both). Unfortunately I don't know what namespace the hd_audit_log is in. Hopefully someone with more experience can assist us further.

IWCC_MTC

Yeah, the DB I'm putting all of the data into doesn't have an hd_audit_log table under any of the schemas that get pulled down with the dap commands I'm running, so I'll have to figure out where it lives so I can grab it.

stimme

@IWCC_MTC Another query you might try is "SELECT timestamp FROM instructure_dap.table_sync WHERE source_table = [table_of_interest] AND source_namespace = [relevant_namespace];"

IWCC_MTC

That looks to be the case, as that timestamp is off by 90 minutes from the last time I performed a manual sync.

Thanks so much everyone!

Unanswered Questions

What is uninitialized workflow_state value for new_quizzes.quizzes in Canvas Data?
On the new_quizzes.quizzes table, all of my institution's records have either "unpublished" or "uninitialized" values in the workflow_state column. The documentation for this type lists "published" as a possible value. It seems like all of my institution's published New Quizzes have the "uninitialized" value. What does…
EDUCAUSE Insights: Data and Decision-Making
EDUCAUSE is full of inspiration, whether you’re onsite or following from afar. Join the evolution by sharing what sparks your curiosity, the questions you have, and the insights you discover. We'll learn from each other, expand our networks, and strengthen our Community. Your perspective matters, and the more we share,…
Problems with GraphQL in User Pageviews reports
Prompted by the most recent Canvas release notes, we looked into the Page Views feature, and we were happy with the new documented changes. However, we notice that more user actions are done by GraphQL API, which does not provide details in the page view report, either in the GUI or in the CSV file download. We and most…
Metrics Easy Button
I am currently teaching five courses this semester with more than 70 students. To track their progress, I currently have to review each course and section individually, which is very time-consuming. Ideally, I would like to consolidate key information for all students into a single Excel file—showing class attendance…
Analytics API / Metrics calculation logic
Hello, We are currently using the Analytics API provided in the Instructure Developer Documentation Portal to retrieve certain metrics-related data. We would like to know if it’s possible to replicate these datasets using the Canvas Data Platform (CDP), instead of relying on API calls. For example, could you please advise…