A high-performance settlement calculation method for electricity spot market based on time series database

Abstract

The present invention discloses a high-performance settlement calculation method for the electricity spot market based on a time series database, comprising the following steps: Step 1: Establishing a high-performance settlement data model based on a time series database; Step 2: Building and optimizing the spot settlement logic based on the time series database; Step 3: Integrating the calculation process of the time series database with the existing power trading platform function. The present invention combines the spot settlement business logic of the power trading platform, uses the time series database to build and reconstruct the calculation logic, achieves a significant improvement in calculation efficiency, and proposes a design scheme for the integrated application of the time series database and the trading platform, and realizes the efficient development of the spot settlement business by assisting the spot settlement function of the trading platform.

Description

Technical Field

[0001] The present invention belongs to the technical field of power transaction settlement, and in particular relates to a high-performance settlement calculation method for a power spot market based on a time series database. Background Art

[0002] A time series database is a database that stores time series data, which is quite different from a traditional relational database. Relational databases are mainly used to describe the relationship between data and use row structures for storage; time series databases are mainly used to store single parameters in time series and use column storage structures to describe the relationship between single data parameters and timestamps. There is no obvious correlation between different data parameters. When inserting and querying data, relational databases often involve correlations between multiple tables, so the insertion and query efficiency is limited. When the data level is high, as the amount of data increases, the efficiency of data insertion and query will decrease exponentially. The column storage of time series databases solves the above bottlenecks of relational databases and has strong performance advantages in data insertion and query.

[0003] The new generation of power market trading platform provides full-process technical support for market-oriented business. With the advancement of spot trading, the platform coordinates medium- and long-term and spot trading to support the coordinated operation of all trading businesses. As the settlement cycle expands from monthly to daily, monthly and annual, the market scale expands, which puts higher requirements on settlement business, requiring the optimization of data models and the use of high-performance computing technology. In addition, the access of massive users will generate a large amount of data, which has put great pressure on the "daily settlement and monthly settlement" of spot settlement business.

[0004] Therefore, faced with the problem of massive data generated by massive user access, and with the actual demand for the expansion of the settlement cycle from monthly to daily, monthly and annual settlement, how to improve computing efficiency, realize the efficient development of spot settlement business, and improve the operating efficiency in the existing settlement platform has become a difficult problem that needs to be urgently solved by technicians in the field of power trading and settlement. Summary of the invention

[0005] In view of the shortcomings of the prior art, the technical problem to be solved by the present invention is to provide a high-performance settlement calculation method for the electricity spot market based on a time series database, which can significantly improve the computing efficiency, integrate the time series database with the trading platform, and realize the efficient development of the spot settlement business by assisting the spot settlement function of the trading platform, thereby improving the operating efficiency in the existing settlement platform.

[0006] In order to solve the above technical problems, the technical solution adopted by the present invention is: a high-performance settlement calculation method for the power spot market based on a time series database, comprising the following steps:

[0007] Step 1: Establish a high-performance settlement data model based on the time series database, which includes:

[0008] Combined with spot settlement business data, analyze the calling method of spot settlement data source in relational database, and build distributed databases and tables in time series database; migrate spot settlement data source from relational database to time series database, and establish data migration mapping relationship between tables;

[0009] Step 2: Build and optimize the spot settlement logic based on the time series database, which includes:

[0010] According to the settlement logic built on the relational database in the settlement platform, the business model and data flow are extracted, and SQL-like statements are optimized in DolphinDB. The computing efficiency is optimized through vectorized computing, functional programming and parallel computing.

[0011] Step 3: Integrate the computational process of the time series database with the existing power trading platform functions, including:

[0012] In the same test environment, run the settlement logic of the power trading platform, and build the time series database table according to steps one and two, deploy the optimized time series database settlement logic code, and integrate the calculation process of the time series database with the existing power trading platform functions.

[0013] In the above-mentioned high-performance settlement calculation method for the electricity spot market based on the time series database, in step 1, the specific process of establishing a high-performance settlement data model based on the time series database is as follows:

[0014] (1) Create database and table:

[0015] Analyze the needs of spot settlement business, analyze the data query method of each table, use TSDB engine and range partitioning, use time information as the partition column to build database and table, and sort out the data sources of spot settlement business data;

[0016] (2) Automated data migration:

[0017] 1. Establishing the data migration process:

[0018] Establish inter-database data table mapping relationships and inter-database communication mechanisms to migrate spot settlement data sources from relational databases to time series databases;

[0019] 2. Automatic triggering of data migration:

[0020] Setting up automated data migration includes the following three steps: deploying a single instance of Kafka for real-time data storage, deploying MySQL data synchronization to Kafka, and deploying Kafka data synchronization to DolphinDB.

[0021] In the above-mentioned high-performance settlement calculation method for the electricity spot market based on a time series database, in step (i), an ODBC plug-in is used to establish a mapping relationship between data tables between libraries, and each table of the spot settlement data source is migrated one by one from the relational database to the time series database.

[0022] In the above-mentioned high-performance settlement calculation method for the electricity spot market based on the time series database, in the step (i), an inter-database communication mechanism is established by establishing an inter-database communication interface based on ODBC.

[0023] In the above-mentioned high-performance settlement calculation method for the electricity spot market based on the time series database, in step 2, the specific process of building and optimizing the spot settlement logic based on the time series database is as follows:

[0024] (1) Construction of spot settlement logic:

[0025] Settlement logic is built in sequence according to the steps of daily settlement data preparation, daily settlement, monthly settlement data preparation, and monthly settlement calculation, and settlement logic development is performed in the time series database;

[0026] (2) Optimization of spot settlement logic:

[0027] The spot settlement code in the time series database is developed with reference to the SQL statements of the relational database, and the calculation modules are divided and encapsulated, and the overall optimization is performed using the following methods:

[0028] 1. Vectorized Calculation

[0029] In the time series database, establish data objects at the settlement unit granularity, vectorize the settlement logic, write the calculation logic, and execute the script to obtain the corresponding results;

[0030] (II) Functional Programming:

[0031] Analyze the requirements and partition the SQL statements of spot settlement logic into functional blocks, select appropriate functions to build function block logic, optimize the functions after replacing the SQL part, and test and verify the computing performance;

[0032] 3. Parallel computing:

[0033] Through data type selection and partitioning, data type selection and data partitioning optimization are achieved; efficient computing functions are applied to achieve computing efficiency optimization; and query optimization is achieved through configuration adjustment and resource regulation.

[0034] In the above-mentioned high-performance settlement calculation method for the electricity spot market based on the time series database, in step 3, the specific process of integrating the calculation process of the time series database with the function of the existing power trading platform is as follows:

[0035] (1) Change capture:

[0036] According to the database update time of the trading platform, when the monitored original business data table generates new data, the data table modification log is automatically generated, and the program automatically captures the spot settlement function involved in the corresponding modified table. As long as the data in any data source table of a certain function is modified, the calculation process of the corresponding spot function on the corresponding date is triggered;

[0037] (2) Comparison of results:

[0038] Compare the settlement result tables of the corresponding functions of the relational database and the time series database before the spot settlement data source is changed. When the data is completely consistent, execute the subsequent calculation process; if there is inconsistency in the data results, push an abnormal alarm;

[0039] (3) Data Migration:

[0040] When the comparison results are consistent, execute this step and subsequent steps, and use the ODBC-based database communication interface established in step 1 to write the spot settlement data source table of the relevant date into the time series database;

[0041] (4) Settlement operation:

[0042] The program automatically triggers the optimized time series database settlement logic in step 2 and stores the results in the settlement result table;

[0043] (5) Result synchronization:

[0044] Use the ODBC-based database communication interface established in step 1 to synchronize the settlement results of the corresponding date from the time series database to the settlement result table of the relational database and present them as preliminary settlement data.

[0045] In the above-mentioned high-performance settlement calculation method for the electricity spot market based on the time series database, in the (iii) parallel calculation, the data types are divided into precise numerical types, time series related types and string types, and partitioned by time and by business key columns to achieve optimization of data type selection and data partitioning; combined with vectorized computing technology, a complex calculation process can be completed through a single function call, thereby achieving optimization of the calculation efficiency of fast processing of large-scale electricity time series data; by adjusting the concurrency-related parameters of the maximum number of connections and the number of parallel tasks, it is adapted to different business loads and achieves optimization of query performance.

