9 ms·
DuckDB 0.8
- rektide 3y agoDamn. This database quacks. Huge list of awesome super helpful tools. Tons of great features & speed for everyone. Also love the stuff from the edge, such as Arrow DataBase Connector support, > From this release, DuckDB natively supports ADBC. We’re happy to be one of the first systems to offer native support, and DuckDB’s in-process design fits nicely with ADBC.
- jamesblonde 3y agoADBC is going to be huge for BI tooling. Right now, most of them go through ODBC/JDBC which are row-oriented formats. So your columnar data is always reshaped from column-oriented (in your data warehouse / lakehouse) to row-oriented and often back again. The data is also serialized, deserialized on top of that. We got 8-20X speed-up replacing JDBC with Arrow Flight Service, which is a more limited version of ADBC - https://www.youtube.com/watch?v=nCxIpMXvCp0 https://www.youtube.com/watch?v=nCxIpMXvCp0
- tibudiyanto 3y agoWhat a great database. This and sqlite-utils are my go to tools for ad hoc data wrangling.
- afordich 3y agoExcited to see how it will progress.
- zxexz 3y agoLack of order-preserving parallel parquet writing was my biggest gripe with DuckDB. Glad to see they implemented it! Between this duckdb release, DataFusion and Apache Arrow (yes, I know that helps power the other projects I listed), I’m hoping I won’t have to touch a spark cluster for a long time.
- winrid 3y agoI've been thinking about rebuilding the website analytics service I have, to be around sharded duckdb and just giving people raw SQL access. ClickHouse is the alternative, but this approach has some interesting advantages, like simplicity.
- higeorge13 3y agoI find clickhouse equally simple. One command to install and then you can either read from local/remote files, create tables, etc
- winrid 3y agoFrom the brief time I used it running signoz it used a surprising amount of resources, which requires further investigation, but yes it's a nice stable option.
- glogla 3y agoClickhouse looks more complicated because instead of create table bla ( id number, xyz varchar ) you're doing things like create table bla ( id uint64 CODEC(DoubleDelta(8)), xyz Nullable(LowCardinality(Varchar)) CODEC(ZSTD(1)) ) engine=MergeTree order by id but I do wonder what is the performance difference between actually carefully specifying everything and leaving defaults (except for order by I guess).
- higeorge13 3y agoFair enough. Some of the settings will have performance impact, e.g. the low cardinality type, data skipping indexes, etc. and you have to spend some time to learn and experiment yourself. However it’s a powerful db and even the default settings will work well in the majority of cases.
- zX41ZdbW 3y agoJust leave everything by default - no need specifying any codecs, etc. People often try to show up their knowledge by using advanced features, but that's not good.
- TACIXAT 3y agoI recently pulled DuckDB out of a project after hitting a memory corruption issue in regular usage. Upon investigating, they had an extremely long list of fuzzer-found issues. I just don't understand why someone would start something in a memory unsafe language these days. I cannot in good conscience put that on a customer's machine. We ended up rewriting a component to drop support for Parquet and to just use SQLite instead. I love the idea of being able to query Parquet files locally and then just ship them up to S3 and continue to use them with something like Athena. The other thing that rubbed me the wrong way was that rather than fix the issue, they just removed functionality. DuckDB (unironically) needs a rewrite in Rust or a lot more fuzzing hours before I come back to it. While SQLite is not written in a memory safe language, it is probably one of the most fuzzed targets in the world.
- 1egg0myegg0 3y agoSorry to hear that! (I work on docs for the DuckDB Foundation) Starting in this release, the DuckDB team invested significantly in adding memory safety throughout catalog operations. There is more on the roadmap, but I would expect this release and all following to have improved stability! That said, at my primary company, we have used it in production for years now with great success!
- TACIXAT 3y agoYea, legit a cool project and if it wasn't on customer machines and being passed user-defined SQL it would have just been worked around.
- chrisjc 3y agoWould you mind explaining your issues in a little more detail? > being passed user-defined SQL What does this mean exactly? Customers were writing their own SQL on their own machines? Maybe expensive operations such as: `SELECT ROW_NUMBER() OVER (PARTITION BY THING ORDER BY TS), * FROM EVENTS` And since it was on a customer's machine, it became a problem you had no control over?
- 3y ago
- ChrisCinelli 3y agoThis is the first time I heard about DuckDB. Why someone would use that instead of a mature engine like SQLite?
- fbdab103 3y agoColumnar database not row based. More appropriate for analytical workloads, and not as a transactional backend.
- 1egg0myegg0 3y agoGreat question! For analytical queries, DuckDB is ~100x faster than SQLite. It also has great connectivity with Parquet, Pandas, Arrow, Postgres, and more! Plus, the syntax follows Postgres closely, so you have strong data typing and true timestamps, etc. All that said - SQLite is still faster for transactions. Now DuckDB can actually read and write from/to SQLite, so you can use SQLite for OLTP and DuckDB for OLAP. https://duckdb.org/docs/sql/statements/attach https://duckdb.org/docs/sql/statements/attach
- fbdab103 3y ago>Now DuckDB can actually read and write from/to SQLite This feels like it opens up some possibilities, but I am struggling to figure out where. Are you aware of any interesting use cases this has enabled? Does all of the Duck syntax then work against the SQLite database? For example, could I now run a pivot without a bunch of hoops?
- 1egg0myegg0 3y agoYou could definitely run a pivot with the SQLite attach! All of the DuckDB syntax works!
- fbdab103 3y agoWell that is just amazingly interesting. The documentation is failing to do this feature justice! Definitely going to be on my todo list to explore this bridge in the near future.
- emmanueloga_ 3y agoSetting up SQL, creating a well-designed schema, and optimizing queries can be a complex, multi-step process. Databases often feel like black boxes, requiring significant training and engineering to get right. One needs to tweak both queries, schema and database configuration to get best results. So why do we need SQL? I know NoSQL came and went (was it nosql or no-relations? not sure...) but honestly I can't think of many things I can do with SQL that i CANNOT do easier in any programming language with a good library for indexing and retrieving data. Any good industrial strength key-value database like rocksdb, lmdb and others would provide these APIs with lots of bindings for multiple programming languages. Maybe we need a second coming of NoSQL (relational no-sql?) focused in providing good cross-language APIs so instead of compiling SQL into efficient programs that use the right indexes, we could just write our queries manually in code. Bad queries would be a lot easier to debug and fix this way, and it seems like this would require a lot less machinery.
- beoberha 3y agoThe simple answer to this is that not only engineers write database queries. Personally, I’d be interested to see a system like the one you describe. Part of me thinks that SQL would still be simpler to reason about (though probably not to maintain).
- bobbylarrybobby 3y agoPolars is an interesting library in this space. A rust DataFrame with bindings in other languages, most notably python
- Icathian 3y agoI think you are drastically overestimating the ability of most devs to write performant code. Do you really think you're going to get better results asking devs who, by your measure cannot write good declarative SQL schemas and queries, to roll their own data storage and retrieval raw? RDBMS software is complicated in large part due to it needing to serve a wide range of use cases, so a custom-built version might end up simpler to use, but you can't sell me that Joe Backend-Dev is going to outperform however many million dev-hours have gone into Postgres.
- mkhattab 3y agoMinor annoyance: Yesterday I was reading the docs and one of the first things I tried is the following, copy and pasted, which didn't work, using version 0.71: COPY (SELECT 42 AS a, 'hello' AS b) TO 'query.json' (FORMAT JSON, ARRAY TRUE); >>> Error: Binder Error: Unrecognized option CSV writer "array" Just tried the above again with this release and it's fixed now. Ideally, all commands listed in the documentation should be tested or verified to work. Aside from that, nice work on the release! And I'm learning more and find it pretty cool, especially WASM support.
- 1egg0myegg0 3y agoHowdy! The docs reflect the latest dev branch. For a specific release, you can pick it from the version dropdown in the top right!
- mkhattab 3y agoSorry! Just realized that when I checked the docs again. My bad.
- 1egg0myegg0 3y agoNo worries! Thanks for reading the docs!
- porker 3y agoI think it would be a better choice to default to showing the current stable release's docs rather than the dev branch? That way users who download it see the docs they expect, and those who build from the dev branch or download preview builds have to go through an extra step.
- rendaw 3y agoWhy not show the latest stable release docs by default? I assume that's what most people would use.
- whoopdeepoo 3y agoSuch an amazing project. Once the geospatial extension lands I'm going to seriously consider switching our geoparquet workflows over.
- wiredfool 3y agoI'm just minorly annoyed that they're not calling it GeoDuck.
- wenc 3y agoYou mean this one? DuckDB Spatial Extension https://duckdb.org/2023/04/28/spatial.html https://duckdb.org/2023/04/28/spatial.html
- whoopdeepoo 3y agoYes, it's still very alpha
- AbuAssar 3y ago> DuckDB is an in-process SQL OLAP databasemanagement system > In-process, serverless C++11, no dependencies, single file build APIs for Python/R/Java/… > Transactions, persistence Extensive SQL support Direct Parquet & CSV querying > Vectorized engine Optimized for analytics, Parallel query processing > Free & Open Source Permissive MIT License
- noobcoder 3y agoThe feature to register arbitrary Python functions as scalar functions within SQL queries is pretty insane. Writing custom functions to manipulate data in ways that aren't possible with SQL, basically without fetching all of it at once in memory and then applying. I hope thats the case
- tored 3y agoSQLite has similar functionality. e.g PHP https://www.php.net/manual/en/sqlite3.createfunction.php https://www.php.net/manual/en/sqlite3.createfunction.php https://www.sqlite.org/appfunc.html https://www.sqlite.org/appfunc.html
- meitham 3y agoI used duckdb successfully in prod to replace SQL Server. We have a micro batch that generates around 5 billion rows of very wide tables every 3 minutes. These data used to go into SQL server, only to be replaced by the new batch of 5 billions and gets marked for deletion. SQL Server was struggling with all these purge activities. Replacing with DuckDB made things much lighter and faster. The only issue I faced is the case sensitivity, where in DuckDB if you ask for your queries to be case insensitive, your results lose their original casing and returned all lowercased.
- porker 3y agoI'm curious, what kind of problem needs 5 billion rows every 3 minutes to replace the previous 5 billion rows? By "micro batch" I understand batch processing of some kind?
- meitham 3y agofinancial risk calculations with many sensitivities
- hawk_ 3y ago> results lose their original casing and returned all lowercased. That's a shame. Have you reported this bug on their repo?
- meitham 3y agoSomeone else beat me to it https://github.com/duckdb/duckdb/issues/3821 https://github.com/duckdb/duckdb/issues/3821
- boomskats 3y agoI know this is irrelevant, and I'm really no a fan of SQL Server either, but I'm curious - did you try partitioning those tables on the batch ID, and then truncating partitions instead of deleting old rows?
- 3y ago
- pknerd 3y agoCan you share some use cases with details where I can learn more how DuckDB is doing better?
- tobilg 3y agoThe main use case for me for DuckDB is in Data Lake-related tasks where cloud providers often do not provide great/cost-effective/otherwise limited services. In the case of AWS, repartitioning Parquet files in S3 via Athena CTAS statements in limited to 100 active partitions, which is a bummer to work around. Therefore, I’m using DuckDB with repartitioning queries, because it doesn’t have the 100 partition limit. I wrote a blog post about it at https://tobilg.com/casual-data-engineering-or-a-poor-mans-data-lake-in-the-cloud-part-i https://tobilg.com/casual-data-engineering-or-a-poor-mans-da... Additionally, to get started with using DuckDB serverlessly in Lambda functions, you can have a look at https://tobilg.com/using-duckdb-in-aws-lambda https://tobilg.com/using-duckdb-in-aws-lambda
- lysecret 3y agoWe extensively explored this approach for a use-case and dropped it in favour of BigQuery external data query for one core reason: It can't do streaming reads if you have to do any kind of operation on the underlying data, for almost everything it will have to load all the relevant parquet files locally to do the operations. You might have been able to solve this using row partitions on the underlying files beforehand, but it was too late for us. Also, it actually turned out to be much more expensive to run the operations in cloud run, than relying on BQ (due to the fact it wouldn't stream as we wished). More than anything it gave me renewed appreciation for BQ.
- chrisjc 3y agoVery interesting. Would you mind mentioning which external table format you were using? Iceberg by any chance? I wonder if there wasn't a middle-ground that could still utilize DuckDB or similar. You still rely on BQ to deliver the subset of data needed, and then rely on DuckDB to do the "last-mile" analytics: grouping, filtering, ordering, etc...?
- tobilg 3y agoI don't think this is correct, or I didn't really understand your use case. Could you give an example for "any kind of operation on the underlying data"?
- polyrand 3y agoAnother cool new feature that's not mentioned in the blog post is function chaining: https://github.com/duckdb/duckdb/pull/6725 https://github.com/duckdb/duckdb/pull/6725 I've been using DuckDB for filtering and post-processing data, specially strings, and this will make writing complex queries easier. By combining nested functions[0] and text functions[1], sometimes I don't even need to go into a Python notebook. [0]: https://duckdb.org/docs/sql/functions/nested https://duckdb.org/docs/sql/functions/nested [1]: https://duckdb.org/docs/sql/functions/char https://duckdb.org/docs/sql/functions/char
- heuermh 3y agoNice to see Parquet support in DuckDB improving with each new release! In earlier versions, I had trouble with enums and nested schema, these issues have been resolved since. The only missing feature for me now is full table, column, &c. metadata support in the DuckDB JDBC driver. Thanks!
- whalesalad 3y agoI have been wanting to try DuckDB forever and finally took it for a spin recently. It is an amazing technology that I hope to utilize in production one day. Hat's off to the UI/UX team behind the website and docs. Love the aesthetic!
- vgt 3y agoWe at MotherDuck are huge supporters of DuckDB, and it's not a big secret that we're working on a managed DuckDB offering. Feel free to give us a ping to chat :)