A redundancy identification method and device

Abstract

The present application relates to the field of data processing, and discloses a redundancy identification method and device. The method comprises: obtaining a target data table pair in a data warehouse; the target data table pair comprises a first data table and a second data table; the first data table and the second data table are data tables in the data warehouse; obtaining a first source field associated with each field in the first data table, and a second source field associated with each field in the second data table; determining the table similarity between the first data table and the second data table according to all the first source fields and all the second source fields; if the table similarity is greater than the similarity threshold, it is determined that there is redundant data between the first data table and the second data table. Applying the technical solution of the present application can improve the redundancy identification efficiency of redundant data in a data warehouse.

Description

Technical Field

[0001] The embodiments of the present invention relate to the field of big data technology, and in particular to a redundancy identification method and device. Background Art

[0002] With the development of the big data industry, most companies will establish their own data warehouse (DW / DWH). Due to the small size of the technical team or the lack of technical capabilities of the technical team in the early stages of enterprise development, the data tables (or data models) in the data warehouses they establish often have unreasonable designs, such as chimney-style construction. As a result, the reusability of the data tables in the data warehouse is not considered enough, resulting in a large amount of data redundancy between different data tables. Secondly, in the process of continuous iteration of the data warehouse, there is a problem that the design ideas of the new and old versions of the data tables are different, which will also cause a large amount of data redundancy between different data tables in the data warehouse. Data redundancy will increase the complexity of using the data warehouse, and the difficulty of data development for the data warehouse will also increase, reducing the user experience.

[0003] Based on this, in order to better use the data warehouse, it is often necessary to eliminate data redundancy, and to eliminate redundancy, redundancy must first be identified. Currently, the way to identify redundancy mostly relies on manual methods. However, there are often thousands of data tables in a company's data warehouse. Using manual methods to identify redundancy not only requires the accumulation of professional knowledge of the identification personnel, but also takes a lot of time, so the identification efficiency is low. Summary of the invention

[0004] In view of the above problems, an embodiment of the present invention provides a redundancy identification method and device, which are used to solve the problem of low redundancy identification efficiency in the prior art.

[0005] In a first aspect, the present application provides a redundancy identification method, which includes: obtaining a target data table pair in a data warehouse; the target data table pair includes a first data table and a second data table; the first data table and the second data table are data tables in the data warehouse; obtaining a first source field associated with each field in the first data table, and a second source field associated with each field in the second data table; determining the table similarity between the first data table and the second data table based on all the first source fields and all the second source fields; if the table similarity is greater than a similarity threshold, it is determined that redundant data exists between the first data table and the second data table.

[0006] In a possible design of the first aspect, obtaining a target data table pair includes: obtaining hierarchical information and subject domain information of multiple data tables in a data warehouse; the hierarchical information is used to indicate the hierarchy of the data table in the data warehouse; the subject domain information is used to indicate the subject category of the data table; determining a target data table pair from multiple data tables based on the hierarchical information and subject domain information of the multiple data tables; the two data tables included in the target data table pair have different hierarchies in the data warehouse; the two data tables included in the target data table pair have different subject categories; the two data tables included in the target data table pair are not a preset data table pair, and the preset data table pair is a predefined data table pair that does not have redundant data.

[0007] In a possible design of the first aspect, obtaining a first source field associated with each field in a first data table and a second source field associated with each field in a second data table includes: obtaining first data lineage information of the first data table and second data lineage information of the second data table; the first data lineage information includes data lineage information of each field in the first data table, and the second data lineage information includes data lineage information of each field in the second data table; according to the first data lineage information, determining a field having a data lineage relationship with the first field as a first source field associated with the first field; the first field is a field in the first data table; according to the second data lineage information, determining a field having a data lineage relationship with the second field as a second source field associated with the second field; the second field is a field in the second data table.

[0008] In a possible design of the first aspect, the table similarity of the first data table and the second data table is determined based on all the first source fields and all the second source fields, including: combining all the first source fields and all the second source fields and removing duplicates to obtain a first array; generating a second array and a third array based on the first array; the number of elements in the second array and the first array are the same; each element in the second array is used to represent the association result between the element at the corresponding position in the first array and the field in the first data table; each element in the third array is used to represent the association result between the element at the corresponding position in the first array and the field in the second data table; the association result is the existence of an association relationship or the absence of an association relationship; determining the group similarity of the second array and the third array; and using the group similarity as the table similarity between the first data table and the second data table.

[0009] In a possible design manner of the first aspect, determining the group similarity between the second data array and the third data array includes: determining the group similarity between the second data array and the third data array according to a preset similarity algorithm; the similarity algorithm includes any one of the following: cosine similarity, Pearson correlation coefficient, Euclidean distance, and Jaccard distance.

[0010] In a possible design of the first aspect, after determining that redundant data exists between the first data table and the second data table, the method further includes: sending a target data table pair and indication information to a management terminal; the indication information is used to indicate that redundant data exists between the first data table and the second data table.

[0011] In a possible design of the first aspect, after sending the target data table pair and indication information to the management terminal, the method also includes: if no response information to the corresponding indication information is received from the management terminal within a first preset time period, obtaining the number of times the first data table and the second data table are called within a preset time period; based on the number of times they are called, determining the data table to be deleted; the data table to be deleted is the first data table or the second data table; and deleting the data table to be deleted.

[0012] In a possible design of the first aspect, a data table to be deleted is determined based on the number of times it is called, including: determining the data table with the smaller number of calls between the first data table and the second data table as the pending data table; if the number of times the pending data table is called is less than a preset threshold, determining the pending data table as the data table to be deleted.

[0013] In a possible design method of the first aspect, based on the number of calls, determining the data table to be deleted also includes: if the number of calls of the first data table and the second data table is the same and both are less than a preset threshold, obtaining characteristic parameters of the first data table and the second data table; the characteristic parameters include any one or more of the following: generation time, data volume; based on the characteristic parameters of the first data table and the second data table, determining the coefficients to be deleted of the first data table and the second data table; determining the data table with the largest coefficient to be deleted in the first data table and the second data table as the data table to be deleted.

[0014] In a possible design of the first aspect, after obtaining the first source field associated with each field in the first data table and the second source field associated with each field in the second data table, the method also includes: if the first source field associated with the third field in the first data table is the same as the second source field associated with the fourth field in the second data table, then the third field and the fourth field are determined as redundant fields.

[0015] Based on the technical solution provided in the embodiment of the present application, when it is necessary to perform redundant identification on two data tables in a data table pair in a data warehouse, the earliest generated source field (i.e., the first source field and the second source field) associated with each field in the two data tables can be obtained first.

[0016] When a data table is generated, the fields in it may be the fields in the previously generated data table that have been converted or processed multiple times. Whether there is redundancy between two different data tables is determined by whether the fields in the two data tables are converted and generated by the same fields. If some fields in the two data tables are converted and generated by the same fields, it indicates that there is redundancy between the two data tables. If there are no fields in the two data tables that are converted and generated by the same fields, it indicates that there is no redundant data in the two data tables. For this reason, you can determine whether there is redundant data between the two data tables by obtaining the source fields associated with the fields in different data tables.

[0017] Therefore, in the present application, the source fields (i.e., the first source field and the second source field) associated with each field in the two data tables can be first obtained. Then, the table similarity of the first data table and the second data table can be accurately determined based on all the first source fields and all the second source fields. The table similarity can indicate the extent to which the number of fields from the same source in the first data table and the second data table is. Finally, if it is determined that the table similarity is greater than a preset similarity threshold, it can be determined that there is redundant data between the first data table and the second data table. It can be seen that based on the technical solution provided by the present application, it can be automatically determined whether there is redundant data between the two data tables. Compared with the prior art, the complex process of manual determination is reduced, manpower and material resources are reduced, and the recognition accuracy and efficiency are improved.

[0018] In a second aspect, the present application provides a redundant identification device, which can be applied to an electronic device to implement the redundant identification method in the first aspect. The functions of the redundant identification device can be implemented by hardware, or by hardware executing corresponding software. The hardware or software includes one or more modules corresponding to the above functions, such as an acquisition module and a processing module.

[0019] Among them, the acquisition module is used to obtain the target data table pair in the data warehouse; the target data table pair includes a first data table and a second data table; the first data table and the second data table are data tables in the data warehouse; the acquisition module is also used to obtain the first source field associated with each field in the first data table, and the second source field associated with each field in the second data table; the processing module is used to determine the table similarity between the first data table and the second data table based on all the first source fields and all the second source fields; the processing module is also used to determine that there is redundant data between the first data table and the second data table if the table similarity is greater than a similarity threshold.

[0020] In a possible design method of the second aspect, the acquisition module is specifically used to: acquire hierarchical information and subject domain information of multiple data tables in a data warehouse; the hierarchical information is used to indicate the level of the data table in the data warehouse; the subject domain information is used to indicate the subject category of the data table; according to the hierarchical information and subject domain information of the multiple data tables, determine a target data table pair from the multiple data tables; the two data tables included in the target data table pair have different levels in the data warehouse; the two data tables included in the target data table pair have different subject categories; the two data tables included in the target data table pair are not a preset data table pair, and the preset data table pair is a predefined data table pair without redundant data.

