Kettle-based database dynamic sub-table processing method and Kettle-based database dynamic sub-table processing system

Abstract

The invention relates to the technical field of big data processing and data integration, in particular to a Kettle-based database dynamic sub-table processing method and system, which comprises the following steps: receiving sub-table configuration parameters through a Kettle graphical interface, the sub-table configuration parameters comprising a fragmentation key, a sub-table algorithm and a sub-table number; table division rule metadata is loaded, a dynamic table division strategy engine is constructed, and the strategy engine supports Hash fragmentation, range fragmentation and time fragmentation algorithms; the construction of the dynamic table division strategy engine comprises the following steps: automatically recommending a table division algorithm based on data sampling analysis; providing a Grouping script interface to support a user-defined fragmentation rule; maintaining table division rule version history, and supporting rule rollback; the method has the beneficial effects that the large table is split into the plurality of sub-tables according to the specified rule, so that the data volume of a single table is remarkably reduced, query and update operations can focus on data in a smaller range, and the query response speed and the update efficiency are greatly improved.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of big data processing and data integration, and particularly to a database dynamic table splitting processing method and system based on Kettle. BACKGROUND

[0002] In the current era of explosive data growth, large tables with large amounts of data often appear in enterprise business systems. These large tables will cause a series of problems as data accumulates: on the one hand, a large amount of data needs to be scanned during query operations, resulting in long query response times and affecting the efficiency of the business system; on the other hand, data updates can cause table locking, slow transaction processing, and other problems, reducing the system's concurrent processing capability.

[0003] In the prior art, although there are some table splitting schemes, most of them require manual writing of a large number of SQL scripts or customized programs, which is complex and has low flexibility. For the widely used ETL tool Kettle, its native functions have limitations in table splitting and writing, and cannot flexibly and efficiently implement large table splitting and writing according to user-defined rules.

[0004] At present, most of the related table splitting tools use independent development mode, which has poor compatibility with mainstream ETL tools such as Kettle, increasing the complexity and operation and maintenance cost of the data processing process. The present application is developed based on Java language and aims to fill the above technical gap. SUMMARY

[0005] The present application aims to provide a database dynamic table splitting processing method and system based on Kettle to solve the problems raised in the background.

[0006] To achieve the above-mentioned purpose, the present application provides the following technical solution: a database dynamic table splitting processing method based on Kettle, comprising the following steps:

[0007] S1. Receive table splitting configuration parameters through the Kettle graphical interface, the table splitting configuration parameters including the shard key, the table splitting algorithm, and the number of tables;

[0008] S2. Load table splitting rule metadata and build a dynamic table splitting strategy engine, the strategy engine supporting hash sharding, range sharding, and time sharding algorithms; wherein the construction of the dynamic table splitting strategy engine includes: automatically recommending a table splitting algorithm based on data sampling analysis; providing a Groovy script interface to support custom sharding rules; maintaining a table splitting rule version history to support rule rollback;

[0009] S3. Embed a routing decision module in the Kettle data conversion process to calculate the target table routing in real time according to the shard key;

[0010] S4. Distribute the data to the corresponding sub-tables according to the routing results by a multi-thread parallel execution engine; wherein the parallel execution engine comprises: dynamic thread pool management, automatically adjusting the concurrency degree according to the number of sub-tables; batch data buffering mechanism, each sub-table corresponding to an independent write queue; back pressure control strategy, preventing memory overflow;

[0011] S5. Adopt a distributed transaction management mechanism to ensure the atomicity of cross-sub-table operations; wherein the distributed transaction management adopts: two-phase commit protocol to coordinate multi-sub-table transactions; timeout retry mechanism, the maximum number of retries being configurable; transaction log persistent storage, supporting breakpoint resuming.

[0012] Preferably, in the process of automatically recommending a sub-table algorithm based on data sampling analysis when constructing the dynamic sub-table strategy engine in step S2, the database data is sampled, the data characteristics are analyzed, and the most suitable sub-table algorithm is recommended according to the data distribution and the data size, combined with the characteristics of the hash sharding, range sharding and time sharding algorithms.

