Data checking, synchronizing and cleaning method and device

Abstract

The invention provides a data checking, synchronizing and cleaning method and device, and the method comprises the steps: obtaining a data source and the information of each data table, and carrying out the data checking according to the information, and generating a data source checking report; according to the data source inventory report, creating or correcting a corresponding source layer data table and a data table for storing cleaning data in a database, and obtaining a field corresponding relationship between the source layer data table and the data table for storing the cleaning data; determining a data synchronization strategy according to the increment change characteristics of the data table, and synchronizing data in the data source into the source-attached layer data table according to the data synchronization strategy; generating a data cleaning code according to a field corresponding relation between the source-attached layer data table and the data table storing the cleaning data and a data cleaning rule; and performing data cleaning on the data in the source layer data table according to the data cleaning code. In this way, intelligent automatic processing of data checking, data table creation and correction, data synchronization and data cleaning is achieved.

Description

Technical Field

[0001] The present disclosure relates to the technical field of data analysis and management, and in particular to a method and device for data inventory, synchronization and cleaning. Background Art

[0002] First, the current data inventory work is usually done by staff using data table exploration to manually analyze and inventory the data source, obtain the metadata of the data table, the relationship between the data tables, analyze the incremental changes of the data stored in the data table, analyze the quality of the data stored in the data table, etc., which requires a lot of manpower. In addition, when manually inventorying the data source, the results of the inventory will vary due to different personal work experience and work seriousness. Some inventory results even have many problems. At the same time, because the data source itself is not static, with the development of the business, the data table structure of some data sources will change, and the logical relationship of the stored data will change, resulting in the original inventory result information being no longer accurate. If the inventory is taken again, it will cost a lot of manpower.

[0003] Secondly, although there are a large number of data synchronization tools to assist in data integration, it is still necessary to manually build the corresponding source layer data tables in the data warehouse and configure the data tables, fields and other information in the synchronization tools. Each source table needs to be configured, which is labor-intensive and error-prone.

[0004] Finally, since the data synchronized from the data source to the source layer comes from different sources and has varying data quality, data cleaning is required. Currently, responsible personnel are usually required to analyze the data, identify problems in the data, and then write program scripts or make relevant configurations in the tools to clean the data. This requires a lot of manpower to complete, and various errors are inevitable during manual processing, resulting in repeated work or erroneous results. Summary of the invention

[0005] The present disclosure provides a method and device for data inventory, synchronization and cleaning. According to a first aspect of the present disclosure, a method for data inventory, synchronization and cleaning is provided, the method comprising:

[0006] Obtaining the data source and its data table information, and performing data inventory based on the information to generate a data source inventory report, wherein the data source inventory report includes data table metadata information, data field characteristics, data table association relationships, data table incremental change characteristics, and data quality conditions;

[0007] According to the data source inventory report, create or modify the corresponding source layer data table and the data table storing the cleaning data in the database, and obtain the field correspondence between the created or modified source layer data table and the data table storing the cleaning data;

[0008] Determine a data synchronization strategy according to the incremental change characteristics of the data table, and synchronize the data in the data source to the source layer data table according to the data synchronization strategy;

[0009] Generate data cleaning code according to the field correspondence between the source layer data table and the data table storing the cleaning data and the pre-set data cleaning rules;

[0010] The data in the source layer data table after data synchronization is cleaned according to the data cleaning code, and the cleaned data is saved in the data table for storing the cleaned data.

[0011] According to the above aspects and any possible implementation manner, an implementation manner is further provided, wherein the data source and its data table information are obtained, and data inventory is performed according to the information to generate a data source inventory report, wherein the data source inventory report includes data table metadata information, data field characteristics, data table association relationships, data table incremental change characteristics and data quality conditions, including:

[0012] Obtaining the data source and its data table information, obtaining metadata information of each data table based on the information and identifying the data increment change characteristics of each data table;

[0013] According to the metadata information of each data table, identifying the data field characteristics of each data table;

[0014] Identify data table association relationships based on the identified data field features;

[0015] According to the information and data quality audit rules, identify the data quality status of each data table;

[0016] The metadata information, data field characteristics, data table association relationships, data table incremental change characteristics and data quality status of each data table are used as a data source inventory report.

[0017] According to the above aspect and any possible implementation manner, an implementation manner is further provided, wherein obtaining metadata information of each data table according to the information includes:

