Typically Request translator is used to provide custom preferences rather than relying on default Geocode API's preferences. In this example we are defining a custom translator which will remove any PBKEY from the API response.
create or replace function aws_geocode_request_translator(event object)
returns object
language javascript as
'
var preferences={
"type": "DefaultPreferences",
"customPreferences": {},
"fallbackToGeographic": true,
"fallbackToPostal": true,
"returnOfAdditionalFields": [],
"maxResults": 1,
"factoryDescription": {
"label": "ggs",
"featureSpecific": {}
},
"returnAllInfo": false
};
return {"body": {"preferences":preferences, "data":EVENT.body.data}};';To use the Request Translator user needs to pass it to the external user defined function
as highlighted
below:
create or replace external function geocode(address object)
returns variant
IMMUTABLE
api_integration = API_INTEGRATION
REQUEST_TRANSLATOR = aws_geocode_request_translator
HEADERS = ('headers-api-key'= '<<DIS API KEY>>', 'headers-api-secret'='<<DIS API SECRET>>',
'application'= 'dis-geoaddressing')
MAX_BATCH_ROWS = 25
as 'https://9a3cydoxrc.execute-api.us-east-1.amazonaws.com/v1/geocode';
create or replace table all_data_result as
select select ID,addressline1,city,state,zip,
object_construct(
'addressId', iff(ADDRESSLINE1 is not null, ADDRESSLINE1,'')
,'addressLines', array_construct(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 sentObject
, geocode(sentObject) from all_data where country = 'UNITED STATES OF AMERICA' ;