File Formats Any Data Scientist Cares About

Hello everyone. Welcome to our session. My name is Piotr Wendy Ker. Together with my colleague Sean Sheren, we will talk today about file formats for data scientists. So we will start from describing the columnar data structure and how that may look like in the future version of Wolfram language. Then I will describe the Apache Arrow library which will serve as one of the backends for that columnar data structure. And finally, in the rest of this talk, I will present three formats, CSV, parquet and RO columnar format and compare the performance of Wolfram language import export with the corresponding Python libraries. So let's start from the columnar data structure in the bracket. I have here. This is work in progress. It will not be available in soon to be released version 14.0. It is currently scheduled for the next release. But. This is work in progress, so we may delayed it for some reason. Hopefully not. All right, so what the columnar data structure is, other names for that structure is data frame, table or structured data. So from this illustration it's right, so you might be familiar basically with the spreadsheet spreadsheet table. So here we have that each column, it typically represents the variable. And in each row we have a data point. There could be cells with the missing data. What's important that each column is usually of the same type. There is a homogeneous cell type in a column. It could be a number, it could be a string, date or category, etc. However, overall the table is usually heterogeneous, I.e. each column is of a different type. So this is quickly, what do we mean by the columnar data structure? What I'm showing here is coming from the our internal prototype build. So again, this is not available in 14.0 beta and it will not be available in the released 14.0. Tentatively we call that data columnar data structure. We call it tabular. Again, subject to change the way it works. Right now this is the constructor, we take that. Basically this is the representation of three columns of data, sex, size and length. We construct it and there is at this point not really a nice output form. But this will definitely change. This is how it looks like in a table view. So that's the table with three columns. We can extract the schema, underlying schema for that columnar data structure. So the first column is of type string. The second column is real 64, so is the third one. Right. So we basically in essence derive the types based on the on the entries for each column. Quickly, how we can extract the data, how we can extract the Data from for each column. So first of all you can call the data vector table property on that tabular object and get something that we call data vector table. So this is basically the the data structure that represents the whole table with three columns. Once we have that data table we can ask for the vector list. And this is basically now three vectors that represent each column. And when I call normal I basically get exactly the values that I entered in the constructor above here. Okay, that's basically everything that I want to show in for this Tabular project. There are actually many people involved, so hopefully it will be very nice addition to the next version of the workflow language. In the rest of this talk I will focus on the import and export formats that will be basically returning tabular object as the import will be tabular as a default element and export can basically take tabular object and export it to the formats that data scientists work with. But before that, let me tell a little bit about the already mentioned RL library. RO Library is an open source project developed by Apache foundation and the way they want us to think about this is the cross language development platform for in memory analytics. That's basically the description taken from the website of the RO library. So RO provides the building blocks for analytics operation and we're talking about large data sets so we can really care about the performance when we talk about about multi gigabyte files. And even though in this talk I will not be discussing any analytic operations, we definitely are very interested in using whatever is provided by RO Library and using it in our tabular object. But hopefully at the next workfarm technology conference you will see much more about this and also see comparisons and benchmarks that shows the performance of analytic operations on large data sets. Here I'm going to focus on the file formats. So Apache RO Library supports a number of file formats. In this talk I will discuss three of them. Parquet, CSV and Arrows ipc. I also mentioned orc. Here we do experiment. We have some implementation of that format. There are some issues with it on Windows platform so I'm not going to discuss it more. Hopefully all of this will be fixed and we also have this format in the first release of Tabular project. Arrow is used by many other projects as a backend analytic engine. The list of the projects can be found at the website and here I'm providing the full list of available languages that you can Once you develop in one of this language, you can easily integrate Arrow into your software. All right, so how I benchmarked Arrow Library In Python and in Wolfram language, I used one quite large file in the benchmark. So the file is available as a comma separated value file. It's more than 20 gigabytes stored in a single CSV file, four columns of type string and more than 160 million row rows. This is the file is taken from the. This is the basically Reddit place official canvas history from 2022. To Benchmark the remaining two formats, I converted CSV file to Parquet and RoIPC and I run all benchmarks on the same machine. All right, so let's start from CSV. So that's basically plain text format that uses commas to separate values. It can store numbers and strings in plain text. There is no compression, so any text editor can be used to open this file and edit. It is a very popular data exchange format, even though it has its flows, but it's still very popular. So here we have two tables, one for import, one for export. So let me start from explaining the columns reading column. This is basically timings in seconds for reading a file content, the whole file content into memory as a RO table. 12.3 seconds for for our workflow language prototype and 5.9 seconds for Python 3.11 and PI arrow version 13.0. And we also use version 13 of arrow in our prototype, so about two times slower. We are investigating why it is we're hoping to get this similar timings here. Now the conversion column means something different depending whether this is Wolfram language or Python. So for Wolfram language we are trying to design the tabular object to be as close to arrow table as possible. That's why the conversion between these two data structures is taking just 17 seconds. What's going on for Python is we are converting from arrow table to the data frame and that is taking significantly more time than in our scenario. So in total it's much better for word form language than for pyro and similarly for for export. So conversion for export again we converting from tabular to arrow table and from for Python we converting from Pandas data frame to arrow table. So that's the conversion once they are converted to arrow table we writing to file. This is writing to the CSV file. The timing is exactly the same for word from language in Python, but again the conversion is much faster here than for Python and the total time is again about twice as fast here for WordPress. So that's more or less everything for comma separated values. Let me move on to the other format. So parquet format was. It's another columnar storage format. It was created designed for the Apache Hadoop ecosystem and it's supposed to be very efficient for data storage and retrieval. It supports various compression schemes and encodings. And again the benchmarks methodology was exactly the same as before. So reading column shows the timings for getting the data into arrow table in memory and then the conversion to tabular and conversion to Pandas data frame and the total import time similar for export. Notice that for CSV our prototype was about twice as slow as Wolfram. Maybe I will go back to remind you it was 12 seconds versus 5.9 for CSV import, but for parquet is more or less the same, slightly faster in my benchmarks. So we are already basically the same speed of reading as part of conversion is exactly the same thing. I basically copied the values from the previous table because these are exactly the same data to be converted to data frame or to tabular and then the total import time 41 vs 192 for exports again the conversion timings are the same and writing is actually much better. 37 seconds versus 81 for Python and overall 60 seconds versus 153. Now I'm gonna talk a little bit about arrow columnar format. So RO library comes with its own format, both for flat and hierarchical data. It's not just format, it's also a protocol for serialization and inter process communication. And that's why the actual file formats are called roipc. And what's important for this format, it supports zero copy reads for fast access without serialization. And you will see that on the next slide. What I mean by that, It comes with two types, one designed for streaming, which we call ROIPC streaming format. And then for random access it's ROIPC file format. But I mean in a sense they are both files on your file system, in your file system. But the storage is optimized either for streaming or for random access. So let's start from the stream version of the RoITC. In this case, both in the import benchmarks, both for Wolfram language and for Python we are using memory mapping. So the file is loaded into memory and then decoded from memory. Notice the difference for reading, not even two seconds to load and to read more than 20 gigabytes of data from a file into memory, it's well a little slower. For Python, 7 seconds. Again for both cases we use memory mapping. So I it's hard to explain why Python is few times slower here. Conversions are again the same as for the previous benchmarks, but the total import time is quite Impressive for language compared to Python and similarly the export benchmarks. Basically writing times are exactly the same for both scenarios here. So once again what I mentioned on the previous slide, this zero copy zero copy reads without serialization. That's how you can get 1.9 seconds for 20 gigabytes of data only this format supports. Such feature. And very similar timings for the random access type of ROIPC 1.8 versus 7.2 and 21 for writing exactly the same for orphan language in Python. So this is more or less everything that I want to show when it comes to benchmarks. And this is the last slide of this presentation. So let me summarize. The new columnar data structure that we call tabular is coming and will probably be reduced in Warframe Language 14.1. Import and export will support new columnar file formats, at least the three of them that I discussed today. Hopefully there will be more already mentioned orc and benchmark results. These preliminary benchmarks shows that the much better performance for workflow language compared to Python libraries. I want to invite everyone to the related talk. Sean will talk on Friday. We'll give some updates to import export features that are coming in version 14.0 as well as discuss some future directions. All right, thank you for listening and we may have some time for questions.

