Dynamic selective real-time data migration method based on ETL

Abstract

The application discloses a kind of dynamic selective real-time data migration methods based on ETL, it is related to data processing and management technical field, to solve the defects of poor real-time adaptability, insufficient dynamic response, migration delay and the like caused by static binding of existing ETL data migration method.The method uses the closed-loop architecture of strategy driving, metadata perception and real-time feedback regulation, including strategy metadata modeling, real-time data preprocessing, dynamic priority selective extraction, instant conversion logic and adaptive loading and feedback.Through the above scheme, the static coupling is effectively released, the data source mode evolution is automatically adapted, and the data migration efficiency, reliability and agility are significantly improved.

Description

Technical Field

[0001] This invention belongs to the field of data processing and management technology, and specifically relates to a dynamic selective real-time data migration method based on ETL. Background Technology

[0002] As data migration technologies continue to evolve, enterprises and organizations are increasingly demanding efficient, real-time, and precisely selective data migration methods and systems. Current mainstream data migration solutions are mostly based on the ETL (Extract-Transform-Load) process, but they still have significant shortcomings in dynamic adaptability, real-time processing capabilities, and refined selection mechanisms, making it difficult to meet the diverse needs of modern complex business scenarios.

[0003] A search revealed that patent CN108153806B (authorized on October 15, 2021) proposes a method to achieve data format conversion by loading a database migration script and complete the migration by relying on the category method calls before and after the software system upgrade. While this solution achieves basic migration functionality, its over-reliance on predefined scripts leads to a lack of dynamic selectivity, resulting in insufficient flexibility in dealing with real-time changing data requirements. Furthermore, it does not explicitly address the real-time processing and synchronization mechanisms during the migration process, making it difficult to meet the needs of high-concurrency and high-real-time application scenarios.

[0004] Another patent, CN105229634B (authorized on July 23, 2019), achieves data storage migration by constructing a mapping relationship between a data system and a temporary storage table. Although it provides a structured migration framework, its core limitation lies in its focus on static data mapping and storage management, lacking dynamic adjustment capabilities, and failing to establish a selective migration strategy for real-time data. This can easily lead to invalid data migration, unnecessarily increasing system resource consumption and migration time.

[0005] In view of the inherent defects of existing technologies in terms of dynamism, real-time performance and selectivity, this invention proposes a dynamic selective real-time data migration method and system based on ETL. It aims to comprehensively improve the efficiency and flexibility of data migration through dynamic target selection mechanism, real-time data processing synchronization technology and migration path optimization algorithm, so as to meet the practical needs of modern complex business scenarios. Summary of the Invention

[0006] The purpose of this invention is to provide a dynamic selective real-time data migration method based on ETL, which mainly solves the defects of existing ETL-based data migration methods, which cannot adapt to real-time business needs and dynamic data source changes due to the static binding of their migration logic and execution process.

[0007] To achieve the above objectives, the technical solution adopted by the present invention is as follows:

[0008] A dynamic selective real-time data migration method based on ETL includes the following steps:

[0009] S1, pre-build and inject a metadata and strategy knowledge base containing data source metadata and dynamic migration strategy rules;

[0010] S2, through a continuously running data stream monitoring and interception service, intercepts real-time data streams from the data source, encapsulates the intercepted logical data units into data items carrying metadata headers, and uses the metadata and policy knowledge base to annotate the metadata headers of the data items to enhance their business semantics.

[0011] S3, Match the metadata header information of the data item with the dynamic migration strategy rules in the strategy knowledge base, dynamically calculate a comprehensive priority score for the data item, and perform selective extraction and distribution processing on the data item based on the result of comparing the score with a preset priority threshold.

[0012] S4 performs a data pattern consistency check on the selected data items. If the patterns are consistent, the predefined transformation logic is called. If the patterns are inconsistent, the automatic generation and application of real-time transformation logic is triggered to adapt to the evolution of the data source pattern.

[0013] S5. Based on the comprehensive priority score of the data item, select the corresponding loading strategy to load the converted data into the target system, and monitor the loading status to form a closed-loop feedback for dynamically adjusting the migration process.

[0014] Furthermore, in this invention, the specific steps of step S1 are as follows:

[0015] S11, Construct a data source description module that defines and injects data source structure and semantic metadata; wherein, the data source structure and semantic metadata include physical layer information of the data source and a set of predefined semantic tags associated with data fields or data content patterns that characterize business meaning;

