CN104216987A - Timestamp-based method for capturing incremental data and supporting delete operation - Google Patents
Timestamp-based method for capturing incremental data and supporting delete operation Download PDFInfo
- Publication number
- CN104216987A CN104216987A CN201410447926.5A CN201410447926A CN104216987A CN 104216987 A CN104216987 A CN 104216987A CN 201410447926 A CN201410447926 A CN 201410447926A CN 104216987 A CN104216987 A CN 104216987A
- Authority
- CN
- China
- Prior art keywords
- data
- time
- timestamp
- capture
- increment
- 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
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/21—Design, administration or maintenance of databases
- G06F16/215—Improving data quality; Data cleansing, e.g. de-duplication, removing invalid entries or correcting typographical errors
Landscapes
- Engineering & Computer Science (AREA)
- Databases & Information Systems (AREA)
- Theoretical Computer Science (AREA)
- Quality & Reliability (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention relates to a timestamp-based method for capturing incremental data and supporting delete operation. The method comprises the steps of mainly identifying the changed data according to the timestamp information in a data table, so as to capture the incremental data. The method aims to solve the problem of extracting the incremental data in the data exchange field, especially solves the scene of physical delete in the data table, so as to achieve the purpose of rapidly, efficiently and exactly capturing the incremental data.
Description
Technical field
The present invention relates to field, a kind of database airport, particularly relate to and a kind ofly support the increment data capturing method of deletion action based on timestamp.
Background technology
In data integration field, for promoting data-handling efficiency, increment data capturing has become wherein indispensable gordian technique.The increment acquisition mode that current industry is relatively commonly used mainly contains the mode based on timestamp, the mode based on database trigger, the mode based on database journal, snapshot table alignments.Wherein, timestamp mode, because of, acquisition performance less on the impact of the service process performance of origin system better, to realize cost little, is subject to the high praise of industry.
But, because traditional timestamp mode cannot catch the data of being deleted by physics, cause this technology to promote the use of.
Summary of the invention
The object of this invention is to provide and a kind ofly support the increment data capturing method of deletion action based on timestamp.
The object of the invention is to realize in the following manner, comprise 1) configuration is based on the increment data capturing rule of timestamp; 2) incrementally capture rule catches incremental data; Wherein:
1) configuration comprises the following steps based on the increment data capturing rule of timestamp:
(1) in source data table, " creation-time stamp " and " last modification time stamp " field is configured;
(2) the deletion record table that source of configuration tables of data is corresponding;
(3) configure increment capture rule, comprise structure that increment catches, timestamp mark and the configuration of deletion record table;
2) incrementally capture rule catches incremental data, for realizing catching based on the increment of timestamp, comprises the following steps:
(1) value of the creation-time stamp field in reference source tables of data and capture time last time, the data that creation-time stamp is greater than capture time last time are the data increasing Insert and operate;
(2) compare value and capture time last time of the erasing time stamp field in deletion record table, the data that erasing time stamp is greater than capture time last time are the data of deleting Delete and operating;
(3) value of the last modification time stamp field in reference source tables of data and capture time last time, last modification time stabs the data being greater than capture time last time and is the data that amendment (Update) operates.
Excellent effect of the present invention: be the mode by configuration deletion record table, supports and is caught by the increment of physics deletion data.
Accompanying drawing explanation
Fig. 1 is the schematic diagram of the data structure of deletion record table;
Fig. 2 is the schematic diagram of the data list structure of the configuration relation of source table and deletion record table.
Embodiment
A kind of support that the increment data capturing method of deletion action is described in detail below based on timestamp with reference to Figure of description to of the present invention.
In order to solve the problem, the invention discloses and a kind ofly support the increment data capturing method of deletion action based on timestamp, it is characterized in that comprising following three steps:
S10: configure the increment data capturing rule based on timestamp;
S11: incrementally capture rule catches incremental data.
In step slo, the increment data capturing rule based on timestamp is configured.Further illustrate, comprise the following steps:
S100: configure " creation-time stamp " and " last modification time stamp " field in source data table, and require that field type is time type, precision is accurate to millisecond;
S101: the deletion record table that source of configuration tables of data is corresponding, multiple source data table is allowed to share a deletion record table, at least will record Major key and the erasing time of deleted data in deletion record table, require that field type is time type, precision is accurate to millisecond simultaneously; When carrying out physics to data and deleting, deleted data will be recorded in deletion record table.
S102: configuration increment capture rule, comprises structure that increment catches, timestamp mark and the configuration of deletion record table.
In step s 11, incrementally capture rule catches incremental data, and it comprises the following steps:
S110: the data of catching increase (Insert) action type.Furtherly, the value of creation-time in reference source tables of data stamp field and capture time last time, creation-time stabs the data being greater than capture time last time and is the data that increase (Insert) operates;
S111: the data of catching deletion (Delete) action type.Furtherly, compare value and capture time last time of the erasing time stamp field in deletion record table, erasing time stabs the data being greater than capture time last time and is the data that deletion (Delete) operates;
S112: the data of catching amendment (Update) action type.Furtherly, the value of the last modification time stamp field in reference source tables of data and capture time last time, last modification time stabs the data being greater than capture time last time and is the data that amendment (Update) operates.
Embodiment
For making the intent of the present invention, feature and advantage more easy-to-understand, below in conjunction with the drawings and specific embodiments, the present invention is further detailed explanation.
Embodiment illustrates: obtain the incremental data in HREmployee tables of data in certain HR system, wherein the major key field of HREmployee is ID.List structure is as follows:
| Field | Field description | Data type | Explanation |
| ID | ID | Character | Major key |
| Code | Numbering | Character | Do not allow for sky |
| Name | Name | Character | Do not allow for sky |
| Company | Company | Character | ? |
| Dept | Department | Character | ? |
| Post | Post | Character | ? |
First configure the timestamp structure of HREmployee, and define increment capture rule.Further illustrate, comprise the steps:
1. in tables of data HREmployee, field is increased: CreatedTime, LastModifiedTime, as follows:
| Field | Field description | Data type | Explanation |
| ID | ID | Character | Major key |
| Code | Numbering | Character | Do not allow for sky |
| Name | Name | Character | Do not allow for sky |
| Company | Company | Character | ? |
| Dept | Department | Character | ? |
| Post | Post | Character | ? |
| CreatedTime | Creation-time | Timestamp | Be accurate to millisecond |
| LastModifiedTime | Last modification time | Timestamp | Be accurate to millisecond |
2. the deletion record table that configuration data table HREmployee is corresponding, configuration relation is as follows:
| Source data table (SrcTableName) | Source table major key (SrcPKCol) | Deletion record table (DelTableName) | Deletion record table association major key (AssSrcPKCol) | Erasing time stabs |
| HREmployee | ID | HREmployeeDELDATA | AssSrcID | DeletedTime |
When performing deletion action to HREmployee, deleted data will be recorded in the deletion record table (HREmployeeDELDATA) of its correspondence.
3. configure increment capture rule, in capture rule, need fixed time stamp field (increase, delete, change) respectively
Then, perform increment data capturing according to the rule of timestamp, further illustrate, comprise following steps:
1. the data of newly-increased operation are caught according to the value of CreatedTime field in tables of data HREmployee;
2. the data of deletion action are caught according to the value of the DeletedTime field in deletion record table (SrcPKCol);
3. the data of operation retouching operation are caught according to the value of LastModifiedTime field in tables of data HREmployee.
A kind ofly the increment data capturing method of deletion action is supported, the detailed description of carrying out based on timestamp above to provided by the present invention.Application scenarios of the present invention, mainly for the tables of data of free stamp field, and deletion action is the scene of the data in tables of data being carried out to physics deletion.
Except the technical characteristic described in instructions, be the known technology of those skilled in the art.
Claims (1)
1. support the increment data capturing method of deletion action based on timestamp pattern, it is characterized in that, comprise 1) configuration is based on the increment data capturing rule of timestamp; 2) incrementally capture rule catches incremental data; Wherein:
1) configuration comprises the following steps based on the increment data capturing rule of timestamp:
(1) in source data table, " creation-time stamp " and " last modification time stamp " field is configured;
(2) the deletion record table that source of configuration tables of data is corresponding;
(3) configure increment capture rule, comprise structure that increment catches, timestamp mark and the configuration of deletion record table;
2) incrementally capture rule catches incremental data, for realizing catching based on the increment of timestamp, comprises the following steps:
(1) value of the creation-time stamp field in reference source tables of data and capture time last time, the data that creation-time stamp is greater than capture time last time are the data increasing Insert and operate;
(2) compare value and capture time last time of the erasing time stamp field in deletion record table, the data that erasing time stamp is greater than capture time last time are the data of deleting Delete and operating;
(3) value of the last modification time stamp field in reference source tables of data and capture time last time, last modification time stabs the data being greater than capture time last time and is the data that amendment (Update) operates.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201410447926.5A CN104216987A (en) | 2014-09-04 | 2014-09-04 | Timestamp-based method for capturing incremental data and supporting delete operation |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201410447926.5A CN104216987A (en) | 2014-09-04 | 2014-09-04 | Timestamp-based method for capturing incremental data and supporting delete operation |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN104216987A true CN104216987A (en) | 2014-12-17 |
Family
ID=52098477
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201410447926.5A Pending CN104216987A (en) | 2014-09-04 | 2014-09-04 | Timestamp-based method for capturing incremental data and supporting delete operation |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN104216987A (en) |
Cited By (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN105488187A (en) * | 2015-12-02 | 2016-04-13 | 北京四达时代软件技术股份有限公司 | Method and device for extracting multi-source heterogeneous data increment |
| CN105912666A (en) * | 2016-04-12 | 2016-08-31 | 中国科学院软件研究所 | Method for high-performance storage and inquiry of hybrid structure data aiming at cloud platform |
| CN106484798A (en) * | 2016-09-22 | 2017-03-08 | 济南浪潮高新科技投资发展有限公司 | A kind of business paper increment catching method of hot swap type |
| CN106649552A (en) * | 2016-11-07 | 2017-05-10 | 湖北省农村信用社联合社网络信息中心 | Normalized data cleaning method |
| CN107967361A (en) * | 2017-12-22 | 2018-04-27 | 北京明朝万达科技股份有限公司 | A kind of incremental processing method and system of database |
| CN109213817A (en) * | 2018-08-10 | 2019-01-15 | 杭州数梦工场科技有限公司 | Incremental data abstracting method, device and server |
| CN111881091A (en) * | 2020-06-08 | 2020-11-03 | 微梦创科网络科技（中国）有限公司 | Data storage method and device, electronic equipment and storage medium |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102193938A (en) * | 2010-03-01 | 2011-09-21 | 深圳市金蝶中间件有限公司 | Method and device for acquiring incremental data |
| CN102521225A (en) * | 2011-09-29 | 2012-06-27 | 用友软件股份有限公司 | Incremental data extraction device and incremental data extraction method |
| CN102915336A (en) * | 2012-09-18 | 2013-02-06 | 北京金和软件股份有限公司 | Incremental data capturing and extraction method based on timestamps and logs |
| CN103226466A (en) * | 2013-04-26 | 2013-07-31 | 浪潮集团山东通用软件有限公司 | Efficient incremental data capturing method |
-
2014
- 2014-09-04 CN CN201410447926.5A patent/CN104216987A/en active Pending
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102193938A (en) * | 2010-03-01 | 2011-09-21 | 深圳市金蝶中间件有限公司 | Method and device for acquiring incremental data |
| CN102521225A (en) * | 2011-09-29 | 2012-06-27 | 用友软件股份有限公司 | Incremental data extraction device and incremental data extraction method |
| CN102915336A (en) * | 2012-09-18 | 2013-02-06 | 北京金和软件股份有限公司 | Incremental data capturing and extraction method based on timestamps and logs |
| CN103226466A (en) * | 2013-04-26 | 2013-07-31 | 浪潮集团山东通用软件有限公司 | Efficient incremental data capturing method |
Cited By (8)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN105488187A (en) * | 2015-12-02 | 2016-04-13 | 北京四达时代软件技术股份有限公司 | Method and device for extracting multi-source heterogeneous data increment |
| CN105912666A (en) * | 2016-04-12 | 2016-08-31 | 中国科学院软件研究所 | Method for high-performance storage and inquiry of hybrid structure data aiming at cloud platform |
| CN105912666B (en) * | 2016-04-12 | 2019-06-25 | 中国科学院软件研究所 | A kind of mixed structure data high-performance storage of facing cloud platform, querying method |
| CN106484798A (en) * | 2016-09-22 | 2017-03-08 | 济南浪潮高新科技投资发展有限公司 | A kind of business paper increment catching method of hot swap type |
| CN106649552A (en) * | 2016-11-07 | 2017-05-10 | 湖北省农村信用社联合社网络信息中心 | Normalized data cleaning method |
| CN107967361A (en) * | 2017-12-22 | 2018-04-27 | 北京明朝万达科技股份有限公司 | A kind of incremental processing method and system of database |
| CN109213817A (en) * | 2018-08-10 | 2019-01-15 | 杭州数梦工场科技有限公司 | Incremental data abstracting method, device and server |
| CN111881091A (en) * | 2020-06-08 | 2020-11-03 | 微梦创科网络科技（中国）有限公司 | Data storage method and device, electronic equipment and storage medium |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN104216987A (en) | Timestamp-based method for capturing incremental data and supporting delete operation | |
| CN105138635A (en) | Method for performing data increment copying through hash value comparison | |
| CN104090889A (en) | Method and system for data processing | |
| CN104331285A (en) | Automatic code generation method and system | |
| CN106599104A (en) | Mass data association method based on redis cluster | |
| CN102521225A (en) | Incremental data extraction device and incremental data extraction method | |
| CN106126601A (en) | A kind of social security distributed preprocess method of big data and system | |
| CN102737108B (en) | Method and device for processing flow diagram | |
| EP2897401B1 (en) | Method and device for guaranteeing consistency of planning data | |
| CN104850611A (en) | Method for updating data from relational database to non-relational database in timed increment mode by using KETTLE | |
| CN104834700A (en) | Method for capturing movement data increment based on track change | |
| CN105900093A (en) | Keyvalue database data table updating method and data table updating device | |
| CN105095330A (en) | Method and system for identifying file format based on compressed package content | |
| CN105260464A (en) | Data storage structure conversion method and apparatus | |
| CN111737355A (en) | MongoDB metadata management-based heterogeneous data source synchronization method and system | |
| CN104021219A (en) | Method and device for generating data template | |
| CN104636401A (en) | Data rollback method and device for SCADA system | |
| CN104239470A (en) | Distributed environment-oriented space data compound processing system and method | |
| CN105446824B (en) | Table increment acquisition methods and long-distance data backup method | |
| CN104461929A (en) | Distributed type data caching method based on interceptor | |
| CN105354110B (en) | Cloud Server data back up method and device | |
| CN106383914B (en) | Method and system for realizing configuration of multiple data sources based on cloud call platform | |
| CN103226466A (en) | Efficient incremental data capturing method | |
| CN104933119A (en) | Big data management method | |
| CN104504779A (en) | Travelling data recorder analysis method and device |
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 | ||
| WD01 | Invention patent application deemed withdrawn after publication |
Application publication date: 20141217 |