Scheduled script for BigQuery to automatically export data to Google Cloud Storage

big_query_scheduled_export

-- This statement gets the date of 2 days back. Did this becasue if the script is running on 25th,
-- I'm not sure if the data for 24th is ready or not. It depends on what timezone the analytics server
-- is running off of and when do they consider 24th to be over. So 2 days period is safe

DECLARE backup_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 2 day);
DECLARE date_str STRING;
DECLARE table_name STRING;
DECLARE sql STRING;
DECLARE date_str_folder STRING;
DECLARE date_str_table STRING;

SET date_str = CAST(backup_date AS STRING);
SET date_str_folder = REPLACE(date_str, '-', '_');
SET date_str_table = REPLACE(date_str, '-', '');

-- Creating the table name dynamically
SET table_name = CONCAT('<project-id>.<dataset_name>.events_', date_str_table);

-- This is what the command would have been if I did not have to put it in a string
-- EXPORT DATA
--   OPTIONS ( 
--     uri = CONCAT('gs://<project-id>/bq_events/', date_str, '*.AVRO'),
--     format='AVRO',
--     overwrite=FALSE ) AS
-- SELECT
--   *
-- FROM
--   CONCAT('<project-id>.<dataset_name>.events_', date_str)

-- Had to express as string so that I could add dynamically created date_str and table_name to the query
-- It also seems that this script can produce multiple files from a single table (which is annoying but unfixable it seems)
-- https://issuetracker.google.com/u/1/issues/181016197
-- https://stackoverflow.com/questions/66045942/big-query-export-data-statement-creating-mutiple-files-with-no-data-and-just-hea

SET sql = CONCAT(
    'EXPORT DATA OPTIONS ( uri = \'',  
    CONCAT('gs://<project-id>/bq_events/', date_str_folder, '/*.AVRO'), 
    '\' , format=\'AVRO\', overwrite=FALSE ) AS ',
    'SELECT * FROM \`',
    table_name,
    '\`');


EXECUTE IMMEDIATE (sql)