[0018] Obtaining information about a data source and its data tables, and determining whether the data source is counted for the first time based on the information;

[0019] If yes, then obtain metadata information of each data table;

[0020] If not, then determining the data table changes of the data source after the last data inventory;

[0021] If there is a new data table, obtain metadata information of the new data table;

[0022] If there is a deleted data table, the deleted data table is marked as having data out of sync.

[0023] According to the above aspects and any possible implementation manner, an implementation manner is further provided, wherein the method further includes:

[0024] If no new or deleted data tables are added, compare the metadata in each data table with the last inventory to see if there are any changes;

[0025] If yes, then save the new metadata information of each data table, and mark the difference information according to the new metadata information of each data table;

[0026] If not, the metadata information of each data table obtained in the last inventory is used as the result of this data inventory.

[0027] According to the above aspects and any possible implementation manner, an implementation manner is further provided, wherein, according to the data source inventory report, a corresponding source layer data table and a data table storing cleaning data are created or modified in a database, and a field correspondence relationship between the created or modified source layer data table and the data table storing cleaning data is obtained, including:

[0028] According to the data source inventory report, analyze and determine whether each data table is a newly added data table;

[0029] If yes, then create a corresponding source layer data table and a data table for storing cleaning data, and record the field correspondence between the source layer data table and the data table for storing cleaning data;

[0030] If not, determine whether the metadata information of each data table has changed since the last data source inventory;

[0031] If yes, then correct the corresponding source layer data table and the data table storing the cleaning data, and record the field correspondence between the corrected source layer data table and the data table storing the cleaning data;

[0032] If not, determine whether the associated data tables of each data table have changed. If so, correct the corresponding data table storing the cleaned data, and record the field correspondence between the source layer data table and the corrected data table storing the cleaned data.

[0033] According to the above aspects and any possible implementation manner, an implementation manner is further provided, wherein the data in the data source is synchronized to the source layer data table according to the data synchronization strategy, including:

[0034] Determine whether each data table is synchronized for the first time;

[0035] If yes, then all existing data is synchronized to the source layer data table.

[0036] If not, determine whether the data synchronization strategy of each data table is full synchronization;

[0037] If yes, all existing data are synchronized to the source layer data table; if no, newly added data are synchronized to the source layer data table.

[0038] According to the above aspects and any possible implementation manner, an implementation manner is further provided, which identifies the data field features in each data table according to the metadata information of each data table, including:

[0039] Analyze the data record characteristics of the fields based on the metadata information of each data table;

[0040] The corpus data is obtained, the metadata information and the data record characteristics of the field are compared with the corpus data, and the data field characteristics of each data table are identified.

[0041] According to the above aspects and any possible implementation manner, an implementation manner is further provided, wherein the step of identifying the data table association relationship according to the identified data field features includes:

[0042] According to the identified data field features, foreign key association information of the data table is obtained, wherein:

[0043] The foreign key association information includes a foreign key field;

[0044] The association relationship between data tables is identified and recorded according to the foreign key association information, and the foreign key field is marked as a mandatory foreign key restriction.

[0045] According to the above aspect and any possible implementation manner, an implementation manner is further provided, wherein the identifying the data table association relationship according to the identified data field features further includes:

[0046] According to the identified data field characteristics, determining whether the data table has an identified suspected foreign key field;

[0047] If so, the associated data table is searched according to the suspected foreign key field. If the associated table can be found, the association relationship between the data tables is identified and recorded, and the suspected foreign key field is marked as no foreign key restriction; if the associated table cannot be found, the suspected foreign key field is marked as no associated table found.

[0048] According to a second aspect of the present disclosure, a data inventory, synchronization and cleaning device is provided, the device comprising:

[0049] The data source inventory module is used to obtain the data source and its data table information, and perform data inventory based on the information to generate a data source inventory report, wherein the data source inventory report includes data table metadata information, data field characteristics, data table association relationships, data table incremental change characteristics, etc.

[0050] The data table creation and modification module is used to create or modify the corresponding source layer data table and the data table for storing the cleaning data in the database according to the data source inventory report, and obtain the field correspondence between the created or modified source layer data table and the data table for storing the cleaning data;

[0051] A data synchronization module, used to determine a data synchronization strategy according to the incremental change characteristics of the data table, and synchronize the data in the data source to the source layer data table according to the data synchronization strategy;

[0052] A cleaning code generation module is used to generate data cleaning codes according to the field correspondence between the source layer data table and the data table storing the cleaning data and the pre-set data cleaning rules;

[0053] The data cleaning module is used to clean the data in the source layer data table after data synchronization according to the data cleaning code, and save the cleaned data into the data table for storing the cleaned data.

[0054] The present disclosure can intelligently perform data source inventory, automatically obtain metadata of data source data tables, associations between data tables, incremental changes in data, and data quality, determine data synchronization strategies and form data source inventory reports, and create or modify corresponding source layer data tables and data tables that store cleaned data based on the data source inventory reports, perform data synchronization based on the data synchronization strategies, and automatically clean the data in the synchronized source layer data tables. Intelligent and automated processing of data inventory, data table creation and modification, data synchronization, and data cleaning is achieved, reducing manual workload and shortening the time for building a data middle station or big data platform. Improve the work efficiency and accuracy of data source inventory, data integration, and data cleaning.

[0055] It should be understood that the contents described in the summary of the invention are not intended to limit the key or important features of the embodiments of the present disclosure, nor are they intended to limit the scope of the present disclosure. Other features of the present disclosure will become easily understood through the following description. BRIEF DESCRIPTION OF THE DRAWINGS

[0056] The above and other features, advantages and aspects of the embodiments of the present disclosure will become more apparent with reference to the following detailed description in conjunction with the accompanying drawings. The accompanying drawings are used to better understand the present solution and do not constitute a limitation of the present disclosure. In the accompanying drawings, the same or similar reference numerals represent the same or similar elements, among which:

[0057] Figure 1 A flow chart of a data inventory, synchronization and cleaning method according to an embodiment of the present disclosure is shown;

[0058] Figure 2 A data inventory flow chart according to an embodiment of the present disclosure is shown;

[0059] Figure 3 A flowchart of creating and modifying a data table according to an embodiment of the present disclosure is shown;

[0060] Figure 4 A flow chart of data synchronization according to an embodiment of the present disclosure is shown;

[0061] Figure 5 A flow chart of data cleaning according to an embodiment of the present disclosure is shown;

[0062] Figure 6 A flow chart of a method for identifying field features according to an embodiment of the present disclosure is shown;

[0063] Figure 7 A flowchart showing an association relationship between identification data tables according to an embodiment of the present disclosure is shown;

[0064] Figure 8 A block diagram of a data inventory, synchronization and cleaning device according to an embodiment of the present disclosure is shown. DETAILED DESCRIPTION

[0065] In order to make the purpose, technical solution and advantages of the embodiments of the present disclosure clearer, the technical solution in the embodiments of the present disclosure will be clearly and completely described below in conjunction with the drawings in the embodiments of the present disclosure. Obviously, the described embodiments are part of the embodiments of the present disclosure, not all of the embodiments. Based on the embodiments in the present disclosure, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present disclosure.

[0066] In addition, the term "and / or" in this article is only a description of the association relationship between the associated objects, indicating that there can be three relationships. For example, A and / or B can represent: A exists alone, A and B exist at the same time, and B exists alone. In addition, the character " / " in this article generally indicates that the associated objects before and after are in an "or" relationship.

[0067] The present disclosure proposes a data inventory, synchronization and cleaning method, which includes: obtaining the information of the data source and each data table thereof, performing data inventory on each data table according to the information, and generating a data source inventory report; creating or modifying the corresponding source layer data table and the data table storing the cleaned data in the database according to the data source inventory report, and obtaining the field correspondence between the source layer data table and the data table storing the cleaned data; synchronizing the data in the data source to the source layer data table according to the data synchronization strategy; generating a data cleaning code according to the field correspondence between the source layer data table and the data table storing the cleaned data and the pre-set data cleaning rules; performing data cleaning on the data in the source layer data table after data synchronization according to the data cleaning code, and saving the cleaned data to the data table storing the cleaned data. In this way, the intelligent and automated processing of data inventory, data table creation and modification, data synchronization, and data cleaning is realized, reducing the manual workload and shortening the time for building a data middle platform or a big data platform.

[0068] A specific embodiment of a data inventory, synchronization and cleaning method provided by the present disclosure is described below in conjunction with the accompanying drawings.

[0069] Figure 1 FIG. 1 is a flow chart of a data inventory, synchronization and cleaning method 100 according to an embodiment of the present disclosure. Figure 1 As shown, a data inventory, synchronization and cleaning method 100 includes:

[0070] S110, obtaining the data source and its data table information, and performing data inventory based on the information to generate a data source inventory report, wherein the data source inventory report includes data table metadata information, data field characteristics, data table association relationships, data table incremental change characteristics and data quality status.

[0071] In some embodiments, Figure 2 As shown, performing data inventory based on the information to generate a data source inventory report includes the following steps:

[0072] (1) Obtain the data source and its data table information. Specifically, the configuration information of the specified data source can be obtained from the data source management module, the data source is connected according to the configuration information, and the data table information is obtained from the data source. Then enter node (2).

[0073] (2) Determine whether the currently running inventory count is the first inventory count. If it is the first inventory count, go to node (3); otherwise, go to node (6).

[0074] (3) Obtain metadata information of all data tables from the data source and save it, then enter node (4).

[0075] (4) Identify and save the data field features in each data table. Specifically, according to the identification results, mark the corresponding information "ID field, UUID field, partition field, data record deletion mark field, data record creation time field, enumeration value field with standard code features, suspected foreign key field" for the data field with identified features. Data fields without identification features are not marked. Then enter node (5).

[0076] (5) Identify the association between data tables based on the metadata information of each data table and save it, and then enter node (15).

[0077] (6) Analyze the changes of the data source tables one by one since the last inventory. If a new table is added, enter node (7); if a table is deleted, enter node (10); other tables (i.e., tables that have neither been deleted nor added) enter node (11).

[0078] (7) Obtain the metadata information of the newly added data table and save it, then enter node (8).

[0079] (8) Identify the characteristics of the data fields in the newly added data table and save them. The specific principles or steps are the same as those in the above node (4), and then enter node (9).

[0080] (9) Identify the association relationship of the newly added data table and save it, and enter node (15).

[0081] (10) Mark the deleted data table so that the table can be marked as out of sync in the subsequent node "(16) Determine the data synchronization strategy for each data table". Then enter node (15).

[0082] (11) Compare the metadata in the table to see if it is different from the last inventory. If the metadata has not changed, go to node (12); if the metadata has changed, go to node (13).

[0083] (12) Use the metadata, data field characteristics, and the relationship between data tables obtained from the last inventory as the corresponding inventory results and enter node (15).

[0084] (13) For a data table whose metadata has changed, save the new metadata information of the data table and mark the difference information with the last time, mark the modified or deleted field information, and enter node (14).

[0085] (14) Identify and save the field features of the difference data field, which is the same as the above node (4), and then enter node (15).

[0086] (15) Based on the acquired data source and its data table information, a large model is used to intelligently identify the data increment change characteristics of each data table, and then enter node (16). Specifically, the data increment change situation can be divided into the following situations:

[0087] Data continues to grow every day, and data does not change after it is added. For example, log data, message data, etc.

[0088] Data continues to grow every day, and data will change in a short period of time. For example, order data, logistics data, etc.

[0089] The total amount of data is small. The data is created in a concentrated period of time when the system is initialized, and does not change or changes only slightly later. For example, system menu data, etc.

[0090] The total amount of data is small, and the data is created in a concentrated period of time when the system is initialized, and will change slowly later. For example, corporate organizational structure data, etc.

[0091] (16) Determine the data synchronization strategy for each data table based on the data increment change characteristics of each data table

[0092] "Deleted data tables are out of sync."

[0093] The data table where "data continues to grow every day and does not change after new data is added" is fully synchronized for the first time, and then incremental synchronization is performed using the "data record creation time field" as the discrimination field.

[0094] For data tables where "data continues to grow every day and changes in a short period of time", full synchronization is performed for the first time, and then incremental synchronization is performed using the "data record modification time field" as the discrimination field.

[0095] For data tables with "a small amount of data, data is created in a period of time when the system is initialized, and does not change or changes slightly later" and "a small amount of data, data is created in a period of time when the system is initialized, and changes slowly later", the full synchronization is performed for the first time and thereafter.

[0096] After determining the data synchronization strategy, enter node (17).

[0097] (17) According to the data quality audit rules, the data quality status in each data table is identified. If the data table is set with "directional rules", the system uses the general rules and "directional rules" to identify the data quality status; otherwise, the general rules are used to identify the data quality status. The identified quality problems are recorded. Then enter node (18).

[0098] (18) After all data tables are counted, an inventory report is generated. The report content mainly includes "data table list, data table metadata information, data field characteristics, data table association relationships, data table data incremental change characteristics, data quality status", etc. Then enter node (19).

[0099] (19) Whether to start data table creation and correction. If the system is set to "yes", enter node (20); if the system is set to "no", end.

[0100] (20) Enter the data table creation and correction process, and automatically start the creation and correction process of the source layer data table corresponding to the data source.

[0101] In some embodiments, the present invention uses a large model to intelligently identify data field features of a data table, specifically, Figure 6 As shown, according to the metadata information of each data table, identifying the data field features in each data table includes the following steps:

[0102] Obtain metadata information of each data table. Specifically, the data metadata information includes field information such as field name, end annotation, field type, etc.

[0103] Analyze the data record characteristics of the field based on field information such as field name, word end annotation, field type, etc. The data record characteristics include "whether the data value is unique, whether the data value is self-incrementing, whether the data value is encoded, whether the data value can be summarized as an enumeration value standard code, whether there is duplicate data, whether there is empty data", etc.

[0104] Obtain corpus data of various feature fields from the corpus; intelligently identify field features and mark feature fields by comparing field information and data record features with the corpus.

[0105] In some embodiments, the present invention uses a large model to intelligently identify the association relationship between data tables, specifically Figure 7 As shown, according to the identified data field features, identifying the data table association relationship includes the following steps:

[0106] Acquire foreign key association information of the data table according to the identified data field characteristics, wherein the foreign key association information includes foreign key fields;

[0107] The association relationship between data tables is identified and recorded according to the foreign key association information, and the foreign key field is marked as a mandatory foreign key restriction.

[0108] According to the identified data field characteristics, determining whether the data table has an identified suspected foreign key field;

[0109] If yes, then search for the associated data table according to the suspected foreign key field;

[0110] If the associated table can be found, the association between the data tables is identified and recorded, and the suspected foreign key fields are marked as not subject to foreign key restrictions;

[0111] If the associated table cannot be found, the suspected foreign key field is marked as the associated table not found.

[0112] S120, creating or modifying a corresponding source layer data table and a data table storing cleaning data in a database according to the data source inventory report, and obtaining a field correspondence relationship between the created or modified source layer data table and the data table storing cleaning data.

[0113] In some embodiments, Figure 3 As shown, after starting the data table creation and modification, the system runs according to the following steps:

[0114] (31) Obtain the most recent data source data table inventory result and enter node (32).

[0115] (32) Analyze the data table one by one and enter node (33).

[0116] (33) Determine whether the data table is a new data table. If so, enter node (34); otherwise, enter node (37).

[0117] (34) Create the corresponding source layer data table in the data warehouse. Specifically, the data fields of the source layer data table copy the fields of the corresponding data source data table, and add three fields: "UUID (unique identification code), pt_d (data synchronization date, partition field), etl_at (data synchronization time)". After completing the creation of the source layer data table, enter node (35).

[0118] (35) Create a corresponding data table in the data warehouse to store the cleaned data. Specifically, if the data source data table corresponding to the source layer data table does not have a data table associated with a foreign key, the table structure of the cleaned data table is the same as that of the source layer data table. If the data source data table corresponding to the source layer data table has a data table associated with a foreign key, the foreign key associated table information is expanded based on the source layer data table to form the table structure of the cleaned data table. After the creation of the cleaned data table is completed, enter node (36).

[0119] (36) Record the field correspondence between the cleaning data table and the source layer data table, and enter node (312).

[0120] (37) Determine whether the data table metadata has changed since the last inventory count. If so, enter node (38). If so, enter node (311).

[0121] (38) Correct the corresponding source layer data table in the data warehouse

[0122] Specifically, the source layer data table is modified with reference to the changes in the corresponding data source data table. If a new field is added to the data source data table, the same field is added to the source layer data table; if the annotation information of the field in the data source data table is modified, the annotation information of the corresponding field in the source layer data table is modified; if the field in the data source data table is deleted, the corresponding field in the source layer data table is marked, and the data synchronization of the field is not performed thereafter. After the modification is completed, enter node (39).

[0123] (39) Correct the corresponding data table for saving cleaning data

[0124] Specifically, the corresponding cleaned data table is modified. If a new field is added to the data source data table (including the data table associated with the foreign key), the same field is added to the cleaned data table; if the added field is a foreign key field, the field information of the table associated with the foreign key field is also added. If the field annotation information of the data source data table (including the data table associated with the foreign key) is modified, the annotation information of the corresponding field of the cleaned data table is modified. After the modification is completed, enter node (310).

[0125] (310) Correct the field correspondence between the cleaning data table and the source layer data table, and enter node (312).

[0126] (311) Determine whether the metadata of the associated data table of the data table has changed. If yes, proceed to node (39); if no, proceed to node (312).

[0127] (312) After waiting for all data tables to be created and modified, enter node (313).

[0128] (313) Whether to start data synchronization

[0129] After all data tables are created and modified, the system settings determine whether to continue the subsequent process or end the process. If the system setting is yes, then go to node (14); if the system setting is no, then end.

[0130] (314) Enter data synchronization

[0131] Automatically start the data synchronization process corresponding to the data source.

[0132] S130, determining a data synchronization strategy according to the incremental change characteristics of the data table, and synchronizing the data in the data source to the source layer data table according to the data synchronization strategy.

[0133] In some embodiments, Figure 4 As shown in the figure, after starting data synchronization, the system runs according to the following steps.

[0134] (41) Obtain the corresponding data warehouse source layer data table information, and then enter node (42).

[0135] (42) Obtain the corresponding data source data table information, and then enter node (43).

[0136] (43) Obtain the data synchronization strategy for each data table and enter node (44).

[0137] (44) Determine whether the data table is the first synchronization data, if "yes" enter node (45), if "no" enter node (46).

[0138] (45) Fully synchronize all data in the data source data table, and then enter node (48).

[0139] (46) Is it full synchronization?

[0140] For non-first synchronization, whether the data synchronization strategy is full synchronization, if "yes", go to node (45), if "no", go to node (47).

[0141] (47) Synchronize data according to the incremental synchronization field, that is, synchronize data whose time is greater than the last synchronization time, and then enter the node (48).

[0142] (48) After waiting for the current round of data synchronization to be completed, enter node (49).

[0143] (49) Whether to start data cleaning

[0144] After the current round of data synchronization is completed, the system settings will determine whether to continue the subsequent process or end the process. If the system setting is "yes", data cleaning will be performed; if the system setting is "no", it will end.

[0145] (10) Enter data cleaning and automatically start the data cleaning process corresponding to the data source.

[0146] S140, generating a data cleaning code according to the field correspondence between the source layer data table and the data table storing the cleaning data and a pre-set data cleaning rule.

[0147] In some embodiments, data cleaning rules include general rules and targeted rules. General rules are applicable to data cleaning work of all data tables, mainly including duplicate data removal, key information missing data removal, etc. Targeted rules are data cleaning work for specific data tables, which are set manually and are only used for specific data tables during data cleaning.

[0148] S150, cleaning the data in the source layer data table after data synchronization according to the data cleaning code, and saving the cleaned data into the data table for storing cleaned data.

[0149] In some embodiments, Figure 5 As shown, after starting the data cleaning process corresponding to the data source, the system executes the following steps:

[0150] (51) Obtain the data table information storing the cleaning data and enter node (52).

[0151] (52) Obtain the field correspondence between the cleaning data table and the source layer data table, and enter node (53).

[0152] (53) Obtain data cleaning rules, obtain data cleaning rules from the "cleaning rule management module", and then enter node (54).

[0153] (54) Integrate the field correspondence of the data table and the data cleaning rules, intelligently generate data cleaning code, and enter node (55).

[0154] (55) Run the data cleaning code, read the data in the source layer data table to perform data cleaning, and then enter node (56).

[0155] (56) Save the cleaned data to the cleaned data table. After all data cleaning is completed, the process ends.

[0156] According to the embodiments of the present disclosure, the following technical effects are achieved:

[0157] (1) The present disclosure can intelligently perform data source inventory, automatically obtain the metadata of the data source data table, the relationship between data tables, the incremental change of data, and the quality of data, determine the data synchronization strategy and form a data source inventory report, and create or modify the corresponding source layer data table and the data table that stores the cleaned data according to the data source inventory report, perform data synchronization according to the data synchronization strategy, and automatically clean the data in the synchronized source layer data table. It realizes intelligent and automated processing of data inventory, data table creation and modification, data synchronization, and data cleaning, reduces manual workload, and shortens the time for building a data middle platform or a big data platform. It improves the work efficiency and accuracy of data source inventory, data integration, and data cleaning.

[0158] (2) The present invention uses a large model to intelligently identify characteristic fields in a data table and the relationship between data tables, thereby providing technical support for automatic data inventory.

[0159] It should be noted that, for the aforementioned method embodiments, for the sake of simplicity, they are all described as a series of action combinations, but those skilled in the art should be aware that the present disclosure is not limited by the order of the actions described, because according to the present disclosure, certain steps can be performed in other orders or simultaneously. Secondly, those skilled in the art should also be aware that the embodiments described in the specification are all optional embodiments, and the actions and modules involved are not necessarily required by the present disclosure.

[0160] The above is an introduction to the method embodiment. The following is a further explanation of the scheme disclosed in the present invention through an apparatus embodiment.

[0161] Figure 8 A block diagram of a data inventory, synchronization and cleaning device 300 according to an embodiment of the present disclosure is shown. Figure 8 As shown, the device 800 includes:

[0162] The data source inventory module 810 is used to obtain the data source and its data table information, and perform data inventory based on the information to generate a data source inventory report, wherein the data source inventory report includes data table metadata information, data field characteristics, data table association relationships, data table incremental change characteristics and data quality conditions;

[0163] The data table creation and modification module 820 is used to create or modify the corresponding source layer data table and the data table for storing cleaning data in the database according to the data source inventory report, and obtain the field correspondence between the created or modified source layer data table and the data table for storing cleaning data;

[0164] A data synchronization module 830 is used to determine a data synchronization strategy according to the incremental change characteristics of the data table, and synchronize the data in the data source to the source layer data table according to the data synchronization strategy;

[0165] A cleaning code generation module 840 is used to generate data cleaning codes according to the field correspondence between the source layer data table and the data table storing the cleaning data and a pre-set data cleaning rule;

[0166] The data cleaning module 850 is used to clean the data in the source layer data table after data synchronization according to the data cleaning code, and save the cleaned data into the data table for storing cleaned data.

[0167] Those skilled in the art can clearly understand that, for the convenience and brevity of description, the specific working process of the described module can refer to the corresponding process in the aforementioned method embodiment, and will not be repeated here.

[0168] It should be understood that the various forms of processes shown above can be used to reorder, add or delete steps. For example, the steps recorded in this disclosure can be executed in parallel, sequentially or in different orders, as long as the desired results of the technical solution of this disclosure can be achieved, and this document is not limited here.

[0169] The above specific implementations do not constitute a limitation on the protection scope of the present disclosure. It should be understood by those skilled in the art that various modifications, combinations, sub-combinations and substitutions can be made according to design requirements and other factors. Any modification, equivalent substitution and improvement made within the spirit and principle of the present disclosure shall be included in the protection scope of the present disclosure.

Claims

1. A data inventory, synchronization and cleaning method, characterized in that: include: Obtaining the data source and its data table information, and performing data inventory based on the information to generate a data source inventory report, wherein the data source inventory report includes data table metadata information, data field characteristics, data table association relationships, data table incremental change characteristics, and data quality conditions; According to the data source inventory report, create or modify the corresponding source layer data table and the data table storing the cleaning data in the database, and obtain the field correspondence between the created or modified source layer data table and the data table storing the cleaning data; Determine a data synchronization strategy according to the incremental change characteristics of the data table, and synchronize the data in the data source to the source layer data table according to the data synchronization strategy; Generate data cleaning code according to the field correspondence between the source layer data table and the data table storing the cleaning data and the pre-set data cleaning rules; The data in the source layer data table after data synchronization is cleaned according to the data cleaning code, and the cleaned data is saved in the data table for storing the cleaned data.

2. The method according to claim 1, characterized in that The data source and its data table information are obtained, and data inventory is performed according to the information to generate a data source inventory report, wherein the data source inventory report includes data table metadata information, data field characteristics, data table association relationships, data table incremental change characteristics and data quality conditions, including: Obtaining the data source and its data table information, obtaining metadata information of each data table based on the information and identifying the data increment change characteristics of each data table; According to the metadata information of each data table, identifying the data field characteristics of each data table; Identify data table association relationships based on the identified data field features; According to the information and data quality audit rules, identify the data quality status of each data table; The metadata information, data field characteristics, data table association relationships, data table incremental change characteristics and data quality status of each data table are used as a data source inventory report.

3. The method according to claim 2, characterized in that The obtaining metadata information of each data table according to the information includes: Obtaining information about a data source and its data tables, and determining whether the data source is counted for the first time based on the information; If yes, then obtain metadata information of each data table; If not, then determining the data table changes of the data source after the last data inventory; If there is a new data table, obtain metadata information of the new data table; If there is a deleted data table, the deleted data table is marked as having data out of sync.

4. The method according to claim 3, characterized in that The method further comprises: If no new or deleted data tables are added, compare the metadata in each data table with the last inventory to see if there are any changes; If yes, then save the new metadata information of each data table, and mark the difference information according to the new metadata information of each data table; If not, the metadata information of each data table obtained in the last inventory is used as the result of this data inventory.

5. The method according to claim 1, characterized in that The step of creating or modifying a corresponding source layer data table and a data table storing cleaning data in a database according to the data source inventory report, and obtaining a field correspondence relationship between the created or modified source layer data table and the data table storing cleaning data includes: According to the data source inventory report, analyze and determine whether each data table is a newly added data table; If yes, then create a corresponding source layer data table and a data table for storing cleaning data, and record the field correspondence between the source layer data table and the data table for storing cleaning data; If not, determine whether the metadata information of each data table has changed since the last data source inventory; If yes, then correct the corresponding source layer data table and the data table storing the cleaning data, and record the field correspondence between the corrected source layer data table and the data table storing the cleaning data; If not, determine whether the associated data tables of each data table have changed. If so, correct the corresponding data table storing the cleaned data, and record the field correspondence between the source layer data table and the corrected data table storing the cleaned data.

6. The method according to claim 1, characterized in that Synchronizing the data in the data source to the source layer data table according to the data synchronization strategy includes: Determine whether each data table is synchronized for the first time; If yes, then all existing data is synchronized to the source layer data table. If not, determine whether the data synchronization strategy of each data table is full synchronization; If yes, all existing data are synchronized to the source layer data table; if no, newly added data are synchronized to the source layer data table.

7. The method according to claim 3, characterized in that According to the metadata information of each data table, identifying the data field characteristics in each data table includes: Analyze the data record characteristics of the fields based on the metadata information of each data table; The corpus data is obtained, the metadata information and the data record characteristics of the field are compared with the corpus data, and the data field characteristics of each data table are identified.

8. The method according to claim 3, characterized in that The step of identifying data table associations according to the identified data field features includes: Acquire foreign key association information of the data table according to the identified data field characteristics, wherein the foreign key association information includes foreign key fields; The association relationship between data tables is identified and recorded according to the foreign key association information, and the foreign key field is marked as a mandatory foreign key restriction.

9. The method according to claim 8, characterized in that The step of identifying the data table association relationship according to the identified data field features further comprises: According to the identified data field characteristics, determining whether the data table has an identified suspected foreign key field; If so, the associated data table is searched according to the suspected foreign key field. If the associated table can be found, the association relationship between the data tables is identified and recorded, and the suspected foreign key field is marked as no foreign key restriction; if the associated table cannot be found, the suspected foreign key field is marked as no associated table found.

10. A data inventory, synchronization and cleaning device, characterized in that: include: A data source inventory module is used to obtain the data source and its data table information, and perform data inventory based on the information to generate a data source inventory report, wherein the data source inventory report includes data table metadata information, data field characteristics, data table association relationships, data table incremental change characteristics and data quality status; A data table creation and modification module, used to create or modify the corresponding source layer data table and the data table for storing cleaning data in the database according to the data source inventory report, and obtain the field correspondence between the created or modified source layer data table and the data table for storing cleaning data; A data synchronization module, used to determine a data synchronization strategy according to the incremental change characteristics of the data table, and synchronize the data in the data source to the source layer data table according to the data synchronization strategy; A cleaning code generation module is used to generate data cleaning codes according to the field correspondence between the source layer data table and the data table storing the cleaning data and the pre-set data cleaning rules; The data cleaning module is used to clean the data in the source layer data table after data synchronization according to the data cleaning code, and save the cleaned data into the data table for storing the cleaned data.

Images