빅쿼리 파티션 타임존

타임존 처리 방법

개요

BigQuery에서 파티션을 생성할 때 timezone(시간대) 처리는 데이터의 정확한 분할과 쿼리 성능에 중요한 역할을 합니다. 이 문서에서는 BigQuery 파티션에서 timezone을 지정하고 다루는 방법을 설명합니다.

파티션 타입과 Timezone

1. Time-unit Column Partitioning (시간 단위 컬럼 파티셔닝)

시간 기반 컬럼을 사용한 파티셔닝에서는 다음과 같은 컬럼 타입을 지원합니다:

지원 컬럼 타입

DATE: daily, monthly, yearly 단위 파티셔닝
TIMESTAMP: hourly, daily, monthly, yearly 단위 파티셔닝
DATETIME: hourly, daily, monthly, yearly 단위 파티셔닝

중요한 Timezone 규칙

모든 파티션 경계는 UTC 시간을 기준으로 합니다
사용자가 다른 timezone의 데이터를 입력하더라도 파티션 분할은 UTC 기준으로 수행됩니다

2. Ingestion Time Partitioning (수집 시간 파티셔닝)

데이터 수집 시간을 기준으로 자동 파티셔닝을 수행합니다.

Timezone 특성

_PARTITIONTIME 및 _PARTITIONDATE 가상 컬럼 사용
수집 시간은 항상 UTC 기준으로 기록됩니다
파티션 경계는 UTC 시간 기준으로 설정됩니다

Timezone 처리 방법

1. 파티션 생성 시 Timezone 고려사항

-- DATE 컬럼 기반 일별 파티셔닝 (기본)
CREATE TABLE mydataset.sales_table (
    transaction_id INT64,
    transaction_date DATE,
    amount FLOAT64
)
PARTITION BY transaction_date;

-- TIMESTAMP 컬럼 기반 일별 파티셔닝
CREATE TABLE mydataset.events_table (
    event_id INT64,
    event_timestamp TIMESTAMP,
    user_id STRING
)
PARTITION BY DATE(event_timestamp);

-- TIMESTAMP 컬럼 기반 시간별 파티셔닝
CREATE TABLE mydataset.hourly_events (
    event_id INT64,
    event_timestamp TIMESTAMP,
    data STRING
)
PARTITION BY TIMESTAMP_TRUNC(event_timestamp, HOUR);

2. 다른 Timezone에서 파티션 생성

한국 시간대(KST) 기준 파티셔닝

-- KST 기준으로 파티셔닝하려면 TIMESTAMP를 KST로 변환 후 DATE 추출
CREATE TABLE mydataset.korea_sales (
    transaction_id INT64,
    transaction_timestamp TIMESTAMP,
    amount FLOAT64
)
PARTITION BY DATE(DATETIME(TIMESTAMP(transaction_timestamp), "Asia/Seoul"));

미국 동부 시간대(EST) 기준 파티셔닝

CREATE TABLE mydataset.us_east_sales (
    transaction_id INT64,
    transaction_timestamp TIMESTAMP,
    amount FLOAT64
)
PARTITION BY DATE(DATETIME(TIMESTAMP(transaction_timestamp), "America/New_York"));

3. Ingestion Time 파티션에서 Timezone 처리

기본 수집 시간 파티셔닝

-- UTC 기준 일별 수집 시간 파티셔닝
CREATE TABLE mydataset.ingestion_table (
    id INT64,
    data STRING
)
PARTITION BY _PARTITIONDATE;

특정 시간대 기준 쿼리

-- 한국 시간대 기준으로 수집 시간 필터링
SELECT *
FROM mydataset.ingestion_table
WHERE _PARTITIONTIME >= TIMESTAMP("2024-01-01", "Asia/Seoul")
  AND _PARTITIONTIME < TIMESTAMP("2024-01-02", "Asia/Seoul");

Partition Decorator를 이용한 Timezone 처리

특정 파티션에 데이터 로드

-- 특정 날짜 파티션에 직접 데이터 삽입 (한국 시간 기준)
INSERT INTO mydataset.sales_table$20240101
SELECT 
    transaction_id,
    DATE("2024-01-01") as transaction_date,
    amount
FROM source_table
WHERE DATE(transaction_timestamp, "Asia/Seoul") = "2024-01-01";

