A table duplicate detection method and device based on data blood relationship and column processing caliber

Abstract

The application discloses a table duplicate judgment method and device based on data blood relationship and column processing caliber, which comprises the following steps: obtaining the upstream and downstream node tables of a seed table and taking them as comparison tables respectively; judging whether the seed table and the comparison tables constitute primary duplication; judging whether the seed table and the comparison tables constitute secondary duplication; judging whether the seed table and the comparison tables constitute tertiary duplication, and outputting the comparison table information if the tertiary duplication is constituted; taking the upstream and downstream node tables of the table which constitutes the tertiary duplication with the seed table as new comparison tables, and repeating the above-mentioned tertiary duplicate judgment until there is no upstream and downstream node table. In the scene of full-amount table duplicate judgment, the columns in the table are grouped according to the table column characteristics, and then the repetition degree is calculated based on the grouping information. The application can more accurately and efficiently realize the duplicate judgment of the big data data warehouse table by hierarchical tracing and layer-by-layer grouping, and taking the caliber abstract as the main duplicate judgment basis.

Description

Technical Field

[0001] This invention belongs to the field of big data warehouse technology, and in particular relates to a table deduplication method and device based on data lineage and column processing caliber. Background Technology

[0002] A big data warehouse is a system used to store and process large-scale data; it is a data warehouse built based on big data technology. Compared to traditional data warehouses, big data warehouses can handle massive amounts of data, meeting the computational and storage needs of large-scale data. Big data warehouses can store data in various ways, among which structured tables are commonly used to store structured data. Structured tables use a structured data model, storing data in a data structure represented in tabular form, with each column representing a different data field. This tabular storage format has a clear structure, facilitating data organization and management, and enabling convenient data querying, analysis, and processing.

[0003] In data governance, to address the pain point of high data storage costs, data governance personnel often aim to decommission tables with high duplication rates in big data warehouses to reduce storage costs. Additionally, to address the pain point of poor usability of deduplication metrics due to excessive business growth, data governance personnel often aim to decommission duplicate metrics to improve metric reliability. Decommissioning duplicate metrics to improve reliability means identifying similar or duplicate metrics and retaining only one to ensure the metric's credibility. Currently, data governance personnel frequently employ the following two methods for these two aspects:

[0004] First, data feature comparison: extract table features (table name, description, quantity, primary key, etc.) and indicator features (atomic indicators, dimensions, etc.), and determine the degree of duplication between tables and indicators based on the similarity between features.

[0005] Second, data content comparison: The sampling table and indicator data are compared by left join to check if the data exists and whether the data is consistent, so as to obtain the degree of duplication between the table and the indicator.

[0006] The main drawbacks of the two methods mentioned above are: first, poor performance: the data feature comparison method has many false positives and false negatives, making the deduplication results unreliable; second, low efficiency: the data content comparison method compares data samples, which is essentially a calculation of the data, consuming computing resources, and the sampling comparison between tables is time-consuming.

[0007] Therefore, it is necessary to provide a table deduplication method that combines deduplication efficiency and accuracy with greater applicability. Summary of the Invention

[0008] To solve the above-mentioned technical problems, the present invention provides a method and apparatus for determining table deduplication based on data lineage and column processing caliber. The specific technical solution adopted is as follows:

[0009] A table deduplication method based on data lineage and column processing criteria, wherein the table deduplication method includes the following steps in a specified table deduplication scenario:

[0010] S110, Obtain the upstream and downstream node tables of the seed table and use them as comparison tables respectively; the seed table is the specified initial deduplication table;

[0011] S120, determine whether the seed table and the comparison table constitute a first-level duplicate; if they constitute a first-level duplicate, proceed to step S130.

[0012] S130, determine whether the seed table and the comparison table constitute a second-order duplicate; if they constitute a second-order duplicate, proceed to step S140.

[0013] S140, determine whether the seed table and the comparison table constitute a third-level repetition. If they constitute a third-level repetition, output the comparison table information and proceed to step S150.

[0014] S150, take the upstream and downstream node tables of the table that forms a three-level repetition with the seed table as the new comparison table, and repeat steps S120 to S140 until there are no more upstream and downstream node tables.

[0015] Further, in step S120, the calculation of the first-level repetition includes: querying the ROOT column of each column in the seed table and the comparison table respectively, wherein the ROOT column refers to a column without an upstream column; comparing the ROOT column of each column in the seed table with the ROOT column of each column in the comparison table respectively, and if the ROOT column of two columns is the same, then the two columns are considered to be first-level repetition columns; calculating the proportion of first-level repetition columns in the seed table and using it as the first-level repetition; if the first-level repetition is greater than a set threshold, then the two tables constitute first-level repetition.

