Data processing method and system based on ETL architecture

Abstract

The invention discloses a data processing method and system based on an ETL architecture, and the method comprises the steps: obtaining a multi-dimensional heterogeneous data source of a power system, carrying out the data extraction based on the multi-dimensional heterogeneous data source, and carrying out the preprocessing of the extracted data; and performing data conversion on the preprocessed data, and loading the converted data to a target system according to a preset mode to complete data processing. Extraction of various data sources is supported, data can be obtained from different data sources, the real-time performance and accuracy of the data are improved in the data reconstruction process, sub-second-level data delay can be achieved, a distributed computing framework is combined, the fault-tolerant mechanism of the system is enhanced, and the robustness of the whole system is improved.

Description

Technical Field

[0001] The technical field involved in the present invention particularly relates to a data processing method and system based on an ETL architecture. Background Art

[0002] The grid connection and access of new energy sources such as photovoltaics, wind energy, and hydropower, as well as new technologies and new methods widely used in power dispatching, all mark the technological progress of the power industry. The improvement of communication technology and the increase in complexity have led to a sharp increase in the amount of information in grid dispatching and operation and maintenance, posing higher requirements for the intelligent and informatization transformation of the power system. Existing technologies using knowledge graphs are used to construct and study the power system, extracting entity and relationship information from various data sources. However, constructing a knowledge graph is a dynamic process, and with the addition of new data, the graph needs to be continuously updated. Moreover, intelligent graphs have limited capabilities in processing unstructured data and real-time data streams and are difficult to efficiently adapt to the changing situations in the power industry. Existing edge computing is also applied in the power industry, but edge computing devices are usually deployed at the edge of the network, and their processing capabilities, storage, and network bandwidth may be limited, which may affect their performance when processing large-scale data sets. In addition, the distributed characteristics of edge devices may lead to the complexity of data integration and centralized management, especially when data coordination across devices and systems is required.

[0003] Since the data sources involved in the power industry are extensive, and the data may come from devices of different manufacturers, there are differences in specifications and formats. It is necessary to solve the problem of how to uniformly integrate these heterogeneous data sources and convert them into a unified format for further analysis and processing. The relationship between entities and data is constructed using a knowledge graph, and when new data is added, the update time is too long. There are problems such as data missing, inaccurate status, and incorrect timestamps in the power system, and the quality of some data is difficult to guarantee. Previous technologies such as cloud computing and edge computing will bring greater pressure to the network bandwidth and have low real-time performance. Summary of the Invention

[0004] The purpose of this part is to outline some aspects of the embodiments of the present invention and briefly introduce some preferred embodiments. Some simplifications or omissions may be made in this part, as well as in the abstract and title of the present application, to avoid obscuring the purpose of this part, the abstract, and the title, and such simplifications or omissions shall not be used to limit the scope of the present invention.

[0005] In view of the above existing problems, the present invention is proposed.

[0006] Therefore, the present invention provides a data processing method and system based on an ETL architecture to solve the problems mentioned in the background art.

[0007] To solve the above technical problems, the present invention provides the following technical solutions:

[0008] In a first aspect, an embodiment of the present invention provides a data processing method based on an ETL architecture, including: obtaining multi-dimensional heterogeneous data sources of a power system, performing data extraction based on the multi-dimensional heterogeneous data sources, and preprocessing the extracted data;

[0009] Performing data conversion on the preprocessed data, and loading the converted data into a target system in a predetermined manner to complete data processing.

[0010] As a preferred solution of the data processing method based on the ETL architecture of the present invention, wherein: the data extraction from the multi-dimensional heterogeneous data sources includes: performing data extraction from the multi-dimensional heterogeneous data sources with the goal of minimizing data processing time and cost.

[0011] As a preferred solution of the data processing method based on the ETL architecture of the present invention, wherein: the preprocessing of the extracted data includes: cleaning and validating the extracted data before entering the conversion stage after transmission, removing invalid data, and identifying and marking problem data.

[0012] As a preferred solution of the data processing method based on the ETL architecture of the present invention, wherein: the data conversion of the preprocessed data includes: deeply cleaning, converting, and sorting the preprocessed data to meet the requirements of the target system.

[0013] As a preferred solution of the data processing method based on the ETL architecture of the present invention, wherein: it further includes: dividing a task into multiple subtasks and performing parallel processing under a distributed computing framework.

[0014] As a preferred solution of the data processing method based on the ETL architecture of the present invention, wherein: it further includes: monitoring data quality indicators through data verification and cleaning rules, and performing quality monitoring on the data according to a preset alarm and automatic repair mechanism.

[0015] As a preferred solution of the data processing method based on the ETL architecture of the present invention, wherein: obtaining the multi-dimensional heterogeneous data sources of the power system includes at least: relational data sources, text files, and log files.

[0016] In a second aspect, the present invention provides a data processing system based on an ETL architecture, including:

[0017] A first processing module, configured to obtain multi-dimensional heterogeneous data sources of a power system, perform data extraction based on the multi-dimensional heterogeneous data sources, and preprocess the extracted data;

[0018] A second processing module, configured to perform data conversion on the preprocessed data, and load the converted data into a target system in a predetermined manner to complete data processing.

[0019] In a third aspect, the present invention provides an electronic device, including:

[0020] A memory and a processor;

[0021] The memory is used to store computer-executable instructions, and the processor is used to execute the computer-executable instructions. When the computer-executable instructions are executed by the processor, the steps of the data processing method based on the ETL architecture are implemented.

[0022] In a fourth aspect, the present invention provides a computer-readable storage medium, which stores computer-executable instructions. When the computer-executable instructions are executed by a processor, the steps of the data processing method based on the ETL architecture are implemented.

[0023] Compared with the prior art, the beneficial effects of the present invention are as follows: The present invention supports the extraction of multiple data sources, can obtain data from different data sources, and the data reconstruction process improves the timeliness and accuracy of the data, and can achieve sub-second data latency. Combined with a distributed computing framework, the fault tolerance mechanism of the system is enhanced, and the robustness of the overall system is improved. BRIEF DESCRIPTION OF THE DRAWINGS

[0024] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the following will briefly introduce the drawings required for the description of the embodiments. Obviously, the drawings in the following description are only some embodiments of the present invention. For those of ordinary skill in the art, without creative efforts, other drawings can be obtained based on these drawings. Among them:

[0025] Figure 1 is a flowchart of a data processing method and system based on the ETL architecture according to an embodiment of the present invention;

[0026] Figure 2 is a schematic diagram of the ETL process of a data processing method and system based on the ETL architecture according to an embodiment of the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0027] In order to make the above objects, features, and advantages of the present invention more obvious and understandable, the following will describe the specific embodiments of the present invention in detail with reference to the drawings of the specification. Obviously, the described embodiments are some embodiments of the present invention, rather than all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those of ordinary skill in the art without creative efforts shall fall within the protection scope of the present invention.

[0028] In the following description, many specific details are set forth in order to provide a thorough understanding of the present invention. However, the present invention may be practiced in other ways than those specifically described herein. Those skilled in the art can make similar extensions without departing from the spirit of the present invention. Therefore, the present invention is not limited by the specific embodiments disclosed below.

[0029] Secondly, as used herein, an "embodiment" or "embodiments" refers to specific features, structures, or characteristics that may be included in at least one implementation of the present invention. The phrase "in an embodiment" appearing in different places in this specification does not necessarily refer to the same embodiment, nor is it an embodiment that is separate or mutually exclusive of other embodiments.

[0030] The present invention will be described in detail with reference to the schematic diagrams. When describing the embodiments of the present invention in detail, for the sake of clarity, the cross-sectional views showing the device structure will be enlarged locally out of proportion, and the schematic diagrams are only examples and should not limit the scope of protection of the present invention. In addition, in actual production, three-dimensional spatial dimensions including length, width, and depth should be included.

[0031] Meanwhile, in the description of the present invention, it should be noted that the orientation or positional relationship indicated by terms such as "upper, lower, inner, and outer" is based on the orientation or positional relationship shown in the drawings. This is only for the convenience of describing the present invention and simplifying the description, and does not indicate or imply that the device or element referred to must have a specific orientation, be constructed and operated in a specific orientation, and thus should not be construed as a limitation of the present invention. In addition, the terms "first, second, or third" are only used for descriptive purposes and should not be construed as indicating or implying relative importance.

[0032] Unless otherwise clearly defined and limited in the present invention, the terms "mounted, connected, and coupled" should be understood in a broad sense. For example, it can be a fixed connection, a detachable connection, or an integral connection; it can also be a mechanical connection, an electrical connection, or a direct connection, or can be indirectly connected through an intermediate medium, or can be the communication inside two elements. For those of ordinary skill in the art, the specific meanings of the above terms in the present invention can be understood according to specific circumstances.

[0033] Embodiment 1

[0034] Referring to Figures 1 to 2 , which is an embodiment of the present invention, this embodiment provides a data processing method based on an ETL architecture. As shown in Figure 1 , it includes:

[0035] S100: Obtain multi-dimensional heterogeneous data sources of the power system, perform data extraction based on the multi-dimensional heterogeneous data sources, and preprocess the extracted data;

[0036] S200: Perform data conversion on the preprocessed data, and load the converted data into the target system in a predetermined manner to complete data processing.

[0037] It should be noted that when processing power grid data in the power industry, there are problems such as unified integration of heterogeneous data sources, data timeliness and accuracy, robustness, and network pressure. It is difficult to guarantee data quality. This application unifies and integrates data sources with different specifications and formats from devices of different manufacturers, and converts them into a unified format for further analysis and processing. By improving the problem of long knowledge graph update time, the timeliness and accuracy of data processing are improved. It solves data quality problems such as data loss, inaccurate status, and incorrect timestamps in the power system, and reduces the pressure on network bandwidth caused by cloud computing and edge computing, improving the timeliness of data processing.

[0038] It should be noted that in the ETL architecture, "E" represents Extract, which means extraction; "T" represents Transform, which means transformation; "L" represents Load, which means loading. ETL is the process of extracting, cleaning, transforming, and loading a large amount of heterogeneous data to generate a larger unified data warehouse. As Figure 2 shown, the ETL architecture proposed in the embodiments of this application can extract data from multiple data sources and support multiple data sources; the transformation layer of the ETL architecture is a data reconstruction process that cleans the data, such as filtering error values and intercepting strings, which can improve the timeliness and accuracy of the data; the ETL architecture combined with Flink or Spark technology can achieve sub-second data latency and improve the timeliness of the data. In the case of centralized power system data and overloaded loads, downtime of some nodes will cause problems such as data loss or reprocessing. Since the ETL architecture itself has a good fault tolerance mechanism, the robustness of the entire system is improved.

[0039] Among them, the extraction process is to collect different types of data from different systems in the power industry, including relational data sources, text data, etc. After extraction and collection, it will enter the transformation process. During the data transmission process, there may be feature losses. In the transformation stage, this part of invalid data needs to be removed to achieve data cleaning and reduce data redundancy caused by the same data expression meaning. At the same time, since the collected data is heterogeneous and the final structure of the required data is unified, the collected data needs to be processed. In the power industry, although the data is from different sources and may be heterogeneous, there is a certain connection between the data, and data association can be performed. These cleaned and associated data are sorted out to finally meet the standardized requirements. Finally, it is the loading process, that is, storing the converted data in the data warehouse to provide a data source for the subsequent operations of the enterprise.

[0040] It should also be noted that the ETL architecture ensures data consistency and quality through the data transformation and cleaning processes. It can handle data from multiple different sources, standardize this data, and convert it into the format required by power enterprises. In this way, enterprises can obtain accurate and consistent data for further analysis and decision-making. The ETL architecture processes data at a central location, which helps improve the efficiency and quality of data processing, and provides strong data integration capabilities to ensure data consistency and quality, which is particularly important for fields such as the power industry that require high data quality. At the same time, the ETL architecture can be extended according to the needs of the enterprise to handle the growing data volume, and due to its clear technical architecture, it is also easy to maintain and manage.

[0041] It should be noted that before designing the ETL architecture, it is first necessary to fully understand the business requirements and data analysis objectives. Clearly define the types of data, data volume, and processing time requirements to be extracted, transformed, and loaded, etc., in order to make accurate decisions for subsequent architecture design.

[0042] In an optional embodiment, a layered design approach can be adopted for the layered architecture design, including a source data extraction layer, a transformation layer, and a loading layer. Each layer is independent of the others to facilitate expansion and optimization.

[0043] It should be noted that the source data extraction layer is responsible for extracting data from various data sources. Data collection for key information systems across regions and levels in the power industry can be carried out through probes. When designing the extraction layer, based on the requirements of data incremental extraction and incremental loading, the time and cost of data processing are minimized; the transformation layer is responsible for cleaning, transforming, and sorting the extracted data to meet the target data model and business requirements. When designing the transformation layer, various technologies and tools can be used, such as ETL tools, scripting languages (such as Python), data stream processing engines, etc. At the same time, issues such as data quality and data consistency are also considered, such as handling outliers, missing values, and redundant values in the data; the loading layer is responsible for loading the transformed data into the target system, such as a data warehouse, a data lake, or other analysis platforms.

[0044] Furthermore, when designing the loading layer, based on data partitioning and sharding, the loading efficiency and concurrency are improved.

[0045] Exemplarily, based on data characteristics, the data is partitioned according to features such as timestamps and geographical locations, and sharded according to the I / O performance and throughput of the storage system to improve the loading efficiency and concurrency.

[0046] In an optional embodiment, the loading speed can also be increased through bulk loading, parallel loading, and compression technologies.

[0047] In an optional embodiment, technical means such as batch loading, parallel loading, and compression techniques can be used to improve the loading speed.

[0048] In the embodiment of the present application, obtaining the multi-dimensional heterogeneous data sources of the power system at least includes: relational data sources, text files, and log files.

[0049] In the embodiment of the present application, data extraction from the multi-dimensional heterogeneous data sources includes: aiming at minimizing the data processing time and cost, extracting data from the multi-dimensional heterogeneous data sources.

[0050] It should be noted that data quality is a crucial aspect in the ETL process. When designing the ETL architecture, data verification and cleaning rules are also introduced, data quality indicators are monitored, and alarm and automatic repair mechanisms are set to ensure the accuracy and consistency of the data.

[0051] In the embodiment of the present application, preprocessing the extracted data includes: cleaning and verifying the extracted data before it enters the conversion stage after transmission, removing invalid data, and identifying and marking problem data.

[0052] In an optional embodiment, the data cleaning rules may include the following steps:

[0053] A1: For data with missing records, decide whether to delete the entire record or fill in the missing values according to business requirements;

[0054] A2: Ensure the consistency of each data, such as date format, measurement unit, etc.

[0055] It should be noted that the data annotation in the embodiment of the present application can be specifically selected according to actual applications, and the present application does not make specific limitations.

[0056] In an optional embodiment, the verification rules cover the following conditions and other relevant criteria, including:

[0057] B1: Preset data types, data ranges, etc.;

[0058] B2: Detect outliers using statistical methods, etc., and process abnormal data according to the power business requirements.

[0059] In an optional embodiment, the alarm and automatic repair mechanisms include:

[0060] Real-time monitoring of key data, where the key data may include current, frequency, etc.;

[0061] Preset a threshold value. If the monitored value of the key data exceeds the preset threshold value, an alarm process is performed;

[0062] Notify relevant personnel through an alarm mechanism, such as pop-up windows, etc. At the same time, an automated response plan can be set up in the system.

[0063] Exemplarily, during the power distribution process, after exceeding the threshold, modify the threshold or issue an alarm.

[0064] In the embodiment of the present application, performing data conversion on the preprocessed data includes: deeply cleaning, converting, and sorting the preprocessed data to meet the requirements of the target system.

[0065] In the embodiment of the present application, it further includes: dividing the task into multiple subtasks and performing parallel processing under a distributed computing framework.

[0066] It should be noted that, in order to provide scalability and perform performance optimization, the ETL architecture design performs parallel processing and distributed computing. By dividing the task into multiple subtasks and performing parallel processing under a distributed computing framework, the speed of data processing can be accelerated. Commonly used distributed computing frameworks include Hadoop, Spark, and Flink, etc.

[0067] In the embodiment of the present application, it further includes: monitoring data quality indicators through data verification and cleaning rules, and performing quality monitoring on the data according to the preset alarm and automatic repair mechanism.

[0068] It should be noted that, based on the fact that the ETL architecture focuses on data processing, including data extraction, transformation, and loading, it is more effective when processing a large amount of data from different sources and with different structures; ensuring data consistency and quality through the data transformation and cleaning process, eliminating damaged data, and integrating data, which is convenient for enterprises to perform the next operation; the ETL architecture processes data at a central location, which helps to improve the efficiency and quality of data processing, and at the same time can be extended according to the needs of enterprises to process the growing data volume; with the characteristics of clear steps and processes, being easy to understand and implement, the embodiment of the present application adopts the ETL architecture for data extraction, transformation, and loading, and after the data processing is completed, divides the task into multiple subtasks and performs parallel processing under a distributed computing framework to improve the efficiency and quality of data processing, and at the same time ensure data consistency and reliability.

[0069] Embodiment 2

[0070] The above embodiment is a schematic solution of a data processing method based on the ETL architecture. It should be noted that the technical solution of the data processing system based on the ETL architecture belongs to the same concept as the technical solution of the above-mentioned data processing method based on the ETL architecture. For the details not described in detail in the technical solution of the data processing system based on the ETL architecture in this embodiment, reference can be made to the description of the technical solution of the data processing method based on the ETL architecture above.

[0071] In this embodiment, a data processing system based on the ETL architecture includes:

[0072] A first processing module, configured to obtain multi-dimensional heterogeneous data sources of a power system, perform data extraction based on the multi-dimensional heterogeneous data sources, and preprocess the extracted data;

[0073] A second processing module, configured to perform data conversion on the preprocessed data, and load the converted data into a target system in a predetermined manner to complete data processing.

[0074] This embodiment further provides an electronic device, applicable to the case of a data processing method based on the ETL architecture, including:

[0075] A memory and a processor; the memory is used to store computer-executable instructions, and the processor is used to execute the computer-executable instructions to implement the data processing method based on the ETL architecture as proposed in the above embodiment.

[0076] This embodiment further provides a storage medium, on which a computer program is stored, and when the program is executed by a processor, it implements the data processing method based on the ETL architecture as proposed in the above embodiment.

[0077] The storage medium proposed in this embodiment and the data processing method based on the ETL architecture proposed in the above embodiment belong to the same inventive concept. For technical details not described in detail in this embodiment, reference can be made to the above embodiment, and this embodiment has the same beneficial effects as the above embodiment.

[0078] It should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention and not to limit them. Although the present invention has been described in detail with reference to the preferred embodiments, those of ordinary skill in the art should understand that the technical solutions of the present invention can be modified or equivalently replaced without departing from the spirit and scope of the technical solutions of the present invention, and they should all be covered by the scope of the claims of the present invention.

[0079] Those skilled in the art should understand that the embodiments of the present application can be provided as a method, a system, or a computer program product. Therefore, the present application can adopt the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware aspects. Moreover, the present application can adopt the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk memories, CD-ROMs, optical memories, etc.) containing computer-usable program codes. The solutions in the embodiments of the present application can be implemented in various computer languages, for example, object-oriented programming languages such as Java and interpreted scripting languages such as JavaScript.

[0080] This application is described with reference to the flowcharts and / or block diagrams of methods, apparatuses (systems), and computer program products according to embodiments of the present application. It should be understood that each flow and / or block in the flowchart and / or block diagram can be implemented by computer program instructions, as well as the combination of flows and / or blocks in the flowchart and / or block diagram. These computer program instructions can be provided to the processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing devices to generate a machine, such that the instructions executed by the processor of the computer or other programmable data processing devices generate a device for implementing the functions specified in one or more of the flows Figure 1 one or more of the flows and / or blocks Figure 1 or one or more of the blocks.

[0081] These computer program instructions can also be stored in a computer-readable memory that can direct a computer or other programmable data processing device to work in a specific manner, such that the instructions stored in the computer-readable memory generate a manufactured article including an instruction device that implements the functions specified in one or more of the flows Figure 1 one or more of the flows and / or blocks Figure 1 or one or more of the blocks.

[0082] These computer program instructions can also be loaded onto a computer or other programmable data processing device, such that a series of operational steps are executed on the computer or other programmable device to generate a computer-implemented process, and thus the instructions executed on the computer or other programmable device provide steps for implementing the functions specified in one or more of the flows Figure 1 one or more of the flows and / or blocks Figure 1 or one or more of the blocks.

[0083] Although the preferred embodiments of the present application have been described, those skilled in the art can make additional changes and modifications once they know the basic creative concepts. Therefore, the appended claims are intended to be construed to include the preferred embodiments as well as all changes and modifications falling within the scope of the present application.

[0084] Obviously, those skilled in the art can make various changes and modifications to the present application without departing from the spirit and scope of the present application. Thus, if these modifications and variations of the present application fall within the scope of the claims of the present application and their equivalent technologies, the present application is also intended to include these changes and modifications.

Claims

1. A data processing method based on ETL architecture, characterized in that: include: Acquire a multi-dimensional heterogeneous data source of the power system, extract data based on the multi-dimensional heterogeneous data source, and pre-process the extracted data; The preprocessed data is converted, and the converted data is loaded into the target system in a predetermined manner to complete the data processing.

2. The data processing method based on the ETL architecture according to claim 1, characterized in that: Extracting data based on the multi-dimensional heterogeneous data source includes: extracting data from the multi-dimensional heterogeneous data source with the goal of minimizing data processing time and cost.

3. The data processing method based on ETL architecture according to claim 2, characterized in that: Preprocessing the extracted data includes: cleaning and verifying the extracted data after transmission and before entering the conversion stage, eliminating invalid data, and identifying and marking problematic data.

4. The data processing method based on the ETL architecture according to claim 3, characterized in that: The data conversion of the preprocessed data includes: deep cleaning, conversion and sorting of the preprocessed data to meet the requirements of the target system.

5. The data processing method based on ETL architecture according to claim 4, characterized in that: Also includes: The task is divided into multiple subtasks and processed in parallel under the distributed computing framework.

6. The data processing method based on ETL architecture according to claim 5, characterized in that: Also includes: Through data verification and cleaning rules, data quality indicators are monitored, and data quality is monitored based on preset alarms and automatic repair mechanisms.

7. The data processing method based on ETL architecture according to claim 6, characterized in that: The multi-dimensional heterogeneous data sources for obtaining the power system include at least: relational data sources, text files and log files.

8. A data processing system based on ETL architecture, characterized in that: include: The first processing module is used to obtain a multi-dimensional heterogeneous data source of the power system and extract data based on the multi-dimensional heterogeneous data source. and preprocessing the extracted data; The second processing module is used to perform data conversion on the preprocessed data, load the converted data into the target system in a predetermined manner, and complete data processing.

9. An electronic device, comprising: Memory and processor; The memory is used to store computer-executable instructions, and the processor is used to execute the computer-executable instructions. When the computer-executable instructions are executed by the processor, the steps of the data processing method based on the ETL architecture described in any one of claims 1 to 7 are implemented.

10. A computer-readable storage medium storing computer-executable instructions, which, when executed by a processor, implement the steps of the data processing method based on the ETL architecture as claimed in any one of claims 1 to 7.

Images