Method for realizing column storage based on row relational database

Abstract

The invention discloses a method for realizing column storage based on a row relational database, and aims to solve the problems that an existing column storage realization mode is high in development cost and an existing framework of the row database is difficult to fully utilize. According to the method, by means of the characteristic that the BLOB data type in a row relational database supports big data block storage, column storage of time series data is achieved by adding the steps of incremental row-column conversion and data compression and decompression. According to the method, the development difficulty and cost are greatly reduced, the method is particularly suitable for a time sequence database scene in the industrial field, and the data storage and query efficiency is effectively improved.

Description

Technical Field

[0001] The present invention relates to the technical field of database storage, and in particular to a method for implementing columnar storage based on a row-based relational database, which is particularly applicable to the application scenario of time series databases in the industrial field. Background Art

[0002] In the field of database storage, column-based storage and traditional row-based storage are two important data organization methods. Row-based storage stores each row of data in a table as a whole continuously, while column-based storage stores each column of data in a table separately and continuously.

[0003] There are significant differences between row-based storage and column-based storage in terms of data storage structure. During batch loading, columnar storage has a high compression ratio and less disk I / O volume, but row-based storage has a strong parallel loading ability; during data update, it is more convenient to modify a single record in row-based storage, and the cost of column-based storage is high; during data reading, columnar storage can accurately read relevant columns, and row-based storage is likely to read unnecessary columns; during point query, row-based storage has high efficiency with the help of B+ tree index, and the index granularity of column-based storage is coarse.

[0004] The Internet of Things and sensors generate a large amount of time series data. Although columnar storage databases can effectively process it, currently there is a lack of a method for implementing columnar storage based on a row-based relational database to reuse the existing resources of row-based databases, reduce the development cost, and meet the requirements of specific application scenarios such as time series databases in the industrial field. At the same time, time series data is different from traditional relational data in terms of identification, relationship, growth trend, and update operations, which further highlights the urgency of this technology. Summary of the Invention

[0005] To overcome the deficiencies of the prior art, the present invention provides an embodiment, a method for implementing columnar storage based on a row-based relational database, including: The application initiates an insert or data import operation from the data access layer through the insert statement. This step is the same as that of the row-based database. When creating a table, it is specified as a time-series storage table; for data insertion into the time-series storage table, incremental data is stored in the form of an in-memory table in the data buffer of the database, stored in pages of the in-memory table in row format, and these pages are marked as write pages. The row-column conversion module fetches data from the in-memory table page by page, fetches non-overwriteable write pages, performs row-column conversion, and caches data by column. The processed pages will be modified to be overwriteable in the in-memory table. When the row-to-column data reaches a certain threshold, sparse index key values will be generated according to the time-series time range of the data batch for querying and locating the data of the batch; after reaching the threshold batch, the compression module compresses the data by column, and different compression methods are adopted according to the data types and characteristics of different columns; the compressed data is written into a BLOB field, and individual BLOB fields can be written by column, or certain rules can be set to write multiple columns into one BLOB field in column order and locate by offset;

[0006] In one embodiment, the incremental row-column conversion steps are as follows: When data is inserted, the application initiates an insert or data import operation from the data access layer through the insert statement, specifies that the table creation is a time-series storage table, and the data is stored in the in-memory table pages of the database data buffer in row format, and the pages are marked as write pages; the module fetches non-overwriteable write page data from the in-memory table for row-column conversion and caches data by column; when the row-to-column data reaches a certain threshold, sparse index key values are generated according to the time-series time range of the data batch.

[0007] In one embodiment, the data compression and decompression working steps are as follows: In the data insertion process, after reaching the threshold batch, appropriate compression methods are selected according to the data types and characteristics of different columns to compress the column data, and the compressed data is written into a BLOB field. Individual BLOB fields can be written by column or multiple columns can be written into one BLOB field in column order according to rules and different column data can be located by offset; in the query process, corresponding decompression algorithms are selected according to the compression methods adopted by different columns to decompress the data read from the BLOB field.

[0008] In one embodiment, the sparse index key values are used to quickly locate the data of a specific batch during query, improving the query efficiency.

[0009] In one embodiment, for the compression of numerical data, the run-length encoding (RLE) or the method combining Delta encoding and run-length encoding is adopted, and for text data, Huffman encoding is used for compression.

[0010] In one embodiment, the insertion process of the method includes: data insertion and caching: The application initiates an insertion or import operation, and the data is stored in the memory table page in row format, and the page is marked as a write page; row-column conversion and index generation: The row-column conversion module obtains the data of the write page for conversion and caching, and generates a sparse index key value when reaching the threshold; data compression: The compression module compresses the column data; storing the data into the BLOB field: The compressed data is written into the BLOB field.

