Select ML from Databases

Speaker 1 [00:05]

Awesome. Hello everyone. So my name is Gregor. Today I'm talking about ML from databases. I'm glad that everyone is here. So the agenda for today I'm talking a bit about the background of ML. So what is out there, how you can use it, what's the advantages, what is disadvantages, then in particular how to do ML in databases um then a demo how you do that in a couch space then a conclusion at the end and obviously live demo in the third part and so on so a bit about myself so i started python developing in 2010 so during my diploma at the fachhochschule i did flight performance simulations at the german aerospace center in oberpfaffenhofen so some people might remember the volcano eruption in iceland with the a of yellow yaku the research aircrafts were actually the ones from oberpfaffenhofen and after that event they wanted some simulations about at which flight level they need to fly over the clouds or through clouds and so on and this i did with python after that i went to a device management company so they do basically the dsl router management with a python framework called soap so that was also interesting framework because they had a database which stored basically the python objects in its state so not sure if everyone has seen that before but interesting concept and since four years i'm managing the pre-sale solutions engineering team at couchbase so we are responsible for everything technically to explain customers how to use the product what they can do with us help with pocs and so on one part of the job is also going to conferences and present that so that's i'm happy to be here so background about ml so why ml so we do this since many many years as it had different names before machine learning is right now the most common one the the issue usually the customers have is that they have a lot of data a bunch of data and they want to do anything meaningful out of it so what can meaningful things be solve business problems right so you have a lot of data and you want to have something out of it might be personalized offers so the more data you have from your customers what they want how they want it and you have the profile of a customer then you can also predict what the new customer wants turn prevention that's also a very business problem so if you have an online shop for example and you don't want the customers to run away or if you have a product might be an insurance or something which offers you something you want to prevent the customer from going to the competition right so this is called term prevention if you have all the data of usage maybe he's not using it right he's not using it often enough just think about crms or something else is he using it in the right way if you know that then you can prevent them from leaving maybe with dedicated training or anomaly detection so i used to work in telco right so networks it's a very good example if you have all the log data from all your network components and that's the usual traffic and you have a lot of data out of it then you can also make estimates if something is different what is the problem or if there is at all a problem. And lots more. I think you also have maybe more infos. So how do you usually do ML? So there's a few steps you usually follow. The first is getting the training data. So everything you collect, you need to store it somewhere or it might be generated out of something so that's true usually your operational database your transactional database where you have the data inside so then very important you cannot do ml usually directly in the database you need to put the data into some other store so this is usually done with an etl job and this is done by a data engineer someone who knows the database how to extract data and bring it in the right format for the next step next step is then feature engineering so data wrangling so bringing it in the right format enriching it with other formats or other information from other systems so then also etl jobs to get the data constantly out then feature stores to have the right data in the right store this is usually done by a few people so it could be done by a data scientist and also the engineer because feature store development that's usually more maybe into the science world but depending on the system also the engineers can do it so then we have the data in the right format what do we do then we develop our algorithm and we train it and either on existing models or develop it on our own or we tune it so this is usually done then purely by data scientists next step is then deploying the model so once we have the trained model then we need to package it so because with just the model you cannot do anything usually you need to deploy then a microservice system a front end for it or an api layer which then takes the request of the information you want to do applies the model on top of it and then returns the result and this can be done by just a usual data engineer scientist or a programmer who just gets the model and uses it and last but not least um call models from an app so once you have the interface ready then you can build an application for it front end or mobile apps or any predictive maintenance system who uses that and so on. So this is usually different steps and done by a back-end engineer or front-end engineer, whoever uses the systems in the end. So pros, this is basically in the industry since forever, so this is usual workflows works very well um scale services independently also this is important because the front end is extra from the back end and also from the operational data so you can scale each individually the cons are obviously you have two data stores you have your operational data which collects the data and you have then your data where you put the machine learning models on and have the messages out right so transactional database and feature store duplicates so you can have inconsistent data for example because it's two times there you can have with the etl job and also problems because sometimes it's not trivial right you have different stores where you need to grab the data from you need to combine it into one source can happen data corruption can happen also lag can happen so if you have a time difference between collecting data and processing data then it might not be the freshest could happen then also data models if you change the data model on the transactional database side then you also need to change it on the processing side two times the work or even more times depending on how many stores you have and it's a combined effort of multiple people so you have to manage and organize the work between back-end engineers front-end engineers and different people at once so that's the complexity so concepts which overcome this a little bit is machine learning in databases so what i showed you is basically that's one database and you have it outside so now we can process it also inside so what does this database support so usually you do your ml queries then in a familiar language like sql so the inference is done using sql this is done in a few different ways so either it's built in machine learning modules so in the big clouds and the major clouds like big query or Redshift, you can put your data inside and then you have your machine learning modules you can select from their storage they built in. Then there are third-party additions to the existing databases. You can have your normal database and you plug in something above, like a translation layer. This is, for example, MindsDB. And last but not least, support for custom machine learning modules. Something you develop and you can put it into the database and upload it there. Examples for this, SQL Server, Oracle, Couchbase and Firebase. So let's go one by one how that looks like. So built-in machine learning modules. So it's very tiny, I cannot read it very well. So this is for BigQuery for example and there are some default ones. Usually depending on the use case it works very well or good enough for example linear regression binary logics multi-class logic regression k-means clustering time series and so on and also what's interesting like deep neural networks that's also interesting for learning things but mostly like linear regression is probably the common and most used one because if you have like data coming in then you can see if well that's interpolated and that makes sense or not so how do you do that so usually it's a module you just hook in into bigquery for example and the workflow is with the statements create model then you reference this one and the query is then select models the input parameters as inference from the database. So basically select which model you want to use then the input parameters can be from different tables or from input parameters and then it gives you a result the next part is third-party additions to a database so minds db is a pluggable machine learning support so you can imagine you have your running database then there's always api might be sdk might be rest interface might be odbz jdbz drivers and then MindsDB hooks into it from the top. So then the workflow is create a predictor from DB that is called there and then select the output from model where the data is blah, blah, blah, okay? The thing is, this is a model which is above. So that means you have an additional layer which is called and processing data before you get the results so that adds latency and reduces your query performance also not that flexible so custom machine learning mod methods that's the other thing so that's when you can basically create the model and don't use pre-manufactured ones you can basically just develop everything, package it, upload it into the database and then query it from within the database with the statement you uploaded. So this is the typical workflow. Usually that's done in two languages. Most of them are written in Python and some are in R. This is also why we're here. Python is basically the programming language for all these kind of machine learning models. So as a summary, flexibility for built-in machine learning is obviously limited. So that's a selected set of different models you can use. Third-party integrations also limited because they also have their own store for similar or different things. Custom ML methods, obviously there we have to complete flexibility. You do whatever you want inside. Explainability that's a nice expression that means actually if it makes sense to a human the results. So built and machine learning obviously that can also range from low to medium I mean it depends on a model on the data but it's limited to what you have as models right so it could make sense but you cannot really tune it you have to use whatever is there third-party integrations there you have a bit more flexibility of the different models because it's different databases so depending on what you choose also low to medium the highest explainability is on custom ml methods because there you you are the one to do it right so you can make meaningful results out of it because you control it and the target audience usually for the easier parts like built-in and third party are data analysts or engineers itself or just software developers because it's built in it's easy to use it's harder to use the custom ML methods, because there you really need to understand the data, how to build models, how to tune them. So that's more for data scientists. So pros and cons of the concepts. We reduce ETL for all of them, of course, because we don't need to put the data into another store. We basically process it either within or we plug something on top which doesn't copy the data just queries it over so that's good that's a pro it doesn't leave the database storage also for security reasons that also makes sense to have it on one central place and then process it there out-of-the-box models obviously if you just want to play around with the data and get some infos out of it it's easy because it's there you just selected upgrade paths for the models is also easy because they are coming from the developers of the platform so if you have like a better model you can just change it or you can update the existing if there is an update available in the store of the cloud or wherever also no external micro services are needed because usually the systems have their API might be rest interface might be SDK and so on you don't need to build anything extra so the cons flexibility obviously if you need something prepackaged it's proprietary to the database you don't have the flexibility versus building it completely from scratch yourself that's con performance is also something if you think about it if you cannot basically work or cannot do workload isolation and the micro services the the API from the actual data processing if that's all within the same then it could be that the performance is not that great especially for the pluggable ML stuff so that can actually reduce your performance and you cannot scale also explainability so if it makes sense for you depends if you do the pre-built models and applied on the data, it might not be the best fit for your data. It depends. You need to play around and figure it out yourself. So demo how we do that in Couchbase. So first question, who of you actually know Couchbase? Okay, very few. We change that now. That's good. All right. So machine learning in Couchbase. Couchbase is actually a database platform which combines no SQL database plus relational database in one system. So we store JSON documents, but our query language is SQL. So we are ANSI compliant. That means we combine the best of both worlds. Everything you know from sql like the the table structure joins multi-document transactions everything you can do from the outside it looks like a relational database but under the hood it's a completely distributed flexible json document store with no schema so you can scale individually nothing and That's the big advantage of Couchbase. So data service is basically our data store. What we have in the platform is an architecture which uses a pub-sub system similar to Kafka to distribute data changes. So if you get data on your operational data changed, insert, update, delete, then the changes are streamed to the other services. might be index might be full text search or analytics service so we do actually do workload isolation here data is individual from analytics but they're updated in real time so usually you have then your applications might be an online store data warehouse or whatever application which feeds the data and then we internally update as a shadow copy the analytics service. So, into the analytics service, it also uses SQL as inference for ML, the language is SQL, and it's using a different framework. It's using MPP, so multi-parallel processing. That means it chunks the data into different parts and lets all of the available nodes calculate data or do machine learning on top of it. So, that's great. It scales individually and horizontally. The more you need, the more you go, right? And there we can hook in business intelligence tools like Power BI, Quilk, Tableau, and so on. That's usable for data scientists and then also data lakes to analyze data like blob storage or from Azure, the equivalent. What else we implemented? Something called custom user-defined functions, UDFs. So that's Python code. you can run inside the database on top of your data. So that's called UDF. So in the demo I'm going to show is basically about insurance data. So simple example, we have data which consists of age, gender, BMI, smoking, region, and children. and then there is a quote for the life insurance for that person so that's a large data set i'm not sure if everyone knows but there is websites outside called kegel that's a huge collection of data sets so if you want to try it out yourself you can always go there load a custom data set. There's also models you can reuse to play around and get insights into data. Maybe you have similar data, then you can find something which is suitable there. I used it as well. This data set is from Kaggle and has basically the estimates. So what do I get with this kind of information? Real-time information of insurance quotes whenever you put some of the data in. So you can imagine some of the insurance companies also take ages to process this data and give you like a quote, like what is your dedicated insurance quote? But it doesn't have to be, right? So it's the same for loans, if you take out a loan, like what's your interest rate? So they have huge processing to get this, but it doesn't have to be. It can also be instant with something like this. so what will i do so i train my model in python locally then i package it then i upload it to couchbase analytics i create the user defined function and then i use sql statement to query it so let's do that so I have here my container running so also everyone who of you would like to play around with couchbase very easy it's in the docker hub so you can just create any docker container with the couchbase running and then you're good to go so I have here my folder which is containing of the data and the code so let's have a look at the data first. So this is the format of the data. Can everyone read? Is it good enough? Okay. So it's basically a CSV format. I downloaded it from Kaggle. And you see it's age, sex, BMI, children, smoker region, and the charge. That's dollar, euros, whatever. And this is my input, it has like I think 1500 lines of items. So then let's have a look at the training model. So how does that look like? So I just need a few modules like numpy, pandas, pickle, sklearn to get it. So what will I do? I just, with pandas, load the data as the CSV, then I need to clean the data a bit, so I tell it basically which axis is what, so the charges is the one I want to train it on, so I remove it on, and the charges is basically the data which is the age, sex, smoker children and so on that's what i tell them here the model itself is basically encoded the model always expects numerical values and this is what it can do automatically some are here for example the regression model they are already numbers so i can tell it that it's this one the other one needs to be encoded so the sex m or f it will be translated into a numerical number themselves so that's what the model does the same goes for region it doesn't matter which string it is it basically automatically translates it to a meaningful format and then it can train the model automatically on top of it so then here is the model where it's just say x and y x so that's what it does here and here we test it so if the predictions are basically done here's my test data and then we print the model last but not least this is the most important step so once that goes through then i dump the model in pickle is the tool and the format is this pkl doesn't matter i mean you can also use other tools also inside couchbase ml here i just use this one so it's very simple we train the model so it basically reads the data depending on how fast your machine is it can be faster or slower here it did its job and this This is basically the print what I did for the quote. So with the test data, this is what it does. So I put it off so now you can read a bit better. So what is needed now is just like a curl post to the server. This I already did. But here you can see basically the model. well you need to package it first that's true so the data what I have is then packaged into this one so I have that here I do that with shift so that's this one here so this takes some time and basically creates then my pipeline pyz The cool thing about this packaging is it basically puts everything what this code needs into this package so all the libraries and stuff everything is inside so that's why it is so easy to package and upload it and run it because the Python environment doesn't care it basically has everything and just runs it so it makes sure that for the Python version everything is right and does it stuff but this takes a bit longer because it needs to download also also most of these things I did upload it into the system already but it's just a curl push it there and that's it all right so this is how the server looks like and what I will do first is basically and create a new bucket so that's equivalent to database and relational database world. I just name it insurance data and here is different storage types some are optimized for memory some are optimized for disk usage I'll leave it as it is but I give it less memory quota also I have only one machine so that's what I do. So I create this bucket now it's empty and I go to my document viewer and import basically the test data so that's what I have here that's what you have seen and basically I just say I put it into the bucket I created each of the items gets an UUID individually and this is how the table looks like. I import the data and then it should load in a few seconds. So inference data we can have a look there for sure some already. So here you see the UUID and as I said this is the big advantage of Couchbase, basically relational plus NoSQL. So this is the NoSQL part where we have our JSON documents. Also editing is very simple. You can just edit it on the fly and save it without schema change. That's great. So then we go to analytics. So this is basically all the services, what you have in the platform. Analytics is one of them. And what you need to do is basically just create the statement so create analytics function get insurance and estimate so this is our user defined function and this is the input parameters it takes as quote and models get predictions at insurance quote so also this takes a bit i did this already so let's have a look what model we have inside. So when I select basically everything what I have the insurance quote is there. That's cool so I can use it. Then we have a look at the functions. So inside the model I uploaded there is the name get insurance estimate that's also in the Python code I did with the parameters age and so on and a few other things what the database needs so that's also there that's great so let's test the data so I do now my select statement all right I need to add my collection of course data otherwise it doesn't know of course so that should It should be a minus, I didn't like that before. let's see use default okay then let's try that again that's the minus all right so that doesn't seem to work now it's okay no problem then we just do like a real life example with the data i have here so when i use now the statements the inference select get insurance estimate so that's the user defined function i created and then as we saw in the function the different input parameters so that's age 40 male region 30 and two kids non-smoker and southeast as quote and then it basically returns me exactly the the quote for the insurance based on the model and so on. So this is how you can do it. The cool thing is now, as this is inside the database, you can use the built-in REST interface to query the data, you can use the SDK, build your own front-end application on top of it, and have immediately results trained on your data for the results of the quotes. Let's switch back. That was the demo, instant quote demo, summary, I think I said everything. We have the custom model in Python and can really use any module you have. So I used this one. But as long as it's in Python, if you package it, everything you need is basically inside. Look at this as a container and then you can apply it on top with your select statement. Redeploying is also super easy. Once you rebuild it, you package it again, upload it via the curl command again, then it's deployed, usable again. Very easy. And reduces the infrastructure for just testing out things. Using it briefly, that's good enough. You don't need any extra infrastructure. Good thing also about Couchbase is you can scale individually. So you just add more analytics nodes. If you really have more demand for the interface, then it will basically scale horizontally. And no ETL job, because that's built in. So the conclusion. Machine learning in database. More and more data stores, databases, do integrate these kind of things, because the industry demands that also models are becoming more flexible more tuned for the different use cases more and more are also available on Kaggle and so on also built-in but it's not suitable for all use cases obviously and some of the ML modules use graphic cards this is CPU bound so that's probably then not your use case, but for most of the standard use cases or if you want to try out things, that's good enough. When to use it? It's an out-of-the-box solution, right? So you don't need anything extra. You can just start using it and experimenting with it. It's simplified deployment because, well, you don't need any extra microservices. You don't need to build anything extra you can have it inside and no etl jobs which need to be created maintained and also yeah your data model and data consistency not a problem here so a bit more things about couchbase what else we have so we basically are built around the key value store so we are in memory extremely fast and then we have SQL as query language but we store JSON documents that's one thing and we have our AI chatbot assists they can also help you with writing the SQL queries we have built-in full-text search so that's another store then vector search brand-new so for your LLMs and they usually need to store the data somewhere that's also possible in Couchbase and you can query it similarity searches real-time analytics that's what I showed you graph traversal is there we have time series storage and query we have eventing engine which can be like stored procedures we also have a mobile database which can be installed on your mobile phone in your app code as library and then sync with the backend. Integrated cache, that's key value in memory. Magma storage for optimized disk reads. Asset transaction, you know from SQL. Cluster map, so we basically scale horizontally or vertically based with the cluster map. Brand new this year is also columnar storage coming up. So that's another way of doing analytics. basically you turn the table around and can do better analytics queries like min max of the whole table that's much quicker as if you turn it the other way around and go line by line then active clustering we do multi-master trees rings whatever workload isolation that's a big and geo-replication over different data centers and so on all built in. So what does that translate to? That so all these services I basically told you this might be the familiar ones you've seen we are a platform which combines all of them in one so that's great one product one API you can save quite a lot of integration work if you just need one. If you want to try this out, you can very well do. We have a developer portal, free trials, Playground Academy. The code I showed you is also in GitHub. It's one of my colleagues who did it. That's Nitish R, machine learning coach base. Also, I'm available for questions, and you can just contact me anytime. All right.

Speaker 2 [38:45]

All right. Thank you, Gregor, for the great talk and the illustration of the demo. Let's get to some questions. There are 12 questions or even more. Are you ready to take questions? Sure. To the people. You can upload questions. We have some kind of triage for the questions. Let's get to the first question. What about images data?

Speaker 1 [39:08]

So, this is, I mean, we can store blobs inside Couchbase, but this is then like a binary image. You need to basically create an embedding just like an LLM would do to translate an image to another format. And then you can do image processing. For example, when I told you about the mobile component we have for mobile phones, in this machine learning module, there is for image processing something. And you can do it exactly the way I showed you. You say basically select input of the file, which can be stored in the database, and then the result will be the JSON, where it says with a probability of, I don't know, 0.9%, this is a tree or a mountain or whatever. So yes, this is doable, but you have to do it with your model.

Speaker 2 [40:08]

And then there's a question using onX. Maybe can you compare Couchbase to onX? It was a question like decoupling the model creation and execution by using onX.

Speaker 1 [40:21]

I'm not sure what 1x is.

Speaker 2 [40:25]

Well, the person who wrote the question just write in the Discord and then the question can be answered there. So let's get on to the next question. We've seen the user-defined functions using Python library. That was already answered in your presentation. Next. Next, does CouchBase require an active connection to the database through the model development lifecycle?

