Parquet, ORC, and Avro in Data Lakes
Published: 2023-08-03 03:44:38

In the era of big data, data lakes have emerged as a popular storage solution for handling vast amounts of structured and semi-structured data. Data lakes allow organizations to store data in its raw form and later process it for analysis and decision-making. Three prominent file formats used in data lakes are Parquet, ORC (Optimized Row Columnar), and Avro. Each of these formats offers unique advantages and drawbacks that can significantly impact data processing, storage, and analytics. In this blog post, we will explore the advantages and disadvantages of each format and provide insights into choosing the most suitable format for different use cases.

**Parquet**

Parquet is an open-source columnar storage file format developed by the Apache Arrow project. It was created to address the shortcomings of traditional row-based storage formats like CSV¹. Here are the advantages and disadvantages of using Parquet in data lakes:

Advantages of Parquet:

1. Columnar Storage: 
Parquet stores data in a columnar format, which organizes data by columns rather than rows. This allows for highly efficient compression and encoding, reducing storage requirements and enhancing query performance.
2. Compression: 
Parquet supports various compression algorithms such as Snappy, Gzip, and Zstandard, which further reduces storage costs while maintaining query speed.
3. Schema Evolution: 
Parquet files can handle schema evolution gracefully, allowing users to add new columns without modifying the existing data. This makes Parquet a suitable choice for evolving data structures and data versioning.
4. Wide Ecosystem Support: 
Parquet is well-supported across multiple data processing frameworks and platforms, including Apache Spark, Apache Hive, and Apache Impala. It enables seamless integration into existing big data ecosystems.
5. Predicate Pushdown: 
Parquet supports predicate pushdown, a technique where query predicates are pushed down to the storage layer, reducing the amount of data that needs to be scanned during query execution.
_¹Row based storage could be any like CSV, TSV, JSON_

Disadvantages of Parquet:
1. Write Overhead: 
Parquet files have some overhead during the write process due to the columnar storage structure. Writing small amounts of data frequently can result in inefficiencies.
2. Slow Writes for Small Data: 
While Parquet excels in handling large datasets, it may not be the most efficient format for storing small amounts of data due to the overhead associated with columnar storage.
3. Complexity: 
Although Parquet is widely used and supported, the underlying complexity of the format may pose challenges for users who are new to the technology.

![Data storage in Parquet](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3vbpoa634ceug0sqtvp3.png)
<figcaption>Data storage in Parquet</figcaption>


**ORC (Optimized Row Columnar)**

ORC is another open-source columnar storage file format developed by the Apache Hive project. It was designed to improve upon the limitations of older formats like RCFile and is optimized for performance in Hive-based data lakes. Let's examine the advantages and disadvantages of using ORC in data lakes:

Advantages of ORC:

1. High Compression: 
ORC provides exceptional compression rates, reducing storage costs and enhancing overall data lake efficiency.

![File size comparison between parquet and ORC](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jyecj8ldwdzce1own1ed.png)
<figcaption>File size comparison between parquet and ORC</figcaption>


2. Predicate Pushdown: 
Similar to Parquet, ORC also supports predicate pushdown², leading to reduced data scans during query execution and improved query performance.
3. Lightweight Indexing: 
ORC uses lightweight indexes, which enables faster data access and skip-reads during query execution.
4. Enhanced Type Support: 
ORC supports a wide range of data types, including complex types like maps, arrays, and structures. This makes it suitable for handling diverse datasets.
5. Fine-Grained Compression: 
ORC allows users to apply different compression algorithms to individual columns, tailoring storage efficiency to the specific characteristics of each column.
_²A predicate push down filters the data in the database query, reducing the number of entries retrieved from the database and improving query performance. By default the Spark Dataset API will automatically push down valid WHERE clauses to the database_

![Partition Pruning to improve query speeds](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/09bdtwu1ihdoqo7kid4d.png)
<figcaption>Partition Pruning to improve query speeds</figcaption>


Disadvantages of ORC:

1. Limited Ecosystem Support: 
While ORC is supported in Apache Hive, it might not be as widely adopted in other data processing frameworks as Parquet is.
2. Schema Evolution: 
While ORC handles schema evolution, it may not be as flexible as Parquet in this aspect. Modifying the schema of an existing ORC file can be more challenging.
3. Complexity in Implementation: 
Creating custom ORC readers and writers can be more challenging due to the format's complexity, limiting custom data processing workflows.


![ORC File Layout](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vjgfw1vjgro297e6zpc6.png)
<figcaption>ORC File Layout</figcaption>

**Avro**
Avro is a data serialization system developed by the Apache Software Foundation. It provides a compact binary format and supports schema evolution, making it suitable for data interchange and data storage in data lakes. Below are the advantages and disadvantages of using Avro in data lakes:

Advantages of Avro:
1. Compact Binary Format: 
Avro's binary format is compact and efficient for both storage and transmission, reducing storage costs and data transfer overhead.
2. Schema Evolution: 
Avro supports schema evolution, allowing users to introduce schema changes without breaking compatibility with existing data. This feature is particularly beneficial for evolving data structures.
3. Dynamic Typing: 
Avro supports dynamic typing, making it flexible when dealing with semi-structured or schema-less data. This adaptability is advantageous in rapidly evolving data scenarios.
4. Language Independence: 
Avro supports multiple programming languages, allowing seamless integration with various data processing platforms.
5. Resilience to Schema Changes: 
Avro files store the schema along with the data, which means that the data can be self-describing. This resilience to schema changes simplifies data processing and analysis.

