[BQ Loop over days] Date loop for backfills #bigquery #sql #date #loop

bq_date_loop.sql

DECLARE i INT64 DEFAULT 0;
DECLARE DATES ARRAY<DATE>;
DECLARE event_date DATE;
  
SET DATES = GENERATE_DATE_ARRAY(DATE(2019,1,1), DATE(2019, 12, 31), INTERVAL 1 DAY);

LOOP
    SET i = i + 1;  

    IF i > ARRAY_LENGTH(DATES) THEN 
      LEAVE; 
    END IF;

    SET event_date = DATES[ORDINAL(i)];

    DELETE FROM `uc-puzzle-data.xxx.yyy` WHERE local_event_date = event_date;

    INSERT INTO `uc-puzzle-data.xxx.yyy` 
    
    WITH 
    
    .....
    
    ;
  
END LOOP;