{"id":1174,"date":"2023-05-05T10:21:27","date_gmt":"2023-05-05T14:21:27","guid":{"rendered":"https:\/\/blogs.mathworks.com\/matlab\/?p=1174"},"modified":"2023-06-15T06:55:44","modified_gmt":"2023-06-15T10:55:44","slug":"working-efficiently-with-data-parquet-files-and-the-needle-in-a-haystack-problem","status":"publish","type":"post","link":"https:\/\/blogs.mathworks.com\/matlab\/2023\/05\/05\/working-efficiently-with-data-parquet-files-and-the-needle-in-a-haystack-problem\/","title":{"rendered":"Working efficiently with data: Parquet files and the Needle in a Haystack problem"},"content":{"rendered":"
This is a guest post by Onomitra Ghosh who is the Product Manager for MATLAB Data Analysis and Big Data workflows.\r\n\r\nHow big does data need to be before it can be called big? While there is no definite number beyond which data can be called big data, it's safe to say that when it becomes uncomfortably large for the memory of your computer, it is getting big. One way to tackle big data is to throw a lot of processing power at it (either locally or in cloud). But quite often, when working with big data, we do not always need all of it for our analysis. So, a smarter first step is to read only the data that is needed. This is especially true for the <\/span>\"needle in a haystack\" problem <\/span>where we need to find a small slice of information in an ocean of data. In this blog, we will show how using Parquet files for storing big data and performing read-time filtering can make the subsequent analysis more efficient than storing and reading them from conventional file formats like csv, spreadsheets etc.<\/span><\/div>
<\/span><\/div>
<\/span>\"\"<\/img>\"\"<\/img><\/div>

A quick introduction to Parquet<\/span><\/h2>
Before we get too far ahead, let's do a quick introduction to Parquet file format. <\/span>Parquet<\/span><\/a> is in open-source column-oriented data storage format developed and maintained as a part of the <\/span>Apache Software Foundation<\/span><\/a>. Usage of Parquet files for big data has been steadily growing since its inception because they are very efficient to store and read data.<\/span><\/div>
At the lowest level, a <\/span>Parquet file stores data in a columnar format<\/span>. Unlike more traditional row-based storages, Parquet files store data of each column together. As a result, if we want to read specific columns, we can read a contiguous set of data instead of spending time skipping and seeking or loading the whole file and then selecting specific columns in memory. Selecting and reading specific columns is also called <\/span>projection pushdown<\/span>.<\/span><\/div>
<\/span>\"\"<\/img><\/div>
Along with reading specific columns, often we also only need a subset of rows for our analysis. To support this, Parquet allows writing the data into <\/span>row groups. <\/span>While within each row group data is laid out in a columnar format, the row groups themselves separate out the slices of data that can be useful for filtering based on specific row values.<\/span><\/div>
<\/span>\"\"<\/img><\/div>
In addition, each Parquet file maintains a set of metadata about the file, each row group, and each column. This may contain information about data type, codec, number of values, read offset etc. These metadata help to quickly seek the right set of rows to read for a specific filtering condition. This is also called <\/span>predicate pushdown<\/span>. These pushdown capabilities help to filter the data while reading from the file instead of reading the entire dataset into memory and then filtering it. Last but not the least, Parquet also allows efficient compression techniques. Together these capabilities help in storing large amount of data with a relatively smaller footprint and read only the values that are needed. <\/span><\/div>
In this blog, we are going to focus on how writing out a flat tabular data into Parquet files using row groups can help in faster data analysis. To keep things simple, we will not be exploring any kinds of parallelization (that's for a future blog). Parallelization using <\/span>Parallel Computing Toolbox<\/span><\/a> can make the code run even faster. But today, we will measure and highlight the performance impact of simply using Parquet files instead of csv while analyzing data with just your desktop machine resources.<\/span><\/div>

Dataset <\/span><\/h2>
The data we are going to use is a modified subset of the <\/span>Flight Data from Dashlink<\/span><\/a>. We are going to work with the flight data for <\/span>Tail 660<\/span><\/a>. The dataset contains information of 4578 unique flights flown by this plane between 2001 to 2010. Each flight data is stored in a separate CSV file. Individually each of these files are not very big. But together there are about <\/span>22 million rows of flight information contained in these 4578 files<\/span>. Also, each file has different flight sensor information stored in its 87 columns. The entire dataset is about <\/span>15GB in memory<\/span>. While, this is not terabytes or petabytes of data, it is still big enough that can make analyzing it in a standard computer uncomfortable and frustrating. <\/span><\/div>
\"\"<\/img><\/div>

Problem statement<\/span><\/h2>
For our analysis problem, we want to find if any of these 4578 flights flew over Massachusetts. Two of the flight sensors record the latitude and longitude information. We can use this information to track the flight path for each of these flights. Let's read a sample file and plot the flight path.<\/span><\/div>
exampleCSVFile = <\/span>\"data\\csv\\660200110191613_1HZ.csv\"<\/span>;<\/span><\/span><\/div><\/div>
tcsv = readtable(exampleCSVFile);<\/span><\/span><\/div><\/div>
head(tcsv)<\/span><\/span><\/div>
TripNo<\/strong> Time<\/strong> ABRK<\/strong> ACMT<\/strong> AIL_1<\/strong> AIL_2<\/strong> ALTS<\/strong> APFD<\/strong> ATEN<\/strong> A_T<\/strong> BLV<\/strong> BPGR_1<\/strong> BPGR_2<\/strong> BPYR_1<\/strong> BPYR_2<\/strong> CALT<\/strong> CASS<\/strong> CRSS<\/strong> DFGS<\/strong> DWPT<\/strong> EAI<\/strong> ELEV_1<\/strong> ELEV_2<\/strong> EVNT<\/strong> FADF<\/strong> FADS<\/strong> FGC3<\/strong> FIRE_1<\/strong> FIRE_2<\/strong> FIRE_3<\/strong> FIRE_4<\/strong> FLAP<\/strong> FQTY_1<\/strong> FQTY_2<\/strong> FQTY_3<\/strong> FQTY_4<\/strong> GLS<\/strong> GPWS<\/strong> HDGS<\/strong> HF1<\/strong> HF2<\/strong> HYDG<\/strong> HYDY<\/strong> ILSF<\/strong> LATP<\/strong> LGDN<\/strong> LGUP<\/strong> LMOD<\/strong> LOC<\/strong> LONP<\/strong> MNS<\/strong> MRK<\/strong> MW<\/strong> N1CO<\/strong> OIPL<\/strong> OIP_1<\/strong> OIP_2<\/strong> OIP_3<\/strong> OIP_4<\/strong> OIT_1<\/strong> OIT_2<\/strong> OIT_3<\/strong> OIT_4<\/strong> PACK<\/strong> PH<\/strong> POVT<\/strong> PTRM<\/strong> PUSH<\/strong> SAT<\/strong> SMKB<\/strong> SMOK<\/strong> SNAP<\/strong> SPLG<\/strong> SPLY<\/strong> SPL_1<\/strong> SPL_2<\/strong> TAI<\/strong> TAT<\/strong> TCAS<\/strong> TMAG<\/strong> TMODE<\/strong> VHF1<\/strong> VHF2<\/strong> VHF3<\/strong> VMODE<\/strong> VSPS<\/strong> WAI_1<\/strong> WAI_2<\/strong> WOW<\/strong> WSHR<\/strong>\r\n _________<\/strong> ________________________<\/strong> ______<\/strong> ____<\/strong> ______<\/strong> ______<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> ___<\/strong> ___<\/strong> ______<\/strong> ______<\/strong> ______<\/strong> ______<\/strong> ____<\/strong> ____<\/strong> _______<\/strong> ____<\/strong> _____<\/strong> ___<\/strong> ______<\/strong> ______<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> ______<\/strong> ______<\/strong> ______<\/strong> ______<\/strong> ____<\/strong> ______<\/strong> ______<\/strong> ______<\/strong> ______<\/strong> ________<\/strong> ____<\/strong> _______<\/strong> ___<\/strong> ___<\/strong> ____<\/strong> ____<\/strong> _________<\/strong> ______<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> ________<\/strong> _____<\/strong> ___<\/strong> ___<\/strong> __<\/strong> ____<\/strong> ____<\/strong> _____<\/strong> _____<\/strong> _____<\/strong> _____<\/strong> ______<\/strong> ______<\/strong> ______<\/strong> ______<\/strong> ____<\/strong> __<\/strong> ____<\/strong> ______<\/strong> ____<\/strong> ___<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> ______<\/strong> _____<\/strong> ___<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> _____<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> _____<\/strong> _____<\/strong> _____<\/strong> _____<\/strong> ___<\/strong> ____<\/strong>\r\n\r\n 6.602e+14 19-Oct-2001 16:13:18.000 119.32 60 80.422 80.668 4000 2 0 1 0 0 34.18 58.594 0 0 134 -70.048 1 32924 0 17.551 65.979 1 15 15 120 0 0 0 0 98 8216 4464 0 8048 -0.00195 1 -131.04 1 1 0 0 1.189e+06 36.897 0 1 12 -0.36868 -76.2 201 7 0 1 0 0 0 0 0 26.361 25.018 18.302 20.989 2 1 0 3.7082 1 2.5 1 0 1 1 1 72.275 72.41 0 3 0 1 2 1 1 1 11 -2000 0 0 0 1 \r\n 6.602e+14 19-Oct-2001 16:13:19.000 119.32 59 80.402 80.688 4000 2 0 1 0 0 29.297 39.063 0 0 134 -70.048 1 32924 0 17.571 65.959 1 15 15 120 0 0 0 0 98 8216 4464 0 8048 -0.03939 1 -131.04 1 1 0 0 1.189e+06 36.897 0 1 12 -0.37181 -76.2 201 7 0 1 0 0 0 0 0 26.361 26.361 19.645 20.989 2 1 0 3.7082 1 2.5 1 0 1 1 1 72.275 72.41 0 3 0 1 2 1 1 1 11 -2000 0 0 0 1 \r\n 6.602e+14 19-Oct-2001 16:13:20.000 119.31 60 80.402 80.668 4000 2 0 1 0 0 34.18 43.945 0 0 134 -70.048 1 32924 0 17.571 65.979 1 15 15 120 0 0 0 0 98 8216 4464 0 8048 -0.00819 1 -131.04 1 1 0 0 1.189e+06 36.897 0 1 12 -0.37162 -76.2 201 7 0 1 0 0 0 0 0 26.361 26.361 19.645 20.989 2 1 0 3.7287 1 2.5 1 0 1 1 1 72.275 72.41 0 3 0 1 2 1 1 1 11 -2000 0 0 0 1 \r\n 6.602e+14 19-Oct-2001 16:13:21.000 119.32 60 80.402 80.668 4000 2 0 1 0 0 34.18 43.945 0 0 134 -70.048 1 32924 0 17.551 65.959 1 15 15 120 0 0 0 0 98 8216 4464 0 8048 -0.00156 1 -131.04 1 1 0 0 1.189e+06 36.897 0 1 12 -0.37024 -76.2 201 7 0 1 0 0 0 0 0 26.361 26.361 19.645 22.332 2 1 0 3.7287 1 2.5 1 0 1 1 1 72.275 72.4 0 2.75 0 1 2 1 1 1 11 -2000 0 0 0 1 \r\n 6.602e+14 19-Oct-2001 16:13:22.000 119.31 60 80.402 80.688 4000 2 0 1 0 0 34.18 43.945 0 0 134 -70.048 1 32924 0 17.571 65.959 1 15 15 120 0 0 0 0 98 8216 4464 0 8048 -0.00156 1 -131.04 1 1 0 0 1.189e+06 36.897 0 1 12 -0.36593 -76.2 201 7 0 1 0 0 0 0 0 26.361 26.361 19.645 22.332 2 1 0 3.7082 1 2.5 1 0 1 1 1 72.275 72.4 0 2.75 0 1 2 1 1 1 11 -2000 0 0 0 1 \r\n 6.602e+14 19-Oct-2001 16:13:23.000 119.31 60 80.402 80.668 4000 2 0 1 0 0 29.297 43.945 0 0 134 -70.048 1 32924 0 17.571 65.959 1 15 15 120 0 0 0 0 98 8216 4464 0 8048 0.00585 1 -131.04 1 1 0 0 1.189e+06 36.897 0 1 12 -0.36907 -76.2 201 7 0 1 0 0 0 0 0 26.361 26.361 19.645 22.332 2 1 0 3.7287 1 2.5 1 0 1 1 1 72.275 72.4 0 2.75 0 1 2 1 1 1 11 -2000 0 0 0 1 \r\n 6.602e+14 19-Oct-2001 16:13:24.000 119.32 59 80.402 80.668 4000 2 0 1 0 0 34.18 43.945 0 0 134 -70.048 1 0 0 17.571 65.959 1 15 15 120 0 0 0 0 98 8216 4464 0 8048 0.0039 1 -131.04 1 1 0 0 1.189e+06 36.897 0 1 12 -0.36907 -76.2 201 7 0 1 0 0 0 0 0 26.361 26.361 19.645 22.332 2 1 0 3.7287 1 2.5 1 0 1 1 1 72.275 72.41 0 2.75 0 1 2 1 1 1 11 -2000 0 0 0 1 \r\n 6.602e+14 19-Oct-2001 16:13:25.000 119.31 60 80.402 80.668 4000 2 0 1 0 0 29.297 43.945 0 0 134 -70.048 1 32928 0 17.592 65.959 1 15 15 120 0 0 0 0 98 8216 4464 0 8048 0.01092 1 -131.04 1 1 0 0 1.189e+06 36.897 0 1 12 -0.36672 -76.2 201 7 0 1 0 0 0 0 0 27.704 25.018 19.645 22.332 2 1 0 3.7082 1 2.5 1 0 1 1 1 72.266 72.41 0 2.75 0 1 2 1 1 1 11 -2000 0 0 0 1 <\/div><\/div><\/div><\/div><\/div>
We use <\/span>geoplot<\/span><\/a> to plot the flight path.<\/span><\/div>
geoplot(tcsv.LATP,tcsv.LONP,<\/span>'Color'<\/span>,<\/span>'b'<\/span>,<\/span>'LineWidth'<\/span>,3);<\/span><\/span><\/div><\/div>
geobasemap <\/span>colorterrain<\/span><\/span><\/div>
<\/div>
<\/div>
<\/div>
<\/div><\/div><\/div><\/div><\/div>
Looks like this particular flight flew from Norfolk, VA to Minneapolis, MN. To find flights that flew over Massachusetts, we have to compare each flight's path (from each of those 4578 files!) against Massachusetts' coordinates. We will assume the following as a rough estimate of Massachusetts latitude and longitude.<\/span><\/div>
MALatMin = 42.03;<\/span><\/span><\/div><\/div>
MALatMax = 42.72;<\/span><\/span><\/div><\/div>
MALonMin = -73.37;<\/span><\/span><\/div><\/div>
MALonMax = -70.06;<\/span><\/span><\/div><\/div>
MACoordinates = [MALatMin, MALatMax, MALonMin, MALonMax];<\/span><\/span><\/div><\/div><\/div>
By the way, <\/span>this a true \"Needle in a haystack\" problem. <\/span>There are only 301 out of 22 million flight records (0.001%) spread over these 4578 files that has Massachusetts latitude and longitudes in this dataset. Our exercise will be to find these 301 rows as efficiently as possible.<\/span><\/div>

CSV vs. Parquet: Single file<\/span><\/h2>
Let's start by understanding the differences between csv and Parquet a little bit. We have already looked at how to read the csv file into a MATLAB table. Now let's try the same with Parquet. First, let's write out this flight information in a Parquet file. The simplest way to write to a Parquet file is by calling the <\/span>parquetwrite<\/span><\/a> function. We can then check the information of the Parquet file using <\/span>parquetinfo<\/span><\/a>.<\/span><\/div>
exampleParquetFile = <\/span>\"exampleparquet.parquet\"<\/span>;<\/span><\/span><\/div><\/div>
parquetwrite(exampleParquetFile, tcsv); <\/span><\/span><\/div><\/div>
parquetinfo(exampleParquetFile)<\/span><\/span><\/div>
ans = <\/span><\/div>
ParquetInfo with properties:\r\n\r\n Filename: \"C:\\Blogs\\Introduction to Parquet\\exampleparquet.parquet\"\r\n FileSize: 703457\r\n NumRowGroups: 1\r\n RowGroupHeights: 14136\r\n VariableNames: [\"TripNo\" \"Time\" \"ABRK\" \"ACMT\" \"AIL_1\" \"AIL_2\" \"ALTS\" \"APFD\" \"ATEN\" \"A_T\" \"BLV\" \"BPGR_1\" \"BPGR_2\" \"BPYR_1\" \"BPYR_2\" \"CALT\" \"CASS\" \"CRSS\" \"DFGS\" \"DWPT\" \"EAI\" \u2026 ]\r\n VariableTypes: [\"double\" \"datetime\" \"double\" \"double\" \"double\" \"double\" \"double\" \"double\" \"double\" \"double\" \"double\" \"double\" \"double\" \"double\" \"double\" \"double\" \"double\" \"double\" \u2026 ]\r\n VariableCompression: [\"snappy\" \"snappy\" \"snappy\" \"snappy\" \"snappy\" \"snappy\" \"snappy\" \"snappy\" \"snappy\" \"snappy\" \"snappy\" \"snappy\" \"snappy\" \"snappy\" \"snappy\" \"snappy\" \"snappy\" \"snappy\" \u2026 ]\r\n VariableEncoding: [\"dictionary\" \"dictionary\" \"dictionary\" \"dictionary\" \"dictionary\" \"dictionary\" \"dictionary\" \"dictionary\" \"dictionary\" \"dictionary\" \"dictionary\" \"dictionary\" \"dictionary\" \u2026 ]\r\n Version: \"2.0\"\r\n<\/div><\/div><\/div><\/div><\/div><\/div>
Let's read the file back. We can use <\/span>parquetread<\/span><\/a> function to read data from Parquet files into a MATLAB table.<\/span><\/div>
tparquet = parquetread(exampleParquetFile);<\/span><\/span><\/div><\/div>
head(tparquet)<\/span><\/span><\/div>
TripNo<\/strong> Time<\/strong> ABRK<\/strong> ACMT<\/strong> AIL_1<\/strong> AIL_2<\/strong> ALTS<\/strong> APFD<\/strong> ATEN<\/strong> A_T<\/strong> BLV<\/strong> BPGR_1<\/strong> BPGR_2<\/strong> BPYR_1<\/strong> BPYR_2<\/strong> CALT<\/strong> CASS<\/strong> CRSS<\/strong> DFGS<\/strong> DWPT<\/strong> EAI<\/strong> ELEV_1<\/strong> ELEV_2<\/strong> EVNT<\/strong> FADF<\/strong> FADS<\/strong> FGC3<\/strong> FIRE_1<\/strong> FIRE_2<\/strong> FIRE_3<\/strong> FIRE_4<\/strong> FLAP<\/strong> FQTY_1<\/strong> FQTY_2<\/strong> FQTY_3<\/strong> FQTY_4<\/strong> GLS<\/strong> GPWS<\/strong> HDGS<\/strong> HF1<\/strong> HF2<\/strong> HYDG<\/strong> HYDY<\/strong> ILSF<\/strong> LATP<\/strong> LGDN<\/strong> LGUP<\/strong> LMOD<\/strong> LOC<\/strong> LONP<\/strong> MNS<\/strong> MRK<\/strong> MW<\/strong> N1CO<\/strong> OIPL<\/strong> OIP_1<\/strong> OIP_2<\/strong> OIP_3<\/strong> OIP_4<\/strong> OIT_1<\/strong> OIT_2<\/strong> OIT_3<\/strong> OIT_4<\/strong> PACK<\/strong> PH<\/strong> POVT<\/strong> PTRM<\/strong> PUSH<\/strong> SAT<\/strong> SMKB<\/strong> SMOK<\/strong> SNAP<\/strong> SPLG<\/strong> SPLY<\/strong> SPL_1<\/strong> SPL_2<\/strong> TAI<\/strong> TAT<\/strong> TCAS<\/strong> TMAG<\/strong> TMODE<\/strong> VHF1<\/strong> VHF2<\/strong> VHF3<\/strong> VMODE<\/strong> VSPS<\/strong> WAI_1<\/strong> WAI_2<\/strong> WOW<\/strong> WSHR<\/strong>\r\n _________<\/strong> ____________________<\/strong> ______<\/strong> ____<\/strong> ______<\/strong> ______<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> ___<\/strong> ___<\/strong> ______<\/strong> ______<\/strong> ______<\/strong> ______<\/strong> ____<\/strong> ____<\/strong> _______<\/strong> ____<\/strong> _____<\/strong> ___<\/strong> ______<\/strong> ______<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> ______<\/strong> ______<\/strong> ______<\/strong> ______<\/strong> ____<\/strong> ______<\/strong> ______<\/strong> ______<\/strong> ______<\/strong> ________<\/strong> ____<\/strong> _______<\/strong> ___<\/strong> ___<\/strong> ____<\/strong> ____<\/strong> _________<\/strong> ______<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> ________<\/strong> _____<\/strong> ___<\/strong> ___<\/strong> __<\/strong> ____<\/strong> ____<\/strong> _____<\/strong> _____<\/strong> _____<\/strong> _____<\/strong> ______<\/strong> ______<\/strong> ______<\/strong> ______<\/strong> ____<\/strong> __<\/strong> ____<\/strong> ______<\/strong> ____<\/strong> ___<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> ______<\/strong> _____<\/strong> ___<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> _____<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> _____<\/strong> _____<\/strong> _____<\/strong> _____<\/strong> ___<\/strong> ____<\/strong>\r\n\r\n 6.602e+14 19-Oct-2001 16:13:18 119.32 60 80.422 80.668 4000 2 0 1 0 0 34.18 58.594 0 0 134 -70.048 1 32924 0 17.551 65.979 1 15 15 120 0 0 0 0 98 8216 4464 0 8048 -0.00195 1 -131.04 1 1 0 0 1.189e+06 36.897 0 1 12 -0.36868 -76.2 201 7 0 1 0 0 0 0 0 26.361 25.018 18.302 20.989 2 1 0 3.7082 1 2.5 1 0 1 1 1 72.275 72.41 0 3 0 1 2 1 1 1 11 -2000 0 0 0 1 \r\n 6.602e+14 19-Oct-2001 16:13:19 119.32 59 80.402 80.688 4000 2 0 1 0 0 29.297 39.063 0 0 134 -70.048 1 32924 0 17.571 65.959 1 15 15 120 0 0 0 0 98 8216 4464 0 8048 -0.03939 1 -131.04 1 1 0 0 1.189e+06 36.897 0 1 12 -0.37181 -76.2 201 7 0 1 0 0 0 0 0 26.361 26.361 19.645 20.989 2 1 0 3.7082 1 2.5 1 0 1 1 1 72.275 72.41 0 3 0 1 2 1 1 1 11 -2000 0 0 0 1 \r\n 6.602e+14 19-Oct-2001 16:13:20 119.31 60 80.402 80.668 4000 2 0 1 0 0 34.18 43.945 0 0 134 -70.048 1 32924 0 17.571 65.979 1 15 15 120 0 0 0 0 98 8216 4464 0 8048 -0.00819 1 -131.04 1 1 0 0 1.189e+06 36.897 0 1 12 -0.37162 -76.2 201 7 0 1 0 0 0 0 0 26.361 26.361 19.645 20.989 2 1 0 3.7287 1 2.5 1 0 1 1 1 72.275 72.41 0 3 0 1 2 1 1 1 11 -2000 0 0 0 1 \r\n 6.602e+14 19-Oct-2001 16:13:21 119.32 60 80.402 80.668 4000 2 0 1 0 0 34.18 43.945 0 0 134 -70.048 1 32924 0 17.551 65.959 1 15 15 120 0 0 0 0 98 8216 4464 0 8048 -0.00156 1 -131.04 1 1 0 0 1.189e+06 36.897 0 1 12 -0.37024 -76.2 201 7 0 1 0 0 0 0 0 26.361 26.361 19.645 22.332 2 1 0 3.7287 1 2.5 1 0 1 1 1 72.275 72.4 0 2.75 0 1 2 1 1 1 11 -2000 0 0 0 1 \r\n 6.602e+14 19-Oct-2001 16:13:22 119.31 60 80.402 80.688 4000 2 0 1 0 0 34.18 43.945 0 0 134 -70.048 1 32924 0 17.571 65.959 1 15 15 120 0 0 0 0 98 8216 4464 0 8048 -0.00156 1 -131.04 1 1 0 0 1.189e+06 36.897 0 1 12 -0.36593 -76.2 201 7 0 1 0 0 0 0 0 26.361 26.361 19.645 22.332 2 1 0 3.7082 1 2.5 1 0 1 1 1 72.275 72.4 0 2.75 0 1 2 1 1 1 11 -2000 0 0 0 1 \r\n 6.602e+14 19-Oct-2001 16:13:23 119.31 60 80.402 80.668 4000 2 0 1 0 0 29.297 43.945 0 0 134 -70.048 1 32924 0 17.571 65.959 1 15 15 120 0 0 0 0 98 8216 4464 0 8048 0.00585 1 -131.04 1 1 0 0 1.189e+06 36.897 0 1 12 -0.36907 -76.2 201 7 0 1 0 0 0 0 0 26.361 26.361 19.645 22.332 2 1 0 3.7287 1 2.5 1 0 1 1 1 72.275 72.4 0 2.75 0 1 2 1 1 1 11 -2000 0 0 0 1 \r\n 6.602e+14 19-Oct-2001 16:13:24 119.32 59 80.402 80.668 4000 2 0 1 0 0 34.18 43.945 0 0 134 -70.048 1 0 0 17.571 65.959 1 15 15 120 0 0 0 0 98 8216 4464 0 8048 0.0039 1 -131.04 1 1 0 0 1.189e+06 36.897 0 1 12 -0.36907 -76.2 201 7 0 1 0 0 0 0 0 26.361 26.361 19.645 22.332 2 1 0 3.7287 1 2.5 1 0 1 1 1 72.275 72.41 0 2.75 0 1 2 1 1 1 11 -2000 0 0 0 1 \r\n 6.602e+14 19-Oct-2001 16:13:25 119.31 60 80.402 80.668 4000 2 0 1 0 0 29.297 43.945 0 0 134 -70.048 1 32928 0 17.592 65.959 1 15 15 120 0 0 0 0 98 8216 4464 0 8048 0.01092 1 -131.04 1 1 0 0 1.189e+06 36.897 0 1 12 -0.36672 -76.2 201 7 0 1 0 0 0 0 0 27.704 25.018 19.645 22.332 2 1 0 3.7082 1 2.5 1 0 1 1 1 72.266 72.41 0 2.75 0 1 2 1 1 1 11 -2000 0 0 0 1 <\/div><\/div><\/div><\/div><\/div>
The CSV file is over 8Mb!<\/span><\/div>
s = dir(exampleCSVFile); <\/span><\/span><\/div><\/div>
disp(<\/span>\"CSV file size: \" <\/span>+ s.bytes\/1024\/1024 + <\/span>\"MB\"<\/span>)<\/span><\/span><\/div>
CSV file size: 8.3831MB<\/div><\/div><\/div><\/div><\/div>
While the Parquet file is much smaller at only 0.67Mb.<\/span><\/div>
s = dir(exampleParquetFile); <\/span><\/span><\/div><\/div>
disp(<\/span>\"Parquet file size: \" <\/span>+ s.bytes\/1024\/1024 + <\/span>\"MB\"<\/span>)<\/span><\/span><\/div>
Parquet file size: 0.67087MB<\/div><\/div><\/div><\/div><\/div>
We can immediately observe one of the benefits of using Parquet. Just simply writing this dataset as a Parquet file helps in<\/span> reducing the file size on disk by approximately 12x compared to CSV. <\/span><\/div>
tstart = tic;readtable(exampleCSVFile);tend = toc(tstart)<\/span><\/span><\/div>
tend = 0.4944<\/div><\/div><\/div>
tstart = tic;parquetread(exampleParquetFile);tend = toc(tstart)<\/span><\/span><\/div>
tend = 0.0814<\/div><\/div><\/div><\/div>
Even the <\/span>read time is 6x faster when reading from Parquet vs. csv.<\/span> Note, that the exact speed up and file size will depend on many factors including the kind of data contained in the files, how many row groups are formed etc.<\/span><\/div>

Datastore, Tall and Rowfilter<\/span><\/h2>
While reading data from a single Parquet file was faster, the real benefit of using Parquet files is realized when the dataset is big. But we cannot use <\/span>readtable<\/span><\/a> and <\/span>parquetread<\/span><\/a> when working with data spread over multiple files like this one. Instead, we will use the following functionalities in MATLAB that can help in working with big and distributed data.<\/span><\/div>
1. Datastores<\/span> are a way to read big and distributed data in MATLAB. A <\/span>datastore<\/span><\/a> does not directly read the data. Instead, it holds information about which files to read and how to read them when requested. This is very helpful in iteratively reading a large collection of files instead of loading all of it at once. MATLAB has datastores for various file formats, including <\/span>text<\/span><\/a>, <\/span>spreadsheet<\/span><\/a>, <\/span>image<\/span><\/a>, <\/span>Parquet<\/span><\/a>, and even <\/span>custom format files<\/span><\/a>. For a full list of types of datastores, see <\/span>documentation on available datastores<\/span><\/a>. In this exercise we will use tabularTextDatastore for processing the csv files and parquetDatastore for the parquet files.<\/span><\/div>
2. Tall<\/span> is a lazy evaluation framework in MATLAB which is created based on a datastore. The advantage of using <\/span>tall<\/span><\/a> is that we can take the same code that runs on regular MATLAB tables and run on a tall table backed by the datastore. We don't need to learn a whole lot of new syntaxes and functions to work with bigger data. But, because of its lazy evaluation, tall code is not executed immediately. Instead, when the <\/span>gather<\/span><\/a> function is called, tall reads the data from the underlying datastore and runs the operations as they are read. Tall supports hundreds of data analysis and machine learning functions that work with MATLAB tables and timetables. Tall tables can also be indexed like regular tables to find the relevant rows and columns.<\/span><\/div>
3. Rowfilter<\/span> is a relatively new concept in MATLAB introduced in R2022a. A <\/span>rowfilter<\/span><\/a> is a MATLAB object that helps to specify which rows to import. For Parquet files, rowfilter takes advantage of predicate pushdown and filters the rows at the file level using Parquet's rowgroup information. Rowfilters can be specified in <\/span>parquetread<\/span><\/a> and <\/span>parquetDatastore<\/span><\/a>. But the nice thing about using tall is that we we don't need to specify rowfilter explicitly. If we index tall tables to find relevant rows and columns (like a regular MATLAB table), it automatically uses rowfilter to perform read-time filtering on Parquet data without us having to write any additional code.<\/span><\/div>

Data reading strategies<\/span><\/h2>
For this exercise, we will consider 3 different data reading strategies. For each of them, we will first create a datastore (<\/span>ds<\/span>) and then a tall table (<\/span>tds<\/span>) on the datastore. We will then use <\/span>tds<\/span> to index and read the data (shown in the following code examples).<\/span><\/div>
Read strategy 1:<\/span> First read the entire data into memory and then find the relevant flights over MA (Of course this will be slow and ill-advised, but just for comparison's sake)<\/span><\/div>
allData = gather(tds);<\/span><\/span><\/div>
flightsOverMA = allData(allData.LATP>coordinates(1) & allData.LATP<coordinates(2) & allData.LONP>coordinates(3) & allData.LONP<coordinates(4),[<\/span>\"TripNo\"<\/span>,<\/span>\"LATP\"<\/span>,<\/span>\"LONP\"<\/span>]);<\/span><\/span><\/div><\/div>
Read strategy 2: <\/span>Filter the data for the matching latitudes and longitudes at read-time and read all columns for the filtered rows. Then choose the relevant columns.<\/span><\/div>
flightsOverMAtall = tds(tds.LATP>coordinates(1) & tds.LATP<coordinates(2) & tds.LONP>coordinates(3) & tds.LONP<coordinates(4),:); <\/span><\/span><\/div>
flightsOverMAAllColumns = gather(flightsOverMAtall);<\/span><\/span><\/div>
flightsOverMA = flightsOverMAAllColumns(:,[<\/span>\"TripNo\"<\/span>,<\/span>\"LATP\"<\/span>,<\/span>\"LONP\"<\/span>])<\/span><\/span><\/div><\/div>
Read strategy 3: <\/span>Read only relevant columns for matching latitudes and longitudes (all filtering happens at read-time)<\/span><\/div>
flightsOverMAtall = tds(tds.LATP>coordinates(1) & tds.LATP<coordinates(2) & tds.LONP>coordinates(3) & tds.LONP<coordinates(4),[<\/span>\"TripNo\"<\/span>,<\/span>\"LATP\"<\/span>,<\/span>\"LONP\"<\/span>]); <\/span><\/span><\/div>
flightsOverMA = gather(flightsOverMAtall);<\/span><\/span><\/div><\/div>
Since we have to do the same reads twice (csv and Parquet), the above read codes are converted to convenient helper functions at the end of this blog.<\/span><\/div>
Also note that when tall tables are evaluated (using gather), it displays the time taken by the evaluation passes. However, the actual data analysis time for each of the above strategies are slightly more than just the gather command. They are captured using tic\/toc around the complete read and filtering code. <\/span><\/div>

CSV<\/span><\/h3>
First let's trying solving our problem by using csv files. To read data from the dataset, we will use Tall on <\/span>tabularTextDatastore<\/span><\/a>. Note that neither of these objects actually reads the entire dataset. Datastore just stores information on how to read the files and the tall table reads the first few lines to show us what the data looks like.<\/span><\/div>
ds = tabularTextDatastore(<\/span>\"data\\csv\\*.csv\"<\/span>)<\/span><\/span><\/div>
ds = <\/span><\/div>
TabularTextDatastore with properties:\r\n\r\n Files: {\r\n 'C:\\Blogs\\Introduction to Parquet\\data\\csv\\660200104100743_1HZ.csv';\r\n 'C:\\Blogs\\Introduction to Parquet\\data\\csv\\660200104100915_1HZ.csv';\r\n 'C:\\Blogs\\Introduction to Parquet\\data\\csv\\660200104101125_1HZ.csv'\r\n ... and 4575 more\r\n }\r\n Folders: {\r\n 'C:\\Blogs\\Introduction to Parquet\\data\\csv'\r\n }\r\n FileEncoding: 'UTF-8'\r\n AlternateFileSystemRoots: {}\r\n VariableNamingRule: 'modify'\r\n ReadVariableNames: true\r\n VariableNames: {'TripNo', 'Time', 'ABRK' ... and 87 more}\r\n DatetimeLocale: en_US\r\n\r\n Text Format Properties:\r\n NumHeaderLines: 0\r\n Delimiter: ','\r\n RowDelimiter: '\\r\\n'\r\n TreatAsMissing: ''\r\n MissingValue: NaN\r\n\r\n Advanced Text Format Properties:\r\n TextscanFormats: {'%f', '%{dd-MMM-uuuu HH:mm:ss.SSS}D', '%f' ... and 87 more}\r\n TextType: 'char'\r\n ExponentCharacters: 'eEdD'\r\n CommentStyle: ''\r\n Whitespace: ' \\b\\t'\r\n MultipleDelimitersAsOne: false\r\n\r\n Properties that control the table returned by preview, read, readall:\r\n SelectedVariableNames: {'TripNo', 'Time', 'ABRK' ... and 87 more}\r\n SelectedFormats: {'%f', '%{dd-MMM-uuuu HH:mm:ss.SSS}D', '%f' ... and 87 more}\r\n ReadSize: 20000 rows\r\n OutputType: 'table'\r\n RowTimes: []\r\n\r\n Write-specific Properties:\r\n SupportedOutputFormats: [\"txt\" \"csv\" \"xlsx\" \"xls\" \"parquet\" \"parq\"]\r\n DefaultOutputFormat: \"txt\"<\/div><\/div><\/div><\/div><\/div>
tds = tall(ds)<\/span><\/span><\/div>
tds =\r\n\r\n M\u00d790 tall table\r\n\r\n TripNo<\/strong> Time<\/strong> ABRK<\/strong> ACMT<\/strong> AIL_1<\/strong> AIL_2<\/strong> ALTS<\/strong> APFD<\/strong> ATEN<\/strong> A_T<\/strong> BLV<\/strong> BPGR_1<\/strong> BPGR_2<\/strong> BPYR_1<\/strong> BPYR_2<\/strong> CALT<\/strong> CASS<\/strong> CRSS<\/strong> DFGS<\/strong> DWPT<\/strong> EAI<\/strong> ELEV_1<\/strong> ELEV_2<\/strong> EVNT<\/strong> FADF<\/strong> FADS<\/strong> FGC3<\/strong> FIRE_1<\/strong> FIRE_2<\/strong> FIRE_3<\/strong> FIRE_4<\/strong> FLAP<\/strong> FQTY_1<\/strong> FQTY_2<\/strong> FQTY_3<\/strong> FQTY_4<\/strong> GLS<\/strong> GPWS<\/strong> HDGS<\/strong> HF1<\/strong> HF2<\/strong> HYDG<\/strong> HYDY<\/strong> ILSF<\/strong> LATP<\/strong> LGDN<\/strong> LGUP<\/strong> LMOD<\/strong> LOC<\/strong> LONP<\/strong> MNS<\/strong> MRK<\/strong> MW<\/strong> N1CO<\/strong> OIPL<\/strong> OIP_1<\/strong> OIP_2<\/strong> OIP_3<\/strong> OIP_4<\/strong> OIT_1<\/strong> OIT_2<\/strong> OIT_3<\/strong> OIT_4<\/strong> PACK<\/strong> PH<\/strong> POVT<\/strong> PTRM<\/strong> PUSH<\/strong> SAT<\/strong> SMKB<\/strong> SMOK<\/strong> SNAP<\/strong> SPLG<\/strong> SPLY<\/strong> SPL_1<\/strong> SPL_2<\/strong> TAI<\/strong> TAT<\/strong> TCAS<\/strong> TMAG<\/strong> TMODE<\/strong> VHF1<\/strong> VHF2<\/strong> VHF3<\/strong> VMODE<\/strong> VSPS<\/strong> WAI_1<\/strong> WAI_2<\/strong> WOW<\/strong> WSHR<\/strong>\r\n _________<\/strong> ________________________<\/strong> ______<\/strong> ____<\/strong> ______<\/strong> ______<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> ___<\/strong> ___<\/strong> ______<\/strong> ______<\/strong> ______<\/strong> ______<\/strong> ____<\/strong> ____<\/strong> ______<\/strong> ____<\/strong> __________<\/strong> ___<\/strong> ______<\/strong> ______<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> ______<\/strong> ______<\/strong> ______<\/strong> ______<\/strong> ____<\/strong> ______<\/strong> ______<\/strong> ______<\/strong> ______<\/strong> ________<\/strong> ____<\/strong> ______<\/strong> ___<\/strong> ___<\/strong> ____<\/strong> ____<\/strong> __________<\/strong> ______<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> ________<\/strong> _______<\/strong> ___<\/strong> ___<\/strong> __<\/strong> ____<\/strong> ____<\/strong> _____<\/strong> _____<\/strong> _____<\/strong> _____<\/strong> ______<\/strong> ______<\/strong> ______<\/strong> ______<\/strong> ____<\/strong> __<\/strong> ____<\/strong> ______<\/strong> ____<\/strong> ___<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> ______<\/strong> ______<\/strong> ___<\/strong> _____<\/strong> ____<\/strong> ____<\/strong> _____<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> _____<\/strong> ____<\/strong> _____<\/strong> _____<\/strong> ___<\/strong> ____<\/strong>\r\n\r\n 6.602e+14 10-Apr-2001 07:43:24.000 119.98 59 72.239 73.671 7000 2 0 1 0 0 24.414 24.414 0 0 134 118.92 1 2.0132e+05 0 19.208 64.179 1 15 15 120 0 0 0 0 98 8112 0 0 7952 0.03861 1 118.92 1 1 0 0 2.1577e+06 44.882 0 1 12 -0.35064 -93.203 204 7 0 1 0 0 0 0 0 27.704 26.361 27.704 27.704 2 1 0 3.1354 1 19 1 0 1 1 1 72.439 72.65 0 19.75 0 1 2 1 1 1 11 0 0 0 0 1 \r\n 6.602e+14 10-Apr-2001 07:43:25.000 119.98 60 72.239 74.142 7000 2 0 1 0 0 24.414 24.414 0 0 134 118.92 1 2.0132e+05 0 19.188 64.179 1 15 15 120 0 0 0 0 98 8112 0 0 7952 -0.01053 1 118.92 1 1 0 0 2.1577e+06 44.882 0 1 12 -0.32301 -93.203 204 7 0 1 0 0 0 0 0 27.704 26.361 27.704 27.704 2 1 0 3.074 1 19 1 0 1 1 1 72.439 72.65 0 19.75 0 1 2 1 1 1 11 0 0 0 0 1 \r\n 6.602e+14 10-Apr-2001 07:43:26.000 119.98 59 72.362 74.285 7000 2 0 1 0 0 24.414 24.414 0 0 134 118.92 1 2.0132e+05 0 19.188 64.199 1 15 15 120 0 0 0 0 98 8112 0 0 7952 0.05772 1 118.92 1 1 0 0 2.1577e+06 44.882 0 1 12 -0.3677 -93.203 204 7 0 1 0 0 0 0 0 27.704 26.361 26.361 27.704 2 1 0 3.0536 1 19 1 0 1 1 1 72.429 72.65 0 19.75 0 1 2 1 1 1 11 0 0 0 0 1 \r\n 6.602e+14 10-Apr-2001 07:43:27.000 119.98 59 72.423 74.367 7000 2 0 1 0 0 24.414 24.414 0 0 134 118.92 1 2.0132e+05 0 19.208 64.179 1 15 15 120 0 0 0 0 99 8112 0 0 7952 0.0078 1 118.92 1 1 0 0 2.1577e+06 44.882 0 1 12 -0.32948 -93.203 204 7 0 1 0 0 0 0 0 27.704 26.361 26.361 27.704 2 1 0 3.0536 1 19 1 0 1 1 1 72.429 72.65 0 19.75 0 1 2 1 1 1 11 0 0 0 0 1 \r\n 6.602e+14 10-Apr-2001 07:43:28.000 119.98 60 72.444 74.326 7000 2 0 1 0 0 24.414 24.414 0 0 134 118.92 1 2.0132e+05 0 19.208 64.179 1 15 15 120 0 0 0 0 98 8112 0 0 7952 0.02301 1 118.92 1 1 0 0 2.1577e+06 44.882 0 1 12 -0.39396 -93.203 204 7 0 1 0 0 0 0 0 27.704 26.361 27.704 27.704 2 1 0 3.074 1 19 1 0 1 1 1 72.439 72.65 0 19.75 0 1 2 1 1 1 11 0 0 0 0 1 \r\n 6.602e+14 10-Apr-2001 07:43:29.000 119.98 59 72.403 74.305 7000 2 0 1 0 0 24.414 24.414 0 0 134 118.92 1 2.0132e+05 0 19.188 64.22 1 15 15 120 0 0 0 0 98 8104 0 0 7952 -0.13689 1 118.92 1 1 0 0 2.1577e+06 44.882 0 1 12 -0.34672 -93.203 204 7 0 1 0 0 0 0 0 27.704 26.361 27.704 27.704 0 1 0 3.0536 1 19 1 0 1 1 1 72.439 72.65 0 19.5 0 1 2 1 1 1 11 0 0 0 0 1 \r\n 6.602e+14 10-Apr-2001 07:43:30.000 119.98 60 72.403 74.326 7000 2 0 1 0 0 24.414 24.414 0 0 134 118.92 1 2.0132e+05 0 19.167 64.261 1 15 15 120 0 0 0 0 98 8112 0 0 7952 0.1794 1 118.92 1 1 0 0 2.1577e+06 44.882 0 1 12 -0.38416 -93.203 204 7 0 1 0 0 0 0 0 27.704 27.704 27.704 27.704 0 1 0 3.074 1 19 1 0 1 1 1 72.439 72.65 0 19.75 0 1 2 1 1 1 11 0 0 0 0 1 \r\n 6.602e+14 10-Apr-2001 07:43:31.000 119.98 59 72.239 74.224 7000 2 0 1 0 0 24.414 19.531 0 0 134 118.92 1 2.0132e+05 0 19.208 64.179 1 15 15 120 0 0 0 0 98 8112 0 0 7952 0.02769 1 118.92 1 1 0 0 2.1577e+06 44.882 0 1 12 -0.36632 -93.203 204 7 0 1 0 0 0 0 0 27.704 26.361 27.704 27.704 0 1 0 3.0536 1 19 1 0 1 1 1 72.439 72.641 0 19.75 0 1 2 1 1 1 11 0 0 0 0 1 \r\n : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : :\r\n : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : :<\/div><\/div><\/div><\/div><\/div>
Let's try our 3 read strategies next.<\/span><\/div>

CSV Read Strategy 1 - Read the entire data into memory<\/span><\/h3>
[flightsOverMA, csvTime1] = findFlightsAllData(tds, MACoordinates);<\/span><\/span><\/div>
Evaluating tall expression using the Local MATLAB Session:\r\n- Pass 1 of 1: Completed in 9 min 42 sec\r\nEvaluation completed in 9 min 42 sec<\/div><\/div><\/div><\/div>
head(flightsOverMA)<\/span><\/span><\/div>
TripNo<\/strong> LATP<\/strong> LONP<\/strong> \r\n _________<\/strong> ______<\/strong> _______<\/strong>\r\n\r\n 6.602e+14 42.032 -73.323\r\n 6.602e+14 42.033 -73.323\r\n 6.602e+14 42.035 -73.323\r\n 6.602e+14 42.037 -73.323\r\n 6.602e+14 42.039 -73.323\r\n 6.602e+14 42.04 -73.323\r\n 6.602e+14 42.042 -73.323\r\n 6.602e+14 42.044 -73.323<\/div><\/div><\/div><\/div>
height(flightsOverMA)<\/span><\/span><\/div>
ans = 301<\/div><\/div><\/div>
csvTime1<\/span><\/span><\/div>
csvTime1 = 582.7319<\/div><\/div><\/div><\/div>

CSV Read Strategy 2 -<\/span> <\/span>Read all columns for matching latitudes and longitudes<\/span><\/h3>
[flightsOverMA, csvTime2] = findFlightsFilteredRowsAllColumns(tds, MACoordinates);<\/span><\/span><\/div>
Evaluating tall expression using the Local MATLAB Session:\r\n- Pass 1 of 1: Completed in 8 min 33 sec\r\nEvaluation completed in 8 min 33 sec<\/div><\/div><\/div><\/div>
height(flightsOverMA)<\/span><\/span><\/div>
ans = 301<\/div><\/div><\/div>
csvTime2<\/span><\/span><\/div>
csvTime2 = 514.1776<\/div><\/div><\/div><\/div>

CSV Read Strategy 3<\/span> - <\/span>Read only relevant columns for matching latitudes and longitudes<\/span><\/h3>
[flightsOverMA, csvTime3] = findFlightsFilteredRowsAndColumns(tds,MACoordinates);<\/span><\/span><\/div>
Evaluating tall expression using the Local MATLAB Session:\r\n- Pass 1 of 1: Completed in 4 min 3 sec\r\nEvaluation completed in 4 min 3 sec<\/div><\/div><\/div><\/div>
height(flightsOverMA)<\/span><\/span><\/div>
ans = 301<\/div><\/div><\/div>
csvTime3<\/span><\/span><\/div>
csvTime3 = 242.9099<\/div><\/div><\/div><\/div>
We were able to find the 301 rows with Massachusetts coordinates with each reading strategy but it can take up to 10 min to read the csv dataset and perform this read. <\/span><\/div>

Parquet<\/span><\/h3>
Analyzing this data directly from csv files was painstakingly slow. Let's explore how using Parquet files instead can make a significant difference in our analysis time. First, we will rewrite the data into Parquet files. There are different ways to write Parquet files in MATLAB. We can use the <\/span>writeall<\/span><\/a> method of datastore to write all the data that are being read into new file types. However, this will write a Parquet file for each individual csv file. Parquet files of such small sizes are not going to be very helpful. Instead we would like to coalesce the flight information into larger Parquet files that can still be read efficiently into MATLAB. This can be done using the <\/span>write<\/span><\/a> method for tall and using parquetwrite as a custom write function. <\/span><\/div>
write(<\/span>\"data\/parquet\/\"<\/span>, tds, <\/span>\"WriteFcn\"<\/span>, @dataWriter);<\/span><\/span><\/div>
function <\/span>dataWriter(info, data )<\/span><\/span><\/div>
filename = info.SuggestedFilename;<\/span><\/span><\/div>
parquetwrite(filename, data)<\/span><\/span><\/div>
end<\/span><\/span><\/div><\/div>
d = dir(<\/span>\"data\/parquet\/\"<\/span>);<\/span><\/span><\/div><\/div>
struct2table(d)<\/span><\/span><\/div>
ans = <\/span>14\u00d76 table <\/span><\/div>
 <\/span><\/th>name<\/span><\/th>folder<\/span><\/th>date<\/span><\/th>bytes<\/span><\/th>isdir<\/span><\/th>datenum<\/span><\/th><\/tr><\/thead>
1<\/span><\/th>'.'<\/span><\/td>'C:\\Blogs\\Introduction to Parquet\\data\\parquet'<\/span><\/td>'09-Apr-2023 13:00:15'<\/span><\/td>0<\/span><\/td>1<\/span><\/td>7.3899e+05<\/span><\/td><\/tr>
2<\/span><\/th>'..'<\/span><\/td>'C:\\Blogs\\Introduction to Parquet\\data\\parquet'<\/span><\/td>'09-Apr-2023 12:37:14'<\/span><\/td>0<\/span><\/td>1<\/span><\/td>7.3899e+05<\/span><\/td><\/tr>
3<\/span><\/th>'data_1_000001.parquet'<\/span><\/td>'C:\\Blogs\\Introduction to Parquet\\data\\parquet'<\/span><\/td>'09-Apr-2023 12:46:28'<\/span><\/td>63133595<\/span><\/td>0<\/span><\/td>7.3899e+05<\/span><\/td><\/tr>
4<\/span><\/th>'data_1_000002.parquet'<\/span><\/td>'C:\\Blogs\\Introduction to Parquet\\data\\parquet'<\/span><\/td>'09-Apr-2023 12:48:08'<\/span><\/td>64111087<\/span><\/td>0<\/span><\/td>7.3899e+05<\/span><\/td><\/tr>
5<\/span><\/th>'data_1_000003.parquet'<\/span><\/td>'C:\\Blogs\\Introduction to Parquet\\data\\parquet'<\/span><\/td>'09-Apr-2023 12:49:38'<\/span><\/td>64799996<\/span><\/td>0<\/span><\/td>7.3899e+05<\/span><\/td><\/tr>
6<\/span><\/th>'data_1_000004.parquet'<\/span><\/td>'C:\\Blogs\\Introduction to Parquet\\data\\parquet'<\/span><\/td>'09-Apr-2023 12:51:02'<\/span><\/td>64212887<\/span><\/td>0<\/span><\/td>7.3899e+05<\/span><\/td><\/tr>
7<\/span><\/th>'data_1_000005.parquet'<\/span><\/td>'C:\\Blogs\\Introduction to Parquet\\data\\parquet'<\/span><\/td>'09-Apr-2023 12:52:23'<\/span><\/td>62082870<\/span><\/td>0<\/span><\/td>7.3899e+05<\/span><\/td><\/tr>
8<\/span><\/th>'data_1_000006.parquet'<\/span><\/td>'C:\\Blogs\\Introduction to Parquet\\data\\parquet'<\/span><\/td>'09-Apr-2023 12:53:22'<\/span><\/td>45843227<\/span><\/td>0<\/span><\/td>7.3899e+05<\/span><\/td><\/tr>
9<\/span><\/th>'data_2_000001.parquet'<\/span><\/td>'C:\\Blogs\\Introduction to Parquet\\data\\parquet'<\/span><\/td>'09-Apr-2023 12:54:35'<\/span><\/td>63200246<\/span><\/td>0<\/span><\/td>7.3899e+05<\/span><\/td><\/tr>
10<\/span><\/th>'data_2_000002.parquet'<\/span><\/td>'C:\\Blogs\\Introduction to Parquet\\data\\parquet'<\/span><\/td>'09-Apr-2023 12:55:49'<\/span><\/td>63200250<\/span><\/td>0<\/span><\/td>7.3899e+05<\/span><\/td><\/tr>
11<\/span><\/th>'data_2_000003.parquet'<\/span><\/td>'C:\\Blogs\\Introduction to Parquet\\data\\parquet'<\/span><\/td>'09-Apr-2023 12:57:03'<\/span><\/td>64229179<\/span><\/td>0<\/span><\/td>7.3899e+05<\/span><\/td><\/tr>
12<\/span><\/th>'data_2_000004.parquet'<\/span><\/td>'C:\\Blogs\\Introduction to Parquet\\data\\parquet'<\/span><\/td>'09-Apr-2023 12:58:18'<\/span><\/td>62165017<\/span><\/td>0<\/span><\/td>7.3899e+05<\/span><\/td><\/tr>
13<\/span><\/th>'data_2_000005.parquet'<\/span><\/td>'C:\\Blogs\\Introduction to Parquet\\data\\parquet'<\/span><\/td>'09-Apr-2023 12:59:31'<\/span><\/td>61241659<\/span><\/td>0<\/span><\/td>7.3899e+05<\/span><\/td><\/tr>
14<\/span><\/th>'data_2_000006.parquet'<\/span><\/td>'C:\\Blogs\\Introduction to Parquet\\data\\parquet'<\/span><\/td>'09-Apr-2023 13:00:19'<\/span><\/td>40526588<\/span><\/td>0<\/span><\/td>7.3899e+05<\/span><\/td><\/tr><\/tbody><\/table><\/div><\/div>
<\/div>
<\/div>
<\/div><\/div><\/div><\/div><\/div>
When written this way, tall coalesced the entire dataset into 12 parquet files with a single rowgroup in each file. Let's now run our tests on the parquet files. <\/span><\/div>
Just like we used <\/span>tabularTextDatastore<\/span><\/a> to read the csv files, we can use <\/span>parquetDatastore<\/span><\/a> to read it from the Parquet files and then create the tall table.<\/span><\/div>
ds = parquetDatastore(<\/span>\"data\\parquet\\*.parquet\"<\/span>)<\/span><\/span><\/div>
ds = <\/span><\/div>
ParquetDatastore with properties:\r\n\r\n Files: {\r\n 'C:\\Blogs\\Introduction to Parquet\\data\\parquet\\data_1_000001.parquet';\r\n 'C:\\Blogs\\Introduction to Parquet\\data\\parquet\\data_1_000002.parquet';\r\n 'C:\\Blogs\\Introduction to Parquet\\data\\parquet\\data_1_000003.parquet'\r\n ... and 9 more\r\n }\r\n Folders: {\r\n 'C:\\Blogs\\Introduction to Parquet\\data\\parquet'\r\n }\r\n AlternateFileSystemRoots: {}\r\n ReadSize: 'rowgroup'\r\n\r\n Properties that control the table returned by preview, read, and readall:\r\n VariableNames: {1\u00d790 cell}\r\n SelectedVariableNames: {1\u00d790 cell}\r\n VariableNamingRule: 'modify'\r\n OutputType: 'table'\r\n RowTimes: []\r\n RowFilter: <unconstrained>\r\n\r\n Properties that control output format when writing:\r\n SupportedOutputFormats: [\"txt\" \"csv\" \"xlsx\" \"xls\" \"parquet\" \"parq\"]\r\n DefaultOutputFormat: \"parquet\"\r\n<\/div><\/div><\/div><\/div><\/div>
tds = tall(ds)<\/span><\/span><\/div>
tds =\r\n\r\n M\u00d790 tall table\r\n\r\n TripNo<\/strong> Time<\/strong> ABRK<\/strong> ACMT<\/strong> AIL_1<\/strong> AIL_2<\/strong> ALTS<\/strong> APFD<\/strong> ATEN<\/strong> A_T<\/strong> BLV<\/strong> BPGR_1<\/strong> BPGR_2<\/strong> BPYR_1<\/strong> BPYR_2<\/strong> CALT<\/strong> CASS<\/strong> CRSS<\/strong> DFGS<\/strong> DWPT<\/strong> EAI<\/strong> ELEV_1<\/strong> ELEV_2<\/strong> EVNT<\/strong> FADF<\/strong> FADS<\/strong> FGC3<\/strong> FIRE_1<\/strong> FIRE_2<\/strong> FIRE_3<\/strong> FIRE_4<\/strong> FLAP<\/strong> FQTY_1<\/strong> FQTY_2<\/strong> FQTY_3<\/strong> FQTY_4<\/strong> GLS<\/strong> GPWS<\/strong> HDGS<\/strong> HF1<\/strong> HF2<\/strong> HYDG<\/strong> HYDY<\/strong> ILSF<\/strong> LATP<\/strong> LGDN<\/strong> LGUP<\/strong> LMOD<\/strong> LOC<\/strong> LONP<\/strong> MNS<\/strong> MRK<\/strong> MW<\/strong> N1CO<\/strong> OIPL<\/strong> OIP_1<\/strong> OIP_2<\/strong> OIP_3<\/strong> OIP_4<\/strong> OIT_1<\/strong> OIT_2<\/strong> OIT_3<\/strong> OIT_4<\/strong> PACK<\/strong> PH<\/strong> POVT<\/strong> PTRM<\/strong> PUSH<\/strong> SAT<\/strong> SMKB<\/strong> SMOK<\/strong> SNAP<\/strong> SPLG<\/strong> SPLY<\/strong> SPL_1<\/strong> SPL_2<\/strong> TAI<\/strong> TAT<\/strong> TCAS<\/strong> TMAG<\/strong> TMODE<\/strong> VHF1<\/strong> VHF2<\/strong> VHF3<\/strong> VMODE<\/strong> VSPS<\/strong> WAI_1<\/strong> WAI_2<\/strong> WOW<\/strong> WSHR<\/strong>\r\n _________<\/strong> ____________________<\/strong> ______<\/strong> ____<\/strong> ______<\/strong> ______<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> ___<\/strong> ___<\/strong> ______<\/strong> ______<\/strong> ______<\/strong> ______<\/strong> ____<\/strong> ____<\/strong> ______<\/strong> ____<\/strong> __________<\/strong> ___<\/strong> ______<\/strong> ______<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> ______<\/strong> ______<\/strong> ______<\/strong> ______<\/strong> ____<\/strong> ______<\/strong> ______<\/strong> ______<\/strong> ______<\/strong> ________<\/strong> ____<\/strong> ______<\/strong> ___<\/strong> ___<\/strong> ____<\/strong> ____<\/strong> __________<\/strong> ______<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> ________<\/strong> _______<\/strong> ___<\/strong> ___<\/strong> __<\/strong> ____<\/strong> ____<\/strong> _____<\/strong> _____<\/strong> _____<\/strong> _____<\/strong> ______<\/strong> ______<\/strong> ______<\/strong> ______<\/strong> ____<\/strong> __<\/strong> ____<\/strong> ______<\/strong> ____<\/strong> ___<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> ______<\/strong> ______<\/strong> ___<\/strong> _____<\/strong> ____<\/strong> ____<\/strong> _____<\/strong> ____<\/strong> ____<\/strong> ____<\/strong> _____<\/strong> ____<\/strong> _____<\/strong> _____<\/strong> ___<\/strong> ____<\/strong>\r\n\r\n 6.602e+14 10-Apr-2001 07:43:24 119.98 59 72.239 73.671 7000 2 0 1 0 0 24.414 24.414 0 0 134 118.92 1 2.0132e+05 0 19.208 64.179 1 15 15 120 0 0 0 0 98 8112 0 0 7952 0.03861 1 118.92 1 1 0 0 2.1577e+06 44.882 0 1 12 -0.35064 -93.203 204 7 0 1 0 0 0 0 0 27.704 26.361 27.704 27.704 2 1 0 3.1354 1 19 1 0 1 1 1 72.439 72.65 0 19.75 0 1 2 1 1 1 11 0 0 0 0 1 \r\n 6.602e+14 10-Apr-2001 07:43:25 119.98 60 72.239 74.142 7000 2 0 1 0 0 24.414 24.414 0 0 134 118.92 1 2.0132e+05 0 19.188 64.179 1 15 15 120 0 0 0 0 98 8112 0 0 7952 -0.01053 1 118.92 1 1 0 0 2.1577e+06 44.882 0 1 12 -0.32301 -93.203 204 7 0 1 0 0 0 0 0 27.704 26.361 27.704 27.704 2 1 0 3.074 1 19 1 0 1 1 1 72.439 72.65 0 19.75 0 1 2 1 1 1 11 0 0 0 0 1 \r\n 6.602e+14 10-Apr-2001 07:43:26 119.98 59 72.362 74.285 7000 2 0 1 0 0 24.414 24.414 0 0 134 118.92 1 2.0132e+05 0 19.188 64.199 1 15 15 120 0 0 0 0 98 8112 0 0 7952 0.05772 1 118.92 1 1 0 0 2.1577e+06 44.882 0 1 12 -0.3677 -93.203 204 7 0 1 0 0 0 0 0 27.704 26.361 26.361 27.704 2 1 0 3.0536 1 19 1 0 1 1 1 72.429 72.65 0 19.75 0 1 2 1 1 1 11 0 0 0 0 1 \r\n 6.602e+14 10-Apr-2001 07:43:27 119.98 59 72.423 74.367 7000 2 0 1 0 0 24.414 24.414 0 0 134 118.92 1 2.0132e+05 0 19.208 64.179 1 15 15 120 0 0 0 0 99 8112 0 0 7952 0.0078 1 118.92 1 1 0 0 2.1577e+06 44.882 0 1 12 -0.32948 -93.203 204 7 0 1 0 0 0 0 0 27.704 26.361 26.361 27.704 2 1 0 3.0536 1 19 1 0 1 1 1 72.429 72.65 0 19.75 0 1 2 1 1 1 11 0 0 0 0 1 \r\n 6.602e+14 10-Apr-2001 07:43:28 119.98 60 72.444 74.326 7000 2 0 1 0 0 24.414 24.414 0 0 134 118.92 1 2.0132e+05 0 19.208 64.179 1 15 15 120 0 0 0 0 98 8112 0 0 7952 0.02301 1 118.92 1 1 0 0 2.1577e+06 44.882 0 1 12 -0.39396 -93.203 204 7 0 1 0 0 0 0 0 27.704 26.361 27.704 27.704 2 1 0 3.074 1 19 1 0 1 1 1 72.439 72.65 0 19.75 0 1 2 1 1 1 11 0 0 0 0 1 \r\n 6.602e+14 10-Apr-2001 07:43:29 119.98 59 72.403 74.305 7000 2 0 1 0 0 24.414 24.414 0 0 134 118.92 1 2.0132e+05 0 19.188 64.22 1 15 15 120 0 0 0 0 98 8104 0 0 7952 -0.13689 1 118.92 1 1 0 0 2.1577e+06 44.882 0 1 12 -0.34672 -93.203 204 7 0 1 0 0 0 0 0 27.704 26.361 27.704 27.704 0 1 0 3.0536 1 19 1 0 1 1 1 72.439 72.65 0 19.5 0 1 2 1 1 1 11 0 0 0 0 1 \r\n 6.602e+14 10-Apr-2001 07:43:30 119.98 60 72.403 74.326 7000 2 0 1 0 0 24.414 24.414 0 0 134 118.92 1 2.0132e+05 0 19.167 64.261 1 15 15 120 0 0 0 0 98 8112 0 0 7952 0.1794 1 118.92 1 1 0 0 2.1577e+06 44.882 0 1 12 -0.38416 -93.203 204 7 0 1 0 0 0 0 0 27.704 27.704 27.704 27.704 0 1 0 3.074 1 19 1 0 1 1 1 72.439 72.65 0 19.75 0 1 2 1 1 1 11 0 0 0 0 1 \r\n 6.602e+14 10-Apr-2001 07:43:31 119.98 59 72.239 74.224 7000 2 0 1 0 0 24.414 19.531 0 0 134 118.92 1 2.0132e+05 0 19.208 64.179 1 15 15 120 0 0 0 0 98 8112 0 0 7952 0.02769 1 118.92 1 1 0 0 2.1577e+06 44.882 0 1 12 -0.36632 -93.203 204 7 0 1 0 0 0 0 0 27.704 26.361 27.704 27.704 0 1 0 3.0536 1 19 1 0 1 1 1 72.439 72.641 0 19.75 0 1 2 1 1 1 11 0 0 0 0 1 \r\n : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : :\r\n : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : :<\/div><\/div><\/div><\/div><\/div>
Now let's try the same read strategies on Parquet files.<\/span><\/div>

Parquet Read Strategy 1 - Read the entire data into memory<\/span><\/h3>
[flightsOverMA, parquetTime1] = findFlightsAllData(tds, MACoordinates);<\/span><\/span><\/div>
Evaluating tall expression using the Local MATLAB Session:\r\n- Pass 1 of 1: Completed in 46 sec\r\nEvaluation completed in 46 sec<\/div><\/div><\/div><\/div>
head(flightsOverMA)<\/span><\/span><\/div>
TripNo<\/strong> LATP<\/strong> LONP<\/strong> \r\n _________<\/strong> ______<\/strong> _______<\/strong>\r\n\r\n 6.602e+14 42.032 -73.323\r\n 6.602e+14 42.033 -73.323\r\n 6.602e+14 42.035 -73.323\r\n 6.602e+14 42.037 -73.323\r\n 6.602e+14 42.039 -73.323\r\n 6.602e+14 42.04 -73.323\r\n 6.602e+14 42.042 -73.323\r\n 6.602e+14 42.044 -73.323<\/div><\/div><\/div><\/div>
height(flightsOverMA)<\/span><\/span><\/div>
ans = 301<\/div><\/div><\/div>
parquetTime1<\/span><\/span><\/div>
parquetTime1 = 48.3910<\/div><\/div><\/div><\/div>

Parquet Read Strategy 2 -<\/span> <\/span>Read all columns for matching latitudes and longitudes<\/span><\/h3>
This where parquet's predicate pushdown comes into action. When we index into the tall array using the latitude and longitude values, tall uses that information to create the corresponding rowfilter and then reads from the parquet files. <\/span><\/div>
[flightsOverMA, parquetTime2] = findFlightsFilteredRowsAllColumns(tds, MACoordinates);<\/span><\/span><\/div>
Evaluating tall expression using the Local MATLAB Session:\r\n- Pass 1 of 1: Completed in 15 sec\r\nEvaluation completed in 15 sec<\/div><\/div><\/div><\/div>
height(flightsOverMA)<\/span><\/span><\/div>
ans = 301<\/div><\/div><\/div>
parquetTime2<\/span><\/span><\/div>
parquetTime2 = 18.3050<\/div><\/div><\/div><\/div>

Parquet Read Strategy 3<\/span> - <\/span>Read only relevant columns for matching latitudes and longitudes<\/span><\/h3>
Now, in addition to predicate pushdown, let's just read the relevant columns.<\/span><\/div>
[flightsOverMA, parquetTime3] = findFlightsFilteredRowsAndColumns(tds,MACoordinates);<\/span><\/span><\/div>
Evaluating tall expression using the Local MATLAB Session:\r\n- Pass 1 of 1: Completed in 1.1 sec\r\nEvaluation completed in 1.1 sec<\/div><\/div><\/div><\/div>
height(flightsOverMA)<\/span><\/span><\/div>
ans = 301<\/div><\/div><\/div>
parquetTime3<\/span><\/span><\/div>
parquetTime3 = 1.5900<\/div><\/div><\/div><\/div>

Results<\/span><\/h2>
Like the csv reads, we were able to find the 301 rows with Massachusetts coordinates from the Parquet files, but the results were much faster. Let's review what we have achieved here by working with Parquet files instead of csv. <\/span><\/div>
plotResults([csvTime1, parquetTime1; csvTime2, parquetTime2; csvTime3, parquetTime3])<\/span><\/span><\/div>
<\/div>
<\/div>
<\/div>
<\/div><\/div><\/div><\/div><\/div>