Large-scale education data migration method based on dynamic service routing

Abstract

The invention relates to a large-scale education data migration method based on dynamic service routing, and the method comprises the steps: carrying out the optimization of a ClickHouse batch writing operation, employing a transaction control and distributed lock mechanism in the data conversion and writing process, effectively preventing the data from being repeated or lost, enabling the converted data to be efficiently written into a ClickHouse target table through a batch loading mode, and enabling the data to be more stable and reliable. Dynamic table name adaptation and complex data structure conversion are supported, efficient and reliable migration of ten-million-level education data is achieved through asynchronous parallel processing, fragmentation task scheduling, a multi-dimensional duplicate removal mechanism and an abnormal retry strategy, and the throughput and consistency of data writing are remarkably improved.

Description

[0001] Technical Field This application relates to the field of electronic digital data technology, specifically to a method for large-scale educational data migration based on dynamic service routing.

[0002] Background Technology: In digital education platforms, educational evaluation data is typically stored in relational databases like MySQL. However, with the growth in data volume and the increasing demands for analysis, it is necessary to migrate the data to analytical databases like ClickHouse to support efficient querying and statistical analysis. Traditional data migration methods often employ single-threaded or simple batch processing, which presents the following problems: The migration process is inefficient, with large-scale educational data (tens of millions of answer records) taking too long to migrate and failing to meet real-time requirements; it has poor business adaptability, with different questionnaire types, including student evaluations and supervisor evaluations, having significantly different data structures, making it difficult for general migration rules to meet diverse processing needs; and it is difficult to ensure data consistency, with issues such as abnormal interruptions and duplicate writes during the migration process easily leading to data loss or corruption. When adding new questionnaire types or business rules, the migration logic needs to be reconstructed, resulting in low system flexibility. Existing technologies lack dynamic routing and parallel processing mechanisms for various types of data in educational scenarios, making it difficult to achieve efficient, reliable, and scalable data migration.

[0003] Summary of the Invention In order to solve the problems existing in the prior art, the purpose of this application is to provide a method for large-scale educational data migration based on dynamic service routing.

[0004] This application describes a method for large-scale educational data migration based on dynamic service routing, comprising: Step S101: Receive data migration request, parse the school code, semester identifier, and questionnaire type list parameters contained therein, verify the validity of the parameters, and use them to determine the migration target and data range; Step S102: Based on the questionnaire type parameter, dynamically select the corresponding data processing service through the strategy mode. The data processing service uses differentiated data parsing and transformation logic for different questionnaire types. Step S103: Obtain the data to be migrated from the MySQL source database according to the query conditions, and divide the data into multiple data shards based on the preset sharding rules; Step S104: Create asynchronous processing tasks for each data shard, use a thread pool to concurrently execute data transformation operations, and use the synchronization controller to determine when all shard tasks are completed before proceeding to the next stage. Step S105: During the data conversion and writing process, transaction control and distributed lock mechanisms are used to prevent data duplication or loss, and the converted data is loaded into the ClickHouse target table in batches. Step S106: Summarize the processing status and performance indicators of each shard, generate a migration report and return it to the requesting end, supporting abnormal data recording and retry mechanisms.

[0005] Preferably, in step S101, after receiving a data migration request, the system extracts the school code, semester identifier, and questionnaire type list using regular expressions, performs format verification and database existence verification, queries the scale of data to be migrated based on the combination of semester and questionnaire type, analyzes the data type distribution using the Naive Bayes algorithm to assess migration feasibility, calculates task priority weights based on the hash algorithm, and generates a migration execution plan that includes the target path, data range, and time window.

[0006] Preferably, in step S102, according to the questionnaire type parameter, the system matches and loads the corresponding data processing service instance through the strategy mapping table, calls the parsing interface of the service, converts the original data into a standardized field set, and then converts it into the target format through the field mapping rule library. After cleaning and standardizing the text fields, the system uses a decision tree algorithm to evaluate the data quality level, generates a standardized data object with quality tags, and completes the differentiation processing flow.

[0007] Preferably, in step S103, after obtaining the data to be migrated from MySQL, the data is sharded according to preset rules, and each shard is allocated to the target storage node using a consistent hashing algorithm. After format verification and standardization of the sharded data, the migration is completed by batch insertion. The shard status log is recorded throughout the process, and the log is clustered and analyzed based on the K-Means algorithm to determine the overall migration result status.

