Going beyond Parquet's default settings – be surprised what you can get

Speaker 1 [00:06]

So, hello and welcome. I'm talking to you today a bit about what Parquet is a format and how you can use it a bit more than just using the default settings, but get more effective use out of it. I'm Uwe. You can find me on all online media with this handle called Xochi. I'm mostly now a manager, but I previously worked mostly as a data engineer. And what makes me larger to talk about Parquet format is I worked on the Apache Parquet project since some time now and I'm working on other open source stuff which you can also ask me about but a lot of my time has been spent in the past on Parquet and all the details of it and that's what I'm going to introduce you to a bit today. What first comes down is why actually use Parquet instead of like another format like CSV because CSV is really comfortable. You can read it row after row. As a human you can modify it with any text editor, you get more insight into it. But on the other hand, there's the trade-off. It's a row-based format. That's maybe not the best thing for our algorithms to work on. And human-readable format normally implies it's not machine-readable, because machines read something else. And if you want to have, like, a format that's really good for your machines to work with, that's efficient for, like, analytical workloads, Parquet has become nowadays the default. And I'm happy to see that, like, most of the audience have heard it before. And most of the non-technical audience and a lot of the spam that's coming is to the Parquet mailing list is all about this type of flooring. It's a beautiful flooring. We get a lot of spam on the Parquet mailing list about bulk sales of truckloads of Parquet. But also, it's a good name for the format because everything in the Parquet file is organized in columns, same like you would do the flooring. And it's also on disks, bit on the floor and on this format it's there to persist your data somewhere. It's not meant for using it in RAM. You could theoretically use some of the approaches but just the sweet spot is persist some data which you then want to use for an analytical workload. And that's how it started in 2012. Cloudera and Twitter, they had this use case where they wanted to analyze log files but the log file search they're using was just getting too big and too inefficient and then they spent some time in developing a format that's just good to read, to analyze, and so on in a long-term format. And later on, they put it into Apache Foundation, make it Apache Parquet, and then it became, like, standard format. But it took, like, four years in 2016 to actually have Parquet support fully in Python and C++ so that all the PyData ecosystem could use it. But it has been already before. It has been the state-of-the-art format used in the Hadoop ecosystem. Like, basically, every analytical data it could process parquet files and nowadays nearly every tool that can process data and do analytic data is able to read and work with parquet files and even like do the native operations on these. CSV is still better supported like that's the one format every tool supports but I think like 80% of all tools support parquet and this is down to some really clear benefits like saving things in columnar format is just really matching the operations we do on it. A lot of people tell you in the NumPy tutorials you get things fast by using vectorized operations. Parquet is just doing this on the file level to do vectorized operations and serializing, dual serializing your data, and also dual serializing already in a format that is good for these vectorized operations. It also has the benefit, it knows what kind of data it's storing, so it can use something called encodings to already store it more efficiently with not so much of work, but on top of that, it still uses compression, but compression in the way that it's adjusted to the format to make it even smaller. But compression works without actually knowing what kind of data it is, and therefore it's a bit more expensive, but still working quite well. Another feature which makes it good if you do analytics on top of it is you don't actually have to load the full data from this, but you can use predicate pushdown. Predicate pushdown is something like if you have a SQL query and then in your workloads you're saying age should be bigger than that and so on, And Parquet has information in the file that can match what is in this work clause down to what is in your file and only load the part of the file you actually need. And they're safe on network traffic, safe on de-sterilization time, and make everything a lot faster because these things are also quite expensive. And it's widespread. That comes down to the part it's not specific to a language. Like, there's nothing in the format that makes it, like, really specific to Java or Python. You can just use it everywhere. It's just Fidget, naturally. and it's also based on principles that apply to most data processing systems so it's just an easy way to integrate it and there's not so much conversion needed. But knowing all the benefits, it's now more the thing like how can we actually construct these files? And the benefit of it's widely used and it's just preferred format brings us nowadays to the point that we can just use our favorite data frame library called readpacke, writepacke and it just works, it's fast and normally you don't need to question it. Nowadays there is more than just one data frame library, but luckily everyone supports Parquet, so you just need to replace your PL with a PD and then it works in the other library. There's a bit of difference in writing with Polars and Pandas. That's also what you will learn from this talk. They have different default settings because they just come from different backgrounds. I think half of the default settings in Pandas may be influenced by the stuff I used in the past. the people that wrote the Polar's integration did some default settings differently because they probably had different data sets. So it all comes a bit down to which settings to use, how your data does look, but you also need to know what settings are actually influencing the file format. And before knowing which settings actually influence the file, it's good to know how actually the file is made up. The one thing that's always advertised is Parquet is a columnar format, but the first thing we're splitting up is actually in row groups. We're typically splitting a Parquet file in several millions of rows in a group so that we already have a bit of chunking in the file internally. And then because we're writing the file out of with all the data and only have certain information at the end of writing, in Parquet, the meta information like the schema and so on is stored in the footer at the end of the file, meaning that one thing should be aware, if you write a Parquet file, you only can read it in again once the writing has finished completely. It's not like if you see a CSV file, while one is writing and someone else can read it. Parquet have to wait until it's written. And then inside a row group, we come down to the actual columnar format. Every column of your table is then stored one after another. And inside this column, or column chunk, which is in here, it's actually split up into data pages. Data pages is typically somewhere between four kilobytes to a megabyte of compressed data. this is the one unit on which all the encoding algorithms and all the compression is working so important thing if you have a somehow compressed parquet file it's compressed on these small pages everything else is uncompressed because it's not so much data and compression would add a lot of overhead and little benefit to it so compressing a parquet file after it's written with like utility on top that actually makes it quite worse and not using all the features of parquet but in here if you only compress these bits you get nearly exactly the same compression performance with a much higher speed and all the package features in these pages these are the ones that typically or you have to read all the data that's in such a page to actually load the data but you don't need to read the whole file because we have this thing called predicate push down and that works because we have basic statistics like minimum maximum and maybe unit count and And if you really want to go fancy, then you need to really set this up so you can have Bloom filters in there. But actually not so many people are using that. And this gives you the ability to actually filter on different levels of the file so that you can save some time on shipping data over the network, save some time on decompressing the data. Normally the thing is, if you have a blibretic pushdown, everyone will filter out row groups. So if you know your query doesn't match this row group, you will not even put it over network or load it from disk. Most implementations can filter on a page level, but pages are too big in the size that it doesn't really matter or it's just really expensive to actually filter them out before reading over the network. So here's the thing. You transfer it over network. You see does the filter apply or not. Then you just skip over deserializing that page. Also one thing that makes this a bit more annoying is row groups is a line in a row. a data page may have different number of rows depending on the column you are, because columns have different data types and they compress differently. But overall, this is the basic structure of how ParquetFile is built in. And now I want to focus a bit on the talk, like how can you tune that? How can you make a bit more efficient use of the format in your specific use case? And for that, there are actually four knobs you can tune, or the first one is actually one, but you can depend a bit on the level of what you want to tune, that makes sometimes quite an enormous difference in how fast you can read a file. We use compression in there, so one thing you need to be aware of is how well do the different compression algorithms work in a Parquet file in the specific setting. The row group is making a difference depending on what your use case is. You maybe want to have a single one, or you maybe want to have like 10 or 50. and Parquet really shines through these encodings which make the data small but doesn't cost a lot of CPU so we want to tune on that. One thing that's missing which is normally in every tutorial working efficiently with data is people will tell you you should tune your data types. The smaller and the more efficient you store your data the better processing will go but in the case of Parquet it doesn't really save anything on disk. This is actually a really good message that we don't have to think about data types if you look at the example like my favorite trial data set is a new york taxi data set this is just one of the few really big open data sets you can use and for me it's tabular data that's quite what i use at work if you just use the one case where we have the biggest possible data types that come with it versus the smallest data types you can compress it down losslessly It makes a difference on 936 bytes on disk. So all your data type tuning, it's not worth it for the Parquet file, which is really awesome because you will do data type tuning for your data in RAM because in some cases you really need a precision of a float 64, and in some cases you can really say, okay, I can go down on maybe int 8. It's still the same data. It doesn't lose any information, and I can work with that, which means you only have to think about the data types in memory and the on-disk format, it takes care of the data, but it doesn't need to know the right size of your data type. With that, we can come to the first thing we can actually do. It's compression algorithm. And for the compression algorithm, there's different trade-offs. You have to look about the time it takes to write to disk, and it takes about the time, or the size of the data that comes out, and it takes about the time it takes to read. And there's several available. It depends a bit on how your data is structured. I'm using the New York taxi data set here as an example. It's quite average. It shows most of the cases. One thing that's missing, it doesn't have a prediction column, so I trained a semi-complicated light GBM model to actually have input data and a machine learning output. We will see later on why this is useful. Then I took two other big data sets I could find and also run some testing with that. One thing is UK house prices over the last 30 years and some COVID data from Google so that I can just verify if everything I'm telling you here applies to other data sets. I also used some private ones, had the same properties, so I'm feeling quite confident that things are quite general in the overall behavior. And because there's time measurements inside, I picked a median of five runs for every time measurement. for the compression algorithms themselves we have various different available but these have to be implemented in all parquet implementations so we cannot choose anyone we have the basic selection we don't compress at all there's like slightly different performance in how well the size is like Snappy is an outlier a bit more compared to all other compression algorithms that's bigger files But if you look at the time it takes to compress, you will see there are two different types of compression algorithms. There's Snappy and SetStandard, which are quite fast, and there's Brodley and G-SIP, which take a long time. Actually, Brodley and G-SIP also take a long time to decompress. But one thing we've seen here before, they're a bit better in compression. But we can select a bit here already. Do we want a fast algorithm, or do we want to have something small? And, therefore, we actually can look a bit more into the algorithms because some of them you can tune them. You can change, like, your cost function setting a different compression level before diving into what we want to say and maybe should tune that too. Snappy and none don't have a compression level you can tune. Actually Snappy annoyed me a bit because last week they released a compression level which broke a lot of things. But also you can now tune that too. But I haven't had the time to look into it yet. But one thing we want to see is if we tune something, we want to have like a thing where you can choose take long time and have it small or don't take so much time and make it bigger. One thing is in the PyRO implementation, the higher compression level, it's faster and smaller. So there's somewhere a bug hidden in here. If one fancies looking at it, maybe worthwhile to look. But in the end, with all the measurements, I can tell you G-SIP is never the best one, so as long as you don't have any compatibility issues, just don't use G-SIP. Broadly on the other hand, it has a nice curve where you have fast and big, or slow and small. Set standard gives you a similar curve. There's always in the bottom left, there's a thing where it changes fast, so we can zoom in a bit, because that's probably the space we want to look at, because the other ones is you spend a tiny bit more amount and it gets a lot smaller but in this space you have more like a selection and you can compare it with broadly because broadly in some cases you spend a bit more time or you spend the same amount of time and in some cases you get smaller data and set standard you have to think though broadly actually takes a lot more time to decompress so it also comes to the point how often do you read the data and looking at at some of the other charts beforehand you could say like yeah it probably takes a long time to decompress it doesn't improve so much you look at numbers down where you see like a two percent difference or less than two percent difference but actually in some cases you never read them again because i like storage is cheap parquet files get small so i had to some time ago the thing where i've just dumped a lot of debug data one point where we had an incident it was really good to read that but it just happens once in the year but it also overall cloud costs were like 50 euros per year so if you can save like half an hours and hours of work by having like this debug data just write them out always and this is the case where maybe broadly is much better because in some cases it just writes fast and produces smaller files whereas set standard is faster and reading but if you don't expect to read the file then just take the one which compresses better but overall i would say set standard is the thing to go for, because it gives you a good trade-off, and in some cases small network drives or things you will never read again, take broadly. The other thing is row group size, it does not actually depend so much on the kind of data you have, but actually what you want to do with the data. If you have a machine learning model, you just want to work on it and iterate and iterate on it. And the thing is, yeah, you just load the whole data set every time. Having one row group is just the best performing thing because then all the algorithms can work on the whole data set, can make all the compression and encoding most efficiently. But if you want to query stuff on it, you will always load the whole data over network. So maybe you should be thinking, should you make your row group smaller? Because then you can filter out more data over the network. but then compression and so don't work that well and you can see that a bit i've made it like for the demo file i've used the row group one row group and two row groups for a file and you can see like a two to five percent difference if you go from one grouped row group to two expect this difference you get a bit smaller to more row groups you're going but you always have to know there's like a trade-off more row groups make it less efficient but in in size but make it more efficient in reading. All comes down to trying it out on your use case or think beforehand do you query it a lot or not. The main feature I said beforehand is encodings. Encodings is something where you need a bit more knowledge because it's things that only happen so in the format. But the default in Parquet is to actually dictionary encode everything and then put something called run length encoding on top, so two encodings at once. That's the default in PyArrow and Produs to do that with every column. Works quite well. Some cases where your dictionary gets too big, it just falls back to plainly encoding stuff. There are delta encodings which store things based on, you take the first value and then you store only the delta to that. PyArrow can read them. I think you see Blasphemous Limitation, you can also write them. There's no interface yet on the Python side, but But it's a cool encoding, so I tested this out manually, we'll see later how it works. And recently there has been a byte-stream-split encoding, I first thought it was quite niche but it actually has one really specific use case where it's good to know it. Overall dictionary encoding, it works by just taking, looking at all the values, storing a dictionary and giving every value an index, where you first store the index at the dictionary itself and then you only store the indices. And if you have several times the same value, it will put everything in a dictionary, everything else will be smaller. There can still be repetitions, or most cases realistically there is a lot of repetition in the data. And for that, you can use the run length encoding. Run length encoding does several tricky things at the same time. First it looks at the data and sees, for example, for the first four values, there's no repetition in there. Like repetition means it's the same value one after another. So it encodes, this is literal data, or a literal run, and it just literally writes out the values with a literal flag set in before. But what it also does, it uses variable sized int encoding, that means the integers, you only save the bits that you really need to store, so it's independent if you have an int 64 or an int 32, it will just try to optimize the bit size. And if it then comes to a point where you see several times the same value, it will encode this is a run so it will set a bit for run and then write out a number of repetitions and then the actual value and my last four values are just one so I say it's a run of four of the value of one and this also brings my data quite small already for most cases so that later on the compression algorithm which is not data type specific has much less work to do and also already decent size compression on the other hand byte stream split encoding just shuffles my data around and it doesn't actually make the data small but it comes in the assumption that parts of your values are repeating and parts are not repeating so it just reorders them that you just take the triplets the blitz of every data and put them one after another so that the compression algorithm later on has much easier work and then can maybe make the data much smaller this only works like it works if your data has like if it floats and the first part of the float is always the same and the second part is maybe different your data once you quite quickly come ready to a point where there's a bit of repetition your data then dictionary encoding because it's much simpler for the compression algorithm afterwards is actually faster so the bytes in split encoding may be on itself faster than dictionary coding but the compression afterwards takes longer There's one thing, if you have a high entropy column, meaning there's lots of unique values in there, byte stream split encoding actually shines, and that's why I used a data set with a machine learning prediction in there. Machine learning predictions, if you have similar data, they will have similar predictions but not equally the same. So like three quarters of the prediction is the same for similar rows, and the remaining part is something blah blah blah, but this blah blah blah is different in every row, so it changes a bit. For data encoding, I just wrote something up where I took the data column and then sorted after it and computed a delta for these two columns in your taxi data set, and that actually helped me compress my file by 17.5%, which is a huge saving because it's on the whole file, not on the column, but it also means if you have data times or something that's increasing, data encoding is really worthwhile, and if anyone is looking for a bit of contribution If it's really only the C++ to Python interface missing, it actually could be a really worthwhile thing if you want to start contributing to open source and have a bit of Python and C++ knowledge because it's on my data and it's probably on a lot of other data that you will get a lot of benefit from that. And encodings are fast, so having a 17% saving is really good. One other thing I did, I ordered my data. This is useful in a lot of cases because if you order data, compression is faster, but in most cases your data is already naturally sorted and so I've checked most data sets and only the price paid data set really made a difference that I got a quarter smaller otherwise the natural sorting is already the best sorting so think about that beforehand also because sorting in a data pipeline is always quite expensive overall you don't need to adjust your data types just use what you want to use in memory byte stream spit encoding is a useful addition but it's probably only for your predictions. You should check sorting beforehand, but don't unnecessarily do it. Data encoding in Parquet, it's really going to help your data. You can do it manually, or you can maybe start and contribute it, because it will be useful. Otherwise, set standard on level three or four, and sometimes five is really good. And these are good things to start to tune, but what I would recommend is always run a cost function. Think about how often do you read, how often do you write your data, what's your speed? Like my speed I've had in the cloud setting was I got an average of 60 megabyte per second, which is good if you have concurrent writes. Maybe it's better nowadays. And then I used this cost function. I ran through my data and used it to hand graph the data, used it by just with bit encoding. And then wanted to see what does this make as a difference. And I've actually got good comparisons because I'm using the Pandas default. My cost is not really understandable, but you can use it to relate. Compared to the Pandas default settings, which are already quite good, tuning it down to my data, setting a different compression level, and you can see it depends a bit on read and write times, how often you read it. I could get, with the normal New York taxi data set, 20 to 25% saving. And also with the predictions where I couldn't do anything ever else, I also got a really good compression here. on all the columns where otherwise without using the device I got quite high compression ratios and overall it's also visible the one data set said 5 is the optimal compression level here it's 201 so it depends on your use case tune a bit but in this area Parquet is really worthwhile yeah that's it I'll be for questions now and hope I could help you a bit with tuning your Parquet files

