Time series database processing method and apparatus, medium, device

Abstract

The application provides a time series database processing method and device, medium and equipment. The method comprises the following steps: determining a sampling interval time according to a data requirement of a user; adding sampling table definition information in a source table creation statement; wherein the sampling table definition information comprises the sampling interval time of the sampling table; executing the source table creation statement to make the time series database create a source table and a sampling table corresponding to the source table; when writing data in the source table, sampling according to the sampling interval time, and adding each collected data to the sampling table, so that the user can query data in the sampling table. The embodiment of the application can improve the data query efficiency.

Description

Technical Field

[0001] This invention relates to the field of time-series database technology, and in particular to a time-series database processing method, apparatus, medium, and device. Background Technology

[0002] As the number of user-connected devices increases, meaning the number of measurement points grows, measurement data is continuously written to the time-series database. Over time, the data volume in the time-series database grows significantly, consuming substantial disk space. This necessitates a high compression ratio in the time-series database to conserve disk space. Some databases utilize multi-level cold / hot compression storage technology to effectively address storage issues. However, the high compression ratio and multi-level storage present significant challenges to query performance. Summary of the Invention

[0003] To address at least one of the above technical problems, embodiments of the present invention provide a time-series database processing method, apparatus, medium, and device.

[0004] According to a first aspect, the time-series database processing method provided by the embodiments of the present invention includes:

[0005] Determine the sampling interval based on the user's data requirements;

[0006] Add sampling table definition information to the source table creation statement; wherein, the sampling table definition information includes the sampling interval time of the sampling table;

[0007] Execute the source table creation statement to create a source table and a corresponding sampling table in the time series database.

[0008] When writing data to the source table, sampling is performed according to the sampling interval, and each collected data is added to the sampling table so that the user can query the data in the sampling table.

[0009] According to a second aspect, the time-series database processing apparatus provided in the embodiments of the present invention includes:

[0010] The frequency determination module is used to determine the sampling interval time based on the user's data requirements;

[0011] The statement determination module is used to add sampling table definition information to the source table creation statement; wherein, the sampling table definition information includes the sampling interval time of the sampling table;

[0012] The table creation module is used to execute the source table creation statement so that the time series database creates a source table and a sampling table corresponding to the source table.

[0013] The data sampling module is used to sample data according to the sampling interval when data is written to the source table, and add the collected data to the sampling table so that the user can query the data in the sampling table.

[0014] According to a third aspect, embodiments of the present invention provide a computer-readable storage medium having a computer program stored thereon, which, when executed in a computer, causes the computer to perform the method provided in the first aspect.

[0015] According to a fourth aspect, the computing device provided in the embodiments of the present invention includes a memory and a processor, wherein the memory stores executable code, and when the processor executes the executable code, it implements the method provided in the first aspect.

[0016] The time-series database processing method, apparatus, medium, and device provided in this invention first determine the sampling interval, then add sampling table definition information to the source table creation statement to indicate the sampling interval of the sampling table, and then execute the source table creation statement, thereby creating a source table and a corresponding sampling table for the time-series database. When writing data to the source table, sampling is performed according to the sampling interval, and each collected data is added to the sampling table so that users can query data in the sampling table. Although the data structure of the sampling table and the source table is the same, the data volume is greatly reduced, thus reading is faster. Compared with the method of searching in the source table, data searching in the sampling table can be completed quickly, improving search efficiency. Attached Figure Description

[0017] Figure 1 This is a flowchart illustrating a time-series database processing method in one embodiment of the present invention. Detailed Implementation

[0018] In a first aspect, embodiments of the present invention provide a time-series database processing method, see [link to previous document]. Figure 1 The method includes the following steps S110 to S140:

[0019] S110. Determine the sampling interval based on the user's data requirements;

[0020] In most scenarios, users do not need the full data. For example, if the user's data needs to be updated every 5 minutes, it is not necessary to obtain the full data. In this case, the sampling interval can be set to 5 minutes, 2.5 minutes, etc.

