Super-Structured Data: Rethinking the Schema

Re:Super-Structured Data: Rethinking the Schema by simonw on Tuesday May 17, 2022 @05:52PM (#31416726)

> The idea here is that instead of manually creating schemas, what if the schemas were automatically created for you? When something doesn’t fit in a table, how about automatically adding columns for the missing fields?

I've been experimenting with this approach against SQLite for a few years now, and I really like it.

My sqlite-utils package does exactly this. Try running this on the command line:

    brew install sqlite-utils
    echo '[
      {"id": 1, "name": "Cleo"},
      {"id": 2, "name": "Azy", "age": 1.5}
    ]' | sqlite-utils insert /tmp/demo.db creatures - --pk id
    sqlite-utils schema /tmp/demo.db

It outputs the generated schema:

    CREATE TABLE [creatures] (
       [id] INTEGER PRIMARY KEY,
       [name] TEXT,
       [age] FLOAT
    );

When you insert more data you can use the --alter flag to have it automatically create any missing columns.

Full documentation here: https://sqlite-utils.datasette.io/en/stable/cli.html#inserti...

It's also available as a Python library: https://sqlite-utils.datasette.io/en/stable/python-api.html

[ Parent | on HN ]

Re:Super-Structured Data: Rethinking the Schema by mccanne on Tuesday May 17, 2022 @07:45PM (#31417550)

Simon, brilliant observations here and kudos on sqlite-utils.

I'm all for layers, a fundamental approach in our field to tame complexity. And the SQL model and SQLite have stood the test of time and are solid foundations.

I'm just wondering could we be stuck in a local maximum where the presumed answer is always the relational model? Maybe if we built the relational model on top of a different set of lower-level primitives (a type system instead of schemas and tables) we could escape local maximum we're stuck in? Just a thought.

There are a few somewhat ad hoc perf measurements here regarding the sqlite-utils and sqlite... https://zed.brimdata.io/docs/commands/zq/#73-performance-com...

I'm not a SQLite expert so if I did something wrong, please holler and let me know :)

[ Parent | on HN ]

Re:Super-Structured Data: Rethinking the Schema by simonw on Wednesday May 18, 2022 @12:41AM (#31419287)

Cool, I didn't realize you used sqlite-utils for your performance demo!

It's not particularly designed for speed - it should be fast as far as Python code goes (I use some generator tricks to stream data and avoid having to load everything into memory at once) but I wouldn't expect "sqlite-utils insert" to win any performance competitions with tools written in other languages.

Those benchmarks against sqlite itself are definitely interesting. I'm looking forward to playing with the "native ZNG support for Python" mentioned on https://github.com/brimdata/zed/blob/main/docs/libraries/pyt... when that becomes available.

[ Parent | on HN ]

Re:Super-Structured Data: Rethinking the Schema by simonw on Wednesday May 18, 2022 @12:57AM (#31419367)

One trick I sometimes use is to pipe it through pv to get a progress bar:

    % pv conn.json | sqlite-utils insert conn.db conn - --nl
    36.9MiB 0:00:06 [5.81MiB/s] [==>             ]  9% ETA 0:01:00

[ Parent | on HN ]