Create table with date shards of the form

Say I create this table for example:

-- create a temporary table to use in this minimum example
CREATE OR REPLACE TABLE `project_id.dataset_id.example_table` AS
 (SELECT 'Adams' as LastName, 51 as OpponentID, 3 as PointsScored UNION ALL
  SELECT 'Buchanan', 77, 0 UNION ALL
  SELECT 'Coolidge', 77, 1 UNION ALL
  SELECT 'Adams', 52, 4 UNION ALL
  SELECT 'Buchanan', 50, 13)

And I want to loop over it so that I save a table shared by dates in a for loop with a WHERE clause. For example saving all the rows where OpponentID is equal to 70 sharded by dates as

tablename_20200801
tablename_20200901
tablename_20201001

How would I do so? I have an example below but this does not work, could you explain what is missing?


DECLARE end_of_month date;
DECLARE final_snapshot_date DEFAULT DATE '2020-10-01';
DECLARE initial_snapshot_date date DEFAULT DATE '2020-08-01';
DECLARE formatted_date STRING;

-- FOR LOOP, is working
FOR date_suffix in (
  select * from UNNEST(GENERATE_DATE_ARRAY(initial_snapshot_date, final_snapshot_date, INTERVAL 1 MONTH)) date
)
DO

  SET formatted_date = FORMAT_DATE("%Y%m%d", date_suffix.date);
  SET end_of_month = LAST_DAY(date_suffix.date);
  IF 
    date_suffix.date > final_snapshot_date THEN
    LEAVE; -- kill
  END IF;
  -- create some variables, through a table called tablename. this is the table we want to shard with formatted_date and save
  EXECUTE IMMEDIATE
    FORMAT("CREATE OR REPLACE TABLE project_id.dataset_id.tablename_%s", formatted_date);
    FROM `project_id.dataset_id. example_table `
    WHERE OpponentID = 77

END FOR;

CodePudding user response：

If I understand correctly you would like to create tables and populate them with rows where OpponentID = 77. If not, let me know so I can delete my answer.

See query below for the implementation:

DECLARE end_of_month date;
DECLARE final_snapshot_date DEFAULT DATE '2020-10-01';
DECLARE initial_snapshot_date date DEFAULT DATE '2020-08-01';
DECLARE formatted_date STRING;

FOR date_suffix IN (select * from unnest(GENERATE_DATE_ARRAY(initial_snapshot_date, final_snapshot_date, INTERVAL 1 MONTH)) date)

DO
  SET formatted_date = FORMAT_DATE("%Y%m%d", date_suffix.date);
  SET end_of_month = LAST_DAY(date_suffix.date);
  IF 
    date_suffix.date > final_snapshot_date THEN
    LEAVE; -- kill
  END IF;
  EXECUTE IMMEDIATE FORMAT (
    """
    CREATE OR REPLACE TABLE `project_id.dataset_id.tablename_%s` AS (
    SELECT * FROM `project_id.dataset_id.example_table` where OpponentID = 77  
    )
    """,formatted_date);
END FOR;