[0008] Preferably, in S104, the dataset to be processed is divided into multiple data shards according to the sharding algorithm, and an asynchronous task is created for each shard and submitted to the thread pool for concurrent conversion. The completion status of all tasks is monitored through a synchronization mechanism to ensure that the shards are processed completely. Then, a merging algorithm is used to integrate them into a unified dataset. Consistency verification is performed on the merged data. After passing the verification, the data is transmitted to the subsequent modules, metadata is recorded, and the business process is completed.

[0009] Preferably, in step S105, after standardizing the format of the data to be processed, a transaction control mechanism is enabled to ensure the atomicity of the operation. Then, a distributed lock is acquired to achieve exclusive write access. The locked data is format converted and efficiently written to ClickHouse through batch loading. After writing, the data integrity is verified, deduplication is performed if necessary, the distributed lock is released, and the data entry is completed.

[0010] Preferably, in S106, the processing status and performance indicators of each shard are summarized, the task execution status is collected and evaluated through the monitoring module, and when abnormal data is detected, the shard status is recorded according to the rules and a retry mechanism is triggered to update the shard status, integrate the latest status and performance results, generate a structured migration report, and after format adaptation, transmit it to the requesting end through the specified protocol to complete the delivery.

[0011] The large-scale educational data migration method based on dynamic service routing described in this application has the following advantages: it supports efficient migration of tens of millions of data points through data sharding and asynchronous parallel processing, improving processing speed by more than 10 times; the dynamic service routing mechanism based on the strategy pattern supports automatic matching of differentiated processing logic according to questionnaire type, flexibly adapting to multi-source educational data; combined with distributed locks, transaction control, and exception retry mechanisms, it ensures the atomicity and integrity of the migration process; the modular design supports rapid access to new questionnaire types, and dynamic table name configuration adapts to multi-tenant and multi-environment scenarios; through standardized interfaces and layered architecture, it reduces system coupling and improves operation and maintenance monitoring capabilities. Attached Figure Description

[0012] Figure 1 This application describes a method for large-scale educational data migration based on dynamic service routing. Figure 1 ; Figure 2 This application describes a method for large-scale educational data migration based on dynamic service routing. Figure 2 . Detailed Implementation

[0013] like Figures 1-2 As shown, the large-scale educational data migration method based on dynamic service routing described in this application includes: like Figures 1-2 As shown, in step S101, a data migration request is received, and the parameters including school code, semester identifier, and questionnaire type list contained therein are parsed to verify the legality of the parameters, which is used to determine the migration target and data range.

[0014] Further, in step S101, a data migration request message is obtained, and three core parameters—school code, semester identifier, and questionnaire type list—are extracted by regular expression matching to obtain a structured parameter set; The structured parameter set is validated according to the preset parameter rule base. If the school coding format conforms to the eight-digit standard and the semester identifier matches the year and semester pattern, the parameter format is determined to be valid. The existence of the school code in the school information table is verified by database query, and the target school entity record is determined by code matching operation; Based on the combination of the semester identifier and the questionnaire type list, perform a range query operation in the questionnaire data table to obtain the total number of records and data capacity of the data to be migrated. The data to be migrated is analyzed by the Naive Bayes classification algorithm. If the data type distribution meets the preset threshold range, the feasibility of data migration is determined. A hash algorithm is used to calculate the association between the target school entity record and the data record to be migrated, and the priority weight value of the migration task is obtained. Based on the priority weight value and data capacity parameter, a migration execution plan is generated that includes the migration target path, data range boundary, and execution time window.

[0015] Specifically, in step S101, after receiving the data migration request, the system first extracts key parameters through a JSON parser. The school code is “SC20240315” in 8-digit format, the semester identifier is “2024S1” indicating the spring semester of 2024, and the questionnaire type list includes three types: [“teaching_eval”, “course_feedback”, “satisfaction_survey”]. The parameter verification module uses a regular expression algorithm to verify the school coding format. It ensures coding standardization by matching the pattern “^SC[0-9]{8}$”, and verifies that the semester identifier conforms to the “YYYY[S|F][1-2]” format requirement. Questionnaire type verification uses a whitelist mechanism to match the received questionnaire types with 15 predefined standard questionnaire types. Verification is passed when the matching success rate reaches 100%. The system queries the target database instance based on the school code, calculates the target shard location as the 3rd data node using a hash algorithm, and estimates the amount of data to be migrated to be approximately 0.5MB, containing 12,000 questionnaire records. The data range determination module filters the time range from February 26, 2024 to July 15, 2024 based on the semester identifier, and the number of records that meet the criteria is 11,847, which is obtained through SQL query statistics. The migration strategy generator assesses data volume and network bandwidth, and adopts a batch migration method, processing 1,000 records per batch, with an estimated total time of 30 seconds, ensuring system stability and data integrity during the migration process.

[0016] like Figures 1-2 As shown, in step S102, the corresponding data processing service is dynamically selected based on the questionnaire type parameter through a strategy mode. The data processing service is used for differentiated data parsing and transformation logic for different questionnaire types.

[0017] Further, in step S102, a set of questionnaire type parameters is obtained, and the questionnaire type parameters are classified and labeled by a type recognizer to obtain questionnaire parameter objects with type identifiers; Based on the type identifier, a matching query operation is performed in the preset strategy mapping table to determine the corresponding data processing service component identifier; Using the service component identifier, the corresponding data processing service instance is loaded from the service registry to obtain a dedicated questionnaire data processor object; By calling the type-specific parsing interface through the data processor object, a structured parsing operation is performed on the original questionnaire data to obtain a standardized set of data fields; Based on the field type attributes in the data field set, a field mapping rule base is used to perform data format conversion processing to generate data records in the target format. If the data record contains text type fields, then the text preprocessing module performs cleaning and standardization operations to determine the standardized text data content; The standardized text data content is classified using a decision tree algorithm, and the data quality level is determined according to preset classification rules. By using the data quality level and the transformed data records, a final questionnaire data object containing quality markers is generated, completing the differentiated data processing workflow.

[0018] Specifically, in step S102, the system starts a strategy mode selector based on the received questionnaire type parameter. The selector maintains a strategy registry containing 8 different processing strategies, each strategy corresponding to a specific questionnaire type processing logic. When the system recognizes the "student_evaluation" type, the strategy selector locates the student evaluation processing service within 0.3 milliseconds using a hash mapping algorithm. This service uses a weighted scoring conversion algorithm to convert the original 5-point score into a standardized score by a factor of 1.2. For questionnaires of the "faculty_assessment" type, the system automatically switches to the teacher assessment processing service. This service uses the Bayesian classification algorithm to perform sentiment analysis on the text evaluation with an accuracy rate of 94.7%, and at the same time converts the unstructured evaluation content into quantitative indicators in 7 dimensions. When the "curriculum_review" type is encountered, the course review processing service is activated. This service uses a natural language processing engine to extract keywords from course suggestions, identifies an average of 3.8 valid suggestion points per questionnaire, and assigns weights from 0 to 10 according to their importance. The data conversion module maps 32 fields of the source data to 28 fields of the target format according to the data model requirements of the target system, achieving a field matching rate of 87.5%. Mismatched fields are filled in using a default value filling mechanism to ensure the integrity and consistency of the data structure.

[0019] like Figures 1-2 As shown, in step S103, the data to be migrated is obtained from the MySQL source database according to the query conditions, and the data is divided into multiple data shards based on the preset sharding rules.

[0020] Further, in step S103, the data filtering results that meet the query conditions are obtained from the MySQL system through a database connection, resulting in a set of data to be migrated; The dataset to be migrated is divided according to preset sharding rules to determine the number and content of shards, resulting in multiple data shards. For each data shard, a consistent hashing algorithm is used to perform shard allocation operations, determine the target storage node, and obtain the mapping relationship between shards and nodes; If the fragmented content contains structured data, the data fragments are format-validated through the field parsing module to obtain standardized fragmented data objects. Based on standardized fragmented data objects, the data fragments are transferred to the target storage node using a batch insertion method to obtain the fragmented records that have been migrated. The logging module tracks the status of the migrated shard records to determine the integrity of the migration process and obtains shard logs containing status markers. The K-Means algorithm is used to perform cluster analysis on the sharded logs, and the final migration data status is determined based on the migration results classified by the status labels.

