Data storage method and engine for online analytical processing database

Abstract

The invention provides a data storage method and engine for an online analytical processing database, and the method comprises the steps: storing to-be-stored original data into a data table of the online analytical processing database in modes of partitioning, partitioning compression and column storage, so that the data reading granularity unit of the online analytical processing database is a compressed data block; and generating a current partition index of the data table, a multi-level index under each partition directory and a corresponding data mark, so as to search target data in an online analysis processing database based on the partition index, the multi-level index and the data mark in sequence. According to the method and the device, the data reading granularity of the online analytical processing database can be reduced to the compressed data block level, and only the required data can be loaded to the memory by searching the index, so that the data reading range can be effectively reduced, the data volume loaded to the memory can be reduced, and the data reading efficiency and reliability can be effectively improved.

Description

Technical Field

[0001] The present application relates to the field of data processing technology, and in particular to a data storage method and engine for an online analytical processing database. Background Art

[0002] With the popularization of Internet technology, more and more data is generated by enterprises and individuals, and the storage and calculation of these massive data are becoming more and more important. At present, it is still difficult to achieve real-time response of multidimensional analysis under massive data. Online analytical processing database OLAP, also known as multidimensional analysis, is a concept proposed by Edgar Frank Codd, the father of relational database, in 1993. As the name suggests, it refers to examining data through a variety of different dimensions for in-depth analysis. At present, the open source solutions in the industry include analytical database Apache Doris, extremely fast full-scenario MPP database StarRocks, distributed data analysis platform Druid, etc. Analytical databases are used by most companies due to their excellent query performance. The underlying storage of analytical databases adopts a partitioned and bucketed design, and uses the built-in index generator to intelligently generate data indexes, which can effectively reduce the amount of data that needs to be loaded into memory and the amount of data during calculation.

[0003] However, currently analytical databases mainly support two types of indexes: built-in smart indexes and secondary indexes manually created by users. They do not support creating indexes on any columns. In addition, since the current version uses full-memory computing, it is easy to cause insufficient memory when facing large-scale data and complex data extraction, transformation, and loading processes (ETL). This will not only affect the efficiency of data reading in the online analytical processing database, but also affect the reading reliability. Summary of the invention

[0004] In view of this, embodiments of the present application provide a data storage method and engine for an online analytical processing database to eliminate or improve one or more defects existing in the prior art.

[0005] One aspect of the present application provides a data storage method for an online analytical processing database, comprising:

[0006] The original data to be stored is stored in a data table of an online analytical processing database in a partitioned, block-compressed and column-stored manner, so that the data reading granularity unit of the online analytical processing database is a compressed data block;

[0007] The current partition index of the data table, the multi-level index under each partition directory and the corresponding data tags are constructed or updated to search for target data in the online analytical processing database based on the partition index, the multi-level index and the data tags in sequence.

[0008] In some embodiments of the present application, the storing of the raw data to be stored in a data table of an online analytical processing database in a partitioned, block-compressed and column-stored manner so that the data reading granularity unit of the online analytical processing database is a compressed data block includes:

[0009] Generate each compressed data block corresponding to the original data to be stored;

[0010] storing each of the compressed data blocks in a data file in an online analytical processing database so that the data reading granularity unit of the online analytical processing database is a compressed data block, wherein the data file is one of each basic file stored in a column storage manner under a partition directory of a data table in the online analytical processing database;

[0011] The data files in the same partition directory are periodically merged.

[0012] In some embodiments of the present application, the step of constructing or updating the current partition index of the data table, the multi-level index under each partition directory, and the corresponding data tag, so as to sequentially search for target data in the online analytical processing database based on the partition index, the multi-level index, and the data tag, includes:

[0013] Build or update the current partition index in the data table and the multi-level index under each partition directory;

[0014] Generate a data marker file for associating data files and multi-level indexes, so as to search for a target compressed data block in the data table of the online analytical processing database based on the partition index and the multi-level index, load the target compressed data block into the memory and then decompress it, and then extract the target data from the decompressed data based on the data marker file corresponding to the target compressed data block.

[0015] In some embodiments of the present application, the generating of each compressed data block corresponding to the original data to be stored includes:

[0016] Acquire data in batches from the original data at a preset index granularity to obtain each single batch of data;

[0017] Based on a preset compression algorithm, each of the single batches of data is compressed to obtain compressed data corresponding to each of the single batches of data;

[0018] Generating header information corresponding to each single batch of data respectively according to the type of the compression algorithm, the data size of each single batch of data and the data size of the corresponding compressed data;

[0019] Based on the header information and compressed data corresponding to each of the single batches of data, compressed data blocks corresponding to each of the single batches of data are generated respectively.

[0020] In some embodiments of the present application, the multi-level index includes a primary index and a secondary index;

[0021] Correspondingly, the construction or updating of the current partition index in the data table and the multi-level index under each partition directory includes:

[0022] When a primary key is set in the data table, a primary index is constructed or updated for each partition directory of the data table at intervals of the index granularity, and the primary index is stored in a primary index file under the partition directory where the primary index is located, wherein the primary index is a sparse index;

[0023] A secondary index under the partition directory is constructed or updated according to the data aggregation information under the partition directory, and the secondary index is stored in a secondary index file under the partition directory where the secondary index is located.

[0024] In some embodiments of the present application, generating a data tag file for associating a data file with a multi-level index includes:

[0025] Generate corresponding data tags according to the offset information of each compressed data block in the data file and the offset information of each compressed data block after decompression, and align the data tags with the index interval of the primary index, thereby generating a primary data tag file that corresponds one-to-one with the data file and is used to associate the data file with the primary index;

