GCP BigQuery ile Veri Analizi: Büyük Veriyi Bulutta Yönetmek

Tarafındankomut
0 Comments

Veri analizi dünyasında BigQuery’yi keşfetmek, ilk kez gerçek anlamda ölçeklenebilir bir altyapıyla tanışmak gibi hissettiriyor. Terabaytlarca veriyi saniyeler içinde sorgulamak, geleneksel veritabanı yönetimine alışmış biri için neredeyse sihir gibi görünüyor. Ama altında yatan mantık son derece pratik ve öğrenilebilir. Bu yazıda GCP BigQuery’yi gerçek dünya senaryolarıyla ele alacağız, hem temel kurulum hem de ileri düzey kullanım konularını adım adım geçeceğiz.

BigQuery Nedir ve Neden Kullanmalısın?

BigQuery, Google’ın sunduğu serverless, yüksek ölçeklenebilirliğe sahip bir veri ambarı çözümüdür. Klasik bir veritabanında indeks oluşturur, shard yönetimi yapar, replikasyon kurarsın. BigQuery’de bunların hiçbirini yapmana gerek yok. Altyapı yönetimini Google üstleniyor, sen sadece SQL yazıyorsun.

Pratikte ne anlama geliyor? Bir e-ticaret şirketini düşün. 5 yıllık sipariş geçmişi, 200 GB’ın üzerinde log verisi, günlük milyonlarca kullanıcı etkileşimi. Bu veriler üzerinde “geçen ay hangi ürün kategorisi en çok satıldı” sorusunu sormak istiyorsun. Geleneksel bir yaklaşımda saatlerce sürebilecek bir sorgu, BigQuery’de birkaç saniyede tamamlanabiliyor.

BigQuery’nin temel avantajları şunlar:

  • Serverless mimari: Sunucu provisioning yok, kapasite planlaması yok
  • Columnar storage: Sadece sorguladığın kolonları okur, maliyet optimize olur
  • Ayrılmış hesaplama ve depolama: Storage ve compute ayrı ücretlendirilir
  • Standart SQL desteği: ANSI SQL ile çalışır, özel syntax öğrenmen gerekmiyor
  • ML entegrasyonu: BigQuery ML ile doğrudan SQL ile model eğitebilirsin
  • GCS, Pub/Sub ve diğer GCP servisleriyle native entegrasyon

Ortam Kurulumu ve İlk Adımlar

Başlamadan önce gcloud CLI’ın kurulu ve authenticate edilmiş olması gerekiyor. Projen de BigQuery API’ının aktif edilmiş olması şart.

# gcloud SDK kurulumu sonrası authentication
gcloud auth login
gcloud auth application-default login

# BigQuery API'ını etkinleştir
gcloud services enable bigquery.googleapis.com

# Proje ID'ni set et
gcloud config set project YOUR_PROJECT_ID

# bq CLI aracını test et
bq version
bq ls

BigQuery’de veri organizasyonu şu hiyerarşiyle çalışır: Project > Dataset > Table. İlk olarak bir dataset oluşturalım.

# Dataset oluştur (EU region, 90 gün veri retention)
bq mk 
  --dataset 
  --location=EU 
  --default_table_expiration=7776000 
  --description="E-ticaret analitik dataset" 
  YOUR_PROJECT_ID:ecommerce_analytics

# Dataset bilgilerini görüntüle
bq show YOUR_PROJECT_ID:ecommerce_analytics

# Dataset listele
bq ls --datasets YOUR_PROJECT_ID

Dataset oluşturuldu. Şimdi veri yükleyelim. Gerçek dünyada çoğunlukla CSV, JSON veya Parquet formatlarında veri gelir.

# GCS'deki CSV dosyasını BigQuery'ye yükle
bq load 
  --source_format=CSV 
  --skip_leading_rows=1 
  --autodetect 
  ecommerce_analytics.orders 
  gs://your-bucket/orders/2024/*.csv

# JSON formatında yükleme
bq load 
  --source_format=NEWLINE_DELIMITED_JSON 
  --autodetect 
  ecommerce_analytics.user_events 
  gs://your-bucket/events/user_events.json

# Schema manuel tanımlama (autodetect yerine)
bq load 
  --source_format=CSV 
  --skip_leading_rows=1 
  ecommerce_analytics.products 
  gs://your-bucket/products/catalog.csv 
  product_id:STRING,name:STRING,category:STRING,price:FLOAT,stock:INTEGER,created_at:TIMESTAMP

Önemli not: --autodetect kullanışlı görünse de production ortamında schema’yı manuel tanımlamanı öneririm. Autodetect bazen FLOAT olması gereken kolonları INTEGER olarak algılayabiliyor ya da tarih formatlarında yanılabiliyor. Bu durum downstream süreçlerde ciddi sorunlara yol açar.

Temel Sorgular ve Optimizasyon

BigQuery’de sorgu yazmak bildiğin SQL ile neredeyse aynı. Ama bazı nüanslar var. Özellikle maliyet ve performans açısından dikkat etmen gereken konular mevcut.

BigQuery on-demand pricing modunda işlenen veri başına ücret alır. 1 TB işlenen veri için yaklaşık 5 dolar. Bu yüzden sorgu optimizasyonu hem performans hem maliyet meselesi.

-- KÖTÜ: SELECT * her kolonu okur, gereksiz maliyet
SELECT * FROM `ecommerce_analytics.orders`
WHERE order_date >= '2024-01-01';

-- İYİ: Sadece ihtiyacın olan kolonları seç
SELECT
  order_id,
  customer_id,
  order_date,
  total_amount,
  status
FROM `ecommerce_analytics.orders`
WHERE order_date >= '2024-01-01'
  AND status = 'completed';

-- Partition filter kullanımı (partition tablosunda)
SELECT
  order_id,
  customer_id,
  total_amount
FROM `ecommerce_analytics.orders_partitioned`
WHERE DATE(order_date) BETWEEN '2024-01-01' AND '2024-03-31'
  AND status = 'completed';

Sorguyu çalıştırmadan önce ne kadar veri işleyeceğini görmek için --dry_run flagini kullan:

# Sorgu maliyetini önceden hesapla
bq query 
  --dry_run 
  --use_legacy_sql=false 
  'SELECT order_id, total_amount FROM `your_project.ecommerce_analytics.orders` WHERE status = "completed"'

# Çıktı şöyle görünür:
# Query successfully validated. Assuming the tables are not modified,
# running this query will process 1073741824 bytes of data.

Partition ve Clustering Stratejileri

BigQuery’de gerçek performans kazanımı partition ve clustering ile geliyor. Bu ikisini doğru kullanmak, hem sorgu süresini hem de maliyeti dramatik şekilde düşürüyor.

Partition: Tabloyu belirli bir kolona göre bölümlere ayırır. Tarih bazlı partition en yaygın kullanım senaryosu. Bir gün için sorgu çektiğinde, BigQuery sadece o günün partition’ını okur.

Clustering: Partition içindeki veriyi belirli kolonlara göre fiziksel olarak sıralar. Yüksek kardinaliteli filtrelerde etkili.

# Tarih bazlı partition'lı tablo oluştur
bq mk 
  --table 
  --schema='order_id:STRING,customer_id:STRING,order_date:DATE,total_amount:FLOAT,status:STRING,region:STRING,product_category:STRING' 
  --time_partitioning_field=order_date 
  --time_partitioning_type=DAY 
  --clustering_fields=region,status,product_category 
  ecommerce_analytics.orders_optimized

# Partition bilgilerini kontrol et
bq show --schema ecommerce_analytics.orders_optimized

# Partition istatistikleri
bq query --use_legacy_sql=false 
'SELECT
  partition_id,
  total_rows,
  total_logical_bytes / (1024*1024*1024) as size_gb,
  last_modified_time