Load Job을 통한 파티션 지정

# bq 명령어로 특정 파티션에 데이터 로드
bq load \
    --source_format=CSV \
    --time_partitioning_field=transaction_date \
    mydataset.sales_table$20240101 \
    gs://my-bucket/data-20240101.csv \
    "transaction_id:INTEGER,transaction_date:DATE,amount:FLOAT"

CLI를 통한 파티션 생성 및 타임존 지정

BigQuery CLI(bq 명령어)를 사용하여 파티션 테이블을 생성하고 타임존을 고려한 데이터 처리를 수행하는 방법입니다.

1. 파티션 테이블 생성

TIME-UNIT 파티션 테이블 생성

# DATE 컬럼 기반 일별 파티션 테이블 생성
bq mk \
    --table \
    --time_partitioning_field=transaction_date \
    --time_partitioning_type=DAY \
    mydataset.sales_table \
    transaction_id:INTEGER,transaction_date:DATE,amount:FLOAT

# TIMESTAMP 컬럼 기반 시간별 파티션 테이블 생성
bq mk \
    --table \
    --time_partitioning_field=event_timestamp \
    --time_partitioning_type=HOUR \
    mydataset.hourly_events \
    event_id:INTEGER,event_timestamp:TIMESTAMP,data:STRING

INGESTION TIME 파티션 테이블 생성

# 수집 시간 기반 일별 파티션 테이블 생성 (UTC 기준)
bq mk \
    --table \
    --time_partitioning_type=DAY \
    mydataset.ingestion_table \
    id:INTEGER,data:STRING

# 파티션 만료 기간 설정 (7일)
bq mk \
    --table \
    --time_partitioning_type=DAY \
    --time_partitioning_expiration=604800 \
    mydataset.temp_ingestion_table \
    id:INTEGER,data:STRING

2. 타임존을 고려한 데이터 로드

CSV 파일에서 특정 타임존 데이터 로드

# 한국 시간(KST) CSV 데이터를 UTC로 변환하여 로드
bq load \
    --source_format=CSV \
    --time_partitioning_field=event_timestamp \
    --skip_leading_rows=1 \
    mydataset.events_table \
    gs://my-bucket/korea-events.csv \
    event_id:INTEGER,event_timestamp:TIMESTAMP,user_id:STRING

# 특정 파티션에 직접 로드 (날짜 지정)
bq load \
    --source_format=CSV \
    --replace \
    mydataset.sales_table\$20240101 \
    gs://my-bucket/sales-20240101.csv \
    transaction_id:INTEGER,transaction_date:DATE,amount:FLOAT

sales-20240101.csv

transaction_id,transaction_date,amount
1001,2024-01-01,150.50
1002,2024-01-01,89.99
1003,2024-01-01,245.00
1004,2024-01-01,67.25
1005,2024-01-01,199.99
1006,2024-01-01,45.75
1007,2024-01-01,320.00
1008,2024-01-01,78.50
1009,2024-01-01,156.25
1010,2024-01-01,92.00

JSON 파일에서 타임존 정보가 포함된 데이터 로드

# JSON 파일에서 ISO 8601 형식의 타임스탬프 로드
bq load \
    --source_format=NEWLINE_DELIMITED_JSON \
    --time_partitioning_field=created_at \
    --autodetect \
    mydataset.user_events \
    gs://my-bucket/events-with-timezone.json

events-with-timezone.json

{"event_id": 1001, "created_at": "2024-01-01T15:30:00+09:00", "user_id": "user123", "action": "login"}
{"event_id": 1002, "created_at": "2024-01-01T16:45:00+09:00", "user_id": "user456", "action": "purchase"}
{"event_id": 1003, "created_at": "2024-01-01T09:15:00-05:00", "user_id": "user789", "action": "view_product"}
{"event_id": 1004, "created_at": "2024-01-01T22:30:00+00:00", "user_id": "user321", "action": "logout"}
{"event_id": 1005, "created_at": "2024-01-02T08:00:00+09:00", "user_id": "user654", "action": "search"}

3. CLI를 통한 타임존 변환 쿼리 실행

기본 타임존 변환 쿼리

