Method and device for data synchronization based on file mode

Abstract

The invention relates to a data synchronization method based on a file mode. The method comprises the steps that source end configuration is carried out, a source data source is selected, and SQL statements are defined; configuring a target end data source, a target table and a data synchronization strategy of data synchronization; the method comprises the following steps of: segmenting an SQL (Structured Query Language) statement, initializing source end threads with the same quantity according to the number of segmented pieces, assembling an sqluldr2 starting command in each thread, and exporting source end database data into a text file by utilizing an sqluldr2 tool; and initializing the same number of target end threads according to the number of the source end threads, transmitting the prefix name of the local file generated by each source end thread to the corresponding target end thread, and importing the text file exported by the source end thread corresponding to each target end thread into a target end data table by each target end thread. According to the method, data synchronization is carried out in a file mode, the problem of low data extraction performance of a JDBC connection mode is solved, and the overall performance and efficiency of data synchronization are improved. The method is high in operability and easy to implement and has universality.

Description

Technical Field

[0001] The present invention relates to the technical field of data synchronization, and in particular to a method and device for performing data synchronization based on a file mode. Background Art

[0002] At present, with the continuous development of information construction and the gradual emphasis on the value of data, more and more enterprises and government departments have realized that the "data island" phenomenon caused by the lack of business concentration, process interconnection, and data sharing has brought great difficulties to enterprises and government departments in data analysis and mining. The first step to solve the "data island" problem is to bring together the data of various separate business systems or departments and establish a unified data collection, processing, and sharing center. One of the data processing methods used in this process is data synchronization technology.

[0003] Data synchronization generally requires the help of some ETL tools. However, open source ETL tools often read and write data through JDBC connection. In actual project applications, for relational databases, the performance of data reading using JDBC connection is poor, especially for large-scale data synchronization scenarios, which have high requirements for data synchronization performance and efficiency. Using traditional JDBC connection to access the database will result in poor query data performance, long data extraction time, and long database connection occupation, which seriously affects the performance and efficiency of data synchronization. Summary of the invention

[0004] In order to overcome the above-mentioned defects of the existing data synchronization methods, the present invention proposes a new file-based data synchronization method, which helps to solve the problems of slow data extraction speed and low data synchronization performance and efficiency in the traditional JDBC connection method.

[0005] Terminology explanation:

[0006] (1) SQL: is a database query and programming language used to access data and query, update, and manage relational database systems.

[0007] (2)Hadoop: is a distributed system infrastructure developed by the Apache Foundation.

[0008] (3) HDFS: a distributed file system based on Hadoop.

[0009] (4).sh script file: It is a shell script file, which usually contains a series of commands for running on Unix / Linux systems. It can be used to automate tasks, simplify workflows, and improve the efficiency of system management and maintenance.

[0010] In order to achieve the above object, the present invention mainly makes the following designs:

[0011] (1) Based on the file export and import method, a method and device for data synchronization based on file mode is designed.

[0012] (2) Use the sqluldr2 tool on the source side to export the Oracle database data into a text file.

[0013] (3) The target side uses the HDFS file loading method to quickly import local text files into the target side Hive table.

[0014] (4) In order to improve the export performance of the source end, it supports segmentation according to SQL statements. The source end export module sets the number of parallel threads according to the number of SQL segments, and performs operations such as obtaining configuration, assembling sqluldr2 startup commands, generating .sh script files, and running .sh script files in each independent thread.

[0015] (5) In order to improve the import performance of the target side, the target side and the source side maintain the same segmentation strategy, and use the prefix name of the file generated by the source side as the association basis to achieve a one-to-one correspondence between the target side file processing thread and the source side export thread. The target side import module initializes the number of target side threads based on the number of source side threads, and runs simultaneously with the source side file export thread. In each thread of the target side, operations such as obtaining configuration, starting monitoring, importing files, and cleaning up local files are performed.

[0016] (6) Since the operations on the source and target ends are in different independent threads, the execution status of the file export process in the source thread is set through a shared variable, and the target thread determines whether the file export is completed by monitoring the value of the corresponding shared variable.

[0017] Specifically, the present invention provides a method for data synchronization based on a file mode, such as Figure 8 As shown, the method comprises the following steps:

[0018] S1. Perform source configuration, select the database from which data needs to be extracted as the source data source, and select or define the SQL statement;

[0019] S2. Perform target-side configuration, including target data source, target table, and data synchronization strategy for data synchronization;

[0020] S3. Split the SQL statements selected or defined by the user, initialize the same number of source threads according to the number of SQL statement splits, assemble the startup command of the sqluldr2 tool in each thread, multi-threaded in parallel, and use the sqluldr2 tool in each independent thread to export the data in the source database data table into a text file in csv or txt format;

[0021] S4. Initialize the same number of target-side threads according to the number of source-side threads, pass the prefix name of the local file generated by each source-side thread to the corresponding target-side thread, and multi-thread in parallel, each target-side thread imports the csv or txt format text file exported by the corresponding source-side thread into the target-side data table.

[0022] Furthermore, in step S1 of the method for data synchronization based on a file mode of the present invention, a visual SQL editor is used to define SQL statements, and the SQL editor supports single-table query, multi-table joint query, function conversion, and field addition scenarios;

[0023] The SQL statement defined using the SQL editor must ensure that the order of fields in the source-side export file is completely consistent with the order of fields in the target-side data table.

[0024] Furthermore, in the method for performing data synchronization based on a file mode of the present invention, the target end data source in step S2 is a database to which data needs to be written;

[0025] The target table is a data table to be written with data, and the target table is in TextFile format;

[0026] The data synchronization strategy is a target-side data writing strategy, which includes the following two strategies:

[0027] (1) Overwrite table: Before writing data each time, clear the original data in the target table;

[0028] (2) Append: Before writing data each time, insert new data into the target table in an append manner.

[0029] Furthermore, the method for data synchronization based on a file mode of the present invention, in step S3, segmenting the SQL statement selected or defined by the user comprises:

[0030] When the source database is an Oracle database and its Oracle data table has a numeric primary key field, use the primary key field as the basis for SQL statement segmentation;

[0031] When the source database is an Oracle database and its Oracle data table does not have a numeric primary key field, the rownum pseudo column is used as the basis for SQL statement segmentation;

[0032] In addition, SQL statements can be segmented by defining multiple SQL statements.

[0033] Furthermore, the method for synchronizing data in a file-based manner according to the present invention includes, in step S3:

[0034] S31. Split the SQL statement selected or defined by the user, and initialize the same number of source threads according to the number of SQL statement splits;

[0035] S32. Obtain relevant information required for the sqluldr2 startup command line in each thread (including: connection information of the source database (user name, password, service name), assigned SQL statements, generation location and file naming rules of the export file, number of file segmentation, etc.), and then assemble the startup command of the sqluldr2 tool according to the predefined command line template;

[0036] S33. In each thread, the assembled sqluldr2 startup command is encapsulated into an executable .sh script file and generated to the location specified by the server;

[0037] S34. Run the respective .sh script file in each thread to export the data in the source database data table into a text file in csv or txt format;

[0038] S35. When the sqluldr2 process in each thread is executed, the thread saves the exit code to its own shared variable, and the shared variable is used by the corresponding thread on the target end to determine whether the file export is completed.

[0039] Furthermore, the method for performing data synchronization based on a file mode of the present invention includes, in step S4:

[0040] S41. Initialize the same number of target threads according to the number of source threads;

[0041] S42. passing the name prefix of the local file generated by each source thread to the corresponding target thread, so that the target thread and the source thread have a one-to-one correspondence through the file name prefix;

[0042] S43. Each target thread cyclically monitors the shared variable of the corresponding source thread, and when it detects that a file matching the file name prefix has been generated, the import process is immediately started for the generated file;

[0043] S44. In multi-threaded parallel operation, each target-side thread imports the text file in csv or txt format exported by the corresponding source-side thread into the target-side data table. After the import is completed, the local file is cleaned up.

[0044] Furthermore, in the method for data synchronization based on the file mode of the present invention, when the source end determines the number of threads to be initialized and the file name prefix to be generated for each thread (at this time, the sqluldr2 export process has not been started and the local file has not started to be generated), the target end can synchronously initialize the target end thread;

[0045] When the target database is a Hive database, the text file in CSV or TXT format exported by the source thread is directly written into the Hive table in TextFile format by file loading.

[0046] Furthermore, in step S43 of the method for performing data synchronization based on a file mode of the present invention, each target-side thread cyclically monitors the shared variable of the corresponding source-side thread, including the following situations:

[0047] If the corresponding shared variable has a value, all the files exported by the corresponding source thread are imported into the target data table;

[0048] If the corresponding shared variable has no value, it is necessary to determine the number of local files that meet the file name prefix that the target thread needs to process; if the number of generated local files is greater than 1, they are sorted in reverse order according to the file name, the first local file is excluded, and other local files are imported into the target data table; if the number of generated local files is not greater than 1, no processing is performed, and the loop continues to monitor the value of the shared variable.

[0049] Furthermore, the method for data synchronization based on files of the present invention also includes: using the LineNumberReader class in the IO stream, passing in the file path of each local file, using the lineNumberReader.getLineNumber() method to obtain the number of line breaks in the file, and then obtaining the number of data items in each file by adding 1 to the number of line breaks, using a log plug-in to output the number of data items corresponding to each file in a log, and then summarizing and counting the number of data items corresponding to the files printed in the log during the log collection process to obtain the number of synchronized data items;

[0050] The byte size of each file is obtained, and then the log input is used to perform summary calculations during the log collection process to obtain the total amount of data that has been synchronized.

[0051] On the other hand, the present invention also provides a device for performing data synchronization based on a file mode. When the device is running, the steps of the method for performing data synchronization based on a file mode are implemented. The device includes:

[0052] Source-side export module: Use the sqluldr2 tool to export the data in the source-side database table into a text file in csv or txt format according to the SQL statement selected or defined by the user;

[0053] Target-side import module: imports the local text file in csv or txt format generated in the source-side export module into the target-side data table;

[0054] Indicator statistics module: used to count the number of synchronized data items and the total amount of data.

[0055] In addition, the present invention also provides a computer-readable storage medium, on which a computer program is stored, and when the program is executed by a processor, the steps of the above-mentioned method for data synchronization based on a file mode are implemented.

[0056] In summary, the method for data synchronization based on files in the present invention has the following advantages:

[0057] (1) The present invention performs data synchronization in a file manner, thereby solving the problem of low performance of data extraction using a traditional JDBC connection method, and significantly improving the overall performance and efficiency of data synchronization.

[0058] (2) The present invention makes use of Oracle's data export tool sqluldr2 to give full play to its performance advantage of large-scale data export, and by segmenting SQL statements, assembling multiple sqluldr2 commands, generating multiple .sh script files, and exporting files by concurrently executing the .sh script files, giving full play to the parallel advantage and further improving the performance of data export.

[0059] (3) During the target-side file import process, the present invention initializes the number of target-side file import threads according to the number of source-side threads in parallel, thereby realizing a one-to-one correspondence between the target-side file processing threads and the source-side export threads. The target-side threads adopt a monitoring mechanism, and when the local file generation is completed, the files can be imported in a timely manner, thereby effectively shortening the overall target-side data import time.

[0060] (4) The solution of the present invention is highly operable, simple to implement, and universal, and can be applied to data synchronization between other databases that support file export and import. BRIEF DESCRIPTION OF THE DRAWINGS

[0061] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the following briefly introduces the drawings required for use in the description of the embodiments of the present invention. Obviously, the following drawings are only some embodiments recorded in the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative work.

[0062] Figure 1 This is a schematic diagram of the source end configuration interface in the present invention.

[0063] Figure 2 A schematic diagram of customizing multiple SQL statements in the source configuration interface of the present invention.

[0064] Figure 3 This is a schematic diagram of the multi-threaded workflow of the source-side export module in the present invention.

[0065] Figure 4 This is a schematic diagram of the multi-threaded workflow of the target-side import module in the present invention.

[0066] Figure 5 It is a schematic diagram of the collaborative processing flow between the source-side export module and the target-side import module threads in the present invention.

[0067] Figure 6 This is a schematic diagram of a target-side import module thread in the present invention obtaining an exported complete file by reversing the file name order.

[0068] Figure 7 The figure is a schematic diagram of the target end import module thread loop monitoring and processing file flow in the present invention.

[0069] Figure 8 It is a schematic diagram of the implementation process of the method of the present invention. DETAILED DESCRIPTION

[0070] In order to make the purpose, technical solution and advantages of the present invention clearer, the technical solution of the present invention will be clearly and completely described below in conjunction with specific embodiments. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments, and the present invention can also be implemented or applied through other different specific implementation methods. The details in this specification can also be modified or changed in various ways based on different viewpoints and applications without departing from the spirit of the present invention.

[0071] At the same time, it should be understood that the protection scope of the present invention is not limited to the specific embodiments described below; it should also be understood that the terms used in the embodiments of the present invention are for describing specific embodiments rather than for limiting the protection scope of the present invention.

[0072] Embodiment: A method for data synchronization based on file mode

[0073] sqluldr2 tool description:

[0074] sqluldr2 is a small tool that supports fast export of data in Oracle database and is applicable to Linux and Windows platforms.

[0075] sqluldr2 is mainly used to quickly export large amounts of Oracle data into csv or txt format files. Its export efficiency is 70% faster than ordinary exports, and it can also well support the export of data volumes of billions.

[0076] In order to achieve data synchronization in file mode, it is necessary to export the data of the database deployed on the remote server into a text file and generate it on the local server. The sqluldr2 tool can meet this requirement.

[0077] In addition, the sqluldr2 tool supports starting the export process in command line mode. The common parameters of the startup command are shown in the following table:

[0078] Table 1 Examples and descriptions of common parameters of the sqluldr2 tool

[0079]

[0080] Example of command:

[0081] sqluldr2 user=username / password@tnsname query=“SQL query statement” head=nofield=“,” file=xxx_%B.csv safe=yes batch=yes rows=500000

[0082] Among them, sqluldr2 is the startup command, which needs to be executed in the absolute path of the sqluldr2 tool.

[0083] The sqluldr2 tool will export the query results of the SQL statement set in the query property to a text file in csv format (it also supports exporting to a text file in txt format).

[0084] In addition, when the file name suffix is ​​added with _%B and the batch attribute is set to yes, multiple files can be generated according to the number of data rows set by the rows attribute. These files will be created in the order of the suffixes _1, _2, and _3.

[0085] Task Configuration

[0086] As a widely used relational database, Oracle database has always been in a leading position in market share. Hive database is the infrastructure of data warehouse built on Hadoop (data is stored in HDFS as files) and is widely used in the field of big data.

[0087] This solution takes the data synchronization from Oracle database to Hive database as an example to illustrate the process of implementing data synchronization in a file manner in the present invention. Other databases that support file import and export can also use the implementation ideas of the present invention to build a file-based data synchronization method.

[0088] In order to make it more intuitive and convenient for users to perform data synchronization from Oracle to Hive, the present invention provides a visual interface to facilitate users to configure data synchronization tasks.

[0089] (1) Source configuration

[0090] In order to support more flexible SQL statement definition, including single-table query, multi-table joint query, function conversion, field addition and other usage scenarios and requirements, in the source configuration, the source data source and SQL statement selection and configuration are required. The source configuration interface is as follows: Figure 1 shown.

[0091] Source Data Source: Select the Oracle database from which you want to extract data as the source data source.

[0092] SQL: The present invention provides a visual SQL editor to facilitate users to customize SQL statements, supporting usage scenarios including: single-table query, multi-table joint query, function conversion, field addition, etc.

[0093] In this solution, in order to improve the file export performance of source data and the import performance of target files, the present invention adopts a solution based on SQL statements for segmentation, and sets the parallelism of the data synchronization program according to the number of SQL statements segmented. The query range of the multiple SQL statements after segmentation can still cover the source data that needs to be queried.

[0094] The segmentation here is achieved by defining multiple SQL statements. When the user needs to implement segmentation, multiple SQL query statements can be entered in the SQL editor, and ";" is used as a separator.

[0095] Figure 2 The following shows how to customize multiple SQL statements on the source configuration interface.

[0096] When the user does not need to split, he only needs to enter a single SQL query statement in the SQL editor.

[0097] (2) Target side configuration

[0098] The target configuration is mainly used to configure the data source, data table information and data writing strategy of the data synchronization target, including: target data source, target table and data synchronization strategy.

[0099] (a) Target data source

[0100] In this embodiment, the Hive database to be written is selected.

[0101] (b) Target table

[0102] In this embodiment, select the Hive data table to be written. It should be noted that since the file import method is used to write data to the target end Hive table, the target end Hive table needs to be in TextFile format (TextFile is the default format of the Hive database, and it does not need to be specified when creating a table. This format is generally the default. Since the TextFile format does not process the imported data file, the data can be directly loaded using the load method, which has the highest loading efficiency).

[0103] (c) Data synchronization strategy:

[0104] Configure the write strategy for the target data, including overwriting the table and appending.

[0105] Overwrite table: Before writing data each time, the original data in the target Hive table will be cleared.

[0106] Append: Before writing data each time, new data is inserted into the target Hive table in an appended manner.

[0107] (3) Field Mapping

[0108] Since the solution of the present invention uses file export and import to achieve data synchronization, and the file cannot flexibly adjust the field order, it is necessary to strictly ensure that the field order in the file exported from the source end is consistent with the field order of the Hive data table on the target end.

[0109] Since the solution of the present invention uses a custom SQL statement to obtain Oracle data on the source side, the user needs to ensure in the SQL statement that the order of the fields queried on the source side is consistent with the order of the fields in the Hive data table on the target side.

[0110] (4) SQL segmentation

[0111] For Oracle databases, when an Oracle data table has a numeric primary key field, the primary key field is used as the basis for source-side query SQL segmentation, that is, the primary key field is used as the field for conditional query. This can take advantage of the index and speed up the query.

[0112] For Oracle databases, when the Oracle data table does not have a numeric primary key field, in order to better implement query SQL segmentation, the rownum pseudo column (a pseudo column in Oracle databases, which is the number of the row returned from the query result set, starting from 1 each time and increasing sequentially) can be used in the solution as the basis for source-side query SQL segmentation.

[0113] Source export module

[0114] The source-side export module is a file export plug-in developed by the present invention based on the data synchronization framework, which is conducive to the expansion of the source-side database type.

[0115] In the source-side export module, the main operation is to export the data in the data table of the Oracle database into a text file in csv format according to the query SQL statement defined by the user.

[0116] Each time the sqluldr2 tool starts a command, it only generates one file according to the query SQL statement defined in the query attribute. When the batch attribute is set to yes, it only splits the file according to the number of rows set in the rows attribute, and then generates multiple files in sequence.

[0117] When the amount of data on the source side is particularly large, it will take a long time to export the data that the source side needs to obtain into a file by relying solely on a sqluldr2 export process, and thus cannot meet our requirements for data synchronization efficiency. In the present invention, in order to speed up the export speed of the source side data, the solution we adopt is: initialize multiple threads based on the segmented query SQL statements, use the concurrency advantage of multiple threads, assemble the sqluldr2 startup command in each thread and generate a separate .sh script file for each thread, and then run the .sh script file separately. These threads can start multiple sqluldr2 export processes to export the source side Oracle data into a text file in csv format in parallel, which will significantly improve the export efficiency of the source side data.

[0118] Assume that the query SQL statement set after segmentation is:

[0119] ["SELECT*FROM EMPLOYEE where ID<10000",

[0120] "SELECT*FROM EMPLOYEE where ID>=100000AND ID<=200000",

[0121] "SELECT*FROM EMPLOYEE where ID>200000"]

[0122] One of the core points of the present invention is that the source-side export module needs to initialize a corresponding number of threads according to the number of query SQL statements that are segmented. Figure 3 As shown, the query SQL statement set on the source side includes three query SQL statements. Correspondingly, in the source-side export module, three threads are initialized, and the three query SQL statements are respectively assigned to the three threads. The following steps are performed in each thread: obtaining configuration, assembling sqluldr2 startup commands, generating .sh script files, and running .sh script files.

[0123] (1) Get configuration

[0124] In order to assemble the startup command line of the sqluldr2 tool, in this step, you need to obtain the relevant information required in the startup command line, including: Oracle database connection information (user name, password, service name), query SQL statement assigned by the source export module, the generation location and file naming rules of the export file, the number of file splits, etc.

[0125] (2) Assembly of sqluldr2 startup command

[0126] After obtaining the relevant information required for the sqluldr2 tool startup command line in the previous step, in this step, the startup command of the sqluldr2 tool will be assembled according to the defined command line template.

[0127] Example of command line template for this solution:

[0128] sqluldr2 user=username / password@tnsname query="SQL" head=no field="," file= / home / output / fileName_index_%B.csv safe=yes batch=yes rows=500000

[0129] Among them, SQL is a query SQL statement assigned to each thread, / home / output / in / home / output / fileName_index_%B.csv is the directory location of the generated file on the specified Linux server, fileName is the name of the generated file, and index is the index position of the current query SQL statement in the entire SQL statement set, starting from 0.

[0130] As mentioned above, the sqluldr2 startup commands assembled by the three threads according to the command line template are:

[0131] The sqluldr2 startup command assembled by thread 1:

[0132] sqluldr2 user=username / password@orcl query="SELECT*FROM EMPLOYEEwhere ID<10000" head=no field="," file= / home / output / employee_0_%B.csv safe=yes batch=yes rows=500000

[0133] The sqluldr2 startup command assembled by thread 2:

[0134] sqluldr2 user=username / password@orcl query="SELECT*FROM EMPLOYEEwhere ID>=100000AND ID<=200000" head=no field="," file= / home / output / employee_1_%B.csv safe=yes batch=yes rows=500000

[0135] The sqluldr2 startup command assembled by thread 3:

[0136] sqluldr2 user=username / password@orcl query="SELECT*FROM EMPLOYEEwhere ID>200000" head=no field="," file= / home / output / employee_2_%B.csv safe=yes batch=yes rows=500000

[0137] (3). Generation of sh script file

[0138] In order to facilitate the subsequent startup of the sqluldr2 process, this solution encapsulates the assembled sqluldr2 startup command into an executable .sh script file and generates it to the location specified by the server.

[0139] After completing the assembly of the sqluldr2 startup command in the previous step, each thread writes its own sqluldr2 startup command into the .sh script file. In this example, the .sh script file generated by each thread is named / home / output / employee_index.sh, where index is the index of the query SQL statement, starting from 0.

[0140] (4). Execution of sh script file

[0141] When the .sh script file in each thread is generated, the thread will start its own sqluldr2 process through the bash / home / output / employee_index.sh instruction to export the source data into a text file in csv format.

[0142] Before the file export is completed, the sqluldr2 process is blocked. Only when the file export is completed, the sqluldr2 process ends and returns an exit code.

[0143] In this solution, when the sqluldr2 process in each thread is executed, the thread will save the exit code to the thread's own shared variable to facilitate the corresponding thread in the target end import module to determine the end of file export.

[0144] Import module on target side

[0145] The target-side import module is a file import plug-in developed by the present invention based on the data synchronization framework, which is conducive to the expansion of the target-side database type.

[0146] In the target-side import module, the main operation is to import the local text file generated in the source-side export module into the target-side Hive table.

[0147] In Hive, data is stored in the Hadoop Distributed File System (HDFS) in the form of text files. Since the local text file format generated in the source export module is csv or txt, it can be written into the Hive table in the TextFile format by file loading.

[0148] Another core point of the present invention is that for the target-side import module, it is also necessary to initialize the same number of target-side threads according to the number of threads of the source-side export module. The source-side export module will pass the prefix name of the local file that needs to be generated by each thread in the source-side module to the target-side import module. The target-side import module will evenly distribute the prefix name to the target-side threads according to the prefix name of the file, and associate the target-side thread with the source-side thread through the file name prefix, so as to realize that each thread of the target-side module only processes the file of the corresponding thread of the source-side module.

[0149] Through the above design, when the source-side export module determines the number of threads that need to be initialized on the source side and the file name prefix that needs to be generated for each thread (at this time, the sqluldr2 export process has not yet started and the local file has not yet begun to be generated), the target-side import module also synchronously initializes the target-side threads to reduce the system overhead caused by thread initialization and achieve the purpose of saving time.

[0150] Using the above solution, if Figure 5 As shown, when each thread of the source-side export module starts to export the source-side data as a file, each thread in the target-side import module also starts to listen to the local file generated by the corresponding thread of the source side, and multiple threads are processed concurrently and collaboratively, which can further improve the efficiency of target-side file processing and improve data synchronization performance.

[0151] like Figure 4 As shown, in the target-side import module, each thread needs to perform the following steps: obtain configuration, start listening, import files, and clean up local files.

[0152] (1) Get configuration

[0153] In order to ensure that each thread in the target-side import module can process the files generated in the corresponding source-side thread, this solution adopts the method of passing the prefix of the files generated by each thread on the source side to the target-side import module.

[0154] Assume that the file lists generated by the three threads of the source-side export module are as follows:

[0155] Source thread 1:

[0156] / home / output / employee_0_1.csv

[0157] / home / output / employee_0_2.csv

[0158] / home / output / employee_0_3.csv

[0159] The file prefix is: / home / output / employee_0

[0160] Source thread 2:

[0161] / home / output / employee_1_1.csv

[0162] / home / output / employee_1_2.csv

[0163] / home / output / employee_1_3.csv

[0164] The file prefix is: / home / output / employee_1

[0165] Source thread 3:

[0166] / home / output / employee_2_1.csv

[0167] / home / output / employee_2_2.csv

[0168] / home / output / employee_2_3.csv

[0169] The file prefix is: / home / output / employee_2

[0170] After receiving the prefix name of the local file passed by the source-side export module, the target-side import module will assign the file prefix to each thread on the target side. Assume that the file prefixes assigned to the target-side threads are as follows:

[0171] The unified file directory is: / home / output /

[0172] Target thread 1:

[0173] The file prefix is: employee_0

[0174] Target thread 2:

[0175] The file prefix is: employee_1

[0176] Target thread 3:

[0177] The file prefix is: employee_2

[0178] After each target-side thread is assigned a file prefix, in fact, a corresponding source-side thread is assigned to each target-side thread. The target-side file import thread and the source-side file export thread correspond one-to-one. The target-side thread will process the corresponding local file based on the file prefix. In this way, multiple target-side threads work in parallel, which can significantly improve the efficiency of importing local files into the Hive database.

[0179] (2) Enable monitoring

[0180] In the target-side import module, each thread will start a loop to listen to the corresponding source-side thread shared variables.

[0181] In the present invention, in order to minimize the overall time consumed by the target end to import local files, we do not adopt the method of importing all files at once after all the files are generated. Instead, once it is detected in the loop that files that match the file prefix name have been generated, these generated files will be immediately imported into the HDFS path corresponding to the target end Hive table.

[0182] For example, when the source-side export thread 1 is split according to a certain number of rows, the batch export generates three files. They are as follows:

[0183] employee_0_1.csv, employee_0_2.csv, employee_0_3.csv

[0184] In this solution, you do not need to wait until the last file employee_0_3.csv is generated before importing the local file to the target end. Instead, you can import the generated files employee_0_1.csv and employee_0_2.csv to the target end in advance.

[0185] Figure 7 The following table shows the logic of each target thread loop.

[0186] When the corresponding shared variable has no value, it means that the sqluldr2 file export process of the corresponding source thread in the source export module has not yet been completed. At this time, in order to speed up the efficiency of importing local files on the target side and reduce the time-consuming overall data synchronization, it is necessary to determine the number of local files that meet the file prefix that need to be processed by this thread. When the number of generated local files is greater than 1, it means that multiple local files have been generated. Since the sqluldr2 export process has not yet ended, in order to prevent the import of incompletely exported files into the target side (because the sqluldr2 tool generates multiple files, these files will be created in the order of suffixes _1, _2, and _3), resulting in inaccurate imported data, such as Figure 6As shown, in this solution, the first local file needs to be excluded after sorting in reverse order according to the file name, so as to ensure that other local files are fully exported files. At this time, these fully exported files need to be imported into the target data table in time. After the import is completed, the local file needs to be deleted synchronously to prevent repeated import. When the number of local files is not greater than 1, it means that the local file has not been generated or only one local file is being generated (because the sqluldr2 export process has not been completed, the local file has not been fully exported), so this loop does not need to be processed temporarily, and the next loop needs to be performed to continue monitoring the value of the shared variable.

[0187] When the corresponding shared variable has a value, it means that the sqluldr2 file export process of the corresponding source thread in the source export module has been completed. At this time, the local files that meet the file prefix that need to be processed by this thread are all exported complete files. Therefore, they need to be imported into the target data table. After the import is completed, the local file needs to be deleted synchronously and the loop in the current thread ends.

[0188] (3) File import

[0189] In this example, the target end is the Hive data source, and the data is stored in the Hadoop Distributed File System (HDFS) in the form of text files. Therefore, in this solution, with the help of the FileSystem.copyFromLocalFile() method, the file import method adopted is to load the local text file into the HDFS path corresponding to the Hive data table.

[0190] (4) Cleaning up local files

[0191] After each local file is successfully uploaded to the target end, it is necessary to delete the local file in a timely manner.

[0192] The purpose of local file cleaning is, on the one hand, to prevent duplicate import of local files, and on the other hand, to reduce the disk space occupied by the server.

[0193] Indicator statistics module

[0194] When using files to synchronize data, you also need to consider indicator statistics, such as the number of synchronized data items and the total amount of data.

[0195] In the present invention, the LineNumberReader class in the IO stream is used, which can be used to read data from the input stream and record line number information.

[0196] In the present invention, by passing in the file path of each local file and then using the lineNumberReader.getLineNumber() method to obtain the number of line breaks in the file, it is necessary to obtain the final number of data items in each file by adding the number of line breaks + 1, and use the log plug-in to output the number of data items corresponding to each file in the log, and then during the log collection process, the number of data items corresponding to the files printed in the log is summarized and counted, so that the number of data items that have been synchronized can be correctly calculated.

[0197] For the total data size, a similar method is used to obtain the byte size of each file, and then use the log input and perform summary calculations during the log collection process to correctly calculate the total data size that has been synchronized.

[0198] The above description is only a preferred embodiment of the present invention and does not limit the present invention in any form. Any technician familiar with the profession can make some changes or modifications to the technical contents disclosed above without departing from the scope of the technical solution of the present invention to obtain equivalent embodiments of equivalent changes. However, any simple modification, equivalent changes and modifications made to the above embodiments based on the technical essence of the present invention without departing from the content of the technical solution of the present invention should be included in the scope of protection of the claims of the present invention.

Claims

1. A method for data synchronization based on a file mode, characterized in that: The method comprises: S1. Perform source configuration, select the database from which data needs to be extracted as the source data source, and select or define the SQL statement; S2. Perform target-side configuration, including target data source, target table, and data synchronization strategy for data synchronization; S3. Split the SQL statements selected or defined by the user, initialize the same number of source threads according to the number of SQL statement splits, assemble the startup command of the sqluldr2 tool in each thread, multi-threaded in parallel, and use the sqluldr2 tool in each independent thread to export the data in the source database data table into a text file in csv or txt format; S4. Initialize the same number of target-side threads according to the number of source-side threads, pass the prefix name of the local file generated by each source-side thread to the corresponding target-side thread, and multi-thread in parallel, each target-side thread imports the csv or txt format text file exported by the corresponding source-side thread into the target-side data table.

2. The method for data synchronization based on file mode according to claim 1, characterized in that: In step S1, a visual SQL editor is used to define SQL statements, wherein the SQL editor supports single-table query, multi-table joint query, function conversion, and field addition scenarios; The SQL statement defined using the SQL editor must ensure that the order of fields in the source-side export file is completely consistent with the order of fields in the target-side data table.

3. The method for data synchronization based on file mode according to claim 1, characterized in that: The target data source in step S2 is a database to which data needs to be written; The target table is a data table to be written with data, and the target table is in TextFile format; The data synchronization strategy is a target-side data writing strategy, which includes the following two strategies: (1) Overwrite table: Before writing data each time, clear the original data in the target table; (2) Append: Before writing data each time, insert new data into the target table in an append manner.

4. The method for data synchronization based on file mode according to claim 1, characterized in that: The step S3 of segmenting the SQL statement selected or defined by the user includes: When the source database is an Oracle database and its Oracle data table has a numeric primary key field, use the primary key field as the basis for SQL statement segmentation; When the source database is an Oracle database and its Oracle data table does not have a numeric primary key field, the rownum pseudo column is used as the basis for SQL statement segmentation; In addition, SQL statements can be segmented by defining multiple SQL statements.

5. The method for data synchronization based on file mode according to claim 1, characterized in that: Step S3 includes: S31. Split the SQL statement selected or defined by the user, and initialize the same number of source threads according to the number of SQL statement splits; S32. Obtain the relevant information required for the sqluldr2 startup command line in each thread, and then assemble the startup command of the sqluldr2 tool according to the predefined command line template; S33. In each thread, the assembled sqluldr2 startup command is encapsulated into an executable .sh script file and generated to the location specified by the server; S34. Run the respective .sh script file in each thread to export the data in the source database data table into a text file in csv or txt format; S35. When the sqluldr2 process in each thread is executed, the thread saves the exit code to its own shared variable, and the shared variable is used by the corresponding thread on the target end to determine whether the file export is completed.

6. The method for data synchronization based on file mode according to claim 1, characterized in that: Step S4 includes: S41. Initialize the same number of target threads according to the number of source threads; S42. passing the name prefix of the local file generated by each source thread to the corresponding target thread, so that the target thread and the source thread have a one-to-one correspondence through the file name prefix; S43. Each target thread cyclically monitors the shared variable of the corresponding source thread, and when it detects that a file matching the file name prefix has been generated, the import process is immediately started for the generated file; S44. In multi-threaded parallel operation, each target-side thread imports the text file in csv or txt format exported by the corresponding source-side thread into the target-side data table. After the import is completed, the local file is cleaned up.

7. The method for data synchronization based on file mode according to claim 6, characterized in that: When the source end determines the number of threads to be initialized and the file name prefix to be generated for each thread, the target end can synchronously initialize the target end threads; When the target database is a Hive database, the text file in CSV or TXT format exported by the source thread is directly written into the Hive table in TextFile format by file loading.

8. The method for data synchronization based on file mode according to claim 6, characterized in that: In step S43, each target-side thread cyclically monitors the shared variable of the corresponding source-side thread, including the following situations: If the corresponding shared variable has a value, all the files exported by the corresponding source thread are imported into the target data table; If the corresponding shared variable has no value, it is necessary to determine the number of local files that meet the file name prefix that the target thread needs to process; if the number of generated local files is greater than 1, they are sorted in reverse order according to the file name, the first local file is excluded, and other local files are imported into the target data table; if the number of generated local files is not greater than 1, no processing is performed, and the loop continues to monitor the value of the shared variable.

9. The method for data synchronization based on file mode according to claim 1, characterized in that: The method further includes: using the LineNumberReader class in the IO stream, passing in the file path of each local file, using the lineNumberReader.getLineNumber() method to obtain the number of line breaks in the file, then adding 1 to the number of line breaks to obtain the number of data items in each file, using a log plug-in to output the number of data items corresponding to each file in a log, and then summarizing and counting the number of data items corresponding to the files printed in the log during the log collection process to obtain the number of synchronized data items; The byte size of each file is obtained, and then the log input is used to perform summary calculations during the log collection process to obtain the total amount of data that has been synchronized.

10. A device for data synchronization based on a file method, characterized in that: When the device is running, the steps of the method for performing data synchronization based on a file mode according to any one of claims 1 to 9 are implemented, and the device includes: Source-side export module: Use the sqluldr2 tool to export the data in the source-side database table into a text file in csv or txt format according to the SQL statement selected or defined by the user; Target-side import module: imports the local text file in csv or txt format generated in the source-side export module into the target-side data table; Indicator statistics module: used to count the number of synchronized data items and the total amount of data.

Images