Create External User Defined Function with Response Translator for Geocode

For using Response Translator to transform the geocode response, it is required to define the translator function before creating the user defined function.

Precisely Geocode Response Translator helps response transformation and makes response according to the user's need.

When we receive Precisely geocode 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 geocode-response.

create or replace function precisely_geocode_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 Geocoding. If you want to provide specific information for Precisely API's Geocoding, 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.

RESPONSE_TRANSLATOR: It expects Response Translator name here which is created earlier.

create or replace external function geocode(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_geocode_response_translator
as 'https://9a3cydoxrc.execute-api.us-east-1.amazonaws.com/v1/geocode';

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 Geocode 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, geocode(
    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 ;