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, including privileges required, 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, including privileges required, see 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, including privileges required, see Creating a secret to represent credentials.

The secret must specify a refresh token with its OAUTH_REFRESH_TOKEN parameter. To obtain a refresh token from the service provider (in this case, from the Google Cloud Translation API service), you can use a way the provider offers or use Snowflake system functions.

To create a secret with a refresh token, use either Google OAuth Playground or Snowflake system functions, as described by the following:
- Snowflake system functions
  1. Execute CREATE SECRET to create a secret. You’ll update it with the refresh token in a later step.
    USE DATABASE my_db; USE SCHEMA secret_schema; CREATE OR REPLACE SECRET oauth_token TYPE = oauth2 API_AUTHENTICATION = google_translate_oauth;
    Copy
  2. Execute the SYSTEM$START_OAUTH_FLOW function to retrieve a URL with which you can obtain a refresh token, specifying as its argument the name of the secret you created previously.
    CALL SYSTEM$START_OAUTH_FLOW( 'my_db.secret_schema.oauth_token' );
    Copy
    The function will generate a URL you can use to complete the OAuth consent process.
  3. In a browser, visit the generated URL and complete the OAuth2 consent process. When you’ve finished, leave the browser open to the last page of the process.
  4. From the browser address bar, copy all of the text after the question mark in the URL of the last page of the consent process.
  5. Execute the SYSTEM$FINISH_OAUTH_FLOW function, specifying as an argument the parameters you just copied from the browser address bar.
    
    This updates the secret with a refresh token.
    
    Be sure to execute SYSTEM$FINISH_OAUTH_FLOW in the same session as SYSTEM$START_OAUTH_FLOW. SYSTEM$FINISH_OAUTH_FLOW will update the secret you specified in SYSTEM$START_OAUTH_FLOW with access token and refresh token it obtained from the OAuth server.
    CALL SYSTEM$FINISH_OAUTH_FLOW( 'state=<remaining_url_text>' );
    Copy
- Google OAuth Playground
  1. In Google OAuth Playground, select and authorize the Cloud Translation API as specified in step 1.
  2. In Step 2, click exchange authorization code for tokens, then copy the refresh token token value.
  3. Execute CREATE SECRET to create a secret that specifies the refresh token value you copied.
    
    For more information about the role of a secret in external access, including privileges required, see Creating a secret to represent credentials.
    CREATE OR REPLACE SECRET oauth_token TYPE = oauth2 API_AUTHENTICATION = google_translate_oauth OAUTH_REFRESH_TOKEN = 'my-refresh-token';
    Copy
Create an external access integration using the network rule and secret.

For more information about the role of an external access integration, including privileges required, see Creating an external access integration.
```
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
Create a developer role that will be assigned to users who need to create a UDF or procedure that uses the integration.
```
CREATE OR REPLACE ROLE developer;
CREATE OR REPLACE ROLE user;
```
Copy
Grant to the developer role privileges needed to create a UDF that uses the objects for external access. This includes the following:
- The READ privilege on the secret.
- The USAGE privilege on the schema containing the secret.
- The USAGE privilege on the integration.
  GRANT READ ON SECRET oauth_token TO ROLE developer; GRANT USAGE ON SCHEMA secret_schema TO ROLE developer; 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, see 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 user 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.
```
CREATE OR REPLACE ROLE developer;
CREATE OR REPLACE ROLE user;
```
Copy
Grant to the developer role privileges needed to create a UDF that uses the objects for external access. This includes the following:
- The READ privilege on the secret.
- The USAGE privilege on the schema containing the secret.
```
GRANT READ ON SECRET secret_password TO ROLE developer;
GRANT USAGE ON SCHEMA secret_schema TO ROLE developer;
```
Copy
Create an external access integration to specify the external endpoint and credentials through the network rule and secret you created.

For more information about the role of an external access integration, including privileges required, see Creating an external access integration.
```
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