[0021] In a possible design method of the second aspect, the acquisition module is specifically used to include: acquiring first data lineage information of the first data table and second data lineage information of the second data table; the first data lineage information includes data lineage information of each field in the first data table, and the second data lineage information includes data lineage information of each field in the second data table; according to the first data lineage information, determining the field that has a data lineage relationship with the first field as the first source field associated with the first field; the first field is a field in the first data table; according to the second data lineage information, determining the field that has a data lineage relationship with the second field as the second source field associated with the second field; the second field is a field in the second data table.

[0022] In a possible design method of the second aspect, the processing module is specifically used to: combine all the first source fields and all the second source fields obtained by the acquisition module and remove duplications to obtain a first array; generate a second array and a third array based on the first array; the number of elements in the second array is the same as that in the first array; each element in the second array is used to represent the association result between the element at the corresponding position in the first array and the field in the first data table; each element in the third array is used to represent the association result between the element at the corresponding position in the first array and the field in the second data table; the association result is the existence of an association relationship or the absence of an association relationship; determine the group similarity of the second array and the third array; and use the group similarity as the table similarity between the first data table and the second data table.

[0023] In a possible design manner of the second aspect, the processing module is specifically used to: determine the group similarity of the second array and the third array according to a preset similarity algorithm; the similarity algorithm includes any one of the following: cosine similarity, Pearson correlation coefficient, Euclidean distance, and Jaccard distance.

[0024] In a possible design of the second aspect, the device further includes a communication module. The communication module is used to send a target data table pair and indication information to a management terminal after the processing module determines that redundant data exists between the first data table and the second data table; the indication information is used to indicate that redundant data exists between the first data table and the second data table.

[0025] In a possible design manner of the second aspect, after the communication module sends the target data table pair and the indication information to the management terminal, the processing module is further configured to:

[0026] If the communication module does not receive any response information to the corresponding indication information from the management terminal within the first preset time period, the number of times the first data table and the second data table are called within the preset time period is obtained; based on the number of times they are called, the data table to be deleted is determined; the data table to be deleted is the first data table or the second data table; and the data table to be deleted is deleted.

[0027] In a possible design method of the second aspect, the processing module is specifically used to: determine the data table with the smaller number of calls in the first data table and the second data table as a pending data table; if the number of calls of the pending data table is less than a preset threshold, determine the pending data table as a data table to be deleted.

[0028] In a possible design method of the second aspect, the processing module is also used to: if the number of times the first data table and the second data table are called is the same and both are less than a preset threshold, then obtain characteristic parameters of the first data table and the second data table; the characteristic parameters include any one or more of the following: generation time, data volume; based on the characteristic parameters of the first data table and the second data table, determine the coefficients to be deleted of the first data table and the second data table; determine the data table with the largest coefficient to be deleted in the first data table and the second data table as the data table to be deleted.

[0029] In a possible design of the second aspect, after obtaining the first source field associated with each field in the first data table and the second source field associated with each field in the second data table, the processing module is also used to: if the first source field associated with the third field in the first data table is the same as the second source field associated with the fourth field in the second data table, then the third field and the fourth field are determined as redundant fields.

[0030] In a third aspect, an electronic device is provided, comprising a processor, a memory, a communication interface and a communication bus. The processor, the memory and the communication interface communicate with each other via the communication bus. The memory is used to store computer instructions. When the computer instructions are executed on the processor, the processor executes the redundancy identification method described in any one of the first aspects.

[0031] In a fourth aspect, a computer-readable storage medium is provided, wherein computer instructions are stored in the computer-readable storage medium. When the computer instructions are executed on an electronic device, the electronic device executes the redundant identification method as described in any one of the above-mentioned first aspects.

[0032] In a fifth aspect, a computer program product including computer instructions is provided. When the computer instructions are executed on an electronic device, the electronic device executes the redundant identification method as described in any one of the above-mentioned first aspects.

[0033] It can be understood that the solutions of the second aspect to the fifth aspect provided above are all used to execute the corresponding methods provided in the first aspect above. Therefore, the beneficial effects that can be achieved can refer to the beneficial effects in the corresponding methods provided above, and will not be repeated here.

[0034] It should be understood that in this application, the names of the above-mentioned redundant identification devices and electronic devices do not limit the devices or functional modules themselves. In actual implementation, these devices or functional modules may appear with other names. As long as the functions of each device or functional module are similar to those of the present invention, they fall within the scope of the claims of the present disclosure and their equivalent technologies. In addition, it should be understood that the above general description and the detailed description below are only exemplary and explanatory and cannot limit the present disclosure.

[0035] The above description is only an overview of the technical solution of the embodiment of the present invention. In order to more clearly understand the technical means of the embodiment of the present invention, it can be implemented in accordance with the contents of the specification. In order to make the above and other purposes, features and advantages of the embodiment of the present invention more obvious and easy to understand, the specific implementation methods of the present invention are listed below. BRIEF DESCRIPTION OF THE DRAWINGS

[0036] The accompanying drawings are only used to illustrate the embodiments and are not to be considered as limiting the present invention. In addition, the same reference symbols are used to represent the same components throughout the accompanying drawings. In the accompanying drawings:

[0037] Figure 1 A schematic diagram of the structure of a redundancy identification system provided in an embodiment of the present application;

[0038] Figure 2 A flowchart of a redundancy identification method provided in an embodiment of the present application Figure 1 ;

[0039] Figure 3 A schematic diagram of a scene associated with fields provided in an embodiment of the present application;

[0040] Figure 4 A flowchart of a redundancy identification method provided in an embodiment of the present application Figure 2 ;

[0041] Figure 5 A flowchart of a redundancy identification method provided in an embodiment of the present application Figure 3 ;

[0042] Figure 6 A schematic diagram of data lineage information provided in an embodiment of the present application;

[0043] Figure 7 A flowchart of a redundancy identification method provided in an embodiment of the present application Figure 4 ;

[0044] Figure 8 A schematic diagram of a scenario for generating an array provided in an embodiment of the present application;

[0045] Fig. 9 A flowchart of a redundancy identification method provided in an embodiment of the present application Figure 5 ;

[0046] Fig.10 A flowchart of a redundancy identification method provided in an embodiment of the present application Figure 6 ;

[0047] Fig.11 A flowchart of a redundancy identification method provided in an embodiment of the present application Figure 7 ;

[0048] Fig.12 A flowchart of a redundancy identification method provided in an embodiment of the present application Figure 8 ;

[0049] Fig.13 A flowchart of a redundancy identification method provided in an embodiment of the present application Figure 9 ;

[0050] Fig.14 A schematic diagram of the structure of a redundant identification device provided in an embodiment of the present application;

[0051] Fig.15 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0052] The following will be combined with the drawings in the embodiments of the present application to clearly and completely describe the technical solutions in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0053] In the following, the terms "first" and "second" are used for descriptive purposes only and are not to be understood as indicating or implying relative importance or implicitly indicating the number of the indicated technical features. Thus, a feature defined as "first" or "second" may explicitly or implicitly include one or more of the features. In the description of this application, unless otherwise specified, "plurality" means two or more. "A and / or B" includes the following three combinations: only A, only B, and a combination of A and B.

[0054] In order to help those skilled in the art better understand the technical solutions provided by the present application, some concepts involved in the embodiments of the present application are first briefly introduced.

[0055] Data warehouse: The English name is data warehouse, which can be abbreviated as DW or DWH, and can be abbreviated as Shucang in Chinese. Data warehouse is a strategic collection that provides all types of data support for decision-making processes at all levels of the enterprise. It is a single data storage created for analytical reporting and decision support purposes. It provides guidance for business process improvement, monitoring time, cost, quality and control for enterprises that need business intelligence. Data warehouse is a subject-oriented, integrated, relatively stable (Non-Volatile), historically changing (Time Variant) data collection used to support management decisions (Decision Making Support).

[0056] Data lineage: From the generation, processing, integration, circulation, to the final extinction of data, a relationship will be formed between data. We use a similar relationship in human society to express this relationship between data, which is called data lineage. Data lineage can also be called data lineage relationship, data lineage information, etc. Data lineage contains information such as data flow and historical records in the data management life cycle, recording the origin and development of data. Data lineage can be used to track the source of data and the data processing process. Common methods for obtaining data lineage include automatic parsing, system tracking, and machine learning. Automatic parsing is to parse the table in structured query language (SQL), or the file of the table in the stored procedure or extract-transform-load (ETL) process to obtain the data lineage of the table. System tracking is the kind of data processing flow process, in which the data processing program is responsible for sending data lineage information. For example, in the annotation part of the data, add description information about lineage, etc. Machine learning is based on the dependency relationship between tables to calculate the data similarity between tables.

[0057] Data model: Data model is the way to store data in a database and is the foundation of the database system. In a data warehouse, data models usually organize data in the form of data tables. Unless otherwise specified, the data model described in this article is equivalent to a data table.

[0058] Cosine similarity: also known as cosine similarity, it evaluates the similarity of two vectors by calculating the cosine of the angle between them. The cosine value of a 0-degree angle is 1, while the cosine value of any other angle is not greater than 1; and its minimum value is -1. Therefore, the cosine value of the angle between two vectors determines whether the two vectors point in roughly the same direction. When two vectors have the same direction, the cosine similarity value is 1; when the angle between the two vectors is 90°, the cosine similarity value is 0; when the two vectors point in completely opposite directions, the cosine similarity value is -1. This result is independent of the length of the vector, but only related to the direction in which the vector points. Cosine similarity is usually used in positive space, so the value given is between -1 and 1.

[0059] With the development of the big data industry, most companies will build their own data warehouse (DW / DWH). Due to the small size of the technical team or the lack of technical capabilities of the technical team in the early stages of enterprise development, the data tables (or data models) in the data warehouse they build often have unreasonable designs, such as chimney-style construction. As a result, the reusability of the data tables in the data warehouse is not considered enough, resulting in a large amount of data redundancy between different data tables.

[0060] In the middle and late stages of enterprise development, data model redundancy is also common, for example:

[0061] 1. After the technical team has been replaced many times, a new data model (or data table) will be introduced due to the inconsistency of design ideas between the previous team and the next team. The new and old data models generated for the same data will contain a lot of redundant data.

[0062] 2. After multiple iterations of the company's business, the original data model can no longer meet the needs of the business, so the data model needs to be rebuilt. During reconstruction, a new set of data models is usually created, and then the old data model is gradually eliminated. During this period, there will be a large number of redundant data models in the data warehouse, which means there will be a large amount of redundant data.

[0063] The redundancy of data will lead to the following problems:

[0064] 1. Since the same data (or data generated by the same data) exists in multiple data models, it is very easy for different calculation calibers to lead to data inconsistency.

[0065] 2. For the data in the data warehouse, since the same data will be developed, stored and calculated multiple times, the R&D, storage and computing costs will increase.

[0066] 3. Faced with numerous redundant data models in the data warehouse, users are unable to determine which data model to use in what scenario, causing confusion in understanding and use, increasing the complexity of using the data warehouse and reducing the user experience.

[0067] Based on this, in order to better use the data warehouse, it is often necessary to eliminate data redundancy, and to eliminate redundancy, redundancy must first be identified. Currently, the way to identify redundancy mostly relies on manual methods. For example, manually determine whether two models (data tables) contain the same field information, or check the ETL code to confirm whether these fields come from the same upstream model and whether their calculation logic is consistent. However, there are often thousands of data tables in a company's data warehouse. Using manual methods to identify redundancy not only requires the accumulation of professional knowledge of the identification personnel, but also takes a lot of time, so the identification accuracy and efficiency are low.

[0068] In view of the above problems, the present application implements a method for redundancy identification. In this method, when redundancy identification is to be performed on two data tables in a data table pair, the source fields (i.e., the first source field and the second source field) associated with each field in the two data tables can be obtained first. Then, the table similarity of the first data table and the second data table can be accurately determined based on all the first source fields and all the second source fields. The table similarity can indicate the proportion of the number of fields from the same source in the first data table and the second data table to the total number of fields in the corresponding data table. Finally, if it is determined that the table similarity is greater than the preset similarity threshold, it can be determined that there is redundant data between the first data table and the second data table. Because when the data table is generated, the fields therein may be fields in the previously generated data table that have been converted or processed multiple times. Whether there is redundancy between two different data tables is determined by whether the fields in the two data tables are generated by the same field conversion. If some fields in the two data tables are generated by the same field conversion, it indicates that there is redundancy in the two data tables. If there are no fields in the two data tables that are generated by the same field conversion, it indicates that there is no redundant data in the two data tables. For this reason, it can be determined whether there is redundant data between the two data tables by obtaining the source fields associated with different data table fields. Therefore, based on the technical solution provided by the present application, it is possible to automatically determine whether there is redundant data between two data tables. Compared with the prior art, the complex process of manual determination is reduced, manpower and material resources are reduced, and recognition efficiency is improved.

[0069] Figure 1FIG. 1 is a schematic diagram of a redundant identification system for applying a redundant identification method according to an exemplary embodiment. Figure 1 As shown, the system may include an electronic device 01 and a data warehouse server 02. The electronic device 01 and the data warehouse server 02 may communicate with each other via wired communication or wireless communication.

[0070] For example, in the embodiment of the present application, the electronic device 01 can be a mobile phone, a tablet computer, a handheld computer, a personal computer (PC), a cellular phone, a personal digital assistant (PDA), a wearable device, and other electronic devices. The embodiment of the present application does not limit the specific form of the electronic device. The electronic device 01 can also be a server or a server cluster composed of multiple servers.

[0071] Exemplarily, the data warehouse server 02 in the present disclosure may be a single server, or a server cluster consisting of multiple servers, or a cloud computing service center, which is not limited in the present disclosure. Among them, the data warehouse server 02 may mainly carry a data warehouse. The data warehouse server 02 is mainly used for electronic devices to obtain data tables in the data warehouse and data lineage information of the data tables (or fields in the data tables).

[0072] The redundancy identification method provided in the embodiment of the present application is described in detail below with reference to the accompanying drawings.

[0073] The embodiment of the present application provides a redundancy identification method applied to a redundancy identification device. The redundancy identification device may be an electronic device or a part of an electronic device, or the redundancy identification device may be a data warehouse server or a part of a data warehouse server. Figure 2 As shown, the method may include S201-S205:

[0074] S201. Obtain a target data table pair in a data warehouse.

[0075] Among them, the target data table pair includes a first data table and a second data table, and the first data table and the second data table are data tables in the data warehouse. The first data table and the second data table included in the target data table pair can be two data tables for which it has not been determined whether there is redundant data. In an embodiment of the present application, the implementation of step S201 can obtain multiple target data table pairs from the data warehouse, and the subsequent S202-S205 can be implemented for each target data table. At present, it can be implemented synchronously or one by one, and the present application does not make specific restrictions on this.

[0076] In the embodiment of the present application, each data table in the data warehouse may store fields corresponding to a specific business, such as "price", "views", "inventory", "sales", "annual statistics", etc. The target data table pair may be two different data tables selected from the data warehouse according to any rule without determining whether there is redundant data.

[0077] In order to facilitate the subsequent acquisition of the first source field associated with each field in the first data table and the second source field associated with each field in the second data table, during the implementation of step S201, the electronic device can acquire table structure information of the first data table and the second data table. The table structure information includes the fields contained in the data table to which it belongs.

[0078] In addition, in order for the electronic device to successfully obtain the target data table pair, the electronic device may have connection information of the data source of the data warehouse where the data table to be redundantly identified is located. For example: data source type, data source name, data source address, database name, login account, login password, etc. In this way, the electronic device can obtain the target data table pair from the database where the data warehouse is located (located in the data warehouse server) according to the connection information.

[0079] S202: Acquire a first source field associated with each field in the first data table, and a second source field associated with each field in the second data table.

[0080] When a data table is generated, the fields therein may be fields in a previously generated data table that have been converted or processed multiple times. Whether there is redundancy between two different data tables is determined by whether the fields in the two data tables are generated by converting the same fields. If some fields in the two data tables are generated by converting the same fields, it indicates that there is redundancy between the two data tables. If there are no fields in the two data tables that are generated by converting the same fields, it indicates that there is no redundant data in the two data tables. For this reason, the technical solution provided in the present application can determine whether there is redundant data between two data tables by obtaining the source fields associated with the fields in different data tables.

[0081] However, if you arbitrarily obtain the source fields associated with the fields in the two data tables to determine whether the two data tables are redundant, the following situation may occur: Take the first data table as data table A with only field B, and the second data table as data table D with only field E as an example, refer to Figure 3 As shown in the figure, if field B in data table A is associated with field C, and field E in data table D is associated with field F, then it can be determined that there is no redundant data between data table A and data table D. However, if field C and field F are actually generated by converting field G, then the previously determined conclusion that there is no redundant data between data table A and data table D is wrong.

[0082] Therefore, in the implementation of the present application, in S202 after S201, the earliest source field (i.e., the first source field and the second source field) associated with each field in the first data table and the second data table may be obtained. Subsequently, the table similarity between the first data table and the second data table may be accurately determined based on all the first source fields and all the second source fields, and then it may be determined whether there is redundant data in the first data table and the second data table. That is, the subsequent S203-S205 are executed.

[0083] It should be noted that, because a field in a data table may be generated by converting multiple previously generated fields in practice, the first source field corresponding to a field in the first data table may be one or more, and the same is true for the second data table, and this application does not impose specific restrictions on this. In addition, in a special case, there may be a field in a data table that is not generated by converting other fields. In this case, the source field associated with the field (such as the first source field or the second source field) is itself.

[0084] S203: Determine the table similarity between the first data table and the second data table according to all the first source fields and all the second source fields.

[0085] In the embodiment of the present application, the table similarity can be used to characterize the extent to which the number of fields in the first data table and the second data table have the same source. That is, the greater the table similarity, the more fields in the first data table and the second data table have the same source.

[0086] S204: Determine whether the table similarity is greater than a similarity threshold.

[0087] In the embodiment of the present application, the similarity threshold may be set in advance. If the table similarity between two data tables is greater than the similarity threshold, it can be considered that there is redundant data between the two data tables, otherwise there is no redundant data.

[0088] In a possible implementation, the similarity threshold can be obtained by the data warehouse manager based on technical experience or data statistics. For example, the data manager can use the minimum value of table similarity when there is redundant data between two data tables as the similarity threshold, or use the maximum value of table similarity when there is no redundant data between two data tables as the similarity threshold based on technical experience or data statistics.

