Introduction to DuckDB SQL

Hi there, Data Scamps and Data Champs, this is Richie. Welcome to the session. I've got to confess, before this, I just tried some caffeinated chewing gum while I was also drinking a coffee. So I'm a little bit hyper at the moment. I have switched to camomile tea to try and calm things down, but if I have a heart attack, it's probably because the webinar is too exciting. With that, let's get started. So, if you work with data, SQL is completely inescapable. It's just used by every business and it had its 50th anniversary earlier this year. So we did an interview with Don Chamberlain, the inventor of SQL, on the DataFrame podcast. But the language is still evolving. And one of my favorite evolutions of SQL is the DuckDB database. So DuckDB has its own dialect of SQL, which make takes inspiration from R and from Python, so it's a lot easier for many analytics tasks. And then on top of DuckDB there's a commercial implementation called MotherDuck, which also makes things easier if you're working with a database in business. Now, DuckDB is also very highly integrated into Datacamp's data lab platform that we're going to be using today. So if you write SQL against a CSV file, then DuckDB is the database you're using. So if you take any datacamp projects, or if you take the datacamp data scientist or data engineer certification, then DuckDB SQL is the type of SQL that you'll be using. So very important for all of you to learn, which means I'm especially excited about today. Now, our guest is Mehdi Uaza. He's a data engineer and also developer advocate at MotherDoc. And so as a data engineer, he's got a lot of experience in data pipelines, modeling and reporting the whole. Also as a developer advocate, he's a very prolific teacher of particularly using DuckDB SQL. So you can find him on YouTube, LinkedIn and many live meetups around the world. So with that, please take it away. Mehdi.

