Method, device and readable storage medium for determining inter-table redundancy relationship

Abstract

The application provides an inter-table redundancy relationship determination method, device and equipment and a readable storage medium. The method comprises the following steps: obtaining blood relationship set data based on source table primary keys Id and parent table primary keys Id; traversing the blood relationship set data, querying source table blood field primary keys Id and parent table blood field primary keys Id according to the source table primary keys Id and the parent table primary keys Id, and obtaining redundancy detail table result set data according to the query result; traversing the redundancy detail table result set data, determining a reference table primary key Id with the same parent table primary key Id as the source table primary key Id in the current round of loop in the loop; and if the redundancy field proportion corresponding to the source table primary key Id and the reference table primary key Id in the current round of loop is greater than a preset proportion, determining that the data tables corresponding to the two primary keys Id are redundant tables. Through the application, automatic identification of redundant tables is realized, which provides strong assistance for data management.

Description

Technical Field

[0001] This invention relates to the field of data processing, and in particular to a method, apparatus, device, and readable storage medium for determining redundancy relationships between tables. Background Technology

[0002] In today's big data era, data extraction, governance, and management play a crucial role. Data governance generates a large number of tables, and these tables inevitably contain some with similar data sources or structures. Identifying these redundant tables with similar sources is of great help to data governance. Therefore, a solution for identifying redundant tables is urgently needed. Summary of the Invention

[0003] To achieve the above objectives, the present invention provides a method, apparatus, device, and readable storage medium for determining inter-table redundancy relationships.

[0004] In a first aspect, the present invention provides a method for determining inter-table redundancy relationships, the method comprising:

[0005] Based on the primary key Id of the source table and the primary key Id of the parent table that have blood relations, the blood relation set data is obtained;

[0006] Iterate through the bloodline relationship set data, query the primary key Id of the bloodline field in the source table and the primary key Id of the parent table based on the primary key Id of the source table and the primary key Id of the parent table, and obtain the redundant detail table result set data based on the query results;

[0007] Traverse the result set of redundant detail tables, and in the loop determine the primary key Id of the reference table that has the same parent table primary key Id as the primary key Id of the source table in the current loop.

[0008] Calculate the percentage of redundant fields corresponding to the primary key Id of the source table and the primary key Id of the reference table in the current loop;

[0009] If the proportion of redundant fields is greater than a preset ratio, then the data table corresponding to the primary key Id of the source table and the data table corresponding to the primary key Id of the reference table in the current loop are determined to be redundant tables.

[0010] Optionally, the step of calculating the proportion of redundant fields corresponding to the primary key Id of the source table and the primary key Id of the reference table in the current iteration includes:

[0011] Identify duplicate primary key Id fields in the source table and reference table in the current loop.

[0012] The ratio of the number of repeated fields to the total number of fields in the data table corresponding to the primary key Id of the source table in the current round of the loop is used to obtain the percentage of redundant fields.

[0013] Optionally, the step of determining the duplicate fields of the source table primary key Id and the reference table primary key Id in the current round of iteration includes:

[0014] Determine the set of primary key Id fields of the first parent table corresponding to the primary key Id of the source table in the current loop;

[0015] Determine the set of primary key Id fields in the second parent table corresponding to the primary key Id in the reference table;

[0016] The parent table lineage field corresponding to the intersection of the primary key Id set of the first parent table lineage field and the primary key Id set of the second parent table lineage field is the duplicate field.

[0017] Optionally, after determining that the data table corresponding to the primary key Id of the source table and the data table corresponding to the primary key Id of the reference table are redundant tables if the proportion of redundant fields is greater than a preset ratio, the method further includes:

[0018] The associated storage includes the percentage of redundant fields, the primary key Id of the source table in the current loop, and the primary key Id of the reference table.

[0019] Secondly, the present invention also provides an inter-table redundancy relationship determination device, the inter-table redundancy relationship determination device comprising:

[0020] The first construction module is used to obtain the set of bloodline relationship data based on the primary key Id of the source table and the primary key Id of the parent table that have bloodline relationship;

[0021] The second construction module is used to traverse the blood relationship set data, query the blood relationship field primary key Id of the source table and the blood relationship field primary key Id of the parent table based on the primary key Id of the source table and the primary key Id of the parent table, and obtain the redundant detail table result set data based on the query results.