[0011] In one embodiment, the query process of the method includes: index positioning: The query corresponds to the sparse index key value according to the time series time condition, locates the BLOB storage location for reading; data decompression: The decompression module decompresses the read compressed data; column-to-row operation: The decompressed column data is subjected to a column-to-row operation and written to the memory table by page, and the page is marked as a non-write page; result return: The query interface matches the qualified rows from the memory table according to the query conditions and returns the results.

[0012] In one embodiment, the method does not require a dedicated design of the columnar storage disk format and file processing mechanism, and reuses the existing code and capabilities of the row-based relational database. In one embodiment, the method is particularly applicable to the time series database scenario in the industrial field and can efficiently store and query data with time series characteristics.

[0013] In one embodiment, the BLOB data type is a variable-length binary large object with a maximum length of up to 4GB, stores unstructured data with a format, is not affected during database character set conversion, and the database does not need to care about its specific stored content.

[0014] The method for implementing columnar storage based on a row-based relational database provided by the above embodiments has the following beneficial effects: reducing the development cost and difficulty. One of the greatest advantages of the present invention is that it does not require a dedicated design of a complex columnar storage disk format and file processing mechanism. By fully reusing the existing code and capabilities of the row-based relational database, the development workload and technical difficulty required to implement the columnar storage function are greatly reduced. This means that when an enterprise introduces the columnar storage function, it can avoid investing a large amount of manpower and material resources in new development, reducing the development cost and development cycle, and improving the implementation efficiency of the project. The present invention is also applicable to the time series database scenario: The method is specifically designed for the time series database scenario and can well meet the storage and query requirements of time series data. In the industrial field, a large amount of device operation data, sensor monitoring data, etc. have time series characteristics, and these data usually have the characteristics of large data volume, fast growth rate, and rarely updated. The method of the present invention can quickly store and query these time series data through efficient row-column conversion, data compression, and timestamp-based index mechanism, providing strong support for industrial production monitoring, fault prediction and other applications. Brief Description of the Drawings

[0015] In order to more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the following will briefly introduce the drawings required for use in the description of the embodiments or the prior art. Obviously, the drawings in the following description are only some embodiments of the present invention. For those of ordinary skill in the art, without creative efforts, other drawings can be obtained based on the structures shown in these drawings.

[0016] Figure 1 It is a flowchart of a method for realizing columnar storage based on a row-based relational database provided by the first embodiment of the present invention;

[0017] Figure 2 It is a working principle diagram of the method for realizing columnar storage based on a row-based relational database provided by the first embodiment of the present invention. Detailed Embodiments

[0018] The following will clearly and completely describe the technical solutions in the embodiments of the present invention in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are only some embodiments of the present invention, rather than all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those of ordinary skill in the art without creative efforts belong to the scope of protection of the present invention.

[0019] It should be noted that if there are directional indications (such as up, down, left, right, front, back...) involved in the embodiments of the present invention, the directional indications are only used to explain the relative positional relationship and movement conditions between components in a specific posture. If the specific posture changes, the directional indications will also change accordingly.

[0020] In addition, if there are descriptions involving "first", "second", etc. in the embodiments of the present invention, the descriptions of "first", "second", etc. are only for descriptive purposes and cannot be understood as indicating or implying their relative importance or implicitly indicating the quantity of the indicated technical features. Thus, the features defined with "first" and "second" may explicitly or implicitly include at least one of the features. In addition, if "and / or" or "and / or" appears throughout the text, its meaning includes three parallel solutions. Taking "A and / or B" as an example, it includes solution A, solution B, or a solution where A and B are satisfied simultaneously. In addition, the technical solutions between various embodiments can be combined with each other, but it must be based on the fact that those of ordinary skill in the art can implement them. When the combination of technical solutions results in contradictions or cannot be implemented, it should be considered that such a combination of technical solutions does not exist and is not within the scope of protection required by the present invention.

[0021] Embodiment 1

[0022] Reference Figure 1 - Figure 2 Figure 1 - Figure 2 , to solve the above problems, the core objective of the present invention is to provide an innovative method. Based on the existing row-based relational database, it ingeniously utilizes the characteristics of the BLOB (Binary Large Object) data type that supports large data block storage to achieve efficient columnar storage of time series data. Through this method, it aims to solve the problems of high development difficulty and high cost in the existing columnar storage implementation methods, and at the same time meet the special requirements of the industrial field time series database in terms of data storage and query, and improve the overall performance and applicability of the database system.

