A hierarchical storage system for historical data of a comprehensive monitoring system

Abstract

The present invention proposes a hierarchical storage system for historical data of a comprehensive monitoring system, which divides the historical data of the rail transit comprehensive monitoring system into four levels according to the granularity of the data and the usage scenarios, and improves the efficiency of data query and reduces the access pressure through the multi-dimensional table partitioning principle and the unified table building method; in terms of data processing, the management thread of the thread pool is used to obtain the number of various data processing tasks, and weighted calculation is performed on them. The tasks are arranged in descending order according to the absolute value of the difference between the calculation result and the threshold, and the tasks are dynamically allocated to achieve load balancing between multiple task threads, thereby improving the efficiency of data statistics and warehousing.

Description

Technical Field

[0001] The present invention relates to the research and development of historical storage software for an integrated rail transit monitoring system (ISCS), belongs to the field of automation system / software design, and specifically relates to a hierarchical storage system for historical data of an integrated monitoring system. Background Art

[0002] The Integrated Supervisory Control System (ISCS) for rail transit is a large-scale distributed automation system. Currently, data storage in this system still relies on a relational database. However, the amount of historical data has grown rapidly, from tens of millions to billions of rows. Furthermore, data relationships have become more complex and hierarchical. Therefore, it is necessary to rationally stratify the historical data in the ISCS to ensure a more organized historical data system and facilitate the location and understanding of tables used by upper-layer applications. Furthermore, as the system continues to operate, data storage volume rapidly increases. Relying on a single, rule-based table partitioning approach significantly reduces the efficiency of data insertion and query retrieval, significantly increasing the execution time of tasks such as graphs and reports. Regarding data processing, simply using multithreading to perform fixed statistical and data entry operations can lead to excessive load on some threads when the data volume is large or the execution time is long, while others become idle. This results in significantly reduced data entry efficiency and unnecessary server resource usage.

[0003] Prior art CN115438126A discloses a data hierarchical management and storage method and system based on a DCIM system for hierarchical data storage management. This method not only meets the requirements of high-performance real-time data query, ensures the stability of DCIM system-related operations, but also can store more data. However, this invention stores basic information of equipment in the DCIM system in a relational database, stores real-time data of equipment measurement points in the DCIM system in a KV database, and stores historical data of equipment measurement points in the DCIM system in a NoSQL database, thereby ensuring the storage and display of basic data and the real-time display of equipment measurement point data. However, this solution only describes a hierarchical storage method and does not optimize database table partitioning and data processing. Relying solely on hierarchical data storage in different databases, it still cannot solve the problem of low database query efficiency in the scenario of querying historical data over a long span of time.

[0004] The existing technical solution CN109783695B discloses a method and device for storing historical data in a rail transit integrated monitoring system.

[0005] Although it solves the problem of storing massive amounts of historical data in the rail transit integrated monitoring system, and can store the massive amounts of historical data generated daily in the rail transit integrated monitoring system without affecting the operation of the overall dispatching system, this invention creates a data table of historical data in the rail transit integrated monitoring system by operating HBase via Phoenix; when the acquired historical data reaches a preset number, this batch of historical data is submitted in batches to the historical data storage table of the corresponding application in the HBase database through the database middleware VDB. However, this solution only describes the use of the HBase database to replace the relational database to store historical data. There are many types of historical data in the rail transit integrated monitoring system, such as sampling data, statistical data, business data, etc. If these data are not stored in layers, the upper-level application will need to identify and distinguish various types of data by itself when querying historical data, which reduces the overall query efficiency. Summary of the Invention

[0006] The present invention discloses a hierarchical storage system for historical data of a comprehensive monitoring system. Based on the hierarchical storage method for historical data of "time series library + relational library", the historical data of the rail transit comprehensive monitoring system is divided into four levels according to the granularity of the data and the usage scenario, namely the sampling data layer, the primary statistical data layer, the secondary statistical data layer, and the business subject data layer. The data of the sampling data layer and the primary statistical data layer are stored in different instances of the time series library, while the data of the secondary statistical data layer and the business subject data layer are stored in the relational library. The efficiency of data query is improved and the access pressure is reduced by the multi-dimensional table partitioning principle and the unified table building method. The technical solution also discloses a data processing method with dynamic load balancing based on a thread pool. The management thread dynamically allocates tasks through weighted calculation of the processing time and pressure of the tasks, realizes load balancing between multiple task threads, and improves the efficiency of data statistics and warehousing.

[0007] The technical solution of this application is:

[0008] A hierarchical storage system for historical data of a comprehensive monitoring system stores historical data in layers, including a real-time database, a relational database, and a time series database; the real-time database stores the historical data of the comprehensive monitoring system, and the hierarchical storage data layers include a sampling data layer, a primary statistical data layer, a secondary statistical data layer, and a business subject data layer;

[0009] Historical data is stored and written into a real-time database according to the type of data application scenario. Sampled data is generated through regular statistics. After regular statistics are performed on the sampled data, primary statistical data is extracted and stored in a time series database. Statistical data is queried from the time series database. After statistical analysis, secondary statistical data is obtained and stored in a relational database.

[0010] Based on the database table partitioning principle, the sampled data is classified and saved, and a primary statistical data table and a secondary statistical data table are established to store the primary statistical data and the secondary statistical data respectively; the data processing load balancing based on the thread pool is used to perform statistics and storage operations.

[0011] The sampling data layer samples historical data to obtain sampling data; historical data includes telemetry data, telesignaling data and electricity measurement;

[0012] The sampling data layer samples the historical data of the real-time database. The sampling data layer writes the historical data into an instance of the real-time database through second-level sampling storage according to the type of data application scenario, and forms statistical data through regular statistics.

[0013] The primary statistical data layer generates primary statistical data from sampled data through a periodic statistical method and stores the primary statistical data in an instance of the time series library. The specific business storage service or ETL tool queries the primary statistical data from the time series library, obtains secondary statistical data through a secondary data statistical method, and stores it in the relational library. The data in the business subject data layer is business application data formed by refining the secondary statistical data based on the business. The actual business logic of the business layer application determines the data storage method.

