Time sequence data query optimization method based on pre-grouping

Abstract

The invention discloses a time series data query optimization method based on pre-grouping, and relates to the technical field of Internet of Things data management and analysis. Comprising the steps of 1, creating a tag table and a data table according to a time sequence table of an opening service database, storing tags corresponding to equipment by using the tag table, storing timestamps and time sequence data collected by the corresponding equipment by using the data table, and 2, performing time sequence data query based on pre-grouping: scanning the tag table, performing pre-grouping on tags of the equipment according to the tag attributes, and performing time sequence data query on the basis of pre-grouping, the method comprises the following steps: acquiring tags of the same pre-grouped equipment, scanning corresponding time series data in a data table according to an entity id corresponding to each tag, performing aggregation calculation according to the time series data to obtain an aggregation calculation result of the time series data corresponding to the current same group tag, then acquiring a next equipment group to perform data table scanning and aggregation calculation, and merging the aggregation calculation results to obtain a data table; and returning a query result according to a combined aggregation calculation result.

Description

Technical Field

[0001] The invention discloses a method, which relates to the technical field of data management and analysis of the Internet of Things, and specifically is a time series data query optimization method based on pre-grouping. Background Art

[0002] The IoT environment contains a large amount of sensor data / real-time data, which has the characteristics of high frequency, large data volume, and low data value density, which puts forward new requirements and challenges for databases. The concept of time series database has been proposed in the database field, and open source time series database systems have emerged continuously, including InfluxDB, TimescaleDB, TDEngine, etc. Inspur has also launched a distributed multi-mode open service database that supports relational data models, time series data models, and predictive analysis models.

[0003] With the continuous promotion of time series databases, the demand for time series database systems in IoT applications is constantly increasing, but time series databases do not have more complex query processing capabilities, and query processing performance needs to be improved to meet the management and complex analysis needs of large-scale IoT systems. Summary of the invention

[0004] In view of the problems of the prior art, the present invention provides a time series data query optimization method based on pre-grouping, which can utilize the characteristics of the time series data storage engine to manage by device grouping, avoid grouping aggregation of large amounts of time series data, improve the efficiency of time series data aggregation, and thus improve the complex query performance of the time series database.

[0005] The specific scheme proposed by the present invention is:

[0006] The present invention provides a time series data query optimization method based on pre-grouping, comprising:

[0007] Step 1: Create a tag table and a data table based on the time series table of the service database. Use the tag table to store the tags corresponding to the device. The tags contain tag attributes and entity ids. Use the data table to store timestamps and the time series data collected by the corresponding device.

[0008] Step 2: Query time series data based on pre-grouping: Scan the tag table to pre-group the tags of the devices according to the label attributes, obtain the tags of the same pre-grouped devices, scan the corresponding time series data in the data table according to the entity ID corresponding to each tag, perform aggregation calculation based on the time series data, and obtain the aggregation calculation results of the time series data corresponding to the current same group of tags, then obtain the next device group to scan the data table and perform aggregation calculations, merge the aggregation calculation results, and return the query results based on the merged aggregation calculation results.

[0009] Furthermore, in the method for optimizing time series data query based on pre-grouping, in step 1, the tag table is used to store the tag corresponding to the device, and all tag attributes are continuously stored in the same file. The time series data in the data table is stored in files according to different tags. Each tag entity id corresponds to a data file, and all historical data of the tag is stored in the data file. All historical data are connected in a block manner, and the newly written data is appended to the end in a block manner.

[0010] Furthermore, step 2 in the time series data query optimization method based on pre-grouping specifically includes:

[0011] Divide the tag table into m different ranges according to the entity id, start m threads, and perform tag table scanning and pre-grouping processing respectively.

[0012] Pre-grouping: All entity IDs scanned from the tag table are pre-grouped according to tag attributes. After pre-grouping, tags are divided into k groups, namely Group 1, Group 2, ..., Group k. The k groups are shared by m threads.

[0013] According to the concurrency n of the data table scan, n threads are started to scan the data table simultaneously: one group is taken out from the k groups for processing each time. Each time the data table scanning thread processes, all entity IDs in this group are traversed, and time series data are read from the data table for aggregation calculation to obtain the aggregation calculation results. Then, the next group is taken to scan the data table and perform aggregation calculation, and the aggregation calculation results are merged. The query results are returned based on the merged aggregation calculation results.

[0014] Furthermore, in the method for optimizing time series data query based on pre-grouping, step 2 performs grouping and aggregation calculation on historical data in combination with a time window, including:

[0015] Divide the tag table into m different ranges according to the entity id, start m threads, and perform tag table scanning and pre-grouping processing respectively.

[0016] Pre-grouping: All entity IDs scanned from the tag table are pre-grouped according to tag attributes. After pre-grouping, tags are divided into k groups, namely Group 1, Group 2, ..., Group k. The k groups are shared by m threads.

[0017] According to the concurrency n of the data table scan, n threads are started to scan the data table simultaneously: one group is taken out from the k groups for processing each time. The thread scanning the data table will traverse all entity IDs in this group each time, read the time series data from the data table respectively, and group the time series data according to time_bucket.

[0018] Where time_bucket(timestamp) = timestamp%time_window, the time window size of time_bucket is time_window seconds,

[0019] Then, perform aggregation calculation on the time series data after grouping by time_bucket to obtain the aggregation calculation result. Then, take the next tag group to scan the data table and perform aggregation calculation. Merge the aggregation calculation results and return the query result based on the merged aggregation calculation result.

[0020] Furthermore, in the method for optimizing time series data query based on pre-grouping, the time series data is grouped according to time_bucket in step 2, including:

[0021] Calculate time_bucket for each time series data obtained in the pre-grouping.

[0022] Sort the time series data by time_bucket.

[0023] Traverse each time series data in the pre-grouping and group the time series data with the same time_bucket into one group.

[0024] The present invention also provides a time series data query optimization device based on pre-grouping, comprising a data table management module and a grouping query module.

[0025] The data table management module creates a tag table and a data table based on the time series table of the service database. The tag table is used to store the tags corresponding to the device. The tags contain tag attributes and entity ids. The data table is used to store the timestamps and the time series data collected by the corresponding devices.

[0026] The grouping query module performs time series data query based on pre-grouping: scan the tag table to pre-group the device tags according to the tag attributes, obtain the tags of the same pre-grouped devices, scan the corresponding time series data in the data table according to the entity id corresponding to each tag, perform aggregation calculation based on the time series data, and obtain the aggregation calculation result of the time series data corresponding to the current same group tag, then obtain the next device group to scan the data table and perform aggregation calculation, merge the aggregation calculation results, and return the query result based on the merged aggregation calculation result.

[0027] Furthermore, the data table management module of the time series data query optimization device based on pre-grouping utilizes the label corresponding to the tag table storage device, stores all label attributes continuously in the same file, utilizes the time series data in the data table to store files according to different labels, each label entity id corresponds to a data file, and stores all historical data of the label in the data file, connects all historical data in a block manner, and appends the newly written data to the end in a block manner.

[0028] Furthermore, the grouping query module of the time series data query optimization device based on pre-grouping divides the tag table into m different ranges according to the entity id, starts m threads, and performs tag table scanning and pre-grouping processing respectively.

[0029] Pre-grouping: All entity IDs scanned from the tag table are pre-grouped according to tag attributes. After pre-grouping, tags are divided into k groups, namely Group 1, Group 2, ..., Group k. The k groups are shared by m threads.

[0030] According to the concurrency n of the data table scan, n threads are started to scan the data table simultaneously: one group is taken out from the k groups for processing each time. Each time the data table scanning thread processes, all entity IDs in this group are traversed, and time series data are read from the data table for aggregation calculation to obtain the aggregation calculation results. Then, the next group is taken to scan the data table and perform aggregation calculation, and the aggregation calculation results are merged. The query results are returned based on the merged aggregation calculation results.