[0013] Preferably, in the process of implementing the routing decision module in step S3, the process of performing consistent hashing calculation on the shard key to obtain virtual node mapping is: mapping the shard key to a virtual node through a consistent hashing algorithm, each virtual node corresponding to one or more actual sub-tables, and determining the target sub-table range to which the data should be routed through this way; the process of dynamically adjusting the hash ring distribution according to the current number of sub-tables is: when the number of sub-tables changes, the distribution of virtual nodes on the hash ring is recalculated to ensure that data can be evenly distributed to each sub-table; the process of supporting cross-sub-table composite query routing rewriting is: for a composite query involving multiple sub-tables, the query conditions are rewritten according to the sub-table rules to enable the query to be executed on the corresponding sub-tables respectively, and the results are merged.

[0014] Preferably, in the process of dynamic thread pool management of the parallel execution engine in step S4, automatically adjusting the concurrency degree according to the number of sub-tables is: dynamically allocating the number of threads in the thread pool according to the current number of sub-tables of the system, to ensure that the data write operation of each sub-table can be executed in parallel, improving the data write efficiency; the process of each sub-table corresponding to an independent write queue in the batch data buffering mechanism is: creating an independent data write queue for each sub-table, and putting the batch data allocated to the sub-table according to the routing results into the corresponding queue, and then a special thread takes out the data from the queue for write operation; the process of preventing memory overflow by the back pressure control strategy is: when the amount of data in the data write queue exceeds a certain threshold, the data routing and write operation is suspended, and the execution is continued after the amount of data in the queue drops to a safe level, avoiding memory overflow caused by too fast data write speed.

[0015] Preferably, the process of coordinating multi-table transactions in step S5 by the distributed transaction management using the two-phase commit protocol is: at the beginning of the transaction, the coordinator sends a transaction start message to all participants, the participants perform transaction operations and feed back the execution results to the coordinator; the coordinator decides whether to commit the transaction or rollback the transaction according to the feedback results of all participants, and sends corresponding instructions to all participants; the process of the maximum retry number of the timeout retry mechanism being configurable is: when a timeout occurs during transaction execution, the system will automatically perform a retry operation, and the retry number can be set through a configuration file; if the maximum retry number is reached and the transaction is still not successful, the transaction execution is considered to fail; the process of supporting breakpoint resuming of the transaction log persistent storage is: key information in the transaction execution process, such as transaction start time, participant information and operation content, is recorded in the transaction log, and the transaction log is persistent stored on the disk; when the system fails or is interrupted abnormally, breakpoint resuming can be performed according to the transaction log during recovery, so as to ensure the integrity and consistency of the transaction.

[0016] A system of a Kettle-based database dynamic table splitting processing method, comprising:

[0017] A configuration parameter receiving module receives table splitting configuration parameters through a Kettle graphical interface, wherein the table splitting configuration parameters include a sharding key, a table splitting algorithm and a table splitting number;

[0018] A dynamic table splitting strategy engine construction module loads table splitting rule metadata and constructs a dynamic table splitting strategy engine, wherein the strategy engine supports hash sharding, range sharding and time sharding algorithms;

[0019] A routing decision module is embedded in a Kettle data conversion process and calculates a target table splitting route in real time according to the sharding key;

[0020] A multi-thread parallel execution engine module distributes data to corresponding tables according to the routing results;

[0021] A distributed transaction management module adopts a distributed transaction management mechanism to ensure the atomicity of cross-table operations.

[0022] Preferably, the dynamic table splitting strategy engine construction module comprises:

[0023] An algorithm recommendation submodule automatically recommends a table splitting algorithm based on data sampling analysis, analyzes data characteristics by sampling part of the data in the database, and recommends a suitable table splitting algorithm according to data distribution and table splitting algorithm characteristics;

[0024] A custom rule submodule provides a Groovy script interface to support custom sharding rules, and users can define personalized sharding logic by writing a Groovy script;

[0025] Rule management submodule: maintain the version history of the table splitting rules, support rule rollback, record the change information of each table splitting rule, and restore the specified version of the table splitting rule when needed.

[0026] Preferably, the routing decision module comprises:

