데브카페 - 사용자 기여 [ko]

BigQuery AutoScaling Usage SQL

2024-11-11T08:07:07Z

19ksh88:

* BigQuery Capacity 사용 시 AutoScaling 사용량에 대한 모니터링 SQL
* GCP Billing 기준 TimeZone인 PST(태평양 표준시) 기준으로 TimeZone을 사용
* https://cloud.google.com/bigquery/docs/slots-autoscaling-intro#monitor_autoscaling_with_information_schema
<pre>
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_MICROS(cast(end_change_timestamp_new as timestamp)) - UNIX_MICROS(cast(change_timestamp_new as timestamp))) / 1000 / 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)
, sum(current_slots*diff_mill)
FROM tb_main
GROUP BY project_id, edition, reservation_name, change_date
</pre>

BigQuery AutoScaling Usage SQL

2024-11-11T06:27:16Z

19ksh88:

* BigQuery Capacity 사용 시 AutoScaling 사용량에 대한 모니터링 SQL
* GCP Billing 기준 TimeZone인 PST(태평양 표준시) 기준으로 TimeZone을 사용
* https://cloud.google.com/bigquery/docs/slots-autoscaling-intro#monitor_autoscaling_with_information_schema
<pre>
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
</pre>

BigQuery AutoScaling Usage SQL

2024-11-11T06:26:36Z

19ksh88: GCP 문서 참고사항 업데이트

* BigQuery Capacity 사용 시 AutoScaling 사용량에 대한 모니터링 SQL
* GCP Billing 기준 TimeZone인 PST(태평양 표준시) 기준으로 TimeZone을 사용
* https://cloud.google.com/bigquery/docs/slots-autoscaling-intro#monitor_autoscaling_with_information_schema
<pre>
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
</pre>

BigQuery AutoScaling Usage SQL

2024-11-11T06:25:56Z

19ksh88: Slot 사용량을 timestamp 기준에서 UNIX 기준으로 변경하여 계산

* BigQuery Capacity 사용 시 AutoScaling 사용량에 대한 모니터링 SQL
* GCP Billing 기준 TimeZone인 PST(태평양 표준시) 기준으로 TimeZone을 사용
<pre>
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
</pre>