Migrating legacy data to your Django project by Adolfo Fitoria

Speaker 1: So thank you for coming to my talk. I will be speaking about migrating legacy data to our your Django project. So Hi, my name is Adolf Vittoria. I'm from Nicaragua, a country located in Central America and yes, it's shaped like a Dorito. So I work for a company called ePublishing. They are providers for Software as a service solutions for news outlets as and publications and they are the owners now for Ellington. How many of you have heard about Ellington? Okay. So Ellington, if you

Speaker 1: the majority of the group that doesn't know it, is the first Django project ever. Basically, Ellington was the reason why Django was created. So Ellington is the CMS that power the Laurent Journal War back in the day and it is still being maintained, it's alive, it's great. So that's my work currently. So let's get into it. Legacy data, what is it? So your applications, we always talk about legacy code, about cobalt and the Y2K bug. about all this fortune code. But we also have to talk about legacy data. It's the data that comes

Speaker 1: from old old times. Like Humans we have been doing data since we have been talking and speaking. So back in the day we draw into caves. Then we got that into paper, then we got into computers and we got it into magnetic tapes and all these old storages. Then we got into diskettes and zip drives. Anybody use zip drives in this room? Okay, we are all. And and also these drives and cloud and everything that you can put data on. So but in your Django apps, it's basically two things: your database And your media files or user uploads.

Speaker 1: Django is awesome. That's the reason that why we're here. And SpecDB This management command that comes with the framework trace care of creating models. py of a legacy database. Inspectivic has many options. You can just inspect one specific table, but in this case for this talk, I will be doing a migration from WordPress to Django. So I wanted to get the whole WordPress database that is small enough so we can digest it in this talk. And it's a good example of what we are going to do in the future. So Inspectivity is great, but it's not super magic. It's not fully magic. Sometimes it fails

Speaker 1: when they have When you have a database that has uh a field that is not supported by the Django RM, in that case Django puts a text field And it leaves a comment that says, hey, we cannot guess this field. So come come back again and make it better. You can implement your own field class. So it works great. Django RM needs a primary key. It's a rule and we cannot avoid it. Sometimes we have old legacy data designed by People that maybe didn't need a primary key, so we have to either or add one explicitly or see if there is a primary key on the table

Speaker 1: and it's not correctly um. indicated in the table structure. This happens a lot with WordPress. Within old versions of WordPress, you have all the tables, they need to have any primary keys. And even to this day they have primary keys by they don't have relationships between tables. Everything's everything goes through a single app. You can then split it manually. You cannot work with database schemas. It tries to guess it, but it's not perfect. So you have to manually At the debut table trick that we saw last day with the first talk by Tim Allen. Um Legacy data has surprises.

Speaker 1: You will have stuff that won't validate to your new schema or to your new database, so you have to work a bit and do some database massage as I would call it And not all DV engines work the same way. So some field length varies between the engine type, so you have to adapt to that. So, how do we do this? We can take advantage of Django multiple database support. Basically, the first pace, the first um step is to add a new database That is our legacy database. We point it to the right hostname and driver and everything. And we have our default database that is our target database.

Speaker 1: Then we I what I usually do is start an app, a new app, empty app called legacy. Then I add it to the settings. py um in the install app settings And then there's something very important to do. At least I like to do a legacy router. Basically what I say that if the app name Is legacy, use the legacy database. If the app name is not legacy, use the default database. So in that way we can save a little bit of typing. Instead of doing uh model dot objects uh parenthesis using equals legacy, we can just do model. objects dot

Speaker 1: filter or whatever we can do. We add that router to the settings. py and then we start the magic. We start to do the manage. py, inspect db. dash dash database equals legacy and then with the greater than sign you can redirect that output to a models dot the models. py file So you have something like this. This is the structure for just one table, but normally that model is is huge depending on the database size. And then you have to investigate and look how the data is in your legacy database. You can use

Speaker 1: uh many tools for this wherever you feel comfortable. I tend to use the um uh SQL terminal Also by reading the models. py file is a great tool. But we have something, a third-party package called Django Extensions that has a function called Grass models that is a management command that produces this beautiful schema uh relational schemas uh in a picture so you can print it out And have it with you as a chit sheet. So you can have it always there. Now to work. So the approach that I take is to create management commands for each