[0027] Hash calculation submodule: consistent hash calculation is performed on the shard key to obtain virtual node mapping, the shard key is mapped to a virtual node through a consistent hash algorithm, the virtual node corresponds to an actual table, and the target table range of the data is determined;

[0028] Dynamic adjustment submodule: dynamically adjust the hash ring distribution according to the current number of tables, recalculate the position of the virtual node on the hash ring when the number of tables changes, and ensure that the data is evenly distributed to each table;

[0029] Query rewriting submodule: support cross-table composite query routing rewriting, for a composite query involving multiple tables, rewrite the query condition according to the table splitting rule, so that the query can be executed on the corresponding table and the results can be combined.

[0030] Preferably, the multi-thread parallel execution engine module comprises:

[0031] Thread pool management submodule: dynamic thread pool management is adopted, the concurrency degree is automatically adjusted according to the number of tables, the number of threads in the thread pool is dynamically allocated according to the current system table number, and parallel execution of the write operation of each table data is realized;

[0032] Data buffering submodule: with batch data buffering mechanism, each table corresponds to an independent write queue, and the data batch distributed to each table according to the routing result is put into the corresponding queue, and a special thread takes data from the queue for writing;

[0033] Back pressure control submodule: implement back pressure control strategy to prevent memory overflow, when the amount of data in the data write queue exceeds the set threshold, suspend data routing and writing operation, and continue execution when the amount of data in the queue decreases to a safe level.

[0034] Preferably, the distributed transaction management module comprises:

[0035] Transaction coordination submodule: adopt two-phase commit protocol to coordinate multi-table transaction, when the transaction starts, the coordinator sends a transaction start message to all participants, the participants execute the operation and feed back the result, and the coordinator decides to commit or rollback the transaction according to the feedback and sends the instruction;

[0036] Retry control submodule: with timeout retry mechanism, the maximum number of retries is configurable, the transaction is automatically retried when it times out, the number of retries is set through a configuration file, and if it still fails after the maximum number of retries, the transaction is considered to have failed;

[0037] Log management submodule: realize transaction log persistent storage support breakpoint continuation, record transaction execution key information such as start time, participant information, operation content, and persistent storage to disk, and after system failure recovery, the log breakpoint can be continued, guaranteeing transaction integrity and consistency.

[0038] Compared with the prior art, the beneficial effects of the present application are:

[0039] The Kettle-based database dynamic table splitting processing method and system significantly reduce the data volume of a single table, enabling query and update operations to focus on smaller ranges of data, and greatly improving query response speed and update efficiency.

[0040] Supports user-defined table splitting fields and rules, can adapt to data table splitting requirements in different business scenarios, improves data processing flexibility and pertinence. The plug-in provides a visual configuration interface, and users can complete table splitting rule setting and table splitting operation without writing complex code or scripts, reducing the technical threshold of table splitting processing and facilitating the use of non-professional technical personnel. The plug-in is based on Kettle secondary development, fully utilizes the support capabilities of the Kettle platform for various data sources and target databases, can be compatible with mainstream databases and data processing systems, reduces the difficulty of system integration. The log monitoring module can record the plug-in running state in real time, facilitating users to discover and solve problems in time. At the same time, the structure of the table after splitting is clear, facilitating data backup, migration and maintenance, and reducing the operation and maintenance cost of the system. BRIEF DESCRIPTION OF DRAWINGS

[0041] Figure 1 The method flowchart of the present application. DETAILED DESCRIPTION

[0042] In order to make the purpose, technical scheme of the present application clear, complete description, and the advantages are more clear and obvious, the following will be further described in detail by combining with the drawings. It should be understood that the specific embodiments described herein are part of the embodiments of the present application, not all embodiments, and are used to explain the embodiments of the present application, and do not limit the embodiments of the present application, all other embodiments obtained by those skilled in the art without creative labor are within the scope of protection of the present application.

[0043] Embodiment one, the present application provides a kind of technical scheme: a kind of Kettle-based database dynamic table splitting processing method, comprising the following steps:

[0044] The plug-in adopts a modular design and mainly includes a data source access module, a split table rule configuration module, a data splitting processing module, a target table writing module, and a log monitoring module. Each module is integrated through a plug-in extension interface of Kettle to ensure seamless connection with the Kettle platform.