[0026] Furthermore, corresponding data tags are generated according to the offset information of each compressed data block in the data file and the offset information of each compressed data block after decompression, and the data tags are aligned with the index interval of the secondary index, thereby generating a secondary data tag file that corresponds one-to-one with the data file and is used to associate the data file and the secondary index.

[0027] In some embodiments of the present application, each basic file stored in a column-based storage manner under a partition directory of a data table in the online analytical processing database includes:

[0028] Verification file, which uses binary format to store data and is used to save the size of other files in the partition directory where it is located and the corresponding hash value to verify other files in the partition directory where it is located;

[0029] Column information files use plain text format to store data and are used to save column field information under the partition directory where they are located;

[0030] Count files use plain text format to store data and are used to record the total number of data rows in the partition directory where they are located;

[0031] The primary index file uses binary format to store data and is used to store the sparse index as the primary index in the multi-level index;

[0032] Data files use compressed format to store data, and each column field corresponds to an independent data file;

[0033] And, a first-level data marker file stores data in binary format and is used to save the offset information of the data in the data file; each column field has a corresponding first-level data marker file, and the first-level data marker file is aligned with the sparse index and has a one-to-one correspondence with the data file to store the mapping relationship between the first-level index and the data file.

[0034] In some embodiments of the present application, the partition directory further includes a partition file and a secondary file stored in a column storage manner, and the partition file is used to store a partition index file and a minimum value and a maximum value of the original data;

[0035] The secondary files include:

[0036] A secondary index file is used to store a secondary index in a multi-level index, where the secondary index is constructed by the aggregated information of the data stored in the partition directory where the secondary index is located;

[0037] And, a secondary data marker file stores data in binary format and is used to save the offset information of the data in the data file. The secondary data marker file is aligned with the secondary index and corresponds one-to-one with the data file to store the mapping relationship between the secondary index and the data file.

[0038] Another aspect of the present application provides a data storage engine for an online analytical processing database, comprising:

[0039] A data storage module, used for storing the original data to be stored in a data table of an online analytical processing database in a partitioned, block-compressed and column-stored manner, so that the data reading granularity unit of the online analytical processing database is a compressed data block;

[0040] The index generation module is used to construct or update the current partition index of the data table, the multi-level index under each partition directory and the corresponding data tags, so as to search for target data in the online analytical processing database based on the partition index, the multi-level index and the data tags in turn.

[0041] The third aspect of the present application provides an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the data storage method for an online analytical processing database when executing the computer program.

[0042] A fourth aspect of the present application provides a computer-readable storage medium having a computer program stored thereon, which implements the data storage method for an online analytical processing database when executed by a processor.

[0043] The data storage method for an online analytical processing database provided by the present application stores the original data to be stored in a data table of the online analytical processing database in a partitioned, block-compressed and column-stored manner, so that the data reading granularity unit of the online analytical processing database is a compressed data block; constructs or updates the current partition index of the data table, the multi-level index under each partition directory and the corresponding data tags, so as to search for target data in the online analytical processing database based on the partition index, the multi-level index and the data tags in sequence; by adopting the partitioned, block, multi-level index and column-stored manner, the data reading granularity of the online analytical processing database can be reduced to the compressed data block level, and by searching the index, only the required data can be loaded into the memory, thereby effectively narrowing the scope of data reading, reducing the amount of data loaded into the memory, and effectively improving the data reading efficiency and reliability.

[0044] Additional advantages, purposes, and features of the present application will be partially described in the following description, and will become partially apparent to those skilled in the art after studying the following, or may be learned from the practice of the present application. The purposes and other advantages of the present application can be achieved and obtained by the structures specifically pointed out in the specification and the drawings.

[0045] Those skilled in the art will understand that the purposes and advantages that can be achieved by the present application are not limited to the above specific description, and the above and other purposes that can be achieved by the present application will be more clearly understood based on the following detailed description. BRIEF DESCRIPTION OF THE DRAWINGS

[0046] The drawings described herein are used to provide a further understanding of the present application, constitute a part of the present application, and do not constitute a limitation of the present application. The components in the drawings are not drawn to scale, but are only for the purpose of illustrating the principles of the present application. In order to facilitate the illustration and description of some parts of the present application, the corresponding parts in the drawings may be enlarged, that is, they may become larger relative to other components in the exemplary device actually manufactured according to the present application. In the drawings:

[0047] Figure 1This is a first flow chart of a data storage method for an online analytical processing database in one embodiment of the present application.

[0048] Figure 2 This is a schematic diagram of a data storage architecture design for an online analytical processing database in one embodiment of the present application.

[0049] Figure 3 This is a second flow chart of a data storage method for an online analytical processing database in one embodiment of the present application.

[0050] Figure 4 This is a schematic diagram of the process of creating, merging, and deleting partition directories in an example of this application.

[0051] Figure 5 This is a third flow chart of a data storage method for an online analytical processing database in one embodiment of the present application.

[0052] Figure 6 This is a schematic diagram of a compressed data block in an example of the present application.

[0053] Figure 7 This is a logical diagram of cutting and compressing data blocks in an example of the present application.

[0054] Figure 8 This is a schematic diagram of the primary index generation rules for the test table in an example of this application.

[0055] Fig. 9 This is a schematic diagram of marking data in a data marking file in an example of the present application.

[0056] Fig.10 This is a schematic diagram of the correspondence between the mark file and the compressed data file in an example of the present application.

[0057] Fig.11 This is a schematic diagram of a process of minimizing the scan data range in an example of the present application.