[0014] Telemetered data includes voltage, current, power, power factor and temperature; telesignaling data includes the value and time of each change; and energy measurements include active energy and reactive energy.

[0015] The secondary data statistics method includes periodic statistics of the telemetry data in the sampling data layer, and setting the statistical period for each telemetry data point; the periodic statistical content of the telemetry data includes the maximum value, minimum value, average value and instantaneous value at the end of the period within the period; the secondary data statistics method also includes the number of changes in the telesignal within the period and the duration of the state, as well as the accumulated amount of electricity.

[0016] The database table partitioning principle specifically includes the following steps:

[0017] The integrated monitoring system adopts object-oriented design. Each sampling data corresponds to a data object. Each sampling data is set with a unique identification number ObjectId. For the sampling data layer and the one-time statistical data layer, the sampling data and the one-time statistical data are classified based on the identification number ObjectId and data type of the sampling point and saved in different sampling data tables. The sampling data table name is set to HD_ <attribute> _ <index>Format, where attribute indicates the sampling data type, which includes integer and floating-point types. Index is the integer obtained by converting the lower 32 bits of the ObjectId of the sampling data in the real-time database to decimal and dividing it by 100.

[0018] After acquiring the sampled data, the sampled data type is determined to determine the enumeration value of the attribute in the primary statistical data table name. The enumeration value of the attribute is represented as nType. Then, the ObjectId of the sampled data is parsed, the lower 32 bits are converted to decimal, and the result is divided by 100 and the remainder is obtained to obtain a primary statistical data table number nIndex. The sampled data will be stored in a primary statistical data table named HD_nType_nIndex.

[0019] A secondary statistical data table is created in the relational database. The secondary statistical data table is divided into tables based on time, OType and attribute. The table name format of the secondary statistical data table is H <otype> _ <attribute> _ <yyyymm>;

[0020] For the relational database that stores data in the secondary statistical data layer and the business data layer, the secondary statistical data table must be created in advance before the database write operation can be performed; the table is divided according to the three dimensions of time, OType and attribute, and the table name format of the secondary statistical data table is H <otype> _ <attribute> _ <yyyymm>OType represents the object type enumeration value of the object to which the statistical data belongs in the real-time database; attribute represents the same meaning as the attribute in the time series database table partitioning principle, indicating the type of data; yyyymm represents the time format.

[0021] The data attributes of the sampling data table and the one-time statistical data table in the time series library include the timestamp time, the indicator column field, and the dimension column tags. The structure of the sampling data table and the one-time statistical data table is the same. The timestamp time is the time of each data record and is the primary index of the sampling data table and the one-time statistical data table.

[0022] Indicator column field: includes the value field to indicate the value of the corresponding row data;

[0023] The dimension column tags includes the ObjectId, OType, and StatisticsType fields. The ObjectId data type is a 64-bit integer, representing the unique identifier objectId of the corresponding row data; OType is a 32-bit integer, representing the object type of the corresponding row data; the StatisticsType field is a string type, indicating the category of the corresponding data. Avg represents the average value, Min represents the minimum value, Max represents the maximum value, Acc represents the accumulated value, and Limit represents the limit value.

[0024] The fields of the secondary statistical data table include:

[0025] Occurrencetime: the time when the statistics are generated;

[0026] ObjectId: The data type is a 64-bit integer, which indicates the unique identifier objectId of the sampling point corresponding to the statistical data in the real-time database;

[0027] StatisticsType: string type, indicating the type of statistical value. The specific type is consistent with the StatisticsType field in the dimension column of the time series library table;

[0028] Value: represents the value of the statistic;

[0029] StartOffSet: indicates the start time offset of the statistical period;

[0030] EndOffSet: indicates the end time offset of the statistical period.

[0031] The data processing load balancing based on thread pool specifically includes the following steps:

[0032] S101, setting statistical processing of sampled data into different tasks, classifying them into different categories according to the statistical value type, and presetting a load weighting coefficient according to the complexity of the task processing logic;

[0033] S102, establishing a thread pool, which includes a management thread and a number of task threads. The management thread is responsible for assigning tasks to each task thread, and the task thread processes the assigned tasks;

[0034] S103, initializing the thread pool. When the thread pool is initialized, the task thread load detection time, the task load weighting coefficient, and the preset task thread load threshold θ of the load management thread are loaded, and the task thread load is managed based on the load balancing process.

[0035] When the thread pool is initialized, the task thread load detection time of the management thread, the load weighting coefficients of various tasks and the preset task thread load threshold θ are loaded;

[0036] S104: After the management thread is started, it randomly assigns tasks to each task thread to perform statistical tasks. The task thread records the number of times each task is executed within a detection time interval in the thread. After the task thread load detection time has elapsed, the management thread obtains the recorded execution count from each task thread and multiplies the execution count of each task by the load weighting coefficient corresponding to the task to obtain the load w of each task thread during the previous detection time.

[0037] S105: The management thread subtracts the load w of each task thread from a preset task thread load threshold θ to obtain a load difference Δ. Based on the load difference Δ, an overloaded thread queue and a lightly loaded thread queue are obtained. The number of overloaded thread queues and the number of lightly loaded thread queues are n and m, respectively. The absolute value of the load difference of each thread in the overloaded thread queue and the lightly loaded thread queue is c = |w-θ|. The overloaded thread queues and the lightly loaded thread queues are arranged in descending order according to the absolute value of the load difference, respectively, to obtain overloaded thread descending queues D = {d1, d2…d i …d n } and the light-load thread descending queue R={r1,r2…r j …r m }, the load difference between the overloaded thread and the lightly loaded thread is C = {c1, c2…c i …c n } and S={s1,s2…s j …s m }, i=1,2…n; j=1,2…m;