[0031] Furthermore, the grouping query module of the time series data query optimization device based on pre-grouping performs grouping aggregation calculation on historical data in combination with a time window, including:

[0032] Divide the tag table into m different ranges according to the entity id, start m threads, and perform tag table scanning and pre-grouping processing respectively.

[0033] Pre-grouping: All entity IDs scanned from the tag table are pre-grouped according to tag attributes. After pre-grouping, tags are divided into k groups, namely Group 1, Group 2, ..., Group k. The k groups are shared by m threads.

[0034] According to the concurrency n of the data table scan, n threads are started to scan the data table simultaneously: one group is taken out from the k groups for processing each time. The thread scanning the data table will traverse all entity IDs in this group each time, read the time series data from the data table respectively, and group the time series data according to time_bucket.

[0035] Where time_bucket(timestamp) = timestamp%time_window, the time window size of time_bucket is time_window seconds,

[0036] Then, perform aggregation calculation on the time series data after grouping by time_bucket to obtain the aggregation calculation result. Then, take the next tag group to scan the data table and perform aggregation calculation. Merge the aggregation calculation results and return the query result based on the merged aggregation calculation result.

[0037] Furthermore, the grouping query module of the time series data query optimization device based on pre-grouping groups the time series data according to time_bucket, including:

[0038] Calculate time_bucket for each time series data obtained in the pre-grouping.

[0039] Sort the time series data by time_bucket.

[0040] Traverse each time series data in the pre-grouping and group the time series data with the same time_bucket into one group.

[0041] The benefits of the present invention are:

[0042] Based on the time series model of the open service database, the present invention uses the device tag grouping characteristics to perform pre-grouping, then scans the time series data according to the device tag grouping, and directly performs aggregation calculation on the scan results, which can avoid grouping calculation of a large amount of time series data and improve the aggregation calculation performance of time series data. It avoids grouping aggregation after time series data scanning, so it can allow the aggregation operator to be pushed down to the time series storage engine during the time series data scanning process, thereby greatly improving the time series data scanning performance. BRIEF DESCRIPTION OF THE DRAWINGS

[0043] Figure 1 It is a schematic diagram of the storage mode of the tag table and the data table involved in the method of the present invention.

[0044] Figure 2 The present invention is a flowchart of a method for querying pre-grouped time series data.

[0045] Figure 3 It is a schematic diagram of the query flow of the method of the present invention involving time series data time_bucket grouping. DETAILED DESCRIPTION

[0046] The present invention is further described below in conjunction with the accompanying drawings and specific embodiments so that those skilled in the art can better understand the present invention and implement it, but the embodiments are not intended to limit the present invention.

[0047] Example 1

[0048] The present invention provides a time series data query optimization method based on pre-grouping, comprising:

[0049] Step 1: Create a tag table and a data table based on the time series table of the service database. Use the tag table to store the tags corresponding to the device. The tags contain tag attributes and entity IDs. Use the data table to store timestamps and the time series data collected by the corresponding device.

[0050] The tag table is used to store the tags corresponding to the device, and all tag attributes are continuously stored in the same file. The time series data in the data table is stored in files according to different tags. Each tag entity id corresponds to a data file. The data file stores all historical data of the tag. All historical data are connected in block mode, and the newly written data is appended to the end in block mode.

[0051] To improve the retrieval efficiency of the tag table, you can create an index for the primary tag on the tag table. You can also allow users to create other indexes. To improve the retrieval efficiency of the data table, you can also create a time index for the data block.

[0052] Step 2: Query time series data based on pre-grouping: Scan the tag table to pre-group the tags of the devices according to the label attributes, obtain the tags of the same pre-grouped devices, scan the corresponding time series data in the data table according to the entity ID corresponding to each tag, perform aggregation calculation based on the time series data, and obtain the aggregation calculation results of the time series data corresponding to the current same group of tags, then obtain the next device group to scan the data table and perform aggregation calculations, merge the aggregation calculation results, and return the query results based on the merged aggregation calculation results.

[0053] Step 2 may further specifically include:

[0054] Divide the tag table into m different ranges according to the entity id, start m threads, and perform tag table scanning and pre-grouping processing respectively.

[0055] Pre-grouping: All entity IDs scanned from the tag table are pre-grouped according to tag attributes. After pre-grouping, tags are divided into k groups, namely Group 1, Group 2, ..., Group k. The k groups are shared by m threads.

[0056] According to the concurrency n of the data table scan, n threads are started to scan the data table simultaneously: one group is taken out from the k groups for processing each time. Each time the data table scanning thread processes, all entity IDs in this group are traversed, and time series data are read from the data table for aggregation calculation to obtain the aggregation calculation results. Then, the next group is taken to scan the data table and perform aggregation calculation, and the aggregation calculation results are merged. The query results are returned based on the merged aggregation calculation results.

[0057] The present invention can greatly improve the complex query processing capability of the time series database system, enhance the competitiveness of the time series database system, and better meet the growing query needs of Internet of Things applications.

[0058] Example 2

[0059] Based on Example 1, in step 2 of the method of the present invention, grouping and aggregation calculation is performed on historical data in combination with a time window, including:

[0060] Divide the tag table into m different ranges according to the entity id, start m threads, and perform tag table scanning and pre-grouping processing respectively.

[0061] Pre-grouping: All entity IDs scanned from the tag table are pre-grouped according to tag attributes. After pre-grouping, tags are divided into k groups, namely Group 1, Group 2, ..., Group k. The k groups are shared by m threads.

[0062] According to the concurrency n of the data table scan, n threads are started to scan the data table simultaneously: one group is taken out from the k groups for processing each time. The thread scanning the data table will traverse all entity IDs in this group each time, read the time series data from the data table respectively, and group the time series data according to time_bucket, including:

[0063] Calculate time_bucket for each time series data obtained in the pre-grouping.

[0064] Sort the time series data by time_bucket.

[0065] Traverse each time series data in the pre-grouping and group the time series data with the same time_bucket into one group.

[0066] Where time_bucket(timestamp) = timestamp%time_window, the time window size of time_bucket is time_window seconds,

[0067] Then, perform aggregation calculation on the time series data after grouping by time_bucket to obtain the aggregation calculation result. Then, take the next tag group to scan the data table and perform aggregation calculation. Merge the aggregation calculation results and return the query result based on the merged aggregation calculation result.

[0068] The present invention provides a time series data query optimization solution based on time_bucket grouping, which effectively scans time series data in time periods, thereby greatly improving the time series data scanning performance.

[0069] Example 3

[0070] The present invention also provides a time series data query optimization device based on pre-grouping, comprising a data table management module and a grouping query module.

[0071] The data table management module creates a tag table and a data table based on the time series table of the service database. The tag table is used to store the tags corresponding to the device. The tags contain tag attributes and entity ids. The data table is used to store the timestamps and the time series data collected by the corresponding devices.

[0072] The grouping query module performs time series data query based on pre-grouping: scan the tag table to pre-group the device tags according to the tag attributes, obtain the tags of the same pre-grouped devices, scan the corresponding time series data in the data table according to the entity id corresponding to each tag, perform aggregation calculation based on the time series data, and obtain the aggregation calculation result of the time series data corresponding to the current same group tag, then obtain the next device group to scan the data table and perform aggregation calculation, merge the aggregation calculation results, and return the query result based on the merged aggregation calculation result.

[0073] As the information interaction and execution process between the modules in the above-mentioned device are based on the same concept as the embodiment of the method of the present invention, the specific contents can be found in the description of the embodiment of the method of the present invention and will not be repeated here.