[0058] Fig.12 It is a structural diagram of a data storage engine for an online analytical processing database in one embodiment of the present application. DETAILED DESCRIPTION

[0059] In order to make the purpose, technical solution and advantages of the present application more clearly understood, the present application is further described in detail below in conjunction with the implementation modes and the accompanying drawings. Here, the illustrative implementation modes and descriptions of the present application are used to explain the present application, but are not intended to limit the present application.

[0060] It should also be noted here that in order to avoid obscuring the present application due to unnecessary details, only the structures and / or processing steps closely related to the scheme according to the present application are shown in the accompanying drawings, while other details that are not very relevant to the present application are omitted.

[0061] It should be emphasized that the term “include / comprises” when used herein refers to the presence of features, elements, steps or components, but does not exclude the presence or addition of one or more other features, elements, steps or components.

[0062] It should also be noted that, unless otherwise specified, the term “connection” herein may refer not only to a direct connection but also to an indirect connection involving an intermediate.

[0063] Hereinafter, embodiments of the present application will be described with reference to the accompanying drawings. In the accompanying drawings, the same reference numerals represent the same or similar components, or the same or similar steps.

[0064] In order to solve the problem that Doris is prone to insufficient memory when facing large-scale batch processing, the embodiments of the present application respectively provide a data storage method for an online analytical processing database, a data storage engine for an online analytical processing database for executing the data storage method for the online analytical processing database, a physical device and a computer-readable storage medium. Starting from the underlying data storage, through the partitioning + blocking + multi-level indexing + columnar storage solution, the data reading granularity can be reduced to the compressed data block level. By searching the index, only the required data can be loaded into the memory, thereby further narrowing the scope of data reading.

[0065] The details are described in detail through the following examples.

[0066] Based on this, the embodiment of the present application provides a data storage method for an online analytical processing database that can be implemented by a data storage engine for an online analytical processing database. The data storage engine for the online analytical processing database can be written as NewDB, which reduces the amount of data loaded into the memory to avoid the problem of memory overflow under large data volume calculation. In order to reduce the amount of data loaded into the memory, NewDB is designed from the perspective of storage and indexing, see Figure 1 The data storage method for the online analytical processing database specifically includes the following contents:

[0067] Step 100: storing the original data to be stored in a data table of an online analytical processing database in a partitioned, block-compressed and column-stored manner, so that the data reading granularity unit of the online analytical processing database is a compressed data block.

[0068] For data storage, the data storage engine for the online analytical processing database uses the current mainstream partitioning, i.e., columnar storage solution, and introduces compressed data blocks as the smallest unit of data storage.

[0069] The column storage means that each column field has a corresponding data file. Data files are organized and stored in the form of partition directories, so only the data in the current partition segment will be saved in the data file. The advantages of this design are: first, data compression can be better performed (data of the same type is put together, which is more friendly to compression), and second, the scope of data scanning can be minimized. First, the data stored in the data file is compressed, and the LZ4 algorithm is used by default; second, the data will be sorted in advance according to the declaration of the sorting rule ORDER BY for the query results; the data is organized and written into the data file in the form of compressed blocks.

[0070] In one or more embodiments of the present application, the block compression storage method can be replaced by a page storage method.

[0071] Step 200: construct or update the current partition index of the data table, the multi-level index under each partition directory and the corresponding data tags, so as to search for target data in the online analytical processing database based on the partition index, the multi-level index and the data tags in sequence.

[0072] From the above description, it can be seen that the data storage method for the online analytical processing database provided in the embodiment of the present application can reduce the data reading granularity of the online analytical processing database to the compressed data block level by adopting partitioning, blocking, multi-level indexing and columnar storage. By searching the index, only the required data can be loaded into the memory, thereby effectively narrowing the scope of data reading, reducing the amount of data loaded into the memory, and effectively improving the data reading efficiency and reliability, thereby improving the running stability and query speed of the online analytical processing database software.

[0073] In order to further improve the effectiveness of data storage for an online analytical processing database and to further improve the efficiency of data reading, in a data storage method for an online analytical processing database provided in an embodiment of the present application, each basic file stored in a columnar storage manner under a partition directory of a data table in the online analytical processing database specifically includes the following content:

[0074] (1) Verification file, which uses binary format to store data and is used to save the size of other files in the partition directory where it is located and the corresponding hash value to verify other files in the partition directory where it is located;

[0075] (2) Column information file, which uses plain text format to store data and is used to save the column field information under the partition directory where it is located;

[0076] (3) Count file, which stores data in plain text format and is used to record the total number of data rows in the partition directory where it is located;

[0077] (4) A primary index file, which stores data in binary format and is used to store a sparse index that is a primary index in a multi-level index;

[0078] (5) Data files, which store data in a compressed format. Each column field corresponds to an independent data file.

[0079] (6) A primary data tag file, which stores data in binary format and is used to save the offset information of the data in the data file; each column field has a corresponding primary data tag file, and the primary data tag file is aligned with the sparse index and has a one-to-one correspondence with the data file to store the mapping relationship between the primary index and the data file.

[0080] The partition directory also includes partition files and secondary files stored in a column-based storage manner, wherein the partition files are used to store partition index files and the minimum and maximum values ​​of the original data;

[0081] The secondary files include:

[0082] (1) A secondary index file, which is used to store a secondary index in a multi-level index. The secondary index is constructed by the aggregated information of the data stored in the partition directory where it is located;

[0083] (2) A secondary data marker file, which stores data in binary format and is used to save the offset information of the data in the data file. The secondary data marker file is aligned with the secondary index and has a one-to-one correspondence with the data file to store the mapping relationship between the secondary index and the data file.

