TWI860074B - Data processing method and data processing device - Google Patents

Abstract

A data processing method applied in a data center is provided. The data processing method includes obtaining stored procedures of a development environment, a data quality assurance system environment and a production environment from the data center, generating table lineage graphs of the development environment, the data quality assurance system environment and the production environment respectively according to the stored procedures of the development environment, the data quality assurance system environment and the production environment, wherein the step including analyzing each stored procedure to determine a directed graph of each stored procedure for each environment and merging the directed graphs of all stored procedures in each environment to generate a table lineage graph for each environment, comparing the table lineage graphs of the development environment, the data quality assurance system environment and the production environment to generate a comparison result, and performing a notification function according to the comparison result.

Description

Data processing method and data processing device

The present invention relates to a data processing method and a data processing device, and more particularly to a data processing method and a data processing device capable of instantly comparing the differences in blood relationship between data tables in different environments.

The data center usually includes data tables and stored procedures. Data tables can be used as data carriers to store data. Stored procedures are responsible for the conversion and related logic between data tables and data. When the software engineers or developers in the data center modify the logical relationship between data tables and data, they need to modify the stored procedures. Since the number of stored procedures in the data center is huge and the logic between them is interrelated, when the software engineers in the data center add or modify a stored procedure, it may cause changes to the blood relationship of the data tables in the data center. The existing verification method is to perform sampling verification on the data before and after the stored procedure is modified. However, this verification method is time-consuming and cannot confirm complete correctness. Moreover, many errors often rely on data users to discover anomalies and feedback to the software engineers in the data center. However, erroneous data will cause companies to suffer potential and unassessed losses, so modifying stored procedures is a very dangerous job for software engineers in the data center. And as the number of stored procedures increases, the risk of modification will increase proportionally, which will affect the speed of the company's digital transformation. Therefore, the existing technology really needs to be improved.

In order to solve the above problems, the present invention provides a data processing method and a data processing device that can instantly compare the differences in blood relationship between data tables in different environments to solve the above problems.

The present invention provides a data processing method for a data center, comprising: obtaining a development environment, a data quality verification environment, and a pre-stored program of a formal environment from the data center; generating a data table lineage relationship graph of the development environment, the data quality verification environment, and the formal environment according to the pre-stored programs of the development environment, the data quality verification environment, and the formal environment, wherein the step includes: for each environment in the development environment, the data quality verification environment, and the formal environment, parsing each pre-stored program to determine a directed graph of each pre-stored program; and merging A directed graph of all stored programs in each environment is used to generate a data table lineage relationship graph for each environment, wherein the steps include merging a first node of the source data table representing the first stored program and a second node of the target data table representing the second stored program when it is determined that a source data table of the first stored program and a target data table of the second stored program are the same data table; comparing the data table lineage relationship graphs of the development environment, the data quality verification environment and the formal environment to generate a comparison result; and executing a notification function based on the comparison result.

The present invention provides a data processing device for use in a data center, comprising: a storage device for storing instructions; and a processing circuit configured to execute the instructions, wherein the instructions include: obtaining a development environment, a data quality verification environment, and a pre-stored program of a formal environment from the data center; generating a data table blood relationship diagram of the development environment, the data quality verification environment, and the formal environment according to the pre-stored programs of the development environment, the data quality verification environment, and the formal environment, wherein the step includes: parsing each pre-stored program for each environment in the development environment, the data quality verification environment, and the formal environment; sequence to determine the directed graph of each stored procedure; and merge the directed graphs of all stored procedures in each environment to generate a data table lineage relationship graph for each environment, wherein the step includes merging a first node of the source data table representing the first stored procedure with a second node of the target data table representing the second stored procedure when it is determined that a source data table of a first stored procedure and a target data table of a second stored procedure are the same data table; comparing the data table lineage relationship graphs of the development environment, the data quality verification environment, and the formal environment to generate a comparison result; and executing a notification function based on the comparison result.