[0022] The determination module is used to traverse the result set data of redundant detail tables and determine the primary key Id of the reference table that has the same parent table primary key Id as the primary key Id of the source table in the current loop.

[0023] The calculation module is used to calculate the proportion of redundant fields corresponding to the primary key Id of the source table and the primary key Id of the reference table in the current loop.

[0024] The analysis module is used to determine that the data table corresponding to the primary key Id of the source table and the data table corresponding to the primary key Id of the reference table are redundant tables if the proportion of the redundant fields is greater than a preset proportion.

[0025] Optionally, the computing module is used for:

[0026] Identify duplicate primary key Id fields in the source table and reference table in the current loop.

[0027] The ratio of the number of repeated fields to the total number of fields in the data table corresponding to the primary key Id of the source table in the current round of the loop is used to obtain the percentage of redundant fields.

[0028] Optionally, the computing module is used for:

[0029] Determine the set of primary key Id fields of the first parent table corresponding to the primary key Id of the source table in the current loop;

[0030] Determine the set of primary key Id fields in the second parent table corresponding to the primary key Id in the reference table;

[0031] The parent table lineage field corresponding to the intersection of the primary key Id set of the first parent table lineage field and the primary key Id set of the second parent table lineage field is the duplicate field.

[0032] Optionally, the inter-table redundancy determination device further includes a storage module for:

[0033] The associated storage includes the percentage of redundant fields, the primary key Id of the source table in the current loop, and the primary key Id of the reference table.

[0034] Thirdly, the present invention also provides an inter-table redundancy relationship determination device, the inter-table redundancy relationship determination device including a processor, a memory, and an inter-table redundancy relationship determination program stored in the memory and executable by the processor, wherein when the inter-table redundancy relationship determination program is executed by the processor, the steps of the inter-table redundancy relationship determination method as described above are implemented.

[0035] Fourthly, the present invention also provides a readable storage medium storing a table redundancy determination program, wherein when the table redundancy determination program is executed by a processor, it implements the steps of the table redundancy determination method as described above.

[0036] In this invention, a family relationship set is obtained based on the primary key Id of the source table and the primary key Id of the parent table, which have a family relationship. This family relationship set is then traversed, and the primary key Ids of the family relationship fields in the source and parent tables are queried based on the primary key Ids of the source and parent tables. A redundant detail table result set is obtained based on the query results. This redundant detail table result set is then traversed, and in a loop, the primary key Id of the reference table that shares the same parent table primary key Id as the primary key Id of the source table in the current iteration is determined. The proportion of redundant fields corresponding to the primary key Id of the source table and the primary key Id of the reference table in the current iteration is calculated. If the proportion of redundant fields is greater than a preset ratio, the data table corresponding to the primary key Id of the source table and the data table corresponding to the primary key Id of the reference table in the current iteration are determined to be redundant tables. This invention automatically identifies all tables in the system with highly similar data sources or table structures, providing powerful assistance for data governance. Attached Figure Description

[0037] Figure 1 This is a flowchart illustrating an embodiment of the method for determining inter-table redundancy relationships according to the present invention.

[0038] Figure 2 This is a functional module diagram of an embodiment of the inter-table redundancy relationship determination device of the present invention;

[0039] Figure 3 This is a schematic diagram of the hardware structure of the device for determining inter-table redundancy relationships in the embodiments of the present invention.

[0040] The realization of the objective, functional features and advantages of the present invention will be further explained in conjunction with the embodiments and with reference to the accompanying drawings. Detailed Implementation

[0041] It should be understood that the specific embodiments described herein are merely illustrative of the invention and are not intended to limit the invention.

[0042] In a first aspect, embodiments of the present invention provide a method for determining inter-table redundancy relationships.

[0043] In one embodiment, reference is made to Figure 1 , Figure 1 This is a flowchart illustrating an embodiment of the method for determining inter-table redundancy relationships according to the present invention. Figure 1 As shown, the methods for determining redundancy relationships between tables include:

[0044] Step S10: Based on the primary key Id of the source table and the primary key Id of the parent table that have a blood relationship, obtain the blood relationship set data;

