Method, system and equipment for importing time series data into openGauss database

Abstract

The invention relates to the technical field of database data import, and provides a method, system and device for importing time sequence data into an openGauss database, and the method comprises the steps: reading original time sequence data through a worker thread, partitioning the original time sequence data according to a time mark and a label mark, and storing the partitioned original time sequence data into a corresponding skip list in a memory; reading the data in the skip list through a write thread, and writing the read data into a bulkload rows memory structure according to columns; and constructing corresponding metadata information according to the data in the bulkload rows memory structure through a write thread, and writing the data into a column storage file. According to the method, the system and the equipment for importing the time sequence data in the openGauss database, resources such as a CPU (Central Processing Unit) and a memory of an operating system can be fully utilized, the bottleneck of the system resources is reduced, the data importing efficiency is ensured, and the storage characteristic of the time sequence database is not lost.

Description

Technical Field

[0001] The present invention relates to the technical field of database data import, and in particular to a method, system and device for importing time series data into an openGauss database. Background Art

[0002] A time series database (TSDB) is a database designed specifically for processing time series data, with efficient storage, query, and complex analysis capabilities. Its typical features include: more writes and less reads, large data volumes, and complex analysis requirements. Time series databases are widely used in scenarios such as the Internet of Things, financial transactions, operational monitoring, and log analysis. Common time series databases include InfluxDB, OpenTSDB, and TimescaleDB. Time series databases need to cope with high-frequency, large-scale data writes and provide low-latency query capabilities, which poses a severe challenge to performance. Traditional relational databases mostly use B-Tree index structures, which have random read and write bottlenecks, especially in write-intensive scenarios. In order to improve write performance, mainstream time series databases generally use LSM Tree (Log-Structured Merge Tree) instead of B-Tree, such as KairosDB (based on Cassandra), OpenTSDB (based on HBase), and LevelDB. The core idea is to sacrifice some read performance to maximize write performance.

[0003] openGauss is a high-performance, highly available, highly secure, easy-to-operate and fully open open source relational database. If openGauss is used to store time series data, its import and query efficiency are difficult to guarantee. Research has found that in addition to supporting traditional row storage, openGauss also supports column storage of data. Column storage is generally suitable for large-scale import and analytical query scenarios, which is very consistent with the storage and query of time series data. Therefore, using openGauss column storage to implement the storage and access of time series data is a feasible solution. However, if column storage is directly used to store time series data, some data characteristics of the time series itself will be lost. For example, time series storage is generally clustered by label and sorted by time, thereby ensuring the query efficiency of time series data. If these fields are sorted directly when inserting column storage, the efficiency of data import will be greatly affected.

[0004] Therefore, how to provide a suitable data import method to import time series data into the openGauss database, ensuring the efficiency of data import while not losing the storage characteristics of the time series database itself, has become a technical problem that needs to be solved urgently. Summary of the invention

[0005] In view of this, in order to overcome the deficiencies of the prior art, the present invention aims to provide a method, system and device for importing time series data into an openGauss database.

[0006] According to a first aspect of the present invention, a method for importing time series data into an openGauss database is provided, the method comprising: The original time series data is read through the worker thread, partitioned according to the time mark and label mark, and stored in the corresponding jump table in the memory; Read the data in the jump list through the writer thread, and write the read data into the bulkload rows memory structure by column; The writer thread builds the corresponding metadata information based on the data in the bulkload rows memory structure and writes the data into the column storage file.

[0007] Preferably, in the method for importing time series data into an openGauss database of the present invention, the original time series data is read through a worker thread, and the original time series data is partitioned according to time tags and label tags and stored in a corresponding jump table in the memory, including: The worker thread reads the original time series data row by row, and divides the read data rows into partitions of corresponding time periods according to the time stamps of the original time series data. The tag ID corresponding to the partition is obtained according to the tag mark of the original time series data, and the data rows are stored in the skip table corresponding to the tag ID in the memory in chronological order.

