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',@DS_START_DATE) as date), CAST(parse_date('%Y%m%d',@DS_END_DATE) 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 `ns-bill-data.region-asia-northeast3`.INFORMATION_SCHEMA.RESERVATION_CHANGES a
  -- WHERE  change_timestamp >= CAST(parse_timestamp('%Y%m%d %H:%M:%S', @DS_START_DATE || ' 00:00:00', 'PST8PDT') AS TIMESTAMP)
  )
  where 1=1
  and action NOT IN ('DELETE')
  -- AND change_timestamp_org BETWEEN CAST(parse_timestamp('%Y%m%d %H:%M:%S', @DS_START_DATE || ' 00:00:00', 'PST8PDT') AS datetime)  AND  CAST(parse_timestamp('%Y%m%d %H:%M:%S', @DS_END_DATE || ' 23:59:59', 'PST8PDT') AS datetime)
)
, base2 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
        , base.project_id
        , base.reservation_name
        , base.ignore_idle_slots
        , base.action
        , base.slot_capacity
        , base.current_slots
        , base.max_slots
        , base.user_email
        , base.edition
  from DATE_BUCKET
    join RESERVATION_CHANGES
    on base.change_date <> base.end_change_date
    and cal_date >= base.change_date
    and cal_date <= base.end_change_date

  union all

  select cal_date
        , change_timestamp_org as change_timestamp_new
        , end_change_timestamp_org as end_change_timestamp_new
        , base.project_id
        , base.reservation_name
        , base.ignore_idle_slots
        , base.action
        , base.slot_capacity
        , base.current_slots
        , base.max_slots
        , base.user_email
        , base.edition
  from DATE_BUCKET
    join RESERVATION_CHANGES
    on base.change_date = base.end_change_date
    and cal_date = base.change_date
  -- order by cal_date desc, change_timestamp_new desc

)
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 base2
where 1=1
order by cal_date desc, change_timestamp_new desc