CN114926099A

CN114926099A - Automatic defect filling method for power grid dispatching EMS data

Info

Publication number: CN114926099A
Application number: CN202210775316.2A
Authority: CN
Inventors: 徐尧强; 舒乔晔; 周瑾; 钱晨; 李丽; 吴超; 卫哲鸣
Original assignee: Shanghai Chenhua Network Technology Services Co ltd; East China Branch Of State Grid Corp ltd
Current assignee: Shanghai Chenhua Network Technology Services Co ltd; East China Branch Of State Grid Corp ltd
Priority date: 2022-07-01
Filing date: 2022-07-01
Publication date: 2022-08-19

Abstract

The invention provides an automatic defect filling method for power grid dispatching EMS data, which comprises the following steps: reading a file from a source by using an ETL scheduler and calling an ETL tool to store power grid scheduling EMS data into a target data table of a database; writing a lost point file checking program, checking whether power grid dispatching EMS data in a target data table have lost points, and if so, storing a statistical result of a lost point file name corresponding to the lost point data in a lost point statistical table; writing a missing point file filling program, acquiring a missing point file name from a missing point statistical table, feeding the missing point file name back to the ETL scheduler, reading the missing point file name by the ETL scheduler, re-reading the missing point file from a source and calling an ETL tool to fill the missing point file into a target data table. The invention can inquire the loss point file of the power grid dispatching EMS data in the database and automatically fill the loss point file in the database, thereby improving the integrity and timeliness of the data.

Description

Automatic defect filling method for power grid dispatching EMS data

Technical Field

The invention relates to the field of enterprise data quality, in particular to an automatic defect filling method for power grid dispatching EMS data.

Background

The power grid enterprise is a data-intensive enterprise, the power grid dispatching is a core department of the power grid enterprise, an EMS system (energy management system) used in daily life is a set of production system with very important power grid dispatching, and the system data reflects the operation state of the power grid in real time by taking the second level as a unit. In order to store EMS real-time data for subsequent analysis and application, the control center takes 1 minute as a time section, reads real-time power grid data from an EMS real-time base and throws out the real-time power grid data into a file, and integrates the file data into a downstream big data center. The EMS system throws more than 20 files per minute, and throws more than 3 ten thousand files cumulatively all day. The EMS data has very important application to analyzing the operation state of the power grid, real-time early warning and the like, and is one of very important data assets of enterprises.

When storing EMS data into a database of a large data center, the first problem is data integrity, and when any software and hardware related to a data access process such as a network, a disk, a database, an ETL tool and the like fails, a file storage failure may be caused, so that data content is incomplete. Because the EMS files are more than 20 per minute and are thrown out within 7 × 24 hours, although the probability of single file warehousing failure is relatively low, the phenomenon that the file warehousing (storing in a database) failure often occurs due to multiplication of the number of the files. In addition to the problem of data integrity, another file is that the file is not put in storage in time, when the data storage is normal, the file can be thrown out from an EMS to the final data storage within 2 minutes generally, and once a system fails, the data storage is delayed for a long time or even is not put in storage, if data loss and data supplement are found manually, the data is finally put in storage and a period of time is passed before the file is thrown out, and the timeliness of the data is difficult to guarantee.

Disclosure of Invention

The invention aims to provide an automatic supplementing method for power grid dispatching EMS data, which can inquire a lost point file of the power grid dispatching EMS data in a database target table and automatically supplement the lost point file into a database.

In order to achieve the above object, the present invention provides an automatic defect-filling method for power grid scheduling EMS data, comprising:

reading a file from a source by using an ETL scheduler and calling an ETL tool to store power grid scheduling EMS data into a target data table of a database;

writing a lost point file inspection program, inspecting whether power grid dispatching EMS data in the target data table have lost points, and if so, storing a statistical result of a lost point file name corresponding to the lost point data in a lost point statistical table;

writing a missing point file filling program, acquiring a missing point file name from the missing point statistical table, feeding the missing point file name back to the ETL scheduler, reading the missing point file name by the ETL scheduler, re-reading the missing point file from a source and calling an ETL tool to fill the missing point file into the target data table.

Optionally, in the automatic gap filling method, the method for storing the power grid scheduling EMS data into the target data table of the database by using the ETL tool includes: and uploading the EMS data file for power grid dispatching to an SFTP server.

Optionally, in the automatic gap filling method, Informatica is used as the ETL tool.

Optionally, in the automatic gap filling method, the target data table includes: the equipment ID of the power grid scheduling EMS data, the time of the power grid scheduling EMS data, the value of the power grid scheduling EMS data and the timestamp of the time when the power grid scheduling EMS data are stored in the database.

Optionally, in the automatic make-up method, the time when the EMS data are scheduled by the power grid has regularity, and the time difference between every two adjacent EMS data is the same.

Optionally, in the automatic defect-filling method, the method for checking whether there is a point loss in the power grid scheduling EMS data in the target data table includes:

whether the time of the power grid scheduling EMS data is lost or not is inquired; and

and if so, finding out the power grid scheduling EMS data corresponding to the lost time.

Optionally, in the automatic gap filling method, the content of the missing point statistical table includes: a missing point file type, a missing point file name, and a timestamp.

Optionally, in the automatic gap filling method, the ETL scheduler reads multiple types of files from a source, where each type includes a gap filling interface file.

Optionally, in the automatic padding method, the method for the ETL scheduler to pad the missing point file in the target data table according to the missing point statistics table includes:

and transmitting the name of the lost file to the vacancy filling interface file, reading the file from a source by the ETL scheduler according to the name of the lost file in the interface file, and filling the data of the lost file in the vacancy filling interface file into the target data table by calling the ETL tool.

The invention also provides an automatic defect filling method for power grid dispatching EMS data, which sets Windows plan tasks, and automatically calls the lost point file inspection program and the lost point file defect filling program after every certain period of time so as to realize the lost point file detection function and the lost point file defect filling function.

According to the automatic supplementing method for the power grid dispatching EMS data, the lost point file of the power grid dispatching EMS data in the database can be automatically inquired, and the lost point file is automatically supplemented into the database, so that the integrity and timeliness of the data are improved.

Drawings

Fig. 1 is a flowchart of an automatic defect-filling method for power grid scheduling EMS data according to an embodiment of the present invention;

FIG. 2 is a schematic diagram of missing point detection in an embodiment of the present invention;

FIG. 3 is a flow chart of a lost point filename write padding interface file according to an embodiment of the present invention;

fig. 4 is a flowchart of an automatic defect-filling method for power grid scheduling EMS data according to an embodiment of the present invention.

Detailed Description

The following describes in more detail embodiments of the present invention with reference to the schematic drawings. The advantages and features of the present invention will become more apparent from the following description. It is to be noted that the drawings are in a very simplified form and are not to precise scale, which is merely for the purpose of facilitating and distinctly claiming the embodiments of the present invention.

In the following, the terms "first," "second," and the like are used for distinguishing between similar elements and not necessarily for describing a particular sequential or chronological order. It is to be understood that the terms so used are interchangeable under appropriate circumstances. Similarly, if the method described herein comprises a series of steps, the order in which these steps are presented herein is not necessarily the only order in which these steps may be performed, and some of the described steps may be omitted and/or some other steps not described herein may be added to the method.

Referring to fig. 1, the present invention provides an automatic defect-filling method for EMS data in power grid dispatching, including:

s1: reading a file from a source by using an ETL scheduler and calling an ETL tool to store power grid scheduling EMS data into a target data table of a database;

s2: writing a lost point file inspection program, inspecting whether lost points exist in the power grid dispatching EMS data in the target data table, if so, storing a statistical result of a lost point file name corresponding to the lost point data in a lost point statistical table (if not, writing the lost point statistical table or writing the lost point statistical table to be 'empty', and then continuing the inspection of the next data);

s3: writing a missing point file filling program, acquiring a missing point file name from the missing point statistical table, feeding the missing point file name back to the ETL scheduler, reading the missing point file name by the ETL scheduler, re-reading the missing point file from a source and calling an ETL tool to fill the missing point file into the target data table.

In the embodiment of the invention, the EMS data for dispatching the power grid are generated by an EMS system. The big data center autonomously develops an ETL scheduler, downloads a newly-added EMS file from a source SFTP by using the ETL scheduler, and calls an ETL tool to perform data warehousing (storing in a database). In addition, the ETL scheduler also provides a filling-up interface file buque.txt, in step S3, when the missing point file filling-up program performs missing point file filling-up, the missing point file is written into the filling-up interface file buque.txt of the ETL scheduler, and each time the ETL scheduler reads a file on the source SFTP, the name of the file to be filled up in the interface file buque.txt is read first, and then the file to be filled up is downloaded from the SFTP and the ETL program is called to be stored in the target data table of the database, thereby realizing filling-up. In the embodiment of the invention, an ETL program is developed by using Informatica (a commercial ETL tool, the whole name of which is Informatica PowerCenter, Informatica for short) as an ETL tool, and the specific function of reading EMS files and storing the EMS files into a database is realized.

Further, the method for storing the power grid scheduling EMS into the target data table of the database by using the ETL tool comprises the following steps: the EMS system generates data files and puts the files on an SFTP server, the EMS system generates more than 20 files every 1 minute, and the SFTP is the source for acquiring the EMS files by a large data center. The function of the ETL scheduler is realized by reading EMS data files from a source SFTP server and calling an ETL tool to execute EMS data warehousing, in addition, the ETL scheduler also has a timing function, the embodiment of the invention defines the ETL scheduler as a warehousing process executed every 10 seconds, namely, the ETL scheduler can automatically check the SFTP server and the gap filling interface file buque.

Preferably, the target data table includes: the equipment ID of the power grid scheduling EMS data, the time of the power grid scheduling EMS data, the value of the power grid scheduling EMS data and the timestamp of the power grid scheduling EMS data. Specifically, as shown in table 1, table 1 exemplifies ac line terminals.

TABLE 1

Column name	Chinese notes	Examples of the invention
			RESOURCEID	Device ID	1191006770788
T_ID	Time	2021-5-20 14:35
			MW	Active power	-872.26
MX	Reactive power	42.73
			LASTUPDATE	Time stamp, data entry time	2021-5-20 14:36:14

As can be seen from table 1, the operation data mainly includes 4 types of contents, a device ID, a time, a value, and a timestamp, where the device ID is: the big data center provides equipment ID for the end of the AC line; the time is as follows: the time when this data is provided by the EMS system, i.e., the time when the measurement value occurred; the active and reactive power are: a measured value of the device; the time stamp is: time when data is saved into the target data table;

further, the time of the power grid scheduling EMS data has regularity, and the time difference between every two adjacent power grids is the same. The EMS data for power grid dispatching selected by the embodiment of the invention are section data with an interval of 1 minute. In Table 1, the data is 1 minute in cross-section, so the field "T _ ID" (time) is a regular 1 minute interval, such as "2021-5-2014: 33, 2021-5-2014: 34, 2021-5-2014: 35", i.e., having a value every minute, which is derived from the EMS system provided filename, such as "2021-5-2014: 35", derived from the EMS data file "Aclinende20210535. txt". Therefore, by checking the missing condition of the time of the "T _ ID" field in the target data table, it can be found which files can not be put in storage.

Further, the method for checking whether the power grid scheduling EMS data in the target data table has a missing point comprises the following steps: whether the time of the power grid scheduling EMS data is lost or not is inquired; and if so, finding out the power grid scheduling EMS data corresponding to the lost time. Specifically, referring to fig. 2, a missing point detection program is written, which generates a series of standard times spaced 1 minute apart, compares the standard times with the times (T _ ID) in the target table data table, determines the missing time as the missing point time if the missing time in the target table data table is found relative to the standard time, and finally translates the missing point time into a file name. For example, if the time 2021-5-2014: 34 is found to be missing, the data at the ac line end of the grid scheduling EMS corresponding to the time 2021-5-2014: 34 is considered as a missing point, and needs to be converted into a corresponding file name to find a corresponding missing point file, where the conversion rule is as follows: and (5) Aclinend year, month and day, txt, so that the name of the converted point-missing file is Aclinend202105201434. txt, and the Aclinend202105201434. txt is the point-missing file.

Further, the contents of the lost point statistical table include: a missing point file type, a missing point file name, and a timestamp. The missing point statistics are shown in table 2.

TABLE 2

Column name	Chinese notes	Examples of the invention
			FILE_TYPE	File type	D5000_MEAS_ACLINEEND
T_ID	Missing point file time	2021-5-20 14:34
			FILENAME	Missing point file name	Aclineend202105201434.txt
LASTUPDATE	Time stamp, data entry time	2021-5-20 15:01:13

The types of the lost point files are used for distinguishing the types of the lost point files, and when the loss is automatically filled, the lost point files of different types are respectively written into the filling interface files corresponding to the ETL scheduler according to the content D5000_ MEAS _ ACLINEEND of the example; the point missing file time is the time of data of the point missing file; the name of the lost point file is the name of the lost point file, namely the name of the file needing to be supplemented; the timestamp is the time of the lost point inspection statistical result entering the warehouse, so that operation and maintenance personnel can conveniently know the time of the occurrence of the lost information.

Further, the ETL scheduler reads multiple types of files from the source, each type including a padding interface file. For example, there is more than one gap filling interface file buque.txt in the "ETL scheduler", and each device type data thrown out by the EMS system has a corresponding gap filling interface file buque.txt, so that the loss point files of different devices need to be written into the corresponding gap filling interface files. Therefore, in order to realize the configurability and easy expansion of the missing point supplement, the embodiment of the present invention uses the missing point supplement configuration table, and defines information such as the location of the "supplement interface file buque.txt" of different devices, and the missing point supplement configuration table is shown in table 3:

TABLE 3

Wherein, FILE _ TYPE is that each TYPE of FILE corresponds to one EMS device TYPE, for example, "D5000 _ MEAS _ ACLINEEND" represents a line end device in the EMS system, and the content of this row example can associate a "loss filling configuration table" (table 3) with a "loss statistics table" (table 2); FILE _ LOC _ NAME is the position of the 'gap filling interface FILE buque.txt', and the NAME of the lost point FILE is written into the buque.txt FILE; BQ _ START _ DAYS and BQ _ END _ DAYS are filling START time and filling END time respectively, and only missing point files in the START time range and the END time range are filled, so that the efficiency is improved; the IS _ BQ IS a switch for judging whether to make up the vacancy, so that whether to make up the vacancy automatically IS conveniently controlled; the REMARK is a supplementary description of the current supplementing configuration information, such as that "EMS system line end supplementing configuration information" represents a supplementary description of an example of the configuration information, which is the configuration information about the automatic supplementing of the EMS line end.

Further, referring to fig. 3, the method for feeding back the lost point statistical table to the ETL scheduler and feeding back the file name of the lost point in the lost point statistical table to the ETL scheduler according to the lost point configuration information includes: firstly, acquiring configuration information, specifically, reading all configuration information in a missing point defect filling configuration table (table 3DDJK _ EMS _ BQ _ CONF), requiring IS _ BQ to be 'yes', and putting the configuration information into a list; then, judging whether the configuration information is not read completely; if so, acquiring single TYPE configuration information, specifically acquiring information of a FILE _ TYPE, FILE _ LOC _ NAME, BQ _ START _ DAYS and BQ _ END _ DAYS of the current TYPE; then, obtaining the FILE name of the LOST point, specifically, reading the FILE name which meets the condition in the LOST point statistical table (table 2DDJK _ EMS _ LOSTFILES) according to the information of FILE _ TYPE, BQ _ START _ DAYS and BQ _ END _ DAYS, and writing the FILE name into an array LOST _ FILES; then, judging whether the array LOST _ FILES has data or not; if yes, writing the gap filling interface FILE, specifically, writing the FILE NAME in the array LOST _ FILES into the buque.txt FILE specified by FILE _ LOC _ NAME.

The invention also provides an automatic defect filling method for power grid dispatching EMS data, which sets Windows planning tasks, and automatically calls the lost point file inspection program and the lost point file defect filling program after a certain period of time to realize the lost point file detection function and the lost point file defect filling function. Specifically, referring to fig. 4, in order to realize the automatic execution of steps S2 to S3, the present invention uses the "planning task" of the Windows operating system. The embodiment of the invention firstly uses an Oracle storage process to realize the function of a lost-point file checking program, secondly uses Python to realize a lost-point file supplementing program, and then uses a bat file' ems _ buque. In order to realize automatic calling, the invention defines a plan in a task plan of an operating system, and calls a file ems _ buque. Once the lost point file checking program, the lost point file supplementing program and the calling program are written, the time range adjustment of lost point supplementing of different types of EMS data and the control of whether to supplement the lost point can be finished only by modifying the lost point supplementing configuration table without modifying the programs. The invention sets the task plan to be executed once in 30 minutes, namely, the current latest data missing condition is fed back once every 30 minutes, and the 'warehousing' is executed once every 10 seconds, which is generally equivalent to that the completion work is automatically carried out within zero 10 seconds of 30 minutes.

The embodiment of the invention has good effects on improving the data quality and reducing the operation and maintenance workload. For data quality, data integrity and timeliness are mainly improved, the original one-time storage opportunity is improved to be N times by applying an automatic gap filling technology, 100% of data can be stored in a target database as long as a source throws out a correct data file, the problem of data point loss is solved, and the data integrity is improved; the automatic filling-up program can automatically set the filling-up time frequency, if the filling-up frequency is set to 30 minutes/time, the time frequency can meet the timeliness requirement of the current large data center user on data, if the user has higher requirement on timeliness in the future, the filling-up frequency can be set to 10 minutes/time and 5 minutes/time, and manual filling-up is difficult to realize such high-frequency filling-up, so that the timeliness of the data is also improved. In the ETL operation and maintenance of data, monotonous and repeated missing point supplement work is handed to an automatic supplement function, so that the conventional operation and maintenance workload of operation and maintenance personnel is greatly reduced.

In summary, in the automatic supplementing method for power grid scheduling EMS data provided in the embodiment of the present invention, the point missing file of the power grid scheduling EMS data in the database can be automatically queried, and the data of the point missing file can be automatically supplemented into the database, thereby improving the integrity and timeliness of the data.

The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention in any way. It will be understood by those skilled in the art that various changes, substitutions and alterations can be made herein without departing from the spirit and scope of the invention as defined by the appended claims.

Claims

1. An automatic defect supplementing method for power grid dispatching EMS data is characterized by comprising the following steps:

2. The automatic make-up method of claim 1, wherein the method of using the ETL tool to store grid schedule EMS data into the target data table of the database comprises: and uploading the EMS data file for power grid dispatching to an SFTP server.

3. The automatic gap filling method as claimed in claim 1, wherein Informatica is used as the ETL tool.

4. The automatic gap filling method of claim 1, wherein the target data table comprises: the equipment ID of the power grid scheduling EMS data, the time of the power grid scheduling EMS data, the value of the power grid scheduling EMS data and the timestamp of the time when the power grid scheduling EMS data are stored in the database.

5. The automatic replenishment method of claim 4, wherein the time at which the EMS data is scheduled by the power grid has regularity and the time difference between every two adjacent EMS data is the same.

6. The automatic make-up method of claim 5, wherein the method of checking whether grid dispatch EMS data within the target data table has a point of loss comprises:

7. The automatic gap filling method of claim 1, wherein the contents of the loss statistics table comprise: a missing point file type, a missing point file name, and a timestamp.

8. The automatic replenishment method of claim 1 wherein the ETL scheduler reads multiple types of files from a source, each type including a replenishment interface file.

9. The automatic padding method of claim 8 wherein the ETL scheduler padding the missing point file in the target data table according to the missing point statistics table comprises:

and transmitting the name of the lost point file to the defect supplement interface file, reading the file from a source by the ETL dispatcher according to the name of the lost point file in the interface file, and supplementing the data of the lost point file in the defect supplement interface file into the target data table by calling the ETL tool.

10. An automatic defect supplementing method for power grid dispatching EMS data is characterized in that a Windows planning task is set, and a missing point file checking program and a missing point file defect supplementing program are automatically called after a certain period of time, so that a missing point file detection function and a missing point file defect supplementing function are realized.