如何从BigQuery的GCP账单明细导出中获取指定标签及类型的资源列表

现有按环境分组的资源成本统计SQL #

#standardSQL
SELECT  
  (SELECT value from UNNEST(project.labels) where key = "environment") env,
  ROUND(SUM(cost), 2) as charges,
  ROUND(SUM((SELECT SUM(amount) FROM UNNEST(credits))),2) as credits 
FROM
  myproject.mygcp_billing_export_dataset
GROUP BY env

1. 获取带有environment标签的全部资源列表 #

通过提取账单中的资源标识与标签信息，去重后得到唯一资源列表：

#standardSQL
SELECT DISTINCT
  resource.id AS resource_id,
  resource.name AS resource_name,
  (SELECT value FROM UNNEST(project.labels) WHERE key = "environment") AS environment
FROM
  myproject.mygcp_billing_export_dataset
WHERE
  EXISTS (SELECT 1 FROM UNNEST(project.labels) WHERE key = "environment")

• 使用DISTINCT避免同资源因多条账单记录重复出现
• EXISTS子句确保只返回带有environment标签的资源

2. 获取带有environment标签且属于Compute资源类型的资源列表 #

在上述查询基础上，添加资源类型筛选条件（通过service.id精准匹配Compute服务）：

#standardSQL
SELECT DISTINCT
  resource.id AS resource_id,
  resource.name AS resource_name,
  (SELECT value FROM UNNEST(project.labels) WHERE key = "environment") AS environment
FROM
  myproject.mygcp_billing_export_dataset
WHERE
  EXISTS (SELECT 1 FROM UNNEST(project.labels) WHERE key = "environment")
  AND service.id = 'compute.googleapis.com'

• service.id = 'compute.googleapis.com'精准定位Compute Engine资源，也可根据需求替换为其他服务ID（如storage.googleapis.com对应云存储）
• 若需按服务名称筛选，可改用service.description = 'Compute Engine'，但服务ID更稳定不易随名称变更

内容的提问来源于stack exchange，提问作者user2736158