Heterogeneous database data transmission method based on DataX framework

What is AI technical title?
AI technical title is built by Patsnap AI team. It summarizes the technical point description of the patent document.
The heterogeneous database data transmission method of the DataX framework solves the compatibility and transmission difficulties of MySQL and Oracle databases in data synchronization, and achieves efficient and reliable data transmission, which is suitable for fields such as finance and government.

CN121743071APending Publication Date: 2026-03-27CHINA CONSTRUCTION BANK

View PDF 0 Cites 0 Cited by

Patent Information

Authority / Receiving Office: CN · China
Patent Type: Applications(China)
Current Assignee / Owner
Filing Date: 2025-11-07
Publication Date: 2026-03-27

AI Technical Summary

Technical Problem

In existing technologies, MySQL and Oracle databases have significant differences in data types, date formats, etc., which makes it difficult for traditional JDBC direct connection methods to achieve efficient and reliable bidirectional data synchronization, difficult to transmit across network domains, poor data format compatibility, high risk of traditional interface calls, low synchronization efficiency, and insufficient breakpoint resume capability.

Method used

By employing the DataX framework, efficient data transmission between heterogeneous databases is achieved through steps such as generating and uploading TXT files, downloading and converting files to their formats, and loading data into the database. This is combined with FTP relay mechanisms, breakpoint resume mechanisms, and parallel channel mechanisms.

Benefits of technology

It enables efficient and reliable data transmission across platforms and network domains, ensuring data consistency and transmission reliability. It is particularly suitable for scenarios with high requirements for data security and stable transmission, such as finance and government.

✦ Generated by Eureka AI based on patent content.

Smart Images

Figure CN121743071A_ABST

Patent Text Reader

Abstract

The invention discloses a heterogeneous database data transmission method based on a DataX framework. The method comprises the steps that bidirectional transmission from MySQL to Oracle and bidirectional transmission from Oracle to MySQL are conducted, in the direction from MySQL to Oracle, a TXT file is generated and uploaded, after downloading, the TXT file is converted into a CSV through a Python script, then a Java program is triggered by XXL-Job to be loaded and put in storage, and manual retransmission is conducted when the Java program is abnormal; in the direction from Oracle to MySQL, CSV files are generated and cleaned, breakpoint resume is loaded and started through DataX, full-amount and increment synchronization is supported, and manual retransmission is carried out when abnormity occurs. According to the method, a data source is decoupled through standard JSON configuration, format differences are processed through Python scripts, cross-network obstacles are broken through FTP transfer, efficiency and reliability are guaranteed through XXL-Job and breakpoint resume, and efficient and reliable data transmission between heterogeneous databases is achieved.

Need to check novelty before this filing date? Find Prior Art

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of computer, in particular to a heterogeneous database data transmission method based on DataX framework. BACKGROUND

[0002] With the diversified development of enterprise information system, MySQL and Oracle database coexist has become a common phenomenon, but there are significant differences between the two in data types (such as NVARCHAR2, CLOB of Oracle, TEXT, VARCHAR of MySQL are incompatible), date format (DATE of Oracle contains time, minute and second, while DATE of MySQL only supports year and month) and other aspects, which leads to the difficulty of efficient and reliable two-way data synchronization by traditional JDBC direct connection. At the same time, the demand for data interaction between enterprises is increasing, covering modeling data transmission, data verification and other scenarios, which has the following characteristics: large data scale, efficient transmission of massive data is the key demand; low timeliness requirement, T+1 delay is acceptable, but data integrity and reliability must be ensured; cross-network domain transmission barrier, lack of effective file transfer mechanism between physically isolated production network and management network; poor data format compatibility, the data structure of MySQL and Oracle is incompatible, direct synchronization may cause failure; traditional interface calling risk, high concurrency may cause high service load. And the existing solutions have the following defects: low synchronization efficiency, the traditional single-threaded synchronization method has obvious performance bottleneck when processing large-scale data; insufficient breakpoint resume capability, incremental data synchronization may cause data loss or repetition due to interruption. These problems have a great constraint on the continuity of enterprise core business and data governance capability, and an efficient, reliable, cross-platform and cross-network domain automatic data transmission solution is urgently needed. SUMMARY

[0003] The present application relates to the technical field of computer, in particular to a heterogeneous database data transmission method based on DataX framework.

