You are viewing documentation about an older version (1.9.0). View latest version

snowflake.snowpark.functions.sproc¶

snowflake.snowpark.functions.sproc(func: Optional[Callable] = None, *, return_type: Optional[DataType] = None, input_types: Optional[List[DataType]] = None, name: Optional[Union[str, Iterable[str]]] = None, is_permanent: bool = False, stage_location: Optional[str] = None, imports: Optional[List[Union[str, Tuple[str, str]]]] = None, packages: Optional[List[Union[str, module]]] = None, replace: bool = False, if_not_exists: bool = False, session: Optional[Session] = None, parallel: int = 4, statement_params: Optional[Dict[str, str]] = None, execute_as: Literal['caller', 'owner'] = 'owner', strict: bool = False, source_code_display: bool = True, external_access_integrations: Optional[List[str]] = None, secrets: Optional[Dict[str, str]] = None, **kwargs) → Union[StoredProcedure, partial][source]¶

Registers a Python function as a Snowflake Python stored procedure and returns the stored procedure.

It can be used as either a function call or a decorator. In most cases you work with a single session. This function uses that session to register the stored procedure. If you have multiple sessions, you need to explicitly specify the session parameter of this function. If you have a function and would like to register it to multiple databases, use session.sproc.register instead. See examples in StoredProcedureRegistration.

Note that the first parameter of your function should be a snowpark Session. Also, you need to add snowflake-snowpark-python package (version >= 0.4.0) to your session before trying to create a stored procedure.

Parameters:

func – A Python function used for creating the stored procedure.
return_type – A DataType representing the return data type of the stored procedure. Optional if type hints are provided.
input_types – A list of DataType representing the input data types of the stored procedure. Optional if type hints are provided.
name – A string or list of strings that specify the name or fully-qualified object identifier (database name, schema name, and function name) for the stored procedure in Snowflake, which allows you to call this stored procedure in a SQL command or via session.call(). If it is not provided, a name will be automatically generated for the stored procedure. A name must be specified when is_permanent is True.
is_permanent – Whether to create a permanent stored procedure. The default is False. If it is True, a valid stage_location must be provided.
stage_location – The stage location where the Python file for the stored procedure and its dependencies should be uploaded. The stage location must be specified when is_permanent is True, and it will be ignored when is_permanent is False. It can be any stage other than temporary stages and external stages.
imports – A list of imports that only apply to this stored procedure. You can use a string to represent a file path (similar to the path argument in add_import()) in this list, or a tuple of two strings to represent a file path and an import path (similar to the import_path argument in add_import()). These stored-proc-level imports will override the session-level imports added by add_import().
packages – A list of packages that only apply to this stored procedure. These stored-proc-level packages will override the session-level packages added by add_packages() and add_requirements(). To use Python packages that are not available in Snowflake, refer to custom_package_usage_config().
replace – Whether to replace a stored procedure that already was registered. The default is False. If it is False, attempting to register a stored procedure with a name that already exists results in a SnowparkSQLException exception being thrown. If it is True, an existing stored procedure with the same name is overwritten.
if_not_exists – Whether to skip creation of a stored procedure the same procedure is already registered. The default is False. if_not_exists and replace are mutually exclusive and a ValueError is raised when both are set. If it is True and a stored procedure is already registered, the registration is skipped.
session – Use this session to register the stored procedure. If it’s not specified, the session that you created before calling this function will be used. You need to specify this parameter if you have created multiple sessions before calling this method.
parallel – The number of threads to use for uploading stored procedure files with the PUT command. The default value is 4 and supported values are from 1 to 99. Increasing the number of threads can improve performance when uploading large stored procedure files.
execute_as – What permissions should the procedure have while executing. This supports caller, or owner for now. See owner and caller rights for more information.
statement_params – Dictionary of statement level parameters to be set while executing this action.
strict – Whether the created stored procedure is strict. A strict stored procedure will not invoke the stored procedure if any input is null. Instead, a null value will always be returned. Note that the stored procedure might still return null for non-null inputs.
source_code_display – Display the source code of the stored procedure func as comments in the generated script. The source code is dynamically generated therefore it may not be identical to how the func is originally defined. The default is True. If it is False, source code will not be generated or displayed.
external_access_integrations – The names of one or more external access integrations. Each integration you specify allows access to the external network locations and secrets the integration specifies.
secrets – The key-value pairs of string types of secrets used to authenticate the external network location. The secrets can be accessed from handler code. The secrets specified as values must also be specified in the external access integration and the keys are strings used to retrieve the secrets using secret API.

Returns:

A stored procedure function that can be called with python value.

Note

1. When type hints are provided and are complete for a function, return_type and input_types are optional and will be ignored. See details of supported data types for stored procedure in StoredProcedureRegistration.

You can use Variant to annotate a variant, and use Geography or Geometry to annotate geospatial types when defining a stored procedure.

typing.Union is not a valid type annotation for stored procedures, but typing.Optional can be used to indicate the optional type.

2. A temporary stored procedure (when is_permanent is False) is scoped to this session and all stored procedure related files will be uploaded to a temporary session stage (session.get_session_stage()). For a permanent stored procedure, these files will be uploaded to the stage that you provide.

3. By default, stored procedure registration fails if a function with the same name is already registered. Invoking sproc() with replace set to True will overwrite the previously registered function.

4. To describe the return type for a stored procedure that returns tabular data, use one of the following ways:

(Recommended) Describe the return type using StructType and StructField. Set return_type = StructType([StructField("a", DataTypeA()), ...]) to describe the case RETURNS TABLE(A DataTypeA, ...).

Set return_type = StructType() to describe the case RETURNS TABLE().

When using type hints, the return type of function can be set as DataFrame. This registers a table stored procedure with return type defined using RETURNS TABLE(). Check See also below for more examples.