Learning Google BigQuery

UDF format

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 ...