CN103823797A - FTP (file transfer protocol) based real-time industry database data synchronization system - Google Patents
FTP (file transfer protocol) based real-time industry database data synchronization system Download PDFInfo
- Publication number
- CN103823797A CN103823797A CN201210460308.5A CN201210460308A CN103823797A CN 103823797 A CN103823797 A CN 103823797A CN 201210460308 A CN201210460308 A CN 201210460308A CN 103823797 A CN103823797 A CN 103823797A
- Authority
- CN
- China
- Prior art keywords
- incremental
- data
- incremental data
- source
- ftp
- 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
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/23—Updating
- G06F16/2358—Change logging, detection, and notification
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/27—Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
- G06F16/275—Synchronous replication
-
- H—ELECTRICITY
- H04—ELECTRIC COMMUNICATION TECHNIQUE
- H04L—TRANSMISSION OF DIGITAL INFORMATION, e.g. TELEGRAPHIC COMMUNICATION
- H04L67/00—Network arrangements or protocols for supporting network services or applications
- H04L67/01—Protocols
- H04L67/10—Protocols in which an application is distributed across nodes in the network
- H04L67/1095—Replication or mirroring of data, e.g. scheduling or transport for data synchronisation between network nodes
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Databases & Information Systems (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Computer Networks & Wireless Communication (AREA)
- Signal Processing (AREA)
- Computing Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
Disclosed is an FTP (file transfer protocol) based real-time industry database data synchronization system. The system comprises an incremental data export tool module, an incremental data import tool module, an FTP server and an incremental data browsing tool module. The incremental data export tool module is used for reading incremental information of databases to form incremental data packets, and stores the incremental data packets in the configured FTP server, and a trigger mode is adopted as a database incremental data extract-transform-load 'short for ETL' mode; the incremental data import tool module is used for acquiring incremental update packet files from the FTP server and executing the files, and synchronizes data managed and used by legacy systems into a new system in an incremental updating manner; the incremental data browsing tool module analyzes incremental SQL (structured query language) statements, and displays incremental data, needing to be executed, in the new system.
Description
Technical field
The present invention relates to database in phase field, particularly relate to data real-time between two databases of model identical synchronous, for the data sharing solving between each system, proposed a kind of general solution.
Background technology
In the development and implementation process of enterprise's new-old system, there is such situation: 1, in enterprise, have a lot of existing systems and database, we are referred to as legacy (Legacy); 2, system newly developed or new enforcement often needs to use the part in existing database or database; 3, a lot of existing systems or database, as inline system, considers from safety perspective, does not support the direct access of new system; 4,, in order to guarantee the consistance of business data, simultaneously also for managerial convenience, enterprise administrator is only safeguarded a piece of data.In this case, need to study sector database real time data synchronization system and method thereof.
In general application system, conventionally adopt the mode of hard coded, data synchronous needs are directly realized in code, call the interface of external system by coding simultaneously, by data manipulation event notice to external system.For example, database A has been carried out to data inserting operation, these data need to be inserted into database B simultaneously, hard coded can will write in code the operation of database B in code so.Meanwhile, by calling the interface of external system, by the operational notification of database to external system.
There are two problems in this system: 1, in the time that application scenarios changes, just need to revise source code and satisfy the demands, along with the increase of data and application scenarios, need to constantly revise source code, thereby can cause the difficulty of code maintenance increasing.For example, when business is changed, database A is carried out after data inserting operation, do not need data to be inserted into B, do not need to be notified external system yet, owing to being hard coded, recompilate after code so need to modify and dispose, could meet business demand.2, need legacy system development business to revise code, implant the synchronously function to new system database by its Data Update, so do, have potential safety hazard, also have commercial problem; 3, simultaneously, legacy system must have the new system database authority of accessing operation, has increased potential safety hazard.
To revise the inconvenience that brings of source code in order reducing, can also to carry out the synchronous of data by configuration file, call the interface of external system, by event notice to external system.For example, database A is carried out to data inserting operation, these data need to be inserted in database B simultaneously.Now, read configuration file and obtain the link information of database B, by Data Update in database B.Meanwhile, by calling the interface of external system, by the operational notification of database to external system.
Although it is synchronous to carry out data by the mode of configuration file, the information in configuration file is database linkage information, still needs hard coded to realize to the synchronous operation statement of data; And owing to having called the interface of external system in code, in the time that the interface of external system changes, can cause system to go wrong, the external interface for example originally calling in code is for receiving, and as after external system interface changes, become transmission.But former code is not revised, can cause system normally not move.And, in the time having new application system to expect database manipulation notice, also need to revise code, affect the extendability of system.
Summary of the invention
For above problem, the invention provides a kind of sector database real time data synchronization system based on File Transfer Protocol, the present invention can allow data in legacy system and database can incremental update in new system, make managerial personnel only need to safeguard a piece of data, do not need again extra interface exploitation workload simultaneously, avoid a large amount of modifications of code, can strengthen again the extendability of system.
Technical scheme: to the invention provides a kind of sector database real time data synchronization system based on File Transfer Protocol in order overcoming the above problems, to it is characterized in that: comprise that incremental data derives tool model, incremental data import tool module, ftp server, incremental data browser module;
Incremental data derives tool model, for reading database increment information, forms incremental data bag, and deposits the ftp server configuring in, and database incremental data extracts " being called for short ETL " mode and adopted trigger mode;
Incremental data import tool module, for obtaining incremental update APMB package from ftp server and carrying out, arrives new system by the synchronous incremental update of data of legacy use that system is managed;
Incremental data browser module, analyzes increment SQL statement, shows the incremental data in the new system that needs to carry out.
Described trigger mode is according to extracting requirement, on the source table that will be extracted, set up and insert, revise, delete 3 triggers, data in the table of source change, just by corresponding trigger, the data of variation are write to an Incremental Log table, the increment extraction of ETL is from Incremental Log table rather than direct extracted data in the table of source, and the data that simultaneously extracted in Incremental Log table will be labeled in time or delete; Incremental Log table is not stored all field informations of incremental data, and just store the key value that source table name claims, upgrades and upgrade action type " knsen, update or delete ", the key value that first ETL increment extraction process claims and upgrade according to source table name, from the table of source, extract corresponding complete documentation, according to upgrading action type, object table is processed accordingly again.
Described incremental data import tool module: 1. configuration data correlation rule; 2. obtain incremental update bag from ftp server; 3. carry out incremental update bag; 4. multithreading is carried out the SQL statement being kept in database; 5. delete incremental update file from ftp server; Complete incremental data between legacy system and new system synchronous, realize legacy system and the newly data sharing between system.
Described incremental data import tool module is started by timer.
Described data correlation rule configuration file is XML file.
Described obtains incremental update bag from ftp server:
1. incremental data import tool sends the message of configuration ftp server address to FTP proxy class object, and this message only sends once;
2. incremental data import tool main classes sends request to FTP proxy class object, judges whether new incremental update bag ftp file, and FTP proxy class can be confirmed to ftp server;
If 3. there is new incremental update bag ftp file, sends and obtain the request of incremental update APMB package to FTP proxy class, and enter the process of carrying out incremental update bag of resolving; If there is no incremental update bag,, after dormancy a period of time, continue step 2..
Described execution incremental update bag:
1. use JSON converter, convert incremental update APMB package to incremental data entity class object array;
2. use incremental data entity resolver to resolve each incremental data entity class object, realize the incremental update of data; The parsing of incremental data entity: can obtain source by data correlation rule and show the relation that corresponding object table table name and each field of object table and source are shown; Incremental data entity resolver operates according to incremental data entity content, if Operate==D generates a cancel statement, if Operate==I generates one and inserts statement, if Operate=U generates one and upgrades statement; The binding variable value of these statements, from incremental data entity, or the function of incremental data entities field and object literary name section;
3. preserve the SQL statement generating in database.
The demonstration point two-stage of described incremental data browser module is carried out, and first realizes incremental update table, then the incremental update data in indicator gauge.
Described data correlation Rule Expression is: " from the source table of legacy system " is mapped to " the object table of new system "; The field of object table or be certain field of source table, or be the function that some field is shown in source, the function of having realized comprises:
① strncat
Function: character string connects;
Form: strncat (parameter 1 ... .)
Parameter type:
Source literary name section is prefix with " s_ ";
Object literary name section is prefix with " d_ ";
Common character string, causes with " ";
② replace
Function: character string is replaced;
Form: replace (subject string, searches string, replaces string)
" subject string " " searches string " and " replacing string " parameter type:
Source literary name section is prefix with " s_ ";
Object literary name section is prefix with " d_ ";
Common character string, causes with " ";
③ validate
Function: character string is replaced;
Form: validate (content, type)
Content parameter declaration:
Source literary name section is prefix with " s_ ";
Object literary name section is prefix with " d_ ".
Type parameter declaration:
" I.D. ": whether checking content is I.D.;
" Email ": whether checking content is Email;
" scope [M-N] ": content is within M and N scope in checking;
" comprise (comprising value) ": whether checking content comprises " comprising value ";
" enumerate value 1, value 2 ... .} ": checking content value value 1, value 2 ... within .};
Described data correlation Rule Expression is: the table NewWord in new system, from certain legacy system table Word, claim that Word is the source table of NewWord table, and NewWord table is the object table of Word table.Wherein, the Word field in object table is from the Word of source table, and WordClass field is from the WordClass of source table, and WordID field is from the ID of source table.
Beneficial effect:
1. the present invention allows user to be related to demand according to the data correlation between new system data and existing system, configuration data correlation rule.
2. method of data synchronization provided by the invention, does not need to revise legacy system, does not need to revise new system yet, imports and exports but customer data synchronisation requirement is summed up as to incremental data.
3. the present invention has adopted existing database journal analytic function, can realize the centralized management of incremental data.
4. the present invention is setting up contact between new system database and existing legacy database, can be on the basis of understanding customer demand, and help client to complete data between these two databases synchronous.
5. in relational database, retain history extremely important concerning user, the present invention can help user easily to realize this function, and user can go to check the historical operation of database at any time.
Accompanying drawing explanation
Fig. 1 is the incremental data flow graph of system in the present invention;
Fig. 2 is the activity diagram of working-flow in the present invention;
Fig. 3 is increment information example in the present invention;
Fig. 4 is the use case figure of incremental data import tool module in the present invention;
Fig. 5 is correlation rule edit page in the present invention;
Fig. 6 is data correlation rule syntax in the present invention;
Fig. 7 is data correlation Sample Rules in the present invention;
Fig. 8 is the sequence chart of obtaining incremental data renewal packet procedures in the present invention;
Fig. 9 is the sequence chart that the execution incremental data in the present invention is upgraded packet procedures;
Figure 10 is the process flow diagram of the resolving of incremental data entity in the present invention;
Figure 11 is SQL statement exemplary plot corresponding to incremental update information in the present invention;
Figure 12 is the incremental data table display page that has renewal in the present invention;
Figure 13 is the incremental data display page of certain tables of data in the present invention.
Embodiment
Below in conjunction with accompanying drawing, the present invention is described in further detail.
As shown in Figure 1, the system architecture in the present invention, between legacy system and database thereof and new system new database, completes new system required, from the synchronous renewal of the incremental data of legacy system.
A sector database real time data synchronization system based on File Transfer Protocol, comprises that incremental data derives tool model, incremental data import tool module, ftp server, incremental data browser module.
Fig. 2 is the activity diagram of the working-flow in the present invention, has described comprise in these main modular in system movable and their precedence relationship.
Introduce in detail modules below:
incremental data derives tool model,for reading database increment information, form incremental data bag, and deposit the ftp server configuring in.Common database incremental data extracts (being called for short ETL) mode, comprising: 1, trigger mode: trigger mode is a kind of increment extraction mechanism of generally taking.2, timestamp mode: when timestamp mode refers to increment extraction, extraction process is decided and extracted which data by the value of the timestamp field of comparison system time and extraction source table.3, full list deletion inserted mode: first delete object table data before full list deletion inserted mode refers to each extraction, the brand-new data that load when extraction.In fact which is equal to increment extraction full dose and extracts.Little for data volume, the time cost that full dose extracts is less than while carrying out the algorithm of increment extraction and condition cost, can adopt which.4, entirely show comparison mode: full table comparison is in the time of increment extraction, and ETL process is the record of reference source table and object table one by one, and record newly-increased and that revise is read out.5, log sheet mode: for the Production database of having set up operation system, can create business diary table in database, in the time that the business datum of specific needs monitoring changes, carry out updating maintenance log sheet content by corresponding operation system program module.Which when increment extraction, determine load data and how to load by reading log sheet data.The maintenance of log sheet need to be completed with code by operation system program.6, syslog analysis mode: which judges the data of variation by the daily record of analytical database self.7, certain database mode, such as Oracle changes data capture mode (changed data capture, CDC).
The present invention has adopted trigger mode.Which is according to extracting requirement, on the source table that will be extracted, set up and insert, revise, delete 3 triggers, data in the table of source change, just by corresponding trigger, the data of variation are write to an Incremental Log table, the increment extraction of ETL is from Incremental Log table rather than direct extracted data in the table of source, and the data that simultaneously extracted in Incremental Log table will be labeled in time or delete.For simplicity, Incremental Log table is not generally stored all field informations of incremental data, and just store the key value that source table name claims, upgrades and upgrade action type (knsen, update or delete), the key value that first ETL increment extraction process claims and upgrade according to source table name, from the table of source, extract corresponding complete documentation, according to upgrading action type, object table is processed accordingly again.
1, incremental data extracts.Take oracle database as example, the process that employing trigger mode is carried out increment data capturing is as follows:
Like this, all DML operations of his-and-hers watches T are just recorded in Incremental Log table DML_LOG, note not having complete recording increment data itself, the source of just having recorded incremental data in Incremental Log table.While carrying out increment ETL, only need to be according to the record case in Incremental Log table, the anti-source table of looking into obtains real incremental data.
(1) create Incremental Log table DML_LOG:
create table DML_LOG(
ID NUMBER primary key,
--certainly increase major key
TABLENAME VARCHAR2 (200),
--source table name claims
RECORDID NUMBER,
--the Major key of source table incremental record
DMLTYPE char (1),
--increment type, I represents to increase newly: U represents to upgrade; D represents to delete
EXECUTEDATE DATE
--time of origin
);
(2) for DML_LOG, to create sequence SEQ_DML_LOG upper, so that trigger generates ID value while writing Incremental Log table.
create sequence SEQ_DML_LOG
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;
(3) for each table that will monitor, create a trigger, it is as follows that for example his-and-hers watches Word creates trigger:
create Or replace trigger IUDTest
BEFORE INSERT OR UPDATE OR DELETE ON Word
for each row
declare
l_dml_type varchar2(1);
begin
if INSERTING then
l_dml_type := 'I';
elsif UPDATING then
l_dml_type := 'U';
elsif DELETING then
l_dml_type := 'D';
end if;
if DELETING then
insert into DML_LOG
(ID, TABLENAME, RECORDID, EXECUTEDATE, DMLTYPE)
values
(seq_dml_log.nextval, 'WORD', :old.ID, sysdate, l_dml_type);
else if UPDATING and (:old.ID=:new.ID) then
insert into DML_LOG
(ID, TABLENAME, RECORDID, EXECUTEDATE, DMLTYPE)
values
(seq_dml_log.nextval, 'WORD', :old.ID, sysdate, 'D');
insert into DML_LOG
(ID, TABLENAME, RECORDID, EXECUTEDATE, DMLTYPE)
values
(seq_dml_log.nextval, 'WORD', :new.ID, sysdate, l_dml_type);
else insert into DML_LOG
(ID, TABLENAME, RECORDID, EXECUTEDATE, DMLTYPE)
values
(seq_dml_log.nextval, 'WORD', :new.ID, sysdate, l_dml_type);
end if;
end;
2, read increment information.Increase derivation instrument reads incremental data from database, uses JSON format string to represent.These incremental datas comprise three kinds of forms:
1. insert the form of record: { Operate: " I ", RecordID: " Record ID ", the complete content % of Content:% record, TableName: " source table name " }
2. the form of amendment record: Operate: " U ", RecordID: " Record ID ", the complete content % of Content:% record,, TableName: " source table name " }
3. the form of deletion record: Operate: " D ", RecordID: " Record ID ",, TableName: " source table name " }
Wherein, inserting the complete content of record, is also a JSON string.Its form is as follows:
The complete content %:={ field name 1 of % record: " field value 1 ", field name 2: " field value 2 " ...
As shown in Figure 3, this increment information represents there is the incremental data of three operation Word tables to the increment information example reading, and wherein inserts 1, upgrades 1, deletes 1.Wherein, inserting Record ID is that 100, Word field contents is " open-minded ", and WordClass field contents is " opening nearly class "; Amendment record ID is that 88, Word field is " cancellation ", and WordClass field contents is " cancelling nearly class "; The ID of deletion record is 90.
3, generate incremental data APMB package, and be saved on the ftp server configuring.
incremental data import tool module,for obtaining incremental update APMB package from ftp server and carrying out, the synchronous incremental update of data of legacy use that system is managed is arrived to new system.Fig. 4 is the use case figure of incremental data import tool module, and from this figure, we can find out that the main process of this module is as follows: correlation rule configuration personnel's configuration data correlation rule also saves as XML file; This incremental data import tool is started by timer, after startup, reading out data correlation rule configuration file, then from ftp server, obtain incremental update APMB package, and carry out this file, complete incremental data between legacy system and new system synchronous, realize legacy system and the newly data sharing between system.Main activities and case comprise:
1, configuration data correlation rule, Fig. 5 is the data correlation rule configuration page, and data correlation rule configuration result is preserved and is formed correlation rule file, and this file name is " object table .xml ".The present invention adopts XML representation of file data correlation rule.Rule syntax as shown in Figure 6.This correlation rule represents, " from the source table of legacy system " is mapped to " the object table of new system ".The field of object table or be certain field of source table, or be the function that some field is shown in source.The function of having realized comprises:
④ strncat
Function: character string connects;
Form: strncat (parameter 1 ... .)
Parameter type:
Source literary name section is prefix with " s_ ";
Object literary name section is prefix with " d_ ";
Common character string, causes with " ".
For example: strncat (s_word, d_wordclass), represents the word field in the table of source and the wordclass field in object table to carry out attended operation.
⑤ replace
Function: character string is replaced;
Form: replace (subject string, searches string, replaces string)
" subject string " " searches string " and " replacing string " parameter type:
Source literary name section is prefix with " s_ ";
Object literary name section is prefix with " d_ ";
Common character string, causes with " ".
For example: replace (s_wordclass, " near class ", " parent "), represents that " near class " replaces with " parent " by the wordclass field in the table of source.
⑥ validate
Function: character string is replaced;
Form: validate (content, type)
Content parameter declaration:
Source literary name section is prefix with " s_ ";
Object literary name section is prefix with " d_ ".
Type parameter declaration:
" I.D. ": whether checking content is I.D.;
" Email ": whether checking content is Email;
" scope [M-N] ": content is within M and N scope in checking;
" comprise (comprising value) ": whether checking content comprises " comprising value ";
" enumerate value 1, value 2 ... .} ": checking content value value 1, value 2 ... within .}.
For example: validate (s_wordclass, " comprising (near class) "), whether the wordclass field in the table of expression checking source comprises " near class " two words.
As shown in Figure 7, this correlation rule represents correlation rule example, and the table NewWord in new system, from certain legacy system table Word, claims that Word is the source table of NewWord table, and NewWord table is the object table of Word table.Wherein, the Word field in object table is from the Word of source table, and WordClass field is from the WordClass of source table, and WordID field is from the ID of source table.Data rule can also configure more complicated calculated field.
2, obtain incremental update bag from ftp server, as shown in Figure 8, main process is:
1. incremental data import tool sends the message (this message only sends once) of configuration ftp server address to FTP proxy class object,
2. incremental data import tool main classes sends request to FTP proxy class object, judges whether new incremental update bag ftp file, and FTP proxy class can confirm to ftp server,
If 3. there is new incremental update bag ftp file, sends and obtain the request of incremental update APMB package to FTP proxy class, and enter the process of carrying out incremental update bag of resolving.If there is no incremental update bag,, after dormancy a period of time, continue step 2..
3, carry out incremental update bag, as shown in Figure 9, main process is:
1. use JSON converter, convert incremental update APMB package to incremental data entity class object array;
2. use incremental data entity resolver to resolve each incremental data entity class object, realize the incremental update of data.The resolving of incremental data entity is as shown in figure 10: by data correlation rule, we can obtain source and show the relation of corresponding object table table name and each field of object table and source table.Incremental data entity resolver operates according to incremental data entity content, if Operate==D generates a cancel statement, if Operate==I generates one and inserts statement, if Operate=U generates one and upgrades statement.The binding variable value of these statements, from incremental data entity, or the function of incremental data entities field and object literary name section.
Take cancel statement as example, the major parameter of cancel statement is to need the corresponding field name of tables of data table name, Record ID of deleting, the Record ID value of deletion.Need the tables of data table name of deleting, can be obtained by the TableName field in incremental data entity and data correlation rule; The corresponding field name of Record ID, can be obtained by data correlation rule; The Record ID value of deleting, from the id field in incremental data entity.For example: there is incremental update information (see figure 3), the correlation rule (see figure 7) of use, Figure 11 be shown in the corresponding statement generating.
3. preserve the SQL statement generating in database;
4, multithreading is carried out the SQL statement being kept in database.
5, delete incremental update file from ftp server.
incremental data browser module,increment SQL statement is analyzed, shown the incremental data in the new system that needs to carry out.Its demonstration point two-stage is carried out, and first realizes incremental update table, then the incremental update data in indicator gauge.Upgrade as which tables of data Figure 12 has shown; Figure 13 has shown incremental data to be updated in the Chinese tables of data that is called " ITV-sees sheet ".
The foregoing is only the preferred embodiments of the present invention, be not restricted to the present invention, for a person skilled in the art, the present invention can have various modifications and variations.Within the spirit and principles in the present invention all, any modification of doing, be equal to replacement, improvement etc., within all should being included in claim scope of the present invention.
Claims (10)
1. the sector database real time data synchronization system based on File Transfer Protocol, is characterized in that: comprise that incremental data derives tool model, incremental data import tool module, ftp server, incremental data browser module;
Incremental data derives tool model, for reading database increment information, forms incremental data bag, and deposits the ftp server configuring in, and database incremental data extracts " being called for short ETL " mode and adopted trigger mode;
Incremental data import tool module, for obtaining incremental update APMB package from ftp server and carrying out, arrives new system by the synchronous incremental update of data of legacy use that system is managed;
Incremental data browser module, analyzes increment SQL statement, shows the incremental data in the new system that needs to carry out.
2. the sector database real time data synchronization system based on File Transfer Protocol according to claim 1, it is characterized in that: described trigger mode is according to extracting requirement, on the source table that will be extracted, set up and insert, revise, delete 3 triggers, data in the table of source change, just by corresponding trigger, the data of variation are write to an Incremental Log table, the increment extraction of ETL is from Incremental Log table rather than direct extracted data in the table of source, and the data that simultaneously extracted in Incremental Log table will be labeled in time or delete; Incremental Log table is not stored all field informations of incremental data, and just store the key value that source table name claims, upgrades and upgrade action type " knsen, update or delete ", the key value that first ETL increment extraction process claims and upgrade according to source table name, from the table of source, extract corresponding complete documentation, according to upgrading action type, object table is processed accordingly again.
3. the sector database real time data synchronization system based on File Transfer Protocol according to claim 1, is characterized in that: described incremental data import tool module: 1. configuration data correlation rule; 2. obtain incremental update bag from ftp server; 3. carry out incremental update bag; 4. multithreading is carried out the SQL statement being kept in database; 5. delete incremental update file from ftp server; Complete incremental data between legacy system and new system synchronous, realize legacy system and the newly data sharing between system.
4. according to the sector database real time data synchronization system based on File Transfer Protocol described in claim 1 or 3, it is characterized in that: described incremental data import tool module is started by timer.
5. the sector database real time data synchronization system based on File Transfer Protocol according to claim 3, is characterized in that: described data correlation rule configuration file is XML file.
6. the sector database real time data synchronization system based on File Transfer Protocol according to claim 3, is characterized in that: described obtains incremental update bag from ftp server:
1. incremental data import tool sends the message of configuration ftp server address to FTP proxy class object, and this message only sends once;
2. incremental data import tool main classes sends request to FTP proxy class object, judges whether new incremental update bag ftp file, and FTP proxy class can be confirmed to ftp server;
If 3. there is new incremental update bag ftp file, sends and obtain the request of incremental update APMB package to FTP proxy class, and enter the process of carrying out incremental update bag of resolving; If there is no incremental update bag,, after dormancy a period of time, continue step 2.
7. the sector database real time data synchronization system based on File Transfer Protocol according to claim 3, is characterized in that: described execution incremental update bag:
1. use JSON converter, convert incremental update APMB package to incremental data entity class object array;
2. use incremental data entity resolver to resolve each incremental data entity class object, realize the incremental update of data; The parsing of incremental data entity: can obtain source by data correlation rule and show the relation that corresponding object table table name and each field of object table and source are shown; Incremental data entity resolver operates according to incremental data entity content, if Operate==D generates a cancel statement, if Operate==I generates one and inserts statement, if Operate=U generates one and upgrades statement; The binding variable value of these statements, from incremental data entity, or the function of incremental data entities field and object literary name section;
3. preserve the SQL statement generating in database.
8. the sector database real time data synchronization system based on File Transfer Protocol according to claim 1, it is characterized in that: the demonstration point two-stage of described incremental data browser module is carried out, first realize incremental update table, then the incremental update data in indicator gauge.
9. according to the sector database real time data synchronization system based on File Transfer Protocol described in claim 3 or 5, it is characterized in that: described data correlation Rule Expression is: " from the source table of legacy system " is mapped to " the object table of new system "; The field of object table or be certain field of source table, or be the function that some field is shown in source, the function of having realized comprises:
strncat
Function: character string connects;
Form: strncat (parameter 1 ... .)
Parameter type:
Source literary name section is prefix with " s_ ";
Object literary name section is prefix with " d_ ";
Common character string, causes with " ";
replace
Function: character string is replaced;
Form: replace (subject string, searches string, replaces string)
" subject string " " searches string " and " replacing string " parameter type:
Source literary name section is prefix with " s_ ";
Object literary name section is prefix with " d_ ";
Common character string, causes with " ";
validate
Function: character string is replaced;
Form: validate (content, type)
Content parameter declaration:
Source literary name section is prefix with " s_ ";
Object literary name section is prefix with " d_ ";
Type parameter declaration:
" I.D. ": whether checking content is I.D.;
" Email ": whether checking content is Email;
" scope [M-N] ": content is within M and N scope in checking;
" comprise (comprising value) ": whether checking content comprises " comprising value ";
" enumerate value 1, value 2 ... .} ": checking content value value 1, value 2 ... within .}.
10. according to the sector database real time data synchronization system based on File Transfer Protocol described in claim 3 or 5; it is characterized in that: described data correlation Rule Expression is: the table NewWord in new system; from certain legacy system table Word; claim that Word is the source table of NewWord table, NewWord table is the object table of Word table;
Wherein, the Word field in object table is from the Word of source table, and WordClass field is from the WordClass of source table, and WordID field is from the ID of source table.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201210460308.5A CN103823797A (en) | 2012-11-16 | 2012-11-16 | FTP (file transfer protocol) based real-time industry database data synchronization system |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201210460308.5A CN103823797A (en) | 2012-11-16 | 2012-11-16 | FTP (file transfer protocol) based real-time industry database data synchronization system |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN103823797A true CN103823797A (en) | 2014-05-28 |
Family
ID=50758870
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201210460308.5A Pending CN103823797A (en) | 2012-11-16 | 2012-11-16 | FTP (file transfer protocol) based real-time industry database data synchronization system |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN103823797A (en) |
Cited By (35)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN104063301A (en) * | 2014-07-02 | 2014-09-24 | 网神信息技术（北京）股份有限公司 | Monitoring method and device |
| CN105306585A (en) * | 2015-11-12 | 2016-02-03 | 焦点科技股份有限公司 | Data synchronization method for plurality of data centers |
| CN105373621A (en) * | 2015-12-07 | 2016-03-02 | 高新兴科技集团股份有限公司 | Rapid database-system-across data increment migration method |
| CN105528378A (en) * | 2014-10-22 | 2016-04-27 | 阿里巴巴集团控股有限公司 | Service message sending method and device |
| CN105677849A (en) * | 2016-01-06 | 2016-06-15 | 北京京东尚科信息技术有限公司 | Data updating method and device |
| CN105760174A (en) * | 2016-03-09 | 2016-07-13 | 浪潮软件集团有限公司 | Suggested data extraction method based on Windows timed task |
| CN105975502A (en) * | 2016-04-25 | 2016-09-28 | 南京优测信息科技有限公司 | Method for realizing incremental data extract based on CDC (Change Data Capture) mode |
| CN106294732A (en) * | 2016-08-10 | 2017-01-04 | 北京海誉动想科技股份有限公司 | Log collecting method based on ETL instrument |
| CN106708891A (en) * | 2015-11-17 | 2017-05-24 | 中兴通讯股份有限公司 | Network management data synchronizing method and device |
| CN106874389A (en) * | 2017-01-11 | 2017-06-20 | 腾讯科技（深圳）有限公司 | The moving method and device of data |
| CN107562931A (en) * | 2017-09-15 | 2018-01-09 | 新智云数据服务有限公司 | Data pick-up system and data abstracting method |
| CN107766521A (en) * | 2017-10-25 | 2018-03-06 | 中国电力工程顾问集团西南电力设计院有限公司 | A kind of method of Electric Power Network Planning versions of data control |
| CN107784043A (en) * | 2016-08-31 | 2018-03-09 | 北京京东尚科信息技术有限公司 | The monitoring method of data warehouse data table, apparatus and system |
| CN107798039A (en) * | 2017-05-19 | 2018-03-13 | 平安科技（深圳）有限公司 | A kind of method of data synchronization and device |
| CN107995302A (en) * | 2017-12-11 | 2018-05-04 | 北京恒华伟业科技股份有限公司 | A kind of system of data synchronization |
| CN108197171A (en) * | 2017-12-20 | 2018-06-22 | 北京辰森世纪科技股份有限公司 | Report goods allocates method and platform, supply-chain management system |
| CN108322492A (en) * | 2017-01-16 | 2018-07-24 | 医渡云（北京）技术有限公司 | Medical data synchronous method and device |
| CN109040252A (en) * | 2018-08-07 | 2018-12-18 | 平安科技（深圳）有限公司 | Document transmission method, system, computer equipment and storage medium |
| CN109165260A (en) * | 2018-09-25 | 2019-01-08 | 安徽信息工程学院 | Method of data transfer based on ORACLE data basd link |
| WO2019015566A1 (en) * | 2017-07-17 | 2019-01-24 | 中兴通讯股份有限公司 | Processing method and apparatus for planning area increment record, and computer readable storage medium |
| CN109885581A (en) * | 2019-03-14 | 2019-06-14 | 苏州达家迎信息技术有限公司 | Synchronous method, device, equipment and the storage medium of database |
| CN110232091A (en) * | 2017-09-19 | 2019-09-13 | 北京京东尚科信息技术有限公司 | Mthods, systems and devices for synchrodata |
| CN110389988A (en) * | 2019-07-30 | 2019-10-29 | 四川虹魔方网络科技有限公司 | A kind of the user data processing method and system of real-time high-efficiency |
| CN110413672A (en) * | 2019-07-03 | 2019-11-05 | 平安科技（深圳）有限公司 | Data automatically imported method, device and computer readable storage medium |
| CN110502478A (en) * | 2019-08-23 | 2019-11-26 | 成都九洲电子信息系统股份有限公司 | Data interactive method and system between a kind of off-line system |
| CN110659328A (en) * | 2019-08-30 | 2020-01-07 | 中国人民财产保险股份有限公司 | Data query method, device, equipment and computer readable storage medium |
| CN110941615A (en) * | 2019-11-25 | 2020-03-31 | 西安精雕软件科技有限公司 | Distributed hierarchical CNC data acquisition and dump method and system |
| CN111367975A (en) * | 2018-12-25 | 2020-07-03 | 中国移动通信集团浙江有限公司 | Multi-protocol data conversion processing method and device |
| CN111680106A (en) * | 2020-06-17 | 2020-09-18 | 深圳前海微众银行股份有限公司 | Method and device for synchronizing data of multiple application systems |
| CN111782618A (en) * | 2020-06-24 | 2020-10-16 | 中国电子科技集团公司电子科学研究院 | Data synchronization system |
| CN111813845A (en) * | 2020-06-29 | 2020-10-23 | 平安国际智慧城市科技股份有限公司 | ETL task-based incremental data extraction method, device, equipment and medium |
| CN112287180A (en) * | 2020-10-26 | 2021-01-29 | 深圳市友华软件科技有限公司 | Fault machine repair management method based on MAC (media access control) search |
| CN113010609A (en) * | 2020-12-23 | 2021-06-22 | 上海海鼎信息工程股份有限公司 | Differentiated synchronization method and system applied to store operation |
| CN113590533A (en) * | 2021-08-12 | 2021-11-02 | 城云科技（中国）有限公司 | File storage method and device, electronic device and computer program product |
| CN114756628A (en) * | 2022-06-16 | 2022-07-15 | 天津七一二移动通信有限公司 | High-multiplexing extensible data and file synchronization method |
-
2012
- 2012-11-16 CN CN201210460308.5A patent/CN103823797A/en active Pending
Non-Patent Citations (1)
| Title |
|---|
| 戴浩等: ""ETL 中的数据增量抽取机制研究"", 《ETL 中的数据增量抽取机制研究》 * |
Cited By (55)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN104063301A (en) * | 2014-07-02 | 2014-09-24 | 网神信息技术（北京）股份有限公司 | Monitoring method and device |
| CN110532242B (en) * | 2014-10-22 | 2023-04-25 | 创新先进技术有限公司 | Service message sending method and device |
| CN105528378A (en) * | 2014-10-22 | 2016-04-27 | 阿里巴巴集团控股有限公司 | Service message sending method and device |
| CN105528378B (en) * | 2014-10-22 | 2019-10-15 | 阿里巴巴集团控股有限公司 | Service message sending method and device |
| CN110532242A (en) * | 2014-10-22 | 2019-12-03 | 阿里巴巴集团控股有限公司 | Service message sending method and device |
| CN105306585A (en) * | 2015-11-12 | 2016-02-03 | 焦点科技股份有限公司 | Data synchronization method for plurality of data centers |
| CN105306585B (en) * | 2015-11-12 | 2019-02-05 | 焦点科技股份有限公司 | A kind of method of data synchronization of multiple data centers |
| CN106708891A (en) * | 2015-11-17 | 2017-05-24 | 中兴通讯股份有限公司 | Network management data synchronizing method and device |
| WO2017084410A1 (en) * | 2015-11-17 | 2017-05-26 | 中兴通讯股份有限公司 | Network management data synchronization method and apparatus |
| CN105373621A (en) * | 2015-12-07 | 2016-03-02 | 高新兴科技集团股份有限公司 | Rapid database-system-across data increment migration method |
| CN105677849A (en) * | 2016-01-06 | 2016-06-15 | 北京京东尚科信息技术有限公司 | Data updating method and device |
| CN105760174A (en) * | 2016-03-09 | 2016-07-13 | 浪潮软件集团有限公司 | Suggested data extraction method based on Windows timed task |
| CN105975502A (en) * | 2016-04-25 | 2016-09-28 | 南京优测信息科技有限公司 | Method for realizing incremental data extract based on CDC (Change Data Capture) mode |
| CN106294732A (en) * | 2016-08-10 | 2017-01-04 | 北京海誉动想科技股份有限公司 | Log collecting method based on ETL instrument |
| CN107784043A (en) * | 2016-08-31 | 2018-03-09 | 北京京东尚科信息技术有限公司 | The monitoring method of data warehouse data table, apparatus and system |
| CN107784043B (en) * | 2016-08-31 | 2022-04-12 | 北京京东尚科信息技术有限公司 | Monitoring method, device and system for data table of data warehouse |
| CN106874389B (en) * | 2017-01-11 | 2023-04-07 | 腾讯科技（深圳）有限公司 | Data migration method and device |
| CN106874389A (en) * | 2017-01-11 | 2017-06-20 | 腾讯科技（深圳）有限公司 | The moving method and device of data |
| CN108322492A (en) * | 2017-01-16 | 2018-07-24 | 医渡云（北京）技术有限公司 | Medical data synchronous method and device |
| CN108322492B (en) * | 2017-01-16 | 2021-09-17 | 医渡云（北京）技术有限公司 | Medical data synchronization method and device |
| CN107798039A (en) * | 2017-05-19 | 2018-03-13 | 平安科技（深圳）有限公司 | A kind of method of data synchronization and device |
| WO2018210080A1 (en) * | 2017-05-19 | 2018-11-22 | 平安科技（深圳）有限公司 | Data synchronisation method, apparatus, device, and computer readable storage medium |
| WO2019015566A1 (en) * | 2017-07-17 | 2019-01-24 | 中兴通讯股份有限公司 | Processing method and apparatus for planning area increment record, and computer readable storage medium |
| CN107562931A (en) * | 2017-09-15 | 2018-01-09 | 新智云数据服务有限公司 | Data pick-up system and data abstracting method |
| CN110232091B (en) * | 2017-09-19 | 2021-10-15 | 北京京东尚科信息技术有限公司 | Method, system and apparatus for synchronizing data |
| CN110232091A (en) * | 2017-09-19 | 2019-09-13 | 北京京东尚科信息技术有限公司 | Mthods, systems and devices for synchrodata |
| CN107766521A (en) * | 2017-10-25 | 2018-03-06 | 中国电力工程顾问集团西南电力设计院有限公司 | A kind of method of Electric Power Network Planning versions of data control |
| CN107766521B (en) * | 2017-10-25 | 2021-12-14 | 中国电力工程顾问集团西南电力设计院有限公司 | Power grid planning data version control method |
| CN107995302A (en) * | 2017-12-11 | 2018-05-04 | 北京恒华伟业科技股份有限公司 | A kind of system of data synchronization |
| CN108197171A (en) * | 2017-12-20 | 2018-06-22 | 北京辰森世纪科技股份有限公司 | Report goods allocates method and platform, supply-chain management system |
| CN109040252A (en) * | 2018-08-07 | 2018-12-18 | 平安科技（深圳）有限公司 | Document transmission method, system, computer equipment and storage medium |
| CN109165260A (en) * | 2018-09-25 | 2019-01-08 | 安徽信息工程学院 | Method of data transfer based on ORACLE data basd link |
| CN111367975B (en) * | 2018-12-25 | 2023-11-03 | 中国移动通信集团浙江有限公司 | Multi-protocol data conversion processing method and device |
| CN111367975A (en) * | 2018-12-25 | 2020-07-03 | 中国移动通信集团浙江有限公司 | Multi-protocol data conversion processing method and device |
| CN109885581B (en) * | 2019-03-14 | 2021-09-17 | 苏州达家迎信息技术有限公司 | Database synchronization method, device, equipment and storage medium |
| CN109885581A (en) * | 2019-03-14 | 2019-06-14 | 苏州达家迎信息技术有限公司 | Synchronous method, device, equipment and the storage medium of database |
| CN110413672A (en) * | 2019-07-03 | 2019-11-05 | 平安科技（深圳）有限公司 | Data automatically imported method, device and computer readable storage medium |
| CN110413672B (en) * | 2019-07-03 | 2023-09-19 | 平安科技（深圳）有限公司 | Automatic data importing method and device and computer readable storage medium |
| CN110389988A (en) * | 2019-07-30 | 2019-10-29 | 四川虹魔方网络科技有限公司 | A kind of the user data processing method and system of real-time high-efficiency |
| CN110502478A (en) * | 2019-08-23 | 2019-11-26 | 成都九洲电子信息系统股份有限公司 | Data interactive method and system between a kind of off-line system |
| CN110502478B (en) * | 2019-08-23 | 2023-05-23 | 成都九洲电子信息系统股份有限公司 | Data interaction method and system between offline systems |
| CN110659328B (en) * | 2019-08-30 | 2022-04-29 | 中国人民财产保险股份有限公司 | Data query method, device, equipment and computer readable storage medium |
| CN110659328A (en) * | 2019-08-30 | 2020-01-07 | 中国人民财产保险股份有限公司 | Data query method, device, equipment and computer readable storage medium |
| CN110941615A (en) * | 2019-11-25 | 2020-03-31 | 西安精雕软件科技有限公司 | Distributed hierarchical CNC data acquisition and dump method and system |
| CN111680106A (en) * | 2020-06-17 | 2020-09-18 | 深圳前海微众银行股份有限公司 | Method and device for synchronizing data of multiple application systems |
| CN111680106B (en) * | 2020-06-17 | 2024-04-09 | 深圳前海微众银行股份有限公司 | Method and device for synchronizing data of multiple application systems |
| CN111782618A (en) * | 2020-06-24 | 2020-10-16 | 中国电子科技集团公司电子科学研究院 | Data synchronization system |
| CN111782618B (en) * | 2020-06-24 | 2023-01-31 | 中国电子科技集团公司电子科学研究院 | Data synchronization system |
| CN111813845A (en) * | 2020-06-29 | 2020-10-23 | 平安国际智慧城市科技股份有限公司 | ETL task-based incremental data extraction method, device, equipment and medium |
| CN112287180A (en) * | 2020-10-26 | 2021-01-29 | 深圳市友华软件科技有限公司 | Fault machine repair management method based on MAC (media access control) search |
| CN113010609B (en) * | 2020-12-23 | 2023-05-16 | 上海海鼎信息工程股份有限公司 | Differentiated synchronization method and system applied to store operation |
| CN113010609A (en) * | 2020-12-23 | 2021-06-22 | 上海海鼎信息工程股份有限公司 | Differentiated synchronization method and system applied to store operation |
| CN113590533A (en) * | 2021-08-12 | 2021-11-02 | 城云科技（中国）有限公司 | File storage method and device, electronic device and computer program product |
| CN114756628B (en) * | 2022-06-16 | 2022-10-28 | 天津七一二移动通信有限公司 | High-multiplexing extensible data and file synchronization method |
| CN114756628A (en) * | 2022-06-16 | 2022-07-15 | 天津七一二移动通信有限公司 | High-multiplexing extensible data and file synchronization method |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN103823797A (en) | FTP (file transfer protocol) based real-time industry database data synchronization system | |
| US9519700B2 (en) | Automatically synchronizing production data of a source system and a target system | |
| US11494688B2 (en) | Learning ETL rules by example | |
| US10579634B2 (en) | Apparatus and method for operating a distributed database with foreign tables | |
| US8645434B2 (en) | Techniques for schema production and transformation | |
| EP2672397B1 (en) | Generating reports based on materialized view | |
| CN104899295B (en) | A kind of heterogeneous data source data relation analysis method | |
| US20150046389A1 (en) | Post-migration validation of etl jobs and exception management | |
| US20060259442A1 (en) | System method and program product to estimate cost of integrating and utilizing heterogeneous data sources | |
| US11321315B2 (en) | Methods and systems for database optimization | |
| CN114116716A (en) | Hierarchical data retrieval method, device and equipment | |
| US8626729B2 (en) | Database index monitoring system | |
| CN103778133A (en) | Database object changing method and device | |
| AU2017254893A1 (en) | Adapting database queries for data virtualization over combined database stores | |
| CN107423452A (en) | A kind of power network heterogeneous database synchronously replicates moving method | |
| CN114218218A (en) | Data processing method, device and equipment based on data warehouse and storage medium | |
| US6915313B2 (en) | Deploying predefined data warehouse process models | |
| CN113420026A (en) | Database table structure changing method, device, equipment and storage medium | |
| CN114691704A (en) | Metadata synchronization method based on MySQL binlog | |
| US8938445B2 (en) | System and method for rowset inserts | |
| CN109446262A (en) | A kind of data assemblage method and device | |
| CN109829003A (en) | Database backup method and device | |
| US7016906B1 (en) | Data processing method and apparatus employing OLE DB and having dual schema and auto update features | |
| CN102521408B (en) | Method for accessing flat file by using java database connectivity (JDBC) interface | |
| CN113556344A (en) | General index monitoring billboard based on organizational performance scene |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| WD01 | Invention patent application deemed withdrawn after publication |
Application publication date: 20140528 |
|
| WD01 | Invention patent application deemed withdrawn after publication |