Complete Script for GeoTAX Address - Precisely Data Integrity Suite

/*

Snowflake SQL script for Production environment.

Description: This sql script helps in getting geotax information 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');



/*
Creater external User Defined function.
*/

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


/*
Send address object as accepted by geotax address endpoint.
Refer below GeoTAX swagger page:
https://developer.cloud.precisely.com/api-reference/GeoTAX%252FGeoTAX
*/

create or replace table geotax_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,'')),
      ,'admin1',iff(state  is not  null, state,'')
      ,'city',iff(city is not null, city,'')
     ,'postalCode',iff(zip  is not  null, zip,'')
) as geotaxObject
, geotax_address(geotaxObject)
as geotaxResponse from GEOTAX_LOCATIONS order by zip;

/*
If you want to pass preferences in request, use request translator as shown below.
Replace your preference with var preferences variable in below function.
*/
create or replace function geotax_preference_request_translator(event object)
	returns object
	language javascript as
	'
	  var preferences={
		  "output": {
		  "taxDistrict": "SPD",
		  "salesTaxRateType": "GENERAL",
		  "outputCasing": "MIXED"
		},
		"geocoding": {
		  "defaultBufferWidth": "0",
		  "latLongOffset": "NONE",
		  "squeeze": "NO",
		  "latLongAltFormat": "DecimalSign"
		},
		"matching": {
		  "matchMode": "CLOSE"
		}
	  };
	  
	  return {"body": {"preferences":preferences, "data":EVENT.body.data}};
	';


/*
Now recreate the UDF with geotax_preference_request_translator translator
*/
create or replace external function geotax_address(address object)
returns variant
IMMUTABLE
api_integration = API_INTEGRATION
REQUEST_TRANSLATOR = geotax_preference_request_translator
HEADERS = ('headers-api-key'= '<<API KEY>>', 'headers-api-secret'='<<API SECRET>>')
MAX_BATCH_ROWS = 10
as 'https://9a3cydoxrc.execute-api.us-east-1.amazonaws.com/v1/geo-tax-address';

create or replace table geotax_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,'')),
      ,'admin1',iff(state  is not  null, state,'')
      ,'city',iff(city is not null, city,'')
     ,'postalCode',iff(zip  is not  null, zip,'')
) as geotaxObject
, geotax_address(geotaxObject)
as geotaxResponse from GEOTAX_LOCATIONS order by zip;
  
  
  
/**
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 geotax_address(address object)
returns variant
IMMUTABLE
api_integration = API_INTEGRATION
REQUEST_TRANSLATOR = geotax_preference_request_translator
HEADERS = ('headers-api-key'= '<<API KEY>>', 'headers-api-secret'='<<API SECRET>>')
MAX_BATCH_ROWS = 10
as 'https://9a3cydoxrc.execute-api.us-east-1.amazonaws.com/v1/geo-tax-address';