[0023] In the field of database storage, columnar storage and traditional row-based storage are two important data organization methods. Row-based storage stores each row of data in a table as a whole continuously, while columnar storage stores each column of data in a table separately and continuously. Under row-based storage, each row of data in a table is closely connected, while columnar storage splits each row of data by column and stores them separately. Columnar storage has a higher compression ratio and can effectively reduce the disk I / O volume, but it is not as good as columnar storage in terms of compression and I / O optimization. When updating data, the advantage of row-based storage is obvious, and it is relatively easy to modify a single record. However, since columnar storage stores data by column, modifying a single record may require multiple I / O accesses, which is costly. In the data reading scenario, the advantage of columnar storage is prominent. It can accurately read only the relevant column data, avoid reading unnecessary information, and reduce the data processing volume; row-based storage may read in unnecessary columns, increasing the data processing burden. In point queries, row-based storage can directly locate the row with the help of a B+ tree index, and the query efficiency is relatively high; the index granularity of columnar storage is coarser, usually locating to the column unit. In terms of data compression, the data types of the same column in columnar storage are the same and there are many duplicate values, so the compression algorithm has a good effect and a high compression ratio; the repetition rate between different records in row-based storage is low, and the compression effect is relatively low. Currently, a new method for implementing columnar storage based on a row-based relational database is needed, which can reuse the existing resources of the row-based database to the greatest extent, implement the columnar storage function with a small development cost, and meet the requirements of specific application scenarios, especially the industrial field time series database.

[0024] This method solves the problem of how to implement columnar storage in a row-based relational database by introducing the concept of a time-series storage table. The specific technical features include: the application initiates insert or data import operations from the data access layer through the insert statement, and specifies it as a time-series storage table when creating the table; when data is inserted, the incremental data is stored in the pages of the in-memory table in row format, and the pages are marked as written pages; the row-column conversion module fetches the non-overwriteable written page data from the in-memory table by page for row-column conversion, and caches the data by column, and the processed pages are modified to be overwriteable in the in-memory table; when the row-to-column data reaches a certain threshold, sparse index key values are generated based on the time sequence range of the data batches; after reaching the threshold batch, the compression module compresses the data by column, and adopts different compression methods according to the data types and characteristics of different columns; the compressed data is written into the BLOB field, which can write a separate BLOB field by column, or write multiple columns into a BLOB field in column order according to the rules, and locate through the offset.

[0025] Current row-based relational databases have certain limitations in handling columnar storage. To break through these limitations, the present invention proposes a new method, which realizes effective columnar storage in a row-based relational database by introducing the concept of a time-series storage table. Specifically, the application initiates insert or data import operations from the data access layer through the insert statement, and specifies it as a time-series storage table when creating the table. When data is inserted, the incremental data is stored in the pages of the in-memory table in row format, and the pages are marked as written pages. The row-column conversion module fetches the non-overwriteable written page data from the in-memory table by page for row-column conversion, and caches the data by column, and the processed pages are modified to be overwriteable in the in-memory table. When the row-to-column data reaches a certain threshold, sparse index key values are generated based on the time sequence range of the data batches. After reaching the threshold batch, the compression module compresses the data by column, and adopts different compression methods according to the data types and characteristics of different columns. The compressed data is written into the BLOB field, which can write a separate BLOB field by column, or write multiple columns into a BLOB field in column order according to the rules, and locate through the offset.

[0026] In the implementation process, the key terms include time-series storage table, in-memory table, row-column conversion module, sparse index key value, compression module, and BLOB field. A time-series storage table refers to a table specified for storing time-series data when creating the table. The in-memory table is used to cache incremental data and is stored in pages in row format. The row-column conversion module is used to fetch data from the in-memory table by page and perform row-column conversion. Sparse index key values are used to quickly locate specific batch data during query. The compression module selects appropriate compression methods according to the data types and characteristics of different columns to compress the column data. The BLOB field is used to store the compressed data.

[0027] The application initiates insert or data import operations from the data access layer through the insert statement, and specifies a time-series storage table when creating the table. When data is inserted, the incremental data is stored in pages of the memory table in row format, and the pages are marked as written pages. The row-column conversion module obtains non-overwriteable written page data from the memory table by page for row-column conversion, caches the data by column, and modifies the processed pages in the memory table to be overwriteable. When the row-to-column data reaches a certain threshold, sparse index key values are generated based on the time series time range of the data batches. After reaching the threshold batch, the compression module compresses the data by column and adopts different compression methods according to the data types and characteristics of different columns. The compressed data is written into the BLOB field, and a separate BLOB field can be written by column, or multiple columns can be written into a BLOB field in column order according to the rules and located through offsets.

