A database construction method and system for multi-source big data collection and preprocessing

Abstract

The application discloses a kind of multi-source big data acquisition and pre-processing database construction method and system, it is related to database technical field, the present application completes multi-source heterogeneous data acquisition using edge-center two-level distributed architecture, and the security of data transmission is guaranteed by transmission encryption and integrity check mechanism, hierarchical pre-processing is carried out to data to realize heterogeneous data semantic alignment and multidimensional fusion;Distributed hybrid storage architecture is constructed, adaptive database index mechanism is set, and through multidimensional data quality evaluation, the incremental update and full-amount synchronization of database are realized.The present application can realize the unified collection of various source data, standardized processing, effectively break the data island barrier, significantly improve data processing efficiency and storage running performance, and stably guarantee the overall data quality and data consistency of database.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to a database construction method and system for multi-source big data acquisition and preprocessing. Background Technology

[0002] With the deepening of digital transformation, the amount of data generated by information systems across various industries is growing exponentially, and the data sources are becoming increasingly diversified, encompassing multiple channels such as relational databases, non-relational databases, file systems, API interfaces, and IoT devices. This has formed a multi-source heterogeneous data environment containing structured, semi-structured, and unstructured data. Traditional data acquisition often adopts a point-to-point, single-point acquisition method, developing independent acquisition programs for different data sources. This results in long development cycles, high maintenance costs, and a lack of unified task scheduling and operation monitoring mechanisms, making it difficult to cope with the needs of large-scale distributed data acquisition. Real-time streaming data generated by IoT devices is characterized by high concurrency and low latency. Traditional centralized acquisition architectures cannot effectively handle massive amounts of real-time data, easily leading to data loss, transmission delays, and system overload. At the same time, the lack of robust security protection and integrity verification mechanisms during data transmission poses risks of data leakage, tampering, and loss.

[0003] Existing data preprocessing methods are mostly batch processing with fixed workflows, making it difficult to adapt to the personalized processing needs of different types of data. Data cleaning rules rely on manual experience and cannot automatically identify and handle new types of anomalous and noisy data. Severe semantic heterogeneity exists between different data sources; the same entity may use different naming conventions, data formats, and attribute definitions in different data sources, making it impossible to directly link and integrate data and form a globally unified data view. Data conflict resolution mechanisms are relatively simple, often employing timestamp-first or fixed data source-first strategies, lacking scientific conflict quantification assessment and intelligent solutions. When different data sources describe the same entity differently, it is impossible to accurately determine the authenticity and credibility of the data. Sensitive data anonymization is not standardized, with issues of over-anonymization or under-anonymization. The data processing process lacks complete traceability records, making it impossible to trace the source and processing history of data, and failing to meet data security compliance requirements.

[0004] Traditional databases often employ a single storage architecture, which cannot simultaneously meet the high-concurrency transaction processing requirements of structured data and the massive storage needs of unstructured data. Mixed storage of hot and cold data leads to low storage resource utilization and poor query performance. Indexing mechanisms are mostly statically designed, requiring database administrators to manually create and maintain indexes based on experience. This prevents dynamic adjustments to the index structure based on changes in data access patterns, easily resulting in index redundancy or missing indexes, impacting database query efficiency and operational performance. Data updates often rely on full synchronization, a process that consumes significant system resources, has a long synchronization cycle, and cannot guarantee data real-time performance and consistency. Furthermore, the lack of a comprehensive, multi-dimensional data quality assessment system makes it impossible to fully quantify data integrity, accuracy, consistency, and timeliness, hindering the identification and repair of low-quality data and impacting the effectiveness of subsequent data analysis and business applications. Summary of the Invention

[0005] This invention proposes a database construction method and system for multi-source big data acquisition and preprocessing to solve the problems mentioned in the prior art.

[0006] To achieve the above objectives, the present invention adopts the following technical solution: a database construction method for multi-source big data acquisition and preprocessing, comprising the following steps: Collect multi-source heterogeneous data, covering structured data, semi-structured data and unstructured data. It adopts an edge-center two-level distributed collection architecture to obtain raw data from relational databases, non-relational databases, file systems, API interfaces and IoT devices. All collected data adopts transmission encryption and MD5 integrity verification mechanism. Perform hierarchical data preprocessing, including data cleaning, data transformation, data standardization, and data desensitization, to remove invalid, duplicate, and noisy data, unify data format and coding standards, and perform irreversible hashing and masking desensitization on sensitive data. To achieve semantic alignment and multi-dimensional fusion of heterogeneous data, a unified data metadata standard based on a resource description framework is established, entities and attributes from different data sources are associated, semantic heterogeneity is eliminated, and a unified data view after fusion is generated. Construct a distributed hybrid storage architecture, storing data in row-based databases, column-based databases, and object storage systems according to data type and access frequency, to achieve layered storage and dynamic migration of hot and cold data; Establish an adaptive database indexing mechanism that automatically generates and optimizes the index structure based on data access patterns and query frequency, and supports dynamic adjustment of full-text indexes, composite indexes, and spatiotemporal indexes; Perform multi-dimensional data quality assessment, quantitatively evaluate the data from four dimensions: completeness, accuracy, consistency, and timeliness, generate a data quality report, and mark low-quality data; To achieve incremental updates and full synchronization of the database, a data change capture mechanism combining log parsing and triggers is established to synchronize changed data from the data source in real time and perform full data verification and consistency repair on a regular basis.

[0007] Furthermore, it also includes a heterogeneous data semantic alignment mechanism, constructing a semantic knowledge base that includes domain ontology and knowledge graph, extracting entity features and attribute features from different data sources, using a multi-head attention mechanism to calculate semantic similarity between entities, and identifying the same entity and related entities across data sources. ; in, For entities With entity semantic similarity, For the first Attention weight coefficients for each feature dimension For entities In the Feature vectors with 1 feature dimension For entities In the Feature vectors with 1 feature dimension (·) is the cosine similarity function. When the semantic similarity is higher than the set threshold, it is determined to be the same entity. It automatically completes entity alignment and attribute merging, and retains the attribute source information of different data sources.

