CREATE FUNCTION (Snowpark Container Services)¶

Syntax¶

CREATE [ OR REPLACE ] FUNCTION <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS <result_data_type>
  [ [ NOT ] NULL ]
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ { VOLATILE | IMMUTABLE } ]
  SERVICE = <service_name>
  ENDPOINT = <endpoint_name>
  [ COMMENT = '<string_literal>' ]
  [ CONTEXT_HEADERS = ( <context_function_1> [ , <context_function_2> ...] ) ]
  [ MAX_BATCH_ROWS = <integer> ]
  AS '<http_path_to_request_handler>'

Required parameters¶

name

Specifies the identifier (name) and any input arguments for the function.

• The identifier does not need to be unique for the schema in which the function is created because functions are identified and resolved by the combination of the name and argument types.

• The identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (for example, “My object”). Identifiers enclosed in double quotes are also case-sensitive. See Identifier requirements.

( [ arg_name arg_data_type ] [ , ... ] )

Specifies the arguments/inputs for the service function. These should correspond to the arguments that the service expects.

If there are no arguments, then include the parentheses without any argument name(s) and data type(s).

RETURNS result_data_type

Specifies the data type of the result returned by the function.

SERVICE = service_name

Specifies the name of the Snowpark Container Services service.

ENDPOINT = endpoint_name

Specifies the name of the endpoint as defined in the service specification.

AS http_path_to_request_handler

Specifies the HTTP path to the service code that is executed when the function is called.

Optional parameters¶

[ [ NOT ] NULL ]

Specifies whether the function can return NULL values or must return only NON-NULL values. The default is NULL (that is, the function can return NULL).

CALLED ON NULL INPUT or . { RETURNS NULL ON NULL INPUT | STRICT }

Specifies the behavior of the function when called with null inputs. In contrast to system-defined functions, which always return null when any input is null, functions can handle null inputs, returning non-null values even when an input is null:

• CALLED ON NULL INPUT will always call the function with null inputs. It’s up to the function to handle such values appropriately.

• RETURNS NULL ON NULL INPUT (or its synonym STRICT) will not call the function if any input is null. Instead, a null value will always be returned for that row. Note that the function might still return null for non-null inputs.

Default: CALLED ON NULL INPUT

{ VOLATILE | IMMUTABLE }

Specifies the behavior of the function when returning results:

• VOLATILE: function might return different values for different rows, even for the same input (for example, due to non-determinism and statefulness).

• IMMUTABLE: function assumes that the function, when called with the same inputs, will always return the same result. This guarantee is not checked. Specifying IMMUTABLE for a function that returns different values for the same input will result in undefined behavior.

Default: VOLATILE

[ MAX_BATCH_ROWS = integer ]

Specifies the batch size when sending data to a service to increase concurrency

COMMENT = 'string_literal'

Specifies a comment for the function, which is displayed in the DESCRIPTION column in the SHOW FUNCTIONS and SHOW USER FUNCTIONS output.

Default: user-defined function

CONTEXT_HEADERS = ( context_function_1 [ , context_function_2 ...] )

This binds Snowflake context function results to HTTP headers. (For more information about Snowflake context functions, see: Context functions.)

Not all context functions are supported in context headers. The following are supported:

• CURRENT_ACCOUNT()

• CURRENT_CLIENT()

• CURRENT_DATABASE()

• CURRENT_DATE()

• CURRENT_IP_ADDRESS()

• CURRENT_REGION()

• CURRENT_ROLE()

• CURRENT_SCHEMA()

• CURRENT_SCHEMAS()

• CURRENT_SESSION()

• CURRENT_STATEMENT()

• CURRENT_TIME()

• CURRENT_TIMESTAMP()

• CURRENT_TRANSACTION()

• CURRENT_USER()

• CURRENT_VERSION()

• CURRENT_WAREHOUSE()

• LAST_QUERY_ID()

• LAST_TRANSACTION()

• LOCALTIME()

• LOCALTIMESTAMP()

When function names are listed in the CONTEXT_HEADERS clause, the function names should not be quoted.

Snowflake prepends sf-context to the header before it’s written to the HTTP request.

Example:

CONTEXT_HEADERS = (current_timestamp)

Copy

In this example, Snowflake writes the header sf-context-current-timestamp into the HTTP request.

Context functions can generate characters that are illegal in HTTP header values, including (but not limited to) the following:

• newline

• Ä

• Î

• ß

• ë

• ¬

• ±

• ©

• ®

Snowflake replaces each sequence of one or more illegal characters with one space character. (The replacement is per sequence, not per character.)

For example, suppose that the context function CURRENT_STATEMENT() returns the following:

select
  /*ÄÎß묱©®*/
  my_service_function(1);

Copy

The value sent in sf-context-current-statement is the following:

select /* */ my_service_function(1);

Copy

To ensure that your service code can access the original result (with illegal characters) from the context function even if illegal characters have been replaced, Snowflake also sends a binary context header that contains the context function result encoded in base64.

In the example above, the value sent in the base64-encoded header is the result of the following call:

base64_encode('select\n/ÄÎß묱©®/\nmy_service_function(1)')

Copy

The remote service is responsible for decoding the base64 value if needed.

Each such base64 header is named according to the following convention:

sf-context-<context-function>-base64

Copy

In the example above, the name of the header would be the following:

sf-context-current-statement-base64

Copy

If no context headers are sent, then no base64 context headers are sent.

If the rows sent to a service function are split across multiple batches, then all batches contain the same context headers and the same binary context headers.

Access control requirements¶

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

Note that operating on any object in a schema also requires the USAGE privilege on the parent database and schema.

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¶

• CREATE OR REPLACE <object> statements are atomic. That is, when an object is replaced, the old object is deleted and the new object is created in a single transaction.

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

In Tutorial-1, you create the following service function:

CREATE FUNCTION my_echo_udf (InputText VARCHAR)
  RETURNS VARCHAR
  SERVICE=echo_service
  ENDPOINT=echoendpoint
  AS '/echo';

This function connects with the specific ENDPOINT of the specified SERVICE. When you invoke this function, Snowflake sends a request to the /echo path inside the service container.

Note the following:

• The my_echo_udf function takes a string as input and returns a string.

• The SERVICE property identifies the service (echo_service), and the ENDPOINT property identifies the user-friendly endpoint name (echoendpoint).

• The AS '/echo' specifies the path for the service. In echo_service.py (see service code), the @app.post decorator associates this path with the echo function.