The embodiment of the present invention can be applied to a data center or a database system. In order to prevent errors caused by adding or modifying stored programs, the embodiment of the present invention can establish a development environment, a data quality assurance system environment, and a formal environment in the data center. The development environment can be used to add or modify stored programs and confirm that the stored programs can be run normally after the addition or modification without any coding errors. In the development environment, developers or software engineers can load original data and stored programs and add or modify the stored programs according to requirements. After adding or modifying the stored programs, the modified new version of the data and stored programs in the development environment can be copied to the data quality assurance environment. The data quality verification environment allows data users to confirm whether the data changes meet their requirements after adding or modifying stored procedures. In the data quality verification environment, data users are provided with various testing methods to conduct tests to determine whether the data changes meet their requirements. When data users find any problems, flaws or defects during testing, developers or software engineers can process the problems found in the data quality verification environment in the development environment, and then copy the processed data and stored procedures to the data quality verification environment to conduct related tests again until the expected results are obtained or no problems occur. The formal environment is a truly operational data center environment. Therefore, when the data quality verification environment completes the data user test and confirms that the data changes meet the requirements, the data center developers or software engineers can synchronize the modified new version of the stored program to the formal environment.

Please refer to FIG. 1, which is a schematic diagram of a process 10 of a data processing method according to an embodiment of the present invention. Process 10 includes the following steps:

Step S100: Start.

Step S102: Obtain the stored procedures of the development environment, data quality verification environment, and formal environment from the data center.

Step S104: Generate data table lineage relationship diagrams of the development environment, data quality verification environment, and formal environment respectively according to the pre-stored procedures of the development environment, data quality verification environment, and formal environment.

Step S106: Compare the data table lineage relationship diagrams of the development environment, the data quality verification environment, and the formal environment to generate a comparison result.

Step S108: Execute a notification function according to the comparison result.

Step S110: End.

According to process 10, in step S102, the embodiment of the present invention can obtain the pre-stored programs of the development environment, the pre-stored programs of the data quality verification environment, and the pre-stored programs of the formal environment from the data center. Then, in step S104, the data table lineage relationship graphs of the development environment, the data quality verification environment, and the formal environment are generated respectively according to the pre-stored programs of the development environment, the pre-stored programs of the data quality verification environment, and the pre-stored programs of the formal environment. In one embodiment, the data table lineage relationship graph of the development environment is generated according to the pre-stored programs of the development environment. Each pre-stored program of the development environment can be parsed to determine the directed graph of each pre-stored program. For example, a stored procedure parsing engine can be used to parse stored procedures and generate a directed graph of stored procedures. For example, the SQL parser parsing engine of Python program software can be used to parse stored procedures and generate a directed graph of stored procedures. Each stored procedure may include at least one source data table and at least one target data table. Each stored procedure may only include at least one source data table. Each stored procedure may also only include at least one target data table. The directed graph of stored procedures may include nodes used to represent source data tables and/or target data tables. The directed graph of stored procedures may include directed edges used to represent stored procedures, and the direction of the directed edges is from the source data table to the target data table. Each stored procedure may correspond to at least one directed graph.

For example, please refer to FIG. 2. As shown in the left half of FIG. 2, the first stored procedure is INT_CUSTOMER_SATISFACTION_SP.STOREDPROCEDURE.SQL. A directed graph of the first stored procedure includes a node 202, a node 206, and a directed edge 204. Node 202 is used to represent a target data table of the first stored procedure, wherein the target data table of the first stored procedure is the data table TP1_INT_CUSTOMER_SATISFACTION. Node 206 is used to represent a source data table of the first stored procedure, wherein the source data table of the first stored procedure is the data table ODS_CUSTOMER_SATISFACTION. Directed edge 204 is used to represent the aforementioned first stored procedure, wherein the direction of directed edge 204 is from node 202 of the source data table to node 206 of the target data table. Please continue to refer to Figure 2. As shown in the left half of Figure 2, the second stored procedure is ODS_CUSTOMER_SATISFACTION_SP.STOREDPROCEDURE.SQL, and a directed graph of the second stored procedure includes node 208, node 212, and directed edge 210. Node 208 is used to represent a target data table of the second stored procedure, wherein the target data table of the second stored procedure is the data table ODS_CUSTOMER_SATISFACTION. Node 212 is used to represent a source data table of the second stored procedure, wherein the source data table of the second stored procedure is the data table TP1_ODS_CUSTOMER_SATISFACTION. Directed edge 208 is used to represent that the direction of directed edge 208 of the second stored procedure is from node 212 of the source data table to node 208 of the target data table.

