Thank you for being here.
We have Mark Andre Lemberg, who is giving a talk on Dr. B. Thank you Mark for the talk.
Yes, thank you very much.
It's me giving a talk instead of introducing others.
I'm going to give a talk about Dr. B. How many of you know Dr. B or have heard of
Dr. B before? I have some amazing, very good.
By the way, I'm not affiliated with Dr. B or the project or the company.
I just love the project that's why I'm giving this talk.
A bit about myself. I'm Mark Lemberg. I've been around for ages. I don't even want to say how long.
I am one of the core developers. I implemented the Unicode string implementation that you have in
the company. I've been around for the different foundations for quite a lot.
What's the motivation for the talk?
You're sitting there. You're getting lots and lots of data from somewhere.
Very different sources. You want to put everything into your database somehow.
You need to somehow organize all your data that you put into your database.
A good way to do this is what's called ETL. How many of you know what ETL is?
Most of you, excellent. Just going to go quickly over this. ETL is basically the standard
business term for taking data and putting it into a database or convert it from one form
into another format. ETL stands for extract, transform and load.
Where extract basically means you take the data from somewhere, then you transform it,
and then you load it into your system. Nowadays, it's not done like this anymore.
It's actually done in a different order. You first extract the data, then you load it into
some database and then you do the transformations in some database in the
or maybe the final database because that's a lot more efficient.
Databases are simply very, very good at manipulating data.
And then once you've done that, you can start processing.
So what's stuck to me? Many of you already know what ductuby is for those who don't.
Dactuby is kind of like similar to SQLite for transactional data loads,
so over TP. But ductuby uses an approach that is more focused on analytics.
So in general, the picture is that you already have lots and lots of data in your database,
and you want to run fast analytics on them. For example, run reports on them,
get the data out in certain ways, maybe transform the data in certain ways.
So that's what ductuby is very good about. Also ductuby originated from a
or let's say has an academic background to it. So there are lots of things in ductuby,
which are really brand new, which are the latest in what's there in database technology.
So you get really, really good technology and implementations to run your code.
It's then like SQLite, it's a database that actually runs in your process.
So there's nothing extra to install. You just do a pip install or UVat,
and then you have your ductuby, and you can immediately start using it.
So that's very nice. It's column based like most old app databases are nowadays.
It uses Apache error for speed. Apache error is very much focused on not copying things,
so that gives you a lot of speed. It has SQLite to standard language, and it uses the Postgres
dialect of SQL. So it's very easy to get started with if you have a bit of knowledge of Postgres.
If you don't, I can recommend just talk to your favorite AI, chat tool,
and they are very, very good at writing the SQL for you.
It's single write multiple readers, so it's not, you cannot write to the database for multiple processes.
This is kind of like the effect that you have because it's like a database that runs in your process.
So it's not a distributed database, and of course it has very, very nice Python support.
So I'm going to go through typical steps that you do in ETL, first to generate ones,
and then I'm going to focus more on the ductuby ones. So the first ones, of course,
you need the data from somewhere. You read it from all the different sources that you can think of.
I listed some sources here on the slide, typically what you get is you get data as CSD files or pocket files.
You download them from somewhere, but you can also have data sources that are completely different.
For example, you can read an RSS feed, or you can go to web page and scrape it and extract data from it.
It doesn't really matter where the data is coming from. You just need to first get it onto your system somehow.
And once you have it, then you can start preparing it for actually loading it into a database.
So this is something that people sometimes forget. It does make a lot of sense to prepare your data load,
because then you can actually use tooling that runs a lot faster than your standard time follow-up where you basically load the data one by one.
And so what you need to be focused on is you need to make it so that the data is already in a format that the data base tools that you have can easily read.
So for example, for ductuby, that would be reading CSD files, that would be reading parka files.
It also was other formats, but these are the most common ones that you typically find. So you have to prepare everything, make it nice and clean.
This typically requires going over to the complete data set that you have.
And because you don't want to load everything into RAM, that usually doesn't work if you have a lot of data, you need to do that line by line.
So you basically stream your data through your application and then prepare it.
And there's a very nice package that can help you with this. How many of you know Polas?
Few, if you don't, and you need to do this, then do have a look at this. Polas is very, very nice for processing data.
And it can actually do quite a bit.
But the thing is with Polas is it's it's geared towards working in memory mostly, right?
It's fast when it does in memory stuff, whereas ductuby can actually then also go to disk.
And then offload things to disk and make things run even if you have data sets that don't fit your memory.
Polas is something to definitely have in mind here, but you can also do it just using a standard for loop that you have in Python.
Now once you've prepared everything, you can then run a single command and get everything into your ductuby.
For the CSE files, for example, that would be a single select and then the function read, underscore CSE.
And that would load the complete data set into your database.
You can basically use white products, for example, to have multiple files ready in one go.
This is extremely fast.
So ductuby, like many tools nowadays, well, no wrong.
Most tools nowadays are written and rust, right?
So ductuby is actually written in C++, and it doesn't fairly good job at that.
It's really very fast. It's actually faster than Polas and Polas is written in rust.
So what you can do is you can first load the data into staging tables.
If you don't, when necessary, you want to go directly into your final tables, or maybe you want to upload things into some other database later on.
Or maybe you just want to try things, and then, you know, maybe just for those tables away again, if you find that your initial load did not work.
So this is usually a good thing. You state staging tables to basically prepare everything, and then once you're done, then you can then use the final tables.
Once you have it in the database, then certain operations are very, very easy to do, which would be very cumbersome otherwise.
For example, you can filter out unended data, or you can handle missing data.
Even detecting missing data is easier in the database than using that, but doing that in Python and then, you know, read line by line.
There are always some things that you have to do.
A very common thing is, for example, you have to inspect the daytime values that you have in there.
Maybe convert them to the proper time zones, or maybe add some missing parts of those daytime values.
Inflot correction is something that you sometimes have to do.
You can convert complete data types into other data types.
You can add new columns to your data.
You can have aggregate columns, for example, added to your data, so that later on, you don't have to process these aggregates over and over again.
There are lots and lots of things.
I just listed a few of these here on the slide that you can do in the database, transform your data and then make it work better with your application.
By doing that, you gain more robustness, you regain your more performance later on.
Like I said, everything can be done using SQL and I already mentioned the LLMs.
Those are very good at these things.
Once you've done that, you have everything in your duct-to-be.
Now you can then move the data out of duct-to-be into your final database.
If you're lucky, duct-to-be is already your final database, so you don't have to do anything.
If not, there are quite a few adapters that basically can write directly from duct-to-be into that particular database.
So you don't have to go via, for example, Python again, to read everything and then put the data into some other database.
I listed a couple of databases here, very popular Postgres of course, my SQL, but they also support iceberg or data lakes.
So essentially you can get the data out of your duct-to-be very easily.
So it's not basically buried in your duct-to-be.
Then once you're done, you remove those stages and tables again and you can then go to the next step.
So let me see how much time I have left it.
I'm actually speeding through this talk.
So I added a few extra slides here for advanced ETL, but before going into those, do you have any questions on these things?
How many of you have used duct-to-be for doing ETL?
A few.
So you should definitely give it a try.
It's very, very easy to basically use and get into your Python process.
Yeah, there's a question there.
Handle what?
Large databases.
So essentially there are two modes in duct-to-be.
You can work in memory or you can basically have a persist on disk.
And then if you have really large data sets, then there's an extension called duct-to-be.
Which basically turns duct-to-be into a data lake implementation.
And you can easily then handle terabytes of data, no problem.
Of course those terabytes of data would then be stored on disk and not the memory anymore.
But it does all the administration that is needed to then handle all these files and then handle all the data.
So the performance of these of the queries that it does is quite amazing given that it's just such a small implementation.
Right, so some guidelines to basically help you with all this and make it maybe more efficient.
So one thing in database design is that you always have to know how the data is going to be extracted from your database.
And you have to think about how you put data into your database when you know how things are going to be pulled out again.
Because you can optimize very easily this operation of extracting data from the database at the early stages of putting the data in there.
So a typical example would be that you calculate aggregates while loading the data.
So that the database doesn't have to do that anymore later on when you run your reports.
Something else that you need to keep in mind is you need to optimize for things that are done often.
So let's say you very often want to maybe you know extract user data.
Then you should focus on these queries and optimize for those queries and not maybe for example put an index on something that is not used often enough.
And both of these you know if you add extra data to make your queries run fast of course that takes more space and you have to be aware of that and so it's like it's a compromise that you have to find there.
And you have to strike a good balance between these things and there's nothing much that you can really you know I can really recommend for these things you just have to try out certain approaches and see what works best for you.
So one thing that takes very, very long in databases is drawings.
You know what drawings are? Yes, most of you so you take two tables and then you create a third table essentially out of those two tables.
Join to take very long because typically what happens is if you're lucky you can use an index for that so it runs a bit faster, but if you're unlucky it actually has to scan the complete table and then create something new that you then use as basis for your query.
And the typical thing to do there is to denormalize your data denormalizing means that you don't have too many references inside your data from one table to another.
Essentially you don't need the joints when you run the queries instead what you do is you copy the data to multiple tables of course.
That introduces dependencies in your tables and you have to be aware of that so it has to be done with you know this in mind and you have to really be sure that you know what you're doing.
But if you do and you know that these queries are going to happen and you're going to be used a lot and this can speed up your queries a whole lot.
Something else that you can do in traditional databases is materialized views.
Unfortunately, that DB isn't quite day yet.
I hope they will add this as well.
Materialized views is basically something where you essentially view is something like a select statement that is defined in sort of like the same way as you would define a table.
And then behind the scenes when you use that table the database will then run these select statements and essentially gets all the data from the different other tables that you're referencing.
And then make it look like you're actually working a table.
And when you materialize these views the database then manages the actual tables for you instead of always running these queries.
So that makes things run a lot faster.
But not day yet. So I'm pretty sure they're going to add this.
Something that people often do wrong is working with indexes.
They basically they try to put indexes on all the different columns they have in the database.
That's not a good thing to do so typically you should just start with a single index on the most commonly of course on the primary key that you have in there.
And then add indexes very very carefully one by one.
And the way to do that or the best way to do that is you run your query analyzer and you check which kinds of queries actually run.
And then you put indexes on exactly those fields instead of just randomly defining something as you might see fit.
The problem there is when you get new data into your database then these indexes always have to be updated.
And especially if you're loading lots of data into your database is this becomes an issue.
So what you typically do is you either you switch off the indexes if that's possible with your database or you remove the indexes and then have them re added later on.
So that's very important to keep in mind when you have lots of data to insert.
If you have lots and lots of data then you can petition it.
It means that you basically spread it to different.
To different locations essentially inductively you would not necessarily do this directly which then use this dark lake extension and the dark lake extension would do this petitioning for you.
So that's also something that you can try if you have too much data to handle and you want to make things still run fast.
Right, do duplicate data is pretty obvious you know we moved up duplicate that you have in your data.
If you're dealing with IoT data for example you're very often get duplicate data.
For example from temperature sensors sending things more than just once.
So this happens depending on what kind of data source you have you might want to look into that as well.
And some additional kind of things that you can do is you can pre-filter your data so you actually don't put all the data inside the final database.
But instead you keep it inductively and then just load it from inductively into your target database when you actually have a use for it.
So you don't throw the way but it just doesn't create load in your target database.
You can pre aggregate data already went into that and you can create something that's called data sketches.
Do you know what data sketches are?
No, no one.
So data sketches is a very nice technology.
It's also not so well known as you can see here.
It's a strategy to make queries that normally run very long to make some run a lot faster by giving up a bit of accuracy.
So if you say that okay, I want the total number of let's say I don't know students in a certain course.
And that across let's say your whole country.
Then normally you would have to go and account all the students in that course across the whole country, right?
So that would be like a huge and very data intense query that you have to run.
If you say I just want this number to be accurate by you know to the order of like 10%
then you can have a data sketch calculated.
And the data sketch would then not go into all the different tables with all the different data.
But instead it would do a sample of this data across all these different tables.
And then collect the samples and out of the sample it then calculates a final result.
That is not exact but it's exact to a certain percentage.
And then you can make run things run a lot faster.
If you're not if you don't need to count you know all the different in that particular case the students.
If it's good enough if you have a count that okay by 10% right.
So I am a bit quick with the with the talk so basically the conclusion is that
Dr. B makes things a whole lot easier.
You can actually do things on your notebook which you'd normally use a cloud server for.
And I would really recommend you to you know make some experiments try it out and see how it works for you.
Right.
So that's it.
Thank you.
Any questions?
Yes.
One from the chat can duct be moved the transformation before the extraction by
the query optimizing knowing the schema and a required transformation.
So like lazy reading from a pocket file.
Production for the extraction.
Yes it actually can do that.
It is very smart about these things very much like Polas for example is when you.
Right.
So duct can optimize these things.
It's smart in in reading just the data that actually needs for.
For a certain query right.
So Polas does the same thing.
It basically first goes into the data checks what data is there.
And then tries to extract all the data that needs for particular query instead of reading everything.
So that's a great optimization that you can do as well.
If you don't need to hold data from your sources.
Another way?
Other questions from the room.
Hey.
Thank you for the talk.
So when you said you said that denormalizing data would help you a lot.
And most of the time when you want to denormalize data in a database you would do that was a trigger to keep the data in sync.
Is that how you would do it also in the DB?
Or would you denormalize it?
So when you talk about denormalizing data.
Yes.
Usually when you want to do that in a database you have a trigger that will keep the data in sync.
Is that how you would do it also in the DB?
Or would you denormalize it?
It would probably do it at low time.
I'm not sure how well duct to be actually implement triggers.
That I've never done that with duct to be.
So it definitely works with Postgres, for example.
That's how you do it in Postgres.
But with duct to be, I don't know.
Thank you.
Are there more questions from the room?
Hi.
I was just wondering.
Does the DB have any first party support for these data sketches?
Like does it?
Like built into the SQL syntax?
That's a very good question.
I do a specific query for it to do something.
They may be extensions for duct to be doing this.
So I'm not 100% sure whether there are.
But their new extensions are being built basically daily.
There's a whole universe of extensions being done for duct to be.
And it's really amazing how the community picks up all these things.
It tries to make everything work with duct to be.
Because it's just an amazing project to solve things, right?
And so I'm pretty sure there is something.
Awesome.
That's really good.
Thank you.
Yeah, I just have a question about the use case.
Just to know if it's a good idea about the idea to use duct to be in that case.
So for example, for a dump and restore of database that is not duct to be,
would it be a good or bad idea?
Like mySQL, duct to be reads in it, and then stores in parquet,
Macdoca files, something, and then same for restore.
And then second use case, transforming into, for example, mySQL.
So migration from mySQL to PG, could duct to be be the ETL in between these two.
Yes, definitely.
Because it has to.
It has direct interfaces to, for example, mySQL or Postgres, right?
And it can read parquet files directly.
So it's like the ideal thing to put in between.
So basically to transform from one to another.
Thank you.
That was to talk by Mark Andre.
Give him some applause, please.
Thank you.
And then we have five minutes for switching room and off to bed.