Blazing-Fast Python in Your Database: Unlocking Data Science at Scale with Exasol

Speaker 1 [00:07]

We're going to talk a bit about how to get blazing fast Python inside your database. Before we start, let's do a quick introduction of me, like who I am. My name is Alexander Stiksen. I was a former life. I was the founder of a database company called Realm. Which later got acquired by MongoDB and then I spent a lot of years at MongoDB working on their edge and mobile strategy. And now I'm at Exasol, which is a German database company that makes one of the world's fastest databases. Pretty cool. So I've had a long long history in databases and it's most of it in the US so it's kind of fun to be back in Europe and actually do a European database Because we actually pretty good at doing databases in Europe. Most people don't know that but We have some really really strong universities and we do some really really cool research So, what we're going to go through today is talk a little bit about how to scale data science. Right? Everything is easy when you do it small. How do you scale it to really high scale? We'll talk a little bit about how challenging is it, how to use the solution, and then we'll talk a little bit about XSL, of course, and the components that we use to enable this. A lot of kind of technical components. We'll go a little bit into tech, even show a little bit of code, make it actually work. go into a real use case where we'll see how to do this kind of prediction and machine learning at a really high scale from a real customer. And then in the end, we'll have a bit of chance to do some questions and Q&A. Cool. So what is the challenge, right? I mean, again, everything is easy when it's small. When you are starting to do machine learning and if you're just doing a POC, you're just experimenting for yourself, I mean, it's not too hard. There's lots of good libraries out there. You can sit locally, you can work, and you can put your data into Polar, or DocDB, or Pandas, or whatever, and you can use all the standard open source libraries. And it's all fine. It's a great experience. You can get things running quick. The question comes, what happens when you have to scale this out to a massive scale? When it's not just a little bit of data, but just have so much data that there's no way it's ever going to fit on a single machine. And you have to do so much compute that you can never do this on a single node at all. That's really where the interesting question comes in. That's where the rubber hits the road, as I say, right? That's where things get really interesting. And this is really the area where XSL is working. XSL is, again, a super powerful database. And for many use cases, it's probably too powerful. Again, DocDB is an amazing database if you just want to do ad hoc work. probably want to use something like that but if you really really want to work with huge amount of days you need like an industrial strength tool you'd want something like xsl so so what is xsl right so xsl is is an like a really powerful analytics engine so as you see in the middle there you have the the in the database which is a what's called an mppp database so it's that stands for massively parallel processing And it allows you basically to take a workload and spread it out across as many nodes as you need. That's a really, really powerful thing to be able to do. But of course, that doesn't really help much if just in itself. It's like, what are you actually going to do with that workload? And the cool thing with Excel, because it is a database, it allows you to orchestrate all that using SQL. So you can use SQL and you can use that to control, but SQL is super powerful. You can actually structure everything. You can work with all your data in one go. But then we also allow you to bring in native code. Because SQL is awesome for everything you can describe declaratively. But the moment you actually want to do something iteratively, which you really want to do in most machine learning context, SQL hits its limits. But then it's really cool when you can actually use a language like Python. And this is what we allow. You can put Python directly into your SQL and run it. and it will be automatically parallelized across the whole stack. So if you see this, I mean, we don't only support Python, we actually support, you can interface with database using Python, but you can also do R, you can do Java, Lua, there's a lot of options, where you can all intermix, mix and match that together with the SQL, which is a really, really powerful capability. And on top of that, of course, you can build all kinds of applications, and especially now, we're gonna talk about predictive analytics, which is one of the things that are really powerful, why you really want to use machine learning inside the database. And again, you can get your data from anywhere you want. I mean, whether it's in a data lake, in a lake house, coming from some streaming source, anything. You can just pull out the data you want and work with it. So what are the key features that allows this to be possible? Because you say, well, can all databases do that? No, they cannot, because there's a lot of key features that you want that are AI-focused. So the first one, as I mentioned earlier, is the idea of massively parallel processing. So really put emphasis on the massive part, right? That you want to be able to spin up a lot of nodes, and you want to be able to use every single computing resource across these nodes. So it's not just the nodes themselves, but it's also like every CPU unit inside those nodes you will use individually to their max capability. So being able to distribute all the work out across every single resource in your system, and do that in a simple and easy way easy way that you don't really have to know about. That's really the power of an MPVP database. So you just describe in SQL what you want to do, and it will optimize it and find the optimal way to spread this workload across every available resource. The other thing that's really powerful is what's called UDFs. So UDFs stands for a user-defined function. And a user-defined function is basically allowing you to use real code, native code, in your SQL statements. And, of course, for this audience, the most relevant one, of course, is Python. This is the interesting one. And the really cool thing is that you can actually use Python. It's real native Python. It's not, I mean, I've seen other databases say, well, we can run UDFs. But when you look at it, they're, like, running Python, but they're compiling it down to, like, WebAssembly and then running it on top of the JVM. And it's, like, a horror show. With Datastore, you run, like, real native Python. And that also brings in the next thing, because you say, yeah, that's cool. I can run Python. but I have all these libraries I need to use. I mean, how do you do that? And we pre-ship with all the standard libraries you would want to use. But if you have very specific libraries you want to use, we allow you to make your own script language containers. So you can build a full container that contains all the libraries you need. So if you have really complex libraries or you have big data models, all the kind of things that you want to put in, you can actually put that into a custom script language container, give it to the database, and it will be distributed again across all nodes so it's available all over the place. and you can run all your local code and anything you want basically and we do that because it's not just since we are a distributed system it's not just a database there's also something like a built-in file system called the pocket file system that allows you to distribute files across the whole system so let's say you have a big machine learning model could be like gigabytes of data you can actually put that in the pocket FS and all notes will have access to it instantly in memory available for all of them and it's all automated for You don't have to think about it afterwards. And these four things, when you actually put those together, that's really what adds the power. I mean, if you had only one of them, it would be good, but it's really the interplay of all of them that allows all this to happen. So how this ties together into the architecture. So you have, again, the database itself, and it has the UDF framework that allows you to run these functions. You have model management. Again, in PocketFS you can put up models and you can version them, you can have them shared across all nodes. And then all that combined with the MPVP query engine that allows you to distribute all the work across all nodes. And then we have something called AI Lab, which is really a Jupyter environment. So you can sit in a notebook and you can control it all from your notebook environment. And that's, you're familiar with everything, you can sit in Python, you can work again with all your third party tools and libraries, and it all just feels natural. And then you can just pull data in from everywhere you want and distribute it out and work with it in parallel. So let's dive a little bit in and look at a real use case, because this is all kind of theory, and you're like, oh, that's cool, but how would you actually use this? So we had one customer here that had a really interesting use case. So this was a major US telecom provider, and they have antennas and base stations all over the US. They literally have like 120,000 towers, which is a lot to manage. And per tower there's multiple sites, which means like multiple small base stations on it. And they're collecting, all these towers are collecting historical data on how much load there is on the tower. So basically how many people are using these towers, how many phones are walking past, how many people are having conversations. All this data is being recorded on every single tower all the time. And they keep around like 52 weeks of history of this. This is a lot of data. And so they keep all this, and basically this gives them a window saying, how has the load on these towers been over all these weeks? When are the peaks? When are the lows? Are people using their phones more on the weekends than they do, are they using them more in the evenings? What about if there's a big shopping mall opening up or anything that all these things are things they are measuring and it's really important for them to be able to predict the load ahead of time because then they know if first of all they know about maintenance they have to be done they know how much they can allocate because they have amount of spectrum they have to buy all these things that it's really really important that they have a fine-grained knowledge of the expected load for the next weeks ahead of you the problem is how do you do that for that many towers with that much information at the same time when this is a massive amount of information and the problem is that each tower is different right so it's not enough like you say well could we just make a model and then we put in the data and you put it out and the promise you can't you basically have to make 120,000 models because each one needs their own model based on their own individual historical data so obviously that is a huge challenge and it's basically for them it was something that was almost impossible to do and when we came and started working with them they tried to do this and it was just impossible it just took it took literally like weeks to calculate and build all these models and then run them in parallel so what we did was we actually built as scripting this is basically the three steps to this and of course this is super simplified code that's as much complex but it kind of shows the principle so the first thing we did was we made a script container and actually in step one which contained the libraries they needed and this exam we use profit which is a meter library that is good for time series prediction but so you create a script container so it has the Python environment and it has these libraries it needs specifically profit in this case. Then we create a user-defined function and the syntax is pretty simple, it just says it's right in the sequel, you can write it while you're doing your sequel, not on the side, and it says create a project that script that uses this new language container and we want the script to be called forecast volume so it'll make a function called that that takes a list of like data in the form of a list of charts in this case and it outputs also a little charts and it will then it just the code underneath is just Python so it's regular Python just an import profit in front of pandas it will read the input that the database gives it which is will just be the subset for that specific one single tower it will upstart profit it will do a best fit on that and then it will make a prediction basically create a new data frame with prediction for the next weeks and then it will emit that forecast again so this is the function you define and it just becomes a function that then you can use directly into in your sequel so if you look at the third step you can just do a select and say select forecast volume on the traffic from all the tower data and then you can group by ID and again of course this is super simplified they do a lot more because of course what are you I mean you get this information what are they then going to do then you're going to do analytics on top of all these towers and you're gonna allocate resources and all that stuff which all happens in sequel all parallelized which is really really cool but this is the core functionality that you can define like a Python method which is totally arbitrary can do anything you want and then you can have it parallelized so in this case it will when they run this they have tower data again from 120,000 towers and it just get all parallelized they're all running parallels across all the different nodes and you get the result and then you can do all the rest of your analytics and your decision-making on top of that. So you see, so what are the benefits of this for you? I mean the thing that you can use your database is the main thing, right? You want to do your data where you're processing where your data is. Because you can say well you could do this on all kind of other ways. You could do it manually, you could try to spin it You spin up a lot of nodes, and you could try to run it with Spark and everything complex. But the fact that you have all your data in one place, all this historical data is in the database already. All the analytics they have to do on the result is something you described in the database already. So when you can actually create your models and execute your models and get the results from them, the predictions in the database, it's all in a single architecture, which massively simplifies your whole stack. You can run everything within the same architecture and everything gets parallelized automatically without you having to think about it. It's basically self-optimizing. And it also means that you can leverage all your hardware. You have so much hardware there around that with this database you can actually spin up all your nodes. And in this case, they run on-prem, so they have their own hardware, which means they can use basically every single resource in the system. That's a whole separate discussion, but for a lot of these cases, it's more efficient to run on-prem than in the cloud because it gives you much more fine-grained control of the actual hardware. But of course, that's kind of like a trade-off between cost and ease of use. But just the fact that you have this much lower complexity, that you only have one single tool you really have to work with, and at the same time, you meet people where they are, right? I mean, if you're a Python developer, you should be able to use your Python in this context. doing alpha, like statistical analysis, you should be able to use that. Why not? You should be able to pull all that in and use it within your database in the same tool you already have. And then of course the reason this becomes so efficient is because since you're doing it on your existing data, you don't have to move data around. The most expensive thing when you're to work with like giant amounts of data, terabytes, petabytes of data, is moving the data. that's why things gets really really expensive and slow right but if you can work with the data where it is in the database itself then there's no copying of data everything gets super efficient and fast so the super important this case so I mean this this just I mean it basically as an analyst it empowers you people to do things they couldn't do before usually they will be really afraid of cost and running these kind of big jobs how long time it will take how much work will it be this makes it really simple and for this customer it was pretty crazy I mean they basically went from being it was totally impossible to do this because it took so long time that the results were out of date when they got them and now they went to the every week they can just run a new forecast and they instantly get result and like okay all the towers what are they predicted notes for the next weeks and they can use this to like buy more spectrum and like adjust the the energy for the towers and everything which is a really really really big step. So I mean a super super powerful solution for this. So I mean to open up for a bit of Q&A here, I mean I'll just summarize that. I mean it really helps having AI in your database. Being able to take your data and work directly with it, with the freedom of the language you like and you love that actually can express these things. This is a huge huge step. Yeah and Exosolid is a really really powerful way. It's one of the only ones that allows you to do this at this scale and then such a simple easy approachable way. Cool so I will open up for questions. That's a question down there in the end. So that's a really good question, knowledge graphs, because XSL is not a graph database.

