5 ms·

Nice writeup, but is jq & GNU parallel or a notebook full of python spaghetti the best (least complex) tool for the job? DuckDB might be nice here, too. See ht

Nice writeup, but is jq & GNU parallel or a notebook full of python spaghetti the best (least complex) tool for the job? DuckDB might be nice here, too. See https://duckdb.org/2023/03/03/json.html https://duckdb.org/2023/03/03/json.html

nojito 4y ago
Calling Dask python spaghetti is quite hilarious. That spaghetti can auto scale to hundreds of machines without skipping a beat. Which is far more useful than the other tools you mentioned which are only useful for one off tasks.
- isoprophlex 4y ago
  Parallelizing a turd across hundreds of machines doesn't mean you're doing something genius, it just means you now have a hundred machines that have to deal with your shit.
samwillis 4y ago
DuckDB is awesome. As a comparison, I have a dataset that starts life as a 35gb set of json files. Imported into Postgres it's ~6gb, and a key query I run takes 3 min 33 seconds. Imported into DuckDB (still about ~6gb for all columns), the same SQL query takes 1.1 second! The key thing is that the columns (for all rows) the query scans total only about 100mb, so DuckDB has a lot less to scan. But on top of that it's vectorised query execution is incredibly quick. https://mobile.twitter.com/samwillis/status/1633213350002798593 https://mobile.twitter.com/samwillis/status/1633213350002798...
- pletnes 4y ago
  I found that exporting big tables as a bunch of parquet files is faster and uses less memory than duckdb’s internal format.
- samus 4y ago
  PostgreSQL would probably be way faster if you add proper indexes.
  - mattpallissard 4y ago
    And not store the data as Json/jsonb to begin with.
pletnes 4y ago
Duckdb is fantastic. Doesn’t need a schema, either.
e12e 4y ago
There's also clickhouse/clickhouse local - eg: https://clickhouse.com/blog/worlds-fastest-json-querying-tool-clickhouse-local https://clickhouse.com/blog/worlds-fastest-json-querying-too... https://clickhouse.com/docs/en/operations/utilities/clickhouse-local https://clickhouse.com/docs/en/operations/utilities/clickhou... https://clickhouse.com/blog/getting-data-into-clickhouse-part-2-json https://clickhouse.com/blog/getting-data-into-clickhouse-par...

nojito 4y ago

Calling Dask python spaghetti is quite hilarious. That spaghetti can auto scale to hundreds of machines without skipping a beat. Which is far more useful than the other tools you mentioned which are only useful for one off tasks.

isoprophlex 4y ago
Parallelizing a turd across hundreds of machines doesn't mean you're doing something genius, it just means you now have a hundred machines that have to deal with your shit.

samwillis 4y ago

DuckDB is awesome. As a comparison, I have a dataset that starts life as a 35gb set of json files. Imported into Postgres it's ~6gb, and a key query I run takes 3 min 33 seconds. Imported into DuckDB (still about ~6gb for all columns), the same SQL query takes 1.1 second! The key thing is that the columns (for all rows) the query scans total only about 100mb, so DuckDB has a lot less to scan. But on top of that it's vectorised query execution is incredibly quick. https://mobile.twitter.com/samwillis/status/1633213350002798593 https://mobile.twitter.com/samwillis/status/1633213350002798...

pletnes 4y ago
I found that exporting big tables as a bunch of parquet files is faster and uses less memory than duckdb’s internal format.
samus 4y ago
PostgreSQL would probably be way faster if you add proper indexes.
- mattpallissard 4y ago
  And not store the data as Json/jsonb to begin with.

pletnes 4y ago

Duckdb is fantastic. Doesn’t need a schema, either.

e12e 4y ago

There's also clickhouse/clickhouse local - eg: https://clickhouse.com/blog/worlds-fastest-json-querying-tool-clickhouse-local https://clickhouse.com/blog/worlds-fastest-json-querying-too... https://clickhouse.com/docs/en/operations/utilities/clickhouse-local https://clickhouse.com/docs/en/operations/utilities/clickhou... https://clickhouse.com/blog/getting-data-into-clickhouse-part-2-json https://clickhouse.com/blog/getting-data-into-clickhouse-par...