Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save vadirajks/a41a0e3d959382482982900a7fef26ab to your computer and use it in GitHub Desktop.
Save vadirajks/a41a0e3d959382482982900a7fef26ab to your computer and use it in GitHub Desktop.
bq_per_table_storage_billing_recommendation.sql
DECLARE active_logical_price_per_gb NUMERIC DEFAULT 0.02;
DECLARE long_term_logical_price_per_gb NUMERIC DEFAULT 0.01;
DECLARE active_physical_price_per_gb NUMERIC DEFAULT 0.04;
DECLARE long_term_physical_price_per_gb NUMERIC DEFAULT 0.02;
WITH storage AS
(
SELECT DISTINCT
tb.table_schema AS dataset,
tb.table_name,
total_rows,
total_partitions,
-- Uncompressed bytes logical
total_logical_bytes AS total_logical_bytes,
ROUND(total_logical_bytes/POW(1024, 3),2) AS total_logical_gib,
ROUND(total_logical_bytes/POW(1024, 4),2) AS total_logical_tib,
active_logical_bytes AS active_logical_bytes,
-- ROUND(SUM(IF(deleted=false, active_logical_bytes, 0)) / power(1024, 3),2) AS active_logical_gib,
-- ROUND(SUM(IF(deleted=false, active_logical_bytes, 0)) / power(1024, 4),2) AS active_logical_tib,
ROUND(active_logical_bytes/POW(1024, 3),2) AS active_logical_gib,
ROUND(active_logical_bytes/POW(1024, 4),2) AS active_logical_tib,
long_term_logical_bytes AS long_term_logical_bytes,
-- ROUND(SUM(IF(deleted=false, long_term_logical_bytes, 0)) / power(1024, 3),2) AS long_term_logical_gib,
-- ROUND(SUM(IF(deleted=false, long_term_logical_bytes, 0)) / power(1024, 4),2) AS long_term_logical_tib,
ROUND(long_term_logical_bytes/POW(1024, 3),2) AS long_term_logical_gib,
ROUND(long_term_logical_bytes/POW(1024, 4),2) AS long_term_logical_tib,
-- Compressed bytes physical
total_physical_bytes AS total_physical_bytes,
ROUND(total_physical_bytes/POW(1024, 3),2) AS total_physical_gib,
ROUND(total_physical_bytes/POW(1024, 4),2) AS total_physical_tib,
-- Note that active physical bytes includes time travel so need to remove that
active_physical_bytes-time_travel_physical_bytes AS active_physical_bytes,
ROUND((active_physical_bytes-time_travel_physical_bytes)/POW(1024, 3),2) AS active_physical_gib,
ROUND((active_physical_bytes-time_travel_physical_bytes)/POW(1024, 4),2) AS active_physical_tib,
long_term_physical_bytes AS long_term_physical_bytes,
ROUND(long_term_physical_bytes/POW(1024, 3),2) AS long_term_physical_gib,
ROUND(long_term_physical_bytes/POW(1024, 4),2) AS long_term_physical_tib,
time_travel_physical_bytes AS time_travel_physical_bytes,
ROUND(time_travel_physical_bytes/POW(1024, 3),2) AS time_travel_physical_gib,
ROUND(time_travel_physical_bytes/POW(1024, 4),2) AS time_travel_physical_tib,
fail_safe_physical_bytes AS fail_safe_physical_bytes,
ROUND(fail_safe_physical_bytes/POW(1024, 3),2) AS fail_safe_physical_gib,
ROUND(fail_safe_physical_bytes/POW(1024, 4),2) AS fail_safe_physical_tib,
-- Compression ratios
ROUND(SAFE_DIVIDE(total_logical_bytes, total_physical_bytes),2) AS total_compression_ratio,
ROUND(SAFE_DIVIDE(active_logical_bytes, active_physical_bytes),2) AS active_compression_ratio,
ROUND(SAFE_DIVIDE(long_term_logical_bytes, long_term_physical_bytes),2) AS long_term_compression_ratio,
-- logical Pricing
ROUND(((active_logical_bytes/POW(1024, 3))*active_logical_price_per_gb) +
((long_term_logical_bytes/POW(1024, 3))*long_term_logical_price_per_gb),2) AS total_logical_price,
ROUND(((active_logical_bytes/POW(1024, 3))*active_logical_price_per_gb),2) AS active_logical_price,
ROUND(((long_term_logical_bytes/POW(1024, 3))*long_term_logical_price_per_gb),2) AS long_term_logical_price,
-- physical Pricing
ROUND((((active_physical_bytes-time_travel_physical_bytes)/POW(1024, 3))*active_physical_price_per_gb) +
((long_term_physical_bytes/POW(1024, 3))*long_term_physical_price_per_gb),2) AS total_physical_price,
ROUND((((active_physical_bytes-time_travel_physical_bytes)/POW(1024, 3))*active_physical_price_per_gb),2) AS active_physical_price,
ROUND((long_term_physical_bytes/POW(1024, 3))*long_term_physical_price_per_gb,2) AS long_term_physical_price,
ROUND((time_travel_physical_bytes/POW(1024, 3))*active_physical_price_per_gb,2) AS time_travel_physical_price,
ROUND((fail_safe_physical_bytes/POW(1024, 3))*active_physical_price_per_gb,2) AS fail_safe_physical_price,
-- Price differences, note that >0 means physical storage is cheaper before adding in time travel and failsafe
ROUND((((active_logical_bytes/POW(1024, 3))*active_logical_price_per_gb))-((((active_physical_bytes-time_travel_physical_bytes)/POW(1024, 3))*active_physical_price_per_gb)),2) AS active_price_difference,
ROUND((((long_term_logical_bytes/POW(1024, 3))*long_term_logical_price_per_gb))-((long_term_physical_bytes/POW(1024, 3))*long_term_physical_price_per_gb),2) AS long_term_price_difference,
-- Time travel and fail safe storage reductions
ROUND((( (time_travel_physical_bytes/POW(1024, 3))*active_physical_price_per_gb)+((fail_safe_physical_bytes/POW(1024, 3))*active_physical_price_per_gb)),2) AS additional_costs_for_physical_storage,
-- Totals for each model
ROUND((((active_logical_bytes/POW(1024, 3))*active_logical_price_per_gb))+(((long_term_logical_bytes/POW(1024, 3))*long_term_logical_price_per_gb)),2) AS logical_storage_price,
ROUND((((((active_physical_bytes-time_travel_physical_bytes)/POW(1024, 3))*active_physical_price_per_gb))+((long_term_physical_bytes/POW(1024, 3))*long_term_physical_price_per_gb))+
(( (time_travel_physical_bytes/POW(1024, 3))*active_physical_price_per_gb)+((fail_safe_physical_bytes/POW(1024, 3))*active_physical_price_per_gb)),2) AS physical_storage_price,
-- Difference in values (logical - active)
ROUND(((((active_logical_bytes/POW(1024, 3))*active_logical_price_per_gb))+(((long_term_logical_bytes/POW(1024, 3))*long_term_logical_price_per_gb)))
-
(
(((((active_physical_bytes-time_travel_physical_bytes)/POW(1024, 3))*active_physical_price_per_gb))+((long_term_physical_bytes/POW(1024, 3))*long_term_physical_price_per_gb))+
(( (time_travel_physical_bytes/POW(1024, 3))*active_physical_price_per_gb)+((fail_safe_physical_bytes/POW(1024, 3))*active_physical_price_per_gb))
),2) AS difference_price,
-- Recommendation
IF((ROUND((((active_logical_bytes/POW(1024, 3))*active_logical_price_per_gb))+(((long_term_logical_bytes/POW(1024, 3))*long_term_logical_price_per_gb)),2)) < (ROUND((((((active_physical_bytes-time_travel_physical_bytes)/POW(1024, 3))*active_physical_price_per_gb))+((long_term_physical_bytes/POW(1024, 3))*long_term_physical_price_per_gb))+(( (time_travel_physical_bytes/POW(1024, 3))*active_physical_price_per_gb)+((fail_safe_physical_bytes/POW(1024, 3))*active_physical_price_per_gb)),2)),'Logical storage', 'Physical storage') AS recommendation
FROM
`region-us`.INFORMATION_SCHEMA.TABLE_STORAGE AS tb
JOIN `region-us`.INFORMATION_SCHEMA.TABLES AS t
ON t.table_catalog = tb.project_id
AND t.table_name = tb.table_name
WHERE
tb.deleted = false
)
select * from storage order by dataset,table_name,difference_price;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment