To nest, or not to nest? Nested data types in Polars with big data

Speaker 1 [00:10]

Yeah, good afternoon, all. This is the last talk before the lightning talk today. And, yeah, this session will be 30 minutes as well, five minutes for the questions at the end. Questions can be asked through the talks.pycon.de. Please ask them there. And, yeah, the session is to nest or not to nest. Nested data types in pollers with big data Presented by Daniel Finn, and please warm applause.

Speaker 2 [00:48]

Hello, you hear me there okay? Good afternoon. So very happy here to present at PyData, my first time. And we're here in Darmstadt in this wonderful building called the Darmstadium. From what I understand, that's because Darmstadium, the chemical element, was discovered here in Darmstadt. And we're here, actually, I don't know if you've noticed, all the different rooms are different chemical elements. This one is helium. So I did wonder whether it would be appropriate to do the presentation in a very squeaky voice like this. Moving on. So my name's Daniel Finnan. I'm in the second year of a PhD contract at the Lirce Laboratory at the Conservatoire National d'Arz-et-Métier in Paris. My thesis is on decentralized finance, and specifically it's on centralized and decentralized exchanges and the liquidity flows between them. But I'm here today because I use polars extensively in my data pipeline. Specifically, I tend to ingest and create my features using Python and then onto R for my econometric analysis. So I kind of went down a little rabbit hole last year looking at nested types in polars. So that's why I'm here today talking about that. So what are polars? Nested types, so they're vector-like containers for storing multiple values per row. They have the same data type. Why would you want to use them? Well, you may think that it's better to structure the relations in your data using nested types, if you're maybe from a relational database background. If you like the idea of expressing the cardinality Explicitly, you might want to use nested types. And you may find them maybe a little bit more intuitive. Maybe it kind of triggers you to see observations, several observations, with just one variable changing. So very, very basic outline of a basic list. And I won't dwell on this for too long. If you've used pollers, you've seen these data frames before. What are the characteristics? Well, a list can be variable length. An array must be a fixed length. It's got the same square bracket syntax as Python lists, but these are not Python lists. Polar's assumes list by default unless you specify it. And the namespace is list, and R, or array, provide access to the methods. So just a little bit of context in terms of the Polar's library. And I've got to say, I've become a big fan of the Polar's library, and it's been rapidly developing over the last few years. Lists and arrays are a kind of long-standing feature in Polars, and more things have been added to the namespaces to give extra functionality. So for example, last year with filter, list aggregate, array aggregate, that was also added last year. Other expressions, such as filter on arrays, that's still in the feature list for the moment. So I wanted to show you a few examples. We're going to look at just some basic examples, and then I'm going to go on to the benchmarking, which I've done. And I wanted a few kind of toy data sets. So given we're here in Germany, I was kind of thinking, you know, what's appropriate to show Germans? And what are Germans known for? So beer, of course, very simple. The first beer there actually, I think, is a beer from, which is brewed here in Darmstadt. We've got three columns in this data frame, which are lists, energy, containers, and ingredients. The source for these few items is actually from a website which has a nice database of beers. So if you like beers, and maybe you're going to have a beer after this talk, you might want to check out the different beers on this website. Sebastian runs it. It's called Beer Universum. So some basic queries. Find all the wheat beers. Calculate units of alcohol for each container. determine the percentage of recommended calorie intake per container. So I'm just going to do this live coding, but I've got lots to go through. So we're just going to step through the code in real time. So you can see the data frame there. And it's activating my environment. And let's hope that the laptop doesn't die or anything unexpected happens. OK, so just to show you the data frame, we'll step through to the first, which contains on the list namespace looking for wheat malts, if you like wheat beers. How can I do that? I'm not sure. There we go. OK. Yeah, we're on the wheat beers. So it didn't show us our first query. OK. Well, you can see that we can pull out the results of the query here if we look in the debug window. But we'll run through onto units. So calculating, this is using two of the columns which contain lists, alcohol containers. We explode that then, because we've got some of the results which are empty, just in order to drop those empty lists. And then we show the data frame at the end. And for some reason, I'm not getting the results in my console, which is kind of strange. Yeah, it should still usually show it in the no, it should be coming in the terminal. Ah. There we go. I think I must have pressed it twice. No worries. Okay. Now you can see the results. We were on the wheat one. Well, it's picked out. Should have picked out, I think, three wheat beers there. The point really isn't kind of the results. The point is just to see a little bit of how the query structure is using the nested types. Next one on units. And you can see the calculation there. And finally we've got the calorie intake. So this is based on recommended calories per day from the German Nutrition Society. I don't think you'll be thinking about that later at the bar, though. And, OK, anyway, the point was just to show you briefly some queries on a very, very basic data frame. The next example I have, well, Germans are also known for their punctuality. So I thought that we could look at train punctuality. We've got four operators here, Deutsche Bahn, SNCF, so France, SNCB, Belgium, and SBB, which is Swiss. So you can see the construction of the data frame there. Some very, very basic statistics operating on the array namespace mean and standard deviation. And Deutsche Bahn kind of comes in second there in terms of the average train punctuality just after SNCF. And number of months below the EU average of 87%. So we're not using filter because we haven't got that yet in the array namespace. We're using the evaluation on the elements of the array below 87. And then we count the number of matches in that. You can see that the Belgians and the Swiss, they do quite well, really. And now looking at the mean across seasons. So we're pulling out various elements of the array and then calculating an average for each season. And this is just really to show you it kind of starts to get a little bit messy. I don't really like this, personally. The array namespace doesn't have gather yet. But if we switch it into a list, then we can use the gather. We can create another column, and we can have the seasons in there. And then we can start to gather them from the array and calculate the mean on those elements. So I'm showing you this because there are a few different kind of design decisions going on here. And maybe it kind of panics me not to see this sort of data in a panel format, really having each observation according to the temporal element. And really, it depends on your data format. But it also leads you down this road where the syntax for your array and list calculations kind of changes according to how you have it structured. And it can kind of result in something that's quite nice and expressive. Or you can kind of end up in a space where you've got something rather inscrutable, these series of methods on namespace that look rather convoluted. Also to say that the ability to work across element, sorry, to work element-wise across columns is kind of crucial to the usefulness of nested types. So now moving on to the kind of main part of this, which is to try and demonstrate how the different nested types perform and the storage implications. And I'm going to go on to an example which is kind of drawn from my work, which is using limit order books. So I ingest a large number of limit order books. And these are kind of a very basic economic design where you have a number of bids and a number of asks on two sides of the book at different prices and at different quantities. And so my work involves creating features from this and then using those features in order to econometrically look at the relationship between different types of exchange. I'm not going to use real data. I'm using some data which is simulated from NumPy just to make it reproducible. And I'm going to have the GitHub available for you so you can take a look at that if you're interested. We do a random walk which uses hourly observations over one year, so we're looking at about 9,000 observations and order book snapshots of 5,000 levels on the bid and the ask, and that's kind of typical for crypto exchanges. And the spread in terms of the book as well as the volume and the shape are randomly generated according to different limits that I've created using NumPy. So the goal is to compare the storage performance and the query structure, as I said. And in particular, looking at five different schemas. No nesting, a flat array, a nested array, a flat list, and a nested list. So just to show you a very basic idea with this random walk. And I simulated these using a Bitcoin price from last week, which is around $70,000. But who knows where it will be next week. I mean, it's very, very volatile. And the order book snapshots look like this. So at each point in time, the shape changes, the spread changes, and the volume changes. So with your 5,000 levels and around 9,000 observations, if you've got it in a flat format with no nesting whatsoever, we're talking about 43 million rows. And this is not massive, massive data. So in terms of my simulation, I first of all saved this in pickle format, and the file is around 1.4 gigabytes. So I think it's useful to see how Polar's performs, but it's not really massive, massive data that you might be treating if you're really doing big data stuff. Just to look at the structure, so you can see no nesting. We've got both an extra column for price on the bids and the ask and the volume or the quantity on the bids and the ask. With the flat array, we've got just two elements in the array indicating price and the quantity. And then the nested array, we have the whole thing nested together with the price and the quantity inside a larger array, which will be 5,000 long. Same for the lists. It's the same principle. And I'm not actually comparing here the variable size of lists, that feature. We are just looking as if the lists are just the same length as the array. So on to the storage. So I just went for the default here using the Z standard compression algorithm, which is the default in polars. And you can see that no nesting wins by far. You're talking about, this is in bytes on the y-axis, but you're talking about 100 megabytes difference, really. And we've got some strange peaks here, which I think is really down to the compression algorithm rather than Polar's itself. Z-Standard kind of sells itself as a good kind of balance between compression and performance. So I think there's something going on there with those peaks that we see. And you also notice that the peaks are much more pronounced for the nested types. So the queries for this, I'm looking at, first of all, a very, very basic query, the mid-price and the spread. these are just acting on the prices and looking at the maximum bid and the minimum ask and then the difference between the maximum bid and minimum ask then the total queue imbalance so we're then looking at the total volume and summing that and we're doing a calculation to indicate whether there's buying pressure or selling pressure on one side of the book and finally we're doing slightly more sophisticated query looking at the depth at a given level so So I've chosen here the depth of 500 on the ask side of the book. So we're first of all finding the minimum ask, and then we're going 500 levels up, and then we're summing the volume within that interval. And just to kind of demonstrate the query structure to you, I did actually have this in graph format, which is quite nice to look at, but in presentation, they kind of get a little bit unwieldy. So I've kind of bastardized the syntax here so you can get an idea of how the queries are constructed. You can see the nested versions. We go from a select into an aggregation on the array, and then a further get on the nested array. For the flat list, we're doing a group by and an aggregation, and then a get on the array. And for no nesting, we're simply doing a group by and an aggregation. The structure is pretty much the same for a list and array, except we're just changing the namespace. So for the queue imbalance, we're doing a select, array aggregation, then array get, and then further with columns. Flat list, flat array, group by aggregation, then a get on the array and a select. No nesting, select by aggregation. So you get the idea. And the most sophisticated one, we've got several things going on with columns. array, aggregation, array, get, with columns, explode, filter. You get the idea. You see the differing levels of complexity in the queries. With no nesting, we simply go by group by, aggregation, with columns, join, filter. And the join might possibly be expensive. That was kind of my thinking to begin with. So performance, what does it give? Does anybody have any ideas? Does anybody? No nesting wins. By how much? Not quite. So this is run just on this laptop, 100 repetitions, taking the minimum value of those repetitions. No nesting does indeed win. It's about two times better. Nested list, nested array, much worse. What's kind of curious is on the most sophisticated query, which is, I mean, not a crazy calculation, really. We've got considerably bad performance on the nested list. So, what's the conclusion? Nested types add storage overhead. The query structure can become rather inscrutable. Not nesting has much better performance despite needing to use both group bys and joins. And so, really, are the gains in the kind of relational structure, If you're a relational database person and you really like this idea of using the nested lists, is it really worth it? And would any further development really make any difference? And I can't really answer that question. I'm not really a Polar's core developer. But I really liked the lists and the arrays when I first discovered them. It kind of solved that little bit of a niggle that I had in seeing the same observations over and over again with just one value changing. And in comparison to looking at SQL databases, I kind of thought, yeah, maybe this is a nice way of doing it. But it's obviously not. And you just have to get over it, I think. So what's the alternative? Well, I played also a little bit with UDFs. Number works well, vectorizing it. There's also a great presentation, I think, at the last PI data by Mario Garelli, where he actually live-coded a Rust plug-in, which I wouldn't have attempted, but good on him. And I think that would offer also an increased performance gain. So, yeah, there you have it. The results of a little rabbit hole that I went down. and we'd be happy to answer any questions that you have. Thanks for your attention.

