If you want to torture data - store it in CSV format

-


Are you using CSV files as a primary file format for your data?

CSV is a very useful file format if you want to open the file in Excel and analyze it right away.

CSV format stores tabular data in plain text, it is old and was wildly used in the early days of business computing.

However, if you plan to keep raw data in the data lake, you should reconsider using CSV. There are many modern file formats that were designed for data analysis.

In the cloud world of data lakes and schema-on-read querying systems, like AWS Glue or Databricks, CSV files will slow you down.


Today I want to talk about Parquet, a modern file format, invented for fast analytical querying.


Parquet files organize data in columns, while CSV files organize data in rows.

Columnar storage allows much better compression so Parquet data files need less storage, 1 TB of CSV files can be converted into 100GB of parquet files – which can be a huge money saver when cloud storage is used. This also means that scanning parquet file is much faster than scanning CSV files – fewer data would be scanned and there is no need to load unneeded columns into memory and aggregations will run faster. Parquet files contain both data and metadata, information about data schema and structure. When you load the file, having metadata helps the querying tool define proper data types.


To test both formats' performance, I will use January 2022 New York Yellow taxi dataset available in both CSV and Parquet online:

https://nyc-tlc.s3.amazonaws.com/trip+data/yellow_tripdata_2022-01.parquet

https://s3.amazonaws.com/nyc-tlc/csv_backup/yellow_tripdata_2022-01.csv

Both files have 2463931 rows. However, the CSV file is 239MB, and the Parquet file is only 36MB.


I will be using the Azure Databricks Spark cluster to analyze the dataset. I want to see the total number of passengers that took a yellow taxi between the 20th and 26th of January. I will run the script twice, first using the parquet file and then using the CSV file.



Querying parquet took 5.19 sec

Querying CSV took 2.28 min

As you see, querying CSV files is slower, requires more IO, and will take much more space on your cloud storage.


Yours,

Maria


"If you torture the data long enough, it will confess."

Comments

Post a Comment

Popular posts from this blog

Unlocking Microsoft Fabric: A Simple Guide when you only have a personal account.

-
Image
" You cannot create a Microsoft capacity using a personal account. Use your organizational account instead." What a disappointment and frustration! I've been trying to set up Microsoft Fabric for a while now and figure out how to work around this error. I use my personal Gmail for an MVP subscription to learn and experiment with Azure services. When I visit app.fabric.microsoft.com and try to use my private Gmail address, I keep getting a similar message: It's frustrating because I'm eager to dive into Fabric and explore its capabilities. I would get the same error trying to set up Azure Data Catalog, PowerBI embedded, and more. What are the different account types and how do they differ? A work or school account is created through Active Directory or other cloud directory services, such as Microsoft 365. On the other hand, a personal account is one that's manually created for individual use, consisting simply of a username and password. After digging into ...
Read more

ETL to ELT journey: Break free your Transformations and discover Happiness and Data Zen

-
Every data integration pipeline consists of 3 stages: Data Extraction (E), Data Transformation (T) and Data Load (L) During the  Data Extraction stage, the source data is read from its origins: transactional databases, CRM or ERP systems or through data scraping from web pages. During the  Data Transformation stage, the necessary modifications are applied to the source data. This includes data filtering, enrichment or merging with existing or other source datasets, data obfuscation, dataset structure alignment or validation, fields renaming and data structuring, according to the canonical data warehouse model.  During the Data Loading stage, the data is stored in the pipeline destination, which could be a staging area, data lake or data warehouse. There are two principal methods for the data integration process: transferring it from where it originated to the destination, where the data will be used for analysis, ETL and ELT. The difference between ETL and ELT pipelines...
Read more

Coding is a rollercoaster of efficiency and eyebrow-raising discoveries.

-
Image
Data Engineers or Developers - many of us love to be gourmet chefs in the kitchen. When it comes to planning and design, we would rather throw all ingredients in the pot and see how it comes out.  Coding without a plan is like assembling a puzzle in a dark room. The result will most probably be unexpected and off the canvas. Whether you follow Waterfall or Agile development strategy, planning and design phases are non-negotiable and are essential to reduce development cycles and redo work. Once upon a time, one data engineer created an amazing piece-of-cake automation pipeline. This masterpiece had very complex logic, pulling data from multiple sources, and merging and persisting the data in a complex, incremental way. When the pipeline started to run successfully and automation flows worked, the data engineer got very excited and considered this development done. A few days later QA engineer found out that the result dataset was never created in the destination. Why has that ...
Read more