Columnar Databases: BigQuery & DuckDB

Database columnar menyimpan data per kolom, bukan per baris. Ini membuatnya sangat cepat untuk query analitik yang hanya butuh beberapa kolom dari jutaan baris.

Row-based vs Columnar Storage

// Row-based (PostgreSQL, MySQL)
// Baris disimpan berurutan di disk
Row 1: [id=1, name="Budi", amount=150000, date="2024-01-15"]
Row 2: [id=2, name="Siti", amount=275000, date="2024-01-16"]

// Columnar (BigQuery, DuckDB, Parquet)
// Kolom disimpan berurutan di disk
id:     [1, 2, 3, 4, 5, ...]
name:   ["Budi", "Siti", "Ahmad", ...]
amount: [150000, 275000, 89000, ...]

// SELECT SUM(amount) FROM orders
// Row-based: harus baca SEMUA kolom di setiap baris
// Columnar: hanya baca kolom "amount" → jauh lebih cepat!

DuckDB — SQLite untuk Analitik

DuckDB adalah database analitik embedded (tanpa server) yang bisa langsung query file Parquet, CSV, dan JSON.

import duckdb

# Query langsung dari file Parquet tanpa import!
result = duckdb.sql("""
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS revenue,
        COUNT(*) AS order_count
    FROM 'orders/*.parquet'
    GROUP BY 1
    ORDER BY 1
""").df()  # .df() konversi ke Pandas DataFrame

# Query CSV
duckdb.sql("SELECT * FROM 'data/users.csv' WHERE plan = 'pro' LIMIT 10")

# Gabung multiple sumber
duckdb.sql("""
    SELECT u.name, SUM(o.amount)
    FROM 'users.parquet' u
    JOIN 'orders/*.parquet' o ON u.id = o.user_id
    GROUP BY u.name
""")

BigQuery — Warehouse di Cloud

Google BigQuery adalah fully-managed data warehouse. Bayar per query (jumlah data yang di-scan), bukan per server.

-- BigQuery SQL (standard SQL)
-- Query petabytes data dalam hitungan detik

SELECT
    EXTRACT(MONTH FROM order_date) AS month,
    product_category,
    SUM(amount) AS total_revenue,
    COUNT(DISTINCT user_id) AS unique_buyers
FROM `project.dataset.orders`
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY 1, 2
ORDER BY total_revenue DESC;

-- Partitioned table: HEMAT biaya query
-- Hanya scan partisi yang relevan
CREATE TABLE `project.dataset.orders`
PARTITION BY DATE(order_date)
CLUSTER BY product_category
AS SELECT * FROM raw_orders;

Kapan Menggunakan Apa?