Data synchronization desensitization method and device, computer device, and readable storage medium

Abstract

The application provides a data synchronization desensitization method and device, computer equipment and a readable storage medium. The method comprises the following steps: preconfiguring synchronization parameters; receiving a change event of a source database output by a change data capture engine; performing desensitization processing on an event load of the change event according to a preset desensitization function and desensitization parameters; judging an event type of the change event; mapping an abstract type of a corresponding field in the event load to a physical type that can be understood by a target database according to the event type and a dialect identifier, to obtain type information of the field; generating a parameterized equivalent SQL template compatible with a dialect of the target database according to the dialect identifier, the event type and the type information of the field; binding the event load to the parameterized equivalent SQL template to obtain a parameterized SQL statement after binding; and executing the parameterized SQL statement in the target database. Through the application, a general data synchronization and desensitization mechanism is constructed.

Description

Technical Field

[0001] This invention relates to the field of data processing technology, and in particular to a data synchronization and desensitization method, apparatus, computer equipment, and readable storage medium. Background Technology

[0002] With the widespread adoption of big data and cloud computing, enterprises' demand for real-time data analysis and online applications continues to grow. As a crucial link in the data chain, the efficiency, stability, and functional completeness of data synchronization directly impact the usability of downstream data warehouses, analytical databases, and business applications. A typical requirement is to capture data changes in the source database (e.g., MySQL) in real time and synchronize them to the target data warehouse or analytical database to support data ingestion, warehousing, and service provisioning at the minute or even second level.

[0003] In many industry scenarios (such as finance, government and enterprise, and internet companies expanding overseas in compliance with regulations), in order to ensure the security of customer identity information and sensitive data, it is often necessary to perform anonymization (such as hashing, encryption, or masking) on ​​specified tables / fields during real-time synchronization. However, most real-time synchronization tools on the market lack the ability to perform configurable anonymization of fields at the CDC event level, making it difficult to simultaneously meet the dual requirements of "low-latency synchronization" and "field-level security compliance".

[0004] Taking Apache FlinkCDC as an example, it can capture change events from various mainstream databases and distribute them in a streaming manner, and can also perform data anonymization processing in conjunction with custom functions. However, in practical engineering applications, although the community provides adapter connectors for different types of databases, for certain specific target databases, especially emerging databases or databases that are compatible with specific protocols but have dialect differences, such as TiDB, the official or mature Sink Connector is not perfect or even missing, resulting in the need for additional development or modification when implementing it.

[0005] Therefore, how to build a universal data synchronization and desensitization mechanism has become a technical problem that urgently needs to be solved in this field. Summary of the Invention

[0006] The purpose of this invention is to provide a data synchronization and desensitization method, apparatus, computer device, and readable storage medium to solve the aforementioned technical problems in the prior art.

[0007] On the one hand, in order to achieve the above objectives, the present invention provides a data synchronization and desensitization method.

[0008] The data synchronization and desensitization method includes: pre-configuring synchronization parameters, wherein the synchronization parameters include desensitization parameters and a dialect identifier of the target database; receiving change events from the source database output by a change data capture engine; performing desensitization processing on the event payload of the change events according to a preset desensitization function and the desensitization parameters; determining the event type of the change events, wherein the event types include table structure change events and data change events; mapping the abstract type of the corresponding field in the event payload to a physical type understandable by the target database according to the event type and the dialect identifier, thereby obtaining the field type information; generating a parameterized equivalent SQL template compatible with the dialect of the target database according to the dialect identifier, the event type, and the field type information; binding the event payload to the parameterized equivalent SQL template to obtain a bound parameterized SQL statement; and executing the parameterized SQL statement in the target database.

[0009] Furthermore, the synchronization parameters also include JDBC connection parameters and connection pool setting parameters. After receiving the change event of the source database output by the change data capture engine, the method further includes: generating a unique pool identifier based on the host, port, username, and connection identifier of the target database in the JDBC connection parameters; searching for a connection pool in the global connection pool registry based on the unique pool identifier; if no connection pool is found, creating a connection pool according to the JDBC connection parameters and the connection pool setting parameters and registering it in the registry, and setting the name of the created connection pool as the unique pool identifier; and borrowing a real JDBC connection from the connection pool when an operation needs to be performed on the target database for the first time.

[0010] Furthermore, the data synchronization and desensitization method also includes at least one of the following: when borrowing a real JDBC connection from the connection pool fails, the retry interval is extended according to an exponential fallback strategy; when the number of retries and / or the total retry duration exceeds the corresponding threshold in the connection pool settings, an exception prompt is output; during the connection period, heartbeat detection is performed periodically to detect exceptions; when the exception detection determines that the connection pool is unavailable, the corresponding connection pool is removed from the registry and rebuilt according to the unique pool identifier; after each execution of the parameterized SQL statement, the real JDBC connection is returned to the connection pool; when the job terminates or a failover occurs, the corresponding connection pool is closed according to the unique pool identifier, and the unique pool identifier is deleted from the registry.

[0011] Furthermore, the step of mapping the abstract type of the corresponding field in the event payload to a physical type understandable by the target database based on the event type and the dialect identifier includes: determining a mapping rule based on the dialect identifier of the target database; determining a mapping range based on the event type; and mapping the abstract type of the corresponding field in the event payload to a physical type understandable by the target database within the mapping range based on the mapping rule.

[0012] Further, the step of generating a parameterized equivalent SQL template compatible with the target database dialect based on the dialect identifier, the event type, and the field type information includes: when the event type is the data change event, generating a data insertion template, a data update template, and / or a data deletion template; when the event type is the table structure change event, generating a table creation template, a column addition template, a column deletion template, a column type modification template, and / or a column renaming template.

[0013] Furthermore, when the change event is the data change event, before binding the event payload to the parameterized equivalent SQL template to obtain the bound parameterized SQL statement after generating a parameterized equivalent SQL template compatible with the target database dialect, the method further includes: when the operation type of the data change event is an insert event or an update event, writing the event payload to a first cache; when the operation type of the data change event is a delete event, writing the event payload to a second cache, and binding the event payload to the parameterized equivalent SQL template to obtain the bound parameterized SQL statement, the steps include: if the first cache meets the batch execution condition, binding the event payload in the first cache to the parameterized equivalent SQL template to obtain the bound first batch SQL statement; after executing the first batch SQL statement in the target database, binding the event payload in the second cache to the parameterized equivalent SQL template to obtain the bound second batch SQL statement.

[0014] Further, the step of binding the event load in the first cache to the parameterized equivalent SQL template includes: when the first cache involves two or more update events with the same primary key, only the last arriving update event is retained in the binding among the two or more update events, wherein the primary key is a set of columns in the target database that identify row records, used to specify the change target of the data change event; the method further includes: when the change event is a table structure change event, firstly executing the parameterized SQL statement corresponding to the current first cache and the second cache in the target database, and then executing the parameterized SQL statement corresponding to the table structure change event in the target database; when the task is closed or a global snapshot event is triggered, executing the parameterized SQL statement corresponding to the current first cache and the second cache in the target database.

[0015] On the other hand, in order to achieve the above objectives, the present invention provides a data synchronization and desensitization device.

[0016] The data synchronization and desensitization device includes: a configuration module for pre-configuring synchronization parameters, wherein the synchronization parameters include desensitization parameters and a dialect identifier of the target database; a desensitization module for receiving change events from the source database output by a change data capture engine, and performing desensitization processing on the event payload of the change events according to a preset desensitization function and the desensitization parameters; an event processing module for determining the event type of the change events, wherein the event types include table structure change events and data change events, mapping the abstract type of the corresponding field in the event payload to a physical type understandable by the target database according to the event type and the dialect identifier, obtaining the type information of the field, and generating a parameterized equivalent SQL template compatible with the dialect of the target database according to the dialect identifier, the event type, and the type information of the field; and a writing module for binding the event payload to the parameterized equivalent SQL template to obtain the bound parameterized SQL statement, and executing the parameterized SQL statement in the target database.

[0017] On the other hand, to achieve the above objectives, the present invention also provides a computer device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement the steps of the above method.

[0018] On the other hand, to achieve the above objectives, the present invention also provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps of the above method.

[0019] The data synchronization and desensitization method, apparatus, computer equipment, and readable storage medium provided by this invention first pre-configure synchronization parameters for runtime control, including desensitization parameters for field-level processing and dialect identifiers for adapting to the target database. Then, it receives change events output by the CDC engine and performs pre-defined desensitization processing on the event payload. Based on this, it identifies the event type (data change or table structure change) and performs type mapping on the abstract types in the event payload according to the dialect identifier to obtain physical type information understandable by the target database. It generates a parameterized equivalent SQL template based on the dialect identifier, event type, and type information, and then binds the specific field values ​​in the event payload to the template to form an executable statement. Finally, it executes the parameterized SQL statement in the target database. This invention seamlessly integrates field-level desensitization and cross-dialect type / syntax adaptation without changing the main CDC process. By performing desensitization processing at the event payload layer, it achieves a unified approach to real-time synchronization and security compliance. Dialect-driven type mapping and parameterized templates avoid type incompatibility and injection risks caused by dialect differences, ensuring that different target databases can correctly receive and execute the data, thus realizing a universal data synchronization and desensitization mechanism. Attached Figure Description

[0020] Various other advantages and benefits will become apparent to those skilled in the art upon reading the following detailed description of preferred embodiments. The accompanying drawings are for illustrative purposes only and are not intended to limit the invention. Furthermore, the same reference numerals denote the same parts throughout the drawings. In the drawings: Figure 1 A flowchart of a data synchronization and desensitization method provided in an embodiment of the present invention; Figure 2 This is a block diagram of the data synchronization and desensitization method provided in an embodiment of the present invention; Figure 3 A logical block diagram of the configuration module provided in an embodiment of the present invention; Figure 4 A logic block diagram of the connection module provided in an embodiment of the present invention; Figure 5 This is a logic block diagram of the event handling module provided in an embodiment of the present invention; Figure 6 A logic block diagram of the writing module provided in an embodiment of the present invention; Figure 7 This is a schematic block diagram of the data synchronization and desensitization device provided in an embodiment of the present invention; Figure 8 A hardware structure diagram of a computer device provided for an embodiment of the present invention. Detailed Implementation

[0021] To make the objectives, technical solutions, and advantages of this invention clearer, the invention will be further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the invention. All other embodiments obtained by those skilled in the art based on the embodiments of this invention without inventive effort are within the scope of protection of this invention.

[0022] Example 1 This invention provides a data synchronization and desensitization method, which realizes a general data synchronization and desensitization mechanism. Specifically, Figure 1 This is a flowchart of the data synchronization and desensitization method provided in Embodiment 1 of the present invention. Figure 2 This is a block diagram of a data synchronization and de-identification method provided in an embodiment of the present invention. This data synchronization and de-identification method can be applied to data synchronization between Apache Flink and JDBC. The module executing the data synchronization and de-identification method acts as a connector, such as... Figure 1 and Figure 2 As shown, the data synchronization and desensitization method provided in this embodiment includes the following steps S101 to S108.

[0023] Step S101: Pre-configure synchronization parameters.

[0024] The synchronization parameters are configured through a configuration module. These parameters include de-identification parameters and a dialect identifier for the target database. De-identification parameters specify the list of tables / fields to be de-identified, the de-identification functions (such as hashing, encryption, masking), and necessary function parameters (salt value, key identifier, rule number, etc.). The dialect identifier identifies the target database's product / version or an equivalent database dialect, serving as the basis for subsequent type mapping and syntax conversion. De-identification parameters are centrally managed in the configuration module and support default values ​​and consistency checks. The dialect identifier can be directly provided in the configuration; alternatively, it can be automatically detected and written back to the configuration after a connection is established.

[0025] Step S102: Receive change events from the source database output by the change data capture engine.

[0026] The change event, also known as the CDC (Change Data Capture) event, is a standardized event output by the CDC engine. Optionally, it includes the event type (data change / table structure change), table name / database name, event payload (field name, abstract type, value), primary key set, and timestamp / location information. The event payload is a list of row-level data fields and their values ​​(e.g., {name: "order_id", type: "BigIntType", value: 1001}), which is the direct object for subsequent data masking and binding.

[0027] Step S103: Perform desensitization processing on the event payload of the change event according to the preset desensitization function and desensitization parameters.

[0028] The event payload is de-identified through the de-identification module. The de-identification scope targets the fields in the event payload that are configured to be de-identified, and supports hashing, encryption, masking, etc.

[0029] Step S104: Determine the event type of the change event.

[0030] In this embodiment, steps S104 to S106 are executed through the event handling module. Event types include table structure change events and data change events. Data change events include data insertion, updating, and deletion; table structure change events include table creation, adding / deleting columns, changing column types, and renaming columns.

[0031] Optionally, when determining the event type of a change event, it can be determined directly based on the event object type. If the event object belongs to the Schema Change Event category, it is determined as a table structure change event; at the same time, the specific DDL actions (such as creating a table, adding a column, changing the column type, renaming a column, etc.), table identifier, and metadata are parsed and recorded. If the event object belongs to the Data Change Event category, it is determined as a data change event, and the operation is further subdivided into INSERT, UPDATE, or DELETE, while the table identifier, primary key set, event payload, and metadata are recorded.

[0032] Alternatively, a determination method based on opcode or line semantics may be used, or other determination methods in the prior art may be used, which will not be described in detail in this embodiment.

[0033] Step S105: Based on the event type and dialect identifier, map the abstract type of the corresponding field in the event payload to the physical type that the target database can understand, and obtain the type information of the field.

[0034] Abstract types are general logical types independent of specific databases, carrying attributes such as length, precision, time precision, and nullability. For example, Flink's own abstract and universal expression of data types belongs to the abstract types in this step. Physical types are column type definitions that the target database can recognize. Optionally, mapping rules are selected based on dialect identifiers. For example, TiDB does not provide native BOOLEAN, so it is mapped to TINYINT(1); excessively long VarCharType can be downgraded to TEXT / MEDIUMTEXT. Type mapping is performed according to the selected mapping rules. The resulting metadata, such as column names, physical types and their parameters, nullability, default expressions, and primary key markers, constitute the mapping information. Optionally, for data change events such as data insertion or updating, full field mapping is performed; for data change events such as data deletion, only the primary key column is mapped; for table structure change events, columns that are newly added or changed in type are mapped; renaming or deleting columns does not involve new types and is not mapped.

