Large data volume query method for monitoring system

Abstract

The invention discloses a large data volume query method for a monitoring system. A data splitting module, a data splitting progress query module and a data partition storage module are included. The data splitting module is used for performing time level splitting on the monitoring time sequence data to obtain split sequence data of each time level; the data write-in module is used for writing the obtained split sequence data of each time level into a data partition unit so as to obtain monitoring time sequence storage data of different time levels; the data splitting progress query module is used for obtaining the splitting progress of the monitoring time sequence data by the data splitting module and obtaining a progress query signal; and the data quick query module is used for receiving a query request of to-be-queried data sent by a preset client and obtaining target query data according to the monitoring time sequence storage data. According to the method, the problem that the query is slow as the data volume is larger along with the accumulation of time in the conventional method based on index and query strategy optimization is solved.

Description

Technical Field

[0001] This invention relates to the field of data query technology, and in particular to a method for querying large amounts of data in a monitoring system. Background Art

[0002] Offshore jacket platforms are subjected to harsh working environments and natural conditions due to long-term exposure to wind, waves, currents, seawater corrosion, and oil and gas production loads. To achieve health management of the offshore platform jackets, a health monitoring system is required, integrating stress and strain sensors, acceleration sensors, displacement sensors, tilt sensors, load strain gauges, anemometers, wave radar, cathodic protection sensors, and current meters to continuously collect monitoring data. The daily data volume is approximately 10GB. Over time, this massive data volume poses a significant challenge to data querying. Traditional methods based on indexing and query strategies tend to become increasingly slow as the data volume grows. For example, with 40,000 data entries, the average query speed is only 15 milliseconds; with 17 million data entries, the average query speed drops to 2.95 seconds. The larger the data, the lower the query efficiency. With continuous data growth, this can eventually lead to unqueryable data or even server denial-of-service. Summary of the Invention

[0003] This invention provides a method for querying large amounts of data in a monitoring system to overcome the aforementioned technical problems.

[0004] To achieve the above objectives, the technical solution of the present invention is as follows: A method for querying large amounts of data in a monitoring system includes the following steps: S1: Build a large-scale data query system for the monitoring system; The large data volume query system includes a data acquisition module, a data splitting module, a data splitting progress query module, a data writing module, a data partition storage module, and a data fast query module; The data acquisition module is used to acquire the monitoring time-series data of the offshore platform jacket health monitoring system; The data partition storage module is used to encode and obtain data partition units for data storage according to data type; The data splitting module includes at least a first time-level splitting submodule, a second time-level splitting submodule, and a third time-level splitting submodule, which are used to split the monitoring time series data according to the time-level data splitting strategy to obtain minute-level sequence data, hour-level sequence data, and day-level sequence data respectively, thereby obtaining the split sequence data of each time level. The data writing module is used to write the split sequence data of each time level obtained by the data splitting module into the data partitioning unit, so as to realize the storage of monitoring time series data according to the data type, and thus obtain monitoring time series storage data of different time levels. The data splitting progress query module is used to poll and obtain the splitting progress of the monitoring time series data by the first time-level splitting sub-module, the second time-level splitting sub-module, and the third time-level splitting sub-module in the data splitting module in a scheduled task manner, and obtain the progress query signal. The data splitting progress query module is also used to control the splitting sub-module corresponding to the data splitting module to stop running after confirming that the splitting sequence data of the corresponding time level has been split according to the progress query signal. The data fast query module is used to receive query requests for data to be queried from preset clients and obtain the target query data based on the monitoring time series stored data; S2: Implement data query for the monitoring system based on the big data query system.

[0005] Furthermore, the time-level data splitting strategy is as follows: The data splitting progress query module retrieves the current progress timestamps of each type of data in the corresponding monitoring time series data. The current progress timestamp is used as the starting time point. Based on the splitting time levels corresponding to the first, second, and third time-level splitting sub-modules in the data splitting module, the timestamp nodes that serve as the ending time points are determined to enable the data splitting at each time level to obtain the splitting sequence data of the corresponding time level.

[0006] Furthermore, the data writing module is used to write the split sequence data of each time level obtained by the data splitting module into the data partitioning unit according to the following writing rules: Use the start and end times as data query conditions, that is, use the split sequence data that is greater than the start time and less than the end time as the query conditions for time span data collection. The maximum and minimum values ​​of the data corresponding to the split sequence data, along with the corresponding query conditions, are written into the data partitioning unit.

[0007] Furthermore, the writing rules also include: After the current split sequence data is written through the data writing module, the end time point is updated to the current progress timestamp in the data splitting progress query module.

[0008] Furthermore, the data partition storage module is also equipped with a data query buffer and a data query hot table area; The data query buffer is a storage area with a fixed storage length used to store buffered data. The buffered data refers to the final data retained after the monitoring time-series data is split and written to the data partition table based on the time-level sequence data by the data writing module, and the data is written to the storage area simultaneously based on the buffer writing strategy. Buffered write strategy: Based on the first-in-first-out (FIFO) principle, data is written and deleted in chronological order according to a fixed storage length; The data query hotspot area is used to store periodic data; The periodic data refers to the periodically updated data that is periodically updated and written to the data query hot table area simultaneously when the monitoring time series data is split and written to the data partition table based on the time-level sequence data by the data writing module.

[0009] Furthermore, the data fast query module is equipped with an API interface service unit; The API interface service unit is equipped with multiple API interface service sub-units for receiving query requests for data to be queried from preset clients and querying priority based on timestamps. The API interface service subunit is used to determine the response data corresponding to the query request based on the query request for the data to be queried. Specifically, the timestamp query priority is as follows: If the query request for the data to be queried is within a one-minute query range, the API interface service sub-unit corresponding to the second level will be called first. If the query request for the data to be queried is within a one-hour query range, the API interface service sub-unit corresponding to the minute level will be called first. If the query request for the data to be queried is a data query condition within a day's query range, the API interface service sub-unit corresponding to the hour level will be called first. If the query request for the data to be queried is within a year, the API interface service sub-unit corresponding to the day level will be called first.

[0010] Furthermore, the data types of the monitoring time series data include at least: Data from pre-installed stress-strain sensors, acceleration sensors, displacement sensors, tilt sensors, load strain gauges, wind speed and direction meters, wave radar, cathodic protection sensors, and current meters are stored on the offshore platform jacket.

[0011] Beneficial Effects: This invention provides a method for querying large volumes of data in a monitoring system. A data partitioning storage module obtains storage data partition units based on data type encoding. A data splitting module splits the monitoring time-series data into time-level segments according to a time-level data splitting strategy, obtaining segmented sequence data for each time level. A data writing module writes the segmented sequence data obtained by the data splitting module into the data partition units, thus storing the monitoring time-series data according to data type. In a large amount of data that is not significant for decision-making, data is filtered by retaining the maximum and minimum data for each minute, hour, or day to obtain the most valuable data for querying. This improves data query efficiency while ensuring accuracy. A fast data query module sets different API interface service sub-units for different levels of query needs to query data at different time levels, greatly improving query efficiency. Attached Figure Description

[0012] 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 some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0013] Figure 1 This is a schematic diagram of the structure of the big data query method of the present invention applied to a monitoring system. DETAILED DESCRIPTION

[0014] To make the objectives, technical solutions, and advantages of the embodiments of the present invention clearer, 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 some embodiments of the present invention, not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0015] This embodiment provides a method for querying large amounts of data in a monitoring system, specifically including: S1: Constructing a large amount of data query system for the monitoring system; such as Figure 1 As shown, it includes a data acquisition module, a data splitting module, a data splitting progress query module, a data writing module, a data partition storage module, and a data fast query module; The data acquisition module is used to acquire the monitoring time-series data of the offshore platform jacket health monitoring system; The data partitioning storage module is used to encode and obtain data partitioning units for data storage according to data type. The data types of the monitoring time-series data include at least the data from stress-strain sensors, acceleration sensors, displacement sensors, tilt sensors, load strain gauges, anemometers, wave radar, cathodic protection sensors, and current meters pre-installed on the offshore platform jacket. The monitoring time-series data is partitioned according to date range, and combined with the database's built-in database tasks and stored procedure technology (such as MySQL), a new storage data partition table is automatically created daily to ensure that the data for each natural day has a corresponding independent partition table. Specifically, the data partition storage module also includes a data query buffer and a data query hot table area; the data query buffer is a storage area with a fixed storage length used to store buffered data; The buffered data refers to the final data retained after the monitoring time-series data is split and written to the data partition table based on the time-level sequence data by the data writing module, and the data is written to the storage area simultaneously based on the buffer writing strategy; the length of the buffer is fixed at 30 records. Buffered write strategy: Based on the first-in-first-out (FIFO) principle, data is written and deleted in chronological order according to a fixed storage length; The data query hotspot area is used to store periodic data; The periodic data refers to the periodically updated data that is written to the data query hot table area simultaneously when the monitoring time-series data is split and written to the data partition table based on the time-level sequence data through the data writing module. For example, in this embodiment, while writing data to the second-level data table, the data is simultaneously copied to the data query hot table area. In order to facilitate partition management, the data query hot table area stores the data volume of the most recent day. Through a scheduled task (executed once per period), only the most recent hot table data partition is retained, and expired data partition tables are deleted (in order to save hardware resources and ensure execution effectiveness, the scheduled task execution period is set to 1 hour) to periodically update the data in the data query hot table area. The data splitting module includes at least a first time-level splitting submodule, a second time-level splitting submodule, and a third time-level splitting submodule, which are used to split the monitoring time series data according to the time-level data splitting strategy to obtain minute-level sequence data, hour-level sequence data, and day-level sequence data respectively, thereby obtaining the split sequence data of each time level. In a specific embodiment, the time-level data splitting strategy is as follows: the current progress timestamp of each type of data in the corresponding monitoring time series data is obtained through the data splitting progress query module; The current progress timestamp is used as the starting time point. Based on the splitting time levels corresponding to the first, second, and third time-level splitting sub-modules in the data splitting module, the timestamp nodes that serve as the ending time points are determined to enable the data splitting at each time level to obtain the splitting sequence data of the corresponding time level.

[0016] For example: In this embodiment, the data splitting process based on the time-level data splitting strategy is as follows: ① Split into minute-by-minute data tables When splitting to a minute-level data table, the current progress timestamp of each type of data in the corresponding monitoring time series data is obtained through the data splitting progress query module; and the current progress timestamp is used as the starting time point, and the next minute 0 seconds is used as the ending time point to realize the splitting of minute-level sequence data; ② Split into hourly data tables When splitting to hourly data tables, the current progress timestamp of each type of data in the corresponding monitoring time series data is obtained through the data splitting progress query module; and the current progress timestamp is used as the starting time point, and the time from the current timestamp to the next hour 0 minutes 0 seconds is used as the ending time point, so as to realize the splitting of hourly sequence data; ③ Split into daily-level data tables The data splitting progress query module obtains the current progress timestamp of each type of data in the corresponding monitoring time series data; and uses the current progress timestamp as the starting time point and the time from that timestamp to 0:00:00 on the next day as the ending time point, so as to achieve the splitting of daily-level sequence data. The data writing module is used to write the split sequence data of each time level obtained by the data splitting module into the data partitioning unit, so as to realize the storage of monitoring time series data according to the data type, and thus obtain monitoring time series storage data of different time levels. And the writing rules of the data writing module are as follows: The start and end times are used as data query conditions; that is, the split sequence data that is greater than the start time and less than the end time is used as the query condition for the time span data collection. The maximum and minimum values ​​of the split sequence data, along with the corresponding query conditions, are written into the data partitioning unit. The writing rules also include... After the current split sequence data is written through the data writing module, the end time point is updated to the current progress timestamp in the data splitting progress query module.

[0017] The data splitting progress query module is used to poll and obtain the splitting progress of the first time-level splitting sub-module, the second time-level splitting sub-module, and the third time-level splitting sub-module of the data splitting module on the monitored time-series data in a time-slotted manner, and obtain a progress query signal; the data splitting progress query module is also used to control the corresponding splitting sub-module of the data splitting module to stop running after confirming that the splitting sequence data of the corresponding time level has been split according to the progress query signal. Specifically, the data splitting progress query module uses scheduled tasks to poll time-series data at various levels. The standard time interval for executing second-level tasks is 1 second, for minute-level tasks it's 1 minute, for hour-level tasks it's 1 hour, and for day-level tasks it's 1 day. In this embodiment, to increase the frequency of data collection and shorten the time of insufficient business data, the task interval can be shortened, and the number of task cycles can be increased. Preferably, in actual production practice, the standard time interval for executing second-level tasks is 3s to 5s. Specifically, the data splitting progress query module establishes a record table to record the data splitting progress, enabling the querying of the splitting progress of the monitored time-series data. The record table consists of a key and a current_timestamp. The key is used to record the type of split data, and the current_timestamp is used to represent the timestamp of the currently split data. The system polls and retrieves the splitting progress of the first, second, and third time-level splitting sub-modules of the data splitting module for monitoring time-series data, and obtains progress query signals. When the splitting progress of the corresponding sub-module reaches the completion stage, that is, the corresponding time-level splitting sub-module has completed the splitting of all monitoring time-series data, a stop splitting signal is sent to the corresponding sub-module; otherwise, the data splitting module continues to split the monitoring time-series data. The data fast query module is used to receive query requests for data to be queried from preset clients and obtain the target query data based on the monitoring time series stored data; In a specific embodiment, the data fast query module is equipped with an API interface service unit; The API interface service unit is equipped with multiple API interface service sub-units for receiving query requests for data to be queried from preset clients and querying priority based on timestamps. The API interface service subunit is used to determine the response data corresponding to the query conditions of the query request based on the query request for the data to be queried; The timestamp query priority refers to the query matching rules established for different time span query conditions, specifically as follows: If the query request for the data to be queried is within a one-minute query range, the API interface service sub-unit corresponding to the second level will be called first. If the query request for the data to be queried is within a one-hour query range, the API interface service sub-unit corresponding to the minute level will be called first. If the query request for the data to be queried is a data query condition within a day's query range, the API interface service sub-unit corresponding to the hour level will be called first. If the query request for the data to be queried is within a year, the API interface service sub-unit corresponding to the day level will be called first.

[0018] This embodiment also includes an API interface service subunit for querying data in the data query hot table area. When dealing with query business, the most frequent and unavoidable query is setting default query conditions. In this embodiment, the current default query condition is preferably data in the last 30 minutes. Under this default query condition, the API interface service subunit for querying data in the data query hot table area is directly called to avoid the problem of low query efficiency caused by large data volume, reduce the query workload and thus improve the efficiency of data query. S2: Implement data query for the monitoring system based on the large data volume query system.

[0019] In this embodiment, a data partitioning storage module obtains a storage data partition table based on data type encoding; a data splitting module performs time-level splitting of monitoring time-series data based on a time-level data splitting strategy to obtain time-level sequence data; and a data writing module splits the monitoring time-series data based on the time-level sequence data and writes it into the data partition table to obtain a timestamp data partition table. In a large amount of data that is not significant for decision-making, data is filtered by retaining the maximum and minimum data for each minute, hour, or day to obtain the most valuable data for querying, improving data query efficiency while ensuring accuracy. A fast data query module sets different API interface service sub-units to query different time levels of data for different levels of query needs, greatly improving query efficiency. Furthermore, using this system, data is split into hot table level, second level, minute level, hour level, and day level business data, and written using database partitioning. Different levels of data are read according to actual query business needs. When querying data, the average response time for each data query is approximately 40 milliseconds, improving query efficiency by approximately 60 times.

[0020] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it. Although the present invention has been described in detail with reference to the above embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the above embodiments, or replace some or all of the technical features therein with equivalents. However, these modifications or replacements do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present invention.

Claims

1. A method for querying large amounts of data in a monitoring system, characterized in that, Specifically, the following steps are included: S1: Build a large-scale data query system for the monitoring system; The large data volume query system includes a data acquisition module, a data splitting module, a data splitting progress query module, a data writing module, a data partition storage module, and a data fast query module; The data acquisition module is used to acquire the monitoring time-series data of the offshore platform jacket health monitoring system; The data partition storage module is used to encode and obtain data partition units for data storage according to data type; The data splitting module includes at least a first time-level splitting submodule, a second time-level splitting submodule, and a third time-level splitting submodule, which are used to split the monitoring time series data according to the time-level data splitting strategy and obtain minute-level sequence data, hour-level sequence data, and day-level sequence data respectively, thereby obtaining the split sequence data of each time level. The data writing module is used to write the split sequence data of each time level obtained by the data splitting module into the data partitioning unit, so as to realize the storage of monitoring time series data according to the data type, and thus obtain monitoring time series storage data of different time levels. The data splitting progress query module is used to poll and obtain the splitting progress of the monitoring time series data by the first time-level splitting sub-module, the second time-level splitting sub-module, and the third time-level splitting sub-module in the data splitting module in a scheduled task manner, and obtain the progress query signal. The data splitting progress query module is also used to control the corresponding splitting sub-module of the data splitting module to stop running after confirming that the splitting sequence data of the corresponding time level has been split based on the progress query signal. The data fast query module is used to receive query requests for data to be queried from preset clients and obtain the target query data based on the monitoring time series stored data; S2: Implement data query for the monitoring system based on the large data volume query system.

2. The method for querying large amounts of data in a monitoring system according to claim 1, characterized in that, The time-level data splitting strategy is as follows: The data splitting progress query module retrieves the current progress timestamps of each type of data in the corresponding monitoring time series data. The current progress timestamp is used as the starting time point. Based on the splitting time levels corresponding to the first, second, and third time-level splitting sub-modules in the data splitting module, the timestamp nodes that serve as the ending time points are determined to enable the data splitting at each time level to obtain the splitting sequence data of the corresponding time level.

3. The method for querying large amounts of data in a monitoring system according to claim 2, characterized in that, The data writing module is used to write the split sequence data of each time level obtained by the data splitting module into the data partitioning unit according to the following writing rules: Use the start and end times as data query conditions, that is, use the split sequence data that is greater than the start time and less than the end time as the query conditions for time span data collection. The maximum and minimum values ​​of the data corresponding to the split sequence data, along with the corresponding query conditions, are written into the data partitioning unit.

4. The method for querying large amounts of data in a monitoring system according to claim 3, characterized in that, The write rules also include: After the current split sequence data is written through the data writing module, the end time point is updated to the current progress timestamp in the data splitting progress query module.

5. The method for querying large amounts of data in a monitoring system according to claim 1, characterized in that, The data partition storage module is also equipped with a data query buffer and a data query hot table area; The data query buffer is a storage area with a fixed storage length used to store buffered data. The buffered data refers to the final data retained after the monitoring time-series data is split and written to the data partition table based on the time-level sequence data by the data writing module, and the data is written to the storage area simultaneously based on the buffer writing strategy. Buffered write strategy: Based on the first-in-first-out (FIFO) principle, data is written and deleted in chronological order according to a fixed storage length; The data query hotspot area is used to store periodic data; The periodic data refers to the periodically updated data that is periodically updated and written to the data query hot table area simultaneously when the monitoring time series data is split and written to the data partition table based on the time-level sequence data by the data writing module.

6. The method for querying large amounts of data in a monitoring system according to claim 1, characterized in that, The data fast query module is equipped with an API interface service unit; The API interface service unit is equipped with multiple API interface service sub-units for receiving query requests for data to be queried from preset clients and querying priority based on timestamps. The API interface service subunit is used to determine the response data corresponding to the query conditions of the query request based on the query request for the data to be queried; Specifically, the timestamp query priority is as follows: If the query request for the data to be queried is within a one-minute query range, the API interface service sub-unit corresponding to the second level will be called first. If the query request for the data to be queried is within a one-hour query range, the API interface service sub-unit corresponding to the minute level will be called first. If the query request for the data to be queried is a data query condition within a day's query range, the API interface service sub-unit corresponding to the hour level will be called first. If the query request for the data to be queried is within a year, the API interface service sub-unit corresponding to the day level will be called first.

7. The method for querying large amounts of data in a monitoring system according to claim 1, characterized in that, The data types of the monitoring time series data include at least: Data from pre-installed stress-strain sensors, acceleration sensors, displacement sensors, tilt sensors, load strain gauges, wind speed and direction meters, wave radar, cathodic protection sensors, and current meters are stored on the offshore platform jacket.

Images