[0028] Compared with the prior art, the method of the present invention solves the problem of how to implement columnar storage in a row-based relational database by introducing the concept of a time-series storage table. The specific technical features include that the application initiates insert or data import operations from the data access layer through the insert statement, and specifies a time-series storage table when creating the table. When data is inserted, the incremental data is stored in pages of the memory table in row format, and the pages are marked as written pages. The row-column conversion module obtains non-overwriteable written page data from the memory table by page for row-column conversion, caches the data by column, and modifies the processed pages in the memory table to be overwriteable. When the row-to-column data reaches a certain threshold, sparse index key values are generated based on the time series time range of the data batches. After reaching the threshold batch, the compression module compresses the data by column and adopts different compression methods according to the data types and characteristics of different columns. The compressed data is written into the BLOB field, and a separate BLOB field can be written by column, or multiple columns can be written into a BLOB field in column order according to the rules and located through offsets.

[0029] This method solves the problem of how to implement columnar storage in a row-based relational database by introducing the concept of a time-series storage table in the row-based relational database. The specific technical features include that the application initiates an insert or data import operation from the data access layer through the insert statement, and specifies it as a time-series storage table when creating the table. When data is inserted, the incremental data is stored in pages of the in-memory table in row format, and the pages are marked as written pages. The row-column conversion module obtains non-overwriteable written page data from the in-memory table page by page for row-column conversion, and caches the data by column. The processed pages are modified to be overwriteable in the in-memory table. When the row-to-column data reaches a certain threshold, sparse index key values are generated based on the time sequence range of the data batches. After reaching the threshold batch, the compression module compresses the data by column, and different compression methods are adopted according to the data types and characteristics of different columns. The compressed data is written into the BLOB field, and a separate BLOB field can be written by column, or multiple columns can be written into a BLOB field in column order according to the rules, and positioned through the offset. Through the mutual cooperation of the above technical features, effective columnar storage is achieved in the row-based relational database, and the technical problem of how to implement columnar storage in the row-based relational database is solved.

[0030] Further, this application also proposes that when data is inserted, the application initiates an insert or data import operation from the data access layer through the insert statement, the data is stored in pages of the in-memory table in row format, and the pages are marked as written pages; the module obtains non-overwriteable written page data from the in-memory table page by page for row-column conversion, and caches the data by column; when the row-to-column data reaches a certain threshold, sparse index key values are generated based on the time sequence range of the data batches.

[0031] When data is inserted, the application initiates an insert or data import operation from the data access layer through the insert statement, the data is stored in pages of the in-memory table in row format, and the pages are marked as written pages; the module obtains non-overwriteable written page data from the in-memory table page by page for row-column conversion, and caches the data by column; when the row-to-column data reaches a certain threshold, sparse index key values are generated based on the time sequence range of the data batches. Through these technical features, row-column conversion and generation of sparse index key values can be achieved during the data insertion process, thereby improving data processing efficiency and query efficiency.

[0032] Among them, the data is stored in the memory table page in row format, and the page is marked as a write page, indicating that the data is stored in a specific page of the memory table in row form when inserted, and these pages are marked as write pages for subsequent modules to identify. The module fetches the non-overwriteable write page data from the memory table by page for row-column conversion and caches the data by column. Further, the module extracts data from these write pages, converts the row-formatted data into column-formatted data, and caches it by column. This allows for generating sparse index key values based on the time sequence time range of data batches after the data reaches a certain amount, which is used for fast querying and locating data.

[0033] Specifically, the row-column conversion module can be implemented in various ways. For example, the module can use a temporary storage structure in memory to cache the converted column data and write this data to persistent storage after reaching a certain threshold. At the same time, different strategies can be selected for the process of generating sparse index key values according to the specific application scenario. For example, index key values can be generated based on the range of timestamps to quickly locate relevant data during querying.

[0034] Thus, through the technical means of performing row-column conversion and generating sparse index key values during data insertion, this application solves the technical problem of how to perform row-column conversion and generate sparse index key values during data insertion. Compared with the prior art, this application can achieve efficient row-column conversion and sparse index key value generation during the data insertion process, thereby improving data processing efficiency and query efficiency, and is particularly suitable for application scenarios that need to process a large amount of time-series data.

[0035] Furthermore, this application also proposes that in the data insertion process, after reaching the threshold batch, the compression module selects an appropriate compression method according to the data types and characteristics of different columns to compress the column data, and the compressed data is written into the BLOB field; in the query process, the decompression module selects the corresponding decompression algorithm according to the compression method used for different columns to decompress the data read from the BLOB field. In the data insertion process, when the data insertion volume reaches a certain threshold batch, the compression module will select the most suitable compression method according to the data type and characteristics of each column to compress the column data. The compressed data will be written into the BLOB field. The purpose of data compression is to reduce the occupancy of storage space, thereby improving storage efficiency. In the query process, the decompression module will select the corresponding decompression algorithm according to the compression method used for each column during compression to decompress the data read from the BLOB field. This can ensure that the data can be correctly and efficiently decompressed during querying, thereby improving query efficiency.