[0074] Similarly, the device of the present invention uses the device tag grouping characteristics to perform pre-grouping based on the time series model of the open service database, and then scans the time series data according to the device tag grouping, and directly performs aggregation calculation on the scan results, which can avoid grouping calculations of a large amount of time series data and improve the aggregation calculation performance of time series data. It avoids grouping aggregation after time series data scanning, so it can allow the aggregation operator to be pushed down to the time series storage engine during the time series data scanning process, thereby greatly improving the time series data scanning performance.

[0075] It should be noted that not all steps and modules in the above-mentioned processes and device structures are necessary, and some steps or modules can be ignored according to actual needs. The execution order of each step is not fixed and can be adjusted as needed. The system structure described in the above-mentioned embodiments can be a physical structure or a logical structure, that is, some modules may be implemented by the same physical entity, or some modules may be implemented by multiple physical entities, or some components in multiple independent devices may be implemented together.

[0076] The above-described embodiments are only preferred embodiments for fully illustrating the present invention, and the protection scope of the present invention is not limited thereto. Equivalent substitutions or changes made by those skilled in the art based on the present invention are within the protection scope of the present invention. The protection scope of the present invention shall be subject to the claims.

Claims

1. A time series data query optimization method based on pre-grouping, characterized by include: Step 1: Create a tag table and a data table based on the time series table of the service database. Use the tag table to store the tags corresponding to the device. The tags contain tag attributes and entity ids. Use the data table to store timestamps and the time series data collected by the corresponding device. Step 2: Query time series data based on pre-grouping: Scan the tag table to pre-group the tags of the devices according to the label attributes, obtain the tags of the same pre-grouped devices, scan the corresponding time series data in the data table according to the entity ID corresponding to each tag, perform aggregation calculation based on the time series data, and obtain the aggregation calculation results of the time series data corresponding to the current same group of tags, then obtain the next device group to scan the data table and perform aggregation calculations, merge the aggregation calculation results, and return the query results based on the merged aggregation calculation results.

2. According to the method for optimizing time series data query based on pre-grouping described in claim 1, it is characterized in that in step 1, the tag table is used to store the tag corresponding to the device, all the tag attributes are continuously stored in the same file, and the time series data in the data table is stored in files according to different tags. The entityid of each tag corresponds to a data file, and all the historical data of the tag is stored in the data file. All the historical data are connected in a block manner, and the newly written data is appended to the end in a block manner.

3. The method for optimizing time series data query based on pre-grouping according to claim 1, characterized in that Step 2 specifically includes: Divide the tag table into m different ranges according to the entity id, start m threads, and perform tag table scanning and pre-grouping processing respectively. Pre-grouping: All entityids scanned from the tag table are pre-grouped according to tag attributes. After pre-grouping, tags are divided into k groups, namely Group 1, Group 2, ..., Group k. The k groups are shared by m threads. According to the concurrency n of the data table scan, n threads are started to scan the data table simultaneously: one group is taken out from the k groups for processing each time. Each time the data table scanning thread processes, all entity IDs in this group are traversed, and time series data are read from the data table for aggregation calculation to obtain the aggregation calculation results. Then, the next group is taken to scan the data table and perform aggregation calculation, and the aggregation calculation results are merged. The query results are returned based on the merged aggregation calculation results.

4. The method for optimizing time series data query based on pre-grouping according to claim 1, characterized in that In step 2, historical data is grouped and aggregated in combination with the time window, including: Divide the tag table into m different ranges according to the entity id, start m threads, and perform tag table scanning and pre-grouping processing respectively. Pre-grouping: All entityids scanned from the tag table are pre-grouped according to tag attributes. After pre-grouping, tags are divided into k groups, namely Group 1, Group 2, ..., Group k. The k groups are shared by m threads. According to the concurrency n of the data table scan, n threads are started to scan the data table simultaneously: one group is taken out from the k groups for processing each time. The thread scanning the data table will traverse all entityids in this group each time, read the time series data from the data table respectively, and group the time series data according to time_bucket. Where time_bucket(timestamp) = timestamp%time_window, the time window size of time_bucket is time_window seconds, Then, perform aggregation calculation on the time series data after grouping by time_bucket to obtain the aggregation calculation result. Then, take the next tag group to scan the data table and perform aggregation calculation. Merge the aggregation calculation results and return the query result based on the merged aggregation calculation result.

