Several ways to get database incremental data

Several ways to get database incremental data

A. Trigger: To create the required trigger on the table to be extracted, generally to establish insert, modify, delete three triggers, whenever the data in the source table changes, the corresponding trigger will change the data into a temporary table, the extraction thread from the temporary table to extract data, temporary table in the extracted data is marked or deleted. The advantage of the trigger method is that the performance of the data pump is high, the disadvantage is that it requires the business table to set up the trigger and has some influence on the business system.

B. Timestamp: It is a method of capturing the change data based on snapshot comparison, adding a timestamp field to the source table, and modifying the value of the timestamp field while updating the table data in the system. When data extraction is performed, the data that is extracted is determined by comparing the values of the system time and timestamp fields. Some database timestamps support Automatic Updates, that is, when the data for other fields in the table changes, the value of the timestamp field is automatically updated. Some databases do not support automatic timestamp updating, which requires the business system to update the timestamp field manually when updating the business data. As with the trigger mode, the time stamp mode performance is also better, the data extraction is relatively clear and simple, but the business system also has a large pour (add additional timestamp field), especially for the automatic update of the database does not support the timestamp, also requires the business system to perform additional update timestamp operations.  　　　 In addition, delete and update operations on previous data of timestamps cannot be captured and are subject to certain limitations in data accuracy. C. Full-scale comparison: A typical full-table alignment is the use of the MD5 check code. The ETL tool establishes a similar MD5 temporary table in advance for the table to be extracted, which records the primary key of the source table and the MD5 checksum computed from the data for all fields. Each time the data is pumped, the source table and the MD5 temporary table are compared to the MD5 checksum, which determines whether the data in the source table is new, modified, or deleted, and the MD5 checksum is updated. The advantage of the MD5 method is that it is less inclined to the source system (only need to set up a MD5 temporary table), but the disadvantage is obvious, unlike the active notification in the trigger and timestamp mode, the MD5 mode is passive for the whole table data, and the performance is poor. The MD5 method is less accurate when there are no primary keys or unique columns in the table and duplicate records are included.

D. Log comparison: The data is judged by analyzing the log of the database itself.

Several ways to get database incremental data