Creating a user-defined function (UDF) in Databricks lets you encapsulate custom logic in Python, SQL, or Scala and reuse it across notebooks, jobs, and SQL queries. This guide walks through the three main ways to create functions: SQL UDFs, Python UDFs (PySpark), and SQL scalar functions. You’ll learn the exact syntax, how to register them, and how to call them in both notebooks and SQL warehouses.
What You’ll Need
- A Databricks workspace with a running cluster (for Python/Scala UDFs) or a SQL warehouse (for SQL functions).
- Access to a notebook (for Python/Scala) or the SQL editor (for SQL).
- Basic familiarity with SQL or Python syntax.
- If using Python UDFs, ensure your cluster has the necessary libraries (e.g., pandas if using pandas UDFs).
Step-by-Step
Method 1: SQL Scalar Function (Simplest)
- Open the SQL editor in your Databricks workspace (click “New” > “Query”).
- Write a
CREATE FUNCTIONstatement. For example, to create a function that doubles a number:CREATE OR REPLACE FUNCTION double_value(x INT) RETURNS INT RETURN x * 2; - Run the statement. The function is now registered in the current schema (or the schema you specified with
USE). - Call it in any SQL query:
SELECT double_value(5); -- returns 10
Method 2: Python UDF (PySpark) in a Notebook
- Create a new notebook (click “New” > “Notebook”) and attach it to a running cluster.
- Import the necessary PySpark functions:
from pyspark.sql.functions import udf from pyspark.sql.types import IntegerType - Define a regular Python function. For example:
def add_one(x): return x + 1 - Register it as a UDF using
udfand specify the return type:add_one_udf = udf(add_one, IntegerType()) - Use the UDF in a DataFrame operation:
from pyspark.sql import Row df = spark.createDataFrame([Row(value=1), Row(value=2)]) df.withColumn("result", add_one_udf("value")).show() - To use the UDF in SQL, register it as a temporary function:
spark.udf.register("add_one_sql", add_one, IntegerType())Then you can use
SELECT add_one_sql(5)in Spark SQL.
Method 3: Python UDF with Decorator (Pandas UDF for Performance)
- For vectorized operations, use a pandas UDF with the
@pandas_udfdecorator. First, import:from pyspark.sql.functions import pandas_udf from pyspark.sql.types import IntegerType - Define a function that takes a pandas Series and returns a Series:
@pandas_udf(IntegerType()) def multiply_by_three(s: pd.Series) -> pd.Series: return s * 3 - Apply it to a DataFrame column:
df.withColumn("tripled", multiply_by_three("value")).show()
Method 4: Scala UDF (if you prefer Scala)
- In a Scala notebook, define a function and register it:
import org.apache.spark.sql.functions.udf val square = udf((x: Int) => x * x) - Use it in a DataFrame:
df.withColumn("squared", square(col("value"))).show()
Common Mistakes
- Forgetting to specify the return type in Python UDFs – this can cause errors or unexpected nulls. Always provide a
DataType. - Using a reserved word as the function name (e.g.,
date). Avoid names that are SQL keywords. - Not using
CREATE OR REPLACEwhen redefining a function – you’ll get an error if the function already exists. - Calling a SQL function from a Python notebook without registering it – you must use
spark.udf.register()for SQL access. - Forgetting to attach the notebook to a cluster – you’ll get an error like “No active SparkSession”.
- Using Python UDFs on large data without optimization – regular UDFs are slow; prefer pandas UDFs when possible.
Tips
- Use SQL functions for simple logic – they are faster and can be used directly in SQL queries without registration.
- For Python UDFs, always specify the return type – this helps Spark optimize execution.
- Test your UDF on a small sample before running on the full dataset to catch errors early.
- Use pandas UDFs (also called vectorized UDFs) for better performance – they process data in batches.
- Store frequently used UDFs in a shared library (e.g., a Python wheel) and import them in notebooks to avoid duplication.
- Document your UDFs – add comments or a docstring so others know what they do.
Troubleshooting
- Error: “Function not found” – Check that you’re in the correct schema. Use
SHOW FUNCTIONSto list available functions. If you created it in a notebook, ensure you usedspark.udf.registerand that the notebook is attached to the same cluster as your SQL query. - Error: “Type mismatch” – Verify that the input types match the function signature. For example, if the function expects an INT, passing a STRING will fail. Cast the column with
.cast("int")if needed. - Python UDF returns null unexpectedly – Check for exceptions inside the function. Add a
try-exceptblock to log errors. Also ensure the return type matches the actual data. - Performance is slow – If using a regular Python UDF, switch to a pandas UDF. Also avoid using UDFs when built-in functions can do the job.
- Cannot create function in SQL warehouse – SQL warehouses only support SQL functions, not Python/Scala UDFs. Use a cluster for Python UDFs.
- Function persists after cluster restart – If you want a permanent function, create it in a SQL database (e.g.,
CREATE FUNCTIONin the default schema). Temporary functions (registered viaspark.udf.register) are lost when the cluster stops.
Frequently Asked Questions
Can I create a function in Databricks that works across notebooks?
Yes. For SQL functions, create them in a shared database (e.g., CREATE FUNCTION my_db.my_func). For Python UDFs, you can define them in a separate notebook and run it to register them, or package them into a library and import that library in each notebook.
What is the difference between a SQL UDF and a Python UDF?
SQL UDFs are written in SQL and are executed natively by the SQL engine, making them fast and simple for scalar logic. Python UDFs are written in Python and can handle more complex logic, but they incur serialization overhead and are slower. Use SQL UDFs for simple transformations, Python UDFs for complex logic.
Can I use a Python UDF in a SQL query?
Yes, after registering it with spark.udf.register("function_name", python_function, return_type), you can call it in Spark SQL like any built-in function. However, this only works in notebooks and jobs, not in the SQL warehouse UI.
How do I drop a function I created?
Use DROP FUNCTION IF EXISTS function_name in SQL. For Python UDFs, you can simply stop using them, but to remove from the catalog, you may need to drop the temporary function if you used spark.udf.register – but temporary functions are automatically cleared when the cluster stops.
Are there any limitations on the number of functions I can create?
No hard limit, but the number of UDFs can affect cluster performance if they are large or numerous. It’s good practice to keep UDFs minimal and reuse common ones.
Can I create a function in Databricks that returns a table (table-valued function)?
Yes, Databricks supports SQL table-valued functions (TVFs) using CREATE FUNCTION ... RETURNS TABLE. For example, you can create a function that returns a set of rows. Python UDFs cannot return tables directly; you would use a pandas UDF with a struct type or return a DataFrame from a notebook.