Create External User Defined Function with Response Translator for Verify

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.

This is an example that demonstrates the customization you can perform with the verify-response.

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'= '<<DIS API KEY>>', 'headers-api-secret'='<<DIS API SECRET>>',
         'application'= 'dis-geoaddressing')
        MAX_BATCH_ROWS = 25
        RESPONSE_TRANSLATOR = precisely_verify_response_translator
        as 'https://9a3cydoxrc.execute-api.us-east-1.amazonaws.com/v1/verify';

Below is an example:

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.

Run the Verify function and user will be able to see the new field NEW_ADDRESS_LINE1:

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 ;