Complete Script for Geocode
This section has a combined script with all the steps at one place.
/*
Snowflake SQL script for Production environment.
Description: This sql script helps in geocoding from Snowflake using External User defined functions.
Only accountadmin role can create "API INTEGRATION" and "External User Defined Functions".
Once accountadmin created above items in Snowflake, it is expected that execution rights to be given to other roles. In this script,
we choose SYSADMIN. In the actual Snowflake environment, it can be any role.
*/
use role accountadmin;
use warehouse testdb;
-- create or replace database presales;
use database presales;
-- create or replace schema presales;
use schema presales;
/*API Integration is required for External User Defined function.*/
create or replace API Integration API_INTEGRATION
api_provider=aws_api_gateway
enabled = true
api_aws_role_arn = 'arn:aws:iam::508747789874:role/prd-sf-geocode-assume-role'
API_ALLOWED_PREFIXES = ('https://9a3cydoxrc.execute-api.us-east-1.amazonaws.com/v1');
--- format 1
/*
In this external defined function, user required to send country.
headers-api-key: It expects GeoAddressing API's production environment key
headers-api-secret: It expects GeoAddressing API's production environment secret
*/
create or replace external function geocode(addressLines string ,country string )
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
as 'https://9a3cydoxrc.execute-api.us-east-1.amazonaws.com/v1/geocode';
grant ALL PRIVILEGES ON database PRESALES to role SYSADMIN;
grant ALL PRIVILEGES ON SCHEMA PRESALES to role SYSADMIN;
grant usage on function geocode(string ,string ) to sysadmin;
-- changing the requested role.
use role sysadmin;
-- Performing Geocoding in Snowflake environment.
create or replace table all_data_result as
select ID,addressline1,city,state,zip, geocode(
iff(ADDRESSLINE1 is not null, ADDRESSLINE1,'')
||iff(city is not null, ','||city,'')
||iff(state is not null, ','||state,'')
||iff(zip is not null, ','||zip,'')
,country) as address, country from all_data;
-- Check the successful geocoded results.
select country,count(country) from all_data_result where parse_json(address):status = 'OK'
group by country;
/*
Below example try to give more flexibility when performing Geocoding. If we want to provide specific information
for GeoAddressing API's Geocoding, we can use it below information. Kindly notice the Geocode signature which expects
input type addresses should be in array.
headers-api-key: It expects GeoAddressing API's production environment key
headers-api-secret: It expects GeoAddressing API's production environment secret
*/
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
as 'https://9a3cydoxrc.execute-api.us-east-1.amazonaws.com/v1/geocode';
grant ALL PRIVILEGES ON database PRESALES to role SYSADMIN;
grant ALL PRIVILEGES ON SCHEMA PRESALES to role SYSADMIN;
grant usage on function geocode(array) to sysadmin;
/* Format 2
It supports the above example in array.
*/
-- changing the requested role.
use role sysadmin;
create or replace table all_data_result as
select ID,addressline1,city,state,zip, geocode(array_construct(
iff(ADDRESSLINE1 is not null, ADDRESSLINE1,'')
||iff(city is not null, ','||city,'')
||iff(state is not null, ','||state,'')
||iff(zip is not null, ','||zip,''),country))
as address,country from all_data;
select * from all_data_result;
/*
Format 3 GeoAddressing API's Single Line
It helps user to send addressLines and country in the input address.
*/
-- changing the requested role.
use role sysadmin;
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
))
as address,country from all_data;
/*
Format 4 GeoAddressing API's Multiline
It helps user to multiple keys like addressLines,city, admin1, postcode and country in the input address.
*/
-- changing the requested role.
use role sysadmin;
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 ;
-- Check for records which were unable to get geocode.
select country,count(country) from all_data_result where parse_json(address):status = 'OK' group by country;
-- Check for records which were not able to get geocode.
select country,count(country) from all_data_result where parse_json(address):status != 'OK'
group by country;
/**
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 make response according to the user's need.
Below function will create a separate new field in NEW_ADDRESS_LINE1 in customFields. This customFields is devoid of any
HIUNIT. It is a example which demonstrate the customization we 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 } };
';
/*
Below example try to give more flexibility when performing Geocoding. If we want to provide specific information
for GeoAddressing API's Geocoding, we can use it below information. Kindly notice the Geocode signature which expects
input type addresses should be in array.
headers-api-key: It expects GeoAddressing API's production environment key
headers-api-secret: It expects GeoAddressing API's production environment secret
RESPONSE_TRANSLATOR = precisely_geocode_response_translator
*/
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';
/*
Execute the Geocode function and user will be able to see 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 ;
/*
Format 5 GeoAddressing's Address format 5 where user has full flexibility to send the addresses as expected in the
GeoAddressing swagger page.
https://regional-addressing-qa.libigdata.cloud.precisely.services/swagger/index.html#/Geocode%20V1/geocode
*/
create or replace external function geocode(address object)
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
as 'https://9a3cydoxrc.execute-api.us-east-1.amazonaws.com/v1/geocode';
create or replace table all_data_result as
select ID,addressline1,city,state,zip,
object_construct(
'addressId', iff(ID is not null, ID,'')
,'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) as address,country from all_data;
/*
Format 5 GeoAddressing's Address format 5 where user has full flexibility to send the addresses alongwith custom preferences
as expected in the GeoAddressing swagger page.
https://regional-addressing.libigdata.cloud.precisely.com/swagger/index.html#/Geocode%20V1/geocode
*/
/*
Setting the preferences for all the batch addresses
*/
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}};';
/*
Now recreate the UDF with aws_geocode_request_translator translator
*/
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(ID is not null, ID,'')
,'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 ;
/**
Suppose user data is very sensitive and user want to secure the information end to end. They can use the "SECURE" keyword while creating User Defined Function.
Please notice the keyword "SECURE" in the below syntax.
**/
create or replace SECURE external function geocode(address object)
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
as 'https://9a3cydoxrc.execute-api.us-east-1.amazonaws.com/v1/geocode';