Intro

AWS

GCP

I am currently using both GCP and AWS for a certain project. While each cloud providers provides very nice tool and visualizations for their own spending (Cloud billing for GCP and Cost&Usage report for AWS respectively), I wanted to consolidate both cloud providers usage report and visualize using single BI tool. The report does not need to be realtime, I only need daily granularity on my report. (No streaming)

So I had to spin up something and these were couple options

• House data to AWS using Athena+Quicksight(or other BI tool)
• House data to GCP using Bigquery+datastudio(or other BI tool)
• Spend more time searching for 3rd party tools

I am a big fan of BigQuery and I prefer to to ELT rather than ETL on BQ. Moreover, I was already using BigQuery for the project with holistics.io, I chose to house usage and cost data to bigquery.

Move data to BQ

GCP Cloud Billing

Setup

This is easy, cloud billing natively support bigquery export.

• Enable billing for the GCP project.

  I assume that you already have billing account setup. (You wouldn't need data analytics unless you are spending any moeny) But if you need to enable billing for the project, refer to the following link.

  Modify a project's billing settings | Cloud Billing | Google Cloud

• Create a dataset in BigQuery whthin the GCP project you desire - I named it billing_export

  Creating datasets | BigQuery | Google Cloud

• You need appropriate permissions to setup export

  • Billing account Admin
  • Bigquery Admin for the project
  • resourcemanager.projects.update permission

• Enable Cloud Billing export to BigQuery dataset

  • Go to Billing menu in Navigation menu (https://console.cloud.google.com/billing/)
  • Go to linked billing account (this should be already set, if not you need to add payment info)
  • Select Billing export → Select BigQuery export tab
  • Select edit setting on Daily cost detail
    • Select the project and dataset where you want the data to be sinked. (note) The BigQuery API is required to export data to BigQuery. If the project you selected doesn't have the BigQuery API enabled, you will be prompted to enable it. Click Enable BigQuery API and the API will be enabled for you

Once everything is set, you will be able to see your screen like this.

When you first enable the daily cost detail export to BigQuery, it might take a few hours to start seeing your Google Cloud cost data. Table with gcp_billing_export_v1_<some_hash> will be generated. Table will be automatically partitioned by day. (you can query partitioned data to save cost)

Schema