Lake warehouse integrated power distribution internet of things data real-time processing system and method

Abstract

The application discloses a power distribution Internet of Things data real-time processing system and method based on lake warehouse integration, the system comprises an Internet of Things pre-acquisition module and a lake warehouse integration core processing module deployed by using a Docker container; the Internet of Things pre-acquisition module is used for acquiring power distribution Internet of Things data and sending the data to the lake warehouse integration core processing module; the lake warehouse integration core processing module comprises a data processing unit and a multidimensional analysis unit; the data processing unit comprises an original data processing unit, a dimension model processing unit, a fact data processing unit, an aggregation association processing unit and an application data processing unit; the application can effectively process and store massive heterogeneous data, thereby improving the confusion of data calculation analysis and statistics, reducing repeated calculation, realizing reuse of calculation results, improving the rapid retrieval efficiency of data, simultaneously providing multidimensional data support for the application side, and ensuring the response performance during large-scale complex data calculation.

Description

Technical Field

[0001] This invention relates to a real-time data processing system and method, and more particularly to a real-time data processing system and method for power distribution Internet of Things based on lake-warehouse integration. Background Technology

[0002] Against the backdrop of the State Grid's smart IoT system construction, the scale of distribution IoT system platform construction is gradually expanding, with the number of IoT smart terminal devices connected to provincial platforms reaching tens of millions, and the daily data volume reaching terabytes. Such a massive amount of data places high demands on the system platform in terms of real-time processing and computing, heterogeneous data storage, and multi-dimensional correlation query analysis.

[0003] Traditional power distribution IoT data processing systems utilize self-built CDH clusters as the technical platform for various component applications. The overall system is constructed using a Lambda architecture, with offline data acquisition and offline data warehousing as the core technologies. Different streaming computing technologies are used to achieve stream and batch processing respectively. Existing power distribution IoT data processing systems adopt a lake-warehouse separation approach, mainly consisting of a data lake, data warehouse, and application components. This provides unified data management and computing for the application side. However, at the physical level, the data lake and data warehouse are still separate, which can only achieve functional complementarity to a certain extent and is difficult to meet the higher demands of the system for real-time computing, processing, storage, data consistency, and operation and maintenance of massive amounts of data.

[0004] The emergence of lake warehouse technology provides a unified and shareable data foundation, avoiding data movement between traditional data lakes and data warehouses. However, there are many problems with using traditional lake warehouse technology for power distribution IoT data processing, which cannot meet the processing of massive power distribution IoT data: (1) The interactive data of power distribution IoT is divided into uplink data and downlink data according to the flow direction, and the data of different professional scenarios have ordered relationships. The massive interactive data has a large number of circular dependencies or complex structures. However, traditional data processing does not distinguish between different data and processes them all in a unified manner, resulting in repeated calculations and low retrieval efficiency; (2) Traditional data analysis is mostly carried out by integrating various types of data to perform wide table analysis. Since the wide table contains all relevant data, the data quality is high and the business calculation has a certain accuracy. However, in the scenario of massive heterogeneous data, the development difficulty is high and the maintenance cost is high. The wide table has many dependencies, which will lead to low system stability. In addition, the calculation of complex business logic will lead to problems such as slow operation of the wide table, high resource consumption, and low performance. Summary of the Invention

[0005] Purpose of the invention: The purpose of this invention is to provide a fast and effective real-time data processing system and method for power distribution Internet of Things based on lake-warehouse integration.

[0006] Technical solution: The real-time data processing system for power distribution IoT based on lake warehouse integration described in this invention includes an IoT front-end acquisition module and a lake warehouse integrated core processing module deployed using Docker containerization;

[0007] The IoT front-end acquisition module is used to collect power distribution IoT data and send it to the lake-warehouse integrated core processing module.

[0008] The integrated lake-warehouse core processing module includes a data processing unit; the data processing unit includes a raw data processing unit for writing the power distribution IoT data into a data lake table to generate a raw table, a dimension model processing unit for merging different data tables of related businesses into the same data lake table to generate a dimension table, a fact data processing unit for setting reasonable primary keys according to data types and performing association operations on the raw tables to generate a fact table, an aggregation association processing unit for associating the dimension tables with the fact tables, and an application data processing unit for classifying the data processed by the aggregation association processing unit and outputting it to the database.

[0009] Furthermore, the raw data processing unit uses Flink SQL to create a Flink temporary table and writes all the data in the Flink temporary table into the Hudi table;

[0010] The model dimension processing unit uses Flink SQL to create a Hudi dimension model processing public database, which aggregates the original table data of all related businesses into the corresponding dimension tables.

[0011] The fact data processing unit uses Flink SQL to create the Hudi fact data processing public database, and aggregates all the original table data representing the same business type into a fact data table;

[0012] The aggregation and association processing unit uses Flink SQL to create the Hudi aggregation and association processing database, and creates aggregation and association Hudi tables by performing association operations on dimension tables and fact tables;

[0013] The application data processing unit uses Flink SQL to create a Hudi application data processing database, and outputs the data from the aggregated and associated Hudi tables to the ClickHouse database after classifying them by business.

[0014] Furthermore, the integrated lake warehouse core processing module also includes a multidimensional analysis unit. The multidimensional analysis unit is used to summarize and perform real-time query analysis on the data tables processed by the application data processing unit according to ClickHouse columnar storage, and to pre-aggregate frequently occurring business aggregation operations through materialized views and store them in the materialized views.

[0015] Furthermore, the integrated lake warehouse core processing module also includes a management and control unit, which is used to manage and schedule metadata and operating environment resources.

[0016] Furthermore, the system also includes a northbound interface for connecting the integrated lake-warehouse core processing module to the application side.

[0017] Furthermore, the IoT front-end acquisition module sends the power distribution IoT data to the integrated lake-warehouse core processing module through a data transmission middleware; the data transmission middleware is a distributed stream processing middleware built on the message middleware Kafka.

[0018] The real-time data processing method for distribution IoT based on lake-warehouse integration described in this invention collects distribution IoT data and transmits it to the core processing module of lake-warehouse integration deployed in Docker containers for real-time processing. The distribution IoT data is written into a data lake table to generate an original table. Different data tables related to related businesses are merged into the same data lake table to generate dimension tables. A reasonable primary key is set according to the data type, and the original table is associated to generate a fact table. The dimension table and the fact table are associated and processed, and the data is classified and output to the database.

[0019] Furthermore, based on ClickHouse columnar storage, the data tables in the database are summarized and queried and analyzed in real time. Frequently used aggregation operations are pre-aggregated through materialized views and stored within the materialized views.

[0020] The electronic device of the present invention includes a memory, a processor, and a computer program stored in the memory and capable of running on the processor. When the computer program is loaded onto the processor, it implements the real-time data processing method for power distribution Internet of Things based on lake-warehouse integration.

[0021] The computer-readable storage medium of the present invention stores a computer program, which, when executed by a processor, implements the real-time data processing method for power distribution Internet of Things based on lake-warehouse integration.

[0022] Beneficial effects: Compared with the prior art, the advantages of the present invention are: (1) It effectively organizes, processes and stores massive heterogeneous data, thereby improving the chaos of data processing, analysis and statistics, reducing redundant calculations, realizing the reuse of calculation results and improving the efficiency of rapid data retrieval; (2) Multidimensional analysis takes data analysis as the starting point, and performs dimensional analysis and data modeling according to the data classification of various business scenarios and professional directions of power distribution Internet of Things. Each dimension table in the data processing process can be queried and modified in full, incremental and historical data during multidimensional analysis, so as to provide multidimensional data support for the application side more quickly and ensure that the system has sufficient response performance in large-scale complex data calculation; (3) The components and applications of the Internet of Things front-end acquisition module and the lake warehouse integrated core processing module are deployed in Docker containers, and the whole has cloud-native capabilities and is uniformly orchestrated based on Kubernetes cluster. (3) Control and management can freely increase or decrease computing and storage resources, realize dynamic elastic expansion, and improve development and operation and maintenance efficiency; (4) In response to the performance bottleneck problem of traditional power distribution IoT system in the storage, retrieval and analysis of massive concurrent data, the lake warehouse integrated core processing and storage architecture is adopted to completely avoid data silos, inability to integrate multiple types of data, lengthy modeling paths, performance bottlenecks and other problems; through full data T+0 stream processing and real-time computing, data modeling, application construction, data query and other operations are realized more flexibly, so that the system has full real-time T+0 processing capability, making up for the lack of real-time data processing capability based on Hadoop technology system; (5) The real-time computing stages and process data of data lake and data warehouse are uniformly stored on disk, and all stages of computing status and raw data are guaranteed to be stored globally. Combined with a unified transaction mechanism, it is ensured that the data queried and updated by different business scenarios on the user side has strong consistency. Attached Figure Description

[0023] Figure 1 This is a structural diagram of the real-time data processing system for the power distribution Internet of Things (IoT) of the present invention.

[0024] Figure 2 This is a data processing logic block diagram of the data processing unit of the present invention.

[0025] Figure 3 This is a schematic diagram of the multidimensional data mapping of the present invention. Detailed Implementation

[0026] The technical solution of the present invention will be further described below with reference to the accompanying drawings.

[0027] like Figure 1As shown, the real-time data processing system for power distribution IoT based on lakehouse integration includes an IoT front-end acquisition module, a lakehouse integrated core processing module, and a northbound interface. The lakehouse integrated core processing module includes data processing, storage, management, and multi-dimensional analysis units to perform real-time calculations, analysis, storage, and external access to data. The northbound interface exposes the business data and metadata of the lakehouse core processing to the application side through a unified interface, providing data support and business support. All components, applications, and programs of the IoT front-end acquisition module and the lakehouse integrated core processing module are implemented using Docker containers, with overall container orchestration and cluster management through Kubernetes, and support for migration and operation in cloud environments.

[0028] To ensure the versatility of the integrated lake warehouse functionality, Flink SQL is used for data ingestion, computation, and output. Hudi's table format is used for data storage around the IoT real-time data timeline. Core computational tasks in Flink utilize SavePoints to implement rollback points, allowing recovery in case of abnormal task exit or restart.

[0029] The IoT front-end acquisition module collects power distribution IoT data and sends the data to the lake warehouse integrated core processing module in real time through the data transmission middleware; in this embodiment, the data transmission middleware is a distributed stream processing middleware built on the message middleware Kafka.

[0030] The data processing units of the lake warehouse integrated core processing module include raw data processing units, dimensional model processing units, factual data processing units, aggregation and association processing units, and application data processing units. For example... Figure 2 The diagram shows the data processing logic of the raw data processing unit, the dimensional model processing unit, the factual data processing unit, the aggregation and association processing unit, and the application data processing unit.

[0031] The raw data processing unit processes and maps different types of data separately, writing them into the data lake tables, such as... Figure 3 The diagram shows a multidimensional data mapping. The data is written to the data lake table using the public database db_flink_temp (created using Flink SQL) and the public database db_hudi_ods (created using the original Hudi data).

[0032] After configuring the data source information for the Kafka connector using Flink, a Flink streaming task is created to directly map the relevant topic data from the data transfer middleware to a Flink temporary table. The Flink output task is then constructed, creating a Hudi table in `db_hudi_ods` to store the source data, and writing all the data from the Flink temporary table to the Hudi table, thus completing the mapping of all original data to the Hudi table.

[0033] This implementation leverages Flink CDC to perform real-time monitoring of all tables in a relational database. By configuring the relevant data source information through the mysql-cdc connector, it directly transforms raw data into streaming data and maps it to a Flink temporary business table in `db_flink_temp`. A primary key must be set when using table data with Flink CDC. A Flink output task for the business data is then constructed, creating a Hudi table in `db_hudi_ods` to store the business data, and writing all data from the corresponding Flink temporary business table into the Hudi table.

[0034] Each business data table in the relational database MySQL corresponds to a Flink processing task. Finally, the N relational tables, corresponding to N Flink processing tasks, are written into N Hudi tables.

[0035] When creating a Hudi table in db_hudi_ods, the data storage location, table type, merged primary key, merged field, concurrency level, and stream read mode are configured based on the Hudi connector. The merged field is configured by setting "compaction.delta_seconds" to delay the merge time, and this time must be greater than the time set by the Flink Checkpoint. The table type is set to real-time processing MOR mode.

[0036] When creating the Hudi table in db_hudi_ods, a primary key field, a partition field, and a window timestamp are added as supplementary fields for Hudi storage based on the original data and the device ID.

[0037] When creating the Hudi table in db_hudi_ods, the Flink data mapping task should be executed based on the Flink checkpoint when restarting to avoid duplicate data being written to the Hudi table.

[0038] The dimensional model processing unit joins different business data tables from the original data processing into the same data lake table. It mainly includes four main aggregation tables: IoT interaction business data, working condition data, response data, and management data, and uniformly constructs the corresponding dimensional tables. Specifically, it uses Flink SQL to create the Hudi dimensional model processing public database db_hudi_dim, and then creates the corresponding dimensional tables according to the business scenario.

[0039] Aggregate the original table data of all related businesses from the db_hudi_ods database into the corresponding dimension tables. When the field combination process involves a large number of join operations, set the concurrency level "read.tasks" based on the actual resource size.

[0040] To avoid incomplete data when performing join operations after importing raw data multiple times, the parameter "read.start-commit='earliest'" needs to be specified for each query operation on the original Hudi table during aggregation operations.

[0041] The fact data processing unit creates the Hudi fact data processing public database db_hudi_dwd using Flink SQL, and then creates corresponding fact tables according to the business scenario.

[0042] Aggregate all raw table data representing the same business type from the db_hudi_ods database into a fact table.

[0043] When performing aggregation operations on multiple Hudi tables using the `groupby` syntax, it's necessary to filter the grouping fields to ensure they are not null. When performing aggregation operations on a large number of Hudi tables, setting the Flink state expiration time using `table.exec.state.ttl` prevents excessive Flink state storage space from causing task errors.

[0044] The aggregation and association processing unit creates the Hudi aggregation and association processing database db_hudi_dws using Flink SQL, and creates aggregation and association Hudi tables by performing association operations on the dimensional model table and the fact data processing table.

[0045] The lookup join method in Flink is used to query data from the dimensional model table and the fact data processing table to enrich the aggregated related table data, and the statistical period is set to one day for aggregation.

[0046] To ensure performance during concurrent operations, the frequency of database access is controlled by setting "lookup.cache.ttl", thereby guaranteeing stability.

[0047] During the computation process, Flink's native aggregation functions are used during aggregation operations to avoid data inconsistencies caused by state data judgment.

[0048] The application data processing unit creates the Hudi application data processing database db_hudi_ads using Flink SQL, and outputs the data from the related tables of the aggregation and association processing to the OLAP database ClickHouse by classifying the data for business purposes.

[0049] Specifically, by configuring the relevant data source information through the ClickHouse connector, all data in the Hudi table that is aggregated and related is written to the corresponding ClickHouse table.

[0050] Different tables that require aggregation and relational processing need to be processed by a separate Flink task.

[0051] The multi-dimensional analysis unit of the lake warehouse integrated core processing module summarizes and performs real-time query analysis on the data tables after application data processing and calculation based on ClickHouse columnar storage. It mainly includes four major professional data types: IoT operating conditions, response, management, and business.

[0052] To improve write performance, data is written to ClickHouse in batches via Flink tasks, preventing MergeDataPart from excessively impacting performance and reducing memory pressure on the client.

[0053] To improve the query performance of external interfaces, frequently occurring aggregation operations are pre-aggregated using materialized views and stored within these views. This allows application-side queries to directly hit the pre-aggregated materialized views, thereby speeding up the query process.

[0054] The storage unit of the lake warehouse integrated core processing module is based on a distributed file system to store the raw data, status data, calculation results, and all data lake table data at each stage of data processing. This embodiment stores only one copy of the business data and one copy of the metadata, while globally calculating, analyzing, and processing shared data, saving storage costs and improving development and operation efficiency. To ensure efficient data access, an acceleration engine can be used on top of the distributed file system to uniformly accelerate the data.

[0055] The management and control unit of the lake-warehouse integrated core processing module provides unified management of metadata for both the data lake and data warehouse, as well as unified management of system cloud-native environment resources. Unified metadata management addresses data silos by registering, managing, synchronizing, and sharing metadata across multiple dimensions for both the data lake and data warehouse, enabling efficient data querying. The main meta-models include general metadata, Hudi metadata, and the schema model. Unified resource management utilizes a Kubernetes cluster environment as the runtime environment for the data lake, data warehouse, OLAP computing engine, and application service components, providing unified resource control and scheduling, as well as lifecycle management of application containers, enabling rapid construction of the lake-warehouse integrated core platform.

[0056] During the data processing task calculation process, all tables are first created based on the metadata schema model. Then, all real-time tasks for dimensional model processing, fact data processing, aggregation and association processing, and application data processing are started respectively. Finally, the original data processing real-time task is run to prevent data duplication or inconsistency.

[0057] The northbound interface connects the lakewarehouse integrated core processing module and the application side, providing a unified RESTful interface for northbound external access services. This RESTful interface is used as an HTTP interface for data processing, querying, and displaying operations on the application side. The northbound interface includes a unified OLAP interface, a unified SQL interface, a unified declarative data access interface, a unified metadata interface, and a unified global synchronization interface. Among these, the unified OLAP interface and the unified declarative data access interface are reactive interfaces implemented based on WebFlux, enabling fast interaction through asynchronous and non-blocking methods, improving the efficiency of the interface in network transmission.

[0058] The present invention describes a real-time data processing method for distribution IoT based on a lakehouse integration. This method collects distribution IoT data and transmits it to a Docker containerized lakehouse core processing module for real-time processing. The distribution IoT data is written into a data lake table to generate a raw table. Different data tables related to various business processes are merged into a single data lake table to generate dimension tables. Appropriate primary keys are set according to data types, and join operations are performed on the raw tables to generate fact tables. The dimension tables and fact tables are then joined, categorized by business process, and output to a database. Based on ClickHouse columnar storage, the data tables in the database are summarized and analyzed in real-time, mainly including four major professional data types: IoT operating conditions, responses, management, and business. Frequently occurring aggregation operations are pre-aggregated through materialized views and stored within the materialized views, improving the query performance of external interfaces.

[0059] The electronic device of the present invention includes a memory, a processor, and a computer program stored in the memory and capable of running on the processor. When the computer program is loaded onto the processor, it implements the real-time data processing method for power distribution Internet of Things based on lake-warehouse integration.

[0060] The computer-readable storage medium of the present invention stores a computer program, which, when executed by a processor, implements the real-time data processing method for power distribution Internet of Things based on lake-warehouse integration.

[0061] The computer-readable storage medium may include RAM, ROM, EEPROM, CD-ROM or other optical disc storage devices, magnetic disk storage devices or other magnetic storage devices, flash memory, or any other media that can be used to store desired program code in the form of instructions or data structures and is accessible by a computer.

[0062] The processor is used to execute a computer program stored in memory to implement the various steps in the methods described in the above embodiments.

Claims

1. A real-time data processing system for power distribution IoT based on lake-warehouse integration, characterized in that, The system includes an IoT front-end acquisition module and a lake-warehouse integrated core processing module deployed using Docker containers; The IoT front-end acquisition module is used to collect power distribution IoT data and send it to the lake warehouse integrated core processing module; the components, applications and programs of the IoT front-end acquisition module and the lake warehouse integrated core processing module are implemented in Docker containerization, and the overall container orchestration and cluster management are carried out through Kubernetes, supporting cloud environment migration and operation; The integrated lake-warehouse core processing module includes a data processing unit; the data processing unit includes a raw data processing unit for writing the power distribution IoT data into a data lake table to generate a raw table, a dimension model processing unit for merging different data tables of related businesses into the same data lake table to generate a dimension table, a fact data processing unit for setting reasonable primary keys according to data types and performing association operations on the raw tables to generate a fact table, an aggregation association processing unit for associating the dimension tables with the fact tables, and an application data processing unit for classifying the data processed by the aggregation association processing unit and outputting it to the database. The dimensional model processing unit includes four aggregate tables: IoT interaction business data, operating condition data, response data, and management data, which are used to uniformly construct the corresponding dimensional tables. The fact data processing unit aggregates all the original table data representing a unified business type into a fact table; The aggregation and association processing unit creates an aggregation and association Hudi table by performing association operations on the dimension model table and the fact data processing table. It then uses Flink's Lookup Join method to query data from the dimension table and the fact table to enrich the data in the aggregation and association table. The statistical period is set to one day for aggregation. The integrated lake warehouse core processing module also includes a multi-dimensional analysis unit, which summarizes and performs real-time query analysis on the data tables calculated by the application data processing unit based on ClickHouse columnar storage, including four professional data types: IoT operating conditions, response, management, and business.

2. The real-time data processing system for power distribution IoT based on lake-warehouse integration as described in claim 1, characterized in that, The raw data processing unit uses Flink SQL to create a Flink temporary table and writes all the data in the Flink temporary table into the Hudi table; The dimensional model processing unit uses Flink SQL to create a Hudi dimensional model processing public database, which aggregates the original table data of all related businesses into the corresponding dimensional tables. The fact data processing unit uses Flink SQL to create the Hudi fact data processing public database, and aggregates all the original table data representing the same business type into a fact data table; The aggregation and association processing unit uses Flink SQL to create the Hudi aggregation and association processing database, and creates aggregation and association Hudi tables by performing association operations on dimension tables and fact tables; The application data processing unit uses Flink SQL to create a Hudi application data processing database, and outputs the data from the aggregated and associated Hudi tables to the ClickHouse database after classifying them by business.

3. The real-time data processing system for power distribution IoT based on lake-warehouse integration as described in claim 1, characterized in that, The multidimensional analysis unit is used to summarize and perform real-time query analysis on the data tables processed by the application data processing unit according to ClickHouse columnar storage, and to pre-aggregate frequently occurring business aggregation operations through materialized views and save them in the materialized view.

4. The real-time data processing system for power distribution IoT based on lake-warehouse integration as described in claim 1, characterized in that, The integrated lake warehouse core processing module also includes a management and control unit, which is used to manage and schedule metadata and operating environment resources.

5. The real-time data processing system for power distribution IoT based on lake-warehouse integration as described in claim 1, characterized in that, The system also includes a northbound interface for connecting the integrated lake and warehouse core processing module to the application side.

6. The real-time data processing system for power distribution IoT based on lake-warehouse integration according to claim 1, characterized in that, The IoT front-end acquisition module sends power distribution IoT data to the lake-warehouse integrated core processing module through a data transmission middleware; the data transmission middleware is a distributed stream processing middleware built on the message middleware Kafka.

7. A real-time data processing method for power distribution IoT based on the integrated lake-warehouse system described in claim 1, characterized in that, Data from the power distribution IoT is collected and transmitted to the Docker containerized lake warehouse core processing module for real-time processing. The power distribution IoT data is written into a data lake table to generate a raw table. Different data tables related to the business are merged into the same data lake table to generate dimension tables. A reasonable primary key is set according to the data type, and the raw table is associated to generate a fact table. The dimension table and the fact table are associated and processed, and the data is classified and output to the database.

8. The real-time data processing method for power distribution IoT based on lake-warehouse integration according to claim 7, characterized in that, Based on ClickHouse columnar storage, the database tables are summarized and queried in real time. Frequently used aggregation operations are pre-aggregated through materialized views and stored in the materialized view.

9. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the computer program is loaded into the processor, it implements the real-time data processing method for power distribution IoT based on lake-warehouse integration as described in any one of claims 7-8.

10. A computer-readable storage medium storing a computer program, characterized in that, When the computer program is executed by the processor, it implements the real-time data processing method for power distribution Internet of Things based on lake-warehouse integration as described in any one of claims 7-8.

Images