Skip to content

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

- 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>)
databases:
  <database-name>:
    schemas:
      <schema-name>:
        external_functions:
          <external-function-name>:
            <configuration-key>: <value>
            grants:
              <privilege>:
                - <role-name>
                - <role-name>

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 the examples section on this page.

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.

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
depends_on Optional List of String: See Object Dependencies for a definition of depends_on List of SOLE-managed objects that this external function depends on. Objects defined in the list will be added to the generated HCL dependencies list to ensure proper creation order.
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

Warning

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