[0016] Further, in step S130, the calculation of the second-level repetition degree includes: querying and standardizing the traceability summary of the first-level repeating columns in the seed table and the comparison table respectively, wherein the traceability summary refers to the expression describing the column processing process, and the traceability summary refers to the processing expression traced back to the ROOT column; comparing the standardized traceability summary of each first-level repeating column in the seed table with the standardized traceability summary of each first-level repeating column in the comparison table respectively, if the standardized traceability summary of two first-level repeating columns or the transformation value generated based on the standardized traceability summary is the same, then the two first-level repeating columns are considered to be second-level repeating columns; calculating the proportion of second-level repeating columns in the seed table and using it as the second-level repetition degree; if the second-level repetition degree is greater than a set threshold, then the two tables constitute a second-level repetition.

[0017] Further, in step S140, the calculation of the third-level repetition includes: querying the summary granularity of each column in the seed table and the comparison table respectively, wherein the summary granularity refers to the column grouped by during column processing, and obtaining the traceability summary after the summary granularity is standardized; comparing the standardized traceability summary of the summary granularity of each column in the seed table with the standardized traceability summary of the summary granularity of each column in the comparison table respectively, if the standardized traceability summary of the summary granularity of two columns or the transformation value generated based on the standardized traceability summary is the same, then the two columns are considered to be third-level repetition columns; calculating the proportion of third-level repetition columns in the seed table and using it as the third-level repetition; if the third-level repetition is greater than a set threshold, then the two tables constitute third-level repetition.

[0018] A table deduplication method based on data lineage and column processing caliber, in the scenario of full table deduplication in a data warehouse, the table deduplication method includes the following steps:

[0019] S210, retrieve the ROOT table from the data warehouse and use it as the seed table;

[0020] S220, group the columns in the seed table according to column characteristics;

[0021] S230, obtain the downstream table of the seed table and use it as the new seed table, repeat steps S220 and S230 until there is no downstream table;

[0022] S240, based on grouping information, determine whether each table in the data warehouse constitutes a second-level duplicate with other tables;

[0023] S250, based on the grouping information, determine whether the tables that constitute second-level duplication constitute third-level duplication. If they constitute third-level duplication, then assign the third-level duplication value and the corresponding table name.

[0024] Furthermore, in step S220, a group ID is generated based on the column characteristics, and columns with the same group ID are grouped together;

[0025] The generation of the group ID includes:

[0026] For columns with no upstream relationship, generate a group ID based on the column name;

[0027] For columns that do not have an upstream relationship but whose tables have an upstream relationship, generate a group ID based on the column's categorical summary;

[0028] For columns with upstream relationships and tables containing those columns also with upstream relationships, first obtain the upstream column and query the group ID of the upstream column. Then, obtain the column's caliber summary and replace the information in the caliber summary that involves the upstream column with the group ID of the upstream column's group. Finally, generate the group ID based on the replaced caliber summary.

[0029] Further, in step S240, assuming the calculation of the second-level repetition degree between table A and other tables in the data warehouse, table A is designated as the seed table, and the other tables in the data warehouse are respectively used as comparison tables; the group IDs of each column in the seed table and the comparison table are compared respectively. If the group IDs of two columns are the same, the two columns are considered to be second-level repetition columns; the proportion of second-level repetition columns in the seed table is calculated and used as the second-level repetition degree; if the second-level repetition degree is greater than the set threshold, the two tables constitute second-level repetition.

[0030] Further, in step S250, for the two tables that constitute a second-level repetition, the summary granularity of each column in the two tables is queried respectively. If the group ID of the summary granularity of any two columns in the two tables is the same, then the two columns constitute a third-level repetition column. The proportion of the third-level repetition column is used as the third-level repetition degree. If the third-level repetition degree is greater than the set repetition degree threshold, then the two tables constitute a third-level repetition.

[0031] A table deduplication device corresponding to the table deduplication method in the specified table deduplication scenario described above includes:

[0032] The query unit is used to query and retrieve the upstream and downstream node tables of the seed table or comparison table;

[0033] The first-level repeatability calculation unit is used to calculate the first-level repeatability between the seed table and the comparison table.

[0034] The secondary repeatability calculation unit is used to calculate the secondary repeatability between the seed table and the comparison table;

[0035] The third-level repeatability calculation unit is used to calculate the third-level repeatability between the seed table and the comparison table.

[0036] The discrimination output unit determines whether the seed table and the comparison table constitute a first-level repetition, a second-level repetition, or a third-level repetition based on the set repetition threshold, and outputs the comparison table information when a third-level repetition is constituted.

[0037] A table deduplication device corresponding to the table deduplication method in the above-mentioned full-scale table deduplication scenario includes:

[0038] The query unit is used to query and retrieve the ROOT table and its downstream tables in the data warehouse.

[0039] Grouping unit, used to group columns in the seed table and its downstream tables according to column characteristics;

[0040] Calculation unit: Calculates the second-level repetition of each table in the data warehouse with other tables based on the grouping information, and calculates the third-level repetition when two tables constitute a second-level repetition;

[0041] The discrimination output unit determines whether any two tables constitute a third-level repetition based on the set repetition threshold. If they constitute a third-level repetition, it outputs the third-level repetition value and the table name.

[0042] Compared with the prior art, the beneficial effects of the present invention are as follows:

[0043] (1) Fast plagiarism detection speed: Through hierarchical tracing, the initial judgment is made to narrow down the plagiarism detection range of the fine judgment, thereby improving the plagiarism detection speed.

[0044] (2) High deduplication efficiency: By grouping layer by layer, the ROOT node spreads the grouping layer by layer, which can complete the grouping with low computing resources and perform fast deduplication based on the grouping information.

[0045] (3) More accurate plagiarism detection: By tiered tracing and layered grouping, the abstract is used as the main basis for plagiarism detection, which is more in line with the duplication standard. Attached Figure Description

[0046] The accompanying drawings are provided to further illustrate the invention and form part of the specification. They are used together with the embodiments of the invention to explain the invention and do not constitute a limitation thereof.

[0047] Figure 1 This is a flowchart illustrating the table deduplication method for a specified table scenario provided in Embodiment 1 of the present invention.

[0048] Figure 2 This is a flowchart illustrating the table deduplication method in the data warehouse full table scenario provided in Embodiment 1 of the present invention;

[0049] Figure 3 This is a schematic diagram of the full representation of the data warehouse provided in Embodiment 1 of the present invention. Detailed Implementation

[0050] To make the objectives, technical solutions, and advantages of the embodiments of the present invention clearer, the technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0051] Table deduplication in data governance includes two scenarios: specified table deduplication and full table deduplication. Specified table deduplication involves providing a seed table (the initial deduplication table) and determining its duplication rate relative to its upstream and downstream node tables. The duplication calculation for the specified table has a clearly defined calculation object, and the scan range is a local area related to the seed table. Full table deduplication involves performing mutual deduplication on all tables in the data warehouse, scanning the entire data warehouse. Full table deduplication is performed when there are too many specified tables, leading to excessive calculation time, or when data warehouse administrators are unsure about the duplication situation in the data warehouse.

[0052] Example 1

[0053] Data model redundancy is an indicator that measures the degree of overlap between two data models. For example, the redundancy between Table A and Table B can be expressed as the ratio of the number of duplicate columns (columns in a data warehouse can also be called fields) in Table A to the total number of columns in Table A; that is, the proportion of fields in Table A that can be replaced by Table B. A column is the basic unit that makes up a table; a table will have at least one column. The criteria for determining duplicate columns can vary depending on the application requirements. This embodiment classifies column redundancy into three levels based on the processing source and scope summary of the column: Level 1 redundancy, Level 2 redundancy, and Level 3 redundancy.

[0054] First-level duplication refers to columns having the same source of processing. The source of processing refers to which fields in the upstream node table a column is processed from. For example, if column c1 is obtained by combining a1 and b1, then the source of processing for column c1 is a1 and b1. The source of processing refers to tracing back to the ROOT column. For example, if column c1 is obtained by combining a1 and b1, and a1 is processed by field o1, and b1 is processed by field s1, and o1 and s1 have no upstream fields (i.e., both o1 and s1 are ROOT columns), then the source of processing for column c1 is o1 and s1. If the source of processing is represented as an ordered array, then identical source processing means that the ordered arrays of the source processing for two columns are the same. For example, if columns c1 and c2 both have the source processing of o1 and s1, then columns c1 and c2 have the same source of processing, constituting first-level duplication.

[0055] Second-degree duplication refers to columns having the same source caliber summary. A caliber summary is the processed expression of a column. For example, if field c1 in table P2 is obtained by adding fields a1 and b1 in table P1, then the processed expression a1+b1 is the caliber summary. The source caliber summary refers to the processed expression traced back to the ROOT column. For example, the caliber summary of field c1 is a1+b1, the caliber summary of a1 is o1+3, and the caliber summary of b1 is s1*5. Since o1 and s1 have no upstream fields, the source caliber summary of c1 is (o1+3)+(s1*5). Identical source caliber summaries mean that the processed expressions after tracing the source of two columns are the same. For example, if the source caliber summaries of c1 and c2 are both (o1+3)+(s1*5), then c1 and c2 have the same source caliber summary, constituting second-degree duplication.

[0056] Level 3 repetition refers to the identical source categorization summary of the summary granularity of columns. In databases or data warehouses, the meaning of the `GROUP BY` function is to divide a dataset into several smaller regions according to certain rules, and then process the data in several smaller regions. The summary granularity refers to the column grouped by during column processing. For example, if `c5` is processed by `c4`, then the summary granularity of column `c5` is `c4`. The extraction of summary granularity is based on the SQL parser, resulting in an SQL Abstract Syntax Tree (AST). The column information grouped by is extracted from the AST, i.e., the summary granularity. The source categorization summary of the column's summary granularity refers to tracing the source of the summary granularity summary. If the source categorization summaries of the summary granularity are identical, it constitutes level 3 repetition. For example, if the summary granularity of `c5` is `c4`, and the summary granularity of `c6` is `c3`, and the source categorization summaries of both `c3` and `c4` are `c1+c2`, then columns `c5` and `c6` constitute level 3 repetition.

[0057] To improve the efficiency and accuracy of duplicate calculation in table deduplication within a data warehouse, this embodiment provides a table deduplication method based on data lineage and column processing parameters, based on the aforementioned grading standards. For example... Figure 1 As shown, in the scenario of a specified table, the deduplication method for this table mainly includes the following steps:

[0058] S110: Obtain the first-degree upstream and downstream node tables of the seed table and use them as comparison tables. Here, upstream and downstream node tables refer to tables in the data lineage that are upstream or downstream of the seed table, while first-degree upstream and downstream node tables refer to upstream and downstream node tables with a relationship depth of one degree with the seed table. For example, if table A1 is processed to obtain table A2, and table A2 is processed to obtain table A3, then there is a data lineage A1->A2->A3 between A1, A2, and A3. For A2, A1 is its upstream, and A3 is its downstream, with a relationship depth of one degree. That is, A1 and A3 are respectively the first-degree upstream node table and the first-degree downstream node table of A2. However, if for A1, A3 is its downstream, and its relationship depth is two degrees.

[0059] Data lineage refers to the transmission paths and relationships of data during processing or storage. For table data, data lineage is the data association relationship based on table processing task data and table metadata. Data lineage is generally built and stored using graph databases. Using graph databases can more intuitively display the data transmission paths and relationships, facilitating data quality and compliance analysis and management. When retrieving the first-degree upstream and downstream node tables, the first-degree upstream and downstream node tables of the node containing the seed table can be queried through the graph database interface. The graph database interface refers to the API interface and services provided by the graph database that stores table data lineage for querying, saving, and modifying data.

[0060] S120: Calculate the first-level repetition degree between the seed table and the comparison table, and determine whether the two tables constitute first-level repetition based on the set first-level repetition degree threshold; if they constitute first-level repetition, proceed to step S130; otherwise, the two tables do not repetite.

[0061] The calculation steps for the first-level repetition degree are as follows: query the ROOT column of each column in the seed table and the comparison table respectively. The ROOT column refers to the column without an upstream column; compare the ROOT column of each column in the seed table with the ROOT column of each column in the comparison table. If the ROOT column of two columns is the same, the two columns are considered to be first-level repetition columns; calculate the proportion of the first-level repetition column in the seed table to the total number of columns in the seed table, and use it as the first-level repetition degree.

[0062] S130: Calculate the second-order repetition of the seed table and the comparison table, and determine whether the two tables constitute second-order repetition based on the set second-order repetition threshold; if they constitute second-order repetition, proceed to step S140; otherwise, the two tables do not repetite.

[0063] The calculation steps for the second-order repeatability are as follows:

[0064] (1) Query the source citation summaries of the first-level duplicate columns in the seed table and comparison table respectively and standardize them, including case standardization, SQL format standardization, function standardization, function elimination, sorting standardization, etc. (examples of each standardization are shown in Table 1). Since the source citation summaries are expression text, there will be many subtle differences before standardization. For example, the source citation summaries of c1 and c2 are both a1+b1, but the text represented by the source citation summary of c2 may be a1+b1. Therefore, it is necessary to standardize the source citation summaries.

[0065] Table 1 Examples of Standardization

[0066]

[0067] (2) Compare the standardized source citation digests of each first-level repeating column in the seed table with the standardized source citation digests of each first-level repeating column in the comparison table. If the standardized source citation digests of two first-level repeating columns are the same, then the two first-level repeating columns are considered to be second-level repeating columns. For cases where the source citation digest is very long, the MD5 (Message Digest Algorithm 5) value of the source citation digest can be calculated first, and then the MD5 values ​​can be compared. If they are the same, the two columns constitute second-level repeating columns. MD5 is a widely used hash algorithm used to calculate the digest or fingerprint of a text. It can convert input text of any length into a fixed-length 128-bit (16-byte) hash value. Since it is existing technology, the specific calculation process will not be described here.

[0068] (3) Calculate the proportion of the second-level repeating column in the seed table to the total number of columns in the seed table, and use it as the second-level repeating degree.

[0069] S140: Calculate the third-level repetition degree between the seed table and the comparison table, and determine whether the two tables constitute a third-level repetition based on the set third-level repetition degree threshold; if they constitute a third-level repetition, output the third-level repetition degree value and the information of the comparison table (such as the table name or the node position), and use the first-level upstream and downstream node tables of the comparison table as the new comparison table, repeating steps S120 to S140 until there are no more upstream and downstream node tables.

[0070] The calculation steps for level three repeatability are as follows:

[0071] Query the summary granularity of each column in the seed table and the comparison table respectively, and obtain the source caliber summary of the summary granularity; compare the source caliber summary of the summary granularity of each column in the seed table with the source caliber summary of the summary granularity of each column in the comparison table. If the source caliber summary of the summary granularity of two columns is the same, then the two columns are considered to be third-level repeating columns; calculate the proportion of third-level repeating columns in the seed table to the total number of columns in the seed table, and use it as the third-level repeatability.

[0072] Similar to the calculation of second-order repeatability, since it involves comparing source tracing summaries, they also need to be standardized before comparison. For very long source tracing summaries, the MD5 value of each summary can be calculated first, and then the MD5 values ​​can be compared to see if they are the same.

[0073] The above describes a deduplication method for specified table scenarios. Calculating the first-level duplication score filters out most tables that don't require further second-level or third-level duplication scoring, significantly saving computational resources and improving efficiency. However, for full-scale data warehouse tables, since it's necessary to separately determine the duplication of all tables in the data warehouse with other tables (equivalent to specifying multiple seed tables), using the above method would still be time-consuming, especially when the data warehouse is large and the data lineage is deep (equivalent to a large number of specified seed tables). Traversing each node during the process of obtaining the source summary would consume a significant amount of time. Therefore, this embodiment also provides a deduplication method for full-scale data warehouse tables. This method aims to pre-group the columns in the full table, so that when judging duplicate columns, only a query of the first-level upstream node is needed. The grouping information can be used for quick judgment, thereby avoiding repeated source summary tracing. Figure 2 As shown, the main steps include:

[0074] S210 retrieves the ROOT table of the data warehouse through the graph database interface and uses it as the seed table.

[0075] S220: Group the columns in the seed table and generate group IDs based on column characteristics. Columns with the same group ID are grouped together. Group ID generation falls into three categories: First, for columns without upstream relationships, the column name is used as the grouping basis, and the MD5 hash of the column name is calculated, which is then used as the group ID. Second, for columns with upstream relationships, both the table and the table first obtain the upstream column and query its group ID. Then, the column's summary is obtained, and information related to upstream columns in the summary is replaced with the group ID of the upstream column's group. The replaced summary is then used as the grouping basis, and its MD5 hash is calculated to generate the group ID. Third, for columns without upstream relationships (usually constant columns), the column's summary is used as the grouping basis, and its MD5 hash is calculated to generate the group ID.

[0076] In step S230, the graph database interface is used to query and retrieve the first-degree downstream table in the table data lineage of the seed table. This first-degree downstream table is then used as the new seed table to execute steps S220-S230. If the downstream table is empty, grouping is completed and step S240 is executed. Through grouping, we obtain the grouping information of columns in all tables in the data warehouse, meaning each column has a corresponding group ID.