Speaker 2 [17:55]

Should I? I can say again. Thank you. Thanks for the great talk. My question was, knowledge graph based RAG systems for RLMs are a hip thing, hip topic. So what is the proposed solution from Exasol for knowledge graph based databases?

Speaker 1 [18:14]

That's a really good question. Yeah, because it I mean with XSL is a relational database. It's not It's not a graph database So it doesn't have any kind of native built-in graph capabilities Sadly for that you have to go to a real graph database like a new 4j or something But that's it. I mean most Not a graph problem can be expressed in sequel. I Mean you can put in triples. You can do everything you want to make something that looks like an IDF framework in SQL. It might not be the most, I'll say personally since I have done this, that it's not the most nice syntax for it, I think, I mean SQL was not built for that originally, clearly, but it's still surprisingly powerful, because when you have built it up in SQL, you still have the freedom to use the entire SQL language on top of it, which often gives you a lot more capabilities than you have in a native graph database. So I think, as an answer to your question, that it will very much depend on the type of use case you have the knowledge graph. There are certain knowledge graphs that will be a really good fit that you can actually express in SQL with really, really good results. And there are other knowledge graphs where you look and say, wow, that's probably not going to be the best fit. You will have to go to a real graph database to really get the best out of it. Did that answer the question?

Speaker 2 [19:37]