[0038] If n≥m, that is, the number of overloaded threads is greater than the number of lightly loaded threads, then traverse the descending queue of overloaded threads D={d1,d2…d i …d n}; If the load difference c of the i-th overloaded thread d i is less than the load difference s1 of the lightly loaded thread r1, the management thread transfers the part of the tasks executed in the d i thread to the r1 thread, and the load required for this part of the tasks is equal to the load difference c of the overloaded thread d i ; If the load difference c of the overloaded thread d i is greater than the load difference s1 of the lightly loaded thread r1, the management thread sequentially transfers the part of the tasks executed in the overloaded thread d i to r1, r2,..., r i such that the load differences c1, c2,..., c of the lightly loaded threads except the r i thread are equal to 0, and the load required for this part of the tasks is equal to the load difference s1 of the lightly loaded thread r1; delete the threads with a load difference equal to 0 from the lightly loaded thread queue after the task transfer, and at the same time move the element subscripts in the lightly loaded thread queue forward; i t t thread; t of the lightly loaded threads are equal to 0, and the load required for this part of the tasks is equal to the load difference c of the overloaded thread d t-1 ; If the load difference c of the overloaded thread d

[0039] If n < m, that is, the number of overloaded threads is less than the number of lightly loaded threads, traverse the descending queue of lightly loaded threads; if the load difference s of the i-th lightly loaded thread r i is less than the load difference c1 of the overloaded thread d1, the management thread will let the r i thread receive part of the tasks executed by the d1 thread, and the load required for this part of the tasks is equal to the load difference s of the lightly loaded thread r i ; If the load difference s of the i-th lightly loaded thread r i is greater than the load difference c1 of the overloaded thread d1, the management thread will sequentially transfer part of the tasks of the overloaded threads d1, d2,..., d i to the r i thread until the load difference s i is equal to 0; after the task transfer, update the load difference of the task lightly loaded thread queue, and delete the overloaded threads with a load difference equal to 0 from the overloaded thread queue; determine whether the load difference of the r t thread is equal to 0, if it is equal to 0, perform load balancing for the next lightly loaded thread, otherwise, the management thread continues to let the r i thread receive part of the tasks executed by the d1 thread to achieve load balancing of each task thread. i ; i Preferably, the principle of load balancing processing is that if the difference between the load of the task thread and the load threshold θ is less than 5%, the thread is considered a normal load thread and does not participate in load balancing. i

[0040] ​

[0041] Preferably, ObjectId: data type is a 64-bit integer.

[0042] Compared with the prior art, the present invention has the following beneficial effects:

[0043] The present application discloses a hierarchical storage system for historical data of an integrated monitoring system, which divides the historical data of the rail transit integrated monitoring system into four levels according to the granularity of the data and the usage scenarios, namely, the sampling data layer, the one-time statistical data layer, the one-time statistical data layer, and the business subject data layer. The data of the sampling data layer and the one-time statistical data layer are stored in different instances of the time series library, while the data of the data summary layer and the data application layer are stored in the relational library. The efficiency of data query is improved and the access pressure is reduced by the multi-dimensional table partitioning principle and the unified table building method. In terms of data processing, the management thread of the thread pool is used to obtain the number of various data processing tasks, and a weighted calculation is performed on them. The tasks are arranged in descending order according to the absolute value of the difference between the calculation result and the threshold, and the tasks are dynamically allocated to achieve load balancing between multiple task threads, thereby improving the efficiency of data statistics and warehousing; the historical data of the integrated monitoring system are layered according to the granularity of the data and the usage scenarios, and each layer stores different types of historical data, which is helpful for data query and analysis and simplifies complex problems.

[0044] This application improves data query efficiency and reduces access pressure through the multi-dimensional table partitioning principle and unified table creation method.

[0045] This application uses the management thread of the thread pool to obtain the number of various data processing tasks, performs weighted calculations on them, arranges them in descending order according to the absolute value of the difference between the calculation results and the threshold, dynamically allocates tasks, achieves load balancing among multiple task threads, and improves data statistics and warehousing efficiency.

[0046] This application aims to improve the efficiency of data query, reduce access pressure, and achieve unified management and compatibility by targeting the large amount of historical data stored in the sampling data layer and the primary statistical data layer of the comprehensive monitoring system through the principle of table partitioning based on multiple dimensions such as the ObjectId, data type, and time of the sampling point and the consistent table construction method of the two data layers. BRIEF DESCRIPTION OF THE DRAWINGS

[0047] Other features, objects and advantages of the present application will become more apparent upon reading the detailed description of non-limiting embodiments made with reference to the following drawings:

[0048] Figure 1 This is a system structure diagram of a hierarchical storage system for historical data of a comprehensive monitoring system according to this embodiment;

[0049] Figure 2 A diagram of the data layer structure for hierarchical storage of this application;

[0050] Figure 3 Timing library segmentation diagram;

[0051] Figure 4 Schematic diagram of data processing flow based on thread pool

[0052] Figure 5 Schematic diagram of load balancing method. DETAILED DESCRIPTION

[0053] The following will clearly and completely describe the technical solution of the present invention in conjunction with the accompanying drawings. Obviously, the embodiments described are only some embodiments of the present invention, not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative efforts are within the scope of protection of the present invention.

[0054] A hierarchical storage system for historical data of a comprehensive monitoring system, such as Figure 1 As shown, historical data is stored in layers, including a real-time database, a relational database, and a time series database; the real-time database stores the historical data of the integrated monitoring system, and the layered storage data layers include a sampling data layer, a primary statistical data layer, a secondary statistical data layer, and a business subject data layer;

[0055] Historical data is stored and written into a real-time database instance according to the data application scenario. Sampled data is generated through periodic statistics. After periodic statistics are performed on the sampled data, primary statistical data is extracted and stored in an instance of the time series database. Specific business storage services or ETL tools query the primary statistical data from the time series database. After statistical analysis, secondary statistical data is obtained and stored in the relational database.

[0056] With the intelligent upgrade of the rail integrated monitoring system, more and more professional and data are accessed, and various advanced applications have put forward higher requirements for the flexibility of data query and analysis. The amount of various types of historical data in the integrated monitoring system is huge and the types are complex. In order to better manage historical data and improve storage and query efficiency, the overall architecture design diagram of historical data processing in the technical solution of the present invention is as follows: Figure 1 As shown in the figure, historical data in the real-time database is written to an instance of the time series database through second-level sampling storage according to the type of data application scenario. The primary statistical data generated through periodic statistics is then stored in another instance of the time series database. The specific business storage service or ETL tool queries the required primary statistical data from the time series database according to specific needs, obtains secondary statistical data after statistical analysis, and stores the secondary statistical data in the relational database for use by business-layer applications.