[0077] by Figure 3 Taking the data warehouse shown as an example, the data warehouse contains 5 tables: O, A, B, C, and D. Table O includes three columns: o1, o2, and o3; Table A includes three columns: a1, a2, and a3; Table B includes three columns: b1, b2, and b3; Table C includes three columns: c1, c2, and c3; and Table D includes three columns: d1, d2, and d3. Furthermore, a1 = o1 = b1, a2 = o2 = b2, a3 = o3 = b3, c1 = a1 + b1 = d1, c2 = a2 + b2 = d2, and c3 = a3 + b3 = d3.

[0078] Step 1: Locate the ROOT table in the data warehouse and use it as the seed table. There is only one ROOT table, namely table O.

[0079] Step 2: Group the columns in the seed table. Columns o1, o2, and o3 in table O are columns without upstream relationships. Take the MD5 hash of their column names and use it as the ID of their group, i.e., MD5(o1), MD5(o2), and MD5(o3), respectively.

[0080] Step 3: Find the first-degree downstream tables of the seed table and use them as new seed tables to continue column grouping. The first-degree downstream tables of table O are tables A and B. Since a1 = o1 = b1, a2 = o2 = b2, a3 = o3 = b3, the group ID of columns a1 and b1 is also MD5(o1), the group ID of columns a2 and b2 is also MD5(o2), and the group ID of columns a3 and b3 is also MD5(o3). Continuing to search the first-degree downstream tables of tables A and B, we find that the first-degree downstream tables of tables A and B are tables C and D. Since c1 = a1 + b1 = d1, c2 = a2 + b2 = d2, and c3 = a3 + b3 = d3, the caliber summary of columns c1 and d1 is a1 + b1, the caliber summary of columns c2 and d3 is a2 + b2, and the caliber summary of columns c3 and d3 is a3 + b3. We replace the information of upstream columns in the caliber summary with the group ID of the group to which the upstream column belongs, and then calculate the MD5 value of the replaced caliber summary to generate the group ID. We can get that the group ID of columns c1 and d1 is MD5(MD5(o1) + MD5(o1)), the group ID of columns c2 and d2 is MD5(MD5(o2) + MD5(o2)), and the group ID of columns c3 and d3 is MD5(MD5(o3) + MD5(o3)).

[0081] Tables A and B have no downstream tables, so the grouping is complete. From the above grouping, we know that columns o1 / a1 / b1 belong to the same group with group ID MD5(o1), columns o2 / a2 / b2 belong to the same group with group ID MD5(o2), columns o3 / a3 / b3 belong to the same group with group ID MD5(o3), columns c1 / d1 belong to the same group with group ID MD5(MD5(o1)+MD5(o1)), columns c2 / d2 belong to the same group with group ID MD5(MD5(o2)+MD5(o2)), and columns c3 / d3 belong to the same group with group ID MD5(MD5(o3)+MD5(o3)). Through this hierarchical grouping, each column in each table in the data warehouse has a corresponding group ID.

[0082] S240, based on grouping information, calculate the second-order duplication value of each table in the data warehouse compared to other tables, and determine whether a second-order duplication exists based on a set duplication threshold. Specifically, assuming the calculation of the second-order duplication of table A in the data warehouse compared to other tables, table A is designated as the seed table, and the other tables in the data warehouse are designated as comparison tables. The following duplication determination steps are performed: compare the group IDs of each column in the seed table and the comparison table respectively. If two columns have the same group ID, they are considered to be second-order duplication columns. Calculate the proportion of second-order duplication columns in the seed table and use it as the duplication value. If the duplication value is greater than the set duplication threshold, the seed table and the comparison table constitute a second-order duplication.

[0083] Similarly Figure 3Taking the data warehouse shown as an example, suppose we want to calculate the second-order repetition between tables C and D. By comparing the group IDs of each column in tables C and D, we can see that the group IDs of columns c1, c2, and c3 in table C are the same as the group IDs of columns d1, d2, and d3 in table D, respectively. Therefore, there are 3 duplicate columns, and the second-order repetition is 3 / 3. Similarly, we can find that the second-order repetition between tables C and B is 0 / 3, the second-order repetition between tables C and A is 0 / 3, and the second-order repetition between tables C and O is 0 / 3.

