Dagster Dagster Demos, 22 Jun 2023

Previous Meeting

Next Meeting

⏯

youtube image

►

From YouTube: Revisiting the Poor Man's Data Lake with Mother Duck 🦆

Description

Last year we wrote an article on the Dagster blog called "Building a Poor Man’s Data Lake with DuckDB". In it, we showed that you could use two excellent open-source technologies - DuckDB and Dagster - and build a powerful, cheap, and useful data platform.

Since then, MotherDuck has emerged from stealth to build a high-powered cloud version of DuckDB that competes with other cloud data warehouses like Snowflake.

In this video, Pete Hunt (CEO) shows you how to migrate the Poor Man’s Data Lake away from S3 and Parquet files into a single system: MotherDuck. As you’ll see in this blog post, it’s very straightforward due to the elegant design of both DuckDB, MotherDuck, and Dagster, and we can realize all of the benefits of MotherDuck without even touching our business logic.

A

Hey everybody, I'm Pete, I work on dagster and today, I am going to talk about revisiting a blog post that we did um a while ago called the poor man's data Lake, uh the URL for this blog post when we finally posted it is going to be here and the URL of the code is is right here, it's all hosted on GitHub.

A

So um if you recall, you know, in October of last year, uh Sandy and I posted a blog post, where we built a data Lake from scratch with duck TV and parquet files and S3, and now that mother duck is kind of emerging from stealth. uh This is a startup built on top of duck TV. We wanted to revisit this post and talk about how, in kind of you know, take mother duck integrate it with this approach and get you know something very similar to a modern cloud data warehouse.

A

um So I'm just going to take you, through this blog post step by step, uh this one's a little less complicated than the previous one, um but it's still pretty interesting. So uh just to catch you up uh on this old blog post. You know the the idea here is that um you know using something like Duck TV. If you don't have massive data sets that require like lots of parallel computation, you can actually dramatically simplify your architecture by adopting duck TV, uh parquet and S3, and just kind of creating a very simple data.

A

Lake like that and orchestrating it with dagster. You get a lot of the benefits of a modern. uh You know data platform with just those tools. When you combine it with mother duck, you can um get even more benefits, so you can basically take these uh parquet files and um and replace them with tables and mother duck, and you can you know visualize them share them, make it makes collaboration a lot easier and I'll show you what I mean by that.

A

So, first of all, like what is mother duck, um I think it's easiest to explain what mother duck is by comparing it with with vanilla duck DB. So if you were to install duck DB on your computer from package manager or go to ducktb.org to install it, you would get this command line tool where we can like create tables.

A

You know and and read from them and and we can see the data that comes out now- the problem with using duckdb in this way, where we just run duck DB, is that by default it creates an in-memory ephemeral database. So what that means is like, if you were to restart duckdb and try to select from the same table you get this error message that says table with name. Food does not exist, um so you need to process your data somewhere, like a database.

A

That can't store data is not that useful and so out of the box stuck DB lets. You persist data to a local file, so I'm running the CLI here and I pass in this file name that points to like a local file on disk and again I run the same commands. I did last time, I create this table, Foo I select from that table and then I see the results and then, if I quit and I reopen it again and point it to the same. My database.dv file, the data persists.

A

Now this is really useful in like a local development, environment um or otherwise a single player environment. But when you start to kind of collaborate across different teams, you really want to be able to share these tables in a pretty. You know, transparent and easy to use way, and so, in the previous blog post, we used parquet file sitting on S3 um and in this one we're going to use mother duck. Mother duck brings a lot of the advantages um that of a modern cloud data warehouse to this problem.

A

So with parquet file, synonym and S3, you don't have like a beautiful UI to go visualize all your tables.

A

um You know, access control is limited to what you can do in S3 and just overall, it's kind of a bit more of a homegrown DIY experience than using something like mother duck uh using something like mother. That gives you closer to like the experience of using something like snowflake, and so what's what I didn't realize before working on this blog post? Is that actually the mother duck client is already bundled with duct TV, so you don't have to install anything.

A

um You simply give it this funny looking file name, and this is actually a a URL. The MD is like the protocol, so instead of HTTP, for example, it's MD standing from other duck. This is the name of your database and then you pass in a token, so you have to go to motherduck.com, get your access token and paste it in there or otherwise provide it as an environment variable, and this will create a new database on Startup.

A

So if the hello world database doesn't exist, they'll just create it for you, and then you run the exact same. You know, docdb commands that you would normally run, and it just operates on this remote.

A

um This remote database since sitting inside of mother duck and I can actually like as long as you pass the same database name and a token that has access to it. To like um you know, you can run that from a different machine and select the same data.

A

um This uh should not seem like super mind-blowing uh to you other than the fact that it like it's very simple. It just works like if you're already using duckdb, it was like. It was very, very easy to transition this project from from local, like DB to mother duck, um so uh let's actually uh transition our project from uh uh local.tv to mother duck.

A

So before we do. This I think it's worth like taking a read at the old blog post. Here's the URL right here. This is where we kind of assemble the um the the performance data Lake from scratch, using like parquet file sitting in S3. We're gonna, basically take those parquet files sitting on S3 and like throw out the S3 client throw at the parquet file and instead replace it with tables sitting in mother duck.

A

So the first thing we need to do is change our DB connection. So in the old project we have this class representing duct TV and you passed in some options and it gave you the ability to query.tv. So you can see here we call the duckdb connect function. We pass it. This magic string indicating that it should use an ephemeral, in-memory database. We load the httpfs extension, so it can read and write parquet files sitting in S3, and then we we do a little bit of other stuff here that um you know converts this.

