User-defined functions (UDFs) in BigQuery represent a pivotal tool in the arsenal of data analysts and engineers, facilitating the customization and enhancement of data processing pipelines within Google’s powerful data warehouse solution. UDFs empower users to extend the functionality of BigQuery by encapsulating custom logic into reusable code snippets, thereby enabling complex transformations, calculations, and analyses that may not be achievable through standard SQL queries alone.<\/p>\r\n
At its core, a UDF is a piece of code authored by the user, typically written in languages such as JavaScript or SQL, which can be invoked within BigQuery SQL queries to perform specific operations on data. These functions can range from simple mathematical computations to intricate data manipulations, providing a versatile mechanism for tailoring data processing workflows to meet the unique requirements of each use case.<\/p>\r\n
A Temporary JavaScript Function<\/h2>\r\n
Paste the following into the query editor:<\/p>\r\n
CREATE TEMP FUNCTION multiplyput(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS “””
return x*y;
“””;
WITH numbers AS
\u00a0\u00a0(SELECT 1 AS x, 5 AS y
\u00a0\u00a0UNION ALL
\u00a0\u00a0SELECT 2 AS x, 10 AS y)
SELECT x, y, multiplyput(x, y) AS product
FROM numbers;<\/p>\r\n
Click Run.<\/p>\r\n\r\n\r\n\r\n This function only exists for the query it was defined in. Once the query finishes, multiplyput is gone, and typing it into your next query means writing the whole thing out again.<\/p>\r\n The example above uses LANGUAGE js, but the same logic can be written as a plain SQL function instead:<\/p>\r\n CREATE TEMP FUNCTION multiplyput(x FLOAT64, y FLOAT64) AS (x * y); This is not just a style preference. Google’s own performance guidance for BigQuery states that a JavaScript UDF has to spin up a subprocess to run, which uses meaningfully more slot resources than the same logic written in SQL, and slows the query down as a result. Whenever your logic can be expressed as a SQL expression, a SQL UDF is the faster choice. JavaScript earns its place for genuinely complex logic, such as parsing an awkward text format, that SQL cannot express cleanly on its own.<\/p>\r\n A temporary function only lives inside the query that defines it. A persistent<\/b><\/strong>\u00a0function, created with CREATE FUNCTION instead of CREATE TEMP FUNCTION, is saved into a dataset and can be called from any query afterward, the same as a built in function.<\/p>\r\n CREATE FUNCTION your_dataset.multiplyput(x FLOAT64, y FLOAT64) AS (x * y); Google’s own best practice guidance recommends exactly this, building a shared dataset of persistent SQL and JavaScript functions that your whole team can call, rather than pasting the same temporary function definition into query after query. To remove a persistent function later, use DROP FUNCTION your_dataset.multiplyput.<\/p>\r\n A scalar UDF, returning a single value, covers most everyday needs, but BigQuery supports a few related routine types worth knowing exist:<\/p>\r\n![\"\"](\"https:\/\/prwatech.in\/blog\/wp-content\/uploads\/2021\/05\/image-74.png\")
<\/figure>\r\nPrefer SQL Over JavaScript When You Can<\/h2>\r\n
WITH numbers AS
\u00a0\u00a0(SELECT 1 AS x, 5 AS y
\u00a0\u00a0UNION ALL
\u00a0\u00a0SELECT 2 AS x, 10 AS y)
SELECT x, y, multiplyput(x, y) AS product
FROM numbers;<\/p>\r\nMaking a Function Persistent<\/h2>\r\n
SELECT your_dataset.multiplyput(3, 4) AS product;<\/p>\r\nOther Kinds of Routines Worth Knowing About<\/h2>\r\n
\r\n
Common Mistakes to Avoid<\/h2>\r\n