[0084] S250: For two tables constituting a second-level duplicate, query the summary granularity of each column in both tables. If the group IDs of the summary granularity of any two columns in the two tables are the same, then these two columns constitute third-level duplicate columns. The proportion of the third-level duplicate columns is used as the third-level duplicate degree. If the third-level duplicate degree is greater than the set duplicate degree threshold, then the two tables constitute a third-level duplicate, and the third-level duplicate degree value and the corresponding table name are output.

[0085] Example 2

[0086] Corresponding to the aforementioned table deduplication method based on data lineage and column processing standards, this embodiment provides a table deduplication device based on data lineage and column processing standards, applied in a specified seed table scenario. It mainly includes a query unit, a first-level repetition calculation unit, a second-level repetition calculation unit, a third-level repetition calculation unit, and a discrimination output unit. Specifically, the query unit queries and retrieves the upstream and downstream node tables of the seed table or comparison table; the first-level repetition calculation unit calculates the first-level repetition between the seed table and the comparison table according to the aforementioned first-level repetition calculation method; the second-level repetition calculation unit calculates the second-level repetition between the seed table and the comparison table according to the aforementioned second-level repetition calculation method; the third-level repetition calculation unit calculates the third-level repetition between the seed table and the comparison table according to the aforementioned third-level repetition calculation method; and the discrimination output unit determines whether the seed table and the comparison table constitute first-level, second-level, or third-level repetition based on a set repetition threshold, and outputs the comparison table information when they constitute third-level repetition. The device has the same functions and beneficial effects as the above-mentioned table deduplication method. For technical details not described in detail in this embodiment, please refer to the table deduplication method for a specified seed table application scenario provided in Embodiment 1 of the present invention.

[0087] Example 3

[0088] Corresponding to the table deduplication method based on data lineage and column processing criteria described above, this embodiment provides a table deduplication device based on data lineage and column processing criteria, applied in a data warehouse full-scale table scenario. It mainly includes a query unit, a grouping unit, a calculation unit, and a discrimination output unit. The query unit queries and retrieves the ROOT table and its downstream tables in the data warehouse; the grouping unit groups the columns in the seed table and its downstream tables according to column characteristics, with specific grouping methods described in Embodiment 1; the calculation unit calculates the duplication value between each table in the data warehouse and other tables based on the grouping information; and the discrimination output unit determines whether any two tables constitute a third-level duplication based on a set duplication threshold. If a third-level duplication is found, the duplication value and table name are output. This device possesses the functions and beneficial effects corresponding to the aforementioned table deduplication method. Technical details not described in detail in this embodiment can be found in the table deduplication method for the data warehouse full-scale table application scenario provided in Embodiment 1 of this invention.

[0089] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, and not to limit them; under the concept of the present invention, the technical features of the above embodiments or different embodiments can also be combined, and the steps can be implemented in any order. For the sake of brevity, they are not provided in the details; although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features; and these modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of this application.

Claims

1. A table deduplication method based on data lineage and column processing caliber, characterized in that, In scenarios where table deduplication is specified, the table deduplication method includes the following steps: S110, Obtain the upstream and downstream node tables of the seed table and use them as comparison tables respectively; the seed table is the specified initial deduplication table; S120, determine whether the seed table and the comparison table constitute a first-level duplicate; if they constitute a first-level duplicate, proceed to step S130. S130, determine whether the seed table and the comparison table constitute a second-order duplicate; if they constitute a second-order duplicate, proceed to step S140. S140, determine whether the seed table and the comparison table constitute a third-level repetition. If they constitute a third-level repetition, output the comparison table information and proceed to step S150. S150, take the upstream and downstream node tables of the table that forms a three-level repetition with the seed table as the new comparison table, and repeat steps S120~S140 until there are no more upstream and downstream node tables. The determination of first-level duplication includes: querying the ROOT column of each column in the seed table and the comparison table respectively, where the ROOT column refers to a column without an upstream column; comparing the ROOT column of each column in the seed table with the ROOT column of each column in the comparison table respectively, and if the ROOT column of two columns is the same, then the two columns are considered to be first-level duplication columns; calculating the proportion of first-level duplication columns in the seed table and using it as the first-level duplication degree; and determining whether it constitutes first-level duplication based on the first-level duplication degree. The determination of second-order duplication includes: querying and standardizing the source tracing summary of the first-order duplicate columns in the seed table and the comparison table, where the source tracing summary refers to the processed expression traced back to the ROOT column; comparing the standardized source tracing summary of each first-order duplicate column in the seed table with the standardized source tracing summary of each first-order duplicate column in the comparison table; if the standardized source tracing summary of two first-order duplicate columns or the transformation value generated based on the standardized source tracing summary is the same, then the two first-order duplicate columns are considered to be second-order duplicate columns; calculating the proportion of second-order duplicate columns in the seed table and using it as the second-order duplication degree; and determining whether second-order duplication is constituted based on the second-order duplication degree. The determination of third-level repetition includes: querying the summary granularity of each column in the seed table and the comparison table respectively, and obtaining the traceability summary after standardization of the summary granularity. The summary granularity refers to the column grouped by during column processing; comparing the standardized traceability summary of the summary granularity of each column in the seed table with the standardized traceability summary of the summary granularity of each column in the comparison table. If the standardized traceability summary of the summary granularity of two columns or the transformation value generated based on the standardized traceability summary is the same, then the two columns are considered to be third-level repetition columns; calculating the proportion of third-level repetition columns in the seed table and using it as the third-level repetition degree; and determining whether third-level repetition is constituted based on the third-level repetition degree.

