A table-valued function (TVF) in Databricks is a user-defined function that returns a table (a set of rows and columns) instead of a single scalar value, and you call it directly in the FROM clause of a query. In Databricks SQL and PySpark, TVFs let you encapsulate complex logic, reuse it across queries, and treat the result like any other table or view.
Background: What Are Table-Valued Functions in Databricks?
Databricks is built on Apache Spark, and its SQL engine supports two main types of user-defined functions: scalar UDFs (return a single value) and table-valued functions (return a relation). The concept of TVFs is borrowed from traditional SQL databases like SQL Server and PostgreSQL, but Databricks implements them using Spark SQL’s CREATE FUNCTION syntax. In Databricks, a TVF can be written in SQL or Python (via PySpark UDFs that return DataFrames). The key advantage is that a TVF can be used in joins, subqueries, and even in the FROM clause with parameters, making it a powerful tool for parameterized reporting and data transformation.
How Table-Valued Functions Work in Databricks SQL
In Databricks SQL, you define a TVF using the CREATE FUNCTION statement with RETURNS TABLE. The function body is a SQL query that returns the result set. Here’s a basic example:
CREATE FUNCTION get_orders_by_status(status STRING)
RETURNS TABLE (order_id INT, customer_id INT, order_total DECIMAL(10,2))
RETURN SELECT order_id, customer_id, order_total FROM orders WHERE order_status = status;
To call it, you use it in the FROM clause:
SELECT * FROM get_orders_by_status('shipped');
You can also pass a column as a parameter, which enables set-based logic. For example, you could join a table of statuses to the TVF:
SELECT s.status, t.order_id FROM statuses s
LATERAL VIEW get_orders_by_status(s.status) t;
Note: Databricks SQL supports LATERAL VIEW for this purpose, though the exact syntax may vary by version. In PySpark, you can define a Python function that returns a DataFrame and register it as a temporary table function using spark.udf.register, but the SQL-based approach is more straightforward for most users.
Choosing Between a TVF, a View, or a Stored Procedure
When working in Databricks, you’ll often wonder whether to use a TVF, a regular view, or a stored procedure. Here’s a quick guide:
- Use a TVF when you need to pass parameters that change the result set, and you want to reuse that logic in multiple queries. TVFs are ideal for reporting where date ranges or category filters vary.
- Use a view when the logic is static and doesn’t require parameters. Views are simpler and can be optimized by the query optimizer.
- Use a stored procedure when you need to perform multiple steps (like inserting into a table, then updating another) and don’t need to return a result set to the caller. Procedures are for side effects, not for querying.
In practice, TVFs are the best choice for parameterized read-only queries. They are also more secure than dynamic SQL in stored procedures because they reduce SQL injection risk.
Common Problems and How to Avoid Them
One common issue is that TVFs in Databricks SQL may not support all SQL constructs inside the function body. For example, you cannot use INSERT or UPDATE statements; the function must be purely a SELECT. Also, TVFs do not support dynamic SQL (building a query string and executing it) inside the function. If you need dynamic logic, consider using a stored procedure or a PySpark UDF.
Another problem is performance: if you call a TVF in a join with a large table, the function may be executed once per row, leading to slow performance. To mitigate this, try to filter the input before joining, or use a broadcast hint. Also, be aware that TVFs are not materialized; they are re-evaluated each time you call them. If the underlying data is huge, consider caching the result in a temporary view.
Comparison: Databricks TVFs vs. Scalar UDFs
Scalar UDFs return a single value per row and are used in the SELECT list or WHERE clause. Table-valued functions return multiple rows and columns and are used in the FROM clause. For example, a scalar UDF could compute a discount percentage, while a TVF could return all orders for a customer. Scalar UDFs are easier to write but can be slower if used in row-by-row operations. TVFs are more flexible and can be optimized better because they are treated as a relation. In Databricks, Python UDFs (both scalar and table) run in a separate process, which adds serialization overhead. For large datasets, prefer native SQL functions or TVFs written in SQL.
Cost and Performance Considerations
Databricks charges based on compute (DBUs) used by clusters or SQL warehouses. TVFs themselves don’t add cost beyond the underlying query execution. However, if you use a TVF in a loop or call it many times with different parameters, each call incurs compute cost. To optimize, consider using a single query with a LATERAL VIEW to avoid multiple invocations. Also, be mindful of data shuffling: if the TVF returns a large result set, it may cause network shuffles. Use partitioning and bucketing on the input tables to reduce shuffle. For SQL warehouses, you can scale up or down based on workload; TVFs are generally efficient if written with proper filters.
Safety, Permissions, and Best Practices
When creating a TVF, you need appropriate permissions on the underlying tables. In Databricks, you can use CREATE FUNCTION with USING to specify a location for the function’s code, but for SQL TVFs, the function is stored in the metastore. To prevent SQL injection, never concatenate user input into the function body; always use parameters. Also, follow these best practices:
- Always specify the return schema explicitly to avoid ambiguity.
- Use meaningful names for functions and parameters.
- Test the function with sample data before deploying.
- Document the function’s behavior and expected input types.
- Limit the use of TVFs in recursive or iterative processes; they are not designed for that.
Alternatives to TVFs in Databricks
If TVFs don’t fit your needs, consider these alternatives:
- Parameterized views (not natively supported in Databricks, but you can simulate with a temporary view and a variable).
- PySpark DataFrames: Write a Python function that takes parameters and returns a DataFrame. You can call it in a notebook and use the result as a temp view.
- Stored procedures with output parameters (but they don’t return a table directly).
- Delta Live Tables (DLT) for streaming or incremental pipelines, where you define tables with SQL and can parameterize them using pipeline parameters.
For example, in a notebook, you can do:
def get_orders(status):
return spark.sql("SELECT * FROM orders WHERE order_status = '{}'".format(status))
df = get_orders('shipped')
df.createOrReplaceTempView('shipped_orders')
This is not a true TVF but achieves similar results. However, it lacks the reusability across different notebooks unless you package it as a library.
Frequently Asked Questions
Can I use a table-valued function in a JOIN in Databricks?
Yes, you can use a TVF in a JOIN, but you need to use LATERAL VIEW or a subquery. For example: SELECT * FROM table1 t1 JOIN get_orders(t1.status) t2 ON t1.id = t2.id may not work directly; instead use LATERAL VIEW to expand the function per row.
What is the difference between a table-valued function and a view in Databricks?
A view is a saved query with no parameters. A TVF accepts parameters and returns a table based on those parameters. Views are static; TVFs are dynamic.
Can I write a table-valued function in Python (PySpark)?
Yes, you can write a Python function that returns a DataFrame and register it as a temporary table function using spark.udf.register with returnType as a StructType. However, the syntax is more complex than SQL TVFs, and performance may be slower due to serialization.
Are table-valued functions supported in Databricks SQL Serverless?
Yes, SQL Serverless warehouses support TVFs as long as you use the correct syntax. The function is stored in the metastore and can be called from any warehouse.
How do I drop a table-valued function in Databricks?
Use the DROP FUNCTION statement: DROP FUNCTION IF EXISTS function_name;. This removes the function from the metastore.
Can a table-valued function call another table-valued function?
Yes, you can nest TVFs. For example, a TVF can call another TVF in its SELECT statement, as long as the inner function is defined first. This allows for modular design.
Related
- Databricks STRING Data Type: Uses and Examples
- SOLIDWORKS Table: How to Split It on a Drawing Sheet
- SOLIDWORKS Split Command: How to Divide a Part File
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Variance Between Two CellsTo calculate the difference between two cells in Excel, subtract one from the other: with the earlier or…
- Snowflake SQL String Functions: Examples and Usage GuideSnowflake SQL string functions let you combine, inspect, search, split, extract, and clean text values in queries. Common…
- Excel IFERROR for Multiple Cells: Formulas and TipsTo handle errors across multiple cells in Excel, use IFERROR in the first output cell and fill the…
- Excel Formula Conditional Formatting by RowTo apply conditional formatting by row in Excel, use a formula that references the row’s first cell with…
- Excel: How to Hide Columns to the Right of Your DataTo hide every column to the right of your data in Excel for Microsoft 365 on Windows, select…
- Excel LET: Can It Use Multiple Variables?Yes. Excel’s LET function allows multiple named values in one formula: write each name and value as a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.