Exception handling in BigQuery

Exception handling is like a superpower.

Disclosure: All opinions expressed in this article are my own, and represent no one but myself and not those of my current or any previous employers.

Exception handling gives you the ability to control your code, even when it is misbehaving.

BigQuery pipelines are just like any other software application, and they can also encounter errors. Exception handling is essential for BigQuery pipelines to ensure that they continue to function even when errors occur.

Exception handling allows you to take control of how your queries handle errors. You can use exception handling to:

• Log the error message: This can be helpful for debugging and troubleshooting errors.
• Retry the query: This can be helpful if the error is transient, such as a network error.
• Continue processing the query with a fallback value: This can be helpful if the error is not fatal, such as a missing record.
• Notify the user of the error: This can be helpful for informing the user of the error and preventing them from continuing with an invalid operation

Let’s see an implementation of exception handling in stored procedures,

CREATE OR REPLACE PROCEDURE `gcp-sandbox-3-393305.df_ecom_bz.sp_distribution_centre`()
  BEGIN

-- below are  multi sqls running inside stored procs , these are randomly written sqls and has no releveance of any result .
-- The idea is to show exception handling 
SELECT * FROM `gcp-sandbox-3-393305.df_ecom_bz.stg_distribution_centre` ;

SELECT count(1),id FROM `gcp-sandbox-3-393305.df_ecom_bz.stg_distribution_centre` group by id;

INSERT INTO `gcp-sandbox-3-393305.df_ecom_bz.stg_distribution_centre` (id,name,latitude,longitude) VALUES
(

    100  ,
    'test_dc',
    20.3 ,
    100.9
);

-- there might be more complex sqls as well  

    EXCEPTION WHEN ERROR THEN
  BEGIN
      DECLARE procedurename STRING;
      DECLARE errmessage STRING;
      DECLARE unknownnumber INT64;

      SET procedurename = 'revenue_allocate';
      SET errmessage = @@error.message;
      INSERT INTO `gcp-sandbox-3-393305.df_ecom_bz.error_log` (entrytime, procedurename, error_statement, error_msg)
        VALUES (timestamp_trunc(current_timestamp(), SECOND), procedurename, @@error.statement_text, errmessage)
      ;
      select concat(@@error.statement_text , '\n\n',@@error.message);  -- this is to print the error in the stdout
      SET unknownnumber = div(1, 0);   -- this is to fail the job at the end
      END;
    END;

in above query the interesting part is

EXCEPTION WHEN ERROR THEN
  BEGIN
      DECLARE procedurename STRING;
      DECLARE errmessage STRING;
      DECLARE unknownnumber INT64;

      SET procedurename = 'revenue_allocate';
      SET errmessage = @@error.message;
      INSERT INTO `gcp-sandbox-3-393305.df_ecom_bz.error_log` (entrytime, procedurename, error_statement, error_msg)
        VALUES (timestamp_trunc(current_timestamp(), SECOND), procedurename, @@error.statement_text, errmessage)
      ;
      select concat(@@error.statement_text , '\n\n',@@error.message);  -- this is to print the error in the stdout
      SET unknownnumber = div(1, 0);  -- this is to fail the job at the end
      END;

So here we are creating a stored proc with a couple of queries (this can be anything I have just written some random queries).

In case any SQL statement fails then the exception block is going to run.

In my case, I have stored the error logs with timestamp and procedure name in an error_log table, so that I can check, debug, or fix later.

And in the last line I want my execution to fail so I added. “SET unknownnumber = div(1, 0);”. This will fail my stored procs.

This is one way of handling any exception, but also we can do different tasks in different types of issues, we do it through error message (@@error.message).

Lets see one more example:

CREATE OR REPLACE PROCEDURE `gcp-sandbox-3-393305.df_ecom_bz.sp_distribution_centre`()

  BEGIN

  DECLARE errorcount int64;
  DECLARE rec_count int64;

-- below are  multi sqls running inside stored procs , these are randomly written sqls and has no releveance of any result .
-- The idea is to show exception handling 
SELECT * FROM `gcp-sandbox-3-393305.df_ecom_bz.stg_distribution_centre` ;


SET rec_count = (SELECT count(1) FROM `gcp-sandbox-3-393305.df_ecom_bz.stg_distribution_centre` )
;

 IF rec_count > 0 THEN
      RAISE USING MESSAGE = 'empty_table_error';  -- raising exception with a message 
    END IF;

INSERT INTO `gcp-sandbox-3-393305.df_ecom_bz.stg_distribution_centre` (id,name,latitude,longitude) VALUES
(
    100  ,
    'test_dc',
    20.3 ,
    100.9
);

    SET errorcount = (
      SELECT
          count(1)
        FROM
          `gcp-sandbox-3-393305.df_ecom_bz.stg_distribution_centre`
    );
    IF errorcount > 0 THEN
      RAISE USING MESSAGE = 'dm_content_error';  -- raising exception with a message 
    END IF;

-- there might be more complex sqls as well  

     EXCEPTION WHEN ERROR THEN
    BEGIN
      DECLARE procedurename STRING;
      DECLARE UndefinedNumber FLOAT64;
      SET procedurename = 'revenue_allocate';

      IF (@@error.message="empty_table_error")
      THEN
        INSERT INTO `gcp-sandbox-3-393305.df_ecom_bz.stg_distribution_centre` (id,name,latitude,longitude) VALUES
      (

        100  ,
        'test_dc',
        20.3 ,
        100.9
      );
      END IF;
            IF (@@error.message="dm_content_error")
      THEN
        INSERT INTO `gcp-sandbox-3-393305.df_ecom_bz.error_log` (entrytime, procedurename, error_statement, error_msg)
        VALUES (timestamp_trunc(current_timestamp(), SECOND), procedurename, @@error.statement_text, errmessage)
      ;
      END IF;
      SET unknownnumber = div(1, 0);  -- this is to fail the job at the end
    END;
  END;

If you observe the above SQL query, the important part is raising the exception with an error message so that it will be treated differently for different errors.

IF rec_count > 0 THEN
      RAISE USING MESSAGE = 'empty_table_error';  -- raising exception with a message 
    END IF;

In the above block of code, I am checking certain conditions and raising exceptions with the message.

In the exception block, I am checking the error message with the if operator and treating them differently.

IF (@@error.message="empty_table_error")
      THEN
        INSERT INTO `gcp-sandbox-3-393305.df_ecom_bz.stg_distribution_centre` (id,name,latitude,longitude) VALUES
      (

        100  ,
        'test_dc',
        20.3 ,
        100.9
      );

IF (@@error.message="dm_content_error")
      THEN
        INSERT INTO `gcp-sandbox-3-393305.df_ecom_bz.error_log` (entrytime, procedurename, error_statement, error_msg)
        VALUES (timestamp_trunc(current_timestamp(), SECOND), procedurename, @@error.statement_text, errmessage)
      ;
      END IF;

In my case, if our error message is “empty_table_error”, I am inserting the record in the source table and if it is “dm_content_error”, then I am logging the error in the error log table.

We can access more details about the error, the EXCEPTION clause has access to below system variables:

@@error.formatted_stack_trace
@@error.message
@@error.stack_trace
@@error.statement_text

I hope you learn something new , happy coding guys.