[0036] The compression module can select different compression methods according to different data types and characteristics. For example, for numerical data, the run-length encoding (RLE) or a method combining Delta encoding and run-length encoding can be used for compression; for text data, Huffman encoding can be used for compression. During the query process, the decompression module will select the corresponding decompression algorithm for decompression according to the data type and compression method of each column. For example, for numerical data compressed using run-length encoding, the decompression module will use the corresponding decompression algorithm for decompression; for text data compressed using Huffman encoding, the decompression module will use the Huffman decoding algorithm for decompression.

[0037] The technical solution of this application effectively solves the problem of how to efficiently compress and decompress data in columnar storage to improve storage and query efficiency by performing data compression and decompression respectively during data insertion and query processes. Compared with the prior art, the technical solution of this application can select the most suitable compression method and decompression algorithm according to the data types and characteristics of different columns, thereby ensuring the data compression rate while ensuring that the data can be correctly and efficiently decompressed during query, improving the overall efficiency of storage and query.

[0038] Furthermore, this application also proposes that sparse index key values are used to quickly locate specific batches of data during query, improving query efficiency. The technical feature of sparse index key values lies in their use for quickly locating specific batches of data. This feature can effectively locate the required data batches by utilizing sparse index key values during query, thereby accelerating the query speed and improving query efficiency. By using sparse index key values, specific batches of data can be quickly located during query, avoiding full table scans or row-by-row searches, and improving query efficiency. This method is particularly suitable for scenarios dealing with large amounts of time-series data and can significantly enhance the performance of data query.

[0039] The generation of sparse index key values is based on the time-series time range of data batches. When the row-to-column data reaches a certain threshold, sparse index key values are generated based on the time-series time range of data batches. The generation strategy of sparse index key values can be adjusted in real time according to the data insertion speed and data volume to optimize the index performance under different data volumes and operation loads. For example, in the case of a relatively fast data insertion speed or a large data volume, the generation strategy of sparse index key values will be dynamically adjusted to ensure the effectiveness of the index and query efficiency.

[0040] By using sparse index key values, the query efficiency can be significantly improved, especially in scenarios dealing with a large amount of time-series data. Compared with the prior art, the method proposed in this application avoids full-table scans or row-by-row lookups through the use of sparse index key values, thus accelerating the query speed and improving the query efficiency. In addition, the dynamic adjustment mechanism of sparse index key values can effectively optimize the index performance under different data volumes and operation loads, further enhancing the query performance and flexibility of the system. Therefore, the technical solution of this application has significant advantages in improving query efficiency and processing a large amount of time-series data.

[0041] Furthermore, this application also proposes to use run-length encoding (RLE) or a combination of Delta encoding and run-length encoding for compressing numerical data, and Huffman encoding for compressing text data. The technical features included in this application are: using run-length encoding (RLE) or a combination of Delta encoding and run-length encoding to compress numerical data, and using Huffman encoding to compress text data. These technical features play an important role in solving the problem of improving the compression efficiency of numerical and text data. The combination of run-length encoding (RLE) and Delta encoding with run-length encoding can effectively compress numerical data and reduce storage space. Huffman encoding can efficiently compress text data by constructing an optimal prefix code tree, further reducing the storage requirements.

[0042] By adopting run-length encoding (RLE) or a combination of Delta encoding and run-length encoding, numerical data can be effectively compressed, the compression efficiency can be improved, and the storage space can be reduced. For text data, using Huffman encoding can achieve efficient compression through the construction of an optimal prefix code tree, further improving the storage efficiency. These technical means achieve the efficient compression of numerical and text data by selecting the optimal compression method for different data types, and solve the technical problem of improving the compression efficiency.

[0043] Run-length encoding (RLE) is a simple and effective data compression method, especially suitable for compressing repetitive data. Delta encoding reduces data redundancy by recording the data change values and is suitable for processing data with continuous numerical changes. Run-length encoding achieves compression by recording the length of consecutive identical data. Combining Delta encoding with run-length encoding can achieve a more efficient compression effect when processing numerical data. Specifically, Delta encoding is used to convert the original data into a sequence of change values, and then run-length encoding is applied to this sequence to further reduce the data volume.

[0044] Huffman coding is a lossless data compression algorithm. By constructing an optimal prefix code tree, characters with high occurrence frequencies are represented by shorter codes, and characters with low occurrence frequencies are represented by longer codes, thus achieving efficient compression. Specifically, Huffman coding first counts the occurrence frequencies of each character in the text, then constructs a Huffman tree based on the frequencies, and finally generates a corresponding Huffman coding table for compressing text data.

[0045] Therefore, this application effectively compresses numerical data and reduces storage space by combining the methods of run-length encoding (RLE), Delta encoding, and run-length encoding. By adopting Huffman coding, it efficiently compresses text data and further improves the storage efficiency. Compared with the prior art, this application selects the optimal compression method for different data types, achieving efficient compression of numerical data and text data, and significantly improving the compression efficiency and storage utilization rate.