[0046] The advantages of the high-performance settlement calculation method for the electricity spot market based on the time series database of the present invention are: the present invention combines the spot settlement business logic of the power trading platform, uses the time series database to build and reconstruct the calculation logic, achieves a significant improvement in calculation efficiency, and proposes a design scheme for the integrated application of the time series database and the trading platform, and realizes the efficient development of the spot settlement business by assisting the spot settlement function of the trading platform. BRIEF DESCRIPTION OF THE DRAWINGS

[0047] Figure 1 This is a schematic diagram of the settlement data source and data preprocessing process in the process of building databases and tables in step 1;

[0048] Figure 2 This is a flow chart of the process of building spot settlement logic in step 2 of the present invention;

[0049] Figure 3 This is a schematic diagram of the calculation results of spot daily clearing and settlement;

[0050] Figure 4 This is a flow chart of the vectorized calculation optimization step in the spot settlement logic optimization process in step 2 of the present invention;

[0051] Figure 5 This is a flow chart of the functional programming optimization steps in the spot settlement logic optimization process in step 2 of the present invention;

[0052] Figure 6 This is a flow chart of the parallel computing optimization steps in the spot settlement logic optimization process in step 2 of the present invention;

[0053] Figure 7 An interaction diagram is designed for the integrated application of the time series database and the power trading platform in step three of the present invention. DETAILED DESCRIPTION

[0054] The present invention is further described in detail below with reference to the accompanying drawings and specific embodiments.

[0055] In the present invention, unless otherwise stated, directional words such as "upper" and "lower" generally refer to the upper and lower parts of the device in actual use or working state, specifically the drawing direction in the attached drawings; while "inside" and "outside" refer to the outline of the device. In addition, in the description of this application, the term "including" means "including but not limited to". The terms first, second, third, etc. are used only as labels and do not impose numerical requirements or establish an order. The term "plurality" means "two or more".

[0056] A high-performance settlement calculation method for power spot market based on time series database includes the following steps:

[0057] Step 1: Establish a high-performance settlement data model based on the time series database, which includes:

[0058] Combined with the spot settlement business data, the calling method of the spot settlement data source in the relational database is analyzed, and distributed database and table construction is performed in the time series database; the spot settlement data source is migrated from the relational database to the time series database, and the data migration mapping relationship between tables is established; the specific process is as follows:

[0059] (1) Create database and table:

[0060] Analyze the needs of spot settlement business, analyze the data query method of each table, use TSDB engine and range partitioning, use time information as the partition column to build database and table, and sort out the data sources of spot settlement business data;

[0061] (2) Automated data migration:

[0062] 1. Establishing the data migration process:

[0063] Establish inter-database data table mapping relationships, use ODBC plug-ins to establish inter-database data table mapping relationships, and migrate each table of the spot settlement data source from the relational database to the time series database one by one. And establish an inter-database communication mechanism by establishing an ODBC inter-database communication interface, and migrate the spot settlement data source from the relational database to the time series database;

[0064] Connect to the time series database in ODBC mode. The process of establishing an ODBC-based database communication interface is as follows:

[0065] conn1 = odbc::connect("Dsn=mySQLOdbcDsn")

[0066] odbc::close(conn1)

[0067] 2. Automatic triggering of data migration:

[0068] Setting up automated data migration includes the following three steps: deploying a single instance of Kafka for real-time data storage, deploying MySQL data synchronization to Kafka, and deploying Kafka data synchronization to DolphinDB.

[0069] like Figure 1 As shown, step 1 sorts out the data sources of spot settlement data. Spot settlement data comes from multiple data sources, such as marketing department data, scheduling data, marketing data, etc., and has been built in the relational database. The present invention refers to the database and table building method of the relational database and uses the database and table building in the time series database. Spot settlement data has time series characteristics, so the present invention adopts the TSDB engine and range partitioning, and uses time information as the partition column to build the database and table. It takes 28ms to query the full table of measurement data in the time series database, which saves about 98.55% compared to 1.929s in the relational database.