Hey. Welcome everybody. Good morning, good evening. Whatever it is for you, please let me know in the chat. Where you're joining from, I'm based in Berlin, but I'm currently traveling, so I'm in Southeast Africa actually, so it's pretty far. Bear with me with my Internet connection. So, yeah, I think it's going to be. It's going to be great. I need to put the slides on the screen, so if we can go there, that's perfect. All right, so we have a lot to cover. I do workshop of three hours sometimes and people say it wasn't enough. Right. And we have really like 40 minutes now. So let's, let's buckle up so quickly. About Me Rich already did an intro. I'm Belgian but I live in Germany. I have more than 9 years experience in data engineering. I do data tech content on YouTube, on my personal YouTube channel and the Motherduck one because I'm also working of course at Data as a data engineer and developer advocate at Mother duck, which is DuckDB in the cloud in a short version, but we'll get into that and I'm going to expand it further. This is a real picture of me building Data pipeline. Yes, it's all about building blocks. The requirement for today you need a laptop, a deck that might be helpful and an Internet connection, an Internet browser, a terminal. We're going to use the CLI. I'm going to show you the DuckDB CLI. It's optional. You have multiple ways to interact with DuckDB will see that also and as we said in the introduction, datacamp account so you can open the shared notebook and set up a motherduck account for free and create the notebook connection. I'll get with you on that, don't worry. So this is how you need to connect to Modern Dark from datacam. I'm not going to go in details, but if you want to go back and you start going to do that, it's really fairly easy. But I'll put the step for you. So the webinar goal today is just to learn one way to use DuckDB in the project and please let me know in the chat if you already use DuckDB or if you're curious to hear about DuckDB. I'm really curious to know about that. I'll see in the chat and watch your comments. But yeah, the bottom line is that there is a lot of things you can do with DuckDB and we'll cover different use case and we go really pragmatic. So we're going to write some queries in pure SQL using Python, using datacamp Notebook so that you get a bit of a vision on what's possible. And rather than saying here are the use case of DuckDB and let you go on your own, we're going to dive straight into the pragmatic stuff. So, so what is the product doing? And then I'm going to zoom out and show you some schema to understand the use case. That's usually the best because like if you, if you show for example that a plane to someone and saying yeah, people used for that to travel and you've never seen a plane of real life. It's difficult to grasp the component, right? It's big, there is wings and so on. So we're going to take a flight with DuckDB. So here is the agenda. Quickly I'm going to talk a bit about some theory why DuckDB, where is it coming from? Then I'm going to straight go to some SQL and then we're going to talk about DAGDB storage extension, go back to coding, talking about the DuckDB ecosystem and use case. So it's the zoom out part that I was talking in the intro and then going back to coding, it's not a straight agenda. I may go jump off back to coding or back to site depending on your question. We have a Q and A for a deeper question I would say. But if there is something you don't understand of what I'm doing right now, please don't hesitate to comment and I'll make my best to watch out the comment while live coding. Can you imagine this? It's live coding, so for sure it's going to fail. You're going to see there is no video recording, it's just me directly writing SQL. So what makes a great database? Let me know in the chat whether you think it makes a great database and do you think it's correctness, performance, ease of use or acute mascots? And often the answer is the ease of use. And what about performance then? Because everybody show their benchmark and how fast their database and don't worry, we'll show you that too. That's DuckDB is then fast but if we actually step out for a bit, which one of these should be optimized? The time between when I click run and the time the query completes or the time between when I have a question and the time I have an answer. And these are not the same. Okay, so the point is there is really an important thing about ease of use. Another graph I like to show which is if you look at the performance on the different data warehouse, so OLAP Database, Synapse, Redshift, Snowflake, BigQuery. It's already a bit old. But the point is if you look at the performance conclusion you could say okay, that's the one that is faster, so lower is better on this graph, right? Synapse, then redshift, then Snowflake, then bigquery. And if you've been working in data, probably you know that that is not really true because the Market conclusion is Snowflake, mostly Bigquery, Redshift and Synapse. Synapse doesn't exist. They have been rebranded now with fabric and so on but at least for the time I would say between 2019 to 2023. So that's the point that it's all about ease of use. So come DuckDB. So this is Hannes run of the co creator of DuckDB. So that's a real duck, it's not a, you know, AI image in Amsterdam. And so the story behind this is that he noticed that data scientists don't like databases. They work with CSB all around the place. But however they do use Pandas, they do use R&DPLYR and data frame and copying those all about doing multil step make the copy every time really slow and it's hard to manage. You end up with a project with a bunch of CSV or part A here and there and it's not only the transformation is challenging, but the management of the data set is challenging. So, so enter DuckDB. So it was created by Hannes as I was just showing the picture and Mark at CWEI which is a research institute in Amsterdam. And so it is maintained by DuckDB Labs and the DuckDB Foundation. So DuckDB is a lightweight in process equal and IATX engine. And the thing is it's run in process. It means that it's not like a client server. I have a graph just after if you use python within DuckDB you can just do a pip install DuckDB and that's it. There is nothing to install on the service side. It's an IT license and we passed the 6.5 million download per month on just the Python client. So that's pretty huge. There is a huge adoption right now. I see a couple of comments that I was amazed on how small it handles parquet for amazing parquet, amazing. Just mind blowing. I use DuckDB, I use and I love it. I feel like there is already a couple of DuckDB fans in the chat. It's a new kind of database category we have basically in process transactional database. So SQLite is also just a library. If you're using Python you just install pip, install SQLite and it's in process in your Python run process. But there is no in process analytical database. So I put a client server side data warehouse here. Snowflake and BigQuery Redshift is the same. So this is really a new kind of database. That's the main key takeaway here. And because of that it's really opening up the use case and the possibility that you can do and you're going to see that after. But think about that's the foundation on what is DuckDB? It's an in process analytical database. It's a new kind of database if you compare it to the others. So it can run really anywhere. So there is DuckDB CLI that you can use on macOS, Windows or Linux. You can just do a pip install dagdb if you're using Python, if R is your thing, if Java is your thing. Node JS I'm curious for people I seem like there is already DuckDB fan in the chat. Let me know which can actually of client are you using if it's Python, if it's the CLI or R I would be curious to know. So it supports input and output multiple formats so you can read from CSV, Gson, Parquet, Iceberg, Delta, Lake Also right now that stable format and it has really good interaction with Pandas and Polar. So you can query directly Pandas data frame with zero copy thanks to Arrow. So there is no overwrite if you switch between pandas frame to DuckDB for transformation. And we've seen that a lot people refactoring their their Python pipeline using Pandas to use DuckDB for your transformation and keep the input and output if they need to display something with Pandas. Another important thing is that DuckDB has its own file format. So it's kind of like a database file that includes all the tables and all the metadata is super efficient and it's a great file format to work with to again have a single place to manage all your data. That's great. You can create views to S3 file, you can import CSV. We'll play a bit also with that. So I see in the chat that a lot of people are using Python jupyter Notebook Magic DuckDB extensions a lot of Python. So yeah, that's what we saw in the stat. Also a lot of Python downloads data source same thing. You can Query directly a Postgres database within DagDB you can query directly S3 Google Cloud Storage Azure file Again super fast and super easy. So enough talking. So we've seen that DuckDB is Swiss Army Knife. So I'm going to start by. So I'm just having a VS code here and basically you can install if you're on macOS just to brew. There is also another package manager for Windows people. There is the installation page. Do not worry, everything you do here you can do in the data camp Notebook and actually I'm going to show you quickly how it looks like for diving into code you have the workbook which is here. If you set up your MotherDuck account and you set up your connection, you just have to configure your motherduck token and you're ready to go to start query. So MotherDuck is the server cloud offering of DuckDB. We'll go into that. But you can also run all the SQL I'm going to run over there or you can run within Python so you can just run the two first cell and you have a DuckDB connection ready and then you can start query. So that's also a good place to start with just the notebook there and so coming back to the VS code here. So I just did a pre install DuckDB so that's simply it. And now I have CLI which is ready there. So we're going to start really easy just to get understanding how the in memory DuckDB is kind of working. The first thing is that if you create basically a table just like this I'm just sending by the way, it's just a shortcut, it's SQL file and I'm sending it to the terminal to the DuckDB CLI. So here now if you see I have basically a table that I create which is DAX and this table has just one line. The first thing interesting to see Richie was talking about SQL is that there is a SQL standard but this one has been evolving pretty slow because it's really hard to get new things into the SQL standard and DuckDB has been leading the way where there is a lot of friendly function to help you just being more productive within SQL. The first thing you can see is that I'm not doing a select star from, I'm just doing a from because directb support from first statement. So that is beautiful. It's happened to me now when I'm going to another database I just do from and I get an error and say okay, let's select star limit Anyway, so that's one first friendly SQL we're going to see others. So you see if I do select database, show database I just have in memory. So that means that now if I exit the process, the CLI process here and I'm launching DuckDB again and I'm showing the tables, I have no more tables, my ducks are gone. So what happened? It's in process, that's why how do I purchase the data? Well, there is multiple ways to process the data. You can write to CSV, you can write to parquet or you can use also the DuckDB file format. We're going to play a bit with that. So if I do a launch CLI and do DuckDB and the name of the database, the extension can be DB or ddb. It's just by convention can be anything. Now basically, as you can see, as soon as I'm going to do an operation here, so let's do a create table of my duck again. So you see now on the right side I have now a wall and a file that's been created and it's the DuckDB basically file format and the data is over there. So I'm exiting the process again. Now I'm launching against DuckDB. I do show tables. My DAX is there. Okay, So I persist the data over this file. So this is pretty neat because you can create multiple view also over S3 and so on using this file format. So that's a bit like hello world. Now let's go into. Because time is running, let's go into this one that I really like. So I'm going to do a from and basically just this is just a parquet file over history. Just a Parquet file over S3. I would challenge you to use to show me any tool where in a single line of code you can query a remote file on object storage. Here is three and in parquet directly with just deductive ecli so I can do any query on that I can describe the table. All right. So this is actually a tool I use often, the CLI tool to debug or inspect things that has been wrong in pipelines just over the street. You see how quick it is. I can directly inspect any parquet file, any CSV quickly. That's beautiful. What's happening behind the scene there? Well, there's multiple things. The first thing we talked about, the prompt first statement. I'm not going to go back but there is extension being loaded behind the scene. DuckDB is written in C and it has built in all the necessary dependencies. It's not like when you install pip install DuckDB in Python. You're going to need to install different things to read for S3 or authenticate to AWS. Everything is in package and it's included into a single a single tool. So you can see here I can. There is multiple supported extension. And you see there is the postgres scanner. You can read from postgres I was explaining before and the HTTPFS is the one used here to be able to read Promist 3 and this is A public packet. And so that's a bit. The mechanism of extension is that the beauty of it is that it is downloaded for you behind the scene. So it's installed and loaded for you. We're going to come back quickly on the operation. If you need to install it, load manually in the extension. But that's the beauty of it here in this line. I didn't specify the file was on the street. I didn't specify the file was parquet. DuckDB loaded all the extensions. So you see the parquet extension is loaded for me. That's a really pretty neat thing to just highlight how easy it is to get started from there, basically you can create again a table within DuckDB. Now I'm going to load this parquet file internally. Now I have it in my DuckDB database file. So I have this Netflix data set and I can also start query. This is the most popular TV show during COVID in the U.S. that's interesting because it's a lot of basically kids show Kokomalan. If you have kids, it's kids music. Cobra Kai was really successful and same on the movie. Anyway, so that's all about working with extension. Working with remote file. That's basically the two things I'm looking at. We covered also how to load the database. And by the way, if I load the CLI first and I do after. So I don't have any table right now. Right. And I do just an attach command. This attach command is magic. You can attach multiple database file. Then I can see basically again all my table which is there. So the DAX one and the Netflix I just created before. All right, so we talked about extension. What else is to be talked about? So there is. I have a couple of slides also around that maybe I'll jump back to it, but I'll cover that first. So as I explained, we have. You can see which kind of extension is installed using the from DuckDB extension. And by the way, again, all those commands, you can run it in the colab. Sorry, in the notebook from Data Camp in Python. This is pure SQL. The interface is the same. If you installed manually an extension, you just do install and load. So this is the extension and if you want to install a community extension. So it's not extension supported directly by DuckDB. So let me actually show you that right here. So there is a website here called community extension and you can also patch your own extension, but you have to know C. So that's a bit more challenging. But we have a bunch of people contributing about different Community extensions as you can see. So that's all the extension and there is one extension for example Scrooge that provide an interface to Yahoo Finance. Yahoo Finance is basically a website to get crypto transaction or basically stocks information. Now if I coming back to VS code so what I do is that I do install Scrooge. That's the name of the extension from community. I load the extension and then for example here this query is going to do Yahoo Finance and going to me some information between those two dates about the S&P 500 on stocks and the Bitcoin and the dollar. So here we go. So here what's happening behind the scene is that it's called an API, an open API and retrieve me the result into a table and the extension make it really easy to query different stocks. So again just an example like how taggedB is easily extensible by this extension mechanism as we see and this is like extension coming from a community but there is a lot also in the building extension from wb. One thing I want to talk also we talked about S3 in a private bucket but maybe in reality my data in object storage are in a private bucket. And of course DuckDB has a secrets manager and so you can just store your different secrets safely either you know to AWS key and add with secrets or also to sso. So here I'm logging actually. So this is a private bucket. So it's going to fail this one. You see I have a 403 so access. Let me hide this part. I think you hide it, put it there. So you have a 403 right there. And basically what's happening if you create the secrets it's going to use here my SSO connection. So I already log in using AWS SSO so I have credential in my config file locally. Now I have a secret containing that inductdb and I can query this again and it's not working. Of course it's not working because it's a demo. So probably I haven't done go to let me try again. Or probably it's just because I haven't but my SSR refresh before or I did it a couple of hours before and so what I'm going to do is just logging it in. All right, while I'm doing this, what I'm going to show you another thing which is super fun kind of a SQL kung fu and then I'm going to stop there for the CLI because there is a lot of things to do is that here for example, I'm reading from an API from GitHub API and then I'm doing some compute here where I want to get the top contributor and I'm displaying a bar chart. So we've just SQL, I'm doing an API call and I'm parsing the result and I'm visualizing this into a bar chart because DuckDB support also bar chart within the DuckDB CLI. So let's wait a couple of seconds. Bear with me. And there you go. So this is the top contributor. Actually as you can see from the DuckDB repo you can change to any repository and it's using the GitHub API and then parsing the JSON. So it's a read JSON for that and getting the result. So again, super, super powerful to parse JSON JSON log JSON from an API you can do directly call from an API. That's all their use case. I'm going to go back to the slide where we are standing now. So DuckDB storage and extension, we talked about that. So by default DuckDB won't process your data. It will be created if not exist or you attach it. It's a one file that contains all tables. It's efficient compression and support updates. So for installing extension which I have shown it to you, we also saw how to load it from a community extension and loading finance data. It's important to understand that. So when I do from S3, yada yada, there is extension that's being installed automatically. But if I'm doing for example an operation where I have which was this one, so this one which is calling a yahoo finance function DuckDB won't load explicitly the extension because it's a community extension. So use it with responsibility because it's not supported officially by DuckDB so there is an autoload function for the other. So whenever you do a from data to JSON you have the JSON which is extension, which is load. So that's why most of the time actually you bom install and load extension and there is the community extension website which is referred at all Right, yeah, let's go into the data camp notebook and talk about Python. So I just installed, I have to initialize the session because of course I haven't done it just before. So bear with me. So I'm just installing the latest DuckDB 1.111 version because there isn't one already pre installed over there. But it's just to get the latest. So this is done and so now basically how do I initiate the connection is just import DuckDB and DykDB Connect. So one interesting thing when you work with Python I mentioned that is that it's the interoperability with the existing data transformation library like Pandas or Polarz. So for example, here I'm doing a query against our S3 data set from Netflix and you see it's the same query, it's again SQL. I'm just using the connection and I'm going to convert it as a Panda data frame. This is run as a Panda data frame and now I can inspect it. So this is the Panda data frame with the data. And now what I can do is that I can query directly this Panda data frame. As you can see, I don't specify any table here. I specified the Python object which is the data frame. If you have existing pipeline using Pandas or prowars, it's really useful to get started with wdd. You install it and you can start to do a couple of transformation or do pure SQL on top of a Panda data frame. If I run this, you see this is the top movie, so we did the TV show before and with the CLI during the COVID and you can see that it's also a lot of kids movie. The reason for that is simple. Kids like to watch over and over and over the same thing. So for one kid you're gonna get multiple watch time, which is completely different for us, you know, normal human adults which watch one or two times, maybe 10 favorite movie, but not like 100 times. All right, so that's for Python. Everything I've done before with the SQL query, right? You can do the same thing here with this interface. Let me jump back on the side to see what we got here. So let's talk a bit about the ecosystem. So DarkDB and MotherDuck support a lot of of interface in the data stack. And this is where ModernX is going to come into play. I have actually a couple of slides quickly to understand what is Mother Duck. But basically It's a serverless DuckDB platform and you can store your DuckDB file format, the one we play directly on Mother Duck. So you can store directly your tables. You can create a share so easily share it with other people. So that's really great for open data sets. So we have a bunch of shared database, that's what there is in the notebook here right now. So yeah, let's play a bit and poke around for a couple of minutes around Mother Duck. So here I'm using directly the connection and as you can See here it's just basically getting the last downloads from Pypi from DuckDB. So it's come from a shared database here. And so I'm gonna run, I'm gonna run this query and of course this doesn't work because I think this share is not directly that. Let me come back here. So this is just an example of other SQL friendly function. So let's show you how you can do function chaining. This one show you also how you can do excludes. So here I'm selecting all the table from ambient air quality data. So this is the sample database which you can have access everybody that start a MOTHER account get access to those data sets. There is also data set around Hacker News Stack Overflow which is in there. So feel free to explore. Here's another one which is dynamic selection. Pretty cool. So here I'm selecting only the column which is like pop based on a column expression. So a lot, a lot of things regarding pure SQL and here you see, for example, that's a group by all. I could talk for hours about pure SQL for this thing. It's just that for this query that the database is not shared yet. So I'll just, I just fix it in a couple of seconds and actually digit in DirkDB stats. Here we go. So the next thing I want to talk is before jumping off because what we already talk is how to use mother duck. So the important thing maybe I can mention is that whenever I run those query and this one that I'm not using any more duckd in the process I'm leveraging the cloud. One thing I want to show you against using the CLI is that coming from because we use the native integration of datacamp but what's happening behind the scene? So if I do an 8H MD column now I'm going to connect to Motherduck Cloud and I have a Motherduck token in my environment variable and now basically I can see all the database from motherduck. All right. And if I start to do some query over there, basically the query is going to be compute on the cloud. All right. There is also a mechanism where we do hybrid dual execution and we leverage your local compute. So if some stuff can be computed on your laptop, they'll push it back to your laptop and some stuff cannot be computed on the laptop and we leverage the cloud. So it's really the best of both worlds. And you see that there is really a smooth, I would say workflow from working local to basically connecting to the cloud. I just did attach MD and now I'm start to can query on the cloud share database and so on. And so that's kind of what's happening here for you in the ui. It's just doing an attach and that's pretty much it. So we have a couple of minutes left. We talked about the ecosystem, we talked about using MotherDuck, how to leverage a cloud, how coming from local DuckDB to working on the cloud. Now I want to. There is a one last one that I want to show. I'm not going to be able to go into details, but I do want to show that this one. So DuckDB can run in the browser. What does that mean? Because you know the notebook datacam is in the browser. But I think what's happening here is that it's actually compute on the server side here it use wasm, which is stand for webassembly. And basically what happening here is that if I disconnect my Internet I won't do it because otherwise I will lose the connection with you. But you can do the experience after this webinar because you will lose it and you connect to shell.darkdb.org you're going to have a DuckDB session in here and if you do call DB Gensf. So I'm going to generate some data. Here. So you see I've just generated data in the browser, so in compute. So this is computing locally in my browser. I haven't installed anything. It's time to webassembly and now I can run a simple query, for example on this data set. And you see everything is compute locally. And so the beauty of that is that we do leverage this in the mother duck UI where basically if stuff can be compute on your browser, it will do it for you. And so that means you're not connecting to the cloud, you're reducing cloud cost. And the experience as a user is not much snappier. Okay, so that's another big use case which is growing around DuckDB is DuckDB was and so I invite you also to check out the other resource and link for other demo. All right, it's time to wrap up. We have. I want to talk now about the use case. We've seen how the CLI is great to work. We can do C SQL, we can store the data, we can also export. I haven't gone into the function, but of course you can export to parquet or CSV directly use the dyedb file format. What are the main use case? So the main use case first is really data wrangling and ad hoc analytics. So basically what you're going to use is you use any client would it be, you know, a jupyter notebook, Data camp, a CLI and you're going to read from CSV Parquet, GZone or WDB file format. You're going to ingest the data and do some compute and write it back either to an object storage or to a DIAGDB file format or to motherduck. So that's pretty classic. The second is really about ETL pipeline. So here you're really using as a scheduled job often typically it's run in process in Python. That's how people using it. You do pure transformation against you using IDB, SS3 as your blob storage or Modern Duck. And it works out so great with DuckDB, with DBT and DuckDB and MotherDuck. So you can also use it with those frameworks. So this is typically for pure data processing. ETFL PI, that's another big case. And the third use case is the one that kind of I show with the data apps and the waste because now that you have basically the database that can run in the browser here and running the server side, it can help cache any analytic tool, dashboarding tool. So if you need to display a large shed of data, typically a dashboard and then you need to do a couple of filter, every filters and compute is going to happen on the client side. All right, I'm gonna, I'm gonna show you, we might get a bit just out of time, but I want to show you this demo. So this is a demo. It's also in the slide by the way, so don't worry. Which is using Mother, Duck and Wasman. So what's happening here is that we're going to display some, some data to a dashboard. I'm going to take this yellow water. So when I'm going to select this, you see I'm applying a filter. So behind the scene I have a data set that's contain all the weather at Seattle and you see how much sun, how much fog there is. Now if I move the filter, typically in a dashboard, you're going to see this hell loop because it's loading the data from the server, there is a latency. You change a filter, there is a request for new data on the server and it's coming back because the data is already loaded Here in my browser, every compute is happening locally. So if I move the cursor, you see the data filter is instant, there is no loading time because every query is happening locally and you See like for example here I'm going to take 10 millions row on flight. Same thing, I'm taking a filter and you see all my graph are adapting directly because every compute is happening on the browser. So that's really. Basically one of the key features of WASM is empowering dashboard for caching. All right, wrapping up everything. So where can I use DuckDB? So ETLS data wrangling, exploration. It's also a good SQL engine over data Lake. We did a lot of query over S3, right. So you can think about that local development. We use the CLI for data wrangling, data exploration, it's great for testing. Or you can use DuckDB, CLI, Python or any language. And as soon as you need to scan scale or have the multiplayer game you need to share your data set easily, come to see Mother Duck and check it out. So there is all the links of the resource which is there. And yeah, so that's, that's, that's about it. It's, it's easy like if you count like everything we've been doing like reading data from S3, parsing the data from JSON Query, an API, if you remember, with the gdapp contributor directly in SQL, a lot of friendly SQL function exploring a file format. Everything was super easy and super simple. That was a packed agenda. And that's it for me. Just stop quacking and. All right.

