To calculate daily storage costs using the TABLE_STORAGE_USAGE_TIMELINE view in BigQuery, it's essential to accurately convert storage usage metrics from MB-seconds to GB-day and then apply the appropriate cost calculations.
Step-by-Step Conversion and Calculation
-
Convert MB-seconds to GB-seconds: The first step involves converting the storage usage from MB-seconds to GB-seconds. Since there are 1,024 MB in a GB, you divide the MB-seconds value by 1,024.
-
Convert GB-seconds to GB-day: Next, to convert the usage to a daily rate, multiply the GB-seconds value by 86,400, which is the number of seconds in a day.
-
Calculate the Cost per GB per Day: Storage costs are typically provided on a monthly basis. To find the daily rate, divide the monthly cost per GB by 30. For instance, if the monthly cost is $0.02 per GB, the daily cost per GB is approximately $0.00066667.
-
Compute the Daily Storage Cost: Finally, multiply the GB-day value by the daily cost per GB to determine the daily storage cost.
Practical Example
Consider the MB-seconds value for a specific date: 4,325,568,725,105 MB-seconds for 27/11/2023.
-
Convert MB-seconds to GB-seconds: GB-seconds=4,325,568,725,105 MB-seconds1,024=4,224,175,708,203.125 GB-seconds\text{GB-seconds} = \frac{4,325,568,725,105 \text{ MB-seconds}}{1,024} = 4,224,175,708,203.125 \text{ GB-seconds}GB-seconds=1,0244,325,568,725,105 MB-seconds=4,224,175,708,203.125 GB-seconds
-
Convert GB-seconds to GB-day: GB-day=4,224,175,708,203.125×86,400=365,510,015,519,755.44 GB-day\text{GB-day} = 4,224,175,708,203.125 \times 86,400 = 365,510,015,519,755.44 \text{ GB-day}GB-day=4,224,175,708,203.125×86,400=365,510,015,519,755.44 GB-day
-
Calculate the Daily Storage Cost: Given a monthly storage cost of $0.02 per GB: Cost per GB per day=0.0230=0.00066667\text{Cost per GB per day} = \frac{0.02}{30} = 0.00066667Cost per GB per day=300.02=0.00066667 Daily Storage Cost=365,510,015,519,755.44×0.00066667≈243,671,354,730.66\text{Daily Storage Cost} = 365,510,015,519,755.44 \times 0.00066667 \approx 243,671,354,730.66Daily Storage Cost=365,510,015,519,755.44×0.00066667≈243,671,354,730.66
This result appears excessively high, suggesting a potential error in the initial MB-seconds value or an anomaly in storage usage. To ensure accuracy, it's important to verify the MB-seconds data and consider averaging over a longer period, such as a week.
Averaging for Accuracy
To smooth out anomalies, calculate the average MB-seconds over a week:
-- Define the cost per GB per month
DECLARE cost_per_gb_per_month FLOAT64 DEFAULT 0.02;
-- Calculate the cost per GB per day
DECLARE cost_per_gb_per_day FLOAT64 DEFAULT cost_per_gb_per_month / 30;
WITH storage_usage AS (
SELECT
DATE(usage_date) AS date,
table_schema,
table_name,
AVG(billable_active_physical_usage) AS avg_storage_in_mb_seconds
FROM
`region-EU.INFORMATION_SCHEMA.TABLE_STORAGE_USAGE_TIMELINE`
WHERE
project_id = '<project_name>'
AND table_schema = '<dataset_name>'
AND table_name = '<table_name>'
AND usage_date BETWEEN DATE_SUB('2023-11-27', INTERVAL 7 DAY) AND '2023-11-27'
GROUP BY
date, table_schema, table_name
),
storage_cost AS (
SELECT
date,
table_schema,
table_name,
avg_storage_in_mb_seconds,
-- Convert MB-second to GB-second
avg_storage_in_mb_seconds / 1024 AS storage_in_gb_seconds,
-- Convert GB-second to GB-day
(avg_storage_in_mb_seconds / 1024) * 86400 AS storage_in_gb_day,
-- Calculate daily storage cost
((avg_storage_in_mb_seconds / 1024) * 86400) * cost_per_gb_per_day AS storage_cost_usd
FROM
storage_usage
)
SELECT
date,
table_schema,
table_name,
ROUND(storage_in_gb_day, 6) AS storage_in_gb_day,
ROUND(storage_cost_usd, 6) AS storage_cost_usd
FROM
storage_cost
ORDER BY
date DESC;
LinkedIn
Twitter