Speaker 2 [25:15]

Thank you, Uwe. It was quite interesting. And yeah, we have plenty of questions. So, is here anything special I should consider when I have a large file, for example, 30 gigabytes or even more? Should I divide them by smaller files or any other advice?

Speaker 1 [25:40]

30 gigabytes sounds really large divided, but one thing to know about a package format is Whether you have several files or several row groups. It comes normally down to the same thing I would say normally try to have several files But don't overload your file system if it's just one file it will make it hard for some tools or sometimes It's even simpler to filter on file names But also the same time if you're in a cloud storage and other files if you have hundreds of files It will also slow down that system. So have a good balance of that but split it Thank you.

Speaker 2 [26:11]

Okay, good. Maybe you can give us any examples of next big features planned for the pack at standard.

Speaker 1 [26:23]

I think there are some more encodings coming, because you can still do a bit with the encoding. It's also, the other side, it's a standard, it's widely used, so it's a hassle to introduce new things. Because if you want to introduce, you need it in the Rust implementation, you need it in Python, you need it in C++, you need it in Java, you need it in Dr. B, and other readers also. But the byte stream split is quite new, and it's just not yet rolled out to all implementations. But that's quite the one new thing.

Speaker 2 [26:49]

Okay, sorry, could you notice Avro, because we have a question about that, Parket, and ORC, and Avro, which is good in which situation, and why?

