DataFlowを使用して履歴データをアーカイブする

CREATE PROCEDURE archive()

BEGIN

-- Get the max date from the premium data

SELECT @maxDataDate:= MAX(`Date`)

FROM   ytd_premium_data;

-- Get the max date from the archived data

SELECT @maxArchiveDate:= MAX(`Date`)

FROM   move_to_historical_method_1;

-- We will use these two dates later to determine whether or not the

-- current year-to-date data has already been added to the historical

-- archive

-- The following is a shortcut to dynamically create a table structure

-- that's identical to another table, but leave it with no data in it.

-- We’re going to use this trick to create a table with the same

-- structure as the year-to-date-data

-- Create a table by selecting only one row from the premium data

CREATE TABLE archive_data

SELECT     *

FROM    ytd_premium_data LIMIT 1;

-- Get rid of all rows from the newly created table

TRUNCATE TABLE archive_data;

-- If the conditions are met to archive the year-to-date data, this

-- new table will be populated with the year-to-date data and appended

-- to the historical DataSet. If the conditions are not met, the table

-- will remain empty, and an empty table will be appended to the

-- historical DataSet.

-- The year of data to be archived is last year's data. Remember that

-- the year-to-date data is a month behind, so December’s data won’t

-- be loaded until January of the next year.

IF YEAR(CURRENT_DATE()) - 1 = YEAR(@maxDataDate)

-- It's January, which means we should have December's data

AND MONTH(CURRENT_DATE()) = 1

-- The data to be archived contains December's data

AND MONTH(@maxDataDate) = 12

-- The data from the year-to-date has not already been added to the

-- archive

AND @maxDataDate!= @maxArchiveDate

-- If every condition checks out, then insert the year-to-date data to

-- be archived into the archive_data table for unioning to the

-- historical data. Because of the date checks in the previous step,

-- the following code will only ever be executed once.

THEN INSERT INTO archive_data

SELECT     *

FROM ytd_premium_data;

END IF;

END;

FROM move_to_historical_method_1

UNION ALL

SELECT     *

FROM archive_data

-- This DataFlow won't automatically trigger until the scheduled

-- update file gets updated. When that happens, it will append last

-- year's data to historical data.

SELECT     *

FROM move_to_historical_method_2

UNION ALL

SELECT     *

FROM ytd_premium_data