Speaker 1 [41:01]

I mean, the development of the model you did and what I showed can be done completely offline, so this is decoupled. You train it on either the CSV data which you dumped or have it from any source, and then you can upload it into the live set, so they don't need to.

Speaker 2 [41:20]

Alright, thank you. And what does the database store exactly? The model checkpoints? And who bears the cost of loading the checkpoints? The database or the user application? The checkpoints.

Speaker 1 [41:34]

The checkpoints? Yes.

Speaker 2 [41:36]

Yes, the model checkpoints, I think. So training the model, and then the model checkpoints.

Speaker 1 [41:41]

completely user-defined. So just see it as a standalone Python script. Everything you want to do is basically inside. You just define the user-defined functions, the input parameters, and then what is the outcome? Completely separated. And then you upload it into the database. Database is pretty much a black box. It just executes the code applied to what you put in. The input can be either directly like I showed or it can be like a select statement where you have multiple rows in your database

Speaker 2 [42:16]

Next question, what about machine learning lifecycle operations such as model monitoring and performance monitoring?

Speaker 1 [42:26]

So there is API REST interfaces and Grafana dashboards and Prometheus exporters who can monitor each parameter and performance of each of the models of Couchbase. So this you can monitor. Also on the SDK side, you can monitor the query performance of each individual query and then see if there are slow queries or what's the performance.

Speaker 2 [42:53]

Where can one get Couchbase? Is it free or only paid?

Speaker 1 [42:59]

So there is two versions. So there is a community edition, which is completely free to use, but it's limited. It doesn't have all the features of the enterprise edition, but you can use and download everything to try it out. If you go into production with the enterprise edition, then there is license which applies. So it runs locally on your computer. You can install it on a VM or whatever. But we also have a fully managed system called Capella. which you can try out 30 days for free so cloud.couchbase.com 30 days free trial

Speaker 2 [43:37]

All right, just in time, give another round of applause, please.