That was really fantastic. I got to say, I love this, just being able to pull in data from wherever it is, whether it's CSV or parquet or it's like somewhere in an S3 bucket is like one line of code. You got your data, you're not messing about with all the setup stuff. So very fantastic, exciting stuff. I think it's making analytics a little bit easier. All right, so for anyone in the audience, please do ask questions. For Mehdi, we've got plenty of time for questions before we get to that. As I say, tomorrow we have a webinar on breaking into data analysis careers. So if you're interested in your first job in data, please do come back tomorrow. Then on Thursday we've got a Leader Focus session. So it's the Learning Leaders Guide to AI Literacy. So if you want your colleagues to be better at working with data, then please do come along to that. And I should also mention on the podcast on Data Framed, we have met his colleague. So Ryan Boyd is one of the founders of Mother Duck. He's going to be on the show on Monday. So, uh, watch out for that.

All Right.

With that, uh, let's go to some audience questions. Uh, so first of all, uh, Christiane says, uh, what are the advantages of using DuckDB over a typical relational database system? So yeah, uh, why might you. You covered this a little bit, but just want to summarize why you might want to use DuckDB versus whatever else.

So typically standard, I mean there is two kind of relational database, right? There is the identical database and the transactional. So you shouldn't use transactional database like postgres to do your analytical workload. You can start with. At some point you're going to kill your transactional database because it's not meant for that for analytical database. As I said, there is a lot of today client server side setup like Snowflake BigQuery, but you always need a server to run this database where here basically everything can be run locally on a single node so you can leverage more your local power. So it's really a new kind of database that run in process rather than. And you can still put it on the cloud. But it's. You have this flexibility and this workflow of leveraging this expensive MacBook that we all have. Right Richie?

