Assigned
Status Update
No update yet.
Comments
jo...@google.com
pp...@google.com
+1
da...@trafficguard.ai
+1
ni...@bmg.com
Hi All,
I am leading Data FinOps at BMG Rights Management and would also love this feature. However, to unlock internal reporting in the meantime we have two approaches:
1. The Extended GCP Billing Export to BigQuery contains a line for every single job, associated with the reservation under Service "BigQuery" and SKU "Analysis Slots Attribution". For all these lines Cost is 0 but the usage column is filled with the slots used and the project_id column refers to the execution project. We use to these rows do attribution of the Reservation cost back to the execution project. Essentially adding rows that multiply the slots with the price and then add additional rows under the "BigQuery Reservation API" Service that subtract the attributed amount again. This allows for two perspectives on the data (1) attributed and (2) not attributed original.
Due to the overhead on the reservations API (caused by the auto scaler scaling down with some delay) we also multiply the slots used with a monthly "overhead_ratio". This way we can attribute all cost in a reasonable way (all execution projects share the overhead proportionally to their usage).
2. The BigQuery INFORMATION_SYSTEMS.JOBS_BY_ORGANIZATION table also allows for a decent allocation of cost if you add and "estimated cost" column. You can do this by multiplying the reported slots with the SKU price. In our case, to again account for the Autoscaler overhead, we calculate the effective price per slot hour by taking the cost reported in GCP Billing on the usage month and divide it by reported slots, resulting in a total amount that matches the Billing report. There will be obvious inaccuracies in these estimates but it can help getting a perspective on user / service account level.
All the above is done in dbt but the code is slightly contaminated with a bit of proprietary logic. If there is interest here to collaborate on an open source version of this, I am happy to start a repo otherwise the above might already help you get something off the ground.
Best
Nico
I am leading Data FinOps at BMG Rights Management and would also love this feature. However, to unlock internal reporting in the meantime we have two approaches:
1. The Extended GCP Billing Export to BigQuery contains a line for every single job, associated with the reservation under Service "BigQuery" and SKU "Analysis Slots Attribution". For all these lines Cost is 0 but the usage column is filled with the slots used and the project_id column refers to the execution project. We use to these rows do attribution of the Reservation cost back to the execution project. Essentially adding rows that multiply the slots with the price and then add additional rows under the "BigQuery Reservation API" Service that subtract the attributed amount again. This allows for two perspectives on the data (1) attributed and (2) not attributed original.
Due to the overhead on the reservations API (caused by the auto scaler scaling down with some delay) we also multiply the slots used with a monthly "overhead_ratio". This way we can attribute all cost in a reasonable way (all execution projects share the overhead proportionally to their usage).
2. The BigQuery INFORMATION_SYSTEMS.JOBS_BY_ORGANIZATION table also allows for a decent allocation of cost if you add and "estimated cost" column. You can do this by multiplying the reported slots with the SKU price. In our case, to again account for the Autoscaler overhead, we calculate the effective price per slot hour by taking the cost reported in GCP Billing on the usage month and divide it by reported slots, resulting in a total amount that matches the Billing report. There will be obvious inaccuracies in these estimates but it can help getting a perspective on user / service account level.
All the above is done in dbt but the code is slightly contaminated with a bit of proprietary logic. If there is interest here to collaborate on an open source version of this, I am happy to start a repo otherwise the above might already help you get something off the ground.
Best
Nico
Description
This will create a feature request which anybody can view and comment on.
Please describe your requested enhancement. Good feature requests will solve common problems or enable new use cases.
What you would like to accomplish:
How this might work:
Currently, "Adding labels to resources" [1] is not supported for Bigquery reservations, Slot commitments or assignments.
Users can adding a label to a label to Bigquery reservations, Slot commitments or assignments as same instructions in the documentation [1].
If applicable, reasons why alternative solutions are not sufficient:
Other information (workarounds you have tried, documentation consulted, etc):
[1]https://cloud.google.com/bigquery/docs/adding-labels