External network access examples¶

This topic provides examples of accessing external network locations from user-defined functions and procedures.

Accessing the Google Translate API¶

The following steps include code to create an external access integration for access to the Google Translation API. The steps add the security integration and the permissions needed to execute the statements.

Create a network rule representing the external location.

For more information about the role of a network rule in external access, see Creating a network rule to represent the external network location.
CREATE OR REPLACE NETWORK RULE google_apis_network_rule MODE = EGRESS TYPE = HOST_PORT VALUE_LIST = ('translation.googleapis.com');
Copy

Create a security integration to hold the OAuth credentials required to authenticate with the external network location specified by the google_apis_network_rule network rule.

For reference information on the command, refer to CREATE SECURITY INTEGRATION (External API Authentication).

CREATE OR REPLACE SECURITY INTEGRATION google_translate_oauth
  TYPE = API_AUTHENTICATION
  AUTH_TYPE = OAUTH2
  OAUTH_CLIENT_ID = 'my-client-id'
  OAUTH_CLIENT_SECRET = 'my-client-secret'
  OAUTH_TOKEN_ENDPOINT = 'https://oauth2.googleapis.com/token'
  OAUTH_AUTHORIZATION_ENDPOINT = 'https://accounts.google.com/o/oauth2/auth'
  OAUTH_ALLOWED_SCOPES = ('https://www.googleapis.com/auth/cloud-platform')
  ENABLED = TRUE;

Copy

Create a secret to represent the credentials contained by the google_translate_oauth security integration.

For more information about the role of the secret in external access, see Creating a secret to represent credentials.

For the OAUTH_REFRESH_TOKEN value, you’ll need to specify a refresh token that you obtain from the service provider (in this case, for the Google Cloud Translation API service). For example, to get the refresh token, you can use:
- Google OAuth Playground. There, in Step 1, select and authorize the Cloud Translation API. In Step 2, click exchange authorization code for tokens, then copy the refresh token token value.
CREATE OR REPLACE SECRET oauth_token TYPE = oauth2 API_AUTHENTICATION = google_translate_oauth OAUTH_REFRESH_TOKEN = 'my-refresh-token';
Copy
Create a developer role and grant to it READ privileges on the secret. This role will be assigned to users who need to create a UDF or procedure that uses the secret.

Also, create the role that users will use to call the function.
USE ROLE USERADMIN; CREATE OR REPLACE ROLE developer; CREATE OR REPLACE ROLE user;
Copy
Grant the READ privilege to the developer role.
GRANT READ ON SECRET oauth_token TO ROLE developer;
Copy
Create an external access integration using the network rule and secret, as described in Creating an external access integration.

To execute this command, you must use a role that has the CREATE INTEGRATION privilege, which the ACCOUNTADMIN role has by default.
USE ROLE ACCOUNTADMIN; CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION google_apis_access_integration ALLOWED_NETWORK_RULES = (google_apis_network_rule) ALLOWED_AUTHENTICATION_SECRETS = (oauth_token) ENABLED = TRUE;
Copy
Grant USAGE privileges on the integration to the developer role so that UDF developers can use it. To use the integration for access to an external network location in a UDF or procedure, users must use a role with the USAGE privilege for the integration.
GRANT USAGE ON INTEGRATION google_apis_access_integration TO ROLE developer;
Copy

Create a UDF google_translate_python that translates the specified text into a phrase in the specified language. For more information, refer to Using the external access integration in a function or procedure.

USE ROLE developer;

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']
$$;

Copy

Grant the USAGE privilege on the google_translate_python function so that those with the users role can call it.
GRANT USAGE ON FUNCTION google_translate_python(string, string) TO ROLE user;
Copy

Execute the google_translate_python function to translate a phrase.

USE ROLE user;
SELECT google_translate_python('Happy Thursday!', 'zh-CN');

Copy

This generates the following output.

-------------------------------------------------------
| GOOGLE_TRANSLATE_PYTHON('HAPPY THURSDAY!', 'ZH-CN') |
-------------------------------------------------------
| 快乐星期四！                                          |
-------------------------------------------------------

Accessing an external lambda function¶

The following steps include example code to create an external access integration for access to a lambda function external to Snowflake. The example uses a placeholder for the external endpoint itself, but it could be a function available at a REST service endpoint, for example.