[0046] Furthermore, this application also proposes that the insertion process of the method includes: data insertion and caching: the application initiates an insertion or import operation, and the data is stored in the memory table page in row format, and the page is marked as a write page; row-column conversion and index generation: the row-column conversion module obtains the data of the write page for conversion and caching, and generates a sparse index key value when reaching the threshold; data compression: the compression module compresses the column data; data storage into the BLOB field: the compressed data is written into the BLOB field. The insertion process of this method includes four main steps. First, data insertion and caching, the application initiates an insertion or import operation, and the data is stored in the memory table page in row format, and the page is marked as a write page. This step ensures that the data is first stored in memory in row format. Second, row-column conversion and index generation, the row-column conversion module obtains the data of the write page for conversion and caching, and generates a sparse index key value when reaching the threshold. This step improves the data query efficiency through row-column conversion and generating index key values. Next, data compression, the compression module compresses the column data, and selects a suitable compression method according to the data types and characteristics of different columns to reduce the storage space. Finally, data storage into the BLOB field, the compressed data is written into the BLOB field to ensure efficient storage of the data in the compressed form. Through these steps, this method realizes efficient columnar storage in a row-based relational database system, and solves the problem of how to implement the columnar storage function in a row-based database.

[0047] In the steps of row-column conversion and index generation, the row-column conversion module fetches non-overwriteable written page data from the in-memory table page by page for row-column conversion and caches the data by column. The processed pages are modified to be overwriteable in the in-memory table. When the row-to-column data reaches a certain threshold, sparse index key values are generated based on the chronological time range of the data batches for querying and locating the data of the batches. In the data compression step, the compression module performs data compression by column and adopts different compression methods according to the data types and characteristics of different columns. The compressed data is written into the BLOB field. It can write a separate BLOB field by column, or write multiple columns into one BLOB field in column order according to the rules, and locate through the offset.

[0048] Through steps such as row-column conversion and index generation, data compression, and storing data into the BLOB field, this method realizes efficient columnar storage in a row-based relational database system. Specifically, by performing row-column conversion and generating sparse index key values, the data query efficiency is improved; by compressing the column data through the compression module, the storage space is reduced; by writing the compressed data into the BLOB field, it ensures that the data is efficiently stored in the compressed form. Thus, this method solves the technical problem of how to implement the columnar storage function in a system based on a row-based relational database and has significant technical advantages.

[0049] Furthermore, this application also proposes that the query process includes: Index positioning: The query corresponds to the sparse index key value according to the chronological time condition and locates the BLOB storage location for reading; Data decompression: The decompression module decompresses the read compressed data; Row-column conversion operation: The decompressed column data is subjected to a row-column conversion operation and written to the in-memory table page by page, and the page is marked as a non-written page; Result return: The query interface matches the rows that meet the conditions from the in-memory table according to the query conditions and returns the results. Index positioning corresponds to the sparse index key value through the chronological time condition, quickly locates the BLOB storage location, and realizes efficient reading; the data decompression module decompresses the read compressed data to restore the original data; the row-column conversion operation converts the decompressed column data into a row format and writes it to the non-written page of the in-memory table to ensure the integrity and consistency of the data; the result return module matches the query conditions from the in-memory table and returns the row data that meets the conditions. These technical features cooperate with each other to realize efficient positioning and reading of columnar storage data during the query process, improving the query efficiency and data processing performance.

[0050] Specifically, the index positioning module can reduce the scanning range during querying by establishing a sparse index, thereby improving the query efficiency. The data decompression module can adopt various decompression algorithms to adapt to different types of data and improve the decompression speed and accuracy. The row-column conversion operation module can accelerate the data conversion process through batch processing and parallel computing. The result return module can further improve the return speed of the query results by optimizing the query condition matching algorithm.

[0051] Thus, through technical features such as introducing sparse index, data decompression, column-row transformation operation and result return, the present application realizes efficient query of columnar storage data. This not only improves the data reading speed, but also ensures the integrity and consistency of data, and has significant advantages compared with the prior art.

[0052] Furthermore, the present application also proposes that the memory table page has a dual identification mechanism, including a written page identification and a non-written page identification. The written page identification is used to mark the data page undergoing column-row transformation operation, and the non-written page identification is used to mark the processed data page that has completed column-row transformation. The dual identification mechanism ensures that during the data transformation process, incremental data and processed data will not be confused, improving the efficiency of data processing.

