Go Back
Ellucian Ethos Join
Publisher
: Lingk
Run In Lingk
Description
Join Ellucian Ethos APIs and create a flattened table
Browse the knowledge base
Twitter
E-Mail
# _____ _ _____ __ # | __ \ (_) |_ _| / _| # | |__) |___ ___ _ _ __ ___ | | _ __ | |_ ___ # | _ // _ \/ __| | '_ \ / _ \ | | | '_ \| _/ _ \ # | | \ \ __/ (__| | |_) | __/ _| |_| | | | || (_) | # |_| \_\___|\___|_| .__/ \___| |_____|_| |_|_| \___/ # | | # |_| # Project Name - ELLUCIAN ETHOS JOIN # Recipe URL - https://app.lingk.io/a/10932/tf/17510 # Description - This recipe joins responses from three Ethos APIs into a flattened table # Industry - Higher Ed # Business Process - Graduate Reporting # Systems - Ethos # Connectors - Ethos # Data Flows - Single Direction # Connection Type - API # Data Models ## Ethos Models Used: #1. Academics-Programs #2. student-academic-programs #3. persons # Add Recipe notes / Change log information here! # _____ _ # / ____| | | # | | ___ _ __ _ __ ___ ___| |_ ___ _ __ ___ # | | / _ \| '_ \| '_ \ / _ \/ __| __/ _ \| '__/ __| # | |___| (_) | | | | | | | __/ (__| || (_) | | \__ \ # \_____\___/|_| |_|_| |_|\___|\___|\__\___/|_| |___/ # # CONNECTORS specify what data will be pulled into the in-memory database during processing connectors: # Configure Ethos credentials in your Environment before running this recipe # Ethos Setup - https://help.lingk.io/en/articles/292-ellucian-ethos-connector-setup-guide ###### Start - Ethos Reader connectors ###### - name: academicProgramsReader type: ethosReader properties: path: /api/academic-programs dataModelVersion: 6 - name: studentAcademicProgramsReader type: ethosReader properties: path: /api/student-academic-programs dataModelVersion: 6 - name: studentReader type: ethosReader properties: path: /api/persons dataModelVersion: 12.1.0 ###### End - Ethos 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: (getPrograms) => select * from academicProgramsReader #- statement: print getPrograms - statement: (getProgramsCount) => select count(*) from academicProgramsReader #- statement: print getProgramsCount - statement: (getStudentPrograms) => select * from studentAcademicProgramsReader #- statement: print getStudentPrograms - statement: (getStudentProgramsCount) => select count(*) from studentAcademicProgramsReader #- statement: print getStudentProgramsCount - statement: | (joinedDataset) => select * from studentAcademicProgramsReader studentProgram INNER JOIN academicProgramsReader program on program.id = studentProgram.program.id #- statement: print joinedDataset - statement: (joinedDatasetCount) => select count(*) from joinedDataset #- statement: print joinedDatasetCount - statement: (getStudent) => select * from studentReader #- statement: print getStudent - statement: (getStudentCount) => select count(*) from studentReader #- statement: print getStudentCount # explode function in the query below splits the phones array in different rows and creates a lateral view - statement: | (bannerCredentials) => select student.id student_id, a.credentials.* from studentReader student lateral view explode(student.credentials) a as credentials #- statement: print bannerCredentials # Note that the dot notation and '[0]' enables you to choose a value from an array. - statement: | (joinedDataset) => select bannerCredentials.value banner_id, student.id student_id, student.names[0].fullname full_name, student.names[0].firstName first_name, student.names[0].lastName last_name, program.id program_id, program.code program_code, program.title program_title, studentProgram.enrollmentStatus.status program_status from studentAcademicProgramsReader studentProgram INNER JOIN academicProgramsReader program on program.id = studentProgram.program.id INNER JOIN studentReader student on student.id = studentProgram.student.id INNER JOIN bannerCredentials on bannerCredentials.student_id = studentProgram.student.id and bannerCredentials.type = "bannerId" #- statement: print joinedDataset - statement: (joinedDatasetCount) => select count(*) from joinedDataset #- statement: print joinedDatasetCount # Add more statements to convert, join, aggregrate, transform, and integrate your data
Ellucian Ethos to SFTP
Ellucian Ethos Reader