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

BigQuery AutoScaling Usage SQL

2024-10-22T08:34:48Z

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

BigQuery-LookerSutdio Audit Log SQL

2024-10-22T08:19:52Z

Kokoksh: 설명 수정

* LookerStudio Pro 사용시 Audit Log To BigQuery Export 에서 Workspace 목록 추출

<pre>
SELECT
FORMAT_TIMESTAMP(('%Y-%m-%d %H:%M:%S'), TIMESTAMP_MICROS(log.time_usec), 'Asia/Seoul') AS inq_dttm,
gwslog.event_name,
gwslog.email,
gwslog.event_type,
gwslog.data_studio.asset_id,
gwslog.data_studio.asset_name,
gwslog.data_studio.parent_workspace_id,
rs.asset_name latest_asset_name,
gwslog.data_studio.owner_email,
gwslog.data_studio.visibility,
rs.owner_email latest_owner_email,
rs.visibility latest_visibility,
rs.parent_workspace_id latest_parent_workspace_id
FROM
`[Project_ID].[DataSet].activity` gwslog
LEFT JOIN ( -- 가장 최근에 변경된 보고서명을 가져오기 위한 서브쿼리 조인
SELECT
data_studio.asset_id,
data_studio.asset_name,
data_studio.visibility,
data_studio.owner_email,
data_studio.parent_workspace_id,
ROW_NUMBER() OVER (PARTITION BY data_studio.asset_id ORDER BY time_usec DESC) AS rn
FROM
`[Project_ID].[DataSet].activity`
WHERE
record_type ='data_studio'
AND data_studio.asset_type='WORKSPACE'
QUALIFY
rn=1 ) rs
ON
gwslog.data_studio.asset_id = rs.asset_id
WHERE
gwslog.record_type = 'data_studio'
AND event_name='CREATE' -- 생성보고서 기준으로 필터링 조건
AND data_studio.asset_type='WORKSPACE' -- LookerStudio Pro에서 Workspace 생성 목록 필터링 조건
AND NOT EXISTS ( -- 현재 삭제된 Workspace는 제외
SELECT
x.data_studio.asset_id
FROM
`[Project_ID].[DataSet].activity` x
WHERE
1=1
AND event_name='TRASH' -- LookerStudio Pro에서 삭제된 리스트 필터링 조건
AND x.data_studio.asset_id = gwslog.data_studio.asset_id
AND data_studio.asset_type='WORKSPACE'
AND x.data_studio.asset_id = gwslog.data_studio.asset_id)
</pre>

BigQuery-LookerSutdio Audit Log SQL

2024-10-22T08:17:27Z

Kokoksh: 새 문서: * LookerStudio Audit Log를 위해 BigQuery Export 시 Workspace 목록 추출 <pre> SELECT FORMAT_TIMESTAMP(('%Y-%m-%d %H:%M:%S'), TIMESTAMP_MICROS(log.time_usec), 'Asia/Seoul') AS inq_dttm, gwslog.event_name, gwslog.email, gwslog.event_type, gwslog.data_studio.asset_id, gwslog.data_studio.asset_name, gwslog.data_studio.parent_workspace_id, rs.asset_name latest_asset_name, gwslog.data_studio.owner_email, gwslog.data_studio.visibility, rs.owner_email lates...

* LookerStudio Audit Log를 위해 BigQuery Export 시 Workspace 목록 추출

<pre>
SELECT
FORMAT_TIMESTAMP(('%Y-%m-%d %H:%M:%S'), TIMESTAMP_MICROS(log.time_usec), 'Asia/Seoul') AS inq_dttm,
gwslog.event_name,
gwslog.email,
gwslog.event_type,
gwslog.data_studio.asset_id,
gwslog.data_studio.asset_name,
gwslog.data_studio.parent_workspace_id,
rs.asset_name latest_asset_name,
gwslog.data_studio.owner_email,
gwslog.data_studio.visibility,
rs.owner_email latest_owner_email,
rs.visibility latest_visibility,
rs.parent_workspace_id latest_parent_workspace_id
FROM
`[Project_ID].[DataSet].activity` gwslog
LEFT JOIN ( -- 가장 최근에 변경된 보고서명을 가져오기 위한 서브쿼리 조인
SELECT
data_studio.asset_id,
data_studio.asset_name,
data_studio.visibility,
data_studio.owner_email,
data_studio.parent_workspace_id,
ROW_NUMBER() OVER (PARTITION BY data_studio.asset_id ORDER BY time_usec DESC) AS rn
FROM
`[Project_ID].[DataSet].activity`
WHERE
record_type ='data_studio'
AND data_studio.asset_type='WORKSPACE'
QUALIFY
rn=1 ) rs
ON
gwslog.data_studio.asset_id = rs.asset_id
WHERE
gwslog.record_type = 'data_studio'
AND event_name='CREATE' -- 생성보고서 기준으로 필터링 조건
AND data_studio.asset_type='WORKSPACE' -- LookerStudio Pro에서 Workspace 생성 목록 필터링 조건
AND NOT EXISTS ( -- 현재 삭제된 Workspace는 제외
SELECT
x.data_studio.asset_id
FROM
`[Project_ID].[DataSet].activity` x
WHERE
1=1
AND event_name='TRASH' -- LookerStudio Pro에서 삭제된 리스트 필터링 조건
AND x.data_studio.asset_id = gwslog.data_studio.asset_id
AND data_studio.asset_type='WORKSPACE'
AND x.data_studio.asset_id = gwslog.data_studio.asset_id)
</pre>