Data checking method and device for multi-source heterogeneous database

Abstract

The invention relates to the technical field of database quality management, and particularly provides a data checking method and device for a multi-source heterogeneous database, and the method comprises the following steps: S1, establishing data source information; s2, establishing a checking task; and S3, checking task scheduling and execution. Compared with the prior art, the method has the advantages that data quality checking challenges in a multi-source heterogeneous database environment can be effectively handled, unified management and efficient checking of various database types are achieved, powerful support is provided for data quality guarantee through diversified checking rules and a flexible task scheduling mechanism, and the method is suitable for popularization and application. And the data management level of the organization and the reliability of the decision basis can be improved.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database quality management, and specifically provides a data checking method and device for multi-source heterogeneous databases. BACKGROUND

[0002] With the rapid development of information technology, data plays an increasingly critical role in enterprises, research institutions and other types of organizations, and almost becomes an important basis for core assets and decision-making. In order to adapt to different business needs, ensure system compatibility and improve data processing efficiency, many organizations widely use various types of databases to store and manage data. Common database systems include international mainstream databases such as MySQL, PostgreSQL, Oracle, and domestic databases such as KingBase and GoldenDB.

[0003] However, in the multi-source heterogeneous database environment, the accuracy, completeness, timeliness and other qualities of the data are difficult to be effectively guaranteed. On the one hand, different databases have significant differences in data storage structure, data type definition, data operation mode, etc., which leads to deviations and errors in the process of cross-database interaction and integration. On the other hand, each business system runs independently, and the data update frequency and rules are inconsistent, making it difficult to keep data synchronized and consistent between different databases.

[0004] Traditional checking methods are often targeted at a single database type and lack the ability to adapt to multi-source heterogeneous environments. In terms of operation process, traditional checking usually requires manual writing of a large number of scripts or query statements, and data checking is performed one by one for different databases, which is tedious and prone to errors. In terms of resource utilization, traditional checking methods are difficult to achieve efficient scheduling and parallel processing of tasks, resulting in low checking efficiency and inability to timely discover data quality problems.

[0005] With the explosive growth of data volume and the continuous change of business needs, traditional checking methods have been unable to meet the current complex and changing database checking needs, and there is an urgent need for a system and method that can uniformly manage and efficiently check the data quality of multi-source heterogeneous databases. SUMMARY

[0006] The present application is aimed at the deficiencies of the prior art and provides a multi-source heterogeneous database data checking method with strong practicality.

[0007] The further technical task of the present application is to provide a multi-source heterogeneous database data checking device with reasonable design and safety.

[0008] The technical solution adopted by the present application to solve its technical problems is:

[0009] A multi-source heterogeneous database data checking method, comprising the following steps:

[0010] S1, establish data source information;

[0011] S2, establish verification task;

[0012] S3, verification task scheduling and execution.

[0013] Further, in step S1, in actual application, the user first configures the multi-source heterogeneous database to be verified through the data source management module, including adding connection information of different types of databases and testing whether the connection is successful, saving the information after successful connection;

[0014] Among them, the data source type is input after being selected by the drop-down box, and the selected data source type is Mysql, Postgresql, Oracle or KingBase8;

[0015] After selecting the data source type, the data source url template is displayed in the data source address bar through light-colored words, then the username is input in the username input box, and the password is input in the password input box, wherein the password is visible by clicking the visible mark, real-time viewing is improved. The accuracy of password input.

[0016] Further, after inputting the data source information, click Test Connection to return the information of whether the connection is successful;

[0017] After successful connection, the system returns the "connection successful" word; after connection failure, the system returns the "connection failed" word and the reason information of connection failure;

[0018] After inputting, click the "OK" button to save the information to the system; click the "Close" button to cancel this addition operation and close the addition page.

[0019] Further, the user inputs the query condition: data source name in the data source name input box to retrieve the related data source information of the name in the case of a large amount of data source information;

[0020] Click the query button without inputting the data source name to complete the full-quantity query, and the user can see all the data source connection information;

[0021] Click the "Reset" button to clear the query condition and perform full-quantity query to view all the data source connection information;

[0022] The user clicks the "link" mark button in the "operation" column of each record to test the database connection and check whether the data source information is connected;