[0016] S12, construct and inject the strategy rule engine module that defines and injects dynamic migration strategy rules; each dynamic migration strategy rule consists of four parts: a trigger condition that defines the activation logic premise, a priority calculation function for dynamically calculating the migration priority of data items, a conversion logic pointer that points to the set of conversion operations, and a loading mode instruction that specifies the data loading method.

[0017] Furthermore, in this invention, the physical layer information of the data source includes table name, field name, data type, length, and constraints; the semantic tags are forcibly associated with specific data fields or data content patterns to identify the business value of data items in subsequent steps.

[0018] Furthermore, in step S2, the specific steps for annotating the metadata header of the data item include:

[0019] S21, query the data source description module, match the source table name and field name information of the data item with the data source description in the knowledge base, and write the corresponding structure metadata into its metadata header;

[0020] S22, apply regular expressions or keyword matching algorithms to identify whether there is a content pattern associated with the semantic tag in the data load of the data item. If so, write the corresponding semantic tag into its metadata header.

[0021] Furthermore, the specific implementation process of step S3 is as follows:

[0022] S31, Strategy rule matching and activation: Taking the metadata header information of the data item as input, all dynamic migration strategy rules are traversed in the strategy rule engine module, the triggering conditions of each rule are evaluated, and all activated rules are aggregated into an active strategy set.

[0023] S32, Dynamic calculation of priority score: If no strategy rule is activated, the data item is assigned a preset default low priority score; if there are one or more active strategies, the priority calculation function corresponding to each active strategy is called to calculate one or more priority sub-items for the data item, and the preset aggregation algorithm is used to merge all sub-items to obtain the final comprehensive priority score P.

[0024] S33, Selective extraction and distribution based on priority thresholds: The calculated comprehensive priority score P is compared with a set of preset priority thresholds, and the data items are distributed to different processing queues or modules according to the comparison results.

[0025] Furthermore, the priority calculation function is a multi-factor linear weighted model, and its mathematical expression is:

[0026] P = w c *V c +w t *V t +w s *V s +w d *V d ;

[0027] Where P is the final calculated overall priority score; V c V is the content value factor. t For time sensitivity factor, Among them, t nowt represents the current time. event V represents the time when the data item was generated, where λ is the time decay coefficient; s V is the model novelty factor; d For data dependency factors; w c w t w s w d The weight coefficients of each factor are denoted by 1, and their sum is 1.

[0028] Furthermore, the specific implementation rules for step S33 are as follows:

[0029] If the overall priority score P is higher than a preset high priority threshold, the data item is sent to a high priority processing queue with the minimum processing delay and the highest resource allocation.

[0030] If the overall priority score P is between a preset medium priority threshold and a high priority threshold, then the data item is sent to a medium priority processing queue that allows buffering and merging.

[0031] If the overall priority score P is lower than the medium priority threshold, the data item is sent to a low priority archiving and batch processing module for temporary storage, batch migration, or discarding after timeout.

[0032] Further, in step S4, the processing path for data items entering the high-priority processing queue or the medium-priority processing queue is as follows: pattern consistency verification, extracting the data pattern information of the data item, and comparing it with the latest stable version pattern of the data source recorded in the metadata and policy knowledge base; if the verification result is that the patterns are completely consistent, then proceed to path A: according to the transformation logic pointer in the activity policy set associated with the data item, directly retrieve and call the corresponding transformation function or function chain from a pre-compiled transformation function library to perform transformation operations on the data load of the data item; if the verification result is that the patterns are inconsistent, then proceed to path B: trigger the automatic generation and application of immediate transformation logic.

[0033] Furthermore, in this invention, the automatic generation and application of the instantaneous conversion logic in path B specifically includes the following sub-operations:

[0034] Atomized parsing of difference patterns: The real-time pattern of the data item is compared with the latest stable version pattern to generate a structured difference report describing the addition of fields, deletion of fields, changes in field type, or renaming of fields;

[0035] Logical automatic synthesis based on evolutionary rules: query a built-in pattern evolution rule library that stores a series of "IF-THEN" form rules, match the corresponding evolutionary rules according to the structured difference report, and select and assemble a new transformation function chain from the atomic transformation operation library;

