About this talk
This talk introduces DuctTb, an advanced open-source data wrangling tool designed to simplify interactions with large datasets. The speaker, who is a professor of data engineering, discusses the common challenges faced when using traditional tools like Excel and Spark, emphasizing the need for a user-friendly solution to overcome data fear. DuctTb supports SQL, has no dependencies, and runs on various platforms, making it highly portable. It features efficient operations for handling complex data tasks such as joins and aggregations, and supports multiple file formats including CSV and Parquet. Additionally, the internal DuctTb file format allows for transactional operations and can store multiple tables and views in a single file. The speaker highlights the simplicity and speed of DuctTb, aiming to boost users' confidence when dealing with data.
Full transcript
All right. Well, please give a big round of applause to Hanis. [Applause] >> Thank you. Uh hello everyone. Uh it's uh thanks great to see you here. Uh the die hard crowd I suppose. Um I'm here to talk about DuctTb and how you wrangle data uh with DuctTb. But before I want to uh just quickly check who here has heard of DuctTb. Quick show of hands. I
think that's about half I want to say roughly half. So that's nice. Um I think at the end you can all you raise your hand because you have all heard of it. So the first thing I want to start with is a little bit of my background. So this uh was my actual license plate from when I from my first car uh about 20 years ago. So
you can probably tell from this that I have been in this sort of data space for a while. And these days I spend most of my time at ductb but I'm also a professor of data engineering at the university in na remember this movie. Yes. So um this is how I felt uh about the state of data uh about you know 10 years ago where there was
something wrong with the way we were doing data analysis but I couldn't quite sort of put my finger on top right and it uh regrettably not you know no more came and told me what was wrong with the world I had to figure it out myself uh luckily I wasn't alone with this because I was with Mark my then PhD student and we started thinking about what
was wrong with the way people were doing data analysis, right? So, we just, you know, we realized there was something wrong. We just couldn't quite put our uh finger on it. And much much later, actually, we figured out that people generally feared data, right? So in general I think the the the general impression is that whenever the data set becomes bigger than whatever fits in Excel uh
things start to get a little bit painful and then um yeah others you have to use other tools and most of these other tools are actually quite heavy like uh for example if you have used spark that's a gigantic thing to get running and to you know to work with so you would not probably not want to go straight from Excel to Spark that is like that's
really not nice. And so we thought we need to really work on bringing sort of this fear into what's towards something like data confidence where people are just confidently wrangling data sets that they find somewhere and are not really worried about you know setup or you know clicking the right button on the cloud thing and you know incurring gigantic snowflake bill or god or whatever right you
get the idea and the result of this is ductb so ductb is a um a a software project first and foremost. It's on GitHub and everything. Um, it's a advanced data wrangling tool. Um, that's kind of universal in the sense that it works with tables and tables are a universal representation of data. Um, and it's it's free. It's open source under the MIT license. So, everybody can
use it. You can build a company on top of it and you know people do. We don't care. So uh it is it is yeah just a software project that's contains the system that is there to wrangle data and I will tell you a little bit more about ductb and then later we'll look at a bit of examples together. Yeah. So first of all, duct speaks SQL.
Um, who here who here knows SQL? Okay, who here likes SQL? That's much fewer people. There's a there's Yeah, there's one I can see that. No, the um this is it's not universally loved, but it is a universal language for data. I think this is fair to say. Um, and Dr. B speaks SQL because there's just many people that understand and speak SQL. So we pick picked
the language that people tend to know but it which tries to do things very differently from normal SQL systems. For example, ductb is trying to be radically simple or I wouldn't say it's not trying to be it is radically simple. Um it has zero dependencies so you can just install it on any computer at any time. Uh it's ultra portable, runs on like your phone, Windows, Linux,
Mac, FreeBSD, risk processors, you name it. um doesn't require any Docker container, doesn't require root, doesn't require any server setup, no clients, anything. It's it's all built to be extremely simple to use. And in fact, in you know, in order to install DuctTb on your computer, you can just type either for example, if you're in pip in Python, you can say pip install DB or you can
run this magic script um that will just get it running for you in no time. And that's because we really really tried to make ductb simple to use. But simplicity doesn't mean that it doesn't have features. So ductb's batter is included. That means that it has extensive sort of operations like complex joins, complex aggregates, window functions, all that stuff. It can read paret files out of the
box, CZ files, JSON files, god knows what file. Um, it can directly talk to cloud storage like S3. um or Google cloud or Azure or whatever uh talks to other databases like it's it's really a lot of features that that are built in this very small package and the integrations the client integrations of ductbr actually exists for a bunch of languages so you have clients for R
Python Java Node command line zoo of other languages I think I have a list in the end so it's really sort of everywhere you can in your environment there's probably a ductb integration for and ductb is fast. So this is you know everybody says the database is fast but uh being a professor of data engineering I can tell you that duct is really fast as a user
it's sometimes hard to tell um but one thing that you can usually observe is that your uh your CPUs your CPUs have get quite a lot of work done uh with duct DB if you're running it because we automatically parallelize all the work that you're doing in SQL all the par all the cores that you have um And uh you know this is done with highly efficient
uh operators. Duct can even use the hard disk if there's not enough memory to complete a query. Um and all this happens completely automatically. This is like this one of the issues maybe with this is that your computer can get warm when you're using ductb. I apologize. Uh but it is it is getting warm because it's doing a lot of work. We had some fun issues with
this because we have some bug reports from countries like Brazil um where you think why Brazil? Well, it's generally warm there and computers that in Brazil are already running quite hot and if they're running ductb sometimes that pushes them over the edge. So we had people complaining about that but there's nothing we can do. Um, and again the one of the main reasons DTB is fast is
this high efficiency in operations and that is the result of decades of research in analytical data systems. So you're really getting a state-of-the-art system there. And the best part is duct is free. So what can I say? There's no cost associated with it. There's uh people like there's millions and millions of downloads uh each month. We don't get a scent from it. uh we we run one
of the bigger websites of the Netherlands at this point for duct DB because we have so many people clicking on our documentation. So it's really interesting to see. Here's a a chart that uh for JavaScript frameworks is a tiny number, but I can show you for databases this number of GitHub stars is quite a lot. Um but the place where the curve almost tried to get vertical
was when we released our 1.0 by the way. And what I also want to stress is ductb is not uh funded by venture capital. So ductb is a project that was spun out of the of the Dutch national research lab for computer science and mathematics the CWI here in Amsterdam uh where I used to work. Uh which is by the way also the place that Python was
invented at. Uh but eventually spun the project team out of the institute into this company DuctTb Labs that I'm now heading. That's uh and you would ask how on earth are you making money if the if the system is free? Um well uh we are selling support to corporate customers basically. So if you need help with ductb and you are Google then we can work with you.
Um but I I wanted to stress this because there's so many systems out there in data that um have gone through license changes and things like that in the rec recent years and that's something that we we you know we just built to to not do because we're not funded by VC money and we kind of build this company the oldfashioned way. Okay. So that's briefly about
DuctTb. Now let's look at uh everyone's favorite uh evil CSV files. Okay, who here um has had fun with CZ files? I think that's everyone. That's a that's good. Again, who has enjoyed the experience? Okay, this is this is this is this is the this is a great this is a great setup. So, here with apologies to to Mr. Rembrandt. Uh I had the AI make uh
the the people fearing the CSV file. Um again coming back to our fundamental goal of turning data fear into data confidence. um we are really trying to change this uh initial reaction that people have. But let's we'll walk through an example together uh and then we'll I'll I'll show you how duct DB is different maybe from the tools that have created a non nice experience with CSV
files. So I went on the internet and found and literally just Googled big CSV file and I found this Yeah. You know, I found this website from the New Zealand stats uh agency, and they do have a website with CSV files, like most statistics agencies. And so I went ahead and and grabbed the biggest CV file I could find. It's a 103 megabytes. It's not gigantic, but
it is actually the size where it can get quite painful. This uh Whoops, I ran into a a light. Um this is the biggest file I could find. It's a 100 megabyte zip file. It has about 34 million rows, uh, six columns. Nice. It shouldn't really be an issue because it does totally fit on memory on your computer, but uh, it is, you know, as you will
see, is an interesting case. For those who want to follow along, I put the link here on the, um, to the file on the or later uh, the link on the slide. uh we mirror the file so we don't do the stats people in New Zealand accidentally. Okay. So you find the zip file on the internet or you supposed to look a zip file on the internet.
You unpack it. It looks like this. Uh so you see aha we have some data CSV file here with 800 megabytes. That sounds that sounds a bit annoying, right? Um so if what's the first what's the first instinct when you get a CSV file? Double click. No. Excel says no. It says uh this data set is too large. Screw you. Okay. So, let's grab duct DB. Okay.
That's that's uh it will not go for Spark. Uh we'll just go use DuctTb for this. Um the first thing we want to do is want to explore what what on earth is in that file. And luckily, DUTDB makes this extremely easy for you. All you do have to do in DuctTb in order to look at a CSV file is use it as a table name. Okay,
so you type from file name. That seems doable, right? Um, and the reason there's no select there is because induct select star is actually optional. So you don't have to type it if you don't because it's actually redundant. So um you type from data8277.csv. Okay. What do we see now? Um you see that a lot has happened already. We have the columns. We have six columns. They
have names. They have types for example in 64 bar chart. Um and it actually has helpfully created a count here. 34 million um of all these rows in total six shown. And it shows you some example rows. In in fact, it shows you the first three rows here and the last three rows. The number of rows shown depends on your terminal and you can configure it. It's
not always six. It's just happens to be six for the slide. So this is quite a lot as has already happened here because what has happened here already is that we looked at the file. We used absolute magic to determine you know what what on earth the dialect of the CSV file was, whether there was a header. there is a header and what the types are. We
al have already also figured out by just you know going over the file and seeing what are the actual values in the columns so we can figure out the types. Um, and all that is something that happens in in a fraction of a second, right? This is not like type and then wait. No, no, this is a bof. Here it is. Um, and it's actually interesting and
we have spent an enormous amount of time on the CSV reader. Actually, one of our guys, Pedro, has spent an enormous amount of time on a CSV reader. And it's kind of ironic because he has a PhD in databases and the first thing we did was put him on the CSV reader and he was a bit frustrated. Um, it's not true. He actually this was his idea.
Um but the reason we did this is because the first thing you do when you interact with data is just to want to look at it. And if that is difficult, you're not going to be a happy camper. So we spend an enormous amount of time and money on this exact experience that you just type from data set and it will just work. But we're not done
by far. So DDB also has this cool feature called summarize. So you can just say summarize and then you know arbitrary query actually in this case we just repeat the query from before with the the from which basically says read this file and what you see is that we see a different representation where we see the column names the types and then the various sort of summary
statistics and we actually made this a fairly efficient onepass operation to create the summary statistics. So what we show is you show the minimum, the maximum, all the quantiles, the how many values in this column happen to be null or not. Uh loads of stuff all in this in this sort of summary uh sort of thing that is kind of the second thing that I at least
usually do with DDB on the sort of new and unknown data set. again, this is also fast. So we we we claim to have the world's best and fastest CSV reader in the same sort of package. Here's a short here's a short comparison and we actually had to sort of do something that duct would normally not do which is create a table from the file. Uh and
you can see that from the sort of typical tools here that people use to wrangle CSV files. Uh DDB is the fastest as well. So that's nice. So now let's look a bit more at what can we do with the file now that we've managed to read it. So, in case you haven't heard yet, uh, parket files are better than CSV files. Um, and DUTDB has built-in
support for paret. Um, so what you can do in ductb for example is to as a second step after you looked at the file and decided it was good, you can tell it to convert the whole file to a park file. Right? So, this is done in ductb with the copy command. So you basically say copy again the file name to another file name and it will
again magically figure out that because of the extensions here that you want to read from this CSV file and write to this new paret file. And again this is not something um that is not something that uh takes forever. This is a few seconds and uh one of the nice advantages of parquet files is they also get quite a lot smaller. So the paret file of this
800 megaby CSV file is only 75 megabyte. Nice. Um that has also has the added benefit of making it faster. Of course you can do whatever you want in this command in the in the second part. You can also put a complete query there. So here we have another copy command but let's say in this case I just want uh a subset of the columns to end
up in my p file. Right? So fine just write it there. You can order there you can do aggregation. So you can do whatever you want and and one of the cool things about ductb is that it will actually not first ingest the whole input then decide what to do with it and then write to output node. This is a single sort of streaming sort of operation
from the input file to the output file. So we're never even materializing uh this in RAM or anything like that. And I've mentioned ductb has already has integration with with cloud storage. So if you say okay I have this strange CSV file here and I actually want to put this on my S3 bucket so my colleagues can look at it or I can look at it with
another tool or whatever. That's not an issue at all in DTB. You can also just specify that the output should not be a local file but a file on S3 or on Google Cloud Storage or on Azure or on Cloudflare or on whatever, right? Like these things are all built in into DuctTb. So you can just say I want to copy this file to this uh location
on S3 and again provided you have set up uh the credentials it it will just do that right it's pretty cool of course we can also do the opposite where we um can directly again I told you we can just use file names as table names in duct DB and uh just read from them. This also works with S3 URLs, right? So here I'm just saying from
and then I put, you know, a parquet URL from from S3 that I just written before. And again, it will directly be able to read the table from that file without even copying the whole thing. This is actually quite finicky to get right is something I worked on myself. Um, so DTB will actually be able to not read the entire parket file uh from from S3 in
this case because you're only requesting the first 10 rows. So what we can do is we use these range requests. I don't know if you know range requests, but you in HTTP you can require part request part of a file. So we use that trick to actually only read the file metadata from this park file and the first couple of rows. So, this is actually quite fast
um in the um to to read this stuff, but so far we've only had extremely simple queries. Don't get confused, right? I just didn't want to I just didn't want to throw gigantic queries at you. Here is a bit more complicated query on this data set that you know I I dreamt up. This is something that duct is completely unimpressed by and can actually run this in
under a second directly on the CSV file. So here we have uh you know I think five or six dimension joins on this giant data set with aggregation with filters um and uh post filtering even I think so and uh basically that uh will also just will happily eat this query and run this for you in under a second. So this is is really a high highly
efficient query engine on you know arbitrary files essentially as long as they're tables like don't put your images in ductb there's no point tables. Cool. So that was the first part um of wrangling data where you know we'll just looked at this CSV file that we found somewhere. Next we'll look a bit more at uh something that duct also has which is in its internal storage format.
So, DuckDB has an internal storage format that doesn't have a name. We are not so good at marketing. We're not Americans. Um, we just call them ductb files. So, this is what they look like. I mean, they usually don't have even have an icon. But, um, they're just files. And what can these files do? Uh, well, let's start with many tables. So, ductb files can have many
tables in them. They can have views, they can tons of other stuff. Um, this is very different from formats like parquet which you may know or CSV which we just looked at where a file ideally only contains a single table. Right? I think in in CSV you can have multiple tables in a file but it's very evil. Um, but in in in duct file format you can
have many tables. You can have thousands of tables in one of these files. No problem. The format in the duct file format is also fully optimized for analytics. It's similar to parquet in that sense that we use a columnar storage format uh that stores individual row groups groups of rows into into blocks on on disk. Um it's actually uses lightweight compression. So that's really efficient way of
sort of storing these files. Um so they're compact with and this format is specifically optimized to do well for analytical use cases. Maybe I should briefly explain um traditional database formats like for example the poserris format or the SQLite format they're optimized for running transaction which is like small changes to data. Our file format the duct file format is optimized for running large queries like aggregations or
you know filters that touch the whole table or things like that. Um, one of the really cool things of the file format, the DTB file format is that it is fully transactional. This is something that you know the it has somehow gotten lost in the last 10 years in analytics is that file that files are But the duct file format is fully transactional. It supports asset snapshot
snapshot isolation. We really mean it. You know, it has it has the proper synchronization. Uh, it has a rider headlock. So if ductb crashes halfway through it, it will not lose anything. The blocks are check summed actually. So if you know anything gets corrupted on disk, it will um be detected. Uh as far as I know, we are the only uh database management system for analytics that
does this. So it's You can, you know, make changes and they either go through or they entirely or they don't go through at all. And one of the really cool things about the file format is it is a single file. It's just one file. You can and and and the reason we made this just to be one file is so people can email it around because in
our interaction with practitioners, we learned that people love emailing around files and people generally don't like or don't know how to email around folders. So, it's just a single file. You can give that to your colleague. You can put it on free. It's up to you. So the last couple of minutes here I want to show you some examples of what you can do with the ductb
file format that you couldn't do with uh the existing formats that you may know. So the first thing is many tables. So in this example CSV collection that I downloaded from stats NZ, we had six files and you probably know this problem when you have a data science project that you get this zoo of files and various versions of all the files and so on so forth.
Not with duct. What you can do is you can just throw all that stuff in one file, right? And here up here is the is the code to do it. And you can see this is all wrapped in this begin transaction and commit block which means that either this entire job goes through or none of it does. So we have actually an efficient way of um managing
of of of treating large changes to database files in a transactional way. So even though we are loading millions of rows in this transaction, it's now two seconds or something like that and uh it will only sort of finish commit if everything is happy. So that's good sort of peace of mind to have when dealing with data transformations because you're not like ending up with a half-loaded
table or something, right? Or you're not ending up with a double loaded table or anything like that. It's either all or nothing. And again, the file is just a file. So here I made this file A277 to duct DB. And if you point ductB at this file, you can just say show tables and it will show you here's the tables I have. I can start quering these
tables. It's just just a normal file. What we can also store in these files is views. You might know views from databases. So let's say I have some part of this ugly query from before that I need to sort of always run to sort of do the pre-processing and pre-enriching of the data and I don't want to really copy paste that to my colleagues all the time
but I can just create a view in the file that is sort of a virtual representation of this right so I just tell ductb hey you should create this view that creates this sort of virtual table that's the result of this query and again if I give somebody the file they can say show tables and they say yes it's not strictly a table. They can see the
view uh themselves and they can start to query it. Now that's really practical to um basically share these things with other people because you can again you can just give send people some some send somebody this file and they have everything they need or everything that you have set up without you copy pasting around a giant corresponding Python notebook or whatever. And here is something is really
hard to state for me, but you can update data. Like this is something that somehow got completely lost in the world of parquet files on S3 that you could do actual updates. And we're now building these crazy replacements like iceberg in order to somehow get updates back. But the ductb file format, as I said, is a fully format. So if you wanted to I don't know remove
clean up the data set a little bit um because count for some reason is sometimes dot dot C in the CSV file you know don't ask me why um you can easily do that so this file format allows you again to transactionally um change the data and then again it's just stays being a single file that you can send around and it will have the changes in
um the files are mutable and again transactionality is really cool here because it means that um if somehow this operation fails halfway through because I don't know your disc was full or something nothing will have happened. It will only give through if it all uh if it's all happening. Excuse me. I'm really happy I'm standing here today because it really didn't look like this last Friday. So
it didn't look like this like on Monday. So it's I'm I'm really happy I I get to stand. Um so that was kind of what I wanted to show about the the file format. Um briefly about integrations. I had mentioned this before. So duct DB is more is more like database as a library, right? It's not like you install a server and you have clients in in
those languages. The in it's very different. It's that the the library actually is part of these integrations directly. So for example in Python if you say pip install ductb you download the entire thing it's not huge don't worry it's not a gigabyte of stuff it's like 20 30 megabytes something like that but it's all part of it the same in in in node for example we have
recently revamped the node API for ductb um it is just a node package that contains the the binary of ductb so you can immediately within the actual node instance run your queries. So it's not like you have to set up a separate server you know that you know it has to approve. No, no, no. You install a package and you're off, right? Um again this is one
of the packages. There is one that's quite popular also is the JDBC driver and that's pretty crazy. We have a JDBC driver that contains the entire database, right? That's pretty uncommon. Normally the JDBC driver talks to something else that is the database. But in our case, we just put the entire ductb code into the JDBC driver. Um, so you can run queries in your Java app without
ever installing a database. Again, there's a there's R clients, there's like Go clients, there's OBBC, there's Vasin, there's Rust, Go Candline. What I showed today was mostly command line, but the other stuff works similarly. In conclusion, um, DUTDB is a universal fast data wrangling tool that's also free. Um, and it's really meant to stop you fearing CSV files. Um, and I just I I wanted to I
wanted to show the DB file format as a sort of alternative to to the you know file zoos that tend up tend to sort of crop up in data engineering projects. Um, yeah. And that was it. So thank you.
