Cloud Billing データのエクスポート クエリの例

SELECT
  invoice.month,
  SUM(cost)
    + SUM(IFNULL((SELECT SUM(c.amount)
                  FROM UNNEST(credits) c), 0))
    AS total,
  (SUM(CAST(cost AS NUMERIC))
    + SUM(IFNULL((SELECT SUM(CAST(c.amount AS NUMERIC))
                  FROM UNNEST(credits) AS c), 0)))
    AS total_exact
FROM `project-ID.dataset.gcp_billing_export_v1_XXXXXX-XXXXXX-XXXXXX`
GROUP BY 1
ORDER BY 1 ASC
;

SELECT
  invoice.month,
  cost_type,
  SUM(cost)
    + SUM(IFNULL((SELECT SUM(c.amount)
                  FROM   UNNEST(credits) c), 0))
    AS total,
  (SUM(CAST(cost AS NUMERIC))
    + SUM(IFNULL((SELECT SUM(CAST(c.amount AS NUMERIC))
                  FROM UNNEST(credits) AS c), 0)))
    AS total_exact
FROM `project-ID.dataset.gcp_billing_export_v1_XXXXXX-XXXXXX-XXXXXX`
GROUP BY 1, 2
ORDER BY 1 ASC, 2 ASC
;

SELECT
  sku.description,
  TO_JSON_STRING(labels) as labels,
 cost as cost
FROM `project-ID.dataset.gcp_billing_export_v1_XXXXXX-XXXXXX-XXXXXX`;

TO_JSON_STRING not supported.

SELECT
  TO_JSON_STRING(labels) as labels,
  sum(cost) as cost
FROM `project-ID.dataset.gcp_billing_export_v1_XXXXXX-XXXXXX-XXXXXX`
GROUP BY labels;

TO_JSON_STRING not supported.

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

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

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

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

SELECT
    invoice.month AS invoice_month,
    SUM(cost) as commitment_fees
FROM `project-ID.dataset.gcp_billing_export_v1_XXXXXX-XXXXXX-XXXXXX`
WHERE LOWER(sku.description) LIKE "commitment%"
GROUP BY 1

SELECT
    invoice.month AS invoice_month,
    SUM(credits.amount) as CUD_credits
FROM `project-ID.dataset.gcp_billing_export_v1_XXXXXX-XXXXXX-XXXXXX`
LEFT JOIN UNNEST(credits) AS credits
WHERE credits.type = "COMMITTED_USAGE_DISCOUNT"
GROUP BY 1

SELECT
    invoice.month AS invoice_month,
    TO_JSON_STRING(project.ancestors) as ancestors,
    SUM(cost)
        + SUM(IFNULL((SELECT SUM(c.amount) FROM UNNEST(credits) c), 0))
        AS net_cost
  FROM `project-ID.dataset.gcp_billing_export_v1_XXXXXX-XXXXXX-XXXXXX` as bq
  WHERE TO_JSON_STRING(project.ancestors) like "%resource_name\":\"folders/1234"
  GROUP BY invoice_month, ancestors
  ORDER BY invoice_month, ancestors

SELECT
    invoice.month AS invoice_month,
    TO_JSON_STRING(project.ancestors) as ancestors,
    SUM(cost)
        + SUM(IFNULL((SELECT SUM(c.amount) FROM UNNEST(credits) c), 0))
        AS net_cost
  FROM `project-ID.dataset.gcp_billing_export_v1_XXXXXX-XXXXXX-XXXXXX` as bq, UNNEST(project.ancestors) as ancestor
  WHERE ancestor.resource_name = "folders/1234"
  GROUP BY invoice_month, ancestors
  ORDER BY invoice_month, ancestors