[0036] Compilation, caching, and application of newly generated logic: The newly synthesized transformation function chain is compiled or interpreted on the fly and immediately applied to the current data item; at the same time, the newly generated logic and the new pattern that triggers it are cached together in a dynamic transformation logic cache area, and the pattern benchmark in the metadata and policy knowledge base is updated.

[0037] Furthermore, in step S5, the closed-loop feedback adjustment logic includes at least one of the following:

[0038] Error handling and isolation feedback logic: When a specific type of transformation result is detected to continuously cause loading failure, the transformation logic that generated the result is automatically marked as unstable, and the data items that subsequently use the logic are imported into the exception handling queue for isolation;

[0039] Feedback logic for performance bottleneck adjustment: When the average loading delay of the target system is monitored to continuously exceed the preset threshold, an adjustment signal is automatically sent to the dynamic priority assignment and selective extraction module (300) to temporarily reduce the weight coefficient value of the priority calculation function in all policy rules, thereby dynamically reducing the amount of data flowing into the high and medium priority processing queues.

[0040] Feedback logic for strategy effectiveness evaluation: Long-term statistical analysis of the amount of data activated by different strategy rules, migration success rate, and average latency, and generation of strategy optimization suggestions based on statistical data for offline optimization reference.

[0041] Compared with the prior art, the present invention has the following beneficial effects:

[0042] (1) This invention achieves automatic adaptation to the evolution of data source patterns through a closed-loop architecture driven by strategy and aware of metadata, solving the problem of insufficient real-time response caused by static binding in traditional ETL, and can handle pattern changes such as adding fields and changing field types without manual intervention.

[0043] (2) The dynamic priority selective extraction mechanism of the present invention is based on a multi-factor weighted model (content value, time sensitivity, etc.) to classify and process data items, ensuring that high-value data (such as potential fraudulent transactions and large transactions) are migrated first, reducing end-to-end latency of key business data, and reducing the occupation of system resources by low-value data.

[0044] (3) The closed-loop feedback adjustment logic of the present invention monitors and dynamically adjusts the migration process in real time through error isolation, performance bottleneck adjustment and strategy optimization evaluation, avoids data loss caused by mode change, balances the target system load, and improves the migration success rate and long-term operation stability. Attached Figure Description

[0045] Figure 1 This is a flowchart illustrating the method of the present invention. Detailed Implementation

[0046] The present invention will be further described below with reference to the accompanying drawings and embodiments. The embodiments of the present invention include, but are not limited to, the following embodiments.

[0047] Example

[0048] like Figure 1 As shown, this invention discloses a dynamic selective real-time data migration method based on ETL. Its core lies in constructing a closed-loop processing system that integrates metadata and policy knowledge bases, data stream monitoring and interception services, dynamic priority assignment and selective extraction modules, real-time transformation logic generation and execution modules, and adaptive loading and closed-loop feedback modules. Through deep integration and collaborative work, this system achieves dynamic, intelligent, and adaptive control of the data migration process.

[0049] In one specific embodiment of the present invention, a typical enterprise-level application scenario will be used as an example for illustration. This scenario involves migrating real-time data from a high-concurrency online transaction processing (OLTP) system (source system, using a MySQL 5.7 database) to a cloud-based big data analytics platform (target system, using a Google BigQuery data warehouse) to support real-time business monitoring, fraud detection, and business intelligence analysis.

[0050] First, before executing the data migration task, a metadata and policy knowledge base containing data source metadata and dynamic migration policy rules needs to be pre-built and injected. This step is the foundation of the entire dynamic migration method, providing a structured, machine-readable basis for all subsequent automated decisions. This knowledge base can be physically implemented as a high-performance key-value store (such as Redis) or a document database (such as MongoDB), and internally logically divided into a data source description module and a policy rule engine module.

[0051] Specifically, in this step, deep metadata parsing is performed on the core business tables in the source MySQL database, such as the transactions table and the user_profiles table. For the transactions table, its structured metadata not only includes physical layer information such as field names (e.g., transaction_id, user_id, amount, timestamp, status, payment_gateway_response) and data types (e.g., BIGINT, INT, DECIMAL(18,2), DATETIME, VARCHAR(20), TEXT), but also must be forcibly associated with a set of predefined business semantic tags. For example, the transaction_id field is assigned the tag "CriticalTransactionID"; the amount field is associated with the tag "HighValueTransaction" through a rule (e.g., amount>100000.00); and the payment_gateway_response field is associated with the tag "PotentialFraudIndicator" if its content matches a specific fraud risk keyword regular expression (e.g., .*(risk_score:>0.9|fraud_detected|blacklist_hit).*). All this metadata is formatted into a single JSON document and injected into the data source description module. An example code snippet for the metadata description document of a `transactions` table is shown below:

