Harnessing the Power of BigQuery and CloudQuery for Google Cloud Cost Optimization
This tutorial will show how to correlate between GCP billing data and CloudQuery data with BigQuery to optimise cost.
Yevgeny Pats • Dec 15, 2022
Last week we just announced our BigQuery destination and already saw some interesting use cases around cost we want to share, and also thank our great community helping with ideas for this blog!
Exporting GCP billing data to BigQuery is a powerful way to analyse your GCP cost. However, sometimes it is not enough to have the billing data in BigQuery. You also need to correlate it with your cloud infrastructure data. This tutorial will show how to correlate between GCP billing data and CloudQuery data with BigQuery to optimize cost.
- GCP Billing Export to BigQuery is enabled
First, we need to sync our GCP data with CloudQuery to the same BigQuery dataset we synced our billing data to. To do that we will use the following CloudQuery configuration file (For full config reference, checkout the GCP Source Plugin and BigQuery Destination Plugin):
kind: source spec: # Source spec section name: 'gcp' path: 'cloudquery/gcp' registry: 'cloudquery' version: 'VERSION_SOURCE_GCP' destinations: ['bigquery'] tables: ['gcp_billing*'] spec: # GCP Spec section described below project_ids: ['<project-id>'] --- kind: destination spec: name: bigquery path: cloudquery/bigquery registry: 'cloudquery' version: 'VERSION_DESTINATION_BIGQUERY' write_mode: 'append' spec: project_id: '<project-id>' dataset_id: costdata
Once the data is synced we will see something like the below in the BigQuery UI:
You can see all CloudQuery tables prefix with
gcp_and two GCP billing table prefixed with
gcp_billing_export_resource. In our case the interesting one is
gcp_billing_export_resourcewhich contains the billing data for each resource so we can join with CloudQuery data easily.
Now let's dive into some examples.
Let's say we want to find all unattached disks and figure out how much they cost.
First let's find all unattached disks by query all disks with
usersfield is null (which per gcp documentation is the case for unattached disks):
select * from gcp_compute_disks where users is null
Now let's join with the GCP billing data.
SELECT sum(cost) FROM `cq-playground.costdata.gcp_billing_export_resource_v1_0183D4_4E0A4D_60E401` gcp_billing_export_resource join `cq-playground.costdata.gcp_compute_disks` gcp_compute_disks on gcp_billing_export_resource.resource.name = gcp_compute_disks.name WHERE DATE(_PARTITIONTIME) = "2022-12-14" and ARRAY_LENGTH(gcp_compute_disks.users) = 0
This query should give us total of all costs of unattached disks for a specific date, so we don't scan the whole historical table. We can also run this on a date range.
Let's say we are running an experiment of migrating our workloads to different architecture and we want to compare costs. This can be easily done by the following queries.
Calculate cost for
SELECT sum(cost) FROM `cq-playground.costdata.gcp_billing_export_resource_v1_0183D4_4E0A4D_60E401` gcp_billing_export_resource join `cq-playground.costdata.gcp_compute_instances` gcp_compute_instances on gcp_billing_export_resource.resource.name = gcp_compute_instances.name WHERE DATE(_PARTITIONTIME) = "2022-12-14" and gcp_compute_instances.cpu_platform = "Intel Broadwell"
And you can run the same by replacing
AMD Romeand/or any other architecture.
In this short blog post we just shared a sample of what you can do by combining cost data with your infrastructure state/metadata synced by CloudQuery to BigQuery. The number of use cases around cost (aka "FinOps" :) ) is really infinite and it all depends on what you are trying to achieve and optimize for. CloudQuery with BigQuery is a powerful tool for analysis which ensures cheap storage and fast querying on large amount of data.