SELECT
    invoice.month AS invoice_month,
    TO_JSON_STRING(project.ancestors) as ancestors,
    SUM(cost)
        + SUM(IFNULL((SELECT SUM(c.amount) FROM UNNEST(credits) c), 0))
        AS net_cost
  FROM `project-ID.dataset.gcp_billing_export_v1_XXXXXX-XXXXXX-XXXXXX` as bq
  WHERE TO_JSON_STRING(project.ancestors) like "%display_name\":\"MyFolderName%"
  GROUP BY invoice_month, ancestors
  ORDER BY invoice_month, ancestors

SELECT
    invoice.month AS invoice_month,
    TO_JSON_STRING(project.ancestors) as ancestors,
    SUM(cost)
        + SUM(IFNULL((SELECT SUM(c.amount) FROM UNNEST(credits) c), 0))
        AS net_cost
  FROM `project-ID.dataset.gcp_billing_export_v1_XXXXXX-XXXXXX-XXXXXX` as bq, UNNEST(project.ancestors) as ancestor
  WHERE ancestor.display_name = "MyFolderName"
  GROUP BY invoice_month, ancestors
  ORDER BY invoice_month, ancestors

SELECT
  invoice.month AS invoice_month,
  tag.value AS cost_center,
  ROUND((SUM(CAST(cost AS NUMERIC))
    + SUM(IFNULL((SELECT SUM (CAST(c.amount AS NUMERIC))
                  FROM UNNEST(credits) AS c), 0))), 2)
    AS net_cost
FROM `project-ID.dataset.gcp_billing_export_resource_v1_XXXXXX-XXXXXX-XXXXXX`, UNNEST(tags) AS tag
WHERE tag.key = "cost_center" AND tag.namespace = "821092389413"
GROUP BY invoice.month, tag.value
ORDER BY invoice.month, tag.value;

SELECT
 invoice.month AS invoice_month,
 ROUND((SUM(CAST(cost AS NUMERIC))
   + SUM(IFNULL((SELECT SUM(CAST(c.amount AS NUMERIC))
                 FROM UNNEST(credits) AS c), 0))), 2)
   AS net_cost
FROM
 `project-ID.dataset.gcp_billing_export_v1_XXXXXX-XXXXXX-XXXXXX`
WHERE "color" NOT IN (SELECT key FROM UNNEST(tags))
GROUP BY invoice_month
ORDER BY invoice_month;

SELECT
  project.name,
  TO_JSON_STRING(project.labels) as project_labels,
  sum(cost) as total_cost,
  SUM(IFNULL((SELECT SUM(c.amount) FROM UNNEST(credits) c), 0)) as total_credits
FROM `project-ID.dataset.gcp_billing_export_v1_XXXXXX-XXXXXX-XXXXXX`
WHERE invoice.month = "202006"
GROUP BY 1, 2
ORDER BY 1;

SELECT
  SUM(cost)
    + SUM(IFNULL((SELECT SUM(c.amount)
      FROM   UNNEST(credits) c), 0))
    AS total
FROM `project-ID.dataset.gcp_billing_export_v1_XXXXXX-XXXXXX-XXXXXX`
WHERE
  invoice.month = '202403' AND
    DATE(TIMESTAMP_TRUNC(usage_start_time, Day, 'US/Pacific')) < '2024-03-01';