[0008] Preferably, in the method of importing time series data into the openGauss database of the present invention, the tag ID corresponding to the partition is obtained according to the label mark of the original time series data, and the data rows are stored in a skip table corresponding to the tag ID in the memory in chronological order, including: each tag ID of each partition uniquely corresponds to a skip table in the memory.

[0009] Preferably, in the method of importing time series data into the openGauss database of the present invention, the original time series data is read through a worker thread, and the original time series data is partitioned according to time tags and label tags and stored in a corresponding jump table in the memory, including: when the data rows stored in the jump table reach a set threshold, the jump table is sent to the linked list to be processed for storage.

[0010] Preferably, in the method of importing time series data into the openGauss database of the present invention, the original time series data is read through a worker thread, and the original time series data is partitioned according to time tags and label tags and stored in corresponding skip tables in the memory, and also includes: when the memory is insufficient, the memory space is released by writing the largest skip table in the memory to a temporary external memory.

[0011] Preferably, in the method for importing time series data into the openGauss database of the present invention, data in a skip list is read through a writer thread, and the read data is written into a bulkload rows memory structure by column, including: reading a skip list from a linked list to be processed through a writer thread, traversing each row of data in the read skip list, and writing each row of data obtained through the traversal into the bulkload rows memory structure by column.

[0012] Preferably, in the method of importing time series data into the openGauss database of the present invention, data in the jump list is read through a writer thread, and the read data is written into the bulkload rows memory structure by column, and also includes: after all the data in the jump list is written into the bulkload rows memory structure, continuing to read the next jump list from the linked list to be processed.

[0013] Preferably, in the method for importing time series data into an openGauss database of the present invention, corresponding metadata information is constructed according to the data in the bulkload rows memory structure by a writer thread, and the data is written into a column storage file, including: compressing the data in the bulkload rows memory structure by column by a writer thread, writing the compressed column data into a mapping structure of a column storage file storage unit in memory, saving the metadata information of the column storage file storage unit, and writing the compressed column data into the column storage file according to the metadata information of the column storage file storage unit.

[0014] According to a second aspect of the present invention, a system for importing time series data into an openGauss database is provided, the system comprising an import server, the import server being used to: read original time series data through a worker thread, partition the original time series data according to time tags and label tags, and store them in a corresponding skip list in a memory; read data in the skip list through a writer thread, and write the read data into a bulkload rows memory structure by column; construct corresponding metadata information according to the data in the bulkloadrows memory structure through a writer thread, and write the data into a column storage file.

[0015] According to a third aspect of the present invention, a computer device is provided, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the method described in the first aspect of the present invention when executing the program.

[0016] The method, system and device for importing time series data into an openGauss database of the present invention apply the multi-threaded architecture of openGauss to hierarchically process time series data. The first-level pipeline is responsible for reading data and sorting it by partition and block, and the second-level pipeline is responsible for saving metadata and writing data into files. It can more fully utilize the CPU and memory and other resources of the operating system, reduce the bottleneck of system resources, and ensure the efficiency of data import without losing the storage characteristics of the time series database itself. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings required for use in the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0018] Figure 1 This is an example diagram of the architecture of a system for importing time series data into an openGauss database according to an embodiment of the present invention; Figure 2 A flowchart of a method for importing time series data into an openGauss database according to an embodiment of the present invention; Figure 3 A schematic diagram of the structure of the device provided by the present invention. DETAILED DESCRIPTION

[0019] The embodiments of the present invention are described in detail below with reference to the accompanying drawings.

[0020] It should be noted that the following embodiments and features in the embodiments may be combined with each other in the absence of conflict; and, based on the embodiments in the present disclosure, all other embodiments obtained by ordinary technicians in the field without making any creative work are within the scope of protection of the present disclosure.

[0021] It should be noted that various aspects of the embodiments within the scope of the appended claims are described below. It should be apparent that the aspects described herein may be embodied in a wide variety of forms, and any specific structure and / or function described herein is merely illustrative. Based on the present disclosure, it should be understood by those skilled in the art that an aspect described herein may be implemented independently of any other aspect, and two or more of these aspects may be combined in various ways. For example, any number of aspects described herein may be used to implement the device and / or practice the method. In addition, other structures and / or functionalities other than one or more of the aspects described herein may be used to implement this device and / or practice this method.