Speaker 1 [19:33]

Thank you, Daniel, for the deep dive in nesting. Yeah, we have for now one question. How is the performance with structs?

Speaker 2 [19:47]

with struts. I didn't play with that, although I did play with that using the user-defined functions. It's kind of the best way of passing stuff into the user-defined functions. So I can't answer that in any comprehensive way.

Speaker 1 [20:08]

Yeah, maybe someone has questions from the audience.

Speaker 2 [20:18]

Maybe just one quick note for like a use case outside of polars that I know of is Spark because Spark ML actually kind of wants you to use this format as an input for model training output. So I don't know, just a quick note. So Spark wants you to use the nested types? Yeah, the vector transformer function specifically. They want you to use like nested list, like kind of like array columns. Yeah. So this is just one single use case that I know of.

Speaker 1 [20:52]

Okay, we have a new question. Would the difference in performance be similar in other libraries or PewPython?

Speaker 2 [21:02]

In terms of the node library and the R library, is that what they...

Speaker 1 [21:08]

I don't know what was meant.

Speaker 2 [21:25]

You mean other libraries aside from polars? I'm not sure, to be honest, and I think it would probably depend on the library itself. I think they're using the PyArrow implementation behind what they're doing with polars, and they're writing it in Rust, so that all relies on vectors. So I think by adding the nested element, then you're purely, you're simply adding more and more vectors inside vectors. And so I think that's probably what destroys the performance. In terms of how that would perform on the other wrappings for polars in R and in Node, I think it would be the same thing. Probably anything that uses error. I think if you're going for performance with large data, yeah, I think the nesting is going to be a concern. But that's my kind of, that's my quick take. And I haven't done any benchmarking on any other libraries. So, yeah, you mentioned PySpark. And I've not got access to a, you know, to a PySpark setup. So, I'm not sure, to be honest.

