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.

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.

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
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

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

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

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

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 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') |
-------------------------------------------------------
| 快乐星期四！                                          |
-------------------------------------------------------