Here is the general format for creating a UDF:

CREATE TEMPORARY FUNCTION function_name(input, data_type) AS
((return function, data_type));

Let's look at an example where a user might want to use a UDF for transformation. In this case, the user has a table with date values in string format (DD/MM/YYYY) but wants to store the dates in date format (YYYY-MM-DD). Rather than having to write the following function multiple times, the user can use a UDF to streamline the process.

The following is the example function to replace

PARSE_DATE('%d/%m/%Y',datecolumn)

The following is the same example using UDF:

CREATE TEMPORARY FUNCTION stringtodate(x, STRING) AS (PARSE_DATE('%d/%m/%Y',x));

It may seem like the UDF is more verbose than the original function (it is); however, for the rest of the query, the user will only have to type this:

stringtodate(datecolumn)

Here is an example that can be executed in BigQuery:

CREATE TEMPORARY FUNCTION stringtodate(x, STRING) AS (PARSE_DATE('%d/%m/%Y',x));
WITH date_example AS



(SELECT '24/01/2017' as date

UNION ALL

SELECT '17/03/2017' as date)
SELECT stringtodate(date) AS result
FROM date_example

This type of programming is extremely valuable when very complicated functions are repeated multiple times in a query. UDFs are also covered in next chapter.