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>' ]
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';
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;
Grant the READ privilege to the
developer
role.USE ROLE SECURITYADMIN; GRANT READ ON SECRET oauth_token TO ROLE developer;
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');
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;
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;
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 user 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') | ------------------------------------------------------- | 快乐星期四! | -------------------------------------------------------