Anyone both tried duckdb and clickhouse-local?
DuckDB – An in-process SQL OLAP database management system
61–70 of 104 posts
Re: DuckDB – An in-process SQL OLAP database management system
#62Related—others?:
Motherduck Raises $47.5M for DuckDB - https://news.ycombinator.com/item?id=33610218 - Nov 2022 (2 comments)
Modern Data Stack in a Box with DuckDB - https://news.ycombinator.com/item?id=33191938 - Oct 2022 (5 comments)
Querying Postgres Tables Directly from DuckDB - https://news.ycombinator.com/item?id=33035803 - Sept 2022 (38 comments)
Notes on the SQLite DuckDB Paper - https://news.ycombinator.com/item?id=32684424 - Sept 2022 (28 comments)
Show HN: CSVFiddle – Query CSV files with DuckDB in the browser - https://news.ycombinator.com/item?id=31946039 - July 2022 (13 comments)
Show HN: Easily Convert WARC (Web Archive) into Parquet, Then Query with DuckDB - https://news.ycombinator.com/item?id=31867179 - June 2022 (15 comments)
Range joins in DuckDB - https://news.ycombinator.com/item?id=31530639 - May 2022 (24 comments)
Friendlier SQL with DuckDB - https://news.ycombinator.com/item?id=31355050 - May 2022 (133 comments)
Fast analysis with DuckDB and Pyarrow - https://news.ycombinator.com/item?id=31217782 - April 2022 (53 comments)
Directly running DuckDB queries on data stored in SQLite files - https://news.ycombinator.com/item?id=30801575 - March 2022 (23 comments)
Parallel Grouped Aggregation in DuckDB - https://news.ycombinator.com/item?id=30589250 - March 2022 (10 comments)
DuckDB quacks Arrow: A zero-copy data integration between Arrow and DuckDB - https://news.ycombinator.com/item?id=29433941 - Dec 2021 (13 comments)
DuckDB-Wasm: Efficient analytical SQL in the browser - https://news.ycombinator.com/item?id=29039235 - Oct 2021 (58 comments)
Comparing SQLite, DuckDB and Arrow with UN trade data - https://news.ycombinator.com/item?id=29010103 - Oct 2021 (79 comments)
DuckDB is the better SQLite with APIs for Java/Python/R and it's got potential - https://news.ycombinator.com/item?id=28692997 - Sept 2021 (2 comments)
Fastest table sort in the West – Redesigning DuckDB's sort - https://news.ycombinator.com/item?id=28328657 - Aug 2021 (27 comments)
Querying Parquet with Precision Using DuckDB - https://news.ycombinator.com/item?id=27634840 - June 2021 (32 comments)
DuckDB now has a Node.js API - https://news.ycombinator.com/item?id=25289574 - Dec 2020 (4 comments)
DuckDB – An embeddable SQL database like SQLite, but supports Postgres features - https://news.ycombinator.com/item?id=24531085 - Sept 2020 (160 comments)
DuckDB: SQLite for Analytics - https://news.ycombinator.com/item?id=23287278 - May 2020 (67 comments)
Re: DuckDB – An in-process SQL OLAP database management system
#63Earlier quoted context omitted.
This blog post offers a nice summary: https://motherduck.com/blog/six-reasons-duckdb-slaps/
The blog post doesn't really make a comparison between DuckDB and data frame libraries. It mentions that the DuckDB Python bindings can interoperate with Pandas, but it doesn't really explain why you would use DuckDB instead of Pandas, or Polars (which is both faster and more portable than Pandas).
Don't Polars and Pandas both require your entire data to fit in memory?
Re: DuckDB – An in-process SQL OLAP database management system
#64Earlier quoted context omitted.
I'd love to hear any real world experiences of anyone who's tried to run jobs that would usually require a spark cluster on a single machine with loads of cores and memory. How big can you go, and how does speed compare to Spark? (I'm guessing significantly faster from my experience using Duckdb on smaller machines)
I have a single machine EC2 instance with 32 cores and 240GB memory and about 200 GB of partitioned Parquet files. I use DuckDB and Python with complex SQL (window functions, inequality joins, quantile functions etc) to extract data from this data. Because it’s a single machine (no distributed cluster) DuckDB can heavily parallelize and vectorize. I don’t know if I can give you perf numbers but complex analytic queri…
Thanks - very interesting
Re: DuckDB – An in-process SQL OLAP database management system
#65DuckDB is terrific. I'm bullish on its potential for simplifying many big data pipelines. Particularly, it's plausible that DuckDB + Parquet could be used on a large SMP machine (32+ cores and 128GB+ memory) to deal with data munging for 100s of gigabytes to several terabytes, all from SQL, without dealing with Hadoop, Spark, Ray, etc. I have successfully used DuckDB like above for preparing an ML dataset from about…
DuckDB is a relational OLAP store. If you want to do transformations on relational data using SQL then I think nowadays you would look at the modern data stack and do it with DBT. If you have genuinely big and unstructured data then of course you need a cluster and would reach for Spark. If you have smallish data then maybe DuckDB has a role because working with SQL is nicer than Pandas. But a lot of time you actuall…
I am not sure I understand the first comment very well. Are you saying that instead of duckdb use modern data stack? Because DBT and DuckDB don't seem to contradict, but can work together. FWIW, I think the only important breakthrough in the "modern data stack" is really dbt. The rest, nothing modern about it
Re: DuckDB – An in-process SQL OLAP database management system
#66Isn’t SQLite adding features for analytic queries that should take the wind out of the sails of duckdb?
Not really. There was an academic project around this but I've not seen signs that it will be integrated with SQLite core: https://simonwillison.net/2022/Sep/1/sqlite-duckdb-paper/
The key thing to consider here is trade-offs.
Analytical databases tend to be optimized for analytical queries at the expense of fast atomic read-write transactions.
SQLite is mainly used in situations where fast atomic read-write transactions are key - that's why it's used in so many mobile phone applications, for example.
It's not going to grow analytical-query-at-scale capabilities if that means negatively impacting the stuff it's really good at already.
Re: DuckDB – An in-process SQL OLAP database management system
#67We just switched to DuckDB from Postgres to analyze AWS billing data and wrote up our experience, https://www.vantage.sh/blog/querying-aws-cost-data-duckdb Arguably Postgres was never the right tool to use for this analysis but nonetheless I was surprised at how much faster DuckDB was.
ha! i'm using duckdb for a similar use-case.. plotting 18-month aggregates take less than 2 seconds.. vendor only provides data for the last 3 months (through the web portal).
(i'm "only" working with 30 million rows though)
Re: DuckDB – An in-process SQL OLAP database management system
#68Earlier quoted context omitted.
DuckDB is a relational OLAP store. If you want to do transformations on relational data using SQL then I think nowadays you would look at the modern data stack and do it with DBT. If you have genuinely big and unstructured data then of course you need a cluster and would reach for Spark. If you have smallish data then maybe DuckDB has a role because working with SQL is nicer than Pandas. But a lot of time you actuall…
> working with SQL is nicer than Pandas Really? I prefer working with dataframe apis. You get a nice sql-like paradigm plus all the control structures of the runtime.
SQL is much nicer for anything non-trivial. Pandas methods get unwieldy for complex aggregations.
Also Pandas methods are imperative so cannot be optimized. SQL is declarative so it can be optimized to the hilt and DuckDB is faster than Pandas in almost all cases, even on Pandas data frames themselves! (partly due to vectorization).
Re: DuckDB – An in-process SQL OLAP database management system
#69DuckDB is terrific. I'm bullish on its potential for simplifying many big data pipelines. Particularly, it's plausible that DuckDB + Parquet could be used on a large SMP machine (32+ cores and 128GB+ memory) to deal with data munging for 100s of gigabytes to several terabytes, all from SQL, without dealing with Hadoop, Spark, Ray, etc. I have successfully used DuckDB like above for preparing an ML dataset from about…
32 cores and 128GB RAM are now desktop-class specs. Latest generation commodity servers can supply you with hundreds of cores and TBs of RAM. Note: "commodity" != "cheap", at least not necessarily.
Ninja edit before anyone misconstrues this. I am not saying that the typical desktop has these specs. I am saying that the class of hardware that is most commonly run on desktops includes SKUs that can meet this spec. Desktop-class means the same motherboard socket and processor architecture.
Re: DuckDB – An in-process SQL OLAP database management system
#70Does anyone have experience using client-side DuckDB with WASM? It looks really compelling to ship analytics to the browser by running DuckDB over parquet files.