[0045] In this embodiment, before step S10, the lineage relationship between any two tables in the system is generated using syntaxes such as insertoverwrite..select, createtable..asselect, or inserttinto..select, and key data such as the source table primary key Id (sourceTableId), parent table primary key Id (parentTableId), source table lineage field primary key Id (sourceFieldId), and parent table lineage field primary key Id (parentFieldId) of the tables with the lineage relationship are saved.

[0046] Based on the analysis of the lineage relationships between tables, the sourceTableId and parentTableId with lineage relationships are queried from the lineage data. A group by query is then performed based on sourceTableId and parentTableId, and the query results are assembled into a lineage relationship set data, which is then used as a List. <bloodtableidvo>This indicates that the BloodTableIdVO entity class contains sourceTableId and parentTableId properties.

[0047] Step S20: Traverse the bloodline relationship set data, query the primary key Id of the bloodline field in the source table and the primary key Id of the parent table based on the primary key Id of the source table and the primary key Id of the parent table, and obtain the redundant detail table result set data based on the query results;

[0048] In this embodiment, the blood relationship set data List obtained in step S10 is traversed. <bloodtableidvo>Based on `sourceTableId` and `parentTableId`, query the primary key `Id` of the lineage field in the source table and the primary key `Id` of the lineage field in the parent table, assemble them into a redundant detail table result set, and use a List to... <rymxbresult>This indicates that the RymxbResult entity class contains the following attributes: sourceTableId, parentTableId, and a collection of redundant detail field results, represented by a List. <rymxzdvo>RymxZdVO entity class contains attributes: sourceFieldId and parentFieldId.

[0049] For example, since the sourceTableId and parentTableId are known, all sourceFieldId and parentFieldId between the two tables can be found as query conditions according to the two fields. For example: the sourceTableId of table A (source table) is 02022060715411040000000101001113, and the parentTableId of table B (parent table) is 02022060711221075300000101001703, so the blood relationship between the 4 fields of table A and table B can be found:

[0050] The source table blood field 02022060715411049000000101001114 corresponds to the parent table blood field 02022060711221075700000101001704, and the other three fields also have one-to-one blood relationship, which will not be described here.

[0051] Step S30, traverse the redundancy detail table result set data, and determine the reference table primary key Id with the same parent table primary key Id as the source table primary key Id in the current round loop in the loop;

[0052] In this embodiment, the List <rymxbresult>In the loop, find the table primary key Id that has the same parent table primary key Id as the source table primary key Id in the current round of the loop, that is, the reference table primary key Id. It is easy to understand that the reference table primary key Id has one or more.

[0053] In step S40, the redundancy field proportion corresponding to the source table primary key Id and the reference table primary key Id in the current round of the loop is calculated.

[0054] In step S50, if the redundancy field proportion is greater than a preset proportion, it is determined that the data table corresponding to the source table primary key Id in the current round of the loop and the data table corresponding to the reference table primary key Id are redundant tables.

[0055] In the embodiment, it is assumed that there are multiple reference table primary keys Id, and the redundancy field proportion corresponding to the source table primary key Id and each reference table primary key Id in the current round of the loop is calculated, for example, the redundancy field proportion corresponding to the source table primary key Id and the first reference table primary key Id in the current round of the loop is calculated, which is denoted as redundancy field proportion 1; the redundancy field proportion corresponding to the source table primary key Id and the second reference table primary key Id in the current round of the loop is calculated, which is denoted as redundancy field proportion 2, and so on.

[0056] If the redundancy field proportion n is greater than the preset proportion, it is determined that the data table corresponding to the source table primary key Id in the current round of the loop and the data table corresponding to the nth reference table primary key Id are redundant tables. The preset proportion is a preset value, which is set according to actual needs.

[0057] In the embodiment, based on the source table primary key Id and the parent table primary key Id having blood relationship, the blood relationship set data is obtained; the blood relationship set data is traversed, the source table blood field primary key Id and the parent table blood field primary key Id are queried according to the source table primary key Id and the parent table primary key Id, and the redundancy detail table result set data is obtained according to the query result; the redundancy detail table result set data is traversed, and in the loop, the reference table primary key Id that has the same parent table primary key Id as the source table primary key Id in the current round of the loop is determined; the redundancy field proportion corresponding to the source table primary key Id and the reference table primary key Id in the current round of the loop is calculated; if the redundancy field proportion is greater than a preset proportion, it is determined that the data table corresponding to the source table primary key Id in the current round of the loop and the data table corresponding to the reference table primary key Id are redundant tables. Through the embodiment, all tables with high similarity in data source or table structure source in the system are automatically determined, which provides strong assistance for data governance.

