Awesome Automated APIs with Automagic REST by Timothy Allen

Speaker 1: Yeah, we'll see how we do on time. Thanks everybody for coming out. This is awesome automated APIs with auto-magic rest. Good decisions, bad decisions, and pushing the limits of Django. And uh thanks to the practical dev for some of these fun fake covers I've included. Uh to Bartek for a couple photos from past Django cons that are in here. Bartek is our fantastic photographer. Glad to see him back again. And uh to my team at Words, Wharton Research Data Services, this was not a one-person project, obviously. So my colleagues are uh some of my favorite people in the world. And uh We're very responsible for a lot of the work you're about to see. So before we dive in, what is AutoMagic REST? It's basically something we've built that lets you automatically build a Django REST framework API on top of an existing database

Speaker 1: So if you have a legacy database, or let's say you download a couple hundred tables of baseball data going back to the 1880s. Automagic REST will build an API on top of that so your end users can look at the data without needing a login or an admin interface, although you can apply any permissions you want through Django Rest framework. Slice and dice the results, export them to Excel without you really having to do any hand holding. And while this might not be your exact use case that you need today or tomorrow, some of the boundaries we pushed uh could prove useful in many other areas. So without further ado, howdy y'all, I'm Tim. My pronouns are he, him. I'm an IT director over at Wharton Research Data Services, or Words as we call it

Speaker 1: at the Wharton School. We hosted DjangoCon in 2016. It was awesome. You can find me. I'm flipper PA pretty much everywhere. And uh aside from my full-time job, I help organize the Philly Python Users Group, uh, various community tech events like Barcamp Philly. I was a DjangoCon organizer for a couple years. I'm a Pythonista and Django Not and both a PSF and DSF member. I'm a fun-loving geek, a hockey fan, go flyers! uh guitarist and owned by a cat. Um I also happen to really like ice cream. So I've been at Warden Research Data Services for about ten years. And if you want to hear more about the ice cream comment, my good friend and colleague Ryan Sullivan, who is speaking over there right now in the other room.

Speaker 1: Uh has a really good story about my ice cream intake at DjangoCon 2017 in Washington. It's a it's a pretty frightening story So if you get a chance, talk to him about that in the hallway. So as Don mentioned, and I mean getting introduced by a friend like Don is just fantastic and part of what DjangoCon is all about. But if we have time at the end for questions, I'll be happy to take any that you might have. So I want to start off by saying thank you. I want to give thanks to the organizers. I know from personal experience that putting on DjangoCon US is a mammoth effort. And uh and you all do it with love style in class. So giving this talk will really be a highlight of my year. I really look forward to DjangoCon every single year. And uh special thanks to uh Heats, Kenneth, and Lacey, who all helped out with organizing, even though they weren't able to attend this year.

Speaker 1: That's how much people love this conference. They work on it even when they can't make it. And uh we really miss y'all I'd also like to thank the Django and Python communities, including the communities within communities like Wagtail, Django Rest framework, and regional communities like the Philly Python Users Group. I'm also pretty open that I'm in recovery. I've talked about this before. I started using Python and Django right around the time I got clean and sober. And I can't thank everyone enough for welcoming me with open arms and uh in many ways helping give me a new purpose. It's a testament to the quality of this community. But most of all, thank you all for being here. It wouldn't be a Django Con without the people. Y'all can have a conference without me, but I can't have a conference without you. I attended my first DjangoCon

Speaker 1: US in 2015, and I've made some amazing, amazing friends over the years. Not just people I go to to tech advice. for tech advice, people I go to for life advice. So if you're new, I want you to know that you can do that too within just a couple years or even a couple days here, because it really is a great community. So, without further ado, let's go into a brief history of Wharton Research Data Services, or Words as we call it around the office. It's a single source for leading global research databases. It was founded in 1992 for Werton faculty. It initially pro provided it was initially provided for SAS, the statistical analysis software, with uh standard and poor's and crisp data, the Center for Research and Security Prices. Our first academic client was Stanford University

Speaker 1: in 1997. It sort of happened as an accident when a Wharton faculty member left and went to Stanford and found out they were having a lot harder time doing their research. Our first governmental client was the Federal Reserve Bank of New York in 2003, and our first corporate client was Compass Lexicon in 2011. So it's multidisciplinary data, accounting, banking, economics, finance, insurance, marketing, statistics, healthcare. We specialize in entity management and data linking. It's available to It's available as a subscription service. And uh, you know, when we first supported only the SAS, only SAS was really supported as both a language and a data format. A few brave souls without any kind of help or documentation

Speaker 1: dove into C and Fortran for looking at the data we provide. But if we fast forward to today, We're still a single source for leading global research databases, but we're up to almost 500 institutions who use our product, and 50% of that growth has been international over the past six years. And you've probably heard of some of these fly-by-night schools that subscribe to our product. Harvard, Stanford, University of Chicago booth. You know the non-Warton ones. But when I started, um everything ran on a single Sun E20K, and we maxed out the RAM CPU and were pegged at 100% capacity on that. But since then we've switched to a sort of a sea of white Linux boxes backed by NetApp. And this has allowed us to grow our product for the 63,000 plus active users we have.

Speaker 1: And all of those users have access through our website. Through SSH and also through remote connects and direct connecting to Postgres. So yes, we are quite insane. We allow 63,000 users SSH access to our cloud. Through SSH, we support SaaS, R, Python, and more locally on our cloud that people can use. And uh we have about 400 terabytes of data raw and about three petabytes of a total storage footprint across our data center. So we're working at a pretty large scale of data. And trying to merge Django into this mix has uh has flexed Django's muscles pretty nicely. We're also continuing our expansion beyond financial data, while that's been our wheelhouse for many years.

Speaker 1: We are starting to get into more healthcare data and things of that nature. We use Sun Grid Engine and LDAP to support our horizontal scalability across all these technologies and uh and our storage and Permissions and access control is really our biggest concern because each of our 500 subscribers have a different set of the over 300 data products we provide. So it's all a la carte. And we have to make sure that these institutions only have access to the data that they have paid the data vendors for, because our relationships with the data vendors are very important to what we do. Over the years, having this size of data has caused us to make some very dubious database decisions. And a little bit of a spoiler alert: Postgres has made our lives a lot better.

Speaker 1: All of our research data, as I mentioned, was stored in SaaS in a very arcane format called SAS 7BDAT. We had MySQL backing our website. We had SQL Server also backing our website as we transferred from MySQL to SQL Server but never completely got rid of MySQL. We had our research data except for our New York Stock Exchange Trades and Quotes database all in Oracle. And then permissions became an ongoing problem no matter what we did. So we couldn't give our end users access to Oracle directly because we couldn't get the permissions applied properly throughout it. So on the old Sun E20K I mentioned we had all the research data in that SAS format and trying to get it to Postgres.

Speaker 1: um was quite a bit of a challenge that has uh taken a couple of years to get done. Um the attempt to store the research data in Oracle was sort of a partial success, but it only really worked. We could only use it from the web because we couldn't let our engine Users access it. And again, without the New York Stock Exchange Trades and Quotes database, it wasn't that much use. The New York Stock Exchange Trades and Quotes database makes up 80% of our total storage footprint I mentioned earlier. It is uh it's mammoth. It's every bid trade and quote that happens on the New York Stock Exchange since 1993. So you can imagine how much data that is. So I'm not saying any of this to slag any of the other databases that I've mentioned. They just weren't the right choice for our usage profile.

Speaker 1: I've used all of these other databases with great success in other situations. But when it came down to the permissions, Microsoft wanted us to use Active Directory, Oracle wanted us to use their solution, OID, and all promised they could get it working with our open LD app, but it never quite did. So when we entered Postgres. There was apprehension about yet another database, and rightly so. So the head of our division um was a little bit skeptical because we already had data in SaaS, MySQL, Oracle, SQL Server. And BDB if you include LDAP. So we're already five databases deep and we're talking about bringing Postgres in as a savior. This is a opera he has heard us sing before. So I do not blame him one bit for his skepticism.

Speaker 1: So we made a promise to him that we'd eliminate at least one of the others in six months, or we would move on from our promise of Postgres. And we managed to replace both Oracle and MySQL within six months. So the the the carrot that was provided of Postgres and the stick of having to get off the other database worked pretty effectively for us. So the New York Stock Exchange data was also successfully loaded into Postgres with a little bit of help from CITUS data's columnar store extension. Which allowed us to do it and also compress the data, which let us keep it down to about 100 terabytes total, rather than the 400 terafootprint we saw on raw disk with SATH data. So goodbye, Oracle. We are also in the process of con

Speaker 1: we are still in the process of converting our main Django backed site uh to Postgres from a Cold Fusion and SQL Server site. So we're continuing to reduce this database footprint. and get everything around Postgres, which has been working out very well for us. So all the research data was successfully loaded. I talked about Postgres. backing Django and that we're now using Django and Wagtail over Cold Fusion and WordPress. Uh Postgres also managed to integrate with our open LDAP solution. And we've been able to apply our ACLs successfully over on the Postgres side. So we can give our end users direct access to Postgres now with only permissions to the schemata that they should have access to.

Speaker 1: And uh the CITUS data extension has been a real find for us. So if you ever have to work with really, really, really big data, look into the CITUS data columnar store. They were uh they were recently acquired by Microsoft. and uh are continuing to expand their offerings for Postgres. So from that point, we've gotten a complete foundation built and we started building the RESTful API. So in 1992, Warden Research Data Services was initially founded so Wharton faculty could dump data to Lotus 123 The uh choice spreadsheet program of the time. And in 2018-2019, a restful API. So before I dive too much into the RESTful API, some jargon. Django REST Framework is a wonderful package for Django that I'm sure many here are familiar with that allows you to take Django models and also build around serializers and views to provide RESTful API endpoints.

Speaker 1: out of the Django ecosystem. It contains an enormous amount of tools which have made what we want to do possible. So the model serializers, views, filters, and permissions are at the core of what we had to do with Django REST framework. uh to have through LDAP and through Postgres and on the file system, we had to extend to Django Rest framework. For filters, we didn't want to have to build endpoints for each and every one. manually. So the filters had to be automated in some way through the views. The models and the serializers also we wanted to automate because we have a total of 30,000 tables and 30,000 aliases to the tables for a total of 60,000 endpoints

Speaker 1: within our total infrastructure. So trying to build this manually and maintain it manually would just have been a non-starter. So we built a code generator that introspected Postgres and uh we wanted a set of web browsable endpoints that were permitted by users so users could only see the endpoints that they had access to. And then we decided we were going to create filters for the first column in any index within the Postgres database. So the key here was that by introspecting Postgres we could map each and every part of Our Postgres database to the Django REST framework endpoint serializers and Django models.

Speaker 1: So by this mapping, we didn't have to manually create a single one of these models, views, or tables. So how did we build it? Here's the first step: field mapping. Using Django Rust framework with the 60,000 endpoints proved a challenge, but the first step was to map all the fields So if you take a look here, this is a Python dictionary, which maps Postgres fields such as small int, integer, and big int over to equivalents on the Django side with placeholders where we could insert other pieces that we needed. To successfully build out Django models. So for example, those squiggly braces right there might be where we insert something like a primary key equals true, because Django models require a primary key.

Speaker 1: And the second one at the end of the line with the squiggly braces. is where we'd put in things like if we had to map a column name. So I'll show that in a second. We created a Django management command to build the models by introspecting all this data. And we made the first column the primary key by default here, since this is built for a read-only environment. So the primary key does not become as crucial as it does in a read-write environment. So it turns out words like yield and return are not just popular in Python They're also popular in finance. So we ran into one heck of a namespace collision here. And I was looking through the errors. Oh, why can't I use yield?

Speaker 1: Why can't I use return? And it turns out that uh those are reserved words in Python. So what we did is put together an entire list, which you can get out of uh directly out of the command line of Python. from if you ever need to look up the current reserved words list. And uh we built a dictionary of all the reserved words and tacked on a couple more for Django Rest framework like format, limit, and offset. And for any columns that matched those, I know PEP 8 says just to put an underscore at the end, but just to be a little more explicit, we put underscore var at the end for those. So this was our first trick that we needed to get around because a lot of those 60,000 tables that had already been built and assembled by our data team, provided by our data vendors.

Speaker 1: had those words in them because they don't care about Python. They might not be using Python. They might be a SAS shop. They don't care about our reserved words list. And uh when you're talking about some of these data vendors or the US government, they're not likely to change them. So we appended var there, and the end result was we ended up with some models. So this is an example of One of the models built automatically off a Postgres table, which has all the necessary mapping for Django Rest framework in it, for the uh Center for Research and Security Prices fund summary. Endpoint. And it has successfully created us a model. You'll see that the first column automatically becomes the primary key. You'll see that yield var, since it hit a name collision

Speaker 1: there. has yield var as the actual field name, but toward the end of it we have the dB column specifically mapped back to the yield column within the Postgres table. You'll also see a pretty horrific hack here under db table. This is the first uh maybe don't try this at home, but it actually works. Django doesn't have support for multiple schemata yet. This might be a good idea to work on at sprints. A couple people have started work on it. If you want multiple schemata support for Django. But for now, what you can do is hack it by fooling out the quoting system within Django by specifying a db table explicitly here with some pretty horrible escaping, and it actually works. Thank you, Stack Overflow, for pointing me to this

Speaker 1: solution. It works for now, and that's something we have to test every Django version because obviously this is not a officially supported feature. But it has worked just fine for us so far. Once we get multiple schemata support in, if anybody is uh looking to volunteer to do that, please come talk to me. So most of our models had about 20 columns, but some had over a thousand. And this system allowed us to generate the entire API anytime the source data changed as well. So we literally have a script every night now that at 3 a. m. analyzes the entire Postgres database and rewrites all the models. And it's kind of nice because this has given us an added bonus that we didn't expect. We have all these changes under version control.

Speaker 1: now. So we can see how these fields change over time. Because a lot of the data loading processes we have are completely automated. So we're not necessarily even aware when Standard Imports copy stat updates a field or removes a field that it's gone. But now every night I you know every morning I wake up and I see the 3 a. m. auto commit to git that shows me exactly what fields have been added, what have been deleted, what if any have been renamed. It's sort of a nice sanity check to have as well, to have it all under version control. And uh moving on from there, those were a couple hacks, but it actually worked. But that led us to some big data problems.

Speaker 1: So while we're not on the scale of analyzing, you know, the human genome or anything like that, 100 tera in a Postgres database talking to our friends who actually work on Postgres. They like using us as a pretty big use case. And talking to our friends in Django, they're saying, you know, having this kind of database behind a REST framework is a really good test for everybody else because it's a fairly big one. But we do run into big data problems. So this gave us some several interesting points to solve. And while you know this might not be your exact use case, some of these problems are uh things we could all benefit from. First, with 60,000 tables, models, and endpoints, uh not many Django projects have 60,000 models within them.

Speaker 1: We had permissions issues. So 63,000 users across 500 subscribers all needed individual permission sets within Postgres and Django REST Framework API. The count problem. So with very, very large tables, millions and billions of rows, count gets very slow. So we do have tables like the New York Stock Exchange data that have billions of roads, and it can take minutes for a select count to come back. And with Django Rest Frameworks Default Limit Offset Pagination, it requires select counts. So we need to come up with a solution for that. I mentioned briefly filters. How do we let people search on this? We only want them to be able to search on indexed columns. So how do we automatically create them? And also, I don't know if you know this, but finance folks really love spreadsheets.

Speaker 1: So we needed to be able to get away so they can slice their and dice their data, but to really sell this to anyone, they're gonna be able to need to get this out into a spreadsheet So those were some of the problems we had to attack. Because when you have 60,000 models being imported and checked into Django, that takes long system checks to So I'm sure enough people here have used Django where when you first do your Django admin start project, my project, and you fire up run server for the first time, bam, it's right there. It's very exciting. You're like, oh, I'm gonna get so much done. And then as the project gets older and older, the run server starts to lag and lag and lag, and you miss one column and you have to wait for it to restart. Well this run server was taking over two hours to spin up

Speaker 1: on a machine with a heck of a lot of cores and 64 gigs of RAM. So that that was making working on it kind of difficult and especially with a code generator going on too. You know, you met one thing wrong in the code generator and it would blow up in absolutely epic and wonderful ways. So the way we solved that was kind of interesting. So if you take a look here, this is an example. We ended up building a generic View set rather than having a different view for each of the models. So when we had initially started, we had a different view, a different serializer, and a different model for each. We ended up getting into some metaprogramming and making the view set

Speaker 1: generic. So what we did, every Django Rest framework endpoint requires a unique base name. So we took a uh we took the opportunity here to establish a convention for our backend and overload it with a bunch of data. So within the URLs file, each of these base names contain Django's database identifier, because we don't use the Django default, so that would be the PG data The app name, so that would be data. The schema, in this case, would be CRISP, and the table name in this case would be DSF as you can see highlighted up there for daily stock file and concatenated them with periods. And this was nice because since it has to be unique within the DRF

Speaker 1: URL namespace uh we needed it to be unique within Postgres as well. So it ended up uh working out for us. When we extract those values in our generic viewset class and use those values to import The models, the serializer, and the permission for the view. What we've done here is kind of built a lazy loading mechanism. So rather than having to check all of these models for validity, at load time of run server or even worse, when we publish to production, this would take a two-hour lag where Apache would just be spinning while it was building this all up in memory. We made the decision that we would be okay to run into those errors at execution time rather than during a system check. And uh and that decision has worked out very it's worked out very well for us.

Speaker 1: So By using the getAder import module trick you can see over here, um you will see that that has actually caused the systems check of Django to kind of be pool fooled into not having to check it each time. So what's the end result of this? This cut down our load time from over two hours to about three minutes when publishing to our production nodes So it's much, much faster. And more importantly, for my day-to-day life, it made run server usable again. So I didn't have to, you know, make a maximum of two or three changes a day to see if they worked. So permissions for 63,000 users. This was another interesting problem we had to get.

Speaker 1: Since our entire service at all levels is backed by LDAP across the board, Web, SSH, Postgres, and the API. We do share a single username and password namespace across our entire infrastructure. But uh yes, just a reminder, we are quite insane. We are giving sixty-three thousand users access to both SSH and And Postgres, and we do spend a fair amount of time seeing if they're mining for Bitcoin on our servers. But permissions really have to be automated at this scale for each subscribing institution, or it's not gonna work. And since permissions are applied at the schema level, we basically just built a shim to inherit the same permissions that we already have in Postgres into Django Rest framework. So we built this check permission function, which

Speaker 1: you'll see the magic there, actually drops into Postgres and checks to see if the current user that's logged into Django has access to the schema and table that we're looking at So it's uh it it's just a little shim, and that's all it took, and it works for us. Um So the end you the the the user experience is when they log in, they now only see the endpoints they have access to. If they try to access one that they don't have access to, it tells them they don't have permissions. So Django Rust Framework comes with a really amazing uh set of permissions tools that let you extend them, including fairly straightforward tricks like this. Speeding up the slow count was a pretty interesting problem to solve as well.

Speaker 1: And I know other people who've worked with Django Rest framework have run into this. My friend uh Jembe was giving a talk on this. Last year at this very conference and this was a problem we were talking over because he was running into it as well. And this is one of those moments I've had a lot of great moments with Django over the years where I've been looking to kind of hack something in on the side. And then the next version comes out and it has the exact feature I was looking for. This was one of those moments. So if you take a look here, you'll see there are a couple highlighted points here where I have if table estimate count is greater than a million. What I ended up doing was using Postgres 's query analyzer here to come up with estimates for how many rows it's going to be rather than the exact select count. To cut down the time from minutes and minutes and minutes to just a couple

Speaker 1: milliseconds. So what we're saying here is we do an estimate count at the very top, that's the select star from the schema and table made lowercase. And if that estimate says there's estimated more than a million rows, within the view we are going to switch the pagination class to this count estimate pagination class I wrote. Instead of Django Rest Frameworks traditional limit offset pagination. So what this will do is override Django Rest Framework's pagination class and use the estimates rather than the exact select counts. thereby eliminating them from the from the query plan that has to be executed to give you the endpoint. And uh much to my shock, it actually worked.

Speaker 1: And I had initially done this all in raw SQL, but then Django 2. 1 came out and added queryset. explain. So if you look at the second highlighted part, you'll see it says parsexplain. And then Query set. explain. This will give you the explain of any query set you have within Django starting in version 2. 1. So that eliminated a bunch of ugly code I had written to parse it. And now I just had to have the single regex to parse the explain syntax to give me the estimate count of the rows. And this has worked out really well for us. So it's not always exactly the same, and we've modified the pagination code on the front end a little bit. So that you just keep clicking next and next and next and the last few pages might be off by a few when you're talking millions, but for things like data browsers, who's really going to notice?

Speaker 1: You can keep clicking next and the last five pages might be blank due to a miscount. But would you rather have your end users waiting? three minutes for a result because of a slow select count or a couple milliseconds and just have the page count be off by one or two when you're talking thousands of pages. We opted for the latter and it's worked out very well for us. The next trick was getting filters in for just uh indexed columns. So we wanted our end users to be able to use Django REST framework. uh filters to do searches and slice and dice data, but only on columns that were already indexed within the database itself. So we came up with this uh SQL query against Postgres 's information schema

Speaker 1: Which gives us the first column of any index within Postgres 's uh entire database. And we check it table by table. So for each table we go through and we pull out this metadata. And then you'll see we've written some logic into this custom view set which says depending on what type of field it is. If it's a car field or a text field, we'll add it to be a searchable field as well as a filterable field with the correct kind of filters. You can do an exact search, a contained search, a starts with search, or an ends with search. And if the field type is instead an integer type or a numeric type or a date type, we'll allow you to do an exact search less than, less than or equals, greater than or greater than equals. And um

Speaker 1: we use the Django Rest framework filters package. I want to give a shout out to this package. It's um it's It's a drop-in replacement for Django filters in many ways, but also gives some additional features, such as the ability to do complex querying, which I'll show in a couple minutes. The final big gotcha was spreadsheets. So our users can now slice and dice their data with all these filters we've built, but finance people absolutely love their sh love love their spreadsheets. So Django REST framework ships with several renderers, the browsable API, JSON, uh there's one for XML, Django HTML templates, even the admin. So what we did is

Speaker 1: we built and open sourced a renderer called Django Rest Framework renderer XLSX, which literally takes your results for any Django Rest framework endpoint. And just adds an XLSX option. So any point with any data you have, you can get Excel spreadsheets out of them. So we'll take a look at that in a second. And uh you can add that to your Django Rest framework package today. With OpenPy Excel, it'll just generate them for the final endpoints. Um there's also been a lot of great contributions for the community there for things like styling the spreadsheets, adding logos, things of that nature. if you need them. It's all pip installable. So we have this to-do list When we first started, this was kind of started as a skunk

Speaker 1: work project that I did a few years ago, and then suddenly one of our Fortune 500 clients uh pretty much wanted it tomorrow, so it became a high priority. That was a to-do list, but over the past year it's really become a done list. So before we open sourced it, these were all the things we wanted to get done. And uh we've managed to go through them all and get them done. Um Django, Django Rest framework, and Postgres all allowed us to address these needs pretty quickly. And uh it really went from sort of a personal Skunkworks experimentation project to production in a little over a month. And uh while the Auto Magic Rest package we've released is definitely for a niche,

Speaker 1: Some of these techniques we've come up with, I think, could be useful for many, many Django projects. And I learned a lot about the internals of DRF and Django itself. So I think it's worth sharing and showing that yes, Django can really scale with a couple little tricks to handle this kind of data and be a pass-through. So without further ado, let's all uh pray to the Wi-Fi gods a little bit, because I know it can get trickier. I'm actually treat cheating. I've switched over to my hotspot. But uh let's see if we can actually get away with doing a little bit of a live demo because I think seeing is believing in these cases. Um but before we go there, if uh any of you want to get in touch with me or want to talk about working at the Wharton School Feel free to catch me in the hallway track. And uh

Speaker 1: I will quickly switch over to uh mirroring mode. And you'll see I've cheated a little bit here, but here's the actual front of the words API. Good, I'm logged in. And uh if you're looking to steal data, you will be able to use this token for about the next oh 10 minutes and 36 seconds, I guess the countdown timer is at. Before I change it back. But this is the actual front end that our users log into for the API. So we do provide documentation, and a nice thing we've done here is we use Django REST Frameworks Auth tokens. And we actually give the end users examples where we inject their auth

Speaker 1: token right into the code so that they can actually copy and paste this and run it and get results right away. So this will take a second to come up because it has to repaint the screen. But if you're wondering what 60,000 endpoints looks like in DRF , So there we're up to the B's. If you look at where my mouse is on the right, oh, we're up to the C's. Yeah, it goes on and on and on and on for quite a bit. So when you actually see this visually, it gives you an idea of what we've built here. All of these are automated. Each of those has a model behind it. Each of those has a table behind it. And the entire process being automated allows us to make this a reality.

