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
We have introduced SOLE for Data Products as a new framework for SOLE to help you easily build an ecosystem of data products. Learn more about SOLE for Data Products which is currently available as a private preview.
- Function in Current Configuration
- Function in SOLE for Data Products
- Overloading
databases:
<database-name>:
schemas:
<schema-name>:
functions:
<function-name>:
<configuration-key>: <value>
grants:
<privilege>:
- <role-name>
- <role-name>
databases:
<database-name>:
schemas:
<schema-name>:
functions:
<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 Examples.
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.
Due to a Snowflake limitation, you can't create a UDF and procedure with the same signature in the same schema.
- function:
name: <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>)
Supported parameters
The engine supports the parameters listed below.
Configuration Key | Required/Optional | Data Types and Values | Description |
---|---|---|---|
return_type | Required | String | Specifies the data type returned by the function |
statement | Required | String | Specifies the JavaScript / Java / SQL/ Python code used to create the function |
argument | Optional | Object: See here for a definition of argument | List of the arguments for the function |
comment | Optional | String | Specifies a comment for the function |
deleted | Optional | Boolean: True enables deletion prevention, False does nothing | Specifies what objects are allowed to be deleted |
grants | Optional | Map: See Supported Function Grants to Roles | Lists Privileges and Roles to which privileges are granted on the current function |
handler | Optional | String | The handler method for Java function |
Imports | Optional | List of String | Jar files to import for Java function |
language | Optional | String: java , javascript , sql , python | The language of the statement |
manage_mode | Optional | String: all (default), none , grants | Configures what properties to manage for the function. See Changing Manage Mode before changing the value. |
null_input_behavior | Optional | String | Specifies the behavior of the external function when called with null inputs |
packages | Optional | List of String | Jar files to import for Java function |
return_behavior | Optional | String | Specifies the behavior of the function when returning results |
runtime_version | Optional | String | Required 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_path | Optional | String | The target path for compiled jar file for Java function |
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 arguments parameter, you can specify the arguments for the function.
Multiples arguments in an object format can be listed 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
Supported function grants to roles
Following are the privileges you can grant to roles in the function definition:
- USAGE
- OWNERSHIP
Examples
JavaScript function
- Function in Current Configuration
- Function in SOLE for Data Products
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"
- function:
name: JAVASCRIPT_FUNCTION
database: rel(database.DATABASE_1)
schema: rel(schema.SCHEMA_1)
comment: "Function using JavaScript language"
language: javascript
return_type: VARCHAR
return_behavior: IMMUTABLE
null_input_behavior: "RETURNS NULL ON NULL INPUT"
statement: "return 1"
Java function
- Function in Current Configuration
- Function in SOLE for Data Products
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
- function:
name: JAVA_FUNCTION
database: rel(database.DATABASE_1)
schema: rel(schema.SCHEMA_1)
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
- Function in Current Configuration
- Function in SOLE for Data Products
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
- function:
name: PYTHON_FUNCTION
database: rel(database.DATABASE_1)
schema: rel(schema.SCHEMA_1)
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
- Function in Current Configuration
- Function in SOLE for Data Products
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
- function:
name: SQL_FUNCTION
database: rel(database.DATABASE_1)
schema: rel(schema.SCHEMA_1)
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
- Function in Current Configuration
- Function in SOLE for Data Products
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"
- function:
name: SQL_TABLE_FUNCTION
database: rel(database.DATABASE_1)
schema: rel(schema.SCHEMA_1)
comment: "Function using SQL language that returns table"
language: sql
return_type: "TABLE (A NUMBER, B NUMBER)"
statement: "select 1, 2"