Building a multi-purpose platform for bulk data using sqlalchemy
Trebing, Christian
Christian Trebing - Building a multi-purpose platform for bulk data using sqlalchemy At Blue Yonder, we've built a platform that can accept and process bulk amounts of data for multiple business domains (e.g. handling retail store location and sales data) using SQLAlchemy as a database abstraction layer. We wanted to use as much of SQLAlchemy as possible, but we quickly found that the ORM (Object Relational Mapper) is not suitable for handling large amounts of data at once. At the same time, we did not want each team of developers working on individual business domains to have to handcraft their own SQL statements. To solve this problem, we built an application configuration that closely resembles an SQLAlchemy model, but also contains application-specific logic settings. In this talk I will demonstrate: - an application architecture for multiple business domains - the structure of the domain configuration utilized in the generation of the SQLAlchemy model, SQLAlchemy core statements, and other application functionality - how the domain configuration is used throughout the application
 Thomas and I'm working in the team where we process that data, where we make that available for the machine learning and where we therefore build these APIs. And that's the main topic of my talk. It's mainly three things you can see here in the title. It's building a mighty purpose platform, meaning we want to use it for different business domains. I will explain that later on. We have bulked data, so not just a few data sets, but lots of data which need to be processed in a fast way. And we do it using SQL Alchemy. These are the three points that you will see throughout the presentation. Introducing a way of building data processing applications that can be used in many business domains. That's the topic. Yeah, SQL Alchemy. We are building on SQL Alchemy. I mean, most of you, I assume, know that it's just a statement from the website which are copied here and have to say what I really like about SQL Alchemy is that it gives us so much flexibility in how to build your application. You really have both ends of the spectrum doing it much database oriented for high performance and you can, you also have the OAM where you can broke them in a much more abstract way. And within our application, we use both of these flavors depending on in what area we are and what we do exactly in the API processing part I will show here. So let's build a multi-domain platform. What do we have to do? What do our customers expect from us? Well, we have to load the bulk data via a CSV file. This is what I will show in my example or in real life. We also use XML files. We get that into the system via HTTP interface, post request. We have to verify that data. We have had several talks already regarding clean data, big data, which is not always clean, which can be quite messy. And so that our machine learning can work on that. We need to have clean data that has well-added to preferences and that's one important part of our application, what we do. Yeah, and we use it for different business domains. So what we currently do in our company is for retail, for tourism and for other areas. But yeah, what I will show in the demo I will explain on the next slide then. So there's still a lot of technical to do what we have to do. We have to create a database schema based on the business domain we are in. We have to parse the CSV. We have to save that parse CSV data to the database. We have to validate that data. Validations, there can be multiple things. We have to check that the required fields are filled. We have to check that the data is correct and that in a date field there is no time for example or no other descriptions like today or tomorrow. And we have to check that the references between the data records are correct. We want to give the feedback. We want to give feedback to the customer about the processing status of his data, whether it was accepted, whether we were able to process it, what is done with it. And it is important for us that we can separate the data that we received from the customer from the clean and validated data that we will use for machine learning. So we want to be always be able to track what was sent to us and what we made from that. Having thought about that, let's have a look at our first customer. Our first customer is a pub and what could a pub want from a machine learning algorithm? It wants to predict how many drinks are sold in the next evenings so that they can plan accordingly how much to buy, how much waiters to have. So to do that, they want to send us the drinks they have available. They want to send us how many drinks were ordered per evening for the last half year and how many visit us they had on each evening so that we can do our learning on that. How could the data model for this look like? It's quite simple. We have on the one hand the drinks, the orders reference the drinks and the visitors. That's just another table that we have for information. I told you that we need to separate the data we got from the customer to the data that we validated. To do that, we have two sets of tables. On the one hand, these are the stage tables. These are the data we got from the customer just as we get it from the customer. So maybe he sends several updates, then we have several lines in them. Maybe there are some duplicates because he sent the same file twice, then we have several lines in there and maybe there are some errors in. Then we also have them all in that stage. What you want to get out of that process is the core and in the core, we also have the drinks, the orders and the visitors but there we have one unique ID for each data record and when we have updates to the data, we will update that data record and not saving it several times. So the machine learning algorithms can use that and can be confident that they will get sensible data. How could such a CSV delivery look like? Well, let's take a simple pub. We have some beer. We have some additional information here. Alcoholic content. We have whiskey. Let's see maybe the pub since Scotland. We serve Scottish whiskey without an E and we have some coke for the people not wanting some beer or whiskey and we have these orders on that day. We sold 10 beer and eight coke on the 11th of July, 15 beer and two whiskey and on 12th of July, we sold 13 beer and one. Yeah, well, we got a new waiter from Ireland. There they're rather whiskey with an E but that would be bad for us as we only know the Scottish whiskey, but things can happen. We get that at a delivery. Now, what do we want to do with that? Ideally, what our code should be able to do. It should find references between objects. So we have that stage table here. These are the orders we get and you can see that's the external code you saw. That's the drinks you saw. That's the count you saw on the orders table and then there's this new column, the drinks reference. This is nothing the customer sent to us. This is the reference to the unique IDs of the drinks we want to find. They are available here in the core. You can see a lot core table. You see here we have a unique ID and we want to write that in there. Yes, one implementation detail I missed at the last slide. Here we don't have a foreign key relationship between this column and this column. You can be anything in so at the moment it's empty. But in the core, we defined a foreign key relationship between that table and that table. So that also the database ensures that really there's sensible data in here. So whenever we want to copy that data to that table, we really need to make sure that we find the correct references first. This we do in two steps. We have the reference finding step which writes them in here and then when they are in, it writes the validated data to the core and copies and then you can see it omits just this information but keeps the reference information with the foreign key to the drinks table. And you also see the last line is omitted. This was key. He could not process. He did not copy it in there. We have to decide in our application whether we throw an exception then whether to write some log file to give information to the customer in some way. But at least it should not come into the core. So our task as developers is how can we write the code that does these steps? How do we do that? We have several possibilities. We have plain SQL works fine and if we want to start playing around with that, that's always a good choice on just playing around with the database to be able that there's really a sensible way of doing that. We can do that in the core. So SQL Alchemy Core model which is closely resembles the SQL Alchemy and where we have here orders stage. This is a SQL Alchemy metadata object which contains the information about the stage table for the orders. We issue an update statement and we say what are the values? The values are that the strings reference column should be filled by a select and you want to select the IDs of the core table of the drinks where the external code of that core table equals the drinks name of the order stage and let me just go back to show it to you again here. We want to make sure really that this ID gets into that column and that for exactly where this name of the drinks matches here the external code of the core. So therefore, okay, this works fine. That's a nice idea and I would say maybe would be the best for implementation. We have slightly in the back of our head that we might get different customers with different models and we are thinking about, well, maybe it would be a good idea to look in the OOM so that we are more flexible there. We have here the tables as objects and we have each row as an object and it's much nicer to implement the stuff here. We can loop over the orders. We can query the table with the correct filters and update the table. It works fine also, but as we do here the single database access that might be not a good idea from a performance point of view when you really have big customers. But these are the things, the tools we have at hand at the moment. So let's assume in our team we use that statement and we are really happy. Everything works fine. We have great data. Customer is happy. Our data scientists are happy. Everything's good. Now, well, as you can see, good or bad, the customer is happy. The pub and she tells the brewery about that. So they are talking when they're getting new delivery and the brewery is quite excited because they say, well, that machine learning stuff, we read about that in the newspapers. We are thinking about our brewery. We have the machines. We have the boilers and the fermenters and we have some sensors in there. We measure some stuff like temperature and pressure. And then must be some way to find out, I mean, brewing and storing beer is quite a long process. We want to know in the beginning what will be the quality of our beer in the end. Couldn't you help us with that? And our data scientists are quite happy with that. Interesting new task. And we just need to get the data into the system. This can't be that complicated. Well, looking at that statement here, it might be. We have to rewrite all that because there are different references between the categories. We now have machines. We now have sensors. We now have measurements. All are named differently. And to make that customer happy, we would have to rewrite that complete statement. So it would work. But when we look into the future and maybe they are more interested, more interesting business domains, we might have really lots of work to do. So what could be the solution? We thought now a team. And we said, well, we could describe these things in a more abstract way. We can say we have one business domain, which is the pub at first. And the pub consists of categories. We have the drinks. We have the orders. And we have the visitors. They consist of the elements. Well, that's the external code. There's a reference to the drinks. They have some types we need for the database. And what is some of these elements are special? We looked at that reference finding task and we have seen they need special processing. And it would be good if you just could have a way to determine that these are special elements that we can inherit here from that element. And we also have, I mean, each element has a name, which we see in the CSV file, and it has a name on the database, which is, well, most cases the name in capital letters. But for this order, strings reference, you had seen there's an additional field. There's this name reference. You remember in our reference finding step, we wanted to fill that column in the stage table. So we add this here in our subclass and we say that this belongs to the category. So what does that help us if we can do that? I mean, we also thought that somehow resembled the SQL alchemy model also. I mean, a SQL alchemy model also has some tables. It has some elements. It has some types. It has foreign keys. But the SQL alchemy model is for a database description. It's not so much for an algorithmic processing of that stuff. So therefore, I will discuss that at the end. We said it really makes sense to have this in a more abstract way. How now does that look like? I mean, SQL alchemy also has parsers. Here in ORM, for example, we have that generic ORM parser. And we have here in the SQL alchemy model, we have our business domain. And we have the business domain that is also described in the application. So in both here, we need to have that business domain. What we wanted to do, we wanted to factor out some knowledge of that. So that this application does not need to know the business domain, that we really can set this here in that domain model. And that we can have specific task renderers. So for reference finding, we have here one renderer which uses that information to generate SQL statements. And we can have one for other tasks also. How does that look like? I will give you here a code sample for that pub. So we have here the domain, the category, the elements from our lingo package. We have that pub, which is a domain. And that pub consists of three categories, the drinks, the visitors and the orders. That's quite easy to write down. I mean, there's nothing more than you need. And it's easily understandable. The interesting stuff is here that reference, which you see links directly to the drinks. Having that, how does that task specific renderer look like? So this is Python code which at first checks for each category, what are the references in there? And we can just check that with an instance. And we loop over the references. We have here just one, but there might be other categories where we have multiple ones. By getting the stage and core tables from the SQL Alchemy metadata. As you can see here, we can find that by the name of the stage table. We can find the reference core table. And we issue an update statement. So this update statement here, you see update the stage with what values. These values are constructed dynamically. So because we cannot give here the keyword arguments dynamically, we construct the dict at first. This is here the update dict with the keyword values. And you see here, that's the column which should be updated. And this is a SQL Alchemy core statement on how to update that with what name. So we print that, I will show in the demo. And then we can execute that. So now let's switch to that. We can see that this really works. We are prepared in the demo, it's a simple SQL light database, and I have prepared some script. No, it's quite... Let me do it that way. Okay, great. So at the moment, we do not have to SQL light database. You see here, these tables do not exist at all. And what we want to do, we want to create them. We do that by calling our Python script, create database, and these tables are there. You can see here, by the way, we have a configuration file where we see what's the database and what is our business domain. So now we need to get data in there. So we call that Python script. We do the CSV import. We do that first for the trinks category. And you see here, that's the SQL statement created from that, and it's here in the category. So now we do that for the orders also. And you see it's in here. Now we want to do the reference finding, and for sure before we find the references, the trinks need to get into the core, otherwise it doesn't make sense. So what do we do? We are calling the core load script, and we're doing that first for the trinks. You see here, that is the generated SQL statement, and this is the core. And you see that the trinks ref column is empty. So now, for the most interesting, you can see these SQL statements are issued, and here you see that this is filled, and also that the orders core is filled. Well, it doesn't fit completely on the screen, but you can see that it's all in there. So that works fine. That's great. But that was a step. We were already five slides ago. Thank you. Now let's go back. And let's say we have now another domain. We have a brewery, and that brewery, we say it has machines, it has sensors, and it has measurements. And that brewery here, you can see the sensors referenced to machines, and the measurements reference the sensors. I would like to show that in the demo also, but unfortunately the time is not sufficient for that, but it's really as simple as we have seen it here. You just need to change the config JSON, you create the database, and the new tables will be there, and you can import the differences. We find this will be in there also. So what does that mean? When we say, what does this domain model help us? Well, it is optimized for high throughput. As you see these SQL statements that are issued, they can be processed directly on the database. So we put all things in the database into the stage, and now we have some processing of our domain knowledge, and we'll generate the SQL statements, and the things will be processed on the database. That's a really good fit for analytical models. Then when I thought about the demo here for the talk, I found it might not fit that well for transactional models where you have more complex end-to-end relations, but for analytical models this is really great and helps a lot. When I compare that to a SQL Alchemy model, which is also some kind of meta model, then we see that the SQL Alchemy is focused on a database description. The domain model in contrast can contain more information. In our team, we had also the task that we have time-dependent stuff. So some crinks are only available at several days, or maybe they were available last week, but they're not available this week, and we need to check these cross-time dependencies also. This can be done in the domain model also. We can note that there. And we can generate the SQL Alchemy model out of that domain model, so in that case, we have both. So an additional bonus is we can use that domain model for much further stuff. You can see here, we can generate the SQL Alchemy model. We can generate a SQL for our tasks. We have to CSV load our configuration, but also what we do, we generate documentation out of that, so how to fill that CSV table, and we can generate demo data and much more stuff. But that's just to show you some ideas. So that you are able also to have some questions. I will close here, and that's what I wanted to show you. Are there any questions? Hi. Is a lingo library open source and available? No. This is something we developed internally. I mean, what I did here for that talk, I prepared a small demo application, and I also thought about providing that. But I've seen it takes much stuff around to make that example somehow sensible. And yeah, before making that open source, I would have to check also internally in our company. And before going into that direction, I just want to see see whether there's interest at all in that. So if you have some questions to that, I want to get some further updates, which I can just talk after the talk. Any more questions? No? All right. Thank you, Christian. Okay. Thank you.
 Thomas and I'm working in the team where we process that data,  where we make that available for the machine learning and where we  therefore build these APIs.  And that's the main topic of my talk.  It's mainly three things you can see here in the title.  It's building a mighty purpose platform, meaning we want to use  it for different business domains.  I will explain that later on.  We have bulked data, so not just a few data sets, but lots of  data which need to be processed in a fast way.  And we do it using SQL Alchemy.  These are the three points that you will see throughout the presentation.  Introducing a way of building data processing applications that can  be used in many business domains.  That's the topic.  Yeah, SQL Alchemy.  We are building on SQL Alchemy.  I mean, most of you, I assume, know that it's just a statement from  the website which are copied here and have to say what I really  like about SQL Alchemy is that it gives us so much flexibility in  how to build your application.  You really have both ends of the spectrum doing it much database  oriented for high performance and you can, you also have the OAM where  you can broke them in a much more abstract way.  And within our application, we use both of these flavors depending on  in what area we are and what we do exactly in the API processing  part I will show here.  So let's build a multi-domain platform.  What do we have to do?  What do our customers expect from us?  Well, we have to load the bulk data via a CSV file.  This is what I will show in my example or in real life.  We also use XML files.  We get that into the system via HTTP interface, post request.  We have to verify that data.  We have had several talks already regarding clean data, big data,  which is not always clean, which can be quite messy.  And so that our machine learning can work on that.  We need to have clean data that has well-added to preferences and  that's one important part of our application, what we do.  Yeah, and we use it for different business domains.  So what we currently do in our company is for retail, for  tourism and for other areas.  But yeah, what I will show in the demo I will explain on the  next slide then.  So there's still a lot of technical to do what we have to do.  We have to create a database schema based on the business  domain we are in.  We have to parse the CSV.  We have to save that parse CSV data to the database.  We have to validate that data.  Validations, there can be multiple things.  We have to check that the required fields are filled.  We have to check that the data is correct and that in a date  field there is no time for example or no other descriptions  like today or tomorrow.  And we have to check that the references between the data  records are correct.  We want to give the feedback.  We want to give feedback to the customer about the processing  status of his data, whether it was accepted, whether we were  able to process it, what is done with it.  And it is important for us that we can separate the data that  we received from the customer from the clean and validated  data that we will use for machine learning.  So we want to be always be able to track what was sent to us  and what we made from that.  Having thought about that, let's have a look at our first customer.  Our first customer is a pub and what could a pub want from a  machine learning algorithm?  It wants to predict how many drinks are sold in the next  evenings so that they can plan accordingly how much to buy,  how much waiters to have.  So to do that, they want to send us the drinks they have  available.  They want to send us how many drinks were ordered per  evening for the last half year and how many visit us they had  on each evening so that we can do our learning on that.  How could the data model for this look like?  It's quite simple.  We have on the one hand the drinks, the orders reference  the drinks and the visitors.  That's just another table that we have for information.  I told you that we need to separate the data we got from  the customer to the data that we validated.  To do that, we have two sets of tables.  On the one hand, these are the stage tables.  These are the data we got from the customer just as we get it  from the customer.  So maybe he sends several updates, then we have several  lines in them.  Maybe there are some duplicates because he sent the same file  twice, then we have several lines in there and maybe there  are some errors in.  Then we also have them all in that stage.  What you want to get out of that process is the core and in  the core, we also have the drinks, the orders and the visitors  but there we have one unique ID for each data record and when  we have updates to the data, we will update that data record  and not saving it several times.  So the machine learning algorithms can use that and can be  confident that they will get sensible data.  How could such a CSV delivery look like?  Well, let's take a simple pub.  We have some beer.  We have some additional information here.  Alcoholic content.  We have whiskey.  Let's see maybe the pub since Scotland.  We serve Scottish whiskey without an E and we have some coke  for the people not wanting some beer or whiskey and we have  these orders on that day.  We sold 10 beer and eight coke on the 11th of July, 15 beer  and two whiskey and on 12th of July, we sold 13 beer and one.  Yeah, well, we got a new waiter from Ireland.  There they're rather whiskey with an E but that would be bad  for us as we only know the Scottish whiskey, but things can  happen.  We get that at a delivery.  Now, what do we want to do with that?  Ideally, what our code should be able to do.  It should find references between objects.  So we have that stage table here.  These are the orders we get and you can see that's the external  code you saw.  That's the drinks you saw.  That's the count you saw on the orders table and then there's  this new column, the drinks reference.  This is nothing the customer sent to us.  This is the reference to the unique IDs of the drinks we want  to find.  They are available here in the core.  You can see a lot core table.  You see here we have a unique ID and we want to write that in  there.  Yes, one implementation detail I missed at the last slide.  Here we don't have a foreign key relationship between this  column and this column.  You can be anything in so at the moment it's empty.  But in the core, we defined a foreign key relationship between  that table and that table.  So that also the database ensures that really there's sensible  data in here.  So whenever we want to copy that data to that table, we really  need to make sure that we find the correct references first.  This we do in two steps.  We have the reference finding step which writes them in here  and then when they are in, it writes the validated data to the  core and copies and then you can see it omits just this information  but keeps the reference information with the foreign key  to the drinks table.  And you also see the last line is omitted.  This was key.  He could not process.  He did not copy it in there.  We have to decide in our application whether we throw an exception  then whether to write some log file to give information to the  customer in some way.  But at least it should not come into the core.  So our task as developers is how can we write the code that  does these steps?  How do we do that?  We have several possibilities.  We have plain SQL works fine and if we want to start playing around  with that, that's always a good choice on just playing around with  the database to be able that there's really a sensible way of doing that.  We can do that in the core.  So SQL Alchemy Core model which is closely resembles the SQL Alchemy  and where we have here orders stage.  This is a SQL Alchemy metadata object which contains the information  about the stage table for the orders.  We issue an update statement and we say what are the values?  The values are that the strings reference column should be filled by a select  and you want to select the IDs of the core table of the drinks  where the external code of that core table equals the drinks name of the  order stage and let me just go back to show it to you again here.  We want to make sure really that this ID gets into that column and that for  exactly where this name of the drinks matches here the external code of the core.  So therefore, okay, this works fine.  That's a nice idea and I would say maybe would be the best for implementation.  We have slightly in the back of our head that we might get different customers  with different models and we are thinking about, well,  maybe it would be a good idea to look in the OOM so that we are more flexible there.  We have here the tables as objects and we have each row as an object  and it's much nicer to implement the stuff here.  We can loop over the orders.  We can query the table with the correct filters and update the table.  It works fine also, but as we do here the single database access that might be  not a good idea from a performance point of view when you really have big customers.  But these are the things, the tools we have at hand at the moment.  So let's assume in our team we use that statement and we are really happy.  Everything works fine.  We have great data.  Customer is happy.  Our data scientists are happy.  Everything's good.  Now, well, as you can see, good or bad, the customer is happy.  The pub and she tells the brewery about that.  So they are talking when they're getting new delivery and the brewery is quite excited  because they say, well, that machine learning stuff,  we read about that in the newspapers.  We are thinking about our brewery.  We have the machines.  We have the boilers and the fermenters and we have some sensors in there.  We measure some stuff like temperature and pressure.  And then must be some way to find out, I mean, brewing and storing beer is quite a long process.  We want to know in the beginning what will be the quality of our beer in the end.  Couldn't you help us with that?  And our data scientists are quite happy with that.  Interesting new task.  And we just need to get the data into the system.  This can't be that complicated.  Well, looking at that statement here, it might be.  We have to rewrite all that because there are different references between the categories.  We now have machines.  We now have sensors.  We now have measurements.  All are named differently.  And to make that customer happy, we would have to rewrite that complete statement.  So it would work.  But when we look into the future and maybe they are more interested, more interesting business domains,  we might have really lots of work to do.  So what could be the solution?  We thought now a team.  And we said, well, we could describe these things in a more abstract way.  We can say we have one business domain, which is the pub at first.  And the pub consists of categories.  We have the drinks.  We have the orders.  And we have the visitors.  They consist of the elements.  Well, that's the external code.  There's a reference to the drinks.  They have some types we need for the database.  And what is some of these elements are special?  We looked at that reference finding task and we have seen they need special processing.  And it would be good if you just could have a way to determine that these are special  elements that we can inherit here from that element.  And we also have, I mean, each element has a name, which we see in the CSV file, and  it has a name on the database, which is, well, most cases the name in capital letters.  But for this order, strings reference, you had seen there's an additional field.  There's this name reference.  You remember in our reference finding step, we wanted to fill that column in the stage  table.  So we add this here in our subclass and we say that this belongs to the category.  So what does that help us if we can do that?  I mean, we also thought that somehow resembled the SQL alchemy model also.  I mean, a SQL alchemy model also has some tables.  It has some elements.  It has some types.  It has foreign keys.  But the SQL alchemy model is for a database description.  It's not so much for an algorithmic processing of that stuff.  So therefore, I will discuss that at the end.  We said it really makes sense to have this in a more abstract way.  How now does that look like?  I mean, SQL alchemy also has parsers.  Here in ORM, for example, we have that generic ORM parser.  And we have here in the SQL alchemy model, we have our business domain.  And we have the business domain that is also described in the application.  So in both here, we need to have that business domain.  What we wanted to do, we wanted to factor out some knowledge of that.  So that this application does not need to know the business domain, that we really  can set this here in that domain model.  And that we can have specific task renderers.  So for reference finding, we have here one renderer which uses that information to generate  SQL statements.  And we can have one for other tasks also.  How does that look like?  I will give you here a code sample for that pub.  So we have here the domain, the category, the elements from our lingo package.  We have that pub, which is a domain.  And that pub consists of three categories, the drinks, the visitors and the orders.  That's quite easy to write down.  I mean, there's nothing more than you need.  And it's easily understandable.  The interesting stuff is here that reference, which you see links directly to the drinks.  Having that, how does that task specific renderer look like?  So this is Python code which at first checks for each category, what are the references  in there?  And we can just check that with an instance.  And we loop over the references.  We have here just one, but there might be other categories where we have multiple ones.  By getting the stage and core tables from the SQL Alchemy metadata.  As you can see here, we can find that by the name of the stage table.  We can find the reference core table.  And we issue an update statement.  So this update statement here, you see update the stage with what values.  These values are constructed dynamically.  So because we cannot give here the keyword arguments dynamically, we construct the dict  at first.  This is here the update dict with the keyword values.  And you see here, that's the column which should be updated.  And this is a SQL Alchemy core statement on how to update that with what name.  So we print that, I will show in the demo.  And then we can execute that.  So now let's switch to that.  We can see that this really works.  We are prepared in the demo, it's a simple SQL light database, and I have prepared some  script.  No, it's quite...  Let me do it that way.  Okay, great.  So at the moment, we do not have to SQL light database.  You see here, these tables do not exist at all.  And what we want to do, we want to create them.  We do that by calling our Python script, create database, and these tables are there.  You can see here, by the way, we have a configuration file where we see what's the database and  what is our business domain.  So now we need to get data in there.  So we call that Python script.  We do the CSV import.  We do that first for the trinks category.  And you see here, that's the SQL statement created from that, and it's here in the category.  So now we do that for the orders also.  And you see it's in here.  Now we want to do the reference finding, and for sure before we find the references,  the trinks need to get into the core, otherwise it doesn't make sense.  So what do we do?  We are calling the core load script, and we're doing that first for the trinks.  You see here, that is the generated SQL statement, and this is the core.  And you see that the trinks ref column is empty.  So now, for the most interesting, you can see these SQL statements are issued, and here  you see that this is filled, and also that the orders core is filled.  Well, it doesn't fit completely on the screen, but you can see that it's all in there.  So that works fine.  That's great.  But that was a step.  We were already five slides ago.  Thank you.  Now let's go back.  And let's say we have now another domain.  We have a brewery, and that brewery, we say it has machines, it has sensors, and it has  measurements.  And that brewery here, you can see the sensors referenced to machines, and the measurements  reference the sensors.  I would like to show that in the demo also, but unfortunately the time is not sufficient  for that, but it's really as simple as we have seen it here.  You just need to change the config JSON, you create the database, and the new tables will  be there, and you can import the differences.  We find this will be in there also.  So what does that mean?  When we say, what does this domain model help us?  Well, it is optimized for high throughput.  As you see these SQL statements that are issued, they can be processed directly on the database.  So we put all things in the database into the stage, and now we have some processing  of our domain knowledge, and we'll generate the SQL statements, and the things will be  processed on the database.  That's a really good fit for analytical models.  Then when I thought about the demo here for the talk, I found it might not fit that well  for transactional models where you have more complex end-to-end relations, but for analytical  models this is really great and helps a lot.  When I compare that to a SQL Alchemy model, which is also some kind of meta model, then  we see that the SQL Alchemy is focused on a database description.  The domain model in contrast can contain more information.  In our team, we had also the task that we have time-dependent stuff.  So some crinks are only available at several days, or maybe they were available last week,  but they're not available this week, and we need to check these cross-time dependencies  also.  This can be done in the domain model also.  We can note that there.  And we can generate the SQL Alchemy model out of that domain model, so in that case, we  have both.  So an additional bonus is we can use that domain model for much further stuff.  You can see here, we can generate the SQL Alchemy model.  We can generate a SQL for our tasks.  We have to CSV load our configuration, but also what we do, we generate documentation  out of that, so how to fill that CSV table, and we can generate demo data and much more  stuff.  But that's just to show you some ideas.  So that you are able also to have some questions.  I will close here, and that's what I wanted to show you.  Are there any questions?  Hi.  Is a lingo library open source and available?  No.  This is something we developed internally.  I mean, what I did here for that talk, I prepared a small demo application, and I also thought  about providing that.  But I've seen it takes much stuff around to make that example somehow sensible.  And yeah, before making that open source, I would have to check also internally in our  company.  And before going into that direction, I just want to see see whether there's interest  at all in that.  So if you have some questions to that, I want to get some further updates, which I can just  talk after the talk.  Any more questions?  No?  All right.  Thank you, Christian.  Okay.  Thank you.
Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_0_323.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_324_719.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_720_3755.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_3756_4343.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_4344_4775.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_4776_6491.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_6492_11303.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_11304_12935.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_12936_13091.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_13092_14987.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_14988_18551.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_18552_19103.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_19104_19367.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_19368_22247.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_22248_22979.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_22980_23075.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_23076_23351.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_23352_23567.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_23568_25511.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_25512_25655.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_25656_25787.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_25788_25967.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_25968_26087.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_26088_26435.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_26436_26579.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_26580_26639.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_26640_26723.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_26724_27011.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_27012_27863.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_27864_31823.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_31824_32591.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_32592_32711.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_32712_33479.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_33480_33755.png Christian_Trebing_-_Building_a_multi-purpose_platform_for_bulk_data_using_sqlalchemy_33756_34424.png
Thomas and I'm working in the team where we process that data, where we make that available for the machine learning and where we therefore build these APIs. And that's the main topic of my talk. It's mainly three things you can see here in the title. It's building a mighty purpose platform, meaning we want to use it for different business domains. I will explain that later on. We have bulked data, so not just a few data sets, but lots of data which need to be processed in a fast way. And we do it using SQL Alchemy. These are the three points that you will see throughout the presentation. Introducing a way of building data processing applications that can be used in many business domains. That's the topic. Yeah, SQL Alchemy. We are building on SQL Alchemy. I mean, most of you, I assume, know that it's just a statement from the website which are copied here and have to say what I really like about SQL Alchemy is that it gives us so much flexibility in how to build your application. You really have both ends of the spectrum doing it much database oriented for high performance and you can, you also have the OAM where you can broke them in a much more abstract way. And within our application, we use both of these flavors depending on in what area we are and what we do exactly in the API processing part I will show here. So let's build a multi-domain platform. What do we have to do? What do our customers expect from us? Well, we have to load the bulk data via a CSV file. This is what I will show in my example or in real life. We also use XML files. We get that into the system via HTTP interface, post request. We have to verify that data. We have had several talks already regarding clean data, big data, which is not always clean, which can be quite messy. And so that our machine learning can work on that. We need to have clean data that has well-added to preferences and that's one important part of our application, what we do. Yeah, and we use it for different business domains. So what we currently do in our company is for retail, for tourism and for other areas. But yeah, what I will show in the demo I will explain on the next slide then. So there's still a lot of technical to do what we have to do. We have to create a database schema based on the business domain we are in. We have to parse the CSV. We have to save that parse CSV data to the database. We have to validate that data. Validations, there can be multiple things. We have to check that the required fields are filled. We have to check that the data is correct and that in a date field there is no time for example or no other descriptions like today or tomorrow. And we have to check that the references between the data records are correct. We want to give the feedback. We want to give feedback to the customer about the processing status of his data, whether it was accepted, whether we were able to process it, what is done with it. And it is important for us that we can separate the data that we received from the customer from the clean and validated data that we will use for machine learning. So we want to be always be able to track what was sent to us and what we made from that. Having thought about that, let's have a look at our first customer. Our first customer is a pub and what could a pub want from a machine learning algorithm? It wants to predict how many drinks are sold in the next evenings so that they can plan accordingly how much to buy, how much waiters to have. So to do that, they want to send us the drinks they have available. They want to send us how many drinks were ordered per evening for the last half year and how many visit us they had on each evening so that we can do our learning on that. How could the data model for this look like? It's quite simple. We have on the one hand the drinks, the orders reference the drinks and the visitors. That's just another table that we have for information. I told you that we need to separate the data we got from the customer to the data that we validated. To do that, we have two sets of tables. On the one hand, these are the stage tables. These are the data we got from the customer just as we get it from the customer. So maybe he sends several updates, then we have several lines in them. Maybe there are some duplicates because he sent the same file twice, then we have several lines in there and maybe there are some errors in. Then we also have them all in that stage. What you want to get out of that process is the core and in the core, we also have the drinks, the orders and the visitors but there we have one unique ID for each data record and when we have updates to the data, we will update that data record and not saving it several times. So the machine learning algorithms can use that and can be confident that they will get sensible data. How could such a CSV delivery look like? Well, let's take a simple pub. We have some beer. We have some additional information here. Alcoholic content. We have whiskey. Let's see maybe the pub since Scotland. We serve Scottish whiskey without an E and we have some coke for the people not wanting some beer or whiskey and we have these orders on that day. We sold 10 beer and eight coke on the 11th of July, 15 beer and two whiskey and on 12th of July, we sold 13 beer and one. Yeah, well, we got a new waiter from Ireland. There they're rather whiskey with an E but that would be bad for us as we only know the Scottish whiskey, but things can happen. We get that at a delivery. Now, what do we want to do with that? Ideally, what our code should be able to do. It should find references between objects. So we have that stage table here. These are the orders we get and you can see that's the external code you saw. That's the drinks you saw. That's the count you saw on the orders table and then there's this new column, the drinks reference. This is nothing the customer sent to us. This is the reference to the unique IDs of the drinks we want to find. They are available here in the core. You can see a lot core table. You see here we have a unique ID and we want to write that in there. Yes, one implementation detail I missed at the last slide. Here we don't have a foreign key relationship between this column and this column. You can be anything in so at the moment it's empty. But in the core, we defined a foreign key relationship between that table and that table. So that also the database ensures that really there's sensible data in here. So whenever we want to copy that data to that table, we really need to make sure that we find the correct references first. This we do in two steps. We have the reference finding step which writes them in here and then when they are in, it writes the validated data to the core and copies and then you can see it omits just this information but keeps the reference information with the foreign key to the drinks table. And you also see the last line is omitted. This was key. He could not process. He did not copy it in there. We have to decide in our application whether we throw an exception then whether to write some log file to give information to the customer in some way. But at least it should not come into the core. So our task as developers is how can we write the code that does these steps? How do we do that? We have several possibilities. We have plain SQL works fine and if we want to start playing around with that, that's always a good choice on just playing around with the database to be able that there's really a sensible way of doing that. We can do that in the core. So SQL Alchemy Core model which is closely resembles the SQL Alchemy and where we have here orders stage. This is a SQL Alchemy metadata object which contains the information about the stage table for the orders. We issue an update statement and we say what are the values? The values are that the strings reference column should be filled by a select and you want to select the IDs of the core table of the drinks where the external code of that core table equals the drinks name of the order stage and let me just go back to show it to you again here. We want to make sure really that this ID gets into that column and that for exactly where this name of the drinks matches here the external code of the core. So therefore, okay, this works fine. That's a nice idea and I would say maybe would be the best for implementation. We have slightly in the back of our head that we might get different customers with different models and we are thinking about, well, maybe it would be a good idea to look in the OOM so that we are more flexible there. We have here the tables as objects and we have each row as an object and it's much nicer to implement the stuff here. We can loop over the orders. We can query the table with the correct filters and update the table. It works fine also, but as we do here the single database access that might be not a good idea from a performance point of view when you really have big customers. But these are the things, the tools we have at hand at the moment. So let's assume in our team we use that statement and we are really happy. Everything works fine. We have great data. Customer is happy. Our data scientists are happy. Everything's good. Now, well, as you can see, good or bad, the customer is happy. The pub and she tells the brewery about that. So they are talking when they're getting new delivery and the brewery is quite excited because they say, well, that machine learning stuff, we read about that in the newspapers. We are thinking about our brewery. We have the machines. We have the boilers and the fermenters and we have some sensors in there. We measure some stuff like temperature and pressure. And then must be some way to find out, I mean, brewing and storing beer is quite a long process. We want to know in the beginning what will be the quality of our beer in the end. Couldn't you help us with that? And our data scientists are quite happy with that. Interesting new task. And we just need to get the data into the system. This can't be that complicated. Well, looking at that statement here, it might be. We have to rewrite all that because there are different references between the categories. We now have machines. We now have sensors. We now have measurements. All are named differently. And to make that customer happy, we would have to rewrite that complete statement. So it would work. But when we look into the future and maybe they are more interested, more interesting business domains, we might have really lots of work to do. So what could be the solution? We thought now a team. And we said, well, we could describe these things in a more abstract way. We can say we have one business domain, which is the pub at first. And the pub consists of categories. We have the drinks. We have the orders. And we have the visitors. They consist of the elements. Well, that's the external code. There's a reference to the drinks. They have some types we need for the database. And what is some of these elements are special? We looked at that reference finding task and we have seen they need special processing. And it would be good if you just could have a way to determine that these are special elements that we can inherit here from that element. And we also have, I mean, each element has a name, which we see in the CSV file, and it has a name on the database, which is, well, most cases the name in capital letters. But for this order, strings reference, you had seen there's an additional field. There's this name reference. You remember in our reference finding step, we wanted to fill that column in the stage table. So we add this here in our subclass and we say that this belongs to the category. So what does that help us if we can do that? I mean, we also thought that somehow resembled the SQL alchemy model also. I mean, a SQL alchemy model also has some tables. It has some elements. It has some types. It has foreign keys. But the SQL alchemy model is for a database description. It's not so much for an algorithmic processing of that stuff. So therefore, I will discuss that at the end. We said it really makes sense to have this in a more abstract way. How now does that look like? I mean, SQL alchemy also has parsers. Here in ORM, for example, we have that generic ORM parser. And we have here in the SQL alchemy model, we have our business domain. And we have the business domain that is also described in the application. So in both here, we need to have that business domain. What we wanted to do, we wanted to factor out some knowledge of that. So that this application does not need to know the business domain, that we really can set this here in that domain model. And that we can have specific task renderers. So for reference finding, we have here one renderer which uses that information to generate SQL statements. And we can have one for other tasks also. How does that look like? I will give you here a code sample for that pub. So we have here the domain, the category, the elements from our lingo package. We have that pub, which is a domain. And that pub consists of three categories, the drinks, the visitors and the orders. That's quite easy to write down. I mean, there's nothing more than you need. And it's easily understandable. The interesting stuff is here that reference, which you see links directly to the drinks. Having that, how does that task specific renderer look like? So this is Python code which at first checks for each category, what are the references in there? And we can just check that with an instance. And we loop over the references. We have here just one, but there might be other categories where we have multiple ones. By getting the stage and core tables from the SQL Alchemy metadata. As you can see here, we can find that by the name of the stage table. We can find the reference core table. And we issue an update statement. So this update statement here, you see update the stage with what values. These values are constructed dynamically. So because we cannot give here the keyword arguments dynamically, we construct the dict at first. This is here the update dict with the keyword values. And you see here, that's the column which should be updated. And this is a SQL Alchemy core statement on how to update that with what name. So we print that, I will show in the demo. And then we can execute that. So now let's switch to that. We can see that this really works. We are prepared in the demo, it's a simple SQL light database, and I have prepared some script. No, it's quite... Let me do it that way. Okay, great. So at the moment, we do not have to SQL light database. You see here, these tables do not exist at all. And what we want to do, we want to create them. We do that by calling our Python script, create database, and these tables are there. You can see here, by the way, we have a configuration file where we see what's the database and what is our business domain. So now we need to get data in there. So we call that Python script. We do the CSV import. We do that first for the trinks category. And you see here, that's the SQL statement created from that, and it's here in the category. So now we do that for the orders also. And you see it's in here. Now we want to do the reference finding, and for sure before we find the references, the trinks need to get into the core, otherwise it doesn't make sense. So what do we do? We are calling the core load script, and we're doing that first for the trinks. You see here, that is the generated SQL statement, and this is the core. And you see that the trinks ref column is empty. So now, for the most interesting, you can see these SQL statements are issued, and here you see that this is filled, and also that the orders core is filled. Well, it doesn't fit completely on the screen, but you can see that it's all in there. So that works fine. That's great. But that was a step. We were already five slides ago. Thank you. Now let's go back. And let's say we have now another domain. We have a brewery, and that brewery, we say it has machines, it has sensors, and it has measurements. And that brewery here, you can see the sensors referenced to machines, and the measurements reference the sensors. I would like to show that in the demo also, but unfortunately the time is not sufficient for that, but it's really as simple as we have seen it here. You just need to change the config JSON, you create the database, and the new tables will be there, and you can import the differences. We find this will be in there also. So what does that mean? When we say, what does this domain model help us? Well, it is optimized for high throughput. As you see these SQL statements that are issued, they can be processed directly on the database. So we put all things in the database into the stage, and now we have some processing of our domain knowledge, and we'll generate the SQL statements, and the things will be processed on the database. That's a really good fit for analytical models. Then when I thought about the demo here for the talk, I found it might not fit that well for transactional models where you have more complex end-to-end relations, but for analytical models this is really great and helps a lot. When I compare that to a SQL Alchemy model, which is also some kind of meta model, then we see that the SQL Alchemy is focused on a database description. The domain model in contrast can contain more information. In our team, we had also the task that we have time-dependent stuff. So some crinks are only available at several days, or maybe they were available last week, but they're not available this week, and we need to check these cross-time dependencies also. This can be done in the domain model also. We can note that there. And we can generate the SQL Alchemy model out of that domain model, so in that case, we have both. So an additional bonus is we can use that domain model for much further stuff. You can see here, we can generate the SQL Alchemy model. We can generate a SQL for our tasks. We have to CSV load our configuration, but also what we do, we generate documentation out of that, so how to fill that CSV table, and we can generate demo data and much more stuff. But that's just to show you some ideas. So that you are able also to have some questions. I will close here, and that's what I wanted to show you. Are there any questions? Hi. Is a lingo library open source and available? No. This is something we developed internally. I mean, what I did here for that talk, I prepared a small demo application, and I also thought about providing that. But I've seen it takes much stuff around to make that example somehow sensible. And yeah, before making that open source, I would have to check also internally in our company. And before going into that direction, I just want to see see whether there's interest at all in that. So if you have some questions to that, I want to get some further updates, which I can just talk after the talk. Any more questions? No? All right. Thank you, Christian. Okay. Thank you.
Thomas and I'm working in the team where we platform, meaning we want to use it for different business domains. I will explain that later on. data processing applications that can be used in both ends of the spectrum doing it much database company is for retail, for tourism and for other Validations, there can be multiple things. We process. He did not copy it in there. We have to task as developers is how can we write the code is closely resembles the SQL Alchemy and where we objects and we have each row as an object and described in the application. So in both here, we need to have that business domain. What we wanted be updated. And this is a SQL Alchemy core the time is not sufficient for that, but it's knowledge, and we'll generate the SQL statements, talk, I found it might not fit that well for the SQL Alchemy is focused on a database can be done in the domain model also. We can note have to CSV load our configuration, but also what