Creates a service function.
This command supports the following variants:
CREATE OR ALTER FUNCTION (Snowpark Container Services): Creates a service function if it doesn’t exist or alters an existing service function.
Service functions, CREATE EXTERNAL FUNCTION, DESC FUNCTION, DROP FUNCTION, ALTER FUNCTION, CREATE OR ALTER <object>
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> ]
[ MAX_BATCH_RETRIES = <integer> ]
[ ON_BATCH_FAILURE = { ABORT | RETURN_NULL } ]
[ BATCH_TIMEOUT_SECS = <integer> ]
AS '<http_path_to_request_handler>'
Copy
Variant syntax¶ CREATE OR ALTER FUNCTION (Snowpark Container Services)¶Creates a new service function if it doesn’t already exist, or transforms an existing service function into the service function defined in the statement. A CREATE OR ALTER FUNCTION (Snowpark Container Services) statement follows the syntax rules of a CREATE FUNCTION (Snowpark Container Services) statement and has the same limitations as an ALTER FUNCTION (Snowpark Container Services) statement.
Supported function alterations include changes to the following:
CONTEXT_HEADERS
SERVICE
ENDPOINT
MAX_BATCH_ROWS
MAX_BATCH_RETRIES
ON_BATCH_FAILURE
BATCH_TIMEOUT_SECS
For more information, see CREATE OR ALTER FUNCTION (Snowpark Container Services) usage notes.
CREATE [ OR ALTER ] FUNCTION ...
Copy
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.
[ [ 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. SpecifyingIMMUTABLEfor 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
MAX_BATCH_RETRIES = integer
Specifies the number of times you want Snowflake to retry a failed batch.
Default: 3
ON_BATCH_FAILURE = { ABORT | RETURN_NULL }
Specifies the behavior of the function after Snowflake reaches the maximum number of retries processing the batch.
ABORT: Service function aborts execution. Any remaining batches of rows are not processed.
RETURN_NULL: Service function returns a NULL for each row in the failed batch and continues processing the remaining batches. If you choose this option, note the following caveats:
If these batches depend on each other and one batch fails, this could lead to unexpected results.
If your service can return a NULL as a valid response, then it is not possible to differentiate NULL returned by Snowflake due to batch failure and NULL returned by your service.
Default: ABORT
BATCH_TIMEOUT_SECS = integer
Specifies the maximum duration for processing a single batch of rows, including retries (and polling for async function requests), after which Snowflake should terminate the batch request.
Acceptable Values: greater than 0 and less than or equal to 604800 seconds (7 days).
Default: 604800 seconds (7 days)
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.
A role used to execute this operation must have the following privileges at a minimum:
Privilege
Object
Notes
CREATE FUNCTION
Schema
USAGE
Service Endpoint
Usage on a service endpoint is granted to service roles defined in the service specification. You then grant the service role to the role creating the service function.
The USAGE privilege on the parent database and schema are required to perform operations on any object in a 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.
General 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.
The following alterations are not supported:
RETURNS
Volatility (VOLATILE/IMMUTABLE)
Null handling (CALLED ON NULL INPUT / RETURNS NULL ON NULL)
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';
Copy
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.
Alter a function my_echo_udf to set the maximum number of batch rows to 100, and add a context header and endpoint:
CREATE OR ALTER FUNCTION my_echo_udf (InputText VARCHAR) RETURNS VARCHAR SERVICE = echo_service ENDPOINT = reverse_echoendpoint CONTEXT_HEADERS = (current_account) MAX_BATCH_ROWS = 100 AS '/echo';
Copy
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4