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');
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;
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';
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;
Grant the READ privilege to the
developer
role.GRANT READ ON SECRET oauth_token TO ROLE developer;
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;
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;
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'] $$;
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;
Execute the
google_translate_python
function to translate a phrase.USE ROLE user; SELECT google_translate_python('Happy Thursday!', 'zh-CN');
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 locationmy_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');
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';
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;
Grant the READ privilege on the secret to the
developer
role.GRANT READ ON SECRET secret_password TO ROLE developer;
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;
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] $$;
Grant the USAGE privilege on the
return_double_column
function so that those with theuser
role can call it.GRANT USAGE ON FUNCTION return_double_column(int) TO ROLE user;
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 columna
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;