Pydata duckdb transcript

transcript_pydata_duckdb.txt

[00:00:00] Hello everyone.
[00:00:01] Uh, welcome again to PyData Global 2024.
[00:00:04] We are very excited to have all of you join from around the world today.
[00:00:08] Uh, I'm Conrad, uh one of the co-chairs for the conferences.
[00:00:13] pleasure to introduce Dr.
[00:00:15] uh Hannes uh
[00:00:18] uh Mühleisen.
[00:00:20] Um, he is the creator of DuckDB and he will be talking to us uh about changing data with confidence using DuckDB.
[00:00:28] Um Thank you.
[00:00:30] Please take it away.
[00:00:31] Thank you.
[00:00:32] Um, just give me a second to share my screen here.
[00:00:37] Um,
[00:00:42] Can you see the the big DuckDB logo?
[00:00:46] Yep.
[00:00:46] Very good.
[00:00:48] All right.
[00:00:49] Um, yes, hello everyone.
[00:00:51] Um, my name is Hannes Mühleisen, thanks for the introduction and I'm indeed one of the people behind DuckDB.
[00:00:56] Um, here today to talk about changing data with confidence using DuckDB.
[01:02] Um, and confidence is an important uh word for us here here at Team DuckDB is because we have sort of noticed that many people have a sort of problematic relationship with their data.
[01:12] Um, data is generally feared, like anything that stops fitting in Excel, people are terrified.
[01:18] It's my experience, you know, after working with people for data people for a while.
[01:23] And um, so we kind of set out to change that because, you know, we like tables and we we feel like that there should actually be no need to be terrified of data, right?
[01:32] Like there this has in my opinion, uh this fear has to do a lot with um tools being difficult to use.
[01:40] um with, you know, general know knowledge gaps perhaps.
[01:44] But yeah, we set out to to to change that.
[01:47] Uh as our mission is to get people to get to data confidence.
[01:51] And the vehicle that we have created to increase people's confidence in their abilities to wrangle data is DuckDB.
[02:00] So in case you don't know, DuckDB is a free and open source data management system.
[02:05] Uh it's special because it's uh analytical focused, it's in process architecture.
[02:10] And um, yeah, it's uh it's pretty interesting to work on a database system in case you have uh ever tried that.
[02:17] Uh I can not entirely recommend the experience.
[02:21] Uh there were a lot of sequel things I had to learn.
[02:24] But speaking of sequel, uh DuckDB is a relational system, uh which means it does speak sequel among other things.
[02:30] We also have some um programmatic access methods.
[02:33] But the fundamental abstraction are tables.
[02:36] Uh we can store things like nested types and so on if you want to have more rich data types.
[02:41] But fundamentally it's a SQL system.
[02:44] And I've already mentioned it that DuckDB is trying to be simple.
[02:47] We have we have zero external dependencies.
[02:49] It's one big sort of collection of C++.
[02:52] It's ultra portable.
[02:54] It has no external dependencies.
[02:55] It doesn't make use of any sort of special instructions or things like that.
[02:59] Uh, it is a fairly small binary.
[03:01] And it does not contain require any container, Docker root, anything server set up or anything of the like.
[03:08] You just download the binary uh or indeed you can just pip install it.
[03:13] Uh here is the screenshot of installing uh DuckDB on a on an empty pip.
[03:19] Uh you can see you just say pip install DuckDB, you download 15 megabytes of stuff and there you are, you have DuckDB.
[03:27] And after that, um you can for example run DuckDB from Python.
[03:31] There's also other ways of talking to it like a CLI or R API or Java API or God knows which API.
[03:38] But basically you can then import DuckDB here in this example in your Python shell and run SQL queries.
[03:44] Pretty simple.
[03:44] It runs anywhere, Mac, Linux, Windows, as I said, it's ultra it's meant to be ultra portable.
[03:51] And I think what's also interesting about DuckDB is that it's really batteries included, much like Python.
[03:57] Um, which by the way, in case you didn't know, uh Python was also created here in Amsterdam.
[04:02] And it's actually only, I would say 100 meters away from where I'm standing right now where Guido's office was when he created Python, just as a side note.
[04:11] Uh, but DuckDB is also batteries included, uh learn from the best.
[04:14] So we have for data, we have transactions, uh we have full uh asset compliance, we have multiversion concurrency control, more about that later.
[04:22] We have persistence, extensive support for relational transformations like joints, aggregates, window functions, you name it.
[04:31] Uh DuckDB can out of the box read parquet files, write parquet files.
[04:34] We have I think the world's best CSV reader.
[04:37] We can read JSON files.
[04:38] As of today, we can read Avro files.
[04:40] And you can connect to a zoo of other things like cloud storage, S3, you can talk directly to Postgress, you can talk directly to MySQL, you can talk directly to SQlite.
[04:51] You can import and export data with Arrow.
[04:53] You have as I mentioned already, you have clients for R, Python, Java and I don't know, 20 other languages.
[04:58] Uh it's getting a bit hard to follow at this point.
[05:01] So, even though we um we are really trying to sort of copy some of the simplicity of maybe something like SQlite.
[05:08] Um we really try to, you know, still not not sort of, you know, rob you of features that people kind of expect from data management systems.
[05:17] DuckDB is also fast.
[05:19] Uh DuckDB has a state of the art query execution engine.
[05:23] Um it is technology that was kind of known to researchers for a while, but so far has been hidden in a very few commercial products.
[05:30] It's a vectorized execution engine.
[05:33] It's one of the sort of two paradigms of writing analytical execution engines right now.
[05:38] People always ask me what exactly makes DuckDB fast?
[05:41] And I always say it's magic.
[05:43] Uh of course, we know that uh any sufficiently advanced technology is is indistinguishable from magic.
[05:48] And it's actually the culmination of decades of research also here in Amsterdam in uh analytical database systems.
[05:54] Many people got their PhD uh working on this stuff.
[05:58] But for you as a user, what is often the most visible effect of DuckDB being fast and efficient is that your computer gets warm.
[06:07] So we uh DuckDB can automatically paralyze queries across all the available CPU cores.
[06:13] But again, you know, blind parallelization is not enough.
[06:15] I things like Spark also parallelize, but then they suffer from a kind of low per core efficiency.
[06:20] DuckDB on the contrast has highly efficient C++ implementation for the operators and as a result, we have very high per core efficiency.
[06:31] Uh DuckDB can use the disk if there is not enough memory to complete a query and again, all of this happens completely automatically.
[06:38] And DuckDB is free.
[06:39] Um it's free and MIT licensed.
[06:42] Uh we are actually not VC funded.
[06:45] So DuckDB is not a project that uh is uh you know, in threat of a rug pull in the near future.
[06:51] Um, we are, we have a non-profit that's that's holding all the IP in DuckDB.
[06:57] Um and then there's a commercial company called DuckDB Labs that I'm heading that sells consulting and support services for DuckDB.
[07:04] And with this revenue, we basically fund the entire show.
[07:07] So we have a viable and long-term funding model to basically keep DuckDB free uh for the time coming.
[07:15] And DuckDB loves Python.
[07:17] So Python is actually one of our primary APIs.
[07:21] A lot of cutting edge DuckDB features come to the Python API first.
[07:24] I mean, first they come to C++ and then usually afterwards they come to the Python API.
[07:30] So, oops, that was not great.
[07:32] Something happened.
[07:34] Not sure what happened here.
[07:36] I'll just um go back.
[07:39] Uh so has some unique features uh in Python land.
[07:43] So one of the interesting things is that DuckDB can run read pandas data frames directly because the architecture of DuckDB is different from what you maybe know from database systems is where um we actually run within whatever process the user is interacting with.
[07:58] So for example, in the case of Python, we just run directly in the Python process.
[08:04] And that means you can directly access things that are living inside that Python process like data frames.
[08:08] So DuckDB understands the in-memory binary layout of data frames.
[08:12] And we can also directly turn the results of any queries back into Panda's data frames within the same process.
[08:19] This is quite efficient.
[08:21] Uh as far as I know, we are the only really database management system that can do this.
[08:26] The same trick also works with Arrow.
[08:27] So if you're, you know, if your tools can read and write Arrow structures, then you can directly pass those structures to a DuckDB instance and you can read them as if they were query tables that uh are in DuckDB already.
[08:39] And you can again take the results that come out of DuckDB and put them back into Arrow structures with minimal overhead.
[08:47] And that really uh enables a bunch of really interesting use cases that uh maybe with traditional systems like Postgress would not be possible because of the immense overhead of transferring data over a socket.
[09:02] Okay.
[09:03] So now let's talk about talk about change.
[09:08] So, change, um Change is as old as the universe.
[09:12] It's a there's wonderful there's wonderful quotes from Greek philosophers on this.
[09:16] But somehow the Python data stack treats changes as a complete afterthought.
[09:21] So, if you think about it, like a data frame is something that's completely static.
[09:24] There is really no notion of changing this uh data frame uh without just completely recreating it.
[09:30] There is like no notion of updates really.
[09:33] Um and there's also no notion of sort of transactionality.
[09:37] But change is something that is inevitable and data changes.
[09:41] And we have to be basically be able to do better than to completely reload uh from scratch.
[09:47] So I have I've collected some examples from sort of Python data interactions.
[09:51] So here we have a a pretty simple data frame here in the in the upper example, where we just have a data frame with one column A 1, 2, 3.
[10:00] And now we have this sort of pattern where we assign DF to an operation on the DF.
[10:06] And in this case, we make a small mistake.
[10:08] We filter uh A bigger than four.
[10:10] Obviously there is no four, so now DF is empty.
[10:13] And there is no undo here, right?
[10:15] So the only thing that you can do now is go back to wherever you got the data frame from.
[10:19] That's fine if that's a tiny file, but that's problematic when uh the file when when you just spent 5 hours creating this data frame.
[10:27] Below is another example.
[10:29] Imagine we are trying to write a data frame to a file.
[10:31] In Python land, for example, you can write it to a pickle um or parquet or I don't know.
[10:38] Um, but imagine now your Python process crashes and now we're trying to read this thing again from disk, and it will just yell at us that there is some sort of problem with unpickling this because we ended up writing a partial file to disk.
[10:50] That's not great.
[10:52] Here's another example.
[10:55] Um, there's lots of funny race conditions in the Python data stack.
[10:58] So imagine you have two um sort of threads here.
[11:01] One one is um powering a dashboard for example and it's reading it's input data from a CSV file here.
[11:07] Um uh sorry, it's reading the CSV file and then does something with the DF to power the dashboard.
[11:13] And then we have another thread maybe that is a background refresh thread that does something to prepare the DF and then writes it to a CSV.
[11:20] You can probably imagine there's lots of funny race conditions here.
[11:23] So we can read an old file, we can read an empty file, we can read a partial file file, we can fail because the file is in some really inconsistent state.
[11:31] The right might even fail because of Windows uh treating locks kind of funny.
[11:35] Um fun times.
[11:38] We also have this uh issue that whenever we have a data science project that uh that uh we end up with these endless folders that these wild collection of variations of the same data subsets, transformations, scripts, uh all that stuff.
[11:53] And so I want to today show you a little bit about analytics on acid.
[12:00] So, there is actually so for those who don't know, Acid is this uh fundamental sort of guarantee that relational traditional database management systems give to users.
[12:12] Um, and these are I want to say rules written in blood.
[12:16] Much like the rules that govern aviation, the acid principles are something that have emerged from decades of data management practice as a good idea.
[12:25] Um, and something that you kind of want your data system or your, you know, your data environment to provide because the alternative is that you as a sort of app developer or data analyst have to sort of deal with the fallout of all these of not guaranteeing these acid acid properties.
[12:42] And you really don't want to do that.
[12:44] So I'm going to go a bit over these four properties and tell you how this might be useful in analytical use cases.
[12:51] So first, we have atomicity.
[12:54] Uh it sounds really cool, Atomic.
[12:56] I mean, it used to be cool in the 50s.
[12:57] Atomicity, the idea is that any change to uh to data either happens completely or not at all.
[13:05] This is pretty unheard of and this can this can kind of this can be many different changes to many different sort of data sets.
[13:11] That can be schema changes, can be all sorts of things.
[13:14] This is unheard of in Python.
[13:15] Um well, there is some protection that you don't accidentally delete a data frame if something happens in your in a transformation, there's sort of no notion of bundling changes to multiple data frames.
[13:26] But in databases there is.
[13:28] So here's an example.
[13:29] We create a table.
[13:31] These boxes here are essentially the transactional boundary.
[13:34] So every green box here is a single transaction.
[13:36] So we create a table customer, okay, that's pretty simple.
[13:39] And now we actually use a transactional, so the syntax is here begin transaction and commit to bound to bind these two inserts together.
[13:48] So we insert two rows, DuckDB and Polar Inc.
[13:51] And indeed, if we then run the the query, which is also happening in a transaction, um it will basically see this state because the transaction uh that inserted them has committed before, and we'll see exactly those two values.
[14:04] But this is really so this is the happy path.
[14:06] Everything goes well.
[14:07] Uh again, it's cool to bind sort of things together so that you either happen together or not at all.
[14:12] We should also note that usually database management systems, when you don't say begin transaction, it will operate in something called auto commit mode where uh it's implied.
[14:20] So if you have a single statement, we have an implied begin transaction and commit before and after.
[14:28] Here is an example where we have now in this table that we have just created, um uh we um trying to insert another value.
[14:40] So in this in this table customer, we're inserting another value called clickhouse.
[14:44] And then for some reason, we decide that's not what we want.
[14:47] So we can roll back.
[14:49] We can actually not make this change happen.
[14:51] And if we run the query again, then uh you will see that the value has not been inserted, right?
[14:56] So this is a user, this is a user uh sort of triggered rollback.
[15:01] I will show an example of a automatic rollback in a bit.
[15:05] This of course is really interesting also in things like uh analytics.
[15:10] So here, so the previous examples were kind of from the textbook of databases.
[15:13] Here's an example that's a bit more interesting.
[15:15] So here we're creating a table um in uh DuckDB, you can create a table basically directly from a CSV file like so you say create table people as select star from people.csv.
[15:29] And now we run another transaction because we now maybe want to modify this database a bit.
[15:34] So we start a transaction.
[15:35] We say for example, we update uh the the table to remove all the values where the age is nonsensical minus 99 because that's actually meant means null.
[15:44] And now we run this delete where uh the name is not equal to a non-existent name.
[15:48] And oops, we just deleted all the rows in this table.
[15:51] However, it's not a problem.
[15:52] We're in a transaction, we can just roll back and nothing has happened.
[15:57] The previous change hasn't happened, this change hasn't happened, and we're back having our table as it existed directly after the load.
[16:05] This can be glued together to do some interesting things.
[16:08] Like, for example, here, we have a bit more complex transaction that creates a table.
[16:12] So in DuckDB, the table creation is also transactional.
[16:16] So we create a table in this transaction.
[16:18] We copy again a bunch of entries in this in this table from a CSV file.
[16:23] We do this update again that uh that sets some values to null that makes no sense.
[16:28] And then we have this interesting sort of hack here where we can actually have a case statement that will trigger an error whenever the count or sort of the number of rows in this table isn't 1 million, which you know, let's assume that's the amount of rows we expect from the CSV file.
[16:45] So that's really cool because that means that whenever this check fails, an error is triggered and the commit which is after it will actually not go through the transaction will be automatically rolled back.
[16:55] I will show more examples about that in a second.
[17:00] The second one in acid is the consistency.
[17:04] Um so the consistency basically means that we can have constraints of our database that uh um basically restrict the kind of data we can have in them.
[17:13] And this is something that's frowned upon for some reason in analytics because it tends to make things slower.
[17:19] But DuckDB actually has a state-of-the art uh index uh implementation finally also called the art index.
[17:25] Um and that actually is optimized for analytical use cases and that can make checks on these on these uh consistency uh conditions quite efficient.
[17:37] So here I'll have an example.
[17:38] We'll create another table.
[17:39] This time we have a primary key, for those of you who know databases, this is pretty obvious, but a primary key basically says that the ID column can only have unique values.
[17:49] Okay.
[17:50] So we insert into the value into the customer, uh we have a value DuckDB labs, we try to insert another value.
[17:55] This time with the same primary key, the same 42, and that will be automatically rolled back.
[18:01] This is also very useful in analytics.
[18:03] So for example, we create the same table.
[18:05] And now we copy a bunch of customers into from a CSV file into that table.
[18:09] And let's say for some reason, we accidentally try to load the same table twice.
[18:14] It has certainly happened to me.
[18:15] I press like up and enter again, and then everything was kind of screwed up.
[18:19] But in this case, this cannot happen because again, the constraint error will prevent this transaction from completing.
[18:27] Very nice.
[18:28] The next uh I is isolation.
[18:30] So I had this example before, um where we have this dashboard that's trying to show some data to to a user.
[18:38] And then have another thread that is running the the the refresh.
[18:41] So DuckDB has this uh multiversion concurrency control that's specifically designed to make analytics go fast and I'll show in a little benchmark later.
[18:50] But basically means that these two things can happen completely isolated from each other at the same time, uh in parallel, uh and it will uh uses a scheme called optimistic concurrency control to not so slow down either of them.
[19:04] And if there's no conflicting changes being made, both transactions can complete.
[19:07] And also ensures that each transaction sees a consistent state of the database.
[19:13] It means that you don't see partial changes from another transaction.
[19:16] You don't see tables that are created in another transaction, but haven't been committed yet.
[19:20] These kind of cool things, right?
[19:22] And you also see never see any changes that have been rolled back.
[19:25] It's really cool feature.
[19:28] And finally on the D, we have durability.
[19:31] People don't really think about durability all that much, uh because hardware has gotten super reliable.
[19:36] Uh most of the time data frame people store data as CSV files anyway, and just eat the performance hit to load from scratch every time the script runs.
[19:44] So if you do that, and your hard disk doesn't break, then I suppose the data is somewhat um durable.
[19:51] But I've shown an example earlier with the pickles where this goes wrong.
[19:54] Here's another example where this goes wrong.
[19:56] Here I have a short Python script.
[19:58] Um, so I'm creating uh DuckDB instance again, that runs into in your process, really cool.
[20:03] So you don't need a server or anything.
[20:05] But now I'm running two transactions.
[20:06] I'm creating a table, excuse me.
[20:13] I'm creating a table.
[20:15] I'm inserting some values into it.
[20:17] And now, I'm and these are auto commit, so these are now committed.
[20:21] And now I'm beginning a transaction with the dot begin.
[20:24] And now I'm inserting another row into the table.
[20:27] But then something bad happens.
[20:29] I'm just simulating here with the OS.
[20:31] kill, but basically my computer crashes or my process goes down or something like that.
[20:35] And there's in DuckDB, three things are now guaranteed.
[20:38] First, the table exists.
[20:41] Second, the first value DuckDB labs has been written to disk and actually fsynced, so the caches have been flushed.
[20:47] And the third thing that's guaranteed is that the second value Wilbur the duck is not part of the table whenever you restart reading the database and never will be.
[20:54] So that's a really cool feature.
[20:56] Now show you an example why this is cool, why this is important.
[20:59] This is just an example that I ran in myself.
[21:02] Uh so uh here is a this is a picture of a hardware device I have at home and it's kept breaking itself.
[21:08] And I found lots of people that had the same problem happen to them.
[21:12] Um and uh I after some investigation I found that just due to MongoDB not actually persisting changes durable to disk and whenever the power failed, it would sort of break the internal databases.
[21:24] So it's just not what you want.
[21:25] You want the database or a system that is acid, um so that you can be safe.
[21:32] Finally, I want to talk about performance.
[21:34] Um so performance is something that's in my opinion a bit overrated in terms of analy in terms of data analysis.
[21:42] We have a lot of people, you know, screaming on the internet that X is faster than Y.
[21:46] And we uh at Team DuckDB generally don't want to do that because while it's, you know, it's nice to be fast at benchmarks and sometimes we're fast at benchmarks.
[21:55] Um people tend to underestimate the sort of the end-to-end sort of cost in running things, right?
[22:01] You have a user uh sitting in front of this computer and if they are blocked for 5 minutes because your syntax is weird, um that's going to be much worse than if your CSV read is 10% slower, right?
[22:12] But regardless, um people also think that having transactional guarantees actually slows things down unbearably.
[22:22] So, you know, in order to kind of dissuade those fears a little bit, I've run a benchmark two benchmarks actually.
[22:27] Here, um let's first let's look at raw read performance.
[22:32] So here is a CSV file, it's a fairly large uh data set.
[22:34] It's about 1 gigabyte.
[22:36] Um and here we have several systems.
[22:39] We have Postgress, Pandas, Polar and DuckDB reading um basically this file into a table.
[22:45] Um there's app this is not an apples to apples comparison because Postgress and DuckDB actually, you know, write these the contents of this file back to the disk, uh, you know, in a transactionally safe way, uh, you know, binary compressed type safe with statistics and schema, everything we flushed all the caches.
[23:04] But you can see that while Postgress indeed is slow, it's not an analytical system, it's not surprising.
[23:09] And Pandas and Polars are doing pretty well, but DuckDB even though it has to do all these extra things to be transactionally safe, to allow you to change your data with confidence later on, it is also still the fastest.
[23:19] So that's a nice thing.
[23:22] Um, here's another example of changing stuff.
[23:25] So here we have another benchmark where we create a table.
[23:29] It's a synthetic example.
[23:31] The table has 100 columns, they're all randomly uh ran like these are just series of data, it doesn't really matter for this example.
[23:38] But we are simulating something that's actually quite a typical change in analytical cases where we're basically changing in these updates down there, we're changing a single row and we're changing the percentage of uh rows that this change affects.
[23:52] So the first one, the first update affects 1%, the second update affect 10% and the third affects 100%.
[24:01] And if we look at the result here, um again, we are used for transactional systems being slow, but DuckDB has the world's first analytics optimized multiversion concurrency control.
[24:11] And so if you look at this result, you see that DuckDB is basically unimpressed by this change at all.
[24:16] And other systems like Postgress or uh Tableau, for example, will, you know, use uh 70 seconds for this change.
[24:25] It's quite a lot.
[24:27] Um if you want to read more about this, uh here I want to point you to two recent uh blog posts uh that uh me and Mark, the co-creator of DuckDB have have written.
[24:37] Uh first one is called changing data with confidence and acid and the other one is called transactional analytics optimized concurrent transactions.
[24:45] If you want sort of hear more about uh the examples that I've just shown with the performance.
[24:51] And that's kind of it that I had or what whatever I had.
[24:53] So I was taking talk about changing data uh with confidence and asset using DuckDB.
[24:59] Um, in my opinion, transactions are a really great idea for analytics.
[25:03] Um, and um, yeah, and because of that, DuckDB has implemented world-class transactional uh sort of handling to support analyst in their tasks.
[25:13] That was it.
[25:14] Thank you very much.
[25:15] Happy to take questions.
[25:20] Brilliant.
[25:20] Thank you Hannes.
[25:21] Uh, I will show a question on stage now.
[25:24] Uh, very interesting talk.
[25:26] Um any architectural advice on how to use DucDB for an on cloud AML workflow which naively has data lake persistence such as Azure containers or AWS S3 buckets?
[25:38] Oh, thanks for reading the question.
[25:39] It gave me some seconds to pause all the words in the sentence.
[25:42] Um, so, um, yes, I do have some advice.
[25:47] Um, so if you have data lake persistence on on buckets, what I've seen people doing is two things.
[25:55] Uh one is you you want to run the first the first general advice is you want to run the DuckDB instance in close to this bucket, right?
[26:03] In the same cloud, in the same in the same availability zone, things like that.
[26:08] Um I've seen two things.
[26:09] People run VMs and then uh have DuckDB directly talk to the buckets in that VM.
[26:15] And that works really great great for sort of interactive use.
[26:18] Um what I've also seen is uh people uh using things like lambdas, which is I think kind of cool, to basically run queries because DuckDB is so lightweight, you can pull it pull an instance up within a couple of milliseconds and you can do that in a lambda and then your lambda can do the data transformation for you and write it back to object store.
[26:40] So what I've So that's I think and maybe I have a third advice, which is if you're running really like heavy queries, it really pays in a in an on cloud uh environment that to have local SSDs connected to your VM and then putting your data database files on that because that just eliminates a bunch of network round trips.
[27:01] But yeah, it's a use case that's pretty common, I would say.
[27:07] I hope that answered your question.
[27:09] Cool.
[27:09] Thank you.
[27:11] Uh, we have another question.
[27:12] Do you have any use case or stories where DuckDB was used for integration tests CI/CD to speed up the total time of run?
[27:19] Time run.
[27:20] Yeah, this is super common use case actually that we see.
[27:22] So we see lots of people running DuckDB to kind of work out their DBT workflows or work out their analysis steps.
[27:32] or also run like basically the unit tests for their transformations locally because DuckDB runs, you know, anywhere, you can run it on your local laptop.
[27:40] You don't need like, you know, special hardware to run it.
[27:43] Um, and we have seen that quite a lot that people use DuckDB for local testing.
[27:48] What they then usually realize after doing that that actually that it's DuckDB is usually good enough to also run on their full data set and then very often that actually translates into a production use case afterwards.
[27:59] But uh to answer this specific question, yes, we see that use case quite a lot.
[28:06] Uh, I'm actually curious uh just to follow up on this.
[28:09] Um is there any uh things to watch out for if, you know, for example, originally we're using MySQL and then now we're using DuckDB for testing, you know, like is there any, yeah, like schema differences, anything like that?
[28:23] Yeah, uh due to the unique way SQL is standardized, uh um there are differences in SQL syntax.
[28:32] So DuckDB mostly follows the Postgress syntax because it's such a widespread sort of dialect.
[28:39] Uh we have extended it a little bit to make it with quality of life features.
[28:43] So we have some blog posts, if you're interested the friendlier SQL with DuckDB, I think you can find them.
[28:48] Um but so that's a gotcha when you're running when you're comparing like running a different SQL dialects, that's sometimes a bit annoying.
[28:57] There are systems that are trying to abstract this.
[28:58] One one I like is Ibis for example in Python Land that uses SQL Alchemy under the under the hood to basically construct SQL queries in a system independent way and we have support for that in DuckDB, so you can use that.
[29:11] There's also other, there's also other toolkits allow you to do that.
[29:15] But it's some like the difference between MySQL dialect and Postgress dialect are pretty pretty strong, I would say.
[29:21] So you would have to watch out for that kind of thing.
[29:24] Yeah.
[29:24] We um we are actually have a project right now to um extend DuckDB's parser with more dialect support.
[29:31] So maybe that's something uh I would maybe check again end of next year or something, we should have that.
[29:39] Okay.
[29:41] Uh, brilliant.
[29:42] I think uh that's about time for questions.
[29:44] Uh thank you everyone again for joining the session and thank you again Hannes for giving us a very uh informational talk.
[29:53] Um just a little bit of housekeeping notes.
[29:56] Uh if anybody wants to continue the discussion, please join the Discord channel uh for Pi Data Global.
[30:03] And uh the talk will be available on uh Airmeet right after uh this uh uh broadcast.
[30:12] And also uh later on it'll also be available on YouTube.
[30:16] Thank you.
[30:18] Thank you.
[30:19] Thank you.