[0052]

[0053] In this way, the data source description module is not only a static record of the data structure, but also a dynamic knowledge base containing business logic.

[0054] Next, dynamic migration strategy rules are defined and injected into the strategy rule engine module. These rules are defined in declarative YAML format, ensuring both human readability and machine parsing. Each rule contains four core parts: trigger condition, priority calculation function, transition logic pointer, and loading mode instruction. For example, to prioritize handling potential fraudulent transactions, a strategy rule can be defined through program code as follows:

[0055]

[0056]

[0057] At the same time, another rule is defined through program code for high-value transactions:

[0058]

[0059] All these rules are loaded into the strategy rule engine module, forming the core logic of decision-making.

[0060] After initialization, the system enters the continuous data processing phase. In this embodiment, the data stream monitoring and interception service is implemented by deploying a Debezium for MySQL connector. This connector directly reads the MySQL binary log (binlog), captures all data change events (INSERT, UPDATE, DELETE) of the database tables, and publishes these events in real time in a structured JSON format to a specific topic in Apache Kafka, such as mysql-prod-db01.trading.transactions.

[0061] The service continuously consumes this Kafka topic. For each raw Change Data Capture (CDC) message consumed from Kafka, it is atomically encapsulated into a standardized data item object. This object contains the raw data payload and a metadata header. A raw Debezium message might look like this:

[0062]

[0063]

[0064] After atomic encapsulation and initial metadata annotation in sub-step 2.2, the message is transformed into the following data item object:

[0065]

[0066] During this process, the preprocessing module queries the data source description module, obtains its structural metadata by matching source, schema, and table information, and applies semantic tagging rules. Since the amount is 150,000.00, exceeding the threshold of 100,000.00, the HighValueTransaction tag is added. Simultaneously, the content in payment_gateway_response matches a regular expression for fraud indicators, so the PotentialFraudIndicator tag is added. Thus, a raw technical data record is enriched into an "information package" carrying business semantic context, enabling intelligent decision-making.

[0067] Next, the dynamic priority assignment and selective extraction module handles the process. In this step, the header of the data item object is first taken as input and matched in the policy rule engine module. For the data item mentioned above, its header.semantic_tags contains both HighValueTransaction and PotentialFraudIndicator. Therefore, the triggering conditions of both rules RULE_FRAUD_001 and RULE_HIGH_VALUE_002 are met, forming an active policy set.

[0068] The system calls the corresponding priority calculation function for each activity strategy:

[0069] For RULE_FRAUD_001:

[0070] Content Value Factor V c Its value is determined based on the semantic tags contained in the metadata header of the data item. In the policy rules, a pre-defined value base, V, is set for each semantic tag. c Take the maximum or sum of the cardinality values ​​corresponding to all semantic tags contained in the data item. Based on this rule parameter, the cardinality value corresponding to the PotentialFraudIndicator tag is 100. c Take the maximum value, which is 100.

[0071] Time sensitivity factor V t Its value is a decreasing function of the difference between the data generation timestamp and the current system time. Specifically, Among them, t now t represents the current time. event The time of data item generation is λ, where λ is the time decay coefficient, defined by the policy rules. Assuming the difference between the current system time and the event timestamp is 2 seconds, V... t =e (-0.001*2)*100 ≈99.8.

[0072] Model Novelty Factor V s Its value is 0 when the data item's schema is completely consistent with the known schema recorded in the metadata knowledge base; when a schema inconsistency is detected, its value is set to a higher preset positive value to increase the processing priority of the new schema data. The data schema is consistent with version v1.0.0 in the knowledge base, therefore V... s =0.

[0073] Data dependency factor V d Its value is determined based on the relationship between this data item and other high-priority data items. This relationship is obtained by querying a pre-defined data dependency graph. Assuming no complex dependencies are configured, V d =0.

[0074] If the weight coefficients wc, wt, ws, and wd of each factor are set to 0.7, 0.2, 0.05, and 0.05, then the overall priority score P is calculated. fraud =0.7*100+0.2*99.8+0.05*0+0.05*0=70+19.96=89.96.

