Creating and using an external access integration¶

Creating a network rule to represent the external network location¶

You can use the CREATE NETWORK RULE command to create a network rule that represents the external network’s location and restrictions for access. For example, a network rule specifies network identifiers such as a hostname and the direction of communication with the network (ingress or egress).

To support access to an external network, an administrator will include the rule when creating an external access integration. Each rule included in the integration specifies an external network location that the function or procedure is allowed to access.

When creating a network rule for use in an external access integration, you specify the following:

• EGRESS as the MODE parameter value.

• HOST_PORT as the TYPE parameter value.

• The external location’s endpoint in the VALUE_LIST parameter.

• (Optional) A port number with the external location’s endpoint name. If you omit a port number, Snowflake will use the default port number for external access, 443.

  For example, if the endpoint requires port 80, the VALUE_LIST parameter might be as follows:

  VALUE_LIST = ('example.com:80')
  

  Copy

CREATE OR REPLACE NETWORK RULE google_apis_network_rule
  MODE = EGRESS
  TYPE = HOST_PORT
  VALUE_LIST = ('translation.googleapis.com');

Creating a secret to represent credentials¶

You can use CREATE SECRET to create a secret that represents credentials required to authenticate with the external network location. For example, the secret can contain credentials such as a username and password.

For access to an external network location that supports OAuth, a best practice is have your secret contain a reference to a security integration that contains values needed for OAuth flow such as a client ID, client secret, token endpoint, and so on.

The secret will be used in the following ways:

• By an administrator when creating the external access integration.

  When creating the integration, the administrator will specify the secrets that developers may use in handler code when creating a function or procedure that uses the integration.

• By a developer when creating a UDF or procedure handler.

  The developer will specify the allowed secret that contains the credentials that handler code can use to authenticate when making a request to the external location. When writing a handler, a developer can use a Snowflake API to retrieve credentials contained by the secret rather than including the credentials as literal values in handler code.

CREATE OR REPLACE SECRET oauth_token
  TYPE = OAUTH2
  API_AUTHENTICATION = google_translate_oauth
  OAUTH_REFRESH_TOKEN = 'my-refresh-token';

CREATE OR REPLACE SECRET bp_maps_api
  TYPE = GENERIC_STRING
  SECRET_STRING = 'replace-with-your-api-key';

Creating an external access integration¶

You can use the CREATE EXTERNAL ACCESS INTEGRATION command to create an external access integration that aggregates allowed network rules (representing external network locations) and allowed secrets (representing credentials for authenticating) for use with UDFs and procedures.

In particular, the external access integration specifies those network rules and secrets that UDFs and procedures referencing the integration may use.

The external access integration will be used by an administrator to manage access to external network locations from UDFs and procedures. The integration specifies only those locations and credentials allowed for use by UDFs and procedures that reference the integration. An administrator can also enable or disable the integration to manage access to external locations.

CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION google_apis_access_integration
  ALLOWED_NETWORK_RULES = (google_apis_network_rule)
  ALLOWED_AUTHENTICATION_SECRETS = (oauth_token)
  ENABLED = true;

Using the external access integration in a function or procedure¶

When using the CREATE FUNCTION or CREATE PROCEDURE command to create a UDF or procedure, you can enable access to external network locations as follows:

• Include the EXTERNAL_ACCESS_INTEGRATIONS parameter, setting its value to one or more integrations.

  Each integration you specify allows access to the external network locations and secrets the integration specifies.

• Include the SECRETS parameter, setting its value to one or more secrets and the names you’ll use to access them from handler code.

  The secrets you specify as values must also be specified in the external access integration.

• In handler code, access the secret to retrieve credentials for authenticating with the external network location.

Snowflake limits the total number of connections that can be made from a particular UDF. To avoid running into resource exhaustion issues, reuse connections as much as possible. You can achieve this by creating the TCP client or session once during the UDF initialization, then using it in the UDF handler for the rest of the query.

CREATE OR REPLACE FUNCTION google_translate_python(sentence STRING, language STRING)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = 3.8
HANDLER = 'get_translation'
EXTERNAL_ACCESS_INTEGRATIONS = (google_apis_access_integration)
PACKAGES = ('snowflake-snowpark-python','requests')
SECRETS = ('cred' = oauth_token )
AS
$$
import _snowflake
import requests
import json
session = requests.Session()
def get_translation(sentence, language):
  token = _snowflake.get_oauth_access_token('cred')
  url = "https://translation.googleapis.com/language/translate/v2"
  data = {'q': sentence,'target': language}
  response = session.post(url, json = data, headers = {"Authorization": "Bearer " + token})
  return response.json()['data']['translations'][0]['translatedText']
$$;