Speaker 1: use case. And then I work from the less related model to the most related model. What I mean by this is that the less related model is the one that doesn't have dependencies on other tables. So In this case for WordPress, that would be a category model that everybody depends on that model, but that model doesn't depend on anyone. And then you are going to build up from the top to bottom the dependencies of each model. And this graph model command helps a lot because, well, in this case doesn't it doesn't look uh doesn't have any errors because WordPress doesn't have relationships, but if the database has a foreign keys

Speaker 1: You will see the relationship between each table so you can know each dependency that it has. So my application is very simple, just for demonstration. It has two models, one called category model. It has just the name. And the second one is a post model. It has a title, a body, a photo. There is an image field, update, and category. Then we start with the model that has less relationships. As we saw, category doesn't have doesn't relate to anything. And it's called by the post. So we cannot start by the post.

Speaker 1: We have to start by the category. So I don't know if you can see it right in the back, but I can make it Bigger. So basically it's a work uh management command. Uh in the handle function you add uh the query set that you want to import. In this case WordPress stored categories in a table called WP terms. And for each term from WordPress, I create a category on my application. So you that's the translation part that you do. Then the second part should be just importing the post.

Speaker 1: In this, uh I'll just query the target table that is the WP post. I do a filter because in WordPress the post table also contains images, draft, also contains pages, attachments, and many stuff Not just post. So I I filter by post type post, then I do a for loop. And insert the tables, the data from the Ruby post to my post model, there's there is a title, body, and pop date. For categories, we get the category by querying the WordPress database. In this case, it uses a intermediary table called

Speaker 1: term relationship. And then you have to query the loop terms table to get the right category from the database. It's always important in this case that you can you have to be prepared for cases like a post don't that doesn't have any relationship with categories because WordPress allows it But and this case you have to always add a good default that can be good for your application so it doesn't break. In this one I'm I'm not importing the images, but continuing

Speaker 1: with that. Just guess that the first part is the same and then I get the image. The images in WordPress are stored in WPPOS table also with um With a post type called attachment and then I get the image and save it into Django using the URL. Basically, the image in WordPress is a full path URL with domain and and full file file extensions. Um You have to do it this way to be able to respect your media um video file upload and backend in Django. So if you're using S3 or file system, it will just

Speaker 1: use that and you have you don't have to worry about special setup. And then you run these commands and wait and wait and wait depending on the size of the database. I have done migrations for Stuff that takes from one hour in a good case up to eight hours with my computer being very hot in a way that you can cook something I'm the top of it. So let me just show you quickly how it looks. So this is my administration. It's empty right now, but for the legacy it has post objects

Speaker 1: and user objects. This is the WordPress site. It's a basic site. And this is my Django site that is empty. Can you see this uh on the back? Okay. So we first import categories. Then you will see that on my admin site you will have the two categories that are in my demo site. And then I can have pause. And then the post with images, so

Speaker 1: There there. So if I reload the site, you'll see the imported data in your Jagmo application. So this is not enough. Um getting back into the waiting icon. This is not enough because this is just a sample and it was just um not optimize enough. So we have to then optimize all the things. This was not very efficient and we can make it better. So Less queries mean more speed. You'll be you'll be reading thousands of data uh depending on the or your database structure. Um You will have you want to avoid queries in loops.

Speaker 1: So there are techniques basically to not having Each loop for loop that we are going to do making queries that can be catched or repeated. You can use caching. Um more importantly, when creating new models, each time you you create, you call save, like in these cases. It's an is a query, it's a connection being open. So you can use uh a method called bulk create that will group many models and just use one query to insert them all. You can also um query just the field that you need.

Speaker 1: So in this case I will just need it post title and body and category. And we don't have to query this whole field things like post name to ping, ping and postmodifying, all that stuff. And you can use pagination. Why? When you have a table that is very large, when you do object. all It takes a lot of memory to do all that serialization from CQL up to Django objects. So it's better to run some pagination. Um like saying it struck me a thousand records each time instead of all the records at once.

Speaker 1: So your memory consumption is less and And the script runs faster. I did this error back in the day and and then I saw that when the time passed the actual and slower and my RAM was getting to 100% every time. So using pagination it allows you to keep a decent RAM usage and also keeping a decent Processor usage. You can see more on these advices on these two links in the button. I will provide the slide links at the end so you can just click them So we have the optimized version of both

Speaker 1: commands. So in this case I have a dictionary called category catch that will save the ID of the category and as a key and the value of the dig will be that category object so we can save it Post data will be a list of post objects that will be used later in bulk create. And the only fields are the fields that we only we just care about the post table In this case just post title, post content, post edit, and ID. We have a default category and we have the new query set that is slightly different, just by adding only And the only fields that we need.

Speaker 1: In the loop, it will be almost the same, just that In this part I will use the local caching of categories. Basically, if the key of the category ID is on the dictionary, I will just return that. And if not, if it's not in the dictionary, I will query the database. So in this first part, we are avoiding queries in loops. We will have them, but it will be much less because in this pattern of categories and post objects, categories can be a few and the post objects will be hundreds or thousands. Then we have this

Speaker 1: post data. atpend post. Basically we add the post subject to the list. And in the end, we just have a bulk create um function that takes that list and do a save with just one query. This bulk create function has another parameter for The quantity of objects that you want for each query, so it does somehow some kind of pagination in the inserts. So you can say 100 objects at a time, and if you send it like 500, it will do five queries instead of just one. So this helps a lot also in performance. So in the past example with this small data

Speaker 1: dataset, the before the optimization, it uses 14 queries. In the default database and 19 queries in the legacy database. And for the after example that is optimized, it uses seven queries for the default database And 13 queries for the legacy database. So it's an improvement, at least for the default database, in 50%. and and some percentage on the legacy. This will be quite different depending on your use case and your data structure. So in this case it's really good, but maybe you cannot avoid Um much queries in your case.

Speaker 1: So there's another um thing that you can do um is that you can use this technique to migrate um stuff from one database engine to another without Doing any transformation. Um so just basically do a model. object. all and save it to your target database. So there's stuff that I didn't do that maybe you shouldn't know is that validating all the things before inserting it into your production database. The bulk create and also Django object. create methods do not apply any validation that are inside of the save

Speaker 1: method. So you want to have you have to take care of that beforehand. You have to also see if you really need all the data that is on your legacy database In that case, you you don't carry any um data yet that is not is not clean or won't be used again. Also, in this case You will have to clean data up, for instance, and when migrating stuff like content sites, maybe some Content has some tags that are not valid anymore or that you don't want to include, or you want to clean some stuff, like

Speaker 1: for instance, if your content size has has um all YouTube embeds codes that won't work because YouTube started to use iframe embeds, you will have to transform those in your content before. Prepare for exceptions, they will happen. You don't know what the data looks like. So You have to take care of those exceptions on those cases. In my case, um I once imported data from HTML files. dating back to nineteen ninety eight. So in some cases um the file were empty or they had

Speaker 1: inconsistencies on the markup or they had basically trash or they were hacked and back in the day where defacing was uh was popular. So all those cases you have to take care. Um this is not a work that That it will be super fast to do. It's fast to code it, but then in the testing phase, you will you will take some time to see that everything is okay. And this is not limited for database only. You can do this technique with CSV files, for HTML files. You can scrape a site that already exists and create a crawler to fetch the data.

Speaker 1: You can use, well, there's another case that I did in the past that a site uses access as a database for production. So basically what uh we did is to export access to XML, a query in access to XML, and then create an importer for doing that. So please if you still use access as a production database, please try to migrate it and don't do it again So the takeaways will be migrant data will be tricky always. Is nobody Nobody takes care about data a lot until you do these processes.

Speaker 1: And data can be dirty, can be incomplete. It will always be tricky. Django Inspect DB helps a lot in the process, but it's not a perfect tool. It won't ever be because databases are really hard. Everything is a loop as an inside of a management command. You can take this approach or you can use something like a normal Python script that calls Django. Less queries equals more speed in inserting and also reading, and every import case is unique. So that will be all from my side. Um

Speaker 1: thank you very much for attending this talk, and I will take questions. If you like. Thank you.

Speaker 2: Thanks for the talk. How many rows?

Speaker 1: Well, the largest one was this this with files that I told you about from ninety eight to two thousand and six. So it was around Um without the dirty files, around one hundred fifty thousand.

Speaker 2: How did you manage like when cause obviously you ran into dirty data and it would break, you'd throw an exception, not caught, you know, because you're like, oh, didn't see that one coming. How did you roll back? How did you manage your rollback to like, okay, I've already done this part. I don't want to touch these again. you know get me to this point.

Speaker 1: Yeah basically uh what it did in that case is I prepared the script to be incremental. So I printed out IDs. So when it broke, you can see the last ID that was inserted. So we can just create a filter from this point on and you can help in this case. But for files that are static, I also created like a small table, temporal table, that was storing what file was processed and what article was created. um uh from that file so you can have control on where do you left uh where do you broke the last time. Thank you very much.

Speaker 3: Any more questions?