BigQuery AutoScaling Usage SQL: 두 판 사이의 차이

2024년 11월 11일 (월) 15:27 판

BigQuery Capacity 사용 시 AutoScaling 사용량에 대한 모니터링 SQL
GCP Billing 기준 TimeZone인 PST(태평양 표준시) 기준으로 TimeZone을 사용
https://cloud.google.com/bigquery/docs/slots-autoscaling-intro#monitor_autoscaling_with_information_schema
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','20241111') 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 (partition by a.reservation_name ORDER BY a.change_timestamp DESC),CURRENT_TIMESTAMP()), ('PST8PDT')) AS end_change_timestamp_org,
           date(datetime(IFNULL(LAG(a.change_timestamp) OVER (partition by a.reservation_name 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
)
, tb_main as (
select change_timestamp_new
    , end_change_timestamp_new
    , TIMESTAMP_DIFF(end_change_timestamp_new,change_timestamp_new,SECOND) as diff
    , ceil(
          (UNIX_MILLIS(cast(end_change_timestamp_new as timestamp)) - UNIX_MILLIS(cast(change_timestamp_new as timestamp))) / 1000
          ) as diff_mill
    -- , TIMESTAMP_DIFF(end_change_timestamp_new,change_timestamp_new,millisecond) as diff_mill
    -- , TIMESTAMP_DIFF(end_change_timestamp_new,change_timestamp_new,MICROSECOND) as diff_micro
    , project_id
    , reservation_name
    , ignore_idle_slots
    , action
    , slot_capacity
    , current_slots
    , max_slots
    , user_email
    , edition
    , CASE WHEN edition='STANDARD' THEN 0.04/60/60 
           WHEN edition='ENTERPRISE' THEN 0.06/60/60 END PAYG_PRICING
from DATE_BUCKET_RESERVATION_CHANGES
where 1=1
-- and edition = 'STANDARD'
order by cal_date desc, change_timestamp_new desc
)
SELECT project_id
     , edition
     , reservation_name
     , date(change_timestamp_new) as change_date
    --  , min(change_timestamp_new) change_timestamp_new
    --  , max(end_change_timestamp_new) end_change_timestamp_new
     , sum(slot_capacity*diff)*MAX(PAYG_PRICING) BASE_PRICING
     , sum(current_slots*diff)*MAX(PAYG_PRICING) AUTOSCALING_PRICING
     , sum(current_slots*diff) -- TIME 기준 
     , sum(current_slots*diff_mill) -- UNIX_MILLIS 기준
  FROM tb_main
  GROUP BY project_id, edition, reservation_name, change_date
2024년 11월 11일 (월) 15:27 판

Comments