SELECT
  DATE(TIMESTAMP_TRUNC(usage_start_time, Day, 'US/Pacific')) AS `Day`,
  service.description AS `Service Description`,
  SUM(CAST(cost_at_list AS NUMERIC)) AS `List cost`,
  SUM(CAST(cost AS NUMERIC)) - SUM(CAST(cost_at_list AS NUMERIC)) AS `Negotiated savings`,
  SUM(IFNULL((SELECT SUM(CAST(c.amount AS numeric)) FROM UNNEST(credits) c WHERE c.type IN ('SUSTAINED_USAGE_DISCOUNT', 'DISCOUNT', 'SPENDING_BASED_DISCOUNT', 'COMMITTED_USAGE_DISCOUNT', 'FREE_TIER', 'COMMITTED_USAGE_DISCOUNT_DOLLAR_BASE', 'SUBSCRIPTION_BENEFIT', 'RESELLER_MARGIN')), 0)) AS `Discounts`,
  SUM(IFNULL((SELECT SUM(CAST(c.amount AS numeric)) FROM UNNEST(credits) c WHERE c.type IN ('CREDIT_TYPE_UNSPECIFIED', 'PROMOTION')), 0)) AS `Promotions and others`,
  SUM(CAST(cost_at_list AS NUMERIC)) + SUM(IFNULL((SELECT SUM(CAST(c.amount AS numeric)) FROM UNNEST(credits) c WHERE c.type IN ('SUSTAINED_USAGE_DISCOUNT', 'DISCOUNT', 'SPENDING_BASED_DISCOUNT', 'COMMITTED_USAGE_DISCOUNT', 'FREE_TIER', 'COMMITTED_USAGE_DISCOUNT_DOLLAR_BASE', 'SUBSCRIPTION_BENEFIT', 'RESELLER_MARGIN')), 0)) + SUM(CAST(cost AS NUMERIC)) - SUM(CAST(cost_at_list AS NUMERIC))+ SUM(IFNULL((SELECT SUM(CAST(c.amount AS numeric)) FROM UNNEST(credits) c WHERE c.type IN ('CREDIT_TYPE_UNSPECIFIED', 'PROMOTION')), 0)) AS `Subtotal`
FROM
  `project-ID.dataset.gcp_billing_export_v1_XXXXXX-XXXXXX-XXXXXX`
WHERE
  invoice.month = '202403' AND
  DATE(TIMESTAMP_TRUNC(usage_start_time, Day, 'US/Pacific')) < '2024-03-01'
GROUP BY
  Day,
  service.description
ORDER BY
  Day DESC,
  Subtotal DESC;

SELECT
  invoice.month,
  resource.name,
  SUM(cost)
    + SUM(IFNULL((SELECT SUM(c.amount)
                  FROM UNNEST(credits) c), 0))
    AS total,
  (SUM(CAST(cost AS NUMERIC))
    + SUM(IFNULL((SELECT SUM(CAST(c.amount AS NUMERIC))
                  FROM UNNEST(credits) AS c), 0)))
    AS total_exact
FROM `project-ID.dataset.gcp_billing_export_resource_v1_XXXXXX-XXXXXX-XXXXXX`
GROUP BY 1, 2
ORDER BY 1 ASC, 2 ASC
;

SELECT
  invoice.month,
  cost_type,
  resource.name,
  SUM(cost)
    + SUM(IFNULL((SELECT SUM(c.amount)
                  FROM   UNNEST(credits) c), 0))
    AS total,
  (SUM(CAST(cost AS NUMERIC))
    + SUM(IFNULL((SELECT SUM(CAST(c.amount AS NUMERIC))
                  FROM UNNEST(credits) AS c), 0)))
    AS total_exact
FROM `project-ID.dataset.gcp_billing_export_resource_v1_XXXXXX-XXXXXX-XXXXXX`
GROUP BY 1, 2, 3
ORDER BY 1 ASC, 2 ASC, 3 ASC
;

SELECT
  SUM(cost) AS cost_before_credits,
  labels.value AS cluster_name
FROM  `project-ID.dataset.gcp_billing_export_resource_v1_XXXXXX-XXXXXX-XXXXXX`
LEFT JOIN UNNEST(labels) as labels
  ON labels.key = "goog-k8s-cluster-name"
GROUP BY labels.value
;

SELECT
  labels.value as namespace,
  SUM(cost) + SUM(IFNULL((SELECT SUM(c.amount) FROM UNNEST(credits) c), 0)) AS cost_after_credits,
FROM `project-ID.dataset.gcp_billing_export_resource_v1_XXXXXX-XXXXXX-XXXXXX`
LEFT JOIN UNNEST(labels) as labels
  ON labels.key = "k8s-namespace"
GROUP BY namespace
;

