Solutions — Homework 02

Solution to Task 1 — Repartition by type

Swapping the partition key is a one-line change — replace partition_cols=["date"] with partition_cols=["type"]:

pq.write_to_dataset(
    table,
    root_path="data/parquet_by_type",
    partition_cols=["type"],
    compression="snappy",
)

The resulting directory tree:

data/parquet_by_type/
  type=PushEvent/part-0.parquet
  type=CreateEvent/part-0.parquet
  type=PullRequestEvent/part-0.parquet
  type=WatchEvent/part-0.parquet
  ...  (~20 partitions for a typical day)

Illustrative benchmark numbers (your values will differ by machine and hour):

Query	date-partitioned	type-partitioned
COUNT(*) WHERE type='PushEvent'	~50 ms	~0.8 ms ← partition pruning
GROUP BY type (all types)	~50 ms	~60 ms ← must scan every partition

Common mistake: assuming the right partition key is always "the column you filter on most". For a balanced workload that mixes single-type counts with cross-type rollups, date is usually a better default. Why? Because date queries are also well-served by the type-column statistics within each date partition, giving you predicate pushdown for free. Type-partitioning only helps queries that filter to a single type and hurts all others.

Solution to Task 2 — Compression face-off

Complete script:

import pyarrow as pa
import pyarrow.parquet as pq
import gzip, json, pathlib, time

# Build table from one hour (reuse your to_parquet implementation)
rows = []
with gzip.open("data/raw/2024-01-01-15.json.gz", "rt") as f:
    for line in f:
        ev = json.loads(line)
        rows.append({
            "type":       ev["type"],
            "actor":      ev["actor"]["login"],
            "repo":       ev["repo"]["name"],
            "created_at": ev["created_at"],
        })
table = pa.Table.from_pylist(rows)

pathlib.Path("data/cmp").mkdir(parents=True, exist_ok=True)

for codec in ["snappy", "gzip", "zstd", None]:
    name = codec or "none"
    out  = pathlib.Path(f"data/cmp/{name}.parquet")
    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")

Expected output (illustrative — verify with your own data):

Compression	Size	Read latency
snappy	2.1 MB	35 ms
zstd	1.4 MB	45 ms
gzip	1.5 MB	80 ms
none	6.2 MB	30 ms

Recommended choice for read-heavy analytics: snappy. It is the fastest to decompress and its file size is good enough (roughly 3× smaller than uncompressed). If storage cost is the constraint and CPU is cheap, choose zstd — it achieves better compression ratios at higher CPU cost, but modern CPUs make the difference negligible for most batch workloads.

Common mistake: picking gzip "because everyone uses it in CSV pipelines". Gzip carries over from the CSV world where it was the only option. For Parquet, gzip is typically the worst trade-off: slower decompression than snappy with only marginally better compression than zstd.

Solution to Task 3 — DuckDB on raw JSON.gz vs Parquet

Timing script:

import duckdb, time

# Query against raw JSON.gz
t = time.perf_counter()
r1 = duckdb.query(
    "SELECT type, COUNT(*) AS n FROM read_json_auto('data/raw/*.json.gz') GROUP BY type ORDER BY n DESC"
).fetchdf()
t_json = round(time.perf_counter() - t, 3)

# Same query against partitioned Parquet
t = time.perf_counter()
r2 = duckdb.query(
    "SELECT type, COUNT(*) AS n FROM 'data/parquet/date=2024-01-01/*.parquet' GROUP BY type ORDER BY n DESC"
).fetchdf()
t_parquet = round(time.perf_counter() - t, 3)

print(f"JSON.gz:  {t_json} s")
print(f"Parquet:  {t_parquet} s")
print(f"Speedup:  {round(t_json / t_parquet)}x")

Typical results:

JSON.gz:  2.5 s
Parquet:  0.05 s
Speedup:  50x

Why is JSON.gz slow? Two compounding reasons:

1. Row-oriented layout. To compute type counts, DuckDB must materialise every column for every row from the JSON — actor, repo, created_at, and all nested fields — even though the query only uses type. Column pruning is impossible in a row-oriented format.
2. JSON parsing overhead. Each line requires tokenisation and string allocation. Parquet column data, by contrast, arrives as a typed binary buffer that can be scanned with SIMD instructions.

Common mistake: attributing all the slowness to "JSON is text". The row- oriented layout is actually the bigger culprit. Even a binary row-oriented format (like uncompressed MessagePack) would still be slower than Parquet for column-aggregation queries, because it can't skip the columns you don't need.