CN-122019547-A - Acquisition method based on time physical sub-table

CN122019547ACN 122019547 ACN122019547 ACN 122019547ACN-122019547-A

Abstract

The invention discloses an acquisition method based on a time physical sub-table, which relates to the technical field of database management and comprises the following steps of S1, acquiring a system starting instruction and a database reference table structure, automatically checking and creating physical sub-tables of the current month and the next month, simultaneously setting a fixed time triggering timing automatic checking and creating logic tasks of each month to ensure that the physical sub-table of the next month is created in advance, S2, receiving a data writing request, and extracting time attributes of data to be written. According to the calculation method based on the time physical sub-table, through the data management strategy, the efficiency and flexibility of the database in processing large-scale time sequence data are improved, full-automatic management is realized, the operation and maintenance cost is effectively reduced, errors and omission possibly caused by manual intervention are avoided, the accuracy of data writing is ensured by a dynamic table name routing mechanism, so that service codes do not need to perceive sub-table details, and usability is enhanced.

Inventors

SU RUIZHI
SHEN BO
ZHAO TIANYE
YU DALIN
LI TAO
XU YUHANG
LI DEXIN
WANG XINYU

Assignees

北京华能新锐控制技术有限公司
西安热工研究院有限公司
华能吉林发电有限公司新能源分公司

Dates

Publication Date: 20260512
Application Date: 20260203

Claims (10)