Absolutely. Yeah. Everyone needs an expensive MacBook, right, if they want to do digitals.

All right, cool.

So there's actually a similar sort of question slightly more specific. This one comes from Harris, that trend saying, does it support querying Clickhouse databases? So Clickhouse is another analytical database. It sounds like a DuckDB competitor, but I don't know. Do you want to.

So there is. Can you share the screen quickly? There is a. I just want to highlight. Yeah. So in the DuckDB community extension, there is actually an extension to help to query or remote ClickHouse instance to HTTPs. So it's still pretty limited, but you could try that. But there is other way around. Also easily to query directly your data at your source because again, qcas is also an analytical database which is probably feeding their data somewhere else. But we've seen starting people also to query data from Kick House.

Okay, that's cool. Seems like it's. It's all supported then. Excellent. All right, so next question comes from Diego. So D saying if you want to break into data analysis, how do you go about learning DuckDB and what previous knowledge you need to have?

Yeah. Can you put it back? Yeah. Mr. Would you recommend learning DuckDB? Yeah. So the thing is DuckDB is great to learn SQL and so we have a lot of University switching their SQL courses with DuckDB because that's super easy to set up. You Just install the CLI or just do a PIM install in Python and it's quick. So that's the best tool to learn, that's the point. So it's not really which step you would be to break into data analysis, but the point is actually if you break, you know, into data and need to learn SQL, DuckDB is really the right tool for you to, to get it started. So in terms of like previous knowledge, I would say that if you are not comfortable with the terminal, that you can learn the BASIC over there, a bit of Python, but to be honest, you don't need that much. And if you go to Data Camp or Modern ui, we have also new app, you can start to write straight SQL only. I think focusing on your SQL skills first, that's super important. And having DugDB in the back to support you for that is great.

