SNOWFLAKE 메타 데이터를 통해 사용 현황 모니터링 하기

INFORMATION Schema, Account Usage, Organization Usage

SNOWFLAKE 서비스를 통해서 생성된 오브젝트(Database, Table 등) 정보 및 사용 정보(로그인 정보, 쿼리 이력)들은 모두 SNOWFLAKE 내부 데이터베이스에 저장 되며, 향후 감사 및 비용 사용 모니터링, 느린 쿼리를 확인할 수 기능을 제공합니다.

SNOWFLAKE는 해당 데이터들을 모두 View 및 함수 형태로 제공하여, 사용자들이 원하는 데이터를 조회하고, 사용자 요구사항에 맞는 대시보드를 생성할 수 있도록 지원합니다.

이러한 SNOWFLAKE가 제공하는 메타 데이터는 크게 아래 3가지 형태로 구분됩니다.

• Account Usage : 사용자 계정의 사용 메트릭 정보(로그인, 데이터 로딩, 쿼리 이력)를 뷰와 관련된 함수를 통해서 제공함.
• Information Schema : SQL-92 ANSI Information Schema 에 맞는 형태로 뷰와 관련된 함수를 제공하며, SNOWFLAKE 특화된 내용(Stage, File Format 등)에 대한 정보 제공
• Organization Usage : SNOWFLAKE는 하나의 조직(Organization)에서 여러 계정을 생성하고 관리할 수 있는 기능을 제공합니다. Organization Usage 스키마는 하나의 장소에서 여러 계정에서 사용되는 비용을 측정할 수 있는 정보를 제공합니다.

위의 3가지 스키마를 통해서 사용자들은 원하는 대시보드를 구성할 수 있으며, 제공되는 뷰들은 종류에 따라서 데이터 보존 기간 및 해당 데이터를 확인할 수 있는 대기 시간이 서로 다릅니다.

일반적으로 과거 데이터 보존 기간은 7일, 6개월, 1년으로 구분되며, 데이터를 조회하기 위한 시간도 실시간, 45분, 3시간등 뷰 별로 다양한 정책을 가져가고 있습니다.

Account Usage와 Information Schema의 차이점 및 데이터 확인 대기 시간 및 보존 기간

Organization Schema 의 뷰별 데이터 확인 대기 시간 및 보존 기간

View 상세 정보 확인 페이지

각 스키마를 구성하는 뷰에 대한 상세 내용은 아래의 링크를 통해서 확인이 가능합니다.

Account Usage - Snowflake Documentation

Snowflake Information Schema - Snowflake Documentation

조직 사용 - Snowflake Documentation

Information Schema 관련 함수

Information Schema에 있는 뷰 데이터들은 SQL을 통해서 직접 조회도 가능하지만, 미리 정의되어 있는 함수를 호출하여 해당 데이터에 대한 조회도 가능합니다.

SELECT * FROM TABLE(INFORMATION_SCHEMA.LOGIN_HISTORY_BY_USER())
ORDER BY EVENT_TIMESTAMP;

함수를 통한 로그인 정보 조회

Snowflake Information Schema - Snowflake Documentation

SQL을 통한 주요 정보 모니터링 하기

SNOWFLAKE는 SNOWSIGHT Pre Built UI를 통해서 사용자들이 모니터링을 할 수 있는 다양한 정보를 제공하고 있으며, 해당 기능을 지속적으로 강화하고 있습니다. 하지만 이러한 화면이 모든 고객사의 요구사항을 만족할 수 없을 수 있으며, 이 때에는 SQL을 통해서 원하는 커스텀 대시보드를 작성할 수 있습니다.

SNOWSIGHT 를 통한 크레딧 사용 현황 모니터링

아래는 몇 가지 중요한 정보를 SQL 기반으로 조회할 수 있는 샘플입니다.

테이블 사이즈 TOP 100

select tsm.table_catalog as database_name
, tsm.table_schema as schema_name
, tsm.table_name
, tsm.is_transient
, t.table_owner
, t.table_type
, t.clustering_key
, t.row_count
, t.retention_time
, tsm.active_bytes / power(1024,3) as active_gb
, tsm.time_travel_bytes / power(1024,3) as time_travel_gb
, tsm.failsafe_bytes / power(1024,3) as failsafe_gb
, tsm.deleted
, tsm.table_created
, tsm.table_dropped
, tsm.table_entered_failsafe
from snowflake.account_usage.table_storage_metrics tsm
left join snowflake.account_usage.tables t on tsm.id = t.table_id
order by active_gb desc
Limit 100