Speaker 1 [22:45]

Have you tried extracting nested data with libraries like GLOM before importing it in Polaris?

Speaker 2 [22:56]

The intention here really wasn't about the problem with nesting. The crypto exchange data which I'm handling is nested in JSON. But this wasn't really a question about having problems with the JSON nesting. It's quite simple in Polar to specify whether you want to ingest it just as is or whether you want to create a schema to handle it as a nested list or nested array. But to answer the question, no, I haven't tried that.

Speaker 1 [23:39]

Is it RAM or CPU issue that's so expensive? Did your whole data set fit into RAM? Or was there some streaming from pollers involved?

Speaker 2 [23:49]

So, these were all done in streaming mode, but the dataset will fit into RAM. It wasn't bigger than 16 gigs, which is the RAM on this machine, and also on my home machine, which does regularly have problems with the size of the data. So, to avoid that, and just to get the kind of unadulterated benchmark, I didn't create a data set which was larger than RAM, but that would not be too difficult with the simulated data setup that I've got with NumPy. That could be added as quite a nice extra actually.

Speaker 1 [24:29]

Thank you. Does the flattening of complex data types cost any performance? What is your experience?

Speaker 2 [24:39]

But the fattening in terms of the width of the data set, I'm kind of imagining the questions about there. So this is a very, very simple, straightforward simulation on quite a flat data set. The limit order book doesn't have a lot of fatness to it. It's simply two variables times by two. So yeah, I think that would also be interesting to benchmark to go further on this.

Speaker 1 [25:12]

All right, thank you. Yeah, we don't have more questions on TalkSpike on the... Yeah, but we still have time. Maybe someone has a question.

Speaker 3 [25:36]

Thanks for your talk. What is the performance of converting the two ways, for example, from nested lists to no nesting? Because if the representation is nicer as a nested list, but the performance is better in nesting, did you look into this? How long compared to these scales does it take to convert the data frame from one version to the other?

Speaker 2 [26:02]

You mean at the ingestion stage when you're ingesting the data

Speaker 3 [26:06]

So let's say you have the data frame as a nested list representation, and now before you run your data processing step, you flatten or you explode these nested lists into your no-nesting version. How long would that take?

Speaker 2 [26:25]

I think that's a valid question. I didn't run those as a benchmark, but I think you're right. That's also a consideration to account for your whole pipeline in terms of taking the raw data and then turning it into a data frame, into a Parquet file. So, no, I didn't run those benchmarks, but they do take longer. I can't tell you to what magnitude they run longer.

Speaker 1 [26:54]

Any more questions No, all right, that was it then and thanks again Daniel and warm applause