[0021] Specifically, in step S103, the data to be migrated is obtained from the MySQL source database according to the query conditions, and the data is divided into multiple data shards based on the preset sharding rules. The specific implementation methods are combined into one section. First, the system receives query conditions. For example, the query conditions for the student information table are "age > 18 and registration time after 2025-01-01". Using the MySQL SELECT statement combined with the WHERE clause, it extracts 50,000 records that meet the conditions from the student_info table, which contains 1 million records. The query takes about 1.2 seconds. The B+ tree index optimization ensures efficient retrieval. The extracted data includes 12 fields such as student ID, name, age, and course rating, with a total data volume of approximately 500MB. The system initiates the data sharding process based on preset sharding rules. The sharding rules are based on the hash value of the student ID (SHA-256 algorithm) modulo 8, and the data is evenly distributed to 8 sharding nodes. Each sharding node receives about 6250 records, and the data volume is about 62.5MB. During the sharding process, the system dynamically adjusts the sharding distribution through a consistent hashing algorithm, ensuring that only 12.5% ​​of the data needs to be migrated when a new node is added, reducing the redistribution overhead; Each data shard is transmitted to the target database through a parallel processing pipeline. The data shards are format-validated before transmission. The validation rules include field type (e.g., age is an integer) and non-null constraints (name cannot be empty). Records that fail validation (approximately 0.8%) are logged and marked as exceptions. After the target database receives the fragmented data, the system completes the data migration through batch insertion operations (1,000 records per batch), with an insertion efficiency of approximately 20,000 records per second. The entire process is coordinated through a distributed task scheduling framework, with tasks allocated to 4 worker nodes. On average, each node processes 2 shards, and the completion time is approximately 15 seconds. The sharded data is stored in the target database according to the sharding key, which facilitates subsequent distributed queries and keeps the query response time within 0.5 seconds; This process is not directly related to questionnaire type processing, but it can be extended to the fragmentation and migration of questionnaire data, such as fragmentation by questionnaire ID, to ensure that the data processing logic is independent and efficient.

[0022] like Figures 1-2 As shown, in step S104, an asynchronous processing task is created for each data shard, and the data transformation operation is executed concurrently using a thread pool. The next stage is entered after all shard tasks are completed by the synchronization controller.

[0023] Further, in step S104, the dataset to be processed is obtained from the data source, and the dataset is divided into multiple data fragments using a fragmentation algorithm to obtain a fragment set; For each data shard in the shard set, generate a corresponding asynchronous task, allocate it to a preset thread pool, perform concurrent transformation operations, and obtain the transformed shard set; The execution status of all asynchronous tasks in the thread pool is monitored through a synchronization mechanism. If all tasks are completed, the transformed shard set is confirmed to be complete, and the task completion status is obtained. Based on the task completion status, the transformed fragmented sets are integrated using a merging algorithm to generate a unified dataset, resulting in the merged dataset. Extract key fields from the merged dataset, apply a consistency check algorithm to determine whether the data consistency meets the preset threshold, and obtain the check result. If the consistency check passes, the merged dataset will be transmitted to the subsequent processing module to perform subsequent processing operations and obtain the final processing result. The system records the metadata of the final processing result, generates a processing completion identifier, and determines the end of the business process.

[0024] Specifically, in step S104, it is assumed that a dataset containing 10 million records needs to be processed, each record containing a user ID and a student's spending amount, and the amount needs to be converted to US dollars (exchange rate 1 RMB = 0.14 USD). First, the dataset is divided into 10 shards, each containing 1 million records, and stored using a distributed file system (HDFS). Each shard is approximately 200MB in size. Based on memory and I / O performance analysis, processing each shard with a single thread takes approximately 30 seconds. To achieve asynchronous processing, a thread pool is created using Python's concurrent.futures module, with a maximum of 8 threads (based on an 8-core CPU), and each thread processes one slice. The task is defined as reading the data shards, iterating through each record, performing an amount conversion (amount_usd=amount_rmb*0.14), and writing the result to a new file. Example code: fromconcurrent.futuresimportThreadPoolExecutor;def process_chunk(chunk_id):data=read_chunk(chunk_id); return[(id,amount*0.14)forid,amountindata], thread pool submits task:withThreadPoolExecutor(max_workers=8)asexecutor:futures=[executor.submit(process_chunk,i)foriinrange(10)]; To ensure the completion of all fragmented tasks, a synchronization controller is used, based on the wait function of the futures module: from concurrent.futures import wait; wait(futures); Analysis shows that processing 10 shards in parallel with 8 threads takes about 38 seconds in total (8 threads process 8 shards at the same time, and the remaining 2 shards are processed in the next round, with each round taking about 30 seconds. Network latency and thread scheduling add about 8 seconds). Once completed, check the state of each Future object in the futures list to ensure there are no exceptions (result=[f.result()forfinfutures]). The next stage is data aggregation, merging the results of 10 shards into a single dataset, checking the total number of records (which should be 10 million), and calculating the total amount (assuming the original total amount is 50 million RMB, converted to 7 million USD). If a record is found to be missing (e.g., 9,999,999 records), a retry mechanism is triggered to reprocess the corresponding shard. This process is executed automatically by the program without manual intervention, ensuring data integrity and processing efficiency.

[0025] like Figures 1-2 As shown in step S105, during the data conversion and writing process, transaction control and distributed lock mechanisms are used to prevent data duplication or loss, and the converted data is loaded into the ClickHouse target table in batches.

