CREATE EXTERNAL ACCESS INTEGRATION

Creates an external access integration for access to external network locations from a UDF or procedure handler.

See also:

ALTER EXTERNAL ACCESS INTEGRATION , DROP INTEGRATION , SHOW INTEGRATIONS , DESCRIBE INTEGRATION

Syntax

CREATE [ OR REPLACE ] EXTERNAL ACCESS INTEGRATION <name>
  ALLOWED_NETWORK_RULES = ( <rule_name_1> [, <rule_name_2>, ... ] )
  [ ALLOWED_API_AUTHENTICATION_INTEGRATIONS = ( <integration_name_1> [, <integration_name_2>, ... ] ) ]
  [ ALLOWED_AUTHENTICATION_SECRETS = ( <secret_name_1> [, <secret_name_2>, ... ] ) ]
  ENABLED = { TRUE | FALSE }
  [ COMMENT = '<string_literal>' ]
Copy

Required Parameters

name

Identifier for the external access integration.

The identifier value must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (e.g. "My object"). Identifiers enclosed in double quotes are case-sensitive.

For more details, see Identifier requirements.

ALLOWED_NETWORK_RULES = (rule_name [ , rule_name ... ])

Specifies the allowed network rules. Only egress rules may be specified.

For reference information about network rules, refer to CREATE NETWORK RULE.

ENABLED = { TRUE | FALSE }

Specifies whether this integration is enabled or disabled. If the integration is disabled, any handler code that relies on it will be unable to reach the external network location.

The value is case-insensitive.

The default is TRUE.

Optional Parameters

ALLOWED_API_AUTHENTICATION_INTEGRATIONS = ( integration_name_1 [, integration_name_2, ... ] )

Specifies the security integrations whose OAuth authorization server issued the secret used by the UDF or procedure. The security integration must be the type used for external API integration.

For reference information about security integrations, refer to CREATE SECURITY INTEGRATION (External API Authentication).

ALLOWED_AUTHENTICATION_SECRETS = (secret_name [ , secret_name ... ])

Specifies the secrets that a UDF or procedure can use when referring to this integration.

For reference information about secrets, refer to CREATE SECRET.

COMMENT = 'string_literal'

Specifies a comment for the external access integration.

Default: No value

Access Control Requirements

A role used to execute this SQL command must have the following privileges at a minimum:

Privilege

Object

Notes

CREATE INTEGRATION

Account

Only the ACCOUNTADMIN role has this privilege by default. The privilege can be granted to additional roles as needed.

USAGE

Secret

Required for all secrets referenced by the integration.

USAGE

Schema

Required for all schemas containing any secrets referenced by the integration.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

Usage Notes

  • Regarding metadata:

    Attention

    Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata Fields in Snowflake.

Examples

Create an external access integration that provides access to the Google Translation API.

For a more complete example, refer to Creating and using an external access integration.

  1. Create a secret representing credentials.

    To create a secret, you must have been assigned a role with the CREATE SECRET privilege on the current schema. For other kinds of secret supported by this command, refer to CREATE SECRET. In this example, google_translate_oauth refers to a security integration. For more information, refer to CREATE SECURITY INTEGRATION (External API Authentication).

    CREATE OR REPLACE SECRET oauth_token
      TYPE = OAUTH2
      API_AUTHENTICATION = google_translate_oauth
      OAUTH_REFRESH_TOKEN = 'my-refresh-token';
    
    Copy
  2. Grant READ privileges on the secret to the developer role so that UDF developers can use it.

    Create the role that will be required for developers needing to use the secret.

    USE ROLE USERADMIN;
    CREATE OR REPLACE ROLE developer;
    
    Copy

    Grant the READ privilege to the developer role.

    USE ROLE SECURITYADMIN;
    GRANT READ ON SECRET oauth_token TO ROLE developer;
    
    Copy
  3. Create a network rule representing the external network location. Use a role with the privileges described in CREATE NETWORK RULE.

    USE ROLE SYSADMIN;
    CREATE OR REPLACE NETWORK RULE google_apis_network_rule
      MODE = EGRESS
      TYPE = HOST_PORT
      VALUE_LIST = ('translation.googleapis.com');
    
    Copy
  4. Create an external access integration using the secret and network rule.

    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
  5. Grant USAGE privileges on the integration to the developer role so that UDF developers can use it.

    GRANT USAGE ON INTEGRATION google_apis_access_integration TO ROLE developer;
    
    Copy
  6. 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
  7. 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
  8. 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') |
    -------------------------------------------------------
    | 快乐星期四!                                          |
    -------------------------------------------------------