51–60 of 81 posts
Besides XML, JSON is about the worst way to format tabular data, right?
I think I write this under every article about DuckDB, but it's become an indispensable tool for me. I used to abuse Excel because going from Excel to a script to process some data was too much friction, but with DuckDB the friction is gone: loading CSV and Parquet (and now JSON) files is a snap, you can create and persist any tables you want, the SQL dialect has lots of useful sugar.
The benefit of “in process” isn’t really clicking for me. We had Pandas or similar if we wanted to load and transform some data in memory. SQL is nicer than Pandas APIs but not sure that’s a killer feature? If we have a lot of data and multiple people working with it then it makes sense to centralise it in a database or warehouse where it’s then easy to access via SQL anyway. We can query files on S3 with it and have…
Sure, the complete and most up to date version of your data can be stored in a warehouse/database but when you're potentially slicing/dicing/filtering/sorting/exploring/munging data, it can get quite expensive to have your warehouse/database servicing these requests. Even if it's a cloud/modern warehouse like Snowflake. This would be especially true if you're doing analytics on a non-OLAP database.
With duckdb you could pull down the subset of data you're working on from your warehouse/db/datalake, and then perform the last-minute-analytics "in-process". That might be in your notepad, browser (WASM), etc... As a result you can expect some pretty amazing query performance since it's all happening locally, on a subset of the entire data that you've selected.
Then of course if you still wanted to use pandas you can point it at duckdb and allow pandas to fill in the deficiencies of SQL (while still potentially pushing-down some SQL to duckdb). Then of course you can take those dataframes and push them right back into duckdb instead of writing back out disk.
> We can query files on S3 with it and have the processing locally, but then we have network latency because compute and storage are further apart.
If you're files on S3 in a sensible form (hive, iceberg, etc), then you can also use duckdb to pull only the data you need from your bucket and work on it locally.
Running into a couple issues right out of the gate: 1) Needed to increase maximum_object_size 2) Unexpected yyjson tag in ValTypeToString Couldn't find a reference anywhere to that error. Loads into Snowflake without a hitch - which is where I normally query large JSON files.
This is really cool! With their Postgres scanner[0] you can now easily query multiple datasources using SQL and join between them (i.e. Postgres table with JSON file). Something I previously strived to build with OctoSQL[1]. There's even predicate push-down to the underlying databases (for Postgres)! It's amazing to see how quickly DuckDB is adding new features. Not a huge fan of C++, which is right now used for auth…
Consider polars for rust. Much, much faster with fewer resources than Duckdb or datafusion in my experience.
Earlier quoted context omitted.
Consider polars for rust. Much, much faster with fewer resources than Duckdb or datafusion in my experience.
Polars is a dataframe library, no? That's a quite different use-case.
I think I write this under every article about DuckDB, but it's become an indispensable tool for me. I used to abuse Excel because going from Excel to a script to process some data was too much friction, but with DuckDB the friction is gone: loading CSV and Parquet (and now JSON) files is a snap, you can create and persist any tables you want, the SQL dialect has lots of useful sugar.
The benefit of “in process” isn’t really clicking for me. We had Pandas or similar if we wanted to load and transform some data in memory. SQL is nicer than Pandas APIs but not sure that’s a killer feature? If we have a lot of data and multiple people working with it then it makes sense to centralise it in a database or warehouse where it’s then easy to access via SQL anyway. We can query files on S3 with it and have…
It’s a local columnar engine that I can use inside a Jupyter notebook. This lowers my cost of iteration tremendously.
Yes I can query data from Postgres and munge with Pandas.
But what if I need to iterate on a large set of parquet files (mine is 200gb on my local machine, Hive partitioned, over a billion records) and munge them with complex SQL with a high perf engine? And seamlessly join with other smaller local datasets (there are always smaller datasets that contain metadata) in CSV, Pandas and JSON format in the same SQL statement?
This is a surprisingly common use case in a lot of data science work and prior to DuckDB you could not do it easily, ergonomically or quickly with a single tool. The authors of DuckDB talked to lots of data scientists to learn their pain points and the final product shows that they really listened well.
I think I write this under every article about DuckDB, but it's become an indispensable tool for me. I used to abuse Excel because going from Excel to a script to process some data was too much friction, but with DuckDB the friction is gone: loading CSV and Parquet (and now JSON) files is a snap, you can create and persist any tables you want, the SQL dialect has lots of useful sugar.
The benefit of “in process” isn’t really clicking for me. We had Pandas or similar if we wanted to load and transform some data in memory. SQL is nicer than Pandas APIs but not sure that’s a killer feature? If we have a lot of data and multiple people working with it then it makes sense to centralise it in a database or warehouse where it’s then easy to access via SQL anyway. We can query files on S3 with it and have…
Besides XML, JSON is about the worst way to format tabular data, right?
Was looking for something to read json files which will house the config via SQL, i.e. a human readable db as an alternative to what the BI tool is using for it's config persistence.
Will give DuckDB a go, thanks for posting!
Ah I was looking for exactly this the other day. I'm try to build a git based interface to our BI tool so that we can get config for our reports in source control instead of configuration in a db. Was looking for something to read json files which will house the config via SQL, i.e. a human readable db as an alternative to what the BI tool is using for it's config persistence. Will give DuckDB a go, thanks for postin…
If that’s of interest, you can read our launch HN here: https://news.ycombinator.com/item?id=28304781
One of our community members has built a pretty cool Duck DB + dbt + evidence data stack that you can run entirely in GitHub codespaces. He’s calling it modern data stack in a box.
You can see that repo here: https://github.com/matsonj/nba-monte-carlo