On Datacamp, most of our courses are based on PostgreSQL just because that's the most commonly used. But if you taking any of our projects, I think some of the projects will make use of DuckDB SQL. And also we have a load of tutorials. If you go to the blog section of the site, then yeah, plenty of control.

Yeah. And I want to add that like DuckDB Creator, we are ably inspired and still are the Postgres syntax, right. So a lot of functions are built inside, so it's actually really close. Like you can most of the time copy paste it. All the SQL function coming from postgres.

Absolutely. Yeah. I'm not sure there's much that's not going to work in DuckDB, like if it's Postgres syntax. So that's cool. All right, next question is from SB Actually there are a few questions in the chat just around are the limits, like how big a data set can you deal with? I know motherduck's very keen on working with small data, but small data is not as small.

Yeah, yeah, that's a good question. So I haven't get into time to that because I had a data set of 4 gigabytes and I crunch it in subseg. Okay. Locally. So if you think about it, 4 gigabytes of data and I can't, you know, with like it's a 33 millionth row. Right. And I can query it with subsequent less than one second query. So there is a lot of margin. Right. And the point is that if you get into 10 to 100 gigabytes, you're going to see, you know, it's still going to be pretty, pretty okay. For most of the workload. But then if you go above 100 gigabytes and then the question is, do you have workloads like data daily that you compute, which is over 100 gigabytes? You have a table, you know, or folder containing a data set daily. That is. And I'm saying daily because you know, there is multiple way to compute and chunk history. Right? That's right. But the point is like a lot of. And that's the, the whole premise of the, of the small data and B data is dead is that most of the people will have you know, daily workload which are far above Android gigabytes. There is a lot of excellent blogs on our model blocks around that around statistic. And so the point is, before getting scary, I see I was, there was a beauty. I tried the 10 gigabyte of file. Even it was able to load it with no hiccups. Yeah. So the point is you're not going to see limits in your workflow when you start it. Like I'm pretty sure it's really like at scale in a, you know, in a company and there you have different way of doing it and motherdirect is also there to help you scale. That's, that's how promises.

