Announcing Scalar User-Defined Functions

Introduction

User-defined functions (UDFs) are callable routines that make it easier for you to write and reuse SQL logic across queries. In addition, UDFs let you extend the capabilities of Dremio SQL, provide a layer of abstraction to simplify query construction, and encapsulate business logic. UDFs can also serve as row and column policies for access control as discussed in this blog post: New: Row-Level and Column-Level Access Controls.

Dremo Cloud and Dremio Software v22.0 introduce scalar UDFs, which take zero or more input parameters, run the query that you specify, and return a single value. (A scalar function is one that returns a single value.) The remainder of this article describes how to create and use scalar user-defined functions in Dremio.

Creating a User-Defined Function

You create a UDF with the following syntax:

CREATE FUNCTION
	function_name ( [ function_parameter [, ...] ] )
	RETURNS { data_type }
	RETURN { query } 
    
function_parameter:
	parameter_name data_type

To learn more, see the documentation for Dremio Cloud and Dremio Software.

Here is an example that returns the product of two numbers:

CREATE FUNCTION multiply (x INT, y INT) RETURNS INT RETURN SELECT x * y; 
SELECT multiply(2, 3); 
	6

Showing User-Defined Functions

You can list all UDFs that you have created with this command:

SHOW FUNCTIONS

Running the command produces output similar to the following:

SHOW FUNCTIONS
	"@dremio".hello
    "@dremio".area
    "@dremio".square

To learn more, see the documentation for Dremio Cloud and Dremio Software.

Examples

The UDFs below demonstrate putting logic into a function that you can call from your queries. The hello UDF takes no parameters and returns a string literal. The next UDF, area, takes two parameters and uses them in a calculation. The query after that shows that UDFs return values that you can filter by in WHERE clauses. The final example, square, illustrates a UDF calling another UDF, allowing you to chain functions together in a query.

-- Make some test data.

CREATE TABLE $scratch.t (c1, c2) AS VALUES (0, 1), (1, 2);

-- Create a function with no parameters.

CREATE FUNCTION hello() RETURNS VARCHAR RETURN 
SELECT 'Hello, World!';
SELECT "@dremio".hello();
	Hello, World!

-- Create a function that takes parameters.

CREATE FUNCTION area (x DOUBLE, y DOUBLE) RETURNS DOUBLE RETURN SELECT x * y;

-- Use a function in the SELECT clause of a query.

SELECT "@dremio".area (c1, c2) AS area FROM $scratch.t;
	0
	2

-- Use a function in the WHERE clause of a query.

SELECT * FROM $scratch.t WHERE "@dremio".area (c1, c2) > 0;
	1, 2

-- Call a function from within another function.

CREATE or replace FUNCTION square (x DOUBLE) RETURNS DOUBLE
	RETURN SELECT "@dremio".area (x, x);
SELECT c1, "@dremio".square (c1) AS square FROM $scratch.t;
    0, 0
    1, 1

To learn more about scalar UDFs, see the documentation for Dremio Cloud and Dremio Software.

Feedback

To share your comments about user-defined functions in Dremio, please contact the product management team at [email protected].

Get Started with Dremio Cloud – It’s Free!

Dremio Cloud: The easy and open, fully managed data lakehouse platform.

Sign Up Now

Everything you need to build, automate, and query your data lakehouse in production.