SELECT
  project.id AS project_id,
  labels.value AS cluster_name,
  sku.id AS sku_id,
  sku.description AS sku_description,
  SUM(cost) AS cost
FROM `project-ID.dataset.gcp_billing_export_resource_v1_XXXXXX-XXXXXX-XXXXXX`
JOIN UNNEST(labels) AS labels
  ON labels.key = "goog-k8s-cluster-name"
GROUP BY
  cluster_name, project_id, sku_description, sku_id
;

SELECT sku.id,  sku.description, list_price.*
FROM `project-ID.dataset.cloud_pricing_export`
WHERE DATE(_PARTITIONTIME) = "2020-07-20"
      AND sku.id = "2DA5-55D3-E679"
;

SELECT sku.id,  sku.description, list_price.*
FROM `project-ID.dataset.cloud_pricing_export`
WHERE DATE(pricing_as_of_time) = "2020-07-20"
      AND sku.id = "2DA5-55D3-E679"
;

SELECT sku.id AS sku_id,
       sku.description AS sku_description,
       service.id AS service_id,
       service.description as service_description,
       list_price.*
FROM project-ID.dataset.cloud_pricing_export
WHERE DATE(_PARTITIONTIME) = "2020-07-20"
      AND sku.id = "2DA5-55D3-E679"
;

SELECT sku.id AS sku_id,
       sku.description AS sku_description,
       service.id AS service_id,
       service.description as service_description,
       tier.*
FROM `project-ID.dataset.cloud_pricing_export` as sku_pricing, UNNEST (sku_pricing.list_price.tiered_rates) as tier
WHERE DATE(_PARTITIONTIME) = "2020-07-20"
      AND sku.id = "2DA5-55D3-E679"
;

SELECT sku.id AS sku_id,
       sku.description AS sku_description,
       service.id AS service_id,
       service.description as service_description,
       product_taxonomy
FROM `project-ID.dataset.cloud_pricing_export`
WHERE DATE(_PARTITIONTIME) = "2020-07-20"
      AND sku.id = "2DA5-55D3-E679"
;

SELECT sku.id AS sku_id,
       sku.description AS sku_description,
       service.id AS service_id,
       service.description as service_description,
       product_taxonomy
FROM `project-ID.dataset.cloud_pricing_export`
WHERE DATE(_PARTITIONTIME) = "2020-07-20"
     AND "Serverless" in UNNEST(product_taxonomy)
LIMIT 10
;

SELECT sku.id AS sku_id,
       sku.description AS sku_description,
       service.id AS service_id,
       service.description as service_description,
       geo_taxonomy,
       product_taxonomy
FROM `project-ID.dataset.cloud_pricing_export`
WHERE DATE(_PARTITIONTIME) = "2020-07-20"
      AND "VMs On Demand" in UNNEST(product_taxonomy)
      AND geo_taxonomy.type = "REGIONAL"
      AND "us-east4" in UNNEST (geo_taxonomy.regions)
;

WITH
  Exports AS (
    SELECT *
    FROM `project-ID.dataset.gcp_billing_export_resource_v1_XXXXXX-XXXXXX-XXXXXX`
  ),
  Prices AS (
    SELECT *
    FROM `project-ID.dataset.cloud_pricing_export`
  )
SELECT
  Exports.sku.description AS sku_description,
  Exports.cost,
  Exports.usage,
  FlattenedPrices.pricing_unit_description,
  FlattenedPrices.account_currency_amount,
  FlattenedPrices.account_currency_code,
FROM Exports
JOIN (SELECT * FROM Prices CROSS JOIN UNNEST(Prices.list_price.tiered_rates)) AS FlattenedPrices
  ON
    Exports.sku.id = FlattenedPrices.sku.id
    AND Exports.price.tier_start_amount = FlattenedPrices.start_usage_amount
WHERE
  DATE(Exports.export_time) = '2023-06-30'
  AND DATE(FlattenedPrices.export_time) = '2023-06-30'
  AND cost > 0
LIMIT 1000