5. The method for optimizing time series data query based on pre-grouping according to claim 4, characterized in that In step 2, the time series data is grouped according to time_bucket, including: Calculate time_bucket for each time series data obtained in the pre-grouping. Sort the time series data by time_bucket. Traverse each time series data in the pre-grouping and group the time series data with the same time_bucket into one group.

6. A time series data query optimization device based on pre-grouping, characterized in that Including data table management module and group query module, The data table management module creates a tag table and a data table based on the time series table of the service database. The tag table is used to store the tags corresponding to the device. The tags contain tag attributes and entity ids. The data table is used to store the timestamps and the time series data collected by the corresponding devices. The grouping query module performs time series data query based on pre-grouping: scan the tag table to pre-group the device tags according to the tag attributes, obtain the tags of the same pre-grouped devices, scan the corresponding time series data in the data table according to the entityid corresponding to each tag, perform aggregation calculation based on the time series data, and obtain the aggregation calculation results of the time series data corresponding to the current same group tag, then obtain the next device group to scan the data table and perform aggregation calculations, merge the aggregation calculation results, and return the query results based on the merged aggregation calculation results.

7. The device for optimizing time series data query based on pre-grouping according to claim 6, characterized in that The data table management module uses the tag table to store the tags corresponding to the device, stores all tag attributes continuously in the same file, and uses the time series data in the data table to store files according to different tags. Each tag's entityid corresponds to a data file, which stores all historical data of the tag. All historical data is connected in block mode, and the newly written data is appended to the end in block mode.

8. The device for optimizing time series data query based on pre-grouping according to claim 6, characterized in that The group query module divides the tag table into m different ranges according to entityid, starts m threads, and performs tag table scanning and pre-grouping processing respectively. Pre-grouping: All entityids scanned from the tag table are pre-grouped according to tag attributes. After pre-grouping, tags are divided into k groups, namely Group 1, Group 2, ..., Group k. The k groups are shared by m threads. According to the concurrency n of the data table scan, n threads are started to scan the data table simultaneously: one group is taken out from the k groups for processing each time. Each time the data table scanning thread processes, all entity IDs in this group are traversed, and time series data are read from the data table for aggregation calculation to obtain the aggregation calculation results. Then, the next group is taken to scan the data table and perform aggregation calculation, and the aggregation calculation results are merged. The query results are returned based on the merged aggregation calculation results.

9. The device for optimizing time series data query based on pre-grouping according to claim 6, characterized in that The group query module performs group aggregation calculations on historical data in combination with time windows, including: Divide the tag table into m different ranges according to the entity id, start m threads, and perform tag table scanning and pre-grouping processing respectively. Pre-grouping: All entityids scanned from the tag table are pre-grouped according to tag attributes. After pre-grouping, tags are divided into k groups, namely Group 1, Group 2, ..., Group k. The k groups are shared by m threads. According to the concurrency n of the data table scan, n threads are started to scan the data table simultaneously: one group is taken out from the k groups for processing each time. The thread scanning the data table will traverse all entityids in this group each time, read the time series data from the data table respectively, and group the time series data according to time_bucket. Where time_bucket(timestamp) = timestamp%time_window, the time window size of time_bucket is time_window seconds, Then, perform aggregation calculation on the time series data after grouping by time_bucket to obtain the aggregation calculation result. Then, take the next tag group to scan the data table and perform aggregation calculation. Merge the aggregation calculation results and return the query result based on the merged aggregation calculation result.

10. The device for optimizing time series data query based on pre-grouping according to claim 9, characterized in that The group query module groups time series data by time_bucket, including: Calculate time_bucket for each time series data obtained in the pre-grouping. Sort the time series data by time_bucket. Traverse each time series data in the pre-grouping and group the time series data with the same time_bucket into one group.

Images