CN117992445A - Method and system for managing heterogeneous database data archiving - Google Patents
Method and system for managing heterogeneous database data archiving Download PDFInfo
- Publication number
- CN117992445A CN117992445A CN202211341941.2A CN202211341941A CN117992445A CN 117992445 A CN117992445 A CN 117992445A CN 202211341941 A CN202211341941 A CN 202211341941A CN 117992445 A CN117992445 A CN 117992445A
- Authority
- CN
- China
- Prior art keywords
- database
- data
- task
- archiving
- source database
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
- 238000000034 method Methods 0.000 title claims abstract description 29
- 238000012217 deletion Methods 0.000 claims description 59
- 230000037430 deletion Effects 0.000 claims description 59
- 230000002159 abnormal effect Effects 0.000 claims description 21
- 230000000977 initiatory effect Effects 0.000 claims 1
- 230000001012 protector Effects 0.000 abstract description 2
- 239000000306 component Substances 0.000 description 147
- 238000007726 management method Methods 0.000 description 38
- 238000012546 transfer Methods 0.000 description 9
- 230000001360 synchronised effect Effects 0.000 description 8
- 230000005540 biological transmission Effects 0.000 description 6
- 230000006870 function Effects 0.000 description 4
- 238000005516 engineering process Methods 0.000 description 3
- 238000012545 processing Methods 0.000 description 3
- 238000009825 accumulation Methods 0.000 description 2
- 238000004590 computer program Methods 0.000 description 2
- 230000007547 defect Effects 0.000 description 2
- 238000013461 design Methods 0.000 description 2
- 238000010586 diagram Methods 0.000 description 2
- 238000012360 testing method Methods 0.000 description 2
- 230000005856 abnormality Effects 0.000 description 1
- 230000009286 beneficial effect Effects 0.000 description 1
- 238000004140 cleaning Methods 0.000 description 1
- 239000008358 core component Substances 0.000 description 1
- 238000012423 maintenance Methods 0.000 description 1
- 238000012544 monitoring process Methods 0.000 description 1
Classifications
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
- Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
- Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a method and a system for managing heterogeneous database data archiving, comprising the following steps of 100: acquiring configuration parameters of an archiving task and storing the configuration parameters in a configuration database; step 200: reading configuration parameters of an archiving task from a configuration database, splicing the configuration parameters of the archiving task into a general execution component, and generating an execution script of the archiving task; step 300: running an execution script of the archiving task, and copying, transferring and storing the data in the source database to the target database; step 400: and determining whether the data in the source database is successfully copied, transferred and stored in the target database, if so, continuing to run the execution script of the archiving task, deleting the data in the source database, and if not, returning to the step 100. The invention can flexibly configure the database archiving background task by configuration management to adjust archiving logic information, can select different databases for storing archiving protectors according to service types, can flexibly configure archiving logic and the like.
Description
Technical Field
The present invention relates to the field of computer technologies, and in particular, to a method and a system for managing heterogeneous database data archiving.
Background
With the accumulation of long time, a great amount of historical transaction data is accumulated in the business systems, and the historical data makes the systems become more and more huge, so that the operation and maintenance complexity is improved.
As the growth in the amount of data has severely degraded the performance, stability, and also placed a significant burden on backup and restore. Through the implementation of data cleaning and data archiving, the performance of the core service database can be effectively improved, and the core service is ensured not to have performance problems due to long-time data accumulation. Meanwhile, with the continuous appearance of new storage technology and database technology, more flexible processing modes are expected for archive developers of historical data: and storing according to the cold and hot degree of the data and storing according to service timeliness.
The existing pt-archive is a tool for archiving tables, and can be used for archiving the tables with low influence and high performance, and old data can be deleted from the tables without greatly influencing the OLTP query. The data may be inserted into another table that does not need to be on the same server, but this technique has the following drawbacks:
1. Failure to flexibly configure archive upper and lower database types and temporarily support only mysql
2. Data archiving can only be done in a single database instance, complex data archiving logic cannot be configured, e.g., multi-table associations, simple data archiving can only be done for a single table,
3. Monitoring and timing scheduling of background archiving tasks cannot be achieved.
Based on the above requirements, a heterogeneous data archiving management method is needed to design and develop a data archiving management system, which can manage archiving task information such as database type, IP address port, task trigger time and the like; the method can flexibly configure the database archiving background task by configuration management to adjust archiving logic information, can select different databases for storing archiving protectors according to service types, can flexibly configure archiving logic and the like.
Disclosure of Invention
The invention mainly aims at providing a method and a system for managing heterogeneous database data archiving, aiming at the defects that the types of databases at the upper end and the lower end of archiving cannot be flexibly configured, complex data archiving logic cannot be configured and timing scheduling cannot be performed in the prior art.
To achieve the above object, a method for managing heterogeneous database data archiving of the present invention includes the steps of:
Step 100: acquiring configuration parameters of an archiving task and storing the configuration parameters in a configuration database;
step 200: reading configuration parameters of an archiving task from a configuration database, splicing the configuration parameters of the archiving task into a general execution component, and generating an execution script of the archiving task;
Step 300: running an execution script of the archiving task, and copying, transferring and storing the data in the source database to the target database;
Step 400: and determining whether the data in the source database is successfully copied, transferred and stored in the target database, if so, continuing to run the execution script of the archiving task, deleting the data in the source database, and if not, returning to the step 100.
Preferably, the configuration parameters of the archiving task in the step 100 include an archiving task ID, an archiving task name, an archiving task scheduling period, a service module definition, a determination on transmission whether there is an undelivered data statement, a pre-SQL statement of the archiving task, a post-SQL statement of the archiving task, a statement on transmission abnormal execution statement, a statement of data synchronization query data, a source database IP, a source database port, a source database user, a source database password, a source database type, a source database name, a target database IP, a target database port, a target database user, a target database password, a target database type, and a target database name.
Preferably, the task management component receives an archive task starting instruction containing an archive task ID and an archive task name, reads configuration parameter information of the archive task corresponding to the ID and the name from a configuration database, and splices the configuration parameter information into the data synchronization component and the data deletion component to generate an execution script of the archive task, wherein the general execution component comprises the data synchronization component and the data deletion component, the data synchronization component comprises FLINK-CDC components, the execution script operates according to the instruction flow of the task management component, and data archiving and data deleting are executed according to the configuration parameters of the archive task.
Preferably, the task management component connects the source database, the target database and the data synchronization component according to the source database IP, the source database port, the source database user, the source database password, the source database type, the source database name, the target database IP, the target database port, the target database user, the target database password, the target database type and the target database name, the task management component connects the source database and the data deletion component according to the source database IP, the source database port, the source database user, the source database password, the source database type and the source database name, the task management component triggers execution script operation according to an archiving task scheduling period, executes script operation, determines a data time period of the source database according to a statement of data synchronization query data, judges whether data is not transmitted, if yes, then executes a data synchronization script part, copies and stores the data in the source database according to a service module definition into a block corresponding to the target database, inserts a table according to a pre-SQL (structured query language) of a statement before determining a time period of the synchronization data, generates update task update state according to a statement after the execution, and if abnormal state is generated when the update task is executed, if abnormal state is generated, and if the update state is executed.
Preferably, the task management component reads updated task state information from the configuration database, and determines whether the execution script succeeds in synchronously archiving the data in the source database to the target database;
If the execution script is successful, the execution script continues to run, the execution data deleting script part deletes the data in the source database, and after the deletion is completed, the update task state information is generated according to the post SQL statement of the archiving task and is stored in the configuration database;
If not, return to step 100.
In addition, to achieve the above object, the present invention further provides a system for managing heterogeneous database data archiving, including:
The system comprises a parameter configuration component, a task management component, a data synchronization component, a data deletion component and a configuration database, wherein the parameter configuration component is used for acquiring configuration parameters of an archiving task and storing the configuration parameters in the configuration database;
The task management component is used for reading configuration parameters of an archiving task from the configuration database, splicing the configuration parameters of the archiving task into the data synchronization component and the data deletion component, and managing the running execution of the data synchronization component and the data deletion component;
the data synchronization component is used for copying, transferring and storing the data in the source database to the target database;
The data deleting component is used for deleting the data in the source database;
The configuration database is used for storing configuration parameter information of the archiving task.
Preferably, the configuration parameters of the archiving task obtained by the parameter configuration component include an archiving task ID, an archiving task name, an archiving task scheduling period, a service module definition, a determination on whether there is an undelivered data statement at the time of sending, a preposed SQL statement of the archiving task, a postposed SQL statement of the archiving task, an abnormal execution statement at the time of sending, a statement of data synchronous query data, a source database IP, a source database port, a source database user, a source database password, a source database type, a source database name, a target database IP, a target database port, a target database user, a target database password, a target database type, and a target database name.
Preferably, the task management component receives an archive task start instruction containing an archive task ID and an archive task name, reads configuration parameter information of the archive task corresponding to the ID and the name from the configuration database, and splices the configuration parameter information into the data synchronization component and the data deletion component to generate an execution script of the archive task, wherein the data synchronization component comprises a FLINK-CDC component.
Preferably, the task management component connects the source database and the target database with the data synchronization component according to the source database IP, the source database port, the source database user, the source database password, the source database type, the source database name, the target database IP, the target database port, the target database user, the target database password, the target database type, the target database name, the task management component connects the source database with the data deletion component according to the source database IP, the source database port, the source database user, the source database password, the source database type, the source database name, the task management component triggers execution script operation according to an archiving task scheduling period, executes script operation, determines a data time period of the source database according to a statement of data synchronization query data, if yes, then executes a data synchronization script part, copies and stores SQL data in the source database into a block corresponding to the target database according to a service module definition, inserts a front statement according to a front archive table before determining the time period of the synchronization data, generates update state according to an archive SQL statement after the update state is completed, if abnormal task execution state is completed, and generates abnormal task execution state according to the archive state after the update state is completed.
Preferably, the task management component reads updated task state information from the configuration database, and determines whether the execution script succeeds in synchronously archiving the data in the source database to the target database;
if the execution script is successful, the execution script continues to run, the data deleting script part deletes the data in the source database, and after the deletion is completed, the update task state information is generated according to the post SQL statement of the archiving task and is stored in the configuration database.
The method for managing heterogeneous database data archiving has the following beneficial effects: acquiring configuration parameters of an archiving task and storing the configuration parameters in a configuration database; forming configuration parameters of an archiving task according to an archiving target of the archiving task and a database object related to the archiving task, and providing original parameter information for generating an execution script for data archiving for a subsequent system; reading configuration parameters of an archiving task from a configuration database, splicing the configuration parameters of the archiving task into a general execution component, and generating an execution script of the archiving task; by splicing the configuration parameters into the universal execution component, the universal execution component is provided with FLINK-CDC components, so that the universal execution component is connected with a source database and a target database, and the synchronous transfer of archive data among different databases is realized, and the universal execution component has specific data archive targets for executing data synchronization and data deletion; running an execution script of the archiving task, and copying, transferring and storing the data in the source database to the target database; according to task trigger time, archiving logic and the like formed by archiving configuration parameters, the execution script rapidly realizes data archiving among different database types; determining whether the data in the source database is successfully copied, transferred and stored in the target database, if so, continuing to run an execution script of the archiving task, deleting the data in the source database, and if not, returning to the step 100; after the data is archived from the source database to the target database, determining, performing the next action of the archiving target, completing the data deletion of the source database, and completing the archiving target of the archiving task.
Drawings
For a clearer description of an embodiment of the invention or of a technical solution in the prior art, the drawings that are needed in the description of the embodiment or of the prior art will be briefly described, it being obvious that the drawings in the description below are only embodiments of the invention, and that other drawings can be obtained, without inventive effort, by a person skilled in the art from the drawings provided:
fig. 1 is a flow chart of a method for managing heterogeneous database data archiving according to an embodiment of the present invention.
Fig. 2 is a schematic diagram of a system module for managing heterogeneous database data archiving according to an embodiment of the present invention.
Detailed Description
In order that the invention may be readily understood, a more complete description of the invention will be rendered by reference to the appended drawings. Exemplary embodiments of the present invention are illustrated in the accompanying drawings. This invention may, however, be embodied in many different forms and should not be construed as limited to the embodiments set forth herein. Rather, these embodiments are provided so that this disclosure will be thorough and complete.
Unless defined otherwise, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this invention belongs. The terminology used herein in the description of the invention is for the purpose of describing particular embodiments only and is not intended to be limiting of the invention.
The invention has the following general ideas: aiming at the defects that the types of the databases at the upper end and the lower end of the archiving can not be flexibly configured, the responsible data archiving logic can not be configured and the regular scheduling can not be realized in the prior art, the archiving management system in the invention obtains the configuration parameters of the archiving task and stores the configuration parameters in the configuration database; forming configuration parameters of an archiving task according to an archiving target of the archiving task and a database object related to the archiving task, and providing original parameter information for generating an execution script for data archiving for a subsequent system; reading configuration parameters of an archiving task from a configuration database, splicing the configuration parameters of the archiving task into a general execution component, and generating an execution script of the archiving task; by splicing the configuration parameters into the universal execution component, the universal execution component is provided with FLINK-CDC components, so that the universal execution component is connected with a source database and a target database, and the synchronous transfer of archive data among different databases is realized, and the universal execution component has specific data archive targets for executing data synchronization and data deletion; running an execution script of the archiving task, and copying, transferring and storing the data in the source database to the target database; according to task trigger time, archiving logic and the like formed by archiving configuration parameters, the execution script rapidly realizes data archiving among different database types; determining whether the data in the source database is successfully copied, transferred and stored in the target database, if so, continuing to run an execution script of the archiving task, deleting the data in the source database, and if not, returning to the step 100; after the data is archived from the source database to the target database, determining, performing the next action of the archiving target, completing the data deletion of the source database, and completing the archiving target of the archiving task.
In order to better understand the above technical solutions, the following detailed description will be made with reference to the accompanying drawings and specific embodiments, and it should be understood that specific features in the embodiments and examples of the present application are detailed descriptions of the technical solutions of the present application, and not limit the technical solutions of the present application, and the technical features in the embodiments and examples of the present application may be combined with each other without conflict.
Referring to fig. 1, fig. 1 is a flow chart of a method for managing heterogeneous database data archiving according to an embodiment of the present invention, where the method for managing heterogeneous database data archiving includes:
Step 100: acquiring configuration parameters of an archiving task and storing the configuration parameters in a configuration database; and forming configuration parameters of the archiving task according to the archiving target of the archiving task and the database object related to the archiving task, and providing original parameter information for generating an execution script for data archiving for a subsequent system.
Specifically, the configuration parameters of the archiving task include an archiving task ID, an archiving task name, an archiving task scheduling period, a service module definition, a determination on whether there is an undelivered data statement at the time of transmission, a pre-SQL statement of the archiving task, a post-SQL statement of the archiving task, a statement on abnormal execution at the time of transmission, a statement of data synchronous query data, a source database IP, a source database port, a source database user, a source database password, a source database type, a source database name, a target database IP, a target database port, a target database user, a target database password, a target database type, and a target database name.
The present business is increased, the generated data is increased, the corresponding data in the source database is increased, the data in the source database is required to be transferred, stored and deleted when the data in the source database is increased for a certain time, so that the capacity of the source database is reduced, the processing event is taken as an archiving task of the present invention, and the archiving task is to synchronize the data in the source database into the target database by the source database data, and then delete the data after the synchronization is successful.
Since the data of different services are not necessarily stored in the same source database and transferred to the same target database when processing various service data, the data object processed by each archiving task is also different, and the archiving task needs to be defined, including an archiving task ID and an archiving task name, so as to distinguish each archiving task, and start the archiving task, the archiving task ID may be formed in a form of a combination of letters and numbers, such as db_job_001, and the archiving task name may be formed in Chinese characters, such as archiving task-test one. After the archiving task is defined, parameters of the archiving task need to be configured so as to complete the archiving target, for example, the current archiving data belongs to the service module, and the current archiving task needs to be defined as the service module, for example, the archiving data belongs to the customer/whole vehicle/after-sale service module, and the like. The method comprises the steps of completing data archiving from source database data to target database, establishing connection with the source database and the target database, only connecting the source database and the target database, archiving data from the source database data to the target database, configuring various parameters of the source database and the target database, establishing connection with a source database path through the source database name, the source database user and the source database password, and establishing connection with the target path through the target database name, the target database user and the target database password. For the IP/port, user name, password, type, name of the source database and the destination database, the following examples can be referenced: IP:192.168.0.1, port: 1521, username: zhang III, password: 123456, name: TEST, type: mySQL/MariaDB/PG/Oracle/MongoDB/SqlServer/OceanBase/PolarDB-X/TiDB, etc.
The connection with the source database and the target database is established, and some auxiliary judgment and record in the process of transferring and deleting configuration data are needed, wherein the auxiliary judgment and record comprises judging whether a data statement is not sent during transmission, a preposed SQL statement of an archiving task, a postponed SQL statement of the archiving task, an abnormal execution statement during transmission and a statement of data synchronous query data, namely: before archiving, whether the source database has data to be archived or not is required to be inquired, the time range of the source database for archiving the data is determined, for example, the data in the last year is inserted into a log table through a preposed SQL statement of an archiving task before data synchronization, the states of data synchronization and deletion are recorded, the states are updated after the data synchronization and the deletion are completed, and if sending abnormality occurs, the updated states are recorded in the log table.
According to the capacity of the source data and the size of the service data, proper data transfer frequency is set, and data can be transferred at maximum efficiency. The data archiving may be arranged to be performed once a day in the morning.
After the configuration of the parameters of the archiving task is completed, a configuration table of the archiving task is formed and stored in a configuration database so as to enable the archiving task to be started.
Step 200: reading configuration parameters of an archiving task from a configuration database, splicing the configuration parameters of the archiving task into a general execution component, and generating an execution script of the archiving task;
By splicing the configuration parameters into the universal execution component, the universal execution component is provided with FLINK-CDC components, so that the universal execution component is connected with the source database and the target database, and the synchronous transfer of the archive data among different databases is realized, and the universal execution component has specific data archiving targets for executing data synchronization and data deletion.
Specifically, the task management component receives an archiving task starting instruction containing an archiving task ID and an archiving task name, reads configuration parameter information of the archiving task corresponding to the ID and the name from a configuration database, and splices the configuration parameter information into the data synchronization component and the data deletion component to generate an execution script of the archiving task, wherein the general execution component comprises the data synchronization component and the data deletion component, the data synchronization component comprises FLINK-CDC components, the execution script operates according to the instruction flow of the task management component, and data archiving and data deleting are executed according to the configuration parameters of the archiving task.
The task management component is used as a system program operation control component, after receiving a starting instruction of an archiving task, the task management component requests to read a configuration parameter table of the archiving task from a configuration database according to the archiving task ID and the archiving task name information in the starting instruction, the configuration database searches according to the archiving task ID and the archiving task name information, the parameter configuration table corresponding to the archiving task ID and the archiving task name is searched, after the task management component reads the configuration parameters, the configuration parameters are spliced into the data synchronization component and the data deletion component, the data synchronization component and the data deletion component are used as components for executing data synchronization and data deletion, the data synchronization component and the data deletion component are provided with action execution commands, no action execution object exists before the configuration parameters are spliced into the data synchronization component and the data deletion component, the action execution commands exist after the configuration parameters are spliced into an execution script of the archiving task, and the data in the source database can be synchronously transferred into the target database only by starting the execution script, and the data in the source data can be deleted. Because of the need of data transfer of different types of databases, the data synchronization component of the invention is composed of FLINK-CDC components as core components, and has the function of synchronously transferring the data in the different types of databases. The task management component acts as a set of execution instructions with archiving logic to archive tasks that control the order and direction of execution of the data synchronization component and the data deletion component. The configuration parameters are used as character strings and program pieces, which can be directly spliced for the data synchronization component and the data deletion component. The data synchronization component and the data deletion component act as execution components that connect different functions, and can also be combined to form a common execution component for multiple functions.
Step 300: running an execution script of the archiving task, and copying, transferring and storing the data in the source database to the target database; according to task trigger time, archiving logic and the like formed by archiving configuration parameters, the execution script rapidly realizes data archiving among different database types;
Specifically, the task management component connects the source database, the target database and the data synchronization component according to the source database IP, the source database port, the source database user, the source database password, the source database type, the source database name, the target database IP, the target database port, the target database user, the target database password, the target database type and the target database name, the task management component connects the source database and the data deletion component according to the source database IP, the source database port, the source database user, the source database password, the source database type and the source database name, the task management component triggers the execution script operation according to the archiving task scheduling period, executes the script operation, firstly determines the data time period of the source database according to the statement of the data synchronization query data, then judges whether the data is not transmitted if yes, then executes the data synchronization script part, copies and stores the data in the source database according to the definition of the service module into the corresponding block of the target database, inserts the table according to the pre-SQL of the statement before the time period of the determining the synchronization data, after the execution is completed, generates the updating task update state according to the pre-SQL statement, and if the updating task update state is abnormal state is generated when the updating task is executed, if the updating state is abnormal state is generated.
After the data synchronization component and the data deletion component are configured with parameters, the task management component establishes connection between the source database and the target database and the data synchronization component and the data deletion component, determines whether the source database and the target database are in an archiving task scheduling period, if yes, triggers the execution script to run, the data synchronization component and the data deletion component execute according to the instruction flow of the task management component, firstly determines whether data in the source data needs to be sent in a last period of time, carries out data synchronization transfer if yes, and the FLINK-CDC component synchronously transfers the data in the source database into a service data area corresponding to the target database according to the service type, carries out action execution record on a log table, and updates the log table after the execution action is completed, including successful sending and abnormal task state record updating.
Step 400: and determining whether the data in the source database is successfully copied, transferred and stored in the target database, if so, continuing to run the execution script of the archiving task, deleting the data in the source database, and if not, returning to the step 100.
After the data is archived from the source database to the target database, determining, performing the next action of the archiving target, completing the data deletion of the source database, and completing the archiving target of the archiving task.
Specifically, the task management component reads updated task state information from the configuration database, and determines whether the execution script succeeds in synchronously archiving the data in the source database to the target database; if the execution script is successful, the execution script continues to run, the execution data deleting script part deletes the data in the source database, and after the deletion is completed, the update task state information is generated according to the post SQL statement of the archiving task and is stored in the configuration database; if not, return to step 100.
After the data synchronization component performs data synchronization from the source database to the target database, in order to prevent the data synchronization from being unsuccessful, the data deletion component continues to perform data deletion, data in the source database is deleted, before the data deletion component performs data deletion, whether the data in the source database is successfully filed synchronously needs to be determined, at this time, the task management component reads the state of completing the current filing task from the update task state information by reading the update task state information in the configuration database, whether the data synchronization is abnormal or not, whether the data synchronization is successfully filed in the target database or not, after the data filing is successfully completed is determined, the data deletion component operates, the data in the source database is deleted, after the deletion is completed, the update task state information is generated according to post SQL sentences of the filing task, and the update task state information is stored in the configuration database, so that the data filing and deleting of the whole filing task are completed.
If it is determined that the data synchronization component generates an abnormal condition during data synchronization, the process needs to go back to step 100 again, detect, modify and update the configuration parameters, rerun the execution script, and perform data synchronization and deletion on the data of the source database.
Based on the above mode, the archiving management system acquires the configuration parameters of the archiving task and stores the configuration parameters in the configuration database; forming configuration parameters of an archiving task according to an archiving target of the archiving task and a database object related to the archiving task, and providing original parameter information for generating an execution script for data archiving for a subsequent system; reading configuration parameters of an archiving task from a configuration database, splicing the configuration parameters of the archiving task into a general execution component, and generating an execution script of the archiving task; by splicing the configuration parameters into the universal execution component, the universal execution component is provided with FLINK-CDC components, so that the universal execution component is connected with a source database and a target database, and the synchronous transfer of archive data among different databases is realized, and the universal execution component has specific data archive targets for executing data synchronization and data deletion; running an execution script of the archiving task, and copying, transferring and storing the data in the source database to the target database; according to task trigger time, archiving logic and the like formed by archiving configuration parameters, the execution script rapidly realizes data archiving among different database types; determining whether the data in the source database is successfully copied, transferred and stored in the target database, if so, continuing to run an execution script of the archiving task, deleting the data in the source database, and if not, returning to the step 100; after the data is archived from the source database to the target database, determining, performing the next action of the archiving target, completing the data deletion of the source database, and completing the archiving target of the archiving task.
Correspondingly, the invention also provides a system for managing the data archiving of the heterogeneous database, referring to fig. 2, fig. 2 is a schematic diagram of a system module structure for managing the data archiving of the heterogeneous database, which is provided by an embodiment of the invention, the system synchronously transfers the data of the source database of the archiving task and deletes the data through the method, the system comprises a parameter configuration component, a task management component, a data synchronization component, a data deletion component and a configuration database, wherein the parameter configuration component is used for acquiring the configuration parameters of the archiving task and storing the configuration parameters in the configuration database;
The task management component is used for reading configuration parameters of an archiving task from the configuration database, splicing the configuration parameters of the archiving task into the data synchronization component and the data deletion component, and managing the running execution of the data synchronization component and the data deletion component;
the data synchronization component is used for copying, transferring and storing the data in the source database to the target database;
The data deleting component is used for deleting the data in the source database;
The configuration database is used for storing configuration parameter information of the archiving task.
Preferably, the configuration parameters of the archiving task obtained by the parameter configuration component include an archiving task ID, an archiving task name, an archiving task scheduling period, a service module definition, a determination on whether there is an undelivered data statement at the time of sending, a preposed SQL statement of the archiving task, a postposed SQL statement of the archiving task, an abnormal execution statement at the time of sending, a statement of data synchronous query data, a source database IP, a source database port, a source database user, a source database password, a source database type, a source database name, a target database IP, a target database port, a target database user, a target database password, a target database type, and a target database name.
Preferably, the task management component receives an archive task start instruction containing an archive task ID and an archive task name, reads configuration parameter information of the archive task corresponding to the ID and the name from the configuration database, and splices the configuration parameter information into the data synchronization component and the data deletion component to generate an execution script of the archive task, wherein the data synchronization component comprises a FLINK-CDC component. Preferably, the task management component connects the source database and the target database with the data synchronization component according to the source database IP, the source database port, the source database user, the source database password, the source database type, the source database name, the target database IP, the target database port, the target database user, the target database password, the target database type, the target database name, the task management component connects the source database with the data deletion component according to the source database IP, the source database port, the source database user, the source database password, the source database type, the source database name, the task management component triggers execution script operation according to an archiving task scheduling period, executes script operation, determines a data time period of the source database according to a statement of data synchronization query data, if yes, then executes a data synchronization script part, copies and stores SQL data in the source database into a block corresponding to the target database according to a service module definition, inserts a front statement according to a front archive table before determining the time period of the synchronization data, generates update state according to an archive SQL statement after the update state is completed, if abnormal task execution state is completed, and generates abnormal task execution state according to the archive state after the update state is completed.
Preferably, the task management component reads updated task state information from the configuration database, and determines whether the execution script succeeds in synchronously archiving the data in the source database to the target database;
if the execution script is successful, the execution script continues to run, the data deleting script part deletes the data in the source database, and after the deletion is completed, the update task state information is generated according to the post SQL statement of the archiving task and is stored in the configuration database.
In the description provided herein, numerous specific details are set forth. However, it is understood that embodiments of the invention may be practiced without these specific details. In some instances, well-known methods, structures and techniques have not been shown in detail in order not to obscure an understanding of this description.
Similarly, it should be appreciated that in the foregoing description of exemplary embodiments of the invention, various features of the invention are sometimes grouped together in a single embodiment, figure, or description thereof for the purpose of streamlining the disclosure and aiding in the understanding of one or more of the various inventive aspects. However, the disclosed method should not be construed as reflecting the intention that: i.e., the claimed invention requires more features than are expressly recited in each claim. Rather, as the following claims reflect, inventive aspects lie in less than all features of a single foregoing disclosed embodiment. Thus, the claims following the detailed description are hereby expressly incorporated into this detailed description, with each claim standing on its own as a separate embodiment of this invention.
Those skilled in the art will appreciate that the modules in the apparatus of the embodiments may be adaptively changed and disposed in one or more apparatuses different from the embodiments. The modules or units or components of the embodiments may be combined into one module or unit or component and, furthermore, they may be divided into a plurality of sub-modules or sub-units or sub-components. Any combination of all features disclosed in this specification (including any accompanying claims, abstract and drawings), and all of the processes or units of any method or apparatus so disclosed, may be used in combination, except insofar as at least some of such features and/or processes or units are mutually exclusive. Each feature disclosed in this specification (including any accompanying claims, abstract and drawings), may be replaced by alternative features serving the same, equivalent or similar purpose, unless expressly stated otherwise.
Furthermore, those skilled in the art will appreciate that while some embodiments herein include some features but not others included in other embodiments, combinations of features of different embodiments are meant to be within the scope of the invention and form different embodiments. For example, in the following claims, any of the claimed embodiments can be used in any combination.
Various component embodiments of the invention may be implemented in hardware, or in software modules running on one or more processors, or in a combination thereof. Those skilled in the art will appreciate that some or all of the functions of some or all of the components in accordance with embodiments of the present invention may be implemented in practice using a microprocessor or Digital Signal Processor (DSP). The present invention can also be implemented as an apparatus or device program (e.g., a computer program and a computer program product) for performing a portion or all of the methods described herein. Such a program embodying the present invention may be stored on a computer readable medium, or may have the form of one or more signals. Such signals may be downloaded from an internet website, provided on a carrier signal, or provided in any other form.
It should be noted that the above-mentioned embodiments illustrate rather than limit the invention, and that those skilled in the art will be able to design alternative embodiments without departing from the scope of the appended claims. In the claims, any reference signs placed between parentheses shall not be construed as limiting the claim. The word "comprising" does not exclude the presence of elements or steps not listed in a claim. The word "a" or "an" preceding an element does not exclude the presence of a plurality of such elements. The invention may be implemented by means of hardware comprising several distinct elements, and by means of a suitably programmed computer. In the unit claims enumerating several means, several of these means may be embodied by one and the same item of hardware. The use of the words first, second, third, etc. do not denote any order. These words may be interpreted as names.
Claims (10)
1. A method of managing heterogeneous database data archiving, comprising the steps of:
Step 100: acquiring configuration parameters of an archiving task and storing the configuration parameters in a configuration database;
step 200: reading configuration parameters of an archiving task from a configuration database, splicing the configuration parameters of the archiving task into a general execution component, and generating an execution script of the archiving task;
Step 300: running an execution script of the archiving task, and copying, transferring and storing the data in the source database to the target database;
Step 400: and determining whether the data in the source database is successfully copied, transferred and stored in the target database, if so, continuing to run the execution script of the archiving task, deleting the data in the source database, and if not, returning to the step 100.
2. The method for managing heterogeneous database data archiving according to claim 1, wherein the configuration parameters of the archiving task in step 100 include an archiving task ID, an archiving task name, an archiving task scheduling period, a service module definition, a determination on sending whether there is an undelivered data statement, a pre-SQL statement of an archiving task, a post-SQL statement of an archiving task, a statement on sending an abnormal execution, a statement of data synchronization query data, a source database IP, a source database port, a source database user, a source database password, a source database type, a source database name, a target database IP, a target database port, a target database user, a target database password, a target database type, a target database name.
3. The method for managing heterogeneous database data archives of claim 2, wherein step 200 comprises:
The method comprises the steps that a task management component receives an archiving task starting instruction containing an archiving task ID and an archiving task name, configuration parameter information of the archiving task corresponding to the ID and the name is read from a configuration database, the configuration parameter information is spliced into a data synchronization component and a data deletion component, and an execution script of the archiving task is generated, wherein the general execution component comprises the data synchronization component and the data deletion component, the data synchronization component comprises FLINK-CDC components, the execution script operates according to the instruction flow of the task management component, and data archiving and data deleting are executed according to the configuration parameter of the archiving task.
4. The method for managing heterogeneous database data archives of claim 3, wherein step 300 comprises:
The task management component connects the source database with the data synchronization component according to the source database IP, the source database port, the source database user, the source database password, the source database type, the source database name, the target database IP, the target database port, the target database user, the target database password, the target database type and the target database name, the task management component connects the source database with the data deletion component according to the source database IP, the source database port, the source database user, the source database password, the source database type and the source database name, the task management component triggers the execution of script operation according to the archiving task scheduling period, executes the script operation, determines the data time period of the source database according to the statement of the data synchronization query data, judges whether the data is not transmitted, if yes, then executes the data synchronization script part, copies and stores the data in the source database into a block corresponding to the target database according to the definition of the service module, inserts a log table according to the front SQL statement of the archiving task before the time period is determined, generates updating statement according to the updating statement after the archiving task is completed, if abnormal state information is generated according to the updating statement, and if abnormal state information is generated when the updating statement is stored in the configuration data is executed.
5. The method for managing heterogeneous database data archives of claim 4, wherein step 400 comprises:
The task management component reads updated task state information from the configuration database and determines whether the execution script succeeds in synchronously archiving the data in the source database to the target database;
If the execution script is successful, the execution script continues to run, the execution data deleting script part deletes the data in the source database, and after the deletion is completed, the update task state information is generated according to the post SQL statement of the archiving task and is stored in the configuration database;
If not, return to step 100.
6. A system for managing heterogeneous database data archiving, comprising:
The system comprises a parameter configuration component, a task management component, a data synchronization component, a data deletion component and a configuration database, wherein the parameter configuration component is used for acquiring configuration parameters of an archiving task and storing the configuration parameters in the configuration database;
The task management component is used for reading configuration parameters of an archiving task from the configuration database, splicing the configuration parameters of the archiving task into the data synchronization component and the data deletion component, and managing the running execution of the data synchronization component and the data deletion component;
the data synchronization component is used for copying, transferring and storing the data in the source database to the target database;
The data deleting component is used for deleting the data in the source database;
The configuration database is used for storing configuration parameter information of the archiving task.
7. The system for managing heterogeneous database data archives of claim 6, wherein the configuration parameters of the archival tasks acquired by the parameter configuration component include an archival task ID, an archival task name, an archival task scheduling period, a business module definition, a send-time determination of whether there are any unsent data statements, a pre-SQL statement of an archival task, a post-SQL statement of an archival task, a send-time abnormal execution statement, a statement of data synchronization query data, a source database IP, a source database port, a source database user, a source database password, a source database type, a source database name, a target database IP, a target database port, a target database user, a target database password, a target database type, a target database name.
8. The system for managing heterogeneous database data archiving of claim 7, wherein the task management component receives an archiving task initiation instruction containing an archiving task ID and an archiving task name, reads configuration parameter information for the archiving task corresponding to the ID and the name from the configuration database, and splices the configuration parameter information into the data synchronization component and the data deletion component, generating an execution script for the archiving task, wherein the data synchronization component comprises a FLINK-CDC component.
9. The system for managing heterogeneous database data archiving according to claim 8, wherein the task management component connects the source database with the data deletion component according to the source database IP, source database port, source database user, source database password, source database type, source database name, target database IP, target database port, target database user, target database password, target database type, target database name, the task management component copies and stores the source database, the target database with the data synchronization component according to the service module definition, the task management component connects the source database with the data deletion component according to the source database IP, source database port, source database user, source database password, source database type, source database name, the task management component triggers execution script operation according to an archiving task scheduling period, executes script operation, firstly determines a data time period of the source database according to a statement of data synchronization query data, then judges whether data is not transmitted, if yes, then executes a data synchronization script portion, copies and stores the data in the source database into a block corresponding to the target database according to the defined by the FLINK-CDC component, generates a task update table according to the determined time period of the synchronization data, updates a task update statement, and then generates and stores an abnormal task update state after the task update is completed according to the configuration statement, if the task update statement is completed.
10. The system for managing heterogeneous database data archiving of claim 9, wherein the task management component reads updated task state information from the configuration database, determines whether an execution script succeeds in synchronously archiving data in a source database to a target database;
if the execution script is successful, the execution script continues to run, the data deleting script part deletes the data in the source database, and after the deletion is completed, the update task state information is generated according to the post SQL statement of the archiving task and is stored in the configuration database.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202211341941.2A CN117992445A (en) | 2022-10-28 | 2022-10-28 | Method and system for managing heterogeneous database data archiving |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202211341941.2A CN117992445A (en) | 2022-10-28 | 2022-10-28 | Method and system for managing heterogeneous database data archiving |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN117992445A true CN117992445A (en) | 2024-05-07 |
Family
ID=90892139
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202211341941.2A Pending CN117992445A (en) | 2022-10-28 | 2022-10-28 | Method and system for managing heterogeneous database data archiving |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN117992445A (en) |
-
2022
- 2022-10-28 CN CN202211341941.2A patent/CN117992445A/en active Pending
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US11468060B2 (en) | Automatic query offloading to a standby database | |
| US9715507B2 (en) | Techniques for reconciling metadata and data in a cloud storage system without service interruption | |
| US9639429B2 (en) | Creating validated database snapshots for provisioning virtual databases | |
| US8572037B2 (en) | Database server, replication server and method for replicating data of a database server by at least one replication server | |
| EP2599019B1 (en) | Enterprise-based searching of new and updated data | |
| US9747291B1 (en) | Non-disruptive upgrade configuration translator | |
| US20120278429A1 (en) | Cluster system, synchronization controlling method, server, and synchronization controlling program | |
| EP3786802A1 (en) | Method and device for failover in hbase system | |
| JP4136615B2 (en) | Database system and database access method | |
| US20220335011A1 (en) | System and Method for Eliminating Full Rescan Synchronizations on Service Restarts | |
| US10089375B2 (en) | Idling individually specified objects during data replication | |
| US20200364241A1 (en) | Method for data synchronization between a source database system and target database system | |
| CN114490677A (en) | Data synchronization in a data analysis system | |
| US9390131B1 (en) | Executing queries subject to different consistency requirements | |
| CN112800060B (en) | Data processing method, data processing device, computer readable storage medium and electronic equipment | |
| WO2023111910A1 (en) | Rolling back database transaction | |
| US20110320416A1 (en) | Eliminating Redundant Processing of Data in Plural Node Systems | |
| CN117992445A (en) | Method and system for managing heterogeneous database data archiving | |
| CN116233146A (en) | Techniques to achieve cache coherency across distributed storage clusters | |
| CN114816682A (en) | Distributed transaction processing method, system and device | |
| US11853321B1 (en) | Data replication without in-place tombstones | |
| JPH034339A (en) | System for updating data base in distributed processing system | |
| JPH09265424A (en) | Synchronization system and method for distributed file | |
| US11874751B2 (en) | Operating a data center | |
| JPH117403A (en) | Replication database mismatching avoiding device and avoiding method |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |