CN102411569A - Database conversion and cleaning information processing method - Google Patents

Abstract

The invention relates to a database conversion and cleaning information processing method, which comprises the following steps of: 1) connecting a target database to a data source; 2) selecting a target data table to be cleaned in the target database; 3) selecting an update mode, executing the fourth step if the incremental update is adopted, and executing the tenth step if the total update is adopted; 4) obtaining the maximum update time last_update in the target data table, and defaulting the last_update as the set time if the target data table is null; 5) screening all records with the update time greater than the last_update in the data source to a temporary table temp_table; 6) deleting repeated records in the temp_table by restraining fields in the target data table; 7) comparing the target data table and the temp_table and obtaining the records of the temp_table in the target data table, and the like. Compared with the prior art, the method has the advantages that the problems of data repetitiveness and omission in the data cleaning process are effectively avoided, the data consistency and the completeness are ensured, and the like.

Description

A kind of database conversion and cleaning information disposal route

Technical field

The present invention relates to a kind of database correlation technique, especially relate to a kind of database conversion and cleaning information disposal route.

Background technology

ETL is also claimed in the cleaning of data and conversion (Extract, Transform, Load), is the problem that often need solve in database field, especially data warehouse field.ETL cleans after being responsible for data that distribute, in the heterogeneous data source such as relation data, flat data file etc. are drawn into interim middle layer, conversion, integrated; Be loaded at last in the object library (data warehouse, Data Mart etc.), become the basis of on-line analytical processing, data mining.

Though the professional tool about data cleansing has much in the market; Like the Datastage of Ascential company, the Powercenter of Informatica company, the ETL Automation of NCR Teradata company etc.; These instruments are mostly powerful, but it uses also comparatively complicated simultaneously.But as general middle-size and small-size application, use these professional tool costs too high, generally can then seek some comparatively instruments of lightweight, like SSIS or directly use storing process programming realization.

Summary of the invention

The object of the invention is exactly for the defective that overcomes above-mentioned prior art existence a kind of database conversion and cleaning information disposal route to be provided.

The object of the invention can be realized through following technical scheme:

A kind of database conversion and cleaning information disposal route is characterized in that, may further comprise the steps:

1) target database is connected to data source;

The target matrix that 2) need clean in the select target database;

3) select update mode, if incremental update, then execution in step 4); If full dose is upgraded, then execution in step 10);

4) obtain last_update update time maximum in the target matrix, if target matrix is empty, then last_update is defaulted as setting-up time;

5) be recorded in a temporary table temp_table greater than all of last_update update time in the garbled data source;

6) adopt the bind field in the target matrix to reject the duplicate record among the temporary table temp_table;

7) compare through target matrix and temporary table temp_table, obtain being present among the temporary table temp_table record in the target matrix;

8) be present in the record in the target matrix among the rejecting temporary table temp_table;

9) with remaining whole records among the temporary table temp_table, insert in the target matrix, and execution in step 14);

10) with the data of data source one end, be organized as the target data list structure form, and with whole recorded and stored to temporary table temp_table;

11) adopt the bind field in the target matrix to reject the duplicate record among the temporary table temp_table;

12) empty target matrix;

13) the whole records among the temporary table temp_table are inserted in the target matrix.

14) record upgrades daily record.

Setting-up time in the described step 4) can be on January 1st, 1900.

Described step 6) bind field is one or more.

Described step 11) bind field is one or more.

Compared with prior art, the present invention has the following advantages:

1, specializes the flow process of data-switching and cleaning, can effectively accomplish the Data Update of full dose and two kinds of update modes of increment;

2, use service logic clearly, can effectively avoid the data in the data cleansing process to repeat and the omission problem, guarantee the consistance and the integrality of data.

Description of drawings

Fig. 1 is a process flow diagram of the present invention;

Fig. 2 is a hardware configuration synoptic diagram of the present invention.

Embodiment

Below in conjunction with accompanying drawing and specific embodiment the present invention is elaborated.

Embodiment

Like Fig. 1, shown in Figure 2, a kind of database conversion and cleaning information disposal route may further comprise the steps:

1) target database 1 is connected to data source 2;

The target matrix that 2) need clean in the select target database 1;

3) select update mode, if incremental update, then execution in step 4); If full dose is upgraded, then execution in step 10);

4) obtain last_update update time maximum in the target matrix, if target matrix is empty, then last_update is defaulted as setting-up time;

5) be recorded in a temporary table temp_table greater than all of last_update update time in the garbled data source 2;

6) adopt the bind field in the target matrix to reject the duplicate record among the temporary table temp_table;

7) compare through target matrix and temporary table temp_table, obtain being present among the temporary table temp_table record in the target matrix;

8) be present in the record in the target matrix among the rejecting temporary table temp_table;

9) with remaining whole records among the temporary table temp_table, insert in the target matrix, and execution in step 14);

10) with the data of data source one end, be organized as the target data list structure form, and with whole recorded and stored to temporary table temp_table;

11) adopt the bind field in the target matrix to reject the duplicate record among the temporary table temp_table;

12) empty target matrix;

13) the whole records among the temporary table temp_table are inserted in the target matrix.

14) record upgrades daily record.

Setting-up time in the described step 4) can be on January 1st, 1900.

Described step 6) bind field is one or more.

Described step 11) bind field is one or more.

Claims (4)

1. a database is changed and the cleaning information disposal route, it is characterized in that, may further comprise the steps:

1) target database is connected to data source;

The target matrix that 2) need clean in the select target database;

3) select update mode, if incremental update, then execution in step 4); If full dose is upgraded, then execution in step 10);

4) obtain last_update update time maximum in the target matrix, if target matrix is empty, then last_update is defaulted as setting-up time;

5) be recorded in a temporary table temp_table greater than all of last_update update time in the garbled data source;

6) adopt the bind field in the target matrix to reject the duplicate record among the temporary table temp_table;

7) compare through target matrix and temporary table temp_table, obtain being present among the temporary table temp_table record in the target matrix;

8) be present in the record in the target matrix among the rejecting temporary table temp_table;

9) with remaining whole records among the temporary table temp_table, insert in the target matrix, and execution in step 14);

10) with the data of data source one end, be organized as the target data list structure form, and with whole recorded and stored to temporary table temp_table;

11) adopt the bind field in the target matrix to reject the duplicate record among the temporary table temp_table;

12) empty target matrix;

13) the whole records among the temporary table temp_table are inserted in the target matrix.

14) record upgrades daily record.

2. a kind of database conversion according to claim 1 and cleaning information disposal route is characterized in that the setting-up time in the described step 4) can be on January 1st, 1900.

3. a kind of database conversion according to claim 1 and cleaning information disposal route is characterized in that described step 6) bind field is one or more.

4. a kind of database conversion according to claim 1 and cleaning information disposal route is characterized in that described step 11) bind field is one or more.

Images