1. The acquisition method based on the time physical sub-table is characterized by comprising the following steps of: s1, acquiring a system starting instruction and a database reference table structure, automatically checking and creating physical sub-tables of the current month and the next month, and simultaneously setting a fixed-time triggering timing automatic checking and creating logic tasks per month to ensure that the physical sub-tables of the next month are created in advance; S2, receiving a data writing request, extracting a time attribute of data to be written, and analyzing a target month physical sub-table name according to a preset table name rule; S3, receiving a query request containing a time range, analyzing start and stop time and calculating all covered months, generating a physical sub-table list according to a physical sub-table corresponding to a preset table name rule, respectively querying each physical sub-table, and aggregating all query results and returning.
2. The method for obtaining the physical time partition table according to claim 1, wherein S1 specifically comprises: the system monitors a starting instruction, wherein a triggering scene of the starting instruction comprises initial starting after the system is deployed for the first time, system fault recovery restarting and system version updating restarting, and when the starting instruction is monitored, the system automatically enters a sub-table initialization flow; Determining a database reference table structure, wherein the database reference table is a pre-configured sub-table structure template table and comprises all fields, field data types and index configuration required by a target service scene, and the target service scene comprises alarm record storage and monitoring data storage; Establishing stable connection with a target database through a database connection pool, calling a database metadata query interface to read field information, index information and table engine configuration of a reference table, and caching the information to a local memory; Calling a time tool class to acquire the current system time, and converting the current time into a yyyy-MM format to determine the current month; The method comprises the steps of calculating the next month based on the current month, ensuring no deviation in month calculation in a annual scene, uniformly converting the current month and the next month into yyyyMM-format time codes, and generating a physical sub-table name corresponding to the current month and a physical sub-table name corresponding to the next month according to a preset sub-table rule; Executing a database table existence check SQL sentence, and respectively inquiring the existence states of the physical sub-tables corresponding to the current month and the next month; judging the existence of the sub-table according to the query result, if the query result returns to the table name record, judging that the corresponding physical sub-table exists, if the query result does not return, judging that the corresponding physical sub-table does not exist, and recording the non-existence sub-table name.
3. The method for obtaining a physical time partition table according to claim 2, wherein S1 further comprises: After judging that at least one of the physical sub-table corresponding to the current month and the physical sub-table corresponding to the next month does not exist, automatically starting a physical sub-table creation flow, wherein the creation flow is based on the reference table field information, the index information and the table engine cached in the local memory; according to the absence of the sub-table names of the records, automatically generating a table building SQL statement by combining the basic table structure information, wherein the table building SQL statement comprises the sub-table names, field definitions, field data types, index creation rules and table engine appointed contents; Executing the generated table-building SQL statement, and respectively performing the creation operation on the physical sub-table of the current month or the physical sub-table of the next month which does not exist.
4. A method of obtaining a physical time-division table according to claim 3, wherein S1 further comprises: After the operation of building the table is executed, the SQL statement of the existence check of the database table is called again, the existence state of the built sub-table is inquired, the successful creation of the sub-table is confirmed, if the inquired result shows that the sub-table exists, the completion of the operation of building the table is judged, the system ends the current sub-table creation flow, if the inquired result still shows that the sub-table does not exist, the failure of the operation of building the table is judged, the log of the failure of building the table is recorded, the sub-table creation flow is paused, and meanwhile, the failure prompt is pushed to the operation and maintenance terminal.
5. The method for obtaining the physical time partition table according to claim 1, wherein S2 specifically comprises: The system receives a data writing request, wherein the request comprises service data to be written, the service data corresponds to time sequence data of a target service scene, and the target service scene comprises alarm record storage and monitoring data storage; Extracting time attribute from service data to be written, wherein the time attribute is a time related field of the service data, and specifically comprises data generation time, data submission time and service association time; The method comprises the steps of carrying out format verification on an extracted time attribute and calling a preset table name rule, wherein the format verification is that a preset standard format is yyyy-MM-dd HH: MM: ss, the preset table name rule is a rule of combining a logic table name and a time dimension suffix, the logic table name is a table identification name corresponding to a service scene, the time dimension suffix is year and month information extracted based on the time attribute, and the year and month information format is yyyyMM; analyzing the corresponding year and month information according to the extracted time attribute, extracting the year and month from the time attribute conforming to the standard format, converting the year and month information into the year and month code of yyyyMM format, combining the logic table name with the yyyyMM format year and month code obtained by analysis to generate a target month physical minute table name corresponding to the data to be written, and completing the analysis of the target month physical minute table name.
6. The method for obtaining a physical time-division table according to claim 5, wherein S2 further comprises: After the target month physical branch table name is obtained based on analysis, the system starts a thread-level data binding procedure, and a thread local mechanism is adopted to realize the binding of the target month physical branch table name and the current processing thread; The system initializes a ThreadLocal instance, wherein the instance is used for storing a target month physical branch table name corresponding to the current thread, invoking a set method of the ThreadLocal instance, taking the target month physical branch table name obtained by analysis as a value to be transmitted in, and binding the target month physical branch table name with the thread which is currently processing a data writing request; After binding is completed, the system confirms that the current thread has associated the target month physical branch table name.
7. The method for obtaining a physical time partition table according to claim 6, wherein S2 further comprises: Before the system executes data and writes the data into the corresponding SQL sentence, a preconfigured MyBatis Plus interceptor triggers SQL interception operation to intercept the original SQL sentence to be executed, wherein the original SQL sentence contains a preset business logic table name; calling a get method from a thread local instance of a current thread by the MyBatis Plus interceptor to acquire a bound target month physical branch table name, replacing a preset logic table name in an original SQL statement with the target month physical branch table name, and generating a final execution SQL adapting to the target physical branch table; and executing the replaced final SQL statement, writing the data to be written into the target month physical sub-table, and judging that the data writing operation is completed after the SQL execution is completed and the data writing is successful.
8. The method for obtaining the physical time partition table according to claim 1, wherein S3 specifically comprises: the system receives a query request containing a time range, and extracts a query starting time and a query ending time in the request; performing format verification on the inquiry starting time and the inquiry ending time; extracting a starting year and a starting month from the inquiry starting time, extracting an ending year and an ending month from the inquiry ending time, and combining the initial year and month codes and the ending year and month codes; And calculating all months covered by the query time range according to the initial year and month codes and the ending year and month codes, wherein the months are covered months if the initial year and month codes are the same as the ending year and month codes, and generating all intermediate year and month codes from the initial year and month according to month increment if the initial year and month codes are different from the ending year and month codes.
9. The method for obtaining a physical time partition table according to claim 8, wherein S3 further comprises: invoking a preset table name rule, extracting the annual and monthly codes of each month covered by the query time range, and splicing the codes with the logical table names of the current query service scene; generating physical branch table names corresponding to each month, and sorting all the physical branch table names according to the year-month codes to form an ordered physical branch table list.
10. The method for obtaining a physical time partition table according to claim 9, wherein S3 further comprises: Performing presence verification on each physical sub-table in the physical sub-table list, recording abnormal information and skipping inquiry if the sub-table does not exist; and (3) sorting all result sets according to the time attribute fields, removing repeated data records according to the unique identification fields, packaging the sorted and de-duplicated data together with the abnormal information, and returning to the service end.