[0057] Based on the database table partitioning principle, the sampled data is classified and saved, and a primary statistical data table and a secondary statistical data table are established to store the primary statistical data and the secondary statistical data respectively; the data processing load balancing based on the thread pool is used to perform statistics and storage operations.

[0058] The sampling data layer samples historical data to obtain sampled data; historical data includes telemetry data, telesignaling data and energy measurement, where telemetry data includes voltage, current, power, power factor and temperature, telesignaling data includes the value and time of each change, and energy measurement includes active energy and reactive energy;

[0059] like Figure 2 As shown, the historical data in the integrated monitoring system is divided into four levels, namely the sampling data layer, the primary statistical data layer, the secondary statistical data layer and the business subject data layer;

[0060] The sampling data layer samples the historical data of the real-time database. The sampling data layer writes the historical data into an instance of the real-time database through second-level sampling storage according to the type of data application scenario. After periodic statistics, statistical data is formed. The real-time database supports the data storage function with second-level accuracy, and saves the historical data generated during the entire system operation into the time series library. The sampling data is stored in the time series library, and the historical data is kept for a short time, not exceeding 3 months.

[0061] The primary statistical data layer generates primary statistical data from the sampled data through a periodic statistical method and stores the primary statistical data in an instance of the time series library. A specific business storage service or ETL tool queries the primary statistical data from the time series library and obtains secondary statistical data through a secondary data statistical method and stores it in the relationship library. The secondary data statistical method includes periodic statistics on the telemetry data in the sampled data layer, setting a statistical period of minutes for each telemetry data point, with a maximum of no more than 60 minutes. The periodic statistical content of the telemetry data includes the maximum value, minimum value, average value within the period, and the instantaneous value at the end of the period. The secondary data statistical method also includes the number of telemetering position changes and the duration of the state within the period, as well as the accumulated electricity.

[0062] Secondary statistical data include periodic statistical data of telemetered data, the number of changes of telesignaling data within a period, the duration of the state, and the accumulated amount of electricity within the period;

[0063] The statistical period is one day, one month, or one year. The statistical period length of specific data can be adjusted through configuration.

[0064] The data in the business subject data layer is business application data formed by refining secondary statistical data based on the business. The actual business logic applied in the business layer determines the storage method of the data. For example, the energy consumption subject queries and calculates secondary statistical data such as electricity consumption and power within a certain period of time to obtain the energy consumption data of lighting, air conditioning, and trains within that period of time.

[0065] The principles for database table sharding specifically include the following steps: Sampled data from the sampling data layer is written to the time series database through a storage process that takes seconds. The amount of data stored per second reaches tens of thousands. Both primary and secondary statistics require a large number of table queries and database write operations. Therefore, a reasonable table sharding approach is necessary to support such a large amount of data and improve data query efficiency. Due to the difference between time series databases and traditional relational databases, time series databases do not require pre-built tables for data storage, while relational databases do. Therefore, the table creation principles for the sampling data tables and primary statistical data tables stored in the time series database are the same.

[0066] The integrated monitoring system adopts an object-oriented design. Each sampled data corresponds to a data object. Each sampled data is set with a unique identification number ObjectId (ObjectId is a 64-bit integer). For the sampling data layer and the primary statistical data layer, the sampling data and the primary statistical data are classified based on the identification number ObjectId and data type of the sampling point and saved in different sampling data tables; the sampling data table name is set to HD_ <attribute> _ <index>, where attribute represents the sampling data type, including integer type (31) and floating-point type (179). (Here, 31 and 179 correspond to the enumeration values of their types in the real-time library.) Index is the integer obtained by converting the lower 32 bits of the ObjectId of the sampling data in the real-time library to decimal and dividing it by 100.

[0067] like Figure 3 As shown, after obtaining the sampled data, the sampled data type is judged to determine the enumeration value of the attribute in the primary statistical data table name. The enumeration value of the attribute is represented as nType. Then, the ObjectId of the sampled data is parsed, the lower 32 bits are converted to decimal and divided by 100 to obtain a primary statistical data table number nIndex. The sampled data will be stored in a primary statistical data table named HD_nType_nIndex.

[0068] A secondary statistical data table is created in the relational database. The secondary statistical data table is divided into tables based on time, OType and attribute. The table name format of the secondary statistical data table is H <otype> _ <attribute> _ <yyyymm>;

[0069] For the relational database that stores data in the secondary statistical data layer and the business data layer, the secondary statistical data table must be created in advance before writing to the database. In order to prevent the amount of data in a single table from being too large and to enable simple and clear query of the required data, the table is divided according to the three dimensions of time, OType, and attribute. The three dimensions are reflected in the table name, that is, the table name format of the secondary statistical data table is H <otype> _ <attribute> _ <yyyymm>OType represents the object type enumeration value of the object to which the statistical data belongs in the real-time database. Attribute has the same meaning as the attribute in the time series database table partitioning principle, indicating the data type. yyyymm represents the time format. For example, yyyymm="202303" means that this table stores statistical data for March 2023.

[0070] The data attributes of the sampling data table and the primary statistical data table in the time series library include timestamp (time), indicator column (field), and dimension column (tags). To achieve unified management and compatibility, the structure of the sampling data table and the primary statistical data table is consistent;

[0071] Timestamp: The time of each data record, which is the primary index of the sampling data table and the statistical data table;

[0072] The indicator column field includes the value field to indicate the value of the corresponding row data, such as telesignaling, telemetry, 5-minute average value, minimum value, and maximum value;

[0073] The dimension column tags includes the ObjectId, OType, and StatisticsType fields. The ObjectId data type is a 64-bit integer, which represents the unique identifier objectId of the corresponding row data; OType is a 32-bit integer, which represents the object type of the corresponding row data, such as telesignaling, telemetry, or environmental professional IO object; the StatisticsType field is a string type, which indicates the type of the corresponding data. Avg represents the average value, Min represents the minimum value, Max represents the maximum value, Acc represents the accumulated value, and Limit represents the crossing (displacement) value.

[0074] When querying time series database tables (time series database tables refer to sampling data tables and primary statistical data tables), the above tags are used to locate the meaning and type of specific statistical values.

[0075] Compared to the structures of sampling data tables and primary statistical data tables, relational database tables are more closely linked to actual business operations, so the table structure in the relational database is relatively complex. The secondary statistical data layer stores data generated by secondary statistical processing of the data in the primary statistical data layer. The time granularity is daily, and the statistical duration of specific data can be adjusted through configuration. Therefore, the fields of the relational database secondary statistical data table include:

[0076] Occurrencetime: the time when the statistic is generated;

[0077] ObjectId: The data type is a 64-bit integer, which indicates the unique identifier objectId of the sampling point corresponding to the statistical data in the real-time database;

[0078] StatisticsType: string type, indicating the type of statistical value. The specific type is consistent with the StatisticsType field in the dimension column of the time series library table (time series library tables include sampling data tables and primary statistical data tables);

[0079] Value: indicates the value of the statistic, such as the average, minimum, or maximum value of a quantity of telemetry data within a statistical period;

[0080] StartOffSet: indicates the start time offset of the statistical period, in minutes (normally a positive value, calculated from 0:00 plus the offset);

[0081] EndOffSet: indicates the end time offset of the statistical period, in minutes (normally a negative value, the offset is added starting from 0:00 the next day). The default statistical period is one natural day, from 0:00 to 0:00 the next day. If StartOffset = 240 and EndOffset = -240, it means the statistical period is from 04:00 to 20:00.

[0082] If a business-layer application needs to query the maximum value of a telemetry measurement between 00:00 on March 1, 2023, and 00:00 on March 2, 2023, it will query the secondary statistical data table named H33_179_202303 and match the corresponding value based on time, ObjectId, offset, and statistical value type.

[0083] The data in the business subject data layer is business application data formed by refining the summary data according to the business. The actual business logic of the upper-level application determines the storage method of the data, so the fields in the table are highly customizable.

[0084] The data processing load balancing based on thread pool specifically includes the following steps:

[0085] After the historical data in the comprehensive monitoring system is layered, the layers do not interfere with each other and are relatively independent, which is conducive to data query and analysis and simplifies complex problems. However, since the amount of data in the sampling data layer and the one-time statistical data layer is very large, the proportion of server resources required for data statistics from the sampling data layer to the one-time statistical data layer is still very high. Generally, a multi-threaded method is used to concurrently query, calculate and write to the database storing the previous layer of data. In the actual operation process, because the number of tables and the amount of calculation required to query different statistical values are different, the time required to execute the entire process is also different. If a multi-threaded method is simply used to perform fixed statistics and warehousing operations, when the amount of data is large or the running time is too long, it will cause the load of some threads to be too high, while other threads will be idle. The result will be a significant reduction in warehousing efficiency and occupying unnecessary server resources. Therefore, this technical solution proposes a method for using a thread pool to achieve data processing load balancing.

[0086] Data statistics from the bottom layer to the upper layer are to query the required batch of data from the bottom layer database at regular intervals, and then calculate the statistical values of these data as needed. This embodiment selects the maximum and average values of telemetry within 3 minutes or the number of telemetering changes within a day. The statistical processing logic for the same statistical value is the same, so in this solution, the above-mentioned statistical processing of a certain number of sampling points (maximum and average values within a certain time period, etc.) is called a task. The processing of the entire historical data can be further divided into several different tasks. Tasks are divided into different types according to the type of statistical value (such as the maximum and average values, etc.), and a load weighting coefficient is pre-set according to the complexity of the task processing logic.

[0087] S101, set the statistical processing of sampled data (maximum value, average value, etc. within the statistical period) as different tasks, divide them into different categories according to the statistical value type (such as statistical maximum value, average value), and pre-set a load weighting coefficient according to the complexity of the task processing logic.

[0088] S102, establishing a thread pool, which includes a management thread and a number of task threads. The management thread is responsible for assigning tasks to each task thread, and the task thread processes the assigned tasks;

[0089] like Figure 4 As shown in the figure, the thread pool consists of a management thread and a number of task threads. All tasks are assigned to the task threads by the management thread, and then the task threads are executed at the task site. Finally, the statistical data is written into the time series library.

[0090] S103, initializing the thread pool. When the thread pool is initialized, the task thread load detection time, the task load weighting coefficient, and the preset task thread load threshold θ of the load management thread are loaded, and the task thread load is managed based on the load balancing process.

[0091] like Figure 5 As shown in FIG, when the thread pool is initialized, the task thread load detection time of the load management thread, the load weighting coefficients of various tasks, and the preset task thread load threshold θ are loaded. The load balancing processing principle is that if the difference between the load of a task thread and the load threshold θ is less than 5%, the thread is considered to be a normal load thread and does not participate in load balancing.

[0092] S104: After the management thread is started, it randomly assigns tasks to each task thread to perform statistical tasks. The task thread records the number of times each task is executed within a detection time interval in the thread. After the task thread load detection time has elapsed, the management thread obtains the recorded execution count from each task thread and multiplies the execution count of each task by the load weighting coefficient corresponding to the task to obtain the load w of each task thread during the previous detection time.

[0093] S105: The management thread subtracts the load w of each task thread from the preset task thread load threshold θ to obtain a load difference Δ. Based on the load difference Δ, the overloaded thread queue and the lightly loaded thread queue are respectively counted. The number of overloaded thread queues and the number of lightly loaded thread queues are n and m respectively. The absolute value of the load difference of each thread in the overloaded thread queue and the lightly loaded thread queue is c = |w-θ|. The overloaded thread queue and the lightly loaded thread queue are respectively sorted in descending order according to the absolute value of the load difference, and the overloaded thread descending queue D = {d1, d2…d i …d n } and the light-load thread descending queue R={r1,r2…r j …r m }, the load difference between the overloaded thread and the lightly loaded thread is C = {c1, c2…c i …c n } and S={s1,s2…s j …s m }, i=1,2…n; j=1,2…m;

[0094] In order to achieve load balancing and minimize the complexity of the task transfer process, the following two cases are divided according to the number of elements in the two queues.

[0095] If n≥m, that is, the number of overloaded threads is greater than the number of lightly loaded threads, then traverse the descending queue of overloaded threads D={d1,d2…d i …d n }; If the i-th overload thread d i The load difference c i If the load difference c of the overloaded thread d is less than the load difference s1 of the lightly loaded thread r1, the management thread will i transfer some of the tasks executed in the d thread to the r1 thread, and the load required for this part of the tasks is equal to the load difference c of the overloaded thread d i The load difference c i . If the overloaded thread d i The load difference c i is greater than the load difference s1 of the lightly loaded thread r1, the management thread will transfer some of the tasks executed in the overloaded thread d i to r1, r2,..., r t in turn, so that the load differences c1, c2,..., c of the lightly loaded threads except the r t thread are equal to 0, and the load required for this part of the tasks is equal to the load difference s1 of the lightly loaded thread r1; delete the threads with load difference equal to 0 from the lightly loaded thread queue after task transfer, and at the same time move the element subscripts forward in the lightly loaded thread queue; when the threads with load difference equal to 0 are deleted from the lightly loaded thread queue, the subscripts of the lightly loaded threads in the queue will move forward. For example, if c1 = 0, c2 = 0, c3 = 0, c4 = 1, c5 = 2, after deleting the threads with c = 0, the queue becomes c1 = 1, c2 = 2;

[0096] If n < m, that is, the number of overloaded threads is less than the number of lightly loaded threads, traverse the descending queue of lightly loaded threads; if the load difference s i of the i-th lightly loaded thread r i is less than the load difference c1 of the overloaded thread d1, the management thread will let the r i thread receive some of the tasks executed by the d1 thread, and the load required for this part of the tasks is equal to the load difference s i of the lightly loaded thread r i . If the load difference s i of the i-th lightly loaded thread r i is greater than the load difference c1 of the overloaded thread d1, the management thread will transfer some of the tasks of the overloaded threads d1, d2,..., d t to the r i thread until the load difference s i = 0; after task transfer, update the load differences of the task lightly loaded thread queue, and delete the overloaded threads with load difference equal to 0 from the overloaded thread queue; judge whether the load difference of the r i thread is equal to 0. If it is equal to 0, perform the load balancing of the next lightly loaded thread. Otherwise, the management thread continues to let the r i thread receive some of the tasks executed by the d1 thread; after completing the entire load balancing process, the load of the vast majority of task threads will tend to the preset load threshold, achieving the load balancing of each task thread.​​

[0097] The technical solution of this application divides the historical data of the integrated monitoring system into layers according to the data granularity and usage scenarios. Each layer stores different types of historical data, which facilitates data query and analysis and simplifies complex problems.

[0098] Given the large amount of historical data stored in the sampling data layer and primary statistical data layer of the integrated monitoring system, the principle of partitioning tables based on multiple dimensions, such as the ObjectId of the sampling point, data type, and time, and creating tables with consistent data table structures in the two data layers improves data query efficiency, reduces access pressure, and achieves unified management and compatibility.

[0099] The management thread of the thread pool is used to obtain the number of various data processing tasks, perform weighted calculations on them, and arrange them in descending order according to the absolute value of the difference between the calculation results and the threshold. The tasks are dynamically allocated to achieve load balancing among multiple task threads, thereby improving data statistics and warehousing efficiency.

[0100] Thus far, the technical solutions of the present invention have been described in conjunction with the preferred embodiments shown in the accompanying drawings. However, it will be readily understood by those skilled in the art that the scope of protection of the present invention is obviously not limited to these specific embodiments. Without departing from the principles of the present invention, those skilled in the art may make equivalent changes or substitutions to the relevant technical features, and the technical solutions after such changes or substitutions will fall within the scope of protection of the present invention.

[0101] In the description provided herein, a large number of specific details are described. However, it is understood that embodiments of the present invention can be practiced without these specific details. In some instances, well-known methods, structures, and techniques are not shown in detail so as not to obscure the understanding of this description.

[0102] Similarly, it should be understood that in order to streamline the present disclosure and aid understanding of one or more of the various inventive aspects, in the above description of exemplary embodiments of the invention, various features of the invention are sometimes grouped together into a single embodiment, figure, or description thereof. However, this disclosed method should not be interpreted as reflecting an intention that the claimed invention requires more features than are expressly recited in each claim. Rather, as reflected in the claims, inventive aspects lie in less than all the features of the individual embodiments disclosed above. Accordingly, the claims that follow the detailed description are hereby expressly incorporated into this detailed description, with each claim standing on its own as a separate embodiment of the invention.

[0103] Those skilled in the art will appreciate that the modules, units, or groups of devices in the examples disclosed herein may be arranged in the device described in the embodiment, or alternatively may be located in one or more devices different from the devices in the examples. The modules in the aforementioned examples may be combined into one module or further divided into multiple submodules.

[0104] It will be appreciated by those skilled in the art that the modules in the devices of the embodiments may be adaptively changed and arranged in one or more devices different from the embodiments. The modules or units or groups in the embodiments may be combined into one module or unit or group, and further may be divided into a plurality of submodules or subunits or subgroups. All features disclosed in this specification (including the accompanying claims, abstract and drawings) and all processes or units of any method or device so disclosed may be combined in any combination, except that at least some of such features and / or processes or units are mutually exclusive. Unless expressly stated otherwise, each feature disclosed in this specification (including the accompanying claims, abstract and drawings) may be replaced by an alternative feature providing the same, equivalent or similar purpose.

[0105] Furthermore, those skilled in the art will appreciate that although some embodiments described herein include certain features included in other embodiments but not other features, combinations of features from different embodiments are intended to be within the scope of the present invention and to form different embodiments. For example, in the claims below, any of the claimed embodiments may be used in any combination.

[0106] In addition, some of the embodiments are described herein as methods or combinations of method elements that can be implemented by a processor of a computer system or by other devices that perform the functions described. Thus, a processor having the necessary instructions for implementing the method or method element forms a device for implementing the method or method element. Furthermore, the elements described herein of the device embodiments are examples of devices for implementing the functions performed by the elements for the purpose of implementing the invention.

[0107] The various techniques described herein may be implemented in conjunction with hardware or software, or a combination thereof. Thus, the methods and apparatus of the present invention, or certain aspects or portions of the methods and apparatus of the present invention, may take the form of program code (i.e., instructions) embedded in a tangible medium, such as a floppy disk, CD-ROM, hard drive, or any other machine-readable storage medium, wherein when the program is loaded into a machine such as a computer and executed by the machine, the machine becomes an apparatus for practicing the present invention.

[0108] When the program code is executed on a programmable computer, the computing device generally includes a processor, a storage medium readable by the processor (including volatile and non-volatile memory and / or storage elements), at least one input device, and at least one output device. The memory is configured to store the program code; the processor is configured to execute the method of the present invention according to the instructions in the program code stored in the memory.

[0109] By way of example and not limitation, computer-readable media include computer storage media and communication media. Computer-readable media include computer storage media and communication media. Computer storage media stores information such as computer-readable instructions, data structures, program modules, or other data. Communication media generally embodies computer-readable instructions, data structures, program modules, or other data in a modulated data signal such as a carrier wave or other transport mechanism, and includes any information delivery media. Combinations of any of the above are also included within the scope of computer-readable media.

[0110] As used herein, unless otherwise specified, the use of ordinal numbers "first," "second," "third," etc. to describe common objects merely indicates that different instances of similar objects are involved and is not intended to imply that the objects so described must have a given order in time, space, ranking, or in any other manner.

[0111] Although the present invention has been described with respect to a limited number of embodiments, it will be apparent to those skilled in the art, having benefit of the foregoing description, that other embodiments are contemplated within the scope of the invention thus described. Furthermore, it should be noted that the language used in this specification has been selected primarily for readability and didactic purposes, rather than for the purpose of explaining or limiting the subject matter of the present invention. Consequently, many modifications and variations will be apparent to those skilled in the art without departing from the scope and spirit of the appended claims. The disclosure of the present invention is intended to be illustrative rather than restrictive of the scope of the invention, which is defined by the appended claims.< / yyyymm> < / attribute> < / otype> < / yyyymm> < / attribute> < / otype> < / index> < / attribute> < / yyyymm> < / attribute> < / otype> < / yyyymm> < / attribute> < / otype> < / index> < / attribute>

Claims

1. A hierarchical storage system for historical data of an integrated monitoring system, characterized by: It includes a real-time database, a relational database, and a time series database. The real-time database stores the historical data of the integrated monitoring system and stores the historical data in layers. The layered storage data layers include the sampling data layer, the primary statistical data layer, the secondary statistical data layer, and the business subject data layer. Historical data is stored and written into a real-time database according to the type of data application scenario. Sampled data is generated through regular statistics. After regular statistics are performed on the sampled data, primary statistical data is extracted and stored in a time series database. Statistical data is queried from the time series database. After statistical analysis, secondary statistical data is obtained and stored in a relational database. Based on the database table partitioning principle, the sampled data are classified and saved, and a primary statistical data table and a secondary statistical data table are established to store the primary statistical data and the secondary statistical data respectively; Data processing load balancing based on thread pool performs statistics and storage operations; The database table partitioning principle specifically includes the following steps: Using object-oriented design, each sampling data corresponds to a data object, and each sampling data is set with a unique identification number ObjectId; for the sampling data layer and the one-time statistical data layer, the sampling data and the one-time statistical data are classified based on the identification number ObjectId and data type of the sampling point and saved in different sampling data tables; the sampling data table name is set to HD_ <attribute> _ <index> Format, where attribute indicates the sampling data type, which includes integer and floating-point types. Index is the integer obtained by converting the lower 32 bits of the ObjectId of the sampling data in the real-time database to decimal and dividing it by 100.< / index> < / attribute> After acquiring the sampled data, the sampled data type is determined to determine the enumeration value of the attribute in the primary statistical data table name. The enumeration value of the attribute is represented as nType. Then, the ObjectId of the sampled data is parsed, the lower 32 bits are converted to decimal, and the result is divided by 100 and the remainder is obtained to obtain a primary statistical data table number nIndex. The sampled data will be stored in a primary statistical data table named HD_nType_nIndex. A secondary statistical data table is created in the relational database. The secondary statistical data table is divided into tables based on time, OType and attribute. The table name format of the secondary statistical data table is H <otype> _ <attribute> _ <yyyymm> ；< / yyyymm> < / attribute> < / otype> For the relational database that stores data in the secondary statistical data layer and the business data layer, the secondary statistical data table must be created in advance before the database write operation can be performed; the table is divided according to the three dimensions of time, OType and attribute, and the table name format of the secondary statistical data table is H <otype> _ <attribute> _ <yyyymm> OType represents the object type enumeration value to which the statistical data belongs in the real-time database. The meaning of attribute is the same as that of attribute in the time series database table partitioning principle, indicating the type of data. yyyymm represents the time format.< / yyyymm> < / attribute> < / otype> 2. The hierarchical storage system for historical data of a comprehensive monitoring system according to claim 1, characterized in that: The sampling data layer samples historical data to obtain sampling data; historical data includes telemetry data, telesignaling data and electricity measurement; The sampling data layer samples the historical data of the real-time database. The sampling data layer writes the historical data into an instance of the real-time database through second-level sampling storage according to the type of data application scenario, and forms statistical data through regular statistics. The primary statistical data layer generates primary statistical data from sampled data through a periodic statistical method and stores the primary statistical data in an instance of the time series library. The specific business storage service or ETL tool queries the primary statistical data from the time series library, obtains secondary statistical data through a secondary data statistical method, and stores it in the relational library. The data in the business subject data layer is business application data formed by refining the secondary statistical data based on the business. The actual business logic of the business layer application determines the data storage method.

3. The hierarchical storage system for historical data of a comprehensive monitoring system according to claim 2, characterized in that: Telemetered data includes voltage, current, power, power factor and temperature; telesignaling data includes the value and time of each change; and energy measurements include active energy and reactive energy.

