User Defined Functions (UDFs)

CREATE FUNCTION

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.

1
2
3
4
5
6
7

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>'[,...])]

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

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

1

CREATE DIRECTORY 'udf';

1
2

UPLOAD FILE 'udf_st.py'
INTO 'udf'

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

1
2
3
4
5
6

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

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')

Executing Functions

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.

1
2
3
4
5
6

EXECUTE FUNCTION <function name>
(
   <function parameter 1> => <function value 1>,
   ...
   <function parameter N> => <function value N>
)

1
2
3
4
5
6
7
8
9

SELECT * FROM TABLE
(
   <function name>
   (
      <function parameter 1> => <function value 1>,
      ...
      <function parameter N> => <function value N>
   )
)

The <function name> should be the name of an existing UDF.

INPUT_TABLE(customer)

INPUT_TABLE(SELECT id, name FROM customer)

INPUT_TABLES
(
    (SELECT id, name FROM customer),
    (SELECT id, customer_id FROM order)
)

OUTPUT_TABLES('sales_summary', 'customer_profile')

KV_PAIRS(customer_id = '1', report_type = 'yearly')

KV_PAIRS(run_tag = 'my-udf')

1
2
3
4
5
6

EXECUTE FUNCTION udf_st /* KI_HINT_SAVE_UDF_STATS */
(
    INPUT_TABLE_NAMES => INPUT_TABLE(example.udf_st_in),
    OUTPUT_TABLE_NAMES => OUTPUT_TABLES('example.udf_st_out'),
    PARAMS => KV_PAIRS(sentence = 'I live in a neighborhood in a small town.')
)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

SELECT TOP 5 score, sentence
FROM
      example.udf_st_in i,
      TABLE
      (
          UDTF_ST
          (
              INPUT_TABLE_NAMES => INPUT_TABLE(example.udf_st_in),
              PARAMS => KV_PAIRS(sentence = 'I live in a neighborhood in a small town.')
          )
      ) o
WHERE i.id = o.id
ORDER BY score DESC

DROP FUNCTION

Removes an existing UDF/UDTF.

1

DROP FUNCTION <function name>

To drop the UDF_SOS_PY_PROC UDF:

1

DROP FUNCTION UDF_SOS_PY_PROC

SHOW FUNCTION

Outputs the configuration of one or more existing UDFs/UDTFs. Users with SYSTEM ADMIN or ADMIN permission on the UDF (or all UDFs) will see the entire UDF configuration, while users with EXECUTE permissions will only see the name of the UDF & its execution mode.

1

SHOW FUNCTION < <function name> | * >

To show the UDF_SOS_PY_PROC UDF:

1

SHOW FUNCTION UDF_SOS_PY_PROC

SHOW FUNCTION STATUS

Outputs the status of one or more running, or previously running, UDFs/UDTFs. Users with SYSTEM ADMIN or ADMIN permission on all UDFs will see all current & historical UDF runs, while users with EXECUTE or ADMIN permissions on individual UDFs will only see the current & historical runs for UDFs/UDTFs that they have executed themselves. Historical run data is preserved when a UDF/UDTF is executed with the KI_HINT_SAVE_UDF_STATS hint.

1

SHOW FUNCTION STATUS [VERBOSE] [FOR '<run ID>']

To show the status of all UDF/UDTF runs:

1

SHOW FUNCTION STATUS

To show the verbose status of a UDF/UDTF with run ID 1000:

1

SHOW FUNCTION STATUS VERBOSE FOR '1000'

DESCRIBE FUNCTION

Displays the attributes of the given UDF/UDTF in tabular format. Users with SYSTEM ADMIN or ADMIN permission on the UDF (or all UDFs) will see the entire UDF configuration, while users with EXECUTE permissions will only see the name of the UDF & its execution mode.

1

DESC[RIBE] FUNCTION < <function name> | * >

To describe the UDF_SOS_PY_PROC UDF:

1

DESCRIBE FUNCTION UDF_SOS_PY_PROC

CREATE FUNCTION ENVIRONMENT

Creates a new function environment for UDFs/UDTFs written using the Python UDF API, where any packages required by the Python UDF can be installed and used. Packages can be installed with the ALTER FUNCTION ENVIRONMENT command.

1

CREATE [OR REPLACE] FUNCTION ENVIRONMENT <environment name>

To create the Python UDF environment udfe_st:

1

CREATE FUNCTION ENVIRONMENT udfe_st;

ALTER FUNCTION ENVIRONMENT

Manages the installed Python packages associated with the given Python UDF/UDTF environment. Packages are given as a space-separated list of names; and for installations, an optional version number per package can be specified.

1
2

ALTER FUNCTION ENVIRONMENT <environment name>
INSTALL PYTHON PACKAGE '<package name>[==<package version>] [...]>'

1
2

ALTER FUNCTION ENVIRONMENT <environment name>
UNINSTALL PYTHON PACKAGE '<package name> [...]>'

To install the sentence-transformers package and urllib3 version 1.26.18 into the Python UDF environment udfe_st:

1
2

ALTER FUNCTION ENVIRONMENT udfe_st
INSTALL PACKAGE 'urllib3==1.26.18 sentence-transformers'

To uninstall the sentence-transformers & urllib3 packages from the Python UDF environment udfe_st:

1
2

ALTER FUNCTION ENVIRONMENT udfe_st
UNINSTALL PACKAGE 'sentence-transformers urllib3'

DROP FUNCTION ENVIRONMENT

Removes an existing Python UDF/UDTF environment.

1

DROP FUNCTION ENVIRONMENT <environment name>

To remove the Python UDF environment udfe_st:

1

DROP FUNCTION ENVIRONMENT udfe_st

SHOW FUNCTION ENVIRONMENT

Outputs the CREATE FUNCTION ENVIRONMENT statement used to create the given Python UDF/UDTF environment(s).

1

SHOW FUNCTION ENVIRONMENT < <environment name> | * >

To show the Python UDF environment udfe_st:

1

SHOW FUNCTION ENVIRONMENT udfe_st

To show all Python UDF environments:

1

SHOW FUNCTION ENVIRONMENT *

DESCRIBE FUNCTION ENVIRONMENT

Displays the Python package listing for the given Python UDF/UDTF environment(s).

1

DESC[RIBE] FUNCTION ENVIRONMENT < <environment name> | * >

To list the packages in the Python UDF environment udfe_st:

1

DESC FUNCTION ENVIRONMENT udfe_st

To list the packages in all Python UDF environments:

1

DESC FUNCTION ENVIRONMENT *