0:00So why would you need DuckDB when you've got the Python peers like Pandas and Polars? How is that useful? Well, a lot of Pythonese people are asking themselves why and when would they even use a database like DuckDB within their Python workflows. In this video we'll understand what are the main
0:19features of Pandas, Polars and DuckDB and what each one brings to the table. And yes, there will be code and benchmarks. So are they enemies or lovers? They are lovers! Wait, did you just poll the entire video? So what is DuckDB, Pandas and Polars? DuckDB is a lightweight all-app database
0:40built in C++. And you probably are thinking but why I need a database within my Python environment? Even this guy said if your data fits in memory there is no advantage to putting it in a database. It will only be slower and more frustrating. Thanks are false. Except that DuckDB is an
1:01in-process database. So within the Python ecosystem it will run under the same process as your Python application. And you can just do a pip install DuckDB and you are ready to use your competed database. DuckDB is fast, blazingly fast. It contains a columnar vectorized
1:21query execution engine where queries are still interpreted but a large batch of value, a vector, are processed in one operation. DuckDB has a lot of built-in features or rather extensions like JSON support, rating, writing over history, spatial data and so forth. These extensions are nice
1:42because it prevents you from thinking of what Python package you need for some action. This extension has dependencies but it's really lightweight and it's not Python dependencies. They are downloaded and loaded directly in DuckDB when needed. DuckDB can read from different file formats but also provides its own single file ACID compliant file format. It has a Python relational
2:06API which enables you to have more a Pythonist approach when coding in Python. Finally DuckDB can query Arrow datasets directly and stream query results back to Arrow. And if you don't know what Apache Arrow is, it's an open source standardized language agnostic columnar memory
2:27format. In a nutshell it's an oversold translator for data. It's a tool that different programs use to understand and work with data quickly and effectively. More on that later. If you want to watch the getting started with DuckDB, watch out the video there or here or in the description. Jumping on Pandas. It was first released in 2008. Oh man, that was a long time ago. And it had a
2:52significant release earlier this year in 2023, Pandas 2. 0 with a lot of improvement. Because it was one of its first bringing the DataFrame concept, by the way still from Arrow language, again the Arrow first, it became the de facto standard in Python for data analysis and
3:10processing. It supports a lot of file formats, CSV, JSON, and can read and write for parquet and also directly from remote storage like S3, BigQuery, etc. Since Pandas 2. 0 it has been shifting to Apache Arrow instead of NumPy for their backend data. This change enables efficient data representation
3:31and operation and make Pandas more robust and versatile. And again, because of its popularity, it has been supported by a lot of data visualization tools like Seaborn, Plotly, Paquet, ggplot, and so forth. Alright, Polars. Polars is the new kit in town everybody is talking
3:49about. It's also blitzingly fast. It's leveraging Rust in the backend to multithread some part of the process. It also uses Apache Arrow columnar format as the memory model. Polars employs lazy evaluation to optimize query execution which can result in faster operation. It's particularly when
4:10working with a large data set. Lazy evaluation is a technique where certain operations or computations are delayed until they are absolutely necessary. So if you do filtering or ordering for example on your data set and using a lazy data frame they would only be executed until you do the call
4:31which will prevent you create and store intermediate results in memory. It's a neural library which allows to learn from the design decision and the limitation of Pandas. So based on the features of all this framework we can already see significant difference in terms of
4:49initial workflow. TugDB is a light SQL DB with a file format, built-in extension, and is SQL driven but has other APIs. While Pandas and Polars are Python data frame focused libraries. Yes, you can use Polars also in Rust. TugDB and Polars are fast and can process larger than memory data sets.
5:10However, the techniques they use are different. So let's dive into the code. So we are going to cover a simple use case where we read data locally, do a couple of transformations, and write the output data set to S3. All the codes and data set will be provided, link in the description. And because
5:29this needs to be fun we are going to answer the following question. Which websites feed the most hacker news? And if you don't know what hacker news is, it's a website run by YComic Editor where people share and discuss articles about computers and business. Users can post links to
5:49interesting articles, vote on them, and talk about them in the comments. It's similar to Reddit but focused on technology and starter topics basically. Our data set contains data from 2008 to 2022. It's about 33 millions of rows stored as one parquet file which is about the size of 5GB
6:12compressed parquet file. For each framework we'll discuss the installation and dependency, the syntax use, performance, and versatility. All are Python libraries. That being said, some features require dependencies from other packages like writing to S3. For TugDB that's
6:32not really the case as such a feature are covered by the extensions. So you don't need an extra Python package. To give you an idea about the size of all those dependencies and Python packages, let's have a look at side packages. So this is where basically all the Python packages are installed and I did three different installations. One for pandas, polars, and you guessed it,
6:56TugDB. In this case the one that required the less dependency is TugDB. It's also worth to know that still polars is a lighter weight library compared to pandas. This size is not just telling you how much space your program is going to take on your container but also in general less
7:14dependencies, less code, less code, less problems. Versatility. So as we mentioned TugDB can be used outside Python. You have a CLI with SQL interface but also binding for Rust, Java, and even recently Swift enabling TugDB for mobile. Polars outside Python and Rust has just released a CLI written
7:39in Rust to execute a couple of SQL actions. I haven't tried it and it's still really early. Pandas is ticked to Python for good or bad but it has a wide range of data visualization library support. However due to Arrow's flexible format and the fact that all these frameworks support
7:58Arrow, they can easily integrate to each other with negligible performance cost thanks to Arrow and the zero copy of data feature. For instance TugDB can provide data as either a pandas or polars data frame. In polars there is a build-in command to convert a pandas data frame. TugDB is
8:19built on SQL so you can do everything using that. That being said there is a relational API for a couple of methods to have a more Python-ish approach. In my example here to extract the domain from the URL, do a group by, and count the number of appearance, I'm doing some regex and as you can see this is how it will look like if I'm using the relational API but you can do all these
8:42operations through a single SQL query. Data frame versus SQL is always a big debate. In my opinion when working with Python I like the data frame approach because I can have a lot more hints into those methods when I construct my operation. Pandas doesn't have a SQL interface and it's more a data frame style as you can see it's really similar but you can query pandas with SQL using
9:06other Python packages or TugDB tends again to arrow. That's how it will look like if you do so. Polars while being also data frame oriented library do have a SQL interface that has been released earlier this year I believe so you can combine also both. Up to you SQL or data frame. All right
9:27let's talk about performance. In terms of performance when running all the script TugDB was the faster. A few comments. Pandas didn't manage to get it true. I could optimize maybe the codes but anyway it was really greedy for memory. When comparing against a sample of data taking only
9:452021 for the all the tree frameworks pandas was still the slower pipeline. For Polars I had to use lazy evaluation data frame otherwise it will blow up also my memory and it might sound like a no brainer because as I said it's an optimization technique but just to say that you need to be aware of those features within the framework to be able to use it correctly. For both pandas and
10:09Polars it was sometimes not clear in their documentation which Python package I need to read and write to S3 or which way is the most straightforward as I had different options. This is mostly a documentation issue but worth to mention. Oh and I forget the most important
10:28the results. So the top website are Github, Medium and YouTube which is interesting to know and not a surprise because you know it's tech news website. So if you want to be features on Acure News probably writing a Medium blog is the easiest way to get attention. All right let's wrap up. So TugDB is way more versatile than Polars and Pandas and the reason is that the scope of TugDB is just
10:52bigger. It's a full OLAP database and it has different client APIs. Through our specific use case with Hacker News we found out that TugDB was indeed faster but in Polars in number two and I'm not sure if we can put Pandas in number three because it killed my memory. We also discussed
11:11that thanks to Apache Arrow we can actually use one or more of those frameworks together as we can easily convert back and forth the data frames with no performance degradation. So should you use TugDB within your Python workflow? Well based on the features we elaborated in the first part of the video
11:31it depends on your use case. Yeah I know it's a lame answer but one thing sure is that TugDB can easily be installed with just a pip install. So you know why not do it? Just do it. It had little to no overhead to your development and you can use it for processing faster your pipeline.
11:51TLDR you can leverage the best of all worlds. That's it for this video thanks for streaming along with us today and see you next time!
