how can we handle exceptions with bigquery functions?

user_10992342 0
Viewed 408

given any function like e.g:

CREATE FUNCTION ds.fn(param ANY TYPE) RETURNS STRING AS (
    (SELECT 1/0)
);

Is there a way for handling errors when the statement fails and return a default value?

Note: My question is about any error that a select statement can throw and not only the arithmetic in the example above.

google-bigquery google-cloud-platform user-defined-functions sql
1 Answers

You can make your function more robust using SAFE functions inside a UDF and also make it return default value if it fails like below.

CREATE FUNCTION ds.fn(param ANY TYPE) RETURNS AS ((
  SELECT IFNULL(SAFE_DIVIDE(1,0), 0)
));

Also, you can catch a error in a BigQuery script using BEGIN...EXCEPTION block:

CREATE OR REPLACE FUNCTION testset.fn(param ANY TYPE) RETURNS FLOAT64 AS ((
  SELECT 1/0
));

BEGIN
  SELECT testset.fn(10);
EXCEPTION WHEN ERROR THEN
  SELECT @@error.message;
END;
user_190399200
Related