[0075] For RULE_HIGH_VALUE_002:

[0076] Content Value Factor V c The value base corresponding to the HighValueTransaction tag is 85.

[0077] Time sensitivity factor V t V t =e (-0.0005*2)*100 ≈99.9.

[0078] V s and V d It is also 0.

[0079] Overall priority score P high_value =0.6*85+0.35*99.9+0.05*0+0.0*0=51+34.965=85.965.

[0080] The system uses an aggregation algorithm that takes the maximum value, and the final comprehensive priority score P of this data item is determined to be max(89.96,85.965)=89.96.

[0081] The system compares P = 89.96 with a preset priority threshold. In this embodiment, the threshold is defined as: high priority (P > 80), medium priority (50 <= P <= 80), and low priority (P < 50). Since 89.96 > 80, the data item is immediately distributed to the high-priority processing queue. This queue can technically be implemented by a RabbitMQ queue with high priority settings or a dedicated, resource-reserved memory queue to ensure minimal processing latency. For a regular transaction record without any special semantic tags, it may only match a default low-priority rule, and the calculated P value may be less than 50. It will then be sent to the low-priority archiving and batch processing module. In this module, the data is temporarily written to inexpensive storage (such as local disk or object storage) to await batch processing during system idle periods, or is automatically cleaned up after exceeding the set time-to-live (TTL).

[0082] Data items that enter the high-priority processing queue are then consumed by the real-time transformation logic generation and execution module. This module is crucial for handling dynamic changes in data source patterns.

[0083] In this step, the retrieved data items are first subjected to a schema consistency check. This involves extracting the real-time schema of the data item (which can be parsed from the structure of its payload or carried in the header) and comparing it with the schema of the latest stable version v1.0.0 of the data source recorded in the data source description module 110.

[0084] Path A: Schema Completely Consistent. Initially, the data item's schema is completely consistent with v1.0.0. After successful validation, the system proceeds to Path A. Based on the highest priority activity policy RULE_FRAUD_001 associated with this data item, its transformation logic pointer is TRANSFORM_CHAIN_FRAUD_ALERT. The system retrieves and calls this function chain from a pre-compiled transformation function library. This function chain may include the following operations: 1) Calling an external risk control API to enrich the data with transaction_id to obtain a more detailed risk profile; 2) De-identifying user_id and replacing it with a pseudonym; 3) Flattening the data structure and converting it to the JSON format required by the target system BigQuery.

[0085] Path B: Schema Inconsistency. Imagine that during system operation, the DBA of the source MySQL database executes the operation `ALTERTABLE transactions ADD COLUMN promo_code VARCHAR(20) NULL;`. The resulting new CDC event will have a payload containing a new `promo_code` field. When this new data item is processed, an inconsistency will be detected during schema validation. At this point, the system triggers Path B.

[0086] The system then compares the real-time mode with the baseline mode (v1.0.0) and generates a structured difference report, such as: {"change_type":"Field_Addition","field_name":"promo_code","data_type":"VARCHAR(20)","is_nullable":true}.

[0087] Simultaneously, the real-time transformation logic generation module queries its built-in pattern evolution rule library. This library pre-stores strategies for handling various pattern changes. For example, one rule is defined as: IF change_type IS "Field_Addition" AND is_nullable IS true THEN GENERATE_LOGIC{target_schema_op:"addColumn(field_name,mapType(data_type),is_nullable)",transform_op:"addField(field_name,payload.get(field_name,null))"}. Based on this rule, the system automatically synthesizes a new transformation operation sequence: First, logically add a nullable STRING field named promo_code to the definition of the target BigQuery table; second, in the data transformation process, add a step to extract the value of the promo_code field from the payload, and fill in NULL if it does not exist.

[0088] The newly synthesized transformation function chain is compiled into executable bytecode or interpreted on demand and immediately applied to the current data item carrying the new schema. Simultaneously, this newly generated transformation logic, along with the new schema that triggered it (marked as v1.0.1), is cached in the dynamic transformation logic cache. All subsequent data items using the v1.0.1 schema will directly use this cached logic, avoiding the overhead of repeated generation. Finally, the system asynchronously updates the definition of the v1.0.1 schema to the data source description module as a new benchmark for future verification. Through this mechanism, the entire data migration process achieves seamless and automatic adaptation to changes in the source schema.