[0008] Furthermore, it includes incremental data synchronization and conflict resolution mechanisms, employing a combination of log parsing and triggers to capture data change events, recording the timestamps, operation types, and change content of data changes, establishing a distributed conflict detection and resolution framework, and calculating the conflict degree of the same data from different data sources. ; in, For data With data The degree of conflict For time weighting coefficients, For data The last update timestamp For data The last update timestamp The maximum time difference threshold is set. This refers to the attribute weight coefficient. The number of conflicting attributes. This represents the total number of attributes. This is the credibility weighting coefficient. For data source Historical credibility score For data source Historical credibility score.

[0009] Furthermore, the multi-source heterogeneous data acquisition process is as follows: a lightweight acquisition agent is deployed at the edge node to complete real-time data acquisition and preliminary filtering; a distributed acquisition cluster is deployed at the central node; multi-threaded parallel acquisition technology is used to process data from relational databases, non-relational databases, and RESTful API interfaces; corresponding acquisition frequencies, concurrency, and timeout times are configured for different data sources; the acquired data undergoes integrity verification before transmission; the transmission process is encrypted using the TLS 1.3 protocol; when acquisition fails, it is automatically retried according to an exponential backoff strategy; when the number of retries exceeds a set threshold, email and SMS alarms are triggered.

[0010] Furthermore, the layered data preprocessing process is as follows: a four-layer pipeline architecture consisting of a raw data layer, a cleaned data layer, a standard data layer, and a fused data layer is constructed. The raw data layer stores the original collected data and retains traceability information. The cleaned data layer removes invalid, duplicate, and abnormal data and completes missing data. The standard data layer unifies the data format, data type, and character encoding, and completes the standardization conversion of various types of data. The fused data layer configures a globally unique identifier for the desensitized data and builds a data traceability chain, recording the entire process processing information.

[0011] Furthermore, the heterogeneous data semantic alignment and multi-dimensional fusion process is as follows: define a unified metadata model containing three core elements: entity, attribute, and relationship; construct an ontology library to standardize data semantics covering the entire business domain; use entity linking technology to map entities from different data sources to standard entities in the knowledge graph; and store the relationships between entities through a graph database to form a structured unified data view.

[0012] Furthermore, the adaptive database index building process is as follows: real-time collection of database query logs, execution plans and performance indicators, statistical analysis of the usage of various fields, generation of optimal index combinations using deep reinforcement learning algorithms, periodic evaluation of index usage-related performance indicators, automatic cleanup of low-usage redundant indexes, dynamic optimization of composite index structure, execution of index updates using asynchronous batch methods, and support for online index reconstruction and partition management.

[0013] Furthermore, a database construction system for multi-source big data acquisition and preprocessing includes the following modules: The multi-source data acquisition module collects raw data from various heterogeneous data sources, supports multiple data transmission protocols and acquisition modes, and provides visual configuration, intelligent scheduling and real-time monitoring functions for acquisition tasks; The layered data preprocessing module performs data cleaning, transformation, standardization, and de-identification operations. It provides a drag-and-drop rule configuration interface and supports custom preprocessing scripts and third-party plugin extensions. The heterogeneous data fusion module enables semantic alignment and multi-dimensional fusion of heterogeneous data, builds a unified metadata model and domain semantic knowledge base, and generates a globally unified data view. The distributed data storage module constructs a hybrid storage architecture that combines row-based, column-based, and object-based storage to achieve distributed storage of multiple data types and automatic hierarchical migration of hot and cold data. The adaptive index building module automatically generates and optimizes database indexes based on data access patterns, and supports dynamic adjustment and performance evaluation of various index types. The data quality assessment module quantifies data quality from multiple dimensions, generates a visual data quality report, marks low-quality data, and provides intelligent repair suggestions. The incremental update and synchronization module captures changes in the data source in real time, enabling incremental updates and full synchronization of the database, and providing distributed data conflict detection and automatic resolution functions.

[0014] Furthermore, the multi-source data acquisition module adopts a microservice architecture design, with each data source type corresponding to an independent acquisition microservice instance, providing a unified task scheduling center, supporting task priority configuration and resource isolation, integrating a visual acquisition monitoring panel to display the running status, data throughput, latency and failure rate of acquisition tasks in real time, supporting full-text search and export of acquisition logs, and providing automatic diagnosis and fault self-healing functions for acquisition anomalies.

[0015] Furthermore, the data quality assessment module establishes an extensible quality assessment rule base, supports custom quality assessment indicators, calculation logic, and alarm thresholds, and adopts a combination of automated batch detection and sampling manual review for data quality detection. It statistically analyzes the distribution, causes, and impact range of low-quality data, establishes a data quality closed-loop optimization mechanism, and automatically feeds back the quality assessment results to the data collection and preprocessing stages, dynamically adjusting the collection strategy, cleaning rules, and transformation logic.

[0016] Compared with existing technologies, the beneficial effects of this invention are: This invention employs a two-tiered distributed acquisition architecture (edge-center) to achieve unified acquisition of multiple heterogeneous data sources. It supports various modes including timed acquisition, event-triggered acquisition, and real-time streaming acquisition, adapting to the acquisition needs of different data sources and improving the efficiency and stability of data acquisition. Edge nodes perform preliminary filtering and preprocessing of data, reducing the computational burden and network bandwidth consumption of the central node, and meeting the low-latency acquisition requirements of real-time streaming data for the Internet of Things (IoT). The data transmission process utilizes encryption and integrity verification mechanisms to ensure the security and integrity of data transmission, preventing data leakage or tampering during transmission.

[0017] This invention constructs a four-layer pipeline-style data preprocessing architecture, sequentially completing data cleaning, transformation, standardization, and de-identification operations. It employs a combination of a rule engine and machine learning models to identify abnormal and missing data, improving the automation and effectiveness of data preprocessing. It unifies data formats, data types, and character encoding standards, and performs irreversible hashing and masking de-identification on sensitive data to protect user privacy. A complete data traceability chain is established, recording the entire data processing information from collection to fusion, achieving full lifecycle traceability of data and meeting data security compliance requirements.

[0018] This invention achieves semantic alignment and multi-dimensional fusion of heterogeneous data, constructs a unified metadata model and domain semantic knowledge base, effectively solves the semantic heterogeneity problem between different data sources, and forms a globally unified data view. A distributed data conflict detection and resolution framework is established, comprehensively considering time factors, attribute differences, and the credibility of data sources to quantify data conflict levels, automatically selecting the optimal data or triggering manual review, thereby improving the accuracy and consistency of data fusion.

[0019] This invention employs a distributed hybrid storage architecture, storing data in row-based databases, column-based databases, and object storage systems according to data type and access frequency. This enables tiered storage and dynamic migration of hot and cold data, optimizes storage resource allocation, and improves data access performance. An adaptive database indexing mechanism is established, automatically generating and optimizing the index structure based on data access patterns. It supports dynamic adjustments to various index types, reducing manual maintenance costs and improving database query efficiency. Incremental data synchronization and full data verification are implemented, capturing changes in data from the data source in real time to ensure data consistency between the database and the data source, reducing the impact of synchronization on system performance. A comprehensive multi-dimensional data quality assessment system is established, fully quantifying data quality, marking low-quality data and providing repair suggestions, forming a closed-loop data quality optimization mechanism to continuously improve the overall data quality of the database. Attached Figure Description

[0020] Figure 1 A flowchart for constructing a multi-source big data acquisition and preprocessing database; Figure 2 This is a diagram of a two-tier distributed data acquisition architecture, consisting of an edge-center architecture. Figure 3 A flowchart of a four-level data preprocessing pipeline; Figure 4 A flowchart for semantic alignment and multi-dimensional fusion of heterogeneous data; Figure 5 Diagram of distributed hybrid storage and adaptive indexing mechanism. Detailed Implementation

[0021] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0022] In the description of this invention, it should be understood that the terms "center," "longitudinal," "lateral," "length," "width," "thickness," "upper," "lower," "front," "rear," "left," "right," "vertical," "horizontal," "top," "bottom," "inner," "outer," "clockwise," and "counterclockwise," etc., indicate the orientation or positional relationship based on the orientation or positional relationship shown in the accompanying drawings. They are only for the convenience of describing this invention and simplifying the description, and do not indicate or imply that the device or element referred to must have a specific orientation, or be constructed and operated in a specific orientation. Therefore, they should not be construed as limitations on this invention.

[0023] Furthermore, the terms "first" and "second" are used for descriptive purposes only and should not be construed as indicating or implying relative importance or implicitly specifying the number of indicated technical features. Thus, features defined with "first" and "second" may explicitly or implicitly include one or more of the stated features. In the description of this invention, "a plurality of" means two or more, unless otherwise explicitly specified. Furthermore, the terms "installed," "connected," and "linked" should be interpreted broadly; for example, they may refer to a fixed connection, a detachable connection, or an integral connection; they may refer to a mechanical connection or an electrical connection; they may refer to a direct connection or an indirect connection through an intermediate medium; and they may refer to the internal connection of two components. Those skilled in the art can understand the specific meaning of the above terms in this invention based on the specific circumstances. The invention will now be described in further detail with reference to the accompanying drawings.

[0024] Reference Figures 1 to 5 A database construction method for multi-source big data acquisition and preprocessing includes the following steps: Collect multi-source heterogeneous data, covering structured data, semi-structured data and unstructured data. It adopts an edge-center two-level distributed collection architecture to obtain raw data from relational databases, non-relational databases, file systems, API interfaces and IoT devices. All collected data adopts transmission encryption and MD5 integrity verification mechanism. Perform hierarchical data preprocessing, including data cleaning, data transformation, data standardization, and data desensitization, to remove invalid, duplicate, and noisy data, unify data format and coding standards, and perform irreversible hashing and masking desensitization on sensitive data. To achieve semantic alignment and multi-dimensional fusion of heterogeneous data, a unified data metadata standard based on a resource description framework is established, entities and attributes from different data sources are associated, semantic heterogeneity is eliminated, and a unified data view after fusion is generated. Construct a distributed hybrid storage architecture, storing data in row-based databases, column-based databases, and object storage systems according to data type and access frequency, to achieve layered storage and dynamic migration of hot and cold data; Establish an adaptive database indexing mechanism that automatically generates and optimizes the index structure based on data access patterns and query frequency, and supports dynamic adjustment of full-text indexes, composite indexes, and spatiotemporal indexes; Perform multi-dimensional data quality assessment, quantitatively evaluate the data from four dimensions: completeness, accuracy, consistency, and timeliness, generate a data quality report, and mark low-quality data; To achieve incremental updates and full synchronization of the database, a data change capture mechanism combining log parsing and triggers is established to synchronize changed data from the data source in real time and perform full data verification and consistency repair periodically to ensure data consistency between the database and the data source.

[0025] This invention also includes a heterogeneous data semantic alignment mechanism, which constructs a semantic knowledge base containing domain ontology and knowledge graph, extracts entity features and attribute features from different data sources, uses a multi-head attention mechanism to calculate semantic similarity between entities, and identifies the same entity and related entities across data sources. ; in, For entities With entity Semantic similarity, dimensionless; For the first Attention weight coefficients for each feature dimension, dimensionless, ranging from 0 to 1, with core attribute features having higher weights than non-core attribute features. For entities In the Feature vectors with 1 feature dimension; For entities In the Feature vectors with 1 feature dimension; (·) is the cosine similarity function, which is dimensionless. When the semantic similarity is higher than the set threshold, it is determined to be the same entity. It automatically completes entity alignment and attribute merging, and retains the attribute source information of different data sources.

[0026] This invention also includes an incremental data synchronization and conflict resolution mechanism. It employs a combination of log parsing and triggers to capture data change events, recording the timestamp, operation type, and change content of the data changes. A distributed conflict detection and resolution framework is established to calculate the conflict degree of the same data from different data sources. ; in, For data With data The degree of conflict is dimensionless and ranges from 0 to 1; The time weighting coefficient is dimensionless. For data The last update timestamp; For data The last update timestamp; The maximum time difference threshold is set. The attribute weight coefficient is dimensionless. The number of conflicting attributes; This represents the total number of attributes. This is a confidence weighting coefficient, dimensionless. For data source The historical credibility score is dimensionless and ranges from 0 to 1. For data source The historical reliability score is dimensionless, ranging from 0 to 1, and Based on the degree of conflict, the system automatically selects the latest data, highly credible data, or triggers a manual review process.

[0027] In this invention, the collection of multi-source heterogeneous data specifically involves deploying a lightweight collection agent at edge nodes to complete real-time collection and preliminary filtering of IoT device data and local file data, supporting incremental reading of IoT protocols such as MQTT and CoAP and local files; deploying a distributed collection cluster at the central node, employing multi-threaded parallel collection technology to process data from relational databases, non-relational databases, and RESTful API interfaces, supporting three modes: scheduled collection, event-triggered collection, and real-time streaming collection; configuring corresponding collection frequencies, concurrency, and timeout times for different data sources; performing integrity verification on all collected data before transmission; encrypting the transmission process using the TLS 1.3 protocol; automatically retrying according to an exponential backoff strategy when collection fails; triggering email and SMS alarms when the number of retries exceeds a set threshold; and supporting breakpoint resumption and pause recovery of collection tasks.

[0028] In this invention, the layered data preprocessing specifically involves constructing a four-layer pipeline architecture comprising a raw data layer, a cleaned data layer, a standard data layer, and a fused data layer. The raw data layer stores all collected data in its original format and retains complete traceability information. The cleaned data layer identifies and removes invalid data, duplicate data, and outliers using a rule engine and an isolated forest model, and completes missing data using linear interpolation and multiple interpolation methods. The standard data layer unifies data formats, data types, and character encodings, converting date, numerical, and text data of different formats into international standard formats. The fused data layer assigns a unique global identifier to the desensitized data, establishes a data traceability chain, and records each step of data processing and operator information from collection to fusion.

[0029] In this invention, the implementation of heterogeneous data semantic alignment and multi-dimensional fusion specifically involves defining a unified metadata model containing three core elements: entities, attributes, and relationships; constructing an ontology library to standardize data semantics across the entire business domain; using entity linking technology to map entities from different data sources to standard entities in a knowledge graph; storing the relationships between entities through a graph database to form a structured unified data view; supporting multi-source merging and version management of entity attributes; retaining the source data source and update time of each attribute value to achieve end-to-end data traceability; and supporting multi-dimensional combined queries by entity name, attribute value, and relationship to meet the data needs of different scenarios such as data analysis, business applications, and decision support.

[0030] In this invention, the adaptive database indexing mechanism specifically involves: real-time collection of database query logs, execution plans, and performance metrics; statistical analysis of query frequency, filtering conditions, sorting methods, and range query ratios for different fields; automatic generation of optimal index combinations using deep reinforcement learning algorithms; support for mixed deployment of B+ tree indexes, hash indexes, inverted indexes, and spatiotemporal indexes; periodic evaluation of index hit rate, query speedup ratio, and storage overhead; automatic deletion of redundant indexes with usage rates below a set threshold; dynamic adjustment of the field order and index granularity of composite indexes; asynchronous batch execution of index updates to avoid affecting normal database read and write operations; and support for online index reconstruction and partition management.

[0031] This invention includes the following modules: The multi-source data acquisition module is used to collect raw data from various heterogeneous data sources. It supports multiple data transmission protocols and acquisition modes, and provides visual configuration, intelligent scheduling and real-time monitoring functions for acquisition tasks. The layered data preprocessing module is used to perform data cleaning, transformation, standardization and de-identification operations. It provides a drag-and-drop rule configuration interface and supports custom preprocessing scripts and third-party plugin extensions. The heterogeneous data fusion module is used to achieve semantic alignment and multi-dimensional fusion of heterogeneous data, build a unified metadata model and domain semantic knowledge base, and generate a globally unified data view; The distributed data storage module is used to build a hybrid storage architecture that combines row, column and object storage, enabling distributed storage of multiple types of data and automatic hierarchical migration of hot and cold data. The adaptive index building module is used to automatically generate and optimize database indexes based on data access patterns, and supports dynamic adjustment and performance evaluation of various index types. The data quality assessment module is used to quantitatively assess data quality from multiple dimensions, generate visual data quality reports, mark low-quality data, and provide intelligent repair suggestions. The incremental update and synchronization module is used to capture changes in data sources in real time, enabling incremental updates and full synchronization of the database, and providing distributed data conflict detection and automatic resolution functions.

[0032] In this invention, the multi-source data acquisition module adopts a microservice architecture design, with each data source type corresponding to an independent acquisition microservice instance. It supports horizontal scaling and dynamic containerized deployment of acquisition tasks, provides a unified task scheduling center, achieves millisecond-level precision task scheduling based on a time wheel algorithm, supports task priority configuration and resource isolation, avoids CPU, memory and network resource competition between different acquisition tasks, integrates a visual acquisition monitoring panel, displays the running status, data throughput, latency and failure rate of acquisition tasks in real time, supports full-text search and export of acquisition logs, and provides automatic diagnosis and fault self-healing functions for acquisition anomalies.

[0033] In this invention, the data quality assessment module establishes an extensible quality assessment rule base, supports custom quality assessment indicators, calculation logic, and alarm thresholds, and adopts a combination of automated batch detection and sampling manual review for data quality detection. It generates incremental data quality reports daily and full data quality reports monthly, statistically analyzes the distribution, causes, and impact range of low-quality data, establishes a data quality closed-loop optimization mechanism, automatically feeds back the quality assessment results to the data collection and preprocessing stages, dynamically adjusts the collection strategy, cleaning rules, and transformation logic, and continuously improves the overall data quality of the database.

[0034] The database construction method for multi-source big data acquisition and preprocessing of this invention adopts a two-level distributed architecture of edge-center, with heterogeneous data semantic fusion and adaptive data management as its core. It integrates multi-source unified acquisition, hierarchical pipeline preprocessing, distributed hybrid storage, intelligent index optimization, and end-to-end data quality control technologies to solve the problems of difficulty in fusion of multi-source heterogeneous data, low acquisition efficiency, low storage resource utilization, and difficulty in ensuring data quality in traditional data processing. The system constructs a processing flow covering the entire data lifecycle, realizing unified acquisition, standardized processing, semantic alignment and fusion, and efficient storage management of multiple types of data, forming a high-quality unified data view. It is suitable for the construction of big data platforms in various industries such as smart cities, healthcare, and the industrial internet. The invention is described in detail below through two specific embodiments.

[0035] Example 1: This embodiment applies to the construction of a smart city transportation big data platform. This platform needs to integrate multiple data sources, including traffic checkpoints, geomagnetic detectors, bus dispatch systems, taxi operation systems, traffic signal control systems, meteorological monitoring stations, and traffic police business management systems. The data types include structured vehicle passage records, semi-structured bus dispatch logs, and unstructured surveillance video clips. The data is generated rapidly and in large quantities, requiring high real-time performance and consistency. The system adopts a distributed deployment model, deploying edge nodes at various intersections and bus stations throughout the city, and a central processing cluster at the city data center.

[0036] During the system initialization phase, a multi-source data acquisition architecture is deployed. Lightweight acquisition agents are deployed at each intersection edge node, connecting to traffic checkpoint cameras and geomagnetic detectors via a dedicated network to collect vehicle traffic data, license plate recognition results, and road traffic flow data in real time. Edge nodes locally filter invalid data and merge duplicate data, removing failed license plate recognition information and speed data exceeding normal ranges. At bus depot edge nodes, bus GPS positioning data and vehicle operation status data are collected, using the MQTT protocol for real-time streaming data transmission. The central node deploys a distributed acquisition cluster, connecting to the bus dispatch system, taxi operation system, and meteorological monitoring station via standard API interfaces, and connecting to the traffic police business management system and traffic light control system via database log parsing. All acquisition tasks are managed through a unified dispatch center, configuring different acquisition frequencies based on data importance: traffic status data is collected once per second, and meteorological data every five minutes. Data transmission uses TLS 1.3 encryption, with each data packet adding an MD5 checksum. In case of acquisition failure, automatic retry is performed according to an exponential backoff strategy. A system alarm is triggered if the number of retries exceeds three. The system supports resuming interrupted acquisition tasks and resuming from paused data transmission.

[0037] A layered data preprocessing workflow is implemented, constructing a four-layer pipeline architecture: raw data layer, cleaned data layer, standard data layer, and fused data layer. The raw data layer stores all collected data in its original format, preserving complete collection time, data source identifier, and transmission link information. The cleaned data layer filters malformed data using a rule engine, identifies abnormal vehicle trajectory data using an isolated forest model, and completes missing timestamps and location information using linear interpolation. The standard data layer unifies data formats and encoding standards, converting different date and time formats to the ISO standard format, converting geographical location data from different coordinate systems to the unified WGS84 coordinate system, and standardizing the encoding standards for vehicle type, road grade, and other classification data. The fused data layer performs irreversible hash desensitization processing on sensitive data such as license plate numbers and driver ID numbers, assigns a unique global identifier to each data entry, establishes a data traceability chain, and records each processing operation and processing time from data collection to fusion.

[0038] This project achieves semantic alignment and multi-dimensional fusion of heterogeneous data, constructing a traffic domain ontology library containing core entities such as vehicles, roads, intersections, traffic lights, and weather, defining the relationships and attribute specifications between entities. It extracts entity and attribute features from different data sources, calculates semantic similarity between entities, and identifies the same entity across data sources. For example, it aligns vehicle passage data collected from checkpoints with vehicle trajectory data from taxi operation systems, merging attributes such as passage time, route, and passenger status of the same vehicle, while retaining the source data source and update time for each attribute value. A graph database is used to store the relationships between entities, constructing a vehicle-road-intersection relationship graph to form a globally unified traffic data view, supporting combined queries by vehicle, road, time, and other dimensions.

[0039] A distributed hybrid storage architecture is constructed, with tiered storage based on data type and access frequency. Row-based databases store real-time traffic status data, traffic light control data, and traffic police operational data, meeting the demands of high-concurrency transaction processing. Column-based databases store historical vehicle passage records, bus route trajectories, and traffic statistics, supporting large-scale data analysis and batch queries. An object storage system stores unstructured data such as surveillance video clips and traffic incident images. An automatic hot / cold data migration mechanism is established, automatically migrating historical trajectory data older than 30 days to low-cost archive storage nodes and automatically deleting video data older than one year, optimizing storage resource allocation.

[0040] An adaptive database indexing mechanism is established, collecting query logs and performance metrics from the database in real time to analyze query frequency and filtering conditions for different fields. Deep reinforcement learning algorithms are used to automatically generate optimal index combinations. For high-frequency queries of vehicle passage records by time and intersection, a composite index of timestamps and intersection numbers is automatically generated. For vehicle trajectory queries, a spatiotemporal index is generated. Index hit rates and storage overhead are periodically evaluated, and redundant indexes with a usage rate below 5% for a consecutive month are automatically deleted. Index updates are performed asynchronously in batches, rebuilding indexes during off-peak hours to avoid impacting normal database read and write operations.

[0041] A multi-dimensional data quality assessment is conducted, quantifying the data across four dimensions: completeness, accuracy, consistency, and timeliness. Completeness checks the proportion of missing fields in data records; accuracy checks whether geographical coordinates fall within the city's administrative area; consistency checks whether the passage time of the same vehicle at adjacent checkpoints conforms to normal driving speeds; and timeliness checks the delay between data collection and database entry. Daily incremental data quality reports are generated, marking low-quality data and analyzing their distribution and causes. A closed-loop data quality optimization mechanism is established, feeding back quality assessment results to the data collection and preprocessing stages, dynamically adjusting collection parameters and cleaning rules.

[0042] To achieve incremental updates and full synchronization of the database, a combination of database log parsing and triggers is used to capture data change events from data sources, recording data insertion, update, and deletion operations in real time. A distributed conflict detection and resolution framework is established. When different data sources describe the same entity differently, the conflict level is calculated by comprehensively considering data update time, the number of attribute differences, and the historical reliability of the data sources. For cases with low conflict levels, data from the most recent or most reliable data source is automatically selected; for cases with high conflict levels, a manual review process is triggered. A full data verification is performed every morning at midnight to correct data inconsistencies and ensure data consistency between the central database and all data sources.

[0043] This embodiment achieves efficient and unified acquisition of multi-source traffic data through an edge-center distributed acquisition architecture. Edge node preprocessing effectively reduces the computational burden and network bandwidth consumption of the central node. A four-layer pipeline preprocessing enhances data standardization and processing efficiency, while semantic alignment and fusion resolve the data silo problem between different traffic systems. Distributed hybrid storage and adaptive indexing optimize storage resource configuration and query performance, while multi-dimensional data quality assessment and incremental synchronization mechanisms ensure data accuracy and real-time performance, providing high-quality data support for traffic flow analysis, signal optimization, and emergency command.

[0044] Example 2: This embodiment applies to the construction of a regional healthcare big data platform. This platform needs to integrate data from multiple hospitals within the region, including their HIS, LIS, PACS, electronic medical record systems, public health management systems, and resident health monitoring equipment. The data types include structured test results, semi-structured electronic medical record texts, and unstructured medical imaging data, involving a large amount of sensitive patient information, thus placing extremely high demands on data security, privacy protection, and data quality. The system adopts a private cloud deployment model, deploying front-end data collection nodes in each hospital and a central processing cluster in the regional health data center.

[0045] During the system deployment phase, a multi-source data acquisition system was constructed. Front-end acquisition servers were deployed in each hospital as edge acquisition nodes, connecting to various internal hospital information systems. These front-end nodes connected to the HIS and LIS systems via the HL7 protocol to collect patient registration information, medical records, and laboratory test results; they connected to the PACS system via the DICOM protocol to collect medical imaging data; and they connected to wearable health devices via dedicated interfaces to collect residents' physiological monitoring data such as heart rate and blood pressure. The front-end nodes performed preliminary data filtering locally, removing incorrectly formatted test reports and corrupted image files. The central node deployed a distributed acquisition cluster, connecting to the regional public health management system through a unified data exchange platform to collect public health data such as infectious disease reports and vaccination records. All acquisition tasks were uniformly managed through a central scheduling platform, with acquisition frequency configured according to business needs. Outpatient registration data was collected in real-time, while inpatient medical record data was synchronized in batches daily. Data transmission was encrypted using national cryptographic algorithms, and each data packet was accompanied by an integrity check code. The entire acquisition process was logged, supporting full-text search and auditing of the acquisition logs.

[0046] A layered data preprocessing workflow is implemented, constructing a four-layer pipeline processing architecture. The raw data layer encrypts and stores all collected medical data, preserving complete original records and traceability information to meet compliance requirements for medical data traceability. The cleaned data layer uses a rule engine to identify abnormal test values, filtering test results outside the normal physiological range, and employs multiple imputation methods to complete missing patient basic information and diagnostic data. The standard data layer unifies medical data coding standards, converting disease codes used by different hospitals to ICD-10 standard codes, drug codes to national drug standard codes, and standardizing the representation format of dates, values, and units. The fusion data layer performs irreversible hashing and masking desensitization processing on sensitive information such as patient names, ID numbers, mobile phone numbers, and medical record numbers, assigning a unique global health ID to each resident, establishing a data traceability chain throughout the patient's entire lifecycle, and recording the entire process of data collection, processing, storage, and use.

[0047] This project aims to achieve semantic alignment and multi-dimensional fusion of heterogeneous medical data, constructing a medical domain ontology library containing core entities such as patients, diseases, drugs, examinations, and surgeries, and defining the diagnostic and treatment relationships and attribute standards between entities. It extracts entity features from data from different hospitals, calculates semantic similarity between entities, and achieves cross-hospital patient entity alignment. For example, it merges and links electronic medical records, test results, and medication records of the same patient from different hospitals, solving the data silo problem between different hospital information systems. A graph database is used to store the patient's diagnostic and treatment relationship graph, constructing a resident's full life-cycle health record, forming a unified regional medical and health data view, supporting comprehensive querying and analysis by patient, disease, time, and other dimensions.

[0048] A distributed hybrid storage architecture is constructed, with tiered storage based on the characteristics and access patterns of medical data. Row-based databases store structured data requiring frequent updates and queries, such as basic patient information, registration information, and outpatient records, supporting high-concurrency transaction processing. Column-based databases store large-scale historical data such as test results, medication records, and hospitalization expenses, supporting batch statistical analysis and data mining. The object storage system stores medical imaging data such as CT scans, MRI scans, and pathology slides, supporting fast upload and download of large files. An automatic cold / hot data migration mechanism is established; inactive imaging data older than 6 months is automatically migrated to archive storage nodes, and historical medical data older than 10 years is backed up offline according to compliance requirements, reducing storage costs while ensuring data accessibility.

[0049] An adaptive database indexing mechanism is established, and query logs and performance data from the medical platform are collected in real time to analyze the query characteristics of medical services. For the high-frequency need to query medical records by patient ID, a composite index of the patient's global ID and visit time is automatically generated; for the need for full-text retrieval of electronic medical records, an inverted index is automatically generated; and for the need for tracking public health events, a spatiotemporal index is automatically generated. The efficiency and storage overhead of the indexes are regularly evaluated, and redundant indexes with low usage are automatically deleted, and the field order of composite indexes is dynamically adjusted. Index updates are performed online incrementally, without affecting the normal operation of medical services, ensuring 24 / 7 continuous service of the platform.

[0050] A multi-dimensional medical data quality assessment is conducted, focusing on the accuracy, completeness, and consistency of the data. Accuracy is checked to ensure test results are within reasonable ranges and diagnostic codes conform to standards. Completeness is checked to ensure electronic medical records contain necessary diagnostic and treatment information and that test reports have no missing items. Consistency is checked to ensure that the basic information of the same patient is consistent across different hospitals and that there are no logical inconsistencies between treatment records. Timeliness is checked to assess the delay between data generation at the hospital and synchronization with the central platform. Weekly data quality reports are generated to summarize the data quality of each hospital, mark low-quality data, and provide feedback to the corresponding hospitals for rectification. A data quality assessment mechanism is established, incorporating data quality into the hospital performance evaluation system to continuously improve the overall quality of regional medical data.

[0051] To achieve incremental updates and full synchronization of the database, database log parsing technology is used to capture data change events in each hospital's information system in real time, synchronizing the changed data to the regional central database in near real-time. A distributed data conflict resolution mechanism is established. When different hospitals describe the same attribute of the same patient differently, the conflict level is calculated by comprehensively considering data update time, hospital level, and data type. Priority is given to the diagnosis and treatment data and the most recently updated data from tertiary hospitals. For cases with high conflict levels, the data is submitted to the regional medical data review committee for manual judgment. A full data verification is performed monthly to correct data inconsistencies and ensure the integrity and consistency of regional medical data.

[0052] This embodiment addresses the data characteristics and compliance requirements of the healthcare industry by constructing a secure and reliable multi-source medical data acquisition and processing system. Front-end acquisition nodes ensure secure isolation and preliminary processing of internal hospital data, while sensitive data anonymization protects patient privacy. Semantic alignment and fusion enable interoperability of medical data across hospitals, forming a comprehensive health record for residents throughout their life cycle. Distributed hybrid storage and adaptive indexing meet the needs for massive storage and efficient querying of medical data, while multi-dimensional data quality assessment and incremental synchronization mechanisms ensure the accuracy and real-time nature of medical data, providing a high-quality data foundation for clinical auxiliary diagnosis, public health monitoring, and regional medical management.

[0053] Reference Figure 1This diagram illustrates the overall workflow of building a multi-source big data acquisition and preprocessing database. The system first collects heterogeneous data from different sources using a two-tiered edge and center architecture, and performs integrity verification. The data then enters the preprocessing stage, undergoing cleaning, transformation, standardization, and de-identification to remove invalid information and unify the format. The processed data then enters the semantic alignment and fusion stage, eliminating semantic differences through knowledge graphs and similarity calculations to generate a globally unified view. For storage management, the system employs a hot and cold data tiering mechanism, combining row-based, column-based, and object-based storage, and utilizes artificial intelligence algorithms to automatically optimize database indexes. Finally, the system executes a multi-dimensional data quality assessment and incremental synchronization mechanism, feeding back the quality detection results to the acquisition end, thus forming a complete data processing closed loop to ensure efficient database operation and data consistency.

[0054] Reference Figure 2 This diagram details a distributed, multi-source data acquisition architecture combining edge and central processing. At the edge, a lightweight acquisition agent is deployed, specifically responsible for acquiring data from IoT devices and local files in real time and performing initial filtering. At the central processing center, a distributed cluster utilizes multi-threading technology to concurrently capture data from relational databases, non-relational databases, and application interfaces. Data collected from both ends undergoes rigorous integrity checks before transmission, and advanced encryption protocols ensure network security. After data aggregation, a unified task scheduling center manages the data. The system supports multiple acquisition modes, including scheduled, event-triggered, and real-time streaming, and features intelligent retry, resume interrupted transmission, and anomaly alerting. This microservice architecture effectively isolates computing resources, avoids performance competition between different tasks, and ensures high-concurrency processing capabilities for massive data access.

[0055] Reference Figure 3 This diagram illustrates the four-layer preprocessing pipeline designed for massive amounts of heterogeneous data. The first layer is the raw data layer, primarily responsible for preserving all collected initial data intact and recording complete traceability information. The second layer is the cleaned data layer, where the system uses a rule engine and anomaly detection model to automatically identify and remove invalid, duplicate, and abnormal noise data, while employing interpolation algorithms to fill in missing parts. The third layer is the standard data layer, whose core task is to convert various dates, numerical values, and text data into internationally recognized standards, unifying character encoding and data types. The fourth layer is the fused data layer, where the system performs irreversible desensitization on the cleaned and standardized data, masking and hiding sensitive information, and assigning a globally unique identifier to each data entry. This four-layer architecture, progressively enhancing the standardization and purity of subsequent data entering the database, significantly improves the overall quality and purity of the data.

[0056] Reference Figure 4This diagram illustrates the execution logic of semantic alignment and multi-dimensional fusion of heterogeneous data. Since different data sources often describe the same business entity differently, the system first constructs a domain ontology and knowledge graph covering the entire business domain. During data import, the feature extraction module automatically captures entity and attribute features from each data source. Subsequently, the system uses an attention-based computational mechanism to compare these features, focusing on the matching degree of core attributes. When the semantic similarity between entities exceeds the system's set threshold, the engine automatically identifies them as the same entity and performs a cross-source attribute merging operation. All merged entities and their interconnected network relationships are uniformly stored in the graph database. Through this series of steps, the system completely eliminates semantic discrepancies in the underlying data, ultimately providing upper-layer business applications with a structured and comprehensive data view with end-to-end traceability capabilities.

[0057] Reference Figure 5 This diagram illustrates the system's distributed hybrid storage architecture and adaptive index generation mechanism. In terms of data storage, the system intelligently routes data to different storage media based on the data's business type and historical access frequency. Frequently accessed "hot" data is typically stored in row-oriented or column-oriented databases to ensure extremely fast read / write responses, while rarely accessed "cold" data is dynamically migrated to a low-cost object storage system, achieving a rational allocation of computing and storage resources. Regarding retrieval optimization, the system backend continuously collects database query logs, execution plans, and various performance metrics. Based on this monitoring data, machine learning algorithms can automatically analyze data query patterns and dynamically generate optimal index combinations, including full-text, composite, and spatiotemporal indexes. Simultaneously, this mechanism periodically eliminates redundant indexes with low usage, completing online smooth index reconstruction without affecting normal business read / write operations.

[0058] The above are merely preferred embodiments of the present invention, but the scope of protection of the present invention is not limited thereto. Any equivalent substitutions or modifications made by those skilled in the art within the scope of the technology disclosed in the present invention, based on the technical solution and inventive concept of the present invention, should be covered within the scope of protection of the present invention.

Claims

1. A database construction method for multi-source big data collection and preprocessing, characterized in that, Includes the following steps: Collect multi-source heterogeneous data, covering structured data, semi-structured data and unstructured data. It adopts an edge-center two-level distributed collection architecture to obtain raw data from relational databases, non-relational databases, file systems, API interfaces and IoT devices. All collected data adopts transmission encryption and MD5 integrity verification mechanism. Perform hierarchical data preprocessing, including data cleaning, data transformation, data standardization, and data desensitization, to remove invalid, duplicate, and noisy data, unify data format and coding standards, and perform irreversible hashing and masking desensitization on sensitive data. To achieve semantic alignment and multi-dimensional fusion of heterogeneous data, a unified data metadata standard based on a resource description framework is established, entities and attributes from different data sources are associated, semantic heterogeneity is eliminated, and a unified data view after fusion is generated. Construct a distributed hybrid storage architecture, storing data in row-based databases, column-based databases, and object storage systems according to data type and access frequency, to achieve layered storage and dynamic migration of hot and cold data; Establish an adaptive database indexing mechanism that automatically generates and optimizes the index structure based on data access patterns and query frequency, and supports dynamic adjustment of full-text indexes, composite indexes, and spatiotemporal indexes; Perform multi-dimensional data quality assessment, quantitatively evaluate the data from four dimensions: completeness, accuracy, consistency, and timeliness, generate a data quality report, and mark low-quality data; To achieve incremental updates and full synchronization of the database, a data change capture mechanism combining log parsing and triggers is established to synchronize changed data from the data source in real time and perform full data verification and consistency repair on a regular basis.