Description

Acquisition method based on time physical sub-table Technical Field The invention relates to the technical field of database management, in particular to an acquisition method based on a time physical sub-table. Background In the rapid development process of the fields of the Internet of things, industrial monitoring and the like, massive time sequence data continuously emerge, and the explosive growth of the data scale puts higher demands on the storage capacity, the read-write efficiency and the query response speed of a database. In order to adapt to the management requirement of the data, technical schemes such as database splitting and table splitting, database partition table, ORM framework expansion and the like are gradually formed in the industry, and the core thought is to reduce single table load through data splitting and storage, so that the basic operation performance of the system in a large-scale data scene is ensured, and the system becomes a main technical path for time sequence data storage and management. The prior art scheme has achieved a certain effect in practical application, but still has some limitations that need to be perfected. The method has the advantages that the method is simple in structure, the method is convenient to use, the operation and maintenance workload is increased, the situation that the table structure is inconsistent or missing is likely to occur due to manual operation is avoided, the built-in partition function of the database is limited by engine characteristics, the number of partitions and the configuration flexibility are insufficient, the performance optimization space in cross-partition query is limited, the integrated support of automatic table establishment, table existence check and query routing is lacking in some schemes, and the application layer is required to additionally develop adaptation logic, so that the code invasiveness is high, and the dynamic change time range data storage requirement is difficult to flexibly deal with. The situations make the prior scheme still have room for improvement in terms of considering the high efficiency, usability and expansibility of large-scale time sequence data management. In this regard, we propose an acquisition method based on a time physical partition table. Disclosure of Invention In order to solve the technical problems, the technical scheme solves the problems that the manual maintenance of the sub-tables increases operation and maintenance workload and is easy to generate table structure, the built-in partition of the database is not flexible, the cross-partition query performance is limited, part of schemes lack integrated support such as automatic table building and the like, adaptation logic needs to be additionally developed, the code invasiveness is high, and the data storage requirement of a dynamic time range is difficult to flexibly cope with. In order to achieve the above purpose, the invention adopts the following technical scheme: an acquisition method based on a time physical sub-table comprises the following steps: s1, acquiring a system starting instruction and a database reference table structure, automatically checking and creating physical sub-tables of the current month and the next month, and simultaneously setting a fixed-time triggering timing automatic checking and creating logic tasks per month to ensure that the physical sub-tables of the next month are created in advance; S2, receiving a data writing request, extracting a time attribute of data to be written, and analyzing a target month physical sub-table name according to a preset table name rule; S3, receiving a query request containing a time range, analyzing start and stop time and calculating all covered months, generating a physical sub-table list according to a physical sub-table corresponding to a preset table name rule, respectively querying each physical sub-table, and aggregating all query results and returning. Preferably, the S1 specifically includes: the system monitors a starting instruction, wherein a triggering scene of the starting instruction comprises initial starting after the system is deployed for the first time, system fault recovery restarting and system version updating restarting, and when the starting instruction is monitored, the system automatically enters a sub-table initialization flow; Determining a database reference table structure, wherein the database reference table is a pre-configured sub-table structure template table and comprises all fields, field data types and index configuration required by a target service scene, and the target service scene comprises alarm record storage and monitoring data storage; Establishing stable connection with a target database through a database connection pool, calling a database metadata query interface to read field information, index information and table engine configuration of a reference table, and caching the information to a local memory; C