BigQuery AutoScaling Usage SQL

• BigQuery Capacity 사용 시 AutoScaling 사용량에 대한 모니터링 SQL
• GCP Billing 기준 TimeZone인 PST(태평양 표준시) 기준으로 TimeZone을 사용

with DATE_BUCKET as (
  select cal_date
  FROM UNNEST(GENERATE_DATE_ARRAY(CAST(parse_date('%Y%m%d','20240601') as date), CAST(parse_date('%Y%m%d','20241031') as date), INTERVAL 1 DAY)) as cal_date
)
, RESERVATION_CHANGES as (
  select *
  from (
    SELECT datetime(change_timestamp, ('PST8PDT')) change_timestamp_org,
           date(date_trunc(datetime(change_timestamp, ('PST8PDT')), DAY)) as change_date,
           datetime(IFNULL(LAG(a.change_timestamp) OVER (ORDER BY a.change_timestamp DESC),CURRENT_TIMESTAMP()), ('PST8PDT')) AS end_change_timestamp_org,
           date(datetime(IFNULL(LAG(a.change_timestamp) OVER (ORDER BY a.change_timestamp DESC),CURRENT_TIMESTAMP()), ('PST8PDT'))) AS end_change_date,
           project_id,
           reservation_name,
           ignore_idle_slots,
           action,
           slot_capacity,
           autoscale.current_slots,
           autoscale.max_slots,
           user_email,
           edition
    FROM `region-US`.INFORMATION_SCHEMA.RESERVATION_CHANGES a 
  )
  where 1=1
  and action NOT IN ('DELETE')
)
, DATE_BUCKET_RESERVATION_CHANGES as (
  select cal_date
        , case when cal_date > change_date then CAST(parse_timestamp('%Y-%m-%d %H:%M:%S', cal_date || ' 00:00:00') AS datetime)
               else change_timestamp_org
          end as change_timestamp_new
        , case when cal_date < end_change_date then CAST(parse_timestamp('%Y-%m-%d %H:%M:%S', date_add(cal_date, interval 1 day) || ' 00:00:00') AS datetime)
               else end_change_timestamp_org
          end as end_change_timestamp_new
        , R.project_id
        , R.reservation_name
        , R.ignore_idle_slots
        , R.action
        , R.slot_capacity
        , R.current_slots
        , R.max_slots
        , R.user_email
        , R.edition
  from DATE_BUCKET T
    join RESERVATION_CHANGES R
      on R.change_date <> R.end_change_date
     and cal_date >= R.change_date
     and cal_date <= R.end_change_date
  union all
  select cal_date
        , change_timestamp_org as change_timestamp_new
        , end_change_timestamp_org as end_change_timestamp_new
        , R.project_id
        , R.reservation_name
        , R.ignore_idle_slots
        , R.action
        , R.slot_capacity
        , R.current_slots
        , R.max_slots
        , R.user_email
        , R.edition
  from DATE_BUCKET T
    join RESERVATION_CHANGES R
      on R.change_date = R.end_change_date
     and cal_date = R.change_date
)
select change_timestamp_new
    , end_change_timestamp_new
    , TIMESTAMP_DIFF(end_change_timestamp_new,change_timestamp_new,SECOND) as diff
    , project_id
    , reservation_name
    , ignore_idle_slots
    , action
    , slot_capacity
    , current_slots
    , max_slots
    , user_email
   , edition
from DATE_BUCKET_RESERVATION_CHANGES
where 1=1
order by cal_date desc, change_timestamp_new desc