[0022] The technical principles of the present invention are as follows: A two-level pipeline architecture is used to import time series data. Each level of the pipeline uses a type of thread processing. The first-level pipeline uses a worker thread to read the original data, and partitions and blocks the data according to time and label values ​​and stores it in the skip table structure of the memory; the second-level pipeline uses a writer thread to read the data in the memory skip table, build and save metadata information, and finally write the time series data to the file.

[0023] Figure 1 FIG. 1 is an example diagram of the architecture of a system for importing time series data into an openGauss database according to an embodiment of the present invention. Figure 1 As shown, the system may include an import server 101, a communication network 102 and / or one or more import clients 103. Figure 1 The example in FIG. 1 is a plurality of import clients 103 .

[0024] The import server 101 can be any appropriate server for storing information, data, programs and / or any other suitable type of content. In some embodiments, the import server 101 can perform appropriate functions. For example, in some embodiments, the import server 101 can be used to import time series data into an openGauss database. As an optional example, in some embodiments, the import server 101 can be used to import time series data into an openGauss database through a two-stage pipeline architecture. For example, the import server 101 can be used to: read the original time series data through a worker thread, partition the original time series data according to time tags and label tags, and store them in the corresponding skip list in the memory; read the data in the skip list through a writer thread, and write the read data into the bulkload rows memory structure by column; construct corresponding metadata information according to the data in the bulkload rows memory structure through a writer thread, and write the data into a column storage file.

[0025] As another example, in some embodiments, the import server 101 may send a method for importing time series data from the openGauss database to the import client 103 for the user to use based on a request from the import client 103 .

[0026] As an optional example, in some embodiments, the import client 103 is used to provide a visual import interface, which is used to receive a user's selection input operation to import time series data into an openGauss database, and, in response to the selection input operation, obtain an import interface corresponding to the option selected by the selection input operation from the import server 101 and display the import interface, wherein the import interface at least displays information on importing time series data into the openGauss database and operation options for importing the information on importing time series data into the openGauss database.

[0027] In some embodiments, the communication network 102 can be any suitable combination of one or more wired and / or wireless networks. For example, the communication network 102 can include any one or more of the following: the Internet, an intranet, a wide area network (WAN), a local area network (LAN), a wireless network, a digital subscriber line (DSL) network, a frame relay network, an asynchronous transfer mode (ATM) network, a virtual private network (VPN), and / or any other suitable communication network. The import client 103 can be connected to the communication network 102 via one or more communication links (e.g., communication link 104), and the communication network 102 can be linked to the import service end 101 via one or more communication links (e.g., communication link 105). The communication link can be any communication link suitable for transmitting data between the import client 103 and the import service end 101, such as a network link, a dial-up link, a wireless link, a hard-wired link, any other suitable communication link, or any suitable combination of such links.

[0028] The import client 103 may include any one or more clients that present an interface related to importing time series data into the openGauss database in an appropriate form for use and operation by the user. In some embodiments, the import client 103 may include any suitable type of device. For example, in some embodiments, the import client 103 may include a mobile device, a tablet computer, a laptop computer, a desktop computer, and / or any other suitable type of client device.

[0029] Although the import server 101 is illustrated as one device, in some embodiments, any suitable number of devices may be used to perform the functions performed by the import server 101. For example, in some embodiments, multiple devices may be used to implement the functions performed by the import server 101. Alternatively, a cloud service may be used to implement the functions of the import server 101.

[0030] Based on the above system, an embodiment of the present invention provides a method for importing time series data into an openGauss database, which is described below through the following embodiments.

[0031] Figure 2 1 is a flowchart of the steps of a method for importing time series data into an openGauss database according to an embodiment of the present invention. The method for importing time series data into an openGauss database according to this embodiment can be executed on the import server, such as Figure 2 As shown, the method for importing time series data into the openGauss database includes the following steps: Step S201: read the original time series data through the worker thread, partition the original time series data according to the time mark and label mark, and store them in the corresponding jump table in the memory.

