Skip to main content

Function

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

  • Manage the lifecycle of new and existing functions
  • Manage grants of a 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. The major difference is in how you define Snowflake objects in the configuration file. Rather than having a grouped collection of objects, SOLE for Data Products goes for modular, self-describing, and explicit object definition.
Learn more about SOLE for Data Products, currently available as a private preview.

We have also introduced Data products as an extra layer on top of the data product platform capabilities making managing data products easier than ever. Learn more about Data Products, currently available as a private preview.

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

Supported parameters

The engine supports the parameters listed below.

Configuration KeyRequired/OptionalData Types and ValuesDescription
return_typeRequiredStringSpecifies the data type returned by the function
statementRequiredStringSpecifies the JavaScript / Java / SQL/ Python code used to create the function
argumentOptionalObject: See here for a definition of argumentList of the arguments for the function
commentOptionalStringSpecifies a comment for the function
deletedOptionalBoolean: True enables deletion prevention, False does nothingSpecifies what objects are allowed to be deleted
grantsOptionalMap: See Supported Function Grants to RolesLists Privileges and Roles to which privileges are granted on the current function
handlerOptionalStringThe handler method for Java function
importsOptionalList of String: See here for a definition of importsImplementation files to import for Java function
languageOptionalString: java, javascript, sql, pythonThe language of the statement
manage_modeOptionalString: all (default), none, grantsConfigures what properties to manage for the function.
See Changing Manage Mode before changing the value.
null_input_behaviorOptionalStringSpecifies the behavior of the external function when called with null inputs
packagesOptionalList of StringJar files to import for Java function
return_behaviorOptionalStringSpecifies the behavior of the function when returning results
runtime_versionOptionalStringRequired for Python functions. It specifies the Python runtime version in a string format. For example, 3.8 should be specified as runtime_version: "3.8"
target_pathOptionalStringThe target path for compiled jar file for Java function
warning

During subsequent pipeline runs, a force-replacement behavior might be observed in PLAN and APPLY phases for a few parameters like argument, return_type, statement, language, etc.

argument parameter

In the argument parameter, you can specify the arguments for the function.

You can list multiples arguments in an object format in the argument parameter, with each having the following supported parameters:

  • TYPE: Data Type of the Column
    • REQUIRED
    • Configuration key: type
    • Data Type: String

Example

arguments:
ARG_1:
type: VARCHAR
ARG_2:
type: DATE

imports parameter

This parameter helps specify the implementation files to import for the Java function, where the implementation is in a binary Python package or Jar files. However, when importing a Python module, you must have the handler and packages keywords in the DDL.

Example

databases:
my_db:
schemas:
my_schema:
functions:
skimage_parser_fn:
language: PYTHON
return_type: VARIANT
arguments:
image_fl:
type: VARCHAR
runtime_version: "3.8"
packages:
[
"snowflake-snowpark-python",
"numpy",
"pandas",
"scikit-learn",
"scikit-image",
]
imports: ["@named_stage/scripts/skimage_handler_fn.py"]
handler: skimage_handler_fn.main

Supported function grants to roles

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

  • USAGE
  • OWNERSHIP

Examples

JavaScript function

databases:
DATABASE_1:
schemas:
SCHEMA_1:
functions:
JAVASCRIPT_FUNCTION:
comment: "Function using JavaScript language"
language: javascript
arguments:
ARG_1:
type: VARCHAR
ARG_2:
type: DATE
return_type: VARCHAR
return_behavior: IMMUTABLE
null_input_behavior: "RETURNS NULL ON NULL INPUT"
statement: "return 1"
JAVASCRIPT_FUNCTION|11:
comment: "Function using JavaScript language"
language: javascript
arguments:
ARG_1:
type: VARCHAR(16777216)
return_type: VARCHAR
return_behavior: IMMUTABLE
null_input_behavior: "RETURNS NULL ON NULL INPUT"
statement: "return 1"
JAVASCRIPT_FUNCTION|VARCHAR(16777216)|VARCHAR(16777216):
comment: "Function using JavaScript language"
language: javascript
arguments:
ARG_1:
type: VARCHAR(16777216)
ARG_2:
type: VARCHAR(16777216)
return_type: VARCHAR
return_behavior: IMMUTABLE
null_input_behavior: "RETURNS NULL ON NULL INPUT"
statement: "return 1"
JAVASCRIPT_FUNCTION|VARCHAR(16777216)|VARCHAR(16777216)|DATE:
comment: "Function using JavaScript language"
language: javascript
arguments:
ARG_1:
type: VARCHAR(16777216)
ARG_2:
type: VARCHAR(16777216)
ARG_3:
type: DATE
return_type: VARCHAR
return_behavior: IMMUTABLE
null_input_behavior: "RETURNS NULL ON NULL INPUT"
statement: "return 1"

Java function

databases:
DATABASE_1:
schemas:
SCHEMA_1:
functions:
JAVA_FUNCTION:
comment: "Function using Java language"
language: java
return_type: VARCHAR
return_behavior: IMMUTABLE
handler: "CoolFunc.test"
null_input_behavior: "RETURNS NULL ON NULL INPUT"
statement: 'class CoolFunc {public static String test(int n) {return "hello!";}}'
arguments:
ARG1:
type: NUMBER

Python function

databases:
DATABASE_1:
schemas:
SCHEMA_1:
functions:
PYTHON_FUNCTION:
comment: "Function using Python language"
language: python
return_type: NUMBER(38,0)
return_behavior: VOLATILE
null_input_behavior: "CALLED ON NULL INPUT"
runtime_version: "3.8"
handler: "add_py"
statement: "def add_py(i): return i+1"
arguments:
ARG1:
type: NUMBER

SQL function

databases:
DATABASE_1:
schemas:
SCHEMA_1:
functions:
SQL_FUNCTION:
comment: "Function using SQL language"
language: sql
return_type: NUMBER(38,0)
return_behavior: IMMUTABLE
null_input_behavior: "RETURNS NULL ON NULL INPUT"
statement: "a * b"
arguments:
A:
type: NUMBER
B:
type: NUMBER

SQL function that returns a table

databases:
DATABASE_1:
schemas:
SCHEMA_1:
functions:
SQL_TABLE_FUNCTION:
comment: "Function using SQL language that returns table"
language: sql
return_type: "TABLE (A NUMBER, B NUMBER)"
statement: "select 1, 2"