Disadvantages of Avro:
1. Slower Query Performance: 
Avro's compact binary format may not be as optimized for query performance as Parquet or ORC, especially for large-scale analytics.
2. Lack of Predicate Pushdown: 
Unlike Parquet and ORC, Avro does not natively support predicate pushdown, potentially leading to slower query execution times.
3. Limited Compression Options: 
Avro supports basic compression techniques like Deflate, but it lacks the variety of compression algorithms available in formats like Parquet and ORC.


![Format comparison](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/au05ghb302dpqwoktpnf.png)
<figcaption>Format comparison</figcaption>

Any comparison of Parquet, ORC and Avro is generally not complete without Hive. Apache Hive is an open-source data warehousing and SQL-like query language system built on top of Apache Hadoop³. It provides a high-level abstraction over Hadoop's distributed storage and processing capabilities, enabling users to perform data analysis, querying, and processing on large-scale datasets stored in data lakes.

_³Apache Hadoop is a collection of open-source software utilities that facilitates using a network of many computers to solve problems involving massive amounts of data and computation. It provides a software framework for distributed storage and processing of big data using the MapReduce programming model_

Hive uses a SQL-like language called HiveQL, which allows users to write queries similar to traditional SQL queries. These queries are then translated into MapReduce⁴, Tez, or Apache Spark jobs, which are executed on the underlying Hadoop infrastructure.

_⁴MapReduce is a programming paradigm that enables massive scalability across hundreds or thousands of servers in a Hadoop cluster. As the processing component, MapReduce is the heart of Apache Hadoop. The term "MapReduce" refers to two separate and distinct tasks that Hadoop programs perform._

One of the key strengths of Apache Hive is its ability to handle structured and semi-structured data. It supports various data formats such as CSV, Parquet, ORC, Avro, and more, making it a versatile tool for data integration and analysis.

![Hive](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sgwdnedp61c6zlguu1fp.png)
<figcaption>Hive</figcaption>

Hive is widely used in big data environments for its scalability, fault-tolerance, and compatibility with existing SQL-based tools. It is commonly used in data warehousing, data exploration, and reporting applications.

```
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
(col_name data_type [COMMENT 'col_comment'],, ...)
[COMMENT 'table_comment']
[ROW FORMAT row_format]
[FIELDS TERMINATED BY char]
[STORED AS file_format];
```

The choice of file format in a data lake depends on the specific use case and the requirements of the organization. Parquet, ORC, and Avro are all capable file formats, each with its own set of advantages and disadvantages. Parquet excels in query performance and schema evolution, ORC stands out for its high compression and lightweight indexing, while Avro offers flexibility in handling schema evolution and dynamic typing.

```
SET hivevar:database=<DATABASE NAME>;
USE ${database};
DROP TABLE IF EXISTS trip_data;
CREATE EXTERNAL TABLE trip_data
(
medallion NUMERIC, hack_license NUMERIC, vendor_id STRING, rate_code NUMERIC,
  store_and_fwd_flag NUMERIC, pickup_datetime STRING, dropoff_datetime STRING, 
  passenger_count NUMERIC, trip_time_in_secs NUMERIC, trip_distance NUMERIC, 
  pickup_longitude NUMERIC, pickup_latitude NUMERIC, dropoff_longitude NUMERIC, 
  dropoff_latitude NUMERIC  
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ","
STORED AS TEXTFILE
LOCATION "/user/${username}/trip_data/"
TBLPROPERTIES ("skip.header.line.count"="1");

DROP TABLE IF EXISTS trip_data;
CREATE EXTERNAL TABLE fare_data
(
  medallion NUMERIC, hack_license NUMERIC, vendor_id STRING, pickup_datetime STRING,
  payment_type STRING, fare_amount NUMERIC, surcharge NUMERIC, mta_tax NUMERIC,
  tip_amount NUMERIC, tolls_amount NUMERIC, total_amount NUMERIC
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION '/user/${username}/fare_data/'
TBLPROPERTIES ("skip.header.line.count"="1");
```

Using the NYC taxi dataset, we see a chart below of how well the data can be compressed into different formats

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tba94zsse7zc0figbsed.png)
<figcaption>Data storage in Parquet</figcaption>
Compression rate

Considerations such as data size, query performance, schema evolution needs, ecosystem support, and the skillset of the data engineering team should guide the decision-making process. Organizations should carefully evaluate their use cases and data processing requirements to select the most suitable format for their data lake, ensuring optimal performance and cost-efficiency in their big data analytics journey.

References:
Apache ORC: https://orc.apache.org
Apache Parquet: https://parquet.apache.org/
Apache Avro: https://avro.apache.org/
Colibrí Labs. "Comparing ORC, Parquet, and Avro Data Formats in Hive." Medium, 13 Mar. 2021, https://medium.com/colibri-labs/comparing-orc-parquet-and-avro-data-formats-in-hive-f53c31eb4f1e
Xu, Kenny Gorman, et al. "Performance Benchmark: Parquet, ORC, JSON, and Avro." Fast Data Labs, 20 Feb. 2021, https://fast-data.github.io/2021/02/20/performance-benchmark.html
Yabuki, Kai. "Performance Analysis of Data Storage Formats in Apache Hadoop." Apache Big Data
Using Parquet in AWS Glue https://docs.aws.amazon.com/glue/latest/dg/aws-glue-programming-etl-format-parquet-home.html
Using Avro in AWS Glue https://docs.aws.amazon.com/glue/latest/dg/aws-glue-programming-etl-format-avro-home.html
Apache Hive Official Website: https://hive.apache.org/
Hortonworks. "Introduction to Apache Hive." Hortonworks Documentation, https://docs.cloudera.com/HDPDocuments/HDP2/HDP-2.6.5/bk_data-access/content/introduction_to_apache_hive.html
Apache Hive Wiki: https://cwiki.apache.org/confluence/display/Hive/Home