# 한국 시간대로 변환하여 특정 날짜 데이터 조회
bq query \
    --use_legacy_sql=false \
    --parameter=target_date:DATE:2024-01-01 \
    --parameter=timezone:STRING:Asia/Seoul \
    "
    SELECT 
        event_id,
        event_timestamp,
        DATETIME(event_timestamp, @timezone) as local_time,
        user_id
    FROM mydataset.events_table
    WHERE DATE(event_timestamp, @timezone) = @target_date
    "

배치 작업으로 타임존 변환 테이블 생성

# 기존 UTC 데이터를 특정 타임존 기준으로 파티션된 새 테이블 생성
bq query \
    --use_legacy_sql=false \
    --destination_table=mydataset.korea_events \
    --time_partitioning_field=korea_date \
    --time_partitioning_type=DAY \
    --write_disposition=WRITE_TRUNCATE \
    "
    SELECT 
        *,
        DATE(event_timestamp, 'Asia/Seoul') as korea_date
    FROM mydataset.events_table
    WHERE event_timestamp >= '2024-01-01 00:00:00 UTC'
    "

4. 파티션 메타데이터 조회

파티션 정보 확인

# 테이블의 파티션 정보 조회
bq ls --format=prettyjson mydataset.sales_table

# 특정 파티션의 정보 상세 조회
bq show mydataset.sales_table\$20240101

# 모든 파티션 목록과 크기 정보 조회
bq query \
    --use_legacy_sql=false \
    "
    SELECT 
        partition_id,
        total_rows,
        total_logical_bytes,
        last_modified_time
    FROM mydataset.INFORMATION_SCHEMA.PARTITIONS
    WHERE table_name = 'sales_table'
    ORDER BY partition_id
    "

5. 타임존별 데이터 분석

시간대별 데이터 분포 분석

# 여러 시간대에서 동일 기간의 데이터 분포 비교
bq query \
    --use_legacy_sql=false \
    --job_id=timezone_analysis_$(date +%Y%m%d_%H%M%S) \
    "
    SELECT 
        'UTC' as timezone,
        DATE(event_timestamp) as date,
        COUNT(*) as event_count
    FROM mydataset.events_table
    WHERE event_timestamp >= '2024-01-01 00:00:00'
      AND event_timestamp < '2024-01-02 00:00:00'
    GROUP BY DATE(event_timestamp)
    
    UNION ALL
    
    SELECT 
        'Asia/Seoul' as timezone,
        DATE(event_timestamp, 'Asia/Seoul') as date,
        COUNT(*) as event_count
    FROM mydataset.events_table
    WHERE DATE(event_timestamp, 'Asia/Seoul') = '2024-01-01'
    GROUP BY DATE(event_timestamp, 'Asia/Seoul')
    "

6. 스케줄된 쿼리로 타임존 처리

정기적인 타임존 변환 작업 설정

# 매일 한국 시간 기준으로 전일 데이터를 처리하는 스케줄된 쿼리 생성
bq mk \
    --transfer_config \
    --project_id=my-project \
    --target_dataset=mydataset \
    --display_name="Daily Korea Timezone Processing" \
    --data_source=scheduled_query \
    --schedule="0 1 * * *" \
    --params='{
        "query":"INSERT INTO mydataset.daily_korea_summary SELECT DATE(event_timestamp, \"Asia/Seoul\") as event_date, COUNT(*) as total_events FROM mydataset.events_table WHERE DATE(event_timestamp, \"Asia/Seoul\") = DATE_SUB(CURRENT_DATE(\"Asia/Seoul\"), INTERVAL 1 DAY) GROUP BY event_date",
        "destination_table_name_template":"daily_summary_{run_date}",
        "write_disposition":"WRITE_TRUNCATE"
    }'

CLI 사용 시 주의사항

타임존 매개변수: CLI에서 직접 타임존을 지정하는 옵션은 제한적이므로, SQL 쿼리 내에서 타임존 함수를 활용
배치 처리: 대용량 데이터의 타임존 변환 시 --job_id를 지정하여 작업 추적
파라미터 사용: 반복적인 쿼리 실행 시 --parameter 옵션으로 타임존과 날짜를 매개변수화
파티션 프루닝: CLI 쿼리에서도 파티션 프루닝이 효과적으로 작동하도록 필터 조건 최적화