[0070] Step 2: Build and optimize the spot settlement logic based on the time series database, which includes:

[0071] According to the settlement logic built on the relational database in the settlement platform, the business model and data flow are extracted, and the SQL-like statements are optimized in DolphinDB. The computing efficiency is optimized through vectorized computing, functional programming and parallel computing. The specific process is as follows:

[0072] (1) Construction of spot settlement logic:

[0073] Settlement logic is built in sequence according to the steps of daily settlement data preparation, daily settlement, monthly settlement data preparation, and monthly settlement calculation, and settlement logic development is performed in the time series database;

[0074] like Figure 2 , 3 As shown in the figure, this step sorts out the main logic of spot settlement and the settlement logic development process, builds the settlement logic in sequence according to the daily settlement data preparation, daily settlement, monthly settlement data preparation, and monthly settlement calculation steps, and develops the settlement logic in the time series database. Figure 3 As shown, taking daily settlement as an example, the settlement process is to Figure 1 The data is extracted and integrated from various data sources, and various types of electricity, electricity prices and electricity fee data of users on the power generation side and user side are calculated, and the settlement results are integrated on this basis. After the construction is completed, this paper compares the results with those in the relational database to verify the calculation results.

[0075] (2) Optimization of spot settlement logic:

[0076] The spot settlement code in the time series database is developed with reference to the SQL statements of the relational database, and the calculation modules are divided and encapsulated, and the overall optimization is performed using the following methods:

[0077] 1. Vectorized Calculation

[0078] like Figure 4 As shown in the figure, in the time series database, a data object with the granularity of the settlement unit is established, the settlement logic is vectorized, and after the calculation logic is written, the script is executed to obtain the corresponding results; the settlement logic involves data integration and calculation between multiple data sources. The vectorized calculation in the time series database is used to replace the for loop logic in the original SQL statement to improve the settlement efficiency.

[0079] (II) Functional Programming:

[0080] like Figure 5 As shown in the figure, the SQL statements of the spot settlement logic are analyzed for requirements and partitioned into functional blocks, appropriate functions are selected to build the function block logic, the SQL part is replaced to optimize the function, and the computing performance is tested and verified; after using function encapsulation, the data in the same settlement script does not need to be called repeatedly, which can simplify the code logic, facilitate operation and maintenance, and improve operation efficiency.

[0081] 3. Parallel computing:

[0082] like Figure 6 As shown in the figure, data type selection and data partitioning optimization are achieved through data type selection and partitioning; computing efficiency optimization is achieved by applying efficient computing functions; query optimization is achieved through configuration adjustment and resource regulation. Specifically, data types are divided into precise numeric types, time series related types, and string types, and partitioned by time and by business key columns, which achieves the optimization of data type selection and data partitioning; combined with vectorized computing technology, complex computing processes can be completed through a single function call, which is suitable for rapid processing of large-scale power time series data, and achieves the optimization of computing efficiency; by adjusting concurrency-related parameters, such as the maximum number of connections, the number of parallel tasks, etc., to adapt to different business loads, query performance optimization is achieved. Therefore, the parallel logic of statistical analysis of multiple data sources is optimized using parallel computing, and the operating efficiency is improved while ensuring reasonable memory usage.

[0083] The evaluation of the settlement logic effect of the time series database of the present invention is shown in Table 1:

[0084] Table 1: Comparison of spot daily clearing and settlement operation time (unit: seconds)

[0085]

[0086] Taking the daily clearing function on the user side as an example, according to statistics based on data from October 2024, the use of a time series database will reduce the running time of the three commonly used reports for spot settlement by approximately 76.54%. This shows that the present invention can significantly improve the calculation and operation efficiency of spot settlement.

[0087] Step 3: Integrate the computational process of the time series database with the existing power trading platform functions, including:

