Complete Script for Verify
This section has a combined script with all the steps at one place
-- Step1: Select Environment:
use role accountadmin;
use warehouse testdb;
-- create or replace database presales;
use database presales;
-- create or replace schema presales;
use schema presales;
-- Step2: Create API Integration:
/*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');
-- Step3: Perform Verify Operation Using any one of below formats:
--- format 1
/*
In this external defined function, user will be sending the address as well as country.
If the country is not provided then it won't be verify.
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 verify(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/verify';
grant ALL PRIVILEGES ON database PRESALES to role SYSADMIN;
grant ALL PRIVILEGES ON SCHEMA PRESALES to role SYSADMIN;
grant usage on function verify(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, verify(
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 verified 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 verify 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 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
as 'https://9a3cydoxrc.execute-api.us-east-1.amazonaws.com/v1/verify';
grant ALL PRIVILEGES ON database PRESALES to role SYSADMIN;
grant ALL PRIVILEGES ON SCHEMA PRESALES to role SYSADMIN;
grant usage on function verify(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, verify(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, verify(
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, 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 ;
-- Check for records which were unable to get verify.
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 verify.
select country,count(country) from all_data_result where parse_json(address):status != 'OK'
group by country;
/**
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 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 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 } };
';
/*
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 verify 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_verify_response_translator
*/
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';
grant ALL PRIVILEGES ON database PRESALES to role SYSADMIN;
grant ALL PRIVILEGES ON SCHEMA PRESALES to role SYSADMIN;
grant usage on function verify(array) to sysadmin;
/*
Execute the verify 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, 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 ;
/*
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#
*/
create or replace external function verify(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/verify';
grant ALL PRIVILEGES ON database PRESALES to role SYSADMIN;
grant ALL PRIVILEGES ON SCHEMA PRESALES to role SYSADMIN;
grant usage on function verify(object ) to sysadmin;
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
, verify(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#
*/
/*
Setting the preferences for all the batch addresses
*/
create or replace function aws_verify_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_verify_request_translator translator
*/
create or replace external function verify(address object)
returns variant
IMMUTABLE
api_integration = API_INTEGRATION
REQUEST_TRANSLATOR = aws_verify_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/verify';
-- Step4: Grant Access to User:
grant ALL PRIVILEGES ON database PRESALES to role SYSADMIN;
grant ALL PRIVILEGES ON SCHEMA PRESALES to role SYSADMIN;
grant usage on function verify(object ) to sysadmin;
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
, verify(sentObject)
as address,country 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 verify(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/verify';
grant ALL PRIVILEGES ON database PRESALES to role SYSADMIN;
grant ALL PRIVILEGES ON SCHEMA PRESALES to role SYSADMIN;
grant usage on function verify(object) to sysadmin;