빅쿼리 슬롯

-- 쿼리 우선순위 설정 예시
SELECT *
FROM `project.dataset.table`
WHERE condition = true
-- 실행 시 슬롯 우선순위: INTERACTIVE > BATCH

-- 실시간 슬롯 사용량 조회
SELECT reservation_name
     , total_slots
     , assigned_slots
     , idle_slots
     , total_assigned_slots - assigned_slots AS available_slots
FROM `region-US`.INFORMATION_SCHEMA.RESERVATIONS_TIMELINE
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
ORDER BY creation_time DESC;

-- 지난 24시간 동안 쿼리별 슬롯 사용량
SELECT job_id
     , user_email
     , query
     , total_slot_ms
     , total_slot_ms / 1000 / 60 AS slot_minutes
     , start_time
     , end_time
FROM `region-US`.INFORMATION_SCHEMA.JOBS_BY_USER
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
  AND total_slot_ms > 0
ORDER BY total_slot_ms DESC
LIMIT 100;

-- 비효율적인 쿼리 (피해야 할 패턴)
SELECT *
FROM large_table t1
         JOIN another_large_table t2 ON t1.id = t2.id
WHERE t1.date_column BETWEEN '2023-01-01' AND '2023-12-31';

-- 최적화된 쿼리
SELECT t1.id
     , t1.column1
     , t2.column2
FROM large_table t1
         JOIN another_large_table t2 ON t1.id = t2.id
WHERE t1.date_column BETWEEN '2023-01-01' AND '2023-12-31'
  AND t1._PARTITIONTIME BETWEEN TIMESTAMP('2023-01-01') AND TIMESTAMP('2023-12-31');

-- 파티션 테이블 생성
CREATE TABLE `project.dataset.partitioned_table`
(
    id INT64,
    name         STRING,
    created_date DATE
) PARTITION BY created_date
CLUSTER BY id;

-- 조직 내 슬롯 사용량 분석
WITH slot_usage AS (SELECT project_id
                         , reservation_name
                         , SUM(total_slot_ms) / 1000 / 60 / 60 AS slot_hours
                         , COUNT(*)                            AS query_count
                    FROM `region-US`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
                    WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
                      AND reservation_name IS NOT NULL
                    GROUP BY project_id, reservation_name)
SELECT project_id
     , reservation_name
     , slot_hours
     , query_count
     , slot_hours / query_count AS avg_slot_hours_per_query
FROM slot_usage
ORDER BY slot_hours DESC;

-- ML 훈련용 예약 할당
CREATE OR REPLACE MODEL `ml_project.models.customer_segmentation`
OPTIONS(
  model_type='KMEANS',
  num_clusters=5,
  reservation='ml-training-reservation'
) AS
SELECT customer_id
     , feature1
     , feature2
     , feature3
FROM `ml_project.features.customer_features`;

-- 슬롯 사용 효율성 분석
SELECT
  job_id,
  query,
  total_slot_ms,
  total_bytes_processed,
  (total_slot_ms / 1000) / (total_bytes_processed / POW(2, 30)) AS slot_seconds_per_gb
FROM `region-US`.INFORMATION_SCHEMA.JOBS_BY_USER
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
  AND total_slot_ms > 0
ORDER BY slot_seconds_per_gb DESC
LIMIT 20;

-- 일일 슬롯 비용 추이
SELECT DATE(usage_start_time)        as usage_date
     , reservation_name
     , SUM(slot_ms) / 1000 / 60 / 60 AS total_slot_hours
     , total_slot_hours * 0.04       AS estimated_cost_usd -- Flex 슬롯 시간당 약 $0.04
FROM `region-US`.INFORMATION_SCHEMA.RESERVATION_USAGE
WHERE usage_start_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY usage_date, reservation_name
ORDER BY usage_date DESC, estimated_cost_usd DESC;