Alex Monahan, In Process Analytical Data Management with DuckDB
Recording: Alex Monahan, In Process Analytical Data Management with DuckDB
[Music] hi everybody I'm Alex I'm here to talk to you about inprocess analytical data management with duck DB so what if you could take the power of a cloud data warehouse and put it on your laptop or in a cloud function or anywhere your application is already running That's The Power of dctb first I'll say howdy howdy I'm Alex my background is industrial and systems engineering from Virginia Tech I then spent N9 years at Intel breaking into the data world first as a data analyst and later as a data scientist back in 2020 is when I discovered duct DB I was building a self-service analytics platform at Intel and pandas and sqlite were not cutting it and fortuitous Google search brought me to duct DB and it handled all the crazy SQL queries I could throw at it and at that point I realized it was a serious engine so I became a really big Twitter fan or if we're calling it X these days and because of that I was actually offered an opportunity to work part-time for duct Tob Labs doing documentation and blogging so it is the most Millennial thing that I've ever done got recruited based on Twitter really excited to be working with them dctb Labs is created by the creators of dctb they are a support services and custom Integrations company based around the open source project dtb and then 5 months ago I joined mother deck mother duck is a separate company and we're building a cloud service around duct TB duct DB is very efficient so we think we could be disruptively efficient but we're also taking advantage of the fact that duct DB can run anywhere so whenever you connect to mother duck there's going to be a duct DB running on our server in the cloud but also on your laptop so one SQL query can run partially in the cloud partially on your laptop and everything on your laptop is totally free you've already bought your laptop and it has no network latency so follow-up analysis like slice and dice pivot sort is all instant today I'm wearing my duct Labs hat and I'm excited to talk to you about the open source project duct B so why did Hanes mu heisen and Mark roselt create. TB what was the inspiration well they thought something was wrong with the data world it was a splinter in their mind driving them mad do you know what I'm talking about well anytime something got a little bit too big for Excel or a little bit too big for pandas people were using incredibly large and difficult distributed systems to solve the problem Hadoop spark for small or medium-sized data problems it's like cracking a nut with a sledgehammer you're going to end up with powder at the end of the day not the result you want it's not ergonomic Hanz has another saying in German it's to shoot at sparrows with cannons and I love that visual so so how so duct B is designed to help address this another part of the duct B origin story was around Hanes and Mark reaching out to the data science community and they said hey we're database folks you're data science folks what kind of databases do you guys use and they got a lot of crickets the data science folks said well we don't use a lot of databases we've invented these things called Data frames and they're really awesome for all these reasons we really love them and H Mark thought about it and said okay let's collaborate so in a traditional data science workflow where does the database fit you've got a hypothesis you want to pull your data well maybe the database is there maybe it's a flat file on a Shar drive maybe it's on an object store maybe there is no database there then you're going to explore the data well are you going to load it into the database just to explore it well maybe you'll just explore it in a data frame and then when you model and visualize the data that's almost certainly going to be in python or r so where is the database duct DB is the database that can fit everywhere in this workflow so you can cycle through it as fast as you can so what is dctb dctb is an analytical relational SQL database it runs in process and not in a separate server process or a separate physical server it's the SQL light for analytics that allows it to have a superpower of fast data transfer it is MI it licensed so as Han likes to say it's free as in free beer so go and build a company on it we have clients in over 15 languages and our most popular client is in Python and we have a million and a half downloads every month it's written in C++ and was started back in 2018 but what do I want you to remember about duck be first thing it's fast if it weren't fast we wouldn't be here your time is important your crunching data it's got to go fast it's table Stakes secondly it's got to be easy if it takes weeks to set up and configure properly and there's an ongoing maintenance burden that's expensive too that takes up your time duct DB is one pip install away with zero dependencies or you can even download a command line tool it's just 20 megabytes but dctb is an inprocess database not an inmemory database so it can handle much larger than just in memory just like your python process can read and write to disk so can dctb what this means is the problems you can tackle are no longer limited by your 16 gigs of RAM it's limited by your 1 terab SSD so it's two orders of magnitude larger problems that you can solve and here's where I want you guys to get creative dctb is not just designed for your laptop it can fit anywhere it fits in many many languages but it also can even compile the web assembly to run in your browser so let's get creative and think about maybe you can fit in data science or you know data science workflows for sure but also in data engineering pipelines maybe as a part of a data platform dub really fits anywhere so to dig into it a bit more here are two ways to classify databases two axes to split them on on the x-axis you have transactional and analytical transactional is all about high concurrency small operations adding and removing from a shopping cart analytical is about analyzing those Trends which products are performing well where do I want to spend my marketing dollars another way to divide them are by in process and client server and client server is what you think of when you think of a database it's running in a separate process typically it's in a separate physical server somewhere and you're going to send in a message typically SQL and then it's going to Crunch come up with an answer and send it back to you over that socket in process is famously popularized by sqlite and by some metrics it is the most popular software in existence there are over a trillion active installations of sqlite in the world they run on every phone in every browser every operating system that impr process has significant advantages it's much easier and you have fast data transfer back and forth with your host application however if you look at other metrics like how many islands does your CEO own there are other databases that lead in that metric but sqlite is very popular you notice there's an empty space we really feel like data scientists and in many cases folks building data pipelines live in that empty space you're already working in another application you're working in Python you're working in Java but you need to do a little bit of SQL you need to process a little bit of data duct DB fits perfectly here it runs embedded inside of your application but it's built to handle those large analytical problems that you have dctb is also designed to combine the best of both databases and data frames really bring cuttingedge database research and also data frame ease of use so we'll compare data frames client server database sqlite with Deb so if you look at fast analytical queries there are many different tools you can use for fast analytical queries sqlite struggles a bit here the fundamental architecture is designed for fast transactions it's very good at fast transactions but those design decisions mean it's not as fast for analytics ease of use is somewhat in the eye of the beholder there are data frame fans there are SQL fans however we can all agree that managing infrastructure is a lot harder than not managing infrastructure that's why client server databases are weaker fast data transfer is a secret weapon of being in process duck DB can actually read a pandas data frame faster than pandas can because it just it sits in the same memory space all you need is a pointer to where your data lives sqlite is also in process however you have to convert it into the sqlite format before you can do any processing so it's not quite as fast on the data science integration side how many times has your ma machine learning library accepted a postgress database as an input parameter never how about a panis data frame maybe a numpy array all of them do right so it's really about fitting in with the ecosystem how can we read and write and interoperate as best as possible to make your workflow seamless the relational API the data frame style API has tremendous value I'm a big SQL fan I learned it before I learned pandas but I see many opportunities where pandas is the easiest way to accomplish something that API has great value but so does SQL what if one tool could do both not only could you choose when to use the best one in your in one workflow you can easily go back and forth but also maybe some members of your team are more comfortable in one of the other so it really supports more diverse teams query optimization is really the home turf of database research traditionally data frames really struggled here there's been some process lately polar is working on this however this is really something that the database folks have a couple decade had start on have you ever tried to persist a data frame well you could persist it as a CSV file you could pickle it if you never want to update your python or your pandas maybe you'll save it as parquet what about a th parquet files or a nested hierarchy of a th parquet files it's very much a Choose Your Own Adventure with a database that comes built in and it's optimized to speed up your queries seamlessly larger than Ram execution is also very important data frames also were historically weak here polers is making some progress but how many folks have crashed their python process with pandas running out of memory I sure have well you're in the right place so what's the catch Alex why isn't every database in process well duct DB is fundamentally somewhat of a single player experience you can't have concurrent writers because it lives in inside of that same process and to keep things in memory keep things fast you're isolated to that process you can do multiple processes for reading but as soon as you start writing we've got to lock into that one process but I do have an asterisk here for duck DB check out mother duck we're investing a lot in this space we want to make duct Tob a multiplayer experience part of what we love about duct B is that it is not just using the old SQL sql's been around for 50 years we are both smoothing out the rough edges of Sequel and even fixing some things that were wrong from the very beginning and we're even pushing it and supercharging it into new directions this is a long list we will not cover them all but maybe you'll find one in there that looks exciting we can talk after the talk let's look at a couple this is a case where we are fixing things the way they should have always been when you start a SQL query the first thing you need to decide is where's your data what's my table so why is the from Clause second in SQL well with dctb you can choose either way you can put your from first and then select your columns and we're going to assume that you want select star if you don't say anything so you can just say from table the way it should have always been another concept that really is the way it should have always been is Group by all how often do you really want to group by something different than what you're selecting if you have too many group buys you're going to end up with duplicates and how many times have you gotten the a of H that column it's not in your group bu and it's not wrapped in an aggregate Clause right and does it sound like that when you hear it as well that's what it sounds like when I read that error message group bile is going to automatically look at what is not being aggregated and what is a separate column and automatically take care of that that's so much more maintainable Right add and remove columns in one place instead of two less risk of duplicates how it should have always been this is where we're pushing the boundaries of sequel this is typically you know an imperative programming language or or data frame space and that's pivoting and unpivoting so it's been in the SQL standard for a long time but you always had to specify which columns are you pivoting out well that's not Dynamic at all that's not useful to me well here in dub all I have to do is Pivot my purchases on my gear and I don't have to remember the the star dates in Star Trek because that's a large number it'll automatically pivot out the right columns likewise unpivot can happen dynamically as well this is taking advantage of dynamic column selection using the colums expression so here I want to unpivot all my columns excluding item how many times have you wanted to do select star except that one thing right now you can do that with dctb we also have a ppar compatible API so we have just launched this it's in an experimental status the nice thing about this is it's actually written in Python so the entire API is in Python so if you're working in Python you like py spark you like duct DB we'd love to collaborate with you all you have to change is your import statement your existing spark P spark code is going to run on duct Deb for significant speed and efficiency advantages so we've talked a bit about what makes it easy well let's let's prove it what what's hello world look like in our python client for dctb this is a screenshot of Google collab dctb is actually pre-installed in every Google collab today but if you need it you could do a pip install zero dependencies and then in one line of of python you can say duct tb. SQL a string for your SQL statement and then fetchall and you've already got the answer to life the universe and everything now what if I want to work with pandas if I create a data frame now I can do that same duct db. SQL and say select star from my data frame it's going to automatically go look in your python local variables it's going to look for a variable named myor DF and it's going to see is this a type I know how to read is this pandas is it poers is it Apache arrow and if it is it's going to automatically scan it right where it lives doesn't have to insert it in deduc to be first so you can hit any data frame with SQL right where it already is and then if you want to get back into a data frame format maybe your next step in your workflow is best in a data frame just put DF at the end you're back in pandas there's arrow and poers as well so in just a two lines of duct TB code we're able to write any SQL we'd like as well as both read from and write to data frames so we've made it this far and we still haven't talked about why it's called duct B well ducks are very versatile they're the Envy of the animal kingdom they can fly they can walk they can swim and they sit on top of the data Lake but that's not why it's called ducky B it's called ducky B after hanes's pet duck Wilbur Hanes lives on a house booat in the canals of Amsterdam turns out a duckling is a perfect pet so Wilbur is now in the duck pond in the sky but we remember him fond and we call ourselves ducky be in his honor but that's important right we could have called oursel super mega awesome database so we think we're super mega awesome but we're duct be we're friendly we're approachable we want to integrate everywhere so I've said we're fast but let's prove it how fast is Dub so benchmarks are hard I'll say that up front Hana and Mark have actually written an entire paper on why benchmarking databases in particular is very difficult so we'll admit benchmarks are hard one nice thing is this Benchmark was not created by dctb it was developed previously by h2o.ai to Benchmark a wide variety of data processing libraries in this we're analyzing 50 gigs of data so it's a billion rows and we're doing five queries on that billion rows and duct Tob can do it in 24 seconds and it's the fastest across the board it's an order of magnitude faster than spark and 30 times faster than pandas this is on a pretty beefy workstation here on AWS so it's kind of workstation level performance now things really get interesting when you start to do more complex queries here duct DB is seven times faster than the nearest competitor and many libraries can't even finish these queries if we look at joins of a billion rows in one of these five queries it's actually joining a billion rows to a billion rows most libraries cannot handle this even on this very beefy machine and up until two weeks ago or so duct be was first in this Benchmark so fun with benchmarks right so credit to the memory data sets folks in Julia but it's a little bit in the name right if you think you're going to need larger than memory duct DB can handle it in memory might not so let's go a little bit smaller make it a little bit of an easier problem so those libraries can finish and let's see how duct Tob Compares in a 5 gab example for joints and here it leads the pack again again over an order of magnitude faster than some of its competitors so what's under the hood what makes dub so fast the first thing to look at is the design design the storage is it row based or column based so traditionally databases stored your data on disk in order of your rows would store one whole row next to each other in um on disk and that's really good if you want to pull out a row and update it and send it back but it's very slow if you're trying to Crunch a lot of data duct B is column based so that way you can only pull certain columns that you want to work with also means you can really compress your data quite a lot dates tend to be very similar uh back to back to back and has a lot of compression methods implemented it's competitive with paret in terms of total size but you can update each individual row whereas with paret you have to rewrite the entire file so dctb has some serious advantages there let's now look at the execution traditional systems were toule at a time and this is pull out a row process it send it back uh this is great if you're in a memory constrained environment say you're SQL light and you're running on an aircraft because they do I don't want my airplane to run out of memory now data frames traditionally have been built column at a time and this is much faster it aligns much better with modern CPU architectures however this can lead to a lot of those out of memory issues we talked about duct DB uses a vectorized approach this is typical in most modern olap databases and it really is a Goldilocks it's going to break your columns into chunks and process them one chunk at a time so it's much more memory efficient but it's also faster for your CPU it takes better advantage of your cpu's memory hierarchy your your L1 your L2 your L3 caches so it's actually even faster than just the memory savings alone so let's take a deeper look here duct Tob is going to automatically parallelize across all the cores of your machine so we'll break your columns up by a row group somewhere around 100,000 rows and we'll assign that to multiple threads so we're all within the same process so we can share the memory but we're using multiple threads to fully use your CPU and then we're going to break things into individual vectors these are just 248 rows and and that way they fit perfectly in your cpu's L1 cache that is memory that as close as possible to your CPU and it's 100 times faster than Ram so as often as you can keep data in L1 you get a significant performance Advantage so we do something called pipelining where we' try and do operations on the same piece of data back to back so it stays in that really fast memory right next to your CPU so these vectors are sized to fit perfectly in L1 and we take advantage of your processor's s D vectorization simultaneous instruction multiple data So within one clock cycle we can do the same operation on multiple pieces of data so that Vector size needs to be small enough to fit in L1 but large enough to fully utilize your vector registers we use autov vectorization induct Tob that's part of what allows us to work on so many platforms it also dramatically simplifies the code base and for example porting from Mac OS Intel to Mac OS arm took 15 minutes and we also saw that it'll automatically parallelize across these row groups so there's no specifying a partition key worrying if your partition key is too big or too small it's going to use morsal driven parallelisms it's going to break it into very tiny pieces and take full advantage of your CPU course we also want to make sure that we can handle larger than memory execution and now all of our operators support this joins aggregation sorts and window functions but it's not just pulling from disk it's also your intermediate calculations so let's say you do a join and then your data explodes in cardinality and then you reaggregate it back up duct DB can still handle those very large intermediates we don't want it to be where you hit a performance Cliff though where suddenly you run low on memory and all of your jobs start timing out and failing we want it to be graceful so in this example we're taking the memory down by 12x from 24 gigs to 2 and it's only slowing down less than 2X and it's doing it gracefully there's no wall where suddenly oh we're about out of memory let's start over with a disc based algorithm it's going to take 10 times longer here we're going to gracefully buffer out to dis to lower that memory pressure and still finish uh not too long so we take our inspiration from Galaxy Quest never give up never surrender let's take a look at the ecosystem dctb is written in C++ but we precompile it ahead of time for a wide variety of operating systems and architectures it can also Run Anywhere in almost any language that you need you can just download a command line tool it's a 20 megabyte uh file and then anything on your terminal uh becomes supercharged with powerful duct DB SQL you can pip install no dependencies we've also got a very popular jtbc driver you can even use duct DB in the web browser thanks to web assembly and bring the full power of a SQL engine right to your customer's browser we also support obbc adbc So AO database connectivity and 10 more languages that didn't fit on the slide so here's an example application using dctb wasum in web assembly you can just go to shell. dub. org and it's a full SQL engine right in your browser but you can also query data from all over the Internet so I can pull data from every file on GitHub Amazon S3 Google Cloud Storage Microsoft Azure and join it all up right in my browser I can even upload local files so go check it out speaking of reading files from everywhere this is key for data science workflows for data engineering workflows any kind of pipeline dctb can natively read CSV and Json automatically parse it into multiple columns we also have a parket reader and writer so if you need to add parket reading and writing to your your application this is an amazing way to do it we have recently added read support for Iceberg as well we also speak read and write Apache Arrow pandas polers and numpy and we do also integrate well with d player but we're also a database so we speak and actually can read and write to mySQL databases postris databases and sqlite databases and we can read files from all over the internet as well as S3 Google cloud storage and Azure and we can read nested hierarchies of files on those places so you could have one query that pulls from a thousand parquet files joins up with some postgress data combines it with a local CSV and gets your final answer very powerful Swiss army knife tool python is most popular client so we have a lot of deep Integrations with that Community we've talked a lot about the formats but we also integrate very well with data engineering libraries there's a DBT adapter so that means you can run all your DBT jobs natively directly in your cicd workflow on duct TB and do testing on your data workflows we also work very well with multiple orchestrators if you're not a SQL of exion a like me you can also use a data frame API right on top of duct DB so the Ibis project from Voltron data duct DB is their default engine we also have our new ppar API and sauba is a port of deer syntax over to python dctb is also the perfect engine for data visualization tools every time you change how a chart looks that's a data manipulation query and duct B is perfect for that so hex uses duct B under the hood mode uses duct B under the hood so does vega Fusion as well as several others so if you're building a visualization tool in 2023 and you're not powered by Deb you've got an upal battle we also integrate well with SQL Alchemy we have a SQL Alchemy dialect so you can do your schema migrations and it also integrates with all of the tooling that supports the SQL Alchemy orm including jupi SQL turning any cell in a jupyter notebook into a SQL cell so in summary what were those four things I talked about way back half an hour ago first of all duct to be is fast it's multicore it's vectorized to take full advantage of your modern CPU instructions and it's in process so your data transfer is instant we can read pandas faster than pandas can it's easy installation is a simple as downloading a CLI tool or a pip install with no dependencies there's no server to manage so you dramatically simplify your workflows there as well and we have the friendliest SQL in the world plus you can use data frame apis on top of it if you prefer duct Tob can also not just handle in memory you're not limited to that 16 gigs of RAM you can scale up to that one terab SSD that you have and these of course are the symbols for Galaxy and Quest never give up never surrender and here's where we get to be creative and think about where would you like to sprinkle a little powerful sequel in your workflow it fits anywhere 15 languages even in your browser and we've got a lot of connections to the ecosystem we can read and write data frames of all different varieties and we're MIT licensed so feel free to build anything on top and we'd love to collaborate thank you very much for your time and attention and welcome to the flock and if you'd like on your way out there are dub stickers lanyards and bottle openers thanks so much for [Music] joining