[0032] As an optional example, this embodiment reads the original time series data by row through a worker thread, and divides the read data rows into partitions of corresponding time periods according to the time stamps of the original time series data; obtains the tag ID corresponding to the partition according to the tag tag of the original time series data, and stores the data rows in chronological order in the skip list corresponding to the tag ID in the memory. It should be noted that in this embodiment, each tag ID of each partition uniquely corresponds to a skip list in the memory. When the data rows stored in the skip list reach the set threshold, the skip list is sent to the pending linked list for storage. When the memory is insufficient, the memory space is released by writing the largest skip list in the memory to the temporary external memory. The skip list in this embodiment is a data structure used to add multi-level indexes to an ordered linked list to improve search efficiency.

[0033] In actual applications, time series databases will mark the columns of the table as time columns or label columns. The time column is also a partition column. When inserting data, it will enter different partitions according to the time of the data. There can be multiple label columns. When inserting data, a unique label ID is obtained based on the combined values ​​of all label columns. For example, there are two label columns, c1 and c2. The label combination value of the inserted data (1, 2) is set to 1, and the label combination value of the inserted data (2, 3) is set to 2. Each time data is inserted, it will check whether the label combination value already exists. If it exists, its corresponding label ID is used. If it does not exist, the latest label ID value is obtained from the ascending sequence.

[0034] Step S202: read the data in the jump list through the writer thread, and write the read data into the bulkloadrows memory structure by column.

[0035] As an optional example, this embodiment uses a writer thread to read a skip list from a to-be-processed linked list, traverses each row of data in the read skip list, and writes each row of data obtained by traversal into a bulkload rows memory structure by column. The bulkload rows memory structure is an internal storage structure of the openGauss database.

[0036] In this embodiment, after all the data in the skip list are written into the bulkload rows memory structure, the next skip list is read from the to-be-processed linked list.

[0037] Step S203: The writer thread constructs corresponding metadata information according to the data in the bulkload rows memory structure, and writes the data into the column storage file.

[0038] As an optional example, this embodiment compresses the data in the bulkload rows memory structure by column through a writer thread, writes the compressed column data into the mapping structure of the column storage file storage unit in the memory, saves the metadata information of the column storage file storage unit, and writes the compressed column data into the column storage file according to the metadata information of the column storage file storage unit.

[0039] The method and system for importing time series data into an openGauss database of the present invention apply the multi-threaded architecture of openGauss to hierarchically process time series data. The first-level pipeline is responsible for reading data and sorting it by partition and block, and the second-level pipeline is responsible for saving metadata and writing data into files. It can more fully utilize the CPU and memory and other resources of the operating system, reduce the bottleneck of system resources, and ensure the efficiency of data import without losing the storage characteristics of the time series database itself.

[0040] like Figure 3 As shown, the present invention also provides a device, including a processor 310, a communication interface 320, a memory 330 for storing a computer program executable by the processor, and a communication bus 340. The processor 310, the communication interface 320, and the memory 330 communicate with each other through the communication bus 340. The processor 310 implements the above-mentioned method of importing time series data into the openGauss database by running the executable computer program.

[0041] Among them, the computer program in the memory 330 can be implemented in the form of a software functional unit and can be stored in a computer-readable storage medium when it is sold or used as an independent product. Based on this understanding, the technical solution of the present application can be essentially or partly embodied in the form of a software product that contributes to the prior art. The computer software product is stored in a storage medium, including several instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the various embodiments of the present application. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), disk or optical disk, etc. Various media that can store program codes.

[0042] The system embodiments described above are merely illustrative, wherein the units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, i.e., they may be located in one place, or they may be distributed on multiple network units. Some or all of the modules may be selected based on actual needs to achieve the purpose of the solution of this embodiment. Those of ordinary skill in the art may understand and implement it without creative effort.

[0043] Through the description of the above implementation modes, those skilled in the art can clearly understand that each implementation mode can be implemented by means of software plus a necessary general hardware platform, or of course by hardware. Based on such an understanding, the above technical solution can essentially or in other words be embodied in the form of a software product that contributes to the prior art. The computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, a magnetic disk, an optical disk, etc., and includes a number of instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to execute the methods of each embodiment or some parts of the embodiment.

