Complete Script for Verify - Precisely Data Integrity Suite

/*

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-geoaddressing-assume-role'
API_ALLOWED_PREFIXES = ('https://w5mgkth0zg.execute-api.us-east-1.amazonaws.com/v1');



  

-- 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'= '<<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/verify';

/*
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 ;



/*
GeoAddressing's Address format where user has full flexibility to send the addresses as expected in the 
GeoAddressing swagger page.
https://regional-addressing-stg.libigdata.cloud.precisely.com/swagger-ui/index.html
*/

create or replace external function geocode(address object)
returns variant
IMMUTABLE
api_integration = API_INTEGRATION
HEADERS = ('headers-api-key'= '<<API KEY>>', 'headers-api-secret'='<<API SECRET>>')
MAX_BATCH_ROWS = 1000
as 'https://w5mgkth0zg.execute-api.us-east-1.amazonaws.com/v1/verify';

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;
  
  
  

/*
GeoAddressing's Address format 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'= '<<API KEY>>', 'headers-api-secret'='<<API SECRET>>')
MAX_BATCH_ROWS = 1000
as 'https://w5mgkth0zg.execute-api.us-east-1.amazonaws.com/v1/verify';

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'= '<<API KEY>>', 'headers-api-secret'='<<API SECRET>>')
MAX_BATCH_ROWS = 1000
as 'https://w5mgkth0zg.execute-api.us-east-1.amazonaws.com/v1/verify';