WEBVTT 00:00:00.001 --> 00:00:01.360 Have you heard about DuckDB? 00:00:01.360 --> 00:00:05.260 Well, join me for an insightful conversation with Alex Monahan, 00:00:05.260 --> 00:00:09.500 who works on documentation tutorials and training at DuckDB Labs. 00:00:09.500 --> 00:00:14.920 We explore why DuckDB is gaining momentum among Python and data enthusiasts 00:00:14.920 --> 00:00:19.920 from its in-process database design to its blazing fast columnar architecture. 00:00:19.920 --> 00:00:24.880 We also dive into the indexing strategies, concurrency considerations, 00:00:24.880 --> 00:00:28.320 and fascinating way that Mother Duck, the cloud companion to DuckDB, 00:00:28.320 --> 00:00:30.720 handles large-scale data seamlessly. 00:00:30.720 --> 00:00:33.840 Don't miss this chance to learn how a single pip install 00:00:33.840 --> 00:00:36.140 could totally transform your Python data workflow. 00:00:36.140 --> 00:00:41.500 This is Talk Python To Me, episode 491, recorded December 10th, 2024. 00:00:41.500 --> 00:00:43.700 Are you ready for your host, please? 00:00:43.700 --> 00:00:47.360 You're listening to Michael Kennedy on Talk Python To Me. 00:00:47.360 --> 00:00:51.120 Live from Portland, Oregon, and this segment was made with Python. 00:00:51.120 --> 00:00:57.140 Welcome to Talk Python To Me, a weekly podcast on Python. 00:00:57.140 --> 00:00:59.360 This is your host, Michael Kennedy. 00:00:59.360 --> 00:01:02.220 Follow me on Mastodon, where I'm @mkennedy, 00:01:02.220 --> 00:01:07.700 and follow the podcast using @talkpython, both accounts over at fosstodon.org, 00:01:07.700 --> 00:01:12.600 and keep up with the show and listen to over nine years of episodes at talkpython.fm. 00:01:12.600 --> 00:01:17.100 If you want to be part of our live episodes, you can find the live streams over on YouTube. 00:01:17.100 --> 00:01:21.280 Subscribe to our YouTube channel over at talkpython.fm/youtube, 00:01:21.280 --> 00:01:23.420 and get notified about upcoming shows. 00:01:23.600 --> 00:01:25.480 This episode is brought to you by Sentry. 00:01:25.480 --> 00:01:27.260 Don't let those errors go unnoticed. 00:01:27.260 --> 00:01:29.100 Use Sentry like we do here at Talk Python. 00:01:29.100 --> 00:01:32.460 Sign up at talkpython.fm/sentry. 00:01:32.460 --> 00:01:36.860 And it's brought to you by the Data Citizens Dialogues podcast from Colibra. 00:01:36.860 --> 00:01:40.280 If you're ready for a deeper dive into the latest hot topics in data, 00:01:40.280 --> 00:01:43.820 listen to an episode at talkpython.fm/citizens. 00:01:43.820 --> 00:01:46.060 Alex, welcome to Talk Python To Me. 00:01:46.060 --> 00:01:47.540 Howdy, thanks so much for having me. 00:01:47.780 --> 00:01:49.040 Yeah, it's fabulous to have you here. 00:01:49.040 --> 00:01:51.540 I'm really excited to talk about databases. 00:01:51.540 --> 00:01:55.880 Databases are so important for making your app work well. 00:01:55.880 --> 00:02:00.260 Making them fast surprises me how many people have slow databases. 00:02:00.260 --> 00:02:01.200 It doesn't make sense. 00:02:01.200 --> 00:02:03.520 We're pretty big fans of databases around here, for sure. 00:02:03.520 --> 00:02:04.960 And fast ones as well, right? 00:02:04.960 --> 00:02:06.120 As best we can. 00:02:06.120 --> 00:02:07.040 That's right. 00:02:07.680 --> 00:02:13.940 Well, DuckDB is certainly on the rise in terms of me hearing people talk about like, 00:02:13.940 --> 00:02:14.880 oh, have you heard DuckDB? 00:02:14.880 --> 00:02:16.220 Have you seen DuckDB? 00:02:16.220 --> 00:02:17.520 Have you worked with it? 00:02:17.520 --> 00:02:24.560 And I think it continues an interesting tradition of lots of low stress near your application type 00:02:24.560 --> 00:02:25.000 of databases. 00:02:25.000 --> 00:02:27.680 So it's going to be fun to dive into that for sure. 00:02:27.680 --> 00:02:29.240 Before we do, tell us about yourself. 00:02:29.240 --> 00:02:29.640 Sure. 00:02:29.640 --> 00:02:30.840 Well, thanks so much for having me. 00:02:30.840 --> 00:02:31.660 Huge fan of the show. 00:02:31.660 --> 00:02:32.480 Really excited to be here. 00:02:32.480 --> 00:02:33.640 So I appreciate that. 00:02:34.140 --> 00:02:35.880 I work at MotherDuck. 00:02:35.880 --> 00:02:39.380 I'm a forward deployed software engineer, which is kind of a made up title. 00:02:39.380 --> 00:02:42.540 A couple of companies use it, but it's really a customer facing software engineer. 00:02:42.540 --> 00:02:47.020 So working closely with folks to see how MotherDuck fits in with all the rest of their stack. 00:02:47.020 --> 00:02:50.920 So MotherDuck is a cloud data warehouse with DuckDB at its core. 00:02:50.920 --> 00:02:53.080 So absolutely a huge part of what we do. 00:02:53.080 --> 00:02:57.440 I also work part time for DuckDB Labs, which is our partner organization. 00:02:57.440 --> 00:03:01.680 And I do blogging and some developer advocacy types of things for them. 00:03:02.200 --> 00:03:06.060 And so it's a separate company based in Amsterdam, the Netherlands. 00:03:06.060 --> 00:03:09.400 And they are database experts and researchers. 00:03:09.400 --> 00:03:11.380 And they're kind of the stewards of the open source project. 00:03:11.380 --> 00:03:11.800 Yeah. 00:03:11.800 --> 00:03:12.220 Awesome. 00:03:12.220 --> 00:03:15.480 What is a forward deployed engineer do? 00:03:15.480 --> 00:03:19.480 Do you get called into big companies who want to adopt the technology? 00:03:19.480 --> 00:03:21.820 And they say, hey, could you help us do this quicker? 00:03:21.820 --> 00:03:23.860 Or are they running in trouble? 00:03:23.860 --> 00:03:25.900 And they're like, you said it was fast. 00:03:25.900 --> 00:03:26.500 It's not fast. 00:03:26.500 --> 00:03:26.760 Help. 00:03:26.760 --> 00:03:28.920 Well, don't copy this every query. 00:03:28.920 --> 00:03:29.640 So you'll be better. 00:03:30.020 --> 00:03:31.240 There's quite a lot of variety. 00:03:31.240 --> 00:03:36.220 I think there's definitely some aspects of, you know, hey, can you help me with this? 00:03:36.220 --> 00:03:40.820 There's also aspects of as folks are getting started, they're kind of picking out the rest of the tools around the database. 00:03:40.820 --> 00:03:43.040 So what BI tool should I use? 00:03:43.040 --> 00:03:44.780 What orchestrator should I use? 00:03:44.780 --> 00:03:48.160 And then, you know, we do get into some SQL optimization, stuff like that. 00:03:48.220 --> 00:03:49.660 So that's kind of the customer facing side. 00:03:49.660 --> 00:03:50.660 That's the majority of the role. 00:03:50.660 --> 00:03:58.800 There's also troubleshooting that goes along with it, as well as kind of feeding things back into the company, being the voice of the customer saying, hey, we've had three people hit this. 00:03:58.800 --> 00:04:01.440 Is there a way we can build something different? 00:04:01.440 --> 00:04:01.700 Yeah. 00:04:01.700 --> 00:04:02.300 Yeah. 00:04:02.540 --> 00:04:04.720 Like everyone thinks they should do it this way. 00:04:04.720 --> 00:04:08.840 We need to change our docs or they want this particular feature and we don't have it. 00:04:08.840 --> 00:04:09.220 Yeah. 00:04:09.220 --> 00:04:10.000 Yep. 00:04:10.000 --> 00:04:10.420 Yep. 00:04:10.420 --> 00:04:12.320 It's a good cycle that we've got going. 00:04:12.320 --> 00:04:13.360 It sounds really fun. 00:04:13.360 --> 00:04:14.580 It's a lot of fun. 00:04:14.580 --> 00:04:16.140 The variety is my favorite part. 00:04:16.420 --> 00:04:21.560 And, you know, a lot of fun investigation, troubleshooting, you know, got to reframe it as a scavenger hunt. 00:04:21.560 --> 00:04:22.960 You know, it's fun. 00:04:22.960 --> 00:04:23.420 Yeah. 00:04:23.420 --> 00:04:25.500 Every day is like a box of chocolates, huh? 00:04:25.500 --> 00:04:26.440 Oh, yeah. 00:04:26.440 --> 00:04:27.560 A little different all the time. 00:04:27.560 --> 00:04:29.300 You know, you don't know what you're going to get. 00:04:29.300 --> 00:04:33.480 I used to do in-person professional training. 00:04:33.480 --> 00:04:39.780 You know, I'd travel to Chicago or New York or Beijing and I would show up Monday morning. 00:04:39.780 --> 00:04:43.120 I mean, all right, folks, what are we, you know, what's the situation? 00:04:43.280 --> 00:04:46.940 Obviously, I didn't know what we were going to talk about, but it was still, it was different every time. 00:04:46.940 --> 00:04:48.040 Every week was different. 00:04:48.040 --> 00:04:50.960 Sounds like it might be not so different from what you're doing. 00:04:50.960 --> 00:04:55.080 Are a lot of these in-person things or are they mostly Zoom style? 00:04:55.080 --> 00:04:56.520 It's Zoom style. 00:04:56.520 --> 00:05:01.540 You know, just we work with a variety of folks, not, you know, like a couple of really giant customers. 00:05:01.540 --> 00:05:04.820 So with that variety means that I couldn't fly fast enough. 00:05:04.820 --> 00:05:06.120 Yeah, for sure. 00:05:06.120 --> 00:05:10.160 Or, you know, issues I've had as well as like three people on the team are in this city. 00:05:10.160 --> 00:05:12.340 Three people are in that other city and one is here. 00:05:12.440 --> 00:05:15.320 It's like even if you flew somewhere, you would only be, you'd still be on Zoom. 00:05:15.320 --> 00:05:16.500 So you might as well just be on Zoom. 00:05:16.500 --> 00:05:18.000 Definitely, definitely. 00:05:18.000 --> 00:05:18.820 Absolutely. 00:05:18.820 --> 00:05:19.760 The world we live in. 00:05:19.760 --> 00:05:27.440 Well, I mean, that's a blessing, honestly, to not have to fly to all these places as well as just be able to pop in and out, right? 00:05:27.440 --> 00:05:29.080 I mean, this work from home story. 00:05:29.080 --> 00:05:29.700 It's amazing. 00:05:29.700 --> 00:05:31.380 It's only getting better all the time. 00:05:31.900 --> 00:05:35.900 So let's talk DuckDB and databases. 00:05:35.900 --> 00:05:38.960 I think maybe setting a bit of a mental model. 00:05:38.960 --> 00:05:43.080 A lot of folks are familiar with SQLite, right? 00:05:43.080 --> 00:05:43.520 Yep. 00:05:43.520 --> 00:05:43.880 Absolutely. 00:05:43.880 --> 00:05:58.180 And so SQLite is a library, not a server that you can use that then plugs into your application, works on a local file rather than a certain thing you run in another VM or however you, you know, there's not a managed SQLite. 00:05:58.420 --> 00:06:01.140 Not that I know, I know maybe there's a managed SQLite, I don't know of one. 00:06:01.140 --> 00:06:01.560 There are. 00:06:01.560 --> 00:06:02.020 There are. 00:06:02.020 --> 00:06:02.720 Are there? 00:06:02.720 --> 00:06:03.160 Incredible. 00:06:03.160 --> 00:06:09.580 Yeah, there's a bunch of cool stuff that you can do if you have a database that's just part of your application instead of somewhere else. 00:06:10.000 --> 00:06:21.820 You know, another one that I just ran across is Mocha-py or there's no doubt, there's Dashpy, which is a Python wrapper around the, I think it's Rust Mocha caching thing. 00:06:21.820 --> 00:06:27.780 But instead of being Redis or something like that, it's like an in-memory thing that you can run for sort of the same purpose. 00:06:27.780 --> 00:06:31.080 So DuckDB sort of falls into that realm, right? 00:06:31.080 --> 00:06:31.660 Yes. 00:06:31.660 --> 00:06:34.780 I think it's an in-process database, just like you said. 00:06:34.780 --> 00:06:35.880 So it's definitely a library. 00:06:35.880 --> 00:06:37.800 You know, it's a import DuckDB. 00:06:37.940 --> 00:06:42.820 So it's not in the Python standard library, but it is on PyPI, just pip install DuckDB. 00:06:42.820 --> 00:06:45.100 It's pre-compiled, zero dependencies. 00:06:45.100 --> 00:06:46.740 So it is super easy. 00:06:46.740 --> 00:06:49.460 Basically, anywhere you can get to pip, you can get DuckDB run in. 00:06:49.460 --> 00:06:50.760 So that could be your laptop. 00:06:50.760 --> 00:06:52.220 That could be a big, beefy server. 00:06:52.220 --> 00:06:54.300 It could be a Lambda function. 00:06:54.300 --> 00:06:58.120 It could be inside of your orchestrator, you know, in a GitHub action. 00:06:58.120 --> 00:06:59.380 Kind of slots in everywhere. 00:06:59.380 --> 00:07:00.220 That's part of the magic. 00:07:00.220 --> 00:07:05.860 Presumably, when you pip install DuckDB, it's just downloading a wheel for your architecture, for your platform? 00:07:06.340 --> 00:07:06.520 Yep. 00:07:06.520 --> 00:07:08.140 Pre-compiled all the combinations. 00:07:08.140 --> 00:07:09.340 Yeah, that's really good. 00:07:09.340 --> 00:07:16.040 Yeah, if you just sit on the DuckDB.org homepage, you can learn about it as it animates different messages to you. 00:07:16.040 --> 00:07:22.180 It says DuckDB is a fast analytical database system, open source database system, in-memory database system. 00:07:22.180 --> 00:07:23.340 We'll see what else. 00:07:23.340 --> 00:07:23.560 Yeah. 00:07:23.560 --> 00:07:24.280 Portable. 00:07:24.640 --> 00:07:29.040 So, yeah, it works on most of the places, the main operating systems, right? 00:07:29.040 --> 00:07:29.640 Yes. 00:07:29.640 --> 00:07:30.500 Yep, it's all over. 00:07:30.500 --> 00:07:32.100 Can I Raspberry Pi it? 00:07:32.100 --> 00:07:33.460 I'd imagine the answer is yes. 00:07:33.460 --> 00:07:35.500 Can I CircuitPython it? 00:07:35.500 --> 00:07:36.580 Probably not, right? 00:07:36.580 --> 00:07:37.100 I haven't checked that. 00:07:37.260 --> 00:07:38.700 You can do iPhone and Android. 00:07:38.700 --> 00:07:43.440 There's a Swift client, so you can build iPhone apps and Android works as well. 00:07:43.440 --> 00:07:44.340 So, yeah, you could do that. 00:07:44.340 --> 00:07:46.420 Works in your browser with WebAssembly. 00:07:46.420 --> 00:07:49.140 It'll work with Pyodide in the browser as well. 00:07:49.140 --> 00:07:50.740 So, it really goes all over. 00:07:50.740 --> 00:07:51.820 Oh, that's really cool. 00:07:51.940 --> 00:08:06.360 Yeah, one of the things that I said people are doing interesting stuff with, I had in mind, with SQLite is I saw some folks suggesting using SQLite in the browser instead of local storage and like the local, like all the JavaScript-y things. 00:08:06.360 --> 00:08:07.860 And could I do that with DuckDB? 00:08:07.860 --> 00:08:10.140 You could definitely do that with DuckDB. 00:08:10.140 --> 00:08:15.360 I think the use case is slightly different, but that's sort of the benefit is you can use both. 00:08:15.540 --> 00:08:30.160 So, definitely could talk a bit more about the differences, but I'd say in general, a good mental model for DuckDB is kind of a from scratch separate project from SQLite, but taking a lot of the lessons and learnings about what makes people love SQLite and bringing that into the analytical realm. 00:08:30.160 --> 00:08:37.540 And that's really, instead of inserting and updating tons of tiny transactions all the time really fast, it's big bulk operations. 00:08:37.540 --> 00:08:42.580 Aggregate a billion rows, join a billion rows to a billion rows, that type of thing. 00:08:42.580 --> 00:08:45.080 Yeah, doing that in memory in JavaScript is not that great. 00:08:45.080 --> 00:08:47.880 Doing that in memory in Python is not that great. 00:08:47.880 --> 00:08:56.720 But it sounds a little bit more like vector programming in the sense of kind of like you would do with pandas or Polars, right? 00:08:56.720 --> 00:09:02.300 You wouldn't loop over, you shouldn't loop over a pandas data frame processing each item. 00:09:02.300 --> 00:09:11.780 You should issue vector operations that apply to every row, like multiply this column by two rather than for every C in column, you know, whatever, like looping it over. 00:09:11.780 --> 00:09:12.360 Yes. 00:09:12.360 --> 00:09:14.040 So, it is exactly what you said. 00:09:14.100 --> 00:09:15.000 It's designed for that. 00:09:15.000 --> 00:09:17.020 It's vectorized execution. 00:09:17.020 --> 00:09:18.720 So, it's a little different than pandas. 00:09:18.720 --> 00:09:21.340 It's, you know, Polars is a bit more similar. 00:09:21.340 --> 00:09:25.500 Where instead, pandas will process a whole column at a time. 00:09:25.500 --> 00:09:30.180 And that works a lot better than one row at a time until your columns get too big and then it crashes. 00:09:30.180 --> 00:09:30.720 Yeah. 00:09:30.720 --> 00:09:34.340 So, what DuckDB does is it does it in chunks of about 2,000 rows at a time. 00:09:34.340 --> 00:09:41.200 And so, that way you get all the benefits of your modern CPU, you know, hierarchy, your caches, all that, where it's just pumping data through. 00:09:41.580 --> 00:09:46.460 But you also conserve memory and you can not get into issues with that. 00:09:46.460 --> 00:09:51.340 I will also say it's technically we are an in-process database, not an in-memory database. 00:09:51.340 --> 00:09:52.080 Oh, sorry. 00:09:52.080 --> 00:09:54.000 If I said in-memory, I meant in-process. 00:09:54.000 --> 00:09:54.500 Yeah. 00:09:54.500 --> 00:09:58.080 It is, you know, very much an interchangeable word like in English. 00:09:58.080 --> 00:10:00.980 We just are very specific about it in database land. 00:10:00.980 --> 00:10:01.820 No, you do. 00:10:01.820 --> 00:10:04.060 And you do support in-memory databases. 00:10:04.060 --> 00:10:04.740 Yes. 00:10:04.740 --> 00:10:06.400 But that's not the same as in-process. 00:10:06.400 --> 00:10:07.880 Yeah, sorry, in-process is what I meant. 00:10:07.880 --> 00:10:08.600 Oh, no problem. 00:10:08.600 --> 00:10:12.440 All I mean to say there is, you know, you don't need to limit yourself to your size of RAM. 00:10:12.440 --> 00:10:16.560 You know, you can solve one terabyte problems on your laptop with DuckDB, no problem. 00:10:16.560 --> 00:10:17.060 Really? 00:10:17.060 --> 00:10:21.200 Yes, it handles larger than memory data, both streaming from disk. 00:10:21.200 --> 00:10:25.720 So, it'll just read just the pieces it needs as it goes and even in the intermediates. 00:10:25.860 --> 00:10:30.080 So, let's say you join two tables together and you, you know, multiply the number of rows. 00:10:30.080 --> 00:10:34.640 It'll send things to disk as it gets memory pressure close to your memory limit. 00:10:34.640 --> 00:10:38.360 So, it's a lot of effort has been put into that in the last year or so. 00:10:38.360 --> 00:10:40.240 So, it's working pretty smooth now. 00:10:40.240 --> 00:10:42.740 Yeah, I can imagine that that's super important. 00:10:42.740 --> 00:10:48.500 So, I'm really looking forward to talking about schemas, indexes, those kinds of things, 00:10:48.500 --> 00:10:50.760 which sound like they may play important roles here. 00:10:50.760 --> 00:10:54.500 But you talked about these different ways in which you can use DuckDB. 00:10:55.200 --> 00:11:00.800 Looking through the documentation, it looks like it works with not just Python, but quite a few platforms or languages. 00:11:00.800 --> 00:11:03.260 Yes, somewhere north of 15. 00:11:03.260 --> 00:11:05.860 So, Python is the most popular way to use DuckDB. 00:11:05.860 --> 00:11:09.820 We've got some really tight integrations with data frame libraries, which we can definitely talk more about. 00:11:09.820 --> 00:11:11.840 There's a lot of Java use out there. 00:11:12.160 --> 00:11:13.960 So, JDBC, we've got a JDBC driver. 00:11:13.960 --> 00:11:21.600 You can use it in JavaScript, both in the browser with Wasm or on the server with Node, popular Node package. 00:11:21.600 --> 00:11:25.320 You can also do Go, Rust, C++, C. 00:11:25.320 --> 00:11:30.380 I think there's like a community-driven Ruby client, you know, really all over the place. 00:11:30.760 --> 00:11:34.060 So, .NET, C#, you can use it from .NET, all over. 00:11:34.060 --> 00:11:34.480 Yeah. 00:11:34.480 --> 00:11:35.280 Yeah, that's awesome. 00:11:35.280 --> 00:11:38.560 And I'm guessing it's interchangeable, the file? 00:11:38.560 --> 00:11:39.760 Yes. 00:11:39.760 --> 00:11:45.280 So, just like SQLite files are designed to be read for a long time, they're very portable. 00:11:45.280 --> 00:11:49.060 DuckDB just kind of reached the 1.0 milestone in June. 00:11:49.320 --> 00:11:50.600 So, super excited about that. 00:11:50.600 --> 00:11:54.040 And with that, you know, promised stability with the file format. 00:11:54.040 --> 00:12:01.160 And that means that we expect for the next somewhere around five years to have your DuckDB files be totally compatible, 00:12:01.160 --> 00:12:04.380 where you can read a five-year-old file five years from now type thing. 00:12:04.380 --> 00:12:04.980 Yeah. 00:12:04.980 --> 00:12:11.420 Which means you can, it's like SQLite in that you can fit all your tables and all the relationships between them all in one file. 00:12:11.420 --> 00:12:14.280 So, that's one nice thing about SQLite. 00:12:14.280 --> 00:12:15.640 You can email the whole database. 00:12:15.640 --> 00:12:17.160 You can do the same with DuckDB. 00:12:17.600 --> 00:12:21.840 But DuckDB is going to store it a lot more compactly if you've got a lot of data. 00:12:21.840 --> 00:12:22.340 Oh, really? 00:12:22.340 --> 00:12:22.700 Okay. 00:12:22.700 --> 00:12:24.960 You guys have worked a lot on the file format. 00:12:24.960 --> 00:12:29.620 It's, again, taking inspiration from SQLite, the format is very separate. 00:12:29.620 --> 00:12:34.320 And in databases, you kind of have a pretty hard fork in the road pretty early on in your architecture, 00:12:34.320 --> 00:12:37.680 which is you want to be row-based or do you want to be column-based? 00:12:37.680 --> 00:12:38.340 Right. 00:12:38.340 --> 00:12:42.380 And row-based is SQLite and column-based would be your pandas, your Polars, 00:12:42.380 --> 00:12:46.220 and most of your cloud data warehouses, your snowflakes, that type of thing. 00:12:46.640 --> 00:12:53.600 And once you go column-based, suddenly your compression is amazing because you store data that's very similar right next to each other. 00:12:53.600 --> 00:12:54.820 So, you can really compress it. 00:12:54.820 --> 00:12:56.200 You know, your dates are back-to-back. 00:12:56.200 --> 00:13:01.680 Instead of, you know, a date column, string column, integer column in a row-based store, they don't compress well. 00:13:01.680 --> 00:13:04.320 So, we can get somewhere around 5x the compression typically. 00:13:04.320 --> 00:13:04.820 Okay. 00:13:04.820 --> 00:13:05.900 That's really great. 00:13:06.380 --> 00:13:13.220 And you could take this file and you could put it into S3 or use it with some sort of Jupyter Notebook, right? 00:13:13.220 --> 00:13:15.500 And just, they could just grab the file and run with it. 00:13:15.500 --> 00:13:16.360 And it's all the data, yeah? 00:13:16.360 --> 00:13:17.160 Yes. 00:13:17.160 --> 00:13:20.480 Object stores are really the name of the game there. 00:13:20.860 --> 00:13:26.400 You can even work with a file on Object Store and read it without having to download the whole file first. 00:13:26.400 --> 00:13:30.620 So, DuckDB supports something like SQLite in that it's got this attach syntax. 00:13:30.620 --> 00:13:36.860 So, you can say, hey, I want you to attach this file and then just read the pieces of it that I actually query, which is pretty slick. 00:13:37.120 --> 00:13:38.340 Yeah, that is super slick. 00:13:38.340 --> 00:13:38.900 All right. 00:13:38.900 --> 00:13:41.480 Let's, let me scroll down here. 00:13:41.480 --> 00:13:45.240 And let's talk about maybe using it from Python. 00:13:45.240 --> 00:13:49.420 Give us a sense of, like, what working with DuckDB is like here. 00:13:49.420 --> 00:13:50.000 You bet. 00:13:50.000 --> 00:13:54.060 So, you've got your pip install DuckDB, which will download that pre-compiled binary. 00:13:54.060 --> 00:13:55.340 It's like 20 or 30 megabytes. 00:13:55.340 --> 00:13:56.980 So, pretty close to instant. 00:13:56.980 --> 00:14:00.700 Then import DuckDB, and then you can do DuckDB.SQL. 00:14:00.700 --> 00:14:05.440 And then within that function, you just pass in a string of your SQL statement and it'll execute. 00:14:05.440 --> 00:14:06.980 It's really that simple. 00:14:06.980 --> 00:14:09.660 And so, it follows the DB API if you want. 00:14:09.660 --> 00:14:11.600 So, you can get back your typical suples. 00:14:11.600 --> 00:14:15.460 Or you can say .df at the end and get back a Pandas data frame. 00:14:15.460 --> 00:14:17.700 Or .pl and get colors. 00:14:17.700 --> 00:14:18.620 All kinds of stuff. 00:14:18.620 --> 00:14:19.080 Yeah. 00:14:19.080 --> 00:14:24.540 I saw that there's a lot of to and from the popular data science libraries, which seems really nice. 00:14:24.540 --> 00:14:29.640 You work in your data science library or maybe run a query and get a subset of your data. 00:14:29.640 --> 00:14:36.740 And then maybe plot that with PlotLayer, Altair, or pass it along to other things that expect the data frame, right? 00:14:36.740 --> 00:14:37.740 Yes, exactly. 00:14:37.740 --> 00:14:42.840 A lot of the DuckDB ethos is don't ask folks to ship their data necessarily somewhere else. 00:14:42.840 --> 00:14:45.700 Don't ask them to change their workflow dramatically to use a database. 00:14:45.700 --> 00:14:47.540 Meet folks right where they are. 00:14:47.540 --> 00:14:51.200 And that means you can have one line of code be in your data frame library of choice. 00:14:51.480 --> 00:14:53.200 Pandas, Polar, or Arrow. 00:14:53.200 --> 00:14:55.400 And then a line of code in DuckDB. 00:14:55.400 --> 00:14:58.020 And then you're right back in your data frame in the next line. 00:14:58.020 --> 00:14:59.780 So, it fits really wherever you want. 00:14:59.780 --> 00:15:02.220 It's a great fit if you want to throw some SQL at a problem. 00:15:02.220 --> 00:15:06.220 Some problems are easier to formulate in SQL, I think, than in data frame libraries. 00:15:06.220 --> 00:15:06.980 And vice versa. 00:15:06.980 --> 00:15:11.340 But also, some libraries, it's harder to scale larger than memory. 00:15:11.340 --> 00:15:14.860 And DuckDB does have a lot of nice performance under the hood. 00:15:14.860 --> 00:15:22.160 So, maybe the hardest part of your workflow, you can slide in DuckDB and make the minimal change to get it working. 00:15:23.700 --> 00:15:26.780 This portion of Talk Python To Me is brought to you by Sentry. 00:15:26.780 --> 00:15:27.880 Code breaks. 00:15:27.880 --> 00:15:29.180 It's a fact of life. 00:15:29.180 --> 00:15:31.320 With Sentry, you can fix it faster. 00:15:31.320 --> 00:15:37.040 As I've told you all before, we use Sentry on many of our apps and APIs here at Talk Python. 00:15:37.040 --> 00:15:42.760 I recently used Sentry to help me track down one of the weirdest bugs I've run into in a long time. 00:15:42.760 --> 00:15:43.700 Here's what happened. 00:15:43.700 --> 00:15:49.320 When signing up for our mailing list, it would crash under a non-common execution pass. 00:15:49.540 --> 00:15:55.460 Like, situations where someone was already subscribed or entered an invalid email address or something like this. 00:15:55.460 --> 00:16:01.460 The bizarre part was that our logging of that unusual condition itself was crashing. 00:16:01.460 --> 00:16:04.680 How is it possible for our log to crash? 00:16:04.680 --> 00:16:07.240 It's basically a glorified print statement. 00:16:07.240 --> 00:16:08.940 Well, Sentry to the rescue. 00:16:08.940 --> 00:16:15.400 I'm looking at the crash report right now, and I see way more information than you'd expect to find in any log statement. 00:16:15.400 --> 00:16:18.460 And because it's production, debuggers are out of the question. 00:16:19.320 --> 00:16:30.340 I see the traceback, of course, but also the browser version, client OS, server OS, server OS version, whether it's production or Q&A, the email and name of the person signing up. 00:16:30.340 --> 00:16:32.380 That's the person who actually experienced the crash. 00:16:32.380 --> 00:16:35.220 Dictionaries of data on the call stack and so much more. 00:16:35.220 --> 00:16:36.160 What was the problem? 00:16:36.160 --> 00:16:45.860 I initialized the logger with the string info for the level rather than the enumeration dot info, which was an integer-based enum. 00:16:45.980 --> 00:16:52.420 So the logging statement would crash, saying that I could not use less than or equal to between strings and ints. 00:16:52.420 --> 00:16:53.840 Crazy town. 00:16:53.840 --> 00:17:00.040 But with Sentry, I captured it, fixed it, and I even helped the user who experienced that crash. 00:17:00.040 --> 00:17:01.460 Don't fly blind. 00:17:01.460 --> 00:17:03.140 Fix code faster with Sentry. 00:17:03.140 --> 00:17:07.180 Create your Sentry account now at talkpython.fm/sentry. 00:17:07.260 --> 00:17:19.520 And if you sign up with the code TALKPYTHON, all capital, no spaces, it's good for two free months of Sentry's business plan, which will give you up to 20 times as many monthly events as well as other features. 00:17:20.060 --> 00:17:37.880 It seems to me like maybe you put all of the data into DuckDB, and then you can ask questions which result in Python data science objects that then you can work on, where those questions result in, give me the stuff for this state or this time period or something like that where it's a reasonable amount of data. 00:17:37.880 --> 00:17:40.980 But if you tried to just load it all, then it would say no. 00:17:41.500 --> 00:17:43.760 I think that's a great way to go about it for sure. 00:17:43.760 --> 00:17:51.180 You know, another common workflow is to do some, you know, similar things, but with Parquet files or CSVs or JSONs. 00:17:51.180 --> 00:17:59.000 So in the same way you can store a lot of your data in DuckDB, you can periodically write chunks out to Parquet or read in from Parquet, all that type of stuff too. 00:17:59.000 --> 00:17:59.420 Yeah. 00:17:59.420 --> 00:18:00.780 Tell people what Parquet is. 00:18:00.780 --> 00:18:04.020 I know it comes from PyArrow or the Arrow project, right? 00:18:04.020 --> 00:18:05.440 Where does it come from? 00:18:05.440 --> 00:18:06.740 I know they use it. 00:18:06.740 --> 00:18:08.260 I don't know if they came up with it, but yeah. 00:18:08.260 --> 00:18:10.920 They definitely collaborated and were a big part of it. 00:18:11.040 --> 00:18:14.100 So it's a community developed open format. 00:18:14.100 --> 00:18:15.360 It's Apache Parquet. 00:18:15.360 --> 00:18:21.180 So it's a open source community driven and it's a columnar format. 00:18:21.180 --> 00:18:26.180 So instead of storing things by rows, it's columnar storage and it's chunked columnar storage. 00:18:26.180 --> 00:18:30.620 So it's sort of like somewhere around every million rows or so you kind of write out another chunk. 00:18:30.620 --> 00:18:36.920 And it ends up being a really good format to store large amounts of data in the cloud. 00:18:36.920 --> 00:18:40.240 And it's typically what's underneath a data lake or a data lake house. 00:18:40.580 --> 00:18:43.260 The vast majority of those have Parquet under the hood. 00:18:43.260 --> 00:18:44.280 I see. 00:18:44.280 --> 00:18:46.000 We love the Parquet format at DuckDB. 00:18:46.000 --> 00:18:46.940 We read it and write it. 00:18:46.940 --> 00:18:49.920 We have a custom from scratch reader, custom from scratch writer for it. 00:18:49.920 --> 00:18:53.920 We actually prefer our own format given the choice as well. 00:18:53.920 --> 00:18:56.960 Parquet has been around for a decade or so at this point. 00:18:56.960 --> 00:19:00.320 And we've added a few things over time into DuckDB that are new. 00:19:00.320 --> 00:19:00.660 Yeah. 00:19:00.660 --> 00:19:04.800 Parquet is one of those things that Pandas 2.0 supports it, right? 00:19:04.800 --> 00:19:05.940 Polar supports it. 00:19:05.940 --> 00:19:07.480 It's pretty universal. 00:19:07.480 --> 00:19:12.280 So it might be a good way to get data from that you've worked on before into DuckDB. 00:19:12.700 --> 00:19:13.480 Yes, it's great. 00:19:13.480 --> 00:19:17.560 I think, you know, most universal CSV, most difficult, also CSV. 00:19:17.560 --> 00:19:20.260 Parquet is a really great compromise there. 00:19:20.260 --> 00:19:21.260 Yeah. 00:19:21.260 --> 00:19:24.080 Hold on. 00:19:24.080 --> 00:19:25.080 My computer is lagging. 00:19:25.080 --> 00:19:26.240 It's distracting me. 00:19:26.240 --> 00:19:27.300 I apologize. 00:19:27.300 --> 00:19:29.860 One sec. 00:19:29.860 --> 00:19:30.440 Scroll, scroll. 00:19:31.160 --> 00:19:36.460 So DuckDB works well, it sounds like for the data science story, like it's built for the data science story. 00:19:36.460 --> 00:19:42.460 If I was wanting to store relational data, you know, maybe I've already got DuckDB working for something. 00:19:42.460 --> 00:19:48.480 I'm like, you know, do I necessarily want to bring SQLite into the story as well and have these two things? 00:19:48.480 --> 00:19:53.740 Is it possible to, say, do relational type things with DuckDB? 00:19:53.740 --> 00:19:55.200 Yes, that's a great question. 00:19:55.200 --> 00:19:58.500 So I think DuckDB is trying to bring the best of both. 00:19:58.500 --> 00:20:00.160 It is a full relational database. 00:20:00.640 --> 00:20:06.620 And as well as supporting some of the, you know, data science workflows that you typically in the past would have been doing in a data frame. 00:20:06.620 --> 00:20:10.160 So DuckDB is an analytical system, not transactional. 00:20:10.160 --> 00:20:15.980 So it's not going to be the fastest at doing individual row operations, but it supports them all. 00:20:15.980 --> 00:20:23.620 And it supports them with full asset transactions that you'd expect from a database where you won't get database corruption, even if your computer cuts out. 00:20:23.620 --> 00:20:27.220 You know, it's got that kind of robustness that you expect from a database. 00:20:27.400 --> 00:20:34.740 So part of the story, though, is that with an analytical database, it's typically not your only database unless you're doing data science work. 00:20:34.740 --> 00:20:44.420 If you're building an application, you're going to want a transactional database for, you know, saving your, you know, when a customer places an order, you know, all that type of transactional stuff is still critical. 00:20:44.780 --> 00:20:46.920 So typically DuckDB is going to be layered in addition. 00:20:46.920 --> 00:20:50.740 And the interoperability story there with DuckDB is pretty, pretty fantastic. 00:20:50.740 --> 00:20:54.380 You can actually read SQLite files directly with DuckDB. 00:20:54.920 --> 00:21:00.280 And it's going to be, if you're doing any sort of advanced processing on them, it's going to be a lot quicker in DuckDB. 00:21:00.280 --> 00:21:02.140 You can even read Postgres databases. 00:21:02.140 --> 00:21:04.000 You can read MySQL databases. 00:21:04.000 --> 00:21:04.500 Oh, wow. 00:21:04.500 --> 00:21:06.260 There's a community extension to read BigQuery. 00:21:06.260 --> 00:21:14.980 So it's really a universal Swiss Army knife in a lot of ways to read from those operational data stores, but to do those analytical tasks. 00:21:14.980 --> 00:21:17.040 You know, look at all my orders. 00:21:17.040 --> 00:21:19.160 What was the most popular five products? 00:21:19.560 --> 00:21:23.580 You know, that's going to be a tough query for a transactional system, but bread and butter for DuckDB. 00:21:23.580 --> 00:21:35.080 So when you say read from, let's say, Postgres, does that import it into a table in DuckDB or does it translate the queries over to the underlying data system? 00:21:35.080 --> 00:21:36.180 What's happening there? 00:21:36.180 --> 00:21:37.360 It's a great question. 00:21:37.360 --> 00:21:42.100 We are communicating with Postgres, so we're not importing everything. 00:21:42.100 --> 00:21:46.080 We're sending the query to it and we're reading all the data from Postgres. 00:21:46.320 --> 00:21:49.900 Typically, that's going to be sparing the table or forming a view from Postgres. 00:21:49.900 --> 00:21:54.220 And then after that point, bring it into the DuckDB engine for subsequent processing. 00:21:54.220 --> 00:21:56.280 But you don't have to migrate all your data. 00:21:56.280 --> 00:22:00.180 It's kind of a data virtualization is one of the buzzwords. 00:22:00.180 --> 00:22:01.100 It's been around a little bit. 00:22:01.100 --> 00:22:07.840 But, you know, it's getting to get some of the benefits of analytical performance without having to move your data first. 00:22:08.360 --> 00:22:14.220 It'll definitely be more performant if you put it in an analytical format like Parquet, like DuckDB, like an analytical database. 00:22:14.220 --> 00:22:23.160 But you get a big fraction of that benefit with the engine part, which is the vectorized engine of doing things 2,000 rows at a time instead of one row at a time. 00:22:23.160 --> 00:22:24.780 That's a lot of it. 00:22:25.040 --> 00:22:25.900 Yeah, that's a huge benefit. 00:22:25.900 --> 00:22:29.740 It's almost like microservices for databases. 00:22:29.740 --> 00:22:31.280 You can click the things together. 00:22:31.280 --> 00:22:40.640 But the benefit would be, you know, that if you have an operational database, that is, it's getting read to and written to and read from in real time. 00:22:40.640 --> 00:22:43.800 When you ask questions about it, it's using that real time data. 00:22:43.800 --> 00:22:47.700 It's not, well, we did an export last night and here's what we know. 00:22:47.840 --> 00:22:50.100 You can just get more live data, I guess. 00:22:50.100 --> 00:22:52.000 Yes, that's definitely a use case for that. 00:22:52.000 --> 00:22:54.900 You can kind of go either end of that spectrum. 00:22:54.900 --> 00:23:04.020 You can use that as an easy way to import or you can pull it live or you can kind of do midway and say, for any data older than an hour, use what I extracted. 00:23:04.020 --> 00:23:08.320 But grab the last hour right out of the operational database and get the best of both. 00:23:08.320 --> 00:23:08.800 Oh, wow. 00:23:08.800 --> 00:23:09.120 Okay. 00:23:09.120 --> 00:23:10.580 Yeah, that'd be pretty wild, wouldn't it? 00:23:10.580 --> 00:23:16.580 So you say that you all have the simplest or friendliest SQL syntax. 00:23:17.420 --> 00:23:18.280 What's the story with that? 00:23:18.280 --> 00:23:18.980 Sure. 00:23:18.980 --> 00:23:30.380 I think a lot of that is around part of the origin story of DuckDB is reaching out to the data science community and hearing feedback that we've invented this entire other thing called data frames because we didn't love databases. 00:23:30.380 --> 00:23:31.220 Yeah, exactly. 00:23:31.220 --> 00:23:35.340 They didn't answer the questions we wanted or whatever. 00:23:35.340 --> 00:23:35.660 Yeah. 00:23:35.660 --> 00:23:36.200 Right. 00:23:36.200 --> 00:23:37.880 It was, there was a lot of friction. 00:23:37.880 --> 00:23:45.120 And, you know, if you've, you know, folks have installed Postgres and set it up, there's a lot more to it than, you know, import pandas as PD. 00:23:45.600 --> 00:23:49.360 It's just a different level of hassle that you have to do. 00:23:49.360 --> 00:23:51.360 The SQL syntax is no different. 00:23:51.360 --> 00:23:53.780 You know, SQL is an old language, much older than Python. 00:23:54.300 --> 00:24:00.560 And it's got a lot of very interesting behavior that, you know, by modern standards is very different. 00:24:00.560 --> 00:24:04.220 So with DuckDB, we've really pushed the envelope in a lot of ways. 00:24:04.220 --> 00:24:04.960 Nice. 00:24:04.960 --> 00:24:14.380 Well, you know, many of the things that relational databases were built for and when they're designed, this was the 70s and they had different, you know, memory was expensive. 00:24:14.380 --> 00:24:15.640 Disk was expensive. 00:24:15.640 --> 00:24:18.660 So they made these trade-offs in that world, right? 00:24:18.660 --> 00:24:19.620 Yes. 00:24:19.620 --> 00:24:20.940 And it's absolutely. 00:24:21.700 --> 00:24:25.840 There's definitely some aspects of SQL where it's been around a long time, so it'll be around forever. 00:24:25.840 --> 00:24:31.700 But what DuckDB is trying to do is have it not be frozen in time forever and really push the whole language forward. 00:24:31.700 --> 00:24:33.380 So there's a couple of cool ways. 00:24:33.380 --> 00:24:40.200 The syntax itself is designed to be both easier to use and also push it into areas that are new and different for databases. 00:24:40.200 --> 00:24:46.280 So easier to use, for example, in SQL, if you want to aggregate, you kind of typically need two ingredients. 00:24:46.280 --> 00:24:49.860 You need an aggregation function like a sum or a max or an average. 00:24:49.860 --> 00:24:55.160 And then you want to group by clause to say, what's the level of detail I want to group at? 00:24:55.160 --> 00:24:58.040 Do I want to group by customer or customer and order? 00:24:58.040 --> 00:25:00.280 You know, what level of detail am I looking at? 00:25:00.280 --> 00:25:08.900 DuckDB can actually infer that level of detail with group by all and just say, whichever columns you're looking at, if you're looking at the customer ID and you're getting an average, 00:25:09.040 --> 00:25:11.500 okay, I'll just aggregate it up at customer ID level for you. 00:25:11.500 --> 00:25:13.720 And so it cuts out a whole clause of SQL. 00:25:13.720 --> 00:25:17.000 You basically never have to worry about the group by clause ever again. 00:25:17.000 --> 00:25:19.040 Just put the keyword all, you're good to go. 00:25:19.040 --> 00:25:21.160 I love it because the group by clause is hard. 00:25:21.160 --> 00:25:25.940 It's anytime you have to edit something in two places, just you're already in trouble, right? 00:25:25.940 --> 00:25:29.140 I mean, just, you know, the don't repeat yourself is very valuable. 00:25:29.140 --> 00:25:29.620 Yeah. 00:25:29.620 --> 00:25:30.020 Yeah. 00:25:30.020 --> 00:25:35.020 The joins and the group buys and the whole aggregation pipeline stuff, which is incredibly powerful, 00:25:35.020 --> 00:25:38.880 especially in analytical databases like DuckDB, but also hard. 00:25:38.880 --> 00:25:40.320 Yes, absolutely. 00:25:40.320 --> 00:25:43.400 And I think obviously anything we can do to reduce the friction there is helpful. 00:25:43.400 --> 00:25:46.100 So we are talking about the friendly SQL dialect. 00:25:46.100 --> 00:25:51.380 I'll throw a teaser in there to say there are ways to use DuckDB without SQL as well in very Pythonic ways. 00:25:51.380 --> 00:25:54.140 So you really can have it both ways if you want. 00:25:54.140 --> 00:25:54.480 Okay. 00:25:54.480 --> 00:25:55.340 What about ORMs? 00:25:55.340 --> 00:25:58.800 Is there a concept of a ORM for DuckDB? 00:25:59.400 --> 00:26:01.600 I'd have to check exactly which ones support it. 00:26:01.600 --> 00:26:09.660 But I know at MotherDuck, we're working on a TypeScript ORM that'll support DuckDB SQLize. 00:26:10.460 --> 00:26:12.660 I'd have to check which other ones are out there that support it. 00:26:12.660 --> 00:26:14.680 But nothing in principle really stops it. 00:26:14.680 --> 00:26:21.100 We support the fundamental things you need, like transactions, like, you know, all the other pieces you need. 00:26:21.100 --> 00:26:21.460 Yeah. 00:26:21.460 --> 00:26:25.620 Basically, if it runs on pure SQL, then, you know, you should... 00:26:25.620 --> 00:26:31.480 ORMs are mostly about I'm querying against classes, and then I'm going to turn that into SQL for you. 00:26:31.480 --> 00:26:34.820 And then the things that come back, I'm going to turn them into objects, right? 00:26:34.820 --> 00:26:37.140 Like, I feel like both sides of that would be fine. 00:26:37.140 --> 00:26:38.140 Yes. 00:26:38.140 --> 00:26:40.860 And it is, you know, it's a relational database. 00:26:41.000 --> 00:26:42.160 So it's not... 00:26:42.160 --> 00:26:43.140 There are many... 00:26:43.140 --> 00:26:47.540 It'll look a lot very similar to the ORMs themselves, so it should be very compatible. 00:26:47.540 --> 00:26:49.200 Yeah, it seems possible. 00:26:49.200 --> 00:26:50.280 It seems totally possible. 00:26:50.280 --> 00:26:50.820 All right. 00:26:50.820 --> 00:27:00.720 So maybe let's talk through a little bit of the ways of working with DuckDB through, maybe connecting, running some queries, updating. 00:27:00.720 --> 00:27:04.780 But let me first ask you, what does a schema look like? 00:27:05.040 --> 00:27:16.400 If, for example, if I'm going to work with Postgres or SQLite, I need to run create scripts to create the tables and the indices and that kind of stuff before I can even talk to it, generally speaking. 00:27:16.400 --> 00:27:18.180 Is that needed for DuckDB? 00:27:18.180 --> 00:27:21.620 It doesn't look like it from the examples that I've seen, but how does that work? 00:27:21.620 --> 00:27:33.680 I think in those other databases, there are some ways where you don't necessarily have to do that, and DuckDB follows that same approach where it can automatically create tables for you based on the query that you pipe into it. 00:27:33.680 --> 00:27:42.520 So you create a table as the result of another SQL statement, and it will just automatically create that table with the data types that you pass through. 00:27:42.520 --> 00:27:50.840 So one key note, if folks really know a lot about SQLite, one of the defining characteristics of it is that it is very much non-typed as much as possible. 00:27:50.840 --> 00:27:55.160 VectDB is very much more traditional in that it does have typing. 00:27:55.160 --> 00:28:02.360 It does take full advantage of having nice numeric data types, date times that are true date times, not strings. 00:28:03.120 --> 00:28:07.340 It'll do its best to auto-convert for you, but it really does make it very easy. 00:28:07.340 --> 00:28:12.800 You can just create a table as, for example, create table as select star from this parquet file. 00:28:12.800 --> 00:28:14.240 It can really be that easy. 00:28:14.240 --> 00:28:16.580 It'll just auto-create the table based on the parquet file. 00:28:16.580 --> 00:28:16.960 Okay. 00:28:16.960 --> 00:28:18.260 Yeah, that's really neat. 00:28:18.260 --> 00:28:21.060 And a lot of those have their columns have data types and stuff, right? 00:28:21.340 --> 00:28:21.840 Yes. 00:28:21.840 --> 00:28:26.680 And we have also invested quite a lot in our CSV reader as well. 00:28:26.680 --> 00:28:31.360 So the CSV reader we consider to be up there with the best on the planet. 00:28:31.360 --> 00:28:37.300 It auto-infers the data types by sniffing the CSV to really deduce it. 00:28:37.300 --> 00:28:41.380 And it handles some seriously wacky CSV files. 00:28:41.380 --> 00:28:42.540 Okay. 00:28:42.540 --> 00:28:43.600 CSV files. 00:28:43.600 --> 00:28:46.280 I mean, whatever you're imagining, it's worse. 00:28:46.280 --> 00:28:49.000 It's worse than that. 00:28:49.000 --> 00:28:54.380 But DuckDB is okay and ready to handle it and making it easy. 00:28:54.460 --> 00:28:58.580 Because it really aligns with the DuckDB ethos, which is a database shouldn't be extra work. 00:28:58.580 --> 00:28:59.840 It should be saving you work. 00:28:59.840 --> 00:29:00.220 Yeah. 00:29:00.220 --> 00:29:04.580 And most of the time, step one of a database is import your data. 00:29:04.580 --> 00:29:08.760 And a lot of databases, that's not that easy to just import your data. 00:29:08.760 --> 00:29:12.640 Especially if it's not exactly the format of that database, right? 00:29:12.640 --> 00:29:15.520 It's like, well, okay, I have a .SQL file. 00:29:15.520 --> 00:29:16.840 It's a bunch of update statements. 00:29:16.840 --> 00:29:18.000 Sure, that's easy to import. 00:29:18.000 --> 00:29:21.820 But here's my CSV and actually you need to transform it or whatever. 00:29:21.820 --> 00:29:22.100 Yeah. 00:29:22.420 --> 00:29:27.020 Yes, there's a long tail of complexity there, but DuckDB is really trying to handle that 00:29:27.020 --> 00:29:31.760 and just make that first experience using a database just straight out of the box. 00:29:31.760 --> 00:29:32.360 Really easy. 00:29:32.360 --> 00:29:38.540 This portion of Talk Python To Me is brought to you by the Data Citizens Dialogues podcast. 00:29:38.540 --> 00:29:42.700 If you're ready for a deeper dive into the latest hot topics in data, 00:29:42.700 --> 00:29:45.760 you need to listen to the Data Citizens Dialogues podcast. 00:29:45.760 --> 00:29:49.380 Brought to you by Colibra, the leader in data intelligence. 00:29:49.920 --> 00:29:55.340 In every episode of Data Citizens Dialogues, industry leaders unpack data's impact on the world. 00:29:55.340 --> 00:30:01.260 From big picture questions like AI governance and data sharing to more nuanced questions like, 00:30:01.260 --> 00:30:04.920 how do we balance offense and defense in data management? 00:30:04.920 --> 00:30:10.200 You'll hear firsthand insights about the data conversations affecting all kinds of industries. 00:30:10.380 --> 00:30:14.060 With guests sharing unique stories from some of the world's largest companies, 00:30:14.060 --> 00:30:19.220 such as Adobe, Fidelity, Deloitte, Hewlett-Packard, McDonald's, and even the United States Coast Guard, 00:30:19.220 --> 00:30:22.640 you'll get an amazing look inside how these organizations handle their data. 00:30:22.640 --> 00:30:26.840 My favorite episode is Solving Data Discovery with a self-service approach. 00:30:26.840 --> 00:30:31.900 It's an interesting look inside creating a single source of truth at an online university. 00:30:32.360 --> 00:30:34.800 Check them out and try an episode for yourself. 00:30:34.800 --> 00:30:39.320 Find Data Citizens Dialogues at talkpython.fm/citizens. 00:30:39.320 --> 00:30:41.500 That's talkpython.fm/citizens. 00:30:41.500 --> 00:30:43.660 The link is in your podcast player's show notes. 00:30:43.660 --> 00:30:50.580 Or just follow Data Citizens Dialogues on Apple, Spotify, YouTube, or wherever you get your podcasts. 00:30:51.100 --> 00:30:54.600 Thank you to the Data Citizens Dialogues podcast for supporting the show. 00:30:54.600 --> 00:30:59.060 So you have some interesting data ingestion. 00:30:59.060 --> 00:31:01.000 This is a good thing, not a bad thing. 00:31:01.000 --> 00:31:04.280 Data ingestion options. 00:31:04.280 --> 00:31:06.760 You have a, it feels a little bit Pandas-like. 00:31:06.760 --> 00:31:09.960 You have a read CSV, read Parquet, read JSON. 00:31:09.960 --> 00:31:15.720 And then it's just, as you sort of hinted there, it's like part of the database now and you can ask it questions. 00:31:16.260 --> 00:31:21.820 Yes, so you could absolutely do that just with the read CSV and it'll import there. 00:31:21.820 --> 00:31:25.820 You can also even just read it without importing. 00:31:25.820 --> 00:31:34.540 So for example, when we're running that read Parquet, it's lazily evaluated, much like Polars or some of the other more modern data frame libraries are, 00:31:34.540 --> 00:31:42.740 where it's building up this relation object that says, once you look at the results, start by reading the Parquet file and then process the next few steps. 00:31:42.740 --> 00:31:47.560 So you could do something like read Parquet, filter to these things, aggregate up on these columns. 00:31:47.560 --> 00:31:50.600 And it will actually do all those operations together. 00:31:50.600 --> 00:31:54.320 It'll optimize them to do them in the right order to pull the least amount of data. 00:31:54.320 --> 00:31:57.900 And then only at that point will it, you know, materialize what it needs to. 00:31:57.900 --> 00:32:03.980 So the examples that you're looking at on screen are, you know, DuckDB.SQL select star from a Parquet file. 00:32:04.440 --> 00:32:08.720 You don't ever even have to read that part, you know, write that Parquet file into DuckDB format. 00:32:08.720 --> 00:32:13.960 It's just going to read it and return the results to you straight as a pass through, as an engine. 00:32:13.960 --> 00:32:15.480 Oh, that's really cool. 00:32:15.480 --> 00:32:21.020 So if it were a million lines of that, I could just iteratively pull through it. 00:32:21.020 --> 00:32:21.460 Yes. 00:32:21.460 --> 00:32:26.420 It'll just chunk through it at nice low memory usage and just, you know, pass those results out to you. 00:32:26.420 --> 00:32:35.500 And then if you process them also in batches, you can keep your memory, you're super low and take advantage of DuckDB being a streaming engine and not bring it on to memory. 00:32:35.500 --> 00:32:35.920 Right. 00:32:35.920 --> 00:32:41.360 Well, let's say I want to read a CSV file, but then put it into a persistent table in DuckDB. 00:32:41.360 --> 00:32:42.720 What do I do for this? 00:32:42.720 --> 00:32:43.480 Yes. 00:32:43.480 --> 00:32:51.560 You could say create table as my improved CSV table as select star from path to my CSV file. 00:32:51.560 --> 00:32:53.460 Will it copy over the schema? 00:32:53.460 --> 00:32:59.380 It will infer the schema as best as it possibly can, and it will automatically define the schema for you. 00:32:59.380 --> 00:32:59.820 Nice. 00:32:59.820 --> 00:33:02.020 What's the nullability story in your schema? 00:33:02.020 --> 00:33:03.460 I think... 00:33:03.460 --> 00:33:07.060 Do you support nullability or are there things that cannot be null? 00:33:07.060 --> 00:33:07.680 Yes. 00:33:07.680 --> 00:33:12.520 It's more traditional in the database in that everything can have a null value in the column. 00:33:12.520 --> 00:33:19.620 I think in Pandas, that was a little bit complicated by the fact that they were using NumPy in some cases, but extending beyond NumPy in others. 00:33:20.060 --> 00:33:29.400 But in DuckDB, it's more of a traditional database story in that you can have a null value wherever you choose unless you define a table to say this column doesn't allow nulls. 00:33:29.400 --> 00:33:29.820 Right. 00:33:29.820 --> 00:33:30.660 You can do that if you want. 00:33:30.660 --> 00:33:31.020 Yeah. 00:33:31.020 --> 00:33:33.440 I remember that was one of the things that caught me out with SQLite. 00:33:33.440 --> 00:33:35.160 I think it doesn't support... 00:33:35.160 --> 00:33:36.120 I can't remember which way it went. 00:33:36.120 --> 00:33:37.040 It's been a while. 00:33:37.040 --> 00:33:40.240 It doesn't support non-nullable columns. 00:33:40.240 --> 00:33:42.780 Like, everything is nullable or something like that. 00:33:42.780 --> 00:33:47.180 And it was like, wait, why is this not failing when I put in this wrong data or something along those lines? 00:33:47.180 --> 00:33:51.240 You know, SQLite is incredibly flexible and there's a lot of benefits to it. 00:33:51.240 --> 00:33:56.140 At times, that can mean that things pop up later in the lifecycle than at the beginning, like you said. 00:33:56.140 --> 00:34:03.340 So with SQLite, you can put a number in a string column and a string column and a number column, you know, and it'll mostly work until it doesn't work. 00:34:04.040 --> 00:34:11.760 DuckDB will be much more explicit about that, but it offers a lot of tools where you can auto-convert or it'll auto-convert for you. 00:34:11.760 --> 00:34:13.680 So you kind of get the benefits without the cost. 00:34:13.680 --> 00:34:21.480 And it actually has what's called a union type where you can actually have one column that can have strings and numbers in it if you know that you're going to have a little bit of both. 00:34:21.480 --> 00:34:24.080 So really, best of all worlds, I'd say. 00:34:24.080 --> 00:34:27.900 Yeah, I think that's more common in the data science, data lake space. 00:34:27.900 --> 00:34:31.400 You've got a bunch of data, you pull it in, and then you're going to clean it up and work on it. 00:34:31.400 --> 00:34:37.920 Whereas in maybe a relational database powering a web API, like, nope, users always have an email. 00:34:37.920 --> 00:34:39.000 That's just how it is. 00:34:39.000 --> 00:34:39.860 Right. 00:34:39.860 --> 00:34:40.460 Yes. 00:34:40.460 --> 00:34:41.260 Okay. 00:34:41.260 --> 00:34:46.120 So I'm looking at some code on your documentation here about getting started with Python for pandas. 00:34:46.120 --> 00:34:48.540 And I need some help. 00:34:48.540 --> 00:34:49.880 I need some help. 00:34:49.880 --> 00:34:52.560 It says import DuckDB, import pandas as pd. 00:34:52.560 --> 00:34:53.300 This is all normal. 00:34:53.300 --> 00:34:57.060 And then it says pandas df, it's a variable, equals pd.dataframes. 00:34:57.060 --> 00:34:57.580 So it creates one. 00:34:57.580 --> 00:34:59.040 And here's where I need the help. 00:34:59.040 --> 00:35:05.840 The next line is duckdb.sql, select star, in the quotes, pandas df. 00:35:05.840 --> 00:35:08.020 How do those two things connect? 00:35:08.020 --> 00:35:09.640 That's a great question. 00:35:09.640 --> 00:35:14.700 Because you never, like, take the actual object of the data frame and show it to DuckDB in any way. 00:35:14.700 --> 00:35:17.140 Like, you don't pass it to a function or set a property or nothing. 00:35:17.800 --> 00:35:18.220 Exactly. 00:35:18.220 --> 00:35:20.940 And this is the benefit of being an in-process database. 00:35:20.940 --> 00:35:28.300 We are in the same memory space as Python, which means we can actually read the Python locals for its local variables. 00:35:28.300 --> 00:35:34.020 And DuckDB has been built from the very beginning with this concept of interchangeability of storage format. 00:35:34.020 --> 00:35:39.580 That's why you can read parquet files or read CSV files, because we treat them as first-class citizens in our database. 00:35:40.020 --> 00:35:41.180 And data frames are no different. 00:35:41.180 --> 00:35:45.180 What we'll do first when we see a select statement come in and it's got a table in there. 00:35:45.180 --> 00:35:47.500 In this case, it's pandas df is the name of the table. 00:35:47.500 --> 00:35:50.500 First thing we'll do is, hey, do we have a physical DuckDB table like that? 00:35:50.500 --> 00:35:51.880 Is there a table of that name? 00:35:51.880 --> 00:35:54.040 And if we don't find one, we don't give up. 00:35:54.040 --> 00:35:54.820 We keep looking. 00:35:54.820 --> 00:35:58.420 We say, okay, is there another object we know how to read with that name? 00:35:58.620 --> 00:36:00.580 And so we look in Python's local variables. 00:36:00.580 --> 00:36:02.700 There's a variable named pandas df. 00:36:02.700 --> 00:36:04.120 And we check the class name. 00:36:04.120 --> 00:36:05.120 What class is this? 00:36:05.120 --> 00:36:06.180 Is it pandas? 00:36:06.180 --> 00:36:06.840 Is it polars? 00:36:06.840 --> 00:36:07.620 Is it Apache arrow? 00:36:07.620 --> 00:36:13.340 And if it's one of those, we can treat it just like a table and then run SQL right on top of it. 00:36:13.340 --> 00:36:14.040 Pretty wild. 00:36:14.040 --> 00:36:15.360 It's pretty wild. 00:36:15.360 --> 00:36:30.000 I think one of the real values there is that you don't have to do, presumably, I'm presuming, you don't have to do a data conversion from pandas into whatever in-memory thing DuckDB thinks is ideal and then back. 00:36:30.000 --> 00:36:31.160 That's a great question. 00:36:31.160 --> 00:36:33.140 So you definitely don't have to do it all at once. 00:36:33.140 --> 00:36:37.840 So pandas, sometimes it is Python objects under the hood, whereas DuckDB is written in C++. 00:36:37.840 --> 00:36:41.760 So it will change some things, but it'll do it in chunks like we're talking about. 00:36:41.760 --> 00:36:43.200 So it won't be all up front. 00:36:43.800 --> 00:36:51.580 But for things like polars and arrow, in most data types, it's zero copy where it's the same format. 00:36:51.580 --> 00:36:54.480 DuckDB has slightly different data formats. 00:36:54.480 --> 00:37:00.600 And Apache arrow is actually adjusted to align closer to DuckDB in some of their format, like how they store strings. 00:37:00.600 --> 00:37:04.700 So DuckDB has really been one of the leading databases in terms of how it handles strings. 00:37:04.700 --> 00:37:08.060 And Apache arrow is also making those changes. 00:37:08.060 --> 00:37:09.480 Yeah, that's super cool. 00:37:09.480 --> 00:37:11.840 You can't write back to these, can you? 00:37:11.840 --> 00:37:13.600 You can't say, wait, you can't. 00:37:13.600 --> 00:37:13.840 You can't. 00:37:13.840 --> 00:37:19.780 You can say update, like the data frame variable, PD data frame set, whatever. 00:37:19.780 --> 00:37:20.140 Yeah. 00:37:20.140 --> 00:37:24.840 So if you're updating the data frame, you will overwrite the whole data frame. 00:37:24.840 --> 00:37:29.700 But DuckDB table, you can run updates on that object for sure. 00:37:29.700 --> 00:37:30.100 Yeah. 00:37:30.200 --> 00:37:33.120 But you can write data frames for sure. 00:37:33.120 --> 00:37:34.360 So you can replace the whole thing. 00:37:34.360 --> 00:37:34.760 Right. 00:37:34.760 --> 00:37:39.820 But I couldn't change the 172nd row of that data frame with a SQL query. 00:37:39.820 --> 00:37:40.520 Or could I? 00:37:40.520 --> 00:37:44.320 You would just be making a copy of the object. 00:37:44.320 --> 00:37:44.960 Got it. 00:37:44.960 --> 00:37:45.180 Got it. 00:37:45.180 --> 00:37:46.140 Yeah, that's what I thought. 00:37:46.140 --> 00:37:50.140 You would make the changes, but then you would return as data frame and make a copy of it. 00:37:50.140 --> 00:37:50.600 Correct. 00:37:50.760 --> 00:37:52.640 We would return a different object in memory. 00:37:52.640 --> 00:37:54.280 But functionally, it would be the same. 00:37:54.280 --> 00:37:57.420 And DuckDB is fast enough where it would probably be quite performant still. 00:37:57.420 --> 00:37:58.340 Yeah, probably fine. 00:37:58.340 --> 00:37:58.560 Yeah. 00:37:58.560 --> 00:37:59.000 Okay. 00:37:59.000 --> 00:38:00.720 I'm not saying that it should be able to do. 00:38:00.720 --> 00:38:02.660 I'm just trying to figure out how far down this. 00:38:02.660 --> 00:38:03.620 How far down? 00:38:03.620 --> 00:38:03.940 Yeah. 00:38:03.940 --> 00:38:04.280 Yeah. 00:38:05.640 --> 00:38:07.040 How far down does it go? 00:38:07.040 --> 00:38:07.520 Yeah. 00:38:07.520 --> 00:38:13.140 So it does the same kind of magic with the locals, with Pyro and Polars and so on. 00:38:13.140 --> 00:38:13.560 Mm-hmm. 00:38:13.560 --> 00:38:16.780 So there's different ways to get data back. 00:38:16.780 --> 00:38:21.780 And it doesn't really matter where it came from, if it came from Pandas or DuckDB files itself, 00:38:21.780 --> 00:38:23.400 or even just hard-coded SQL. 00:38:23.400 --> 00:38:29.340 But you can say things like fetch all, which will return you Python objects, or .df will return 00:38:29.340 --> 00:38:31.780 you a Pandas data frame or different things, right? 00:38:31.780 --> 00:38:32.760 Yes. 00:38:32.840 --> 00:38:35.200 Yep, we've got .pl for polars, .aero. 00:38:35.200 --> 00:38:37.480 You could do NumPy arrays if you'd like. 00:38:37.480 --> 00:38:43.000 We also support outputting to TensorFlow tensors and PyTorch tensors. 00:38:43.000 --> 00:38:43.460 Wow. 00:38:43.460 --> 00:38:46.140 Because those are NumPy under the hood in a lot of ways. 00:38:46.140 --> 00:38:48.220 So we can output to that as well. 00:38:48.220 --> 00:38:48.700 Okay. 00:38:48.700 --> 00:38:49.520 That's super cool. 00:38:49.520 --> 00:38:53.600 Do I need to be worried about little Bobby tables, SQL injections? 00:38:53.600 --> 00:38:58.640 We could definitely pass in parameters that will get escaped properly and all that. 00:38:58.640 --> 00:39:02.600 You know, with DuckDB, I think that in general, we take the approach of being 00:39:02.600 --> 00:39:06.140 super flexible, but there's a lot of options to lock it down should you choose. 00:39:06.140 --> 00:39:12.240 So the select star from any CSV file on my hard drive, you could turn that off if you want. 00:39:12.240 --> 00:39:16.240 For example, likewise with the data frames, you can disable that also. 00:39:16.240 --> 00:39:17.240 Yeah. 00:39:17.440 --> 00:39:22.520 I guess what I'm asking more, is there a concept of a parameterized query or something like that? 00:39:22.520 --> 00:39:23.160 Yes. 00:39:23.160 --> 00:39:23.500 Yes. 00:39:23.500 --> 00:39:27.800 So you can follow the same Python DB 2.0 kind of API spec. 00:39:27.800 --> 00:39:33.620 You could do .execute and pass in a query with placeholder parameters and then pass in those as arguments. 00:39:33.620 --> 00:39:34.980 So you can absolutely do that. 00:39:34.980 --> 00:39:38.340 It tends to be not good for bulk ingestion. 00:39:38.660 --> 00:39:40.720 The reason for that is Python objects just have a lot more. 00:39:40.720 --> 00:39:43.560 Oh, well, I lost your audio. 00:39:43.560 --> 00:39:46.420 Did you possibly hit something that hit a mute button? 00:39:46.420 --> 00:39:47.300 I did. 00:39:47.300 --> 00:39:47.720 Sorry. 00:39:47.720 --> 00:39:48.220 Okay. 00:39:48.220 --> 00:39:48.640 Sorry. 00:39:48.640 --> 00:39:50.300 Give us the... 00:39:50.300 --> 00:39:52.800 You said it's not good for bulk ingestion. 00:39:52.800 --> 00:39:54.500 Let's go from there. 00:39:54.500 --> 00:39:55.140 Yep. 00:39:55.140 --> 00:39:55.520 Sorry. 00:39:55.520 --> 00:39:56.980 Oh, all good. 00:39:56.980 --> 00:39:57.560 That's on me. 00:39:57.560 --> 00:40:02.160 So you can insert straight from Python objects with parameters. 00:40:02.160 --> 00:40:03.820 It works really great. 00:40:03.820 --> 00:40:06.120 It's very convenient, but it's not great for bulk ingestion. 00:40:06.120 --> 00:40:14.440 Inserting a million rows that way versus a million rows in a Pandas data frame, it's going to be a couple orders of magnitude faster to do it through a data frame. 00:40:14.440 --> 00:40:14.880 Yeah. 00:40:14.880 --> 00:40:15.640 Yeah, absolutely. 00:40:15.640 --> 00:40:25.080 So if by default, if I just do WDB.sql, this is working in this correctly this time, in memory database. 00:40:25.080 --> 00:40:31.960 But if you create a connection to a file, then that where you just call it whatever you want, right? 00:40:31.960 --> 00:40:33.540 Like in your examples, you got file.db. 00:40:33.540 --> 00:40:37.800 That will then start persisting to that file, right? 00:40:37.800 --> 00:40:39.880 But it's pretty simple to do either. 00:40:39.880 --> 00:40:40.780 Yes. 00:40:40.780 --> 00:40:42.660 You're just changing the connection path. 00:40:42.660 --> 00:40:46.440 And if you pass in no path, it'll be in memory. 00:40:46.440 --> 00:40:47.920 Otherwise, you can point to a file. 00:40:47.920 --> 00:40:50.480 And it's really that straightforward. 00:40:50.480 --> 00:40:54.760 It'll save all your tables into that same file, nice and compressed in columnar format. 00:40:54.760 --> 00:40:57.280 And then now the format is all stabilized. 00:40:57.280 --> 00:41:01.960 You can throw that on an object store, share it with anybody, put it in your data lake, anywhere you want it to go. 00:41:01.960 --> 00:41:04.600 Can I read from objects or can I give it an S3 connection? 00:41:04.600 --> 00:41:05.540 Yes, indeed. 00:41:05.540 --> 00:41:07.640 You can read from any of the major clouds. 00:41:08.260 --> 00:41:15.300 So AWS, Google, Azure, you can read individual files or actually it'll understand hierarchies of files. 00:41:15.300 --> 00:41:24.260 So if you have like a top level folder for a year and a folder for month and day, if you filter for I only want three days worth of data, DuckDB will only read three files. 00:41:24.260 --> 00:41:25.040 So it's pretty slick. 00:41:25.040 --> 00:41:25.260 Oh, wow. 00:41:25.260 --> 00:41:25.680 That's wild. 00:41:25.680 --> 00:41:26.200 Okay. 00:41:26.560 --> 00:41:27.520 Yeah, that is really slick. 00:41:27.520 --> 00:41:29.420 And you can even write out to those as well. 00:41:29.420 --> 00:41:31.780 So you can actually write on object stores too. 00:41:31.780 --> 00:41:33.200 That's amazing, actually. 00:41:33.200 --> 00:41:40.240 And a lot of less hyperscale clouds, I don't know, I was almost calling them lesser clouds, but I think of almost as like even better clouds. 00:41:40.240 --> 00:41:47.560 You know, DigitalOcean, Hetzner, Leno, some of these smaller places, a lot of them have their own object store. 00:41:47.560 --> 00:41:54.580 And the object store API is just a different connection string for the S3 API, right? 00:41:54.580 --> 00:41:57.520 So it sounds like you could probably get that to work as well. 00:41:57.520 --> 00:41:58.260 Let's pass that. 00:41:58.260 --> 00:42:01.760 I believe I've seen MinIO used, which is even like self-hosted if you want. 00:42:01.760 --> 00:42:04.600 So if it handles MinIO, you can kind of put it wherever you want it. 00:42:04.600 --> 00:42:05.620 Yeah, yeah, that's for sure. 00:42:05.620 --> 00:42:06.920 MinIO is awesome. 00:42:06.920 --> 00:42:16.200 I haven't got a chance to put MinIO into any useful work, but it's like a self-hosted S3 basically, right? 00:42:16.200 --> 00:42:16.840 Which is really cool. 00:42:16.840 --> 00:42:17.080 Yes. 00:42:17.080 --> 00:42:17.600 Yeah. 00:42:17.600 --> 00:42:18.540 So I don't know. 00:42:18.540 --> 00:42:20.620 It seems like a really neat system. 00:42:20.620 --> 00:42:22.700 But it's also, MinIO is complicated. 00:42:22.700 --> 00:42:26.500 You know, it's not easy to set up and run. 00:42:26.500 --> 00:42:28.460 It seems like there's a lot going on there. 00:42:28.460 --> 00:42:30.000 So that's why I haven't done it yet. 00:42:30.000 --> 00:42:30.980 I'm like, this looks amazing. 00:42:30.980 --> 00:42:32.000 Oh, this looks complicated. 00:42:32.000 --> 00:42:33.180 What else am I going to do today? 00:42:33.180 --> 00:42:36.640 I think S3 is pretty great. 00:42:36.640 --> 00:42:38.360 Drop a file, read a file. 00:42:38.360 --> 00:42:41.160 So it's not a bad starting point for quite a while. 00:42:41.160 --> 00:42:41.860 Yeah, absolutely. 00:42:41.860 --> 00:42:46.240 So with this connection object, like if you want to have a persistent database, right? 00:42:46.240 --> 00:42:52.120 You create a connection object and then it has basically the same API as the DuckDB itself. 00:42:52.120 --> 00:42:53.320 So you can run SQL queries. 00:42:53.320 --> 00:42:54.880 You can do table stuff on it and so on. 00:42:54.880 --> 00:42:58.060 What's the concurrency story with this thing? 00:42:58.460 --> 00:43:10.360 Like, so for example, what if I open, what if I try to use DuckDB in Flask and I have Flask running in Gunicorn and I say, Gunicorn, please use four worker processes. 00:43:10.360 --> 00:43:11.620 And I try to talk to it. 00:43:11.620 --> 00:43:12.460 It's a great question. 00:43:12.460 --> 00:43:15.000 So am I going to be having a bad time or having a good time? 00:43:15.880 --> 00:43:16.880 It depends a little bit. 00:43:16.880 --> 00:43:16.880 It depends. 00:43:16.880 --> 00:43:17.000 It depends a little bit. 00:43:17.000 --> 00:43:19.480 So, but yes, it's, there's some trade-offs. 00:43:19.480 --> 00:43:24.080 There's reasons that there are databases that are in process and that there are databases that are client server. 00:43:24.460 --> 00:43:30.520 And so whenever you want to write data to DuckDB, you can only write data from within a single process. 00:43:30.520 --> 00:43:36.440 And you'll open up a read-write connection and it will actually disallow any access from any other process. 00:43:36.440 --> 00:43:39.860 And it'll be single process access while you're doing reads and writes. 00:43:39.860 --> 00:43:42.100 It does handle multi-threading just fine. 00:43:42.100 --> 00:43:43.860 So you can have multiple Python threads. 00:43:43.860 --> 00:43:46.100 You get nice parallel performance that way. 00:43:46.100 --> 00:43:46.900 No problem. 00:43:46.900 --> 00:43:49.620 But it's all within the same memory space because you're using threads. 00:43:49.620 --> 00:43:52.660 So we're pretty excited about free threading coming soon in Python. 00:43:52.660 --> 00:43:52.980 Yes. 00:43:52.980 --> 00:43:53.640 Yeah. 00:43:53.640 --> 00:43:59.680 Well, that's going to knock out the need for the web, the web garden, not the web farm, the web garden, 00:43:59.680 --> 00:44:07.720 where you've got a bunch of these copies of the web server running in parallel because they're all trying to have separate gills so they can be more concurrent. 00:44:07.720 --> 00:44:10.520 That's not the only reason, but that's a big reason in Python. 00:44:10.520 --> 00:44:11.660 That's definitely a big reason. 00:44:11.660 --> 00:44:12.920 So that we're excited about. 00:44:12.920 --> 00:44:19.180 If you want to be read-only, so since DuckDB is an analytical database and since we can read from files, 00:44:19.180 --> 00:44:24.600 there are a lot of use cases that are read-only where if I'm reading from parquet files on an object store, 00:44:24.600 --> 00:44:28.980 I might not be doing any write operations at all, at which point multi-process, 00:44:29.340 --> 00:44:33.980 access is totally fine as long as you're in read-only mode for all of those processes. 00:44:33.980 --> 00:44:39.440 I will add one big other thing to look into, though, is where I work, which is Mother Duck. 00:44:39.440 --> 00:44:45.060 So we're a cloud serverless data warehouse built around DuckDB and we'll handle the concurrency side. 00:44:45.060 --> 00:44:53.040 So it's really taking this amazing single-player DuckDB experience and bringing it into the cloud as a full multiplayer cloud data warehouse experience. 00:44:53.180 --> 00:45:03.340 So you get concurrency, you get automatic managed storage, it's very efficient, and you also get access control and some of the things you associate with a large-scale database. 00:45:03.820 --> 00:45:09.420 That's the story. If you need concurrency but you're using DuckDB, I think Mother Duck is a great option. 00:45:09.420 --> 00:45:13.260 Right. So this is kind of DuckDB as a service. Is that right? 00:45:13.260 --> 00:45:21.520 That's definitely a key piece of it. I think we have a... DuckDB is a huge part of what we do and it is the engine under the hood. 00:45:21.520 --> 00:45:33.720 I think we are using that to really be meeting the use case of being not just sort of a cloud hosting for DuckDB, it's to really be a full data warehouse with DuckDB as the engine. 00:45:33.720 --> 00:45:41.620 So a little bit of a distinction there, but just as a whole organization could kind of use it as opposed to just one person, you know, cloud hosting it, something like that. 00:45:41.880 --> 00:45:54.140 Yeah. Well, it seems like a really, really great service. And I guess it's worth calling out that DuckDB, the thing that runs in your process, is available on as an MIT licensed thing, right? Which is, that's pretty amazing. 00:45:54.140 --> 00:46:02.500 And you, I saw somewhere, I can't remember where I saw it, but it's something like, you'll promise to be MIT, to stay MIT. No rug pulling, the rugs will stay. 00:46:02.880 --> 00:46:12.360 Yes. So it's a pretty cool kind of corporate structure. And if you can say a cool corporate structure, we'll see if we can get away with that. But as far as corporate structures go, it's pretty cool, right? 00:46:12.360 --> 00:46:22.140 So there's actually three organizations at play. And one of them is a nonprofit foundation called the DuckDB Foundation. And they actually own the intellectual property for DuckDB. 00:46:22.140 --> 00:46:29.500 And that is what ensures that it'll be MIT licensed forever, because it is not a company that owns DuckDB, which is amazing. 00:46:29.700 --> 00:46:37.080 So there is like, it's not even possible to do a rug pull, even if somebody wanted, which nobody does, right? It's not even possible. It's against the law, which is pretty cool. 00:46:37.080 --> 00:46:38.140 So yeah. 00:46:38.140 --> 00:46:43.320 I guess maybe give people, I threw that out there. There might be people who don't know what that means. 00:46:43.320 --> 00:46:44.560 Oh, what a rug pull is? 00:46:44.560 --> 00:46:46.260 Yes, exactly. Yeah. What is that? 00:46:46.260 --> 00:46:56.320 You know, certain products, once they're open source, they begin to, they get funded in certain ways to where they need to be, you know, selling it as a product. 00:46:56.640 --> 00:47:04.020 And at times that means that either they don't always put everything back in open source, or there's different licensing models that get changed, 00:47:04.020 --> 00:47:07.140 so that the license is a little bit more restrictive than it was in the past. 00:47:07.140 --> 00:47:14.080 Kind of the typical maneuver there for a couple different companies has been around Amazon will just host your product. 00:47:14.240 --> 00:47:18.960 And then it's very hard to have your own business if Amazon just hosts it. 00:47:18.960 --> 00:47:24.000 So we're not in that situation with DuckDB because it's open source forever. 00:47:24.000 --> 00:47:27.380 But also Mother Duck is not just wrapping DuckDB. 00:47:27.620 --> 00:47:36.180 We're innovating at the, at that service layer, where with Mother Duck, one of its secret sauce ingredients is it's not just a cloud service. 00:47:36.180 --> 00:47:40.100 When you connect to Mother Duck, you have DuckDB running locally and in the cloud. 00:47:40.100 --> 00:47:45.340 And one query, one Python statement can actually run partially in the cloud and partially on your laptop. 00:47:45.340 --> 00:47:46.340 Oh, wow. Okay. 00:47:46.340 --> 00:47:51.100 And we actually can, we can choose the optimal location for that to run based on your query plan. 00:47:51.100 --> 00:47:55.320 We actually do that as a part of the optimization to say, where's the most efficient place for this to run? 00:47:55.320 --> 00:48:01.840 And that's some serious secret sauce that, you know, it's not just, just wrapping, you know, an open source solution and hosting it. 00:48:02.140 --> 00:48:05.820 That gives some really nice benefits for things like the local development experience. 00:48:05.820 --> 00:48:10.980 You can develop locally at light speed and then push to the cloud at the final step. 00:48:10.980 --> 00:48:13.280 Or alternatively, kind of reverse the polarity. 00:48:13.280 --> 00:48:19.000 Maybe your first step is to bring everything down locally in a cache and then do all kinds of data science processing on it. 00:48:19.000 --> 00:48:23.160 So it's, it's very convenient to have both local and cloud. 00:48:23.160 --> 00:48:23.780 I see. 00:48:23.780 --> 00:48:29.300 Kind of like we talked about writing a query and then turn it into a data science object and then asking questions. 00:48:29.300 --> 00:48:33.920 This is a little bit like distributed versus local, but staying within DuckDB. 00:48:33.920 --> 00:48:38.380 It's definitely, you get to use server side computer, local compute, however you'd like. 00:48:38.380 --> 00:48:39.660 Yeah. 00:48:39.660 --> 00:48:40.860 Super cool. 00:48:40.860 --> 00:48:42.000 I love the idea of it. 00:48:42.000 --> 00:48:42.740 It's a lot of fun. 00:48:42.740 --> 00:48:47.000 We're, we're, we're doing a lot of fun things and we, we, you gotta have fun with it as well. 00:48:47.000 --> 00:48:49.460 You know, what do you call someone who works at mother duck? 00:48:49.460 --> 00:48:50.780 A duckling. 00:48:50.780 --> 00:48:53.240 That's the second most popular one. 00:48:53.240 --> 00:48:55.220 We actually were mother duckers, Michael. 00:48:55.220 --> 00:48:56.820 Oh, okay. 00:48:56.820 --> 00:48:57.580 Mother duckers. 00:48:57.580 --> 00:48:57.920 All right. 00:48:57.920 --> 00:48:59.280 You gotta have fun with it. 00:48:59.280 --> 00:48:59.400 Right. 00:48:59.400 --> 00:49:01.320 Otherwise it's all just ones and zeros at the end of the day. 00:49:01.320 --> 00:49:01.560 Right. 00:49:01.560 --> 00:49:02.760 You have to have fun with it. 00:49:02.760 --> 00:49:03.040 Yeah. 00:49:03.040 --> 00:49:04.080 You have to have fun with it. 00:49:04.080 --> 00:49:04.780 You should. 00:49:04.780 --> 00:49:05.400 I don't know. 00:49:05.400 --> 00:49:05.960 Ducklings. 00:49:05.960 --> 00:49:07.040 But mother duckers is good. 00:49:07.040 --> 00:49:07.360 Still. 00:49:07.360 --> 00:49:09.620 I guess ducklings might send the wrong message. 00:49:09.620 --> 00:49:10.480 They're all good. 00:49:10.480 --> 00:49:17.080 Do you want to, do you want to like sort of a tough, tough persona or facade, or do you want 00:49:17.080 --> 00:49:19.120 to like a, you know, coddled, whatever? 00:49:19.120 --> 00:49:20.320 No, it's a great name. 00:49:21.740 --> 00:49:25.780 It's, you know, like if you look at the branding on our website, you know, we're not going for 00:49:25.780 --> 00:49:29.140 like, we're not like a super muscly duck, you know, at our level ranges. 00:49:29.140 --> 00:49:30.620 We're definitely in the playful side. 00:49:30.620 --> 00:49:36.860 And part of it is because we really think that back in the big data era, we believe the big 00:49:36.860 --> 00:49:38.620 data era is over here at Mother Duck. 00:49:38.780 --> 00:49:43.040 And the reason for that is you no longer need 50 servers to answer your questions. 00:49:43.040 --> 00:49:45.920 It turns out laptops are pretty daggone fast now. 00:49:45.920 --> 00:49:46.300 Yeah. 00:49:46.300 --> 00:49:47.580 And you can do a lot. 00:49:47.580 --> 00:49:52.100 Even, even if you're not able to handle it on a laptop, how about one giant node on AWS? 00:49:52.100 --> 00:49:57.120 You can rent a node with multiple terabytes of RAM, of RAM. 00:49:57.120 --> 00:49:58.960 I mean, you could do a lot, right? 00:49:58.960 --> 00:50:01.700 So at that point, what is a single node? 00:50:01.700 --> 00:50:04.240 You can kind of do, do almost anything that you need. 00:50:04.240 --> 00:50:05.780 So as a result. 00:50:05.780 --> 00:50:08.760 And single server versus distributed is way easier. 00:50:09.140 --> 00:50:09.400 Yes. 00:50:09.400 --> 00:50:10.980 It allows you to innovate a lot faster. 00:50:10.980 --> 00:50:12.300 You can use better algorithms. 00:50:12.300 --> 00:50:15.880 So in many cases, it's actually just plain old faster to do it that way. 00:50:15.880 --> 00:50:17.540 A lot of benefits. 00:50:17.540 --> 00:50:21.620 But what we realized fundamentally at Mother Duck is, in a lot of ways, DuckDB as well. 00:50:21.620 --> 00:50:24.700 The scale of data shouldn't be the most important thing anymore. 00:50:24.700 --> 00:50:26.780 It should be how easy it is to use. 00:50:26.780 --> 00:50:29.500 You should be able to do select star from a CSV. 00:50:29.500 --> 00:50:33.740 You know, it doesn't take scale to parse a CSV file in an intelligent way. 00:50:33.740 --> 00:50:34.980 It takes elbow grease. 00:50:34.980 --> 00:50:37.360 It takes innovation in terms of the algorithm. 00:50:37.620 --> 00:50:40.740 But at the end of the day, it's not about how big is your CSV file. 00:50:40.740 --> 00:50:42.640 It's about how long did it take me to get my job done. 00:50:42.640 --> 00:50:45.260 And in Mother Duck, it's a similar kind of pragmatism. 00:50:45.260 --> 00:50:47.800 And our branding, I think, does a really good job of showing that. 00:50:47.800 --> 00:50:48.560 I think so. 00:50:48.560 --> 00:50:53.540 Well, I asked you about half of what I said I was looking forward to asking about, which 00:50:53.540 --> 00:50:55.560 was the table schemas, how to do that. 00:50:55.560 --> 00:50:57.660 However, what about indexes? 00:50:57.660 --> 00:50:58.320 Sure. 00:50:58.520 --> 00:51:00.380 I think indexes are a great question. 00:51:00.380 --> 00:51:02.840 So DuckDB does support indexes. 00:51:02.840 --> 00:51:06.060 But in many cases, it's not necessarily something that you need. 00:51:06.060 --> 00:51:11.460 And the reason for that is because of the workloads that it's best for, but also because of columnar 00:51:11.460 --> 00:51:11.940 databases. 00:51:11.940 --> 00:51:18.900 So when you have a columnar database, by default, it's going to create some rough indexes for you 00:51:18.900 --> 00:51:19.560 automatically. 00:51:19.960 --> 00:51:25.820 So every about 100,000 rows in DuckDB, we create an index there that says, what's the minimum value 00:51:25.820 --> 00:51:28.260 in this chunk and then the maximum value in this chunk. 00:51:28.260 --> 00:51:32.880 And what we'll do is then if you run a query that says select data from this week, we're going to check, 00:51:32.880 --> 00:51:36.100 hey, which blocks have any data from this week? 00:51:36.100 --> 00:51:41.860 And we can skip 90% of your data because we know that it doesn't match that filter. 00:51:41.860 --> 00:51:44.480 But that's not like an individual row index. 00:51:44.480 --> 00:51:46.960 It's an index for every 100,000 rows. 00:51:47.300 --> 00:51:51.180 And it's for the analytical queries where you're looking at trends, where you're analyzing 00:51:51.180 --> 00:51:54.880 large amounts of data, it tends to be really the optimal way to go about it. 00:51:54.880 --> 00:51:56.700 And that's the way most systems do it. 00:51:56.700 --> 00:51:58.860 There is an external index you can add. 00:51:58.860 --> 00:52:04.700 It's an art index, an adaptive radix tri index, which is a pretty cool way of doing it. 00:52:04.700 --> 00:52:09.340 It's a little different than the B tree in SQLite, but it allows for those vast point lookups 00:52:09.340 --> 00:52:13.840 where if you want to grab one row, you can get a very quick mapping to that individual row. 00:52:14.360 --> 00:52:19.140 It tends to be just less necessary in DuckDB to do that, but we have it if you need it. 00:52:19.140 --> 00:52:25.120 I'm realizing that I need to learn more about databases, especially columnar ones, because 00:52:25.120 --> 00:52:29.860 they're not matching my mental model as much as, say, for relational or document ones. 00:52:29.860 --> 00:52:32.240 It's definitely a different world. 00:52:32.240 --> 00:52:32.940 Different world. 00:52:32.940 --> 00:52:38.940 And that's, I think, the fun of it is I think of databases as an infinitely deep rabbit hole 00:52:38.940 --> 00:52:43.260 of really just fun optimizations, fun things to learn about. 00:52:43.260 --> 00:52:44.340 So a lot of exciting stuff. 00:52:44.700 --> 00:52:44.820 Yeah. 00:52:44.820 --> 00:52:46.400 When you get that stuff right, it's like magic. 00:52:46.400 --> 00:52:49.920 It's like, how could it possibly answer that so quickly? 00:52:49.920 --> 00:52:52.200 At the same time, when it's wrong, it's really frustrating. 00:52:52.200 --> 00:52:57.380 You know, you go to a website and you don't say, oh, you know, click this thing here. 00:52:57.380 --> 00:52:59.820 And then you try to, and it just sits there and spins. 00:52:59.820 --> 00:53:01.560 Like, I know this is happening without an index. 00:53:01.560 --> 00:53:03.980 And I know this could be so easily fixed. 00:53:03.980 --> 00:53:04.860 And it's just not. 00:53:04.860 --> 00:53:06.280 Why, why is it so bad? 00:53:06.280 --> 00:53:08.240 But, you know, you can make your stuff fast. 00:53:08.240 --> 00:53:09.580 You can't make other people's stuff fast. 00:53:09.640 --> 00:53:10.920 So I guess it's an advantage. 00:53:10.920 --> 00:53:11.660 Makes you look good. 00:53:11.660 --> 00:53:12.160 Yes. 00:53:12.160 --> 00:53:18.020 So I think that the workloads are just so different, but the mental model, it does open doors in 00:53:18.020 --> 00:53:18.460 a lot of ways. 00:53:18.460 --> 00:53:23.640 So for example, if you want to get the average of like a billion rows at DuckDB, it's a couple 00:53:23.640 --> 00:53:24.080 seconds. 00:53:24.080 --> 00:53:24.640 Wow. 00:53:24.640 --> 00:53:25.600 It's that fast. 00:53:25.600 --> 00:53:31.560 You know, so just the things that you can do with that kind of tool, an analytical oriented 00:53:31.560 --> 00:53:34.760 tool, really unlocks things you can do in an application sense. 00:53:34.760 --> 00:53:40.660 So data apps, we think are a really interesting market for both DuckDB and MotherDuck, where, 00:53:40.660 --> 00:53:43.840 you know, it's not just show me a little bit of data. 00:53:43.840 --> 00:53:46.100 Maybe it's an event tracker, like a fitness tracker. 00:53:46.100 --> 00:53:50.420 You know, I want to see the history of my bike rides for the last month. 00:53:50.420 --> 00:53:54.720 And I want to see the trends, my moving averages, you know, highlight the outliers for me. 00:53:54.720 --> 00:54:00.040 You know, there's a lot of heavy analytical work to be done in data apps that an analytical 00:54:00.040 --> 00:54:00.940 database is really good for. 00:54:00.940 --> 00:54:01.380 Yeah. 00:54:01.380 --> 00:54:04.620 Give us some, I don't know how much you can share. 00:54:04.620 --> 00:54:06.540 I know you interact with these customers quite a bit. 00:54:06.540 --> 00:54:08.260 So, you know, but I don't know how much you can share. 00:54:08.260 --> 00:54:10.480 Give us some senses of what people are doing. 00:54:10.480 --> 00:54:11.180 Sure. 00:54:11.180 --> 00:54:13.180 On the MotherDuck side or the DuckDB side? 00:54:13.180 --> 00:54:17.480 Well, you know, give us a little example from each, maybe that you think is representative. 00:54:17.480 --> 00:54:18.280 You bet. 00:54:18.280 --> 00:54:24.940 I think on the DuckDB side, I think some of the very interesting things are when you use DuckDB 00:54:24.940 --> 00:54:30.700 as a data processing engine, where each individual processing task is, you know, one node, but you're 00:54:30.700 --> 00:54:31.880 doing a ton of them in parallel. 00:54:31.880 --> 00:54:36.720 So some companies will actually take DuckDB and use it to transform one Parquet file into 00:54:36.720 --> 00:54:38.480 an aggregated Parquet file. 00:54:38.480 --> 00:54:39.880 And they'll just set it up on a trigger. 00:54:39.880 --> 00:54:44.620 So when a Parquet file gets added, they'll automatically just create a little pipeline with DuckDB and 00:54:44.620 --> 00:54:47.660 convert it into a much, you know, cleaner, more aggregated form. 00:54:47.660 --> 00:54:49.380 And you could do that at huge scale. 00:54:49.620 --> 00:54:53.960 So there's some companies that are doing huge amounts of data processing, you know, with 00:54:53.960 --> 00:54:54.320 DuckDB. 00:54:54.320 --> 00:54:59.180 Tons of tiny DuckDBs, a flock of tiny DuckDBs running behind the scenes. 00:54:59.180 --> 00:55:04.140 So that's an interesting use case that, you know, DuckDB originated as a data science tool, 00:55:04.140 --> 00:55:09.160 but I think the data engineering side of DuckDB is also very exciting, where it fits really well 00:55:09.160 --> 00:55:09.800 in those pipelines. 00:55:10.220 --> 00:55:15.160 On the MotherDuck side, I think what we see is a lot of cases where the transactional database, 00:55:15.160 --> 00:55:18.000 you know, a lot of cases, it's Postgres. 00:55:18.000 --> 00:55:23.180 It's got a really friendly SQL syntax, and it aligns very closely with DuckDB's SQL syntax, 00:55:23.180 --> 00:55:28.820 where Postgres just is too slow for those kind of trend-oriented aggregates or join queries 00:55:28.820 --> 00:55:29.500 that you want to run. 00:55:30.000 --> 00:55:35.080 And MotherDuck ends up being a really great way to very easily get a lot more speed in those cases. 00:55:35.080 --> 00:55:37.660 And that could be speed for data engineering tasks. 00:55:37.660 --> 00:55:43.660 Like some folks are running data processing jobs with this tool called DBT, stands for Data Build 00:55:43.660 --> 00:55:46.260 Tool, and it's a Python library. 00:55:46.260 --> 00:55:51.340 And they were running these, you know, sets of computation and aggregations, and they would 00:55:51.340 --> 00:55:52.560 take, you know, eight hours. 00:55:52.560 --> 00:55:57.940 And if it starts running, you know, 2 a.m., by the time it errors out, you've lost your whole 00:55:57.940 --> 00:56:00.220 day of your data analysis being up to date. 00:56:00.220 --> 00:56:02.020 Yeah, you better get it right, right? 00:56:02.020 --> 00:56:03.140 Better get it right. 00:56:03.140 --> 00:56:07.600 Unfortunately, DBT does also stand for, you know, behavioral therapy of a certain kind. 00:56:07.600 --> 00:56:09.760 So you've got to search for DBT data. 00:56:09.760 --> 00:56:11.600 It's a tough acronym. 00:56:11.600 --> 00:56:13.340 Yeah, it's a little short. 00:56:13.340 --> 00:56:16.640 Yeah, DBT Python, that'll get you there too, yeah. 00:56:16.640 --> 00:56:21.760 With MotherDuck and with DuckDB, you can take that, and instead of being eight hours, you can 00:56:21.760 --> 00:56:23.540 run it in, you know, 15 or 30 minutes. 00:56:23.540 --> 00:56:29.680 And just that scale of change means that, you know, it's just a far more delightful experience 00:56:29.680 --> 00:56:33.600 to do those kind of data pipelines on an analytical tool as opposed to a transaction. 00:56:33.600 --> 00:56:34.460 So that's one. 00:56:34.460 --> 00:56:36.400 The other one is business intelligence tools. 00:56:36.400 --> 00:56:40.140 In a lot of cases, those start to get really slow on a transactional database because if 00:56:40.140 --> 00:56:43.100 you're looking at a graph, it's by most of the time, it's a trend. 00:56:43.100 --> 00:56:46.120 And most of the time, it's looking at a lot of data to plot. 00:56:46.120 --> 00:56:49.480 Even if it's just plotting a few points, it's looking at a lot of data to plot it. 00:56:49.480 --> 00:56:52.840 And DuckDB is really excellent for that, and MotherDuck as well. 00:56:52.840 --> 00:56:56.600 There's actually a lot of business intelligence tools where they are powered by DuckDB. 00:56:56.600 --> 00:56:59.280 So Mode was acquired by ThoughtSpot. 00:56:59.280 --> 00:57:00.480 They're powered by DuckDB. 00:57:00.480 --> 00:57:02.660 Hex is a data science notebook. 00:57:02.660 --> 00:57:05.040 They have DuckDB as a part of their architecture. 00:57:05.040 --> 00:57:06.680 There's a couple others. 00:57:06.680 --> 00:57:08.440 We talked about some of the data types. 00:57:08.440 --> 00:57:14.520 What about JSON or document type of things, right, where you've got a little hierarchical 00:57:14.520 --> 00:57:14.880 data? 00:57:14.880 --> 00:57:18.020 You know, there's on one end of that spectrum, we have MongoDB. 00:57:18.200 --> 00:57:18.900 That's all it does. 00:57:18.900 --> 00:57:23.440 And on the other, you've got relational databases like Postgres that say, well, this column can 00:57:23.440 --> 00:57:26.180 just be JSON arbitrary stuff. 00:57:26.180 --> 00:57:27.620 Yes. 00:57:27.620 --> 00:57:31.880 And we're in that space where we absolutely believe that's how most things are, a little 00:57:31.880 --> 00:57:32.300 bit of both. 00:57:32.300 --> 00:57:35.380 So there is a full JSON data type in DuckDB. 00:57:35.380 --> 00:57:40.320 And so you can store any arbitrary document in there and then process it with some really 00:57:40.320 --> 00:57:41.780 fast JSON processing. 00:57:41.780 --> 00:57:47.180 We can also take JSON data and automatically split it out into columns if you want to unnest 00:57:47.180 --> 00:57:48.180 it. 00:57:48.180 --> 00:57:49.120 So you can kind of go back and forth there. 00:57:49.120 --> 00:57:49.180 Oh, nice. 00:57:49.180 --> 00:57:56.100 There are also specific DuckDB nested types if you want to enforce the typing at the column 00:57:56.100 --> 00:57:57.460 level, but you want to be nested. 00:57:57.460 --> 00:58:00.160 So you could say, I know these are going to be my keys. 00:58:00.160 --> 00:58:01.480 I know this will be my structure. 00:58:01.480 --> 00:58:05.900 You can store it all in one column and it'll be very, very quick to work with. 00:58:06.420 --> 00:58:07.540 So you kind of have both. 00:58:07.540 --> 00:58:11.140 You have the full flexibility with JSON and then you also can have nested types. 00:58:11.140 --> 00:58:12.740 Can you query into them? 00:58:12.740 --> 00:58:17.480 So if I have a JSON thing that's got like purchases and then it's got a bunch of purchase objects 00:58:17.480 --> 00:58:21.160 and then like values, can I say, give me the ones that have over $100 purchases? 00:58:21.580 --> 00:58:27.780 You can do much like Postgres and SQLite have the ability to extract pieces of JSON with 00:58:27.780 --> 00:58:30.700 the path syntax of like go to this path and pull that value out. 00:58:30.700 --> 00:58:37.640 You absolutely can do that in DuckDB as well and say, okay, navigate to the customer object 00:58:37.640 --> 00:58:41.060 and then inside the customer object, go to this object and absolutely. 00:58:41.060 --> 00:58:41.560 Yeah. 00:58:41.560 --> 00:58:42.200 Super cool. 00:58:42.200 --> 00:58:42.700 All right. 00:58:42.700 --> 00:58:47.180 Well, I think there's probably more to dive into, honestly, but we've covered it. 00:58:47.180 --> 00:58:48.440 I think we've covered it pretty well. 00:58:48.440 --> 00:58:52.660 I think DuckDB is super exciting for the possibilities that it opens up. 00:58:52.660 --> 00:58:56.500 You know, there might be a ton of people out there who haven't heard of it or really just 00:58:56.500 --> 00:58:58.220 heard of it in passing and didn't really know. 00:58:58.220 --> 00:59:03.540 This in process stuff that you can do, it really makes it quite accessible, quite easy, 00:59:03.540 --> 00:59:05.320 lowers the bar to getting started, right? 00:59:05.320 --> 00:59:10.080 You don't have to understand connections, security, servers, daemons, all of that. 00:59:10.080 --> 00:59:12.560 So I'm excited to see where this goes. 00:59:12.560 --> 00:59:13.980 It's already got a ton of traction. 00:59:13.980 --> 00:59:18.400 So final call to action, maybe tell people who are in that group, what do they do? 00:59:18.400 --> 00:59:18.900 You bet. 00:59:18.900 --> 00:59:21.580 I think, you know, obviously, pip install DuckDB, give it a try. 00:59:21.580 --> 00:59:23.500 It's anywhere you can use it. 00:59:23.500 --> 00:59:27.280 MIT license, it really can fit anywhere that you're running Python. 00:59:27.280 --> 00:59:30.040 A whole lot of other fun things you could do on top of it. 00:59:30.040 --> 00:59:31.760 There's a new extension ecosystem. 00:59:31.760 --> 00:59:36.440 So if it doesn't exist in DuckDB, but you'd like it to, you can actually build an extension 00:59:36.440 --> 00:59:38.380 for DuckDB in a variety of languages. 00:59:38.380 --> 00:59:42.160 And so we can all together make DuckDB into whatever we'd like. 00:59:42.160 --> 00:59:43.740 So it's pretty neat. 00:59:43.740 --> 00:59:44.120 Yeah. 00:59:44.120 --> 00:59:45.540 Well, good work. 00:59:45.540 --> 00:59:53.100 And I love to see the open source side and then maybe a strong company that's built around 00:59:53.100 --> 00:59:56.200 it in a way that doesn't really undermine the open source value. 00:59:56.620 --> 00:59:59.920 So MotherDuck, DuckDB, it looks like a really good relationship there. 00:59:59.920 --> 01:00:00.880 So that's nice. 01:00:00.880 --> 01:00:01.320 Yeah. 01:00:01.320 --> 01:00:02.520 We even have two companies. 01:00:02.520 --> 01:00:04.040 We got DuckDB Labs and MotherDuck. 01:00:04.040 --> 01:00:05.720 Yeah, that's right. 01:00:05.720 --> 01:00:07.160 Got the foundational side as well. 01:00:07.160 --> 01:00:07.580 Yeah. 01:00:07.580 --> 01:00:08.100 Awesome. 01:00:08.100 --> 01:00:09.560 Well, Alex, thanks for being on the show. 01:00:09.560 --> 01:00:12.220 And yeah, thanks for sharing all this stuff with us. 01:00:12.220 --> 01:00:12.580 It's great. 01:00:12.580 --> 01:00:13.420 Thanks for having me. 01:00:13.420 --> 01:00:14.120 Cheers, folks. 01:00:14.120 --> 01:00:14.940 Happy analyzing. 01:00:14.940 --> 01:00:15.180 Yep. 01:00:15.180 --> 01:00:15.620 Bye. 01:00:15.620 --> 01:00:16.620 Bye. 01:00:16.620 --> 01:00:19.340 This has been another episode of Talk Python To Me. 01:00:19.340 --> 01:00:21.180 Thank you to our sponsors. 01:00:21.180 --> 01:00:22.780 Be sure to check out what they're offering. 01:00:22.780 --> 01:00:24.180 It really helps support the show. 01:00:24.180 --> 01:00:26.260 Take some stress out of your life. 01:00:26.260 --> 01:00:31.740 Get notified immediately about errors and performance issues in your web or mobile applications with 01:00:31.740 --> 01:00:32.040 Sentry. 01:00:32.040 --> 01:00:37.040 Just visit talkpython.fm/sentry and get started for free. 01:00:37.040 --> 01:00:40.620 And be sure to use the promo code talkpython, all one word. 01:00:40.620 --> 01:00:44.660 And it's brought to you by the Data Citizens Dialogues podcast from Colibra. 01:00:44.660 --> 01:00:49.260 If you're ready for a deeper dive into the latest hot topics and data, listen to an episode 01:00:49.260 --> 01:00:51.620 at talkpython.fm/citizens. 01:00:51.620 --> 01:00:53.480 Want to level up your Python? 01:00:53.480 --> 01:00:57.540 We have one of the largest catalogs of Python video courses over at Talk Python. 01:00:57.540 --> 01:01:02.720 Our content ranges from true beginners to deeply advanced topics like memory and async. 01:01:02.720 --> 01:01:05.380 And best of all, there's not a subscription in sight. 01:01:05.380 --> 01:01:08.280 Check it out for yourself at training.talkpython.fm. 01:01:08.280 --> 01:01:13.180 Be sure to subscribe to the show, open your favorite podcast app, and search for Python. 01:01:13.180 --> 01:01:14.500 We should be right at the top. 01:01:14.500 --> 01:01:20.280 You can also find the iTunes feed at /itunes, the Google Play feed at /play, and the 01:01:20.280 --> 01:01:23.820 direct RSS feed at /rss on talkpython.fm. 01:01:24.220 --> 01:01:26.820 We're live streaming most of our recordings these days. 01:01:26.820 --> 01:01:30.920 If you want to be part of the show and have your comments featured on the air, be sure to 01:01:30.920 --> 01:01:34.660 subscribe to our YouTube channel at talkpython.fm/youtube. 01:01:35.340 --> 01:01:36.700 This is your host, Michael Kennedy. 01:01:36.700 --> 01:01:38.000 Thanks so much for listening. 01:01:38.000 --> 01:01:39.160 I really appreciate it. 01:01:39.160 --> 01:01:41.060 Now get out there and write some Python code. 01:01:41.060 --> 01:01:57.060 You 01:01:57.060 --> 01:01:59.060 You 01:01:59.060 --> 01:01:59.560 you 01:01:59.560 --> 01:02:00.060 you 01:02:00.060 --> 01:02:30.040 Thank you.