BigQuery / Snowflake のクエリコスト：誰のクエリが高いかを見る

-- 過去 30 日のユーザ別コストSELECT  user_email,  COUNT(*) AS query_count,  SUM(total_bytes_billed) / POW(10, 12) AS tb_billed,  SUM(total_bytes_billed) / POW(10, 12) * 5.0 AS cost_usd_estimate,  -- $5/TB  SUM(total_slot_ms) / 1000 / 60 AS slot_minutesFROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECTWHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)  AND state = 'DONE'  AND statement_type = 'SELECT'GROUP BY 1ORDER BY tb_billed DESCLIMIT 30;

WITH top_queries AS (  SELECT    user_email,    job_id,    LEFT(query, 200) AS query_preview,    total_bytes_billed / POW(10, 9) AS gb_billed,    creation_time  FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT  WHERE DATE(creation_time) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)    AND total_bytes_billed > 100 * POW(10, 9)  -- 100 GB 超)SELECT * FROM top_queriesORDER BY gb_billed DESCLIMIT 10;

SELECT  ROLE_NAME,  USER_NAME,  COUNT(*) AS query_count,  SUM(EXECUTION_TIME) / 1000 / 60 AS execution_minutes,  SUM(CREDITS_USED_CLOUD_SERVICES) AS creditsFROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORYWHERE START_TIME >= DATEADD(day, -30, CURRENT_DATE())GROUP BY 1, 2ORDER BY credits DESCLIMIT 30;