Resources
Oct 31, 2024 |
58 min
|
19.5k views

Hannes Mühleisen - Data Wrangling [for Python or R] Like a Boss With DuckDB

Data wrangling is the thorny hedge that higher powers have placed in front of the enjoyable task of actually analyzing or visualizing data. Common struggles come from importing data from ill-mannered CSV files, the tedious task of orchestrating efficient data transformation, or the inevitable management of changes to tables. Data wrangling is rife with questionable ad-hoc solutions, which can sometimes even make things worse. The design rationale of DuckDB is to support the task of data wrangling by bringing the best of decades of data management research and best practices to the world of interactive data analysis in R or Python. For example, DuckDB has one of the world's most advanced CSV readers, native support for Parquet files and Arrow structures, an efficient parallel vectorized query processing engine, and support for efficient atomic updates to tables. All of this is wrapped up in a zero-dependency package available in a programming language near you for free. In my talk, I will discuss the above as well as the design rationale of DuckDB, which was designed and built in collaboration with the Data Science community in the first place.

Talk by Hannes Mühleisen


Slides: https://blobs.duckdb.org/posit-conf-2024-keynote-hannes-muehleisen-data-wrangling-duckdb.pdf

image: thumbnail.jpg

Transcript#

This transcript was generated automatically and may contain errors.

Hi, hello everybody. Thanks, Hadley, for the introduction. And, yeah, glad to see so many of you are sticking around for the last talk. I know it's a lot to ask.

Yeah, my name is Hannes, and I'm going to talk about data wrangling like a boss with DuckDB. Very briefly about myself, if the clicker stops working. As Hadley has said, I am the co-founder and the CEO of a company called DuckDB Labs. But I'm also a professor of data engineering as a sort of side gig at the University of Nijmegen, which, in case you have never heard of it, that's okay, it's very small.

But I wanted to start with something that really inspires me, and I don't know how many of you have seen this thing. Anybody has seen this? Raise your hand. Nobody. Mark has seen it. Okay. This is one of the first tables that we know of. It is a protoc uniform tablet excavated in Uruk, which is, I think, in today's Iraq. And it's dated to 3200 BC.

It's in the Metropolitan Museum of Art in New York. And this is interesting, because if you look at it and squint a little bit, you can see an Excel table, right? And in fact, it is exactly that. It has two columns, it's item and amount, and two entries. One is hop and one is barley. One can only guess what they were making.

And the cool thing about it is that it predates this thing. Record-keeping predates literature by about a thousand years. So we've been making tables a thousand years before we have the first text. And I really liked it when I found this. I was really happy, because I was always like, what is it about my brain that's so excited about tables? Something must be wrong with me.

Record-keeping predates literature by about a thousand years. So we've been making tables a thousand years before we have the first text.

But now I'm content to thinking that I'm just an older version of a human, which I'm very happy with Stone Age person. Anyways, that's just the intro.

Motivating the case for DuckDB

I want to motivate my talk with a Hadley quote as a tradition. This is on the dbplyr vignette, and I will repeat it until it's removed. If your data fits in memory, there is no advantage to putting it in a database. It will only be slower and more frustrating.

I can hear that. But then I really like tables and databases, and I kind of want to explore this a bit together with you. Let's explore this.

But I want to say that this quote and others by people like Thomas Lumley and Anthony D'Amico actually inspired the work on DuckDB, this kind of notion that data management systems are frustrating and slow, and we kind of set out to change that. But before we get there, let me explore this a little bit.

The frustration of loading a CSV into Postgres

So I went on the internet and I googled big CSV file. This was the first thing that came up. It's the New Zealand Stats Agency. And they have a census data set, multiple CSV files for download on their website. Really, I promise you this was the first thing that I found good on them that had all this stuff for download there. And then I just went through the list and grabbed the biggest CSV file I could find. It's six files in a 100 megabyte zip file or something like this. The biggest one is 857 megabytes large. It has 34 million rows and six columns. You would agree with me that that fits in memory, yes?

Let's unpack the statement that we've seen earlier. Say I want to look at this file from Stats NZ with a database, let's say Postgres, like the world's most commonly overhyped database system, right? I'm going to ignore some of the pain that is required to set Postgres up in the first place. No, I'm not going to talk about credentials, ports, users, all that. It's a pain in itself. Hopefully you believe me that.

But, you know, in principle, what we're going to do in Postgres is going to say, we have to create a table and we have to use this copy command to get the CSV file into the database. And then we can finally do the analysis. OK, so we look at the CSV file first. And you're just on the command line. We go like head data CSV. Very good name. And we see these columns there. It's a year, age, ethnicity and so on. And by squinting and with a lot of experience. And after all, I'm professor of data engineering. I can see that. OK, this probably is a table with these columns. And this looks like these are numbers, although the leading zeros somewhere around there, they seem a bit worrying, right? Like leading zeros.

Are these really numbers we shall see. So then I after figuring all this out, I type the copy command into Postgres and it says Mac. And I'm like, OK. Invalid input syntax for type integer dot dot C line 18. Like, OK, fine. Column count is clearly not an integer, which is weird because that's the only one where I was quite sure it was a count. But it isn't. OK. So then we drop the table again. Fine. We create a table again. We make this count a bar chart because, you know, OK, clearly we have to figure this out later on. And then we call the copy command again. And it says Mac. Invalid input syntax for type integer. CMB 0 7 6 0 1 line four hundred thirty one thousand seven forty one.

