🔵 BigQuery Deep Dive

Partitioning, clustering, optimization, cost control, BigQuery ML

Google Cloud ACE · ACE · ⏱️ 22 min · ❓ 5 savol

🗄️ Partitioning

Table partitioning — ma'lumotlarni fizik bo'laklarga ajratish. Ingestion time partitioning (default). Column partitioning: DATE, TIMESTAMP, INTEGER. Partition pruning — WHERE filter da partition ustunidan foydalanish → skanerlangan data kamayadi → xarajat kamayadi. Max 4000 partition.

⚡ Clustering

Partition ichida qo'shimcha saralash. Cluster columns: 1-4 ta ustun. Clustered column ga filter yoki aggregation — block pruning orqali tezlik. Partitioning + Clustering birgalikda: partitioning macro-level, clustering micro-level. Auto-reclustering — BigQuery o'zi boshqaradi.

💰 Cost Optimization

On-demand: skanerlangan TB uchun ($6.25/TB). Capacity pricing: slot commitments (monthly/annual). `SELECT *` — barcha ustunlarni skanerleydi = qimmat! Materialized Views — tez-tez ishlatilgan query natijasini cache. BI Engine — in-memory query acceleration. `bq --dry_run` — narxni oldindan hisoblash.

🤖 BigQuery ML

SQL da ML model train va serve qilish. Supported models: Linear/Logistic Regression, k-Means clustering, Matrix Factorization, XGBoost, TensorFlow import, Autoencoder. `CREATE MODEL`, `ML.PREDICT`, `ML.EVALUATE`. Data SQL da — Python/Jupyter siz. Proje ko'lami: BigQuery storage.

📊 Partitioning vs Clustering

Xususiyat	Partitioning	Clustering
Mexanizm	Fizik fayl bo'laklash	Partition ichida saralash
Maqsad	Macro data skipping	Micro data skipping (block)
Filter ustuni	Partition column (WHERE)	1-4 cluster column (WHERE/GROUP)
Max	4000 partition	4 ustun
Birgalikda	Ha, birgalikda optimal	Ha, birgalikda optimal

💡 Asosiy nuqtalar

Partitioning: fizik bo'laklash (DATE, INT range)
Clustering: partition ichida saralash, 1-4 ustun
Partition pruning: WHERE filtr → less data scan → arzon
SELECT *: barcha ustun = qimmat, aniq ustun tanlang
BigQuery ML: SQL da model train/predict
Materialized View: tez-tez query natijasini cache

🎯 Imtihon maslahatlari

Partition column: WHERE da ishlatilmasa → barcha partition skanerlanadi!
Cluster column: GROUP BY yoki WHERE da ishlatilsa block pruning ishlaydi
SELECT * → barcha ustun, Columnar storage da faqat kerakli ustunni qiling
BigQuery ML — data scientist larga SQL bilan ML: Python ko'nikmasiz
bq --dry_run — real query ishlatmasdan narxni ko'rish (cost estimate)

⚠️ Ko'p adashadigan

Partitioning va clustering bir xil deb o'ylash — partitioning=fayl level, clustering=block level
Materialized View = regular View deb hisoblash: Materialized = hisoblangan va saqlangan, regular = har run da hisoblaydi
BigQuery ML barcha ML model turlarini qo'llab-quvvatlaydi deb o'ylash — cheklangan model turlari

🧠 Eslab qolish: "PC Power" = Partitioning (fayl bo'lish) + Clustering (ichida saralash) = BigQuery optimization power couple. "SELECT * = Pul yeb qo'yadi" — har doim aniq ustun tanlang