Yes, but probably then hybrid table based approaches because single reads maybe

Speaker 1 [19:37]

Yes. OK, yes, there you go.

Speaker 2 [19:46]

So, since it's single reads, oftentimes, maybe it could also be hybrid tables, not necessarily OLAP kind of column-based tables. Yep.

Speaker 1 [19:58]

Yep, yep. I mean that's true. I mean again, XSL is very unapologetically an OLAP database, right? It's all about blazing fast analytics. If you need like faster writes or something, you probably want a transactional database. Of course, transactional databases are a totally different breed. That's a question over there.

Speaker 3 [20:28]

Thank you for the presentation. Whether it has the capabilities to work with geospatial data?

Speaker 1 [20:38]

Yes, it's a core feature in XSL to work with geospatial data. So we support all the geospatial features of the bot and SQL all along. So any kind of geospatial use case you have, whether it's point lookups or geographical regions, polygons, the whole thing, it's all supported directly in the cryo. And you can, again, you can intermix that with your Python and with your machine learning, everything. So especially in this case, of course, where we had all the base stations, I mean, they all have locations. They all have stuff that is being used as well. And it's important, like where they located, like how close are they to each other? All those things are actually stuff you pull in to the analytics.

Speaker 3 [21:20]

So, in the memory, whether it stores this geometries in a blob or there is this native data types which are there for geospatial data, it is sourced like them.

Speaker 1 [21:35]

Yes, so if you see if I let you says geospatial down there and then on the all the way to the right

Speaker 3 [21:35]

Yes.

Speaker 1 [21:42]

And yes, we do it natively support data types for geospatial. So there are points there polygons all the stuff in the database So that's natively supported

Speaker 3 [21:51]

Thank you So we have lots of questions on Slido So the first one is can you provide some numbers on performance for example inference time etc?

Speaker 1 [22:10]

Good question. Yeah, I mean in this case, I mean it's hard because it's it's at scale, right? So Since the the inference is run in Python. So the if you look at their single Like prediction run here, of course, it'll it has the same speed as if you just run profit natively but the things that is get spread out over all the nodes, so you run like essentially like 120,000 of them at this in parallel and I know for Specifically for this customer this took actually took a few days for them to run all these Which was a rundown from having took several weeks to do which just talks about like how much data that was working with I mean, this is not a there was not small workloads So it used to take multiple weeks and they got it down to a few days Which was really what they needed because it has to be if to be relevant It has to be in within the week. You actually are doing things so they were able to do it in less than a week But this is a code we took like a massive workload, it was not a small thing, yeah.

Speaker 3 [23:12]

Cool, thanks. So the next one is how performance is accessible for real-time analytics, for example instant aggregation of large data used in analytics where multiple results are needed in seconds

Speaker 1 [23:28]

See, this is a fun one. So just going back to the beginning where I said that Europe has some hidden gold here, right? Because I've been in this business for almost my entire career, and mostly in the US. And you say, why did I come back and join someone like Exasol? Because Exasol is literally the world's fastest database. And you can say, everybody says, well, the world's fastest database. Everybody claims that. I mean, that's, who wouldn't say that? But the thing is, there actually is a benchmark that is well audited and documented. It's called the TPC benchmark, which is, there's a separate council that does it. It's not like something the inventors themselves. You can submit your database to it and they will test it and you'll audit that it's done fair with the right hardware. And XSL has been dominating this benchmark for the last decade. I mean, if you go to TPC homepage, you'll find XSL there and you'll see that they, for the last decade they have been totally dominating that benchmark against everybody which is really crazy for like a German database right that like nobody knows about because it's hidden in Germany I thought that was major cool and then I mean and you'd see this I mean we actually come to our booth if you have a small thing but we also show that even if you just go to a single node because of course the cool thing about XSL is you can scale out massively that's that's a whole selling point but even if you go down to a single node performance and you start adding up the data we did a benchmark where we benchmarked against docdb and polars because those are of course what people think okay those are the most fastest thing you can use on a single node and it's true when you work with small data sets but when you start scaling it up and we ran the tpc benchmark official but we just scaled it up to like 25 gigabytes and where it's really complex work i say that's kind of as a question comes like what to do when you have tons of joins tons of analytics all that stuff mixed in it tests or everything aspect of the database and we were literally like 10 times as fast as as DocDB on a single node. And you can imagine that when we scale up, then we can scale out to any number of nodes on top of that, right? So that's really where you see the difference. So I think actually this is one of the kind of hidden gems of Germany, that you actually have this technology, I mean, Excel is not a new company, it's existed for almost 20 years based on university research. Super, super optimized technology.

Speaker 3 [25:40]

Cool. We are at time, but maybe we can do two more questions because there is actually three more. But let's maybe two more, and if you could just give a very brief response.

Speaker 1 [25:50]

Brief response. I will try to be brief. Cool.

Speaker 3 [25:52]

So the next one is from Florian asking is the code underlying the access all database open source if not, why not?

Speaker 1 [25:59]

Yeah, no, it is not so this is not an open source project seller. It's free though. There's a there's a community edition That's totally free and of course a set service with free trial But you can download the community edition. You can just try it out for yourself You can use it as much as you want, which was fine for a single node usage But the actual product it is a commercial product It's not open source Even though there's a lot of open source components if you go to our github You will find tons of stuff all the language containers and everything, but the product itself is closed source

Speaker 3 [26:27]

Thanks, and let's get one more. I took MPP to mean that SQL queries could be split up across the nodes How would that work with UDFs when you want to split the work across? nodes and combine

Speaker 1 [26:40]

Yep, and that's true. I mean so MVP means that the work the workloads basically get split up across nodes and Distributed and of course, this is a very dynamic process. There's an optimizer that finds out how to distribute things So when you bring in UDFs, they will also be split up So every UDF will get a small subset of the data For example in this case like every UDF worked with one single tower It just got supplied the data from one single tower So that will UDF will be run on the node that has the data for that tower So it will optimize so it runs the most optimal location for that data and so you get the best result. So the UDFs they get distributed just like the SQL.

Speaker 3 [27:24]

Well, thank you very much. I think we'll close it there. Cool, guys.

Speaker 1 [27:30]

There's a final thing if you want you can go and join our challenge where if you sign up for our SaaS that's free. So you don't worry about you're not getting a bill suddenly and Then you navigate to as a sample worksheet and you does it once called the challenge And if you can complete that it will take a little bit of sequel skill a little bit of Python skill Then you'll be in the raffle to win this cool Lamborghini Which is actually really big. It's really cool Lego car, which me as a Danish person. I kind of like them We are sponsoring Lego a little bit And you can come down and see it. Also, if you have any questions, we have a booth down in the main hall down there. So you're welcome. I'll be down there. You can come down and ask questions. And if you just want to chat with us, if you want to see things in action.

Speaker 3 [28:14]

Cool. Thanks, everyone. So if you have extra questions, you know where to find our speaker or you can follow up on the Discord as well. So thank you very much. And just as a polite reminder, as you're leaving, please remember to take your empty cups, bottles and whatnot.

Speaker 1 [28:36]

Well, thank you guys.