A universal data loading method and system supporting breakpoints and rerunning

Abstract

The present invention relates to a universal data loading method and system supporting breakpoints and rerunning, wherein a status table corresponding to a target table is established to mark the data loading status of the target table and a temporary table matching the target table is used to specifically execute the data loading work. The method is adapted to various relational database data loading methods by configuring a fixed-length data file format and a variable-length delimiter data file format using JDBC multi-threaded loading; a data loading method suitable for each application scenario is implemented through a data loading status table and a data temporary table; and a flexible data loading method of assigning default values, fixed values, and database system values ​​is implemented through a configuration file.

Description

Technical Field

[0001] The present invention relates to the technical field of database operation maintenance and data processing, and in particular to a universal data loading method and system supporting breakpoints and rerunning. Background Art

[0002] A relational database is one that uses a relational model to organize data, storing data in rows and columns for easy understanding. The series of rows and columns in a relational database are called tables, and multiple tables together make up a database. Users retrieve data from the database through queries, accessing the stored data through executable code that limits access to specific areas of the database. The relational model can be simply understood as a two-dimensional table model, and a relational database is a data organization consisting of two-dimensional tables and the relationships between them.

[0003] Most existing mainstream relational database products, such as Oracle, SQL Server, MySQL, and DB2, support loading data files in various formats, allowing them to be directly imported into predefined relational tables. However, these existing relational database products typically require the use of specialized clients or functional modules, utilizing internal interfaces to manipulate data files for efficient loading. This results in varying configuration requirements for file loading between different database types.

[0004] For conventional relational database usage scenarios, using existing file loading technologies is sufficient to meet normal requirements. However, for some special requirements, such as replacing a relational database system to meet data security update standards, the existing file loading and corresponding batch scripts cannot be used in the new system. This can result in a significant effort being spent on loading data using new client tools, and then batch jobs being bound to a new database. If the database needs to be replaced again, a significant amount of manpower will be required to implement the new functionality. This is especially true for large-scale batch systems with a large number of jobs, and the resulting waste of work is unacceptable.

[0005] To address this issue, existing technologies have proposed corresponding solutions, namely, using a driver that is universally applicable to various relational databases, such as JDBC, to perform cross-database file loading. JDBC is a Java API that can be used to execute SQL statements. It consists of classes and interfaces written in Java. JDBC provides a standard application programming interface for database application developers and database front-end developers, enabling them to write complete database applications in pure Java. JDBC provides connection services between Java applications and various database servers by calling methods provided by its interface. It supports the ANSI SQL-92 standard, enabling standard SQL commands to be called from within Java programs to perform database operations such as querying, inserting, deleting, and updating, and ensuring the normal execution of data transactions. Most relational databases currently on the market provide JDBC drivers. However, as a universal driver, using JDBC to perform file loading still has its limitations, particularly poor actual performance and a limited number of advanced features.

[0006] As can be seen from this, the loading tool provided by the relational database is based on the database's internal interface and has high performance, but its versatility is very poor and it can only support the corresponding database. Simply parsing the file and using the JDBC driver to insert data can achieve the function when the data volume is small, but it cannot achieve high performance and cannot conveniently implement the convenient functions of re-running loading or breakpoint loading for relatively complex application scenarios. Summary of the Invention

[0007] In order to address the deficiencies of the prior art, the present invention proposes a universal data loading method and system that supports breakpoints and rerunning. By configuring fixed-length data file formats and variable-length delimiter data file formats, JDBC multi-threaded loading is used to adapt to various relational database loading methods; a loading method suitable for each application scenario is implemented through a loading status table and a data temporary table; and a flexible loading method of assigning default values, fixed values, and database system values ​​is implemented through a configuration file.

[0008] To achieve the above objectives, the technical solutions adopted by the present invention include:

[0009] A general data loading method supporting breakpoints and rerunning, characterized by comprising:

[0010] S1. Create a status table corresponding to a target table, wherein the status table includes a data loading request and a data loading status of the target table;

[0011] S2. Filter all target tables with data loading requests through the status table to form a request set;

[0012] S3. Further determine, using the status table, whether the data loading status of each target table in the request set is loaded. If the data loading status of the target table in the request set is loaded, terminate the data loading task and delete the data loading request corresponding to the target table.

[0013] S4. When it is determined that the data loading status of the target table in the request set is not loaded, further determine whether there is a temporary table matching the target table. If it is determined that there is a temporary table matching the target table, use the temporary table to load data into the target table.

[0014] S5. When it is determined that no temporary table matching the target table exists, create a temporary table matching the target table, where the temporary table has a table structure and data file fields consistent with those of the target table;

[0015] S6. Insert the data into the temporary table and verify whether the data insertion status is normal. If the data insertion status is not normal, retry the data insertion and verify the data insertion status.

[0016] S7. When the data insertion status is verified to be normal, use the temporary table to load data into the target table.

[0017] S8. Change the data loading status corresponding to the target table for which data loading has been completed, and delete the data loading request corresponding to the target table.

[0018] S9. Repeat steps S2 to S8 until there is no data loading request in the status table.

[0019] Furthermore, the method further comprises:

[0020] Use the status table to determine whether the data loading status of each target table in the request set is rerun;

[0021] When it is determined that the data loading state of the target table is rerun, it is not determined whether there is a data loading request, and steps S6 to S8 are directly executed on the target table.

[0022] Furthermore, when it is determined that there is a temporary table matching the target table, using the temporary table to load data to the target table includes:

[0023] Use the status table to determine whether the data loading status of each target table in the request set is continued;

[0024] When it is determined that the data loading status of the target table is not continued, directly execute steps S6 to S8 on the target table;

[0025] When the data loading status of the target table is determined to be continued, the target table and the temporary table are compared to obtain the breakpoint record;

[0026] Select the continued running data portion in the temporary table according to the breakpoint record, and execute steps S6 to S8 on the target table.

[0027] Furthermore, the step S2 further includes:

[0028] A corresponding processing thread is assigned to each target table in the request set.

[0029] Furthermore, the step S8 further includes:

[0030] End the processing thread corresponding to the target table.

[0031] Furthermore, the step S6 further includes:

[0032] When the verification data insertion status is abnormal, the number of retries to retry data insertion is recorded;

[0033] Determine whether the number of retries exceeds a preset retry threshold, and if it is determined that the number of retries does not exceed the preset retry threshold, retry data insertion;

[0034] When it is determined that the number of retries exceeds the preset retry threshold, the retry is stopped, the data loading task is terminated, and an error report is fed back.

[0035] The present invention also relates to a universal data loading system supporting breakpoints and rerunning, characterized in that it comprises:

[0036] A state management module is used to create and update a state table corresponding to a target table;

[0037] A status judgment module is used to use the status table to judge whether there is a data loading request and the data loading status of the target table;

[0038] A first loading execution module is used to create a temporary table matching the target table and insert data into the temporary table;

[0039] The second loading execution module is used to use the temporary table to execute data loading on the target table.

[0040] The present invention also relates to a computer-readable storage medium, characterized in that a computer program is stored on the storage medium, and the computer program implements the above method when executed by a processor.

[0041] The present invention also relates to an electronic device, characterized in that it comprises a processor and a memory;

[0042] The memory is used to store the state table and the temporary table;

[0043] The processor is used to execute the above method by calling the state table and the temporary table.

[0044] The present invention also relates to a computer program product, comprising a computer program and / or instructions, characterized in that the computer program and / or instructions implement the steps of the above method when executed by a processor.

[0045] The beneficial effects of the present invention are:

[0046] The universal data loading method and system supporting breakpoints and rerunning described in the present invention is used. By configuring a fixed-length data file format and a variable-length delimiter data file format, a multi-threaded data loading method using JDBC is used to adapt to various relational database data loading methods, reducing the versions of different database data loading scripts, and allowing data loading to be implemented using a universal method. While generally supporting data loading from various relational databases, a data loading method suitable for each application scenario is implemented through a data loading status table and a data temporary table, and relatively high performance is achieved through multi-threaded concurrent batch submission. A flexible data loading method for assigning default values, fixed values, and database system values ​​is implemented through a configuration file, allowing new table data loading operations to be quickly implemented. The active system of the present invention is applicable to various relational databases and most application scenarios, and can support rerunning and resuming. BRIEF DESCRIPTION OF THE DRAWINGS

[0047] Figure 1 The figure is a flow chart of a general data loading method supporting breakpoints and rerunning according to the present invention.

[0048] Figure 2 This is a schematic diagram of the structure of a universal data loading system that supports breakpoints and rerunning according to the present invention. DETAILED DESCRIPTION

[0049] In order to more clearly understand the content of the present invention, it will be described in detail with reference to the accompanying drawings and embodiments.

[0050] The first aspect of the present invention relates to a process of steps as follows Figure 1 The general data loading method shown supports breakpoints and reruns, including:

[0051] S1. Establish a status table corresponding to a target table, wherein the status table includes a data loading request and a data loading status of the target table.

[0052] For different target tables in different database systems, it is preferable to adopt the execution method of the standardized data loading configuration file format and configure a separate configuration file for each data loading table. Among them, for the case where each field in the data file has a fixed length, that is, the data of each field is in a fixed position in each line, a POS configuration file is defined for each data file, which contains the name of each field and its fixed position. For example:

[0053] Load data into sample table 1

[0054]

[0055] Among them, the ABCDEF fields are all fixed positions, starting from positions 1, 7, 11, 31, and 41, and ending at positions 5, 9, 19, 35, and 55, respectively. The F field uses a fixed value of 1 when loading numbers, and the G field uses the database system time variable.

[0056] In another case, when the fields in the data file are of varying length and separated by delimiters, define the field order fld configuration file for each data file, for example:

[0057] Load data into sample table 2

[0058]

[0059]

[0060] Among them, field A is of string type, field B is also of string type. If the data is empty, it is loaded with the default value 000000. Field C is in timestamp format, specifically in the form of 2022-02-02 02:02:02:000. Field D is also in timestamp format, specifically in the form of 2022-02-02 02:02:02.000. Field F (the fifth field in the file) is skipped and not loaded.

[0061] S2. All target tables with data loading requests are filtered through the status table to form a request set.

[0062] Preferably, a corresponding processing thread is assigned to each target table in the request set to control the number of concurrent threads. Depending on the server executing the load, database performance, and network configuration, a larger value, if the environment allows, increases the number of concurrent loads and speeds up data loading. The number of insertSQL statements submitted per transaction (the number of records submitted in a batch) varies depending on the network environment, database performance, and server performance, requiring specific configuration.

[0063] S3. The status table is further used to determine whether the data loading status of each target table in the request set is already loaded. If the data loading status of the target table in the request set is already loaded, the data loading task is terminated and the corresponding data loading request for the target table is deleted. At this point, the corresponding data loading status indicated in the status table indicates that the target table has undergone a complete data loading operation and no further processing is required. The corresponding data loading request can be safely deleted.

[0064] S4. When it is determined that the data loading status of the target table in the request set is not loaded, further determine whether there is a temporary table matching the target table. When it is determined that there is a temporary table matching the target table, use the temporary table to perform data loading on the target table.

[0065] Preferably, when the data loading status is not "loaded", there may be multiple situations. For example, a target table that has not been loaded can be marked as "unloaded"; a target table that has been loaded but needs to be reloaded can be marked as "re-run"; and a target table that has been partially loaded but still needs to continue the subsequent loading operation can be marked as "continued".

[0066] Furthermore, when it is determined that the data loading status of the target table is to re-run, there is no need to determine whether there is a data loading request, and the subsequent data loading operation can be directly executed. When it is determined that the data loading status of the target table is to continue running, it is necessary to perform additional breakpoint positioning on the data loading operation to ensure that the data can be normally continued. Specifically, the status table is used to determine whether the data loading status of each target table in the request set is to continue running; when it is determined that the data loading status of the target table is not to continue running, the subsequent data loading steps are directly executed on the target table; when it is determined that the data loading status of the target table is to continue running, the target table and the temporary table are compared to obtain the breakpoint record; the continued data part in the temporary table is selected according to the breakpoint record, and the subsequent data loading steps are executed on the target table.

[0067] S5. When it is determined that there is no temporary table matching the target table, a temporary table matching the target table is created, where the temporary table has a table structure and data file fields consistent with those of the target table.

[0068] For example, a temporary table for the application table is created in the database, and a corresponding status record is generated so that the target table can correspond to the temporary table. In some specific cases, due to the large amount of system data, the data may need to be sharded. This results in multiple homogeneous tables, each of which needs to be loaded with data, requiring the generation of their own temporary table and the update of the status table.

[0069] S6. Insert the data into the temporary table and verify whether the data insertion status is normal. If the data insertion status is not normal, retry the data insertion and verify the data insertion status.

[0070] Preferably, an initialization operation is required before each data loading operation, and according to the performance of the server executing the data loading program, an appropriate number of multi-threaded parallel processing is configured, and multiple data loading threads are started at the same time, each thread processes a data loading table. The following is the process of each thread processing.

[0071] During the loading process, you need to determine the data file format, read the configuration file, determine the data file format based on the configuration file mode, query each field type, and process the data file in a targeted manner. For example, if the configuration file is fixed-length, you can read the data file in fixed-length mode and use each data position to be processed as the value of each field in the insert SQL statement. If the file is a variable-length delimited file, you can split each data entry by the delimiter and select the required fields as the value of each field in the insert SQL statement.

[0072] For the continued running state, it is necessary to read the corresponding position field of the status table record for the current loading task, and determine the offset row number or offset position for reading the data file based on the value of the loaded data record. Generate insert SQL for each record based on the table structure and the values ​​of each field split out of the data file. When generating SQL, it is necessary to call different JDBC field value interfaces for the type of each field in the target table. If the field being processed is a timestamp type field, the corresponding field in SQL uses the setTimestamp interface to set the field value. If the field being processed is a number type field, use the setBigDecimal interface to set the field value. If the field being processed is a date type field, use the setDate interface to set the field value. For most other types of fields, the general setString can be used to set the field value.

[0073] Particularly preferably, when the verification data insertion status is abnormal, the number of retries for retrying data insertion is recorded; it is determined whether the number of retries exceeds a preset retry threshold, and when it is determined that the number of retries does not exceed the preset retry threshold, the data insertion is retried; when it is determined that the number of retries exceeds the preset retry threshold, the retry is stopped, the data loading task is ended, and an error report is fed back.

[0074] S7. When the data insertion status is verified to be normal, use the temporary table to load data into the target table.

[0075] After the above steps, you can preferably insert data into the temporary table by executing SQL inserts in batches. If the insert is successful, the status table is updated to indicate the data is being loaded. At the same time, the insert quantity for each batch is updated in the corresponding status table. After committing the data loading and updating the status table, if the insertion fails, a rollback operation is performed first. If the rollback fails, the status table is updated accordingly, and the data loading operation is aborted.

[0076] Merge the temporary table data into the application table based on the application table's loading data and historical data logic. If the target table's primary key is made unique through primary key merging, and all other fields use the temporary table data, use the merge method. If the corresponding primary key of the temporary table is the same as the target table, update the target table using the other fields of the temporary table. If the corresponding primary key of the temporary table does not exist in the target table, insert the temporary table record into the target table. If you need to clear some application table data first, perform a delete operation on the application table based on the logic, and then insert the temporary table data into the application table.

[0077] The logic for updating the target table through the temporary table is varied, and those skilled in the art can use any appropriate subsequent processing and data loading tools to perform the update.

[0078] S8. Change the data loading status corresponding to the target table for which data loading is completed, delete the data loading request corresponding to the target table, and at the same time, end the processing thread corresponding to the target table.

[0079] For example, the value of the loading status table indicating that the entire data file has been loaded is modified to "loaded".

[0080] S9. Repeat steps S2 to S8 until there is no data loading request in the status table.

[0081] Preferably, if the data file format is abnormal, each rerun or continued run will fail when encountering abnormal data. At this time, the row where the abnormal record is located can modify the batch submission quantity configuration, so that all successful records can be loaded into the database temporary table until the abnormal record, and the problem can be located relatively quickly.

[0082] Another aspect of the present invention relates to a general data loading system that supports breakpoints and reruns, the structure of which is as follows: Figure 2 Shown, including:

[0083] A state management module is used to create and update a state table corresponding to a target table;

[0084] A status judgment module is used to use the status table to judge whether there is a data loading request and the data loading status of the target table;

[0085] A first loading execution module is used to create a temporary table matching the target table and insert data into the temporary table;

[0086] The second loading execution module is used to use the temporary table to execute data loading on the target table.

[0087] By using this system, the above-mentioned calculation and processing method can be executed and the corresponding technical effects can be achieved.

[0088] An embodiment of the present invention also provides a computer-readable storage medium capable of implementing all steps of the method in the above embodiment. The computer-readable storage medium stores a computer program that implements all steps of the method in the above embodiment when executed by a processor.

[0089] An embodiment of the present invention also provides an electronic device for executing the above-mentioned method. As an implementation device of the method, the electronic device has at least a processor and a memory, and in particular, the memory stores the data and related computer programs required for executing the method, such as status tables and temporary tables, etc., and the processor calls the data and programs in the memory to execute all the steps of the implementation method and obtains the corresponding technical effects.

[0090] Preferably, the electronic device may include a bus architecture, which may include any number of interconnected buses and bridges, and the bus will include various circuits linked together by one or more processors and memories. The bus may also link together various other circuits such as peripheral devices, voltage regulators, and power management circuits, which are all well known in the art and, therefore, will not be described further herein. The bus interface provides an interface between the bus and the receiver and transmitter. The receiver and transmitter can be the same component, namely a transceiver, which provides a unit for communicating with various other systems over a transmission medium. The processor is responsible for managing the bus and general processing, while the memory can be used to store data used by the processor when performing operations.

[0091] Additionally, the electronic device may further include components such as a communication module, an input unit, an audio processor, a display, and a power supply. The processor (or controller, operating control) employed may include a microprocessor or other processor device and / or logic device, which receives input and controls the operation of various components of the electronic device. The memory may be one or more of a cache, flash memory, a hard drive, removable media, volatile memory, non-volatile memory, or other suitable devices, and may store the aforementioned data and information. It may also store programs for executing the relevant information, and the processor may execute the programs stored in the memory to implement information storage or processing. The input unit is used to provide input to the processor, and may, for example, be a keypad or touch input device. The power supply is used to provide power to the electronic device. The display is used to display objects such as images and text, and may, for example, be an LCD display. The communication module is a transmitter / receiver that sends and receives signals via an antenna. The communication module (transmitter / receiver) is coupled to the processor to provide input signals and receive output signals, similar to the case of a conventional mobile communication terminal. Based on different communication technologies, multiple communication modules can be provided in the same electronic device, such as a cellular network module, a Bluetooth module, and / or a wireless local area network module. The communication module (transmitter / receiver) is also coupled to a speaker and a microphone via an audio processor to provide audio output via the speaker and receive audio input from the microphone, thereby implementing common telecommunications functions. The audio processor may include any suitable buffer, decoder, amplifier, etc. In addition, the audio processor is also coupled to a central processing unit, enabling local recording via the microphone and playback of stored audio via the speaker.

[0092] It will be understood by those skilled in the art that embodiments of the present invention may be provided as methods, systems, or computer program products. Thus, the present invention may take the form of an entirely hardware embodiment, an entirely software embodiment, or an embodiment combining software and hardware. Furthermore, the present invention may take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to magnetic disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0093] The present invention is described with reference to flowcharts and / or block diagrams of methods, devices (systems), and computer program products according to embodiments of the present invention. It should be understood that each process and / or block in the flowcharts and / or block diagrams, as well as combinations of processes and / or blocks in the flowcharts and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowcharts and / or block diagrams. Figure 1 a process or multiple processes and / or boxes Figure 1 A system that specifies the functions of a box or boxes.

[0094] These computer program instructions may also be stored in a computer-readable memory that can direct a computer or other programmable data processing device to work in a specific manner, so that the instructions stored in the computer-readable memory produce an article of manufacture including an instruction system that is implemented in the process. Figure 1 a process or multiple processes and / or boxes Figure 1 The function specified in one or more boxes.

[0095] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operational steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing the instructions executed on the computer or other programmable device for implementing the process. Figure 1 a process or multiple processes and / or boxes Figure 1 The present invention is described in detail below. ...

[0096] The above description is merely a preferred embodiment of the present invention, but the scope of protection of the present invention is not limited thereto. Any changes or substitutions that can be easily conceived by a person skilled in the art within the technical scope disclosed in the present invention are intended to be covered by the scope of protection of the present invention. Therefore, the scope of protection of the present invention should be based on the scope of protection of the claims.

Claims

1. A general data loading method supporting breakpoints and rerunning, characterized in that: Including steps: S1. Create a status table corresponding to a target table, wherein the status table includes a data loading request and a data loading status of the target table; S2. Filter all target tables with data loading requests through the status table to form a request set; S3. Further determine, using the status table, whether the data loading status of each target table in the request set is loaded. If the data loading status of the target table in the request set is loaded, terminate the data loading task and delete the data loading request corresponding to the target table. S4. When it is determined that the data loading status of the target table in the request set is not loaded, further determine whether there is a temporary table matching the target table. If it is determined that there is a temporary table matching the target table, use the temporary table to load data into the target table. S5. When it is determined that no temporary table matching the target table exists, create a temporary table matching the target table, where the temporary table has a table structure and data file fields consistent with those of the target table; S6. Insert the data into the temporary table and verify whether the data insertion status is normal. If the data insertion status is not normal, retry the data insertion and verify the data insertion status. S7. When the data insertion status is verified to be normal, use the temporary table to load data into the target table. S8. Change the data loading status corresponding to the target table for which data loading has been completed, and delete the data loading request corresponding to the target table; S9, repeat steps S2 to S8 until there is no data loading request in the status table; The method further comprises: Use the status table to determine whether the data loading status of each target table in the request set is rerun; When it is determined that the data loading state of the target table is rerun, it is not determined whether there is a data loading request, and steps S6 to S8 are directly executed on the target table; When it is determined that there is a temporary table matching the target table, using the temporary table to load data to the target table includes: Use the status table to determine whether the data loading status of each target table in the request set is continued; When it is determined that the data loading status of the target table is not continued, directly execute steps S6 to S8 on the target table; When the data loading status of the target table is determined to be continued, the target table and the temporary table are compared to obtain the breakpoint record; Select the continued running data portion in the temporary table according to the breakpoint record, and execute steps S6 to S8 on the target table.

2. The method according to claim 1, wherein The step S2 further includes: A corresponding processing thread is assigned to each target table in the request set.

3. The method according to claim 2, wherein The step S8 further includes: End the processing thread corresponding to the target table.

4. The method according to claim 1, wherein The step S6 further includes: When the verification data insertion status is abnormal, the number of retries to retry data insertion is recorded; Determine whether the number of retries exceeds a preset retry threshold, and if it is determined that the number of retries does not exceed the preset retry threshold, retry data insertion; When it is determined that the number of retries exceeds the preset retry threshold, the retry is stopped, the data loading task is terminated, and an error report is fed back.

5. A general data loading system supporting breakpoints and rerunning, characterized in that: Applied to the method according to any one of claims 1 to 4, the system comprises: A state management module is used to create and update a state table corresponding to a target table; A status judgment module is used to use the status table to judge whether there is a data loading request and the data loading status of the target table; A first loading execution module is used to create a temporary table matching the target table and insert data into the temporary table; The second loading execution module is used to use the temporary table to execute data loading on the target table.

6. A computer-readable storage medium, characterized in that The storage medium stores a computer program, which implements the method according to any one of claims 1 to 4 when executed by a processor.

7. An electronic device, characterized in that: including processor and memory; The memory is used to store the state table and the temporary table; The processor is configured to execute the method according to any one of claims 1 to 4 by calling a state table and a temporary table.

8. A computer program product comprising a computer program and / or instructions, characterized in that When the computer program and / or instructions are executed by a processor, the steps of the method according to any one of claims 1 to 4 are implemented.

Images