Human, SQL, and S3-friendly archives? | Hacker News Reader

Human, SQL, and S3-friendly archives?

4 pointsliquidcarbon7 comments

Suppose there's a collection of 1000 files that can be compressed into blobs of ~1 MB per file (gz or zstd).

It would be cool to be able to put all of these blobs into a CSV-like file shown below, throw it into S3-compatible storage, and query for specific blobs using SQL-like queries, without reading the whole thing. Is this possible?

  byte offset,id,desc,blob  
            0, 1,ABCD,<blob 1>  
      1234567, 2,EFGH,<blob 2>  
      2345678, 3,KLMN,<blob 3>  
      ...  
    987654321,999,XYZ,<blob 999>

Desired features:

- some columns remain human readable

- can use SQL to retrieve specific row(s) without reading everything (is that how it is for Parquet querying from S3?)

- ability to append (updates not necessary)

There's Parquet, but it's not human-readable, and doesn't like appends. And I don't need to be clever about columnar compression and row groups.

The goal is to better deal with very large amounts of relatively small files. Buckets with millions of objects are not fun.

Sounds like a job for the AWS Athena service. Put all the files into S3 and you can query across the whole collection. See https://aws.amazon.com/athena/

DuckDB's file format is OLAP-based but supports CRUD operations. I've created DBs with it with 10s of millions of records and gotten amazing query speeds.

Efficient querying using indices over S3 for their format isn't something I've looked into just yet.

DuckDB is amazing, I'm using it every day.

It would be marginally useful here though. I really just want to get the binary compressed blob. Fun fact: MS SQL Server has "DECOMPRESS" and DuckDB does not.

Wouldn't that blob not be human readable?

no, but it's one step away with gzip.decompress or similar; if we know the exact position, we can retrieve it with range reads, roughly like this:

aws s3api get-object --bucket mybucket --key partially_human_friendly_blob --range bytes=start-end | cut or awk | gunzip

How fixed are your requirements for human readability and ~1MB per file?

If you relax those, a data lake formatted in Delta or Iceberg is exactly what you're asking for.

It's actually not hard to implement if you separate the index file from the blobs... but the append part probably won't be feasible with S3

Reply on news.ycombinator.com