쿼리 시 Timezone 처리

1. 시간대 변환을 통한 파티션 필터링

-- 한국 시간 기준으로 2024년 1월 1일 데이터 조회
SELECT *
FROM mydataset.events_table
WHERE DATE(event_timestamp, "Asia/Seoul") = "2024-01-01";

-- 미국 동부 시간 기준으로 특정 시간 범위 데이터 조회
SELECT *
FROM mydataset.events_table
WHERE DATETIME(event_timestamp, "America/New_York") 
    BETWEEN "2024-01-01 09:00:00" AND "2024-01-01 17:00:00";

2. Ingestion Time 파티션 쿼리

-- UTC 기준 파티션 시간으로 필터링
SELECT *
FROM mydataset.ingestion_table
WHERE _PARTITIONDATE BETWEEN "2024-01-01" AND "2024-01-03";

-- 한국 시간 기준으로 변환하여 조회
SELECT 
    *,
    DATETIME(_PARTITIONTIME, "Asia/Seoul") as korea_partition_time
FROM mydataset.ingestion_table
WHERE _PARTITIONDATE = "2024-01-01";

Best Practices

1. 파티션 설계 시 고려사항

일관된 timezone 사용: 모든 데이터에 대해 동일한 timezone 기준을 사용
UTC 기준 권장: 글로벌 서비스의 경우 UTC 기준으로 파티셔닝 권장
비즈니스 timezone 고려: 지역 기반 서비스는 해당 지역 timezone 기준 고려

2. 성능 최적화

-- 파티션 프루닝을 위한 효율적인 필터 작성
-- 좋은 예: 파티션 컬럼을 직접 필터링
SELECT * FROM mydataset.sales_table
WHERE transaction_date >= "2024-01-01" 
  AND transaction_date <= "2024-01-31";

-- 피해야 할 예: 함수 적용으로 파티션 프루닝 방해
SELECT * FROM mydataset.sales_table
WHERE DATE_ADD(transaction_date, INTERVAL 1 DAY) >= "2024-01-02";

3. 데이터 로드 전략

-- 배치 데이터 로드 시 정확한 파티션 지정
LOAD DATA INTO mydataset.sales_table
PARTITION (transaction_date = "2024-01-01")
FROM 'gs://bucket/sales-20240101.csv'
WITH PARTITION_COLUMNS (transaction_date);

주의사항

1. Timezone 변환의 성능 영향

쿼리에서 timezone 변환 함수 사용 시 파티션 프루닝이 제대로 작동하지 않을 수 있음
가능한 한 파티션 생성 시 적절한 timezone을 고려하여 설계

2. Daylight Saving Time (DST) 처리

-- DST 변경 시점을 고려한 쿼리 작성
SELECT 
    event_timestamp,
    DATETIME(event_timestamp, "America/New_York") as local_time,
    -- DST 정보 확인
    EXTRACT(DAYOFYEAR FROM DATETIME(event_timestamp, "America/New_York")) as day_of_year
FROM mydataset.events_table
WHERE DATE(event_timestamp, "America/New_York") = "2024-03-10"; -- DST 시작일

3. 시간대별 데이터 분포 확인

-- 파티션별 데이터 분포 확인
SELECT 
    _PARTITIONTIME,
    COUNT(*) as record_count,
    MIN(event_timestamp) as min_timestamp,
    MAX(event_timestamp) as max_timestamp
FROM mydataset.ingestion_table
WHERE _PARTITIONDATE BETWEEN "2024-01-01" AND "2024-01-07"
GROUP BY _PARTITIONTIME
ORDER BY _PARTITIONTIME;

마무리

BigQuery 파티션에서 timezone을 올바르게 처리하는 것은 데이터의 정확성과 쿼리 성능에 직접적인 영향을 미칩니다.

파티션 경계는 항상 UTC 기준임을 기억하세요
비즈니스 요구사항에 맞는 timezone 전략을 수립하세요
파티션 프루닝을 고려한 쿼리 작성으로 성능을 최적화하세요
일관된 timezone 처리 방식을 유지하여 데이터 무결성을 보장하세요

이러한 원칙들을 따르면 효율적이고 정확한 시간 기반 파티셔닝을 구현할 수 있습니다.