HN Explorer

Human, SQL, and S3-friendly archives?

4 points by liquidcarbon ↗ HN
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.

8 comments

[ 2.7 ms ] story [ 31.7 ms ] thread
liquidcarbon
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
marklit
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.

liquidcarbon
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.

datadrivenangel
Wouldn't that blob not be human readable?
liquidcarbon
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

datadrivenangel
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.