Hi Ram,
You could apply the GBQ selection results directly to the Reltio connector, as in the sample recipe.
If you'd like to pass the address structure into the connector, it would be easiest to process it using SQL Collections or code blocks to aggregate specific data elements.
Best regards,
Alex
------------------------------
Alexander Grivnin
Workato
Mountain View
------------------------------
Original Message:
Sent: 02-07-2023 11:35
From: Ram Dornala
Subject: New pre-built recipe! Google BigQuery to Reltio
Thank you. I can do UNNEXT or even I can built with flatten GBQ table. But the final target to load the data in Reltio is in JSON format. So I needed the JSON PARSER from the Rows selected from GBQ. Is there any parser for this or some custom logic need to built. Is there any parser from the ROWS selected from GBQ would be helpful to map the final Reltio Json target. So is there any parser I can use for converting GBQ table rows to Reltio Json entity?
I tried to use one Parse JSON document but I get the below error..
Parsing error: 419: unexpected token at '{"SourceSystem"=>"CAH", "CustomerId"=>"100207", "prsn_id"=>"11111207", "SourceSystemUpdateTime"=>nil, "SourceSystemUpdateUser"=>"RAM", "PartnerId"=>"559", "ProducerCodeDetails_0_ProducerCd"=>"AQP156", "ProducerCodeDetails_1_ProducerCd"=>nil, "ProducerCodeDetails_2_ProducerCd"=>nil, "ProducerCodeDetails_3_ProducerCd"=>nil, "ProducerCodeDetails_4_ProducerCd"=>nil, "Prefix"=>nil, "Name"=>nil, "FirstName"=>"WILLIAM", "MiddleName"=>"P", "LastName"=>"SOUSLEY", "FamilySuffix"=>nil, "ProfessionalSuffix"=>nil, "MaritalStatus"=>"S", "PreferredName"=>"WILLIAM", "Nickname"=>"WILLIAM", "Gender"=>"U", "SSN"=>"799606087", "DoB"=>"1975-04-30", "PresumedDead"=>nil, "DoD"=>nil, "SpokenLanguage"=>"English", "DriversLicenseNumber"=>"W0000799607321", "DriversLicenseState"=>"WI", "OriginalLicenseDate"=>"1993-01-07", "EmploymentStatus"=>"true", "Employment_0_Organization"=>"EmpOrg1", "Employment_0_Title"=>"EmpTitle1", "Employment_0_Description"=>"EmpDesc1", "Employment_0_IsCurrent"=>"true", "Employment_1_Organization"=>nil, "Employment_1_Title"=>nil, "Employment_1_Description"=>nil, "Employment_1_IsCurrent"=>nil, "Employment_2_Organization"=>nil, "Employment_2_Title"=>nil, "Employment_2_Description"=>nil, "Employment_2_IsCurrent"=>nil, "Employment_3_Organization"=>nil, "Employment_3_Title"=>nil, "Employment_3_Description"=>nil, "Employment_3_IsCurrent"=>nil, "Employment_4_Organization"=>nil, "Employment_4_Title"=>nil, "Employment_4_Description"=>nil, "Employment_4_IsCurrent"=>nil, "Addresses_0_AddressType"=>"RESIDENCE", "Addresses_0_AddressLine1"=>"107 Moore St", "Addresses_0_City"=>"Beloit", "Addresses_0_StateProvince"=>"WI", "Addresses_0_CountryCode"=>"USA", "Addresses_0_Zip5"=>"53511", "Addresses_0_PrimaryInd"=>"true", "Addresses_1_AddressType"=>"MAILING", "Addresses_1_AddressLine1"=>"107 Moore St", "Addresses_1_City"=>"Beloit", "Addresses_1_StateProvince"=>"WI", "Addresses_1_CountryCode"=>"USA", "Addresses_1_Zip5"=>"53511", "Addresses_1_PrimaryInd"=>"true", "Addresses_2_AddressType"=>nil, "Addresses_2_AddressLine1"=>nil, "Addresses_2_AddressLine2"=>nil, "Addresses_2_City"=>nil, "Addresses_2_StateProvince"=>nil, "Addresses_2_CountryCode"=>nil, "Addresses_2_Zip5"=>nil, "Addresses_2_PrimaryInd"=>nil, "Addresses_3_AddressType"=>nil, "Addresses_3_AddressLine1"=>nil, "Addresses_3_AddressLine2"=>nil, "Addresses_3_City"=>nil, "Addresses_3_StateProvince"=>nil, "Addresses_3_CountryCode"=>nil, "Addresses_3_Zip5"=>nil, "Addresses_3_PrimaryInd"=>nil, "Addresses_4_AddressType"=>nil, "Addresses_4_AddressLine1"=>nil, "Addresses_4_AddressLine2"=>nil, "Addresses_4_City"=>nil, "Addresses_4_StateProvince"=>nil, "Addresses_4_CountryCode"=>nil, "Addresses_4_Zip5"=>nil, "Addresses_4_PrimaryInd"=>nil, "Addresses_5_AddressType"=>nil, "Addresses_5_AddressLine1"=>nil, "Addresses_5_AddressLine2"=>nil, "Addresses_5_City"=>nil, "Addresses_5_StateProvince"=>nil, "Addresses_5_CountryCode"=>nil, "Addresses_5_Zip5"=>nil, "Addresses_5_PrimaryInd"=>nil, "Phone_0_Type"=>"HOME", "Phone_0_Number"=>"7826060817", "Phone_0_CountryCode"=>"US", "Phone_1_Type"=>nil, "Phone_1_Number"=>nil, "Phone_1_CountryCode"=>nil, "Phone_1_PrimaryInd"=>nil, "Phone_2_Type"=>nil, "Phone_2_Number"=>nil, "Phone_2_CountryCode"=>nil, "Phone_3_Type"=>nil, "Phone_3_Number"=>nil, "Phone_3_CountryCode"=>nil, "Phone_4_Type"=>nil, "Phone_4_Number"=>nil, "Phone_4_CountryCode"=>nil, "Email_0_Type"=>"HOME", "Email_0_Email_0"=>"WILLIAM.SOUSLEY@gmail.com", "Email_0_IsCurrent"=>"true", "Email_1_Type"=>nil, "Email_1_Email_0"=>nil, "Email_1_PrimaryInd"=>nil, "Email_1_IsCurrent"=>nil, "Email_2_Type"=>nil, "Email_2_Email_0"=>nil, "Email_2_IsCurrent"=>nil, "MilitaryDeployment_0_DeployedDate"=>nil, "MilitaryDeployment_0_ReturnedDate"=>nil, "MilitaryDeployment_1_DeployedDate"=>nil, "MilitaryDeployment_1_ReturnedDate"=>nil, "MilitaryDeployment_2_DeployedDate"=>nil, "MilitaryDeployment_2_ReturnedDate"=>nil, "MilitaryDeployment_3_DeployedDate"=>nil, "MilitaryDeployment_3_ReturnedDate"=>nil, "MilitaryDeployment_4_DeployedDate"=>nil, "MilitaryDeployment_4_ReturnedDate"=>nil}]
------------------------------
Ram Dornala
AMFAM
Original Message:
Sent: 02-06-2023 22:28
From: Alexander Grivnin
Subject: New pre-built recipe! Google BigQuery to Reltio
Hi Ram,
The best way at the moment is to flatten the GBQ column using the UNNEST construct so that it will produce a separate column for all nested attributes.
Best regards,
Alex
------------------------------
Alexander Grivnin
Workato
Mountain View
Original Message:
Sent: 02-06-2023 15:34
From: Ram Dornala
Subject: New pre-built recipe! Google BigQuery to Reltio
Thank you Ayan. I have this use case and reusing this Recipe. I have one quick question in the above Recipe in STEP : Get batch of rows from query result in BigQuery, if you see the output fields .. the schema can be download only from CSV or is there any way we can give the JSON schema here? As CSV is not able to give the Nested fields, my mapping is executing but all the nested fields are coming as Null as it is not mapping, After I import one CSV format and EDIT the schema in JSON format and change with the Nested fields schema , then the it throws error like not valid format of the Json, So any help in this mapping to handle the Nested attributes like Address/phones/Emails etc..
------------------------------
Ram Dornala
AMFAM
Original Message:
Sent: 12-19-2022 16:36
From: Ayan Basu
Subject: New pre-built recipe! Google BigQuery to Reltio
If you have a need to get Google BigQuery data into Reltio, please check out the pre-built recipe we just put up.
Go to "Reltio Integration hub"--> Community --> search for "Reltio" and you'll see it.
------------------------------
Ayan Basu
------------------------------