Wind power time series data storage management method based on postgresql relational database

Abstract

The application discloses a wind power time sequence data storage management method based on a PostgreSQL relational database. In the PostgreSQL relational database, operation data related to a wind farm is organized together to form a wind farm schema; the schema of different wind farms is replaced by corresponding wind farm IDs, namely, a special schema of a wind farm, and data storage tables corresponding to wind turbine units are replaced by corresponding wind turbine IDs, namely, corresponding wind turbine historical operation data storage tables; for the management of wind turbine operation historical data, corresponding PG storage procedures are written to complete the creation of historical data tables and the addition of measuring points in the wind turbine historical data tables. The application stores full wind turbine operation data, the time granularity of the wind turbine operation data reaches a second level, and the relational database design is used, so that the database design difficulty is not increased.

Description

TECHNICAL FIELD

[0001] The present application mainly relates to the field of wind power technology, and particularly relates to a wind power time series data storage management method based on a PostgreSQL relational database. BACKGROUND

[0002] Before the rise of big data technology, the wind power industry was not very concerned about the operation data of the unit. Therefore, the full-quantity second-level historical data of the unit operation is not stored and analyzed, so that the amount of data to be stored and processed is small, and the storage and processing pressure is not large. Later, professional personnel in the industry found that the unit operation data has great value in analyzing the performance of the unit, processing quality problems and improving design, and thus realized the value of the wind turbine operation history data, and thus more and more attention is paid to the storage and analysis of the unit operation history data. However, after storing the historical data of the unit in full quantity and with a granularity of seconds, the storage capacity is more than two orders of magnitude higher than before, and higher requirements are placed on the database, storage structure and computer resources.

[0003] In order to solve the data storage access efficiency problem from the aspect of data storage management, the following problems need to be solved:

[0004] The first is partition organization and storage. When storing a large amount of data, relational databases need to be optimized and managed. Good partition management can greatly improve storage efficiency and query efficiency. However, partition optimization and management require high requirements for database administrators and software developers, and need to be analyzed repeatedly. The full-quantity second-level historical operation data of the wind turbine, although large in quantity, has two obvious characteristics. The first is the independence of the unit, that is, the data of each unit is independent and has the same structure. The second is the time characteristic, that is, the unit operation data has no meaning without the attribute of time.

[0005] The second is that the data volume is very large, and the storage space requirement is very high. Taking a 2.5MW unit as an example, there are about 360 measuring points in total, half of which are switch quantities (0 or 1), one-third of which are pure digital quantities (state, fault alarm, etc. integer value), and one-sixth of which are analog quantities (floating point numbers). If the average is 2 bytes, 1 second 1 row of records, the data volume is 720B, the data volume of one day is more than 60MB, the data volume of 20 wind turbines (50MW) in a wind farm is more than 1.2GB, and the data volume of one year is about 500GB. These do not calculate data cache and other overheads of the database. The design life of the unit is 20 years, so the storage space requirement is very high.

[0006] Third, access efficiency. Data not only to store, in wind farm, also need to carry on various query analysis. Such as calculating power generation, loss of power, availability and other various index statistics, etc. Then the number of wind turbine involved, time span, data record quantity is huge, query access efficiency has become a difficult point of data storage.

[0007] Therefore, without increasing the complexity of system design, and normal use of relational database as efficiently store and access the historical operation data of wind turbine is the problem to be solved. SUMMARY

[0008] The technical problem to be solved by the present application is that in view of the technical problems existing in the prior art, the present application provides a wind power time series data storage management method based on PostgreSQL relational database, which stores full amount of wind turbine operation data, wind turbine operation data time granularity reaches seconds, uses relational database design, and does not increase the difficulty of database design.

[0009] To solve the above technical problems, the technical scheme provided by the present application is:

[0010] A wind power time series data storage management method based on PostgreSQL relational database, in the PostgreSQL relational database, the operation data related to a wind farm is organized together to form a wind farm mode; wherein the schema of different wind farms is replaced by the corresponding wind farm ID, that is, a special schema for a wind farm, and the second-level data, minute-level data, switching value and digital value change data storage table of the wind turbine corresponding to the wind turbine are replaced by the corresponding wind turbine ID, that is, the corresponding wind turbine historical operation data storage table; for the management of wind turbine operation history data, the corresponding PG storage process is written, the historical data table is created, and the measuring point is added in the wind turbine historical data table; wherein the process of creating the historical data table is: defining the PLPGSQL function, calling the corresponding PLPGSQL function to generate the second-level historical data table, the minute-level historical data table, the switching value and the digital value change value storage data table of a certain wind turbine in the wind farm, and generating the TimescaleDB super table; wherein the process of adding measuring point in the wind turbine historical data table is: defining the PLPGSQL function, calling the corresponding PLPGSQL function, checking all wind turbines of the wind farm specified by the pid according to the corresponding configuration, and checking the second-level historical data measuring point and the minute-level historical data measuring point of the wind turbine identified by the tid; if new analog quantity measuring point is found, it is added in the corresponding data table.

[0011] Preferably, it also includes data caching: using one minute caching for second-level data, first writing into a temporary file in the cache folder, and then submitting in batches every minute to reduce the time of database write lock occupation and improve the efficiency of database operation.

[0012] Preferably, it also includes periodic rolling deletion: deleting the second-level data in the database that exceeds the preset time by using the drop_chunks function.

[0013] Preferably, creating the historical data table comprises:

[0014] Defining the PLPGSQL function create_secdata_table(pid TEXT, wid TEXT, tid INTEGER) RETURNS VOID; calling the function to generate the second-level historical data table of a certain wind turbine in the wind farm, wherein pid represents the wind farm id, wid represents the wind turbine id, and tid represents the model; in the function, the names and types of all measuring points of the wind turbine are determined according to the parameters, and then the second-level historical data table is created using these parameters, and a hyper table of TimescaleDB is generated;

[0015] Defining the PLPGSQL function create_mindata_table(pid TEXT, wid TEXT, tid INTEGER) RETURNS VOID; calling the function to generate the minute-level historical data table of a certain wind turbine in the wind farm, wherein pid represents the wind farm id, wid represents the wind turbine id, and tid represents the model; in the function, the names of all analog measuring points of the corresponding wind turbine are determined according to the parameters, and then the minute-level historical data table is created using these parameters, and a hyper table of TimescaleDB is generated;

[0016] Defining the PLPGSQL function create_digdata_table(pid TEXT, wid TEXT) RETURNS VOID; calling the function to generate the data table for storing the change values of the on-off quantity and digital quantity of a certain wind turbine in the wind farm, wherein pid represents the wind farm id, and wid represents the wind turbine id; after the creation, a timescaled hyper table is generated.

[0017] Preferably, the process of adding measuring points to the second-level historical data table is:

[0018] Defining the PLPGSQL function check_sec_table(pid TEXT, tid INTEGER) RETURNS VOID; calling the function to check all second-level historical data measuring points of all wind turbines in the wind farm specified by pid and identified by tid according to the corresponding configuration; if a new measuring point is found, a measuring point field is added to all second-level data tables of the corresponding model.

[0019] Preferably, the process of adding measuring points to the minute-level historical data table is:

[0020] Define the PLPGSQL function check_min_table(pid TEXT, tid INTEGER) RETURNS VOID; call the function to check all the minute-level historical data points of the wind turbine identified by tid in the wind farm specified by pid according to the corresponding configuration; if a new analog quantity data point is found, add the average value, maximum value, minimum value and standard deviation field of the data point in all the minute-level data tables of the corresponding wind turbine.

[0021] Preferably, the drop_chunks function is: SELECT drop_chunks(INTERVAL 'n months', '<table_name>');

[0022] Preferably, the second-level data is stored: the wind turbine operation data is selected to be sampled once every second, that is, one row of data is generated every second; the data structure is as follows:

[0023] (dt, flag, tag1, tag2,..., tagn)

[0024] Wherein, dt represents the timestamp of the corresponding record, flag represents the record flag, which is -1 when the wind turbine loses communication and 1 when the wind turbine communicates normally, and tag1-tagn represents all the measured point values of the wind turbine.

[0025] Preferably, the minute data is stored: the wind turbine minute data record is 1 row per minute, and the data structure is as follows:

[0026] (dt, flag, tag1_avg, tag1_max_tag1_min, tag1_sd,...)

[0027] Wherein, dt represents the timestamp of the corresponding record, flag represents the record flag, which is 1-60, indicating that the current 1-minute record is aggregated from n 1-second records, tag1_avg represents the average value of measured point 1 in the 1 minute, tag1_max represents the maximum value of measured point 1 in the 1 minute, tag1_min represents the minimum value of measured point 1 in the 1 minute, tag1_sd represents the standard deviation of measured point 1 in the 1 minute, and all analog quantity measured point data are sequentially identified by flag.

[0028] Preferably, the on-off quantity and digital quantity are stored as:

[0029] The data structure of the on-off quantity and digital quantity change value of the wind turbine is as follows:

[0030] (dt, tagid, value)

[0031] Wherein dt also represents a time stamp, tagid represents the id of the measuring point, value represents the measuring point value; it represents that at the time of dt, the value of the measuring point tag becomes value, so the value of tag before this time is the value recorded last time, and the time stamp of the two records is the starting time of keeping a certain value of the measuring point.

[0032] Compared with the prior art, the advantages of the application are that:

[0033] The method uses the mode of the relation table and the storage mode of the time sequence library, takes into account the advantages of both, and does not increase the difficulty of database design; the application can cache real-time data of a wind turbine generator set, and then realize PostgreSQL time sequence data storage, and the main advantage is that:

[0034] 1. Solve the problem of storing all variable measuring points of the wind turbine generator set at a second level.

[0035] For a long time, the operation data storage of main machine manufacturers (including enterprises with leading position in the industry) is only for key variables, and the granularity is rarely lower than 5 seconds. The scheme provided by the method is to store all measuring point data that can be collected during the operation of the unit, and to store and manage the data at a granularity of 1 record per second. Therefore, the integrity and availability of the data are greatly guaranteed, and the application range is greatly extended.

[0036] 2. Great benefits are brought to wind power manufacturing, operation and design.

[0037] Data generates value. In recent years, with the interest in big data, various analysis applications for data have flourished. Since the unit data recording granularity reaches the second level, and all the collected measuring point data are saved, it is convenient to realize fault diagnosis and health management, unit performance post-evaluation and performance improvement, intelligent operation and maintenance, lean production and digital twin research. And it can provide valuable experience data materials for unit micro-siting and selection, mechanical and electrical design simulation, blade aerodynamic model, load calculation. BRIEF DESCRIPTION OF DRAWINGS

[0038] Figure 1 The figure is a schematic diagram of the database structure of the application. DETAILED DESCRIPTION

[0039] The application will be further described below in combination with the drawings and specific embodiments of the specification.

[0040] The embodiment of the application discloses a wind power time sequence data storage management method based on a PostgreSQL relational database, in the PostgreSQL relational database, operation data related to a wind farm are organized together to form a wind farm schema; wherein, the schema of different wind farms is replaced by a corresponding wind farm ID, that is, a special schema of a wind farm, and a second-level data table, a minute-level data table, an on-off quantity change data table and a digital quantity change data table corresponding to a wind turbine are replaced by a corresponding wind turbine ID, that is, a corresponding wind turbine historical operation data storage table; for management of wind turbine historical operation data, a corresponding PG storage process is written to complete the following management work:

[0041] 1) creation of a historical data table: defining a PLPGSQL function, calling a corresponding PLPGSQL function to generate a second-level historical data table, a minute-level historical data table, an on-off quantity change data table and a digital quantity change data table of a wind turbine in a wind farm, and generating a super table of TimescaleDB;

[0042] 2) adding a measuring point in the wind turbine historical data table: defining a PLPGSQL function, calling a corresponding PLPGSQL function, checking second-level historical data measuring points and minute-level historical data measuring points of all wind turbines in a wind farm specified by a pid and identified by a tid according to corresponding configuration; if a new analog quantity measuring point is found, the corresponding data table is added;

[0043] 3) data caching: a one-minute caching mode is used for second-level data, temporary files are written into a cache folder first, and then batch submission is performed every minute, so as to reduce time occupied by database write locks and improve database operation efficiency;

[0044] 4) regular rolling deletion: second-level data in the database that is more than a preset time is deleted, and the drop_chunks function is used for deletion.

[0045] The application is an implementation method for solving storage management and rapid retrieval of operation data generated in the operation process of a wind power monitoring system by using an open source database PostgreSQL (PG).

[0046] In order to better understand the above technical solution, the above technical solution will be described in detail in combination with the description of the drawings and the specific embodiments.

[0047] First, the related content of TimescaleDB time sequence database is described:

[0048] The characteristics of time series data include: (1) mostly inserts, no update required; (2) data mostly have time attributes. TimescaleDB is a time series database based on PostgreSQL database, which extends PostgreSQL in the form of plug-in.

[0049] It has data automatically sharded by time and space (TimescaleDB's shard is called chunk). It has the following characteristics:

[0050] 1. As the data continues to increase, the data of the time series data table is partitioned and stored, ensuring that the index of each partition is maintained at a small scale, thereby maintaining the write performance.

[0051] 2. Based on the query scenario of time series data, the timestamp of time series data is used as the partition key during automatic partitioning, so that the required data partition can be quickly located during query, ensuring query performance.

[0052] 3. The partitioning process is transparent to users, achieving automatic expansion.

[0053] Adding a time series data table to a PG database with TimescaleDB extension is also very simple. First, use the standard sql statement CREATE TABLE<table_name>(

[0054] dt TIMESTAMP WITHOUT TIME ZONE NOT NULL,...

[0055] PRIMARY KEY(dt));

[0056] Create a table, where dt is the timestamp column and serves as the primary key. "..." represents the omitted data fields.

[0057] Then call the stored procedure

[0058] SELECT create_hypertable(‘<table_name>’,‘dt’);

[0059] Create a hypertable based on the specified table (such as<table_name> in the example above) and the timestamp field (such as dt in the example above) as the partition basis. A time series data table will be created. If you need to manage the partitions of the hypertable more, such as setting the partition duration, you can refer to the TimescaleDB manual.

[0060] For example Figure 1As shown, the wind power time series data storage management method based on the PostgreSQL relational database of the embodiment of the application has the specific process as follows

[0061] First, the mode design is performed, and the corresponding wind farm mode is a container in which the operation data of a wind farm is organized together in the database, such as the wind farm with the ID "NuoMuHong", and the wind farm mode name uses the wind farm ID (PID) "NuoMuHong", and the following includes the wind turbine and the second-level data, the minute data, the on-off quantity and digital quantity change data, and the KPI result data, the EMS (energy management system) historical data and the RunLog (running log data) related to the whole wind farm, and the structure is as shown in the following table. Figure 1

[0062] Among them, the schema of different wind farms is replaced by the corresponding PID (wind farm ID) to be a special schema of a wind farm, and the second-level, minute-level and on-off quantity and digital quantity change data storage table of the wind turbine is replaced by the corresponding WID (wind turbine ID) to be the historical operation data storage table of the corresponding wind turbine. The number of the wind turbine historical data storage table is associated with the actual number of wind turbines of the wind farm, and can be increased or decreased.

[0063] The advantages of establishing the special schema of the wind farm and separately storing each wind turbine are as follows: clear structure and convenient management. If the correlation query and comparison analysis are needed, the connection query can be conveniently used through the time stamp field dt and the SQL statement.

[0064] The corresponding data storage is as follows:

[0065] For the second-level data storage, the wind turbine operation data is selected to be sampled once per second, that is, one row of data is generated per second. The data structure is as follows:

[0066] (dt, flag, tag1, tag2,..., tagn)

[0067] Among them, dt represents the time stamp of the corresponding record, flag represents the record flag, -1 when the wind turbine loses communication, and 1 when the wind turbine communication is normal, and tag1-tagn represents all the measurement point values of the wind turbine.

[0068] For the minute data storage, the wind turbine minute data record is 1 row per minute, and the data structure is as follows:

[0069] (dt, flag, tag1_avg, tag1_max_tag1_min, tag1_sd,...)

[0070] ​Wherein, dt represents the timestamp of the corresponding record, flag represents the record flag, ranging from 1 to 60, indicating that the current 1-minute record is aggregated by n (1 <= n <= 60) 1-second records, tag1_avg represents the average value of measuring point 1 in this 1-minute, tag1_max represents the maximum value of measuring point 1 in 1-minute, tag1_min represents the minimum value of measuring point 1 in 1-minute, tag1_sd represents the standard deviation of measuring point 1 in 1-minute, and all analog measuring point data are sequentially identified by the sample size of flag.

[0071] For switch and digital quantity storage, the switch and digital quantity change value data structure of the fan is as follows:

[0072] (dt, tagid, value)

[0073] Wherein, dt also represents the timestamp, tagid represents the id of the measuring point, and value represents the measuring point value. Thus, one record indicates that at the time of dt, the value of measuring point tag becomes value, and thus the value of tag before this time is the value recorded last time, and the timestamps of the two records are the starting time of maintaining a certain value of the measuring point.

[0074] For the management of fan operation history data, the corresponding PG storage process (function) is written according to the needs to complete the following management work.

[0075] 1) Creation of historical data table

[0076] Define the PLPGSQL function create_secdata_table(pid TEXT, wid TEXT, tid INTEGER) RETURNS VOID; calling the function can generate the second-level historical data table of a certain fan in the wind farm, wherein pid represents the wind farm id, such as "NuoMuHong" before, wid represents the fan id, such as "NuoMuHong001" before, and tid represents the model, the function will determine the fan full measuring point name and type according to the parameter, then use these parameters to create the second-level historical data table, and generate the TimescaleDB super table.

[0077] Define the PLPGSQL function create_mindata_table(pid TEXT, wid TEXT, tid INTEGER) RETURNS VOID; calling this function can generate the minute-level historical data table of a certain wind turbine in the wind farm, where pid represents the wind farm id, such as "NuoMuHong" before, wid represents the wind turbine id, such as "NuoMuHong001" before, and tid represents the model. In the function, the full analog quantity measurement point name of the corresponding wind turbine will be determined according to this parameter, and then these parameters are used to create the minute-level historical data table and generate the TimescaleDB super table.

[0078] Define the PLPGSQL function create_digdata_table(pid TEXT, wid TEXT) RETURNS VOID; calling this function can generate the on-off quantity and digital quantity change value storage data table of a certain wind turbine in the wind farm, where pid represents the wind farm id, such as "NuoMuHong" before, and wid represents the wind turbine id, such as "NuoMuHong001" before. This table is relatively simple, only with three columns. After creation, a timescaled super table will also be generated.

[0079] 2) Add measurement points in the wind turbine historical data table

[0080] Define the PLPGSQL function check_sec_table(pid TEXT, tid INTEGER) RETURNS VOID; calling this function will check all wind turbines in the wind farm specified by pid according to the corresponding configuration, and the model is identified by tid. If a new measurement point is found, add the measurement point field in all second-level data tables of the corresponding model;