Long running 쿼리(Last 30 days)

select query_id
, start_time
, end_time
, query_text
, query_type
, user_name
, warehouse_name
, warehouse_size
, cluster_number
, execution_status
, error_code
, error_message
, total_elapsed_time/1000/60 as total_elapsed_minutes
, compilation_time/1000/60 as compilation_time_minutes
, execution_time/1000/60 as execution_time_minutes
, queued_provisioning_time/1000/60 as queued_provisioning_minutes
, queued_repair_time/1000/60 as queued_repair_minutes
, queued_overload_time/1000/60 as queued_overload_minutes
, transaction_blocked_time/1000/60 transaction_blocked_minutes
, bytes_scanned
, percentage_scanned_from_cache
, rows_produced
, rows_inserted
, rows_updated
, rows_deleted
, rows_unloaded
, partitions_scanned
, partitions_total
, bytes_spilled_to_local_storage
, bytes_spilled_to_remote_storage
, bytes_sent_over_the_network
, credits_used_cloud_services
, release_version
from snowflake.account_usage.query_history
where start_time::date >= current_date — 30
order by total_elapsed_time desc

시간대별 쿼리 수행 건수(지난 7일)

SELECT DATE_TRUNC(‘HOUR’, START_TIME) AS QUERY_START_HOUR
,WAREHOUSE_NAME
,COUNT(*) AS NUM_QUERIES
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME >= DATEADD(DAY, -7, CURRENT_TIMESTAMP()) // Past 7 days
GROUP BY 1, 2
ORDER BY 1 DESC, 2

사용자별 크레딧 사용 현황

— THIS IS APPROXIMATE CREDIT CONSUMPTION BY USER
WITH USER_HOUR_EXECUTION_CTE AS (
SELECT USER_NAME
,WAREHOUSE_NAME
,DATE_TRUNC(‘hour’,START_TIME) as START_TIME_HOUR
,SUM(EXECUTION_TIME) as USER_HOUR_EXECUTION_TIME
FROM “SNOWFLAKE”.”ACCOUNT_USAGE”.”QUERY_HISTORY”
WHERE WAREHOUSE_NAME IS NOT NULL
AND EXECUTION_TIME > 0

— Change the below filter if you want to look at a longer range than the last 1 month
AND START_TIME > DATEADD(Month,-1,CURRENT_TIMESTAMP())
group by 1,2,3
)
, HOUR_EXECUTION_CTE AS (
SELECT START_TIME_HOUR
,WAREHOUSE_NAME
,SUM(USER_HOUR_EXECUTION_TIME) AS HOUR_EXECUTION_TIME
FROM USER_HOUR_EXECUTION_CTE
group by 1,2
)
, APPROXIMATE_CREDITS AS (
SELECT
A.USER_NAME
,C.WAREHOUSE_NAME
,(A.USER_HOUR_EXECUTION_TIME/B.HOUR_EXECUTION_TIME)*C.CREDITS_USED AS APPROXIMATE_CREDITS_USED

FROM USER_HOUR_EXECUTION_CTE A
JOIN HOUR_EXECUTION_CTE B ON A.START_TIME_HOUR = B.START_TIME_HOUR and B.WAREHOUSE_NAME = A.WAREHOUSE_NAME
JOIN “SNOWFLAKE”.”ACCOUNT_USAGE”.”WAREHOUSE_METERING_HISTORY” C ON C.WAREHOUSE_NAME = A.WAREHOUSE_NAME AND C.START_TIME = A.START_TIME_HOUR
)

SELECT
USER_NAME
,WAREHOUSE_NAME
,SUM(APPROXIMATE_CREDITS_USED) AS APPROXIMATE_CREDITS_USED
FROM APPROXIMATE_CREDITS
GROUP BY 1,2
ORDER BY 3 DESC

추가로 모니터링을 위해서 사용하실 수 있는 쿼리는 아래의 링크를 통해서 확인하실 수 있습니다.

Resource Optimization: Usage Monitoring

Resource Optimization: Billing Metrics

감사합니다.