[0026] Further, in step S105, the data to be processed is obtained, the original records are extracted from the data source, the data format consistency is determined by parsing, and a standardized data set is obtained; For standardized datasets, a transaction control mechanism is adopted to initialize the transaction context and determine the atomicity of data operations through commit or rollback, thereby obtaining a transaction-protected dataset. From the transaction-protected data set, a distributed lock mechanism is used to obtain a globally unique lock identifier. The exclusiveness of data writing is determined through lock allocation, thus obtaining the locked data set. Based on the locked dataset, perform data transformation operations, determine the format of the transformed data according to preset transformation rules, and obtain the transformed dataset. For the transformed dataset, a batch loading method is used, and the efficient writing of data is determined through ClickHouse's batch write interface to obtain the set of records that have been written. From the set of records that have been written, verify the integrity of the data. If duplicate records are detected, determine the unique set of records through a deduplication algorithm to obtain the deduplicated data set. For the deduplicated data set, release the distributed lock, determine resource availability through the unlocking operation, and obtain the final data set to be written to the ClickHouse target table.

[0027] Specifically, in step S105, during the data conversion and writing process, transaction control and distributed lock mechanisms are used to prevent data duplication or loss, and the converted data is loaded into the ClickHouse target table in batches. This can be achieved through the following specific implementation methods. Assuming we are processing 1 million user behavior logs per day, we need to convert them into a target table in ClickHouse; First, during the data transformation phase, Apache Kafka is used as a message queue to receive raw JSON format logs, each containing a user ID, behavior type, and timestamp. The conversion program is based on the Flink stream processing framework, with a parallelism of 8, and takes about 5 minutes to process 1 million data entries. The transformation logic includes parsing JSON into structured data, filtering invalid records (with empty timestamps), and normalizing behavior types, such as unifying "click" and "tap" into "click". To prevent data duplication, a Redis distributed lock mechanism is used. A unique key ("user:123:202510131024") is generated based on the user ID and timestamp. The lock is set using the Redis SETNX command, and the lock expires after 30 seconds, ensuring that the same record is not processed repeatedly by multiple Flink tasks. Tasks that fail to acquire the lock will be skipped and written to a dead letter queue. The queue data will be retried by a scheduled task, with one retry per hour and a maximum of three retries. The transformed data is temporarily stored in an intermediate topic in Kafka, with each batch consisting of 1000 records, occupying approximately 10MB of memory. During the data writing phase, ClickHouse's JDBC driver is used to batch insert data, with a batch size of 5000 records, an insertion frequency of 10 times per second, and a single insertion time of approximately 0.2 seconds. To ensure data consistency, ClickHouse transaction control is used, and tables using the MERGE_TREE engine support atomic writes. The transaction timeout is set to 60 seconds, and if a write fails, it is rolled back and an error log is recorded. Analysis shows that the distributed lock mechanism reduces the duplicate data rate from 2% to 0.01%, and batch writing improves insertion efficiency by 50%. Through dead-letter queues and retry mechanisms, the data loss rate was controlled below 0.001%. Ultimately, 1 million data entries were converted and written in about 8 minutes, with a system throughput of 2,000 entries per second, meeting the needs of high-concurrency scenarios.

[0028] like Figures 1-2 As shown, in step S106, the processing status and performance indicators of each shard are summarized, a migration report is generated and returned to the requesting end, and abnormal data recording and retry mechanisms are supported.

[0029] Further, in step S106, the processing status and performance data indicators of each shard are obtained, and real-time data is collected through the status monitoring and management module to obtain a set of shard task execution statuses; Performance data metrics are extracted from the set of execution statuses of fragmented tasks, and the summary results are calculated and determined using data aggregation and analysis methods to determine the performance evaluation results. If abnormal data is found in the performance evaluation results, abnormal data points are identified according to the abnormality detection rules and recorded in the abnormal data recording module to obtain an abnormal data set. For abnormal data sets, a retry handling mechanism is executed, and the abnormal shards are reprocessed through preset retry logic to obtain the updated shard processing status; Based on the updated sharding processing status and performance evaluation results, the migration report generation module integrates the data and generates a structured migration report. The migration report format is adjusted by the report format conversion module to adapt to the data transmission protocol, resulting in an adapted migration report; The adapted migration report is transmitted to the requesting end using a data transmission protocol, completing the report delivery and obtaining a transmission confirmation status.