[0089] Finally, the transformed data is passed to the closed-loop feedback, where the adaptive loading and closed-loop feedback module is responsible for loading it into the target system and adjusting the feedback. This module executes different loading strategies based on the data item's source queue (i.e., its priority). For fraud alarm data from the high-priority processing queue, the system uses the REALTIME_ATOMIC_WRITE instruction to write data in real time, either as a single record or in micro-batches, by calling the Google BigQuery tabledata.insertAll streaming API, ensuring that the data can be queried on the target end within seconds. For high-value transaction data from the medium-priority processing queue, the system executes the MICRO_BATCH_WRITE instruction, accumulating dozens or hundreds of records in the local buffer before writing them through a single BigQuery batch load job to achieve higher throughput and lower cost.

[0090] During loading, the system closely monitors the results of each loading operation, recording metrics such as success, failure, latency, and throughput, and generating a structured loading event log. These logs are continuously fed into the feedback analysis module.

[0091] A closed-loop feedback control mechanism is crucial for ensuring the long-term stable and efficient operation of the system. The feedback analysis module executes multiple feedback logics:

[0092] Feedback Logic A, Error Handling and Isolation: If the feedback module finds that data processed by a real-time generated transformation logic (e.g., logic for v1.0.1 mode) continuously returns a "field type mismatch" error when loaded into BigQuery, it will automatically mark the corresponding logic in the dynamic transformation logic cache 420 as "unstable" and redirect all subsequent v1.0.1 mode data items to a dedicated exception queue. At the same time, it will send an alert to the operations team through a monitoring system (such as PrometheusAlertmanager), thus achieving rapid isolation and location of the fault.

[0093] Feedback Logic B, Performance Bottleneck Adjustment: If the feedback module monitors that the average response latency of the BigQuery streaming write API has consistently exceeded a preset threshold of 2000 milliseconds for the past 5 minutes, this indicates that the target system may be overloaded. In this case, it immediately sends a throttling signal to the dynamic priority assignment and selective extraction module. Upon receiving the signal, the module temporarily applies a decay factor to the weight coefficients of the priority calculation functions in all policy rules; for example, multiplying all w values ​​by 0.8. This lowers the overall average priority score of all data items, resulting in fewer data items entering the high and medium priority queues, thereby reducing the real-time write pressure on the target system. When the feedback module detects that the target system latency has returned to normal, it sends a signal to unthrottle, restoring the system to normal operation.

