Account Usage¶

예: 사용자 로그인 메트릭¶

사용자의 로그인 시도 실패 간 평균 시간(초)(당월 합계):

select user_name,
       count(*) as failed_logins,
       avg(seconds_between_login_attempts) as average_seconds_between_login_attempts
from (
      select user_name,
             timediff(seconds, event_timestamp, lead(event_timestamp)
                 over(partition by user_name order by event_timestamp)) as seconds_between_login_attempts
      from login_history
      where event_timestamp > date_trunc(month, current_date)
      and is_success = 'NO'
     )
group by 1
order by 3;

Copy

사용자별 로그인 실패 횟수(당월 합계):

select user_name,
       sum(iff(is_success = 'NO', 1, 0)) as failed_logins,
       count(*) as logins,
       sum(iff(is_success = 'NO', 1, 0)) / nullif(count(*), 0) as login_failure_rate
from login_history
where event_timestamp > date_trunc(month, current_date)
group by 1
order by 4 desc;

Copy

사용자 및 연결 중인 클라이언트별 로그인 실패 횟수(당월 합계):

select reported_client_type,
       user_name,
       sum(iff(is_success = 'NO', 1, 0)) as failed_logins,
       count(*) as logins,
       sum(iff(is_success = 'NO', 1, 0)) / nullif(count(*), 0) as login_failure_rate
from login_history
where event_timestamp > date_trunc(month, current_date)
group by 1,2
order by 5 desc;

Copy

예: 웨어하우스 성능¶

이 쿼리는 하루 동안 15분 간격으로 처리량 및 대기 시간과 같은 가상 웨어하우스 성능 메트릭을 계산합니다.

아래 코드 샘플에서 CURRENT_WAREHOUSE() 를 웨어하우스 이름으로 바꾸어 해당 웨어하우스의 메트릭을 계산할 수 있습니다. 또한 WITH 절에서 time_from 및 time_to 날짜를 변경하여 기간을 지정합니다.

WITH
params AS (
SELECT
    CURRENT_WAREHOUSE() AS warehouse_name,
    '2021-11-01' AS time_from,
    '2021-11-02' AS time_to
),

jobs AS (
SELECT
    query_id,
    time_slice(start_time::timestamp_ntz, 15, 'minute','start') as interval_start,
    qh.warehouse_name,
    database_name,
    query_type,
    total_elapsed_time,
    compilation_time AS compilation_and_scheduling_time,
    (queued_provisioning_time + queued_repair_time + queued_overload_time) AS queued_time,
    transaction_blocked_time,
    execution_time
FROM snowflake.account_usage.query_history qh, params
WHERE
    qh.warehouse_name = params.warehouse_name
AND start_time >= params.time_from
AND start_time <= params.time_to
AND execution_status = 'SUCCESS'
AND query_type IN ('SELECT','UPDATE','INSERT','MERGE','DELETE')
),

interval_stats AS (
SELECT
    query_type,
    interval_start,
    COUNT(DISTINCT query_id) AS numjobs,
    MEDIAN(total_elapsed_time)/1000 AS p50_total_duration,
    (percentile_cont(0.95) within group (order by total_elapsed_time))/1000 AS p95_total_duration,
    SUM(total_elapsed_time)/1000 AS sum_total_duration,
    SUM(compilation_and_scheduling_time)/1000 AS sum_compilation_and_scheduling_time,
    SUM(queued_time)/1000 AS sum_queued_time,
    SUM(transaction_blocked_time)/1000 AS sum_transaction_blocked_time,
    SUM(execution_time)/1000 AS sum_execution_time,
    ROUND(sum_compilation_and_scheduling_time/sum_total_duration,2) AS compilation_and_scheduling_ratio,
    ROUND(sum_queued_time/sum_total_duration,2) AS queued_ratio,
    ROUND(sum_transaction_blocked_time/sum_total_duration,2) AS blocked_ratio,
    ROUND(sum_execution_time/sum_total_duration,2) AS execution_ratio,
    ROUND(sum_total_duration/numjobs,2) AS total_duration_perjob,
    ROUND(sum_compilation_and_scheduling_time/numjobs,2) AS compilation_and_scheduling_perjob,
    ROUND(sum_queued_time/numjobs,2) AS queued_perjob,
    ROUND(sum_transaction_blocked_time/numjobs,2) AS blocked_perjob,
    ROUND(sum_execution_time/numjobs,2) AS execution_perjob
FROM jobs
GROUP BY 1,2
ORDER BY 1,2
)
SELECT * FROM interval_stats;