FROM `ecommerce_analytics.INFORMATION_SCHEMA.PARTITIONS`
WHERE table_name = "orders_optimized"
ORDER BY partition_id DESC
LIMIT 30'

Gerçek Dünya Senaryosu: E-ticaret Analizi

Elimizde şu tablolar olduğunu varsayalım:

  • orders: Sipariş ana tablosu
  • order_items: Sipariş detayları
  • customers: Müşteri bilgileri
  • products: Ürün kataloğu

Yaygın analitik ihtiyaçları karşılayan sorgular yazalım.

-- Aylık gelir ve sipariş sayısı trendi
WITH monthly_metrics AS (
  SELECT
    DATE_TRUNC(order_date, MONTH) as month,
    COUNT(DISTINCT order_id) as order_count,
    COUNT(DISTINCT customer_id) as unique_customers,
    SUM(total_amount) as revenue,
    AVG(total_amount) as avg_order_value
  FROM `ecommerce_analytics.orders`
  WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH)
    AND status = 'completed'
  GROUP BY 1
),
monthly_with_growth AS (
  SELECT
    month,
    order_count,
    unique_customers,
    revenue,
    avg_order_value,
    LAG(revenue) OVER (ORDER BY month) as prev_month_revenue,
    ROUND(
      (revenue - LAG(revenue) OVER (ORDER BY month)) /
      LAG(revenue) OVER (ORDER BY month) * 100, 2
    ) as revenue_growth_pct
  FROM monthly_metrics
)
SELECT * FROM monthly_with_growth
ORDER BY month;

-- RFM (Recency, Frequency, Monetary) analizi
WITH rfm_base AS (
  SELECT
    customer_id,
    DATE_DIFF(CURRENT_DATE(), MAX(DATE(order_date)), DAY) as recency_days,
    COUNT(DISTINCT order_id) as frequency,
    SUM(total_amount) as monetary
  FROM `ecommerce_analytics.orders`
  WHERE status = 'completed'
    AND order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
  GROUP BY 1
),
rfm_scores AS (
  SELECT
    customer_id,
    recency_days,
    frequency,
    monetary,
    NTILE(5) OVER (ORDER BY recency_days DESC) as recency_score,
    NTILE(5) OVER (ORDER BY frequency ASC) as frequency_score,
    NTILE(5) OVER (ORDER BY monetary ASC) as monetary_score
  FROM rfm_base
)
SELECT
  customer_id,
  recency_days,
  frequency,
  ROUND(monetary, 2) as monetary,
  recency_score,
  frequency_score,
  monetary_score,
  (recency_score + frequency_score + monetary_score) as total_rfm_score,
  CASE
    WHEN (recency_score + frequency_score + monetary_score) >= 13 THEN 'Champion'
    WHEN (recency_score + frequency_score + monetary_score) >= 10 THEN 'Loyal Customer'
    WHEN recency_score >= 4 AND frequency_score <= 2 THEN 'New Customer'
    WHEN recency_score <= 2 AND monetary_score >= 4 THEN 'At Risk'
    ELSE 'Needs Attention'
  END as customer_segment
FROM rfm_scores
ORDER BY total_rfm_score DESC;

Scheduled Queries ve Otomatizasyon

Analizleri manuel çalıştırmak yerine otomatikleştirmek production için şart. BigQuery’nin scheduled queries özelliği bu iş için biçilmiş kaftan.

# Scheduled query oluştur (her gün gece 02:00'da çalışır)
bq mk 
  --transfer_config 
  --project_id=YOUR_PROJECT_ID 
  --data_source=scheduled_query 
  --display_name="Daily Revenue Summary" 
  --schedule="every 24 hours" 
  --params='{
    "query": "INSERT INTO `ecommerce_analytics.daily_revenue_summary` SELECT DATE(order_date) as date, SUM(total_amount) as revenue, COUNT(*) as order_count FROM `ecommerce_analytics.orders` WHERE DATE(order_date) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AND status = "completed" GROUP BY 1",
    "destination_table_name_template": "daily_revenue_summary",
    "write_disposition": "WRITE_APPEND"
  }'

