A data processing system integrating lake storage and real-time collaboration

Abstract

This invention discloses a lake-warehouse integrated and real-time collaborative data processing system, belonging to the field of data processing technology. The system includes: a production system data synchronization module for acquiring data from the production system and storing it in an edge warehouse; a data lake-based data management module for receiving data from the production system data synchronization module via dual data pipelines and processing all business domain data based on the data lake processing engine; a data warehouse-based data management module for selecting only a single business domain data from the data lake to synchronize to the data warehouse using a preset data synchronization mode, and processing the single business domain data based on the data warehouse processing engine; and a multimodal data consumption module for providing multimodal data consumption methods to consume data from the data warehouse. This invention achieves end-to-end data management from the production system to data applications, ensuring real-time and high-quality data processing.

Description

Technical Field

[0001] This invention belongs to the field of data processing technology. Specifically, this invention relates to a data processing system that integrates lake storage and real-time collaboration. Background Technology

[0002] The current data architecture in the manufacturing industry has inherent flaws: the separation between batch processing and real-time computing leads to data silos and decision delays; at the same time, the separation of data lakes and data warehouses results in complex architectures and low collaboration efficiency, which cannot meet the critical needs of the manufacturing industry for real-time insights into data across the entire process.

[0003] Currently, manufacturing enterprises typically employ two main data processing methods: centralized processing based on traditional data warehouses and batch processing based on big data platforms. While traditional data warehouses can guarantee data consistency and accuracy, they suffer from poor real-time performance, making it difficult to meet the needs of real-time monitoring and immediate decision-making. On the other hand, while big data platform-based processing can handle massive amounts of data, it often lacks comprehensive data governance and quality assurance mechanisms, making it difficult to guarantee data latency and consistency.

[0004] Furthermore, most existing data processing systems employ a single data pipeline, which cannot provide flexible data services based on different business scenarios. For scenarios with high real-time requirements (such as production monitoring and fault early warning), a single pipeline may lead to data delays; while for complex analysis scenarios (such as quality analysis and production optimization), a single pipeline may not be able to provide sufficient data quality and historical traceability capabilities.

[0005] Therefore, this invention proposes a data processing system that integrates lake storage and real-time collaboration. Summary of the Invention

[0006] This invention aims to overcome the shortcomings of existing technologies and proposes a data processing system that integrates lake and warehouse systems and enables real-time collaboration, in order to achieve the following objectives: realize full-process data management from production and manufacturing systems to data applications, and ensure the real-time performance and high quality of data processing.

[0007] To achieve the above objectives, the technical solution adopted by the present invention is: a data processing system for integrated lake and warehouse operations with real-time collaboration, the system comprising: The production and manufacturing system data synchronization module is used to acquire data from the production and manufacturing systems of each factory and synchronize and store it in the edge warehouse established in the corresponding factory's location; The data management module based on the data lake is used to receive data from the data synchronization module of the production and manufacturing system through dual data pipelines, and to process and manage all business domain data based on the data lake processing engine. The data management module based on the data warehouse is used to select only a single business domain data in the data lake to synchronize to the data warehouse through a preset data synchronization mode, and to process and manage the single business domain data based on the data warehouse processing engine. The multimodal data consumption module is used to provide multimodal data consumption methods for consuming data in the data warehouse.

[0008] Preferably, the production and manufacturing system data synchronization module includes the production and manufacturing systems of each factory, the CDC data synchronization tool, and the edge warehouse established in the location of each factory. The edge warehouse established in the location of each factory captures and stores the data of the corresponding factory's production and manufacturing system in real time through the CDC data synchronization tool.

[0009] Preferably, the dual data pipeline includes: Offline pipelines are directly connected to the production and manufacturing systems of each factory. They use the Sqoop tool to configure periodic batch processing tasks to extract full or incremental data from each factory's production and manufacturing systems into the data lake. The real-time pipeline interfaces with each edge warehouse and uses real-time data migration technology based on Flink-CDC to synchronize data from each edge warehouse to the data lake in real time.

[0010] Preferably, the data lake adopts a hierarchical data model architecture, including: The ODS layer is used to receive raw data from the dual data pipeline; The DIM layer is used to process the data in the ODS layer using SparkSQL to build standardized and unified dimension tables. The DWD layer is used to clean, transform, and normalize the data in the ODS layer using SparkSQL, and to associate it with the dimensions of the DIM layer to form a detailed fact table. The DWS layer is used to perform light summarization and aggregation based on business themes on the basis of the DWD and DIM layers, forming wide tables for different business themes.

[0011] Preferably, the data warehouse adopts a layered data model architecture, including: The DIM layer is used to process single business domain data synchronized from the data lake using DorisSQL, and to build standardized and unified dimension tables. The DWD layer is used to clean, transform, and normalize single business domain data synchronized from the data lake using DorisSQL, and to associate it with the dimensions of the DIM layer to form a detailed fact table. The DWS layer is used to perform light aggregation and summarization of single business domain data synchronized from the data lake, based on the DWD and DIM layers, to form wide tables for the corresponding business themes. The ADS layer is used to provide data that has been processed by the DWD layer, DIM layer, and DWS layer and is geared towards a single business domain.

[0012] Preferably, the preset data synchronization mode includes: Lake-warehouse integrated mapping mode: Utilizing the multi-data source catalog of the data warehouse, the mapping from the data lake to the data warehouse is realized. This mode involves zero data movement and is suitable for exploratory queries on cold data or infrequently updated data in the data lake.

[0013] Preferably, the preset data synchronization mode includes: Real-time synchronous write mode: Using real-time data migration technology based on Flink-CDC, the changed data of the data lake is written to the data warehouse.

[0014] Preferably, the preset data synchronization mode includes: Streaming ETL mode: Use FlinkSQL to extract, transform and load data streams from the data lake in real time, consume CDC streams or timed scan streams from the data lake, and perform real-time data cleaning, multi-stream association and complex aggregation before writing to the data warehouse.

[0015] Preferably, the preset data synchronization mode includes: Accelerated Materialized View Mode within the Data Warehouse: Materialized views are created based on detail tables within the data warehouse, and aggregation results are automatically pre-calculated and maintained based on the data warehouse processing engine.

[0016] Preferably, the multimodal data consumption method includes: Backend microservices: Backend microservices access the data warehouse through standard JDBC / ODBC drivers, providing low-latency data interfaces for various business systems; BI and reporting tools: BI and reporting tools establish a connection between the data warehouse and various reporting systems for interactive analysis and fixed reports; Intelligent Applications: The data warehouse is connected to intelligent applications to meet the large number of ad-hoc queries brought about by the conversion of natural language into SQL for AI query scenarios; AI / ML Platform: The AI / ML platform extracts feature data from the data warehouse for model training.

[0017] The technical effects of this invention are as follows: This invention addresses the data instability issues that can arise from network jitter and schema changes when data flows directly from the production system to the data lake by constructing an edge warehouse, ensuring high availability and low latency for real-time data streams. It utilizes parallel offline and real-time pipelines to synchronize data of varying volumes and timeliness. Simultaneously, data synchronized from the offline pipelines can be used to correct data in the real-time pipeline, guaranteeing data quality. Furthermore, this invention enables collaborative data processing through a data lake and data warehouse, with each working in a specific role to meet the requirements for data timeliness, computational cost, and data consistency across different business scenarios. Finally, this invention provides a multimodal data consumption approach, ultimately achieving end-to-end data management from the production system to data applications. Attached Figure Description

[0018] Figure 1 This is a structural block diagram of a data processing system that integrates lake storage and real-time collaboration. Detailed Implementation

[0019] The specific embodiments of the present invention will be further described in detail below with reference to the accompanying drawings. This is to help those skilled in the art to have a more complete, accurate, and in-depth understanding of the inventive concept and technical solutions of the present invention, and to facilitate its implementation. It should be noted that the terms "first," "second," etc., used in this application are only for the convenience of describing the technical solutions and to distinguish components; the corresponding component configurations may be the same or different, and are not intended to limit the scope of this application. To make the technical solutions of the present invention clearer, the present invention will be explained and illustrated through the following embodiments.

[0020] This invention provides a data processing system integrating a data lake and a data warehouse, enabling real-time collaboration. It constructs a unified data processing architecture that combines parallel offline and real-time pipelines with collaborative processing between the data lake and the data warehouse. It does not simply piece the two together, but rather uses a sophisticated logical design to ensure they complement each other and work collaboratively. For example... Figure 1 As shown, the system includes: The production and manufacturing system data synchronization module is used to acquire data from the production and manufacturing systems of each factory and synchronize and store it in the edge warehouse established in the corresponding factory's location; The data management module based on the data lake is used to receive data from the data synchronization module of the production and manufacturing system through dual data pipelines, and to process and manage all business domain data based on the data lake processing engine. The data management module based on the data warehouse is used to select only a single business domain data in the data lake to synchronize to the data warehouse through a preset data synchronization mode, and to process and manage the single business domain data based on the data warehouse processing engine. The multimodal data consumption module is used to provide multimodal data consumption methods for consuming data in the data warehouse.

[0021] Specifically, the production and manufacturing system data synchronization module in this embodiment includes the production and manufacturing systems of each factory, the CDC data synchronization tool, and an edge warehouse established in each factory's location. Each factory's edge warehouse captures and stores data from its corresponding factory's production and manufacturing system in real time using the CDC data synchronization tool. The production and manufacturing systems of each factory (such as ERP and MES) are typically deployed independently, using relational databases such as MySQL and SQL Server. To achieve unified management and analysis of production data, this embodiment establishes an edge warehouse (using a PostgreSQL database) in each factory's location. Each edge warehouse monitors the change records (including insert, update, and delete operations) of the production and manufacturing system database through the CDC data synchronization tool (e.g., StreamSets). When data changes are detected, the CDC data synchronization tool captures these changes in real time and synchronizes them to the corresponding edge warehouse.

[0022] The establishment of edge warehouses enables the preprocessing of production system data (such as sensitive data removal, data cleaning and verification), which facilitates subsequent data storage and processing. At the same time, as a buffer and initial aggregation node between the production system and the data lake, the edge warehouse reduces the data processing pressure on both the production system and the data lake, while solving the problem of data instability that may be caused by network jitter, schema changes, etc. when directly transferring data from the production system to the data lake, thus ensuring high availability and low latency of real-time data streams.

[0023] For data from the manufacturing system, this embodiment establishes a data management module based on a data lake, which acquires data from the manufacturing system through dual data pipelines. These dual data pipelines include an offline pipeline and a real-time pipeline, capable of simultaneously meeting the needs of batch processing and real-time processing, as well as accommodating data synchronization for different data volumes and timeframes. Furthermore, data synchronized from the offline pipeline can be used to correct data in the real-time pipeline, ensuring data quality.

[0024] The offline pipeline directly interfaces with the production and manufacturing systems of each factory. It uses Sqoop (a tool in the Hadoop ecosystem used to transfer data between relational databases and Hadoop) to configure periodic batch processing tasks, extracting full or incremental data from each factory's production and manufacturing systems into the data lake. This path is primarily used for data processing scenarios with low real-time requirements, such as latency-based processing, historical data backtracking, large-scale complex ETL, and cost-sensitive analysis.

[0025] The real-time pipeline interfaces with each edge warehouse and uses real-time data migration technology based on Flink-CDC (Apache Flink's change data capture technology, which can capture database CRUD operations in real time) to synchronize data from each edge warehouse to the data lake in real time. This path is mainly used in scenarios with high real-time requirements to achieve real-time data ingestion into the data lake.

[0026] After the data is fed into the data lake, the data lake processing engine, with SparkSQL (Apache Spark's SQL query module) at its core, performs batch processing tasks such as heavy ETL (extraction, transformation, and loading), full data construction, and dimension table standardization on the data lake. This constructs a standardized hierarchical data model for the data lake, ensuring data standardization and consistency. The data lake in this embodiment adopts a hierarchical data model architecture, including: The ODS layer (raw data operation layer) is used to receive raw data from the dual data pipeline, retaining the most original data copy as the starting point for subsequent data processing. The DIM layer is used to process the data in the ODS layer using SparkSQL to build standardized and unified dimension tables, such as vehicle model dimension, product dimension, time dimension, etc. The DWD layer is used to clean, transform, and normalize the data in the ODS layer using SparkSQL, and to associate it with the dimensions of the DIM layer to form a detailed fact table. The DWS layer is used to perform light summarization and aggregation based on business themes on the basis of the DWD and DIM layers, forming wide tables for different business themes.

[0027] The data lake's layered data model serves various business domains (e.g., in manufacturing, there are multiple business domains such as finance, sales, and manufacturing), enabling data management for each domain and resolving the issue of data mixing across different business domains. Simultaneously, to achieve independent management of data for a specific business domain, this embodiment constructs a data management module based on a data warehouse. This module stores and manages data for a single business domain through a data warehouse processing engine. The data warehouse processing engine, based on Apache Doris, receives standardized data from the data lake and leverages its high-performance OLAP (Online Analytical Processing) capabilities to directly provide low-latency, high-concurrency query services to applications. Similarly, the data warehouse also adopts a layered data model architecture, including: The DIM layer is used to process single business domain data synchronized from the data lake using DorisSQL (Apache Doris's SQL query module) to build standardized and unified dimension tables. The DWD layer is used to clean, transform, and normalize single business domain data synchronized from the data lake using DorisSQL, and to associate it with the dimensions of the DIM layer to form a detailed fact table. The DWS layer is used to perform light aggregation and summarization of single business domain data synchronized from the data lake, based on the DWD and DIM layers, to form wide tables for the corresponding business themes. The ADS layer provides data processed by the DWD, DIM, and DWS layers and directed to a single business domain. The ADS layer of the data warehouse is entirely self-constructed and does not inherit from the data lake. This allows applications in a single business domain to optimize query performance to the fullest extent based on their unique reporting and interface requirements, without being limited by the general model of the data lake.

[0028] The data lake processing engine and the data warehouse processing engine are not in a master-slave relationship, but rather are partners with a division of labor. The data lake processing engine, as the data foundation and standardized factory, is responsible for storing and processing massive amounts of data; the data warehouse processing engine, as the data service front-end and accelerator, is responsible for providing ultra-fast queries. The two achieve flexible integration through the following preset data synchronization modes to meet the requirements of data timeliness, computing cost, and data consistency in different business scenarios.

[0029] Lakehouse Integrated Mapping Mode: Utilizing the multi-data source catalog of the data warehouse, the mapping from the data lake to the data warehouse is realized. This mode involves zero data movement and is suitable for exploratory queries on cold data or infrequently updated data in the data lake, saving storage costs. For example, it is suitable for periodically refreshing report dashboards with low concurrency and real-time requirements.

[0030] Real-time synchronous write mode: Through real-time data migration technology based on Flink-CDC, changed data from the data lake is stably and with low latency written to the data warehouse. This mode emphasizes the stability and low latency of data synchronization and is suitable for real-time refreshed reports (factory output, line stop alarms, etc.). It can be used to build the core detail layer (DWD) within the data warehouse.

[0031] Streaming ETL mode: This mode uses FlinkSQL to perform real-time ETL (extraction, transformation, and loading) on ​​data streams from the data lake, consuming CDC streams or scheduled scan streams from the data lake, and performing real-time data cleaning, multi-stream association, and complex aggregation before writing to the data warehouse. This mode focuses more on real-time data processing before writing, migrating some ETL logic from batch processing to real-time stream processing. It is suitable for customized needs with high real-time requirements and large data volumes, meeting the business's need for real-time light data aggregation (DWS).

[0032] In-warehouse materialized view acceleration mode: Within the data warehouse, materialized views are created based on detail tables, and aggregation results are automatically pre-calculated and maintained by the data warehouse engine, significantly improving query performance, saving the synchronization task cost of real-time data ingestion, and enhancing the performance of the lake warehouse integration. This mode greatly improves the performance of fixed-dimensional aggregation queries and is used to build the Application Data Service (ADS) layer.

[0033] In practical applications, the above four modes can be flexibly selected according to business scenarios, saving resources and development costs.

[0034] In this embodiment, the data lake's layered data model is a public domain model. The DWD and DWS layers in the data lake are designed for enterprise-level sharing, storing the finest-grained and lightly summarized data, serving various business domains (such as finance, sales, and manufacturing). The data warehouse's layered data model is a single business domain-specific model. Although the DWD and DWS layers in the data warehouse share the same names as the data lake's layered model, they are logically exclusive to a specific business domain. Through "synchronization, trimming, or escaping" during the data synchronization process, only the data and fields relevant to a single business domain are included in the data warehouse. This design achieves physical isolation and logical unity between the enterprise-level data foundation and domain-level data services, ensuring both consistent data sources and meeting the single business domain's requirements for query performance and data focus.

[0035] Finally, this embodiment also constructs a multimodal data consumption module to provide multimodal data consumption methods for consuming data in the data warehouse. The multimodal data consumption module uses Apache Doris to build a unified data service gateway, providing a unified query interface to all upper-layer applications. All queries are completed through Doris SQL, simplifying application development complexity. Subsequently, various application systems, based on this unified query interface, provide multimodal data consumption methods for the data warehouse, including: Backend microservices (Spring Cloud): Backend microservices access the data warehouse through standard JDBC / ODBC drivers, providing low-latency data interfaces for various business systems; BI (Business Intelligence) and Reporting Tools (FineBI, FineReport): BI and reporting tools establish a connection between the data warehouse and various reporting systems for interactive analysis and fixed reports; Intelligent Applications: The data warehouse is connected to intelligent applications to meet the large number of ad-hoc queries brought about by the conversion of natural language into SQL for AI query scenarios; AI (Artificial Intelligence) / ML (Machine Learning) Platform: The AI / ML platform extracts feature data from the data warehouse for model training, realizing an efficient closed loop between data analysis and AI.

[0036] The present invention has been described above by way of example with reference to the accompanying drawings. Obviously, the specific implementation of the present invention is not limited to the above-described manner. Any non-substantial improvements made using the inventive concept and technical solution; or the direct application of the inventive concept and technical solution to other situations without modification, are all within the protection scope of the present invention.

Claims

1. A data processing system integrating lake storage and real-time collaboration, characterized in that: The system includes: The production and manufacturing system data synchronization module is used to acquire data from the production and manufacturing systems of each factory and synchronize and store it in the edge warehouse established in the corresponding factory's location; The data management module based on the data lake is used to receive data from the data synchronization module of the production and manufacturing system through dual data pipelines, and to process and manage all business domain data based on the data lake processing engine. The data management module based on the data warehouse is used to select only a single business domain data in the data lake to synchronize to the data warehouse through a preset data synchronization mode, and to process and manage the single business domain data based on the data warehouse processing engine. The multimodal data consumption module is used to provide multimodal data consumption methods for consuming data in the data warehouse.

2. The integrated lake-warehouse and real-time collaborative data processing system according to claim 1, characterized in that: The production and manufacturing system data synchronization module includes the production and manufacturing systems of each factory, the CDC data synchronization tool, and the edge warehouse established in the location of each factory. The edge warehouse established in the location of each factory captures and stores the data of the corresponding factory's production and manufacturing system in real time through the CDC data synchronization tool.

3. The integrated lake-warehouse and real-time collaborative data processing system according to claim 1, characterized in that: The dual data pipeline includes: Offline pipelines are directly connected to the production and manufacturing systems of each factory. They use the Sqoop tool to configure periodic batch processing tasks to extract full or incremental data from each factory's production and manufacturing systems into the data lake. The real-time pipeline interfaces with each edge warehouse and uses real-time data migration technology based on Flink-CDC to synchronize data from each edge warehouse to the data lake in real time.

4. The integrated lake-warehouse and real-time collaborative data processing system according to claim 1, characterized in that: The data lake adopts a layered data model architecture, including: The ODS layer is used to receive raw data from the dual data pipeline; The DIM layer is used to process the data in the ODS layer using SparkSQL to build standardized and unified dimension tables. The DWD layer is used to clean, transform, and normalize the data in the ODS layer using SparkSQL, and to associate it with the dimensions of the DIM layer to form a detailed fact table. The DWS layer is used to perform light summarization and aggregation based on business themes on the basis of the DWD and DIM layers, forming wide tables for different business themes.

5. The integrated lake-warehouse and real-time collaborative data processing system according to claim 1, characterized in that: The data warehouse adopts a layered data model architecture, including: The DIM layer is used to process single business domain data synchronized from the data lake using DorisSQL, and to build standardized and unified dimension tables. The DWD layer is used to clean, transform, and normalize single business domain data synchronized from the data lake using DorisSQL, and to associate it with the dimensions of the DIM layer to form a detailed fact table. The DWS layer is used to perform light aggregation and summarization of single business domain data synchronized from the data lake, based on the DWD and DIM layers, to form wide tables for the corresponding business themes. The ADS layer is used to provide data that has been processed by the DWD layer, DIM layer, and DWS layer and is geared towards a single business domain.

6. The integrated lake-warehouse and real-time collaborative data processing system according to claim 1, characterized in that: The preset data synchronization mode includes: Lake-warehouse integrated mapping mode: Utilizing the multi-data source catalog of the data warehouse, the mapping from the data lake to the data warehouse is realized. This mode involves zero data movement and is suitable for exploratory queries on cold data or infrequently updated data in the data lake.

7. The integrated lake-warehouse and real-time collaborative data processing system according to claim 1, characterized in that: The preset data synchronization mode includes: Real-time synchronous write mode: Using real-time data migration technology based on Flink-CDC, the changed data of the data lake is written to the data warehouse.

8. The integrated lake-warehouse and real-time collaborative data processing system according to claim 1, characterized in that: The preset data synchronization mode includes: Streaming ETL mode: Use FlinkSQL to extract, transform and load data streams from the data lake in real time, consume CDC streams or timed scan streams from the data lake, and perform real-time data cleaning, multi-stream association and complex aggregation before writing to the data warehouse.

9. The integrated lake-warehouse and real-time collaborative data processing system according to claim 1, characterized in that: The preset data synchronization mode includes: Accelerated Materialized View Mode within the Data Warehouse: Materialized views are created based on detail tables within the data warehouse, and aggregation results are automatically pre-calculated and maintained based on the data warehouse processing engine.

10. The integrated lake-warehouse and real-time collaborative data processing system according to claim 1, characterized in that: The multimodal data consumption methods include: Backend microservices: Backend microservices access the data warehouse through standard JDBC / ODBC drivers, providing low-latency data interfaces for various business systems; BI and reporting tools: BI and reporting tools establish a connection between the data warehouse and various reporting systems for interactive analysis and fixed reports; Intelligent Applications: The data warehouse is connected to intelligent applications to meet the large number of ad-hoc queries brought about by the conversion of natural language into SQL for AI query scenarios; AI / ML Platform: The AI / ML platform extracts feature data from the data warehouse for model training.

Images