[0088] In the same test environment, the settlement logic of the power trading platform is run, and according to steps one and two, the time series database is built and tables are constructed, and the optimized time series database settlement logic code is deployed, thus providing a basis for integrating the calculation process of the time series database with the existing power trading platform functions. Figure 7 As shown, the specific process is as follows:

[0089] (1) Change capture:

[0090] According to the database update time of the trading platform, when the monitored original business data table generates new data, the data table modification log is automatically generated, and the program automatically captures the spot settlement function involved in the corresponding modified table. As long as the data in any data source table of a certain function is modified, the calculation process of the corresponding spot function on the corresponding date is triggered;

[0091] (2) Comparison of results:

[0092] This step is a settlement logic consistency comparison procedure. It compares the settlement result tables of the corresponding functions of the relational database and the time series database before the spot settlement data source is changed. When the data is completely consistent, the subsequent calculation process is executed; if there is inconsistency in the data results, an abnormal alarm is pushed;

[0093] (3) Data Migration:

[0094] When the comparison results are consistent, execute this step and subsequent steps, and use the ODBC-based database communication interface established in step 1 to write the spot settlement data source table of the relevant date into the time series database;

[0095] (4) Settlement operation:

[0096] The program automatically triggers the optimized time series database settlement logic in step 2 and stores the results in the settlement result table;

[0097] (5) Result synchronization:

[0098] Use the ODBC-based database communication interface established in step 1 to synchronize the settlement results of the corresponding date from the time series database to the settlement result table of the relational database and present them as preliminary settlement data.

[0099] Through the above steps, the present invention integrates the calculation process of the time series database with the existing platform functions, builds a time series database and trading platform integrated application design scheme, and enables high-performance spot settlement calculation to assist the power trading platform function.

[0100] The effect evaluation is as follows:

[0101] This test case uses the full data of "Dispatching and Dispatching Power Plant Grid Electricity Meter" (data volume 37.13k rows) as input data, takes the real-time settlement electricity price calculation as the test calculation unit, and reconstructs the code in the time series database. Figure 7 A test environment was built for the design of the integrated application of time series database and power trading platform.

[0102] Table 2: Comparative analysis of spot settlement running time (unit: milliseconds)

[0103]

[0104] As shown in Table 2, the running time of this test example in the time series database is much shorter than that in the relational database, and the main part of the time consumption is the data synchronization between the databases. Therefore, using the time series database to build the settlement logic can achieve a more flexible, practical and efficient computing unit. Compared with the traditional relational database SQL, it has obviously played a good effect in improving the operating efficiency in the existing settlement platform.

[0105] In summary, the present invention combines the spot settlement business logic of the power trading platform, uses the time series database to build and reconstruct the calculation logic, achieves a significant improvement in calculation efficiency, and proposes a design scheme for the integrated application of the time series database and the trading platform. By assisting the spot settlement function of the trading platform, the efficient development of the spot settlement business is achieved.

[0106] Of course, the above description is not a limitation of the present invention, and the present invention is not limited to the above examples. Any changes, modifications, additions or substitutions made by ordinary technicians in this technical field within the essential scope of the present invention should fall within the protection scope of the present invention.

Claims

1. A high-performance settlement calculation method for the electricity spot market based on a time series database, characterized in that: The steps include: Step 1: Establish a high-performance settlement data model based on the time series database, which includes: Combined with spot settlement business data, analyze the calling method of spot settlement data source in relational database, and build distributed databases and tables in time series database; migrate spot settlement data source from relational database to time series database, and establish data migration mapping relationship between tables; Step 2: Build and optimize the spot settlement logic based on the time series database, which includes: According to the settlement logic built on the relational database in the settlement platform, the business model and data flow are extracted, and SQL-like statements are optimized in DolphinDB. The computing efficiency is optimized through vectorized computing, functional programming and parallel computing. Step 3: Integrate the computational process of the time series database with the existing power trading platform functions, including: In the same test environment, run the settlement logic of the power trading platform, and build the time series database according to steps one and two, deploy the optimized time series database settlement logic code, and integrate the calculation process of the time series database with the existing power trading platform functions; In step 2, the specific process of building and optimizing the spot settlement logic based on the time series database is as follows: (1) Construction of spot settlement logic: Settlement logic is built in sequence according to the steps of daily settlement data preparation, daily settlement, monthly settlement data preparation, and monthly settlement calculation, and settlement logic development is performed in the time series database; (2) Optimization of spot settlement logic: The spot settlement code in the time series database is developed with reference to the SQL statements of the relational database, and the calculation modules are divided and encapsulated, and the overall optimization is performed using the following methods: (I) Vectorized Calculation: In the time series database, establish data objects at the settlement unit granularity, vectorize the settlement logic, write the calculation logic, and execute the script to obtain the corresponding results; (II) Functional programming: Carry out demand analysis and functional block partitioning on the SQL statements of spot settlement logic, select appropriate functions to build function block logic, optimize functions after replacing SQL parts, and test and verify computing performance; (III) Parallel computing: Through data type selection and partitioning, data type selection and data partitioning optimization are achieved; efficient computing functions are applied to achieve computing efficiency optimization; and query optimization is achieved through configuration adjustment and resource regulation.