# Transfer config listele
bq ls --transfer_config --transfer_location=EU

# Belirli bir scheduled query'yi manuel tetikle
bq mk 
  --transfer_run 
  --start_time='2024-01-01T00:00:00Z' 
  --end_time='2024-01-02T00:00:00Z' 
  projects/YOUR_PROJECT_ID/locations/EU/transferConfigs/TRANSFER_CONFIG_ID

Python ile BigQuery’yi programatik olarak kullanmak da sıkça karşılaşılan bir ihtiyaç. Özellikle ETL pipeline’larında:

# BigQuery Python client kurulumu
pip install google-cloud-bigquery google-cloud-bigquery-storage pandas pyarrow

# Python script örneği - çalıştırma
python3 bigquery_etl.py --date 2024-01-15 --env production
# bigquery_etl.py - Temel BigQuery Python entegrasyonu
from google.cloud import bigquery
from google.oauth2 import service_account
import pandas as pd

# Client oluştur
client = bigquery.Client(project="YOUR_PROJECT_ID")

# Sorgu çalıştır ve DataFrame'e çek
query = """
    SELECT
        product_category,
        COUNT(DISTINCT order_id) as order_count,
        SUM(quantity) as total_units,
        SUM(revenue) as total_revenue
    FROM `ecommerce_analytics.order_items`
    WHERE DATE(created_at) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
    GROUP BY 1
    ORDER BY total_revenue DESC
"""

df = client.query(query).to_dataframe()
print(df.head(10))

# DataFrame'i BigQuery'ye yaz
job_config = bigquery.LoadJobConfig(
    write_disposition=bigquery.WriteDisposition.WRITE_APPEND,
    schema=[
        bigquery.SchemaField("product_category", "STRING"),
        bigquery.SchemaField("order_count", "INTEGER"),
        bigquery.SchemaField("total_units", "INTEGER"),
        bigquery.SchemaField("total_revenue", "FLOAT"),
    ]
)

table_id = "YOUR_PROJECT_ID.ecommerce_analytics.category_daily_summary"
job = client.load_table_from_dataframe(df, table_id, job_config=job_config)
job.result()
print(f"Loaded {job.output_rows} rows to {table_id}")

Maliyet Yönetimi ve Monitoring

BigQuery masrafları fark ettirmeden büyüyebilir. Özellikle takıma yeni biri katılıp SELECT * ile terabaytlarca veri sorgulamaya başlarsa. Bu yüzden cost control mekanizmaları kurmak kritik.

# Proje bazlı daily quota limit (bytes)
# Bu ayarı Console'dan ya da API ile yapabilirsin
# 100 GB günlük limit
gcloud alpha billing budgets create 
  --billing-account=YOUR_BILLING_ACCOUNT_ID 
  --display-name="BigQuery Monthly Budget" 
  --budget-amount=500USD 
  --threshold-rule=percent=0.5 
  --threshold-rule=percent=0.9 
  --threshold-rule=percent=1.0

# En pahalı sorguları bul
bq query --use_legacy_sql=false 
'SELECT
  user_email,
  job_id,
  creation_time,
  ROUND(total_bytes_processed / (1024*1024*1024), 2) as gb_processed,
  ROUND(total_bytes_processed / (1024*1024*1024) * 0.005, 4) as estimated_cost_usd,
  SUBSTR(query, 1, 200) as query_preview
FROM `region-EU.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = "QUERY"
  AND state = "DONE"
ORDER BY total_bytes_processed DESC
LIMIT 20'

# Kullanıcı bazlı haftalık tüketim
bq query --use_legacy_sql=false 
'SELECT
  user_email,
  COUNT(*) as query_count,
  ROUND(SUM(total_bytes_processed) / (1024*1024*1024), 2) as total_gb_processed,
  ROUND(SUM(total_bytes_processed) / (1024*1024*1024) * 0.005, 2) as estimated_weekly_cost_usd
FROM `region-EU.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = "QUERY"
  AND state = "DONE"
