Introducing SQL User-Defined Functions

A user-defined function (UDF) is a means for a user to extend the native capabilities of Apache Spark™ SQL. SQL on Databricks has supported external user-defined functions written in Scala, Java, Python and R programming languages since 1.3.0. While external UDFs are very powerful, they also come with a few caveats:

• Security. A UDF written in an external language can execute dangerous or even malicious code. This requires tight control over who can create UDF.
• Performance. UDFs are black boxes to the Catalyst Optimizer. Given Catalyst is not aware of the inner workings of a UDF, it cannot do any work to improve the performance of the UDF within the context of a SQL query.
• SQL Usability. For a SQL user it can be cumbersome to write UDFs in a host language and then register them in Spark. Also, there is a set of extensions many users may want to make to SQL which are rather simple where developing an external UDF is overkill.

To cope with the above limitations, we are thrilled to introduce a new form of UDF: SQL UDFs. Available in DBR 9.1 LTS, the SQL UDF is completely defined with the expressive power of SQL and also completely transparent to the SQL Compiler.

Check out the Why the Data Lakehouse is Your Next Data Warehouse ebook to discover the inner workings of the Databricks Lakehouse Platform.

Benefits of using SQL UDFs

SQL UDFs are simple yet powerful extensions to SQL on Databricks. As functions, they provide a layer of abstraction to simplify query construction - making SQL queries more readable and modularized. Unlike UDFs that are written in a non-SQL language, SQL UDFs are more lightweight for SQL users to create. SQL function bodies are transparent to the query optimizer thus making them more performant than external UDFs. SQL UDFs can be created as either temporary or permanent functions, be reused across multiple queries, sessions and users, and be access-controlled via Access Control Language (ACL). In this blog, we will walk you through some key use cases of SQL UDFs with examples.

SQL UDFs as constants

Let’s start with the most simplistic function imaginable: a constant. We all know we’re not supposed to use literals in our code because it harms readability and, who knows, maybe the constant doesn’t remain constant after all. So we want to be able to change it in one place only:

If you are familiar with external UDFs, you can see there are some differences that stand out:

1. A SQL UDF must define its parameter list, even if it’s empty. A constant takes no parameters.
2. The function also declares the data type it will return. In this case that’s a STRING.
3. The implementation of the function is part of the function definition.
4. You specify LANGUAGE SQL to say that it’s a SQL UDF. But really, that’s not needed. The RETURN clause is enough of a give away that we decided to make this optional.

Beyond these differences there are many other things that are the same as external UDF:

• You can replace a function. More on that later.
• You can add a comment that describes the function – as shown above.
• You can even create a temporary function that you can use within the current session, only.

Let’s use the function:

Unsurprisingly this works. But what is happening under the hood?

This is neat! The SQL compiler replaced the function invocation with the constant itself.
That means at least this SQL UDF comes at zero cost to performance.

Now, let’s have a look at another common usage pattern.

SQL UDF encapsulating expressions

Imagine you don’t like the naming of some built-in functions. Maybe you are migrating lots of queries from another product, which has different function names and behaviors. Or perhaps you just can’t stand copy-pasting some lengthy expressions over and over again in your SQL queries. So, you want to fix that.

With SQL UDF, we can simply create a new function with the name we like:

Let’s have a look at what new syntax was used here:

• This function takes an argument, and the parameter is defined by a name, a type and an optional comment.
• The CONTAINS SQL clause is optional, but tells us the function does not read or modify any data in a table. It is the default setting, so you normally wouldn’t specify it.
• DETERMINISTIC is also optional and tells us that the function will always return the same result set given the same arguments. The clause is for documentation only at this point. But at some point in the future it may be used to block non deterministic functions in certain contexts.
• In the RETURN clause the parameter has been referred to by name. In more complex scenarios below you will see that the parameter can get disambiguated with the function name. Naturally you can use arbitrarily complex expressions as the function body.

Not only does it work …

... but it works well:

We can see that the physical plan shows a straight application of the functions lpad, hex, least and greatest. This is the same plan you get invoking the series of functions directly.

You can also compose SQL functions out of SQL functions:

SQL UDF reading from tables

Another common usage of SQL UDF is to codify lookups. A simple lookup may be to decode RGB color codes into English color names:

OK, but there are a lot more than two colors in this world. And we want this translation both ways, so these should really be in a lookup table:

There are multiple new concepts applied here:

• You can REPLACE a SQL UDF. To be allowed to do that, the new function must match the old function’s signature. The signature of a function is defined as the number of its parameters and their types.
• This function looks up information in a table, so you can optionally document that using READS SQL DATA. If you state nothing the SQL Compiler will derive the correct value, but you must not lie and state CONTAINS SQL.
• SQL SECURITY DEFINER is another optional clause, which states that the query accessing the colors table will use the authorization of the function owner. So the function could be executed by the public without compromising the security of the table.
• Just as the function operates under the authorization of its owner it will always be parsed using the current database at time of creation.
• `rgb` is the name of the column in numbers. By qualifying the parameter as `from_rgb`.`rgb` you clarify that you mean the parameter reference, and not the column.

How does the physical plan look like now? It is easy to see that using an external UDF, which itself performs a query that would result in a nested loop join, is an awful way to burn precious resources.

In this case, Catalyst has chosen a broadcast hash join instead of a nested loop join. It can do this because it understands the content of the SQL UDF.

Thus far, all examples discussed used scalar-valued functions – ones that return a single value. That result may be of any type, even complex combinations of structs, arrays, and maps.There is also another type of UDF to discuss – the table-valued UDF.

SQL Table UDF

Imagine if views took arguments! You could encapsulate complex predicates even if they rely on user-provided values. A SQL Table UDF is just that: a view by any other name, except with parameters.

Let’s assume that the color mapping above is not unique. At the very least, we can assert the color names differ across languages.

Therefore the `from_rgb` function needs to be modified to return either an array of names or a relation.

As you can see, the only difference compared to a scalar function is a more complex RETURNS clause. Unlike views, SQL UDFs mandate a declaration of the returned relation’s signature:

• TABLE specifies that the function returns a relation.
• The TABLE clause must include a name for each return column and the column’s data type.
• You may optionally specify a comment for any return column.

User-defined table functions are new to DBR. Let’s have a look at how to invoke them.

In its simplest form, a table function is invoked in the same way and the same places a view is referenced. The only difference are the mandatory braces, which include the function’s arguments. This function is invoked with literal arguments, but the arguments can be any expression, even scalar subqueries.

Most powerful, however, is the usage of SQL table UDF in a join, typically a correlated cross join:

Here the arguments refer (correlate) to a preceding (lateral) relation in the FROM clause. The new LATERAL keyword gives Catalyst permission to resolve these columns. Also note that you can refer to the result of the table function by naming the columns as defined in the result signature and optionally qualified by the function name.

Administration

Naturally, SQL UDFs are fully supported by the existing GRANT, REVOKE, SHOW, DESCRIBE and DROP statements.

The statement worth pointing out in more detail is DESCRIBE.

The basic describe returns what you might expect, but the extended DESCRIBE adds significantly more detail:

Outlook

What we have described represents the initial functionality for SQL UDF. Future extensions we are pondering include support for:

• SQL PATH, so you can create a library of functions in a database and subscribe to them from another, just as you would do in your file system.
• Overloading of UDFs.
• UDFs with default values for parameters.

SQL UDFs are a big step forward in SQL usability and can be used in many different ways as outlined in this blog.  We encourage you to think of even more creative ways to leverage SQL UDFs be it in Databricks SQL or using Photon for Data Engineering jobs. Try the notebook here and see the documentation for more information.