Extract zips on the fly

[RobinL]

Feature request: convert zipped OS CSVs to Parquet without fully extracting first

Summary

ukam_os_builder currently has an extract stage that extracts downloaded OS zip archives before converting CSVs to Parquet. We should consider switching this stage to read CSV members directly from the zip and write Parquet on the fly.

The suggested approach is to use DuckDB's Python fsspec filesystem support with fsspec's zip filesystem. This avoids the DuckDB zipfs community extension and avoids materialising the full decompressed CSV on disk.

Why

OS downloads can contain multi-GB CSVs inside zip files. Extracting first means we temporarily need disk space for:

• the original zip
• the decompressed CSV
• the generated Parquet

Streaming directly from the zip should reduce peak disk usage and simplify cleanup. In a local experiment, it was also faster than extracting first.

Local timings

Experiment file: add_gb_nonaddressableobject.zip

Zip contents included:

CSV member	compressed size	uncompressed size
add_gb_nonaddressableobject.csv	376 MB	2.55 GB
add_gb_nonaddressableobject_rltenty.csv	863 MB	2.91 GB
add_gb_nonaddressableobject_otrclass.csv	7.9 MB	33 MB
add_gb_nonaddressableobject_altadd.csv	2.5 MB	8.3 MB

Comparison of two paths:

CSV member	extract then convert	direct zip via fsspec	result
add_gb_nonaddressableobject.csv	5.29s	2.66s	fsspec about 2.0x faster
add_gb_nonaddressableobject_rltenty.csv	6.39s	3.51s	fsspec about 1.8x faster
add_gb_nonaddressableobject_otrclass.csv	0.172s	0.124s	fsspec slightly faster
add_gb_nonaddressableobject_altadd.csv	0.119s	0.119s	effectively tied

For the two larger files, the extract-then-convert path split as:

CSV member	unzip time	DuckDB CSV to Parquet time	total
add_gb_nonaddressableobject.csv	2.04s	3.15s	5.29s
add_gb_nonaddressableobject_rltenty.csv	3.77s	2.53s	6.39s

Row counts matched across both approaches for the large files:

CSV member	rows
add_gb_nonaddressableobject.csv	3,787,919
add_gb_nonaddressableobject_rltenty.csv	21,239,140

Suggested implementation

Use DuckDB with a registered fsspec zip filesystem during the extract-to-Parquet step:

from pathlib import Path

import duckdb
import fsspec


def zip_csv_member_to_parquet(zip_path: Path, csv_member: str, parquet_path: Path) -> None:
    fs = fsspec.filesystem("zip", fo=str(zip_path))
    parquet_sql = str(parquet_path).replace("'", "''")

    con = duckdb.connect()
    con.register_filesystem(fs)
    try:
        con.execute(
            f"""
            COPY (
                SELECT *
                FROM read_csv(?, header = true, all_varchar = true)
            )
            TO '{parquet_sql}' (FORMAT PARQUET)
            """,
            [f"zip://{csv_member}"],
        )
    finally:
        con.close()

The local experiment used all_varchar = true because DuckDB's default type inference failed on add_gb_nonaddressableobject.csv: column alternatelanguagenumber was inferred as BIGINT, then later contained 40A. The builder may already handle schema/type control elsewhere, but this is worth checking when changing the extract path.

Acceptance criteria

• The extract stage can convert CSV members from OS zip archives directly to Parquet without writing the full decompressed CSV to disk.
• Existing offline behaviour still works when zip archives already exist in the downloads directory.
• Output row counts match the current extract-then-convert implementation.
• The implementation handles multiple CSV members per zip.
• There is either a fallback to the current extraction path or clear error reporting if fsspec zip reading fails.

Notes

• This still decompresses the CSV bytes; it just avoids materialising the whole decompressed CSV as an intermediate file.
• fsspec should probably become an explicit dependency if this approach is adopted.
• A Unix FIFO approach was also tested and worked, but it was much slower locally, so fsspec looks like the better first implementation path.