User-Defined Functions
A user-defined function (UDF) is a callable routine that accepts input parameters, executes the function body, and returns a single scalar value. UDFs are used with Dremio in the context of assigning row-level filtering and column-masking policies.
UDFs are supported in Dremio 22.0.0+.
A UDF can serve as a policy on multiple objects, which means you can change the behavior in one place and have the change reflected in multiple places. You may likewise replace or drop a UDF that is being used as a policy, but if you drop the UDF without replacement, the table or view will become unreadable until you remove the policy or create a new UDF for the policy.
To run a UDF, the user must have the EXECUTE privilege on that function.
Showing a Function
SyntaxSHOW FUNCTIONS [ LIKE { pattern } ]
Parameters
pattern Optional
A LIKE
pattern that is used to filter the results of the statement. The leading and trailing blanks are trimmed in the input pattern before processing. The pattern match is case-insensitive.
Examples
Show all existing functionsSHOW FUNCTIONS;
protect_ssn
SHOW FUNCTIONS LIKE 'protect_ssn';
protect_
SHOW FUNCTIONS LIKE 'protect_%';
Creating a Function
SyntaxCREATE [ OR REPLACE ] FUNCTION [ IF NOT EXISTS ]
function_name ( [ function_parameter [, ...] ] )
RETURNS { data_type }
RETURN { query }
Parameters
[ OR REPLACE ] String Optional
If specified, the function with the same name is replaced. You cannot specify this parameter with IF NOT EXISTS
.
[ IF NOT EXISTS ] String Optional
If specified, creates the function only when it does not exist. The creation of the function succeeds (no error results) if the specified function already exists in the system. You cannot specify this parameter with OR REPLACE
. This is mainly useful to avoid trying to create the same function twice.
<function_name> String
The name of the function. Function names within a project must be unique (no overloading), cannot conflict with system-defined functions, and are case-insensitive.
<function_parameter> String
Specifies a parameter of the function:
parameter_name
- The parameter name must be unique within the function signature.data_type
- Any Dremio SQL data type, including primitive and complex types.
RETURNS <data_type> String
The return data type of a scalar function.
RETURNS TABLE ( <column_spec> [, ...] ) String
The signature of the result of a tabular function:
column_name
- The column name must be unique within the result signature.data_type
- Any Dremio SQL data type, including primitive and complex types. A tabular UDF can be accessed only in theFROM
clause of a query.
RETURN { expression | query } String
The body of the function. For a scalar function, it can either be a query or an expression. For a tabular function, it may only be a query. The expression may not contain the following:
- DML statements such as
INSERT
,UPDATE
, orDELETE
. - DDL statements such as
CREATE
andDROP
.
Examples
Create a function with no parametersCREATE FUNCTION hello() RETURNS VARCHAR RETURN SELECT 'Hello World!';
CREATE FUNCTION multiply (x INT, y INT) RETURNS INT RETURN SELECT x * y;
Naming Best Practices
When creating a function:
- Choose a name that best describes the function.
- Avoid using reserved keywords.
- Avoid using spaces and special characters.
Describing a Function
Syntax{ DESC | DESCRIBE } FUNCTION function_name;
Parameters
<function_name> String
The name of an existing user-defined function. This commands returns the following:
Name
- The name of the function.Input
- The parameters of the function with one tuple per line: parameter_name data_typeReturns
- The return type of the function. For a scalar function,show data_type
. For a tabular function,show TABLE ( column_spec [, ...] )
Owner
- The current owner of the function.Created At
- The timestamp of when the function was created.Modified At
- The timestamp of when the function was last modified.Body
- The body of the function, namely, expression or query from theCREATE FUNCTION … RETURN
clause.
Example
Display the metadata associated with an existing functionDESCRIBE FUNCTION protect_ssn;
Dropping a Function
Only the owner of a UDF may drop it.
SyntaxDROP FUNCTION [ IF EXISTS ] function_name
Parameters
[ IF EXISTS ] String Optional
If specified, prevents an exception when the function does not exist.
<function_name> String
The name of an existing user-defined function.
Example
Remove an existing function with the exact nameredact_ssn
DROP FUNCTION redact_ssn;
redact_ssn
and prevent an exception from being thrown if it doesn't exist
DROP FUNCTION IF EXISTS redact_ssn;