Skip to content

Instantly share code, notes, and snippets.

View nadavwn's full-sized avatar

nadavwn

1 follower · 0 following
View GitHub Profile
@nadavwn
nadavwn / Standard Edition cost estimation.sql
Last active July 31, 2023 16:00
The code creates a table below shows the actual Slot allocation and estimated cost in an hour or day resolution (not accurate since it depends on Autoscaler behaviour: https://cloud.google.com/bigquery/docs/slots-autoscaling-intro#using_autoscaling_reservations)
-- choose. the region DEFAULT: us
-- choose the time window start_time and end_time and
-- choose if it's in a DAY or HOUR level aggregation of results
-- set the project-id and run from the project where the reservation is defined
DECLARE SE_PRICE FLOAT64 DEFAULT 0.04;
DECLARE PAYG_PRICE FLOAT64 DEFAULT 0.06;
DECLARE start_time DEFAULT TIMESTAMP('2023-05-01 07:00:00');
DECLARE end_time DEFAULT TIMESTAMP('2023-06-30 07:00:00');
@nadavwn
nadavwn / Monitor BQ edition autoscaling.sql
Last active July 11, 2023 04:48
Monitor the changes in BQ editions using AutoScaling. show for each change the allocation and the duration of the specific allocation with estimated cost
-- run from the project where the reservation is defined
-- choose. the region DEFAULT: us
-- choose the time window dt_start and dt_end
-- set the project id
DECLARE
dt_start DEFAULT TIMESTAMP('2023-05-01 00:00:00');
DECLARE
dt_end DEFAULT TIMESTAMP('2023-05-01 23:59:00');
--SET @@dataset_project_id = 'my-project-id';
@nadavwn
nadavwn / BQ SE max configuration.sql
Last active July 10, 2023 14:11
Code for getting the median daily slot usage and the estimated max configuration for Standard Edition
-- set the region, DEFAULT: us
-- set the time window dt_start and dt_end
-- set the project 'dataset_project_id' in case running in a different project (i.e. admin)
DECLARE
dt_start DEFAULT TIMESTAMP("2023-03-01 00:00:00") ;
DECLARE
dt_end DEFAULT TIMESTAMP("2023-05-31 23:59:59");
--SET @@dataset_project_id = 'my-project-id';
WITH
@nadavwn
nadavwn / BigQuery OnDemand vs SE.sql
Last active July 12, 2023 11:33
The following code creates a table that shows the comparison between On-Demand and Standard Edition, while the ratio between Slot cost to On-demand presents the estimated cost change moving from On-demand to Standard edition. Ratio value less than 100 shows the possibility of cost reduction, and above 100 shows the possibility to cost increase.
-- choose. the region DEFAULT: us
-- choose the time window dt_start and dt_end
-- In case it's not the current project set the dataset_project_id to the relevant project
DECLARE current_on_demand_price_per_tb INT64 DEFAULT 5;
DECLARE new_on_demand_price_per_tb FLOAT64 DEFAULT 6.25;
DECLARE standard_edition_price FLOAT64 DEFAULT 0.04;
DECLARE standard_edition_factor FLOAT64 DEFAULT 1.5;