MarquinSmith

/*
A SQL query to calculate an estimate for a resonable
'daily query usage' quota in BigQuery.

Defaults to free tier threshold is predicted value is smaller.

Uses most recent period data to predict future values.

The maximum of the predicted values is then chosen and multiplied 
by a 'buffer_multiplier' to provide some buffer.
*/

-- the number of days to build the history from
DECLARE days_of_history DEFAULT 60;


-- the factor by which the buffer for 
DECLARE buffer_multiplier DEFAULT 2;

-- delete the model once script finishes
DECLARE drop_model BOOLEAN DEFAULT TRUE;


-- create a time series model that is capable of predicting
-- future daily tebibytes used by bigquery
CREATE OR REPLACE MODEL `<PROJECT>.<DATASET>.tebibytesbilledmodel`
 OPTIONS(MODEL_TYPE='ARIMA_PLUS',
         time_series_timestamp_col='day',
         time_series_data_col='total_tebi_billed') AS
SELECT
  DATE(creation_time) AS day,
  SUM(total_bytes_billed) / POW(1024, 4) AS total_tebi_billed
FROM
  -- **CHANGE THE BELOW LINE TO THE REGION YOUR DATA IS IN** 
  `region-us-central1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  --uses the days of history as the number of days to create the model
  creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL days_of_history DAY) AND CURRENT_TIMESTAMP()
  AND job_type = "QUERY"
GROUP BY
    day
ORDER BY
    day DESC;



SELECT
  -- get the largest predicted value and scale it up by the 'buffer_multiplier'
  -- returns the free tier quota limit if it is larger.
  ROUND(
    CASE
      WHEN MAX(forecast_value) * buffer_multiplier < 0.03 THEN 0.03
      ELSE MAX(forecast_value) * buffer_multiplier
    END,
    4 
  ) AS recommended_daily_query_usage_quota,
FROM
  ML.FORECAST(MODEL `<PROJECT>.<DATASET>.tebibytesbilledmodel`,
    STRUCT(30 AS horizon, 0.8 AS confidence_level));



-- clean up
IF drop_model THEN
  DROP MODEL`<PROJECT>.<DATASET>.tebibytesbilledmodel`;
END IF;

-- create a time series model that is capable of predicting
-- future daily tebibytes used by bigquery
CREATE OR REPLACE MODEL `<PROJECT>.<DATASET>.tebibytesbilledmodel`
 OPTIONS(MODEL_TYPE='ARIMA_PLUS',
         time_series_timestamp_col='day',
         time_series_data_col='total_tebi_billed') AS
SELECT
  DATE(creation_time) AS day,
  SUM(total_bytes_billed) / POW(1024, 4) AS total_tebi_billed
FROM
  `region-us-central1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  --uses the days of history as the number of days to create the model
  creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL days_of_history DAY) AND CURRENT_TIMESTAMP()
  AND job_type = "QUERY"
GROUP BY
    day
ORDER BY
    day DESC;

SELECT
  -- get the largest predicted value and scale it up by the 'buffer_multiplier'
  -- returns the free tier quota limit if it is larger.
  ROUND(
    CASE
      WHEN MAX(forecast_value) * buffer_multiplier < 0.03 THEN MAX(forecast_value) * buffer_multiplier
      ELSE MAX(forecast_value) * buffer_multiplier
    END,
    4 
  ) AS recommended_daily_query_usage_quota,
FROM
  ML.FORECAST(MODEL `<PROJECT>.<DATASET>.tebibytesbilledmodel`,
    STRUCT(30 AS horizon, 0.8 AS confidence_level));

-- clean up
IF drop_model THEN
  DROP MODEL`<PROJECT>.<DATASET>.tebibytesbilledmodel`;
END IF;