Go Back
Canvas REST API Reader - Retrieve Last Modules in Courses
Publisher
: Lingk
Run In Lingk
Description
This recipe retrieves the last module in each course using Spark SQL windowing. With this data you can retrieve all course completion data for any enrollment.
Browse the knowledge base
Twitter
E-Mail
# _____ _ _____ __ # | __ \ (_) |_ _| / _| # | |__) |___ ___ _ _ __ ___ | | _ __ | |_ ___ # | _ // _ \/ __| | '_ \ / _ \ | | | '_ \| _/ _ \ # | | \ \ __/ (__| | |_) | __/ _| |_| | | | || (_) | # |_| \_\___|\___|_| .__/ \___| |_____|_| |_|_| \___/ # | | # |_| # # Project Name - CANVAS REST API READER - RETRIEVE LAST MODULES IN COURSES # Recipe URL - https://app.lingk.io/a/10932/tf/17547 # Description - This recipe retrieves the last module in each course using Spark SQL windowing. With this data you can retrieve all course completion data for any enrollment. # Industry - Higher Ed # Business Process - Graduate Reporting # Systems - Canvas # Connectors - Canvas # Data Flows - Single Direction # Connection Type - API to API # Data Models ## Canvas Models Used: #1. Accounts #2. Courses # Add Recipe notes / Change log information here! # _____ _ # / ____| | | # | | ___ _ __ _ __ ___ ___| |_ ___ _ __ ___ # | | / _ \| '_ \| '_ \ / _ \/ __| __/ _ \| '__/ __| # | |___| (_) | | | | | | | __/ (__| || (_) | | \__ \ # \_____\___/|_| |_|_| |_|\___|\___|\__\___/|_| |___/ # # CONNECTORS specify what data will be pulled into the in-memory database during processing connectors: # Configure Canvas credentials in your Environment before running this recipe # Canvas Setup - https://help.lingk.io/en/articles/263-canvas-setup-guide ###### Start - Canvas Reader connectors ###### - name: canvasCourses type: canvasReader properties: path: api/v1/accounts/1/courses?completed=false&with_enrollments=true&course_format=online pageSize: 100 - name: canvasCoursesByName type: canvasReader properties: path: api/v1/accounts/1/courses?search_term=PD-HCO-2017-DEMO pageSize: 100 - name: canvasUsers type: canvasReader properties: path: api/v1/accounts/1/users pageSize: 100 - name: canvasEnrollments type: canvasReader properties: path: api/v1/courses pageSize: 100 - name: canvasModules type: canvasReader properties: path: api/v1/courses pageSize: 100 schema: fields: - name: completed_at type: string ###### End - Canvas Reader connectors ###### # _____ _ _ _ # / ____| | | | | | # | (___ | |_ __ _| |_ ___ _ __ ___ ___ _ __ | |_ ___ # \___ \| __/ _` | __/ _ \ '_ ` _ \ / _ \ '_ \| __/ __| # ____) | || (_| | || __/ | | | | | __/ | | | |_\__ \ # |_____/ \__\__,_|\__\___|_| |_| |_|\___|_| |_|\__|___/ # STATEMENTS specify how the data should be processed while in memory statements: #******************************************************************** D I S C L A I M E R *********************************************************************************************** # * # Note that in an effort to keep recipes optimized for DPH (Data Processing Hours), print statements should be commented out after development has concluded for a recipe. * # For more information on DPH optimization, please visit the following help article - https://help.lingk.io/en/articles/212-minimizing-data-processing-hours-on-the-lingk-platform * # * #******************************************************************** D I S C L A I M E R *********************************************************************************************** #- statement: (userLogin) => select '1' as id # 'catalog+limited@instructure.invalid' #- statement: refresh canvasCourses # Courses - statement: (courses) => select * from canvasCourses #- statement: print courses - statement: (coursesCount) => select count(*) from canvasCourses #- statement: print coursesCount # Courses by Name - statement: (courses) => select * from canvasCoursesByName #- statement: print courses - statement: (coursesCount) => select count(*) from canvasCoursesByName #- statement: print coursesCount # Enrollments - statement: (enrollments) => select * from canvasEnrollments ce inner join courses c on c.id = ce.course_id where ce.type = student and ce.include = enrollments and ce.enrollment_state = active #- statement: print enrollments - statement: (enrollmentsCount) => select count(*) from canvasEnrollments #- statement: print enrollmentsCount # Modules #- statement: refresh canvasModules - statement: (modules) => select * from canvasModules cm inner join courses c on c.id = cm.courseId #- statement: print modules - statement: (modulesCount) => select count(*) from canvasModules #- statement: print modulesCount # The dense_rank function - Computes the rank of a value in a group of values. The result is one plus the previously assigned rank value. # Unlike the function rank, dense_rank will not produce gaps in the ranking sequence. ## The PARTITION BY is dividing the rows into small partitions by __parameters field - statement: (modules) => SELECT course_id, id, name, position FROM ( SELECT __parameters.id course_id, id, name, position, dense_rank() OVER (PARTITION BY __parameters.id ORDER BY position DESC) as rank FROM canvasModules) tmp WHERE rank = 12 ORDER by course_id #- statement: print modules # Add more statements to convert, join, aggregrate, transform, and integrate your data
Ellucian Ethos to Salesforce Writer
Canvas REST API Reader - Courses and Enrollments