[0035] Step S106: Generate a parameterized equivalent SQL template that is compatible with the target database dialect based on the dialect identifier, event type, and field type information.

[0036] Among them, the parameterized equivalent SQL template is a precompilable statement template that is compatible with the target dialect and uses placeholders uniformly. The parameterized equivalent SQL template avoids SQL injection and facilitates reuse and batch processing. The parameterized equivalent SQL template only contains the structure and the order / number of placeholders, and does not contain specific values. The specific parameter values ​​are bound in subsequent steps.

[0037] Step S107: Bind the event load to the parameterized equivalent SQL template to obtain the bound parameterized SQL statement.

[0038] Specifically, step S107 and the following step S108 are performed by the writing module.

[0039] Optionally, the field values ​​in the event payload are bound to the corresponding placeholders in a deterministic order according to the list in the template. For data change events involving data deletion, only the primary key value may be bound. The type information generated in step S105 is followed during binding to ensure semantic consistency. Further, optionally, multiple rows are bound in batches using the same template and submitted to reduce round-trip overhead.

[0040] Step S108: Execute parameterized SQL statements in the target database.

[0041] Optionally, pre-compiled reuse, transaction encapsulation, and failure retries can be used to improve stability. Meanwhile, when inserts / updates and deletions occur simultaneously in the same batch, the recommended execution order is inserts / updates first, followed by deletions, to ensure the correctness of the final state.

[0042] In the data synchronization and desensitization method provided in this embodiment, synchronization parameters for runtime control are first pre-configured, including desensitization parameters for field-level processing and dialect identifiers for adapting to the target database. Then, change events output by the CDC engine are received, and pre-defined desensitization processing is performed on the event payload. Based on this, the event type (data change or table structure change) is identified, and type mapping is performed on the abstract type in the event payload according to this, combined with the dialect identifier, to obtain physical type information understandable by the target database. A parameterized equivalent SQL template is generated based on the dialect identifier, event type, and type information. Then, the specific field values ​​in the event payload are bound to the template to form an executable statement, which is finally executed in the target database. Using the data synchronization and desensitization method provided in this embodiment, field-level desensitization and cross-dialect type / syntax adaptation can be seamlessly integrated without changing the main CDC process. By performing desensitization processing at the event payload layer, real-time synchronization and security compliance are unified. Dialect-driven type mapping and parameterized templates avoid type incompatibility and injection risks caused by dialect differences, ensuring that different target databases can correctly receive and execute the data, thus realizing a universal data synchronization and desensitization mechanism.

[0043] Optionally, in one embodiment, the synchronization parameters further include JDBC connection parameters and connection pool setting parameters. After receiving the change event of the source database output by the change data capture engine, the method further includes: generating a unique pool identifier based on the host, port, username, and connection identifier of the target database in the JDBC connection parameters; searching for a connection pool in the global connection pool registry based on the unique pool identifier; if not found, creating a connection pool according to the JDBC connection parameters and connection pool setting parameters and registering it in the registry, and setting the name of the created connection pool as the unique pool identifier; and borrowing a real JDBC connection from the connection pool when an operation needs to be performed on the target database for the first time.

[0044] Specifically, such as Figure 3 As shown, the configuration module is responsible for defining and managing all configuration options for the connector. A centralized parameter list is established, covering parameters for connection information, connection pool thresholds, batch trigger thresholds, retry control, database dialect identifiers, and server timezones. For each parameter, a type description, default value, valid range, and sensitivity level identifier are provided.

[0045] Synchronization parameters also include JDBC connection parameters, connection pool settings, and batch write parameters. JDBC connection parameters include jdbcUrl, username, password, and driver properties such as database, serviceName, schema, and serverTimezone. Connection pool settings are used to instantiate the data source of the connection pool, such as maximumPoolSize, minimumIdle, connection acquisition timeout, idle timeout, maximum lifetime, and pool name.

[0046] The configuration module also provides default values ​​and validation logic for each parameter to ensure configuration correctness. For example, it validates the format of `jdbcUrl`, requiring it to begin with `jdbc:`; the port number must be within a valid range; and the number of records written in batches must not exceed a safe multiple of the connection pool's concurrency capacity. If the port is not explicitly specified in `jdbcUrl`, it is filled in using the dialect default value. If a configuration conflict or missing information is detected, the startup process is immediately terminated, and a traceable error message is generated.

[0047] like Figure 4 As shown, the module executing the data synchronization and desensitization method also includes a connection module. During the startup phase, the configuration module performs item-by-item verification to complete the legality check mechanism and establishes a connection with the target database through configuration parameters. Specifically, a unique pool identifier is first generated based on the host, port, username, and database connection identifier in the JDBC connection parameters. The database connection identifier can be extracted from jdbcUrl; if missing, it can be read and written back after the first successful connection establishment. Optionally, the unique pool identifier (poolKey) is obtained by concatenating the host, port, username, and database connection identifier, used to uniquely identify a connection pool instance. Then, the connection pool is searched in the global connection pool registry based on this unique pool identifier. The global connection pool registry is a thread-safe global mapping structure with the key poolKey and the value being the created data source / connection pool instance. The search is performed using poolRegistry.get(poolKey). If the pool is not found, a connection pool is created according to the JDBC connection parameters and connection pool settings and registered in the registry. The name of the created connection pool is set as the unique pool identifier, i.e., poolName is set as poolKey, and then poolRegistry.put(poolKey, ds) is used for registration. When a connection is found, the found connection pool is used directly. The pool instance is uniquely identified by the poolKey, ensuring that only one pool is created for the same database target, and connections from the same pool are reused subsequently.

[0048] Finally, a lazy loading strategy is adopted. This means that when an operation requiring interaction with the target database is first detected—for example, the first instance in the pipeline where interaction with the target database is necessary (e.g., the first batch of DML operations to be written to the database or the receipt of DDL operations to be executed)—a real JDBC connection is borrowed from the connection pool. Specifically, a real JDBC connection is borrowed from the connection pool using `DataSource.getConnection()`, and returned to the connection pool after execution. This avoids occupying database sessions during other stages, such as event receiving, data masking, type mapping, and template generation, minimizing idle connection and network handshake overhead, and preventing premature resource occupation when there is no write requirement.

[0049] In the data synchronization and anonymization method provided in this embodiment, after receiving a CDC event, the JDBC connection parameters and connection pool settings in the synchronization parameters are first read. Then, a unique pool identifier is generated based on the host, port, username, and database connection identifier in the JDBC parameters, and this identifier is used to search for an existing connection pool in the global connection pool registry. If no match is found, a connection pool is created and registered according to the JDBC and connection pool parameters, and the pool name is set to the unique pool identifier. A lazy loading strategy is adopted, and a real JDBC connection is borrowed from the connection pool for execution only when an operation needs to be performed on the target database for the first time. By using the data synchronization and anonymization method provided in this embodiment, the uniqueness and reuse of the connection pool are achieved through the unique pool identifier combined with the global registry, avoiding duplicate pool creation. Furthermore, the lazy loading connection borrowing technique ensures that a session is only occupied when interaction with the target database is actually needed, reducing resource consumption and handshake overhead. This provides a stable and reusable connection foundation for subsequent parameterized SQL execution, thereby improving the scalability and operational efficiency of the overall data synchronization chain.

[0050] Optionally, in one embodiment, the data synchronization and desensitization method further includes at least one of the following: when borrowing a real JDBC connection from the connection pool fails, the retry interval is extended according to an exponential fallback strategy; when the number of retries and / or the total retry duration exceeds the corresponding threshold in the connection pool settings, an exception message is output; during the connection period, heartbeat detection is performed periodically to detect exceptions; when the exception detection determines that the connection pool is unavailable, the corresponding connection pool is removed from the registry based on the unique pool identifier and rebuilt; after each execution of a parameterized SQL statement, the real JDBC connection is returned to the connection pool; when the job terminates or a failover occurs, the corresponding connection pool is closed based on the unique pool identifier, and the unique pool identifier is deleted from the registry.

[0051] Specifically, when borrowing a real JDBC connection from the connection pool fails, an exponential backoff strategy is used to extend the retry interval, such as backoff=min(B×R^(k-1),M), which can be stacked with jitter; when maxRetries or maxRetryDuration (from the connection pool setting parameter, which can be adjusted in the configuration module) reaches the upper limit, an exception is thrown and handed over to the upper layer restart strategy for handling, thereby achieving fault isolation.

[0052] Continue to refer to Figure 4 The connection module periodically sends lightweight heartbeat statements to prevent network devices from reclaiming long-lived connections. When an anomaly detection determines that the connection pool is unavailable, it removes the corresponding connection pool from the registry based on the unique pool identifier and rebuilds it. After job termination or failover, all connections in the pool are uniformly reclaimed to avoid leaving zombie sessions in the database. This includes calling Connection.close() to return the connection to the pool after each execution of parameterized SQL to prevent session leakage; upon receiving a termination signal or a failover, the unique pool identifier is located, the connection pool resources are closed and reclaimed, and the identifier is deleted from the registry to prevent leaving zombie sessions.

[0053] In the data synchronization and desensitization method provided in this embodiment, measures such as exponential rollback retry, periodic heartbeat and unavailability reconstruction, post-execution return, and termination recovery are used to ensure that the write link remains stable, recoverable, and resource-controllable under network jitter, database fluctuations, and changes in task lifecycle, thereby improving the reliability and availability of the overall synchronization process.

[0054] Optionally, in one embodiment, the step of mapping the abstract type of the corresponding field in the event payload to a physical type that the target database can understand, based on the event type and the dialect identifier, includes: determining a mapping rule based on the dialect identifier of the target database; determining a mapping range based on the event type; and mapping the abstract type of the corresponding field in the event payload to a physical type that the target database can understand within the mapping range based on the mapping rule.

[0055] Specifically, such as Figure 5As shown, the event handling module's processing steps include a data type mapping and conversion step. Since the source database and the target database on the terminal are very likely to be different, a data type mapping and conversion layer is required. During the mapping and conversion, the mapping rules are first determined. Mapping rules are functions or rule sets representing the conversion from abstract types to physical types, including constraints such as length / precision limits, time precision, and Boolean value conventions. The system maintains a registry of type mapping rules registered according to dialect identifiers: registry[dialectId]→profile. At runtime, it reads the rule set matching the current dialect identifier. Optionally, for abstract types with out-of-bounds length / precision, a pruning or degradation strategy is executed; for unsupported abstract types, logs and error codes are generated to block subsequent syntax generation and avoid runtime failures.

[0056] After determining the mapping rules, the mapping scope is determined based on the event type. The mapping scope represents the set of fields that need to participate in the mapping from abstract type to physical type, and the event type determines which columns are mapped. For data change events, data insertion and updates involve mapping all fields, while data deletion only involves mapping the primary key field. For table structure change events, adding / modifying column types only maps the added or changed columns, while renaming / deleting columns do not require type mapping. After obtaining the list of columns to be mapped, the abstract types of the corresponding fields in the event payload are mapped to physical types that the target database can understand based on the mapping rules, thus obtaining the field type information. The metadata generated for each mapped column includes: column name, physical type and its parameters (length / precision / time precision), nullability, default value, and primary key tag. Optionally, after obtaining the mapping rules and mapping scope, the abstract type and additional attributes (length, precision, nullability, etc.) are read column by column according to the scope, the mapping rules are applied to generate the physical type, and the field type information set for the event is generated and summarized, which is then passed to the syntax conversion module to generate a parameterized equivalent SQL template.

[0057] Taking a data change event as an example, the event model received by the event module is as follows: JSON { Event Type: "DataChange", Table Name: "order_tbl", Field List: [{ Name:"order_id", Type:"BigIntType", Value: 1001},{ Name:"user_name", Type:"VarCharType(1024)", Value: "alice"},{ Name:"order_additional_info", Type:"VarCharType(100000)", Value: "xxx"},{ Name:"order_amount", Type:"DecimalType(10,2)", Value: 259.50},{ Name:"is_test", Type:"BooleanType(10,2)", Value:"true"},{ Name:"created_at", Type:"TimestampType(3)", Value:"2025-06-15 10:23:45.123"}], Primary Key: ["order_id"]} The target database cannot directly accept the event types of the raw data. Taking TiDB as an example, the customized transformation performed is as follows: SQLorder_id->BIGINTuser_name->VARCHAR(1024)order_additional_info->MEDIUMTEXTorder_amount->DECIMAL(10,2)is_test->TINYINT(1)created_at->DATETIME(3) After converting the original data type and additional information (such as length, precision, etc.), the type information can be passed to the syntax conversion part of the event handling module.

[0058] In the data synchronization and anonymization method provided in this embodiment, mapping rules are first selected based on dialect identifiers. Then, the mapping range is determined based on event types, and within this range, the mapping rules are applied to map abstract types to physical types, thereby obtaining the field type information and delivering it to the syntax conversion part. This further generates a parameterized equivalent SQL template compatible with the target dialect. Using the data synchronization and anonymization method provided in this embodiment avoids execution failures caused by type incompatibility on different target databases and avoids unnecessary processing of fields that do not need to participate in the current operation. Simultaneously, the generated field type information provides accurate metadata support for the correctness of the subsequent syntax conversion part and pre-compilation reuse, thereby improving the compatibility of the entire real-time synchronization link.

[0059] Optionally, in one embodiment, the step of generating a parameterized equivalent SQL template compatible with the target database dialect based on the dialect identifier, event type, and field type information includes: when the event type is a data change event, generating a data insertion template, a data update template, and / or a data deletion template; when the event type is a table structure change event, generating a table creation template, a column addition template, a column deletion template, a column type modification template, and / or a column renaming template.

[0060] Specifically, due to the different target databases, the supported SQL syntax will vary to varying degrees. For example... Figure 5 As shown, the syntax conversion part of the event handling module generates a parameterized equivalent SQL template based on the target database dialect.

[0061] When the event type is a data change event, the data insertion template, data update template, and / or data deletion template are generated using the field type information and dialect identifier obtained in the previous step. Placeholders (such as ?) are used uniformly for row values ​​to prevent SQL injection and facilitate pre-compilation reuse. The order of the placeholders corresponds one-to-one with the column list, and subsequent bindings are executed in this order. Table names and column names are escaped according to dialect rules to ensure compatibility with reserved words or special characters. Insertion / update uses the equivalent UPSERT semantics of this dialect, and deletion uses batch deletion semantics based on primary key conditions.

[0062] When the event type is a table structure change event, parameterized table creation templates, column addition templates, column deletion templates, column type modification templates, and / or column renaming templates are generated based on the field type information and dialect identifiers. The statements are organized according to the target database's DDL syntax to achieve dialect compatibility. Furthermore, IF NOT EXISTS / IF EXISTS statements are added, provided the dialect is supported, to enhance fault tolerance for exception retries.

[0063] For example, when the change event is a table structure change event, the table structure information received by the event handling module is roughly as follows: SQL { Event type: "SchemaChange_AddColumn", Table name: "order_tbl", Add a new column: { Name: "status", Abstract type: "VarCharType(32)", Nullable: "No"}, Position: "AFTER order_amount" } Taking TiDB as the output database as an example, in order to translate the above events into a language that TiDB can understand, the events are translated into: SQL ALTER TABLE order_tbl ADD COLUMN IF NOT EXISTS status VARCHAR(32) NOT NULL AFTER order_amount; In this way, TiDB can recognize this structure change statement and adds syntax such as IF NOT EXISTS, which can make the statement be executed repeatedly without errors, and is used to handle some exception retries.

[0064] When the change event is a data change event, the data information received by the event handling module is roughly as follows: JSON { Event type: "DataChange", Table name: "order_tbl", Field list: [ { Name: "order_id", Type: "BigIntType", Value: 1001}, { Name: "user_name", Type: "VarCharType(1024)", Value: "alice"}, { Name: "order_amount", Type: "DecimalType(10,2)", Value: 259.50}, { Name: "created_at", Type: "TimestampType(3)", Value: "2025-06-15 10:23:45.123"} ], Primary key: ["order_id"] } Taking TiDB as the output database as an example, in order to convert the event into a language that TiDB can understand, it needs to be converted as follows: SQL INSERT INTO order_tbl (order_id, user_name, order_amount, created_at) VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE user_name = VALUES(user_name), order_amount = VALUES(order_amount), created_at= VALUES(created_at); The VALUES (?, ?, ?, ?) are security measures implemented to prevent SQL injection. ONDUPLICATE KEY UPDATE ensures that even after multiple executions, the terminal database still contains only one record, preventing duplicate insertions. TiDB can directly understand this structure.

[0065] The data synchronization and anonymization method provided in this embodiment generates parameterized data insertion, data update, and / or data deletion templates that are dialect-compatible with the target database when the event type is a data change event; and generates parameterized table creation, column addition, column deletion, column type modification, and / or column renaming templates that are dialect-compatible with the target database when the event type is a table structure change event. The templates uniformly employ placeholder and identifier escaping strategies to ensure compilability and reusability. By generating dialect-compatible parameterized equivalent SQL templates for the two types of events, this method enables executable templates with equivalent semantics to be obtained for different target databases despite differences in type and syntax.

[0066] Optionally, in one embodiment, when the change event is a data change event, before binding the event payload to the parameterized equivalent SQL template to obtain the bound parameterized SQL statement after generating a parameterized equivalent SQL template compatible with the target database dialect, the method further includes: when the operation type of the data change event is an insert event or an update event, writing the event payload to a first cache; when the operation type of the data change event is a delete event, writing the event payload to a second cache, and binding the event payload to the parameterized equivalent SQL template to obtain the bound parameterized SQL statement, the steps include: if the first cache meets the batch execution conditions, binding the event payload in the first cache to the parameterized equivalent SQL template to obtain the bound first batch SQL statement; after executing the first batch SQL statement in the target database, binding the event payload in the second cache to the parameterized equivalent SQL template to obtain the bound second batch SQL statement.

[0067] Specifically, such as Figure 6As shown, after the event handling module outputs the SQL template, the execution on the terminal database (i.e., the target database) is completed by writing the template. After generating the parameterized equivalent SQL template, for data change events, when the operation type of the data change event is an insert or update event, the event payload is written to the first cache; when the operation type of the data change event is a delete event, the event payload is written to the second cache. Specifically, for insert and update events, the first cache writes parameter tuples, i.e., values ​​arranged in the template column order, for subsequent binding; the second cache writes primary key parameters, i.e., values ​​arranged in the primary key column order, for subsequent binding. Here, writing to the cache essentially writes the parameter data to be bound, rather than the already concatenated or bound SQL. Optionally, the first cache is grouped according to the target table's superimposed column list corresponding one-to-one with the SQL template and the target database's structure version, so that the same template can be reused in batches later. The second cache is indexed according to the target table's superimposed primary key.

[0068] Based on the above caching strategy, batch execution is triggered by judging batch execution conditions. These conditions may include, but are not limited to: the number of cached records reaching a preset limit (e.g., 3000 records); or exceeding a preset interval (e.g., 2 seconds) since the last batch. Meeting any of these conditions initiates the subsequent batch binding and execution phase. Specifically, the write module periodically checks whether several trigger conditions are met. Once met, batch writing is triggered. Batch writing can be performed when 3000 records have accumulated in the first cache. Simultaneously, to prevent situations where data cannot accumulate to 3000 records for an extended period, such as when data changes are infrequent, a periodic cache refresh event needs to be set. Setting the cache refresh time to 2 seconds means that a cache refresh write operation is required regardless of whether the data volume has accumulated to the batch write threshold.

[0069] During binding, the parameter tuples in the first cache are bound to placeholders one by one according to the fixed order of the list in the template. Several rows of parameters are bound sequentially and executed in batches. After executing the first batch of SQL statements in the target database, the deletion template is used to perform batch deletions based on primary key conditions. After completing the batch insertion or batch update for the first cache, the batch deletion is executed again to ensure that the final state of the same primary key is correct within the same batch. After successful execution, the second cache that has been executed in this batch is cleared.

[0070] In the data synchronization and desensitization method provided in this embodiment, after generating parameterized equivalent SQL templates, the payloads of insert / update events are first written to a first cache, and the payloads of delete events are written to a second cache. When the first cache meets the batch execution conditions, the event payloads in it are first bound to the corresponding templates to obtain the first batch of SQL and executed in the target database. Subsequently, the event payloads in the second cache are bound to the corresponding templates to obtain the second batch of SQL and executed in the target database. By using the data synchronization and desensitization method provided in this embodiment, by separating the cache and the batch order of insert / update followed by delete, high-throughput parameterized batch writing is achieved on the one hand, reducing network and log overhead; on the other hand, it ensures that the semantics of operations on the same primary key within the same batch are not conflicting and the final state is correct.

[0071] Optionally, in one embodiment, the step of binding the event payload in the first cache to the parameterized equivalent SQL template includes: when the first cache involves two or more update-type events with the same primary key, only the last arriving update-type event is retained for binding among the two or more update-type events, wherein the primary key is a set of columns in the target database that identify row records and is used to specify the change target of the data change event. The method further includes: when the change event is a table structure change event, first executing the parameterized SQL statement corresponding to the current first cache and second cache in the target database, and then executing the parameterized SQL statement corresponding to the table structure change event in the target database; when the task is closed or a global snapshot event is triggered, executing the parameterized SQL statement corresponding to the current first cache and second cache in the target database.

[0072] Specifically, during the binding process for the first cache, if two or more update events with the same primary key are involved in the same batch, that is, the same record is updated multiple times in a short period of time, only the last update event is retained for binding. In other words, the old record is overwritten with the new record, the latest record is retained, the number of data writes is reduced, and performance is improved.

[0073] For table structure change events, the execution order is as follows: Upon receiving the table structure change event from the event handling module, the data in the cache module is refreshed and written to the database. That is, the parameterized SQL statements corresponding to the current first and second caches are executed in the target database first, and then the table structure change statement is executed.

[0074] The following example illustrates the necessity of refreshing cached data before performing table structure changes.

[0075] Suppose the data currently in the cache is as follows, and it has not yet been actually written to the database.

[0076] SQL INSERT INTO order_tbl (order_id, user_name, order_amount, created_at) VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE user_name = VALUES(user_name), order_amount = VALUES(order_amount), created_at= VALUES(created_at); At this point, the write module receives a table structure change event, renaming the user_name column to user_name_cn. If the write module executes the table structure change event first and then tries to write to the old cache, it will find that the terminal database cannot recognize the user_name column because the column has been renamed to user_name_cn.

[0077] In addition, when a task is closed or a global snapshot event is triggered, the batch cache also needs to be refreshed. This involves executing the parameterized SQL statements corresponding to the current first and second caches in the target database. Specifically, the task closure event is triggered when the Flink job receives a termination signal (including normal completion or manual cancellation). At this time, by refreshing the batch cache, it is ensured that all cached data is written to the database before the task terminates, avoiding the loss of intermediate data to be written due to process termination.

[0078] The task snapshot event is triggered when Flink performs a global snapshot. First, the write module synchronizes the data to ensure consistency between the snapshot point and the database state. In a distributed streaming environment, if the cache is not cleared during the snapshot period, replaying old events after a failure will generate duplicate writes. By flushing the cache before taking a snapshot, data inconsistency caused by replaying duplicates can be prevented.

[0079] In summary, the embodiments provided in this application fill a gap in the field of JDBC real-time synchronization. Combining CDC with a general JDBC interface, a seamless mapping process from abstract events to physical SQL is designed, providing a general implementation that conforms to JDBC standards and supports streaming writes and online evolution. A unified type mapping and dialect adaptation layer is introduced to automatically convert abstract field types to the physical column types of various target databases; simultaneously, SQL statements such as insert / update, delete, table creation, column addition, column modification, and column renaming are generated according to the target dialect and fully integrated into the JDBC execution framework. On the write side, multiple triggering strategies (closing events, snapshot events, periodic polling) and row-level overwrite merging logic are implemented, extending the JDBC-driven single-row execution model to achieve millisecond-level streaming data persistence capabilities. It provides a complete solution for streaming change capture, batch pre-compilation, and online structure evolution for the traditional JDBC ecosystem. It eliminates the limitation of JDBC being able to only perform static batch writes, enabling any JDBC-compatible database to receive real-time updates from CDC without requiring developers to perform complex adaptations at the business code level. This improves the availability of technology for real-time database writes in "low latency + high consistency" scenarios within the industry and effectively fills the gap in this field among existing open-source and commercial middleware.

[0080] The embodiments provided in this application feature real-time, strongly consistent writing and high throughput capabilities. Utilizing database batch processing characteristics, multiple writes are merged into a single network round trip and a single transaction, minimizing network I / O and disk log overhead. An overlay buffering mechanism is introduced at the write end to merge multiple change events for the same row (or the same primary key), retaining only the latest state, thereby avoiding duplicate writes to the same record. "Insert / update" and "delete" operations are cached separately, and then multiple rows of INSERT…ON DUPLICATE KEY UPDATE … and DELETE…WHERE Epk IN (…) statements are constructed at the batch trigger point, interacting with the database through pre-compiled placeholders. This avoids the additional overhead of duplicate overlays, significantly improving the overall system throughput and achieving a data write speed of tens of thousands of records per second. Due to batch transactions and row-level overlay, the write order and idempotency of data change events are guaranteed, eliminating the risk of data corruption and loss caused by network retries or fault replays.

[0081] The embodiments provided in this application enable online table structure evolution without downtime. Upon receiving any table structure change event (adding a column, deleting a column, modifying a column type, renaming a column, creating a table, deleting a table, truncating a table), a global refresh notification is first triggered, forcing all currently buffered data to be written to the database. After the old buffer is cleared, the corresponding statement is precisely generated using a specialized dialect template, submitted to the terminal database for execution, and the local metadata (column list, column order, data type, nullability, etc.) is updated synchronously after successful execution. After refreshing the metadata, the SQL template and buffer context of the write module are dynamically reconstructed, so that subsequent data change events can be correctly serialized and written to the database according to the new structure. During real-time streaming synchronization, the database table structure can be freely added, deleted, and modified without pausing data writing; the strict order of table structure change events and data change events is guaranteed, avoiding execution failures caused by cached data under the old structure due to missing columns or type mismatches; for the business side, the write stop window is completely eliminated and the business continuity of database structure evolution and data synchronization is maintained, providing enterprise-level zero-downtime upgrade capabilities.

[0082] The embodiments provided in this application feature rapid cross-dialect adaptation and scalability. Statement concatenation and type mapping logic, which are strongly bound to specific databases, are separated into pluggable modules. Each database dialect only needs to implement a clear set of interfaces: such as templates for building insert / update SQL templates, templates for building delete SQL templates, templates for table structure changes (creating / adding / modifying / renaming columns), and mapping rules from abstract types to physical types. At runtime, the write module automatically loads the corresponding dialect implementation from the registry center using table identifiers, column metadata, and dialect identifiers, without recompiling or adjusting the core logic. Through unified caching and batch commit interfaces, batch write behavior across different dialects remains consistent, with differences only at the template and type mapping levels. Only a minimal number of new dialect implementations need to be developed on top of existing code to quickly support other relational databases such as PostgreSQL and SQL Server; the write execution core is independent of any specific dialect, significantly reducing subsequent maintenance and iteration costs; high cross-dialect reusability allows for parallel synchronization of multiple databases in the same cluster without deleting or modifying existing logic, perfectly meeting the deployment requirements of cloud-native and multi-tenant environments.

[0083] Example 2 Corresponding to Embodiment 1 above, Embodiment 2 of the present invention provides a data synchronization and desensitization device. The technical features and corresponding technical effects can be referred to Embodiment 1 above, and will not be repeated in this embodiment. Figure 7 This is a schematic diagram of the data synchronization and desensitization device provided in an embodiment of the present invention, as shown below. Figure 7 As shown, the device includes: a configuration module 201, a desensitization module 202, an event handling module 203, and a writing module 204.

[0084] The configuration module 201 is used to pre-configure synchronization parameters, including de-identification parameters and a dialect identifier for the target database. The de-identification module 202 is used to receive change events from the source database output by the change data capture engine and perform de-identification processing on the event payload of the change events according to a preset de-identification function and the de-identification parameters. The event processing module 203 is used to determine the event type of the change events, including table structure change events and data change events. Based on the event type and the dialect identifier, the abstract type of the corresponding field in the event payload is mapped to a physical type that the target database can understand, thus obtaining the field type information. Based on the dialect identifier, the event type, and the field type information, a parameterized equivalent SQL template compatible with the dialect of the target database is generated. The writing module 204 is used to bind the event payload to the parameterized equivalent SQL template to obtain the bound parameterized SQL statement and execute the parameterized SQL statement in the target database.

[0085] Optionally, in one embodiment, the synchronization parameters further include JDBC connection parameters and connection pool setting parameters. The device further includes a connection module 205, configured to, after receiving the change event of the source database output by the change data capture engine, generate a unique pool identifier based on the host, port, username, and connection identifier of the target database in the JDBC connection parameters; search for a connection pool in the global connection pool registry based on the unique pool identifier; if no connection pool is found, create a connection pool according to the JDBC connection parameters and the connection pool setting parameters and register it in the registry, and set the name of the created connection pool as the unique pool identifier; and borrow a real JDBC connection from the connection pool when an operation needs to be performed on the target database for the first time.

[0086] Optionally, in one embodiment, the connection module 205 is further configured to perform one of the following: when borrowing a real JDBC connection from the connection pool fails, extend the retry interval according to an exponential fallback strategy; when the number of retries and / or the total retry duration exceeds the corresponding threshold in the connection pool settings parameters, output an exception prompt; during the connection period, periodically perform heartbeat detection to detect exceptions; when the exception detection determines that the connection pool is unavailable, remove the corresponding connection pool from the registry based on the unique pool identifier and rebuild it; after each completion of the execution of the parameterized SQL statement, return the real JDBC connection to the connection pool; when the job terminates or a failover occurs, close the corresponding connection pool based on the unique pool identifier and delete the unique pool identifier from the registry.

[0087] Optionally, in one embodiment, the event processing module includes a type mapping unit, configured to determine mapping rules based on the dialect identifier of the target database, determine a mapping range based on the event type, and map the abstract type of the corresponding field in the event payload to a physical type understandable by the target database within the mapping range based on the mapping rules.

[0088] Optionally, in one embodiment, the event processing module includes a syntax conversion unit, used to generate a data insertion template, a data update template, and / or a data deletion template when the event type is the data change event, and to generate a table creation template, a column addition template, a column deletion template, a column type modification template, and / or a column renaming template when the event type is the table structure change event.

[0089] Optionally, in one embodiment, when the change event is the data change event, before binding the event payload to the parameterized equivalent SQL template to obtain the bound parameterized SQL statement after generating a parameterized equivalent SQL template compatible with the target database dialect, the writing module is further configured to write the event payload to a first cache when the operation type of the data change event is an insert event or an update event; and write the event payload to a second cache when the operation type of the data change event is a delete event. The step of binding the event payload to the parameterized equivalent SQL template to obtain the bound parameterized SQL statement includes: if the first cache meets the batch execution condition, binding the event payload in the first cache to the parameterized equivalent SQL template to obtain the bound first batch SQL statement; and after executing the first batch SQL statement in the target database, binding the event payload in the second cache to the parameterized equivalent SQL template to obtain the bound second batch SQL statement.

[0090] Optionally, in one embodiment, the step of binding the event payload in the first cache to the parameterized equivalent SQL template includes: when the first cache involves two or more update-type events with the same primary key, only the last arriving update-type event is retained among the two or more update-type events to participate in the binding, wherein the primary key is a set of columns in the target database that identify row records, used to specify the change target of the data change event. The writing module is further configured to, when the change event is a table structure change event, first execute the parameterized SQL statement corresponding to the current first cache and the second cache in the target database, and then execute the parameterized SQL statement corresponding to the table structure change event in the target database; when the task is closed or a global snapshot event is triggered, execute the parameterized SQL statement corresponding to the current first cache and the second cache in the target database.

[0091] Example 3 This embodiment also provides a computer device, such as a smartphone, tablet computer, laptop computer, desktop computer, rack server, blade server, tower server, or cabinet server (including a standalone server or a server cluster composed of multiple servers), etc., capable of executing programs. Figure 8 As shown, the computer device 01 in this embodiment includes, but is not limited to, a memory 012 and a processor 011 that can be interconnected via a system bus, such as... Figure 8 As shown. It should be noted that, Figure 8 Only a computer device 01 with component memory 012 and processor 011 is shown; however, it should be understood that it is not required to implement all of the components shown, and more or fewer components may be implemented instead.

[0092] In this embodiment, the memory 012 (i.e., the readable storage medium) includes flash memory, hard disk, multimedia card, card-type memory (e.g., SD or DX memory), random access memory (RAM), static random access memory (SRAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), programmable read-only memory (PROM), magnetic memory, disk, optical disk, etc. In some embodiments, the memory 012 may be an internal storage unit of the computer device 01, such as the hard disk or memory of the computer device 01. In other embodiments, the memory 012 may also be an external storage device of the computer device 01, such as a plug-in hard disk, smart media card (SMC), secure digital (SD) card, flash card, etc., equipped on the computer device 01. Of course, the memory 012 may include both the internal storage unit and its external storage device of the computer device 01. In this embodiment, the memory 012 is typically used to store the operating system and various reference software installed on the computer device 01, such as the program code of the data synchronization and desensitization device in Embodiment 2. In addition, memory 012 can also be used to temporarily store various types of data that have been output or will be output.

[0093] In some embodiments, processor 011 may be a central processing unit (CPU), controller, microcontroller, microprocessor, or other data processing chip. Processor 011 is typically used to control the overall operation of computer device 01. In this embodiment, processor 011 is used to run program code stored in memory 012 or process data, such as data synchronization and desensitization methods.

[0094] Example 4 This embodiment also provides a computer-readable storage medium, such as flash memory, hard disk, multimedia card, card-type memory (e.g., SD or DX memory), random access memory (RAM), static random access memory (SRAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), programmable read-only memory (PROM), magnetic memory, disk, optical disk, server, App referencing e-commerce platform, etc., which stores computer programs. When the program is executed by a processor, it implements corresponding functions. The computer-readable storage medium of this embodiment is used to store a data synchronization and desensitization device, and when executed by a processor, it implements the data synchronization and desensitization method of Embodiment 1.

[0095] It should be noted that, in this document, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Unless otherwise specified, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes that element.

[0096] The sequence numbers of the above embodiments of the present invention are for descriptive purposes only and do not represent the superiority or inferiority of the embodiments.

[0097] Through the above description of the embodiments, those skilled in the art can clearly understand that the methods of the above embodiments can be implemented by means of software plus necessary general-purpose hardware platforms. Of course, they can also be implemented by hardware, but in many cases the former is a better implementation method.

[0098] The above are merely preferred embodiments of the present invention and do not limit the scope of the patent. Any equivalent structural or procedural transformations made based on the description and drawings of the present invention, or direct or indirect applications in other related technical fields, are similarly included within the scope of patent protection of the present invention.

Claims

1. A data synchronization and desensitization method, characterized in that, include: Pre-configured synchronization parameters, wherein the synchronization parameters include de-identification parameters and dialect identifiers of the target database; Receive change events from the source database output by the change data capture engine, wherein the event payload of the change event includes field name, abstract type and field value, and the abstract type is a general logical type that is independent of the specific database; The event payload of the change event is desensitized according to the preset desensitization function and the desensitization parameters; Determine the event type of the change event, wherein the event type includes table structure change events and data change events; Based on the event type and the dialect identifier, the abstract type of the corresponding field in the event payload is mapped to a physical type that the target database can understand, thereby obtaining the type information of the field. This includes: determining a mapping rule based on the dialect identifier of the target database, determining a mapping range based on the event type, and mapping the abstract type of the corresponding field in the event payload to a physical type that the target database can understand within the mapping range based on the mapping rule. Based on the dialect identifier, the event type, and the field type information, a parameterized equivalent SQL template compatible with the target database dialect is generated. The parameterized equivalent SQL template contains only the SQL structure and the order and number of placeholders, but does not contain specific values. The event payload is bound to the parameterized equivalent SQL template to obtain a bound parameterized SQL statement. Specifically, field values ​​in the event payload are bound to corresponding placeholders according to the deterministic order of the column list in the parameterized equivalent SQL template, while adhering to the field type information during binding. Execute the parameterized SQL statement in the target database.

2. The data synchronization and desensitization method according to claim 1, characterized in that, The synchronization parameters also include JDBC connection parameters and connection pool settings. After the step of receiving change events from the source database output by the change data capture engine, the method further includes: A unique pool identifier is generated based on the host, port, username, and connection identifier of the target database in the JDBC connection parameters; The connection pool is located in the global connection pool registry based on the unique pool identifier. If no connection is found, a connection pool is created according to the JDBC connection parameters and the connection pool settings, and registered in the registry. The name of the created connection pool is set as the unique pool identifier. When an operation is first detected to be required to be performed on the target database, a real JDBC connection is borrowed from the connection pool.

3. The data synchronization and desensitization method according to claim 2, characterized in that, It must also include at least one of the following: When borrowing a real JDBC connection from the connection pool fails, the retry interval is extended according to the exponential fallback strategy. When the number of retries and / or the total retry time exceeds the corresponding threshold in the connection pool settings, an error message is output. During connection, heartbeat checks are performed periodically to detect anomalies. When the anomaly detection determines that the connection pool is unavailable, the corresponding connection pool is removed from the registry based on the unique pool identifier and rebuilt. After each execution of the parameterized SQL statement, the real JDBC connection is returned to the connection pool. When a job terminates or a failover occurs, the corresponding connection pool is closed based on the unique pool identifier, and the unique pool identifier is deleted from the registry.

4. The data synchronization and desensitization method according to claim 1, characterized in that, The steps for generating a parameterized equivalent SQL template compatible with the target database dialect based on the dialect identifier, the event type, and the field type information include: When the event type is the data change event, generate a data insertion template, a data update template, and / or a data deletion template; When the event type is the table structure change event, generate table creation template, column addition template, column deletion template, column type modification template, and / or column renaming template.

5. The data synchronization and desensitization method according to claim 1, characterized in that, When the change event is a data change event, before generating a parameterized equivalent SQL template compatible with the target database dialect and binding the event payload to the parameterized equivalent SQL template to obtain the bound parameterized SQL statement, the method further includes: when the operation type of the data change event is an insert event or an update event, writing the event payload to a first cache; when the operation type of the data change event is a delete event, writing the event payload to a second cache. The step of binding the event load to the parameterized equivalent SQL template to obtain the bound parameterized SQL statements includes: if the first cache meets the batch execution conditions, binding the event load in the first cache to the parameterized equivalent SQL template to obtain the bound first batch SQL statements; after executing the first batch SQL statements in the target database, binding the event load in the second cache to the parameterized equivalent SQL template to obtain the bound second batch SQL statements.

6. The data synchronization and desensitization method according to claim 5, characterized in that, The step of binding the event payload in the first cache to the parameterized equivalent SQL template includes: when the first cache involves two or more update events with the same primary key, only the last update event that arrives is retained to participate in the binding, wherein the primary key is a set of columns in the target database that identify row records, used to specify the change target of the data change event; The method further includes: when the change event is the table structure change event, firstly executing the parameterized SQL statement corresponding to the current first cache and second cache in the target database, and then executing the parameterized SQL statement corresponding to the table structure change event in the target database; When the task is closed or a global snapshot event is triggered, the parameterized SQL statements corresponding to the current first cache and second cache are executed in the target database.

7. A real-time data synchronization and desensitization device, characterized in that, include: A configuration module is used to pre-configure synchronization parameters, wherein the synchronization parameters include de-identification parameters and dialect identifiers of the target database; The desensitization module is used to receive change events from the source database output by the change data capture engine, and perform desensitization processing on the event payload of the change event according to the preset desensitization function and the desensitization parameters. The event payload of the change event includes field name, abstract type and field value. The abstract type is a general logical type that is independent of the specific database. An event handling module is used to determine the event type of the change event, wherein the event type includes table structure change events and data change events. Based on the event type and the dialect identifier, the module maps the abstract type of the corresponding field in the event payload to a physical type understandable by the target database to obtain the field type information. Based on the dialect identifier, the event type, and the field type information, a parameterized equivalent SQL template compatible with the target database dialect is generated. The step of mapping the abstract type of the corresponding field in the event payload to a physical type understandable by the target database based on the event type and the dialect identifier to obtain the field type information includes: determining a mapping rule based on the dialect identifier of the target database; determining a mapping range based on the event type; and mapping the abstract type of the corresponding field in the event payload to a physical type understandable by the target database within the mapping range based on the mapping rule. The parameterized equivalent SQL template only contains the SQL structure and the order and number of placeholders, without specific values. The writing module is used to bind the event payload to the parameterized equivalent SQL template to obtain the bound parameterized SQL statement, and execute the parameterized SQL statement in the target database. The field values ​​in the event payload are bound to the corresponding placeholders according to the deterministic order of the list of columns in the parameterized equivalent SQL template, and the binding follows the type information of the field.

8. A computer device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the steps of the method according to any one of claims 1 to 6.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 6.

Images