# _____ _ _____ __ # | __ \ (_) |_ _| / _| # | |__) |___ ___ _ _ __ ___ | | _ __ | |_ ___ # | _ // _ \/ __| | '_ \ / _ \ | | | '_ \| _/ _ \ # | | \ \ __/ (__| | |_) | __/ _| |_| | | | || (_) | # |_| \_\___|\___|_| .__/ \___| |_____|_| |_|_| \___/ # | | # |_| # Project Name - SCHEMA TECHNIQUES FOR NULL NESTED DATA PROPERTIES # Recipe URL - https://app.lingk.io/a/10932/tf/17886 # Description - # This recipe demonstrates how to ensure a recipe complete when nested data structures are used with inline() or explode() functions. # The key is to attach a schema to the connector that defined the temporarily non-existant value that Spark will otherwise drop # the column for (because it's all null) # Note: we are looking for an endOn date that doesn't exist in this payload but could. # Use schema to ensure that the field is available even if null. # Industry - Higher Ed # Business Process - Graduate Reporting # Systems - # Connectors - JSON # Data Flows - Single Direction # Connection Type - JSON # Add Recipe notes / Change log information here! # _____ _ # / ____| | | # | | ___ _ __ _ __ ___ ___| |_ ___ _ __ ___ # | | / _ \| '_ \| '_ \ / _ \/ __| __/ _ \| '__/ __| # | |___| (_) | | | | | | | __/ (__| || (_) | | \__ \ # \_____\___/|_| |_|_| |_|\___|\___|\__\___/|_| |___/ # # CONNECTORS specify what data will be pulled into the in-memory database during processing connectors: # JSON Setup - https://help.lingk.io/en/articles/74-json-connector-reference ###### Start: JSON Connectors ####### # Start: Persons - name: cPersons type: json properties: jsonObject: | [ { "addresses":[ { "address":{ "id":"9e9f656f-d7d4-4c12-95b6-1fe4eab69035" }, "startOn":"2019-08-20T04:00:00.0000000Z", "type":{ "addressType":"mailing", "detail":{ "id":"2f7e01f4-6560-4423-86ca-c0ed041dfbae" } } } ], "citizenshipStatus":{ "category":"citizen", "detail":{ "id":"a1c8f5d3-7de0-4ac1-a00d-a7cf8d74d17e" } }, "credentials":[ { "type":"bannerSourcedId", "value":"12" }, { "type":"bannerUserName", "value":"jdoe" }, { "type":"bannerUdcId", "value":"555555" }, { "type":"bannerId", "value":"A0001XXX" } ], "dateOfBirth":"1988-09-29", "emails":[ { "address":"jdoe@central.edu", "type":{ "detail":{ "id":"6f94b61a-8aad-45d1-a33e-c6f64627f7e3" }, "emailType":"business" } }, { "address":"john.doe@gmail.com", "type":{ "detail":{ "id":"cd02f6eb-70ef-4655-9fc3-5060b62db6c6" }, "emailType":"personal" } }, { "address":"j.doe@central.edu", "preference":"primary", "type":{ "detail":{ "id":"e6bf1d3a-b524-4f49-9f3a-5f3c2506afaa" }, "emailType":"school" } } ], "gender":"male", "id":"e736e5a3-dabe-46c4-9722-ba67cd32accc", "names":[ { "firstName":"John", "fullName":"John Allen Doe", "lastName":"Doe", "middleName":"Allen", "preference":"preferred" } ], "phones":[ { "number":"6033391279", "type":{ "detail":{ "id":"266ca00b-c1f0-4c74-a616-26bac67a9777" }, "phoneType":"home" } } ], "privacyStatus":{ "privacyCategory":"unrestricted" }, "races":[ { "race":{ "id":"57528af6-5102-4a6e-9d69-12ee2762d301" }, "reporting":[ { "country":{ "code":"USA", "racialCategory":"white" } } ] } ], "roles":[ { "endOn":"2019-12-20T05:00:00.0000000Z", "role":"student", "startOn":"2019-09-03T04:00:00.0000000Z" } ] } ] schema: #inline schema fields: - name: id type: 'string' - name: 'addresses' type: 'array' items: name: 'arrayElement' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'endOn' type: 'string' - name: 'startOn' type: 'string' # Start: Persons - name: cPersons1 type: json schemaRef: personSchema # schema by reference (can be reused across multiple connectors) properties: jsonObject: | [ { "addresses":[ { "address":{ "id":"9e9f656f-d7d4-4c12-95b6-1fe4eab69035" }, "startOn":"2019-08-20T04:00:00.0000000Z", "type":{ "addressType":"mailing", "detail":{ "id":"2f7e01f4-6560-4423-86ca-c0ed041dfbae" } } } ], "citizenshipStatus":{ "category":"citizen", "detail":{ "id":"a1c8f5d3-7de0-4ac1-a00d-a7cf8d74d17e" } }, "credentials":[ { "type":"bannerSourcedId", "value":"12" }, { "type":"bannerUserName", "value":"jdoe" }, { "type":"bannerUdcId", "value":"555555" }, { "type":"bannerId", "value":"A0001XXX" } ], "dateOfBirth":"1988-09-29", "emails":[ { "address":"jdoe@central.edu", "type":{ "detail":{ "id":"6f94b61a-8aad-45d1-a33e-c6f64627f7e3" }, "emailType":"business" } }, { "address":"john.doe@gmail.com", "type":{ "detail":{ "id":"cd02f6eb-70ef-4655-9fc3-5060b62db6c6" }, "emailType":"personal" } }, { "address":"j.doe@central.edu", "preference":"primary", "type":{ "detail":{ "id":"e6bf1d3a-b524-4f49-9f3a-5f3c2506afaa" }, "emailType":"school" } } ], "gender":"male", "id":"e736e5a3-dabe-46c4-9722-ba67cd32accc", "names":[ { "firstName":"John", "fullName":"John Allen Doe", "lastName":"Doe", "middleName":"Allen", "preference":"preferred" } ], "phones":[ { "number":"6033391279", "type":{ "detail":{ "id":"266ca00b-c1f0-4c74-a616-26bac67a9777" }, "phoneType":"home" } } ], "privacyStatus":{ "privacyCategory":"unrestricted" }, "races":[ { "race":{ "id":"57528af6-5102-4a6e-9d69-12ee2762d301" }, "reporting":[ { "country":{ "code":"USA", "racialCategory":"white" } } ] } ], "roles":[ { "endOn":"2019-12-20T05:00:00.0000000Z", "role":"student", "startOn":"2019-09-03T04:00:00.0000000Z" } ] } ] schema: fields: - name: id type: 'string' - name: 'addresses' type: 'array' items: name: 'arrayElement' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'endOn' type: 'string' - name: 'startOn' type: 'string' schemas: - name: personSchema fields: - name: id type: 'string' - name: 'addresses' type: 'array' items: name: 'arrayElement' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'endOn' type: 'string' - name: 'startOn' type: 'string' ###### End: JSON 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 *********************************************************************************************** # Get the person data per student #- statement: print cPersons # full schema ... you only need the schema for fields that COULD be blank some of the time #- statement: printschema cPersons # get the Count - statement: (cPersonsCount) => SELECT count(*) FROM cPersons #- statement: print cPersonsCount ## option 1 - use lateral view explode # explode function in the query below splits the addresses array in different rows and creates a lateral view - statement: | (personAddresses) => select id personId, addresses1.* from cPersons lateral view explode(addresses) a as addresses1 #- statement: print personAddresses - statement: | (personAddressStartEnd) => select startOn, endOn from personAddresses #- statement: print personAddressStartEnd # Get the person data per student #- statement: print cPersons1 # full schema ... you only need the schema for fields that COULD be blank some of the time #- statement: printschema cPersons1 ## option 2 - use the inline function - statement: | (personAddresses) => select id personId, inline(addresses) from cPersons1 #- statement: print personAddresses - statement: | (personAddressStartEnd) => select startOn, endOn from personAddresses #- statement: print personAddressStartEnd # Add more statements to convert, join, aggregrate, transform, and integrate your data