CREATE FUNCTION - Kinetica Docs

CREATE [OR REPLACE] FUNCTION <function name> [RETURNS TABLE (<column definition list>)] [MODE = '<execution mode>'] [RUN_COMMAND = '<command>'] [RUN_COMMAND_ARGS = '<command args>'] FILE PATHS '<file paths>' [WITH OPTIONS (<option name> = '<option value>'[,...])]

Parameters

OR REPLACE

Any existing UDF with the same name will be dropped before creating this one

Name of the UDF, which can be referenced in subsequent commands

RETURNS TABLE

Specifies that the UDF returns a table after execution and defines the column names and types to which the function’s output data is expected to conform

This defines the UDF as a User-Defined Table Function (UDTF), which allows the function to be called within a SQL query; see Executing Functions for details.

MODE

Name of the execution mode for the UDF; the following modes are available:

Constant	Description
`DISTRIBUTED`	The function’s processing will be distributed among the nodes in the cluster
`NONDISTRIBUTED`	The function’s processing will be completed on a single node in the cluster

RUN_COMMAND

The command used to execute the files associated with the UDF; e.g., python

RUN_COMMAND_ARGS

The arguments supplied alongside the given RUN_COMMAND; e.g.:

'udf.py','--username','jdoe','--password','jdoe123'

FILE PATHS

A comma-separated list of single-quoted file paths from which the function and any additional files will be loaded; the file paths should be KiFS locations and exist prior to the UDF/UDTF creation.

WITH OPTIONS

Optional indicator that a comma-delimited list of option/value assignments will follow

Option	Description
`max_concurrency_per_node`	The maximum number of concurrent instances of the function that will be executed per node. Setting to `0` allows unlimited concurrency. The default value is `0`.
`set_environment`	The Python UDF environment to use for this Python UDF.

Examples

Before creating a UDF/UDTF, upload the function file, udf_st.py to a new directory, udf, in KiFS:

Create UDF Directory Example

CREATE DIRECTORY 'udf'

UPLOAD UDF FILE Example

UPLOAD FILE 'udf_st.py'
INTO 'udf'

To create a distributed Python udf_st UDF that uses that file:

CREATE FUNCTION (UDF) Example

CREATE FUNCTION udf_st
MODE = 'distributed'
RUN_COMMAND = 'python'
RUN_COMMAND_ARGS = 'udf/udf_st.py'
FILE PATHS 'kifs://udf/udf_st.py'
WITH OPTIONS (SET_ENVIRONMENT = 'udfe_st')

To create the same function as a UDTF that returns a result set instead:

CREATE FUNCTION (UDTF) Example

CREATE FUNCTION UDTF_ST
RETURNS TABLE
(
	id SMALLINT NOT NULL,
	score REAL
)
MODE = 'distributed'
RUN_COMMAND = 'python'
RUN_COMMAND_ARGS = 'udf/udf_st.py'
FILE PATHS 'kifs://udf/udf_st.py'
WITH OPTIONS (SET_ENVIRONMENT = 'udfe_st')

​Parameters

​Examples

Parameters

Examples