A method for implementing columnar storage based on a row-oriented relational database

Abstract

The application discloses a method for realizing column storage based on a row type relational database, and aims to solve the problems of high development cost and difficulty in fully utilizing the existing architecture of a row database in the prior art column storage implementation mode. The method realizes column storage of time series data by means of the characteristics of the BLOB data type in the row type relational database supporting large data block storage and by increasing the steps of incremental row-column conversion and data compression and decompression, and greatly reduces the development difficulty and cost, and is especially suitable for the time series database scene in the industrial field, and effectively improves the data storage and query efficiency.

Description

Technical Field

[0001] This invention relates to the field of database storage technology, specifically to a method for implementing columnar storage based on row-oriented relational databases, which is particularly suitable for time-series database applications in the industrial field. Background Technology

[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, while column-based storage stores each column of data in a table separately.

[0003] Row-based and column-based storage differ significantly in their data storage structures. Column-based storage offers higher compression rates and lower disk I / O during batch loading, but row-based storage boasts stronger parallel loading capabilities. When updating data, row-based storage makes it easier to modify a single record, while column-based storage is more costly. When reading data, column-based storage can accurately read relevant columns, while row-based storage easily reads unnecessary columns. For point queries, row-based storage is more efficient with the help of B+ tree indexes, while column-based storage has coarser index granularity.

[0004] The Internet of Things (IoT) and sensors generate massive amounts of time-series data. While columnar storage databases can effectively process this data, there is currently a lack of methods to implement columnar storage based on row-based relational databases. This would allow for the reuse of existing row-based database resources, reduce development costs, and meet the specific application needs of industrial time-series databases. Furthermore, time-series data differs from traditional relational data in terms of identification, relationships, growth trends, and update operations, further highlighting the urgency of this technology. Summary of the Invention

[0005] To overcome the shortcomings of existing technologies, this invention provides an embodiment of a method for implementing columnar storage based on a row-oriented relational database, comprising: An application initiates an insert or data import operation from the data access layer via an INSERT statement; this step is consistent with that of a row-oriented database, and the table is specified as a time-series storage table during table creation. For data insertion into the time-series storage table, incremental data is stored in the database's data cache in the form of a memory table, stored in rows within pages of the memory table, and these pages are marked as write pages. A row-to-column conversion module retrieves data from the memory table page by page, taking non-overwritable write pages, performs row-to-column conversion, and caches the data by column. Processed pages are modified to be overwritable 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 series range of the data batch, used for querying and locating the data in the batch. After the threshold batch is reached, a compression module compresses the data column by column, using different compression methods according to the data type and characteristics of different columns. The compressed data is written to a BLOB field; it can be written to a separate BLOB field for each column, or multiple columns can be written to a single BLOB field in column order according to certain rules, with positioning via offset.

[0006] In one embodiment, the incremental row-to-column conversion step is as follows: When inserting data, the application initiates an insert or data import operation from the data access layer through an insert statement, specifying the table creation as a time-series storage table, and storing the data in row format in the memory table pages of the database data cache area, with the pages marked as write pages; the module retrieves non-overwritable write page data from the memory table page by page for row-to-column conversion, and caches the data by column; when the row-to-column data reaches a certain threshold, a sparse index key value is generated based on the time series range of the data batch.

[0007] In one embodiment, the data compression and decompression steps are as follows: In the data insertion process, after reaching the threshold batch, a suitable compression method is selected to compress the column data according to the data type and characteristics of different columns. The compressed data is written to a BLOB field. It can be written to a separate BLOB field by column or multiple columns can be written to a BLOB field in column order according to rules, and different column data are located by offset. In the query process, a corresponding decompression algorithm is selected to decompress the data read from the BLOB field according to the compression method used for different columns.

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

[0009] In one embodiment, run-length encoding (RLE) or a combination of Delta encoding and run-length encoding is used for compression of numerical data, while Huffman encoding is used for compression of text data.

[0010] In one embodiment, the insertion process of the method includes: data insertion and caching: the application initiates an insertion 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 and column transformation and index generation: the row and column transformation module obtains the write page data, performs transformation and caching, and generates sparse index key values ​​when a threshold is reached; data compression: the compression module compresses the column data; data storage to BLOB field: the compressed data is written to the BLOB field.

[0011] In one embodiment, the query process of the method includes: index location: the query locates the BLOB storage location by matching the sparse index key value according to the time series conditions; data decompression: the decompression module decompresses the read compressed data; column-to-row operation: the decompressed column data is converted to rows and written to the memory table page by page, with the pages marked as non-write pages; result return: the query interface matches rows that meet the query conditions from the memory table and returns the results.

[0012] In one embodiment, the method eliminates the need for a specially designed columnar storage disk format and file processing mechanism, reusing existing code and capabilities of row-based relational databases. In another embodiment, the method is particularly suitable for time-series database scenarios in industrial applications, enabling efficient storage and querying of 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. It stores formatted unstructured data, is unaffected by database character set conversion, and the database does not need to care about its specific stored content.

[0014] The above embodiments provide a method for implementing columnar storage based on a row-oriented relational database, which has the following beneficial effects: It reduces development costs and difficulty. One of the biggest advantages of this invention is that it eliminates the need for specially designed complex columnar storage disk formats and file processing mechanisms. By fully reusing the existing code and capabilities of the row-oriented relational database, the development workload and technical difficulty required to implement columnar storage functionality are greatly reduced. This means that when introducing columnar storage functionality, enterprises can avoid investing significant manpower and resources in entirely new development, reducing development costs and time cycles, and improving project implementation efficiency. This invention is also applicable to time-series database scenarios: the method is specifically designed for time-series database scenarios and can well meet the storage and querying needs of time-series data. In the industrial field, a large amount of equipment operation data, sensor monitoring data, etc., have time-series characteristics. These data typically have characteristics such as large data volume, rapid growth rate, and infrequent updates. The method of this invention, through efficient row-column conversion, data compression, and a timestamp-based indexing mechanism, can quickly store and query these time-series data, providing strong support for applications such as industrial production monitoring and fault prediction. Attached Figure Description

[0015] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on the structures shown in these drawings without creative effort.

[0016] Figure 1 A flowchart illustrating a method for implementing columnar storage based on a row-oriented relational database, provided in the first embodiment of the present invention;

[0017] Figure 2 This is a schematic diagram illustrating the working principle of the method for implementing columnar storage based on a row-oriented relational database, as provided in the first embodiment of the present invention. Detailed Implementation

[0018] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only a part of the embodiments of the present invention, and not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those of ordinary skill in the art without creative effort are within the scope of protection of the present invention.

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

[0020] Furthermore, if the embodiments of this invention involve descriptions such as "first" or "second," these descriptions are for descriptive purposes only and should not be construed as indicating or implying their relative importance or implicitly specifying the number of technical features indicated. Thus, features defined with "first" or "second" may explicitly or implicitly include at least one of those features. Additionally, the use of "and / or" or "and / or" throughout the text implies three parallel solutions. For example, "A and / or B" includes solution A, solution B, or a solution where both A and B are satisfied. Furthermore, the technical solutions of the various embodiments can be combined, but this must be based on the ability of those skilled in the art to implement them. When the combination of technical solutions is contradictory or impossible to implement, it should be considered that such a combination of technical solutions does not exist and is not within the scope of protection claimed by this invention.

[0021] Example 1

[0022] refer to Figures 1-2 To address the aforementioned problems, the core objective of this invention is to provide an innovative method that, based on existing row-oriented relational databases, cleverly utilizes the BLOB (Binary Large Object) data type's ability to support large data block storage, thereby achieving efficient columnar storage of time-series data. This method aims to solve the problems of high development difficulty and cost associated with existing columnar storage implementations, while simultaneously meeting the specific data storage and query requirements of industrial time-series databases, thus improving 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, contiguously, while columnar storage stores each column of data in a table contiguously. In row-based storage, each row of data in a table is tightly connected, while in columnar storage, each row is split by column and stored separately. Columnar storage has a higher compression ratio, effectively reducing disk I / O, but it is not as good as columnar storage in terms of compression and I / O optimization. When updating data, row-based storage has a clear advantage; modifying a single record is easier, while in columnar storage, because data is stored column-wise, modifying a record may require multiple I / O accesses, resulting in higher costs. In data reading scenarios, columnar storage is advantageous; it can precisely read only the relevant columns, avoiding reading unnecessary information and reducing data processing volume; row-based storage may read unnecessary columns, increasing the data processing burden. During point queries, row-based storage, with the help of B+ tree indexes, can directly locate the row, resulting in higher query efficiency; columnar storage has a coarser index granularity, typically locating the column cell. In terms of data compression, columnar storage, where data types are identical and there are many duplicate values, yields better compression algorithms and higher compression ratios; row-based storage, on the other hand, has lower duplication rates between different records, resulting in relatively lower compression performance. Currently, there is a need for a new method to implement columnar storage based on row-based relational databases. This method should maximize the reuse of existing row-based database resources, implement columnar storage functionality with minimal development costs, and meet the needs of specific application scenarios, especially time-series databases in the industrial sector.

[0024] This method solves the problem of implementing columnar storage in row-oriented relational databases by introducing the concept of time-series storage tables. Specific technical features include: applications initiate insert or data import operations from the data access layer using INSERT statements, specifying the table as a time-series storage table during table creation; during data insertion, incremental data is stored in rows in pages of the memory table, with each page marked as a write page; the row-to-column conversion module retrieves non-overwritable write page data from the memory table page by page for row-to-column conversion and caches the data by column, modifying processed pages in the memory table to be overwritable; when the row-to-column conversion data reaches a certain threshold, sparse index key values ​​are generated based on the time series range of the data batch; after reaching the threshold batch, the compression module compresses the data column by column, employing different compression methods based on the data type and characteristics of different columns; the compressed data is written to a BLOB field, either individually by column or multiple columns sequentially written to a single BLOB field according to rules, with positioning via offset.

[0025] Current row-oriented relational databases have certain limitations when handling columnar storage. To overcome these limitations, this invention proposes a novel method that effectively implements columnar storage within a row-oriented relational database by introducing the concept of a time-series storage table. Specifically, applications initiate insert or data import operations from the data access layer using INSERT statements, specifying the table as a time-series storage table during creation. During data insertion, incremental data is stored in a row format in pages of the memory table, with each page marked as a write page. The row-column transformation module retrieves non-overwritable write page data from the memory table page by page, performs row-column transformation, and caches the data by column. Processed pages are modified to be overwritable in the memory table. When the row-to-column data reaches a certain threshold, sparse index key values ​​are generated based on the time series range of the data batch. After reaching the threshold batch, the compression module compresses the data column by column, employing different compression methods based on the data type and characteristics of different columns. The compressed data is written to a BLOB field, which can be written to a single BLOB field column by column, or multiple columns can be written sequentially to a single BLOB field according to rules, with positioning determined by offsets.

[0026] Key terms used in implementation include time-series storage table, in-memory table, row-column transformation module, sparse index key-value pair, compression module, and BLOB field. The time-series storage table is the table specified during table creation for storing time-series data. The in-memory table is used to cache incremental data, storing it in row format within pages. The row-column transformation module retrieves data from the in-memory table page by page and performs row and column transformations. The sparse index key-value pair is used to quickly locate specific batches of data during queries. The compression module selects an appropriate compression method to compress column data based on the data type and characteristics of different columns. The BLOB field is used to store the compressed data.

[0027] The application initiates insert or data import operations from the data access layer using INSERT statements, specifying the table as a time-series storage table during table creation. During data insertion, incremental data is stored in rows within pages of the in-memory table, with each page marked as a write page. The row-to-column transformation module retrieves non-overwritable write page data from the in-memory table page by page, performs row-to-column transformation, and caches the data by column. Processed pages are modified to be overwritable 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-series range of the data batch. After reaching the threshold batch, the compression module compresses the data column by column, employing different compression methods based on the data type and characteristics of different columns. The compressed data is written to a BLOB field; it can be written to a single BLOB field column by column, or multiple columns can be written sequentially to a single BLOB field according to rules, with positioning determined by offsets.

[0028] Compared with existing technologies, the method of this invention solves the problem of how to implement columnar storage in a row-oriented relational database by introducing the concept of a time-series storage table. Specific technical features include applications initiating insert or data import operations from the data access layer using INSERT statements, specifying a time-series storage table during table creation. During data insertion, incremental data is stored in rows in pages of the memory table, with each page marked as a write page. The row-column conversion module retrieves non-overwritable write page data from the memory table page by page, performs row-column conversion, and caches the data by column. Processed pages are modified to be overwritable in the memory table. When the row-to-column data reaches a certain threshold, sparse index key values ​​are generated based on the time series range of the data batch. After reaching the threshold batch, the compression module compresses the data column by column, employing different compression methods based on the data type and characteristics of different columns. The compressed data is written to a BLOB field; it can be written to a separate BLOB field column by column, or multiple columns can be written sequentially to a single BLOB field according to rules, with positioning via offset.

[0029] This method solves the problem of implementing columnar storage in row-oriented relational databases by introducing the concept of time-series storage tables. Specific technical features include applications initiating insert or data import operations from the data access layer using INSERT statements, specifying the table as a time-series storage table during table creation. During data insertion, incremental data is stored in rows in pages of the in-memory table, with each page marked as a write page. The row-to-column conversion module retrieves non-overwritable write page data from the in-memory table, performs row-to-column conversion, and caches the data by column. Processed pages are modified to be overwritable in the in-memory table. When the row-to-column conversion data reaches a certain threshold, sparse index key values ​​are generated based on the time series range of the data batch. After reaching the threshold batch, the compression module compresses the data column-wise, employing different compression methods based on the data type and characteristics of different columns. The compressed data is written to a BLOB field; it can be written to a single BLOB field column-wise, or multiple columns can be written sequentially to a single BLOB field according to rules, with positioning via offsets. By combining the above-mentioned technical features, columnar storage can be effectively implemented in row-oriented relational databases, thus solving the technical problem of how to implement columnar storage in row-oriented relational databases.

[0030] Furthermore, this application proposes that, during data insertion, the application initiates an insertion or data import operation from the data access layer using an insert statement. The data is stored in a memory table page in row format, and the page is marked as a write page. The module retrieves non-overwritable write page data from the memory table page by page, performs row-to-column conversion, and caches the data by column. 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.

[0031] During data insertion, the application initiates an insert or import operation from the data access layer using the INSERT statement. Data is stored in a row-based format in memory table pages, with each page marked as a write page. The module retrieves non-overwritable write page data from the memory table page by page, performs row-to-column transformation, and caches the data column by column. 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. These technical features enable row-to-column transformation and sparse index key value generation during data insertion, thereby improving data processing and query efficiency.

[0032] The data is stored in memory table pages in row format, and each page is marked as a "write page." This indicates that data is stored in specific pages of the memory table as rows during insertion, and these pages are marked as write pages for subsequent module identification. The module retrieves non-overwritable write page data from the memory table page by page, performs row-to-column conversion, and caches the data column by column. Further, the module extracts data from these write pages, converts the row-formatted data to column-formatted data, and caches it column by column. This allows for the generation of sparse index keys based on the time series range of the data batches once a certain amount of data is collected, enabling fast data querying and location.

[0033] Specifically, the row-column transformation module can be implemented in various ways. For example, the module can use a temporary storage structure in memory to cache the transformed column data and write this data to persistent storage after reaching a certain threshold. Meanwhile, the process of generating sparse index key values ​​can choose different strategies based on specific application scenarios. For example, index key values ​​can be generated based on a timestamp range to quickly locate relevant data during queries.

[0034] Therefore, this application solves the technical problem of how to perform row and column transformations and generate sparse index key values ​​during data insertion by using a technique that performs row and column transformations and generates sparse index key values ​​during data insertion. Compared with existing technologies, this application can achieve efficient row and column transformations and sparse index key value generation during data insertion, thereby improving data processing efficiency and query efficiency, and is particularly suitable for application scenarios that require processing large amounts of time-series data.

[0035] Furthermore, this application proposes that, in the data insertion process, after reaching a threshold batch size, the compression module selects an appropriate compression method to compress the column data based on the data type and characteristics of different columns, and the compressed data is written to the BLOB field. In the query process, the decompression module selects the corresponding decompression algorithm based on 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 size, the compression module selects the most suitable compression method to compress the column data based on the data type and characteristics of each column. The compressed data is then written to the BLOB field. The purpose of data compression is to reduce storage space usage, thereby improving storage efficiency. In the query process, the decompression module selects the corresponding decompression algorithm based on the compression method used for each column to decompress the data read from the BLOB field. This ensures that data can be correctly and efficiently decompressed during queries, thereby improving query efficiency.

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

[0037] The technical solution of this application effectively solves the problem of how to effectively compress and decompress data in columnar storage to improve storage and query efficiency by performing data compression and decompression during data insertion and querying respectively. 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 type and characteristics of different columns, thereby ensuring that the data compression rate is guaranteed while ensuring that the data can be correctly and efficiently decompressed during querying, thus improving the overall efficiency of storage and querying.

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

[0039] The generation of sparse index key values ​​is based on the time series range of the data batch. When the row-to-column data reaches a certain threshold, sparse index key values ​​are generated according to the time series range of the data batch. The sparse index key value generation strategy can be adjusted in real time according to the data insertion speed and data volume to adapt to index performance optimization under different data volume and operational load conditions. For example, when the data insertion speed is fast or the data volume is large, the sparse index key value generation strategy will be dynamically adjusted to ensure the effectiveness of the index and query efficiency.

[0040] By using sparse index keys, query efficiency can be significantly improved, especially in scenarios involving large amounts of time-series data. Compared with existing technologies, the method proposed in this application avoids full table scans or row-by-row searches by using sparse index keys, thereby accelerating query speed and improving query efficiency. Furthermore, the dynamic adjustment mechanism of sparse index keys can effectively address index performance optimization under different data volumes and operational loads, further enhancing the system's query performance and flexibility. Therefore, the technical solution of this application has significant advantages in improving query efficiency and processing large amounts of time-series data.

[0041] Furthermore, this application proposes using run-length encoding (RLE) or a combination of Delta encoding and run-length encoding for compressing numerical data, and using 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 for compressing numerical data, and using Huffman encoding for compressing text data. These technical features play a crucial role in improving the compression efficiency of both 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, by constructing an optimal prefix code tree, can efficiently compress text data, further reducing storage requirements.

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

[0043] Run-length encoding (RLE) is a simple and effective data compression method, particularly suitable for compressing repetitive data. Delta encoding reduces data redundancy by recording changes in data values, making it suitable for processing data with continuous numerical variations. Run-length encoding, on the other hand, achieves compression by recording the length of consecutive identical data. Combining Delta encoding and run-length encoding can achieve even more efficient compression when processing numerical data. Specifically, Delta encoding is used to convert the original data into a sequence of changing values, and then run-length encoding is applied to this sequence, thereby further reducing the data size.

[0044] Huffman coding is a lossless data compression algorithm that achieves efficient compression by constructing an optimal prefix code tree, representing frequently occurring characters with shorter codes and less frequently occurring characters with longer codes. Specifically, Huffman coding first counts the frequency of each character in the text, then constructs a Huffman tree based on the frequency, and finally generates a corresponding Huffman code table for compressing the text data.

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

[0046] Furthermore, this application proposes that the insertion process of the method includes: data insertion and caching: the application initiates an insert or import operation, and the data is stored in a memory table page in row format, with the page marked as a write page; row and column transformation and index generation: the row and column transformation module obtains the write page data, performs transformation and caching, and generates sparse index key values ​​when a threshold is reached; data compression: the compression module compresses the column data; data storage to BLOB field: the compressed data is written to the BLOB field. The insertion process of this method includes four main steps. First, data insertion and caching: the application initiates an insert or import operation, and the data is stored in a memory table page in row format, with the page marked as a write page. This step ensures that the data is first stored in memory in row format. Second, row and column transformation and index generation: the row and column transformation module obtains the write page data, performs transformation and caching, and generates sparse index key values ​​when a threshold is reached. This step improves data query efficiency by performing row and column transformation and generating index key values. Next, data compression: the compression module compresses the column data, selecting an appropriate compression method based on the data type and characteristics of different columns to reduce storage space. Finally, the data is stored in a BLOB field, and the compressed data is written to the BLOB field to ensure efficient storage of data in compressed form. Through these steps, this method achieves efficient columnar storage in a row-oriented relational database system, solving the problem of how to implement columnar storage functionality in a row-oriented database.

[0047] In the row-column transformation and index generation steps, the row-column transformation module retrieves non-overwritable write page data from the memory table page by page, performs row-column transformation, and caches the data column by column. Processed pages are modified to be overwritable 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 series range of the data batch, used for querying and locating data in that batch. In the data compression step, the compression module compresses data column by column, employing different compression methods based on the data type and characteristics of different columns. The compressed data is written to a BLOB field; it can be written to a separate BLOB field column by column, or multiple columns can be written sequentially to a single BLOB field according to rules, and located using offsets.

[0048] This method achieves efficient columnar storage in row-oriented relational database systems through steps such as row-column transformation and index generation, data compression, and data storage in BLOB fields. Specifically, it improves data query efficiency by transforming rows and columns and generating sparse index keys; it reduces storage space by compressing column data using a compression module; and it ensures efficient data storage in compressed form by writing the compressed data into BLOB fields. Therefore, this method solves the technical problem of implementing columnar storage functionality in row-oriented relational database systems, demonstrating significant technical advantages.

[0049] Furthermore, this application proposes a query process including: index location: the query locates the BLOB storage location by mapping the time-series conditions to the sparse index key value; data decompression: the decompression module decompresses the read compressed data; column-to-row conversion: the decompressed column data undergoes column-to-row conversion and is written to the memory table page by page, with each page marked as a non-write page; result return: the query interface matches rows that meet the query conditions from the memory table and returns the results. Index location quickly locates the BLOB storage location by mapping the time-series conditions to the sparse index key value, achieving efficient reading; the data decompression module decompresses the read compressed data to restore the original data; the column-to-row conversion converts the decompressed column data into row format and writes it to the non-write pages of the memory table, ensuring data integrity and consistency; the result return module matches the query conditions from the memory table and returns the row data that meets the conditions. These technical features work together to achieve efficient location and reading of columnar storage data during the query process, improving query efficiency and data processing performance.

[0050] Specifically, the indexing module can reduce the scan range during queries by creating sparse indexes, thereby improving query efficiency. The data decompression module can employ various decompression algorithms to adapt to different data types, improving decompression speed and accuracy. The column-to-row conversion module can accelerate the data conversion process through batch processing and parallel computing. The result return module can further improve the speed of returning query results by optimizing the query condition matching algorithm.

[0051] Therefore, this application achieves efficient querying of columnar stored data by introducing technical features such as sparse indexes, data decompression, column-to-row operations, and result return. This not only improves data reading speed but also ensures data integrity and consistency, offering significant advantages over existing technologies.

[0052] Furthermore, this application proposes a dual-identification mechanism for memory pages, including write page identifiers and non-write page identifiers. Write page identifiers are used to mark data pages undergoing row-to-column transformation operations, while non-write page identifiers are used to mark processed data pages that have already completed row-to-column transformations. This dual-identification mechanism ensures that incremental data and processed data are not confused during data transformation, improving data processing efficiency.

[0053] The technical features include a dual-identification mechanism for memory pages, specifically including write page identifiers and non-write page identifiers. Write page identifiers mark data pages undergoing row-to-column transformation operations, while non-write page identifiers mark processed data pages that have already completed the transformation. These technical features work together to ensure that incremental data and processed data are not confused during data transformation, thereby improving data processing efficiency. The dual-identification mechanism for memory pages solves the problem of confusion between incremental and processed data during data transformation. The use of write page identifiers and non-write page identifiers clearly distinguishes between data pages being processed and those that have already been processed, thus avoiding data confusion and improving data processing efficiency.

[0054] The dual-identification mechanism can be implemented in several ways. For example, write page identifiers and non-write page identifiers can be implemented using different flag bits, which can be set in the metadata of the memory page. Specifically, a write page identifier can be represented by setting a specific flag bit to 1, while a non-write page identifier can be represented by setting a flag bit to 0. Another implementation method is to distinguish write pages and non-write pages using different memory regions, with write pages stored in a dedicated memory region and non-write pages stored in another. Furthermore, write pages and non-write pages can be identified using color coding; for example, write pages can be identified in red, and non-write pages in green.

[0055] The dual-identification mechanism of this application has significant advantages. First, by explicitly distinguishing between write pages and non-write pages, it avoids confusion between incremental data and processed data during data transformation, thereby improving data processing efficiency. Second, the dual-identification mechanism can be implemented flexibly and in various ways, allowing for the selection of an appropriate implementation method based on specific application scenarios. Furthermore, this mechanism can be seamlessly integrated with existing database systems, reducing implementation complexity. Compared with existing technologies, the dual-identification mechanism of this application has significant advantages in data processing efficiency and system integration.

[0056] The sparse index key values ​​are dynamically adjusted based on the data timestamp and the time range of 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 sparse index key value generation strategy is adjusted in real time. This dynamic adjustment mechanism can effectively cope with index performance optimization under different data volumes and different operational loads.

[0057] The specific implementation of dynamically adjusting sparse index key values ​​based on data timestamps and the time range of data batches includes the following steps: During data insertion, sparse index key values ​​are automatically generated according to the time range of time-series data. If the data insertion speed is fast or the data volume is large, the sparse index key value generation strategy is adjusted in real time. The dynamic adjustment mechanism includes real-time monitoring of data insertion speed and data volume, and adjusting the generation frequency and strategy of sparse index key values ​​based on the monitoring results. For example, when the data insertion speed exceeds a preset threshold, the system increases the generation frequency of sparse index key values ​​to ensure optimized index performance. As a preferred implementation, the sparse index key value generation strategy can be predicted and adjusted based on historical data insertion patterns to better adapt to dynamically changing data loads.

[0058] This application solves the problem of sparse index key value generation and adjustment when processing time series data by dynamically adjusting the sparse index key value generation mechanism. Compared with existing technologies, the dynamic adjustment mechanism of this application can maintain optimized index performance under different data volumes and operational loads, improving query efficiency and overall system performance. Specifically, this application can adjust the sparse index key value generation strategy in real time according to changes in data insertion speed and data volume, ensuring the stability and efficiency of index performance. Therefore, this application provides an efficient and flexible time series data processing method that can adapt to various data load conditions and significantly improve the system's query efficiency and overall performance.

[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 contiguously, resulting in higher compression rates and query efficiency. Time-series databases are specifically designed to handle data with time-series characteristics and typically employ columnar storage to improve data storage and query efficiency. Currently, there is a need for a new method to implement columnar storage based on row-based relational databases. This method should maximize the reuse of existing row-based database resources, implement columnar storage functionality with minimal development costs, and meet the needs of specific application scenarios, especially time-series databases in the industrial sector.

[0060] The technical solution of this application includes: when inserting data, the application initiates an insert or 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 the write page. The module retrieves the non-overwritable write page data from the memory table page by page, performs row-to-column conversion, and caches the data by column. When the row-to-column data reaches a certain threshold, a sparse index key value is generated according to the time range of the data batch, which is used to query and locate the data of the batch. After the threshold batch is reached, the compression module compresses the data by column, and adopts different compression methods according to the data type and characteristics of different columns. The compressed data is written to a BLOB field. It can write a separate BLOB field by column, or write multiple columns in column order to a BLOB field according to the rules, and locate the data by offset.

[0061] Furthermore, the memory page employs a dual-identification mechanism, including a write page identifier and a non-write page identifier. The write page identifier marks the data page undergoing row-column transformation, while the non-write page identifier marks the processed data page that has already completed the row-column transformation. This dual-identification mechanism ensures that incremental data and processed data are not confused during data transformation, improving data processing efficiency.

[0062] The sparse index key values ​​are dynamically adjusted based on the data timestamp and the time range of the data batch. During insertion, 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 sparse index key value generation strategy is adjusted in real time. This dynamic adjustment mechanism can effectively address index performance optimization under different data volumes and operational loads.

[0063] This application implements columnar storage in a row-oriented relational database, maximizing the reuse of existing resources and achieving efficient data compression and querying. Compared with existing technologies, this application provides a flexible and efficient solution, particularly suitable for time-series data processing in industrial fields.

[0064] The above description is merely a preferred embodiment of the present invention and does not limit the patent scope of the present invention. Any equivalent structural transformations made using the contents of the present invention's specification and drawings under the inventive concept of the present invention, or direct / indirect applications in other related technical fields, are included within the patent protection scope of the present invention.

Claims

1. A method for implementing columnar storage based on a row-oriented relational database, characterized in that, include: Applications initiate insert or data import operations from the data access layer using the INSERT statement, and specify time-series storage when creating the table. For data insertion into time-series stored tables, incremental data is stored in the database's data cache in the form of a memory table, stored in row format in memory table pages, and these pages are marked as write pages; wherein, the memory table pages have a dual identification mechanism, including: a write page identifier, used to mark data pages that are undergoing row-column transformation operations; and a non-write page identifier, used to mark processed data pages that have completed row-column transformation. The row-column transformation module retrieves data from the memory table page by page, takes the non-overwritable pages, performs row-column transformation, and caches the data by column. Processed pages are modified to be overwritable 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 size, the compression module compresses the data column by column, using different compression methods based on the data type and characteristics of different columns. The compressed data is written to a BLOB field and located by offset. The query process includes: Index location: The query locates the BLOB storage location by matching the sparse index key value with the time series conditions; Data decompression: The decompression module decompresses the read compressed data; Column to row operation: The decompressed column data is transformed into rows and written to memory table pages by page, marked as non-write pages; Result return: The query interface matches rows that meet the query conditions from the memory table and returns the results.

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

3. The method for implementing columnar storage based on a row-oriented relational database according to claim 2, characterized in that, Sparse index key values ​​are used to quickly locate specific batches of data during queries.

4. The method for implementing columnar storage based on a row-oriented relational database according to claim 3, characterized in that, Run-length encoding is used for compressing numerical data, while Huffman coding is used for compressing text data.

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

6. A computer-readable medium having a computer program stored thereon, characterized in that, When the computer program is executed by the processor, it implements the method for implementing columnar storage based on a row-oriented relational database as described in any one of claims 1-5.

Images