[Data] GCP BigQuery에 데이터 저장하며 DW 이해하기

🥅 Goal

Phase 1
Phase 2

🛠️ Tech Stack

👩🏻‍🏫 Subject

Google BigQuery

OLTP vs OLAP

정의 및 비교

Data warehouse, Data lake, Data mart

정의 및 비교

Data warehouse 최적화

데이터 모델링:
- 데이터 구조를 최적화하여 쿼리 성능을 향상시키고, 데이터 저장 및 조회 효율성을 높입니다.
- 정규화와 비정규화 전략을 적절히 사용하여 데이터 무결성과 성능을 균형 있게 유지합니다.
파티셔닝:
- 큰 테이블을 작은 부분으로 나누어 성능을 최적화합니다. 파티션을 사용하면 특정 쿼리에서 필요한 데이터만 읽도록 하여 I/O 작업을 줄일 수 있습니다.
클러스터링:
- 클러스터링 키를 사용하여 데이터를 물리적으로 정렬함으로써 쿼리 성능을 향상시킵니다. 이는 특정 열을 기반으로 데이터를 그룹화하여 액세스 시간을 줄입니다.
인덱싱:
- 쿼리 성능을 향상시키기 위해 인덱스를 생성합니다. 인덱스는 데이터 조회 시 검색 속도를 높여 줍니다.
데이터 압축:
- 데이터 저장 공간을 절약하고 쿼리 성능을 높이기 위해 데이터 압축 기법을 사용합니다.
쿼리 최적화:
- 쿼리 성능을 향상시키기 위해 쿼리 구조를 최적화합니다. 이는 쿼리 계획을 분석하고, 불필요한 조인을 제거하며, 서브쿼리를 최적화하는 것을 포함합니다.
리소스 관리:
- 컴퓨팅 리소스와 저장소 리소스를 효율적으로 사용하도록 설정합니다. 예를 들어, 필요에 따라 컴퓨팅 클러스터를 스케일 업 또는 스케일 다운합니다.
캐싱:
- 자주 사용되는 쿼리 결과를 캐싱하여 반복적인 데이터 조회를 빠르게 처리합니다.

1. 쿼리 최적화

필요 데이터만 조회하기

SELECT user_id, user_name, user_email
FROM `project.dataset.users`
WHERE signup_date > '2023-01-01'

서브쿼리 및 조인 최적화

WITH filtered_users AS (
    SELECT user_id, user_name
    FROM `project.dataset.users`
    WHERE signup_date > '2023-01-01'
)
SELECT fu.user_id, fu.user_name, o.order_id, o.total_amount
FROM filtered_users fu
JOIN `project.dataset.orders` o ON fu.user_id = o.user_id
WHERE o.order_date > '2023-01-01'

2. 데이터 파티셔닝과 클러스터링

테이블 파티셔닝

CREATE TABLE `project.dataset.partitioned_table`
PARTITION BY DATE(timestamp_column)
AS
SELECT * FROM `project.dataset.original_table`

테이블 클러스터링

CREATE TABLE `project.dataset.clustered_table`
CLUSTER BY user_id
AS
SELECT * FROM `project.dataset.original_table`

3. 예약된 쿼리 및 캐시 활용

예약된 쿼리 설정 (Python 예제)

from google.cloud import bigquery
from google.cloud import scheduler_v1

client = bigquery.Client()

query = """
CREATE OR REPLACE TABLE `project.dataset.daily_summary` AS
SELECT
  DATE(transaction_date) as date,
  COUNT(*) as transaction_count,
  SUM(transaction_amount) as total_amount
FROM `project.dataset.transactions`
WHERE transaction_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
GROUP BY date
"""

job_config = bigquery.QueryJobConfig()
job = client.query(query, job_config=job_config)
job.result()  # Wait for the job to complete.

# Setting up Cloud Scheduler to run this query daily (example)
scheduler_client = scheduler_v1.CloudSchedulerClient()
parent = scheduler_client.location_path('your-project-id', 'your-location')

job = {
    'http_target': {
        'uri': 'https://your-endpoint-to-trigger-query',
        'http_method': 'POST',
    },
    'schedule': '0 0 * * *',  # Every day at midnight
    'time_zone': 'UTC',
}

response = scheduler_client.create_job(parent, job)
print('Created job: {}'.format(response.name))

4. 비용 모니터링

쿼리 비용 추정 (Dry Run)

from google.cloud import bigquery

client = bigquery.Client()

query = """
SELECT user_id, COUNT(*) as activity_count
FROM `project.dataset.user_activity`
WHERE activity_date > '2023-01-01'
GROUP BY user_id
"""