[0081] Define the PLPGSQL function check_min_table(pid TEXT, tid INTEGER) RETURNS VOID; calling this function will check all wind turbines in the wind farm specified by pid according to the corresponding configuration, and the model is identified by tid. If a new analog quantity measurement point is found, add the average value, maximum value, minimum value and standard deviation fields of the measurement point in all minute-level data tables of the corresponding model;

[0082] 3) Data caching

[0083] In order to ensure that the data can be submitted, and to minimize the database write occupancy time, as much time as possible is left for data query and other business access to historical data, the method uses 1 minute cache for second-level data, writes into a temporary file in the cache folder, and then submits in batches every minute, thereby reducing the time of database write lock occupancy, and improving the efficiency of the database.

[0084] 4) Regularly rolling deletion

[0085] Since the storage space of the wind farm is limited, combined with the regular backup mechanism, the second-level data in the database that is relatively old, such as second-level data exceeding 2 years, is deleted, and since the historical data is a TimescaleDB hyper table, the deletion uses the drop_chunks function, such as

[0086] SELECT drop_chunks(INTERVAL'18months','<table_name>');

[0087] Indicates that the data of the last 18 months of the hyper table table_name is retained. This deletion operation does not need to delete data line by line, and the processing speed is extremely fast. Compared with MicroSoft SQL Server, the same deletion operation cannot be completed on SQL Server in 24 hours, while the processing time of TimescaleDB is counted in minutes. Due to the difference in the richness of system computing resources, the fastest is less than 1 minute, and the slowest can be completed within a few minutes.

[0088] When running the historical data access, since the hyper table that saves the unit historical data is essentially a relational table, the access to these data is no different from the ordinary data table, and there is no problem in using standard SQL statements for query, connection and aggregation.

[0089] The above method is adapted to the domestic database HighGo database: HighGo database is a domestic database product based on open source data PostgreSQL (PG), which further improves safety and stability, introduces national sm3 encryption algorithm, and makes corresponding reinforcement for national key departments and power grid safety requirements. HighGo database has adapted TimescaleDB and the corresponding monitoring software product, so that HighGo database also has real-time data expansion capability and all the functions of the above invention method.

[0090] The wind power time series data storage management method based on the PostgreSQL relational database of the application mainly realizes the following breakthroughs:

[0091] 1. The full amount of fan operation data is stored, and the previous data storage is only selected for highly concerned variables, which is the general practice in the industry.

[0092] 2. The time granularity of fan operation data reaches seconds: there are many manufacturers in the industry who claim to store data at the second level, and the general granularity is greater than 5 seconds. There are not many whole machine manufacturers that can truly achieve full-variable second-level storage.

[0093] 3. The design of the relational database is used, which does not increase the difficulty of database design.

[0094] There are two storage methods for the previous fan operation history data, one is file storage, and the other is stored in a relational database. The traditional relational database has low query processing capacity for mass time series data, and the calculation efficiency is low. The method uses the mode of the relational table and the storage method of the time series library, which takes into account the advantages of both, and does not increase the difficulty of database design.

[0095] The application can cache real-time data of wind turbines, and then realize PostgreSQL time series data storage, the main advantages are:

[0096] 1. Solve the problem of second-level storage of all variable measuring points of wind turbines.

[0097] For a long time, the operation data storage of major whole machine manufacturers (including industry-leading enterprises) has only targeted key variables, and the granularity is rarely less than 5 seconds. The scheme provided by the method is to store all measuring point data that can be collected during the operation of the unit, and to store and manage them according to the granularity of 1 record per second. Therefore, the integrity and availability of the data are greatly guaranteed, and the application range is greatly extended.

[0098] 2. It brings great benefits to wind power manufacturing, operation and design.

[0099] Data generates value. In recent years, with the interest in big data, various data analysis applications have flourished. Because the unit data recording granularity reaches seconds, and all the collected measuring point data can be saved, it is convenient to realize fault diagnosis and health management, unit performance post-evaluation and performance improvement, intelligent operation and maintenance, lean production and digital twin research. And it can provide valuable experience data materials for unit micro-siting and type selection, mechanical and electrical design simulation, blade aerodynamic model, load calculation.

[0100] Noun explanation of related technical terms:

[0101] PostgreSQL (PG): PostgreSQL is a feature-complete, free software, object-relational database management system (ORDBMS) based on POSTGRES, version 4.2, developed at the University of California, Berkeley.

[0102] TimescaleDB: TimescaleDB (TSDB) is a PostgreSQL extension that adds time-series-based performance and data management optimizations to regular PostgreSQL (PG) databases. While there are many scalable time-series solutions, the best part of TimescaleDB is the time-series awareness outside of traditional SQL databases.

[0103] Hypertable: TimescaleDB's definition of a hypertable, which is a table that manages and maintains time-series data in chunks.

[0104] HighGo DB: A Chinese-made database product developed by HighGo based on PG.

[0105] PID: Plant ID, wind farm identification, defined in the wind power management system for the identification of wind farms.

[0106] WID: Windturbine ID, wind turbine identification, defined in the wind power management system for the identification of wind turbines.

[0107] TID: Windturbine type ID, wind turbine type identification, defined in the wind power management system for the identification of wind turbine types.

[0108] TDengine: TDengine, a time-series spatial big data engine developed by TDengine, has the technical characteristics of high performance, high reliability, scalability, zero management, and simplicity.

[0109] The above are only preferred embodiments of the present application, and the protection scope of the present application is not limited to the above embodiments. Any technical solutions falling within the scope of the present application should be considered within the protection scope of the present application. It should be noted that for ordinary technical personnel in the technical field, some improvements and refinements without departing from the principles of the present application should be considered within the protection scope of the present application.

Claims

1. A wind power time series data storage management method based on a PostgreSQL relational database, characterized in that, In the PostgreSQL relational database, the operation data related to a wind farm is organized together to form a wind farm schema; wherein the schema of different wind farms is replaced by the corresponding wind farm ID, that is, a dedicated schema for a wind farm, and the storage tables of the second-level data, minute-level data, switching value and digital value change data corresponding to the wind turbine are replaced by the corresponding wind turbine ID, that is, the historical operation data storage table of the corresponding wind turbine; for the management of the historical operation data of the wind turbine, the corresponding PG storage process is written, the historical data table is created, and the measuring point is added in the wind turbine historical data table; wherein the process of creating the historical data table is: defining the PLPGSQL function, calling the corresponding PLPGSQL function to generate the second-level historical data table, the minute-level historical data table, the switching value and the digital value change value storage data table of a wind turbine in the wind farm, and generating the TimescaleDB super table; wherein the process of adding the measuring point in the wind turbine historical data table is: defining the PLPGSQL function, calling the corresponding PLPGSQL function, and checking the second-level historical data measuring point and the minute-level historical data measuring point of all wind turbines of the specified wind farm according to the corresponding configuration; if a new analog measuring point is found, it is added in the corresponding data table; The creation of the historical data table includes: defining the PLPGSQL function create_secdata_table(pid TEXT, wid TEXT, tid INTEGER) RETURNS VOID; calling the function to generate the second-level historical data table of a wind turbine in the wind farm, wherein pid represents the wind farm id, wid represents the wind turbine id, and tid represents the model; in the function, the names and types of all measuring points of the wind turbine are determined according to the parameters, and then the second-level historical data table is created using these parameters, and the TimescaleDB super table is generated; defining the PLPGSQL function create_mindata_table(pid TEXT, wid TEXT, tid INTEGER) RETURNS VOID; calling the function to generate the minute-level historical data table of a wind turbine in the wind farm, wherein pid represents the wind farm id, wid represents the wind turbine id, and tid represents the model; in the function, the names of all analog measuring points of the corresponding wind turbine are determined according to the parameters, and then the minute-level historical data table is created using these parameters, and the TimescaleDB super table is generated; defining the PLPGSQL function create_digdata_table(pid TEXT, wid TEXT) RETURNS VOID; calling the function to generate the switching value and digital value change value storage data table of a wind turbine in the wind farm, wherein pid represents the wind farm id, and wid represents the wind turbine id; after the creation, the timescaled super table is generated.