[0053] The technical features include the dual identification mechanism of the memory table page, specifically including the written page identification and the non-written page identification. The written page identification marks the data page undergoing column-row transformation operation, and the non-written page identification marks the processed data page that has completed column-row transformation. These technical features cooperate with each other to ensure that during the data transformation process, incremental data and processed data will not be confused, thereby improving the efficiency of data processing. Through the dual identification mechanism of the memory table page, the problem of confusion between incremental data and processed data during the data transformation process is solved. The use of the written page identification and the non-written page identification clearly distinguishes the data page being processed from the processed data page, thus avoiding data confusion and improving the efficiency of data processing.

[0054] The implementation methods of the dual identification mechanism can include various forms. For example, the written page identification and the non-written page identification can be implemented through different flag bits, and the setting of the flag bits can be carried out in the metadata of the memory table page. Specifically, the written page identification can be represented by setting a specific flag bit to 1, while the non-written page identification can be represented by setting the flag bit to 0. Another implementation method is to distinguish the written page and the non-written page through different memory areas. The written page is stored in a dedicated memory area, while the non-written page is stored in another memory area. Furthermore, the written page and the non-written page can be identified by color coding. For example, the written page can be identified in red, while the non-written page can be identified in green.

[0055] The dual identification mechanism of this application has remarkable advantages. First of all, by clearly distinguishing between written pages and non-written pages, the confusion between incremental data and processed data during data conversion is avoided, thus improving the efficiency of data processing. Secondly, the implementation methods of the dual identification mechanism are flexible and diverse, and appropriate implementation methods can be selected according to specific application scenarios. In addition, this mechanism can also be seamlessly integrated with existing database systems, reducing the complexity of implementation. Compared with the prior art, the dual identification mechanism of this application has obvious advantages in terms of data processing efficiency and system integration.

[0056] The sparse index key values are dynamically adjusted according to the time range of the data timestamp and the data batch. During the insertion process, the sparse index key values are automatically generated according to the time range of the time-series data. If the data insertion speed is fast or the data volume is large, the generation strategy of the sparse index key values is adjusted in real time. The dynamic adjustment mechanism can effectively optimize the index performance under different data volumes and different operation loads.

[0057] The specific implementation method of dynamically adjusting the sparse index key values according to the time range of the data timestamp and the data batch includes the following steps: During the data insertion process, the sparse index key values are automatically generated according to the time range of the time-series data. If the data insertion speed is fast or the data volume is large, the generation strategy of the sparse index key values is adjusted in real time. The dynamic adjustment mechanism includes real-time monitoring of the data insertion speed and data volume, and adjusting the generation frequency and strategy of the sparse index key values according to the monitoring results. For example, when the data insertion speed exceeds the preset threshold, the system will increase the generation frequency of the sparse index key values to ensure the optimization of the index performance. As a preferred implementation manner, the generation strategy of the sparse index key values can be predicted and adjusted based on the insertion pattern of historical data, so as to better adapt to the dynamically changing data load.

[0058] This application solves the problems of the generation and adjustment of sparse index key values when processing time-series data by dynamically adjusting the sparse index key value generation mechanism. Compared with the prior art, the dynamic adjustment mechanism of this application can maintain the optimization of the index performance under different data volumes and operation loads, improving the query efficiency and the overall performance of the system. Specifically, this application can adjust the generation strategy of the sparse index key values in real time according to the changes in the data insertion speed and data volume, ensuring the stability and efficiency of the index performance. Thus, this application provides an efficient and flexible time-series data processing method, which can adapt to various data load situations and significantly improve the query efficiency and overall performance of the system.

[0059] In the field of database storage, columnar storage and traditional row-based storage are two important data organization methods. Columnar storage stores each column of data in a table continuously, with higher compression ratios and query efficiencies. Time series databases are specifically designed to handle data with time series characteristics and usually adopt columnar storage formats to improve data storage and query efficiencies. Currently, a new method for implementing columnar storage based on row-based relational databases is needed, which can reuse the existing resources of row-based databases to the greatest extent and achieve the columnar storage function at a relatively low development cost to meet specific application scenarios, especially the requirements of time series databases in the industrial field.

[0060] The technical solution of this application includes: during data insertion, the application initiates an insertion or data import operation from the data access layer through the insert statement. The data is stored in memory table pages in row format, and the page is marked as a write page. The module fetches non-overwriteable write page data from the memory table page by page for row-column conversion and caches the data by column. When the row-to-column data reaches a certain threshold, sparse index key values are generated based on the time range of the data batch for querying and locating the data of the batch. After reaching the threshold batch, the compression module compresses the data by column and adopts different compression methods according to the data types and characteristics of different columns. The compressed data is written into the BLOB field, and a separate BLOB field can be written by column, or multiple columns can be written into a BLOB field in column order according to rules, and located through offsets.

[0061] Furthermore, the memory table page has a dual identification mechanism, including a write page identification and a non-write page identification. The write page identification is used to mark the data page undergoing row-column conversion operations, and the non-write page identification is used to mark the processed data page that has completed row-column conversion. The dual identification mechanism ensures that during the data conversion process, incremental data and processed data will not be confused, improving the efficiency of data processing.

