Homework 02 — Parquet & Columnar Storage

Task 1 — Repartition by type and re-benchmark

Re-run the L02 dataset, but this time partition by type instead of date. Use pq.write_to_dataset with partition_cols=["type"]. Your output directory should look like:

data/parquet_by_type/
  type=PushEvent/part-0.parquet
  type=CreateEvent/part-0.parquet
  ...

Then repeat the DuckDB benchmark from the lab:

import duckdb, time
# Query 1: count PushEvents
t = time.perf_counter()
n = duckdb.query(
    "SELECT COUNT(*) FROM 'data/parquet_by_type/type=PushEvent/*.parquet'"
).fetchone()[0]
print(f"by-type count PushEvent: {round(time.perf_counter()-t, 4)} s — {n:,} rows")

# Query 2: count all types
t = time.perf_counter()
res = duckdb.query(
    "SELECT type, COUNT(*) AS n FROM 'data/parquet_by_type/**/*.parquet' GROUP BY type ORDER BY n DESC"
).fetchdf()
print(f"by-type GROUP BY: {round(time.perf_counter()-t, 4)} s")

✓ Self-check: the PushEvent-count query opens exactly one directory and is sub-millisecond (partition pruning). The "top-10 types by count" query has to scan every partition and is roughly as slow as — or slower than — the date-partitioned version.

Task 2 — Compression face-off

Write the same one-hour Arrow table with three different compression codecs and one uncompressed baseline. Record the resulting file sizes and the read latencies:

import pyarrow.parquet as pq, time, pathlib

# Assume `table` is already built from one gharchive hour
for codec in ["snappy", "gzip", "zstd", None]:
    name = codec or "none"
    out  = pathlib.Path(f"data/cmp/{name}.parquet")
    out.parent.mkdir(parents=True, exist_ok=True)
    pq.write_table(table, out, compression=codec)

    t = time.perf_counter()
    pq.read_table(out)
    elapsed = round(time.perf_counter() - t, 4)
    size_mb = round(out.stat().st_size / 1_048_576, 2)
    print(f"{name:8s}  {size_mb:6.2f} MB   read {elapsed} s")

Build a small Markdown or plain-text table of your results. Then answer in one sentence: given a read-heavy analytics workload, which codec would you choose and why?

✓ Self-check: zstd produces the smallest file; snappy is the fastest to read; gzip is typically the worst trade-off for analytics (decent compression, slowest decompression). Uncompressed is fastest to read but significantly larger.

Task 3 — Read with DuckDB directly from JSON.gz

Without converting anything, point DuckDB at your raw JSON.gz files and run the same aggregation query you've been benchmarking:

SELECT type, COUNT(*) AS n
FROM read_json_auto('data/raw/*.json.gz')
GROUP BY type
ORDER BY n DESC;

Time it. Then run the same query against your date-partitioned Parquet dataset:

SELECT type, COUNT(*) AS n
FROM 'data/parquet/date=2024-01-01/*.parquet'
GROUP BY type
ORDER BY n DESC;

Record both elapsed times. Calculate the speedup ratio.

✓ Self-check: Parquet is at least 10× faster, often 50–100×. The JSON.gz version is slow not just because of JSON parsing overhead — it is slow because the row-oriented layout forces DuckDB to materialise every column for every row even though the query only needs type.