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.
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 API's production environment key.
headers-api-secret: It expects API's production environment secret.
application: Snowflake users need to define
dis-geoaddressing.
create or replace external function geocode(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/geocode'; 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, 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 ;