2. According to claim 1, the high-performance settlement calculation method for the electricity spot market based on the time series database is characterized in that: In step 1, the specific process of establishing a high-performance settlement data model based on the time series database is as follows: (1) Create database and table: Analyze the needs of spot settlement business, analyze the data query method of each table, use TSDB engine and range partitioning, use time information as the partition column to build database and table, and sort out the data sources of spot settlement business data; (2) Automated data migration:

1. Establishing the data migration process: Establish inter-database data table mapping relationships and inter-database communication mechanisms to migrate spot settlement data sources from relational databases to time series databases; (II) Automatic triggering of data migration: Setting up automated data migration includes the following three steps: deploying a single instance of Kafka for real-time data storage, deploying MySQL data synchronization to Kafka, and deploying Kafka data synchronization to DolphinDB.

3. The high-performance settlement calculation method for the electricity spot market based on a time series database according to claim 2 is characterized by: In the step 1, an ODBC plug-in is used to establish a mapping relationship between data tables in the database, and each table of the spot settlement data source is migrated from the relational database to the time series database one by one.

4. The high-performance settlement calculation method for the electricity spot market based on a time series database according to claim 2 is characterized by: In the step 1, an inter-database communication mechanism is established by establishing an inter-database communication interface based on ODBC.

5. The high-performance settlement calculation method for the electricity spot market based on a time series database according to claim 1 is characterized in that: In step 3, the specific process of integrating the operation process of the time series database with the function of the existing power trading platform is as follows: (1) Change capture: According to the database update time of the trading platform, when the monitored original business data table generates new data, the data table modification log is automatically generated, and the program automatically captures the spot settlement function involved in the corresponding modified table. As long as the data in any data source table of a certain function is modified, the calculation process of the corresponding spot function on the corresponding date is triggered; (2) Results comparison: Compare the settlement result tables of the corresponding functions of the relational database and the time series database before the spot settlement data source is changed. When the data is completely consistent, execute the subsequent calculation process; if there is inconsistency in the data results, push an abnormal alarm; (3) Data Migration: When the comparison results are consistent, execute this step and subsequent steps, and use the ODBC-based database communication interface established in step 1 to write the spot settlement data source table of the relevant date into the time series database; (4) Settlement operation: The program automatically triggers the optimized time series database settlement logic in step 2 and stores the results in the settlement result table; (5) Result synchronization: Use the ODBC-based database communication interface established in step 1 to synchronize the settlement results of the corresponding date from the time series database to the settlement result table of the relational database and present them as preliminary settlement data.

6. The high-performance settlement calculation method for the electricity spot market based on a time series database according to claim 1 is characterized by: In the above (iii), in the parallel computing, data types are divided into precise numerical types, time series related types and string types, and partitioned by time and by business key columns to achieve optimization of data type selection and data partitioning; combined with vectorized computing technology, complex computing processes can be completed through a single function call, thereby achieving optimization of computing efficiency for rapid processing of large-scale power time series data; by adjusting the concurrency-related parameters of the maximum number of connections and the number of parallel tasks, it is possible to adapt to different business loads and achieve optimization of query performance.

Images