Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to achieve read-write separation in analytical data warehouse". The content is simple and clear. I hope it can help you solve your doubts. Next, let the editor lead you to study and learn the article "how to achieve read-write separation in analytical data warehouse".

Compared with the traditional transactional database represented by MySQL, data warehouse has a great feature that it is mainly optimized for batch writing and query, and can not support advanced features such as update and transaction. Some commercial data warehouse analysis systems, such as Vertica, have been able to import and query hundreds of billions of data in seconds.

Shenze data has been committed to helping enterprises build data warehouses, achieve data response in seconds, and accumulate data assets. Through the technical exploration and practice of Shenze data, this paper discusses how to use the existing open source components to achieve the separation of read and write in the analytical data warehouse.

Why is it necessary to separate reading and writing

Analytical data warehouses generally have the following characteristics:

The main results are as follows: (1) faced with complex multi-dimensional analysis requirements, it can roll up and drill down in any dimension.

(2) there are generally many dimensions of stored data, so it is a wide table and is generally sparse.

(3) the amount of data is relatively large, writing once and querying many times.

In view of this characteristic, analytical databases generally choose column storage data format, such as Parquet and so on. The advantage is that it is efficient for statistical analysis and has a high storage compression ratio for sparse wide tables. So we can think of the column storage format as a read-oriented optimized storage format, which we call ReadOptimized Store (ROS).

However, the column storage format also has a disadvantage: once the data in this format is generated, it is difficult to modify, and it is difficult to insert new data into existing data files, only new data files can be added. In a traditional database like MySQL, the row storage file format used is suitable for modification and insertion, and we can think of this row storage format as a write-oriented optimized storage format, called WriteOptimized Store (WOS).

To sum up, in order to achieve an analytical database that can be imported and queried in seconds, if only ROS is selected, it is difficult to support the import of large amounts of data in seconds. If only WOS is selected, it is difficult to implement a second-level query of any dimension, so we need to separate read from write.

The implementation principle of the separation of reading and writing

Both WOS and ROS are needed in the data warehouse, so that we generate WOS files for all write operations; at the same time, all read operations mainly rely on ROS files, but also query a small number of WOS files. The overall diagram is as follows:

Fig. 1 schematic diagram of read-write separation

As shown in the figure, WOS files need to be converted to ROS files periodically, and because ROS is generally divided into multiple Partition in a data warehouse, one WOS may be converted to multiple ROS. The conversion process needs to be atomic, because for the upper query engine, there can only be one copy of the same data at a time.

Operation of open source solution

The principle of the read-write separation scheme is briefly introduced before. in the specific engineering practice, the engineers of Shenze data are also faced with a lot of scheme choices and practical difficulties. The following is a brief introduction to the "hard bones" that Shenze data has chewed in the practice of building a data warehouse.

The choice of ROS is relatively simple, our engineers chose Parquet+ Impala query scheme, and combined with our business characteristics to do a lot of code-level optimization. There may be many choices for WOS, and we can choose the commonly used HDFS line storage file formats, such as TextFile, SequenceFile, Avro, and so on.

Take SequenceFile as an example, when we define our own Impala table, we can specify the storage format of a special Partition file as SequenceFile, while other Partition as normal date Partition data, specify the format as Parquet, the advantage of this way is that there is always only one table.

Later, based on the consideration of query efficiency and future architecture upgrade, we finally chose Kudu as the WOS. The architecture implementation diagram is as follows:

Fig. 2 implementation diagram of read-write separation

As shown in the figure, we will create three physical tables, with two Kudu tables as WOS and one Parquet table as ROS. All writes are written to the Kudu table in the Ingesting state, and when the Ingesting table is written to a certain size, it automatically transitions to the Staging state.

At this point, on the one hand, we generate a new Kudu table as the Ingesting table, and on the other hand, we start the WOS-to-ROS transformation, which is performed through a task called Mover. Convert all the data in the Kudu table of the Staging state to the Parquet table of the corresponding Partition.

When the table transition of Staging state is completed and the table of Ingesting state is full, a table cutting operation will be triggered, and the metadata needs to be updated, telling Impala to query with the new data, and the whole table cutting operation is atomic. Moreover, the converted Staging table needs to be retained for some time to avoid that the query operation initiated before cutting the table is not completed in time.

For query requests, we will create a virtual table that contains the Staging table, the Ingesting table, and the ROS table, that is, a View. The user's query always points to a View, but the following physical table changes frequently. In this way, it takes into account both the continuous updating of query data and the optimization of query performance.

In the process of implementation, there are still a lot of specific work, such as how to add columns to the table to ensure that the structure of each table is consistent; broken files in the Parquet table more affect the query efficiency, how to merge regularly and so on. Limited to space, I will not give a specific introduction here.

The final technical architecture of Shenze data is shown below:

The above is all the contents of the article "how to achieve the separation of reading and writing in analytical data warehouse". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.