Copy

참고

다른 문 유형을 별도로 분석합니다(예: INSERT 또는 DELETE 또는 기타 문과 독립적인 SELECT 문).

• NUMJOBS 값은 해당 시간 간격에 대한 처리량을 나타냅니다.

• P50_TOTAL_DURATION(중앙값) 및 P95_TOTAL_DURATION(피크) 값은 대기 시간을 나타냅니다.

• SUM_TOTAL_DURATION은 다양한 작업 스테이지(COMPILATION_AND_SCHEDULING, QUEUED, BLOCKED, EXECUTION)에 대한 SUM_<job_stage>_TIME 값의 합계입니다.

• 부하(NUMJOBS)가 증가할 때 <job_stage>_RATIO 값을 분석합니다. 평균에서 비율 변경 또는 편차를 찾습니다.

• QUEUED_RATIO가 높으면 웨어하우스에 용량이 충분하지 않을 수 있습니다. 클러스터를 더 추가하거나 웨어하우스 크기를 늘리십시오.

예: 웨어하우스 크레딧 사용¶

계정의 각 웨어하우스에서 사용한 크레딧(당월 합계):

select warehouse_name,
       sum(credits_used) as total_credits_used
from warehouse_metering_history
where start_time >= date_trunc(month, current_date)
group by 1
order by 2 desc;

Copy

계정의 각 웨어하우스에서 시간의 경과에 따라 사용한 크레딧(당월 합계):

select start_time::date as usage_date,
       warehouse_name,
       sum(credits_used) as total_credits_used
from warehouse_metering_history
where start_time >= date_trunc(month, current_date)
group by 1,2
order by 2,1;

Copy

예: 데이터 저장소 사용량¶

시간의 경과에 따라 계정에 저장된 청구 가능한 테라바이트 수:

select date_trunc(month, usage_date) as usage_month
  , avg(storage_bytes + stage_bytes + failsafe_bytes) / power(1024, 4) as billable_tb
from storage_usage
group by 1
order by 1;

Copy

예: 사용자 쿼리 합계 및 실행 시간¶

계정에서 실행된 총 작업 수(당월 합계):

select count(*) as number_of_jobs
from query_history
where start_time >= date_trunc(month, current_date);

Copy

계정의 각 웨어하우스에서 실행된 총 작업 수(당월 합계):

select warehouse_name,
       count(*) as number_of_jobs
from query_history
where start_time >= date_trunc(month, current_date)
group by 1
order by 2 desc;

Copy

사용자별 평균 쿼리 실행 시간(당월 합계):

select user_name,
       avg(execution_time) as average_execution_time
from query_history
where start_time >= date_trunc(month, current_date)
group by 1
order by 2 desc;

Copy

쿼리 유형 및 웨어하우스 크기별 평균 쿼리 실행 시간(당월 합계):

select query_type,
       warehouse_size,
       avg(execution_time) as average_execution_time
from query_history
where start_time >= date_trunc(month, current_date)
group by 1,2
order by 3 desc;

Copy

예: 모든 로그인 이벤트에 대한 쿼리 수 가져오기¶

LOGIN_HISTORY, QUERY_HISTORY, SESSIONS의 열을 조인하여 각 사용자 로그인 이벤트에 대한 쿼리 수를 가져옵니다.

참고

SESSIONS 뷰는 2020년 7월 20일~21일부터 정보를 기록하기 시작하므로, 쿼리 결과에는 이 날짜부터 시작해 세 가지 뷰 각각에 대해 겹치는 정보만 포함됩니다.

select l.user_name,
       l.event_timestamp as login_time,
       l.client_ip,
       l.reported_client_type,
       l.first_authentication_factor,
       l.second_authentication_factor,
       count(q.query_id)
from snowflake.account_usage.login_history l
join snowflake.account_usage.sessions s on l.event_id = s.login_event_id
join snowflake.account_usage.query_history q on q.session_id = s.session_id
group by 1,2,3,4,5,6
order by l.user_name
;

Copy