[0030] Specifically, in step S106, when receiving the processing status and performance indicators of each shard, the system collects data from each node through the distributed task scheduling framework (Apache Kafka). Each node generates 10MB of status data per second, including processing time, success rate, error code, etc., which is stored in the Redis cache with the key format "node:ID:timestamp". During aggregation, the MapReduce algorithm is used. The Mapper groups the data of each node according to the shard ID, and the Reducer calculates the average processing time (100ms), success rate (99.5%) and error rate (0.2%), and detects anomalies (processing time > 500ms). The migration report is generated and encapsulated in JSON format, including shard ID, processing status, and performance metrics (throughput 1000 req / s). It is sent to the requesting client via an HTTP POST request in gzip compressed format, with the URL "http: / / report.api / migration". Abnormal data records are stored using Elasticsearch. Abnormal conditions are error code > 400 or timeout > 1 second. Records include timestamp, shard ID, and error details. The retry mechanism uses an exponential backoff algorithm with an initial retry interval of 100ms, a maximum of 5 retries, and an interval growth factor of 2. After failure, it is recorded in the log system (Logstash) and an alarm is triggered to the monitoring platform (Prometheus). The analysis process found that if the error rate of a certain shard is >1%, the shard will be automatically reassigned and the load balancing API will be called to adjust the task to a healthy node (response time <200ms). Data consistency is ensured through Kafka consumer groups, with an offset commit interval of 500ms. If a node fails, the system uses Zookeeper to detect and reassign tasks, with an average recovery time of 2 seconds. All steps are implemented through automated scripts, relying on the Spring Boot microservice architecture, and log analysis shows that the overall system availability reaches 99.9%.

[0031] like Figures 1-2 As shown in the figure, this application describes a large-scale educational data migration method based on dynamic service routing. The method is applied to a data migration scenario of a large-scale educational evaluation system, and the specific implementation process is as follows: The system first receives the data migration request and parses it as follows: School code ("SC20240315"), semester identifier ("2024S1") The questionnaire type list ("teachingeval", "coursefeedback") parameters are used to verify their validity and determine the migration target and data scope. Based on the questionnaire type, the system core adopts a dynamic service routing mechanism based on the strategy pattern. The business logic layer dynamically selects the corresponding data processing service implementation class in the Spring application context through a predefined enumeration class according to the parsed questionnaire type parameters. For example, when the type is "student_evaluation", the system automatically routes to the student evaluation data processing service, which has a built-in standardized conversion algorithm for 5-point ratings; if it is "faculty_assessment", the teacher evaluation service is activated, which integrates a Bayesian classifier to perform sentiment analysis on the text evaluation. This design ensures the differentiation of data parsing and conversion logic for different questionnaire types, conforms to the open / closed principle, and facilitates the expansion of new questionnaire types. Data sharding and asynchronous parallel processing: After retrieving the data to be migrated from the MySQL source database according to conditions, the system divides the data into multiple data shards based on preset intelligent sharding rules (modulo operation based on the hash value of the primary key). By default, each batch processes 1,000 records. Subsequently, the asynchronous processing layer creates an independent asynchronous processing task for each data shard and submits it to the executor managed by the thread pool to perform data transformation operations concurrently. During this process, the CountDownLatch synchronous controller ensures that all sharding tasks are completed before entering the result aggregation and next writing stage. This hybrid asynchronous batch processing framework significantly improves throughput. To ensure data consistency and facilitate batch writing, the system implements multiple consistency guarantee mechanisms during the data transformation and writing process to the ClickHouse target table. Distributed locks (based on Redis) are used to prevent the same data shard from being processed repeatedly. Transaction control is enabled during batch write operations to ensure the atomicity of the operations. Simultaneously, a thread-safe collection is used to temporarily store the data being processed, and a multi-dimensional deduplication mechanism based on the business identifier field (publishItemDetailId) is used to effectively avoid data duplication. Dynamic table name adaptation and complex structure conversion: The system supports dynamic table names and multi-environment adaptation. The data access layer will dynamically concatenate the target ClickHouse table name based on parameters such as school code and semester information. For complex data structures such as nested JSON in the source data, the complex data conversion module will parse them into Java objects, flatten them, and complete data type mapping and special character encoding conversion. Results summarization and exception handling: The system summarizes the processing status and performance indicators (processing time, success rate) of each shard and generates a detailed migration report to return to the requesting end. Throughout the process, any abnormal data will be recorded in middleware such as Redis and automatically retried based on the preset retry mechanism (such as the exponential backoff algorithm) to ensure the high reliability of the migration task.

[0032] Specifically, once all system components are initialized and ready, the interface layer receives a data migration request, which includes the school code ("SC20240315") and the semester identifier ("2024S1"). The questionnaire type list ("teachingeval", "coursefeedback") is then used by the parameter validation module to verify the validity of the request parameters. Based on the parsed questionnaire type parameters, the business logic layer dynamically selects the corresponding data processing service through the strategy pattern. For example, when the questionnaire type is "student_evaluation", the system locates and calls the StudentEvalDataService implementation class in the Spring context through the enumeration class QuestionnaireType.STUDENT_EVAL. This service is specifically responsible for handling the parsing and transformation logic of student evaluation data. The data access layer retrieves the original data to be migrated from the MySQL source database. The system adopts a sharding processing strategy. For example, 1.2 million records are divided into 1,200 data shards of 1,000 records each. The asynchronous processing layer creates a task for each shard and submits it to a thread pool with 8 cores for concurrent execution. For example: In a task, the system processes a shard (1000 records), performs data cleaning and format conversion (converting MySQL's DATETIME to ClickHouse's DateTime64), and all shard tasks are synchronized through CountDownLatch. The main thread calls latch.await() to wait until the counter reaches zero before continuing the subsequent process. During the conversion and writing process, the system enables consistency guarantee mechanisms, including: To prevent duplicate data from being written in batches, the system uses LinkedHashSet to deduplicate the data in the current shard based on the business primary key publish_item_detail_id before writing in batches. Transactions and atomicity: When performing bulk inserts into ClickHouse at the data access layer, the @Transactional annotation ensures that all records within a shard are either written successfully or rolled back. For complex structure conversion, when the answer_data field in the source data is nested JSON, the complex data structure conversion module will use the Fastjson library to parse it into a JSONObject, then flatten it, and finally map it to multiple independent columns of the ClickHouse table; Dynamic adaptation and completion confirmation: The system dynamically generates the target table name based on the school code "SC20240315" and the semester "2024S1", such as ads_eval_sc20240315_2024s1. After all shards are processed, the result aggregation module summarizes the status, and the completion confirmation module generates a migration report containing performance indicators such as processing time and total number of records, and returns it to the requester.

[0033] In one embodiment, this method is implemented in a typical large-scale educational evaluation data migration scenario, and the specific process is as follows: Upon receiving a data migration request, the module parses out the school code, semester identifier, and questionnaire type list. The parameter validation module verifies the school code format (8-digit number) and the semester identifier format (YYYY+S / F+semester number) using regular expressions, and ensures the validity of the school code through database existence verification. The system loads the corresponding data source configuration and migration strategy based on the school code and semester identifier. If the target ClickHouse table does not exist, the system dynamically creates the table based on a predefined table structure template. The table name format is ads_survey_[school code]_[semester identifier], for example, ads_survey_sc2024001_2024s1. The table creation statement includes the core fields of respondent ID, evaluated object ID, course ID, question ID, and questionnaire ID, as well as extended fields reserved for JSON parsing. The system processes questionnaires in a loop according to the list of questionnaire types: For the "STU_TO_TCH" type (student respondent, teacher being evaluated), the route is to the student evaluation data processing service; For the "TCH_TO_TCH" type (respondent teacher, evaluated teacher), the data is routed to the teacher peer review data processing service; In a MySQL sharded database environment, data to be migrated is queried based on conditions. Taking the spring semester of 2024 at a certain school as an example, the answer result table has approximately 650,000 records by the number of people who answered the questions and approximately 9.8 million records by the questions. The system divides the data to be processed into multiple data shards, creates asynchronous processing tasks for each shard, and during the transformation process: Parse and flatten the JSON-formatted answer content in the source data; Convert nested question options, scoring details, etc., into tiled fields; Multidimensional deduplication is achieved based on the combination key "respondent ID + evaluated object ID + question ID + questionnaire ID"; Standardize and clean the text-based rating content; The transformed data is asynchronously written to the ClickHouse target table in batches: transaction control is used to ensure the atomicity of the write operation, an appropriate batch size (usually 5000-10000 rows) is set to optimize write performance, a distributed lock mechanism is enabled during the write process to prevent the same shard from being processed repeatedly, and a retry mechanism based on the exponential backoff algorithm is enabled for data that fails to be written. The system synchronously processes relevant basic information: Student table: Retrieves approximately 15,000 student records for this semester, including fields such as student ID, name, grade, and major; Teacher list: Approximately 2000 teacher records; Course and class schedules: approximately 800 records; Basic information is obtained in its entirety to ensure data integrity; After all shards are processed, the system summarizes the status and performance metrics of each shard, generates a migration report containing information on the number of processing records, success rate, and time consumption, and returns it to the requesting end through a specified protocol.

[0034] For those skilled in the art, various other corresponding changes and modifications can be made based on the technical solutions and concepts described above, and all such changes and modifications should fall within the protection scope of the claims of this application.

Claims

1. A method for large-scale education data migration based on dynamic service routing, characterized in that, include: Step S101: Receive data migration request, parse the school code, semester identifier, and questionnaire type list parameters contained therein, verify the validity of the parameters, and use them to determine the migration target and data range; Step S102: Based on the questionnaire type parameter, dynamically select the corresponding data processing service through the strategy mode. The data processing service uses differentiated data parsing and transformation logic for different questionnaire types. Step S103: Obtain the data to be migrated from the MySQL source database according to the query conditions, and divide the data into multiple data shards based on the preset sharding rules; Step S104: Create asynchronous processing tasks for each data shard, use a thread pool to execute data transformation operations concurrently, and use the synchronization controller to determine when all shard tasks are completed before proceeding to the next stage. Step S105: During the data conversion and writing process, transaction control and distributed lock mechanisms are used to prevent data duplication or loss, and the converted data is loaded into the ClickHouse target table in batches. Step S106: Summarize the processing status and performance indicators of each shard, generate a migration report and return it to the requesting end, supporting abnormal data recording and retry mechanisms.

2. The method for large-scale education data migration based on dynamic service routing according to claim 1, characterized in that, In step S101, after receiving the data migration request, the school code, semester identifier and questionnaire type list are extracted using regular expressions, and format verification and database existence verification are performed. The scale of the data to be migrated is queried according to the combination of semester and questionnaire type, and the data type distribution is analyzed using the Naive Bayes algorithm to assess the feasibility of migration. 3.The method of claim 1, wherein, In step S102, based on the questionnaire type parameter, the corresponding data processing service instance is matched and loaded through the strategy mapping table, the parsing interface of the service is called, the raw data is converted into a standardized field set, and then converted into the target format through the field mapping rule library. After cleaning and standardizing the text fields, the decision tree algorithm is used to evaluate the data quality level and generate a standardized data object with quality labels.

4. The method for large-scale education data migration based on dynamic service routing according to claim 1, characterized in that, In step S103, after obtaining the data to be migrated from MySQL, the data is sharded according to preset rules, and each shard is allocated to the target storage node using a consistent hashing algorithm. After format verification and standardization of the sharded data, the migration is completed by batch insertion. The shard status log is recorded throughout the process, and the log is clustered and analyzed based on the K-Means algorithm to determine the overall migration result status.

5. The method for large-scale education data migration based on dynamic service routing according to claim 1, characterized in that, In step S104, the dataset to be processed is divided into multiple data shards according to the sharding algorithm, and an asynchronous task is created for each shard and submitted to the thread pool for concurrent conversion. The completion status of all tasks is monitored through a synchronization mechanism. After the shards are fully processed, a merging algorithm is used to integrate them into a unified dataset. A consistency check is performed on the merged data, and after passing the check, it is transmitted to the subsequent modules.

6. The method for large-scale education data migration based on dynamic service routing according to claim 1, characterized in that, In step S105, after the data to be processed is format-standardized, a transaction control mechanism is enabled to ensure the atomicity of the operation. Then, a distributed lock is acquired to achieve exclusive write access, and a format conversion is performed on the locked data.

7. The method of claim 1, wherein the method is based on dynamic service routing for large-scale education data migration. In step S106, the status and performance indicators of each shard are summarized, the task execution is collected and evaluated by the monitoring module, when abnormal data is detected, the retry mechanism is triggered according to the rules, the shard state is updated, the latest state and performance results are integrated, a structured migration report is generated, and after format adaptation, the report is transmitted to the request end through a specified protocol. 8.The method of claim 1, wherein, The strategy mode dynamically selects a data processing service, and the strategy mode dynamically selects a data processing service. According to the questionnaire type parameter, the corresponding service identifier is found in the strategy mapping table, the service instance is loaded from the service registration center, and the data conversion interface is called to perform data conversion. 9.The method of claim 1, wherein, The shard rule distributes data to shard nodes based on the modulo operation of the hash value of the data primary key.

10. The method for large-scale education data migration based on dynamic service routing according to claim 1, characterized in that, The distributed lock mechanism uses Redis, sets the lock through the SETNX command, records abnormal information occurring in the asynchronous processing process, and supports subsequent retries.

Images