BigQuery Editionsの管理プロジェクトの費用を各プロジェクト毎に分割する

BigQuery Editionsを利用する時には、Reservationを作成するための専用のプロジェクト(管理プロジェクト)を作成することが推奨されています。このような構成にすることで複数のプロジェクトで1つのReservationを共有したり、あるReservationで余ったスロットを別のReservationにお裾分けすることができ、コスパ良くBigQueryを利用できます。

しかし、この構成を取ると請求コンソールや請求情報のBigQueryエクスポート的には管理プロジェクトが全ての費用を使っているように見えてしまいます。BigQueryのコスト管理をする際には各プロジェクトの費用が分かっている方がやりやすいため、管理プロジェクトの費用を各プロジェクトに分割する方法を紹介します。

まずは、以下のクエリでそれぞれのReservationに所属するプロジェクト毎のSlot利用割合を求めます。
GCPの費用はPST(米国西海岸時間)で月次の締め処理が行われるため、タイムゾーンをPSTにしてあります。

with consumed_slot_hour_by_reservation_project_date as (
  select
    reservation_id,
    project_id,
    date(creation_time, 'America/Los_Angeles') as date_pst,
    ifnull(sum(total_slot_ms), 0) / 10000 / 60 / 60 as slot_hour,
  from `<INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION>`
  where reservation_id is not null and reservation_id <> 'default-pipeline' and
  job_type = 'QUERY' and 
  statement_type != 'SCRIPT' and
  creation_time > current_timestamp - interval 30 day
  group by all
), reservation_consumption_rate_by_project_date as (
  select
    * except(slot_hour),
    slot_hour / sum(slot_hour) over (partition by reservation_id, date_pst) as reservation_consumption_rate,
  from consumed_slot_hour_by_reservation_project_date
  order by reservation_id, date_pst, project_id
)

次に、このクエリで各Reservationに割り当てられたSlot時間の積算値を日毎に求めます。

with assigned_slot_by_reservation_date as (
  select
    reservation_id,
    date(period_start, 'America/Los_Angeles') as date_pst,
    sum(slots_assigned + autoscale.current_slots) / 60 as slot_hour,
    edition,
  from `<INFORMATION_SCHEMA.RESERVATIONS_TIMELINE>`
  where period_start > current_timestamp - interval 30 day
  group by all
)

最後に上記の2つの情報を組み合わせることで、Edition毎・プロジェクト毎にどのプロジェクトが何割の費用を発生させているのかを求めます。

with assigned_slot_by_project_date as (
  select
    reservation_id,
    edition,
    date_pst,
    project_id,
    slot_hour * reservation_consumption_rate as slot_hour,
  from reservation_consumption_rate_by_project_date
  left join assigned_slot_by_reservation_date
  using(reservation_id, date_pst)
), cost_consumption_rate_by_project as (
  select
    date_pst,
    edition,
    project_id,
    slot_hour / sum(slot_hour) over (partition by date_pst, edition) as cost_consumption_rate,
  from (
    select
      date_pst,
      edition,
      project_id,
      sum(slot_hour) as slot_hour
    from assigned_slot_by_project_date
    group by all
  )
)

select * from cost_consumption_rate_by_project

このクエリで出力された cost_consumption_rate をそれぞれの日のそれぞれのエディションの費用に対して掛けると、管理プロジェクトの費用を各プロジェクトに分割できます。