[0044] The above is only a specific embodiment of the present invention, but the protection scope of the present invention is not limited thereto. Any changes or substitutions that can be easily thought of by a person skilled in the art within the technical scope disclosed by the present invention should be included in the protection scope of the present invention. Therefore, the protection scope of the present invention shall be subject to the protection scope of the claims.

Claims

1. A method for importing time series data into an openGauss database, characterized in that: The original time series data is read through the worker thread, partitioned according to the time mark and label mark, and stored in the corresponding jump table in the memory; Read the data in the jump list through the writer thread, and write the read data into the bulkload rows memory structure by column; The writer thread builds the corresponding metadata information based on the data in the bulkload rows memory structure and writes the data into the column storage file.

2. The method for importing time series data into an openGauss database according to claim 1, characterized in that: The original time series data is read through the worker thread, partitioned according to the time mark and label mark, and stored in the corresponding jump table in the memory, including: The worker thread reads the original time series data row by row, and divides the read data rows into partitions of corresponding time periods according to the time stamps of the original time series data. The tag ID corresponding to the partition is obtained according to the tag mark of the original time series data, and the data rows are stored in the skip table corresponding to the tag ID in the memory in chronological order.

3. The method for importing time series data into an openGauss database according to claim 2, characterized in that: The tag ID corresponding to the partition is obtained according to the tag mark of the original time series data, and the data rows are stored in the skip table corresponding to the tag ID in the memory in chronological order, including: each tag ID of each partition uniquely corresponds to a skip table in the memory.

4. The method for importing time series data into an openGauss database according to claim 1, characterized in that: The original time series data is read through the worker thread, and the original time series data is partitioned and stored in the corresponding jump table in the memory according to the time mark and label mark, including: when the data row stored in the jump table reaches the set threshold, the jump table is sent to the to-be-processed linked list for storage.

5. The method for importing time series data into an openGauss database according to claim 1, characterized in that: The original time series data is read through the worker thread, and the original time series data is partitioned according to the time mark and the label mark and stored in the corresponding skip table in the memory. It also includes: when the memory is insufficient, the memory space is released by writing the largest skip table in the memory to the temporary external memory.

6. The method for importing time series data into an openGauss database according to claim 1, characterized in that: The data in the skip list is read through the writer thread, and the read data is written into the bulkload rows memory structure by column, including: reading the skip list from the to-be-processed linked list through the writer thread, traversing each row of data in the read skip list, and writing each row of data obtained through the traversal into the bulkload rows memory structure by column.

7. The method for importing time series data into an openGauss database according to claim 6, characterized in that: The data in the skip list is read through the writer thread, and the read data is written into the bulkload rows memory structure by column, and also includes: after all the data in the skip list is written into the bulkload rows memory structure, the next skip list is continued to be read from the pending linked list.

8. The method for importing time series data into an openGauss database according to claim 1, characterized in that: The corresponding metadata information is constructed according to the data in the bulkload rows memory structure by the writer thread, and the data is written into the column storage file, including: compressing the data in the bulkload rows memory structure by column by the writer thread, writing the compressed column data into the mapping structure of the column storage file storage unit in the memory, saving the metadata information of the column storage file storage unit, and writing the compressed column data into the column storage file according to the metadata information of the column storage file storage unit.

9. A system for importing time series data into an openGauss database, characterized in that: The system includes an import server, which is used to: read original time series data through a worker thread, partition the original time series data according to time tags and label tags, and store them in a corresponding jump table in the memory; Read the data in the jump list through the writer thread, and write the read data into the bulkload rows memory structure by column; The writer thread builds the corresponding metadata information based on the data in the bulkload rows memory structure and writes the data into the column storage file.

10. A computer device, characterized in that: The computer device comprises a memory, a processor and a computer program stored in the memory and executable on the processor, and the processor implements the steps of the method according to any one of claims 1 to 8 when executing the program.

Images