[0084] To further illustrate the above architecture, in a specific example of the present application, see Figure 2, the unique identifier of the data table is represented by table_name; different partition directories are identified by partition_1, partition_1 and partition_n, etc., where n is a positive integer greater than 2. Taking the partition directory partition_1 as an example, the basic files include the checksum file represented by checksums.txt; the column information file represented by columns.txt; the count file represented by count.txt; the primary index file represented by primary.idx; the data file represented by [Column].bin, which can also be called a .bin data file; and the primary data mark file represented by [Column].mrk2 and [Column].mrk2.

[0085] The partition index file is represented by partition.dat; the minimum and maximum values ​​of the original data are represented by minmax[Column].idx; the secondary index file is represented by skp_idx[Column].idx; the secondary data mark file is represented by skp_idx[Column].mrk, wherein the secondary data mark file is constructed in the same way as the primary index file.

[0086] Based on the above examples, each file is described as follows:

[0087] (1) partition: partition directory. All data files (primary.idx, [Column].mrk, [Column].bin, etc.) are organized and stored in the form of partition directories. Data in the same partition will eventually be merged into the same partition directory.

[0088] (2) checksums.txt: Verification file, stored in binary format. It saves the size and hash value of the remaining files (primary.idx, count.txt, etc.) to quickly verify the integrity and correctness of the files.

[0089] (3) columns.txt: Column information file, stored in plain text format. Used to save column field information under this data partition.

[0090] (4) count.txt: Count file, stored in plain text format. Used to record the total number of rows of data in the current data partition directory.

[0091] (5) primary.idx: primary index file, stored in binary format. Used to store sparse indexes. A table can only declare one primary index (through ORDER BY or PRIMARY KEY).

[0092] (6)[Column].bin: Data file, stored in compressed format, the default compression format is LZ4. Each column field has an independent .bin data file.

[0093] (7)[Column].mrk: A primary data tag file, also known as a column field tag file, stored in binary format. The tag file stores the offset information of the data in the .bin file. Each column field will have a corresponding .mrk tag file. The tag file is aligned with the sparse index and corresponds one-to-one with the .bin file. NewDB establishes a mapping relationship between the primary.idx sparse index and the .bin data file through the tag file. First, the offset information (.mrk) of the corresponding data is found through the sparse index (primary.idx), and then the data is read directly from the .bin file through the offset.

[0094] (8)minmax_[Column].idx: The minmax index is used to record the minimum and maximum values ​​of the original data corresponding to the partition field under the current partition. It can quickly skip unnecessary data partition directories when performing data queries.

[0095] (9)skp_idx_[Column].idx and skp_idx_[Column].mrk:

[0096] If a secondary index is declared, the corresponding secondary index and its corresponding secondary data tag file are additionally generated, and they are also stored in binary format.

[0097] In order to further improve the effectiveness of data storage for the online analytical processing database and to further improve the data reading efficiency, in a data storage method for the online analytical processing database provided in an embodiment of the present application, see Figure 3 , step 100 in the data storage method for the online analytical processing database specifically includes the following contents:

[0098] Step 110: Generate each compressed data block corresponding to the original data to be stored.

[0099] Step 120: Store each of the compressed data blocks in a data file in an online analytical processing database so that the data reading granularity unit of the online analytical processing database is a compressed data block, wherein the data file is one of the basic files stored in a columnar storage manner under a partition directory of a data table in the online analytical processing database.

[0100] The compressed data blocks are closely connected in the order of writing. Introducing compressed data blocks in data files can reduce the reading granularity to the compressed data block level without reading the entire data file, thereby further reducing the scope of data reading.

[0101] Step 130: Periodically merge the data files in the same partition directory.

[0102] Specifically, with the help of data partition storage, when using the data storage engine for online analytical processing database to query data, useless data files can be effectively skipped and only the smallest partition directory is used. When creating a table, the partition field needs to be manually specified. The partition directory does not exist after the data table is created, but is created during the writing process. The data storage engine for online analytical processing database will periodically merge data files under the same partition. For an example of creating, merging, and deleting partition directories, see Figure 4 .

[0103] exist Figure 4 In the example, at time T0, a data table is created. At this time, there is no partition directory, that is, the current partition directory partition_v5 is empty.

[0104] At time T1, the statement "INSERT INTO partition_v5 VALUES(A,c1,2019-05-01),(A,c2,2019-06-01)" is used to write a batch of data covering partition 2; the default activation state of the new partition directory is 1. ID.bin, Code.bin, and EventTime.bin are all data files, and ID.mrk, Code.mrk, and EventTime.mrk are all tag files.

[0105] At time T2, the statement "INSERT INTO partition_v5 VALUES(B',c3,2019-05-08),(C',c4,2019-06-10),(D',c5,2019-07-10)" is used to write a new batch of data covering three partitions (i.e. Figure 4 ), of which two partitions (201905_1_1_0 and 201906_2_2_0) are the same as at T1.

[0106] At time T3, the same partitions will be merged to generate new partitions. At the same time, the state of the old partition directory will be set to inactive, that is, the active state is 0.

[0107] At time T4, the inactive partition directory is deleted.

[0108] In order to further improve the effectiveness of data storage for the online analytical processing database and to further improve the data reading efficiency, in a data storage method for the online analytical processing database provided in an embodiment of the present application, see Figure 3 , step 200 in the data storage method for the online analytical processing database specifically includes the following contents:

[0109] Step 210: construct or update the current partition index in the data table and the multi-level index under each partition directory;

