Analyze data easily with duckdb - and the implications on data architectures

Speaker 1 [00:07]

So I'm Matthias, I'm working for Quocentix, small consulting, not so small anymore, consulting company in Germany doing software projects and also data projects. And in this role I'm doing a lot of data architecture, data workshops, conceptual-wise sometimes I'm allowed to open the IDE, and I'm happily enjoying this, and last time, most of the time it was actually DuckDB. So the usual question up front, who has heard of DuckDB? That's a lot of people and I expected it. Who has actually worked with DuckDB? That's far less, but for you that already worked, you can sleep for 10 more minutes and then wake up. So what is DuckDB? Fast, in-process, analytical, open source, portable database system. Sounds great, isn't it? Before I explain all those, what is it and why is it cool? I thought about hey let's just do a demo that's easier so um I'm on my command line here and I'm starting duck DB just over the command I'm where did I get duck DB from easier just install it by a brew or whatever package or what a manager you might want to want want to have so it's just in CLI tool in this case and I started with an UI that comes since a few weeks actually. Then I get those UI as well but I could also use just this wrapper and execute SQL code here like select ASB. So nothing special just some SQL. So what can we do here. I also make it a little bit bigger. So people that came up front are an advantage now. So what can we do? We can read JSON, for example, via SQL. This JSON is stored locally. We get the results here. We get also some profiling here and we can do, of course, all the SQL stuff on this. You see some of the SQL sugar that DuckDB is adding as well. For instance, we don't specify by what we are grouping by, by which columns, but just say every column that doesn't have an aggregate function on it, just use those for aggregations. Okay, this isn't too impressive, I guess. But we can also query Parquet files here. And this Parquet file is stored on GitHub, so via HTTP. We can just access it and can do all the stuff on this, all the group bars and so on, all the SQL stuff we want to do. And we can also re-JSON, unnest this, have some, this is about space launches. We just have an, it's basically in REST API and we just query it via SQL. Getting a little bit more interesting. For me, this is actually one of the most used things because Parquet files are not that easily readable and if I get some Parquet file and just want to take a look into it, just start up DuckDB, read the Parquet file via SQL and look what is inside. Most of the time the Parquet file is stored on blob storage, for instance S3, might also be Azure Data Lake Gen2 or GCS or whatever, and we can access this as well. So we just create a secret first, because this is authorized. You can happily copy those access keys, so then you get access to exactly the data that we are showing here. So we scope this secret in this case, say, hey, whenever a request is coming to S3 test data materials, just use this secret. And we read this JSON, which is stored in S3 in this case. is roughly 2.5 gigabytes and right here and select it. And so we can also have JSON gzip files and just they expect and DuckDB extracts those data first gets all the data that is stored in these in these files and we can happily query this data. We also have some SQL goodies some of them I told you already so we can create a table as select nothing special so if we select a JSON file here and then create a table T we see this table T here stored in our memory database coming to this later. We have some kind of cooler stuff like select star exclude instead of select column column column column everyone but celery also nice syntax sugar I guess everybody can relate with this we have these group by all here and what you see here is from select where so it's not like this really specific sequel stuff where we say have which is kind of you always say select and then from and it's more natural to say actually from this table I want to select and you can do happily do this here and completely new just released this night therefore it's a new notebook it's instant sequel I just create the table employees here reading my JSON file again and what it can do with in the web UI for example is from employees select and I only want to column ID and it just by just by typing it's actually executing the query because it's so fast underneath you can just rapidly prototype your your tables and your SQL so really cool stuff now I'm I said hey we storing this in this memory database and now we coming to the points of it's It's in process, embedded. So we have this memory database. DuckDB, don't put too much on this DB part of DuckDB. It's not like I replace some Postgres database with it. It's more like a SQLite database coming to this data. So in this case, if you don't say anything, the database you're having is just in memory. It's kept in memory. If I create a table like this, the table employees, it's created in memory. In this case, it's even just a pointer to the JSON file. Sometimes it's cached within the memory. Sometimes it's read when the data is needed. And here you can actually see, hey, we're using the memory database now. But we can also create database files. for instance by using attach persistent DB the persistent DB file is now stored here and just in this in the same directory I started the CLI in create a new table in this also we JSON again I can attach also to SQL database files so to database files that are stored in s3 for example here it's stored in s3 and it's yeah and I can show what is in there so attached to this one duck to be ducky catalog it's called there's some views in it and those are the views that are defined in here and actually the views are defined as a select from an s3 bucket not another view as an select from a three bucket another view is to select from a postgres database so it can also be did postgres and another one is an iceberg scan so reading a nice back table what you can actually do now which is kind of cool and like hey you want this data it's distributed everywhere no problems here's a database and this database file which I downloaded is like a few kilobytes only because it's just metadata in it are the views and the views say the data is there there and there and you can just query the data so if I query the customers table now I actually query everything that is stored in F3 so kind of a lightweight data catalog style if you want to share data pretty cool stuff so now I have these file saying move over to and there's missing PyCharm and I just quickly start the presentation mode this is the first thing so of course DuckDB is like it's it's written in C it's basically fast but hey we want we want to use Python for it don't we so you can just pip install DuckDB you get all the embeddable stuff as well so all the DuckDB engine, then you can just use DuckDB SQL, write SQL code, the same SQL code as before, and print those stuff and show it. Or you can use the DuckDB Python API, which is not like completely Wall String, but only 50% Wall Strings, where we say like, okay, we read the JSON, we select all columns, exclude payload, and we aggregate type and count as count by group, by type and we order it. Just let me quickly run it, so this is the result of should be the first time and this is the second time. So this took two seconds and the fast one where I don't print the time should be way faster because the first start takes a little bit more time. So easy to use in Python but it's getting getting even better we also have a good price and integration so we having some data here our customers and the salary stick we're creating a polos data frame be creating upon this data frame in here and we just query them so we just write duck DB sequel and we query from the polos data frame and join with the Polar's data frame and it magically replaces this one here with knowing this is the Pandas data frame and this one is the Polar's data frame and underneath how is it connected? What does it use in between? Apache Error is the common discriminator in data by now I guess if it comes to exchanging data. So we can just select here and then we can return the result that we have here as a data frame again or as an arrow table again or as a Polars data frame so it's really easy convertible between all the memory types and all the data types you want to have and it's all error in between therefore it's really really fast and not no conversion cost so running this as well just to prove that it runs through yeah we having this is the first one where we do the computation, then a Pandas data frame, an arrow table and a Polars data frame underneath here. Yeah, so easily to use and therefore really easy to use as the drop and in your existing workflows and notebooks and whatever you have you can might just use DuckDB in between. And last but not least There's something called browser here and browser says they go to shelter ducktb.org. That's what I show you Ducktb is not only in written in C and available as Python library and whatever a few other libraries, but it's also a WebAssembly module so it's also confined to WebAssembly and this is actually running within my browser. There's no client process somewhere else It's just running in my browser as a C package or as a wasn't with C code and I can execute all the secret queries it's not all that like all extensions are supported but it works pretty well and works pretty good for some data analytics where is it as so a shell is nice where is it used for example if you go to a hugging face and go into data sets and how do you hugging face you have a sequel UI next to it where you can explore this data set this is actually duck DB wasn't inside this So it's actually using the data. It's actually in the browser where you quickly analyze What is the data in which I would get with this data set? How does it look like and can explore it? So this was a real quick demo, but I think it gave a good explanation of hey What is it capable of and and what can we do with this? So analyze local, analyze remote, analyze from a blob storage. We have the SQL editions. We have the database files We can do data sharing like with the data with the catalog file We have integration in Python. We can run it in the browser the HuggingFace data set browser We can actually read from HuggingFace. So it does support the HuggingFace protocol natively if you read data from HuggingFace So a few words more on the conception level now I already said it's more like SQLite and if you have a look at this you can relate most likely So SQLite is an embedded transactional database, the most widespread database in the world because all of you are using it most likely right now in your pocket because on all of your phones it's running SQLite. DuckDB is the same, but it's more focused on analytical use cases. And it's not too much, as I said, yeah, there's a database part with the database files, but for me, it's mostly like a really, really fast embedded query engine. That's the most important part for me. Yeah, DuckDB files are good, but it's more like the query engine. It's portable, so it doesn't need any external dependencies. DuckDB itself is really like just pure ZIP code without any other dependencies, not the extensions. We are coming later to this. We have a lot of clients, PIP, Java, R, JavaScript, TypeScript. You can just install the CLI as I did with Boo. You have it with Rust, too. In the end, it's C underneath. You can run it everywhere. You can run it like on the PC, on the browser. You can run it in a Docker container. This is an example of Okta. They built a data processing pipeline with it by just putting this into a lambda function and starting lambda functions and stopping lambda function as they need it. So just using DuckDB in a lambda function. Pretty simple to use. Recommended video if you want to take a look. It's quite flexible because we can read a lot of data formats. Especially read because later Delta, Iceberg, write support not there yet. But we can read a lot of data files. We can, from different locations, HTTP, S3, Azure, GCS, and so on. We can also read existing databases and connect those, and we can join data from different databases and data sources. We can go to Pandas, and we can export it as NumPy. If you're using R, it's also compatible with Deployer, so it's really flexible and fits in a lot of ecosystems. And it comes with a lot of extensions. For instance, all the stuff that you have seen here, all the data formats on the left, the storage or the remote protocols on top and so on, it's all extensions. It's all not the core DuckDB. The core DuckDB, as I said, is without any further dependencies. If you load extensions with this, you get those further dependencies. And all of the features are actually extensions. You don't recognize it most of the time because they are autoloaded. Most of the, so for instance, HTTPS, JSON, Parquet, Delta, they are autoloaded. As soon as you do a read Parquet or read Delta, the extension is autoloaded. But there are some extensions that need to be loaded explicitly, like the spatial, one way you can do spatial computations for this. So current state, DuckDB, we are in version 1.2. Version 1.3 will be released mid of May most likely the current current idea to do so 1.3 will focus a lot of on all the lakehouse formats. So iceberg Delta Performance improvement there and all the stuff Yeah, and they're all our claim was at the beginning Hey, let's build a correct system first and then make it fast and you're seeing like this. Hey, we support Delta fights We support iceberg. That's the first part. It's correct now we are proving the performance and you see it like every release with DuckDB is like you see someone on LinkedIn claiming some huge performance gains and here's why and here's why they really getting faster and faster all the time with the support for instance for predicate push-down and all this stuff so this is all nice tech and we love it but what to do it with it now that's the good question the one thing is these local prototyping exploration I get some data over thrown over the fence I get some somebody hands me a parquet file or whatever and this is on SV and I only have the access keys and this is really easy to get get into the data and take a look into us and all this stuff this is the first one no config no DBA just single tool everybody can run. We replaced our local unit tests or our PySpark unit tests with DuckDB during execution there's a library called SQLFrame which mimics the PySpark API which can have different runners underneath. DuckDB is the default runner and we just run our unit test against DuckDB. In this case way faster than spinning up a PySpark session which takes on an enterprise laptop with Windows and all the stuff about a minute minimum. We can build analytical applications and api is way better so i think a lot of you building products right now where it's not just create read update but it way it's more like hey we want to we we need to analyze the data we want to do some self-serve analytics for the users that would is actually something we use ducttp in projects already so i'm more like a data engineer guy my company is more like software engineers but they actually take using DuckDB as well so hey this is really cool this lets us create fast responsibly responsive UIs and just back to be hey it works also with TypeScript nice so they got that important they don't have to learn Python which for some reason they don't want to and reducing costs so really replacing our PySpark jobs with DuckDB Nico sits in front here he has some examples of what what did he tell me from two hours to a few minutes um i have some as well so really like because it's if the data isn't that big you can easily process this with stackdb and spare a lot of money compared to using PySpark um you're doing this by having short-lived instances that really only run when the uh when the compute runs and yeah and don't do it distributed because distributed oftentimes is more than you need. And you can actually use it for data processing pipelines. I mean you can write for instance with dbt at sql you can write all the pipelines as well. There's an asterisk on top because as I said write support with all the lakehouse format is currently not supported so if you want to write there it's not that good but yeah so this is kind of I think we will get there Most likely I won't start with this one at the moment. So limitations, we have some limited multi-user support. You have one user that can read and write or you have multiple users that can read but you don't have both on a database file, on a DuckDB database file. It's not for write-heavy workloads, it's really query engine made. The write support for Lagos formats I already mentioned, it's single-noded. For me it's a good thing because it makes things simple But you can scale it up horizontally. You can just scale up by bigger machines bigger machines bigger machines But I mean this has 32 gigs of RAM. So It's there you still need a runtime. It's just a library You don't you still need something where it's executed a docker container running in Q and it is a lambda function, whatever So it's really just a library and there's comes a lot of complexity that because that to be so easy and simple But yeah, because they don't care about where it's one for instance if you're running Spark, it comes with all the cluster, with all the execution environment as well. Limited on time. So what are the implications on data architecture? The one thing is, before that we always had Pandas and PySpark as the option. So we either could have all the data which fits into memory, then we could use Pandas, or we have had to use PySpark. There was nothing in between. And with DuckDB and also Polar, of course, we have this bigger than memory processing as option we can process data that is bigger than memory because it only reads the portion it reads it caches data it offloads data so this is the one thing that is really important if it comes to data platform we're also using it for an alternative query engine so we have an example where we're using databricks storing data in databricks with the unity catalog and but the data is small to be honest database databricks is a great great platform for all the governance stuff and managing the data but actually we want to use DuckDB to query the data because hey it's just a gigabyte or two or five so and with the new token vending mechanism that is available in database we can actually do this and use DuckDB to read the data and in turn of a queried engine besides all the big proprietary distributed stuff and the important part these simple data driven applications. We convert customer-facing analytics application where in an extreme case basically one customer gets this own customer database and just uses this data. If it's small enough you can just have those DuckDB file or APK file or iSpec file and this is created by the customer as soon as he uses the web UI and if you don't use the web UI yet then the data is stored in a 3 where it basically costs nothing. And you have the query engine embedded in your web application and just query the data when it's needed and don't have it running all the time, keeping costs low. And this is DuckDB Visions. Maybe we're going to behind those two-tier data architecture. What do they mean by two-tier data architecture? Usually we have like an application where the data is stored and then we move all the data via ETL processes and whatever stuff over to some data lake or data warehouse where we do all the other processing and DuckDB in my opinion is something that brings those two closer together we have the applications where the data is created managed and we have the analytical part and we can bring analytics actually to the application no need to first show it to a data warehouse when then build some uis on it and some look at dashboards that are then integrated via an iframe into a web application now we can actually build it into the web application itself and therefore maybe we're coming to and shift left in data flows not having the data lake and data warehouse at the end of the flow not having this at the after the thought when creating applications sometimes we think about data but it's more like hey maybe data is the central part and application is one point using the data and bi and machine learning and AI and analytics is just another use of this data so it's getting more data centric in this case and with that come to the takeaways great for exploring the data and it is easy to use you've seen it it's a huge momentum right now you could call it a hype actually in my opinion it will impact and simplify data architectures in the long run and we already use this especially for data driven application and this gap I think is going to close even more. Thank you. All right, thank you for the great overview. I also like the infographics, it's really nice, also nice showcase. Right, we do have some questions on the Slido. I'm just gonna start and rapid fire this. If I already use Databricks and read Delta tables with Spark SQL, where does the application of DuckDB come in here? Basically, less overhead, less cost, and most likely faster if it comes to certain cases, definitely. But it depends on how much and how big your data is. If the data is so big that it can't be handled by DuckDB, no worries. But if If it's small enough, this makes things easier. But you don't get all the user management authorization stuff. And also, if you need this, you have to stick to the platform tools. Right. How well does DuckDB integrate with tools like dbt or Dexter? So with dbt, it's pretty simple. You still have dbt. dbt needs a SQL engine. DuckDB is your SQL engine. Done. No, it doesn't matter if it's Postgres or Snowflake or whatever. With Dextre, basically you can either use dbt in between, depends on what you do, or you can just use Dextre to run secret queries somewhere, for instance in DuckDB. To be fair, Dextre directly to DuckDB, not seen that often, it's more like for the ETL case, which I said, maybe not the first part because the write support is not that good. All right, how do you decide between Polars and DuckDB? Between Polars and DuckDB. For me, first of all, do I want to write Python or SQL? Okay, taking this. The other one, for me, Polars is a data frame RP written to process data and ETL data and transform data. DuckDB, for me, it's really like this. Embedded, really easy to use, more like database and SQL stuff. Yeah, maybe you would not use Polars, for example, in a data-driven application. It feels more like it's a tool from writing data to A to B, not to analyze data on the fly. But in the end, I'm pretty sure those converge at some points, and it's just a matter of preference. All right, now rapid-fire. Is DuckDB a threat to cloud data warehouses like Snowflake? A threat? I'm not sure if it's a threat. Definitely not because there's because cloud data where I was like snowflaking also Databricks bring all this platform stuff and all these Ecosystem with you, but I'm pretty sure that they will incorporate this easier simpler processing modules Otherwise, or I have to enable people to actually use this like Databricks already does but it's not a threat Although there's a company called mother duck as with each every open source product as a VC backed company in the end which do this cloud data warehousing as well, based on DuckDB. Might be interesting how this turns out. Might be a nice competitor towards the proven and bigger companies that are there. Right, last question. Does DuckDB have its own SQL dialect? It actually is, so it's full support of the Postgres dialect plus X. That's basically it. So if you use Postgres and use the Postgres dialect, dialect you can just continue with it and but it has some additions like the group by all and all this stuff all right thank you with that we have to close we're on time give a round of applause