External Function

You can provide configuration to Snowflake Object Lifecycle Engine for the following operations with external function:

• Manage the lifecycle of new and existing external functions
• Manage grants of an external function

Usage

note

We have introduced SOLE for Data Products as a new framework for SOLE to help you easily build an ecosystem of data products. Learn more about SOLE for Data Products which is currently available as a private preview.

External Function in Current Configuration

databases:
  <database-name>:
    schemas:
      <schema-name>:
        external_functions:
          <external-function-name>:
            <configuration-key>: <value>
            grants:
              <privilege>:
                - <role-name>
                - <role-name>

External Function in SOLE for Data Products

databases:
  <database-name>:
    schemas:
      <schema-name>:
        external_functions:
          <external-function-name>| unique identifier:
            <configuration-key>: <value>
            grants:
              <privilege>:
                - <role-name>
                - <role-name>

Overloading means you can add multiple functions with the same name but with different arguments. Use a pipe | after the function name. If you add more than two functions, each function's text after the pipe must be unique. See Examples.

Each function must have a unique signature. A signature is generated based on the following parameters:

• Functions FQN (fully qualified name), which is made up of:
  • Database name
  • Schema name
  • External Function name
• Number of arguments
• Data types of arguments
• The sequence of arguments (mainly data type)

The function is treated as duplicated if you have identical values for the above parameters.

Overloading

- external_function:
    name: <external-function-name>
    <configuration-key>: <value>
    database: rel(database.<database-name>)
    schema: rel(schema.<schema-name>)
    grants:
      <privilege>:
        - rel(role.<role-name>)
        - rel(role.<role-name>)

Supported parameters

The engine supports the parameters listed below.

Configuration Key	Required/Optional	Data Types and Values	Description
api_integration	Required	String	Specifies the name of the API integration object that should be used to authenticate the call to the proxy service
return_behavior	Required	String	Specifies the behavior of the external function when returning results
return_type	Required	String	Specifies the data type returned by the external function
url_of_proxy_and_resource	Required	String	The invocation URL of the proxy service and resource through which Snowflake calls the remote service
arg	Optional	Object: See here for a definition of arg	Specifies the arguments/inputs for the external function. These should correspond to the arguments that the remote service expects.
comment	Optional	String	A description of the external function
compression	Optional	String	If specified, the JSON payload is compressed when sent from Snowflake to the proxy service, and back from the proxy service to Snowflake
context_headers	Optional	List of String	Binds Snowflake context function results to HTTP headers
deleted	Optional	Boolean: True enables deletion prevention, False does nothing	Specifies what objects are allowed to be deleted
header	Optional	Object: See here for a definition of header	Allows you to specify key-value metadata that is sent with every request as HTTP header
grants	Optional	Map: See Supported External Function Grants to Roles	Lists Privileges and Roles to which privileges are granted on the current external function
manage_mode	Optional	String: all (default), none, grants	Configures what properties to manage for the external function. See Changing Manage Mode before changing the value.
max_batch_rows	Optional	Integer	Specifies the maximum number of rows in each batch sent to the proxy service
null_input_behavior	Optional	String	Specifies the behavior of the external function when called with null inputs
return_null_allowed	Optional	Boolean	Indicates whether the function can return NULL values or must return only NON-NULL values

caution

During subsequent pipeline runs a force-replacement behavior might be observed in PLAN and APPLY phases for a few parameters like comment, max_batch_rows, null_input_behavior, return_behavior, etc.

arg parameter

You can specify the arguments for the external function.

Multiples arguments in an object format can be listed in the arg parameter, with each having the following supported parameters:

Configuration Key	Required/Optional	Data Types and Values	Description
type	Required	String	Data-type of the Column

Example

arg:
  ARG_1:
    type: VARCHAR
  ARG_2:
    type: DATE

header parameter

You can specify the header value for the external function.

Multiple values in an object format can be listed in the header parameter with each having the following supported parameters:

Configuration Key	Required/Optional	Data Types and Values	Description
value	Required	String	Data-type of the Column

Example

header:
  HEADER1:
    value: "Header_value_1"
  HEADER2:
    value: "Header_value_2"

Supported external function grants to roles

Following are the privileges you can grant to roles in the external function definition:

• USAGE
• OWNERSHIP

Examples

External Function in Current Configuration

databases:
  EXT_DATABASE:
    schemas:
      EXT_SCHEMA:
        external_functions:
          EXT_1:
            grants:
              USAGE:
                - ACCOUNTADMIN
                - ROLE_1
            api_integration: "EXT_API"
            return_behavior: "IMMUTABLE"
            return_type: "VARCHAR"
            url_of_proxy_and_resource: "https://123456.execute-api.us-west-2.amazonaws.com/prod/"
            header:
              Header1:
                value: "Test_Header"
          EXT_1|2:
            grants:
              USAGE:
                - ACCOUNTADMIN
                - ROLE_1
            api_integration: "EXT_API"
            return_behavior: "IMMUTABLE"
            return_type: "VARCHAR"
            url_of_proxy_and_resource: "https://123456.execute-api.us-west-2.amazonaws.com/prod/"
            arg:
              ARG_1:
                type: VARCHAR(16777216)
            header:
              Header1:
                value: "Test_Header"
          EXT_1|VARCHAR(16777216)|DATE:
            grants:
              USAGE:
                - ACCOUNTADMIN
                - ROLE_1
            api_integration: "EXT_API"
            return_behavior: "IMMUTABLE"
            return_type: "VARCHAR"
            url_of_proxy_and_resource: "https://123456.execute-api.us-west-2.amazonaws.com/prod/"
            arg:
              ARG_1:
                type: VARCHAR(16777216)
              ARG_2:
                type: DATE
            header:
              Header1:
                value: "Test_Header"

External Function in SOLE for Data Products

- external_function:
    name: EXT_1
    database: rel(database.EXT_DATABASE)
    schema: rel(schema.EXT_SCHEMA)
    grants:
      USAGE:
        - ACCOUNTADMIN
        - rel(role.ROLE_1)
    api_integration: "EXT_API"
    return_behavior: "IMMUTABLE"
    return_type: "VARCHAR"
    url_of_proxy_and_resource: "https://123456.execute-api.us-west-2.amazonaws.com/prod/"
    header:
      Header1:
        value: "Test_Header"