with all_table_names as (
  select table_schema, table_name from region-us.INFORMATION_SCHEMA.TABLES where table_type <> 'VIEW' group by 1,2
),
calender as (
  SELECT * FROM UNNEST(GENERATE_DATE_ARRAY('2021-01-01', DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY), INTERVAL 1 DAY)) AS day 
),
table_calender as (
  select day, table_schema, table_name from calender, all_table_names
),
daily_storage_timeline as (
  select
    cast(date_trunc(timestamp, day) as date) as day,
    table_schema,
    table_name,
    max(total_rows) total_rows, 
    max(total_partitions) total_partitions, 
    avg(active_logical_bytes) active_logical_bytes, 
    avg(long_term_logical_bytes) long_term_logical_bytes
  from
    region-us.INFORMATION_SCHEMA.TABLE_STORAGE_TIMELINE_BY_PROJECT
  group by 1,2,3
),
daily_storage_usage as (
  select 
    day, table_schema, table_name,
    coalesce(
      st.total_rows,
      last_value(st.total_rows ignore nulls) over (partition by table_schema, table_name order by day     ),
      last_value(st.total_rows ignore nulls) over (partition by table_schema, table_name order by day desc)
    ) as total_rows,
    coalesce(
      st.total_partitions,
      last_value(st.total_partitions ignore nulls) over (partition by table_schema, table_name order by day     ),
      last_value(st.total_partitions ignore nulls) over (partition by table_schema, table_name order by day desc)
    ) as total_partitions,
    coalesce(
      st.active_logical_bytes,
      last_value(st.active_logical_bytes ignore nulls) over (partition by table_schema, table_name order by day     ),
      last_value(st.active_logical_bytes ignore nulls) over (partition by table_schema, table_name order by day desc)
    ) as active_logical_bytes,
    coalesce(
      st.long_term_logical_bytes,
      last_value(st.long_term_logical_bytes ignore nulls) over (partition by table_schema, table_name order by day     ),
      last_value(st.long_term_logical_bytes ignore nulls) over (partition by table_schema, table_name order by day desc)
    ) as long_term_logical_bytes,
  from table_calender tc
  left join daily_storage_timeline st
    using(day, table_schema, table_name)
),
daily_storage_usage_billing as (
  select *, active_storage_billing_usd + long_term_billing_usd as total_billing_usd
  from (
    select 
      *,
      active_logical_bytes    / 1024 / 1024 / 1024 * 0.020 / 30 as active_storage_billing_usd,
      long_term_logical_bytes / 1024 / 1024 / 1024 * 0.020 / 30 as long_term_billing_usd
    from daily_storage_usage
  )  
)
select * from daily_storage_usage_billing
order by day desc, table_schema, table_name

/*
select 
  date_trunc(day, month) m, 
  --table_schema, 
  --table_name, 
  sum(active_storage_billing_usd) active_storage_billing_usd, 
  sum(long_term_billing_usd) long_term_billing_usd,
  sum(active_storage_billing_usd) + sum(long_term_billing_usd) total_billing_usd,
from daily_storage_usage_billing
group by 1 --,2,3
order by 1 --,2,3
*/

現存するテーブル一覧とカレンダー作って、日毎の使用量を埋めてから課金額を日毎に計算している。

<aside> ℹ️ 実行には **roles/bigquery.metadataViewer (**bigquery.tables.get, bigquery.tables.list) が必要

</aside>

テーブルのストレージ使用量（現在）

TABLE_STORAGE

https://cloud.google.com/bigquery/docs/information-schema-tables?hl=ja#table_storage_view

テーブル ストレージ使用量の推移

TABLE_STORAGE_TIMELINE_BY_* ビュー

https://cloud.google.com/bigquery/docs/information-schema-tables?hl=ja#table_storage_timeline_by_view