2. The database construction method for multi-source big data acquisition and preprocessing according to claim 1, characterized in that, It also includes a heterogeneous data semantic alignment mechanism, constructing a semantic knowledge base that includes domain ontology and knowledge graph, extracting entity features and attribute features from different data sources, using a multi-head attention mechanism to calculate semantic similarity between entities, and identifying the same entity and related entities across data sources. ； in, For entities With entity semantic similarity, For the first Attention weight coefficients for each feature dimension For entities In the Feature vectors with 1 feature dimension For entities In the Feature vectors with 1 feature dimension (·) is the cosine similarity function. When the semantic similarity is higher than the set threshold, it is determined to be the same entity. It automatically completes entity alignment and attribute merging, and retains the attribute source information of different data sources.

3. The database construction method for multi-source big data acquisition and preprocessing according to claim 1, characterized in that, It also includes incremental data synchronization and conflict resolution mechanisms, using a combination of log parsing and triggers to capture data change events, record the timestamps, operation types, and change content of data changes, establish a distributed conflict detection and resolution framework, and calculate the conflict degree of the same data from different data sources. ； in, For data With data The degree of conflict For time weighting coefficients, For data The last update timestamp For data The last update timestamp The maximum time difference threshold is set. This refers to the attribute weight coefficient. The number of conflicting attributes. This represents the total number of attributes. This is the credibility weighting coefficient. For data source Historical credibility score For data source Historical credibility score.

4. The database construction method for multi-source big data acquisition and preprocessing according to claim 1, characterized in that, The multi-source heterogeneous data acquisition process is as follows: a lightweight acquisition agent is deployed at the edge node to complete real-time data acquisition and preliminary filtering; a distributed acquisition cluster is deployed at the central node; multi-threaded parallel acquisition technology is used to process data from relational databases, non-relational databases, and RESTful API interfaces; corresponding acquisition frequency, concurrency, and timeout are configured for different data sources; the acquired data undergoes integrity verification before transmission; the transmission process is encrypted using the TLS 1.3 protocol; when acquisition fails, it is automatically retried according to an exponential backoff strategy; when the number of retries exceeds a set threshold, email and SMS alarms are triggered.

5. The database construction method for multi-source big data acquisition and preprocessing according to claim 1, characterized in that, The layered data preprocessing process is as follows: a four-layer pipeline architecture consisting of a raw data layer, a cleaned data layer, a standard data layer, and a fused data layer is constructed. The raw data layer stores the original collected data and retains traceability information. The cleaned data layer removes invalid, duplicate, and abnormal data and completes missing data. The standard data layer unifies the data format, data type, and character encoding, and completes the standardization conversion of various types of data. The fused data layer configures a globally unique identifier for the de-identified data and builds a data traceability chain, recording the entire process information.

6. The database construction method for multi-source big data acquisition and preprocessing according to claim 1, characterized in that, The heterogeneous data semantic alignment and multi-dimensional fusion process is as follows: define a unified metadata model containing three core elements: entity, attribute, and relationship; construct an ontology library to standardize data semantics covering the entire business domain; use entity linking technology to map entities from different data sources to standard entities in the knowledge graph; and store the relationships between entities through a graph database to form a structured unified data view.

7. The database construction method for multi-source big data acquisition and preprocessing according to claim 1, characterized in that, The adaptive database index building process is as follows: real-time collection of database query logs, execution plans and performance indicators, statistics on the usage of various fields in queries, generation of optimal index combinations using deep reinforcement learning algorithms, periodic evaluation of index usage-related performance indicators, automatic cleaning of low-usage redundant indexes, dynamic optimization of composite index structure, execution of index updates in an asynchronous batch manner, and support for online index reconstruction and partition management.

8. A database construction system for multi-source big data acquisition and preprocessing, applied to the database construction method for multi-source big data acquisition and preprocessing as described in any one of claims 1-7, characterized in that, Includes the following modules: The multi-source data acquisition module collects raw data from various heterogeneous data sources, supports multiple data transmission protocols and acquisition modes, and provides visual configuration, intelligent scheduling and real-time monitoring functions for acquisition tasks; The layered data preprocessing module performs data cleaning, transformation, standardization, and de-identification operations. It provides a drag-and-drop rule configuration interface and supports custom preprocessing scripts and third-party plugin extensions. The heterogeneous data fusion module enables semantic alignment and multi-dimensional fusion of heterogeneous data, builds a unified metadata model and domain semantic knowledge base, and generates a globally unified data view. The distributed data storage module constructs a hybrid storage architecture that combines row-based, column-based, and object-based storage to achieve distributed storage of multiple data types and automatic hierarchical migration of hot and cold data. The adaptive index building module automatically generates and optimizes database indexes based on data access patterns, and supports dynamic adjustment and performance evaluation of various index types. The data quality assessment module quantifies data quality from multiple dimensions, generates a visual data quality report, marks low-quality data, and provides intelligent repair suggestions. The incremental update and synchronization module captures changes in the data source in real time, enabling incremental updates and full synchronization of the database, and providing distributed data conflict detection and automatic resolution functions.

9. A database construction system for multi-source big data acquisition and preprocessing according to claim 8, characterized in that, The multi-source data acquisition module adopts a microservice architecture design, with each data source type corresponding to an independent acquisition microservice instance. It provides a unified task scheduling center, supports task priority configuration and resource isolation, integrates a visual acquisition monitoring panel, displays the running status, data throughput, latency and failure rate of acquisition tasks in real time, supports full-text search and export of acquisition logs, and provides automatic diagnosis and fault self-healing functions for acquisition anomalies.

10. A database construction system for multi-source big data acquisition and preprocessing according to claim 8, characterized in that, The data quality assessment module establishes an extensible quality assessment rule base, supports custom quality assessment indicators, calculation logic and alarm thresholds, and adopts a combination of automated batch detection and sampling manual review for data quality detection. It statistically analyzes the distribution, causes and impact range of low-quality data, establishes a data quality closed-loop optimization mechanism, and automatically feeds back the quality assessment results to the data collection and preprocessing stages, dynamically adjusting the collection strategy, cleaning rules and transformation logic.

Images