Kinetica provides support for User-Defined Function (UDF) creation and management in SQL. Unlike conventional UDFs, Kinetica UDFs are external programs that can be managed via SQL and may run in distributed fashion across the cluster.
A UDF that is defined to return a specific table type is known as a User-Defined Table Function, which can be used within a SELECT statement to return the output of the function as a query result set.
UDF/UDTF features accessible via SQL include:
For UDF/UDTF execute permission management, see:
Creates a new UDF/UDTF with the given options. A UDTF is simply a UDF defined to return a single table, using the RETURNS TABLE clause. Only users with the SYSTEM ADMIN permission can create UDFs & UDTFs.
|OR REPLACE||Any existing UDF with the same name will be dropped before creating this one|
|<function name>||Name of the UDF, which can be referenced in subsequent commands|
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.
Name of the execution mode for the UDF; the following modes are available:
|RUN_COMMAND||The command used to execute the files associated with the UDF; e.g., python, java|
|RUN_COMMAND_ARGS||The arguments supplied alongside the given RUN_COMMAND; e.g., -jar udf.jar, --username jdoe --password 'Password123!'|
A comma-separated list of single-quoted file paths from which the function and any additional files will be loaded; the file paths can refer to one of two locations:
The files must exist prior to the UDF/UDTF creation.
Optional indicator that a comma-delimited list of option/value assignments will follow
For example, to create a distributed Python UDF_SOS_PY_PROC UDF that returns a table featuring two columns (noting that the udf_sos_py_proc.py file was uploaded prior to running the example):
To create the same function as a UDTF with files loaded from KiFS instead:
An existing UDF/UDTF can be executed using the EXECUTE FUNCTION command. Additionally, a UDTF can be executed as a table function within a SELECT statement. Any user with the SYSTEM ADMIN permission or the EXECUTE FUNCTION permission on a specific UDF/UDTF (or across all UDFs/UDTFs) is allowed to execute it.
The <function name> should be the name of an existing UDF.
The following are the available function parameters that may be passed to a UDF in an EXECUTE FUNCTION or table function call, along with their corresponding function values.
Optional set of "tables" that will be used as input to the UDF/UDTF, specified as a set of queries defining the source data for each "table". The value of this parameter is either the INPUT_TABLE or INPUT_TABLES function, depending on the number of input "tables".
Optional set of tables that will be used as output, specified as a set of names passed as a comma-delimited list of strings to the OUTPUT_TABLES function; e.g.:
Optional list of parameters to pass to the function, specified as a set of key/value pairs passed as a comma-delimited list of <key> = '<value>' assignments to the KV_PAIRS function; e.g.:
KV_PAIRS(customer_id = '1', report_type = 'yearly')
Optional list of function execution options, specified as a set of key/value pairs passed as a comma-delimited list of <key> = '<value>' assignments to the KV_PAIRS function; e.g.:
KV_PAIRS(max_concurrency_per_node = '2')
For example, to execute the UDF_SOS_PY_PROC UDF, specifying an input and output table:
To execute the UDTF_SOS_PY_PROC UDTF, specifying an input table and returning the output as the result set of a SELECT statement:
Removes an existing UDF/UDTF. Only users with the SYSTEM ADMIN permission can remove UDFs.
|<function name>||Name of the UDF to remove|
To drop the UDF_SOS_PY_PROC UDF:
Outputs the configuration of an existing UDF/UDTF. Users with SYSTEM ADMIN permission will see the entire UDF configuration, while users with proc level permissions will only see the name of the UDF & its execution mode.
|<function name>||Name of the UDF whose configuration will be output. Use * instead to output the DDL of all credentials.|
The response to SHOW FUNCTION is a single-column result set with the DDL statement as the value in the DDL column.
To show the UDF_SOS_PY_PROC UDF:
Displays the attributes of the given UDF/UDTF in tabular format. Users with SYSTEM ADMIN permission or proc-level permissions on the function (or across all functions) will be allowed to see the function attributes.
|<function name>||Name of the UDF whose attributes will be output|
The response to DESCRIBE FUNCTION is a six-column result set:
- FUNCTION_NAME - name of the UDF/UDTF
- MODE - execution mode of the function; distributed or non-distributed
- RUN_COMMAND - command used to run the function
- RUN_COMMAND_ARGS - command arguments used to run the function
- FILE_PATHS - list of files involved in the execution of the function
- WITH_OPTIONS - list of key/value pairs of options used to create the function
To describe the UDF_SOS_PY_PROC UDF: