# _____ _ _____ __ # | __ \ (_) |_ _| / _| # | |__) |___ ___ _ _ __ ___ | | _ __ | |_ ___ # | _ // _ \/ __| | '_ \ / _ \ | | | '_ \| _/ _ \ # | | \ \ __/ (__| | |_) | __/ _| |_| | | | || (_) | # |_| \_\___|\___|_| .__/ \___| |_____|_| |_|_| \___/ # | | # |_| # Project Name - Learning Stream Reader Example # Recipe URL - https://app.lingk.io/a/10932/tf/19696 # Description - # This recipe enables you to read data from the Learning Stream API # Systems - LearningStreamReader # Connectors - LearningStreamReader # Data Flows - Single Direction # Connection Type - API # Add Recipe notes / Change log information here! # _____ _ # / ____| | | # | | ___ _ __ _ __ ___ ___| |_ ___ _ __ ___ # | | / _ \| '_ \| '_ \ / _ \/ __| __/ _ \| '__/ __| # | |___| (_) | | | | | | | __/ (__| || (_) | | \__ \ # \_____\___/|_| |_|_| |_|\___|\___|\__\___/|_| |___/ # # CONNECTORS specify what data will be pulled into the in-memory database during processing connectors: # Configure Learning Stream credentials in your Environment before running this recipe # LearningStreamReader Setup - https://help.lingk.io/en/articles/85-learning-stream-connector-reference ###### Start - LearningStreamReader connectors ###### providers: - name: registrations type: LearningStreamReader properties: rootUrl: "https://reg.abcsignup.com/" path: "reg/reg_abc_registration_data.aspx" accountCode: {{env.accountCode}} dataType: "registration" securityKey: {{env.securityKey}} startDate: {{env.startDate}} endDate: {{env.endDate}} schema: fields: - name: registration_questions type: object objectSchema: name: questionObject fields: - name: question type: array items: type: object objectSchema: name: questionDetails fields: - name: id type: string - name: responses type: object objectSchema: name: test fields: - name: response type: string - name: text type: string - name: type type: string - name: address_line1 type: string - name: address_line2 type: string - name: balance type: string - name: city type: string - name: email_address type: string - name: event_code type: string - name: event_categories type: object objectSchema: name: eventCategoryList fields: - name: category_name type: string - name: event_start_date type: string - name: event_end_date type: string - name: event_title type: string - name: first_name type: string - name: last_name type: string - name: method_of_payment type: string - name: phone_number type: string - name: registrant_sid type: string - name: registration_date type: string - name: registration_status type: string - name: registration_time type: string - name: state type: string - name: total_charges type: string - name: total_credits type: string - name: total_payments type: string - name: user_id type: string - name: zip_code type: string - name: login_id type: string ###### End - LearningStreamReader connectors ###### ###### Start - localFileReader connectors ###### ## SFTP connector (for env var management) - name: envVars type: localFileReader format: csv properties: fileName: env_var.csv ###### End - localFileReader connectors ###### # ______ _ # | ____| | | # | |__ ___ _ __ _ __ ___ __ _| |_ ___ # | __/ _ \| '__| '_ ` _ \ / _` | __/ __| # | | | (_) | | | | | | | | (_| | |_\__ \ # |_| \___/|_| |_| |_| |_|\__,_|\__|___/ # FORMATS specify how files should be processed as they are being read or written to readFormats: ## CSV data formats for SFTP Connector - name: csv type: delimited properties: delimiter: ',' header: true # _____ _ _ _ # / ____| | | | | | # | (___ | |_ __ _| |_ ___ _ __ ___ ___ _ __ | |_ ___ # \___ \| __/ _` | __/ _ \ '_ ` _ \ / _ \ '_ \| __/ __| # ____) | || (_| | || __/ | | | | | __/ | | | |_\__ \ # |_____/ \__\__,_|\__\___|_| |_| |_|\___|_| |_|\__|___/ # 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: (thisEnv) => select * from envVars where env = "{{ env.name }}" # - statement: print thisEnv #- statement: print registrations - statement: (registrationCount) => select count(*) from registrations #- statement: print registrationCount - statement: (thisEnv) => select program from envVars where env = "{{ env.name }}" - statement: | (testFlat) => select user_id, question_id, col1.response response, col2, col3 from registrations lateral view outer inline(registration_questions.question) as question_id, col1, col2, col3 where registration_status = "Confirmed" and event_categories.category_name in ( select program from thisEnv ) #- statement: print testFlat # - statement: (distinctAffiliations) => select distinct response from testFlat where question_id = 165657 # - statement: print distinctAffiliations # - statement: (testFlatregistrationCount) => select count(*) from testFlat # - statement: print testFlatregistrationCount # The 'LEFT ANTI JOIN' function retrieves the rows from the first table (table after the FROM statement) that do not match with the rows on the second table (right table) - statement: | (test) => select a.event_categories, a.address_line1, a.address_line2, a.balance, a.city, a.email_address, a.event_code, a.event_categories.category_name, a.event_start_date, to_date(a.event_start_date,"MM/dd/yyyy") event_start_date_transformed, a.event_end_date, to_date(a.event_end_date,"MM/dd/yyyy") event_end_date_transformed, a.event_title, a.first_name, a.last_name, a.method_of_payment, a.phone_number, a.registrant_sid, a.registration_date, a.registration_status, a.registration_time, a.state, a.total_charges, a.total_credits, a.total_payments, a.user_id, a.zip_code, a.login_id, ifnull(b156573.response,"") as middle_name, ifnull(b156574.response,"") as suffix, ifnull(b156560.response,"") as country, ifnull(b149304.response,"") as email_optin, ifnull(b149305.response,"") as professional_areas, ifnull(b150547.response,"") as professional_areas_other, ifnull(b165657.response,"") as affiliation, ifnull(b165658.response,"") as affiliation_other, ifnull(b149298.response,"") as job_title, ifnull(b149299.response,"") as employer, ifnull(b149301.response,"") as highest_degree, ifnull(b149303.response,"") as age, ifnull(b149302.response,"") as lead_channel, ifnull(b150546.response,"") as lead_channel_other from registrations a left join testFlat b156573 on a.user_id = b156573.user_id and b156573.question_id = 156573 left join testFlat b156574 on a.user_id = b156574.user_id and b156574.question_id = 156574 left join testFlat b156560 on a.user_id = b156560.user_id and b156560.question_id = 156560 left join testFlat b149304 on a.user_id = b149304.user_id and b149304.question_id = 149304 left join testFlat b149305 on a.user_id = b149305.user_id and b149305.question_id = 149305 left join testFlat b150547 on a.user_id = b150547.user_id and b150547.question_id = 150547 left join testFlat b165657 on a.user_id = b165657.user_id and b165657.question_id = 165657 left join testFlat b165658 on a.user_id = b165658.user_id and b165658.question_id = 165658 left join testFlat b149298 on a.user_id = b149298.user_id and b149298.question_id = 149298 left join testFlat b149299 on a.user_id = b149299.user_id and b149299.question_id = 149299 left join testFlat b149301 on a.user_id = b149301.user_id and b149301.question_id = 149301 left join testFlat b149303 on a.user_id = b149303.user_id and b149303.question_id = 149303 left join testFlat b149302 on a.user_id = b149302.user_id and b149302.question_id = 149302 left join testFlat b150546 on a.user_id = b150546.user_id and b150546.question_id = 150546 where event_categories.category_name in ( select program from thisEnv ) and registration_status = "Confirmed" #- statement: print test