[0058] Further, in an embodiment, step S40 includes:

[0059] In step S401, the repeated fields of the source table primary key Id and the reference table primary key Id in the current round of the loop are determined.

[0060] In this embodiment, if the source table primary key Id and the reference table primary key Id in the current round have the same parent table blood relationship field parentFieldId, the same parent table blood relationship field is taken as the repeated field, and a repeated field set is obtained. And a Map<String, Map<String, List<String>> is assembled according to the source table primary key Id, the reference table primary key Id and the repeated field set in the current round. <string>>> structure data, key: source table primary key Id in current round loop; Map<String, List <string>key is the reference table primary key Id, List <string>The repeated field Id set is determined.

[0061] Further, in an embodiment, step S401 comprises:

[0062] The first parent table blood relation field primary key Id set corresponding to the source table primary key Id in the current round is determined, the second parent table blood relation field primary key Id set corresponding to the reference table primary key Id is determined, and the parent table blood relation field corresponding to the intersection of the first parent table blood relation field primary key Id set and the second parent table blood relation field primary key Id set is taken as the repeated field.

[0063] In the embodiment, all parent table blood relation field primary keys corresponding to the source table primary key Id in the current round are determined, and are recorded as the first parent table blood relation field primary key Id set. All parent table blood relation field primary keys corresponding to the reference table primary key Id are determined, and are recorded as the second parent table blood relation field primary key Id set. The intersection of the first parent table blood relation field primary key Id set and the second parent table blood relation field primary key Id set is the same parent table blood relation field corresponding to the source table primary key Id and the reference table primary key Id in the current round, that is, the repeated field.

[0064] In step S402, the ratio of the number of the repeated field to the total number of fields of the data table corresponding to the source table primary key Id in the current round is calculated, to obtain the redundant field proportion.

[0065] In the embodiment, after the repeated field is determined, the ratio of the number of the repeated field to the total number of fields of the data table corresponding to the source table primary key Id in the current round is calculated, to obtain the redundant field proportion.

[0066] Further, in an embodiment, after step S50, the method further comprises:

[0067] The redundant field proportion, the source table primary key Id in the current round, and the reference table primary key Id are associated and stored.

[0068] In the embodiment, the redundant field proportion, the source table primary key Id in the current round, and the reference table primary key Id are associated and stored, which facilitates subsequent query of the storage record by relevant personnel, so that it is known which data tables are redundant tables for each other and what the redundant proportion is, thereby providing strong assistance for data management.

[0069] In a second aspect, an embodiment of the present application further provides a table inter-redundancy relationship determining device.

[0070] In an embodiment, the table inter-redundancy relationship determining device comprises: Figure 2 , Figure 2 FIG. 1 is a functional module schematic diagram of an embodiment of the table inter-redundancy relationship determining device of the present application. As shown in FIG. 1, the table inter-redundancy relationship determining device comprises: Figure 2

[0071] ​The first construction module 10 is configured to obtain blood relationship set data based on the source table primary key Id and the parent table primary key Id having blood relationship;

[0072] The second construction module 20 is configured to traverse the blood relationship set data, query the source table blood relationship field primary key Id and the parent table blood relationship field primary key Id according to the source table primary key Id and the parent table primary key Id, and obtain the redundant detail table result set data according to the query result;

[0073] The determination module 30 is configured to traverse the redundant detail table result set data, and determine the reference table primary key Id having the same parent table primary key Id as the source table primary key Id in the current round in the loop;

[0074] The calculation module 40 is configured to calculate the redundant field proportion corresponding to the source table primary key Id and the reference table primary key Id in the current round;

[0075] The analysis module 50 is configured to determine that the data table corresponding to the source table primary key Id and the data table corresponding to the reference table primary key Id are redundant tables to each other if the redundant field proportion is greater than a preset proportion.

[0076] Further, in an embodiment, the calculation module 40 is configured to:

[0077] Determine the repeated field of the source table primary key Id and the reference table primary key Id in the current round;

