UDF in Snowflake for Address Verification and Geocoding in Data Quality - Precisely Data Integrity Suite

Prerequisites

Before proceeding, ensure the following:

• You have the ACCOUNTADMIN role in Snowflake.
• You have access to the required AWS API Gateway and its credentials (API secret and API key).
• You have the necessary permissions to create and manage API integrations and external functions in Snowflake.

Steps to Set Up Address Verification and Geocoding

Follow these steps to configure and use the external UDFs:

• Switch to the ACCOUNTADMIN role:

  USE ROLE ACCOUNTADMIN;

• Select the appropriate warehouse, database, and schema:

  USE WAREHOUSE <<Warehouse>>;
  USE DATABASE <<Database>>;
  USE SCHEMA <<Schema>>;

• Create the API integration:

  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');

• 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';

• Grant usage rights to the SYSADMIN role:

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

• 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(); // Remove last element which contains preference
  }
  return {"body": {"preferences": preferences, "data": EVENT.body.data}};
  ';

• Grant usage rights to the SYSADMIN role:

  GRANT USAGE ON FUNCTION PRECISELY_GEOCODE_REQUEST_TRANSLATOR(OBJECT) TO SYSADMIN;

• 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';

• Grant usage rights to the SYSADMIN role:

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

By following the steps outlined above, you can successfully set up external UDFs in Snowflake for address verification and geocoding. These functions leverage an AWS API Gateway to process address data efficiently and accurately.