Transcript#
This transcript was generated automatically and may contain errors.
So I'm excited to introduce our featured lab manager today, Nic Crane. Nic, would you like to say hello? Tell us a little bit about you and what you're gonna be talking about today?
Yeah, so hi, I'm Nic. I am an open source maintainer and an R consultant as well. I've spent a lot of my time working on Apache Arrow, which is how I'm kind of so deeply involved in Parquet stuff. And yeah, I'm excited to be here and talking about Parquet. So hopefully I'll be talking a bit about like Parquet in general, how it compares to things like CSVs, which people might be more familiar with, and just anything people want to know about really.
Wonderful. I am so excited to learn about Parquet. Isabella, I don't know about you, but I have literally never used a Parquet file. I'm seeing a shaking head. Maybe theoretically.
I feel like I, yeah, I feel like I know Parquet theory a little bit, but not practically. And in the past week, as we've been talking about it, everyone's been so excited and full of enthusiasm because they love Parquet or they're excited to learn about it. So I say we go ahead and get started. Nic, if you would like to share your screen, and everybody watching at home, please let us know if you cannot see Nic's text on their screen so that we can stop and make it bigger, because we want you to be able to see things. So let us know how things look here. Can you read that library call to arrow? Let us know in the chat. Text looks good to Logan.
Good, good. Renato says yes. If there is a problem that changes later, we hope that you will let us know in the chat. All right, Nic, take it away. Oh, you're muted. Take it away in just a second after you unmute. Thank you, Libby. That would have been awkward.
What is Parquet?
Yeah, so I guess rather than jumping straight into code, just like a really quick thing about like what is Parquet, because that's probably useful, right? Parquet is a really, really useful, right? So like it's a file format. And effectively, you know, you can use it to store data. And the point here is, it's smaller than CSV files, it's usually faster to work with CSV files. So I guess historically, Parquet was talked a lot about more in terms of like big data sets, but it's actually really, really useful, regardless of the data set sizes, because it's a bit more, I guess it's more to it than things like CSV. So it stores things like the type of the data with the data. So there's some really cool things, actually, I'm going to maybe I'm going to start with some of my tiny examples. And then we can go down like different paths. Because there's a few bits and pieces working with data where I for years always ended up writing extra code to go with my, you know, like to go with my analysis, I didn't need to write, okay.
So like, just to start off with a really, really, really simple example. And now I've got a question, actually, Libby, is it cheating at live coding, if I'm copying and pasting from something I made earlier? It is absolutely not cheating. I recommend it.
Preserving column types: a CSV vs Parquet example
Okay, okay. Amazing. So like, let's forget about all the sort of bigger data and it being faster and smaller for now. But just a really simple example. And this is how, like Parquet saves column types with the data. And that can be really, really useful. Okay. So what I've got here is I've made a data frame. And let's make this a little bit nicer on the screen. So like really, really, really simple data frame, I've got one column state, so like New York and New Jersey. And then another column zips like the zip code. Okay. And these are just like made up zip codes. I'm sure somebody in that state has a zip code, who knows. But like 11213 could be New York, and this one 07001 could be New Jersey. So like, pretty simple data.
Okay, so let's just, and this is where I find out I haven't got positron quite confirmed. Okay, it's working. Cool. We're good. Nice. Okay. Okay. I just I had, I reinstalled some stuff recently, and I had an old version. And but this, this is great. Okay, amazing. So really simple data set there. And I'm just like, one thing to notice here is this zip code column, it kind of looks like numbers, but technically, you know, I want this to be string data, right? Because I want this zero in front of that at the start. And actually, some of the time when I'm working with CSVs, this is where it can kind of lead me into a bit of trouble. So using some more code I prepared earlier.
So I'm going to start off just writing that to a CSV file. So write dot CSV, zip data to a file called zip dot CSV. Okay. And actually, if I just forget how I open this, actually, just to show the actual code itself, you know, I've, hmm. Ah, open as plain text file. Amazing. Okay, so like, if I look at the raw file, and how it's saved, I've got my two columns, I've got my two states, this is this has got quotes around it. Amazing. Okay, so let's now try reading that back in. So if I do read dot CSV, zip dot CSV. And like, what's happened here is that that first zero has been cut off. Because effectively, like in base R, when we read it in, it's gone, okay, I think this is a number, this is a number column. And it's a really tiny little example. But you can imagine if you've got a really big data set and lots of things going on, and you're, you know, you're sharing data with people and passing things back and forth, it can become a real pain. I mean, I'm sure like my biggest issue I've had around this is like having things between like Excel and saving it somewhere as a CSV and reading it in and just data getting corrupted. And like, this is one of the things that really excites me about Parquet actually.
I mean, I'm sure like my biggest issue I've had around this is like having things between like Excel and saving it somewhere as a CSV and reading it in and just data getting corrupted. And like, this is one of the things that really excites me about Parquet actually.
Because if I take that data and write it to a Parquet, and now I need to load a package to read Parquet. So I can use the arrow package actually to read Parquet files. Aha, this brings us to one of our questions, which we knew we would get, which was, well, what's arrow? What's the difference between arrow and Parquet?
Oh, I can totally come back to that in a minute. But I'll just do this super, super quickly. So here, it's just preserved the data in the actual format it needs to be because Parquet basically, it saves the data type with the data. And it's a tiny example. But like, once you get bigger problems, it can kind of becomes really, really important. And if you had millions of rows, imagine you wouldn't know where that was happening, because it would be hidden and sneaky.
Right. And then it's like, do I go back and edit like one row at a time? Or like, how do I get that back in there? And yeah, that's not fun.
Arrow vs Parquet vs Feather
But yeah, I mean, I've got loads of other code examples I can run through here. I can like, answer that question of like, what is the difference between sort of like arrow and Parquet? Where should we go? I would say, because you are an arrow maintainer, and you have lots of arrow knowledge, would you like to answer that sort of initial? What is the difference between arrow, Parquet and feather? Because that tends to be the next question. Yeah, so that's a really good question.
Actually, it's one of these things where there's bits of things written in different places, but it can be hard to find. So I'm going to start off with, let's start with Parquet, because that's sort of what we're here for. Yeah, everything else will minimize a little bit, but we can talk about differences. Yes. So Parquet, I guess the simplest way to explain Parquet, it's a file format for storing data on disk. And the key difference between like Parquet and say CSVs, is Parquet is a binary format. So it's designed for computers to be able to read it really easily and efficiently, rather than humans to be able to read it easily and efficiently. And it stores extra data, sort of metadata with the data, I guess, is the kind of simplest kind of way of explaining Parquet. And there's other bits we can get into about how all of the optimizations in it can make things fast, but let's leave that alone for the moment.
Okay. Now, Arrow is one of these ones where Arrow, effectively, Arrow itself is a specification for how tabular data should be laid out in memory. And then we get to this confusing thing where we've got Arrow, the specification, this sort of description, and then we've got libraries that implement Arrow. So basically, like the Arrow R package implements, well, okay, the Arrow C++ library implements this specification and the Arrow R package allows you to access this C++ library without having to deal with C++ because some of us don't know any C++ and don't have too much time for that stuff. But yeah, so Parquet versus Arrow. Parquet is for storing stuff on disk. Arrow is basically for working with tabular data in memory, effectively.
And then, just to make life a little bit more confusing for us all, there is a format called the Arrow IPC format. The vast majority of people never need to use this. It's to do with sending memory between different sorry, sending data between different processes. I personally haven't actually used it in any of my data science stuff. I've just worked on, you know, obviously, the Arrow package itself. And then Feather is like this older name for this Arrow IPC format that used to be more popular. And it was all about like making it easier to pass, say, have data that we can kind of share between, say, R and Python without having to convert things back and forth in different formats. But Feather was awesome at the time, but it's kind of been a bit superseded effectively by Parquet.
Okay, we have so many questions, but I want to hold them and we'll do a little question roundup in just a minute. Let's talk a little bit more about Parquet files in practice and what they look like. Yeah, that's a great question. So I'm just trying to think where to start because I've got a load of notes and things I could show you. So I guess what I'll do is I'll find a Parquet file that we can use and I will load that in. That's probably the easiest way to do it.
And we have amazing. We have some sort of key things that we can talk about why it's better. We've said that it's faster and we've said that it's typed, right? Like it's going to preserve your types better. It's smaller to store, which is part of why it's faster to read and write from. And also it is column-oriented and we will get more into that later. We've said the word columnar a couple of times. Like what does that mean? We will get into what column-oriented means and if you have good links to column-oriented meaning resources, feel free to share them in the chat. I also want to say I know that there are going to be questions about DuckDB. I think we're going to have a whole DuckDB episode because it's such a big topic. So if you have a DuckDB question, hold on to it. We will have a DuckDB episode, I promise.
Nice. So sorry, I forgot what the question was. It was columnar stuff, right? Yes. So I would say feel free to show anything you would like about what Parquet looks like in practice, maybe opening one up because I think that there was a question maybe somewhere about whether you need something special to open them up and actually Positron's data viewer will do that for us. Oh, that is an awesome place to start. So actually, I have, let me just find the code that I've got over here somewhere. I'm going to copy this a second.
Loading and previewing a Parquet file in Positron
Okay, so I've got some code and this is quite a nice example file to work with. Sorry, the URL is quite long, but basically this is a Parquet file that's basically saved with a load of data sets on the Arrow project that we have for running examples and things. To load a Parquet file, there's multiple libraries we can use. Arrow is like the sort of main, I guess the main canonical one, but you can also use nanoparquet, which let's totally come back to that because it's a really awesome package that's definitely worth knowing about. I'm using Arrow because I work on Arrow more, but there's different reasons why you might want to use each. You can also, again, like Libby said, work with it using DuckDB, but for now, Arrow is the focus. So I've loaded the Arrow package. I can read a Parquet file in by just using this function read Parquet. So just like if I was using, say, read CSV from the read R package or even just base R's read.csv, and I just, if I've got a file locally, I can just do read Parquet the path to the file.
And if I've run that now, I'll just clear my screen. We'll take a look at this taxi object. So yeah, the default, it reads in, oh, sorry, this has got tons of columns, which is why it's showing up like that on my screen. But the default thing is it reads in as a data frame, or actually, let me just load the tbl library, because then it'll load it as a tbl, which is kind of a bit nicer. Now, let's have a look. Do I have to do that again, or just load it as a tbl? Yeah, here we go. Okay, so it's loaded it as a tbl now. So it feels a lot like if you're just loading a CSV file. So yeah, this is just a tbl, and now you can work with it exactly as you'd work with any other kind of like data frame or tbl.
Now, the nice thing as well in Positron is that if I have so many files in this directory, but if I click on this Parquet file, what it'll do is it'll give us the same preview again, like if we had a CSV file. So like not just saying this, because you've all invited me to come here, but genuinely, like I think Positron is like my favorite tool to preview Parquet files in just because it's there, and it's easy, and it works for me. And because yeah, that is one of the slight kind of different things that you know, if you've got a CSV, you could open that in a text editor, like notepad or something like that. And you can see inside your file, but you can't do that with Parquet because of these binary files, you've got to use something like this, if you do want to get that kind of preview there. So yeah, I think that's exactly the same as we'd see with a CSV, right? Like if I click on? Yeah, yeah. Cool.
Um, so yeah, I guess one of the things I've mentioned is about them, like being columnar, and then being really fast. So I'm just wondering, actually, if it's worth sort of, yeah, I'll show off a sort of a little bit of a speed difference thing, and maybe then go into like, how it's worked, and why it's been that been faster, that could be maybe useful. That would be amazing. I know that sometimes speed is hard to see when you're not comparing to extremely enormous things. Are you going to be able to compare it even with like stuff that we're
working on in a CSV file that we can just open up here? Yeah, so what I've done is, I have let me just tell you, I'm going to clear all this stuff off the screen, so we can come back to if we want to make a bit clearer. And I've got these, let's have a look. I've got a few different Parquet files set around here. So let's have a think. I think this one, taxi underscore big, oh, taxi dot, sorry, I'm just getting what I've got here. So I'm using a function from the FS package called file size, so it'll just literally get us the size of that file. Okay, so this file that I've got here is 135 megabyte Parquet file, so that's going to be big enough. So I kind of mentioned that Parquet files are smaller than CSVs, so what we could actually do is try reading it as a Parquet and writing it as CSV, and we'll see how much bigger it is. So let's do, I'm going to call it taxi big equals read Parquet taxi dot Parquet.
Okay, so that'll take a moment to read in. Cool. So let's just see how many rows there are. Okay, so that's like 7,667,000 rows, so decent sized data set. Yeah. So if I just do read R and then do write underscore CSV, I think when I did this last time, it took like 30 seconds, so I'm definitely gonna have to find things to say when we're doing this, but I'm gonna give it a new file name so it's not just named after anything I've already got in my directions. I'm gonna put my big CSV of taxis because I wanted to give it a really long name. In fact, okay, let's just do it with read R for now. Okay, so we'll give it a moment. Yeah, it did take 27 seconds last time, and being on a Zoom call can kind of slow things down because it, but anyway. Zoom slows everything down, yes. I know, I've had this before, I had this perfect demo, and then I get on Zoom and I'm like, no, but what I'm gonna do is I'm gonna prepare, and we're gonna use the FS file size function once again, and then because I gave a really long name, it means I can take longer to type it, which is amazing. Okay, yeah, so that's 924 megabytes, so way bigger as a CSV, and for small things like this, okay, whatever, it's nothing crazy. This is reasonable, but just think if you've got tons and tons of data at a certain point, this is gonna get unmanageable for some things. Okay, but I guess the thing that could be worth doing actually is seeing how long it takes to read it in.
So my favorite library for demonstrating this actually, just because it is just really simple functions, like actually, Libby, have you worked with the TikTok package before? I have used the TikTok package just a little bit. It predates the app TikTok. Yeah. I used this in undergrad, I think, because we had some assignments where we needed to time things. Oh, that's really cool, nice, like I never, because I'd never heard of it until I started doing kind of, you know, kind of making like arrow workshops and things, and I think it was Danielle Navarro that taught me about it, but I like that you just have a function called tick, then you do the thing you want to time, and then this function called tock, and then it finishes timing it. So if I run that, and we'll see how long it takes to read that in, and it should be faster this time than it was to write it. And to reiterate, we are reading the CSV that we made out of our Parquet file. Okay, thank you, yeah. Right? Okay, making sure I'm right. You know, we are, we are, we are, you absolutely, yeah, yeah, yeah. So that took us 6.43 seconds, okay. And now I'm going to read this Parquet file again. So this is the Parquet file that we used to make the CSV. So it's exactly, exactly the same data. If I run this here, and it took like 0.593 seconds. So like, just way faster. Yeah.
And hopefully, so I've got another fast thing. Now, sometimes when I do fast things, they don't end up being fast. Let's see what happens. Another cool thing we can do as well, is we can just read in like, say if we just needed one column from that data set. So let's do read in that date. And let's now do call select to say if we only care about the, and sorry, I've not even introduced this data set. I'm doing a terrible job of explaining things today. So this data set is about taxi rides in New York. And it's got all sorts of things like the time, the date time that the person was picked up and dropped off, how many people were in the taxi, how long the trip was, the longitude, latitude, and all that kind of stuff. But like, let's just pretend we only want to work with say, like the date time pick up column. So it was no point just under 0.6 seconds to do the whole data set. Okay, and then it's, yeah, so it's like way faster, just to read in one column, which you can't do with a lot of things.
Column-oriented storage explained
Okay, so to give a bit of context about why that's faster, I feel like using a diagram is always good for this kind of thing. So let me just find one in this big, messy Quarto document I made earlier. I am up for a diagram. And I hope that that answers some of the questions. Some of the questions that were coming in, I knew we'd get there, which were like, how much faster is it? Okay, here's this. But there are some questions that I'm not sure if we can answer and might require people to go deep dive on their own. Like Zach had asked, this is more of a CS question, but how are Parquet files so much smaller?
Part of that, I think is Arrow. Yeah, there's like, it's a weird mix of things, right? So should we do smaller or faster? We can do, let's, yeah, okay, so smaller, basically. I don't have a good diagram for this one. But because it's like a binary file format, it's designed to be read by computers rather than humans, it doesn't need to have as much stuff in it as CSV. So there's encoding and compression. So not to get too much into the nitty gritty of these. But there's one example that I really like. Say if you've got a dataset, right? And you've got, let's say you've got like, person name as a column in your dataset. And say that Libby shows up like 10 times in a row. So in a CSV file, you'd have like the name Libby 10 times. But what we can actually do with kind of Parquet files is we'd have a little thing that said Libby comma 10. And then that stored that value 10 times. So like, that's just like one of the kind of optimizations. So like, similar things to that is basically how it kind of ends up being smaller. And there's all sorts of because it's again, because it's a binary file, there's all of these kind of compression algorithms that to be honest, I don't know inside out. But they use clever tricks like that to make things smaller effectively.
Yes. It's kind of if you think about the way that GitHub does things where it's like, hey, I'm not going to store everything, I'm only going to store what's different. That's like a way that you can compress things, right? Where you don't have to have a full copy of something. You can just say, I'm just going to store the difference between A and B. And that can effectively let me store two different copies of the same thing, copy A, copy B. It's a little bit like that. And I hope that that answers your question, Rebecca, on how much smaller Parquet files are. Rebecca also asked, does it scale linearly or something else? And I think we answered that right now. It's not exactly linear, because depending on how many, let's say repetitions you have in your data, that type of compression of we have 10 of Libby, and let's just mark that we have Libby comma 10, you could have completely unique data for 17 million rows, and it's going to be bigger than something that potentially can be compressed further. So I don't think it's linear, but it does make such a big difference. Thank you, Christy, as well. Christy Wade for answering that in the chat. Christy said almost exactly what Nick was saying at the same time.
Okay, so I think that we've answered a lot of questions that have come in, because I knew that we would get to them. And No Tobacco asked when I tagged John Harmon in, and he answered it. Thank you so much, John. Nick, Lauren had asked for later, can we convince Nick to share their Q&A, the one with the AWS URL for the tax data set? Okay, great. Lauren, we will get that shared later. Thank you so much for asking. I mean, do you want me to just share the URL now? Yeah, on the discord, but I'm happy to share it again. Oh, yeah, sorry. I just I just shoved it in the discord somewhere. Oh, thank you, Isabella. You're the best.
Fantastic. Okay, let's keep going. Because I would like to do you do you want to talk about a little bit more about the columnar column? Yeah, yeah, that sounds good. So um, okay, so like, I've got the image on my screen at the moment. I am so example data set, right? We've got some data that's got like year, location and age for a load of people. So yeah, locations like a state in the US and age is a number. So and let me remember the kind of because there's different ways of explaining this. The idea is, this is all to do with are we storing rows in data? I'm sorry, are we storing data in rows or in columns? So when people talk about like row oriented formats, they mean things like CSVs. So the idea with a row oriented format is and actually, right, I didn't do computer science. And one of the really, really helpful things for me to like learn when I was putting all these things together, is if I'm trying to like have a concept of how data is stored in memory, I think of it as like a 1d thing, like you've just got this big long line. And it's not quite like that. But it was a really important concept. So on this image here, if we're storing things in rows, what that means is this first road row of data. So the year 2005, New York and age 70, we store that row, then we store the next row, then we store the next row, and so on, and so on, and so on. And when people talk about a column oriented layout, what that means is we store all the values for year, then all the values for location, and then all the values for age. So different ones have different advantages and disadvantages. But a lot of the time when we're doing analytical stuff, that's like asking questions about the data. So tell me the average age in the data set, or filter by rows for which year is 2005. We're doing this really kind of column oriented kind of way of doing things, right? Like it's focused on do things with a column. So if we are trying to get like, I don't know, like, the average age, for example, what happens here is in the row on if something's saved in rows, I'm going to pull out from this position, then okay, where's the next one, pull out from that, right, where's the next one, pull out from there, and so on, and so on, and so on. But when stuff's stood in columns, I can just pull a big chunk of things out really efficiently, really quick, and work on that in one go. And it's an oversimplified explanation, but that's roughly how it works. And it just makes it a lot faster to pull kind of data out to work with effectively. That makes so much sense.
But when stuff's stood in columns, I can just pull a big chunk of things out really efficiently, really quick, and work on that in one go. And it's an oversimplified explanation, but that's roughly how it works. And it just makes it a lot faster to pull kind of data out to work with effectively.
nanoparquet vs Arrow
Yeah, so that's the column oriented thing. I've got loads of other things I can talk about, but I'm not sure where to go next, actually. What do you reckon? Let's let the community guide us. Because you had mentioned nanoparquet, and you were like, we'll come back to it. And Becca had asked Arrow versus nanoparquet. Maybe you want to talk a little bit about nanoparquet now? Yeah, so actually, like, I was really excited to get invited to do this, because to be honest, I'd seen nanoparquet, and I hadn't worked with it much. And in sort of preparing for this, I actually, I don't want to be too, here's what I made earlier. But let me... No, please, show us all the stuff you made earlier. This is why we're here. This is exciting. And as a time check, we're about halfway through. It's 32 minutes after the hour. And while you are doing that, I will say Zach asked, is there an application that opens .parquet files, like how Microsoft Excel opens .xlsx files? There are parquet file viewers that you can download standalone. I can't vouch for any of them. I think that the file viewer inside of Positron is the best way that I know to open a parquet file easily and look at it. But I don't think there's a dedicated platform or application the way that you would have for Excel files or Word files. Yeah, I think, yeah, exactly. That's right. Because I think, I don't know, parquet, because it's used in so many, in different things. So I think like Apache Spark uses parquet. And it almost ends up being a separate, a conceptually separate, like it's a standalone thing, I guess, more than with an Excel file is the way of doing it. But yeah, I use the viewer and then mostly kind of R code.
But yeah, with nanoparquet. Okay, so nanoparquet, fantastic library. I think this table's right. But the idea is that, like, before nanoparquet came around, the arrow package was really, and kind of WDB in some, to a certain extent, were the only ways to work with parquet files in R. And that's great. And that's, you know, that's fantastic and everything. But I guess if you want just like a really, really tiny package with like no external dependencies, then nanoparquet is fantastic for this. So the sort of different advantages and disadvantages to each, like arrow is like the fully featured all the things. And I think nanoparquet is like, oh, I've got like a small file in parquet. And I don't need to do tons of things with it. So like, this is just really efficient. So like, both of them can read and write single files. And the nice thing as well is like the function names for just read parquet, write parquet are the same with both packages. But then some of the like more, I guess, advanced functionality. So one of the things in arrow is you can have these data sets that are made of lots of different files that can just be easier to handle than having like one huge file. So arrow works with that nanoparquet doesn't. And there's other bits and pieces, like if you've got stuff stored in an S3 bucket, or if you want to do some of that, you know, like I said about that filtering stuff before reading it in, then you need Arrow for that. But actually, there is one really nice feature of nanoparquet that Arrow doesn't have, which is that you can append data to existing files, rather than having to rewrite them. So that's, that's really cool. But yeah, I was like, if someone like if you've got a, if you've got a really, if you've got, I think for me, if I've got really big Parquet files, and just need like the more, some more of the advanced functionality, Arrow is the way to go. But actually, if you've got small things, like nanoparquet is fantastic.
And it was written by Gabor Xardy, who works at Posit. And yeah, it's interesting to see what happens to that package. Because yeah, there's loads of cool things.
CSVs vs Parquet for small files
Well, that was a fantastic question. Thank you for asking that, Becca. There were some that were much earlier on, and I wanted to touch on one, we talked about it a little bit. But it shows a good benefit of a CSV. Nathan had asked, do the benefits of Parquet extend to small files? I routinely use CSVs with only 10 to a couple hundred rows, and the human readability is nice. I agree, human readability is really great.
Yeah, like 100%. Like if you've got, if it's all about what your priorities are, right? Like if you've got something that you really need human readability for, in whatever the workflow is, or how you're sharing things, then yeah, actually CSV, I mean, CSVs are fine. They've been around like for ages. Like I still use CSVs for a lot of tasks. Like if I don't have to care about the data types being read in and out, kind of, you know, in a reasonable thing, and I want something I can play around with, yeah, CSVs are great for that. It's more, as soon as we get these little annoying edge bits, then Parquet sort of ends up being better. And like, when you're doing like speed comparisons, yeah, to be honest, well, one, if you've got 10 rows, I don't think anything is going to be slow. But at that point, I'd expect them to be the same. I mean, Parquet could even be, it could even be slower, but it's such a tiny thing, it wouldn't make a difference. But yeah, yeah.
Audience questions
Okay, great. Amelia, welcome back. Amelia learned a frustrating lesson about the Zoom links. They are unique to you. If you give them to somebody else, they will just join as you and kick you off. So if you have signed up for the Data Science Hangout or Data Science Lab, that link is yours alone. Other people need to sign up for themselves. Amelia, I'm so sorry that that happened today. But Amelia did have a question. She asked, does Parquet have any built-in support or functionality for database-style dataset linking? And I'm not sure if I know exactly what Amelia means, but you might know better than I do, Nick.
I'm afraid I have no idea what that is. I'm on the weird corner of data engineering, where it's mainly arrow-focused. So I don't know if there's other terminology that I might know about that just is called something different. Maybe metadata-style things might be part of the question. Amelia, please feel free to add context for us. I see you typing. So we will come back. But we also have a question from Becca asking if you can write spatial files to Parquet. I do not see why not. But there are definitely spatial data file types which might not work with Parquet. Do you have any experience with that, Nick?
So the way things work in Parquet is so like R has its own data types, right? Like we've got integers and logicals and all the rest of it. And Parquet has its own sort of set of data types. Because the idea is that like Parquet works whether you're in R or Python or Rust or Java or whatever. So if something can map to like a Parquet data type, and it does cover most things, then you can write it to Parquet. There are some specific like special cases. Like there's GeoParquet, which is kind of cool as well.
Just put a link to that in the chat.
So I think like some spatial stuff you can do. I don't know if there's some spatial stuff you can't do. But basically the sort of short answer is like if it maps to Parquet data types, then you can. Yeah. And if it ends up being a bit more complicated, then maybe not. But yeah, there'll be things you can do to go around that.
Fantastic. And we do have a little bit more information from Amelia. She said like in Microsoft Access, there's linking between datasets via specified primary keys or foreign keys. So that's more like I want to join these two. I want to have each one to have a key in it as a column. I think that then, yes, like you could have a primary key in a Parquet file just like you could in a CSV or any other type of.
Yeah. Like I guess with that stuff with Access, it's almost like that extra functionality is almost it feels like it's tied into that data storage. But I mean, you can store extra metadata about your data in your Parquet file, I guess. I guess for me that maybe would the cause of it. Yeah. You can store that in your data. I guess for me that I probably I feel like it live in my code. But again, it depends on exactly what you're doing. But yeah.
Partitioned datasets
Awesome. Thank you so much. OK. I wanted to pause on questions for just a second and dive into something that you slightly mentioned a little bit in the third line of this table of this Arrow versus nanoparquet, which is partitioned datasets, which is partitioning as a function in Parquet files is pretty amazing. So let's talk about it. OK. Yeah, absolutely.
So what is partitioning? Why is it possible?
Is partitioning. So I've not explained this for ages. Let's let's give it a go. OK. Like imagine I mentioned before, actually, a little bit about how the fact that when we read in this, we want to just this one column of this file. Right. And when we read that and it was faster because effectively we were able to just pull that, you know, pull that one column out of our file. And I guess partitioning, it feels like it takes that concept further potentially. So I'm going to live code this without looking at a note. So my candle is lit. Amazing. OK, let me just check. I have my big, like huge data set here. Oh, OK. This is the 7000 row ones. Let's see. Taxi big. OK, OK. This is the yeah, this is the 7 million row one. So let's say that I have this data set. Sure, I can save it to a Parquet file all in one Parquet file. Fine. It's 142 megabytes. But actually, maybe I don't want a 142 megabyte file. Maybe I want a smaller file because it's easy to share with the people. But also maybe I want to split it up because when I'm analyzing this data, I don't need all of that data anyway. So let's imagine a situation in which like the most typical data analysis I do is actually I'm going to like I'm going to do some all of my analyses are comparing these different vendors. OK, so in this data set, we've got these three letter acronym. In fact, let's have a look in the data. So if I load dplyr. Now I can do dplyr stuff. What was the file called? What was the data set called? Taxi big. It must have been taxi big. So if I see how many distinct values there are in a vendor name. OK, OK. Oh, there's only two, but whatever. That's fine.
Because Parquet files are kind of split up in smaller chunks and we can do things in parallel. It's kind of nice to sometimes have things split out. So let me.
Maybe the easiest thing to do is just show you an example of partitioning that we can talk through.
So I'm going to save this right data set. So this is a function from the arrow package. I'm going to save it to a. I'm going to call the folder by vendor and I'm going to do partitioning equals. I haven't run this function for ages, so I think that's how we do it. And this is going to take a moment because it's right in this big 7 million thing to disk. But if I look in this file, it's done it now. It's by vendor folder. What it's done is it split it into vendor name equals CMT. That was one of the values and vendor name equals VTS. So what I can do now is I want to do an analysis. Let's do open data set.
By vendor, do I put that in quote marks? This has been a while. And then I just want to say, like, I don't know, filter. Vendor equals it was it CMT. And then we'll do summarize and we'll just do like time equals me. What's the distance then? I mean, was there a trip distance column? I can't remember. Yeah, underscore distance.
Oh, and then, of course, I've done something wrong here. It's vendor name, isn't it? And then sorry, I need to do something else a second. So there's a little bit more complexity. I need to do collect just to say, get this analysis ready and then pull it into R. Okay, so what has happened here is, because this is in a data set, and it's partitioned by this vendor name column, in other words, we've got three different directories. This is just for NA files. This one has a parquet file for just the data for CMT. And this one just has the data for VTS. So now what that means is it didn't even bother reading in these other partitions when it was doing that analysis. Because we've got it in this kind of partitioning, it knows it just needs to read in this one. So it makes it faster because you're not reading in all of the data to then filter it by CMT. It just filters like that.
So it makes it faster because you're not reading in all of the data to then filter it by CMT. It just filters like that.
And it creates those folders for you automatically when you partition it, right? Exactly. Like that line that Nick ran on 35 and 36, where they have write data set by vendor, partitioning equals vendor name, that created that by vendor folder, which created subfolders for the vendor name, for the CMT data set, as well as the VTS data set. So imagine if you are partitioning by something like year, and you have 25 years of data, then you have 25 different partitioned data sets. And if you only want to look at 2009, it's only going to pull 2009. Even if you have 2 million rows in that one versus something else, that can speed you up a ton if you blow this up to a problem at scale. Exactly.
Yeah. And it's all about taking that filter and just pushing it down onto disk rather than doing it in memory.
Yeah. Fantastic. Okay. Does anybody have any questions about partitioning? I know that Renato had asked one, as you were going through and you were running distinct, he said, wait, did that just go through the entire 7 million rows to run that distinct call? Because we were talking about in the chat, when Parquet is loaded into R, does it become data just like everything else? And the answer is yes, it's just like a big blob of data, just like anything else. It is now stored in memory.
But the way that we partitioned can help fix some of that, I think.
So yes, it did go through an entire 7 million rows to run that distinct. And then we had another question come through. Oh, I'm going to go back because it was very, very early that Rachel and Noor had some questions. So Rachel had asked, is it possible to use Parquet for nested data structures? And if so, are there arrow tools for working with them in R?
Nested data structures in Parquet
Oh, I love this question because I literally, the notes that I had there. I've been holding it, holding it in my brain. I'm like, don't forget, Rachel asked a great nested data question. Yeah, so I'm actually, so actually, so like last year, I ended up publishing a book about arrow. And there's way more examples of what you can do and can't do in there. And we made it like, I published this with John Keane and Neil Richardson, and we published it online for free. So it's like arrowrbook.com.
But this is one of the examples from the book. I've got this table and I've got the first column, species. So it's like cat, dog, mouse, and mouse. First column, species. So it's like cat, dog, bird. And then I've got this list column here, which has got like meow per hiss for cat, woof bark, growl for dog, and tweet and squawk for bird. Because it's less, this particular bird is less versatile than it sounds. I don't know. I just gave up the example, right?
So if I just look at that kind of table, nested data. Okay, so we can see those are kind of like list columns there. And one of the nice things about Parquet is I can, I think you, if you wanted to do this with a CSV, you'd have to just do a little bit more work. So you might want to save them in like one string separated by semicolons or something else. Whereas with Parquet, I can just write that to Parquet. And then if I read that back in, we've still got it as list columns effectively. And I can, yeah, yeah, yeah, yeah.
I think we can do nested, I think we can do further nesting. It gets a little bit weird, the more nesting you get. But it's, yeah, a lot of this is possible. Like, if there's a different example that you want to know if that works or not, let me know. But I think the general idea is you can store nested things. You can't store like arbitrary objects, so say like ggplot objects and things like that. But if we've got kind of like list columns and kind of things like that, that can map to Parquet kind of type system, yeah, we can do that. Yes, I think that's a great designation. So the list of types, which would be readable by all kinds of programs. Yes, things that are super hyper specific to R, like a ggplot object, less so.
Partitioning and database indexing
Okay, we had a question from Dan. Daniel asked, is that by and partitioning similar to how relational databases index their data? And I don't know the answer to this because I am not a relational database expert. I'm afraid I'm not either. And that's okay. What was the by? Oh, maybe it's because the partitioning there. I guess the partition, right, okay. I have no idea, but I can give you a little bit of a brief thing about partitioning. It's like, say if you, it might be, I don't know if it's simpler or not. Because again, I don't know how relational databases do it. But effectively, all I want to do here is passing columns that I want to partition by. So like, it gets a bit more complicated. Say if I want to partition by multiple columns, but I need to have categorical things. Do I have them? I don't need to have categorical things. It just gets weird if not. Basically, I might end up having like nested kind of folders within folders. But I guess if we're saying, I mean, indexing makes things faster. Because we're not looking at every single thing.
So it could be similar in that way. Yeah, I probably don't know more to answer that without ramping for a long time.
We also had, Marcos had said, hey, I think we need a data lab on polars and lazy frames. I am so excited that you think that because we have Cameron Riddell joining us, who is a maintainer for narwhals. And he's going to be sharing a lot more information about narwhals, why it exists, which means we're going to be talking about data frames in Python. So get ready for that. I think it's going to be a fun time. And Cameron is fantastic.
I wanted to go back up to Noor's question. Noor, where is it? It says, is the community moving towards polars or pandas still relevant?
And I would say, yes, we are talking about R here. But how does Parquet function in Python is a good question. Nick, do you have any insights on the Python side?
Yeah, that's a great question. So kind of at the very, very start, I kind of mentioned this idea, like arrows of specification and then different kind of implementations of it. And, you know, the arrow R package basically lets you access the C++ one without having to kind of do things in C++.
And in Python, there's pyarrow. And it's a very similar concept. So it's also based on exactly the same C++ library. But this has got kind of like Python that's talking to it. So we might not have the exact same open data set functions. And because like the R stuff is tailored towards, okay, what's going to make sense to people working in R. But everything that's happening behind the scenes, kind of, you know, in terms of working with that C++ library is doing the same stuff behind the scenes.
Yeah. So like in Python, you've got pyarrow. In like Java, I think it's just arrow Java. But yeah, like all different language versions of it. And yeah, and there's even things, yeah. And even like kind of pandas can use pyarrow as well. So like so much of this stuff is interconnected.
Parquet metadata and schema
It is, yes. And we had a question from Nathan that was, how does Parquet manage metadata? So for context, Nathan works with Likert scales a lot where numeric values and text labels are really important to go together. And anybody who works in SPSS knows that they have labeled data. Or SAS. Does Parquet support data labels in any way where it's not stored as a column itself, but is more stored as like associated metadata?
Yes, it does. And annoyingly, this is one of the ones I didn't come up with an example ahead of time for. But I can definitely find you a link. Because we did have one in the book, actually. Because I'm trying to think, one of the ones that I kind of think there was something about Haven style data. I'm literally clicking through some stuff really fast to find it. And in fact, okay. I'm not going to be able to kind of read this and remember what we wrote. But I'll chuck something in. I'll pop something in Discord that will be useful for that.
But effectively, yeah, we can work with quite a lot of metadata in Parquet. And we can store that with the data. I guess the only thing that can get a little bit like, if stuff's saved in columns, you know, Parquet data types, you can transfer that between, you know, whether you're going to Python to R to whatever, it's fine. But if we've got something that's quite R-specific metadata, then that's only going to work reading those Parquet files in and out of R. So that's just something to be aware of with those, I guess.
And then I found the NOR question I was looking for, which was, can you store data schema in a Parquet? I guess it depends what you mean. Because people use the word schema like in a few different ways. So in like Parquet world, schema is like the mapping between the column names and the column types. And yeah, you absolutely, you have to effectively.
Actually, I found a really cool function in nanoparquet. Again, I didn't know this before, that you can actually use to read the schema of the data set. So it's just nanoparquet readParquet schema. Oh. And now this is more information than you'll probably need most of the time working Parquet with Parquet. But you get loads of stuff here now. So you've got like the column name, the R type, and I think that's the Parquet type, and all this other sort of extra metadata. So I don't know if that's what the question was asking, but yeah. Yes, NOR says, that is what I meant. Fantastic. We have answered.
Oh gosh, I hope and I think we've answered every single question. If we have missed a question, please, please jump in and let me know because we have six minutes left. There was a question from Renato that was about the advantages of Parquet over CSV, DuckDB. But I think that we hit that because we talked a little bit about, you know, if you work with smaller data files and you need them to be small enough to check in to GitHub and all that stuff, then sometimes CSV is still just the best file type for your working style or your instance. And I think that's totally fine.
There have been so many amazing resources in the chat that I always hit this character limit with YouTube where I'm trying to put in every single resource and every single link and every single timestamp, and it'll tell me, you've put too much in the description, Libby, and I have to cut it down. I have a feeling we're going to hit that today. The only thing I wanted to ask is, if you wanted to go over the diagram of what the inside of a Parquet file looks like, because I know that there is one from the Arrow R book, and I was looking at it and I was like, I wonder if this would be useful in the last five minutes. That's the one. Thank you. I would like, no, I was literally thinking, oh, I like that diagram, but I've not had time to talk about it.
Anatomy of a Parquet file
So like, this information is all in the Parquet docs, but I just wanted to kind of take it and make it a bit simpler because there's so much stuff in there. Like, one of the easiest ways, I think, to think about like why Parquet files can be faster than CSVs is because they're basically broken down into smaller subcomponents. So this is effectively part of the anatomy of a Parquet file. So like this data set is slightly like a different data set. So this data set has got columns, SPO.RT. This is actually from a census data set, but probably not worth going into now. But imagine this is all part of one big data set, right?
And within the file, firstly, it's broken up into these things called row groups. Then it's broken up further into these things called column chunks. And then they're broken into smaller things called pages. And it doesn't really matter what each of those are, but the key thing to know is like, it's just smaller and smaller and smaller components. And the great thing about this is because the way Arrow is set up to read Parquet files, it can do things in parallel. So now instead of having to kind of go through all of this data in one big chunk, it can go through these individually.
Now, Parquet files also store metadata about the row groups and the column chunks and the pages. So like if I'm trying to do some clever filter, like show me only values which aren't NA values, because I've got the metadata that says, okay, like this row group has like no NA values or something. It can just not even bother reading that when it's analyzing it. So it's all these little weird things about how it's subdivided that helps it kind of like be faster in that way. Yeah.
it's just smaller and smaller and smaller components. And the great thing about this is because the way Arrow is set up to read Parquet files, it can do things in parallel.
Audience questions
Perfect. Okay, that is what I wanted to know. And it looks like Isabella has flagged a question that we might've missed. Hooray. Can we answer it in three minutes? It says, I have some data saved as a Parquet file. I'd like to load it with Arrow and then use pivot wider or pivot longer on it from dplyr. How would you do that? I resorted to DuckDB and then to Arrow, which seemed kind of clunky.
Nope, that's a great way of doing it. So basically all of these dplyr functions that I was working with before kind of in Arrow, we've had to map them to the sort of underlying Arrow kind of code basically. And there aren't any mappings for pivot wider and pivot longer. So yeah, that approach is great. There's a function called toDuckDB, which basically lets DuckDB do that hard work and pass it back to Arrow. But because DuckDB understands Arrow internals, it means that we don't have to copy things backwards and forth and be slow. It just does it. So if the dataset is not something you just want to read in as like a table, then that is a fantastic way of solving that problem.
Great. Well, then answer question, asker. Your answer is you're doing it the right way. This reminds me of an answer that Joe Chang gave once in a talk when he was at the Hangout. He was saying like, a lot of times people ask me what the right way of doing something is because they think that the only right way is some developer produced way. And he's like, if you did it and it works, congratulations, you did it the right way.
If your code runs. Okay, we have one more. Can we get it in one minute? I don't know. It's from Kale and it says, I used Arrow for the first time about a month ago and I was confused when partitioning led to those variables being dropped from the files themselves, visible when saving as a CSV database, but then realized the Arrow picks it up again when reading the partitions back in. Is that variable stored inside parquet files at all? If an individual partition file is shared, this one might be a little more complex and needs to be answered in the chat, but we can tag Nick. Nick, do you have a? Yeah, pop it in the chat and I will definitely take a look at it. But yeah, definitely. It's a bit too many pieces there for one minute, but definitely happy to answer that.
That's okay. Everybody say thank you to Nick so much for explaining parquet. Are you going to be using parquet more now if you have bigger files? I hope so. I hope that this has been a friendly introduction. So many clapping emojis in Zoom. My screen is just covered. Thank you so much, Nick. I hope you had a good time with us at the lab. Yeah, thanks for inviting me. This has been really fun.