[0062] The sparse index key values are dynamically adjusted according to the time range of the data timestamp and the data batch. During the insertion process, the sparse index key values are automatically generated according to the time range of the time series data. If the data insertion speed is fast or the data volume is large, the generation strategy of the sparse index key values is adjusted in real time. The dynamic adjustment mechanism can effectively optimize the index performance under different data volumes and different operation loads.

[0063] By implementing columnar storage in a row-based relational database, this application can reuse existing resources to the greatest extent and achieve efficient data compression and query. Compared with the prior art, this application provides a flexible and efficient solution, especially suitable for time series data processing in the industrial field.

[0064] The above are only the preferred embodiments of the present invention, and do not thereby limit the patent scope of the present invention. Any equivalent structural transformation made by using the content of the specification and drawings of the present invention under the inventive concept of the present invention, or direct / indirect application in other related technical fields is included in the patent protection scope of the present invention.

Claims

1. A method for implementing column storage based on a row-based relational database, characterized in that: include: The application initiates an insert or data import operation from the data access layer through the insert statement, and specifies a time-series storage table when creating the table; For data insertion into a time-series memory table, incremental data is stored in the data cache of the database in the form of a memory table, and stored in the pages of the memory table in row format. These pages are marked as write pages. The row-column conversion module obtains data from the memory table by page, takes non-overwriteable write pages, performs row-column conversion, and caches data by column. The processed pages are modified to be overwriteable in the memory table; When the row-to-column data reaches a certain threshold, a sparse index key value is generated based on the time range of the data batch, which is used to query and locate the data in the data batch; After reaching the threshold batch, the compression module compresses data by column, using different compression methods according to the data type and characteristics of different columns; The compressed data is written to the BLOB field and located by offset.

2. The method for implementing column storage based on a row-based relational database according to claim 1, characterized in that: The incremental row and column conversion steps also include: When inserting data, the application initiates the data import operation from the data access layer through the insert statement. The data is stored in the memory table page in row format, and the page is marked as a write page. The module obtains non-overwriteable write page data from the memory table page by page, performs row-column conversion, and caches data by column; When the row-to-column data reaches the threshold, a sparse index key value is generated based on the time range of the data batch.

3. The method for implementing column storage based on a row-based relational database according to claim 1, characterized in that: The steps of data compression and decompression include: In the data insertion process, when the threshold batch is reached, the compression module selects the appropriate compression method to compress the column data according to the data type and characteristics of different columns, and writes the compressed data into the BLOB field; In the query process, the decompression module selects the corresponding decompression algorithm according to the compression method used by different columns to decompress the data read from the BLOB field.

4. The method for implementing column storage based on a row-based relational database according to claim 2, characterized in that: Sparse index key values ​​are used to quickly locate a specific batch of data during query.

5. The method for implementing column storage based on a row-based relational database according to claim 3, characterized in that: Run-length encoding is used to compress numerical data, and Huffman coding is used to compress text data.

6. The method for implementing column storage based on a row-based relational database according to claim 1, characterized in that: The insertion process of the method includes: Data insertion and caching: When an application initiates an insert or import operation, the data is stored in a memory table page in row format, and the page is marked as a write page. Row-column conversion and index generation: The row-column conversion module obtains the write page data for conversion and cache, and generates sparse index key values ​​when the threshold is reached; Data compression: The compression module compresses column data; Data is stored in a BLOB field: The compressed data is written to the BLOB field.

7. The method for implementing column storage based on a row-based relational database according to claim 1, characterized in that: The query process steps include: Index positioning: The query corresponds to the sparse index key value according to the time series time condition, and locates the BLOB storage location for reading; Data decompression: The decompression module decompresses the compressed data read; Column-to-row operation: The decompressed column data is converted into columns and rows and written to the memory table by page. The pages are marked as non-write pages. Result return: The query interface matches the rows that meet the query conditions from the memory table and returns the results.

8. The method for implementing column storage based on a row-based relational database according to claim 1, characterized in that: The memory table page has a dual identification mechanism, specifically including a write-in identification and a non-write-in identification: Write page identifier, used to mark the data page that is undergoing row-column conversion operation; The non-write page identifier is used to mark the processed data page that has completed the row-column conversion.

9. The method for implementing column storage based on a row-based relational database according to claim 1, characterized in that: The sparse index key value is dynamically adjusted based on the data timestamp and the time range of the data batch, specifically: During the insertion process, the sparse index key value is automatically generated according to the time range of the time series data, and the generation strategy of the sparse index key value is adjusted in real time.

10. A computer readable medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the method for implementing column storage based on a row-based relational database as claimed in claim 1 is implemented.

Images