[0023] By clicking the "paintbrush" sign button in the "operation" column of each record, the data source information record is edited again. The edited information is the same as when adding a page;

[0024] By clicking the "trash can" sign button in the "operation" column of the record, the data source information is deleted.

[0025] Further, a new verification task detail page is added, and the task name, rule type, data source, database, table name, note and filter condition are input;

[0026] Among them, the rule type is input after being selected by the drop-down box, and the rule data comes from the preset rule library. The selected rule types are: null check, value range check, timeliness check, specification check, duplicate data check and custom check;

[0027] The data source information is also obtained through the drop-down box, and the information is obtained through the data source management module of the system;

[0028] After selecting the data source, perform cascading query, and select all databases under the data source in the database drop-down box.

[0029] Further, after selecting the database name, all table names under the database are obtained in the table name column. After selecting the table name, all fields and notes of the table are displayed in the field display area. Select the field as the to-be-checked field, and support multiple selection.

[0030] Further, after completing the selection of the to-be-checked field, fill in the Cron expression through the Cron expression selection component, which is used to specify the execution period of the verification task;

[0031] Select whether to perform the task immediately after creation through "whether to enable". If "yes" is selected, fill in a verification task, and the verifier is called by the scheduling center to start the verification task immediately. If "no" is selected, the verification task is not executed temporarily, and the verification task is managed in the verification task management page.

[0032] Further, the verification scheduling center triggers the verification task executor according to the set Cron according to the selected enabled verification task, selects executable nodes, and checks the data in each data source database according to the verification rule in turn;

[0033] The verification rule is obtained by storing the preset Sql statement and filter condition in the database. The parameters of the preset Sql are obtained by the filter condition field, and the fields are specified by the filter condition input parameter time field for time range verification.

[0034] A multi-source heterogeneous database data verification device, comprising: at least one memory and at least one processor;

[0035] The at least one memory is configured to store a machine-readable program;

[0036] The at least one processor is configured to call the machine-readable program to execute a data verification method for a multi-source heterogeneous database.

[0037] Compared with the prior art, the data verification method and device for a multi-source heterogeneous database of the present invention have the following outstanding beneficial effects:

[0038] The data verification system and method for multi-source heterogeneous databases provided by the present invention can effectively cope with the data quality verification challenges in a multi-source heterogeneous database environment, realize unified management and efficient verification of various database types, and provide strong support for data quality assurance through diversified verification rules and flexible task scheduling mechanisms, which helps to improve the organization's data management level and the reliability of decision-making basis. BRIEF DESCRIPTION OF THE DRAWINGS

[0039] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the embodiments or the description of the prior art. Obviously, the drawings described below are some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0040] Fig. 1 It is a flow chart of a data verification method for a multi-source heterogeneous database;

[0041] Fig. 2 The present invention is a framework diagram of a data verification method for multi-source heterogeneous databases. DETAILED DESCRIPTION

[0042] In order to enable those skilled in the art to better understand the solutions of the present invention, the present invention will be further described in detail below in conjunction with specific embodiments. Obviously, the embodiments described are only some embodiments of the present invention, rather than all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative work are within the scope of protection of the present invention.

[0043] A best embodiment is given below:

[0044] like Figs. 1-2 As shown, a data verification method for a multi-source heterogeneous database in this embodiment has the following steps:

[0045] S1. Establish data source information;

[0046] In practical application, the user first configures the multi-source heterogeneous database to be checked through the data source management module, including adding connection information of different types of databases (such as MySQL, PostgreSQL, Oracle, KingBase, etc.) and testing whether the connection is successful. After successful connection, the information can be saved.

[0047] Among them, the data source type is input after being selected through a drop-down box. The available data source types include Mysql, Postgresql, Oracle, KingBase8, etc.

[0048] After selecting the data source type, the data source url template is displayed in the data source address bar through light-colored words, such as "jdbc:postgresql: / / %s:%s / ", which facilitates guiding the user to fill in and improves the accuracy and standardization of input.

[0049] Next, enter the username in the username input box and the password in the password input box. The password can be viewed in real time by clicking the visible flag, which improves the accuracy of password input.

[0050] After entering the data source information, click Test Connection to return information about whether the connection is successful.

[0051] After successful connection, the system returns the "Connection Successful" word. After failed connection, the system returns the "Connection Failed" word and the reason information for connection failure, such as "Please check whether the ip and port are correct" in the return information, indicating that there is an input error in the ip and port; "Account password error" in the return information, indicating that there is an input error in the account or password.

[0052] After inputting, click the "Confirm" button to save the information to the system. Note that if the record of failed connection is clicked and confirmed, it will also be saved to the system, avoiding information saving failure due to non-correctness of information, facilitating retesting or reediting of data source information next time. The non-correctness of information refers to problems such as network connection.

[0053] Click the "Close" button to cancel the current addition operation and close the addition page.

[0054] In the data source management interface, all input data source information can be seen, including data source name, data source URL, driver name, username, data source type, operation, etc.

[0055] The user can input the query condition: data source name in the data source name input box to quickly retrieve the data source information related to the name in the case of a large amount of data source information.

[0056] Click the query without inputting the data source name to complete the full query, and the user can see all the data source connection information;

[0057] Click the "Reset" button to quickly clear the query conditions and perform full query to view all data source connection information.

[0058] The user can quickly test the database connection by clicking the "link" logo button in the "operation" column of each record, and check whether the data source information is connected.

[0059] By clicking the "paintbrush" logo button in the "operation" column of each record, the user can perform re-editing operation on the data source information record. The editable information is the same as when adding a new page, and will not be described here.

[0060] By clicking the "trash can" logo button in the "operation" column of each record, the user can perform deletion operation on the data source information.

[0061] S2, establish a verification task;

[0062] In the embodiment, by adding a verification task detail page, the user inputs the task name, rule type, data source, database, table name, note, and filter condition.

[0063] The rule type is input after being selected by a drop-down box, and the rule data comes from a preset rule library. The available rule types include null value check, value range check, timeliness check, specification check, duplicate data check, and custom check.

[0064] The data source information is also obtained through a drop-down box, and the information is obtained through the system's data source management module.

[0065] After selecting the data source, the user performs cascading query to select all databases under the data source in the database drop-down box.

[0066] After selecting the database name, the user obtains all table names under the database in the table name column. In particular, in the four-layer connection structure of "connection-database-schema-table" in Postgresql, the table name column obtains the field of "schema name.table name". Considering that the data amount obtained by "schema name.table name" is large, the user can search and input in the table name column.

[0067] After selecting the table name, the user will see all fields and notes of the table in the field display area. The user selects the field as the to-be-verified field, and supports multiple selection.

[0068] After the completion of the field selection to be checked, fill in the Cron expression through the Cron expression selection component, which is used to specify the execution period of the verification task, such as setting the Cron as "0 0 / 20***?", which represents that the current verification task is set to run once every 20 minutes, and setting the Cron expression as "0 0 3**?", which realizes the automatic start of the verification task at 3 o'clock in the morning every day.

[0069] Select whether to perform the task immediately after creation through "whether to enable". If "yes" is selected, fill in the completion of a verification task, and the verifier is called by the scheduling center to start the verification task immediately. If "no" is selected, the verification task is not executed temporarily.

[0070] The verification task can be managed in the verification task management page.

[0071] S3, verification task scheduling and execution;

[0072] The verification scheduling center triggers the verification task executor according to the set Cron in accordance with the enabled verification task, selects the executable node, and performs verification on the data in each data source database in turn according to the verification rule.

[0073] The verification rule is obtained by the preset Sql statement and the filtering condition stored in the database, wherein the parameters of the preset Sql are obtained by the filtering condition field, and the filtering condition can input the time field to specify the time range of the field for verification, such as in the timeliness check, the filtering condition is "1minute&stime", which represents whether the collection time of the data in the record to be checked is within 1 minute.

[0074] Based on the above method, the data verification device of the multi-source heterogeneous database in the embodiment includes at least one memory and at least one processor.

[0075] The at least one memory is used to store a machine-readable program.

[0076] The at least one processor is used to call the machine-readable program and execute the data verification method of the multi-source heterogeneous database.

[0077] The above specific embodiments are only specific cases of the present application, and the patent protection scope of the present application includes but is not limited to the above specific embodiments. Any suitable changes or replacements made by any ordinary skilled person in the art to the technical solutions described in the above specific embodiments of the present application shall fall within the patent protection scope of the present application.

[0078] While embodiments of the application have been shown and described, it is to be understood that the embodiments described are merely exemplary of the principles and application of the present application. Numerous modifications and adaptions can be effected without departing from the spirit and scope of the present application, which is not limited to the exact construction and arrangement described. It is intended, therefore, to cover all modifications and adaptions that fall within the scope of the claims and their equivalents.

Claims

1. A data verification method for a multi-source heterogeneous database, characterized in that: The steps are as follows: S1. Establish data source information; S2. Establish verification tasks; S3. Verify task scheduling and execution.

2. The data verification method for a multi-source heterogeneous database according to claim 1, characterized in that: In step S1, in actual application, the user first configures the multi-source heterogeneous databases that need to be verified through the data source management module, including adding connection information of different types of databases and testing whether the connection is successful. After the connection is successful, the information is saved; Among them, the data source type is entered by selecting it from the drop-down box. The selected data source types include Mysql, Postgresql, Oracle or KingBase8; After selecting the data source type, the data source URL template is displayed in lighter-colored text in the data source address bar. Next, enter the user name in the user name input box and the password in the password input box. The password can be checked in real time by clicking the visible mark to improve the accuracy of password input.

3. The data verification method for a multi-source heterogeneous database according to claim 2, characterized in that: After entering the data source information, click Test Connection to return information on whether the connection is successful; After the connection is successful, the system returns "Connection successful"; after the connection fails, the system returns "Connection failed" and the reason for the connection failure; After completing the input, click the "OK" button to save the information to the system; click the "Close" button to cancel the new addition operation and close the new addition page.

4. The data verification method for a multi-source heterogeneous database according to claim 3, characterized in that: The user enters the query condition: data source name in the data source name input box to retrieve the data source information related to the name in the case of a large amount of data source information; Clicking Query without entering a data source name completes the full query, and the user sees all data source connection information. Click the "Reset" button to clear the query conditions, perform a full query, and view all data source connection information. The user can test the database connection by clicking the "Link" icon button in the "Operation" column of each record to check whether the data source information is connected; By clicking the "brush" icon button in the "Operation" column of each record, you can edit the data source information record again. The edited information is the same as when adding a new page. By clicking the "trash can" icon button in the "Operation" column of a record, you can delete the data source information.

5. The data verification method for a multi-source heterogeneous database according to claim 4, characterized in that: Added a verification task details page to enter the task name, rule type, data source, database, table name, notes and filter conditions; The rule type is selected from the drop-down box and then entered. The rule data comes from the preset rule library. The rule types available for selection include: null value check, value range check, timeliness check, standardization check, duplicate data check, and custom check. Data source information is also obtained through the drop-down box, and the information is obtained through the system's data source management module; After selecting a data source, perform a cascade query and select all databases under the data source in the database drop-down box.

6. The data verification method for a multi-source heterogeneous database according to claim 5, characterized in that: After selecting the database name, all table names under the database are obtained in the Table Name column. After selecting a table name, all fields and notes of the table will be displayed in the field display area. Select the fields to be verified. Multiple selection is supported.

7. The data verification method for a multi-source heterogeneous database according to claim 6, characterized in that: After selecting the fields to be verified, use the Cron Expression Selection component to enter a Cron expression to specify the execution cycle of the verification task. Use "Enable" to select whether to execute the task immediately after creation. If you select "Yes", the scheduling center will call the verifier to start the verification task immediately after completing the verification task. If you select "No", the verification task will not be executed for the time being. You can manage the verification task later on the verification task management page.

8. The data verification method for a multi-source heterogeneous database according to claim 7, characterized in that: The verification scheduling center will trigger the verification task executor according to the set Cron based on the selected verification tasks, select the executable nodes, and verify the data in each data source database in turn according to the verification rules; The verification rules are based on the preset SQL statements and filter conditions stored in the database. The parameters of the preset SQL statements are obtained by the filter condition field, and the filter condition input parameter time field is used to verify the field within the specified time range.

9. A data verification device for a multi-source heterogeneous database, characterized in that: include: at least one memory and at least one processor; The at least one memory is configured to store a machine-readable program; The at least one processor is configured to call the machine-readable program to execute the method according to any one of claims 1 to 8.

Images