2. A table deduplication method based on data lineage and column processing caliber, characterized in that, In the scenario of full table deduplication in a data warehouse, the table deduplication method includes the following steps: S210, retrieve the ROOT table from the data warehouse and use it as the seed table; S220, Group the columns in the seed table according to column characteristics: Group IDs are generated based on column characteristics; columns with the same group ID belong to the same group. The generation of the group IDs includes: For columns with no upstream relationship, generate a group ID based on the column name; For columns that do not have an upstream relationship but whose tables have an upstream relationship, generate a group ID based on the column's categorical summary; For columns with upstream relationships and tables containing the columns also with upstream relationships, first obtain the upstream column of the column and query the group ID of the upstream column. Then obtain the column's caliber summary and replace the information in the caliber summary involving the upstream column with the group ID of the group to which the upstream column belongs. Finally, generate the group ID based on the replaced caliber summary. S230, obtain the downstream table of the seed table and use it as the new seed table, repeat steps S220 and S230 until there is no downstream table; S240, based on grouping information, determine whether each table in the data warehouse constitutes a second-level duplicate with other tables: To determine whether table A in the data warehouse constitutes a second-order duplicate with other tables, table A is designated as the seed table, and the other tables in the data warehouse are designated as comparison tables. The group IDs of each column in the seed table and the comparison tables are compared. If two columns have the same group ID, they are considered to be second-order duplicate columns. The percentage of second-order duplicate columns in the seed table is calculated and used as the second-order duplicate degree. Based on the second-order duplicate degree, it is determined whether a second-order duplicate exists. S250, based on grouping information, determine whether tables constituting second-level duplication constitute third-level duplication. If they constitute third-level duplication, determine the third-level duplication value and the corresponding table name: For two tables constituting second-level duplication, query the summary granularity of each column in the two tables. If the group ID of the summary granularity of any two columns in the two tables is the same, then the two columns constitute third-level duplication columns. Use the proportion of third-level duplication columns as the third-level duplication degree, and determine whether third-level duplication is constituted based on the third-level duplication degree.

3. A table deduplication device based on data lineage and column processing caliber, corresponding to the table deduplication method of claim 1, characterized in that, include: The query unit is used to query and retrieve the upstream and downstream node tables of the seed table or comparison table; The first-level repeatability calculation unit is used to calculate the first-level repeatability between the seed table and the comparison table. The secondary repeatability calculation unit is used to calculate the secondary repeatability between the seed table and the comparison table; The third-level repeatability calculation unit is used to calculate the third-level repeatability between the seed table and the comparison table. The discrimination output unit determines whether the seed table and the comparison table constitute a first-level, second-level, or third-level repetition based on the calculated repetition degree, and outputs the comparison table information when a third-level repetition is constituted.

4. A table deduplication device based on data lineage and column processing caliber, corresponding to the table deduplication method of claim 2, characterized in that, include: The query unit is used to query and retrieve the ROOT table and its downstream tables in the data warehouse. Grouping unit, used to group columns in the seed table and its downstream tables according to column characteristics; Calculation unit: Calculates the second-level repetition of each table in the data warehouse with other tables based on the grouping information, and calculates the third-level repetition when two tables constitute a second-level repetition; The output unit determines whether any two tables constitute a level 3 repetition based on the calculated level 3 repetition. If they do, it outputs the level 3 repetition value and the table name.

Images