Billing Export to BigQuery Query Examples

Here are some examples of how you might use BigQuery queries on exported billing data.

For these examples, assume the following:

• You have 2 apps (grapefruit-squeezer and chocolate-masher).
• For each app, you have 2 environments (dev and prod).
• The dev environment has 1 small instance per app.
• The prod environment has 1 small instance in Americas and 1 small instance in Asia.
• Each instance is labeled with the app and environment.
• You have 1 instance with no labels that you use for experimentation.

Your total bill is $24 with the following breakdown:

Instance	Labels	Total Cost
Small instance with 1 VCPU running in Americas	None	$4
Small instance with 1 VCPU running in Americas	app: chocolate-masher environment: dev	$2
Small instance with 1 VCPU running in Americas	app: grapefruit-squeezer environment: dev	$3
Small instance with 1 VCPU running in Americas	app: chocolate-masher environment: prod	$3.25
Small instance with 1 VCPU running in Asia	app: chocolate-masher environment: prod	$3.75
Small instance with 1 VCPU running in Americas	app: grapefruit-squeezer environment: prod	$3.50
Small instance with 1 VCPU running in Asia	app: grapefruit-squeezer environment: prod	$4.50

The following examples illustrate ways to query your data.

Query every row without grouping

The most granular view of these costs would be to query every row without grouping. Assume all fields, except labels and sku description, are the same (project, service, and so on).

Standard SQL

SELECT
  sku.description,
  TO_JSON_STRING(labels) as labels,
 cost as cost
FROM `project.dataset.table`;

Legacy SQL

TO_JSON_STRING Not supported.

Row	sku.description	labels	cost
1	Small instance with 1 VCPU running in Americas	[]	$4
2	Small instance with 1 VCPU running in Americas	[{"key":"app","value":"chocolate-masher"},{"key":"environment","value":"dev"}]	$2
3	Small instance with 1 VCPU running in Americas	[{"key":"app","value":"grapefruit-squeezer"},{"key":"environment","value":"dev"}]	$3
4	Small instance with 1 VCPU running in Americas	[{"key":"app","value":"chocolate-masher"},{"key":"environment","value":"prod"}]	$3.25
5	Small instance with 1 VCPU running in Asia	[{"key":"app","value":"chocolate-masher"},{"key":"environment","value":"prod"}]	$3.75
6	Small instance with 1 VCPU running in Americas	[{"key":"app","value":"grapefruit-squeezer"},{"key":"environment","value":"prod"}]	$3.50
7	Small instance with 1 VCPU running in Asia	[{"key":"app","value":"grapefruit-squeezer"},{"key":"environment","value":"prod"}]	$4.50
		TOTAL	$24

Group by label map as a JSON string

This is a quick and easy way to break down cost by each label combination.

Standard SQL

SELECT
  TO_JSON_STRING(labels) as labels,
  sum(cost) as cost
FROM `project.dataset.table`
GROUP BY labels;

Legacy SQL

TO_JSON_STRING Not supported.

Row	labels	cost
1	[]	$4
2	[{"key":"app","value":"chocolate-masher"},{"key":"environment","value":"dev"}]	$2
3	[{"key":"app","value":"grapefruit-squeezer"},{"key":"environment","value":"dev"}]	$3
4	[{"key":"app","value":"chocolate-masher"},{"key":"environment","value":"prod"}]	$7
5	[{"key":"app","value":"grapefruit-squeezer"},{"key":"environment","value":"prod"}]	$8
	TOTAL	$24

Group by label value for a specific key

Breaking down costs for values of a specific label key is a common use case. By using a LEFT JOIN and putting the key filter in the JOIN condition (rather than WHERE), you include cost that does not contain this key, and so receive a complete view of your cost.

Standard SQL

SELECT
  labels.value as environment,
  SUM(cost) as cost
FROM `project.dataset.table`
LEFT JOIN UNNEST(labels) as labels
  ON labels.key = "environment"
GROUP BY environment;

Legacy SQL

SELECT
  labels.value as environment,
  SUM(cost) as cost
FROM [project:dataset.table]
WHERE labels.key = "environment" OR labels.key IS NULL
GROUP BY environment;

Row	environment	cost
1	prod	$15
2	dev	$5
3	null	$4
	TOTAL	$24

Group by key/value pairs

Be careful when interpreting or exporting these results. An individual row here shows a valid sum without any double counting, but should not be combined with other rows (except possibly if the key is the same, or if you are certain the keys are never set on the same resource).

Standard SQL

SELECT
  labels.key as key,
  labels.value as value,
  SUM(cost) as cost
FROM `project.dataset.table`
LEFT JOIN UNNEST(labels) as labels
GROUP BY key, value;

Legacy SQL

SELECT
  labels.key as key,
  labels.value as value,
  SUM(cost)
FROM [project:dataset.table]
GROUP BY key, value;

Row	key	value	cost
1	null	null	$4
2	app	chocolate-masher	$9
3	app	grapefruit-squeezer	$11
4	environment	dev	$5
5	environment	prod	$15
		TOTAL	$44

Note that the total sum is greater than your bill.