For using Response Translator to transform the verify response, it is required to define the translator function before creating the user defined function.
Precisely Verify Response Translator helps response transformation and makes response according to the user's need.
When we receive Precisely verify response, it contains the customFields as a JSON element.
This function will create a separate new JSON element in NEW_ADDRESS_LINE1 in the customFields. The NEW_ADDRESS_LINE1 will have same value as ADDRLINE_SHORT.
create or replace function precisely_verify_response_translator(event object)
returns object
language javascript as
'
var result = [];
for(i=0;i< EVENT.body.data.length;i++) {
let val = EVENT.body.data[i][1];
if(val.results != null && typeof val.results != "undefined" && val.results.length > 0
&& val.results[0].address != null && typeof val.results[0].address != "undefined"
&& val.results[0].customFields != null && typeof val.results[0].customFields != "undefined") {
let tempCustomFields = val.results[0].customFields;
tempCustomFields.NEW_ADDRESS_LINE1 = tempCustomFields.ADDRLINE_SHORT;
val.results[0].customFields = tempCustomFields;
}
let row = [i, val];
result.push(row);
}
return { "body": { "data" : result } };
';This example tries to give more flexibility when performing Verify. If you want to provide specific information for Precisely API's Verify, you can use this information.
headers-api-key: It expects DIS API's production environment key.
headers-api-secret: It expects DIS API's production environment secret.
application: Snowflake users need to define
dis-geoaddressing.
create or replace external function verify(address array)
returns variant
IMMUTABLE
api_integration = API_INTEGRATION
HEADERS = ('headers-api-key'= '<<API KEY>>', 'headers-api-secret'='<<API SECRET>>')
MAX_BATCH_ROWS = 1000
RESPONSE_TRANSLATOR = precisely_geocode_response_translator
as 'https://w5mgkth0zg.execute-api.us-east-1.amazonaws.com/v1/verify';Response Translator
With response translators, you can change the format of data received from remote services used by external functions.
When Snowflake receives data from a remote service, Snowflake expects the data to be formatted according to the same rules. However, many remote services expect to handle data in a different format. Response translator converts data from the remote service's native output format to Snowflake's format.
create or replace table all_data_result as
select ID,addressline1,city,state,zip, verify(
array_construct(
'addressLines', iff(ADDRESSLINE1 is not null, ADDRESSLINE1,'')
,'country',country
,'city',iff(city is not null, city,'')
,'admin1',iff(state is not null, state,'')
,'postalCode',iff(zip is not null, zip,'')
))
as address,country from all_data ;