And that from your laughter, I know exactly what you're feeling right now. So that's not great. OK, fine. Drop the table again. Go for broke. Go for broke. And then just copy the data. And lo and behold, we've copied the 34 million rows in 16 seconds. This is all on this MacBook, by the way. I'm running out of experience on this MacBook. Now I have done have five other files to do this with. Right. I'm going to spare you this. Right. And then after I've done all of this, I finally get to use this SQL thing. At this point, I've kind of spent an hour on this. And indeed, this is more frustrating than I thought it would be.

And it's also an interesting thing to see how the 16 seconds that this is video took actually are completely irrelevant in compared to the hour that I spent like, you know, hacking at it. But OK, let's go to slower next.

Benchmarking CSV readers

Let's just look at sort of the speed for a couple of off the shelves solutions. And again, it's not only about performance. And this is very weird coming from a database person. I know. But if we use reader, for example, as you know, one of the many sees readers that we have in our you pointed at the data. And it says, hey, I've read this file for you. And you think, hang on, wouldn't you only going to tell me that you don't like the data types? And no, no, no. It says, no, I figured it out. There's three character columns in here. And there's three double numeric integer in this case. But numeric columns here and then here is, by the way, how this thing looks like. And we all have been sent 10 seconds again.

The execution performance of 10 seconds is not so relevant for an interactive use case, because this took, you know, 20 seconds overall. I had to type the command press enter. But so this is really interesting to see how the data science tools like are in Python on the ecosystems have kind of solved this frustrating thing. Actually, in many in a pretty good way, like you just do it. And comparing that to Postgres, the shining light on the database sort of ceiling is really a huge, huge difference.

And it's why is that? I think it's interesting question. I think the data management community in the past has really just never cared about reading about also about people. And this is not an exaggeration. I mean, if you if you think about it, how do you sell Oracle to somebody? Well, it's very easy. You meet the other CEO on the golf court. Right. Done. You don't have to talk to the people that actually have to use Oracle because they would tell you or Postgres or anything. They really they really they would like rip you apart. But since this is not relevant in the sales process, they don't care in data science tools. We really care, which is why there's so much better. Having said all that.

We can, of course, do a formal benchmark here and say, let's take a file right and throw it into various readers. And we kind of see that there is a spread of time between like 22 seconds and 2.9 seconds data table. Traditionally had a very good as we reader pollers doesn't really manage this because they are, I think, too eager in their optimizations here. But basically, again, having said this, the performance of the reader is only so important. And it's interesting how that ties these two statements together, because frustrating and slower are kind of similar in that sense. Right. Because slower actually doesn't refer to performance here. It refers to the end to end time.

Introducing DuckDB

So because we realize that this was true for most data management systems, but we really like data management systems, we thought, how can we maybe build a data management system that doesn't is not slower and more frustrating? So the so Mark, who is here and I got together and we started thinking about how analytical database system. That's not slower. Frustrating could look like. And we did this research at CWI in Amsterdam, which is the Dutch National Research Lab of Computer Science and Mathematics, which is famous because it was where Python was invented. But I'm at the wrong conference. I realized that the result of this effort is that to be. I want to do a quick poll. Who here has used that DB? Oh, that's that's excellent. It's excellent. But there's a lot of people that haven't used it yet. So I'm I will you will forgive me in trying to explain why it's maybe a good idea to use that DB.

And I think the user experience is really core to the philosophy of that DB. People have focused too long on this little middle thing of the burger. They are the DBS engine to join algorithms. The I don't know, you know, the scan performance, all these things. And now we really has nobody had really thought about the end-to-end user experience because, you know, remember. And we really set out to change that to make a data management system. That is good. Nice to use. And sort of by accident, we also invented a new category of data management system, which is an in-process analytical system. I will say in a second why it's in process, but basically it means that there is no setup required of a server or files or anything like that, but it just runs wherever you are. And it's not a transactional system like Postgres or SQLite or actually most other data management systems, but it's analytical, meaning that it's really focused on doing analysis and not on sort of tracking orders in a web shop. And it didn't exist yet. So that's pretty good. It's always good to invent a new category, right?

Let's go a little bit in more detail, like the in-process architecture. I think this is the biggest sort of departure from traditional data management systems architecture that we did in DuckDB is that it runs within another process. It's a bit hard to imagine what that means. But for example, if you're in R or in Python or in Java or whatever, if you're pulling up a DuckDB connection, it means that the entire code of the entire database management system actually runs within that process. And this sounds very weird, but we're actually quite used to it because the world's most widely deployed data management system, not Postgres, but SQLite, actually does the same thing. And it has really great effects on usability on, you know, set up simplicity and all these things. It also makes it really easy to set this up.

So you can type install.package with DuckDB. Okay, you can also do pip install DuckDB. We support a bunch of other stuff. We have zero dependencies. So you don't have to first install, I don't know, OpenMP or something else like that. It's just all wrapped into one package. There's no apt-get. There's no Docker. There's no funky drivers, no funky GPU drivers, nothing like that. It runs really anywhere, you know, Mac, Linux, Windows, ARM, Intel, doesn't really matter. As I said, for users, it's just install packages. So that's nice.

And it's also batteries included. I noticed a term from yet another system, but it's a fully featured data management system. We have transactions, we have persistence, and we can write tables to the disk and we can actually have transactional safe changes to these files, even if the changes are quite massive. We have a crazy amount of operators, high-performance implementations for operators like joins, aggregates, window functions. DuckDB can, out of the box, read CSV files, important, parquet files, JSON files, a bunch of other stuff. It's all built in, right? We can connect to cloud storage like S3. We can talk to Postgres. We can talk to MySQL, can do all these things. And it's integrated, as I mentioned, into R, Python, Java, and a zoo of other languages. So it's really, we try to have all the things there.

And it's fast in the sense of efficiency. And I realized that if that, I just said that speed doesn't matter, but that is for the interactive use case. Speed does actually matter in two ways. First, if you're running something every 10 seconds, probably the speed doesn't matter. And the other thing is that, and I will come back to that later, is that by making something faster, you actually expand the capabilities of what you can do on a single node. So this is great.

So how is DuckDB fast? Well, it's hard to explain. You show an effect. If you run DuckDB, your laptop can kind of get warm. So a word of warning here. So we automatically paralyze the analysis tasks that you give us across all the available CPU cores. But line paralyzation is actually not very meaningful without having a good per-core efficiency. And we achieve that by having highly efficient C++ implementations for all the various operators. We can also use the hard disk

if the data set does actually not fit in memory. So if we have this case where we want to run a query on a terabyte file and we actually have to build, have to sort the whole thing, you can imagine that you might not have a terabyte of RAM. And sorting everything is a quite sort of IO-intensive operation. So DuckDB can transparently and gracefully use the available disk space.

That's really cool. But all this happens completely automatically, which is why it's really magic for many people that it just does things quickly and then they don't understand. And then it's clearly magic. This was from earlier today. So I thought it was funny to pull a tweet from today.

And of course, we know that any piece of sufficiently advanced technology is indistinguishable from magic. And I think what's fair to say is that DuckDB is this culmination of decades of research in execution engines, but then really wrapped in a user-friendly package.

is that DuckDB is this culmination of decades of research in execution engines, but then really wrapped in a user-friendly package.

Performance benchmarks

You will forgive me about some more things about speed. So this is a benchmark that originated from the data table folks that we've kind of resurrected and keep updated now. So here's two tasks. There's a group-by task and a join task. And this is a 50 gigabyte data set here. And you can see that, lo and behold, we have also managed to be faster than everything else here. Same is also on the join task.

And this is not even the latest in what we have. I think the next version will be even faster than that.

DuckDB's open source model

But coming back to the golf course, I don't really go to the golf course. So DuckDB is free. It's free and open source under the MIT license. So you can build a company on it and you don't have to talk to us at all, which I think it sounds a bit, you know, difficult from traditional database sort of thinking ways. But we really think that in order to make an impact in how people think about data, which is kind of what we're trying to do here, we really need to give people the tools, the state-of-the-art tools to work with big data sets.

And we can't really, you know, make this just happen for one company or for the customers of one company. We have to really make this work for everyone. The world is kind of lacking DuckDB.

So here's a plot of the GitHub stars. It's a very, it's a crap metric. Mostly VCs care about this. But it's interesting to see how this is going on. So we really have an exponential growth in the adoption of DuckDB. We're really, really grateful about it.

We eventually spun off from the Research Institute into a company called DuckDB Labs because every open source project needs to fund itself somehow. You can fund the open source project by working for another company and then doing nights and weekends, but I can't really do that. You can sell your soul to VCs and then basically, you know, continue until the party stops and somebody has to change a license.

But DuckDB itself is, yeah, backed by DuckDB Labs, which is a company that provides support and consulting contracts around DuckDB, but actually is not the owner of the software itself, which is in a non-profit. So this is really a unique setup that we don't have, you know, we see interest in the company, but it's a long-term effort backed by an independent foundation just to make sure that this thing keeps being available. So even we have trouble, are going to have trouble sort of rug pulling this.

DuckDB loves R and Python

And DuckDB loves R. Hey! As I said, the motivation to build DuckDB came from interactions with the R community. I actually spoke about DuckDB for the first time at Kirill's meetup. Where's Kirill? In Zurich in summer of 2019. That was a long time ago.

And because it's in process, we can actually do some pretty amazing things within R. So for example, DuckDB running inside the R process can directly read any data frame that you have inside the R process as if it were a table. There's no, you know, we don't have to first convert this to anything or like, no, no, no. If a data frame exists inside your R process, we can run queries on it. And we can also take the query results that we produce from DuckDB and convert them directly into data frames.

And some systems out there have kind of also functionally like this, but it tends to be insanely slow. We can do this very quickly because we're sitting in the same process space. We don't have to do any serialization or any of that sort.

We also love Python. So it's the biggest API actually by downloads per month. It's like six million downloads per month. It's pretty ridiculous. And we can do also the same trick with pandas data frames. So if you run DuckDB in a Python process and you have a pandas data frame sitting there, we can also treat it as a table and read it. We can also produce results again as pandas data frames because that allows us to integrate with downstream libraries.

And because many data sets or many tools are starting to support Apache Arrow as intermediates, we can also do the same trick for R and Python. If the data happens to be Arrow, we can read and write from it directly. Even streaming. So we don't actually have to copy things. It's just streaming into Arrow, streaming from Arrow. That works all really well. So that's DuckDB in a nutshell.