Speaker 1: Um, but this is you know living proof here that you can do really big things with big data with Django and do them quickly. If there's ever a question about Django scaling, I think between Instagram on the one end of having lots of people looking at it and how much big data can it handle behind it, you know, there there are plenty of good examples out there on just how flexible Django can be But why don't we look at an actual practical example? So what I've looked up here, a QSIP is an identifier for a company that never changes. So while Apple, we may all be familiar with Apple, has the ticker A-A-P-L, tickers change over time. QCPs do not. So if we take a look here and grab Apple's QCI, you'll see it's right there, 037833100.

Speaker 1: This endpoint right here is the one that I've been talking about most because it's fairly relatable at the Center for Research and Security Prices Daily Stock file. So what this is is end-of-day pricing data for companies going back to 1925. So what I've done here is I've put Apple's QCIP in. And instead of 10, I set the limit to 100. So we can actually see right here end-of-day pricing for Apple from the beginning of time. So if you're wondering when Apple went public, it was on February 4th, 1981. That's the first record. And if we scroll down, you'll see each of these is an individual day. There's February 18th, 1981. All the way down, so here's the first hundred records.

Speaker 1: And you can also see here under filters that we've automatically added filters for ordering and all the fields. So within Django REST Frameworks browsable API, it lets you take a look and slice and dice right from here. So we allow our end users access to this directly because I think this is a great teaching tool for them to understand how RESTful APIs work. Because they can literally see the URL change as they add their filters and quickly things start to click. So you'll see here that I've got the QCIP contains in. That's just the one example. But it does go through for any indexed column. And you'll see here under the GET request that you now have JSON

Speaker 1: API and the Excel spreadsheet. So I'm gonna see if this actually works and hit the XLSX. So what they should give us is only Apple's pricing in a spreadsheet for every day since they started. And voila, there's a file. Let's see if it actually uh did it. It's saved. And voila! There you have a spreadsheet of all Apple's end-of-day pricing going back to the start of time in 1981. I'm really glad that worked. If anybody else is doing a live demo, make sure you have a hot spot because conference Wi-Fi is uh never quite that good.

Speaker 1: So um That's something you can pip install right now and get installed on your DRF endpoint. So just pip install drf-renderer-xlsx. I've got to come up with a better name for that. But uh it doesn't exactly roll off the tongue. It's also featured on the Django REST framework documentation. So if you're looking to get that kind of functionality in. That's something you can also call from call as an endpoint from anywhere with variables directly just through a GET request. So it's not it doesn't just work through the browsable API. We've also built in, under options , Django Rust framework ships with a fairly straightforward um set of options for options calls that returns information

Speaker 1: about um about what fields are available and things like that. We've modified this slightly. So in addition to what Django Rest framework provides We have also provided a full list of the fields here, what type of field it is, and whether it's a filter field. So what this allows us to do is query the options for an endpoint, and on our JavaScript front ends. We will know whether or not it should be a searchable field by looking at that filter field and also what type it is, which can be useful on your front end. So providing this additional metadata allows our front end to do some pretty neat things I told you about Django REST framework filters. These are some of the things it allows us to do, more complex operations. So these are the basics, the exact

Speaker 1: less than, less than, or equal that you can provide in the URLs But Django REST Framework Filters also allows us to provide filters like this with basic and or or logic. So if I were to actually copy this, what they should do is provide us data from the endpoint for the first month of 1991 where the symbol starts with C, or data from the first month of 1995, regardless of Whether or not it starts with C

Speaker 1: If you're wondering, Emacs is the greatest operating system of all time. So it's running, it's running, it's running. And you'll see here what that's done, copy and pasted with that complex filter that we just saw with the and and or logic is return us everything from 1995, regardless of the start point, and then when we get back, you'll see it only provides 1991 where it starts with C. So I've got to give a big shout-out to that package. So that's one example, but how did this work on the front end? So we give examples with jQuery data tables where people can uh create their own front points, but if we take a look here.

Speaker 1: If I come over to the daily stock file that we've been talking about , and hopefully it logs me back in. I had it queued up for you, but of course the twenty minute timeout got me. All right, we'll come back to that in a second. Hopefully it actually logs me in. But uh the project is up on PyPI. So any of these things you've seen are now open sourced. in two different packages. There's Automagic REST, which is available, which covers most of the things we've seen, like the automatic creation. So if you have a secondary database and you want to build an API around it.