[0004] In order to achieve the above-mentioned purpose, the present application is realized by the following technical scheme:

[0005] The present application relates to the technical field of computer, in particular to a heterogeneous database data transmission method based on DataX framework.

[0006] The MySQL to Oracle direction data transmission comprises the following steps:

[0007] S1: TXT file generation and uploading: first, the party B synchronizes the MySQL data of the opposite party to the MySQL database of the party A through datax at a fixed time. The MySQL server deployed by the party A (bank or government unit) has the functions of front-end machine and data conversion. The DataX JSON configuration file is created in the MySQL server of the bank, the data source connection parameters are defined, the source data is read by using the DataX plug-in architecture, the TXT file is generated by using the txtfilewriter plug-in, the crontab is used to run the shell script to scan the file directory for saving the TXT data at a fixed time, and the generated TXT file is automatically uploaded to the specified directory of the FTP server of the bank;

[0008] S2: file download and format conversion: the shell script is run at a fixed time by using the crontab to scan the specified directory of the FTP server, the new TXT file is downloaded to the local storage, the Python script is executed to parse the TXT file, the special characters are processed, the date format is standardized, and the standard CSV file with table header is generated;

[0009] S3: data loading into the database: the XXL-Job scheduling platform triggers the Java batch processing program, reads the CSV file and writes into the Oracle database through the JDBC connection, and the Java program uses the MERGE statement to realize data merging;

[0010] If the application program judges that the file is not normally loaded into the database, the shell script is manually run to retransmit the data that fails to transmit, and the step S1 is returned.

[0011] Preferably, the data transmission from the Oracle to the MySQL includes the following steps:

[0012] S11: CSV file generation and cleaning: the bank's Java application program reads the data of the Oracle database at a fixed time by using the xxlJob, cleans the data, ensures that the field format meets the requirements of the MySQL, and exports the CSV file of the target data table;

[0013] S22: data loading and breakpoint resuming: the DataX directly calls the mysqlwriter plug-in to write the cleaned CSV file into the target MySQL database, enables the breakpoint resuming mechanism, supports the incremental data synchronization;

[0014] S33: full and incremental synchronization: the full data synchronization is performed by starting the DataX task, the parallel channel mechanism is supported to improve the efficiency, the incremental data synchronization is realized based on the update_time field, the failed task is automatically retried; if the application program judges that the file is not normally loaded into the database and notifies the operation and maintenance personnel by email, the xxljob is manually started to retransmit the data that fails to transmit, and the step S11 is returned.

[0015] Preferably, the data source connection parameters defined by the DataXJSON configuration file include the JDBC URL, account password and table structure of MySQL; and the bank FTP server is used for transferring data and only allows intranet access.

[0016] Preferably, the special characters processed by the Python script include line breaks.

[0017] Preferably, the Java program in step S3 submits transactions in batches every 5000 data to improve efficiency.

[0018] Preferably, in step S11, the data cleaning in the data transmission from Oracle to MySQL includes removing spaces and replacing illegal characters.

[0019] Preferably, in step S22, the checkpoint interval of the breakpoint resume mechanism enabled in the data transmission from Oracle to MySQL is 5000.

[0020] Preferably, in step S33, the interval of the automatic retry of failed tasks in the data transmission from Oracle to MySQL is 10 minutes.

[0021] Preferably, the standardized JSON configuration file is used to decouple heterogeneous data sources and support flexible expansion of multiple data sources.

[0022] The application of the heterogeneous database data transmission method based on the DataX framework includes the application of scenes with high requirements for data security and stable transmission, including finance and government affairs.

[0023] Beneficial effects: The application realizes efficient data transmission across servers, supports batch data transmission of heterogeneous databases in big data scenarios, guarantees data consistency and transmission reliability, and is particularly suitable for scenes with high requirements for data security and stable transmission, such as finance and government affairs. BRIEF DESCRIPTION OF DRAWINGS

[0024] Figure 1 is the data transmission flowchart from MySQL to Oracle of the application.

[0025] Figure 2 is the data transmission flowchart from Oracle to MySQL of the application. DETAILED DESCRIPTION

[0026] With reference to the accompanying drawings: clearly and completely describe the technical solutions in the embodiments of the present application. Obviously, the described embodiments are only a part of the embodiments of the present application, rather than all. Based on the embodiments in the present application, all other embodiments obtained by those of ordinary skill in the art without creative labor fall within the scope of the present application.

[0027] The present application aims to solve the problems of low efficiency, poor reliability, difficulty in cross-network domain transmission, poor data format compatibility, insufficient breakpoint resume capability, etc. in existing heterogeneous database (MySQL and Oracle) data transmission, and provides an efficient, reliable, cross-platform and cross-network domain automatic data transmission method, ensuring efficient, complete and reliable transmission of data between heterogeneous databases, meeting the needs of enterprises in modeling data transmission, data verification, etc. scenarios, especially suitable for financial, government and other fields with high requirements for data security and stable transmission.

[0028] Summary / Principle: including MySQL to Oracle direction data transmission and Oracle to MySQL direction data transmission.

[0029] Technology 1: MySQL to Oracle direction data transmission

[0030] S1: TXT file generation and upload: create a DataXJSON configuration file on the MySQL server of Party A (bank or government agency) to define the data source connection parameters (such as MySQL's JDBCURL, account password and table structure). Use the DataX plug-in architecture to call mysqlreader to read source data, and generate TXT files through the txtfilewriter plug-in. Use crontab to run shell scripts to scan the file directory where the txt data is saved regularly, and automatically upload the generated TXT files to the specified directory of Party A's FTP server (Party A's FTP server is used for data transfer, and only allows intranet access).

[0031] S2: File download and format conversion: Use crontab to schedule shell scripts to scan the FTP server's specified directory, download new TXT files to local storage. Execute Python scripts to parse TXT files, handle special characters (such as line breaks), standardize date formats, and generate standard CSV files with table headers. S3: Data loading into database: XXL-Job scheduling platform triggers Java batch processing program to read CSV files and write to Oracle database through JDBC connection. Java program uses MERGE statement to implement data merging (UPDATE / INSERT), and submits transactions in batches of 5000 to improve efficiency. If the application determines that the file is not normally loaded into the database, manually run the shell script to retransmit the failed data, and return to step S1.

[0032] Technology 2: Oracle to MySQL data transmission

[0033] S11: CSV file generation and cleaning: Party A's java application uses xxlJob to read oracle database data at regular intervals, cleans the data (removes spaces, replaces illegal characters), ensures that the field format meets the requirements of MySQL, and exports the CSV file of the target data table.

[0034] S22: Data loading and breakpoint resume: DataX directly calls the mysqlwriter plug-in to write the cleaned CSV file to the target MySQL database. Enable the breakpoint resume mechanism (checkpoint interval 5000), support incremental data synchronization.

[0035] S33: Full and incremental synchronization: Start DataX task to perform full data synchronization, support parallel channel mechanism to improve efficiency. Based on the update_time field, implement incremental data synchronization, and automatically retry failed tasks (interval 10 minutes). If the application determines that the file is not normally loaded into the database and notifies the operation and maintenance personnel by email, manually start xxljob to retransmit the failed data, and return to step S11.

[0036] The efficient and reliable heterogeneous database data transmission is realized through the following technical means: the standardized JSON configuration file decouples the heterogeneous data source, supports flexible expansion of various data sources; the Python script bridges the text and database format difference, ensures the correct conversion of complex fields (such as date, line break); the FTP transfer mechanism breaks through the transmission barrier across network domains, combines SSL encryption and MD5 check to ensure data security; the XXL-Job scheduling platform triggers the Java batch processing task, combines the breakpoint resume mechanism (every 5000 data batch transaction submission) to improve the transmission efficiency and reliability; the file state tracking management system monitors the whole transmission process in real time, automatically cleans up the expired files and sends alarm notification. Through the above technical means, the application realizes efficient data transmission across servers, supports batch data transmission of heterogeneous databases in a big data scenario, guarantees data consistency and transmission reliability, and is especially suitable for financial, government and other scenes with high requirements for data security and stable transmission.

[0037] Embodiment: a heterogeneous database data transmission method based on DataX framework, specifically comprising the following steps:

[0038] As shown in Figure 1 , the data transmission from MySQL to Oracle

[0039] S1: TXT file generation and uploading: first, party B synchronizes the MySQL data of party B to the MySQL database of party A through datax timing, and here the Mysql server deployed by our party has the functions of front-end machine and data conversion. Create a DataXJSON configuration file in the MySQL server of party A, define the data source connection parameters (such as the JDBCURL of MySQL, account password and table structure). Use the DataX plug-in architecture to call mysqlreader to read the source data, and generate TXT files through the txtfilewriter plug-in. Use crontab to run shell script to scan the file directory of saving txt data at regular intervals, and automatically upload the generated TXT file to the specified directory of the FTP server of party A, and here the FTP server of party A is used for data transfer and only allows intranet access.

[0040] S2: file download and format conversion: use crontab to run shell script to scan the specified directory of FTP server at regular intervals, download the new TXT file to local storage. Execute Python script to parse TXT file, process special characters (such as line break), standardize date format, and generate standard CSV file with table header.

[0041] S3: Data loading into the warehouse: The XXL-Job scheduling platform triggers the Java batch processing program to read the CSV file and write to the Oracle database through the JDBC connection. The Java program uses the MERGE statement to implement data merging (UPDATE / INSERT), and each batch of 5000 data is submitted in batches to improve efficiency. The application program judges whether the file is normally loaded into the warehouse, if not, manually run the shell script to retransmit the failed data, return to step S1; if yes, the process is completed.

[0042] As shown in Figure 1 Oracle to MySQL data transmission

[0043] S11: CSV file generation and cleaning: The Party A java application uses xxlJob to read oracle database data regularly, cleans the data (removes spaces, replaces illegal characters), ensures that the field format meets the requirements of MySQL, and exports the CSV file of the target data table.

[0044] S22: Data loading and breakpoint resume: DataX directly calls the mysqlwriter plug-in to write the cleaned CSV file to the target MySQL database. Enable the breakpoint resume mechanism (checkpoint interval 5000), support incremental data synchronization.

[0045] S33: Full and incremental synchronization: Start the DataX task to perform full data synchronization, support parallel channel mechanism to improve efficiency. Based on the update_time field, incremental data synchronization is realized, and failed tasks are automatically retried (interval 10 minutes). The application program judges whether the file is normally loaded into the warehouse, and notifies the operation and maintenance personnel by email, if not, manually start xxljob to retransmit the failed data, return to step S11; if yes, the process is completed.

[0046] In this application, professional terms are explained:

[0047] DataX: Ali open source heterogeneous data source offline synchronization tool, supports data transmission between multiple data sources, and can efficiently realize data synchronization tasks.

[0048] MySQL: Relational database management system, open source and widely used, suitable for various scale application scenarios.

[0049] Oracle: Oracle Corporation developed relational database management system, mainly used in enterprise-level key business systems.

[0050] JDBC: Java Database Connectivity (Java Database Connectivity) is an abbreviation, which is a standard API for connecting databases in Java language.

[0051] JSON: JavaScript Object Notation, a lightweight data interchange format commonly used for configuration files and data transmission.

[0052] Python: A programming language known for its simple and readable syntax, widely used in data analysis, scripting, and other fields.

[0053] FTP: File Transfer Protocol, a protocol used for transferring files over a network.

[0054] SSL: Secure Sockets Layer, a security protocol used to establish encrypted connections for secure data transmission.

[0055] MD5: Message-Digest Algorithm 5, a hash algorithm used to generate data digests for verifying data integrity.

[0056] XXL-Job: A distributed task scheduling platform used for executing scheduled tasks, batch processing, and other tasks, supporting task scheduling, monitoring, and management.

[0057] Java: An object-oriented programming language with cross-platform capabilities, commonly used for enterprise-level application development.

[0058] CSV: Comma-Separated Values, a simple text file format used to store tabular data.

[0059] MERGE: A database statement used to insert data, updating existing data if it already exists, or inserting new data if it does not exist, implementing data merging.

[0060] crontab: A Unix and Linux system tool for scheduling tasks, used to execute commands or scripts at specified times.

[0061] shell: A command interpreter used to execute commands and scripts in an operating system, enabling automation of tasks.

[0062] JDBCURL: Java Database Connectivity Uniform Resource Locator, used to specify database connection information such as address, port, and database name.

[0063] mysqlreader: A DataX plugin used to read data from a MySQL database.

[0064] Txtfilewriter: a plug-in of DataX, used for writing data into a TXT file.

[0065] Mysqlwriter: a plug-in of DataX, used for writing data into a MySQL database.

[0066] Update_time: (field name) a field recording data update time, and incremental data synchronization can be realized based on the field.

[0067] Finally, it should be noted that the present application is not limited to the above embodiments, but can have many variations. All variations that can be directly derived or conceived by those skilled in the art from the disclosure of the present application should be considered as falling within the scope of the present application.

Claims

1. A heterogeneous database data transmission method based on a DataX framework, characterized in that, The data transmission comprises a MySQL-to-Oracle direction data transmission and an Oracle-to-MySQL direction data transmission. The MySQL-to-Oracle direction data transmission comprises the following steps: S1: TXT file generation and uploading: a DataXJSON configuration file is created on a bank-side MySQL server, a data source connection parameter is defined, a source data is read by using a DataX plug-in architecture, a TXT file is generated by using a txtfilewriter plug-in, a shell script is run by using a cronta to scan a file directory for saving the TXT data in a timing manner, and the generated TXT file is automatically uploaded to a specified directory of a bank-side FTP server; S2: file downloading and format conversion: a shell script is run by using a cronta to scan a specified directory of an FTP server in a timing manner, a newly added TXT file is downloaded to a local storage, a Python script is executed to parse the TXT file, special characters are processed, a date format is standardized, and a standard CSV file with a table header is generated; S3: data loading into a database: an XXL-Job scheduling platform triggers a Java batch processing program, a CSV file is read, and the data is written into an Oracle database by using a JDBC connection, and a MERGE statement is used in the Java program to realize data merging; If the application program judges that the file is not normally loaded into the database, a shell script is manually run to retransmit the failed data, and the step S1 is returned. 2.The heterogeneous database data transmission method based on the DataX framework of claim 1, wherein, The Oracle-to-MySQL direction data transmission comprises the following steps: S11: CSV file generation and cleaning: a bank-side Java application program reads data of an oracle database in a timing manner by using an xxlJob, the data is cleaned, it is ensured that a field format meets a MySQL requirement, and a CSV file of a target data table is exported; S22: data loading and breakpoint resuming: a DataX directly calls a mysqlwriter plug-in to write the cleaned CSV file into a target MySQL database, a breakpoint resuming mechanism is enabled, and incremental data synchronization is supported; S33: full-amount and incremental synchronization: a full-amount data synchronization is performed by starting a DataX task, a parallel channel mechanism is used to improve efficiency, incremental data synchronization is realized based on an update_time field, and a failed task is automatically retried; if the application program judges that the file is not normally loaded into the database and notifies an operation and maintenance personnel by using a mail, the xxljob is manually started to retransmit the failed data, and the step S11 is returned. 3.The heterogeneous database data transmission method based on DataX framework of claim 2, wherein, The data source connection parameter defined in the DataXJSON configuration file comprises a JDBC URL, an account password and a table structure of the MySQL; and the bank-side FTP server is used for transferring data and only allows an intranet access.

4. The heterogeneous database data transmission method based on the DataX framework according to claim 2 or 3, characterized in that, The special character processed by the Python script comprises a line feed.

5. The heterogeneous database data transmission method based on the DataX framework according to claim 1 or 2, characterized in that, In the step S3, the Java program submits a transaction in batches every 5000 data to improve efficiency.

6. The heterogeneous database data transmission method based on the DataX framework according to claim 2, characterized in that, In the step S11, the data cleaning in the Oracle-to-MySQL direction data transmission comprises removing spaces and replacing illegal characters.

7. The heterogeneous database data transmission method based on the DataX framework according to claim 2, characterized in that, The checkpoint interval of the breakpoint resume mechanism enabled in the Oracle-to-MySQL direction data transmission in step S22 is 5000. 8.The heterogeneous database data transmission method based on DataX framework of claim 2, wherein, The interval of the automatic retry of the failed task in the Oracle-to-MySQL direction data transmission in step S33 is 10 minutes.

9. The heterogeneous database data transmission method based on the DataX framework according to claim 2 or 3, characterized in that, The standardized JSON configuration file is used for decoupling heterogeneous data sources and supports flexible expansion of various data sources.

10. An application of a heterogeneous database data transmission method based on a DataX framework, characterized in that, The application includes scenarios with high requirements for data security and stable transmission, including finance and government affairs.