Let's welcome Pedro Holanda for his talk on DuckDB and a magnificent snake duck.
Yeah, you guys can be surprised at anything you can find as a rubber duck these days,
you know.
All right, so I'm Pedro Holanda.
I am one of the main contributors of the DuckDB projects, which is an open source database
system, and also I'm the COO of DuckDB Labs.
And today I'm going to be talking a little bit about how DuckDB can bring analytical
SQL power directly into your Python show.
So to give you a little bit of an idea of how the stocks look like, I'm going to start
with what is DuckDB.
So I'm here talking about one more database system.
I want to motivate you guys that we actually needed to do one more database system.
The other ones didn't solve the problems we had.
And then I'm going to go over the main characteristics of DuckDB, so what actually makes it special.
Then I'm going to go over DuckDB in the Python land, so how DuckDB integrates in the Python
ecosystem.
I'm going to do a little demo.
The basic idea is that we're going to use the infamous New York City taxi data sets,
and we're going to try to do some estimation of fair costs, and we're going to use DuckDB
partners and PySpark just to see a couple of the differences of the things I'm going
to be talking over.
And then some summary of the talk.
So what is DuckDB?
Well, DuckDB was actually born at CWI, which is the research center of mathematics and
computer science in the Netherlands.
And what we actually had there is that a lot of the projects, the PhD student projects,
the master projects, they are very data sciencey.
So usually you have a data science problem, and you want to throw a database measurement
system at the data science problem because you're handling data.
So initially we were like, OK, we can probably use a database server, use a database connection,
and then just transfer the data from the relational database to your Python terminal, for example,
like where your analytical tools are.
And it turns out that's quite a bad idea, because you are transferring a lot of data.
So that's pretty costly.
And then you're like, OK, this is really not solving our problem, can we draw inspiration
from somewhere else?
And then, of course, there are SQLites, the most famous database out there, at least the
most used one.
And it has quite a nice property, which is being an embedded database system.
Being an embedded database system, it means it can run inside your Python process.
So you can eliminate this data transfer cost.
SQLite comes with one design decision that is a transactional database, so it's actually
super optimized for small updates, but it's not really optimized for analytics.
So we kind of wanted to do SQLites in terms of being easy to use and eliminating this
data transfer cost, but focusing on analytical queries.
And that's kind of how the database was born, and that's also why we frame it as a SQLite
for analytics.
It also has a very simple installation.
So if you think about Python, you just do a bit so, and you're good.
This is embedded, there's no server management.
So let's say you just want to, I don't know, query a pre-kit file, two lines of code you
can write, query it.
Like, there's no starting of server, there's no schema creation, the schema is inferred
from the object, so it's very easy, very fast.
And we also really focus on this fast transfer between analytical languages and their tools,
like in Python and R, to DuckDB.
DuckDB is currently in pre-release, I think the last version we released was 0.6, 0.7
is coming up soon.
I need the web pages like a little bit more details about all the things that are in each
release.
All right, so I'm going to go over some of the main characteristics of DuckDB, particularly
like the color data storage, a little bit about compression.
I'm going to talk about vectorized execution, so these are all like core database stuff.
Actually talking about vectorized execution engine, it's going to be difficult because
Professor Bonk is here and he actually created that, so I'll try to do it correctly.
A little bit about end-to-end core optimization, parallelism and beyond-memory execution.
So color data storage, well, there's basically two ways that you can do it.
One is a raw store, a scone store, as an example of raw store, we have SQLites, and the whole
thing about the whole idea is they're storing your data consecutively in memory per row.
So that basically means that if you want to fetch an individual row, that's very cheap
because it's continuous in memory, however, you always have to fetch all the columns.
So analytical queries, usually you have very white tables, but you just want to really
get a couple of these columns.
So what if you only want to use a field?
So in this example, what if you just are interested in the price of a product, but not the stores
as sold, right?
In a column store, you actually have your layout that the data of the column is consecutively
in memory.
So if you want to access just a couple columns, you can actually have immense savings on this
KIO and memory bandwidth.
So that's why this type of format is really optimized for analytics.
So to give you a more concrete example, let's say that we have a one terabyte table with
100 columns.
For simplicity, let's say all the columns have the same size, and we just require five
columns of the table in our analytical query.
So in a raw store, let's SQLites, reading this whole table, if you have a disk with
around 100 megabytes per second, it will take you three hours.
If you were using a column store model, which is what Pandas inductively does, for example,
using these five columns from disk, it takes you eight minutes.
So there's a huge improvement by just setting up the correct storage formats for your workload.
Compression.
Well, I'm not going to go into a lot of detail about the compression algorithms that we implement
in Duck2B, but what I can tell you is because of having a column store, you're going to
have your data from your column continuously in memory, which gives you a very good advantage
to compressing it, because usually the data from the same column is somewhat similar.
So you can apply cool things like RLE, FSST and CHIMP for floating point numbers, FSST
for strings.
So you can start applying all these algorithms and really decrease the size of your data.
So in this table here, we actually have, I think this is from one year ago, one year
and a half.
0.2.8 from Duck2B.
We had no compression at that point, and then a year and a half later, we actually managed
to implement all these things, which got us five times better compression, Y19, for example,
3.18 better compression in the taxi data set that I'm going to be using later.
And why is compression so important?
Well, if we go back again to the same example, where we were reading our five columns, and
it was costing us to read them from disk eight minutes because of the storage formats, if
we compress these columns, we suddenly don't have to read 50 gigabytes anymore, right?
You read less.
And then of course, you apply like the best case from what I showed you from the last
table, five times, there are increases that cost you one point, one minute and 40 seconds.
So execution, well, there's three ways of doing a query execution.
There's actually one more, but it's not in the slides.
So our SQLites use the top-of-the-time processing, which means that you process one row at a
time.
Pandas uses column-of-the-time processing, which means that you process one column at
a time.
And DuckDB uses kind of like a mix of the both, which is a technique developed by Peter, the
vectorized processing where you process batches of a column at a time.
So basically, the top-of-the-time model from SQLites, it was optimized for a time where
computers didn't have a lot of memory.
There was low memory to be used because you only need to really keep one row in memory
throughout your whole query plan.
So the memory was expensive, that's what it could do, but this comes with a high CPU overhead
per tuple because you're constantly resetting your caches, you don't have any cache-conscious
algorithm running that piece of data up to the production of your query results.
If you go to the column-of-the-time, which is what Pandas uses, this already brings like
better CPU utilization, it allows for SIMD.
But it comes with the cost of materializing large intermediates in memory.
It basically means that you need the whole column in memory at that point to process
for that operator.
And of course, the intermediates can be gigabytes each, so that's pretty problematic when the
data sizes are large.
And that's why you see, for example, that Pandas, if your data doesn't fit in memory,
what does it happen?
It crashes.
And then if you go to the vectorized query processing, it's actually optimized for CPU
cache locality, you can do SIMD instructions, pipelining, and the whole idea is that your
intermediates are actually going to fit here in L1 cache.
So basically you're going to be paying this latency of one nanosecond to be accessing
your data throughout your query plan instead of paying the latency of a main memory, which
is also the case of a column database, which is 100 nanoseconds.
It seems like a small difference, but when you're constantly executing this, this really
becomes a bottleneck.
And to end-score optimizations, of course, something that we have inducted to be, so
we have stuff like expression rewriting, join ordering, subquery flattening, filtering,
projection pushdown, which is a bit more simple, but it's extremely important and brings a
huge difference in the cost of the query.
So here's an example of a projection pushdown.
Say you have a table with five columns, A, B, C, D, E, and you want to run a query, that's
pretty small, but the query is like a selects minimum from column A, where there's a filtering
column A saying the column A is bigger than zero and you group by B.
So the whole point of this query is that you're only using two columns of the table, right?
So what the ductdb optimizer will do is like, okay, in this scanner, I know I don't need
all the columns, I just need N and B, and you just don't have to read the other ones.
If you do the same one in pandas, for example, you can apply your filter, and then you have
the filter, the group by the aggregator, but at the time you're doing this filter, you're
still filtering all the other columns you're not going to be using your query.
Of course, you can manually make this optimization, but it's pretty nice that the database system
can do that for you.
Of course, the ductdb also has automatic parallelism and beyond-memory execution, so ductdb has
parallel versions of most of its operators, I think all of our scanners, including with
insertion order preservation of parallelize now, aggregations, joins, pandas, for example,
only supports single-threaded execution.
We all have pretty good laptops these days, right?
So it's a shame if you cannot really take advantage of parallelism.
And ductdb, again, supports the execution of data that does not fit in memory.
It's kind of the never give up, never surrender approach, it's like, we're going to execute
this query.
We try to always have graceful degradation, also, that it just doesn't suddenly crash
in performance.
And the whole goal is really to never crash and always execute the query.
All right, so a little bit about ductdb in the Python lens.
Basically we have an API, it's a dbapi to.ocompliant, so, far too much what SQLite has, for example,
you can create a connection and can start executing queries, but we also wanted to have
something similar to the data frame API that still could, people that can't come from pandas,
for example, could still have something familiar to work on.
So here in this example, you can also create a connection.
You can create this relation, which kind of looks like a data frame, you just point it
to a table, you can do a show to inspect what the table is inside, and you can apply, for
example, these chaining operators, right, like a filter, a projection.
So in the end, this is all lazily executed, and this also allows you to take advantage
of the optimizer of ductdb, even if you do the chaining operations.
Of course, I talked to you about memory transfer, so we were very careful as well into being
very integrated with this, very common libraries in Python.
So with pandas and pyarrow, for example, what we actually do is that in the end, for pandas,
the columns are usually not pyarrows, which turns out to be RC vectors, which turns out
that's also kind of what we use.
So with a little bit of makeup in the metadata, we can just directly read them, and they're
all in the same process, right?
So we have access to that piece of memory, which in the end means that you can actually
access the data from pandas in ductdb without paying any transfer costs, at least constant
transfer costs just for doing the metadata makeup, let's say.
And there's the same thing with pyarrow.
We also have what we call zero copy, so we can read error objects and output error objects
without any extra costs.
With NumPy, we also support SQLCAMY, and in IBIS, they're actually the default back-end
from them, I think, since six months ago.
A little bit of usage, so as you can see, this is our PyPy download counts.
The Python library is actually our most downloaded API.
We have APIs for all sorts of languages, and you can see that in the last month, we had
like 900,000 downloads, so there are a lot of people there trying out and using ductdb
in their Python scripts.
So now it's the demo time, let me get this, all right, this looks like you can see.
So this is just installing ductdb PySpark and getting our yellow trip data dataset,
our executor, this, our database, just importing the stuff we're going to be using, and here
is just like getting a connection from ductdb, creating a relation that's just, okay, we're
going to, as a parquet file, ductdb can be parquet files, and then you can just print
to inspect what's out there, right, so if we run this, we can see like, okay, these
are the columns we have, we have vendor ID, we have pick up dates, time, passenger counts,
you have the types of the columns, you can also have a little result preview, just have
an idea of what it looks like, so I think this dataset has about like 20 columns, maybe,
and there's just information about the taxi rides in New York in 2016, and then you can
also, for example, run a simple query here, I'm just doing like accounts to know how many
tuples are there, and we have about 10 million tuples on this dataset.
All right, so this function here is just to do a little bit of benchmarking, coming from
academia, we do have to do something that's kind of fair, I guess, so I run just five
times and take the median time of everything, and then this is actually where then we start,
so we start off with data frame, so Pundas can also read parquet files, and the whole
thing about ductdb again is that it's not here as a replacement for Pundas, this is
not run by itself, but something that can work together with Pundas, so the cool thing
is that we can, again, read and output data frames without any extra cost, so let's say
that in the query here, we're just getting the passenger counts, then the average tip
amount of trips that had a short distance, right, and we group by passengers, by the
number of passengers, so what we want to know is for short trips, does the amount of tip
matters by the number of passengers in that ride, and what you can see here is that you
can, again, read from the data frame, that's what we're doing, and we just have to use
the data frame name in our SQL query, and if you call.df from the query results, you
also output in your data frame, and it's pretty cool because the data frames have these plots
bars, they have plotting capabilities that ductdb doesn't have, and you can get easily
a very nice chart, so you see here, apparently, there's some dirty data because before getting
in tips, when they don't have anyone in their rides, I'm not sure what that is, but apparently
like if you have more people, seven to nine, maybe like the more expensive cars, you get
a higher tip, and you can do the same thing in pandas, of course, right, like in pandas
you don't have SQL, you're going to have to do, to use their own language, to do the
group by, the average, and you can directly use the plots, and the whole point here is
to show the different execution time, like, now we're waiting, okay, so took a second,
and ductdb took 0.2, so this is like a 5x, right, to 0.25, so like 4x, and you also have
to consider that we're using like a, not a very beefy machine, right, this is a co-lib
machine, imagine if you had more cars, this difference would also be bigger, and then
I added spark for fun, so actually spark can also read data frames, but it crashes out
of memory in my co-lib machine, so I had to give up on this, and read directly from par
K files, but it does output it as a data frame, I think we're going to have to wait a little
bit, but as me it's best, so of course spark is not designed for small data sets, but turns
out there are a lot of use cases where you use these small data sets, as you're going,
it's warming up a little bit, it's good for the winter, it produces some energy, I think,
alright, okay, so it took two seconds, 2.2 seconds, the actual execution, and that's
already like, what, more than two times what Pandas was, so, yeah, anyway, for the demo
of course, I showed you something that's fairly simple, can you do actually very complicated
things, maybe not very complicated, but more complicated, so here I'm not really going to
go over the query, but the whole idea is that we can just, for example, use ductDB to run
linear regression, so can we predict, can we estimate the fare with the trip distance,
and turns out you can just calculate the alpha and beta with not such a crazy query, and
then you can again export it to Pandas, and you have a very nice figure there, so you
can really combine these two to get the best out of both, alright, that was the demo, summary,
oh that's my last slide, good, so yeah, ductDB is an embedded database system, again it's
completely open source, it's under the MIT license, since it came from academia, this
is something that we're always worried about, it's to also give it back to everyone, because
it was usually funded by taxpayers money, so everyone can use it, 100% of what we do
is actually open source, there's nothing that's closed source, it's designed for analytical
queries, so data analysis, data science, has binding for many languages, so of course
I'm at the Python dev room, I'm talking about Python, but we have our Java, turns out that
Java is like one of our most downloaded APIs, so I guess that's an interesting sign, Java
scripts, and a bunch of others, it has very tight integrations with the Python ecosystem,
again the whole idea is that you eliminate transfer costs, implements the database in
relation to APIs, the relation to API again is this more data frame like, and has full
SQL support, so anything you can imagine like window functions or what not, you can just
express them using duck to be, and that's it, thank you very much for paying attention,
happy to answer questions.
Thank you Petron, so we have five minutes for your questions.
You mentioned, thanks for the great presentation, you mentioned beyond memory execution, and
kind of that it tries not to degrade as much, can you shine a little bit more light on
kind of what happens under the hood, and how much degradation happens?
Of course, I think that's, there's only the ordering operator that actually does that,
we have Lawrence that's doing his PhD, so there's a lot of operators that need to research
to be developed, that's more of a goal than something that actually happens now, but the
whole goal is that you really don't have this sudden spike in the future, but there's research
going on, in the future there will be more to be shared for sure.
Thank you very much for the talk, and it's very exciting to see such a tool, such a powerful
tool, I'm working usually with data warehouses, and I saw on the website that you do not recommend
using this with data warehouses, I would like to know why.
So of course, there's no one solution for our problems, there are cases that the warehouses
are very good fits, it turns out that for data science for example, which is kind of
what we preach the most, they're usually not good because then you fall back to the senior
data outside your database system, like you're not really going to be running your Python
codes inside the system, you can do that for UDS for example, but they are messy, they're
a bit nasty, so you want really to have it embedded in your Python process, so you completely
eliminate data transfer costs, because usually what you do is like, okay, I have a table,
10 columns, I'm going over 4 columns, but I'm really reading huge chunks of it, so that's
a bottleneck we try to eliminate.
How do you handle updates?
Although we are in the analytical database system, we do do updates, so Mark, I don't
know where he is, but he's there, he developed MVCC algorithm for OLAP, so we have the same
asset transactional capabilities that you would expect from a transactional database,
of course, if you have a transactional workload, you should still go for Postgrease or SQLize
or a database that handles this type of transactions, but Mark developed like a full-on algorithm
to handle updates completely, yeah.
How do you compare to Vertica?
I have a good question, I think in terms of analytical queries, TPCH, probably similar
performance, but then again, the whole point is that if you go again for the Python process,
the data transfer costs will take most of the time there, and then it's really catered
for this type of scenario, the embedded scenario.
We have one minute left for one more question.
Yeah, I actually have a rappel somewhere for a bunch of examples as well, I'm very happy
to share it.
I don't know where I'll post it.
Ah, the false then thing, I guess.
All right.
Thank you a lot, Pedro.
Thanks a lot.
Thank you very much.