[0078] Calculate the ratio of the number of the repeated field to the total number of fields of the data table corresponding to the source table primary key Id in the current round to obtain the redundant field proportion.

[0079] Further, in an embodiment, the calculation module 40 is configured to:

[0080] Determine the first parent table blood relationship field primary key Id set corresponding to the source table primary key Id in the current round;

[0081] Determine the second parent table blood relationship field primary key Id set corresponding to the reference table primary key Id;

[0082] Take the parent table blood relationship field corresponding to the intersection of the first parent table blood relationship field primary key Id set and the second parent table blood relationship field primary key Id set as the repeated field.

[0083] Further, in an embodiment, the inter-table redundant relationship determination apparatus further comprises a storage module configured to:

[0084] Correlation store the redundant field proportion, the source table primary key Id and the reference table primary key Id in the current round.

[0085] The functions of each module in the inter-table redundancy relationship determining apparatus correspond to the steps in the inter-table redundancy relationship determining method, and the functions and implementation processes will not be repeated here.

[0086] In a third aspect, an embodiment of the present application provides an inter-table redundancy relationship determining apparatus, which can be a personal computer (PC), a notebook computer, a server, or the like device having a data processing function.

[0087] Referring to Figure 3 , Figure 3 FIG. 1 is a schematic diagram of a hardware structure of an inter-table redundancy relationship determining apparatus according to an embodiment of the present application. In the embodiment of the present application, the inter-table redundancy relationship determining apparatus can include a processor 1001 (for example, a central processing unit (CPU)), a communication bus 1002, a user interface 1003, a network interface 1004, and a memory 1005. The communication bus 1002 is used to realize the connection and communication between the components; the user interface 1003 can include a display and an input unit such as a keyboard; the network interface 1004 can optionally include a standard wired interface, a wireless interface (for example, a wireless fidelity (WI-FI) interface); the memory 1005 can be a high-speed random access memory (RAM) or a stable memory (for example, a disk memory), and the memory 1005 can optionally be a storage device independent of the aforementioned processor 1001. Those skilled in the art can understand that the hardware structure shown in FIG. 1 does not constitute a limitation on the present application, and can include more or fewer components than those shown in the figure, or combine certain components, or arrange different components. Figure 3 The hardware structure shown in FIG. 1 does not constitute a limitation on the present application, and can include more or fewer components than those shown in the figure, or combine certain components, or arrange different components.

[0088] Continuing to refer to Figure 3 , Figure 3 The memory 1005 as a computer storage medium in the embodiment of the present application can include an operating system, a network communication module, a user interface module, and an inter-table redundancy relationship determining program. The processor 1001 can call the inter-table redundancy relationship determining program stored in the memory 1005, and execute the inter-table redundancy relationship determining method provided by the embodiment of the present application.

[0089] In a fourth aspect, an embodiment of the present application further provides a readable storage medium.

[0090] The present application can store an inter-table redundancy relationship determining program on a readable storage medium, wherein the inter-table redundancy relationship determining program, when executed by a processor, implements the steps of the inter-table redundancy relationship determining method as described above.

[0091] The method implemented when the inter-table redundancy relationship determining program is executed can refer to the embodiments of the inter-table redundancy relationship determining method of the present application, and will not be described here.

[0092] It should be noted that in this document, the terms "comprising", "containing", or any other variant thereof are intended to cover non-exclusive inclusions, such that a process, method, article, or system that comprises a list of elements does not only include those elements, but also other elements not explicitly listed, or other elements inherent to such a process, method, article, or system. Without more limitations, an element defined by the phrase "comprising a" does not exclude the presence of additional identical elements in the process, method, article, or system that includes the element.

[0093] The above-mentioned embodiment numbers of the present application are only for description, and do not represent the advantages or disadvantages of the embodiments.

[0094] From the above description of the embodiments, those skilled in the art can clearly understand that the above-mentioned embodiment methods can be implemented by means of software and a necessary general hardware platform, and of course, they can also be implemented by hardware, but in many cases, the former is a better embodiment. Based on this understanding, the technical solutions of the present application or the part that contributes to the prior art can be embodied in the form of a software product, which is stored in a storage medium such as a ROM / RAM, a magnetic disk, or an optical disk, and includes a number of instructions for causing a terminal device to execute the methods described in the embodiments of the present application.

[0095] The above is only a preferred embodiment of the present application, and does not limit the patent scope of the present application, and any equivalent structure or equivalent process transformation, or direct or indirect application in other related technical fields, is also included in the patent protection scope of the present application.< / string> < / string> < / string> < / rymxbresult> < / rymxzdvo> < / rymxbresult> < / bloodtableidvo> < / bloodtableidvo>

Claims

1. A method for determining redundancy relationships between tables, characterized in that, The method for determining inter-table redundancy relationships includes: Based on the primary key Id of the source table and the primary key Id of the parent table that have blood relations, the blood relation set data is obtained; Iterate through the bloodline relationship set data, query the primary key Id of the bloodline field in the source table and the primary key Id of the parent table based on the primary key Id of the source table and the primary key Id of the parent table, and obtain the redundant detail table result set data based on the query results; Traverse the result set of redundant detail tables, and in the loop determine the primary key Id of the reference table that has the same parent table primary key Id as the primary key Id of the source table in the current loop. Determine the set of primary key Id fields of the first parent table corresponding to the primary key Id of the source table in the current loop; Determine the set of primary key Id fields in the second parent table corresponding to the primary key Id in the reference table; The parent table lineage field corresponding to the intersection of the primary key Id set of the first parent table lineage field and the primary key Id set of the second parent table lineage field is the duplicate field; Calculate the ratio of the number of repeated fields to the total number of fields in the data table corresponding to the primary key Id of the source table in the current loop to obtain the percentage of redundant fields; If the proportion of redundant fields is greater than a preset ratio, then the data table corresponding to the primary key Id of the source table and the data table corresponding to the primary key Id of the reference table in the current loop are determined to be redundant tables.

2. The method for determining inter-table redundancy relationships as described in claim 1, characterized in that, After determining that the data table corresponding to the primary key Id of the source table and the data table corresponding to the primary key Id of the reference table are redundant tables if the proportion of redundant fields is greater than a preset proportion, the method further includes: The associated storage includes the percentage of redundant fields, the primary key Id of the source table in the current loop, and the primary key Id of the reference table.

3. A device for determining inter-table redundancy relationships, characterized in that, The inter-table redundancy relationship determination device includes: The first construction module is used to obtain the set of bloodline relationship data based on the primary key Id of the source table and the primary key Id of the parent table that have bloodline relationship; The second construction module is used to traverse the blood relationship set data, query the blood relationship field primary key Id of the source table and the blood relationship field primary key Id of the parent table based on the primary key Id of the source table and the primary key Id of the parent table, and obtain the redundant detail table result set data based on the query results. The determination module is used to traverse the result set data of redundant detail tables and determine the primary key Id of the reference table that has the same parent table primary key Id as the primary key Id of the source table in the current loop. The calculation module is used to determine the set of primary key Ids of the first parent table corresponding to the primary key Id of the source table in the current loop; determine the set of primary key Ids of the second parent table corresponding to the primary key Id of the reference table; take the parent table lineage field corresponding to the intersection of the set of primary key Ids of the first parent table and the set of primary key Ids of the second parent table as the duplicate field; calculate the ratio of the number of duplicate fields to the total number of fields in the data table corresponding to the primary key Id of the source table in the current loop to obtain the proportion of redundant fields; The analysis module is used to determine that the data table corresponding to the primary key Id of the source table and the data table corresponding to the primary key Id of the reference table are redundant tables if the proportion of the redundant fields is greater than a preset proportion.

4. The table redundancy relationship determination device as described in claim 3, characterized in that, The inter-table redundancy determination device also includes a storage module for: The associated storage includes the percentage of redundant fields, the primary key Id of the source table in the current loop, and the primary key Id of the reference table.

5. A device for determining inter-table redundancy relationships, characterized in that, The inter-table redundancy determination device includes a processor, a memory, and an inter-table redundancy determination program stored in the memory and executable by the processor, wherein when the inter-table redundancy determination program is executed by the processor, it implements the steps of the inter-table redundancy determination method as described in any one of claims 1 to 2.

6. A readable storage medium, characterized in that, The readable storage medium stores a table redundancy determination program, wherein when the table redundancy determination program is executed by a processor, it implements the steps of the table redundancy determination method as described in any one of claims 1 to 2.

Images