71–80 of 81 posts
If you ever need to join two large dataframes, but are OOMing on the join, write them to disk as parquet files then use DuckDB to do the join. It's amazing what you can do on one machine thanks to DuckDB.
This isn't unique to duckdb. Almost all databases allow for sorting and joins of large tables that don't fit into memory.
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…
Are you talking about metabase by any chance?
My plan is to poc a tool that allows you to edit metabase config as files and secondly something that can replicate cloud instances to other environments like local docker image or staging instance
What's funny, is I've wanted something similar as a feature for "Azure Data Studio" that can open/use a CSV file and query it as a sqlite table. Basically an auto-import to a temp or in-memory db/table that you can then query against. Would just be a nice gui feature to have.
JSON is a row based file format. It doesn't allow query engines to skip rows or skip columns when running queries, so all data needs to get read into memory. That's really inefficient.
Column based file formats allow for query engines to skip entire columns of data (e.g. Parquet). Parquet also stores metadata on row groups and allows query engines to skip rows when reading data. These performance enhancements can speed up queries from 0x - 100x or more (depends on how much data is skipped).
Data Lakehouse storage systems abstract the file metadata to a separate layer, which is even better than storing it in the file footer like Parquet does.
This DuckDB functionality is cool, but I think it's best to use it to convert JSON files to Parquet / a Lakehouse storage system, and then query them. JSON is a really inefficient file format for running queries.
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…
Very little amount and unclear pushdown filters are one of the issues, not handling certain data types and thus not being able to scan the table (even if the column in question isn't used) is another.
I think that DuckDB is also missing a PostgreSQL logical replication driver to continuously replicate a subset of tables you want to run stats on.
Syncing the full table every time is too slow.
Mind you this isn’t appropriate for most cases. But I love the idea of “you start with text file. You end with text file. All the database stuff, indexes, etc. are just a detail.” Often I find that the database wants to be the authority and that makes working with different formats a bit uncomfortable.
We’re currently building real-time apis backed by terabytes of compressed parquet… hundreds of billions of ‘rows’… in exactly this fashion using polars. It amazes us at every turn. Join us and help!
Do you mean polars reading Parquet into DuckDB to process that amount of data?
Welcome to the gang! :) https://github.com/multiprocessio/dsq#comparisons Realistically though aside from the variety of input formats that DuckDB doesn't (yet) support, I think most people should probably use DuckDB or ClickHouse-local. Tools like dsq can provide broader support or a slightly simpler UX in some cases (and even that is obviously debatable). But I think the future is more the DuckDB or ClickHouse-loca…
`alias dckr='docker run --rm -it -v $(pwd):/data -w /data duckerlabs/ducker'`
then `dckr` gives you a DuckDB shell with PRQL, httpfs, json, parquet, postgres, sqlite, and substrait enabled.
For example, to get the first 5 lines of a csv file named "albums.csv", you could run it with PRQL
```dckr -c 'from `albums.csv` | take 5;'```
Very nice! Does anyone know if we can query duckdb with a pandas dialect?
See my comment above: https://news.ycombinator.com/item?id=35027712
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.
Thanks for trying it out! Could you perhaps open an issue [1] or share the file with us so we could investigate the problem? [1] https://github.com/duckdb/duckdb/issues
Meanwhile, doing the same with Jq ("jq '.[0]'") completed in 11 seconds and consumed about 2.8GB RAM.
I love DuckDB, but it does seem like something isn't right here.
Earlier quoted context omitted.
We’re currently building real-time apis backed by terabytes of compressed parquet… hundreds of billions of ‘rows’… in exactly this fashion using polars. It amazes us at every turn. Join us and help!
What project? Do you mean polars reading Parquet into DuckDB to process that amount of data?