[0110] Step 220: Generate a data marker file for associating data files and multi-level indexes, so as to search for a target compressed data block in the data table of the online analytical processing database based on the partition index and the multi-level index, and decompress the target compressed data block after loading it into the memory, and then extract the target data from the decompressed data based on the data marker file corresponding to the target compressed data block.

[0111] In order to further improve the effectiveness of data storage for the online analytical processing database and to further improve the data reading efficiency, in a data storage method for the online analytical processing database provided in an embodiment of the present application, see Figure 5 , step 110 in the data storage method for the online analytical processing database specifically includes the following contents:

[0112] Step 111: acquiring data in batches from the original data at a preset index granularity to obtain individual batches of data.

[0113] Step 112: compress each of the single batches of data based on a preset compression algorithm to obtain compressed data corresponding to each of the single batches of data.

[0114] Step 113: Generate header information corresponding to each single batch of data according to the type of the compression algorithm, the data size of each single batch of data and the data size of the corresponding compressed data.

[0115] Step 114: Based on the header information and compressed data corresponding to each of the single batches of data, respectively generate compressed data blocks corresponding to each of the single batches of data.

[0116] Specifically, see Figure 6A compressed data block consists of two parts: header information and compressed data. The header information is fixedly represented by 9-bit bytes, specifically consisting of 1 UInt8 (1 byte) integer and 2 UInt32 (4 byte) integers, which respectively represent the type of compression algorithm used, the size of the compressed data, and the size of the data before compression. Take the data file JavaEnable.bin as an example, it contains compressed data block 0 and compressed data block 1. The header information head stat of compressed data block 0 is (65536, 12000), and the header information head stat of compressed data block 1 is (65536, 14660). The header file corresponding to compressed data block 0 is written as "0x821200065536".

[0117] The first four characters of the header file represent the compression method (which can be written as "CompressionMethod"), and "Type" represents the compressed data type, for example, 8-bit unsigned integer Ulnt8 can be selected, and "Size" represents the compressed data size, for example, it can be set to 1Byte. Different compression algorithms use different expressions, for example, "0x82" represents the LZ4 compression algorithm, "0x90" represents the ZSTD compression algorithm, "0x91" represents the Multiple compression algorithm, and "0x92" represents the Delta compression algorithm.

[0118] The middle five characters of the header file, such as "12000" in "0x821200065536", represent the byte size of the compressed data (which can be written as "CompressedSize"). The compressed data type can be a 32-bit unsigned integer Ulnt32, with a size of 4 bytes.

[0119] The last five characters of the header file, such as "65536" in "0x821200065536", represent the byte size of the compressed data (which can be written as "UncompressedSize"). The compressed data type can be a 32-bit unsigned integer Ulnt32, with a size of 4 bytes.

[0120] The data file is composed of multiple compressed data blocks, and the header information of each compressed data block contains the compression algorithm used for the data block, and the data size information before and after compression. The upper and lower limits of each compressed data block are specified by the min_compress_block_size (default 65536) and max_compress_block_size (default 1048576) parameters respectively. The final size of a compressed data block is related to the actual size of the data within an index granularity (index_granularity). The data storage engine for the online analytical processing database will follow the index granularity (by default, 8192 rows are taken each time) during the specific data writing process, see Figure 7 , get data in batches and process them. If the uncompressed size of a batch of data is set to size, the entire writing process follows the following rules:

[0121] (1) size < 64KB: If a single batch of data is less than 64KB, the next batch of data will continue to be obtained until the accumulated size is greater than or equal to 64KB, and then the next compressed data block will be generated.

[0122] (2) 64KB<=size<=1MB: If the size of a single batch of data is exactly between 64KB and 1MB, the next compressed data block is generated directly.

[0123] (3) size > 1MB: If a single batch of data exceeds 1MB, it is first truncated to 1MB and the next compressed data block is generated. The remaining data continues to follow the above rules. In this case, a batch of data generates multiple compressed data blocks.

[0124] In order to further improve the effectiveness of data storage for an online analytical processing database and to further improve the data reading efficiency, in a data storage method for an online analytical processing database provided in an embodiment of the present application, the multi-level index includes a primary index and a secondary index; see Figure 5 , step 210 in the data storage method for the online analytical processing database specifically includes the following contents:

[0125] Step 211: when a primary key is set in the data table, a primary index is constructed or updated for each partition directory of the data table at intervals of the index granularity, and the primary index is stored in a primary index file under the partition directory where the primary index is located, wherein the primary index is a sparse index;

[0126] Specifically, after the primary key is set for a data table, a primary index will be generated for the data table at intervals of index granularity index_granularity and saved in the primary index file primary.idx. The index data is sorted according to the primary key PRIMARYKEY. The primary index in the primary index file primary.idx is implemented using a sparse index, that is, each row of index mark corresponds to a segment of data, not a row. Because it is a sparse index, the data storage engine for the online analytical processing database will generate an index record only after the index granularity index_granularity rows of data are separated, and its index value will be obtained based on the declared primary key field. With the help of sparse indexes, data files outside the scope of the primary key condition can be excluded when querying data, thereby effectively reducing the data scanning range and speeding up the query. In the data storage engine for the online analytical processing database, the primary index works in memory. The generation rules of the primary index data are as follows: Figure 8 As shown in the figure, the primary key of the primary key data file CounterID.bin is written as CounterID, and the event date of the event date file EventDate.bin is written as EventDate.

[0127] Step 212: construct or update the secondary index under the partition directory according to the data aggregation information under the partition directory, and store the secondary index in the secondary index file under the partition directory where it is located.

