How to perform exception handling in a loop in Google Big Query?

Question

I am trying to take monthly backup of multiple tables present in various datasets by exporting the data to GCS using EXPORT DATA OPTIONS command. These table names are present in another table in the following schema which would be maintained by someone

| Sr.No | Table_Name                            | Date_filter   | Timeline |
|-------|---------------------------------------|---------------|----------|
| 1     | gcs_project_name.dataset_name.table1  | inserted_date | Monthly  |
| 2     | gcs_project_name.dataset_name2.table2 | inserted_date | Monthly  |

I wrote a script in procedural language to make an array of such query strings which would perform the export one table at a time on the bases of the Date_filter. The script doing so is mentioned below

DECLARE folder_name DEFAULT CAST(DATE(CURRENT_DATE()) AS STRING);

DECLARE i INT64 DEFAULT 0;

DECLARE dynamic_query ARRAY <STRING>;

SET dynamic_query = ([![SELECT ARRAY_AGG(CONCAT("EXPORT DATA OPTIONS (uri = 'gs://data_backup/",folder_name,'/',Table_Name,"_*',\n format = 'CSV',","\n overwrite = TRUE,","\n header = TRUE,","\n field_delimiter = ',') AS","\n\n SELECT * FROM \n","   `",Table_Name,"`\n","WHERE \n DATE(",Date_filter,") BETWEEN DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 month), month) AND LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 1 month), month)","\n LIMIT \n 1000000",";","\n")) FROM `google_sheets.backup_tables`][1]][1] );

LOOP
  SET i = i + 1;
  IF i > ARRAY_LENGTH(dynamic_query) THEN 
    SET boolean = FALSE;
    LEAVE; 
  END IF;
  EXECUTE IMMEDIATE dynamic_query[ORDINAL(i)];
END LOOP; 

SELECT dynamic_query;

The output of the dynamic_query is mentioned below

Now I'm iterating over this array and executing the queries one-by-one using EXECUTE IMMEDIATE

The issue I'm facing is that the field mentioned in Date_filter is sometime in STRING datatype. Due to this the script fails and doesn't continue the execution for remaining tables. To tackle this I want to perform exception handling where the execution doesn't stop and I'm able to see the list of tables which have been exported and failed, just like python's try-catch block.

Answer 1

I figured out how to perform exception handling in Loop command. Here's the code for reference

DECLARE folder_name DEFAULT CAST(DATE(CURRENT_DATE()) AS STRING);

DECLARE i INT64 DEFAULT 0;

DECLARE dynamic_query ARRAY <STRING>;

SET dynamic_query = (SELECT ARRAY_AGG(CONCAT("EXPORT DATA OPTIONS (uri = 'gs://data_backup/",folder_name,'/',Table_Name,"_*',\n format = 'CSV',","\n overwrite = TRUE,","\n header = TRUE,","\n field_delimiter = ',') AS","\n\n SELECT * FROM \n","   `",Table_Name,"`\n","WHERE \n DATE(",Date_filter,") BETWEEN DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 month), month) AND LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 1 month), month)","\n LIMIT \n 1000000",";","\n")) FROM `google_sheets.backup_tables` );

  LOOP
    BEGIN
    SET i = i + 1;
    IF i > ARRAY_LENGTH(dynamic_query) THEN 
      SET boolean = FALSE;
      LEAVE; 
    END IF;
    EXECUTE IMMEDIATE dynamic_query[ORDINAL(i)];
    
    EXCEPTION WHEN ERROR THEN
    
    SELECT @@error.message;
    
    END;
    
    CONTINUE;
  
  END LOOP;