{"id":67200,"date":"2025-02-04T04:23:44","date_gmt":"2025-02-04T04:23:44","guid":{"rendered":"https:\/\/peraltafinancing.com\/analytics\/advantages-of-a-denormalised-apache-parquet-storage\/"},"modified":"2025-02-04T04:23:44","modified_gmt":"2025-02-04T04:23:44","slug":"advantages-of-a-denormalised-apache-parquet-storage","status":"publish","type":"post","link":"https:\/\/fivemor.com\/?p=67200","title":{"rendered":"advantages of a denormalised Apache Parquet storage"},"content":{"rendered":"


\n<\/p>\n

\n

It is important for our customers to know that they will always have access to their data when they need it. Whether they want to display a dashboard, drill down or extract data via the API, they need to be able to request a large volume of data in a powerful way to\u00a0achieve their objectives, and therefore\u00a0bring value to their companies.\u00a0 <\/em>
Behind the scenes, AT Internet\u2019s\u00a0engineers are working to set up data processing and storage systems to make this possible. They use the various tools at their disposal for this purpose.\u00a0
This article is the first of\u00a0a series\u00a0that will\u00a0introduce you to some of the data storage technologies we use at AT Internet.\u00a0It\u00a0focuses\u00a0on Apache Parquet, a file storage format designed for big data.<\/em><\/p>\n

AT Internet started a major overhaul of its processing chain\u00a0several years ago. Some fundamental aspects of this redesign have recently become more visible with tools such as\u00a0Data Query 3<\/em><\/a>\u00a0<\/em>and the new\u00a0Data Model<\/em>. Internally, the first\u00a0steps\u00a0of this transition\u00a0started\u00a0a few years ago when the company\u00a0began\u00a0to redesign how it processes and stores data\u00a0from the ground up, taking advantage of the potential that the Big Data ecosystem has to offer.\u00a0
One of the cornerstones of this new approach is\u00a0column-oriented<\/em>\u00a0storage, which makes it possible to\u00a0denormalise\u00a0<\/em>in an\u00a0efficient way. This article will explain what it is and what the benefits are. But before\u00a0I\u00a0get into these explanations,\u00a0I\u2019ll describe\u00a0how data is traditionally stored, especially through\u00a0normalization<\/em>\u00a0and\u00a0line-oriented<\/em>\u00a0storage.\u00a0<\/p>\n

The traditional approach to data storage: standardisation<\/strong>\u00a0 <\/h2>\n

Imagine\u00a0we want to create a database with a certain amount of information related to films.\u00a0 <\/p>\n

\n\n\n\n\n\n\n
Movie Id<\/strong>\u00a0<\/td>\nMovie name<\/strong>\u00a0<\/td>\nRelease year<\/strong>\u00a0<\/td>\nAuthor name<\/strong>\u00a0<\/td>\nAuthor country<\/strong>\u00a0<\/td>\n<\/tr>\n
1\u00a0<\/td>\nThe Matrix\u00a0<\/td>\n1999\u00a0<\/td>\nThe Wachowskis\u00a0<\/td>\nUSA\u00a0<\/td>\n<\/tr>\n
2\u00a0<\/td>\nThe Matrix Reloaded\u00a0<\/td>\n2003\u00a0<\/td>\nThe Wachowskis\u00a0<\/td>\nUSA\u00a0<\/td>\n<\/tr>\n
3\u00a0<\/td>\nThe Matrix Revolutions\u00a0<\/td>\n2003\u00a0<\/td>\nThe Wachowskis\u00a0<\/td>\nUSA\u00a0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/figure>\n

As you can see, in the storage model above, the author\u2019s information is duplicated on each film, despite the fact that in the case in question, this data is exactly the same for each line.\u00a0
In the real world a lot of data is duplicated\/shared in databases. For example, we could want to store the actors who played in each film; or we could\u00a0go to\u00a0a deeper level and store information about the countries the actors or directors come\u00a0from\u2026 or both!\u00a0
We intuitively feel that storing the data by duplicating it can quickly become a problem if a technical solution is not found.\u00a0
Traditionally, the\u00a0\u2018right\u2019<\/em>\/standard<\/em>\u00a0way to store this type of data\u00a0is to\u00a0separate the different\u00a0types of<\/em>\u00a0data into different tables, in order to reduce or even eliminate duplicates, and this technique has long been\u00a0the\u00a0de facto<\/em>\u00a0way to proceed in the industry.\u00a0<\/p>\n

\n\n\n\n\n\n\n
Movie Id<\/strong>\u00a0<\/td>\nMovie name<\/strong>\u00a0<\/td>\nRelease year<\/strong>\u00a0<\/td>\nAuthor id<\/strong>\u00a0<\/td>\n<\/tr>\n
1\u00a0<\/td>\nThe Matrix\u00a0<\/td>\n1999\u00a0<\/td>\n1\u00a0<\/td>\n<\/tr>\n
2\u00a0<\/td>\nThe Matrix Reloaded\u00a0<\/td>\n2003\u00a0<\/td>\n1\u00a0<\/td>\n<\/tr>\n
3\u00a0<\/td>\nThe Matrix Revolutions\u00a0<\/td>\n2003\u00a0<\/td>\n1\u00a0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/figure>\n
\n\n\n\n\n
Author id<\/strong>\u00a0<\/td>\nname<\/strong>\u00a0<\/td>\ncountry<\/strong>\u00a0<\/td>\n<\/tr>\n
1\u00a0<\/td>\nThe Wachowskis\u00a0<\/td>\nUSA\u00a0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/figure>\n

In the current\u00a0(and\u00a0time-honoured)\u00a0processing chain,\u00a0i.e.\u00a0the one used to execute your\u00a0Data Query<\/em>\u00a0requests, most data is stored in this way\u00a0and\u00a0called a\u00a0normal form.<\/a>\u00a0
This approach has the advantage of reducing duplication and therefore reducing the amount of data that needs to be stored. In many cases of use, storing data in this way is the most natural, ecological and efficient because database management systems (DBMS) have mechanisms to make queries on this type of schema efficient. How? Through techniques such as the calculation and storage of statistics internal to the database engine, or the optimisation of queries.\u00a0
In the world of analytics, where\u00a0OLAP<\/a>-type\u00a0queries are mainly carried out, this paradigm is beginning to pose some problems with the explosion of the quantities of data collected.\u00a0
The most important of these problems occurs when you try to cross several large tables. In technical jargon, this is\u00a0called\u00a0performing a\u00a0join<\/em>\u00a0operation between several tables.\u00a0
At the scales of data processed and requested by AT Internet, this joining step can be very costly and complex to perform effectively. In the event that the request is not properly optimised by the DBMS engine, the processing cost may be such that it\u00a0ultimately results in<\/em>\u00a0a request that is too slow for the end customer.\u00a0<\/p>\n

Another way to do things: denormalise the data to get performance at the request<\/strong>\u00a0 <\/h2>\n

Data Engineer 1 \u2013 Why not just store everything in a denormalised way? This way we won\u2019t have to pay the price of these damn joins every time the customer wants to request\u00a0their\u00a0data!<\/em>\u00a0
Data Engineer 2 \u2013 Are you serious? Do you have any idea how much duplication this would create?<\/em>\u00a0
Data Engineer 2 -\u2026<\/em>\u00a0
Data Engineer 2 \u2013 This is madness!<\/em>\u00a0
Data Engineer 1 \u2013 Madness? \u2026 THIS IS DATA!<\/em>\u00a0<\/p>\n

The rationale for adopting such an approach is quite simple.\u00a0Avoiding the need to make joins means simpler queries, easier to optimise, and therefore better response times for the end user at the time of the request.\u00a0
Empirically, we notice that the cost of storing the data is more than compensated by the request performance obtained, and this\u00a0applies even more as\u00a0we can deploy a number of\u00a0tips\u00a0to<\/em>\u00a0relieve the burden of redundancy.\u00a0<\/p>\n

The column format<\/strong>\u00a0 <\/h2>\n

One way to compensate for the problems of data duplication by returning to denormalised storage is by using what is called a\u00a0column-oriented format<\/em>.\u00a0<\/p>\n

In a traditional database, each\u00a0record\u00a0<\/code>is stored in one block. The blocks follow each other, but all the data for each record is in an adjoining\u00a0space:\u00a0<\/p>\n

1;The Matrix;1999;The Wachowskis\u00a02;The Matrix Reloaded;2003;The Wachowskis\u00a03;The Matrix Revolutions;2003;The Wachowskis\u00a0<\/pre>\n
One of the problems associated with storing in this form is that you have to read the whole line from the disk even if you want to load only part of the data. For example, we are obliged to load the titles from the disk\u00a0even if the only information we want to retrieve is the year of release of each film.\u00a0
Reading a relatively small set of all available columns is a prototypical example of the type of requests executed by our customers in an analytical context.\u00a0
In a column-oriented format, each column is stored\u00a0separately:\u00a0<\/p>\n
The Matrix:1;The Matrix Reloaded:2;The Matrix Revolutions:3\u00a01999:1;2003:2;2003:3\u00a0The Wachowskis:1;The Wachowskis:2;The Wachowskis:3\u00a0<\/pre>\n
At first glance, this may not seem like a big difference, but in reality, this alteration changes the constraints\u00a0so much that it is\u00a0a real paradigm shift.\u00a0
One of the immediate advantages is that it is now much easier to read only the data in certain columns. This implies fewer disk I\/Os, and this is crucial because disk I\/Os are one of the first factors limiting performance.\u00a0
A somewhat less obvious advantage of this paradigm shift is that since the data in the same column are generally relatively homogeneous, it allows it to be compressed aggressively by applying appropriate compression algorithms, which can even be chosen on the fly and on a case-by-case basis.\u00a0
To give an overview,\u00a0by using our\u00a0previous example, the date and author columns could be\u00a0stored\u00a0as follows:\u00a0<\/p>\n
1999:1;2003:2,3\u00a0The Wachowskis:*\u00a0<\/pre>\n
Many optimisations are possible, but these are only mentioned here as examples to give you an idea of the possibilities offered by this type of format.\u00a0
Most DBMS on the market offer options to store data in column format. Microsoft SQL Server, the database traditionally used by AT Internet, for example, is able to manage the column format and this feature has been used for several years now in our databases.\u00a0Nevertheless, the data in these databases remains\u00a0normalized, unlike\u00a0in\u00a0the\u00a0New Data Factory.\u00a0<\/code><\/p>\n
 A few words about Apache Parquet<\/strong>\u00a0 <\/h2>\n
The official Apache Parquet site<\/a>\u00a0defines the format as:\u00a0
A column storage format available for any product in the Hadoop ecosystem, regardless of the choice of processing framework, data model or programming language.<\/em>\u00a0<\/p>\n
This technology is one of the most popular implementations of a column-oriented file format.\u00a0
One of the aspects I would like to stress is that this is a\u00a0file format<\/strong>\u00a0and not a\u00a0database management system<\/strong>. This is an important distinction, especially because it implies that being made up of simple files, a\u00a0data lake\u00a0parquet can be stored where you want, whether in your SAN storage bay, in a\u00a0datacentre, or in a cloud computing server.\u00a0
Adopting such a storage format thus makes it possible to start on a sound basis compatible with the principle of\u00a0decoupling\u00a0compute\u00a0and storage<\/strong>, one of the prevailing principles in big data storage and which we try to follow as data engineers at AT Internet.\u00a0<\/p>\n
We will not have time in this article to cover all the features of this Apache Parquet format, but here is a brief summary of its most useful\u00a0features:\u00a0<\/p>\n