Speaker 1: you should be able to right here. We completely genericized it, so there are a lot of different options here. So you can subclass the actual Django command and override any of these commands. uh to change the database name, the owner, the root Python path where you want it installed. It all comes with defaults, but these methods can all be overwritten to give yourself options. And it comes with a couple command line options too for the big ones, like the database, the owner of the schema, and Postgres and the path. Right now it only supports Postgres. But it uses Postgres 's information schema pretty heavily. So to write support for other databases would mainly be to tweak out those comments that get the metadata. And it's all been working well for us in production now.

Speaker 1: We've been using this for well over a year. And uh, as you can see from on here as we do the searches, it's pretty lightning fast. So if I were to change, if I were to just refresh this screen, you'll see bam, it comes right up. If I change uh Let's do it, change it to 50. Comes right up. If we want to go to a different endpoint. Let's go to the monthly stock file instead of the daily stock file. Bam, it comes right up. So for 100 tera of performance in some of our biggest data sets, that really isn't too bad on the performance level.

Speaker 1: And finally, this page has come up. So, what does it look like on our actual front end? If I come over to the dataset list for the CRISP Daily Stock file, You'll see we have this data table here. This is actually pulling from the restful back end. So you'll see all the data is there. And if we want to do that same example we just did with Apple's QSIP, I can come in here and grab the QSIP. Just to keep things consistent. I can come over to our controls pages. And what this has allowed us to do is allow our end users to basically build an advanced query form here. So if I come down here.

Speaker 1: You'll see these are the fields that have been marked as filter fields. So those are the ones that have the index on Postgres all the way back end. It carries through all the way to the front end. So if I say a q sip equal to apple's qsip and apply filters It lets you know what we've run. Now if I come back to the data table, you'll see we're back to the same example we looked at of Apple where it started on February 4th, 1981. So that's a full example of what we've done. If you want to check out Automagic Rest, just Google Automagic Rest and it'll take you to PyPI or GitHub. If you have any ideas for this, I'd love to hear about it. But most of all, I just want to say thank you all again for uh coming in and checking out some of the crazy weird stuff we're doing over at Wharton.

Speaker 1: Thanks very much.

Speaker 2: So I'm a junior software engineer, so I'm not quite sure why um a d um a giant application built on sixty thousand models are not like divided into microservices. Um Why why is that decision? Because the way I'm thinking about it, like larger applications that size could definitely be because we wouldn't um face those kind of problems if if it's um It was a smaller, like multiple applications. So that's my question.

Speaker 1: That's a great question. And we do run into this a lot. And what it comes down to is we have attacked that sort of That comes from the engineers' perspective. You're absolutely right. But for our end users in finance, they want us to get out of the way so they can do their research. and having a single spot where they can go and not have to think about it. A lot of this data is also denormalized because these are people who live in the spreadsheet world. That is how they do their work. We have to get out of the way for them to be able to do that. And if we went to a microservices architecture, they would have to potentially log into different spots. They would have to go to different places. So by having one database backend that they can go to for all the data that they have access to

Speaker 1: removes a whole level of cognitive dissonance and work they would have to do potentially to also join these tables We have over 307 products from a bunch of different data vendors and we're one of the few places where, for example, a PhD in finance can crunch down their algorithm on the New York Stock Exchange data and then bounce that off of joined to a completely separate vendor's database to see if that's affected by effective uh executive compensation. So, you know, do bonuses actually improve the performance of a stock on the market? They can do that kind of thing by keeping everything integrated within Postgres. So yes, it's that's where my brain immediately goes first too. But there there is uh there is a reason for our madness.

Speaker 3: Yeah, so as you said there's about 80,000 endpoints. Has anyone ever run out of memory displaying the list only?

Speaker 1: So you'll notice um I I regularly am a Firefox user, but when I'm displaying these endpoints here, I am using Chrome. Because yes, the browser does choke on it. So we've gotten to the point now. So for every there's a new endpoint added for every day of New York Stock Exchange data. I am going to have to rewrite the template here to be a little more efficient because it actually does chew up all the browser memory in certain cases. So yes, good eye. You've found one of our weak points.

Speaker 4: Thank you so much, Ted.

Speaker 1: Thanks, everyone. You were awesome.