[0094] Feedback Logic C, Strategy Effectiveness Evaluation: Over a longer timeframe (e.g., weeks or months), the feedback module statistically analyzes the activation frequency of each strategy rule, the total amount of data processed, the migration success rate, and the actual usage of this data in the target system (by querying the target system's audit logs). If the analysis reveals that the RULE_HIGH_VALUE_002 rule is frequently triggered, but its corresponding data is rarely queried in BigQuery, the system can generate a strategy optimization suggestion report, prompting the strategy administrator to consider lowering the rule's priority or adjusting its value factor, thereby freeing up valuable system resources for data streams with truly high business value.

[0095] The following comparison uses a specific experiment, employing a traditional, script-based ETL method. This method uses a pre-written Python script, executed every 5 minutes via a Cron job. The script connects to the source MySQL database, retrieves all newly added records from the `transactions` table since the last execution, performs fixed transformations (such as renaming fields and formatting dates), and then writes them to the target BigQuery data warehouse via batch loading. This method lacks data priority differentiation capabilities, and its transformation logic and data schema are hard-coded into the script.

[0096] Specific test environment and scenarios:

[0097] Source database: MySQL 5.7, simulating a transaction system that continuously generates transaction records at an average rate of 1000 records per second.

[0098] Data characteristics: Of all transaction records, approximately 1% are high-value transactions with an amount greater than 100,000, and approximately 0.1% are transactions containing fraudulent characteristics.

[0099] Target database: Google BigQuery.

[0100] Test duration: 4 hours.

[0101] Key event: At the exact second hour of the test, the operation ALTERTABLE transactions ADD COLUMN affiliate_id VARCHAR(50) NULL was executed on the transactions table of the source MySQL to simulate an unplanned schema change.

[0102] Finally, the comparison data on key performance indicators are shown in Table 1.

[0103]

[0104]

[0105] The comparative data in the table above clearly demonstrates that the technical solution provided by this invention exhibits significant advantages over traditional static ETL methods in several key dimensions. Firstly, regarding data timeliness, this invention, through dynamic priority assignment, ensures that the end-to-end latency of the highest priority data, such as fraudulent transactions, is at the sub-second level (485ms). In contrast, Comparative Example 1, due to its fixed 5-minute (300,000ms; for ease of comparison, the table uses the median value of 150,000ms) batch processing cycle, results in latency in the minutes for all data, failing to meet the stringent requirements of scenarios such as real-time risk control. Secondly, regarding system adaptability and robustness, facing sudden changes in data source patterns, the instant conversion logic generation mechanism of this invention can automatically and almost instantaneously adapt, with a response time of less than 1 minute, and without any data loss throughout the process. In contrast, the hard-coded script in Comparative Example 1 became invalid immediately after the mode change, resulting in a downtime of up to 3 hours (180 minutes). During this period, all newly added data, including critical fraudulent transactions, was lost until developers manually modified, tested, and redeployed the script. Finally, regarding system efficiency and cost, the adaptive loading strategy of this invention makes the write load on the target system smooth and controllable, avoiding the huge I / O impact caused by the periodic batch loading in Comparative Example 1. This is not only more beneficial to the stability of the target system, but also often leads to a better cloud service cost model.

[0106] In summary, this invention, through a series of organically combined technological innovations, fundamentally solves the core pain points of traditional ETL methods in dynamic business environments, and successfully constructs a new generation of data migration paradigm that can dynamically perceive data value, automatically adapt to structural evolution, and perform closed-loop self-optimization. The improvement in its technical effect is significant and decisive.

[0107] The above embodiments are merely one of the preferred embodiments of the present invention and should not be used to limit the scope of protection of the present invention. Any modifications or refinements made to the main design concept and spirit of the present invention that are not of substantial significance, but solve the same technical problem as the present invention, should be included within the scope of protection of the present invention.

Claims

1. A dynamic selective real-time data migration method based on ETL, characterized in that, Includes the following steps: S1, a metadata and strategy knowledge base containing data source metadata and dynamic migration strategy rules is pre-built and injected; the specific process is as follows: S11, Construct a data source description module that defines and injects data source structure and semantic metadata; wherein, the data source structure and semantic metadata include physical layer information of the data source and a set of predefined semantic tags associated with data fields or data content patterns that characterize business meaning; S12, construct and inject the strategy rule engine module that defines and injects dynamic migration strategy rules; each dynamic migration strategy rule consists of four parts: a trigger condition that defines the activation logic premise, a priority calculation function that dynamically calculates the migration priority for data items, a conversion logic pointer that points to the set of conversion operations, and a loading mode instruction that specifies the data loading method. S2, through a continuously running data stream monitoring and interception service, intercepts real-time data streams from the data source, encapsulates the intercepted logical data units into data items carrying metadata headers, and uses the metadata and policy knowledge base to annotate the metadata headers of the data items to enhance their business semantics. S3, based on the metadata header information of the data item, match the metadata with the dynamic migration strategy rules in the strategy knowledge base, dynamically calculate a comprehensive priority score for the data item, and selectively extract and distribute the data item based on the result of comparing the score with a preset priority threshold; the specific implementation process is as follows: S31, Strategy rule matching and activation: Taking the metadata header information of the data item as input, all dynamic migration strategy rules are traversed in the strategy rule engine module, the triggering conditions of each rule are evaluated, and all activated rules are aggregated into an active strategy set. S32, Dynamic Calculation of Priority Score: If no strategy rule is activated, the data item is assigned a preset default low priority score; if one or more active strategies exist, the priority calculation function corresponding to each active strategy is called to calculate one or more priority sub-items for the data item, and a preset aggregation algorithm is used to merge all sub-items to obtain the final comprehensive priority score P; wherein, the priority calculation function is a multi-factor linear weighted model, and its mathematical expression is: P = w c * V c + w t * V t + w s * V s + w d * V d ; Where P is the final calculated overall priority score; V c V is the content value factor. t For time sensitivity factor, ,in, For the current time, The time when the data item was generated, V is the time decay coefficient; s V is the model novelty factor; d For data dependency factors; w c w t w s w d These are the weight coefficients of each factor, and their sum is 1; S33, Selective extraction and distribution based on priority threshold: The calculated comprehensive priority score P is compared with a set of preset priority thresholds, and the data items are distributed to different processing queues or modules according to the comparison results; S4 performs a data pattern consistency check on the selected data items. If the patterns are consistent, the predefined transformation logic is called. If the patterns are inconsistent, the automatic generation and application of real-time transformation logic is triggered to adapt to the evolution of the data source pattern. S5. Based on the comprehensive priority score of the data item, select the corresponding loading strategy to load the converted data into the target system, and monitor the loading status to form a closed-loop feedback for dynamically adjusting the migration process.

2. The dynamic selective real-time data migration method based on ETL according to claim 1, characterized in that, The physical layer information of the data source includes table name, field name, data type, length, and constraints; the semantic tags are forcibly associated with specific data fields or data content patterns to identify the business value of data items in subsequent steps.

3. The dynamic selective real-time data migration method based on ETL according to claim 2, characterized in that, In step S2, the specific steps for annotating the metadata header of the data item include: S21, query the data source description module, match the source table name and field name information of the data item with the data source description in the knowledge base, and write the corresponding structure metadata into its metadata header; S22, apply regular expressions or keyword matching algorithms to identify whether there is a content pattern associated with the semantic tag in the data load of the data item. If so, write the corresponding semantic tag into its metadata header.

4. The dynamic selective real-time data migration method based on ETL according to claim 3, characterized in that, The specific implementation rules for step S33 are as follows: If the overall priority score P is higher than a preset high priority threshold, the data item is sent to a high priority processing queue with the minimum processing delay and the highest resource allocation. If the overall priority score P is between a preset medium priority threshold and a high priority threshold, then the data item is sent to a medium priority processing queue that allows buffering and merging. If the overall priority score P is lower than the medium priority threshold, the data item is sent to a low priority archiving and batch processing module for temporary storage, batch migration, or discarding after timeout.

5. The dynamic selective real-time data migration method based on ETL according to claim 4, characterized in that, In step S4, the processing path for data items entering the high-priority processing queue or the medium-priority processing queue is as follows: pattern consistency verification, extracting the data pattern information of the data item, and comparing it with the latest stable version pattern of the data source recorded in the metadata and policy knowledge base; if the verification result is that the patterns are completely consistent, then proceed to path A: according to the transformation logic pointer in the activity policy set associated with the data item, directly retrieve and call the corresponding transformation function or function chain from a pre-compiled transformation function library to perform transformation operations on the data load of the data item; If the verification result indicates a mode inconsistency, proceed to path B: triggering the automatic generation and application of instant conversion logic.

6. The dynamic selective real-time data migration method based on ETL according to claim 5, characterized in that, The automatic generation and application of the instantaneous conversion logic in path B specifically includes the following sub-operations: Atomized parsing of difference patterns: The real-time pattern of the data item is compared with the latest stable version pattern to generate a structured difference report describing the addition of fields, deletion of fields, changes in field type, or renaming of fields; Logical automatic synthesis based on evolutionary rules: query a built-in pattern evolution rule library that stores a series of "IF-THEN" form rules, match the corresponding evolutionary rules according to the structured difference report, and select and assemble a new transformation function chain from the atomic transformation operation library; Compilation, caching, and application of newly generated logic: The newly synthesized transformation function chain is compiled or interpreted on the fly and immediately applied to the current data item; at the same time, the newly generated logic and the new pattern that triggers it are cached together in a dynamic transformation logic cache area, and the pattern benchmark in the metadata and policy knowledge base is updated.

7. The dynamic selective real-time data migration method based on ETL according to claim 6, characterized in that, In step S5, the closed-loop feedback adjustment logic includes at least one of the following: Error handling and isolation feedback logic: When a specific type of transformation result is detected to continuously cause loading failure, the transformation logic that generated the result is automatically marked as unstable, and the data items that subsequently use the logic are imported into the exception handling queue for isolation; Feedback logic for performance bottleneck adjustment: When the average loading delay of the target system is monitored to continuously exceed the preset threshold, an adjustment signal is automatically sent to the dynamic priority assignment and selective extraction module (300) to temporarily reduce the weight coefficient value of the priority calculation function in all policy rules, thereby dynamically reducing the amount of data flowing into the high and medium priority processing queues. Feedback logic for strategy effectiveness evaluation: Long-term statistical analysis of the amount of data activated by different strategy rules, migration success rate, and average latency, and generation of strategy optimization suggestions based on statistical data for offline optimization reference.

Images