[0128] The secondary index is constructed from the aggregate information of the data. The content of the aggregate information varies depending on the index type. The secondary index can reduce the scope of data scanning during query. Like the primary index, if a secondary index is declared, the corresponding index and marker files (skp_idx_[column].idx and skp_idx_[column].mrk) will be generated additionally. The data storage engine for the online analytical processing database supports the creation of common indexes. The index type and field need to be specified when creating the table. The secondary indexes that can be added are BloomFilter Index and inverted index Minmax Index.

[0129] In order to further improve the effectiveness of data storage for an online analytical processing database and to further improve the data reading efficiency, in a data storage method for an online analytical processing database provided in an embodiment of the present application, the multi-level index includes a primary index and a secondary index; see Figure 5 , step 220 in the data storage method for the online analytical processing database specifically includes the following contents:

[0130] Step 221: Generate corresponding data tags according to the offset information of each compressed data block in the data file and the offset information of each compressed data block after decompression, and align the data tags with the index interval of the primary index, thereby generating a primary data tag file that corresponds one-to-one with the data file and is used to associate the data file and the primary index.

[0131] Step 222: Generate corresponding data tags according to the offset information of each compressed data block in the data file and the offset information of each compressed data block after decompression, and align the data tags with the index interval of the secondary index, thereby generating a secondary data tag file that corresponds one-to-one with the data file and is used to associate the data file and the secondary index.

[0132] Specifically, in order to connect the primary index and data, the data storage engine for the online analytical processing database uses the data marker .mrk to establish an association between primary.idx and .bin. The data marker and index interval are aligned, and both are spaced according to the granularity of index_granularity. The data marker file also corresponds to the .bin file one by one. Each column field [Column].bin file has a corresponding [Column].mrk data marker file, which is used to record the offset information of the data in the data file.

[0133] A line of tag data is represented by a tuple, which contains two integer offset information, which respectively represent the starting offset of the compressed data block in the corresponding .bin compressed file within this data interval; and the starting offset of the decompressed block after the data block is decompressed. Tag data does not need to reside in memory, but uses the LRU (least recently used) cache strategy to speed up its access. Fig. 9 It is a schematic diagram of the labeled data in the data label file.

[0134] Based on the above example, data labeling works as follows:

[0135] (1) Reading compressed data blocks

[0136] In the .mrk file, the cutoff offset of the 0th compressed data block is 12016, which includes the header information (fixed 9 bytes, 8 bytes after compression), so the storage engine only needs to load the specific compressed data block into the memory, without loading the entire .bin file at one time. After the compressed data block is loaded into the memory, it will be decompressed, and then the specific data will be read.

[0137] (2) Reading data

[0138] When reading decompressed data, the data storage engine for the OLAP database does not need to scan the entire decompressed data at once. It will load a specific small segment at the index_granularity granularity as needed. To achieve this feature, it is necessary to use the offset in the decompressed data block stored in the marker file. The starting offsets of the two adjacent decompressed data blocks above and below constitute the offset interval for obtaining the data corresponding to the current marker. Through this interval, data can be read on demand according to the offset after its compressed block is decompressed, such as Fig.10 As shown, the first data fragment in the compressed data block 0 can be read through [0,8192].

[0139] See also Fig.11 The overall query process of using the data storage engine for online analytical processing database to query data is as follows: the data storage engine for online analytical processing database first queries the partition index, the primary index and the secondary index in turn to minimize the data scanning range, and then uses data tags to minimize the data range that needs to be decompressed and calculated, and finally obtains the data with the smallest range through layer-by-layer filtering.

[0140] For a query, if an index (primary index, secondary index, partition index) is matched, the data storage engine for the online analytical processing database can be used to minimize the range of data that needs to be decompressed and calculated.

[0141] The data storage method for the online analytical processing database provided in the embodiment of the present application adopts a multi-level index and data tag method, and an index can be created in any column. The data reading granularity is a data block, which ensures that the amount of data loaded into the memory is reduced to a minimum, which is conducive to improving computing efficiency and resource utilization. That is: by adopting a data storage solution composed of partitioning, blocking, multi-level indexing and data tagging, the amount of data that needs to be loaded when the online analytical processing database OLAP is performing online analysis can be effectively reduced, thereby improving the software operation stability and query speed.

[0142] From the software level, the present application also provides a data storage engine for an online analytical processing database for executing all or part of the data storage method for an online analytical processing database, see Fig.12 , the data storage engine for the online analytical processing database specifically includes the following contents:

[0143] The data storage module 10 is used to store the original data to be stored in a data table of the online analytical processing database in a partitioned, block-compressed and column-stored manner, so that the data reading granularity unit of the online analytical processing database is a compressed data block;

[0144] The index generation module 20 is used to construct or update the current partition index of the data table, the multi-level index under each partition directory and the corresponding data tags, so as to search for target data in the online analytical processing database based on the partition index, multi-level index and data tags in sequence.

[0145] The embodiment of the data storage engine for an online analytical processing database provided in the present application can be specifically used to execute the processing flow of the embodiment of the data storage method for an online analytical processing database in the above-mentioned embodiment. Its functions are not repeated here, and reference can be made to the detailed description of the embodiment of the data storage method for an online analytical processing database.

[0146] The data storage engine for the online analytical processing database can be executed in the server or in the client device. The selection can be made based on the processing capability of the client device and the limitations of the user's usage scenario. This application does not limit this. If all operations are completed in the client device, the client device may also include a processor for specific processing of the data storage of the online analytical processing database.

[0147] The client device may have a communication module (i.e., a communication unit) that can communicate with a remote server to achieve data transmission with the server. The server may include a server on the task scheduling center side, and other implementation scenarios may also include a server on an intermediate platform, such as a server on a third-party server platform that has a communication link with the task scheduling center server. The server may include a single computer device, or a server cluster consisting of multiple servers, or a server structure of a distributed device.

[0148] The server and the client device may communicate with each other using any suitable network protocol, including network protocols that have not yet been developed on the date of filing this application. The network protocols may include, for example, TCP / IP, UDP / IP, HTTP, HTTPS, etc. Of course, the network protocols may also include, for example, RPC (Remote Procedure Call Protocol) and REST (Representational State Transfer) protocols used on top of the above protocols.

[0149] From the above description, it can be seen that the data storage engine for the online analytical processing database provided in the embodiment of the present application can reduce the data reading granularity of the online analytical processing database to the compressed data block level by adopting partitioning, blocking, multi-level indexing and columnar storage. By searching the index, only the required data can be loaded into the memory, thereby effectively narrowing the scope of data reading, reducing the amount of data loaded into the memory, and effectively improving the data reading efficiency and reliability, thereby improving the running stability and query speed of the online analytical processing database software.

[0150] The present application also provides an electronic device, which may include a processor, a memory, a receiver, and a transmitter, wherein the processor is used to execute the data storage method for the online analytical processing database mentioned in the above embodiment, wherein the processor and the memory may be connected via a bus or other means, such as by bus connection. The receiver may be connected to the processor and the memory via wired or wireless means.

[0151] The processor may be a central processing unit (CPU). The processor may also be other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), field-programmable gate arrays (FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, or a combination of the above chips.

[0152] As a non-transitory computer-readable storage medium, the memory can be used to store non-transitory software programs, non-transitory computer executable programs and modules, such as the program instructions / modules corresponding to the data storage method for the online analytical processing database in the embodiment of the present application. The processor executes various functional applications and data processing of the processor by running the non-transitory software programs, instructions and modules stored in the memory, that is, the data storage method for the online analytical processing database in the above method embodiment is implemented.

[0153] The memory may include a program storage area and a data storage area, wherein the program storage area may store the operating system, the application programs required for the function; the data storage area may store data created by the processor, etc. In addition, the memory may include a high-speed random access memory, and may also include a non-transient memory, such as a disk storage device, a flash memory device, or other non-transient solid-state storage device. In some embodiments, the memory may optionally include a memory remotely arranged relative to the processor, and these remote memories may be connected to the processor via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0154] The one or more modules are stored in the memory, and when executed by the processor, perform the data storage method for the online analytical processing database in the embodiment.

[0155] In some embodiments of the present application, the user equipment may include a processor, a memory, and a transceiver unit, which may include a receiver and a transmitter. The processor, memory, receiver, and transmitter may be connected through a bus system. The memory is used to store computer instructions, and the processor is used to execute the computer instructions stored in the memory to control the transceiver unit to send and receive signals.

[0156] As an implementation method, the functions of the receiver and the transmitter in the present application can be considered to be implemented through a transceiver circuit or a dedicated chip for transceiver, and the processor can be considered to be implemented through a dedicated processing chip, a processing circuit or a general chip.

[0157] As another implementation method, it is possible to use a general-purpose computer to implement the server provided in the embodiment of the present application, that is, to store the program code for implementing the functions of the processor, receiver, and transmitter in a memory, and the general-purpose processor implements the functions of the processor, receiver, and transmitter by executing the code in the memory.

[0158] The embodiment of the present application also provides a computer-readable storage medium, on which a computer program is stored, and when the computer program is executed by a processor, the steps of the data storage method for the online analytical processing database are implemented. The computer-readable storage medium can be a tangible storage medium, such as a random access memory (RAM), a memory, a read-only memory (ROM), an electrically programmable ROM, an electrically erasable programmable ROM, a register, a floppy disk, a hard disk, a removable storage disk, a CD-ROM, or any other form of storage medium known in the technical field.

[0159] It should be understood by those skilled in the art that the exemplary components, systems and methods described in conjunction with the embodiments disclosed herein can be implemented in hardware, software or a combination of the two. Whether it is specifically performed in hardware or software depends on the specific application and design constraints of the technical solution. Professional and technical personnel can use different methods to implement the described functions for each specific application, but such implementation should not be considered to be beyond the scope of this application. When implemented in hardware, it can be, for example, an electronic circuit, an application specific integrated circuit (ASIC), appropriate firmware, a plug-in, a function card, etc. When implemented in software, the elements of this application are programs or code segments used to perform the required tasks. The program or code segment can be stored in a machine-readable medium, or transmitted on a transmission medium or a communication link via a data signal carried in a carrier.

[0160] It should be clear that the present application is not limited to the specific configuration and processing described above and shown in the figures. For the sake of simplicity, a detailed description of the known method is omitted here. In the above embodiments, several specific steps are described and shown as examples. However, the method process of the present application is not limited to the specific steps described and shown, and those skilled in the art can make various changes, modifications and additions, or change the order between the steps after understanding the spirit of the present application.

[0161] In this application, the features described and / or illustrated for one embodiment may be used in the same manner or in a similar manner in one or more other embodiments, and / or combined with or replace the features of other embodiments.

[0162] The above description is only the preferred embodiment of the present application and is not intended to limit the present application. For those skilled in the art, the embodiments of the present application may have various modifications and variations. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present application shall be included in the protection scope of the present application.

Claims

1. A data storage method for an online analytical processing database, characterized in that: include: The original data to be stored is stored in a data table of an online analytical processing database in a partitioned, block-compressed and column-stored manner, so that the data reading granularity unit of the online analytical processing database is a compressed data block; The current partition index of the data table, the multi-level index under each partition directory and the corresponding data tags are constructed or updated to search for target data in the online analytical processing database based on the partition index, the multi-level index and the data tags in sequence.

2. The data storage method for an online analytical processing database according to claim 1, characterized in that: The storing of the original data to be stored in a data table of an online analytical processing database in a partitioned, block-compressed and column-stored manner, so that the data reading granularity unit of the online analytical processing database is a compressed data block, includes: Generate each compressed data block corresponding to the original data to be stored; storing each of the compressed data blocks in a data file in an online analytical processing database so that the data reading granularity unit of the online analytical processing database is a compressed data block, wherein the data file is one of each basic file stored in a column storage manner under a partition directory of a data table in the online analytical processing database; The data files in the same partition directory are periodically merged.

3. The data storage method for an online analytical processing database according to claim 2, characterized in that: The step of constructing or updating the current partition index of the data table, the multi-level index under each partition directory, and the corresponding data tags, so as to sequentially search for target data in the online analytical processing database based on the partition index, the multi-level index, and the data tags, includes: Build or update the current partition index in the data table and the multi-level index under each partition directory; Generate a data marker file for associating data files and multi-level indexes, so as to search for a target compressed data block in the data table of the online analytical processing database based on the partition index and the multi-level index, load the target compressed data block into the memory and then decompress it, and then extract the target data from the decompressed data based on the data marker file corresponding to the target compressed data block.

4. The data storage method for an online analytical processing database according to claim 3, characterized in that: The generating of compressed data blocks corresponding to the original data to be stored includes: Acquire data in batches from the original data at a preset index granularity to obtain each single batch of data; Based on a preset compression algorithm, each of the single batches of data is compressed to obtain compressed data corresponding to each of the single batches of data; Generating header information corresponding to each single batch of data respectively according to the type of the compression algorithm, the data size of each single batch of data and the data size of the corresponding compressed data; Based on the header information and compressed data corresponding to each of the single batches of data, compressed data blocks corresponding to each of the single batches of data are generated respectively.

5. The data storage method for an online analytical processing database according to claim 4, characterized in that: The multi-level index includes a primary index and a secondary index; Correspondingly, the construction or updating of the current partition index in the data table and the multi-level index under each partition directory includes: When a primary key is set in the data table, a primary index is constructed or updated for each partition directory of the data table at intervals of the index granularity, and the primary index is stored in a primary index file under the partition directory where the primary index is located, wherein the primary index is a sparse index; A secondary index under the partition directory is constructed or updated according to the data aggregation information under the partition directory, and the secondary index is stored in a secondary index file under the partition directory where the secondary index is located.

6. The data storage method for an online analytical processing database according to claim 5, characterized in that: The step of generating a data tag file for associating data files with multi-level indexes includes: Generate corresponding data tags according to the offset information of each compressed data block in the data file and the offset information of each compressed data block after decompression, and align the data tags with the index interval of the primary index, thereby generating a primary data tag file that corresponds one-to-one with the data file and is used to associate the data file with the primary index; Furthermore, corresponding data tags are generated according to the offset information of each compressed data block in the data file and the offset information of each compressed data block after decompression, and the data tags are aligned with the index interval of the secondary index, thereby generating a secondary data tag file that corresponds one-to-one with the data file and is used to associate the data file and the secondary index.

7. The data storage method for an online analytical processing database according to any one of claims 2 to 6, characterized in that: The basic files stored in the column storage mode under the partition directory of the data table in the online analytical processing database include: Verification file, which uses binary format to store data and is used to save the size of other files in the partition directory where it is located and the corresponding hash value to verify other files in the partition directory where it is located; Column information files use plain text format to store data and are used to save column field information under the partition directory where they are located; Count files use plain text format to store data and are used to record the total number of data rows in the partition directory where they are located; The primary index file uses binary format to store data and is used to store the sparse index as the primary index in the multi-level index; Data files use compressed format to store data, and each column field corresponds to an independent data file; And, a first-level data marker file stores data in binary format and is used to save the offset information of the data in the data file; each column field has a corresponding first-level data marker file, and the first-level data marker file is aligned with the sparse index and has a one-to-one correspondence with the data file to store the mapping relationship between the first-level index and the data file.

8. The data storage method for an online analytical processing database according to claim 7, characterized in that: The partition directory also includes partition files and secondary files stored in a column-based storage manner, wherein the partition files are used to store partition index files and the minimum and maximum values ​​of the original data; The secondary files include: A secondary index file is used to store a secondary index in a multi-level index, where the secondary index is constructed by the aggregated information of the data stored in the partition directory where the secondary index is located; And, a secondary data marker file stores data in binary format and is used to save the offset information of the data in the data file. The secondary data marker file is aligned with the secondary index and corresponds one-to-one with the data file to store the mapping relationship between the secondary index and the data file.

9. A data storage engine for an online analytical processing database, characterized in that: include: A data storage module, used for storing the original data to be stored in a data table of an online analytical processing database in a partitioned, block-compressed and column-stored manner, so that the data reading granularity unit of the online analytical processing database is a compressed data block; The index generation module is used to construct or update the current partition index of the data table, the multi-level index under each partition directory and the corresponding data tags, so as to search for target data in the online analytical processing database based on the partition index, the multi-level index and the data tags in turn.

10. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the computer program, the data storage method for an online analytical processing database is implemented as described in any one of claims 1 to 8.

Images