[0045] Through the data source connection interface of Kettle, multiple types of data sources are supported, including relational databases (such as MySQL, Oracle, etc.), data files (such as CSV, Excel, etc.), and large table data to be split can be read.

[0046] The split table rule configuration module provides a visual configuration interface, allowing users to customize split table fields (such as time fields, region fields, etc.), split table methods (such as splitting by field value range, splitting by hash value, splitting by enumeration value, etc.), and naming rules and storage locations of target sub-tables.

[0047] According to the rules set in the split table rule configuration module, the large table data read is parsed and split. The matching of data and target sub-tables is realized through algorithms written in Java language, ensuring that each data can be accurately allocated to the corresponding sub-table.

[0048] According to the results of the data splitting processing module, the split data is written into the corresponding target sub-table. Batch writing and transaction processing are supported to ensure the integrity and consistency of data writing, and the writing syntax and characteristics of different databases are adapted.

[0049] In the second embodiment, based on the first embodiment, a Kettle dynamic split table implementation step is proposed

[0050] Step 1: Preparation 1. Environment configuration

[0051] Install Kettle (PDI) to ensure connection with source database and target database (supports MySQL, Oracle, etc.).

[0052] Create a split table template (including index and constraint) in the target database in advance, or generate it dynamically through Kettle.

[0053] 2. Core component selection

[0054] Transformation: Process data splitting logic.

[0055] Job: Schedule split table tasks (such as timing execution).

[0056] Core control: Table input, calculator, routing, table output, SQL script. Step 2: Dynamic split table conversion design (core) Create a conversion to realize "data reading → split table field calculation → dynamic routing → writing

[0057] Full process of corresponding sub-table. Step 2.1: Read source data using table input control:

[0058] Configure source database connection, write SQL to read data that needs to be sub-tabled (can add conditions for filtering, such as incremental data):

[0059] SELECT id,order_no,amount,create_time FROM order

[0060] WHERE create_time>='${start_time}'AND create_time<'

[0061] ${end_time}';

[0062] (${start_time} and ${end_time} are parameters, supporting incremental extraction) Step 2.2: Calculate sub-table identifier using calculator control, generate target table name according to sub-table rules:

[0063] Target field: target_table (string type)

[0064] Calculate expression (sub-table by month):

[0065] "order_"+DATE_FORMAT(create_time,"yyyyMM")

[0066] (Kettle supports Java date formatting syntax, converts create_time to 20230 1 format)

[0067] Step 2.3: Dynamically determine if the sub-table exists

[0068] Use SQL control to execute query to determine if the target sub-table has been created:

[0069] Different database types, different judgment statements (take MySQL as an example): SELECT COUNT(*)AS table_exists

[0070] FROM information_schema.tables

[0071] WHERE table_name='${target_table}'AND table_schema='your_database';

[0072] (${target_table} is the variable generated in step 2.2) Step 2.4: Dynamically create a sub-table (if it does not exist)

[0073] 1. Use the routing control (Switch / Case):

[0074] Condition: If table_exists = 0 (table does not exist), enter the "create sub-table" branch.

[0075] 2. Use the SQL control to execute the table creation statement (the template must be consistent with the source table structure): CREATE TABLE${target_table}(

[0076] id BIGINT PRIMARY KEY,order_no VARCHAR(50)NOT NULL,amount DECIMAL(10,2),

[0077] create_time DATETIME,

[0078] INDEX idx_create_time(create_time)

[0079] )ENGINE=InnoDB DEFAULT CHARSET=utf8;

[0080] Step 2.5: Dynamically write to the target sub-table Use the table output control to configure the dynamic table name:

[0081] Target database connection Select the target database.

[0082] Table name fill in: ${target_table} (reference variable generated in step 2.2).

[0083] Field mapping: Ensure that the source table fields correspond one-to-one with the sub-table fields. Step 3: Job scheduling (optional) Create a job (Job) to implement the scheduled execution of sub-table conversion:

[0084] 1. Start: Start node of the job.

[0085] 2. Set parameters: Define start_time and end_time (such as the date range of the previous day) through the variable control.

