Export Billing Data for Improved Cost Management
Your provider’s tools are inadequate for managing your cloud bill. Exporting your consumption detail is an essential foundation for cost management.
In this article we show how to establish this on Google Cloud Platform (GCP). We’ll configure a billing account to export to Google’s BigQuery and then make it useful for queries - both for monitoring processes and reporting - building on the cost-center labeling1 we’ve established.
Billing Data Export Overview
A billing data export contains complete billing detail of your platform usage. Service usage gets allocated out by fractions of pennies based on use. It includes breakdown you’ve assigned using project and resource labels1.
Detailed instructions will follow - but here is an overview:
Configure the Billing Account for export, and data is written into Google’s managed Data Warehouse service: BigQuery. Multiple billing accounts requires configuring each for export.
Once configured for export, usage data is written to a BigQuery dataset you’ve defined. Data is only written on a go-forward basis; historical data is not provided. It is good configure this early - even if you haven’t labeled your projects with cost-center labels1 yet - because historical data is helpful in cost management efforts.
Next, create a BigQuery View, using the provided code. This view is key in simplifying management and reporting. It makes the data more usable for reporting by 1) enabling project cost-center label inheritance1 for resources that do not have a cost-center label defined and 2) by summarizing discounts for billing entries.
Once the view is created, you can start reporting and monitoring usage. Create an interface using your favorite BI tool, such as Google’s DataStudio, to provide reports and visualizations to your teams. This will be covered in more detail in a future article.
That’s the overall process. Let’s get into the detail.
Configuring GCP Billing Export
1. Create Cost Management Project
We (and Google) recommend creating a GCP project dedicated to Cost Management resources. This is where you will create a BigQuery dataset for the billing data to be exported.
GCP projects are free, and consolidating these resources will simplify ongoing management and maintenance.
This project will be a good home for the following components:
- BigQuery Dataset for Billing export
- Cloud Scheduler, Pub/Sub, and Function resources related to cost monitoring
- Cloud Billing API access
- Google Compute Engine (GCE) Committed Use Discount (CUD) reservations that apply across multiple projects
- BigQuery Slot Reservations that apply across multiple projects
Even if you are using none of these today, they will be covered in future articles and may become important as your cost management practices mature.
The project you create must be associated with the billing account you want to export data from in order to configure the billing export.
Example CLI project creation2:
gcloud project create YourOrg-gcp-costmgmt -billing-id=YourBillingID -label=cost-center=platformmanagement --organization=YourOrgNumber
and then link the project to your billing account3:
gcloud beta billing projects link YourOrg-gcp-costmgmt --billing-account=Your-Billing-Account-ID
If you don’t know your values for YourOrgNumber or Your-Billing-AccountID, get those from an existing project.
2. Create BigQuery Dataset
You must create a BigQuery dataset for the exported data to be placed. GCP will then create a table to start writing billing data into.
The BigQuery Dataset can be created via the BigQuery interface in the Cloud Console, or via CLI4 as follows:
bq mk YourOrg-gcp-costmgmt:GCP_Billing_Export
Expected confirmation is as follows:
Dataset 'yourorg-gcp-costmgmt:GCP_Billing_Export' successfully created.
3. Configure Billing Export
Configure your billing account to export to your BigQuery dataset through the GCP console5.
Browse to the GCP Billing Console and select your billing account you want to export data from.
Select “Billing Export” on the left menu, and then “Edit Settings”:
Next, select the GCP Project previously created, and the BigQuery Dataset. Click “Save”.
A successful configuration will look as follows:
A uniquely named table will be created in your BQ dataset for the billing data to be written to. This table may take 4-6 hours to be created. You can verify that it has been created with the following command:
bq ls --project_id YourOrg-gcp-costmgmt GCP_Billing_Export
Results should include a table created by the Billing Export process:
tableId Type Labels Time Partitioning Clustered Fields
-------------------------------------------- ------- -------- ------------------- ------------------
gcp_billing_export_v1_010D15_EFDEEE_FEEEE TABLE DAY
Once this table is created, you may continue to the next step.
4. Create BigQuery View
A View is needed to allow project labels and resource labels to both be considered for cost-center allocation. If a resource doesn’t have a cost-center label then the label from the hosting project will be applied to that billing entry.
You can extend the view to apply label inheritance to additional labels for your cost allocation needs. The included cost-center label builds on previous articles and is important if you have defined cost-center breakdown6 and applied1 the label to your projects.
The code for the View is provided in the CloudCostMgmt Toolkit7. You may create a view from that code by executing the following commands:
export PROJECT=YourOrg-gcp-costmgmt
export DATASET=GCP_Billing_Export
curl -sL https://raw.githubusercontent.com/saveoncloud/cloudcostmgmt/master/gcp/create_bq_billing_view.sh | bash
You should receive confirmation of view creation:
View 'YourOrg-gcp-costmgmt:GCP_Billing_Export.gcp_billing' successfully created.
5. Validate Data
At this point we’re ready to validate that the export, and the view, are both working. You can validate with the following query:
bq query --use_legacy_sql=false --project_id $PROJECT 'select count(*) as BillingRecords from GCP_Billing_Export.gcp_billing;'
Expected results (example):
Waiting on bqjob_r92500f05549303_00000172eb97c24e_1 ... (0s) Current status: DONE
+----------------+
| BillingRecords |
+----------------+
| 340 |
+----------------+
Summary
After configuration of the above steps you have detailed usage data exported to a BigQuery table. This will provide an important foundation for cost management. You are ready to begin using the data - both by automated monitoring processes as well as with Business Intelligence reports.