CSV parsing and query execution

Let's revisit the statement. Slower and more frustrating. I'm going to go back to the running example in a bit, our wonderful CSV file. First, I want to say we actually wrote a research paper on CSV parsing. Because it turns out that this is such an annoying issue that you can actually write a computer science research paper on it. I think we may have been the first ones that have done this. There's some happened afterwards.

But as a result, we actually have one of the best CSV readers out there in DuckDB because we realize that your analysis starts with getting data from somewhere else, right? You're not going to sit down and type everything into DuckDB. You're going to use something else. And so we brought this research paper and we took the ideas from the research paper and put them into DuckDB. A shout out to Pedro from our team who worked incredibly hard on this. And we can actually read most files out there without human interaction.

So, for example, in DuckDB, you can just type from data so-and-so, so-and-so, right? And this is technically a SQL query. We can treat table, if you don't, if this is using a table name, using the file as a table name. And we have this logic that says if the table isn't there and there's a file with the same name, well, probably we should read this file. And if it ends with CSV, it's probably a CSV file. So we do a lot of sort of guessing just to reduce the frustration. And you will see that it has indeed read this file correctly with all the column types and so on and so forth. And it can show this.

We also have some cool features in DuckDB like summarize, right? So we have some features that are trying to make your life as an analyst easier, even if the data set is huge. So to summarize is pretty cool, actually, because you just say summarize any query, really, and we will compute a ton of summary statistics very efficiently on this file. So this is a couple of million row files. And in very few, a very short amount of time, we will give you like the minimum, the maximum, the approximate, unique, the average, the standard deviation, the quantiles, and non-allocals, and a lot of stuff for all the columns in the file, right?

And this is a single pass thing. So we read through the file once using our efficient implementation. We give you a good overview of what's in the file. And I said that we're not going to care too much about performance, but I couldn't resist but saying despite this being one of the most flexible CSV readers out there, it also is the fastest, okay? So this is cool.

Complex queries and duckplyr

So now we've got the frustration of running queries a bit out of the, of, sorry, of reading CSV files kind of sorted. Good. But let's do a bit more complex queries, okay? I didn't get to this part with Postgres because it was too frustrated to kind of continue at some point. But we're actually not here to read CSV files. We're here to answer analysis questions.

So here I have a totally made-up analysis questions. So given this data set, give me the number of non-Europeans between 20 and 40 that live in Auckland area from the 2018 census and group that by sex. Okay. So if now somebody has to translate it into a SQL query, if you have generative AI, maybe you can try, but I have just written down the SQL query. And it's basically, you can see it's not, it's nothing dramatic. It's doing a bunch of things where we like joined all these data sets together because the columns are actually codes and not actually values, except for the count, which is sometimes a value, but sometimes also C. And it is a bunch of cleanups because the statistics people in New Zealand somehow include totals in all the dimensions and there's rows in the main data that are aggregates of either other. And we obviously don't want that in a tidy data set.

But if you just ignore all this, we can run this fairly complex query and it completes in one second. That's kind of weird, right? Because, oops, sorry, I wanted to go here. It's kind of weird because I just told you it took 1.1 second to read this file, right? How can you, oops, sorry, how can you run this complicated query over all the files, joining them all together, doing aggregation along multiple things, doing a bunch of projections, filters and all that in one second? Well, again, magic.

And what the reason is actually what DuckDB does, it takes the query and turns it into this query plan, which we call a query plan, which is kind of a high-level concept of how the query should be executed. And this is optimized and then executed in parallel. So the filters, for example, they move all the way down. So we remove data from the path of the data for the query as early as possible. We prune all the columns that we don't need as early as possible. And it's a streaming engine, which means we don't actually have to materialize any of this in memory. And yeah, so it's really efficient.

But then I realized that not everybody loves SQL. I realized this. So let's explore alternatives, shall we say. So obviously we can also do this in dplyr, which I think is much nicer to write and to sort of reason about. So I've written down the exact same thing in dplyr, and I've actually checked whether the results are equivalent, and they are. So this is the same kind of thing where we basically make this sort of joined together data set with all these joins and the mutations and filters. And then the very bottom we have our final analysis on this free current data set, and it takes 15 seconds.

It's not so terrible, but this is exactly why on this reasonably sized data set, we do see a significant difference in speed, just because we don't have a sort of holistic optimization happening here. dplyr is eagerly executing everything as it does.

So enter duckplyr. So we've been actually working with Posit to create sort of a hybrid mutant of dplyr and DuckDB. And the result is duckplyr. I mean, you have maybe seen the stickers I've put a bunch outside. So massive thanks again to Kirill for his hard work on this. It's dplyr, just re-engined with DuckDB. And you have the exact same syntax, so the syntax is exactly the same. And this, from the CSVs, directly finishes in 1.5 seconds.

So that is because we have done this high-level reasoning about how this query should be executed and directly on the CSV files, and we can do this 10 times faster. From parquet files, it's actually half that, so more on that later. If you run the same query from the data frames that are already loaded into our session, it's roughly a third of this time. So if you're not doing the CSV parsing, it will get in faster.

But I want to talk a little bit more about duckplyr. And while you read this, I'm going to have a sip of water.

So if we just run this on standard dplyr, this sequence of events, we're reading 35 million rows. This is what the data set contains, 35 million rows. And then we run this filter. Well, you can't really see it in the script, but the filter only matches to 14,000 rows, because that's just, you know, the way that the data is distributed. So we're throwing away all this other stuff except for these 14,000 rows. And then it's getting worse, because in the summarization, we only ever look at one of the columns. So we read the five other columns for no reason at all.

And what we actually need to make this better is, as I mentioned, we need holistic optimization, where we can derive which columns are going to be used, which rows can be pruned earlier, and all these kind of things. But now we have this problem that if we now start making these data, data2, and results somehow lazy, we would break the integration with other tools. Like, say we make this a lazy sort of placeholder object. We want to pass stuff into ggplot. ggplot would say, I don't know what this lazy thing is. So we somehow have to make the dplyr objects both data frames and lazy placeholders.

And we have thanks to this ALTREP framework, which is really exciting. In R, you can kind of make these objects that are sort of both things at the same time, and it can be both lazy and non-lazy at the same time. So if you use dplyr, these objects data2 and result will actually be data frames, and you can't really see a difference without... other tools cannot really see a difference, but we internally know that there are lazy representations, and we can do the holistic optimization.

So this is really cool. If you want to try it out, it's on CRAN. There is a way of enabling telemetry if something goes wrong, and I encourage you to do that if you want to help us improving dplyr duckplyr. But we've also been super sensible in the sense that it will fall back to dplyr implementation if there's something we don't understand in there, so that you should actually not see a big difference when you just start using it. So I encourage you to do that.

IBIS integration for Python

Okay, duckplyr. I also want to show IBIS briefly, because the cool thing about DuckDB is it's an engine that is kind of agnostic and multi-platform. So DuckDB also runs in Python, and I don't know if Filip is here, but he made this cool integration with IBIS that basically means you can write the same query again in IBIS syntax, which is this Python syntax that looks a bit like dplyr, and it will produce the exact same results,

and it will also take, in this case, 1.3 seconds, because it ends up also just being a DuckDB query plan. So it's really cool.

So now we've done complex queries with DuckDB, and I've shown you that we can basically do very, very complicated questions on fairly annoying datasets very quickly, and we don't really have to fall back to my favorite programming language, SQL.

Okay, but we've kind of... So far this has been a bit of a defense, right? I've been defending against the accusation that databases are frustrating and slow. So I don't think DuckDB is frustrating and slow, but the thing is that it's more to using data management systems than, like, reading CSV files and running queries on them.

Atomic datasets with DuckDB

And so I want to talk a bit about atomic datasets with DuckDB, and atomic is a positive thing. So just imagine it's the 50s, okay?

people say, yeah, okay, let's not use CSV. And I agree. I'm a professor of data engineering. I agree. We should not use CSV files. We should use Parquet files. That's true. And DuckDB can actually help with this. In case you don't know what Parquet is, it's a parallel data format. It's really cool. DuckDB can read and write Parquet files very efficiently.

So tip one, you should probably... Step one before using DuckDB, consider using Parquet if you don't fully trust me yet. That one is a no-brainer. Yeah, and so we have native support for it in DuckDB. There's other options, like Arrow has support for Parquet files or the new Nano Parquet package from Posit. The interesting cool thing about Parquet file for this dataset is actually that if you take this file, which was an 800 megabyte CSV file, if you turn it into a Parquet file, it's only 71 megabytes, which is smaller than even the zip file that we had before. And it's actually much more efficient to read. So there's nothing not to like about it.

But then what happens, and I'm sure you've all seen this, is like I've done a bunch of stuff in this folder with all these files. I've done some conversions. I've done some scripting. I've done all these things. And now I end up with this ungodly mess of files, right? And there's no more sort of tracking what came from where and what version was, you know, used to create this and what are these scripts doing. And some people use makefiles there. And then that makefile essentially interprets the files, the timestamps there. But I don't think that's a great thing either. But we get this ungodly mess of all these files.

And what we should be doing better, and actually DuckDB can help here because we can, in a DuckDB file, we can store many tables. We can store views. We can store functions. All of that in a single file. So what we can do is we can create here in this file, example, we can create a single DuckDB file that contains all the CSVs in parsed, columnar, typed, compressed, binary formats. The ingestion can be atomic. So if I wrap this import statement there in a transaction, it either all goes through or nothing goes through. The resulting file is also around 100 megabytes. So it's about the same as the zip file, just much better.

And we actually have, a couple of months ago, we have finalized our storage format. So this format is now, the DuckDB file format is now stable and will remain stable for the, you know, foreseeable future. And there's actually some organizations like Hugging Face that are already shipping all the data sets that they have, which is really crazy, as DuckDB files. So you go Hugging Face data set, you click a button and you get the DuckDB file. So it's really cool. They can even be read remotely via HTTP and S3. So it's really cool. So we put the data in this file.

But that's not all. We can also do things like this, where we can create something called views, which are like a sort of a virtual table that you define a transformation using SQL or you can also do this using some other tool, but it doesn't really matter. And these views, they're actually stored in the file and they travel with the file. And it makes it actually much easier to write high-level queries. And you can stack the views in order to sort of describe like multi-step derivations. And that means that whenever you change something in the base table, the views will actually update. They don't cost any storage because they are just sort of symbolic descriptions.

And this hides, it's possible to hide a lot of complexity using this kind of tricks. And you can share the file with somebody else and they can look at the file and see the view and can directly use it. It's still a single file.

