Parque Files: The New CSV!
Everything you hate about CSV files, parquet fixes.
Data Types
Column Order*
Performance improvements just reading from the file since it's in columnar format.
Compression for lots of space savings.
Can be read while compressed. (CSVs inside ZIP files cannot)
Two things you like about CSV files, parquet files screw up.
Because of the meta data overhead to define column types and compressions, very small data sets, less than around 100 rows, will be slightly larger. We're talking a CSV of 3 KB might be 4 KB. Ouch.
You cannot edit a parquet file in a text editor, so it has to be constructed, not just ad-hoc.
My journey with parquet files started with a project to export data from an on-prem SQL Server to Azure Synapse, using an Azure Data Lake as the staging area.
We did this with Azure Data Factory.
Using the Copy Data activity from Data Factory, we first tested with a CSV.
This was one file output, and the data stream was high and constant.
We switched to parquet, and the data stream changed to bursts. While it was gathering and compressing data, nothing was going on the wire. Then it would send data. Then over and over. We didn't use multiple files since these were only for transferring data, not for reading.
We no longer had to worry about how NULL values or datetimes were written or read. Or the big one, we didn't have to worry about column delimiters and test fields that had quotation marks inside.
This brings us to the first component to work with parquet files.
Azure Data Factory Copy activity.
This can read and write parquet files.
I've only worked with parquet files in Azure, and so far Azure components make it easy to ready all files inside a folder instead of having to iterate manually through each file. The source for the Copy activity can easily read all files in a folder, or through sub folders.
Column order nor data type definitions have to be defined.
On the output / sink side, you can let the columns flow through automatically in the order of the first source file. Or you can define the order. You can define partitioning of files, if you'd like, and also merge files. Reading parquet files is dynamic, so it can handle changing a source files changing order and even dropping columns as long as the column is not specifically referenced.
The next step in our process is to import from the parquet file in the Azure Data Lake into an Azure Synapse dedicated sql pool.
Here Microsoft's SQL is a little annoying with parquet files. The way that was chosen to for Synapse dedicated SQL to read the parquet files was to use an external table defintion. This requires defining the data type and order of the columns on the receiving code, and requires the parquet file to always have that order of columns. This choice has been a real bummer and makes using parquet files nearly as bad as using CSV files.
This is the second componet for working with parquet files.
Synapse dedicated SQL external table.
This requires defining the data type and order of the columns on the receiving code, and requires the parquet file to always have that order of columns. This is only for reading.
Let's go back a little to the Azure Data Factory.
Sometimes / many times the Data Factory Copy Data activity is too simple for what you need. If you need to do any manipulation of data, you'll probably use a Data Factory Data Flow. This uses a Spark-cluster and runs Java. This is imporant because datetimes have a different format.
This is the third component for working with parquet files.
With a Data Flow, you can choose a specific order to output columns, and you can change the data types with a Data Flow. This is allows you to parition files as you see fit. Reading parquet files is dynamic, so it can handle changing a source files changing order and even dropping columns as long as the column is not specifically referenced.
This is great for processing parquet files. But what about just looking at the data in a parquet file?
The Synapse dedicated SQL is a pain for something quick.
The Data Factory Copy Data activity and Data Flow activity can give you a sample of the top 1000 rows.
My current way, since I do SQL Server, is to use the Synapse on-demand / serverless SQL pool.
Now the fourth component for using parquet files.
Synapse on-demand / serverless SQL pool OPENROWSET
This is a SQL instance that is somewhere between a SQL Server and a Synapse dedicated SQL pool, but it does not store any data. Why all the complexity? I don't know.
In the on-demand / serverless SQL pool, the OPENROWSET command can read parquet files. The dedicated SQL pool cannot. Don't ask me why.
With this, we can query a parquet file just like a table. We can do SELECT * and get back all the columns, or provide a list of columns in a specific order to view. We can do WHERE clauses get specific data back. This one command will read all parquet files in a folder and subfolder, if so defined. This is quick and easy to run, since it does not require a machine with enough memory to importing into a Python data frame. The parquet file is read directly. You just need the path to the file(s).
Let's talk about writing a parquet file with the fifth component.
Synapse, both dedicated and serverless, SQL pools Create External Table As Select (aka CETAS).
The AS SELECT part is a normal select query where you define the data to output. It can be from anywhere and it can be whatever you can fit into a select statement.
To get started writing, in both, you must create both a database object with the file format (just that it is parquet, not the column layout) and a database object with the base folder to write data to.
Now the tricky part. You cannot name the files. The files output will have a guid-like name, and will all be in the folder you specify.
Behind the scenes, Synapse is a cluster of many nodes running in parallel. When the SELECT statement runs, it might be small and only run on one node, or it might spread out to more nodes. This is decided by the Synapse SQL engine. My assumption is that every node that does processing has its own output file. So you can't know ahead of time how many different files you will get. The system decides how many nodes to use, and thus how many files.
So this way, you can specify the ordering of columns, but not the partitioning of the file. You can always rename it afterwards, but the partitions may not be how you want.
We ran into a problem where we needed to add a column to a file. Setting the definition side was easy, just add the column. During our processing steps, a few steps append other columns to the right as the steps progress along. So the new column wasn't the right-most column. And we had lots of existing old files already, so it was easier to manipulate those files in place to append the extra column instead of rerunning all the extractions. Well, that ended up with two different column layouts for new files versus old files. And so I spent a lot of time trying these components out to rearrange the order of columns of an existing parquet file. Basically, it was like working with CSV files again because of the Synapse dedicated SQL External Table definition.
Some other components that I read about, but didn't get to use during all of this.
Synapse dedicated SQL COPY INTO
This will read a parquet file and create a table based on it. A little overkill when you just need to view a file, since it does put the data into a real table. But this would have saved us many headaches if this had been used instead of the External Table. By default, this will import all columns without you having to specify anything. Or you can selectively choose and order what columns to bring in.
Python
There are Python notebooks in Azure, and you can use Python to read and write parquet files. I didn't get far into reading about them to have any more details.
Azure Storage Explorer
The latest version has an option to preview a parquet file inside the app, but the couple files I tested have not worked. So there are either bugs or not all types of parquet files are currently supported.
Desktop applications
There are applications that will view parquet data. My coworker tried some out. I think the ones he tried cannot edit the data, only view it. So there might be some that will let you edit a parquet files, or some that will take another type of file and convert to parquet. We haven't done an exhaustive search.
So, in conclusion, if you don't work yourself into a boneheaded corner, parquet files are super cool.