A method for storing big data databases in an energy management system
By storing newly collected data as a stacked table in the subway energy management system and converting it into an index-organized table the next day, the problems of bloated database tables and slow queries were solved, achieving efficient data storage and querying, and optimizing the database structure and maintenance process.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- TIANJIN KEYVIA ELECTRIC CO LTD
- Filing Date
- 2022-10-28
- Publication Date
- 2026-05-26
AI Technical Summary
In the subway energy management system, the database tables are bloated and the query speed is slow. Even after creating an index for large amounts of data, the existing heap tables still require a long time to query, which cannot meet the requirements of large-scale web queries.
Newly collected data is stored in a heap table. The heap table is converted into an index-organized table in the early morning of the next day. Historical date data is automatically or manually converted at 1 a.m. to optimize the database storage structure and reduce query time.
Effectively control database table size, improve query speed, reduce storage space, facilitate backup and maintenance, and ensure continuous system operation under limited storage hardware conditions.
Smart Images
Figure CN115658691B_ABST
Abstract
Description
Technical Field
[0001] This invention belongs to the field of big data processing, and in particular relates to a method for storing big data databases in an energy management system. Background Technology
[0002] With the development of subway energy management systems, the amount of data accessed by these systems is growing exponentially. Furthermore, the system generates massive amounts of data every moment, leading to a continuous increase in the data volume stored in the subway energy management system's database. Currently, the subway energy management system's database faces two main challenges: firstly, the content in individual database tables is increasing rapidly, becoming bloated; secondly, the increasing size of the database tables and the growing storage space result in very slow web page queries. Generally, energy management system databases use heap tables to store data. Heap tables allow for the rapid insertion of large amounts of data into the database, and creating indexes on heap tables can significantly improve the speed of web database queries. However, even with indexes, heap tables still typically require a considerable amount of time to retrieve data under large data volumes, failing to meet the requirements of large-scale web queries. Summary of the Invention
[0003] In view of this, the present invention aims to propose a big data database storage method for energy management systems, so as to solve at least one of the problems in the background art.
[0004] To achieve the above objectives, the technical solution of the present invention is implemented as follows:
[0005] A method for storing big data databases in an energy management system, the database storage process is as follows:
[0006] S1. Create a heap table based on the data type;
[0007] S2, Store the data obtained from the terminal into a heap table of the corresponding data type;
[0008] S3. Determine if it is time for the scheduled task to be executed on the next natural day. If so, proceed to step S4.
[0009] S4. Convert the heap table generated on the previous day into an index-organized table and store it in the database;
[0010] S5. Complete the storage conversion process;
[0011] S6. Perform a backup operation.
[0012] Furthermore, in step S1, different stack tables are created according to different data types in the energy management system, including current database table, voltage database table, power database table, power factor database table, energy indication database table, frequency database table, temperature, humidity and flow sensor database table, and passenger flow database table.
[0013] Furthermore, it also includes automatic and manual database storage conversion methods. The automatic conversion method is used for future date conversion, while the manual database storage conversion method is used for historical date data conversion. The automatic conversion method is as follows:
[0014] A1. Create log database table D;
[0015] A2. Create the stored procedure E for the write log database table;
[0016] A3. Create a database job.
[0017] Furthermore, in step A2, a stored procedure E for writing log database tables is created, which provides a function interface that can write database operation process tables into database table D according to parameters, and record the log of the operation process.
[0018] Furthermore, in step A3, a database task is created, set as an automatic task, and scheduled to execute at 1:00 AM daily. This task primarily involves converting the contents of a heap-organized table into an index-organized table with the same name. The process is as follows:
[0019] A. Retrieve the contents of database table A from the previous day, create a new index-organized table B, with the same column names as database table A, and create primary keys for both index-organized table B according to table ID and data time. Then copy the contents of database table A to index-organized table B, and create primary key constraints C in index-organized table B.
[0020] B. Delete database table A and its primary key constraint;
[0021] C. Modify the name of index-organized table B to match the name of database table A;
[0022] D. Modify the primary key constraint C of index-organized table B to have the same name as the primary key constraint of database table A.
[0023] E. Call the stored procedure E to write the operation process into the database table D.
[0024] Furthermore, this includes methods for converting historical date data:
[0025] Create a batch database table conversion stored procedure F. The parameters include the start date, end date, and database table name (a database table containing multiple data types). This stored procedure is mainly used to manually convert all database tables within a specified date from heap-organized tables to index-organized tables.
[0026] The main stored procedures are as follows:
[0027] a. Estimate the row storage size of each database table, as shown in Table (1). The storage capacity of database tables of different data types is V. i Num represents the number of database record rows stored daily; x k Define the size of the data type for the k-th field; y k Define the length of the stored content for the k-th field; Z k The size ratio is adjusted for the k-th field, primarily for variable-length data types; for fixed-length data types, it's 1. The above refers to Num, x... k y k z k All of these are predefined in the stored procedures of the database. The database table size V for different data types is calculated. i And sort them according to the database capacity V. i Sort by size from smallest to largest, starting with database size V. i Start with small database tables and gradually increase to larger database sizes (V). i The database tables.
[0028]
[0029] b. Retrieve the contents of database table A (start time) from the database, assign the start time to the current time variable, create a new index-organized table B, with the same column names as database table A, and create primary keys for both index-organized table B based on table ID and data time. Then copy the contents of database table A to index-organized table B, create the index-organized table, and create primary key constraint C; c. Delete database table A and its primary key constraint.
[0030] d. Modify the name of index-organized table B to match the name of database table A;
[0031] e. Modify the primary key constraint C of index-organized table B to have the same name as the primary key constraint of database table A.
[0032] f. Call stored procedure E to write the operation process to the database;
[0033] g. Check if the current time is equal to the end time. If it is less than the end time, add one talent value to the current time and assign it to the start time. Then jump to b to execute. If the current time is equal to the end time, continue to check if all database tables have been processed. If not, jump to a to process the next data type database table. If they are equal, end the loop processing process.
[0034] Furthermore, in step S5, after completing the storage conversion process and waiting for the database storage conversion on the next natural day, the web page queries the database table belonging to each natural day within the query time period, thereby reducing the time the web page waits for all data to be returned.
[0035] An electronic device includes a processor and a memory communicatively connected to the processor and used to store executable instructions of the processor, the processor being used to execute a big data database storage method for an energy management system.
[0036] A server includes at least one processor and a memory communicatively connected to the processor, the memory storing instructions executable by the at least one processor, the instructions being executed by the processor to cause the at least one processor to perform a big data database storage method for an energy management system.
[0037] A computer-readable storage medium storing a computer program, which, when executed by a processor, implements a big data database storage method for an energy management system.
[0038] Compared with existing technologies, the energy management system big data database storage method of the present invention has the following advantages:
[0039] (1) The energy management system big data database storage method described in this invention can effectively control the capacity of a single database table within an ideal range;
[0040] (2) The energy management system big data database storage method described in this invention can store data into the database in a timely manner, solving the problem of slow insertion of a large amount of data into the index organization table and the inability to write the collected data into the database in a timely manner.
[0041] (3) The energy management system big data database storage method described in this invention optimizes the database storage structure and reduces the database storage space;
[0042] (4) The energy management system big data database storage method described in this invention is easy to maintain. It can batch export data tables of specified data types for specified years through a simple script for backup, and facilitate the subsequent restoration of the backup data to the database as needed.
[0043] (5) The energy management system big data database storage method described in this invention facilitates lightweight database maintenance. It deletes older database tables after backup, ensuring that the energy management system can continue to operate normally under the condition of limited server storage space and without adding storage hardware. Attached Figure Description
[0044] The accompanying drawings, which form part of this invention, are used to provide a further understanding of the invention. The illustrative embodiments of the invention and their descriptions are used to explain the invention and do not constitute an undue limitation of the invention. In the drawings:
[0045] Figure 1 This is a schematic diagram of the database storage process described in an embodiment of the present invention;
[0046] Figure 2 This is a schematic diagram of the database automatic conversion method described in an embodiment of the present invention;
[0047] Figure 3 This is a schematic diagram of the batch conversion method for historical date data according to an embodiment of the present invention. Detailed Implementation
[0048] It should be noted that, unless otherwise specified, the embodiments and features described in the present invention can be combined with each other.
[0049] The present invention will now be described in detail with reference to the accompanying drawings and embodiments.
[0050] To address the issue of slow web queries due to the ever-increasing content in database tables, this patented subway energy management system first utilizes the fast data storage characteristic of heap tables. Large amounts of collected data are initially stored in heap tables, facilitating web queries for the latest data. Starting the following day, the data in the heap tables is converted into an index-organized table (IOT) for storage. An IOT is a table stored within an index structure. Data in an IOT is stored and sorted according to the primary key. IOT eliminates the space overhead of a primary key because the index and the data are integrated. However, the benefits of IOT extend beyond saving disk space; more importantly, it significantly reduces I / O, decreases access buffer caching, improves buffer caching efficiency, and returns data query results faster.
[0051] Database storage process as follows Figure 1 As shown, the specific steps are as follows:
[0052] 1. Create stack tables and indexes. Create different stack tables based on the different data types in the energy management system. Examples include current database tables, voltage database tables, power database tables, power factor database tables, energy display database tables, frequency database tables, temperature, humidity, and flow sensor database tables, and passenger flow database tables.
[0053] 2. The heap tables created above are created daily, with all necessary heap tables created for each day. For example, the heap table for electricity consumption readings, dr_e_froze_20220522, represents the table name for storing electricity consumption readings on May 22, 2022. The table name is determined by adding a string representing the data type and the time. This operation splits the traditionally stored database tables into multiple tables based on natural days, simplifying web page queries by reducing the query time range and improving query efficiency. It also facilitates database table maintenance, allowing for exporting, backing up, and deleting tables on a daily basis, reducing database storage space requirements.
[0054] 3. After the energy management system obtains data from the terminal, it first stores the data in the corresponding stack table according to the data type.
[0055] 4. At the arrival of a natural day, according to the program settings, all heap tables from the previous natural day will be converted to index-organized tables with the same name for storage in the early morning, and then the original heap tables will be deleted. This ensures that the system has only one copy of the data. In this patent implementation case, due to the large amount of data, the conversion process is very slow. Therefore, the above database storage format conversion process is generally scheduled for the early morning, a time when the energy management system is not frequently used, and it does not affect other operations or conflict with business needs. Heap tables and index-organized tables with the same name facilitate web queries. For the web, it does not need to know whether the queried database table is a heap table or an index-organized table; the web directly queries and retrieves the corresponding data. The only difference in web queries is the time it takes to return the data.
[0056] This patent provides an automatic database storage conversion method and a manual database storage conversion method. The automatic conversion method is used for converting future dates, while the manual database storage conversion method is used for converting historical date data.
[0057] The following are automatic conversion methods, such as... Figure 2 As shown:
[0058] 1) Create log database table D
[0059] 2) Create a stored procedure E for writing log database tables, which provides a function-like interface that can write database operation procedures into database table D based on parameters.
[0060] 3) Create a database job, set it to automatic, and execute it daily at 1 AM. This job mainly involves converting the contents of a heap-organized table into an index-organized table with the same name. The process is as follows:
[0061] A retrieves the content of database table A from the previous day, creates a new index-organized table B with the same column names as database table A, and creates primary keys for both index-organized table B based on table ID and data time. Then, it copies the content of database table A to index-organized table B and creates a primary key constraint C in index-organized table B.
[0062] b. Delete database table A and its primary key constraint.
[0063] c. Modify the name of the index-organized table B to be consistent with the name of the database table A.
[0064] d. Modify the primary key constraint C of index-organized table B to have the same name as the primary key constraint of database table A.
[0065] e calls stored procedure E to write the operation process to the database.
[0066] 4) Historical date data conversion method:
[0067] Create a batch database table conversion stored procedure F. Parameters include a start date, an end date, and the database table name (containing multiple data types). This stored procedure primarily implements the manual conversion of all database tables within a specified date range from heap-organized tables to index-organized tables. Figure 3 As shown.
[0068] The main stored procedures are as follows:
[0069] a. Estimate the row storage size of each database table, as shown in Table (1). The storage capacity of database tables of different data types is V. i Num represents the number of database record rows stored daily; x k Define the size of the data type for the k-th field; y k Define the length of the stored content for the k-th field; Z k The size ratio is adjusted for the k-th field, primarily for variable-length data types; for fixed-length data types, it's 1. The above refers to Num, x... k y k z k All of these are predefined in the stored procedures of the database. The database table size V for different data types is calculated. i And sort them according to the database capacity V. i Sort by size from smallest to largest, starting with database size V. iStart with small database tables and gradually increase to larger database sizes (V). i The database tables.
[0070]
[0071] b. Retrieve the contents of database table A with the start time from the database, assign the start time to the current time variable, create a new index-organized table B, with the same column names as database table A, and create primary keys for index-organized table B based on table ID and data time. Then, copy the contents of database table A to index-organized table B, create the index-organized table, and create primary key constraint C.
[0072] c. Delete database table A and its primary key constraint.
[0073] d. Modify the name of the index-organized table B to be consistent with the name of the database table A.
[0074] e. Modify the primary key constraint C of index-organized table B to have the same name as the primary key constraint of database table A.
[0075] f calls stored procedure E to write the operation process to the database.
[0076] `g` checks if the current time equals the end time. If it's less, it adds a talent value to the current time and assigns it to the start time, then jumps to `b` for execution. If the current time equals the end time, it checks if all database tables have been processed. If not, it jumps to `a` to process the next data type database table. If they are equal, the loop ends.
[0077] 5. Complete the storage conversion process and wait for the database storage conversion on the next natural day. During the cross-natural-day query process on the web page, the database table corresponding to each natural day within the query time period is queried separately, greatly reducing the time the web page waits for all data to be returned.
[0078] 6. Backup operations: Scripts can be used to create scheduled tasks or manual operations to export database tables of specified data types within a specified time period (in calendar days), or to batch export database tables of all data types within a specified time period. If necessary, corresponding database tables can be deleted. For subsequent queries of older data, the corresponding backed-up database tables can be imported via scripts, eliminating the need to restore all database tables. Those skilled in the art will recognize that the units and method steps of the various examples described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, computer software, or a combination of both. To clearly illustrate the interchangeability of hardware and software, the components and steps of each example have been generally described in terms of functionality in the foregoing description. Whether these functions are implemented in hardware or software depends on the specific application and design constraints of the technical solution. Those skilled in the art can use different methods to implement the described functions for each specific application, but such implementations should not be considered beyond the scope of this invention.
[0079] In the several embodiments provided in this application, it should be understood that the disclosed methods and systems can be implemented in other ways. For example, the division of units described above is merely a logical functional division, and in actual implementation, there may be other division methods. For instance, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. The aforementioned units may or may not be physically separated. The components shown as units may or may not be physical units, that is, they may be located in one place or distributed across multiple network units. Some or all of the units can be selected to achieve the purpose of the embodiments of the present invention according to actual needs.
[0080] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, and not to limit them. Although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some or all of the technical features therein. Such modifications or substitutions 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, and they should all be covered within the scope of the claims and specification of the present invention.
[0081] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the protection scope of the present invention.
Claims
1. A method for storing a big data database in an energy management system, characterized in that, The database storage process is as follows: S1. Create a heap table daily based on data type; S2, Store the data obtained from the terminal into a heap table of the corresponding data type; S3. Determine if it is time for the scheduled task to be executed on the next natural day. If so, proceed to step S4. S4. Convert the heap table generated on the previous day into an index-organized table and store it in the database. This includes automatic and manual database storage conversion methods. The automatic conversion method is used for future date conversions, while the manual database storage conversion method is used for historical date data conversions. The automatic conversion method is as follows: A1. Create log database table D; A2. Create the stored procedure E for the write log database table; A3. Create a database task job; S5. Complete the storage conversion process; S6. Perform a backup operation; In step A2, a stored procedure E is created to write a log database table. This procedure provides a function interface and writes the database operation process table into the database table D according to the parameters, thus recording the log of the operation process.
2. The energy management system big data database storage method according to claim 1, characterized in that: In step S1, different stack tables are created according to different data types in the energy management system, including current database table, voltage database table, power database table, power factor database table, energy indication database table, frequency database table, temperature, humidity and flow sensor database table, and passenger flow database table.
3. The method for storing a big data database in an energy management system according to claim 1, characterized in that, In step A3, a database job is created, set as an automatic job, and scheduled to run at 1:00 AM daily. This job is used to convert the contents of a heap-organized table into an index-organized table with the same name. The process is as follows: A. Retrieve the contents of database table A from the previous day, create a new index-organized table B, with the same column names as database table A, and create primary keys for both index-organized table B according to table ID and data time. Then copy the contents of database table A to index-organized table B, and create primary key constraints C in index-organized table B. B. Delete database table A and its primary key constraint; C. Modify the name of index-organized table B to match the name of database table A; D. Modify the primary key constraint C of index-organized table B to have the same name as the primary key constraint of database table A. E. Call the stored procedure E to write the operation process into the database table D.
4. The energy management system big data database storage method according to claim 3, characterized in that: Including methods for converting historical date data: Create a batch database table conversion stored procedure F. The parameters include a start date, an end date, and a database table name. The database table name can contain database tables with multiple data types. The stored procedure is a manual process to convert all database tables within a specified date from heap-organized tables to index-organized tables. The stored procedure is as follows: a. Estimate the row storage size of each database table, including its table name. The storage capacity of database tables with different data types is V. i Num represents the number of database record rows stored daily; x k Define the size of the data type for the k-th field; y k Define the length of the stored content for the k-th field; z k This setting adjusts the size ratio for the k-th field, and is used for variable-length data types. For fixed-length data types, it's 1. (The values for Num, x, etc. are not specified in the original text.) k y k z k All of these are predefined in the stored procedures of the database, and the database table size V for different data types is calculated. i And sort them according to the database capacity V. i Sort by size from smallest to largest, starting with database size V. i Start with small database tables and then gradually work on larger database sizes (V). i Database tables; （1） b. Retrieve the contents of database table A with the start time in the database, assign the start time to the current time variable, create a new index-organized table B, with the same column names as database table A, and create primary keys for index-organized table B according to table ID and data time. Then copy the contents of database table A to index-organized table B, create the index-organized table, and create primary key constraint C. c. Delete database table A and its primary key constraint; d. Modify the name of index-organized table B to match the name of database table A; e. Modify the primary key constraint C of index-organized table B to have the same name as the primary key constraint of database table A. f. Call stored procedure E and write the operation process into database table D; g. Check if the current time is equal to the end time. If it is less than the end time, add one talent value to the current time and assign it to the start time. Then jump to b to execute. If the current time is equal to the end time, continue to check if all database tables have been processed. If not, jump to a to process the next data type database table. If they are equal, end the loop processing process.
5. The method for storing a big data database in an energy management system according to claim 1, characterized in that: In step S5, the storage conversion process is completed, and the database storage conversion is awaited for the next natural day. During the cross-natural day query process of the web page, the database table to which each natural day belongs within the query time period is queried, reducing the time for the web page to wait for all data to be returned.
6. An electronic device, comprising a processor and a memory communicatively connected to the processor and used for storing processor-executable instructions, characterized in that: The processor is used to execute the energy management system big data database storage method according to any one of claims 1-5.
7. A server, characterized in that: The device includes at least one processor and a memory communicatively connected to the processor. The memory stores instructions executable by the at least one processor, which are executed by the processor to cause the at least one processor to perform a big data database storage method for an energy management system as described in any one of claims 1-5.
8. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by the processor, it implements the energy management system big data database storage method according to any one of claims 1-5.