Another thing that's really cool in DuckDB that you can create like macros, which are like sort of shortcuts into sort of things that you're using a lot. So on the top here, we're kind of repeating ourselves a lot because they have used these total columns in all these different dimensions there. And we kind of have to filter this out in order to not muck up our summaries. But you don't want to repeat like these ugly expressions 15 times. So you can create a macro and say, okay, I want to have a macro not total that has an arbitrary complex expression in it. They can just use it. And again, the cool thing is that macro will live in the file. So that means that you can send the file to somebody else and they can use it. It's really cool. And it's together with all the data, together with all the views, together with everything.

Transactions and atomicity

And finally, I want to talk about transactions. I know that transactions are something that you may remember from a database course where, you know, we move some money from A to B or something like that. But Mark has actually implemented the first really great transaction management for analytical use cases where we're changing data a lot, we're making, we're ingesting a lot of data, we're updating a lot of data, we're adding columns, removing columns, that sort of stuff. And that means we can actually make these atomic, this is why I said atomic, atomic sort of sequences of changes to our data that either all go through or not.

And we can even do a manual rollback if we did something by accident, if we didn't sort of intend. So if I go back to this example here where, you know, I couldn't read the file because it has some dot dot C in it, you can just say, oh no, this was actually not what we want. You can roll back and your file will be unaffected and the data will be unaffected, okay?

There's a bunch of more stuff. These are just three examples I wanted to show that data management systems actually have features that go beyond what we're kind of used to, what we're using. They go beyond what we're used to in like an analytical environment and they can actually be used for good.

Just put your data inductively. Not doing so will only be slower and more frustrating.

But now I have shown you what we can do with DuckDB and similar tools. But I think it's an interesting question and I would like to take the last couple of minutes of the talk to actually discuss that. It's like, what does this actually mean for data, for data engineering, for data infrastructure? What does it mean if we have tools that can actually, you know, comfortably run through gigabytes of data on your laptop?

The data singularity

And there's been some discussion on this, uh, whether big data is dead or not. And this is actually a case where I use GenAI to, uh, to create a picture and I quite like it. So, maybe it's a overrated duck picture generator. But let me take you on a bit of a, um, thought experiment, okay?

if we have, if you say, let's just do a thought experiment on data creation. The human typing speed is roughly 200 characters per minute. The working population in the Netherlands is about 10 million people. I will assume I have infinity money because I've, you know, I'm a VC or something. And, um, I've employed all of them. All 10 million. And the work year of the Netherlands has about 100,000 minutes, give or take. So if I employ the entire population for a whole year to type important data, I'm sure, into a computer, we will end up with roughly 200 terabytes of data. Well, this is, of course, ASCII text, which is compressible, so if you compress this somehow reasonably, we will end up with something like 60 terabytes.

And that sounds like a lot, right? 60 terabytes. But it's actually not. So you can buy these hard disks. They cost around $400. There's a banana for scale. I mean, we have seen IBM hard disks from the 50s, right? They were like these things that you lift into an airplane and things like that. No, no, no, no. These are tiny. So the maximum sort of output in usable data of the entire Netherlands working population would easily fit on these two hard disks. There's no scale-up required. I don't have to buy a Hadoop cluster for this.

In two years, it will be one disk. Will we have two times the amount of people in the Netherlands? I sincerely doubt it. Okay? In ten years, this will fit on a laptop. The human growth in people, and people that can type anything or produce any meaningful data, is growing much slower than our capabilities in crunching through data. And I will go a bit more into that.

In two years, it will be one disk. Will we have two times the amount of people in the Netherlands? I sincerely doubt it. Okay? In ten years, this will fit on a laptop.

We look at compute. The Apple M3, pretty standard computer, I'm sure some of you have one, has 16 cores. They are very fast cores. They have 400 megabytes of cache, which is totally insane, right? It's something that these are the memory that the CPU can access in one or two cycles, right? It's insane fast memory. They have 400 megabytes of it. I'm sure lots of data sets that you use every day are smaller than that. They can all be in the CPU cache, no problem. This kind of performance is a hardcore server machine from 2010. Again, I'm talking like big iron kind of stuff.

These CPUs also have 400 gigabytes per second in RAM throughput, and you can get these machines with hundreds of gigabytes per RAM. The MacBook can have, I don't know, 100-something gigabytes of RAM. So you have 400 gigabytes per second RAM throughput, and that's also completely insane in terms of performance, and we have kind of forgotten how fast this is going, and we're not realizing how much, like how other data sets are growing in comparison. Similar for SSDs, right? The SSDs are a total game-changer for data processing because of their speed. Current MacBooks, again, 5.5 gigabytes per second in I.O. from the SSD. It's insane. And with DuckDB, we can actually take advantage of these capabilities of the hardware, for example in the SSD because, as I said, we can offload intermediates to the SSD. So you can go much larger than RAM with these things, and you won't really notice the difference. But that's not...

Now we can go to bold prediction time. Fun stuff. So the data is growing. Yes. But it's probably slower than we thought. There's a limited amount of people pushing buttons. I think this is something that we sometimes don't realize. There's automatically collected data, yes, but it tends to be less valuable, more repetitive. So here we just plot the useful data growth over time. And for the purpose of this argument, it actually doesn't matter whether the axes are logarithmic or not.

As we have seen, the commodity compute hardware has been improving like absolutely crazy in its capabilities to crunch with data with the right software. And it's faster than the server hardware somehow now. It's really crazy. But we can probably agree that single node capabilities grow faster than the data sets are growing.

In the past, there was a very large gap between what a single node could do, a single computer, a MacBook could do, and sort of the device, the 99% percentile of size of data sets. that meant that we had to go to distributed solutions very quickly, like big clusters of computers. Now it's less so, right? Now we are much closer. And we're probably looking at the data singularity at some point, where 99% of data sets are actually comfortably processable on a single computer. So this is really cool.

It's really a fun thought. And this is especially important because research and a rant like this paper have shown that going through distributed environments is increasing the cost so massively that we actually have to make huge clusters in order to justify these costs. It's such a huge impact in terms of cost and time that if you can run something on a single node, you probably should. And this was always just a hunch, but I can really recommend this blog post. It's called The Redshift Files, The Hunt for Big Data, which is a blog post that talks about a recent paper from Amazon, who has for the first time actually confirmed this because they have published detailed statistics and data about the data sets that they see in their Redshift big data processing system. And the answer is, shockingly, that we're actually quite close to the singularity already. The 99% of data sets is actually something that does now fit on a small laptop.

And the answer is, shockingly, that we're actually quite close to the singularity already. The 99% of data sets is actually something that does now fit on a small laptop.

I'm going to do one more round of experiments. I can't really do another talk here. As a database person, I feel very uncomfortable doing no benchmarks. So I'm doing one more experiment. So we're going to use a data set from TPC. It's the standard benchmark data set, TPC-H, standard analytics benchmark data set. And we're going to run Apache Spark versus DuckDB. Okay? And we're going to run this on a fairly beefy Amazon box. I think it's like 32 nodes. Yeah, 32 cores of CPU. Each node has 64 gigabytes of RAM. And we're running scale factor 100, which is like a 40 gigabyte data set. Not crazy, right? If we run this on a single node... Actually, we can't, because

Spark can't run on a single node. If you run this on a single node or two, it will be seven times slower. Why is that? Well, it comes back to something I said before. First is the per-core performance in Spark that's pretty problematic. And the other thing is this cost paper that I've also mentioned, that running something in a distributed system is actually much more expensive than running it on a single node.

So on a single node, this is slower. But then you say, Hannes, you're being unfair. How can you dare comparing a big data distributed system with your scrappy little database? Okay. We scale up. More computers. We're adding computers, and we're adding computers, and we're adding computers. And it turns out that even if we use 1,024 cores, it will still be slower than the single node DuckDB instance.

We're still paying for all these 1,024 cores here, and we're still producing CO2. And one thing I think that's worse, even, is that we have our high-paid analysts waiting for the EMR cluster to come up, which I think is even more expensive than the actual compute here. And this never catches up because of this coordination cost in distributed systems.

And one thing I think that's worse, even, is that we have our high-paid analysts waiting for the EMR cluster to come up, which I think is even more expensive than the actual compute here. And this never catches up because of this coordination cost in distributed systems.

DuckDB extensions

So that was what I wanted to say. I have one more thing. And that is I want to briefly talk about DuckDB extensions. So we have realized that we are clever people, but we cannot really deal with everything. We don't understand everything. So we are actually opening up DuckDB for external extensions. Anyone. We call them community extensions.

So you can expand DuckDB with new functions, new formats, new syntax, new protocols, new compression — whatever you want. And this has so far been reserved to crazy people, but we're now opening this up to everyone. So all you do is you send us a pull request with your extension. It's like a descriptive file. And basically you have a template for extensions that is a C++, but we're working on extending this to Rust and other languages.

And once you do that, you can actually install these extensions directly from within DuckDB and use them. And we think it's really going to be the next big step for us is to become an ecosystem of specific data transformation tools that are connected with a high fabric, a high-performance fabric that connects them together. This is kind of missing, I think, from the current analytics toolkits. It's like this idea that you can connect everything with consideration for parallelism and all that. And we're going to make this possible with these community extensions. And it's all going to be accessible for a single, sort of unified interface.

So that's kind of it. In conclusion, databases are not frustrating and slow if their database is DuckDB. There's more cool database features beyond reading CSV files and running SQL queries. I really think single node is the future and that big data is kind of dead. And actually, I'm quite happy about this. And if you're still not done with me, tomorrow afternoon, we're actually running DuckCon here in Seattle close by, and there's still some tickets available, so it's free. If you want, you can go to our website and check that out. So with that, I thank you very much. Thank you.

Q&A

Thanks, Hannes. Personally, I really enjoy it when a keynote speaker just destroys something that I've said in the past. So we have a few minutes for questions, so let's start.

Since you can store all tables, views, and macros in a DuckDB file, how does a user explore the file to see what's available? That's a great question. So you can open up the file, and we have a command called show, and it will show you what is in the file. So it will show you, oh, we have these tables, we have these views. I don't think it necessarily lists the functions, but there's also ways of exploring that. But you just type show.

So you said DuckDB is really an analytical database, but can you still kind of use it in lightweight transactional content? Like if you've got a Shiny app that's collecting a little bit of data, how well does that work? Yeah, I think what you shouldn't be doing is make a loop with a thousand iterations, and every iteration of that loop inserts a single row and then commits. Don't do that.

But if you want to, you know, do a couple of hundred thousand, a few changes, that's not an issue, right? You can definitely commit a single row, no problem. It's just not going to be competitive, let's say, with Postgres. I will say I have a Shiny app that generates Madlib stories. I don't know if you know what a Madlib is. Yeah, yeah, yeah. And it logs every Madlib to Mother Duck using DuckDB, which kind of works amazingly well.

I always love, one of my other favorite examples is in Big R Query, which works with Google's Big Query. It uses empty cars as this test data set. I just love the thought of using these massive data sets that can deal with gigabytes of data and just sending it 30 rows of data at a time. They love you too at Google, I'm sure.

Why did you name it DuckDB? Ah, it's a great question. Why is it called DuckDB? So, I live on a boat and I wanted a pet. Boats are kind of dangerous for pets, so I figured I'm going to have a pet that can swim. So, I ended up having a duck called Wilbur. Wilbur at some point grew up and went away. But DuckDB is named in his honor. Not to name drop, but I actually met Wilbur. You have met Wilbur! It's true.

Help me, I am lost. If I load my big CSV into R, it crashes. Would this be different using DuckDB? Yes. The reason why it crashes is because when you load a CSV file into R, it will have to go into memory. If your big CSV file is bigger than memory, at some point the operating system will say, you process, we're out of memory, you die now.

In DuckDB we can use memory to store data, but we can also use the disk. What you can do is load DuckDB up, and we can connect to a file on disk and move the file there, and then we'll never materialize this file in memory entirely. That's, I think, one of the really cool features, that you can go larger than memory in datasets because you never materialize this file in memory. That even applies to intermediates, which is really important. I had this example earlier when I said what if you sort your terabyte file? Traditionally, that has to go into memory to be sorted, and then it would crash because we don't have a terabyte of memory, but we have made a very clever sorting algorithm that can use the disk and offload efficiently, and then it will work.

How well does DuckDB scale up to really big and complex database systems, so hundreds or thousands of users loading and retrieving data, hundreds of tables, user indication? That's a great question. If thousands of users at the same time is not really something that DuckDB is made for. It was made to support in one way the analytics use case, and the other way a data transformation pipeline use case. If you're trying to run with thousands of users, it's probably not the tool for you. Hundreds of tables is not a problem. Hundreds of columns, thousands of columns is not an issue. Complexity of queries is not an issue, but the concurrent use by thousands of users is still something that's not really the design goal, let's say. But I can plug MotherDuck here, so it's kind of what they do.

Kind of a related note, how does DuckDB handle read and write concurrency? Yeah, read and write concurrency, so we use MVCC, multi-version currency control, which basically means that a query will see a consistent local state, and then when we commit, we have to check whether there have been any conflicts, and that happens automatically in the background. So basically, you can have many, many threads at the same time reading and writing to the database, and there is an automated check that sees if there's any sort of conflicts that have to be resolved or cannot be resolved. But in principle that works fine.

Cool. Does DuckDB work well reading and writing data and cloud object storage, like AWS CS3, Azure, Bobstor, etc.? Yes. That was an easy one. Yes, it does.

At my workplace, CSV is the default data format for data sharing. Is now the time to switch to Parquet? Yes. I mean, yes. I think that the time was five years ago, but...

Let's see if I can give you any more easy questions. Here's a tough one. Are there any downsides to using DuckDB you can think of for data analysis, either for interactive work or production apps? I think the kickbacks from your Oracle rep will be less. Downsides... I mean, I'm the wrong person to ask about this. I think what we really... We see a lot of feedback from users on GitHub, right? We see a lot of issues. Not that many, but we see issues reported. We have discussions. There is design choices that work for some people and don't work for other people.

Are there any downsides of using DuckDB? I think there are definitely use cases that it wasn't made for. Like you said earlier, if you're suddenly going to use it for a transactional use case, it's not the tool for the job. It's an analytical system. If you were to force it to do that, that would be not great. Any downsides? I'm really not the person to ask for this, I think. So, no downsides. Ask somebody else. Thanks so much. Thank you.

Closing remarks

To close out the conference, I just wanted to say thanks to everyone. A really big thanks to the speakers. The reason why you all came here. They have put so much time into crafting amazing talks. Working with our partner, Articulation. They've done training for multiple weeks. They've practiced their talks multiple times. All the talks I've seen have been fabulous. I really appreciate all the work that our speakers put in. I'd also like to thank our sponsors. Epsilon, Synchro, Land Analytics, Taurus, Jumping Rivers, and Procogia. Thanks for giving us money.

This conference is really a massive team effort. There are about 100 Posit employees here at conference. Everyone has been working so hard. The people you have seen, the people you haven't seen, in the background making sure everything that's happened in the background have never made their way to you. A big thanks to everyone at Posit who has made this conference happen.

Finally, thanks to all of you. I just know it's like a fire hose of information pouring into your brain over these two days. I just appreciate everyone's time and energy and enthusiasm. Thank you.

The one thing I know that you're all really here for is to learn about where PositConf is going to be next year. Or PositConf or FromPositImportConf if that's what you prefer. I didn't get a light round of laughter about this joke from last year. I really appreciate that. It's going to be Atlanta in September 2016 to 2018. We'll be sending out more information to the email you signed up with in the very near future. We'll have opportunities, as always, to grab some early bird tickets. We are super excited to see you all there next year. Thank you. Thank you.