Speaker 1 [27:03]

and why? So Avro is the one Gladiator and Twitter used before they invented Parquet. They had their log files in Avro because it's a good format to append things row by row. But it doesn't work that well. The compression and multi-row access is not that good. So they've calmed down to use Avro. ORC and Parquet are basically similar. I think one thing where Parquet can learn on are some features from ORC. But ORC, it's mostly influenced in the past which head of distribution you were using. That also determined whether you used Parquet or ORC. I think nowadays most of the things support Parquet and ORC is still there if you have like a Hortonworks distribution, but otherwise Parquet is the one to use.

Speaker 2 [27:47]

Okay, looks like we still have some adepts of Python default library. And we're asking, what are the advantages of Parquet compared with libraries like Pickle or DILL?

Speaker 1 [28:04]

So pickle and DIL just to store anything you have in Python and it's only Python specific It's really good because you can store anything and parquet only can sort columnar tabular data But on the other hand pickle and it's like it's Python specific It's probably only useful in your environment because if you update in library, you're kind of using anymore parquet files you can Put it anywhere else like this got me into parquet because I had stuff in pannas I wanted to use non-Python tools, so I've actually worked on writing stuff to Parquet and enabling Parquet to actually use some tools from the Java space. And nowadays the same thing is, if you have data you want to have somewhere else, you need to put it in a non-Python format.

Speaker 2 [28:43]

Yeah, so I would want to add what pickle is even not, might be not compatible even within the Python themselves. If you store something in a pickle and when you just move your files and it can lead to the situation you cannot read your data. And so the data formats like Parquet is going to solve such problem. okay we have a question for data exchange between systems we store a json string in the package metadata because that's way we can combine structured and unstructured data what do you think is it a good idea

Speaker 1 [29:31]

It depends on what kind of data you store. Like in the Parquet metadata, there are JSON strings. If you write with Pandas, because it stores the Pandas metadata and the Arrow metadata, because not everything can be encoded one-to-one in the Parquet schema. If you want to encode a schema in there and things, that's the way to go. If it is unstructured data, it's maybe better to actually use Parquet because Parquet has certain fields where you can already store a bit of this more complex structured data. on the other hand maybe it just makes sense to put it there as a string in the column but the metadata should be really really small because it's not compressed and it's assumed that you always load the whole footer or metadata into RAM so anything you put in there that's not metadata but actual data will make everything really slow

Speaker 2 [30:18]

Okay, good. Thank you, Uwe. It was extremely nice talk. Thank you.