[0021] S120. Add sampling table definition information to the source table creation statement; wherein, the sampling table definition information includes the sampling interval time of the sampling table;

[0022] The source table creation statement can be an SQL statement.

[0023] Specifically, adding "WITH SAMPLE BY sampling interval a" to the source table creation statement indicates the sampling table definition information. In other words, adding "WITH SAMPLE BY sampling interval a" to the source table creation statement indicates that the sampling table needs to be created simultaneously with the source table, and the sampling interval for the sampling table is 'a'. The sampling interval can be customized by the user and can be in seconds.

[0024] In one embodiment, the source table creation statement for adding sampling table definition information can be:

[0025] CREATE TABLE <source table name>([source table definition options])[WITH SAMPLE BY sampling interval time].

[0026] The source table definition options are common and standardized options in the created source table, such as common fields. Of course, the source table creation statement can also include partitioning options, because partitions will be set in the source table, and these partitioning options allow you to define each partition.

[0027] S130. Execute the source table creation statement to create a source table and a sampling table corresponding to the source table in the time series database.

[0028] In other words, by executing the source table creation statement that adds sampling table definition information, a source table and its corresponding sampling table can be created.

[0029] One source table corresponds to one sampling table.

[0030] The source table is used to store all the data.

[0031] The sampling table is used to store the sampling data.

[0032] In one embodiment, the method provided by this invention may further include:

[0033] After creating the source table and the corresponding sampling table, the sampling interval time is added to the metadata of the source table.

[0034] In other words, after creating the source table and the sampling table, the sampling interval time is added to the metadata of the source table, which makes it easier to perform interval sampling based on the sampling interval time in the source table.

[0035] S140. When writing data to the source table, sampling is performed according to the sampling interval time, and each collected data is added to the sampling table so that the user can query the data in the sampling table.

[0036] In other words, when writing data to the source table, sampling is performed at intervals according to the sampling interval in the source table, and the data obtained from each sampling is added to the sampling table. This way, users can query data in the sampling table without having to query data in the source table. Moreover, since the amount of data in the sampling table is small, the required data can be retrieved quickly.

[0037] In one embodiment, S140 may specifically include:

[0038] The next sampling time is determined based on the previous sampling time and sampling interval recorded in the metadata.

[0039] Determine whether the next sampling time has been reached;

[0040] If so, the data to be written to the source table at the next sampling time will also be written to the sampling table;

[0041] After each sampling is completed, the sampling time is written into the metadata of the source table as the previous sampling time; the time when the first piece of data is written to the source table is the first sampling time.

[0042] That is, the time when the first piece of data is written to the source table is taken as the first sampling time; the first sampling time is added to the sampling interval time to get the second sampling time; the second sampling time is added to the sampling interval time to get the third sampling time; and so on, to get the sampling time for each time.

[0043] After each sampling, the time of this sampling is added to the metadata of the source table as the previous sampling time. This allows the previous sampling time and sampling interval recorded in the metadata to be summed to obtain the next sampling time. Then, at the next sampling time, the data written to the source table is also written to the sampling table. Finally, the previous sampling time in the metadata is updated according to the current sampling time.

[0044] In one embodiment, the method provided by the present invention may further include: storing the source table using a multi-level cold and hot compression storage technique.

[0045] In other words, the source table can be compressed and stored in a way similar to that of the KAIWUDB database. This method can achieve a high compression ratio and multi-level storage, which can greatly save disk space.

[0046] In one embodiment, the method provided by this invention may further include:

[0047] When a user query request is received, if the user query request is a specified sampling table query method, then a data query operation is performed from the sampling table.

[0048] In other words, if a user needs to perform a data query, a user query request will be sent to the time series database. If the user query request is to query a specified sample table, the data can only be queried in the sample table, rather than from the source table.

[0049] Furthermore, the statement corresponding to a user query request using the specified sample table query method is: FROM TABLE_NAME WITH SAMPLE. In other words, adding "WITH SAMPLE" after FROM TABLE_NAME indicates a query from the sample table.

[0050] The time-series database processing method provided in this invention first determines the sampling interval, then adds sampling table definition information to the source table creation statement to indicate the sampling interval of the sampling table, and then executes the source table creation statement, thereby creating a source table and a corresponding sampling table for the time-series database. When writing data to the source table, sampling is performed according to the sampling interval, and each collected data is added to the sampling table so that users can query data in the sampling table. Although the data structure of the sampling table and the source table is the same, the data volume is greatly reduced, thus making reading faster. Compared with the method of searching in the source table, data searching in the sampling table can be completed quickly, improving search efficiency.

[0051] Secondly, embodiments of the present invention provide a time-series database processing apparatus, comprising:

[0052] The frequency determination module is used to determine the sampling interval time based on the user's data requirements;

[0053] The statement determination module is used to add sampling table definition information to the source table creation statement; wherein, the sampling table definition information includes the sampling interval time of the sampling table;

[0054] The table creation module is used to execute the source table creation statement so that the time series database creates a source table and a sampling table corresponding to the source table.

[0055] The data sampling module is used to sample data according to the sampling interval when data is written to the source table, and add the collected data to the sampling table so that the user can query the data in the sampling table.

[0056] In one embodiment, the source table creation statement for adding sampling table definition information is: CREATE TABLE <source table name>([source table definition options])[WITH SAMPLE BY sampling interval time].

[0057] In one embodiment, the table creation module is further configured to: after creating the source table and the sampling table corresponding to the source table, add the sampling interval time to the metadata of the source table.

[0058] In one embodiment, the data sampling module is specifically used to: determine the next sampling time based on the previous sampling time and sampling interval time recorded in the metadata; determine whether the next sampling time has arrived; if so, write the data to be written to the source table at the next sampling time into the sampling table; after each sampling is completed, write the sampling time as the previous sampling time into the metadata of the source table; the time when the first piece of data is written into the source table is the first sampling time.

[0059] In one embodiment, the apparatus further includes:

[0060] The data query module is used to perform a data query operation from the sample table when a user query request is received, if the user query request is a specified sample table query method.

[0061] In one embodiment, the statement corresponding to a user query request using the specified sampling table query method is: FROMTABLE_NAME WITH SAMPLE.

[0062] In one embodiment, the apparatus further includes:

[0063] The data compression module is used to store the source table using a multi-level cold and hot compression storage technique.

[0064] It is understood that explanations, specific implementation methods, beneficial effects, examples, etc. of the contents of the apparatus provided in the embodiments of the present invention can be found in the corresponding parts of the method provided in the first aspect, and will not be repeated here.

[0065] Thirdly, embodiments of the present invention provide a computer-readable medium storing computer instructions, which, when executed by a processor, cause the processor to perform the method provided in the first aspect.

[0066] Specifically, a system or apparatus equipped with a storage medium may be provided, on which software program code implementing the functions of any of the embodiments described above is stored, and the computer (or CPU or MPU) of the system or apparatus may read and execute the program code stored in the storage medium.

[0067] In this case, the program code read from the storage medium can itself implement the function of any of the above embodiments, and therefore the program code and the storage medium storing the program code constitute part of the present invention.

[0068] Storage media embodiments for providing program code include floppy disks, hard disks, magneto-optical disks, optical disks (such as CD-ROM, CD-R, CD-RW, DVD-ROM, DVD-RAM, DVD-RW, DVD+RW), magnetic tapes, non-volatile memory cards, and ROMs. Alternatively, program code can be downloaded from a server computer via a communication network.

[0069] Furthermore, it should be clear that not only can the program code read by the computer be executed, but also the operating system or other components operating on the computer can be instructed based on the program code to perform some or all of the actual operations, thereby realizing the function of any of the embodiments described above.

[0070] Furthermore, it is understood that the program code read from the storage medium is written to the memory set in the expansion board inserted into the computer or to the memory set in the expansion module connected to the computer. Then, based on the instructions of the program code, the CPU or other components installed on the expansion board or expansion module execute some and all of the actual operations, thereby realizing the function of any of the above embodiments.

[0071] It is understood that explanations, specific implementation methods, beneficial effects, examples, etc. of the contents in the computer-readable medium provided in the embodiments of the present invention can be found in the corresponding parts of the method provided in the first aspect, and will not be repeated here.

[0072] Fourthly, one embodiment of this specification provides a computing device including a memory and a processor, wherein the memory stores executable code, and when the processor executes the executable code, it implements the method of any embodiment of the specification.

[0073] It is understood that explanations, specific implementation methods, beneficial effects, examples, etc. of the computing device provided in the embodiments of the present invention can be found in the corresponding parts of the method provided in the first aspect, and will not be repeated here.

[0074] The various embodiments in this specification are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the apparatus embodiments are basically similar to the method embodiments, so the description is relatively simple; relevant parts can be referred to the descriptions of the method embodiments.

[0075] Those skilled in the art will recognize that, in one or more of the examples above, the functions described in this invention can be implemented using hardware, software, widgets, or any combination thereof. When implemented in software, these functions can be stored in a computer-readable medium or transmitted as one or more instructions or code on a computer-readable medium.

[0076] The specific embodiments described above further illustrate the purpose, technical solution, and beneficial effects of the present invention. It should be understood that the above description is only a specific embodiment of the present invention and is not intended to limit the scope of protection of the present invention. Any modifications, equivalent substitutions, improvements, etc., made on the basis of the technical solution of the present invention should be included within the scope of protection of the present invention.

Claims

1. A time-series database processing method, characterized in that, include: Determine the sampling interval based on the user's data requirements; Add sampling table definition information to the source table creation statement; wherein, the sampling table definition information includes the sampling interval time of the sampling table; Execute the source table creation statement to create a source table and a corresponding sampling table in the time series database. When writing data to the source table, sampling is performed according to the sampling interval time, and each collected data is added to the sampling table so that the user can query the data in the sampling table. The source table creation statement for adding sampling table definition information is: CREATE TABLE <source table name> ([source table definition options]) [WITH SAMPLE BY sampling interval]; Also includes: After creating the source table and the corresponding sampling table, the sampling interval time is added to the metadata of the source table; When writing data to the source table, sampling is performed according to the sampling interval, including: The next sampling time is determined based on the previous sampling time and sampling interval recorded in the metadata. Determine whether the next sampling time has been reached; If so, the data to be written to the source table at the next sampling time will also be written to the sampling table; After each sampling is completed, the sampling time is written into the metadata of the source table as the previous sampling time; the time when the first piece of data is written to the source table is the first sampling time. Also includes: Upon receiving a user query request, if the user query request is a specified sampling table query method, then a data query operation is performed from the sampling table; The statement corresponding to a user query request using the specified sampling table lookup method is: FROM TABLE_NAME WITHSAMPLE; Also includes: The source table is stored using a multi-level cold and hot compression storage technique.

2. An apparatus for using the time-series database processing method according to claim 1, characterized in that, The method of claim 1 includes: The frequency determination module is used to determine the sampling interval time based on the user's data requirements; The statement determination module is used to add sampling table definition information to the source table creation statement; wherein, the sampling table definition information includes the sampling interval time of the sampling table; The table creation module is used to execute the source table creation statement so that the time series database creates a source table and a sampling table corresponding to the source table. The data sampling module is used to sample data according to the sampling interval when data is written to the source table, and add the collected data to the sampling table so that the user can query the data in the sampling table.

3. A computer-readable storage medium, characterized in that, It stores a computer program, which, when executed in a computer, causes the computer to perform the method described in claim 1.

4. A computing device, characterized in that, The system includes a memory and a processor, wherein the memory stores executable code, and the processor executes the executable code to implement the method of claim 1.

Images