In step S104, after parsing the directed graph of all stored programs in the development environment, a data table lineage graph of the development environment can be generated by merging the directed graphs of all stored programs in the development environment. The data table lineage graph can represent the relationship between data tables. For example, the directed graphs of all stored programs in the development environment can be compared. When it is determined that a source data table of a stored program and a target data table of another stored program are the same data table, the nodes used to represent the source data table of the stored program and the nodes used to represent the target data table of another stored program can be merged to form a corresponding data table lineage graph. For example, as shown in the left half of FIG. 2, when it is determined that the source data table of the first stored program and the target data table of the second stored program are the same data table (i.e., data table ODS_CUSTOMER_SATISFACTION_CUSTOMER_SATISFACTION), the node 206 and the node 208 can be merged to form a node 214, so as to combine the two directed graphs to form a part of the data table lineage relationship graph. In this case, the node 214 is used to represent the source data table of the first stored program and the target data table of the second stored program, and the directed graph of the first stored program and the directed graph of the second stored program are connected and merged. Through the aforementioned merging method, the directed graphs in all stored programs of the development environment can be merged and converted into the data table lineage relationship graph of the development environment. The data table blood relationship graph may include at least one source directed subgraph and/or at least one target directed subgraph. The source directed subgraph may be a directed subgraph with a data table as the end point. The target directed subgraph may be a directed subgraph with a data table as the starting point. For example, please refer to Figure 3, which is a schematic diagram of the source directed subgraph in the data table blood relationship graph of an embodiment of the present invention. The middle part of Figure 3 shows the source directed subgraph 302 corresponding to the data table INT_CUSTOMER_SATISFACTION. The node used to represent the data table INT_CUSTOMER_SATISFACTION in the source directed subgraph 302 is the end node. The source directed subgraph 302 includes parent nodes related to the data table INT_CUSTOMER_SATISFACTION. For example, please refer to FIG. 4, which is a schematic diagram of a target directed subgraph in a data table blood relationship graph of an embodiment of the present invention. The middle portion of FIG. 4 shows a target directed subgraph 402 corresponding to the data table INT_CUSTOMER_SATISFACTION. The node used to represent the data table INT_CUSTOMER_SATISFACTION in the target directed subgraph 402 is a starting node. The target directed subgraph 402 includes child nodes related to the data table INT_CUSTOMER_SATISFACTION.

Similarly, according to the aforementioned method of generating a data table lineage graph of a development environment, a data table lineage graph of a data quality verification environment can be generated based on the stored procedures of the data quality verification environment. Each stored procedure of the data quality verification environment can be parsed to determine the directed graph of each stored procedure, and after parsing the directed graphs of all stored procedures of the data quality verification environment, the directed graphs of all stored procedures of the data quality verification environment can be merged to generate a data table lineage graph of the data quality verification environment. Similarly, a data table lineage graph of a formal environment can be generated based on the stored procedures of the formal environment. By parsing each stored program in the formal environment, the directed graph of each stored program can be determined. After parsing the directed graphs of all stored programs in the formal environment, the directed graphs of all stored programs in the formal environment can be merged to generate a data table blood relationship graph of the formal environment.

In one embodiment, after the data table lineage diagram of the development environment, the data quality verification environment, and the formal environment is generated, an input menu can be generated for the user to input and select. For example, please continue to refer to FIG. 3 and FIG. 4, such as the input menus 304 and 404 shown in FIG. 3 and FIG. 4. The user can select the desired environment type, data table type (schema), data table name and other fields through the input menu. For example, you can click the item "DEV" in the environment type field to select the development environment, click the item "INT" in the table type field to select the INT table, and click the item "INT_CUSTOMER_SATISFACTION" in the table name field to select the INT_CUSTOMER_SATISFACTION table. This embodiment can visually display the input menu for the user to view and input the items to be selected, and visually display the relevant information of the selected table blood relationship diagram. As shown in FIG. 3 and FIG. 4, the present embodiment can visually display an input menu for the user to view and input the desired item, and visually display the content of the source directed subgraph of the selected data table and the corresponding stored procedure, source data table, target data table and other information. In this way, it can provide users (such as administrators or engineers of the notification data center) with a quick and clear understanding of the stored procedure data structure relationship in the corresponding environment.

In step S106, the data table lineage relationship diagrams of the development environment, the data quality verification environment, and the formal environment may be compared to generate a comparison result. For example, the data table lineage relationship diagrams of at least two environments among the development environment, the data quality verification environment, and the formal environment may be compared to generate a comparison result. For example, the data table lineage relationship diagrams of any two environments among the development environment, the data quality verification environment, and the formal environment may be compared to generate a comparison result. For example, the data table lineage relationship diagrams of the development environment and the data quality verification environment may be compared to generate a comparison result. Compare the data table lineage relationship graph between the data quality verification environment and the formal environment to generate comparison results. Compare the data table lineage relationship graph between the development environment and the formal environment to generate comparison results. In addition, you can also select specific data tables for comparison to generate comparison results. For example, compare the source directed subgraph or target directed subgraph corresponding to at least one data table in the data table lineage relationship graph of any two environments among the development environment, the data quality verification environment, and the formal environment to generate comparison results. For example, common types of data tables include DM data tables, INT data tables, ODS data tables, and STAGE data tables. A data table whose name starts with DM is called a DM data table, for example, data table DM_XXXX. A data table whose name starts with INT is called an INT data table, and so on. The DM data table is usually located at the end point of the data table lineage relationship. Since the DM data table is usually the end point data table in the data table lineage relationship, if there are differences and inconsistencies between two environments, they can be quickly and easily compared. In one embodiment, the source directed subgraph corresponding to the DM data table of the data table lineage relationship diagram of any two environments among the development environment, the data quality verification environment, and the formal environment can be compared to generate a comparison result.

In step S108, a notification function can be executed according to the comparison result of step S106. When the comparison result shows that there are differences in the data table blood relationship graphs of any two environments, the embodiment of the present invention can generate and send a notification signal to remind the administrator or engineer of the data center to execute the notification function. For example, Figure 5 shows a source directed subgraph corresponding to the data table INT_CUSTOMER_X in the data table blood relationship graphs of the development environment and the data quality verification environment, respectively. Figure 6 shows a source directed subgraph corresponding to the data table INT_CUSTOMER_X in the data table blood relationship graphs of the data quality verification environment and the formal environment, respectively. As shown in Figures 5 and 6, in the development environment, data quality verification environment, and formal environment, the data table INT_CUSTOMER_X is the end point of the source directed subgraph. After comparing the data quality verification environment with the development environment, the comparison results show that the data quality verification environment and the development environment have the same source directed subgraph, while the source directed subgraph of the formal environment is different from the development environment and the data quality verification environment. As shown in FIG. 5, in step S106, the software engineer selects the development environment and the data quality verification environment for comparison, and the comparison result shows that the source directed subgraph corresponding to the data table INT_CUSTOMER_X of the development environment is the same as the source directed subgraph corresponding to the data table INT_CUSTOMER_X of the data quality verification environment. As shown in FIG. 6, when the software engineer selects the formal environment and the data quality verification environment for comparison in step S106, the comparison result shows that the source directed subgraph corresponding to the data table INT_CUSTOMER_X of the formal environment is different from the source directed subgraph corresponding to the data table INT_CUSTOMER_X of the data quality verification environment. As shown in Figure 6, the source table of the stored procedure INT_CUSTOMER_X_SP.STOREDPROCEDURE.SQL corresponding to level 0 is inconsistent. More specifically, in the production environment, the source table of the stored procedure INT_CUSTOMER_X_SP.STOREDPROCEDURE.SQL corresponding to level 0 is the table ODS_CUSTOMER_X. However, in the data quality verification environment, the source table of the stored procedure INT_CUSTOMER_X_SP.STOREDPROCEDURE.SQL corresponding to level 0 is the table TP1_INT_CUSTOMER_X. This also means that when the data and stored procedures in the quality verification environment are synchronized and updated to the production environment, the update operation may be abnormal or the update operation has not yet been executed. In this case, the comparison result shows that there are differences in the source directed subgraphs in the data table lineage relationship graphs of the formal environment data and the quality verification environment. Based on the comparison result showing that there are differences between the formal environment data and the quality verification environment, a notification signal can be generated and sent to notify the administrator or engineer of the data center to perform the notification function. In this way, the administrator or engineer of the data center can easily and immediately identify whether there is an error based on the notification signal.

Therefore, during the application service development process, when software engineers modify stored procedures in the development environment, they can compare the table lineage relationship diagram of the development environment with the table lineage relationship diagram of the data quality verification environment to determine whether there is a difference between the two, so as to confirm the correctness of the overall system after the stored procedures are modified and greatly improve the timeliness of application service development. During the application service development process, when the modified stored procedures are synchronized to the formal environment, software engineers can compare the table lineage relationship diagram of the formal environment with the data quality verification environment to determine whether there is a difference between the two, so as to confirm whether the synchronization update of the formal environment is performed correctly. When an error occurs in the stored program that has been modified during synchronization with the formal environment, the embodiment of the present invention will be able to quickly find the affected stored program. In one embodiment, a monitoring cycle can be set, such as every day, every week, or every certain time, so that the data center executes the steps of process 10 every monitoring cycle to compare the data table lineage relationship diagram in the development environment, the data quality verification environment, and the formal environment, and the comparison result is sent back to the data center, thereby realizing an automated test and notification mechanism for the software engineer of the data center and avoiding the occurrence of manual input errors by personnel to achieve complete test automation. At the same time, automated testing will also greatly improve the efficiency of the production test process.

A person of ordinary skill in the art may combine, modify or change the above-described embodiments according to the spirit of the present invention, but is not limited thereto. All of the above statements, steps, and/or processes (including recommended steps) may be implemented through hardware, software, firmware (i.e., a combination of hardware devices and computer instructions, where the data in the hardware devices are read-only software data), electronic systems, or a combination of the above devices. The hardware may include analog, digital, and hybrid circuits (i.e., microcircuits, microchips, or silicon chips). For example, the hardware may be an application-specific integrated circuit (ASIC), a field programmable gate array (FPGA), a programmable logic element, a coupled hardware element, or a combination of the above hardware. In other embodiments, the hardware may include a general purpose processor, a microprocessor, a controller, a digital signal processor (DSP), or a combination of the above hardware. The software may be a combination of program codes, a combination of instructions, and/or a combination of functions (functionality) stored in a storage device, such as a computer-readable recording medium or a non-transitory computer-readable medium. For example, the computer-readable recording medium may include read-only memory (ROM), flash memory (Flash Memory), random access memory (RAM), subscriber identity module (SIM), hard disk, floppy disk or optical disk read-only memory (CD-ROM/DVD-ROM/BD-ROM), but is not limited thereto. The embodiment of the present invention may include a data processing device applied to a data center, the data processing device including a processing circuit and a storage device. The process steps and embodiments of the present invention may be compiled into a program code or instruction form and stored in the storage device of the data processing device. The processing circuit of the data processing device can be used to read and execute the program code or instructions stored in the storage device to implement all the aforementioned steps and functions.

In summary, the data processing method of the embodiment of the present invention can obtain the data table lineage relationship diagram of each environment. When the software engineer is developing application services for the data center, he can instantly compare the differences in the data table lineage relationships of different environments, which will effectively improve the system accuracy and timeliness when modifying the stored program. At the same time, it can also confirm whether the modified stored program is synchronized with the formal environment, thereby effectively improving and optimizing the speed of the company's digital transformation. The above is only a preferred embodiment of the present invention. All equal changes and modifications made according to the scope of the patent application of the present invention should be covered by the present invention.

10: Process 202,206,208,212,214: Nodes 204,210: Directed edges 302: Source directed subgraph 304,404: Input menu 402: Target directed subgraph S100,S102,S104,S106,S108,S110: Steps

FIG. 1 is a flowchart of the data processing method of the embodiment of the present invention. FIG. 2 is a schematic diagram of the directed graph of the stored program of the embodiment of the present invention. FIG. 3 is a schematic diagram of the source directed subgraph in the data table kinship graph of the embodiment of the present invention. FIG. 4 is a schematic diagram of the target directed subgraph in the data table kinship graph of the embodiment of the present invention. FIG. 5 is a comparative schematic diagram of the data table kinship graph of the development environment and the data quality verification environment of the embodiment of the present invention. FIG. 6 is a comparative schematic diagram of the data table kinship graph of the data quality verification environment and the formal environment of the embodiment of the present invention.

10: Process

S100, S102, S104, S106, S108, S110: Steps

Claims (10)

A data processing method for a data center, comprising: Obtaining a development environment, a data quality verification environment, and a pre-stored program of a formal environment from the data center; Generating a data table lineage relationship graph of the development environment, the data quality verification environment, and the formal environment respectively according to the pre-stored programs of the development environment, the data quality verification environment, and the formal environment, wherein the step comprises: For each environment in the development environment, the data quality verification environment, and the formal environment, parsing each pre-stored program to determine the directed graph of each pre-stored program; and Merge the directed graphs of all stored programs in each environment to generate a data table lineage graph for each environment, wherein the step includes merging a first node of the source data table representing the first stored program with a second node of the target data table representing the second stored program when it is determined that a source data table of a first stored program and a target data table of a second stored program are the same data table; Compare the data table lineage graphs of the development environment, the data quality verification environment, and the formal environment to generate a comparison result; and Based on the comparison result, execute a notification function. The data processing method as described in claim 1, wherein the step of comparing the data table lineage relationship diagram of the development environment, the data quality verification environment, and the formal environment to generate the comparison result includes: Comparing the data table lineage relationship diagram of at least two environments among the development environment, the data quality verification environment, and the formal environment to generate the comparison result. The data processing method as described in claim 1, wherein the step of comparing the data table lineage relationship graph of the development environment, the data quality verification environment, and the formal environment to generate the comparison result includes: Comparing the source directed subgraph or the target directed subgraph corresponding to at least one data table of the data table lineage relationship graph of at least two environments among the development environment, the data quality verification environment, and the formal environment to generate the comparison result. The data processing method as described in claim 1, wherein the step of executing the notification function according to the comparison result includes: When the comparison result shows that there is a difference in the data table blood relationship diagram of any two environments among the development environment, the data quality verification environment and the formal environment, a notification signal is generated and sent to execute the notification function. The data processing method as described in claim 1 further comprises: generating an input menu for input selection and presenting the input menu and the related information of the blood relationship diagram of the data table selected through the input menu in a visual manner. A data processing device, used in a data center, comprises: A storage device for storing instructions; and A processing circuit configured to execute the instructions, wherein the instructions include: Obtaining a development environment, a data quality verification environment, and a pre-stored program of a formal environment from the data center; Generating a data table blood relationship graph of the development environment, the data quality verification environment, and the formal environment respectively according to the pre-stored programs of the development environment, the data quality verification environment, and the formal environment, wherein the step comprises: For each environment in the development environment, the data quality verification environment, and the formal environment, parsing each pre-stored program to determine the directed graph of each pre-stored program; and Merge the directed graphs of all stored programs in each environment to generate a data table lineage graph for each environment, wherein the step includes merging a first node of the source data table representing the first stored program with a second node of the target data table representing the second stored program when it is determined that a source data table of a first stored program and a target data table of a second stored program are the same data table; Compare the data table lineage graphs of the development environment, the data quality verification environment, and the formal environment to generate a comparison result; and Based on the comparison result, execute a notification function. The data processing device as described in claim 6, wherein the instruction further includes: Comparing the data table lineage relationship diagram of at least two environments among the development environment, the data quality verification environment and the formal environment to generate the comparison result. The data processing device as described in claim 6, wherein the instruction further includes: Comparing the source directed subgraph or target directed subgraph corresponding to at least one data table of the data table lineage relationship graph of at least two environments among the development environment, the data quality verification environment, and the formal environment to generate the comparison result. The data processing device as described in claim 6, wherein the instruction further includes: When the comparison result shows that there is a difference in the data table blood relationship diagram of any two environments among the development environment, the data quality verification environment and the formal environment, a notification signal is generated and sent to execute the notification function. The data processing device as described in claim 6, wherein the instruction further includes: generating an input menu for input selection and presenting the input menu and the relevant information of the data table blood relationship diagram selected through the input menu in a visual manner.