job_config = bigquery.QueryJobConfig(dry_run=True, use_query_cache=False)
query_job = client.query(query, job_config=job_config)

print("This query will process {} bytes.".format(query_job.total_bytes_processed))

5. 데이터 수명 주기 관리

오래된 데이터 삭제

DELETE FROM `project.dataset.transactions`
WHERE transaction_date < DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)

종합적인 최적화 예제

데이터 파티셔닝 및 클러스터링을 통한 최적화

CREATE TABLE `project.dataset.optimized_table`
PARTITION BY DATE(transaction_date)
CLUSTER BY user_id
AS
SELECT
  user_id,
  transaction_date,
  transaction_amount,
  product_id
FROM `project.dataset.raw_transactions`
WHERE transaction_date > '2023-01-01'

샤딩, 파티셔닝, 클러스터링

개념

데이터베이스에서 샤딩(Sharding), 파티셔닝(Partitioning), 클러스터링(Clustering)은 대규모 데이터베이스를 효율적으로 관리하고 성능을 향상시키기 위해 사용되는 기술들입니다. 각각의 개념과 그 차이점을 설명해드리겠습니다.

1. 샤딩 (Sharding)

샤딩은 데이터베이스를 여러 개의 작은 단위로 분할하여 각 단위가 독립적인 데이터베이스 서버에 저장되는 방식을 말합니다. 이를 통해 하나의 데이터베이스 서버가 처리해야 하는 부하를 분산시켜 성능을 향상시키고, 확장성을 높일 수 있습니다.

수평 샤딩 (Horizontal Sharding): 데이터베이스의 행(row)을 기준으로 데이터를 분할합니다. 예를 들어, 사용자 데이터를 사용자 ID 범위에 따라 여러 서버에 나누어 저장합니다.
장점: 고가용성, 확장성, 성능 향상
단점: 샤드 간의 트랜잭션 관리가 어려울 수 있음, 샤드의 균형이 깨질 경우 특정 샤드에 부하 집중 가능

2. 파티셔닝 (Partitioning)

파티셔닝은 데이터베이스의 테이블을 논리적으로 나누어 관리하는 방법입니다. 하나의 테이블을 여러 파티션으로 나누어 저장하여, 특정 파티션에 접근할 때 나머지 데이터는 무시하고 필요한 데이터에 빠르게 접근할 수 있습니다.

수평 파티셔닝 (Horizontal Partitioning): 샤딩과 유사하게 테이블의 행(row)을 기준으로 분할합니다.
수직 파티셔닝 (Vertical Partitioning): 테이블의 열(column)을 기준으로 분할합니다. 예를 들어, 자주 조회되는 열과 자주 조회되지 않는 열을 분리합니다.
장점: 쿼리 성능 향상, 데이터 관리 용이
단점: 복잡성 증가, 파티션 간의 조인 비용 발생 가능

3. 클러스터링 (Clustering)

클러스터링은 여러 대의 서버를 하나의 논리적 유닛으로 묶어, 단일 데이터베이스처럼 동작하게 만드는 방법입니다. 클러스터링을 통해 고가용성과 부하 분산을 달성할 수 있습니다.

공유 디스크 클러스터링 (Shared Disk Clustering): 모든 노드가 동일한 디스크를 공유합니다. 예를 들어, 오라클 RAC(Real Application Clusters)가 이 방식입니다.
공유-무디스크 클러스터링 (Shared-Nothing Clustering): 각 노드가 독립적인 디스크를 가지고 있으며, 노드 간의 데이터는 공유되지 않습니다. 분산 데이터베이스 시스템에서 자주 사용됩니다.
장점: 고가용성, 부하 분산, 확장성
단점: 복잡한 설정, 데이터 일관성 관리의 어려움

비교 요약

샤딩: 데이터를 여러 개의 독립적인 데이터베이스 서버에 분산 저장하여 확장성과 성능을 향상시킵니다. 주로 데이터베이스 서버의 부하를 분산시키기 위해 사용됩니다.
파티셔닝: 하나의 데이터베이스 테이블을 논리적으로 나누어 관리합니다. 쿼리 성능을 최적화하고 데이터 관리 효율성을 높이기 위해 사용됩니다.
클러스터링: 여러 서버를 하나의 논리적 유닛으로 묶어 고가용성과 부하 분산을 달성합니다. 고가용성과 확장성을 위해 사용됩니다.

이 세 가지 기술은 각각의 목적과 용도에 따라 데이터베이스 시스템의 성능을 최적화하고 관리 효율성을 높이는 데 중요한 역할을 합니다.

🔥 Challenge

Previous[BackEnd] SpringBoot API gateway 개발 Next[Data] GCP Data Studio를 활용하여 데이터 시각화 하기

Last updated 1 month ago