Okay, so if you're dealing with terabytes of data, then that's, that's.

Yeah.

Separate big data problems. But if you dealing with like up to several gigabytes of data, then DuckDB is not covered.

There is no. Yeah, there is completely fine.

All right, super. Actually, one last question before we finish. So it sounds like DuckDB is going to be suitable for like data analyst, data scientists, maybe like some web developers as well. If you're in a company, I guess most companies have some database there already. Like how are you going to get started using it if there's already a database there?

So you mean like they already have a data stack with like.

Yeah. Like is this something where you can just use for like individual projects or is it something where it's going to have to be.

So that's the thing we saw is that you can use it. Like a lot of people are using the CLI to you know, debug or daily query on object storage. But they have their pipeline in Spark or whatsoever. And then some people transition some ETL pipeline like from Pandas to DuckDB. So this is part, you know, it's just a Python library that they change what else that we see. There's like multiple ways to get like, you know, started on different things. I Don't believe, like the data stack, you know, is really big. They're gonna be some stuff that's gonna be merged, but as of today, like DuckDB will definitely fit within one of your use case overpro. Are you use it for learning, for example, or you have a platform like Data Camp and you need the SQL interface. Right. So you use DuckDB. I didn't mention that. But Datacamp is actually using DB on the server side to provide SQL interface. So yeah, there is so much way to get it started. It's not just a data warehouse, it's like, oh, I'm using already bigQuery, so I have no usage of dyb of mother. Right. Um, we've seen like plenty people just starting to pick it up on specific small projects.

All right, super. Okay, so we are at time now. Thank you once again, Mehdi. That was very, very cool stuff. Really interesting. Thank you to Rhys for moderating.

Thank you to everyone. Yeah, I just want to. Yeah, sorry, I just want to add that, like, so I have my LinkedIn so you can find me. Mehdi was on LinkedIn and so feel free. If you have any question I haven't got the time to answer there. Please, you go. You can ping me. This is my work. So you're. You're not going to bother me?

Absolutely, yes. Go and check out Betty's YouTube channel. That'd be brilliant.

Okay.

All right, thank you everyone for coming along. I hope to see you again tomorrow or Thursday. We're taking a break from webinars next.

Week.

Reese and I at an off site and then we'll.