User-defined functions in Snowflake for address verification and geocoding - Precisely Data Integrity Suite

Configure external user-defined functions in Snowflake to support address verification and geocoding from Data Quality pipelines.

1. Switch to the ACCOUNTADMIN role.

   USE ROLE ACCOUNTADMIN;

2. Select the warehouse, database, and schema.

   USE WAREHOUSE WAREHOUSE_NAME;
   USE DATABASE DATABASE_NAME;
   USE SCHEMA SCHEMA_NAME;

3. Create the API integration.

   CREATE OR REPLACE API INTEGRATION API_INTEGRATION
     API_PROVIDER = aws_api_gateway
     ENABLED = true
       API_AWS_ROLE_ARN = 'AWS_ROLE_ARN'
     API_ALLOWED_PREFIXES = ('https://9a3cydoxrc.execute-api.us-east-1.amazonaws.com/v1');

4. Create the external function for address verification.

   CREATE OR REPLACE EXTERNAL FUNCTION PRECISELY_GEOCODE(
     "MAINADDRESSLINE" VARCHAR(16777216),
     "COUNTRY" VARCHAR(16777216)
   )
   RETURNS VARIANT
   IMMUTABLE
   API_INTEGRATION = "API_INTEGRATION"
   HEADERS = (
     'application' = 'dis-geoaddressing',
     'headers-api-secret' = '<<api-secret>>',
     'headers-api-key' = '<<api-key>>'
   )
   MAX_BATCH_ROWS = 25
   AS 'https://9a3cydoxrc.execute-api.us-east-1.amazonaws.com/v1/verify';

5. Grant usage rights for the address verification function to the SYSADMIN role.

   GRANT USAGE ON FUNCTION PRECISELY_GEOCODE(string, string) TO SYSADMIN;

6. Create the JavaScript function for request translation.

   CREATE OR REPLACE FUNCTION PRECISELY_GEOCODE_REQUEST_TRANSLATOR(EVENT OBJECT)
   RETURNS OBJECT
   LANGUAGE JAVASCRIPT AS
   '
   var customPref = EVENT.body.data[0][3];
   var preferences = {
     "customPreferences": {
       "FIND_DPV": "false",
       "FIND_RDI": "false",
       "FIND_LACSLINK": "false",
       "FIND_SUITELINK": "false",
       "EARLY_WARNING_SYSTEM": "false",
       "RETURN_PARSED_INPUT": customPref.returnParsedInput,
       "RETURN_ALT_LANG_FIELDS": customPref.returnAlternateLanguage,
       "ADDRESS_CASING": customPref.addressCasing,
       "USE_ADDRESS_POINT_INTERPOLATION": customPref.useAddressPointInterpolation,
       "MATCH_ON_ADDRESS_NUMBER": customPref.mustMatchAddressNumber,
       "MATCH_ON_STREET_NAME": customPref.mustMatchStreet,
       "MATCH_ON_CITY": customPref.mustMatchCity,
       "MATCH_ON_NEIGHBORHOOD": customPref.mustMatchCitySubdivision,
       "MATCH_ON_ADMIN1": customPref.mustMatchStateProvince,
       "MATCH_ON_ADMIN2": customPref.mustMatchStateProvinceSubdivision,
       "MATCH_ON_POSTAL_CODE": customPref.mustMatchPostalCode,
       "RETURN_PARSED_INPUT": customPref.returnParsedInput,
       "RETURN_ALT_LANG_FIELDS": customPref.returnAlternateLanguage,
       "USE_ADDRESS_POINT_INTERPOLATION": customPref.useAddressPointInterpolation
     },
     "matchMode": customPref.matchMode,
     "maxResults": 1,
     "factoryDescription": {
       "label": "ggs"
     },
     "returnAllInfo": true
   };
   for (i = 0; i < EVENT.body.data.length; i++) {
     EVENT.body.data[i].pop();
   }
   return {"body": {"preferences": preferences, "data": EVENT.body.data}};
   ';

7. Grant usage rights for the request translator function to the SYSADMIN role.

   GRANT USAGE ON FUNCTION PRECISELY_GEOCODE_REQUEST_TRANSLATOR(OBJECT) TO SYSADMIN;

8. Create the external function for address geocoding.

   CREATE OR REPLACE EXTERNAL FUNCTION PRECISELY_GEOCODE_ADDRESS(
     "MAINADDRESSLINE" VARCHAR(16777216),
     "COUNTRY" VARCHAR(16777216),
     PREFERENCES OBJECT
   )
   RETURNS VARIANT
   IMMUTABLE
   API_INTEGRATION = "API_INTEGRATION"
   REQUEST_TRANSLATOR = "PRECISELY_GEOCODE_REQUEST_TRANSLATOR"
   HEADERS = (
     'application' = 'dis-geoaddressing',
     'headers-api-secret' = '<<api-secret>>',
     'headers-api-key' = '<<api-key>>'
   )
   MAX_BATCH_ROWS = 25
   AS 'https://9a3cydoxrc.execute-api.us-east-1.amazonaws.com/v1/geocode';

9. Grant usage rights for the address geocoding function to the SYSADMIN role.

   GRANT USAGE ON FUNCTION PRECISELY_GEOCODE_ADDRESS(string, string, object) TO SYSADMIN;