Calling a native function within a UDF in bigquery

I am trying to convert our legacy functions into standard SQL and so by using UDFs as a gig to reuse the old function names with the native functions.

Here I have my UDF to_char and inside use the parameters to run FORMAT_DATE but when I execute it even though a routine is created I get errors with no additional info. Once the rountine has been created and I comment out the code the errors come as such function to_char not found

If I run a CREATE TEMP FUNCTION I get really long loading times(ca 30 – 90secs) and that obviously cant be right.

CREATE OR REPLACE FUNCTION mytable.TO_CHAR(dstamp DATETIME, fmt STRING)
RETURNS STRING AS
(
    FORMAT_DATE(fmt, dstamp)
);

SELECT
    TO_CHAR(End_Time, 'yyyy-mm') AS Month_and_Year,

I don’t want to create a temp function. Ideally I’d like to have a routine that I can just keep calling when need be but I am messing something up with my UDF and the FORMATE_DATE function. Any help would be much appreciated, thank you!

btw running this in GBQ.

Answer

You need reference UDF with dataset prefix, otherwise BigQuery think it is a builtin function or temporary UDF. In your case, just change TO_CHAR to mytable.TO_CHAR. Assuming your mytable is actually a dataset.

SELECT mytable.TO_CHAR(End_Time, 'yyyy-mm') AS Month_and_Year,

Advertisement

Answer