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.