7 ms·

DuckDB 0.7.0

105 points by rolandm 4y ago

eliasmacpherson 4y ago
So say I wanted to try out a workload on various sql databases, mariadb, sqllite, postgres - is there a database that will act as a front end to them? I find this 'support for pluggable database engines' intriguing. Not least because I can then claim to have used all of the database engines in anger :-) (I know that it is probably a dumb question due to the following, but I asked anyway: https://en.wikipedia.org/wiki/SQL#Interoperability_and_standardization https://en.wikipedia.org/wiki/SQL#Interoperability_and_stand...)
- Gasp0de 4y ago
  Just use an ORM? If you code your workload in Python with SQLAlchemy you can just swap out any (relational) db. If you want to do benchmarking or something, this might not be the best approach, since each db might need some specific tuning to reach full potential.
- dirkderkdurk 4y ago
  You could also try using ODBC or ADO.NET. I've not used the latter, but ODBC was my goto for this kind of thing a decade or so ago. So mileage may vary drastically, and there might be roadkill along the way. https://en.wikipedia.org/wiki/Open_Database_Connectivity https://en.wikipedia.org/wiki/Open_Database_Connectivity
- chrisjc 4y ago
  It's not a dumb question at all. I'm pretty knowledgeable with DBs and still find it very difficult to understand how many of these front-end/pass-through engines work. Checkout Postgres Foreign Data Wrappers. That might be the most well known approach for accessing one database through another. The Supabase team wrote an interesting piece about this recently. https://supabase.com/blog/postgres-foreign-data-wrappers-rust https://supabase.com/blog/postgres-foreign-data-wrappers-rus... You might also want to try out duckdb's approach to reading other DBs (or DB files). They talk about how they can "import" a sqlite DB in the above 0.7.0 announcement, but also have some other examples in their duckdblabs github project. Check out their "...-scanner" repos: https://github.com/duckdblabs/postgres_scanner https://github.com/duckdblabs/postgres_scanner https://github.com/duckdblabs/sqlite_scanner https://github.com/duckdblabs/sqlite_scanner
natrys 4y ago
Upsert support and Lateral Join are really cool additions, thanks a lot for these. My code became quite ugly without these.
krishadi 4y ago
Is duckdb-wasm also going to get this update soon?
- hfmuehleisen 4y ago
  Yes we are working on it
  - krishadi 4y ago
    Is there a roadmap on handling geographic data?
    - maxxen 4y ago
      Yes! I'm working hard on it, I've been distracted with other work for some of our clients but hopefully have something to show soon.
smt88 4y ago
This is an interesting niche. Can anyone explain what they're using it for currently? Much like Redis, I admire the technology but can't think of a project I've worked on that would benefit from it. Is it for games, maybe? Desktop or mobile apps?
- adlpz 4y ago
  I'm interested in this, too. I can totally see how not having to manage a standalone RDBMS makes sense. But, what's the real-world advantage over something like SQLite? I mean, the idea of an in-memory relational engine for things like games or embedded totally makes sense, but this seems to target large datasets and deep analysis. As far as I understand with this model you pretty much re-ingest data from the "raw" source on startup every time. Is this correct? Judging by the rise on interest I'm sure there's an obvious use case I'm not seeing either.
  - shanipribadi 4y ago
    think BI tools, analytics dashboards for exploratory analysis, or even just exploratory analysis on the terminal with it's rich query capabilities. you can keep analytics data in SQLite, but DuckDB will process it faster/easier for the analytics use cases.
    - smt88 4y ago
      > think BI tools, analytics dashboards for exploratory analysis, or even just exploratory analysis on the terminal with it's rich query capabilities I thought about that, but I'd never use DuckDB for it because DuckDB is locked into a single process. I can't figure out a benefit of being suck with one core when I always have between 2 and 32 available to me.
      - 1egg0myegg0 4y ago
        DuckDB uses all of your cores! It just uses threads, not processes!
  - deleted 4y ago
isoprophlex 4y ago
> After this release DuckDB will also be able to write hive-partitioned data using the PARTITION_BY clause. These files can be exported locally or remotely to S3 compatible storage. Kudos to the team for their consistently useful, interesting work. They really seem to know their audience well, to have a well-thought-out feature roadmap. Makes you wonder if a single, well-specced box running DuckDB is going to be 2024's databricks killer.
- Tarq0n 4y ago
  Is Duckdb designed for multiple users? I always got the impression the default use-case is single-user.
0x008 4y ago
> DuckDB is an in-process SQL OLAP database management system I don’t understand what it means. Can someone explain? I don’t get why they put such a complicated claim with unexplained acronyms on their homepage. When I shop for a db, when should I consider duck DB compared to for example Postgres or MySQL? Or do they compete with arrow or parquet? To me it’s unclear because they don’t say what they compete against.
- ddorian43 4y ago
  > I don’t understand what it means. It's like Sqlite(OLTP) but for OLAP.
  - colesantiago 4y ago
    This is still confusing, what do I use this for exactly?
    - ZephyrBlu 4y ago
      OLAP databases are column oriented and are optimized for querying large amounts of high dimensional data (E.g. many columns). They're usually used for analytics. They don't support some features that OLTP databases have, like transactions. OLTP databases are your standard database like MySQL, Postgres, etc. You use an OLAP database if you want to query billions of rows over many different columns. Obviously they can be used for smaller workloads, but I'm exaggerating to show their strengths.
    - Sesse__ 4y ago
      Very, very roughly: OLTP is for dealing with one row at a time (TP = transaction processing; think “handling a sale”). OLAP is for combining many rows and extracting useful information from them (AP = analytics processing; think “figure out how many sales we had of each type of unit last month”). So for OLAP, you get more emphasis on features like joins, grouping and other analysis.
- shanipribadi 4y ago
  https://en.wikipedia.org/wiki/Online_analytical_processing https://en.wikipedia.org/wiki/Online_analytical_processing as opposed to https://en.wikipedia.org/wiki/Online_transaction_processing https://en.wikipedia.org/wiki/Online_transaction_processing DuckDB is when you need to do OLAP analysis, and the data fits in a single node (your laptop), but it's too large for plain excel. technically you can use PG/MySQL/Python+Numpy+Pandas to process those data for that use case as well, but DuckDB does it easier/faster most of the time.
ZephyrBlu 4y ago
I haven't used it yet, but DuckDB looks really cool. I'm looking forwards to what MotherDuck releases with it. Having a great local-first product focused on datasets <100GB would be awesome. Meta comment: it's fascinating to me that so many people seem to have never heard of OLAP databases.
- animuchan 4y ago
  One of those people here (not that I'm proud of it or anything) — I guess a lot of software engineers just don't do data analysis so heavy it requires a different kind of database. Not in the utterly pedestrian CRUD apps I write, anyway.
- frodowtf 4y ago
  It's also fascinating to me that some people seem to believe it's HN's job to define these terms. Seriously, the definition of OLAP is one search away and it is not hard to grasp...
londogard 4y ago
The Polars integration is golden! Now I can use my two favorite tools without any awkward conversations via Arrow. Really smooth, love it!
anonymousDan 4y ago
What is DuckDB's story for concurrency, transactions, multicore etc? Is it multi-threaded?
- jereze 4y ago
  https://duckdb.org/faq https://duckdb.org/faq
merricksb 4y ago
Major discussion of project 2 days ago: https://news.ycombinator.com/item?id=34741195 https://news.ycombinator.com/item?id=34741195 (160 points/2 days ago/97 comments) Also: https://news.ycombinator.com/item?id=33612898 https://news.ycombinator.com/item?id=33612898 – DuckDB 0.6.0 (36 points/89 days ago) https://news.ycombinator.com/item?id=31355050 https://news.ycombinator.com/item?id=31355050 – Friendlier SQL with DuckDB (366 points/9 months ago/133 comments) And many others as per dang's comment: https://news.ycombinator.com/item?id=34746724 https://news.ycombinator.com/item?id=34746724
- chrisjc 4y ago
  Any idea why this has been marked a dupe? I can't find the 0.7.0 story announced anywhere else on HN? The additions to 0.7.0 are quite significant and definitely news/discussion worthy. I would have been upset missing this announcement and related commentary if I hadn't seen it before being marked a dupe.
  - cmdlineluser 4y ago
    Many of the previous duckdb threads have comments advertising Clickhouse features. This was #1 before disappearing and now the current #1 is a blog post about Clickhouse.
  - dang 4y ago
    A moderator downweighted it because there was a DuckDB thread on the front page for 16 hours just a couple days ago: DuckDB – An in-process SQL OLAP database management system - https://news.ycombinator.com/item?id=34741195 https://news.ycombinator.com/item?id=34741195 - Feb 2023 (99 comments) HN operates on the basis of not having too much repetition on the front page. As seen at https://news.ycombinator.com/item?id=34746724 https://news.ycombinator.com/item?id=34746724, there have also been lots of other DuckDB threads in recent months. I realize a new release is rightly significant to the people working on the product and/or who are users of the product, and it would have been better for the major thread not to just be a generic post about the project. However, that distinction isn't as salient from a HN discussion point of view, because either way, the thread will fill up with comments about the product in general. You can see that quite clearly in the current thread. The important criterion from an HN point of view is "is this submission different enough to support a substantially different discussion", and in this case the answer is no, so the moderation call was correct. It's quite impossible to learn about every major release of every major product from HN—frontpage space is the scarcest resource we have [1]. The front page could consist of nothing else and you still couldn't learn about them all from HN alone. Nor is that the purpose of the site; the purpose is intellectual curiosity [2]. Curiosity doesn't do well with repetition [2], so the median curious reader isn't served by having two big threads about the same product within days. Of course, we all have at least one project where we would love to see that, but it's a different choice in everyone's case and we have to try to serve everybody. [1] https://hn.algolia.com/?dateRange=all&page=0&prefix=true&query=by%3Adang%20scarce&sort=byDate&type=comment https://hn.algolia.com/?dateRange=all&page=0&prefix=true&que... [2] https://hn.algolia.com/?dateRange=all&page=0&prefix=true&sort=byDate&type=comment&query=curiosity%20optimiz%20by:dang https://hn.algolia.com/?dateRange=all&page=0&prefix=true&sor... [3] https://hn.algolia.com/?dateRange=all&page=0&prefix=false&sort=byDate&type=comment&query=curiosity%20repetition%20by:dang https://hn.algolia.com/?dateRange=all&page=0&prefix=false&so...
talolard 4y ago
I use Duckdb as a data scientist / analyst. It’s amazing for working with large data locally, because it is very fast and there is almost 0 overhead for use. For example, I helped an Israeli ngo analyze retailer pricing data (supermarkets must publish prices every day by law). Pandas chokes on data that large, Postgres can handle it but aggregations are very slow. Duckdb is lightning fast. The traditional alternative I’m familiar with is spark, but it’s such a hassle to setup, expensive to run and not as fast on these kinds of use cases. I will note that familiarity with Parquet and how columnar engines work is helpful. I have gotten tremendous performance increases when storing the data in a sorted manner in a parquet file, which is ETL overhead. Still, it’s a very powerful and convenient tool for working with large datasets locally
- talolard 4y ago
  Oh just took a look at the release notes, the new ability to write hive partitioned data with the partition by clause makes etl stuff much easier
- stinos 4y ago
  So I'm not super familiar with different databases, but do understand the basics and do know how to work wit data with e.g. pandas, and do think I understand what Duckdb is useful for, but what I'm still completely missing is: how do I get data in Duckdb? I.e. how did you get that data into Duckdb? Or: suppose I have a device producing sensor data, normally I'd connect to some MySQL endpoint somehow and tell it to insert data. How does one do that with Duckdb? Or is the idea rather that you construct your Duckdb first by getting data from somewhere else (like the MySQL db in my example)?
  - talolard 4y ago
    You can do both ways but the latter is the more useful one. Duckdb is designed to read the data very fast and to operate on it fast. So you load a csv/json/parquet and then “create table” and Duckdb lays out the data in a way that makes it fast to read. But you(I) wouldn’t use it like a standard db where stuff gets constantly written in, rather like a tool to effectively analyze data that’s already somewhere
polyrand 4y ago
I'm super excited about the new query building. It's like having CTEs that you can easily debug and explore, and instead of having to work with big queries now you can just play around with Python objects. This will make testing complex SQL easier too, you can do `.limit(20).show()` on any intermediate relation and look at the table.

105 points by rolandm 4y ago

So say I wanted to try out a workload on various sql databases, mariadb, sqllite, postgres - is there a database that will act as a front end to them? I find this 'support for pluggable database engines' intriguing. Not least because I can then claim to have used all of the database engines in anger :-) (I know that it is probably a dumb question due to the following, but I asked anyway: https://en.wikipedia.org/wiki/SQL#Interoperability_and_standardization https://en.wikipedia.org/wiki/SQL#Interoperability_and_stand...)

Gasp0de 4y ago
Just use an ORM? If you code your workload in Python with SQLAlchemy you can just swap out any (relational) db. If you want to do benchmarking or something, this might not be the best approach, since each db might need some specific tuning to reach full potential.
dirkderkdurk 4y ago
You could also try using ODBC or ADO.NET. I've not used the latter, but ODBC was my goto for this kind of thing a decade or so ago. So mileage may vary drastically, and there might be roadkill along the way. https://en.wikipedia.org/wiki/Open_Database_Connectivity https://en.wikipedia.org/wiki/Open_Database_Connectivity
chrisjc 4y ago
It's not a dumb question at all. I'm pretty knowledgeable with DBs and still find it very difficult to understand how many of these front-end/pass-through engines work. Checkout Postgres Foreign Data Wrappers. That might be the most well known approach for accessing one database through another. The Supabase team wrote an interesting piece about this recently. https://supabase.com/blog/postgres-foreign-data-wrappers-rust https://supabase.com/blog/postgres-foreign-data-wrappers-rus... You might also want to try out duckdb's approach to reading other DBs (or DB files). They talk about how they can "import" a sqlite DB in the above 0.7.0 announcement, but also have some other examples in their duckdblabs github project. Check out their "...-scanner" repos: https://github.com/duckdblabs/postgres_scanner https://github.com/duckdblabs/postgres_scanner https://github.com/duckdblabs/sqlite_scanner https://github.com/duckdblabs/sqlite_scanner

natrys 4y ago

Upsert support and Lateral Join are really cool additions, thanks a lot for these. My code became quite ugly without these.

krishadi 4y ago

Is duckdb-wasm also going to get this update soon?

hfmuehleisen 4y ago
Yes we are working on it
- krishadi 4y ago
  Is there a roadmap on handling geographic data?
  - maxxen 4y ago
    Yes! I'm working hard on it, I've been distracted with other work for some of our clients but hopefully have something to show soon.

smt88 4y ago

This is an interesting niche. Can anyone explain what they're using it for currently? Much like Redis, I admire the technology but can't think of a project I've worked on that would benefit from it. Is it for games, maybe? Desktop or mobile apps?

adlpz 4y ago
I'm interested in this, too. I can totally see how not having to manage a standalone RDBMS makes sense. But, what's the real-world advantage over something like SQLite? I mean, the idea of an in-memory relational engine for things like games or embedded totally makes sense, but this seems to target large datasets and deep analysis. As far as I understand with this model you pretty much re-ingest data from the "raw" source on startup every time. Is this correct? Judging by the rise on interest I'm sure there's an obvious use case I'm not seeing either.
- shanipribadi 4y ago
  think BI tools, analytics dashboards for exploratory analysis, or even just exploratory analysis on the terminal with it's rich query capabilities. you can keep analytics data in SQLite, but DuckDB will process it faster/easier for the analytics use cases.
  - smt88 4y ago
    > think BI tools, analytics dashboards for exploratory analysis, or even just exploratory analysis on the terminal with it's rich query capabilities I thought about that, but I'd never use DuckDB for it because DuckDB is locked into a single process. I can't figure out a benefit of being suck with one core when I always have between 2 and 32 available to me.
    - 1egg0myegg0 4y ago
      DuckDB uses all of your cores! It just uses threads, not processes!
- deleted 4y ago

isoprophlex 4y ago

> After this release DuckDB will also be able to write hive-partitioned data using the PARTITION_BY clause. These files can be exported locally or remotely to S3 compatible storage. Kudos to the team for their consistently useful, interesting work. They really seem to know their audience well, to have a well-thought-out feature roadmap. Makes you wonder if a single, well-specced box running DuckDB is going to be 2024's databricks killer.

Tarq0n 4y ago
Is Duckdb designed for multiple users? I always got the impression the default use-case is single-user.

0x008 4y ago

> DuckDB is an in-process SQL OLAP database management system I don’t understand what it means. Can someone explain? I don’t get why they put such a complicated claim with unexplained acronyms on their homepage. When I shop for a db, when should I consider duck DB compared to for example Postgres or MySQL? Or do they compete with arrow or parquet? To me it’s unclear because they don’t say what they compete against.

ddorian43 4y ago
> I don’t understand what it means. It's like Sqlite(OLTP) but for OLAP.
- colesantiago 4y ago
  This is still confusing, what do I use this for exactly?
  - ZephyrBlu 4y ago
    OLAP databases are column oriented and are optimized for querying large amounts of high dimensional data (E.g. many columns). They're usually used for analytics. They don't support some features that OLTP databases have, like transactions. OLTP databases are your standard database like MySQL, Postgres, etc. You use an OLAP database if you want to query billions of rows over many different columns. Obviously they can be used for smaller workloads, but I'm exaggerating to show their strengths.
  - Sesse__ 4y ago
    Very, very roughly: OLTP is for dealing with one row at a time (TP = transaction processing; think “handling a sale”). OLAP is for combining many rows and extracting useful information from them (AP = analytics processing; think “figure out how many sales we had of each type of unit last month”). So for OLAP, you get more emphasis on features like joins, grouping and other analysis.
shanipribadi 4y ago
https://en.wikipedia.org/wiki/Online_analytical_processing https://en.wikipedia.org/wiki/Online_analytical_processing as opposed to https://en.wikipedia.org/wiki/Online_transaction_processing https://en.wikipedia.org/wiki/Online_transaction_processing DuckDB is when you need to do OLAP analysis, and the data fits in a single node (your laptop), but it's too large for plain excel. technically you can use PG/MySQL/Python+Numpy+Pandas to process those data for that use case as well, but DuckDB does it easier/faster most of the time.

ZephyrBlu 4y ago

I haven't used it yet, but DuckDB looks really cool. I'm looking forwards to what MotherDuck releases with it. Having a great local-first product focused on datasets <100GB would be awesome. Meta comment: it's fascinating to me that so many people seem to have never heard of OLAP databases.

animuchan 4y ago
One of those people here (not that I'm proud of it or anything) — I guess a lot of software engineers just don't do data analysis so heavy it requires a different kind of database. Not in the utterly pedestrian CRUD apps I write, anyway.
frodowtf 4y ago
It's also fascinating to me that some people seem to believe it's HN's job to define these terms. Seriously, the definition of OLAP is one search away and it is not hard to grasp...

londogard 4y ago

The Polars integration is golden! Now I can use my two favorite tools without any awkward conversations via Arrow. Really smooth, love it!

anonymousDan 4y ago

What is DuckDB's story for concurrency, transactions, multicore etc? Is it multi-threaded?

jereze 4y ago
https://duckdb.org/faq https://duckdb.org/faq

merricksb 4y ago

Major discussion of project 2 days ago: https://news.ycombinator.com/item?id=34741195 https://news.ycombinator.com/item?id=34741195 (160 points/2 days ago/97 comments) Also: https://news.ycombinator.com/item?id=33612898 https://news.ycombinator.com/item?id=33612898 – DuckDB 0.6.0 (36 points/89 days ago) https://news.ycombinator.com/item?id=31355050 https://news.ycombinator.com/item?id=31355050 – Friendlier SQL with DuckDB (366 points/9 months ago/133 comments) And many others as per dang's comment: https://news.ycombinator.com/item?id=34746724 https://news.ycombinator.com/item?id=34746724

chrisjc 4y ago
Any idea why this has been marked a dupe? I can't find the 0.7.0 story announced anywhere else on HN? The additions to 0.7.0 are quite significant and definitely news/discussion worthy. I would have been upset missing this announcement and related commentary if I hadn't seen it before being marked a dupe.
- cmdlineluser 4y ago
  Many of the previous duckdb threads have comments advertising Clickhouse features. This was #1 before disappearing and now the current #1 is a blog post about Clickhouse.
- dang 4y ago
  A moderator downweighted it because there was a DuckDB thread on the front page for 16 hours just a couple days ago: DuckDB – An in-process SQL OLAP database management system - https://news.ycombinator.com/item?id=34741195 https://news.ycombinator.com/item?id=34741195 - Feb 2023 (99 comments) HN operates on the basis of not having too much repetition on the front page. As seen at https://news.ycombinator.com/item?id=34746724 https://news.ycombinator.com/item?id=34746724, there have also been lots of other DuckDB threads in recent months. I realize a new release is rightly significant to the people working on the product and/or who are users of the product, and it would have been better for the major thread not to just be a generic post about the project. However, that distinction isn't as salient from a HN discussion point of view, because either way, the thread will fill up with comments about the product in general. You can see that quite clearly in the current thread. The important criterion from an HN point of view is "is this submission different enough to support a substantially different discussion", and in this case the answer is no, so the moderation call was correct. It's quite impossible to learn about every major release of every major product from HN—frontpage space is the scarcest resource we have [1]. The front page could consist of nothing else and you still couldn't learn about them all from HN alone. Nor is that the purpose of the site; the purpose is intellectual curiosity [2]. Curiosity doesn't do well with repetition [2], so the median curious reader isn't served by having two big threads about the same product within days. Of course, we all have at least one project where we would love to see that, but it's a different choice in everyone's case and we have to try to serve everybody. [1] https://hn.algolia.com/?dateRange=all&page=0&prefix=true&query=by%3Adang%20scarce&sort=byDate&type=comment https://hn.algolia.com/?dateRange=all&page=0&prefix=true&que... [2] https://hn.algolia.com/?dateRange=all&page=0&prefix=true&sort=byDate&type=comment&query=curiosity%20optimiz%20by:dang https://hn.algolia.com/?dateRange=all&page=0&prefix=true&sor... [3] https://hn.algolia.com/?dateRange=all&page=0&prefix=false&sort=byDate&type=comment&query=curiosity%20repetition%20by:dang https://hn.algolia.com/?dateRange=all&page=0&prefix=false&so...

talolard 4y ago

I use Duckdb as a data scientist / analyst. It’s amazing for working with large data locally, because it is very fast and there is almost 0 overhead for use. For example, I helped an Israeli ngo analyze retailer pricing data (supermarkets must publish prices every day by law). Pandas chokes on data that large, Postgres can handle it but aggregations are very slow. Duckdb is lightning fast. The traditional alternative I’m familiar with is spark, but it’s such a hassle to setup, expensive to run and not as fast on these kinds of use cases. I will note that familiarity with Parquet and how columnar engines work is helpful. I have gotten tremendous performance increases when storing the data in a sorted manner in a parquet file, which is ETL overhead. Still, it’s a very powerful and convenient tool for working with large datasets locally

talolard 4y ago
Oh just took a look at the release notes, the new ability to write hive partitioned data with the partition by clause makes etl stuff much easier
stinos 4y ago
So I'm not super familiar with different databases, but do understand the basics and do know how to work wit data with e.g. pandas, and do think I understand what Duckdb is useful for, but what I'm still completely missing is: how do I get data in Duckdb? I.e. how did you get that data into Duckdb? Or: suppose I have a device producing sensor data, normally I'd connect to some MySQL endpoint somehow and tell it to insert data. How does one do that with Duckdb? Or is the idea rather that you construct your Duckdb first by getting data from somewhere else (like the MySQL db in my example)?
- talolard 4y ago
  You can do both ways but the latter is the more useful one. Duckdb is designed to read the data very fast and to operate on it fast. So you load a csv/json/parquet and then “create table” and Duckdb lays out the data in a way that makes it fast to read. But you(I) wouldn’t use it like a standard db where stuff gets constantly written in, rather like a tool to effectively analyze data that’s already somewhere

polyrand 4y ago

I'm super excited about the new query building. It's like having CTEs that you can easily debug and explore, and instead of having to work with big queries now you can just play around with Python objects. This will make testing complex SQL easier too, you can do `.limit(20).show()` on any intermediate relation and look at the table.