2. The PostgreSQL relational database-based wind power time series data storage management method according to claim 1, characterized in that, Also includes data cache: using a minute cache for second-level data, first write to cache folder as temporary file, then batch submission every minute, to reduce the time of database write lock occupation, to improve the efficiency of database operation. 3.The PostgreSQL relational database-based wind power time series data storage management method of claim 1, wherein, Also includes periodic rolling deletion: delete the second-level data in the database that exceeds the preset time, delete using drop_chunks function. 4.The PostgreSQL relational database-based wind power time series data storage management method of claim 1, wherein, The process of adding measuring points to the second-level historical data table is as follows: Define the PLPGSQL function check_sec_table(pid TEXT, tid INTEGER) RETURNS VOID; call the function, according to the corresponding configuration, check all wind turbine second-level historical data measuring points in the wind farm specified by pid and identified by tid; if new measuring points are found, add measuring point fields to all second-level data tables of the corresponding model.

5. The PostgreSQL relational database-based wind power time series data storage management method according to claim 4, characterized in that, The process of adding measuring points to the minute-level historical data table is as follows: Define the PLPGSQL function check_min_table(pid TEXT, tid INTEGER) RETURNS VOID; call the function, according to the corresponding configuration, check all wind turbine minute-level historical data measuring points in the wind farm specified by pid and identified by tid; if new analog measuring points are found, add the average value, maximum value, minimum value, and standard deviation fields of the measuring point to all minute-level data tables of the corresponding model.

6. The PostgreSQL relational database based wind power time series data storage management method according to claim 3, characterized in that, The drop_chunks function is: SELECT drop_chunks(INTERVAL 'n months', '<table_name>').

7. The PostgreSQL relational database based wind power time series data storage management method according to claim 1 or 2 or 3, characterized in that, Among them, the second-level data storage: the wind turbine running data selects 1 second sampling once, that is, 1 second generates a row of data; the data structure is as follows: (dt, flag, tag1, tag2,..., tagn) Among them, dt represents the timestamp of the corresponding record, flag represents the record flag, which is -1 when the wind turbine loses communication and 1 when the wind turbine communication is normal, and tag1-tagn represents all measuring point values of the wind turbine.

8. The PostgreSQL relational database based wind power time series data storage management method according to claim 1 or 2 or 3, characterized in that, Among them, the minute data storage: the wind turbine minute data record 1 minute 1 row, the data structure is as follows: (dt, flag, tag1_avg, tag1_max, tag1_min, tag1_sd,...) Among them, dt represents the timestamp of the corresponding record, flag represents the record flag, which is 1-60, indicating that the current 1-minute record is aggregated from n 1-second records, tag1_avg represents the average value of measuring point 1 in this 1 minute, tag1_max represents the maximum value of measuring point 1 in 1 minute, tag1_min represents the minimum value of measuring point 1 in 1 minute, tag1_sd represents the standard deviation of measuring point 1 in 1 minute, and all analog measuring point data are in turn.

9. The PostgreSQL relational database based wind power time series data storage management method according to claim 1 or 2 or 3, characterized in that, Among them, the on-off quantity and digital quantity storage is as follows: The data structure of the on-off quantity and digital quantity change value of the wind turbine is as follows: (dt, tagid, value) Wherein dt also represents a time stamp, tagid represents the id of the measuring point, value represents the measuring point value; it indicates that at the time of dt, the value of the measuring point tag becomes value, and before that, the value of tag is the value recorded last time, and the time stamps of the two records are the starting time of maintaining a certain value of the measuring point.

Images