Billing Export to BigQuery Query Examples

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

Return the total costs on an invoice

The following queries demonstrate two ways of viewing cost and credit values using exported billing data.

  • The total field directly sums the floating point cost and credit values, which can result in floating point rounding errors.
  • The total_exact field converts costs and credit values to micros before summing, then converts back to dollars after summing, avoiding the floating point rounding error.

Example 1: Sum of all costs, per invoice month

This query shows the invoice total for each month, as a sum of regular costs, taxes, adjustments, and rounding errors.

Standard SQL

SELECT
  invoice.month,
  SUM(cost)
    + SUM(IFNULL((SELECT SUM(c.amount)
                  FROM UNNEST(credits) c), 0))
    AS total,
  (SUM(CAST(cost * 1000000 AS int64))
    + SUM(IFNULL((SELECT SUM(CAST(c.amount * 1000000 as int64))
                  FROM UNNEST(credits) c), 0))) / 1000000
    AS total_exact
FROM `project.dataset.gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX`
GROUP BY 1
ORDER BY 1 ASC
;

For example, the result of the above query might be:

Row month total total_exact
1 201901 $1005.004832999999984 $1005.00
2 201902 $992.3101739999999717 $992.31
3 201903 $1220.761089999999642 $1220.76

Example 2: Return details by cost type, per invoice month

This query shows the totals for each cost_type for each month. Cost types include regular costs, taxes, adjustments, and rounding errors.

Standard SQL

SELECT
  invoice.month,
  cost_type,
  SUM(cost)
    + SUM(IFNULL((SELECT SUM(c.amount)
                  FROM   UNNEST(credits) c), 0))
    AS total,
  (SUM(CAST(cost * 1000000 AS int64))
    + SUM(IFNULL((SELECT SUM(CAST(c.amount * 1000000 as int64))
                  FROM UNNEST(credits) c), 0))) / 1000000
    AS total_exact
FROM `project.dataset.gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX`
GROUP BY 1, 2
ORDER BY 1 ASC, 2 ASC
;

For example, the result of the above query might be:

Row month cost_type total total_exact
1 201901 regular $1000.501209987994782 $1000.50
2 201901 rounding_error –$0.500489920049387 –$0.50
3 201901 tax $10.000329958477891 $10.00
4 201901 adjustment –$5.002572999387045 –$5.00

Additional query examples

The following examples illustrate other ways to query your data.

For the examples in this section, 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

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.

Oliko tästä sivusta apua? Kerro mielipiteesi

Palautteen aihe:

Tämä sivu
Cloud Billing Documentation