Intelligent mixed storage and cooperative processing method and system for multi-source heterogeneous mass data

Abstract

The invention relates to the technical field of big data storage and processing, and particularly discloses an intelligent mixed storage and cooperative processing method and system oriented to multi-source heterogeneous mass data. The method comprises the steps that firstly, original data are received and analyzed and normalized, then features are extracted from the normalized data, and the extracted features are stored in a database; and making a real-time decision according to a feature matching rule, writing the data into different underlying databases, and recording position mapping and storage strategies of the data. During query, a query request is subjected to lexical analysis and grammatical analysis, whether the query is single-library query or cross-library query is judged, different steps are executed, and finally, data migration and archiving operation is automatically executed according to a predefined data life cycle strategy. Based on the method, the system comprises a unified data access module, an intelligent routing engine module, a unified query service module, a collaborative computing engine module and a metadata and life cycle management module.

Description

Technical Field

[0001] This invention relates to the field of big data storage and processing technology, and in particular to a hybrid database storage, management and collaborative processing technology for multi-source, heterogeneous, massive time-series and relational data generated in the fields of infrastructure environmental monitoring, industrial Internet of Things, and smart cities. Specifically, it discloses an intelligent hybrid storage and collaborative processing method and system for multi-source heterogeneous massive data. Background Technology

[0002] With the rapid development of IoT and sensor network technologies, various monitoring systems, such as environmental quality monitoring, equipment operation status monitoring, and infrastructure safety monitoring, have generated massive amounts of data. This data exhibits typical characteristics of being multi-source and heterogeneous. Multi-source: The data comes from different types of sensors, video surveillance, business systems, etc. Heterogeneous: The data types are diverse, including high-frequency time-series data (such as temperature, pressure, vibration), unstructured file data (such as images and videos), and structured relational data (such as equipment metadata and alarm events). Massive volume: The data volume is enormous and continues to grow rapidly, posing a severe challenge to the scalability and cost control of storage systems.

[0003] Currently, the industry typically employs a single-database approach or a simple combination of multiple databases. A single-database approach attempts to store all types of data using a single database (such as the relational database MySQL or the time-series database InfluxDB). However, relational databases suffer from poor write performance, high storage costs, and inefficient querying when processing high-frequency time-series data; time-series databases struggle to support complex join queries and transaction operations. A simple hybrid database approach selects a dedicated database for different types of data (e.g., using a time-series database to store sensor data and a relational database to store device information). While this approach leverages the strengths of each database to some extent, it leads to a severe "data silo" problem. Application layer developers must maintain multiple database connections and write complex code to coordinate cross-database data join queries and transactions, significantly increasing system complexity and maintenance costs. Furthermore, cross-database data consistency is difficult to guarantee. Therefore, there is an urgent need in this field for a technical solution that can uniformly manage multi-source heterogeneous monitoring data, automatically perform optimal storage and computation based on data characteristics at the underlying level, and provide a unified access interface externally. Summary of the Invention

[0004] To address the shortcomings of existing technologies, this invention provides an intelligent hybrid storage and collaborative processing method and system for massive multi-source heterogeneous data, which breaks down the "data silo" problem in existing technologies and improves the overall efficiency of data processing.

[0005] The specific technical solution adopted in this invention is as follows: A method for intelligent hybrid storage and collaborative processing of massive heterogeneous data from multiple sources includes the following steps: S1. Receive raw data from different data sources, extract key fields according to a predefined semantic model to verify data integrity, form metadata and a global metadata directory, and encapsulate the raw data and corresponding metadata into a unified data object; S2. Based on the different data characteristics of the unified data object, write the data into different underlying databases according to the feature matching rules, and record the data location mapping and storage strategy; S3. Receive query requests and perform lexical and syntactic analysis on the query requests to identify the data entities, conditions and operation types involved, and determine whether the query request is a single-database query or a cross-database query; S4. When the query request is a single-database query, the stored data in the corresponding underlying database is returned directly; when it is determined in S3 that the query is a cross-database query, the stored data in the corresponding underlying databases are integrated to form a unified result set and then returned. S5. Monitor the storage load status and data access patterns of each underlying database in real time, and automatically perform data migration and archiving operations according to predefined data lifecycle strategies.

[0006] Furthermore, the predefined semantic model in S1 includes a device model, a measurement point model, and an event model. The device model is used to extract basic information about the device, the measurement point model is used to extract the attributes of specific measurement points, and the event model is used to extract the structure of events.

[0007] Furthermore, the different data characteristics in S2 include data structure, data size, and data generation frequency. Data with strict time series attributes and a generation frequency at the second level or higher are classified as high-frequency time series data; data that can be clearly defined by a standardized two-dimensional table and has strict entity relationships and referential integrity are classified as device metadata and business relationship data; and data that exists as descriptive indexes of massive unstructured files or semi-structured data that needs to be stored in variable modes are classified as document and file index data.

[0008] Furthermore, the feature matching rules in S2 are as follows: high-frequency time-series data is routed to a time-series database, device metadata and business relationship data are routed to a relational database, and document and file index data are routed to a document database or object storage.

[0009] Furthermore, the method for determining whether a specific query request is a single-database query or a cross-database query in S3 is as follows: verify the query metadata and the global metadata directory to obtain the number of underlying databases involved.

[0010] Furthermore, in step S4, when performing cross-database queries, the following sub-steps are specifically executed: (1) Decompose the original query into multiple subqueries, each subquery targeting a specific underlying database; (2) Push the filtering and aggregation operations down to the corresponding underlying database for local execution; (3) Obtain the intermediate results of the subquery execution from each underlying database, perform association, sorting, and secondary aggregation calculation on the intermediate results, and generate the final result set; (4) Convert the final result set to a standard format and return it.

[0011] Furthermore, in step S4, before the query decomposition in sub-step (1), query rewriting and optimization based on the global metadata directory and predefined cost model are also included. The connection order of the original query and the execution plan of the subquery are reconstructed to generate a distributed query plan with the lowest estimated execution cost.

[0012] Furthermore, the data lifecycle strategy in S5 includes hot and cold data stratification rules and an automatic archiving mechanism. The hot and cold data stratification rules determine the hot and cold status of data based on the most recent access time, access frequency, and data generation time. The automatic archiving mechanism triggers a data migration task based on the hot and cold status, automatically migrating data blocks determined to be cold data from high-performance storage media to low-cost archive storage.

[0013] Furthermore, S5 also includes updating the storage location and access path of the migrated data in the global metadata directory after the migration task is completed.

[0014] A system for implementing any of the above-mentioned intelligent hybrid storage and collaborative processing methods for multi-source heterogeneous massive data includes: a metadata and lifecycle management module, which integrates multiple underlying databases, maintains a global metadata catalog, has built-in lifecycle policies and schedulers, and defines and executes migration tasks; a unified data access module, which is configured with multiple protocol adapters, data parsers, and a unified semantic data model, and has a message queue buffer; an intelligent routing engine module, which has built-in rule engine, data feature analysis model, and machine learning inference model; a unified query service module, which embeds a standard SQL interface and query parser, and sets up a query caching mechanism; and a collaborative computing engine module, which decomposes, optimizes, pushes down execution, and aggregates results for query requests received by the unified query service module, and connects to the raw data stored in the underlying database through a set memory manager. During storage, the unified data access module receives raw data from different data sources and parses and normalizes it. The intelligent routing engine module determines the optimal storage location for different data sources and then sends it to the corresponding database integrated in the metadata and lifecycle management module for storage. During a query, the unified query service module accepts the query request from the application and then sends it to the collaborative computing engine module to parse and obtain the storage location. For queries involving only a single database, the corresponding data found in the underlying database of the metadata and lifecycle management module is returned directly. For queries involving multiple databases, the query results are aggregated in the collaborative computing engine module before being returned.

[0015] The beneficial effects of this invention are as follows: 1. Unified access interface: By providing a standard SQL interface, the heterogeneity of the underlying physical storage is shielded, greatly reducing the complexity of application development.

[0016] 2. Intelligent storage optimization: Based on data characteristics and access patterns, the system automatically selects the optimal storage engine, giving full play to the strengths of various databases and achieving ultimate write performance, query performance, and storage economy.

[0017] 3. Efficient cross-database queries: Through query decomposition, computation pushdown, and distributed aggregation technologies, transparent cross-database relational queries are achieved, solving the data silo problem.

[0018] 4. Automated lifecycle management: Automatically perform data migration and archiving according to preset strategies to optimize storage resource utilization.

[0019] 5. High scalability: Modular design and microservice architecture enable the system to scale horizontally and adapt to the continuous growth of data volume. Attached Figure Description

[0020] Figure 1 This is a flowchart of the method of the present invention.

[0021] Figure 2 This is an architecture diagram of the system of the present invention. Detailed Implementation

[0022] The present invention will now be described in detail with reference to the accompanying drawings and specific embodiments.

[0023] like Figure 1 As shown, this invention provides an intelligent hybrid storage and collaborative processing method for massive amounts of heterogeneous data from multiple sources, comprising the following steps: S1: Receive raw data from different data sources, extract key fields according to a predefined semantic model to verify data integrity, form metadata and a global metadata directory, and encapsulate the raw data and corresponding metadata into a unified data object; Upon receiving data, the raw data is converted into an internal message format according to the receiving protocol; data integrity is verified by extracting key fields; data attributes in the metadata include data source type and access time.

[0024] The semantic data model includes: Equipment Model: Extract basic information about the equipment, including equipment ID, name, type, geographical location, installation time, etc.

[0025] Measurement point model: Extract the attributes of the measurement points, including measurement point ID, associated device ID, measurement type (temperature, pressure, etc.), unit, and data format.

[0026] Event Model: Extracts the structure of events (such as alarms and status changes), including event ID, event type, trigger time, event description, and associated device ID.

[0027] S2: Based on the different data characteristics of the unified data object, write the data into different underlying databases according to the feature matching rules, and record the data location mapping and storage strategy; Specifically, data features, including data structure, data size, and data generation frequency, are first extracted from the normalized data. Data with strict time-series attributes and a generation frequency at the second level or higher are classified as high-frequency time-series data. Data that can be clearly defined by normalized two-dimensional tables and has strict entity relationships and referential integrity are classified as device metadata and business relationship data. Data that exists as descriptive indexes of massive unstructured files or semi-structured data that needs to be stored in variable schemas are classified as document and file index data.

[0028] Real-time decisions are then made based on different feature matching rules. High-frequency time-series data is routed to a time-series database, device metadata and business relationship data to a relational database, and document and file index data to a document database or object storage. Based on the decision results, the data is written to the underlying database through the corresponding database connector. Simultaneously, the data location mapping and storage strategy are recorded.

[0029] Time-series databases: such as InfluxDB or TimescaleDB; Relational databases: such as MySQL or PostgreSQL; Document databases: such as MongoDB; object storage: such as Amazon S3.

[0030] S3: Receives query requests and performs lexical and syntactic analysis on them, parsing and identifying the data entities, conditions, and operation types involved; verifies the query metadata against the global metadata directory to determine whether the data spans multiple underlying databases.

[0031] The unified query service module provides a standard SQL interface (supporting RESTful API or JDBC / ODBC drivers) to receive query requests from the application layer. The query parser uses an open-source SQL parsing library (such as Apache Calcite) to perform lexical and syntactic analysis on the query, identifying the data entities involved (such as tables and views), conditions (such as WHERE clauses), and operation types (such as SELECT and JOIN). During parsing, query metadata (such as table names and field names) is validated against the global metadata directory to determine if the data spans multiple underlying databases.

[0032] S4: When step S3 determines that the query is for a single database, query directly and return the result; when it is a cross-database query across multiple underlying databases, execute the following sub-steps: (1) Query decomposition: Based on the metadata directory, the original query is decomposed into multiple subqueries.

[0033] For example, a query that requests "the temperature data of device A in the most recent hour and its device information" can be broken down into: Subquery 1: Query the temperature data of device A in the time series database (time range filtering).

[0034] Subquery 2: Query the metadata of device A in the relational database.

[0035] (2) Calculation pushdown: Push down pushable operations (such as filtering and aggregation) to the underlying database for execution.

[0036] For example, time range filtering can be pushed down to a time series database, and device ID filtering can be pushed down to a relational database to reduce the amount of data transmitted over the network.

[0037] (3) Result aggregation and merging: After the intermediate results of the subquery are obtained through the data connector, they are subjected to association calculations (such as JOIN operations), sorting, or secondary aggregation in the memory of the collaborative computing engine. The engine uses a distributed computing framework (such as Apache Spark) to handle large data volume scenarios. For example, a hash join algorithm is used to associate device IDs to generate the final result set.

[0038] (4) Result return: The final result set is converted into a standard format and returned (such as JSON or a table).

[0039] Before executing query decomposition, query rewriting and optimization are performed. The query rewriting and optimization are based on the data distribution statistics recorded in the global metadata directory and the predefined cost model. The connection order of the original query and the execution plan of the subqueries are reconstructed to generate a distributed query plan with the lowest estimated execution cost.

[0040] S5: Monitors the storage load status and data access patterns of each underlying database in real time, and automatically performs data migration and archiving operations according to predefined data lifecycle strategies.

[0041] The data lifecycle strategy configuration includes hot and cold data stratification rules and an automatic archiving mechanism. The hot and cold data stratification rules determine the hot and cold status of data based on the most recent access time, access frequency, and data generation time. The automatic archiving mechanism triggers a data migration task according to the hot and cold status, automatically migrating data blocks determined to be cold data from high-performance storage media to low-cost archive storage, and updating the storage location and access path of the data block in the metadata directory after the migration is completed.

[0042] For example, time-series data can be automatically migrated to a low-cost object storage (such as S3 Glacier) after being retained for 30 days.

[0043] Historical data will be automatically deleted after one year.

[0044] like Figure 2 As shown, this invention also provides an intelligent hybrid storage and collaborative processing system for multi-source heterogeneous massive data, comprising: The metadata and lifecycle management module integrates multiple underlying databases, maintains a global metadata catalog, and has built-in lifecycle policies and schedulers to define and execute migration tasks. Specifically, the metadata catalog is stored in a relational database and provides CRUD interfaces for other modules to access.

[0045] The lifecycle manager uses a workflow engine (such as Apache Airflow) to define and execute migration tasks, supporting both manual triggering and automatic scheduling.

[0046] It integrates with the underlying database and performs data migration and cleanup operations via API calls.

[0047] The unified data access module is equipped with multiple protocol adapters, a data parser, and a unified semantic data model, and has a message queue buffer to receive data from different data sources. Specifically, various protocol adapters (such as MQTT, HTTP, CoAP, and Kafka) are used to receive data from sources such as sensors, video surveillance, and business systems. The data parser parses and normalizes the raw data based on a predefined unified semantic data model (defined using JSON Schema or XML Schema). It is developed using Java or Go and deployed as a scalable microservice. Protocol adapters support pluggability, dynamically loading based on the data source type. The data parser configuration is templated, allowing for custom parsing rules.

[0048] Use message queues (such as Kafka) as data buffers to handle data spikes.

[0049] For example, when an MQTT message is received, the protocol adapter converts it into an internal message format. The data parser extracts key fields (such as timestamp, device ID, and measurement point value) based on the device model and measurement point model, and verifies data integrity. The normalized data is encapsulated into a unified data object (such as JSON format) and appended with metadata (such as data source type and access time), and then sent to the intelligent routing engine module.

[0050] The intelligent routing engine module, which incorporates a rule engine, a data feature analysis model, and a machine learning inference model, determines the optimal storage location for data received by the unified data access module and stores it in the corresponding database integrated in the metadata and lifecycle management module. Specifically, the rule engine uses an open-source framework (such as Drools) or a self-developed engine, and the rule files are stored in a configuration center (such as Zookeeper).

[0051] The data feature analysis model is trained and inferred using machine learning libraries implemented in Python or Java (such as Scikit-learn), and the model is updated regularly. It interacts with metadata to obtain real-time storage status and policies.

[0052] The unified query service module has an embedded standard SQL interface and query parser, sets up a query caching mechanism, and receives query requests from the application. It provides a standard SQL interface (supporting RESTful API or JDBC / ODBC drivers) to receive query requests from the application layer. The query parser uses an open-source SQL parsing library (such as Apache Calcite) to perform lexical and syntactic analysis on the query, identifying the data entities involved (such as tables and views), conditions (such as WHERE clauses), and operation types (such as SELECT and JOIN). During parsing, query metadata (such as table names and field names) is validated against the global metadata directory to determine if the data spans multiple underlying databases.

[0053] The collaborative computing engine module decomposes, optimizes, pushes down execution, and aggregates results for query requests received by the unified query service module. It has a built-in query optimizer and result aggregator and connects to the raw data stored in the underlying database through a set memory manager to achieve cross-database queries.

[0054] Specifically, the core components are developed using Scala or Java, and distributed computing capabilities are built on Apache Spark. The query optimizer employs a cost-based optimization (CBO) strategy, taking into account data distribution and network latency. The result aggregator uses in-memory management (such as Redis) to temporarily store intermediate results and supports streaming processing.

[0055] The metadata and lifecycle management module integrates with the underlying database, maintains a global metadata catalog, and defines and executes migration tasks.

[0056] The metadata catalog is stored in a relational database and provides CRUD interfaces for other modules to access.

[0057] The lifecycle manager uses a workflow engine (such as Apache Airflow) to define and execute migration tasks, supporting both manual triggering and automatic scheduling.

[0058] It integrates with the underlying database and performs data migration and cleanup operations via API calls.

[0059] During storage, the unified data access module receives raw data from different data sources and parses and normalizes it. The intelligent routing engine module determines the optimal storage location for different data sources and then sends it to the corresponding database integrated in the metadata and lifecycle management module for storage. During a query, the unified query service module accepts the query request from the application and then sends it to the collaborative computing engine module to parse and obtain the storage location. For queries involving only a single database, the corresponding data found in the underlying database of the metadata and lifecycle management module is returned directly. For queries involving multiple databases, the query results are aggregated in the collaborative computing engine module before being returned.

[0060] The above description is merely a preferred embodiment. Those skilled in the art will understand that different database types, development frameworks, methods, and rule definitions can be selected based on different data sources and types. Any adaptive adjustments made to specific implementations without departing from the principles of this invention should fall within the protection scope of this invention.

Claims

1. A method for intelligent hybrid storage and collaborative processing of massive multi-source heterogeneous data, characterized in that, Includes the following steps: S1. Receive raw data from different data sources, extract key fields according to a predefined semantic model to verify data integrity, form metadata and a global metadata directory, and encapsulate the raw data and corresponding metadata into a unified data object; S2. Based on the different data characteristics of the unified data object, write the data into different underlying databases according to the feature matching rules, and record the data location mapping and storage strategy; S3. Receive query requests and perform lexical and syntactic analysis on the query requests to identify the data entities, conditions and operation types involved, and determine whether the query request is a single-database query or a cross-database query. S4. When the query request is a single-database query, the stored data in the corresponding underlying database is returned directly; when it is determined in S3 that the query is a cross-database query, the stored data in the corresponding underlying databases are integrated to form a unified result set and then returned. S5. Monitors the storage load status and data access patterns of each underlying database in real time, and automatically performs data migration and archiving operations according to predefined data lifecycle strategies.

2. The intelligent hybrid storage and collaborative processing method for multi-source heterogeneous massive data according to claim 1, characterized in that: The predefined semantic model in S1 includes a device model, a measurement point model, and an event model. The device model is used to extract basic information about the device, the measurement point model is used to extract the attributes of specific measurement points, and the event model is used to extract the structure of events.

3. The intelligent hybrid storage and collaborative processing method for multi-source heterogeneous massive data according to claim 1, characterized in that: The different data characteristics in S2 include data structure, data size, and data generation frequency. Data with strict time series attributes and a generation frequency of seconds or higher are classified as high-frequency time series data; data that can be clearly defined by a standardized two-dimensional table and has strict entity relationships and referential integrity are classified as device metadata and business relationship data; and data that exists as descriptive indexes of massive unstructured files or semi-structured data that needs to be stored in variable modes are classified as document and file index data.

4. The intelligent hybrid storage and collaborative processing method for multi-source heterogeneous massive data according to claim 3, characterized in that: The feature matching rules in S2 are as follows: high-frequency time-series data is routed to a time-series database, device metadata and business relationship data are routed to a relational database, and document and file index data are routed to a document database or object storage.

5. The intelligent hybrid storage and collaborative processing method for multi-source heterogeneous massive data according to claim 1, characterized in that: The method for determining whether a specific query request is a single-database query or a cross-database query in S3 is as follows: verify the query metadata and the global metadata directory to obtain the number of underlying databases involved.

6. The intelligent hybrid storage and collaborative processing method for multi-source heterogeneous massive data according to claim 1, characterized in that: In S4, when performing cross-database queries, the following sub-steps are specifically executed: (1) Decompose the original query into multiple subqueries, each subquery targeting a specific underlying database; (2) Push the filtering and aggregation operations down to the corresponding underlying database for local execution; (3) Obtain the intermediate results of the subquery execution from each underlying database, perform association, sorting, and secondary aggregation calculation on the intermediate results, and generate the final result set; (4) Convert the final result set to a standard format and return it.

7. The intelligent hybrid storage and collaborative processing method for multi-source heterogeneous massive data according to claim 6, characterized in that: Before the query decomposition in sub-step (1) in S4, query rewriting and optimization based on the global metadata directory and predefined cost model are also included. The connection order of the original query and the execution plan of the subquery are reconstructed to generate a distributed query plan with the lowest estimated execution cost.

8. The intelligent hybrid storage and collaborative processing method for multi-source heterogeneous massive data according to claim 1, characterized in that: The data lifecycle strategy in S5 includes hot and cold data stratification rules and an automatic archiving mechanism. The hot and cold data stratification rules determine the hot and cold status of data based on the most recent access time, access frequency, and data generation time. The automatic archiving mechanism triggers a data migration task based on the hot and cold status, automatically migrating data blocks determined to be cold data from high-performance storage media to low-cost archive storage.

9. The intelligent hybrid storage and collaborative processing method for multi-source heterogeneous massive data according to claim 8, characterized in that: S5 also includes updating the storage location and access path of the migrated data in the global metadata directory after the migration task is completed.

10. A system for implementing the intelligent hybrid storage and collaborative processing method for multi-source heterogeneous massive data according to any one of claims 1-9, characterized in that, include: The metadata and lifecycle management module integrates multiple underlying databases, maintains a global metadata catalog, and has built-in lifecycle policies and schedulers to define and execute migration tasks. The unified data access module is configured with multiple protocol adapters, a data parser, and a unified semantic data model, and is equipped with a message queue buffer; the intelligent routing engine module has a built-in rule engine, data feature analysis model, and machine learning inference model; the unified query service module has an embedded standard SQL interface and query parser, and is equipped with a query caching mechanism; the collaborative computing engine module decomposes, optimizes, pushes down execution, and aggregates the query requests received by the unified query service module, and connects to the raw data stored in the underlying database through a set memory manager; During storage, the unified data access module receives raw data from different data sources and parses and normalizes it. The intelligent routing engine module determines the optimal storage location for different data sources and then sends it to the corresponding database integrated in the metadata and lifecycle management module for storage. During a query, the unified query service module accepts the query request from the application and then sends it to the collaborative computing engine module to parse and obtain the storage location. For queries involving only a single database, the corresponding data found in the underlying database of the metadata and lifecycle management module is returned directly. For queries involving multiple databases, the query results are aggregated in the collaborative computing engine module before being returned.

Images