Got a couple questions here in the chat. Does Arrow format support random access? If you go back a few slides, he mentioned that Aero IPC has two different formats. They're both, you know, files on your system, but IPC streaming files are going to be very efficient if you're reading through the file linearly. But yes, Arrow does have random access file format that is similar to HDF5 in the sense that it's efficient for accessing random data, we got another question here about improving the DXF import export functionality. There is not an existing project to that, but I'm going to go ahead and write down your note. Thank you. Looks like we have another one asking what format is fastest for large load and save example wdx. Well, that is highly dependent on exactly what your data is. So if you want to serialize a large data set like a Wolfram language data set, again, there's kind of a touch of vagueness to the question, because is the question serializing the data set except itself? If so, Wolfram language has binary serialize, right? So we can serialize a Wolfram language expression in the most efficient format possible. Now, if you actually want to save it in a file format that is more structured and not specifically serialized to A Wolfram language expression, you're probably better off taking the data behind the data set and storing it to one of these arrow formats or something like HDF5. Currently.

Yes, I was going to say that you can already try something like HDF is quite efficiently implemented in the Wolfram language, and you can. This is the hierarchical data format. So if your data fit that kind of structure, then that should be your format of choice. If not binary serialize, for some reason, binary serialize is not.

Well, binary serialize. If you have a Wolfram language expression that you want to serialize, which would be a data set, is a Wolfram language expression, Streaming data and processing it on the fly. So this current iteration, due to the performance of these kinds of things, we're mostly looking at importing large files using these arrow libraries, but they also have random access type features. And then there is this arrow IPC streaming format that has this kind of streaming interface. This first stage of development is mostly taking large files and accessing chunks of them. And then we can imagine a future where we start to look at huge chunks of data and how we might process it as a stream. That's a great question, right?

I just want to add that by default, import will import all the columns from the columnar data file. But there is a way to specify which columns you are interested in. And for some reason you are interested only in the first two columns. That is going to be possible. There should also be possible to actually import specific rows, or maybe not as efficient as columns. But we are trying to work on the partial access also for rows. Okay, I think that's the end of time for questions in these presentations. Thank you for listening and watching.