[0089] In another possible implementation, the preset threshold may be obtained by pre-acquiring some data tables that determine whether there is redundancy as training data and training a corresponding target judgment model. The specific process is as follows:

[0090] 1. Obtain multiple sets of sample data and actual redundancy values ​​(1 or 0) corresponding to the multiple sets of sample data. Specifically, if it is determined that two data tables in the sample data have redundant data, the actual redundancy value of the sample data is 1, otherwise it is 0.

[0091] The sample data includes two data tables, which are any two data tables in the same data warehouse.

[0092] 2. Using the sample data as training data and the actual redundant value as supervision information, the initial judgment model is iteratively trained to obtain the target judgment model.

[0093] Among them, an initial threshold is initially set in the initial judgment model, and the redundancy value output by the initial judgment model is directly related to the initial threshold, that is, if the table similarity of two data tables in the sample data is greater than the initial threshold, the redundancy value is closer to 1, otherwise it is closer to 0. The design of the specific initial judgment model can be any feasible model architecture, and this application does not make specific restrictions on this. In the process of iterative training of the initial judgment model, the initial threshold will be continuously optimized and adjusted.

[0094] Specifically, the iterative training process is as follows:

[0095] (1) Input the sample data into the initial judgment model to obtain the initial redundancy value.

[0096] (2) Calculate the loss value based on the initial redundancy value and the actual redundancy value.

[0097] (3) Iteratively update the initial judgment model according to the loss value to obtain the target judgment model.

[0098] The iterative update specifically refers to repeatedly executing the above (1) and (2) and optimizing the initial judgment model using the loss value after optimizing the initial judgment model using the loss value until the loss value is less than the set threshold.

[0099] After obtaining the target judgment model, the initial threshold after multiple optimizations can be extracted from it as the similarity threshold required in this application. Of course, if it is found that the similarity threshold is set unreasonably in subsequent practical applications, the data table pairs in the actual data warehouse that determine whether there is redundant data can be used as training data to optimize and update the target judgment model to obtain a more accurate similarity threshold.

[0100] Of course, the above implementation is only an example of determining the similarity threshold, and in practice the similarity threshold may also be obtained in other ways.

[0101] If it is determined that the table similarity is greater than the similarity threshold, it indicates that there is a high probability of redundant data in the two data tables of the target data table pair, and S205 is executed.

[0102] If it is determined that the table similarity is not greater than (ie, less than or equal to) the similarity threshold, it indicates that there is a high probability that there is no redundant data in the two data tables of the target data table pair, and the process ends at this time.

[0103] Of course, after the entire redundancy identification method process is completed, the redundancy identification method process can be restarted when needed. For example, the redundancy identification method can be implemented periodically, and after all target data table pairs are redundancy identified and the entire redundancy identification method process is completed, the redundancy identification method can be re-implemented for the target data table pairs in the data warehouse at the beginning of the next cycle. Alternatively, the data warehouse manager can instruct the electronic device to start implementing the redundancy identification method process when determining that redundancy needs to be identified. This application does not impose specific restrictions on this.

[0104] It should be noted that the above step S204 may not exist in practice, and the electronic device may execute S205 when it is determined that the table similarity is greater than the similarity threshold, and terminate the process when it is determined that the table similarity is not greater than the similarity threshold. In addition, the case where the table similarity is equal to the similarity threshold can be attributed to the case where the table similarity is greater than the similarity threshold, and can also be attributed to the case where the table similarity is less than the similarity threshold. In the embodiment of the present application, the case where the table similarity is equal to the similarity threshold is attributed to the case where the table similarity is less than the similarity threshold as an example, but it is not used as a specific display of the technical solution provided by the present application, and can be set according to actual conditions in practice.

[0105] S205: Determine whether redundant data exists between the first data table and the second data table.

[0106] Based on the technical solution provided by the embodiment of the present application, when it is necessary to identify the redundancy of two data tables in the data table pair in the data warehouse, the earliest source field (i.e., the first source field and the second source field) associated with each field in the two data tables can be obtained first. When the data table is generated, the field therein may be the field in the previously generated data table obtained by multiple conversions or processing. Whether there is redundancy between two different data tables is determined by whether the fields in the two data tables are generated by the same field conversion. If some fields in the two data tables are generated by the same field conversion, it indicates that there is redundancy in the two data tables. If there are no fields in the two data tables that are generated by the same field conversion, it indicates that there is no redundant data in the two data tables. For this reason, it can be determined whether there is redundant data between the two data tables by obtaining the source fields associated with different data table fields. Therefore, in the present application, the source fields (i.e., the first source field and the second source field) associated with each field in the two data tables can be first obtained. Then, the table similarity of the first data table and the second data table can be accurately determined based on all the first source fields and all the second source fields. The table similarity can then indicate the degree of the number of fields with the same source in the first data table and the second data table. Finally, if it is determined that the table similarity is greater than the preset similarity threshold, it can be determined that there is redundant data between the first data table and the second data table. It can be seen that based on the technical solution provided by the present application, it is possible to automatically determine whether there is redundant data between the two data tables. Compared with the prior art, the complex process of manual determination is reduced, manpower and material resources are reduced, and the recognition accuracy and efficiency are improved.

[0107] When there are thousands or even tens of thousands of data tables in the data warehouse, the number of target data table pairs will expand dramatically. At this time, it is almost impossible to identify whether there is redundant data in all target data table pairs, or the amount of calculation is extremely large. In the actual implementation process, some rules can be used to effectively reduce the data table pairs that need to be calculated. For example, data tables at different levels in the data warehouse do not participate in judging whether there is redundant data, and data tables of different subject categories do not participate in judging whether there is redundant data. Based on this, refer to Figure 3 As shown, the embodiment of the present application can also provide another embodiment of a redundancy identification method applied in an electronic device. Figure 4 As shown, the redundancy identification method in this embodiment may include S401-S406:

[0108] S401. Obtain hierarchical information and subject domain information of multiple data tables in a data warehouse.

[0109] The hierarchical information is used to indicate the level of the data table in the data warehouse; the subject domain information is used to indicate the subject category of the data table.

[0110] For example, common layers in a data warehouse include: dimension layer (DIM), original data layer (OPERATIONAL DATA STORE, ODS), data detail layer (DATA WAREHOUSE Detail, DWD), data summary layer (DATA WAREHOUSE SUMMARY, DWS), data application layer (APPLICATION DATA SERVICE, ADS), etc.

[0111] When establishing data tables (or data models) in a data warehouse, different layers are established to store different data according to different data. Since each layer has its own special design goals, there is usually no redundancy between them. It should be pointed out here that the tables of the ods layer and the dwd layer are all detailed data. If we only look at the table structure, there are many redundancies, but the ods layer stores the original detailed data, and the dwd layer is a detailed layer fact table built based on the business process and cleaned by ETL. Their design goals are different and cannot be considered redundant.

[0112] Exemplarily, the subject category indicated by the subject domain information may include any one or more of the following: order, user, event, marketing, finance. In practice, data tables under different subjects are data for different business processes or different dimensions, and are usually not redundant, so the target data table pair for redundancy identification in this application includes two data tables that should be data tables of the same subject category.

[0113] In order to enable the electronic device to successfully obtain the hierarchical information and subject domain information of the data table, the electronic device may have connection information of the data source of the data warehouse where the data table to be redundantly identified is located. For example: data source type, data source name, data source address, database name, login account, login password, etc. In this way, the electronic device can obtain the hierarchical information and subject domain information of the data table from the database (located in the data warehouse server) where the data warehouse is located according to the connection information.

[0114] S402: Determine a target data table pair from the multiple data tables according to the hierarchical information and subject domain information of the multiple data tables.

[0115] Among them, the two data tables included in the target data table pair are at different levels in the data warehouse; the subject categories of the two data tables included in the target data table pair are different; the two data tables included in the target data table pair are not a preset data table pair, and the preset data table pair is a pre-defined data table pair without redundant data.

[0116] The preset data table pair may be a data table pair consisting of two data tables that are directly determined by the technicians based on their understanding of the business or technical aspects and that do not contain redundant data.

[0117] In the embodiment of the present application, after step S402, the electronic device can determine multiple target data table pairs from multiple data tables. In the subsequent steps S403-S406, the electronic device can be implemented for all target data tables, specifically, it can be implemented synchronously or one by one, and the present application does not make specific restrictions on this.

[0118] Among them, the relevant content and definition of the target data table pair can refer to the relevant description after S201 in the above implementation, and will not be repeated here.

[0119] S403: Acquire a first source field associated with each field in the first data table, and a second source field associated with each field in the second data table.

[0120] The specific meaning, relevant definitions and explanations of the implementation of S403 can refer to the relevant descriptions of S202 in the aforementioned implementation, and will not be repeated here.

[0121] S404: Determine the table similarity between the first data table and the second data table according to all the first source fields and all the second source fields.

[0122] In the embodiment of the present application, the table similarity can be used to characterize the extent to which the number of fields in the first data table and the second data table have the same source. That is, the greater the table similarity, the more fields in the first data table and the second data table have the same source.

[0123] S405: Determine whether the table similarity is greater than a similarity threshold.

[0124] The specific implementation of S405 may refer to the relevant description of S204 in the aforementioned embodiment, which will not be repeated here.

[0125] If it is determined that the table similarity is greater than the similarity threshold, it indicates that the two data tables of the target data table pair are likely to have redundant data, and S406 is executed. If it is determined that the table similarity is not greater than (i.e., less than or equal to) the similarity threshold, it indicates that the two data tables of the target data table pair are likely to have no redundant data, and the process ends.

[0126] S406: Determine whether redundant data exists between the first data table and the second data table.

[0127] Based on the above technical solutions of S401-S406, in addition to achieving the effects achieved by the technical solutions corresponding to S201-S205, because a data table pair that may have redundant data is selected from the data warehouse as a target data table pair, rather than arbitrarily selecting a combination of two data tables as a target data table pair, the amount of calculation in the process of implementing the redundancy identification method in the electronic device can be reduced, and the efficiency of the redundancy identification method in identifying data table pairs with redundant data in the data warehouse can be improved.

[0128] In practice, whether there is a relationship between different data tables or even between fields in different data tables can be determined by data lineage. Based on this, the present application embodiment also provides another embodiment of a redundancy identification method. Figure 5 As shown, the method may include: S501-S507:

[0129] S501. Obtain a target data table pair in a data warehouse.

[0130] The specific implementation of S501 may refer to the related descriptions of S401 and S402 in the aforementioned embodiments, which will not be described in detail here.

[0131] S502: Obtain first data lineage information of the first data table and second data lineage information of the second data table.

[0132] The first data lineage information includes data lineage information of each field in the first data table, and the second data lineage information includes data lineage information of each field in the second data table.

[0133] The specific method of obtaining data lineage information can refer to the relevant explanation of data lineage in the aforementioned embodiment, which will not be repeated here.

[0134] S503: According to the first data lineage information, determine a field having a data lineage relationship with the first field as a first source field associated with the first field.

[0135] The first field is a field in the first data table. Here, the first source field may be referred to as the first parent node.

[0136] S504: According to the second data lineage information, determine a field having a data lineage relationship with the second field as a first source field associated with the second field.

[0137] The second field is a field in the first data table. Here, the second source field may be referred to as a second parent node.

[0138] Based on the related statements after S202, in the embodiment of the present application, S503 may specifically be: according to the first data lineage information, the earliest generated field among the fields having a data lineage relationship with the first field is determined as the earliest generated first source field associated with the first field. Similarly, S504 may specifically be: according to the second data lineage information, the earliest generated field among the fields having a data lineage relationship with the second field is determined as the earliest generated second source field associated with the second field.

[0139] For example, the first data table is data table F, which includes 5 fields f1, f2, f3, f4 and f5, and the second data table is data table G, which includes 5 fields g1, g2, g3, g4 and g5. The first data lineage information can refer to Figure 6 As shown in (a), the second data lineage information can refer to Figure 6 As shown in (b).

[0140] For F, f1 comes from b1 in table B, f2 comes from b2 in table B, and f3 can come from b3 in table B. b1 comes from a1 in table A, b2 comes from a2 in table A, and b3 comes from a3 in table A. The fields in table A can be stored directly without a source.

[0141] f4 comes from c4 in data table C, and f5 comes from c5 in data table C. The fields in data table C can be stored directly without a source.

[0142] Then, the earliest generated field among the fields with data lineage relationship with f1 is a1, the earliest generated field among the fields with data lineage relationship with f2 is a2, the earliest generated field among the fields with data lineage relationship with f3 is a3, the earliest generated field among the fields with data lineage relationship with f4 is c4, and the earliest generated field among the fields with data lineage relationship with f5 is c5. In other words, the first source fields corresponding to each field in data table F are a1, a2, a3, c4, and c5 respectively.

[0143] For data table G, g1 comes from b1 in data table B, g2 comes from b2 in data table B, g3 can come from b3 in data table B, and g4 can come from b4 in data table B. And b1 comes from a1 in data table A, b2 comes from a2 in data table A, b3 comes from a3 in data table A, and b4 comes from a4 in data table A. The fields in data table A can be stored directly without a source.

[0144] g5 comes from d5 in data table D. The fields in data table D can be stored directly without a source.

[0145] Then, the earliest generated field among the fields with data lineage relationship with g1 is a1, the earliest generated field among the fields with data lineage relationship with g2 is a2, the earliest generated field among the fields with data lineage relationship with g3 is a3, the earliest generated field among the fields with data lineage relationship with g4 is a4, and the earliest generated field among the fields with data lineage relationship with g5 is d5. In other words, the first source fields corresponding to each field in data table G are a1, a2, a3, a4, and d5 respectively.

[0146] It should be noted that in the embodiment of the present application, S503 and S504 may be steps executed simultaneously or may be steps executed successively, and the present application does not impose any specific limitation on this.

[0147] S505: Determine the table similarity between the first data table and the second data table according to all the first source fields and all the second source fields.

[0148] In the embodiment of the present application, the table similarity can be used to characterize the extent to which the number of fields in the first data table and the second data table have the same source. That is, the greater the table similarity, the more fields in the first data table and the second data table have the same source.

[0149] S506: Determine whether the table similarity is greater than a similarity threshold.

[0150] The specific implementation of S506 may refer to the relevant description of S204 in the aforementioned embodiment, which will not be repeated here.

[0151] If it is determined that the table similarity is greater than the similarity threshold, it indicates that the two data tables of the target data table pair are likely to have redundant data, and S507 is executed. If it is determined that the table similarity is not greater than (i.e., less than or equal to) the similarity threshold, it indicates that the two data tables of the target data table pair are likely to have no redundant data, and the process ends.

[0152] S507: Determine whether redundant data exists between the first data table and the second data table.

[0153] Based on the technical solutions of S501-S507 above, the source field that can be traced back to each field of the data table in the target data table pair can be accurately determined according to the data lineage relationship, providing data support for the subsequent steps of determining the table similarity. In addition, based on this technical solution, the table similarity of the two data tables in the target data table pair can be determined based on all source fields. If it is determined that the table similarity is greater than the preset similarity threshold, it can be determined that there is redundant data between the first data table and the second data table. In this way, it is possible to automatically determine whether there is redundant data between the two data tables. Compared with the prior art, the complex process of manual determination is reduced, manpower and material resources are reduced, and the recognition accuracy and efficiency are improved.

[0154] In practice, the number of fields included in different data tables may be the same or different. When the number of fields is the same, it is easier to determine the similarity between the first source field and the second source field, and the similarity between the first source field and the second source field can be determined as the table similarity between the first data table and the second data table.

[0155] However, in most cases, the number of fields between different data tables is different. At this time, if the similarity between the first source field and the second source field is not easy to determine, then the table similarity is also not easy to determine. In one feasible manner, in this case, all associated fields associated with the first data table and / or the second data table can be obtained by having the first source field and all non-repeated fields in the second source fields. Afterwards, the correlation between the first data table and the second data table (i.e., the table similarity) can be determined based on the correlation between these associated fields as a whole and the first data table, as well as the correlation between these associated fields as a whole and the second data table. Based on this, the embodiment of the present application also provides another embodiment of a redundancy identification method. Referring to Figure 7 As shown, the method may include: S701-S708:

[0156] S701. Obtain a target data table pair in a data warehouse.

[0157] The specific implementation of S701 may refer to the related descriptions of S401 and S402 in the aforementioned embodiments, which will not be described in detail here.

[0158] S702: Acquire a first source field associated with each field in the first data table, and a second source field associated with each field in the second data table.

[0159] The specific meaning, relevant definitions and explanations of the implementation of S702 can refer to the relevant descriptions of S202 in the aforementioned implementation, and will not be repeated here.

[0160] The specific implementation of S702 may refer to the related descriptions of S502 to S504 in the aforementioned embodiment, which will not be described in detail here.

[0161] S703: Combine all first source fields and all second source fields and remove duplicates to obtain a first array.

[0162] For example, Figure 6 For example, all first source word terminals and all second source fields corresponding to the example shown in FIG. Figure 8 As shown, the first array may include a1, a2, a3, a4, c4, c5 and d5. The order of the elements in the first array may be any order.

[0163] S704: Generate a second array and a third array according to the first array.

[0164] The number of elements in the second array is the same as that in the first array; each element in the second array is used to represent the association result between the element at the corresponding position in the first array and the field in the first data table; each element in the third array is used to represent the association result between the element at the corresponding position in the first array and the field in the second data table; the association result is that there is an association relationship or there is no association relationship. In some embodiments, the second array and the third array may also be referred to as a second vector and a third vector.

[0165] For example, the first data table is Figure 6 In the example shown, the second data table is Figure 6 As shown in the data table G, the elements of the first array include a1, a2, a3, a4, c4, c5 and d5 in sequence. Figure 8 As shown, there are fields in the first data table that are associated with a1, a2, a3, c4, and c5. Compared with the first array, the elements of the second array corresponding to the first data table F can be 1, 1, 1, 0, 1, 1, 0. Similarly, the third array corresponding to the second data table G can be 1, 1, 1, 1, 0, 0, 1.

[0166] S705: Determine the group similarity between the second array and the third array.

[0167] In some embodiments, S705 may specifically be: determining the group similarity of the second array and the third array according to a preset similarity algorithm. Exemplarily, the preset similarity algorithm may include any of the following: cosine similarity, Pearson correlation coefficient, Euclidean distance, and Jaccard distance. In this way, the group similarity of the second data and the third array can be smoothly calculated to provide data support for the implementation of the subsequent redundancy identification method.

[0168] Exemplarily, taking the preset similarity algorithm as cosine similarity as an example, the group similarity of the second array (second vector) and the third array (third vector) can be obtained according to the following formula:

[0169]

[0170] Among them, Similarity is similarity, F is the second vector, G is the third vector, F i is the i-th element in the second vector, G i is the i-th element in the third vector, and n is the number of elements in the second vector or the third vector.

[0171] S706: Use the group similarity as the table similarity between the first data table and the second data table.

[0172] S707: Determine whether the table similarity is greater than a similarity threshold.

[0173] The specific implementation of S707 may refer to the relevant description of S204 in the aforementioned embodiment, which will not be repeated here.

[0174] If it is determined that the table similarity is greater than the similarity threshold, it indicates that the two data tables of the target data table pair are likely to have redundant data, and S708 is executed. If it is determined that the table similarity is not greater than (i.e., less than or equal to) the similarity threshold, it indicates that the two data tables of the target data table pair are likely to have no redundant data, and the process ends.

[0175] S708: Determine whether redundant data exists between the first data table and the second data table.

[0176] Based on the technical solutions corresponding to the above S701-S708, after obtaining the source fields corresponding to each field of the two data tables in the target data table pair, all source fields can be first de-recombined to obtain the first array. Afterwards, the second array corresponding to the first data table and the third array corresponding to the second data table can be obtained according to the association relationship between the fields in the first data table and the second data table and the elements in the first array. Afterwards, the group similarity of the second array and the third array can be calculated. Because the second array can reflect the association between the first data table and the first array (the non-repeating combination of all fields associated with the first data table and the second data table), and the third array can reflect the association between the second data table and the first array, the group similarity can reflect the similarity between the first data table and the second data table and the association between the first array. And this can be considered as the table similarity of the first data table and the second data table. This provides a data basis for determining whether there is redundant data between the first data table and the second data table based on the table similarity, and ensures the smooth implementation of the redundancy identification method. Of course, this technical solution also has the technical effect that can be achieved by the technical solutions corresponding to the aforementioned implementation S201-S205.

[0177] In some embodiments, in order to enable the data warehouse administrator to promptly know the data table pairs with redundant data, Figure 2, refer to Fig. 9 As shown, in the redundancy identification method, after step S205, the method may further include step S206:

[0178] S206: Send the target data table pair and instruction information to the management terminal.

[0179] The indication information is used to indicate that redundant data exists between the first data table and the second data table. The management terminal may be a terminal corresponding to the administrator of the data warehouse, such as a mobile phone or computer of the administrator of the data warehouse.

[0180] In this way, data warehouse managers can promptly know which data tables in the data warehouse have redundant data, and then process the redundant data in the data warehouse in a timely manner to improve user experience.

[0181] In some embodiments, in order to better inform the data warehouse administrators to know in a timely manner which fields in the target data table pair with redundant data are redundant fields, after obtaining the source fields associated with each field in the two data tables in the target data table pair, the electronic device can determine which fields are redundant fields based on whether the source fields associated with different fields are the same, and inform the data warehouse administrators in any form when appropriate. Fig. 9 , refer to Fig.10 As shown, after S202, the method further includes S207:

[0182] S207: If the first source field associated with the third field in the first data table is the same as the second source field associated with the fourth field in the second data table, the third field and the fourth field are determined to be redundant fields.

[0183] The third field may be any field in the first data table, and the fourth field may be any field in the second data table.

[0184] It is understandable that when the source field associated with field A in a data table is the same as the source field associated with field B in another data table, there must be some redundancy between field A and field B. In this case, field A and field B can be determined as redundant fields between the data tables to which they belong.

[0185] In addition, in S206, the indication information sent by the electronic device to the management terminal may also indicate that the third field and the fourth field in the first data table and the second data table are redundant fields. Alternatively, when implementing S206, the electronic device may also send redundant indication information to the management terminal, and the redundant indication information is used to indicate that the third field and the fourth field in the first data table and the second data table are redundant fields.

[0186] It should be noted that, in any possible embodiment, S207 only needs to be executed after obtaining the source field associated with each field in the two data tables in the target data table pair (for example, S202 or S504) and before sending the indication information to the management terminal (for example, S206), and the present application does not impose any specific restrictions on this.

[0187] In this way, the management terminal can clearly obtain information about which fields in the target data table pair are redundant fields when receiving the target data table pair. Based on this, the administrator of the data warehouse to which the management terminal belongs can manage the redundant data in the target data table pair more quickly based on this information, thereby improving the efficiency of redundant data management.

[0188] Under normal circumstances, after the electronic device sends the target data table pair and indication information to the management terminal, the administrator of the data warehouse corresponding to the management terminal will promptly manage the redundant data in the target data table pair, and use the management terminal to return response information corresponding to the indication information to the electronic device to indicate that there is no redundant data in the target data table pair of the electronic device. However, in some possible embodiments, due to the ever-changing environment in practice, the administrator of the data warehouse may not be able to manage the redundant data in the target data table pair in a timely manner. This will cause the redundant data in the data warehouse to exist for a long time, and the electronic device will continue to send indication information to the management terminal, affecting the normal use of the data warehouse and the electronic device. In order to solve this problem, the electronic device can delete a data table in the target data table pair according to certain rules if it does not receive the response information from the management terminal in time, so as to achieve the purpose of eliminating redundant data or data management. Based on this, combined with Fig. 9 , refer to Fig.11 As shown, after S206, the method further includes S208-S210:

[0189] S208: If no response information corresponding to the indication information is received from the management terminal within the first preset time period, the number of times the first data table and the second data table are called within the preset time period is obtained.

[0190] For a data table in a data warehouse, the number of times it is called can reflect whether the data table is frequently used by other data tables, which can also indicate how much adverse impact the deletion of the data table has on the normal use of other data tables. In addition, since the data table is in its life cycle (i.e., from generation to deletion or no longer being used), the number of times it is called in different periods also varies. To determine whether a data table is currently suitable for deletion, it is necessary to determine the number of times it has been called in the recent period (e.g., within half a year), that is, the number of times it has been called within the above preset time.

[0191] Based on the above description, in the present application, after the electronic device sends the target data table pair and the indication information to the management terminal, if no response information is received from the management terminal within a first preset time period (e.g., one week), the number of times the first data table and the second data table are called within the preset time period can be obtained, and then the data table to be deleted can be determined based on the number of times the first data table and the second data table are called. That is, the subsequent S209 is executed.

[0192] S209: Determine the data table to be deleted based on the number of times it is called.

[0193] The data table to be deleted is the first data table or the second data table.

[0194] S210, deleting the data table to be deleted.

[0195] Based on the technical solutions corresponding to S209-S210, the electronic device can reasonably delete the deletable data tables in the target data table pair when the data warehouse administrator cannot manage the redundant data in the target data table pair in time, so as to achieve the purpose of eliminating redundant data or data management.

[0196] In some embodiments, in combination Fig.11 , refer to Fig.12 As shown, S209 may specifically include: S2091A and S2092A:

[0197] S2091A: Determine the data table with the smaller number of calls between the first data table and the second data table as the pending data table.

[0198] S2092A: If the number of times the pending data table is called is less than a preset threshold, the pending data table is determined as a data table to be deleted.

[0199] Exemplarily, the preset threshold value may be 5. Of course, the preset threshold value may be the maximum or average number of times a data table that is rarely or not relied on by other data tables is called within a preset time period. Its value may be obtained based on the work experience of data warehouse managers in data governance in practice, and this application does not impose any specific restrictions on this.

[0200] Because the smaller the number of calls, the more suitable the corresponding data table is for deletion. If the number of calls for the data table is less than the preset threshold, it is highly likely that the deletion of the data table will have little impact on other data tables. In other words, in the target data table pair, the data table with the smaller number of calls and the number of calls less than the preset threshold can be deleted as redundant data. Therefore, based on the technical solutions corresponding to S2091A and S2092A, the data tables that can be deleted can be automatically deleted when the data warehouse administrator fails to manage the redundant data in the target data table pair in a timely manner, thereby achieving the purpose of eliminating redundancy or data management.

[0201] In other embodiments, the number of times the first data table and the second data table are called may be the same. If the number of times both are called is less than a preset threshold, then which data table to delete needs to be determined based on other factors, such as generation time or data volume. Fig.11 , refer to Fig.13 As shown, S209 may specifically include S2091B-S2093B:

[0202] S2091B: If the number of times the first data table and the second data table are called is the same and both are less than a preset threshold, obtain characteristic parameters of the first data table and the second data table.

[0203] The characteristic parameters include any one or more of the following: generation time and data volume.

[0204] The earlier the data table is generated, the more likely it is that the data table is redundant or unimportant, and is more suitable for deletion. The larger the amount of data in the data table, the more the storage pressure of the data warehouse will be reduced after the data table is deleted, which means that the data table is more suitable for deletion. Therefore, when the first data table and the second data table cannot determine the data table to be deleted based on the number of calls, the data table that can be deleted can be determined by combining the generation time and / or data volume.

[0205] S2092B. Determine coefficients to be deleted in the first data table and the second data table based on characteristic parameters of the first data table and the second data table.

[0206] Exemplarily, if the characteristic parameter includes generation time, the time difference between the generation time of the first data table and the current time can be used as the coefficient to be deleted of the first data table; the same applies to the second data table.

[0207] Exemplarily, if the characteristic parameter includes the data volume, the data volume of the first data table can be used as the coefficient to be deleted of the first data table; the same applies to the second data table.

[0208] For example, if the characteristic parameters include generation time and data volume, the time difference between the generation time of the first data table and the current time can be used as the first sub-coefficient to be deleted of the first data table, the data volume of the first data table can be used as the second sub-coefficient to be deleted of the first data table, and the weighted average of the first sub-coefficient to be deleted and the second sub-coefficient to be deleted can be used as the coefficient to be deleted of the first data table; the same is true for the second data table. Among them, the weight of the generation time and the data volume is obtained based on actual experience.

[0209] Of course, in practice, the specific calculation method of the coefficients to be deleted can be any feasible method. The above description is only an example, and this application does not make any specific restrictions on this.

[0210] S2093B: Determine the data table with the largest coefficient to be deleted in the first data table and the second data table as the data table to be deleted.

[0211] Based on the technical solutions corresponding to S2091B-S2093B, when the data table to be deleted cannot be determined based on the number of calls, the appropriate data table to be deleted can be successfully determined based on the characteristic parameters of the two data tables in the target data table pair. Furthermore, when the data warehouse manager fails to timely manage the redundant data in the target data table pair, the data table that can be deleted can be automatically deleted, thereby achieving the purpose of eliminating redundancy or data management.

[0212] It is understandable that, in order to realize the above functions, the above electronic device includes hardware structures and / or software modules corresponding to the execution of each function. Those skilled in the art should easily realize that, in combination with the units and algorithm steps of each example described in the embodiments disclosed herein, the embodiments of the present application can be implemented in the form of hardware or a combination of hardware and computer software. Whether a function is executed in the form of hardware or computer software driving hardware depends on the specific application and design constraints of the technical solution. Professional and technical personnel can use different methods to implement the described functions for each specific application, but such implementation should not be considered to exceed the scope of the embodiments of the present application.

[0213] In the case of dividing each functional module according to each function, the embodiment of the present application also provides a redundant identification device. The redundant identification device can be applied to electronic equipment to implement the redundant identification method in the above embodiment. The function of the redundant identification device can be implemented by hardware, or by hardware executing corresponding software. The hardware or software includes one or more modules corresponding to the above functions. For example, Fig.14 As shown, the redundancy identification device may include: an acquisition module 101 and a processing module 102 .

[0214] Among them, the acquisition module 101 is used to acquire the target data table pair in the data warehouse; the target data table pair includes a first data table and a second data table; the first data table and the second data table are data tables in the data warehouse; the acquisition module 101 is also used to acquire the first source field associated with each field in the first data table, and the second source field associated with each field in the second data table; the processing module 102 is used to determine the table similarity between the first data table and the second data table based on all the first source fields and all the second source fields; the processing module 102 is also used to determine that there is redundant data between the first data table and the second data table if the table similarity is greater than a similarity threshold.

[0215] In some embodiments, the acquisition module 101 may include a data source module, a metadata module, and a data lineage module. The data source module contains connection information of the data source of the data warehouse where the data table to be redundantly identified is located. The metadata module can obtain the target data table pair and some related information (such as table structure information, hierarchical information of the data table, subject domain information of the data table, etc.) according to the connection information stored in the data source module. The data lineage module is used to obtain the first source field and the second source field. The processing module 102 can be called a redundant identification module.

[0216] In addition, the device may also include a task scheduling module, which may call the redundancy identification module to periodically identify redundant data in the data warehouse.

[0217] In a possible implementation, the acquisition module 101 is specifically used to: acquire hierarchical information and subject domain information of multiple data tables in a data warehouse; the hierarchical information is used to indicate the level of the data table in the data warehouse; the subject domain information is used to indicate the subject category of the data table; based on the hierarchical information and subject domain information of the multiple data tables, determine a target data table pair from the multiple data tables; the two data tables included in the target data table pair have different levels in the data warehouse; the two data tables included in the target data table pair have different subject categories; the two data tables included in the target data table pair are not a preset data table pair, and the preset data table pair is a predefined data table pair that does not have redundant data.

[0218] In one possible implementation, the acquisition module 101 is specifically used to include: acquiring first data lineage information of a first data table and second data lineage information of a second data table; the first data lineage information includes data lineage information of each field in the first data table, and the second data lineage information includes data lineage information of each field in the second data table; based on the first data lineage information, determining a field having a data lineage relationship with the first field as a first source field associated with the first field; the first field is a field in the first data table; based on the second data lineage information, determining a field having a data lineage relationship with the second field as a second source field associated with the second field; the second field is a field in the second data table.

[0219] In a possible implementation, the processing module 102 is specifically used to: combine all the first source fields and all the second source fields obtained by the acquisition module 101 and remove duplications to obtain a first array; generate a second array and a third array based on the first array; the number of elements in the second array is the same as that in the first array; each element in the second array is used to represent the association result between the element at the corresponding position in the first array and the field in the first data table; each element in the third array is used to represent the association result between the element at the corresponding position in the first array and the field in the second data table; the association result is the existence of an association relationship or the absence of an association relationship; determine the group similarity of the second array and the third array; and use the group similarity as the table similarity between the first data table and the second data table.

[0220] In a possible implementation, the processing module 102 is specifically configured to: determine the group similarity between the second array and the third array according to a preset similarity algorithm; the similarity algorithm includes any one of the following: cosine similarity, Pearson correlation coefficient, Euclidean distance, and Jaccard distance.

[0221] In a possible implementation, the device further includes a communication module 103. The communication module 103 is used to send a target data table pair and indication information to a management terminal after the processing module 102 determines that redundant data exists between the first data table and the second data table; the indication information is used to indicate that redundant data exists between the first data table and the second data table.

[0222] In a possible design manner of the second aspect, after the communication module 103 sends the target data table pair and the indication information to the management terminal, the processing module 102 is further configured to:

[0223] If the communication module 103 does not receive any response information to the corresponding indication information from the management terminal within the first preset time period, the number of times the first data table and the second data table are called within the preset time period is obtained; based on the number of times they are called, the data table to be deleted is determined; the data table to be deleted is the first data table or the second data table; and the data table to be deleted is deleted.

[0224] In a possible design of the second aspect, the processing module 102 is specifically used to: determine the data table with the smaller number of calls between the first data table and the second data table as a pending data table; if the number of calls of the pending data table is less than a preset threshold, determine the pending data table as a data table to be deleted.

[0225] In a possible design of the second aspect, the processing module 102 is also used for: if the number of times the first data table and the second data table are called is the same and both are less than a preset threshold, then obtaining characteristic parameters of the first data table and the second data table; the characteristic parameters include any one or more of the following: generation time, data volume; based on the characteristic parameters of the first data table and the second data table, determining the coefficients to be deleted of the first data table and the second data table; determining the data table with the largest coefficient to be deleted in the first data table and the second data table as the data table to be deleted.

[0226] In a possible design of the second aspect, after obtaining the first source field associated with each field in the first data table and the second source field associated with each field in the second data table, the processing module 102 is also used to: if the first source field associated with the third field in the first data table is the same as the second source field associated with the fourth field in the second data table, then the third field and the fourth field are determined as redundant fields.

[0227] In some embodiments, the communication module 103 may be referred to as a redundant distribution module.

[0228] Regarding the redundant identification device in the above embodiment, the specific manner in which each module performs operations and the corresponding beneficial effects have been described in detail in the aforementioned redundant identification method embodiment, and will not be repeated here.

[0229] Fig.15 FIG. 1 is a possible structural diagram of an electronic device according to an exemplary embodiment. The electronic device may be the above-mentioned redundant identification device, or may be a data warehouse server including the redundant identification device. Fig.15 As shown, the electronic device includes a processor 111 and a memory 112. The memory 112 is used to store instructions executable by the processor 111, and the processor 111 can implement the functions of each module in the redundant identification device in the above embodiment. The memory 112 stores at least one instruction, and the at least one instruction is loaded and executed by the processor 111 to implement the methods provided by the above method embodiments.

[0230] In a specific implementation, as an embodiment, the processor 111 (111-1 and 111-2) may include one or more CPUs, such as Fig.15 As an embodiment, the electronic device may include multiple processors 111, such as Fig.151 and 111-2 are shown in FIG. Each CPU in these processors 111 may be a single-core processor (Single-CPU) or a multi-core processor (Multi-CPU). The processor 111 here may refer to one or more devices, circuits, and / or processing cores for processing data (e.g., computer program instructions).

[0231] The memory 112 may be a read-only memory (ROM) or other types of static storage devices that can store static information and instructions, a random access memory (RAM) or other types of dynamic storage devices that can store information and instructions, or an electrically erasable programmable read-only memory (EEPROM), a compact disc read-only memory (CD-ROM) or other optical disc storage, optical disc storage (including compressed optical disc, laser disc, optical disc, digital versatile disc, Blu-ray disc, etc.), a disk computer storage medium or other magnetic storage device, or any other medium that can be used to carry or store the desired program code in the form of instructions or data structures and can be accessed by a computer, but is not limited thereto. The memory 112 may exist independently and be connected to the processor 111 via the communication bus 113. The memory 112 may also be integrated with the processor 111.

[0232] The communication bus 113 may be an industry standard architecture (ISA) bus, a peripheral component interconnect (PCI) bus, or an extended industry standard architecture (EISA) bus. The communication bus 113 may be divided into an address bus, a data bus, a control bus, etc. For ease of representation, Fig.15 Only one thick line is used in the diagram, but this does not mean that there is only one bus or only one type of bus.

[0233] In addition, in order to facilitate information interaction between the electronic device and other devices (for example, when the electronic device is a terminal, it interacts with the server, or when the electronic device is a server, it interacts with the terminal), the electronic device includes a communication interface 114. The communication interface 114 uses any transceiver-like device to communicate with other devices or communication networks, such as control systems, radio access networks (RAN), wireless local area networks (WLAN), etc. The communication interface 114 may include a receiving unit to implement a receiving function, and a sending unit to implement a sending function. The communication interface 114 and the processor 111 and the memory 112 are connected through a communication bus 113 to complete mutual communication.

[0234] In some embodiments, the electronic device may further include an input device 115 and an output device 116 , and these devices may be interconnected via a communication bus 113 and / or other forms of connection mechanisms.

[0235] Exemplarily, the input device 115 may include, for example, a keyboard, a mouse, etc. The output device 116 may output various information to the outside (for example, a management terminal), including the determined target data table pair, instruction information, etc. The output device 116 may include, for example, a display, a speaker, a printer, a communication network and a remote output device connected thereto, etc.

[0236] Of course, to simplify, Fig.15 Only some of the components related to the present disclosure in the electronic device are shown, and components such as a bus, an input / output interface, etc. are omitted. In addition, according to specific application situations, the electronic device may further include any other appropriate components.

[0237] The embodiment of the present application further provides a computer-readable storage medium, which stores computer instructions. When the computer instructions are executed on an electronic device, the electronic device executes the redundancy identification method in the above method embodiment.

[0238] For example, the computer readable storage medium may be a read-only memory (ROM), a random access memory (RAM), a compact disc (CD-ROM), a magnetic tape, a floppy disk, an optical data storage device, and the like.

[0239] The embodiment of the present application also provides a computer program product including computer instructions. When the computer instructions are executed on an electronic device, the electronic device executes the redundancy identification method in the above method embodiment.

[0240] Among them, the electronic device, computer-readable storage medium or computer program product provided in the embodiments of the present application are all used to execute the corresponding methods provided above. Therefore, the beneficial effects that can be achieved can refer to the beneficial effects in the corresponding methods provided above, and will not be repeated here.

[0241] Through the description of the above implementation methods, technicians in the relevant field can clearly understand that for the convenience and simplicity of description, only the division of the above-mentioned functional modules is used as an example. In actual applications, the above-mentioned functions can be assigned to different functional modules as needed, that is, the internal structure of the device (such as an electronic device) is divided into different functional modules to complete all or part of the functions described above. The specific working process of the system, device (such as an electronic device) and unit described above can refer to the corresponding process in the aforementioned method embodiment, and will not be repeated here.

[0242] In the several embodiments provided in the present application, it should be understood that the disclosed systems, devices (such as electronic devices) and methods can be implemented in other ways. For example, the device (such as electronic devices) embodiments described above are only schematic. For example, the division of the modules or units is only a logical function division. There may be other division methods in actual implementation. For example, multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be an indirect coupling or communication connection through some interfaces, devices or units, which can be electrical, mechanical or other forms.

[0243] The units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed on multiple network units. Some or all of the units may be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0244] In addition, each functional unit in each embodiment of the present application may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit. The above-mentioned integrated unit may be implemented in the form of hardware or in the form of software functional units.

[0245] If the integrated unit is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present application can be essentially or partly embodied in the form of a software product that contributes to the prior art, or all or part of the technical solution. The computer software product is stored in a storage medium, including several instructions for a computer device (which can be a personal computer, server, or network device, etc.) or a processor to perform all or part of the steps of the method described in each embodiment of the present application. The aforementioned storage medium includes: flash memory, mobile hard disk, read-only memory, random access memory, disk or optical disk, and other media that can store program codes.

[0246] The above is only a specific implementation of the present application, but the protection scope of the present application is not limited thereto. Any person skilled in the art who is familiar with the present technical field can easily think of changes or substitutions within the technical scope disclosed in the present application, which should be included in the protection scope of the present application. Therefore, the protection scope of the present application should be based on the protection scope of the claims.

Claims

1. A redundancy identification method, characterized in that: The method comprises: Acquire a target data table pair in a data warehouse; the target data table pair includes a first data table and a second data table; the first data table and the second data table are data tables in the data warehouse; Obtain a first source field associated with each field in the first data table, and a second source field associated with each field in the second data table; Determine the table similarity between the first data table and the second data table according to all the first source fields and all the second source fields; If the table similarity is greater than the similarity threshold, it is determined that redundant data exists between the first data table and the second data table.

2. The method according to claim 1, characterized in that The obtaining of the target data table pair includes: Acquire hierarchical information and subject domain information of a plurality of data tables in the data warehouse; the hierarchical information is used to indicate the level of the data table in the data warehouse; the subject domain information is used to indicate the subject category of the data table; The target data table pair is determined from the multiple data tables according to the hierarchical information and subject domain information of the multiple data tables; the two data tables included in the target data table pair have different hierarchies in the data warehouse; the two data tables included in the target data table pair have different subject categories; the two data tables included in the target data table pair are not a preset data table pair, and the preset data table pair is a predefined data table pair without redundant data.

3. The method according to claim 1, characterized in that The obtaining of the first source field associated with each field in the first data table and the second source field associated with each field in the second data table includes: Acquire first data lineage information of the first data table and second data lineage information of the second data table; the first data lineage information includes data lineage information of each field in the first data table, and the second data lineage information includes data lineage information of each field in the second data table; According to the first data lineage information, a field having a data lineage relationship with the first field is determined as a first source field associated with the first field; the first field is a field in the first data table; According to the second data lineage information, a field having a data lineage relationship with the second field is determined as a second source field associated with the second field; the second field is a field in the second data table.

4. The method according to claim 1, characterized in that: The determining the table similarity between the first data table and the second data table according to all the first source fields and all the second source fields includes: Combine all the first source fields and all the second source fields and remove duplicates to obtain a first array; A second array and a third array are generated according to the first array; the number of elements in the second array and the first array is the same; each element in the second array is used to represent the association result between the element at the corresponding position in the first array and the field in the first data table; each element in the third array is used to represent the association result between the element at the corresponding position in the first array and the field in the second data table; the association result is that an association relationship exists or does not exist; determining a group similarity between the second array and the third array; The group similarity is used as the table similarity between the first data table and the second data table.

5. The method according to any one of claims 1 to 4, characterized in that: After determining that redundant data exists between the first data table and the second data table, the method further includes: The target data table pair and indication information are sent to a management terminal; the indication information is used to indicate that redundant data exists between the first data table and the second data table.

6. The method according to claim 5, characterized in that After sending the target data table pair and the indication information to the management terminal, the method further includes: If no response information corresponding to the indication information is received from the management terminal within a first preset time period, obtaining the number of times the first data table and the second data table are called within a preset time period; Based on the number of times the data is called, determining a data table to be deleted; the data table to be deleted is the first data table or the second data table; Delete the data table to be deleted.

7. The method according to claim 6, characterized in that The step of determining the data table to be deleted based on the number of times the data table is called includes: Determine the data table with the smaller number of calls between the first data table and the second data table as a pending data table; If the number of times the pending data table is called is less than a preset threshold, the pending data table is determined as the data table to be deleted.

8. The method according to claim 7, characterized in that The step of determining the data table to be deleted based on the number of times the data table is called further comprises: If the number of times the first data table and the second data table are called is the same and both are less than the preset threshold, then characteristic parameters of the first data table and the second data table are obtained; the characteristic parameters include any one or more of the following: generation time, data volume; Determining coefficients to be deleted in the first data table and the second data table based on characteristic parameters of the first data table and the second data table; The data table with the largest coefficient to be deleted among the first data table and the second data table is determined as the data table to be deleted.

9. The method according to any one of claims 1 to 4, characterized in that: After obtaining the first source field associated with each field in the first data table and the second source field associated with each field in the second data table, the method further includes: If the first source field associated with the third field in the first data table is the same as the second source field associated with the fourth field in the second data table, the third field and the fourth field are determined to be redundant fields.

10. A redundant identification device, characterized in that: The device comprises: An acquisition module, used for acquiring a target data table pair in a data warehouse; the target data table pair includes a first data table and a second data table; the first data table and the second data table are data tables in the data warehouse; The acquisition module is further used to acquire a first source field associated with each field in the first data table, and a second source field associated with each field in the second data table; A processing module, configured to determine the table similarity between the first data table and the second data table according to all the first source fields and all the second source fields; The processing module is further configured to determine that redundant data exists between the first data table and the second data table if the table similarity is greater than a similarity threshold.

Images