ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1
Latest Update Date:
10/2/2023 9:28 AM
2
Make a copy of this to start editing, also note it is per-project so you might need to make a copy per project.
3
4
BigQuery Editions Pricing Analysis Tool
5
This analysis tool will consume your BigQuery usage patterns (both compute and storage) and then generate an analysis of that data to give an estimate of spend with Google's new pricing models that go into effect on July 5, 2023.
6
7
Important Notes
8
Note this tool is doing an estimation of the BigQuery usage for a project with known historical values from your BigQuery usage, there may be additional factors, charges, or changes that are yet to come that are not included in the output of this tool. Thus this tool should only be used as general guidance and not as an absolute source of truth for charges with these new billing models.
9
Note due to limitations with Sheets this can only query a single project at a time and not a whole organization. Making copies of this and running against separate projects is the easiest method to do this for an entire organization
10
11
IAM Notes
12
This tool will be running queries against the INFORMATION_SCHEMA views inside of a project. It is recommended that the user that has opened up this Sheet have at least the BigQuery Data Editor role for the project that will be used for this analysis
13
14
How to Use This Tool
15
After running the below instructions numerous calculations on your usage are performed to calculate estimates on BigQuery compute and storage costs for multiple scenarios with Editions, Compressed Storage, and On-Demand pricing models. These estimates can be used to help determine what pricing may look like after July 5, 2023 (or before if opted into starting early) when this pricing goes into effect.
16
17
Instructions
18
The instructions below are a bit tedious, but unfortunately there is not an easy way to pull data from BigQuery into a format that is usable by both technical BigQuery users and the non-technical users that would need to consume this data to assist in making decisions based upon it. So a balance had to be made which was a Google Sheet showing this data and due to limitations in Sheets and BigQuery integration there are more steps than myself as the author would have liked and if you are able to discover a way to automate this without using App Scripts (that requires more permissions and many organizations restrict its use) please let me know and I will implement it on here.
19
20
1.
Navigate to the BQ Compute Data sheet at the bottom of this page
21
2.
Click the "Connection Settings" link in the upper right corner (has a sprocket icon next to it)
22
3.
Under the "Query settings" and "Schema" dropdowns choose the project you wish to run these calculations on. You are able to also type in the project name as well if it is not appearing.
23
4.
At this point preview data for your jobs will be populated upon that sheet, but is not yet used in calculations
24
5.
Open up the BQ Compute Extract Sheet and at the bottom left side near the row numbers is a refresh button. Click that button and wait for it to finish.
25
6.
At this point all compute calculations should be filled out on the Compute Calculations sheet
26
7.
Next up will be the querying of storage data
27
8.
Navigate to the BQ Storage Data sheet at the bottom of this page
28
9.
On this sheet click the "Connection Settings" link in the upper right corner (has a sprocket icon next to it) as you did previously
29
10.
Again under the "Query settings" and "Schema" dropdowns choose the project you wish to run these calculations on
30
11.
At this point the data for your BQ Storage Data sheet will be populated upon that tab
31
12.
Open up the BQ Storage Extract Sheet and at the bottom left side near the row numbers is a refresh button. Click that button and wait for it to finish.
32
13.
At this point all of the compute and storage data inside of calculations will be populated and showing up on the first 3 sheets in this spreadsheet.
33
14.
On the Compute+Storage sheet a general summary is generated for each
34
15.
If you currently use flat-rate pricing in the project being modeled, check the flat-rate pricing checkbox and enter in the monthly and annual slot counts.
35
36
Additional Notes
37
On the Slot Usage Chart sheet you will find a chart showing your slot usage graphed out, this may need to be refreshed after refreshing your data in the instructions above. This can be an invaluable tool to see how your slot usage is trending across the project. Note this is in minutes.
38
You will notice in the query for compute I am doing just a basic average over all usage and that there are some p-series values in there as well. This was due to some workloads were very inaccurate versus a "standard average."
39
40
Feedback, Bugs, or Contacting Owners
41
Please fill out the form here to file a bug, provide feedback, or contact the maintainers of this tool.
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100