4. The hierarchical storage system for historical data of a comprehensive monitoring system according to claim 2, characterized in that: The secondary data statistics method includes periodic statistics of the telemetry data in the sampling data layer, setting a statistical period for each telemetry data point; the periodic statistical content of the telemetry data includes the maximum value, minimum value, average value within the period and the instantaneous value at the end of the period; The secondary data statistics method also includes the number of telesignal changes within a cycle and the duration of the state, as well as the accumulated amount of electricity.

5. The hierarchical storage system for historical data of a comprehensive monitoring system according to claim 1, characterized in that: The data attributes of the sampling data table and the one-time statistical data table in the time series library include the timestamp time, the indicator column field, and the dimension column tags. The sampling data table and the one-time statistical data table have the same structure. The timestamp time is the time of each data record and is the primary index of the sampling data table and the one-time statistical data table. Indicator column field: includes the value field to indicate the value of the corresponding row data; Dimension column tags include the ObjectId, OType, and StatisticsType fields. The ObjectId data type is a 64-bit integer, representing the unique identifier objectId of the corresponding row data; OType is a 32-bit integer, representing the object type of the corresponding row data; the StatisticsType field is a string type, indicating the category of the corresponding data. Avg represents the average value, Min represents the minimum value, Max represents the maximum value, Acc represents the accumulated value, and Limit represents the limit value.

6. The hierarchical storage system for historical data of an integrated monitoring system according to claim 5, characterized in that: The fields of the secondary statistical data table include: Occurrencetime: the time when the statistics are generated; ObjectId: The data type is a 64-bit integer, which indicates the unique identifier objectId of the sampling point corresponding to the statistical data in the real-time database; StatisticsType: string type, indicating the type of statistical value. The specific type is consistent with the StatisticsType field in the dimension column of the time series library table; Value: represents the value of the statistic; StartOffSet: indicates the start time offset of the statistical period; EndOffSet: indicates the end time offset of the statistical period.

7. The hierarchical storage system for historical data of an integrated monitoring system according to claim 1, characterized in that: The data processing load balancing based on thread pool specifically includes the following steps: S101, setting statistical processing of sampled data into different tasks, classifying them into different categories according to the statistical value type, and presetting a load weighting coefficient according to the complexity of the task processing logic; S102, establishing a thread pool, which includes a management thread and a number of task threads. The management thread is responsible for assigning tasks to each task thread, and the task thread processes the assigned tasks; S103, initializing the thread pool. When the thread pool is initialized, the task thread load detection time, the task load weighting coefficient, and the preset task thread load threshold θ of the load management thread are loaded, and the task thread load is managed based on the load balancing process. When the thread pool is initialized, the task thread load detection time of the management thread, the load weighting coefficients of various tasks and the preset task thread load threshold θ are loaded; S104: After the management thread is started, it randomly assigns tasks to each task thread to perform statistical tasks. The task thread records the number of times each task is executed within a detection time interval in the thread. After the task thread load detection time has elapsed, the management thread obtains the recorded execution count from each task thread and multiplies the execution count of each task by the load weighting coefficient corresponding to the task to obtain the load w of each task thread during the previous detection time. S105: The management thread subtracts the load w of each task thread from a preset task thread load threshold θ to obtain a load difference Δ. Based on the load difference Δ, an overloaded thread queue and a lightly loaded thread queue are obtained. The number of overloaded thread queues and the number of lightly loaded thread queues are n and m, respectively. The absolute value of the load difference of each thread in the overloaded thread queue and the lightly loaded thread queue is c = |w-θ|. The overloaded thread queues and the lightly loaded thread queues are arranged in descending order according to the absolute value of the load difference, respectively, to obtain overloaded thread descending queues D = {d1, d2…d i …d n } and the light-load thread descending queue R={r1,r2…r j …r m }, the load difference between the overloaded thread and the lightly loaded thread is C = {c1, c2…c i …c n } and S={s1,s2…s j …s m }, i=1,2…n; j=1,2…m; If n≥m, that is, the number of overloaded threads is greater than the number of lightly loaded threads, then traverse the descending queue of overloaded threads D={d1,d2…d i d n }; If the i-th overload thread d i The load difference c i The load difference s1 is smaller than the load difference of the lightly loaded thread r1, and the management thread will d i Part of the tasks executed in the thread is passed to the r1 thread, and the load required by this part of the tasks is equal to the load of the overloaded thread d i The load difference c i ; If overload thread d i The load difference c i If the load difference s1 is greater than the load difference of the lightly loaded thread r1, the management thread will overload thread d. i The part of the task executed in is passed to r1, r2, ..., r t , so that except r t The load difference c1, c2, ..., c of the lightly loaded threads outside the thread t-1 = 0, the load required by this part of the task is equal to the load difference s1 of the lightly loaded thread r1; the threads with a load difference equal to 0 in the lightly loaded thread queue after the task is transferred are deleted from the lightly loaded thread queue, and the element index in the lightly loaded thread queue is moved forward; If n < m, that is, the number of overloaded threads is less than the number of lightly loaded threads, then traverse the descending queue of lightly loaded threads; if the load difference s i of the i-th lightly loaded thread r i is less than the load difference c1 of the overloaded thread d1, the management thread will let r i thread receive some tasks executed by the d1 thread, and the amount of load required for this part of the tasks is equal to the load difference s i of the lightly loaded thread r i ; if the load difference s i of the i-th lightly loaded thread r i is greater than the load difference c1 of the overloaded thread d1, the management thread will sequentially transfer some tasks of the overloaded threads d1, d2,..., d t to the r i thread until the load difference s i = 0; after transferring the tasks, update the load differences of the task lightly loaded thread queue, and delete the overloaded threads with load differences equal to 0 from the overloaded thread queue; determine whether the load difference of the r i thread is equal to 0. If it is equal to 0, perform load balancing for the next lightly loaded thread. Otherwise, the management thread continues to let the r i thread receive some tasks executed by the d1 thread to achieve load balancing of each task thread.

8. The hierarchical storage system for historical data of a comprehensive monitoring system according to claim 7, characterized in that: The processing principle of load balancing is that if the difference between the load of a task thread and the load threshold θ is less than 5%, the thread is a normal load thread and does not participate in load balancing.

9. The hierarchical storage system for historical data of an integrated monitoring system according to claim 6, characterized in that: ObjectId: The data type is a 64-bit integer.

Images