BigQuery AutoScaling Usage SQL - 편집 역사

2024년 11월 11일 (월) 08:07에 19ksh88님의 편집

2024-11-11T08:07:07Z

← 이전 판		2024년 11월 11일 (월) 17:07 판
73번째 줄:		73번째 줄:
	, TIMESTAMP_DIFF(end_change_timestamp_new,change_timestamp_new,SECOND) as diff		, TIMESTAMP_DIFF(end_change_timestamp_new,change_timestamp_new,SECOND) as diff
	, ceil(		, ceil(
	(~~UNIX_MILLIS~~(cast(end_change_timestamp_new as timestamp)) - ~~UNIX_MILLIS~~(cast(change_timestamp_new as timestamp))) / 1000		(UNIX_MICROS(cast(end_change_timestamp_new as timestamp)) - UNIX_MICROS(cast(change_timestamp_new as timestamp))) / 1000 / 1000
	) as diff_mill		) 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,millisecond) as diff_mill
101번째 줄:		101번째 줄:
	, sum(slot_capacitydiff)MAX(PAYG_PRICING) BASE_PRICING		, sum(slot_capacitydiff)MAX(PAYG_PRICING) BASE_PRICING
	, sum(current_slotsdiff)MAX(PAYG_PRICING) AUTOSCALING_PRICING		, sum(current_slotsdiff)MAX(PAYG_PRICING) AUTOSCALING_PRICING
	, sum(current_slots*diff) ~~-- TIME 기준~~		, sum(current_slots*diff)
	, sum(current_slots*diff_mill) ~~-- UNIX_MILLIS 기준~~		, sum(current_slots*diff_mill)
	FROM tb_main		FROM tb_main
	GROUP BY project_id, edition, reservation_name, change_date		GROUP BY project_id, edition, reservation_name, change_date
	</pre>		</pre>

2024년 11월 11일 (월) 06:27에 19ksh88님의 편집

2024-11-11T06:27:16Z

차이 보기

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

2024-11-11T06:26:36Z

GCP 문서 참고사항 업데이트

← 이전 판		2024년 11월 11일 (월) 15:26 판
1번째 줄:		1번째 줄:
	* BigQuery Capacity 사용 시 AutoScaling 사용량에 대한 모니터링 SQL		* BigQuery Capacity 사용 시 AutoScaling 사용량에 대한 모니터링 SQL
	* GCP Billing 기준 TimeZone인 PST(태평양 표준시) 기준으로 TimeZone을 사용		* GCP Billing 기준 TimeZone인 PST(태평양 표준시) 기준으로 TimeZone을 사용
			* https://cloud.google.com/bigquery/docs/slots-autoscaling-intro#monitor_autoscaling_with_information_schema
	<pre>		<pre>
	with DATE_BUCKET as (		with DATE_BUCKET as (

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

2024-11-11T06:25:56Z

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

← 이전 판		2024년 11월 11일 (월) 15:25 판
4번째 줄:		4번째 줄:
	with DATE_BUCKET as (		with DATE_BUCKET as (
	select cal_date		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		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 (		, RESERVATION_CHANGES as (
22번째 줄:		22번째 줄:
	user_email,		user_email,
	edition		edition
	FROM `region-US`.INFORMATION_SCHEMA.RESERVATION_CHANGES a		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		where 1=1
	and action NOT IN ('DELETE')		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)
	)		)
	, ~~DATE_BUCKET_RESERVATION_CHANGES~~ as (		, base2 as (
	select cal_date		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)		, case when cal_date > change_date then CAST(parse_timestamp('%Y-%m-%d %H:%M:%S', cal_date \|\| ' 00:00:00') AS datetime)
35번째 줄:		37번째 줄:
	else end_change_timestamp_org		else end_change_timestamp_org
	end as end_change_timestamp_new		end as end_change_timestamp_new
	, R.project_id		, base.project_id
	, R.reservation_name		, base.reservation_name
	, R.ignore_idle_slots		, base.ignore_idle_slots
	, R.action		, base.action
	, R.slot_capacity		, base.slot_capacity
	, R.current_slots		, base.current_slots
	, R.max_slots		, base.max_slots
	, R.user_email		, base.user_email
	, R.edition		, base.edition
	from DATE_BUCKET T		from DATE_BUCKET
	join RESERVATION_CHANGES R		join RESERVATION_CHANGES
	on R.change_date <> R.end_change_date		on base.change_date <> base.end_change_date
	and cal_date >= R.change_date		and cal_date >= base.change_date
	and cal_date <= R.end_change_date		and cal_date <= base.end_change_date

	union all		union all

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

	)		)
	select change_timestamp_new		select change_timestamp_new
	, end_change_timestamp_new		, end_change_timestamp_new
	, TIMESTAMP_DIFF(end_change_timestamp_new,change_timestamp_new,SECOND) as diff		, TIMESTAMP_DIFF(end_change_timestamp_new,change_timestamp_new,SECOND) as diff
	, project_id		, project_id
	, reservation_name		, reservation_name
	, ignore_idle_slots		, ignore_idle_slots
	, action		, action
	, slot_capacity		, slot_capacity
	, current_slots		, current_slots
	, max_slots		, max_slots
	, user_email		, user_email
	, edition		, edition
	from ~~DATE_BUCKET_RESERVATION_CHANGES~~		from base2
	where 1=1		where 1=1
	order by cal_date desc, change_timestamp_new desc		order by cal_date desc, change_timestamp_new desc
	</pre>		</pre>

Kokoksh: 새 문서: * 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_ti...

2024-10-22T08:34:48Z

새 문서: * 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','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_ti...

새 문서

* 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','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
</pre>