GROUP BY 1
ORDER BY total_gb_processed DESC'

Flat-rate pricing: Eğer BigQuery’yi yoğun kullanan bir organizasyondasın, on-demand yerine flat-rate (slots) modeline geçmeyi değerlendirmelisin. Aylık 2000 dolar karşılığında 100 dedicated slot alıyorsun. Yoğun kullanımda bu çok daha ekonomik olabiliyor.

IAM ve Güvenlik Yapılandırması

Veri güvenliği BigQuery’de de kritik. Hassas verilere erişimi granüler şekilde kontrol etmen gerekiyor.

# Dataset seviyesinde erişim ver
bq add-iam-policy-binding 
  --member=user:[email protected] 
  --role=roles/bigquery.dataViewer 
  ecommerce_analytics

# Proje seviyesinde job runner yetkisi (sorgu çalıştırabilir ama veri okuyamaz)
gcloud projects add-iam-policy-binding YOUR_PROJECT_ID 
  --member=user:[email protected] 
  --role=roles/bigquery.jobUser

# Service account oluştur ve yetkilendir (ETL pipeline için)
gcloud iam service-accounts create bq-etl-sa 
  --display-name="BigQuery ETL Service Account"

gcloud projects add-iam-policy-binding YOUR_PROJECT_ID 
  --member=serviceAccount:bq-etl-sa@YOUR_PROJECT_ID.iam.gserviceaccount.com 
  --role=roles/bigquery.dataEditor

# Row-level security policy oluştur (sadece kendi bölge datasını görsün)
bq query --use_legacy_sql=false 
'CREATE ROW ACCESS POLICY regional_access
ON `ecommerce_analytics.orders`
GRANT TO ("group:[email protected]")
FILTER USING (region = "EMEA")'

# Key oluştur ve local'e kaydet
gcloud iam service-accounts keys create ./bq-etl-key.json 
  --iam-account=bq-etl-sa@YOUR_PROJECT_ID.iam.gserviceaccount.com

Performans Sorunlarını Tespit Etmek

Yavaş çalışan bir sorgu ile karşılaştığında BigQuery’nin query execution planına bakmak çok yardımcı oluyor.

# Query plan'ı JSON olarak çek
bq show --format=json 
  --job=YOUR_PROJECT_ID:EU.bqjob_xxxxxxxxxxxx 
  | python3 -c "
import json, sys
job = json.load(sys.stdin)
stats = job.get('statistics', {}).get('query', {})
print('Input bytes:', stats.get('totalBytesProcessed'))
print('Slot ms:', stats.get('totalSlotMs'))
for step in stats.get('queryPlan', []):
    print(f"Stage: {step['name']}, Records: {step.get('recordsWritten', 0)}, Shuffle: {step.get('shuffleOutputBytesSpilled', 0)}")
"

# Sık karşılaşılan performans sorunları:
# 1. Data skew - bir partition/key'de çok fazla veri
# 2. Cross join - kartezyen çarpım, kaçınılmalı
# 3. DISTINCT yerine GROUP BY kullanımı
# 4. Nested subquery yerine CTE tercih et

# Bu sorgu yerine:
# SELECT * FROM table WHERE id IN (SELECT id FROM other_table)

# Bunu kullan:
bq query --use_legacy_sql=false 
'SELECT t.* FROM `ecommerce_analytics.orders` t
INNER JOIN `ecommerce_analytics.vip_customers` v
  ON t.customer_id = v.customer_id
WHERE t.order_date >= "2024-01-01"'

Backup ve Export Stratejileri

BigQuery verilerini düzenli olarak export etmek, hem yedekleme hem de downstream sistem entegrasyonu için gerekli.

# Tabloyu GCS'e export et (Parquet formatında, sıkıştırılmış)
bq extract 
  --destination_format=PARQUET 
  --compression=SNAPPY 
  ecommerce_analytics.orders 
  gs://your-backup-bucket/backups/orders/$(date +%Y%m%d)/orders_*.parquet

