Comma Separated Values File Format & Parquet

Image from https://www.pexels.com/@pixabay/
Image from https://www.pexels.com/@pixabay/

There are many discussions and articles about storing data in CSV (Comma-Separated Values) or Parquet file format. Discussions about file size, time to read and write access, etc. I feel that the most important thing is that Parquet stores the column data type information with it. When we use CSV, we lost all this important information.

Let's look at some statistics on the file size, and time to read and write access,

1. Experiment Setup

The file formats are CSV, Parquet, Parquet (compressed with gzip),  and  Parquet (compressed with snappy).

We want to measure the following

  1. The time to write data to the file format
  2. The time to read data from the file format
  3. The file size of the file format

The numbers of rows are 10000, 50000, and 100000.

We use Faker to generate dummy data and pandas (with pyarrow) to read and write data.

The parquet file is NOT multi-parts.

Tests are performed on my laptop (MacbookPro 2.6 GHz 6-Core Intel Core i7)

The source code is provided here.


2. Results

2.1 File Size (in MBytes)

File type10000 rows50000 rows100000 rows
CSV1.0125.06510.124
Parquet1.1215.21710.062
Parquet gzip0.4912.2384.183
Parquet snappy0.7203.3356.326

Observations

  1. CSV and Parquet take up almost the same space.
  2. gzip has a better compression ratio (compared with snappy).
  3. The chart shows that it is linear.


2.2 Time to write the file (in milliseconds)

File type10000 rows50000 rows100000 rows
CSV45.384229.439447.032
Parquet46.87950.24079.409
Parquet gzip74.706321.714597.501
Parquet snappy13.52958.60494.954

Observations

  1. Slow to write CSV and Parquet gzip (especially the latter)
  2. Parquet snappy is good - compressed and writes faster.
  3. The chart shows that it is linear.


2.3 Time to read the file (in milliseconds)

File type 10000 rows 50000 rows 100000 rows
CSV24.762110.409242.348
Parquet28.93054.046145.249
Parquet gzip15.21057.490124.406
Parquet snappy12.29946.559106.755

Observations

  1. Reading small Parquet files is not great. (see 28.930 vs 24.762 milliseconds - reading Parquet is slower than CSV)
  2. Parquet snappy is the winner again.
  3. The chart shows that it is linear.


3. Summary

Depending on your use cases, CSV may not be the best option. As the number of rows and columns increases, Parquet (which is a columnar storage format) is a good option.


Comments

Post a Comment

Popular posts from this blog

Happy New Year, 2024 from DALL-E

Image
generated by DALL-E Wishing everyone a happy new year. Year 2023 is great for me because I learned much about OpenAI. I am involved in a live deployment with it, and involved in a handful of interesting user scenarios around it. On top of this, a copilot hackathon. The image above is generated by DALL-E and following is the code. 1. Dependencies python = "^3.10" pydantic-settings = "^2.1.0" openai = "^1.3.8" 2. Environment Parameters OPENAI_AZURE_ENDPOINT="<your azure openai endpoint>" AZURE_OPENAI_API_KEY="<your azure openai secret>" OPENAI_API_VERSION="2023-12-01-preview" OPENAI_DALLE_MODEL="dalle" Note: OPENAI_API_VERSION has to be 2023-12-01-preview otherwise you will get an exception openai.NotFoundError: Error code: 404 - {'error': {'code': '404', 'message': 'Resource not found'}} dalle  is the name of my dall-e-3 deployment model. You need to change it to
Read more

Bing Search as tool for OpenAI

Image
  Image from https://www.pexels.com/@skitterphoto/ Many times, we need to search for information in the web when we are working with OpenAI. One of the reasons is that the dataset for training for OpenAI is never up to date. For instance, the OpenAI GPT-4 Turbo has knowledge of events up to April 2023. Here is the Python implementation with Pydantic model. Dependencies python = "^3.10" pydantic-settings = "^2.1.0" pydantic = "^2.5.2" azure-cognitiveservices-search-websearch = "^2.0.0" Environment These are the environment parameters needed. Have the following in a .env file. azure_bing_search_endpoint="https://api.bing.microsoft.com" azure_bing_search_key="<key>" We look under "Keys and Endpoint" for these values. Pydantic Setting Model from pydantic_settings import BaseSettings class BingSearchSettings(BaseSettings): azure_bing_search_endpoint: str azure_bing_search_key: str class Config:
Read more

OpenAI: Functions Feature in 2023-07-01-preview API version

Image
  Image from https://www.pexels.com/@pavel-danilyuk/ One of the cool features of OpenAI version 2023-07-01 is its custom Functions feature. This feature is close to me because I am working on a project where we require OpenAI to generate JSON responses. We always want the generated JSON to be consistent, otherwise, we need to do some post-processing tasks to get the JSON object in the correct schema. In the blog, we observe the completion with and without this new feature. Development Setup python -m venv .venv poetry init poetry shell poetry add openai poetry add python-dotenv poetry add pydantic I am using Python version 3.11 Create .env file OPENAI_API_TYPE="azure" OPENAI_API_BASE="<OpenAI endpoint>" OPENAI_API_KEY="<OpenAI secret>" OPENAI_API_VERSION="2023-07-01-preview" OPENAI_DEPLOYMENT_ID="<deployment identifier>" Without OpenAI functions feature We use two sample texts for experimentation. We want to get the na
Read more