The external access is used in a vectorized Python UDF that receives a Pandas DataFrame containing the data.

Create a network rule lambda_network_rule representing the external location my_external_service (here, a placeholder value for the location of an external endpoint).

For more information about the role of a network rule in external access, see Creating a network rule to represent the external network location.
CREATE OR REPLACE NETWORK RULE lambda_network_rule MODE = EGRESS TYPE = HOST_PORT VALUE_LIST = ('my_external_service');
Copy
Create a secret to represent credentials required by the external service.

Handler code later in this example retrieves the credentials from the secret using a Snowflake API for Python.

For more information about the role of the secret in external access, see Creating a secret to represent credentials.
CREATE OR REPLACE SECRET secret_password TYPE = PASSWORD USERNAME = 'my_user_name' PASSWORD = 'my_password';
Copy
Create a developer role and grant to it READ privileges on the secret. This role will be assigned to users who need to create a UDF or procedure that uses the secret.

Also, create the role that users will use to call the function.
USE ROLE USERADMIN; CREATE OR REPLACE ROLE developer; CREATE OR REPLACE ROLE user;
Copy
Grant the READ privilege on the secret to the developer role.
GRANT READ ON SECRET secret_password TO ROLE developer;
Copy
Create an external access integration to specify the external endpoint and credentials through the network rule and secret you created.

To execute this command, you must use a role that has the CREATE INTEGRATION privilege, which the ACCOUNTADMIN role has by default.

For more information on creating an integration, see Creating an external access integration.
USE ROLE ACCOUNTADMIN; CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION lambda_external_access_integration ALLOWED_NETWORK_RULES = (lambda_network_rule) ALLOWED_AUTHENTICATION_SECRETS = (secret_password) ENABLED = TRUE;
Copy

Create a vectorized Python UDF return_double_column that accesses an external network location to process data received as a Pandas DataFrame.

For more information on using external access in a UDF, see Using the external access integration in a function or procedure.

CREATE OR REPLACE FUNCTION return_double_column(x int)
RETURNS INT
LANGUAGE PYTHON
EXTERNAL_ACCESS_INTEGRATIONS = (lambda_external_access_integration)
SECRETS = ('cred' = secret_password)
RUNTIME_VERSION = 3.8
HANDLER = 'return_first_column'
PACKAGES = ('pandas', 'requests')
AS $$
import pandas
import numpy as np
import json
import requests
import base64
import _snowflake
from _snowflake import vectorized
from requests.auth import HTTPBasicAuth
from requests.adapters import HTTPAdapter
from requests.packages.urllib3.util.retry import Retry

session = requests.Session()
retries = Retry(total=10, backoff_factor=1, status_forcelist=[429, 500, 502, 503, 504], allowed_methods = None)

session.mount('https://', HTTPAdapter(max_retries=retries))

@vectorized(input=pandas.DataFrame)
def return_first_column(df):
  request_rows = []

  df.iloc[:,0] = df.iloc[:,0].astype(int)
  request_rows = np.column_stack([df.index, df.iloc[:,0]]).tolist()

  request_payload = {"data" : request_rows}

  username_password_object = _snowflake.get_username_password('cred');
  basic = HTTPBasicAuth(username_password_object.username, username_password_object.password)

  url = 'my_external_service'

  response = session.post(url, json=request_payload, auth=basic)

  response.raise_for_status()
  response_payload = json.loads(response.text)

  response_rows = response_payload["data"]

  return pandas.DataFrame(response_rows)[1]
$$;

Copy

Grant the USAGE privilege on the return_double_column function so that those with the user role can call it.
GRANT USAGE ON FUNCTION return_double_column(int) TO ROLE user;
Copy
Execute the return_double_column function, making a request to the external endpoint.

Code in the following example creates a two-column table and inserts 100,000,000 rows containing 4-byte integers. The code then executes the return_double_column function, passing values from column a for processing by the external endpoint.
CREATE OR REPLACE TABLE t1 (a INT, b INT); INSERT INTO t1 SELECT SEQ4(), SEQ4() FROM TABLE(GENERATOR(ROWCOUNT => 100000000)); SELECT return_double_column(a) AS retval FROM t1 ORDER BY retval;
Copy