[0086] 3. Execute conversion: Call the sub-table conversion created in step 2.

[0087] 4. Success / failure handling: Add email notification or log recording nodes.

[0088] 5. Schedule configuration: through the Kitchen tool of Kettle combined with Linux cronta b or Windows task plan, set the timing execution (such as daily execution in the early morning).

[0089] Embodiment three, on the basis of embodiment two, proposes a kind of database dynamic table processing method based on Kettle system, comprising:

[0090] Configuration parameter receiving module: receives table splitting configuration parameters through Kettle graphical interface, and the table splitting configuration parameters include shard key, table splitting algorithm and table splitting quantity;

[0091] Dynamic table splitting strategy engine construction module: load table splitting rule metadata, construct dynamic table splitting strategy engine, and the strategy engine supports hash fragmentation, range fragmentation and time fragmentation algorithm;Dynamic table splitting strategy engine construction module includes:

[0092] Algorithm recommendation submodule: based on data sampling analysis, automatically recommend table splitting algorithm, by sampling part of data in database, analyze data characteristics, recommend appropriate table splitting algorithm according to data distribution and table splitting algorithm characteristics;Custom rule submodule: provide Groovy script interface to support custom fragmentation rules, users can define personalized fragmentation logic by writing Groovy script;Rule management submodule: maintain table splitting rule version history, support rule rollback, record the change information of each table splitting rule, and can be restored to the specified version of table splitting rule when needed.

[0093] Routing decision module: embedded in Kettle data conversion process, real-time calculation target table splitting routing according to the shard key;Routing decision module includes: hash calculation submodule: consistent hash calculation is carried out on shard key to obtain virtual node mapping, and shard key is mapped to virtual node through consistent hash algorithm, and virtual node corresponds actual table, so as to determine the target table range of data;Dynamic adjustment submodule: dynamically adjust hash ring distribution according to the current table splitting quantity, when the table splitting quantity changes, the position of virtual node on hash ring is recalculated, to ensure that data is evenly distributed to each table;Query rewriting submodule: support composite query routing rewriting across tables, for composite query involving multiple tables, rewrite query conditions according to table splitting rules, so that query can be executed on corresponding table and the results are merged.

[0094] The multi-thread parallel execution engine module includes: a thread pool management submodule: dynamic thread pool management is adopted, concurrency is automatically adjusted according to the number of sub-tables, the number of threads in the thread pool is dynamically allocated according to the current system sub-table number, and parallel execution of sub-table data write operation is realized; a data buffer submodule: a batch data buffer mechanism is provided, each sub-table corresponds to an independent write queue, data allocated to each sub-table according to the routing result is put into the corresponding queue, and a special thread takes data from the queue for writing; a back pressure control submodule: a back pressure control strategy is implemented to prevent memory overflow, when the amount of data in the data write queue exceeds the set threshold, the data routing and writing operation is suspended, and after the queue data amount decreases to a safe level, the execution is continued.

[0095] The distributed transaction management module: a distributed transaction management mechanism is adopted to ensure the atomicity of cross-sub-table operation; the distributed transaction management module includes: a transaction coordination submodule: a two-phase commit protocol is adopted to coordinate multi-sub-table transactions, when a transaction starts, the coordinator sends a transaction start message to all participants, the participants execute operations and feed back the results, the coordinator decides to commit or rollback the transaction according to the feedback and sends instructions; a retry control submodule: a timeout retry mechanism is provided, the maximum number of retries is configurable, the transaction is automatically retried when it times out, the number of retries is set through a configuration file, and if the transaction fails after the maximum number of retries, it is considered that the transaction execution fails; a log management submodule: transaction log persistent storage is realized to support breakpoint resuming, transaction execution key information such as start time, participant information and operation content is recorded and stored persistently to the disk, and after system failure recovery, the breakpoint resuming can be resumed according to the log to guarantee transaction integrity and consistency.

[0096] Although the embodiments of the present application have been shown and described, it can be understood by those skilled in the art that various changes, modifications, replacements and variations can be made to the embodiments without departing from the principles and spirits of the present application, and the scope of the present application is defined by the appended claims and their equivalents.

Claims

1. A Kettle-based database dynamic table splitting processing method, characterized in that: The method comprises the following steps: S1. receiving table splitting configuration parameters through a Kettle graphical interface, the table splitting configuration parameters comprising a shard key, a table splitting algorithm and a table splitting number; S2. loading table splitting rule metadata, constructing a dynamic table splitting strategy engine, the strategy engine supporting hash splitting, range splitting and time splitting algorithms; wherein the construction of the dynamic table splitting strategy engine comprises: automatically recommending a table splitting algorithm based on data sampling analysis; providing a Groovy script interface to support custom splitting rules; maintaining a table splitting rule version history, and supporting rule rollback; S3. embedding a routing decision module in a Kettle data conversion process, and calculating a target table splitting route in real time according to the shard key; S4. distributing data to corresponding tables according to the routing results through a multi-thread parallel execution engine; wherein the parallel execution engine comprises: dynamic thread pool management, automatically adjusting concurrency according to the table splitting number; a batch data buffering mechanism, each table corresponding to an independent write queue; a back pressure control strategy to prevent memory overflow; S5. using a distributed transaction management mechanism to ensure the atomicity of cross-table operations; wherein the distributed transaction management uses: a two-phase commit protocol to coordinate multi-table transactions; a timeout retry mechanism, with a maximum retry number configurable; a transaction log persistent storage, supporting breakpoint resuming.

2. The Kettle-based database dynamic table splitting processing method of claim 1, wherein: In the process of automatically recommending a table splitting algorithm based on data sampling analysis during the construction of the dynamic table splitting strategy engine in step S2: sampling data in the database, analyzing data characteristics, and recommending the most suitable table splitting algorithm according to data distribution, data size factors and the characteristics of hash splitting, range splitting and time splitting algorithms.

3. The Kettle-based database dynamic table splitting processing method of claim 2, wherein: In the implementation of the routing decision module in step S3: the process of consistent hash calculation for the shard key to obtain virtual node mapping is as follows: the shard key is mapped to a virtual node through a consistent hash algorithm, each virtual node corresponds to one or more actual tables, and the target table range to which the data should be routed is determined in this way; the process of dynamically adjusting the hash ring distribution according to the current table number is as follows: when the table number changes, the distribution of virtual nodes on the hash ring is recalculated to ensure that data can be evenly distributed to each table; the process of supporting composite query routing rewriting across tables is as follows: for a composite query involving multiple tables, the query conditions are rewritten according to the table splitting rules, so that the query can be executed on the corresponding tables and the results can be combined.

4. The Kettle-based database dynamic table splitting processing method of claim 3, wherein: The process of dynamic thread pool management of the engine in step S4 is automatically adjusting concurrency according to the number of sub-tables: dynamically allocating the number of threads in the thread pool according to the current system sub-table number, ensuring that the data write operation of each sub-table can be executed in parallel, improving the data write efficiency; the process of each sub-table corresponding to an independent write queue in the batch data buffering mechanism is: creating an independent data write queue for each sub-table, putting the batch data allocated to the sub-table according to the routing result into the corresponding queue, and then taking out the data from the queue by a special thread for write operation; the process of the back pressure control strategy to prevent memory overflow is: when the amount of data in the data write queue exceeds a certain threshold, the data routing and write operation is suspended, and after the amount of data in the queue drops to a safe level, the execution is continued, avoiding memory overflow caused by too fast data write speed.

5. The Kettle-based database dynamic table splitting processing method according to claim 4, characterized in that: The process of step S5 of distributed transaction management using two-phase commit protocol to coordinate multi-sub-table transactions is: at the beginning of the transaction, the coordinator sends a transaction start message to all participants, the participants execute transaction operations and feed back the execution results to the coordinator; the coordinator decides whether to commit the transaction or roll back the transaction according to the feedback results of all participants, and sends corresponding instructions to all participants; the process of the maximum retry number configurable in the timeout retry mechanism is: when a timeout occurs during transaction execution, the system will automatically perform a retry operation, and the retry number can be set through a configuration file; when the maximum retry number is reached and the transaction is still not successful, it is considered that the transaction execution fails; the process of supporting breakpoint resume in transaction log persistent storage is: recording the key information in the transaction log during transaction execution, such as transaction start time, participant information, and operation content, and persistently storing the transaction log on the disk; When the system fails or is interrupted abnormally, the breakpoint resume can be performed according to the transaction log during recovery, ensuring the integrity and consistency of the transaction.

6. A system for Kettle-based database dynamic table splitting processing method according to claim 5, characterized in that: It includes: A configuration parameter receiving module receives sub-table configuration parameters through a Kettle graphical interface, the sub-table configuration parameters including a shard key, a sub-table algorithm, and a sub-table number; A dynamic sub-table strategy engine construction module loads sub-table rule metadata and constructs a dynamic sub-table strategy engine, the strategy engine supporting hash sharding, range sharding, and time sharding algorithms; A routing decision module is embedded in a Kettle data conversion process and calculates target sub-table routing in real time according to the shard key; A multi-thread parallel execution engine module distributes data to corresponding sub-tables according to routing results; A distributed transaction management module uses a distributed transaction management mechanism to ensure the atomicity of cross-sub-table operations.

7. The system for Kettle-based database dynamic table splitting processing method according to claim 6, characterized in that: The dynamic sub-table strategy engine construction module includes: An algorithm recommendation submodule automatically recommends a sub-table algorithm based on data sampling analysis, analyzes data characteristics by sampling part of the data in the database, and recommends a suitable sub-table algorithm according to data distribution and sub-table algorithm characteristics; A custom rule submodule provides a Groovy script interface to support custom sharding rules, and users can define personalized sharding logic by writing a Groovy script; Rule management submodule: maintain the version history of the table splitting rule, support rule rollback, record the change information of each table splitting rule, and restore the specified version of the table splitting rule when needed.

8. The system for Kettle-based database dynamic table splitting processing method according to claim 7, characterized in that: The routing decision module comprises: Hash calculation submodule: consistent hash calculation is performed on the shard key to obtain virtual node mapping. The shard key is mapped to a virtual node through a consistent hash algorithm, and the virtual node corresponds to an actual table. The target table range of the data is determined in this way; Dynamic adjustment submodule: dynamically adjust the hash ring distribution according to the current number of tables. When the number of tables changes, the position of the virtual node on the hash ring is recalculated to ensure that the data is evenly distributed to each table. Query rewriting submodule: support cross-table composite query routing rewriting. For a composite query involving multiple tables, the query condition is rewritten according to the table splitting rule, so that the query can be executed on the corresponding table and the results can be combined.

9. The system for Kettle-based database dynamic table splitting processing method according to claim 8, characterized in that: The multi-thread parallel execution engine module comprises: Thread pool management submodule: dynamic thread pool management is adopted to automatically adjust the concurrency according to the number of tables. The number of threads in the thread pool is dynamically allocated according to the current system table number, and parallel execution of table data write operations is realized. Data buffering submodule: batch data buffering mechanism is provided. Each table corresponds to an independent write queue. The data batch allocated to each table according to the routing result is put into the corresponding queue, and a special thread takes data from the queue for writing. Back pressure control submodule: implement back pressure control strategy to prevent memory overflow. When the amount of data in the data write queue exceeds the set threshold, suspend data routing and writing operations. When the amount of data in the queue decreases to a safe level, continue execution.

10. The system for Kettle-based database dynamic table splitting processing method according to claim 9, characterized in that: The distributed transaction management module comprises: Transaction coordination submodule: adopt two-phase commit protocol to coordinate multi-table transactions. When a transaction starts, the coordinator sends a transaction start message to all participants. The participants execute the operation and feed back the result. The coordinator decides to commit or rollback the transaction according to the feedback and sends the instruction. Retry control submodule: timeout retry mechanism, maximum retry times configurable, transaction execution timeout automatically retries, retry times set through configuration file, if still unsuccessful after maximum number of retries, consider transaction execution failed. Log management submodule: realize transaction log persistent storage to support breakpoint resume. Record transaction execution key information such as start time, participant information and operation content, and persistently store to disk. After system failure recovery, the log can be used to resume the transaction, ensuring transaction integrity and consistency.

Images