A

This SQL object to string and runs it as a query and then Returns the results as a pandas data frame.

A

So um all we need to do here to update this is basically give it um a second parameter called URL and then we'll connect to this URL, uh rather than the hard-coded string representing in a memory database, we'll default it to in memory, but um there should actually be a call in there. I'll fix that before we post the blog post. But um the point is now we can pass in a URL to mother duck.

A

So if you recall, when we set this DB up in the old project, we pointed it at S3, so we passed in a bunch of these configuration options about S3, and then we instantiated ductdb with these options. Now, with this new unupdated version, we don't need any of those S3 related options, um uh even though we can still include them if we'd like, but we pass in a URL and we'll pass in our mother duck URLs environment variable, so we don't have to hard code that access token in to our project.

A

Hopefully this makes sense. So just to recap, I know: I went through that really quickly, but just to recap, all we really did was we added this little URL parameter and we connected that URL. It's like I think this is technically a three line change, but it really feels like a one-liner, very simple, um and then we pass in our our mother deck URL to our new client.

A

um So we can actually go and run this project as is, and it will um it will work identical to how the local uh duck DB version works. It'll still use S3 files and parquet because we haven't gotten rid of those yet, but um it will be running them on mother deck so like that's not useful on its own, but it kind of just shows that mother duck is really compatible with with how local duck DB works.

A

So um the next step is to uh actually go and get rid of the the references to the S3 client and to get rid of those parquet files and replace them with tables and mother duck, and so dagster makes this super super easy.

A

We can actually do this without touching any of our business logic, which is really really important and really powerful, because Daxter has this abstraction called an I o manager, and the point of an I o manager is to take all of that code related to reading and writing data and dealing with storage and persistence and abstracting that way away into like this one thing called IO manager, so the rest of your code doesn't need to know about it.

A

So as we go through this example you're going to see that we never touch our business logic really all we do is we change this, and so we started out. We have this thing called a duck pond I o manager, which was responsible for reading and writing to a local um or sorry to those S3 files in parquet, and so what this um so. The first step is to to basically just copy and paste this into a new class called mother duck. I o manager and we're going to iterate on this.

A

I o manager and get rid of all the references to parquet and S3 and replace them with references to mother duck. So you can see what this class does right now it takes in the duckdb client it takes in some stuff about S3 like the bucket name, the prefix.

A

It knows how to construct S3 URLs to parquet files based on the name of the the software defined asset um and it uh it knows how to uh you know, do the magic incantation to write the parquet file to S3, and it knows how to do the magic incantation to read the parquet file from S3 again, if this is totally alien to you, go back and check out this blog post, the original blog post, duct TV data, Lake, there's actually a video version of it. You can go.

A

You know, watch that whole thing and then come back to this post, and this should make some more sense. So uh we need to basically make a couple of changes. We need to change, handle output to um basically write that data to a mother duck table instead of an S3 parquet file on S3, and then we need to make the corresponding change to load input to read from that file or sorry to read from that table.

A

So, um let's see here uh we're gonna, replace this function up here that computes S3 URLs and we're actually just going to replace that with something that creates a mother duck table name because we're again we're um you're going to save this to a mother duck table. So this is pretty straightforward right. We just have a prefix and then we have an underscore and then we we construct the uh the asset name. It's pretty.

A

You know it's it's going to create kind of, like you know, valid SQL identifiers that you know, look like table names uh next, uh we're going to um actually use that function and so uh we're gonna, rather than kind of use.

A

The the duct TV feature to write a parquet file, we're going to create or replace a table with a with this specific table name and then we're going to use whatever passed in SQL statement to populate that table um contrast that with kind of the version above it's it's quite similar, but you know the copy. This copy uh statement will basically copy it into S3 as parquet file.

A

This create a replace table will just create it as a normal mother duck table um and then, if you notice, like our load, input function becomes extremely simple right. It's just the select star from table name easy as that again, because this is just a normal. You know native mother duck table just like any other data warehouse um and then uh finally, we construct our mother duck. I o manager and we pass it into our dagster project.

A

um This is how we how we pass. I o managers into our project, we've replaced the duck pond. I o manager, which takes in you, know all those S3 parameters with just a mother duck. I o manager that takes a ducktv client that is pointed at motherduck, and that is literally all you have to do.

A

um I know that I talked through a lot of steps, but really it's it's quite straightforward. We made the code simpler, not more complex. We got rid of the references to S3. We got rid of the references to parquet and the SQL that we're generating now is like pretty vanilla, sequel right. It's Creator replace table, it's select star from table.

A

um You know it's pretty native to SQL, and then you get this like nice, nice looking uh mother duck UI, rather than just a list of files in S3, um you actually have like a you know, an interactive query explorer that can can visualize it, and you can see over here, we've populated our data warehouse in our hello world database uh with all the all the the tables in our project, um which is uh which is pretty neat um so anyway. uh That's that's the post.

A

You know uh nothing groundbreaking here, but the utility of this is is pretty pretty massive right like we. We don't have to set up as much infrastructure like we don't need to think about S3. We don't need to think about parquet. We can just use vanilla SQL and we get a lot of usability benefits from using mother duck as well. So anyway, thanks for for taking a look at this and um as always, if you have any questions, um just hop in the Daxter slack uh or go to daxter.io community and join the community.

A