# JSON formatında export
bq extract 
  --destination_format=NEWLINE_DELIMITED_JSON 
  --compression=GZIP 
  ecommerce_analytics.orders 
  'gs://your-backup-bucket/exports/orders_'$(date +%Y%m%d)'.json.gz'

# Partition bazlı export (sadece dün)
bq extract 
  --destination_format=PARQUET 
  "ecommerce_analytics.orders_partitioned$$(date -d 'yesterday' +%Y%m%d)" 
  gs://your-backup-bucket/daily/$(date -d 'yesterday' +%Y%m%d)/orders_*.parquet

# Bu komutları cron ile otomatikleştir
# /etc/cron.d/bigquery-backup
echo "0 3 * * * root /usr/local/bin/bq-daily-export.sh >> /var/log/bq-backup.log 2>&1" 
  > /etc/cron.d/bigquery-backup

BigQuery ML ile Basit Tahmin Modeli

SQL bilen biri olarak doğrudan BigQuery içinde ML modeli eğitmek güzel bir özellik. Ayrı bir ML altyapısı kurmana gerek kalmıyor.

# Logistic regression ile churn prediction modeli
bq query --use_legacy_sql=false 
'CREATE OR REPLACE MODEL `ecommerce_analytics.churn_model`
OPTIONS(
  model_type = "logistic_reg",
  labels = ["churned"],
  auto_class_weights = true
) AS
SELECT
  f.customer_id,
  f.days_since_last_order,
  f.total_orders_last_90d,
  f.avg_order_value,
  f.total_spend_lifetime,
  f.return_rate,
  IF(f.days_since_last_order > 90, 1, 0) as churned
FROM `ecommerce_analytics.customer_features` f
WHERE f.customer_tenure_days > 180'

# Model değerlendirme
bq query --use_legacy_sql=false 
'SELECT
  *
FROM ML.EVALUATE(
  MODEL `ecommerce_analytics.churn_model`,
  (SELECT * FROM `ecommerce_analytics.customer_features_test`)
)'

# Prediction çalıştır
bq query --use_legacy_sql=false 
'SELECT
  customer_id,
  predicted_churned,
  predicted_churned_probs
FROM ML.PREDICT(
  MODEL `ecommerce_analytics.churn_model`,
  (SELECT * FROM `ecommerce_analytics.customer_features` WHERE is_active = true)
)
ORDER BY predicted_churned_probs[OFFSET(1)].prob DESC
LIMIT 100'

Sonuç

BigQuery, doğru yapılandırıldığında ve doğru kullanıldığında gerçekten güçlü bir analitik platform sunuyor. Bu yazıda ele aldığımız konuları özetlemek gerekirse:

  • Dataset ve tablo organizasyonunu baştan doğru kur. Sonradan taşımak zahmetli.
  • Partition ve clustering olmadan büyük tablolarla çalışma. Hem para ödersin hem de beklede kalırsın.
  • Schema’yı her zaman manuel tanımla, autodetect’e production’da güvenme.
  • Maliyet monitoring’ini günden itibaren kur. Sonradan fatura sürprizi yaşamak istemezsin.
  • IAM’ı granüler yapılandır. roles/bigquery.admin verip geçme.
  • Scheduled queries ile tekrarlayan raporları otomatize et.
  • --dry_run flagini sorgunu çalıştırmadan önce alışkanlık haline getir.

Deneyimlerime göre BigQuery ile ilgili en büyük sorunlar teknik değil, organizasyonel. Kim hangi veriye erişebilir, hangi sorgu ne kadar maliyet çıkarır, kim bu maliyetten sorumlu gibi sorular cevaplandıktan sonra teknik kısımlar oldukça yerli yerine oturuyor. Bu soruların cevaplarını önce netleştir, sonra teknik kuruluma geç.

Bir yanıt yazın

E-posta adresiniz yayınlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir