Data table cleanup methods, devices, equipment, storage media, and program products
By performing word segmentation and lineage analysis on complex cleanup data tables, cleanup SQL statements are generated to automatically clean up the data tables, solving the problems of low cleanup efficiency and resource waste in existing technologies, and achieving efficient and accurate data cleanup.
Patent Information
- Application Number
- CN202410258157.8
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2024-03-07
- Publication Date
- 2026-11-17
- Estimated Expiration
- 2044-03-07
AI Technical Summary
Existing technologies for cleaning complex data tables are inefficient, require significant human resources, and suffer from redundant data storage and reduced system efficiency due to the inability to determine the data tables that need to be associated.
By segmenting the target cleanup requirements, the cleanup fields are obtained, the first data table containing the cleanup fields is determined, and lineage analysis is performed in the metadata management system to generate cleanup SQL statements and automatically clean up complex cleanup data tables.
It automates the cleaning of complex data tables, improving cleaning efficiency, reducing manpower consumption, optimizing application storage structure, controlling data scale, and avoiding accidental data deletion and waste of system resources.
Smart Images

Figure CN118132546B_ABST
Abstract
Description
Technical Field
[0001] This application relates to the field of big data, and in particular to a data table cleaning method, apparatus, device, storage medium, and program product. Background Technology
[0002] With the introduction of relevant data security and information protection regulations by regulatory authorities, it has been clarified that certain data meeting the cleanup criteria must be cleaned up in a timely manner. Failure to do so carries the risk of penalties from regulatory authorities. The data requiring cleanup includes complex data tables.
[0003] Complex cleanup tables refer to data tables whose cleanup conditions cannot be determined based on the status flags of a single table. Cleanup requires checking multiple fields and joining multiple tables. In practice, for complex cleanup tables, because it's difficult to pinpoint which tables to join, redundant data storage is often used to avoid accidental deletion. Alternatively, developers rely on their experience to determine which tables need to be joined, ensuring they contain the necessary cleanup fields. Then, communication between upstream and downstream developers clarifies the cleanup logic for the complex cleanup table before execution.
[0004] The aforementioned cleanup of complex data tables relies on the experience of developers and requires clear communication between upstream and downstream developers to define the cleanup logic. This can result in low data cleanup efficiency and high human resource investment. Summary of the Invention
[0005] This invention provides a data table cleaning method, apparatus, device, storage medium, and program product to solve the problems of low data cleaning efficiency and high human resource input in the prior art.
[0006] In a first aspect, the present invention provides a data table cleaning method, the method comprising:
[0007] Each target cleanup requirement is segmented into words to obtain the segmentation results; the target cleanup requirements are the cleanup requirements corresponding to the target complex cleanup data table; the segmentation results include cleanup fields.
[0008] For each cleanup field, a first data table containing the cleanup field is determined, and a second data table is filtered from the first data table; the second data table is the original data table related to the cleanup field.
[0009] Based on the cleanup fields and word segmentation results in the second data table, a cleanup SQL statement is generated, and the target complex cleanup data table is cleaned according to the cleanup SQL statement.
[0010] Optionally, determining a first data table containing the cleanup fields includes:
[0011] Retrieve the first data table containing the cleanup field from the metadata management system; the metadata management system records the field information corresponding to each data table.
[0012] Optionally, the metadata management system also records the processing SQL statements corresponding to each data table; the processing SQL statements are used to represent the processing procedure of the data table; filtering the second data table from the first data table includes:
[0013] For each of the first data tables, a lineage analysis is performed on the processing SQL statement to obtain the lineage analysis results; the lineage analysis results include the source table of the cleanup field;
[0014] Based on the lineage analysis results of each first data table, the source table of the cleanup field is determined to be its own second data table; each cleanup field corresponds to at least one second data table.
[0015] Optionally, a cleanup SQL statement is generated based on the cleanup field in the second data table and the word segmentation result, including:
[0016] Generate a cleaning parameter table; the cleaning parameter table includes the target complex cleaning data table, the word segmentation processing result, a second data table, and the name of the target field; the name of the target field is the field name in the second data table that corresponds to the cleaning field;
[0017] Define the account ID field as the associated field;
[0018] Based on the associated fields and the cleanup parameter table, generate cleanup SQL statements for the target complex cleanup data table.
[0019] Optionally, the word segmentation processing result further includes: judgment logic, cleanup conditions; generating cleanup SQL statements for the target complex cleanup data table based on the associated fields and the cleanup parameter table, including:
[0020] Based on the cleanup SQL statement template, the associated fields, and the cleanup parameter table, a cleanup SQL statement is generated for the target complex cleanup data table; the cleanup SQL statement template contains the relationship between the filtering conditions, which are composed of target fields, judgment logic, and cleanup conditions.
[0021] Optionally, the method further includes:
[0022] Store the cleanup parameter table corresponding to the target complex cleanup data table in the metadata management system;
[0023] When any of the second data tables in the cleanup parameter table no longer records the target field, the cleanup parameter table is updated, and the cleanup SQL statement is regenerated based on the updated cleanup parameter table.
[0024] In a second aspect, the present invention provides a data table cleaning device, the device comprising:
[0025] The word segmentation module is used to perform word segmentation on various target cleanup requirements and obtain various word segmentation results; the target cleanup requirements are the cleanup requirements corresponding to the target complex cleanup data table; the word segmentation results include cleanup fields.
[0026] A filtering module is used to determine a first data table containing each cleanup field, and to filter a second data table from the first data table; the second data table is the original data table related to the cleanup field.
[0027] The processing module is used to generate a cleanup SQL statement based on the cleanup field and the word segmentation result in the second data table, and to clean up the target complex cleanup data table according to the cleanup SQL statement.
[0028] Thirdly, the present invention provides an electronic device, comprising: at least one processor and a memory;
[0029] The memory stores instructions that the computer executes;
[0030] At least one processor executes computer execution instructions stored in memory, causing at least one processor to perform the method as described in any of the first aspects.
[0031] Fourthly, the present invention provides a computer-readable storage medium storing computer-executable instructions, which, when executed by a processor, implement the method of any one of the first aspects.
[0032] Fifthly, the present invention provides a computer program product, including a computer program that, when executed by a processor, implements the method described in any of the first aspects.
[0033] This invention provides a data table cleanup method, apparatus, device, storage medium, and program product. It involves performing word segmentation processing on various target cleanup requirements to obtain segmentation results. The target cleanup requirements are cleanup requirements corresponding to target complex cleanup data tables. The segmentation results include cleanup fields. For each cleanup field, a first data table containing the cleanup field is determined, and a second data table is selected from the first data table. The second data table is an original data table related to the cleanup field. Cleanup SQL statements are generated based on the cleanup fields in the second data table and the segmentation results. The target complex cleanup data tables are cleaned according to the cleanup SQL statements. By performing word segmentation processing on the target cleanup requirements and determining the original data table containing the cleanup fields, cleanup SQL statements are generated, achieving automated cleanup of complex cleanup data tables, improving cleanup efficiency, and reducing manpower consumption. Attached Figure Description
[0034] The accompanying drawings, which are incorporated in and form part of this specification, illustrate embodiments consistent with the invention and, together with the description, serve to explain the principles of the invention.
[0035] Figure 1 This is an application scenario diagram of a data table cleaning method provided in an embodiment of the present invention;
[0036] Figure 2 This is a flowchart illustrating a data table cleanup method provided in an embodiment of the present invention.
[0037] Figure 3 A flowchart illustrating another data table cleanup method provided in an embodiment of the present invention;
[0038] Figure 4 This is a schematic diagram of the structure of a data table cleaning device provided in an embodiment of the present invention;
[0039] Figure 5 This is a schematic diagram of the hardware structure of an electronic device provided in an embodiment of the present invention.
[0040] The accompanying drawings have illustrated specific embodiments of the invention, which will be described in more detail below. These drawings and descriptions are not intended to limit the scope of the invention in any way, but rather to illustrate the concept of the invention to those skilled in the art through reference to particular embodiments. Detailed Implementation
[0041] Exemplary embodiments will now be described in detail, examples of which are illustrated in the accompanying drawings. When the following description relates to the drawings, unless otherwise indicated, the same numbers in different drawings denote the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with the present invention.
[0042] It should be noted that the data table cleaning method and apparatus of this application can be used in the financial field, or in any field other than the financial field. This application does not limit the application field of the data table cleaning method and apparatus.
[0043] Complex data table cleanup refers to data tables that require data from multiple tables to be cleaned. In existing technologies, because the data tables that need to be linked cannot be determined, redundant data storage is often used to avoid data deletion errors. However, redundant data storage leads to low resource utilization, ineffective control of data scale, and reduced system operating efficiency.
[0044] Alternatively, the cleanup logic can be clarified through communication between upstream and downstream developers, leveraging the developers' experience, to clean up complex data tables. However, this method suffers from time-consuming communication, low cleanup efficiency, and significant human resource investment.
[0045] To address the aforementioned problems, this application proposes a data table cleaning method. Figure 1 This is an application scenario diagram of a data table cleaning method provided in an embodiment of the present invention, such as... Figure 1 As shown, this method can be applied to terminal devices. For target cleanup requirements, cleanup fields can be obtained through word segmentation. Based on these cleanup fields, a first data table containing the cleanup fields can be obtained. Then, a second data table containing the original data can be filtered out. Finally, cleanup SQL statements can be generated based on the word segmentation results and the second data table. These SQL statements are then used to clean up the target complex cleanup data table, achieving automatic cleanup of complex cleanup data tables without the need for communication between upstream and downstream developers. Data that meets cleanup requirements can be cleaned promptly, optimizing application storage structure, effectively controlling data scale, improving production data access efficiency, and reducing system resource waste.
[0046] Figure 2 This is a flowchart illustrating a data table cleanup method provided in an embodiment of the present invention. The method can be applied to a data table cleanup device and includes steps S201 to S203:
[0047] Step S201: Perform word segmentation on each target cleaning requirement and obtain each word segmentation result; the target cleaning requirement is the cleaning requirement corresponding to the target complex cleaning data table; the word segmentation result contains the cleaning field.
[0048] For a complex data table to be cleaned, its corresponding cleanup requirements can be obtained. By performing word segmentation on the cleanup requirements, the word segmentation results can be obtained, and then cleanup SQL statements can be generated based on the word segmentation results.
[0049] Optionally, different complex cleanup data tables may have different cleanup requirements. For example, the cleanup requirement for complex cleanup data table 1 may be cleanup requirement 1, and the cleanup requirement for complex cleanup data table 2 may be cleanup requirement 2, and so on. Cleanup requirements may originate from business needs or regulatory standards.
[0050] After obtaining the target cleanup requirements, they can be segmented into words. The segmentation results contain at least one cleanup field, which is used to retrieve the data table that the target complex cleanup data table depends on.
[0051] For example, regarding data table X, the business requirement is that data cleanup is required if the account status is "closed." However, data table X does not have an "account status" field. It is necessary to join other data tables to obtain account status information, and then determine whether the cleanup requirements are met before proceeding with the cleanup. Data table X is the target complex cleanup data table. For the above cleanup requirements, the cleanup field is the "account status."
[0052] Among them, the target cleanup requirements corresponding to a complex cleanup data table can be one, two or more. For example, when the cleanup requirement is: clean up the data if the account status is closed and the account opening date is less than 19991231, then the cleanup fields are account status and account opening date.
[0053] Step S202: For each cleanup field, determine a first data table containing the cleanup field, and filter a second data table from the first data table; the second data table is the original data table related to the cleanup field.
[0054] Once the cleanup field is determined, a first data table containing that cleanup field can be obtained. If there are multiple cleanup fields, a first data table corresponding to each cleanup field is determined separately. There may be multiple first data tables containing the cleanup fields. To improve the timeliness of cleanup and avoid excessively long dependency chains, a second data table, which is the original data table related to the cleanup field, needs to be selected from the first data tables.
[0055] For example, regarding the account status field, when there are data tables A, B, C, and D, and data tables A, B, and D contain the account status field, the original data table for the account status field can be determined from the three data tables, such as data tables A and B. Then, the target complex cleanup data table can be cleaned based on the account status in the original data table.
[0056] For example, if data cleaning is performed directly based on a non-original data table D, the account status field in data table D will be derived data rather than original data. The account status field in data table D may also come from data table A and / or data table B. Since the account status field in data table D is not original data, there may be data errors. In addition, data table D also has a cleaning cycle. The cleaning cycle of derived data tables may be shorter than that of original data tables. For example, the cleaning cycle of data table D is 2 months, while the cleaning cycle of data tables A and B is 1 year. Therefore, if the complex cleanup data table is cleaned based on data table D, the data table on which the cleanup target complex cleanup data table depends needs to be re-determined after 2 months.
[0057] Step S203: Generate a cleanup SQL statement based on the cleanup field and the word segmentation result in the second data table, and clean up the target complex cleanup data table according to the cleanup SQL statement.
[0058] After obtaining the word segmentation results and the second data table, the data to be cleaned can be determined based on the content of the cleanup field corresponding to different account IDs in the second data table. When the data to be cleaned is determined, a cleanup SQL statement can be generated. The cleanup SQL statement can be executed directly to obtain the filtered data, which is the data to be cleaned. The filtered data can then be deleted to achieve the cleanup of the target complex cleanup data table.
[0059] Optionally, cleanup SQL statements can be executed periodically, such as once a week, to achieve timely cleanup of the target complex cleanup data table.
[0060] This invention provides a data table cleanup method. It involves segmenting various target cleanup requirements to obtain segmentation results. The target cleanup requirements are cleanup requirements corresponding to a target complex cleanup data table. The segmentation results contain cleanup fields. For each cleanup field, a first data table containing the cleanup field is determined, and a second data table is selected from the first data table. The second data table is an original data table related to the cleanup field. A cleanup SQL statement is generated based on the cleanup field in the second data table and the segmentation results. The cleanup SQL statement is then used to clean the target complex cleanup data table. By segmenting the target cleanup requirements and determining the original data table containing the cleanup field, a cleanup SQL statement is generated, thus achieving automated cleanup of complex cleanup data tables. This improves cleanup efficiency and reduces the consumption of human resources.
[0061] Figure 3 This is a flowchart illustrating another data table cleanup method provided in an embodiment of the present invention.
[0062] Optionally, determining a first data table containing the cleanup fields includes:
[0063] Retrieve the first data table containing the cleanup field from the metadata management system; the metadata management system records the field information corresponding to each data table.
[0064] To facilitate the identification of the first data table containing the cleanup fields, the field information corresponding to each data table can be recorded in the metadata management system.
[0065] For example, data tables A, B, C, and D each contain four fields. Additionally, each customer has a unique identifier, known as the account ID, which serves as the unique primary key for all data tables.
[0066] Once the cleanup field is determined, the first data table containing the cleanup field can be retrieved in the metadata management system. For example, when the cleanup field is account status, the first data table containing account status can be retrieved, and each first data table can be obtained through retrieval.
[0067] For example, as shown in Table 1, the first data table that can be obtained by retrieving the account status field includes data table A, data table B, and data table D, all of which contain account status. Therefore, it can be determined that the first data table includes data table A, data table B, and data table D.
[0068] Table 1
[0069]
[0070] By storing the corresponding field information of each data table in the metadata management system, the first data table containing the cleanup field can be quickly retrieved.
[0071] Optionally, the metadata management system also records the processing SQL statements corresponding to each data table; the processing SQL statements are used to represent the processing procedure of the data table; filtering the second data table from the first data table includes:
[0072] For each of the first data tables, a lineage analysis is performed on the processing SQL statement to obtain the lineage analysis results; the lineage analysis results include the source table of the cleanup field;
[0073] Based on the lineage analysis results of each first data table, the source table of the cleanup field is determined to be its own second data table; each cleanup field corresponds to at least one second data table.
[0074] When determining the second data table from the first data table, it can be done based on the processing SQL statements corresponding to each of the first data tables in the metadata management system. As shown in Table 1, each data table corresponds to one processing SQL statement.
[0075] Optionally, for each first data table containing the account status field, such as data table A, data table B, and data table D, the processing SQL statements for the above three data tables can be obtained respectively. Lineage analysis can be performed on the processing SQL statements to analyze the processing process of the data tables, thereby locating the table from which the original data recording the account status field originates.
[0076] For example, the SQL statement for processing data table D is:
[0077] Select A.id as id,
[0078] D.name as xm,
[0079] E.time as zhdzrq,
[0080] A.status as zhzt, ...
[0082] from A
[0083] left join D
[0084] on A.id = D.id
[0085] left join E
[0086] on A.id = E.id ...
[0088] The results of the bloodline analysis are shown in Table 2.
[0089] Table 2
[0090] Table D fields Table D Field Names Source Table Source field id serial number A id xm Name D name zhdzrq Last transaction date E time zhzt Account Status A status
[0091] Based on the lineage analysis results of data table D, it can be found that the account status field of data table D comes from the account status of data table A. The account status field of data table D is derived data. In order to avoid the complex data table X cleaning process having an excessively long chain and reducing processing time, the data table X cleaning process does not rely on data table D to determine whether the account status meets the cleaning conditions.
[0092] Similarly, by analyzing the SQL statements used to process data tables A and B through lineage analysis, the results show that the account status field in both tables is raw data. Therefore, it can be determined that data tables A and B are secondary data tables.
[0093] For a single cleanup field, there can be multiple corresponding second data tables. For example, complex cleanup data table X contains 100 account IDs and their corresponding field 1; data table A contains 60 of these account IDs and their corresponding account status fields and field 2; and data table B contains another 40 account IDs and their corresponding account status fields and field 3. Based on data tables A and B, the account status field corresponding to each account ID in complex cleanup data table X can be determined.
[0094] By setting the processing SQL statements for each data table in the metadata management system and performing lineage analysis on the processing SQL statements, the second data table corresponding to the target complex cleanup data table can be accurately determined.
[0095] Figure 3 This is a flowchart illustrating another data table cleanup method provided in an embodiment of the present invention.
[0096] Optionally, a cleanup SQL statement is generated based on the cleanup field in the second data table and the word segmentation result, including:
[0097] Step S301: Generate a cleanup parameter table;
[0098] Step S302: Determine the account ID field as the associated field;
[0099] Step S303: Generate a cleanup SQL statement for the target complex cleanup data table based on the associated fields and the cleanup parameter table.
[0100] Optionally, the cleanup parameter table includes the target complex cleanup data table, the word segmentation processing result, the second data table, and the name of the target field; the name of the target field is the field name in the second data table that corresponds to the cleanup field.
[0101] Once the original data table containing the word segmentation results and cleanup fields is determined, a cleanup parameter table can be generated. Based on the cleanup parameter table, cleanup SQL statements can be generated. By processing the target complex cleanup data table according to the cleanup SQL statements, the data in the target complex cleanup data table that meets business requirements or regulatory standards can be cleaned.
[0102] The generated cleanup parameter table can be as shown in Table 3. The cleanup parameter table can include the target complex cleanup data table, as well as the word segmentation processing results, such as cleanup fields, judgment logic and cleanup conditions. The cleanup parameter table can also include the second data table (the table that needs to be associated with the cleanup) and the name of the target field (the name of the target field of the table that needs to be associated with the cleanup).
[0103] Table 3
[0104]
[0105] When generating the cleanup SQL statement, the associated field can also be determined. In this case, the associated field is the account ID field. This means that the various second-level data tables and the target data table are linked through the associated field, thereby determining the content of the cleanup field corresponding to each account ID. After linking the various second-level data tables and the target data table, it is necessary to filter account IDs that meet business requirements or regulatory standards, and then clean up the data corresponding to the filtered account IDs.
[0106] By identifying the associated fields and the cleanup parameter table, cleanup SQL statements can be generated quickly, improving the efficiency of cleanup SQL statement generation.
[0107] Optionally, the word segmentation processing result further includes: judgment logic, cleanup conditions; generating cleanup SQL statements for the target complex cleanup data table based on the associated fields and the cleanup parameter table, including:
[0108] Based on the cleanup SQL statement template, the associated fields, and the cleanup parameter table, a cleanup SQL statement is generated for the target complex cleanup data table; the cleanup SQL statement template contains the relationship between the filtering conditions, which are composed of target fields, judgment logic, and cleanup conditions.
[0109] When generating cleanup SQL statements, you can use a cleanup SQL statement template to generate cleanup SQL statements for the current target data table. For example, for the cleanup parameter table shown in Table 3, the corresponding cleanup SQL statement template is as follows:
[0110] DELETE FROM target data table
[0111] left join data table 1
[0112] on target_table.related_field = table1.related_field
[0113] left join data table 2
[0114] on target_table.related_field = table2.related_field
[0115] left join data table 1
[0116] on target_table.related_field = table1.related_field
[0117] left join...
[0118] on target data table.related field = ...
[0119] WHERE (field a logical 1 condition 1 or field c logical 1 condition 1)
[0120] AND(fields_s_logic_2_condition_2)
[0121] AND...
[0122] In the above cleanup SQL statement template, the associated field can be the account ID field. According to Table 3, the cleanup requirements include two cleanup fields, namely field 1 and field 2. For field 1, the associated data tables are data table 1 and data table 2. For field 2, the associated data table is data table 1. The field in data table 1 corresponding to field 1 is field a, the field in data table 2 corresponding to field 1 is field c, and the field in data table 1 corresponding to field 2 is field s. Therefore, the filtering conditions for each field can be pre-set. The filtering condition for field 1 is: field a logical 1 condition 1 OR field c logical 1 condition 1; the filtering condition for field 2 is: field s logical 2 condition 2. The relationship between the filtering conditions corresponding to different fields is "AND", that is, the filtering conditions corresponding to each cleanup field must be satisfied simultaneously.
[0123] Based on the cleanup SQL statement template, related fields, and cleanup parameter table described above, a cleanup SQL statement can be generated. For example, when the cleanup requirement is: clean up when the account status is closed, the cleanup parameter table shown in Table 4 can be obtained.
[0124] Table 4
[0125]
[0126] Based on the cleanup parameter table in Table 4, the cleanup SQL statement for data table X can be obtained according to the cleanup SQL statement template, as shown below.
[0127] DELETE FROM X
[0128] left join A
[0129] on X.ID = A.ID
[0130] left join B
[0131] on X.ID = B.ID
[0132] WHERE(A.zhzt='Account Closure' or B.zhzt='Account Closure')
[0133] For example, if the cleanup requirement is: cleanup should be performed if the account status is closed and the account opening date is less than 19991231, then the cleanup parameter table shown in Table 5 can be obtained.
[0134] Table 5
[0135]
[0136] Accordingly, the cleanup SQL statement can be obtained based on the cleanup SQL statement template, as shown below.
[0137] DELETE FROM X
[0138] left join A
[0139] on X.id = A.id
[0140] left join B
[0141] on X.id = B.id
[0142] left join F
[0143] on X.id = B.id
[0144] WHERE(A.zhzt='Account Closure' or B.zhzt='Account Closure')
[0145] AND(F.khrq<'19991231')
[0146] By pre-setting cleanup SQL statement templates, cleanup SQL statements can be automatically generated without manual processing, improving the efficiency and accuracy of generating cleanup SQL statements.
[0147] Optionally, the method further includes:
[0148] Store the cleanup parameter table corresponding to the target complex cleanup data table in the metadata management system;
[0149] When any of the second data tables in the cleanup parameter table no longer records the target field, the cleanup parameter table is updated, and the cleanup SQL statement is regenerated based on the updated cleanup parameter table.
[0150] To prevent changes in the associated tables of the target complex cleanup data table from affecting data deletion within that table, a cleanup parameter table corresponding to the target complex cleanup data table can be stored in the metadata management system after its generation. This cleanup parameter table reflects the dependencies between the target complex cleanup data table and each secondary data table. Since the metadata management system stores field information for each data table, if it is determined that any secondary data table in the cleanup parameter table no longer records the corresponding target field, the cleanup parameter table can be regenerated to ensure the correctness of the cleanup logic and the optimization of the cleanup process.
[0151] For example, as shown in Table 3, if field 'a' does not exist in data table 1, and data cleanup continues based on this data table, there may be issues of accidental or incorrect deletion, requiring an update to the cleanup parameter table.
[0152] Optionally, after updating the cleanup parameter table, it is necessary to generate corresponding cleanup SQL statements to clean up the target complex cleanup data table based on the updated cleanup SQL statements.
[0153] The above operations can ensure the accuracy of the cleanup logic and avoid problems when cleaning up complex data tables that are subject to changes.
[0154] This application enables the rapid identification of the data tables that complex data tables require for cleanup, identifying the optimal cleanup process without relying on developer experience or communication. This avoids time-consuming communication processes and excessive human resource investment. By determining the optimal cleanup logic, the efficiency and accuracy of the cleanup process are improved. This application also allows for the rapid fulfillment of cleanup requirements in relevant regulatory frameworks, accurately and efficiently cleaning data that meets these requirements, thus avoiding the risk of penalties.
[0155] Figure 4 This is a schematic diagram of a data table cleanup device provided in an embodiment of the present invention. The device 40 includes:
[0156] The word segmentation processing module 401 is used to perform word segmentation processing on each target cleanup requirement and obtain each word segmentation processing result; the target cleanup requirement is the cleanup requirement corresponding to the target complex cleanup data table; the word segmentation processing result contains a cleanup field.
[0157] The filtering module 402 is used to determine a first data table containing the cleanup field for each cleanup field, and to filter a second data table from the first data table; the second data table is the original data table about the cleanup field.
[0158] The processing module 403 is used to generate a cleanup SQL statement based on the cleanup field and the word segmentation result in the second data table, and to clean up the target complex cleanup data table according to the cleanup SQL statement.
[0159] Optionally, when determining the first data table containing the cleanup field, the filtering module 402 is specifically used for:
[0160] Retrieve the first data table containing the cleanup field from the metadata management system; the metadata management system records the field information corresponding to each data table.
[0161] Optionally, the metadata management system also records the processing SQL statements corresponding to each data table; the processing SQL statements are used to represent the processing procedure of the data table; when the filtering module 402 filters the second data table from the first data table, it is specifically used for:
[0162] For each of the first data tables, a lineage analysis is performed on the processing SQL statement to obtain the lineage analysis results; the lineage analysis results include the source table of the cleanup field;
[0163] Based on the lineage analysis results of each first data table, the source table of the cleanup field is determined to be its own second data table; each cleanup field corresponds to at least one second data table.
[0164] Optionally, when the processing module 403 generates a cleanup SQL statement based on the cleanup field in the second data table and the word segmentation result, it is specifically used for:
[0165] Generate a cleaning parameter table; the cleaning parameter table includes the target complex cleaning data table, the word segmentation processing result, a second data table, and the name of the target field; the name of the target field is the field name in the second data table that corresponds to the cleaning field;
[0166] Define the account ID field as the associated field;
[0167] Based on the associated fields and the cleanup parameter table, generate cleanup SQL statements for the target complex cleanup data table.
[0168] Optionally, the word segmentation processing result further includes: judgment logic and cleanup conditions; when the processing module 403 generates a cleanup SQL statement for the target complex cleanup data table based on the associated fields and the cleanup parameter table, it is specifically used for:
[0169] Based on the cleanup SQL statement template, the associated fields, and the cleanup parameter table, a cleanup SQL statement is generated for the target complex cleanup data table; the cleanup SQL statement template contains the relationship between the filtering conditions, which are composed of target fields, judgment logic, and cleanup conditions.
[0170] Optionally, the device further includes: an update module, used for:
[0171] Store the cleanup parameter table corresponding to the target complex cleanup data table in the metadata management system;
[0172] When any of the second data tables in the cleanup parameter table no longer records the target field, the cleanup parameter table is updated, and the cleanup SQL statement is regenerated based on the updated cleanup parameter table.
[0173] The data table cleaning device provided in this embodiment of the invention can achieve the above-mentioned... Figure 2 The data table cleaning method shown in the embodiment has a similar implementation principle and technical effect, and will not be described again here.
[0174] It should be understood that the above-described device embodiments are merely illustrative, and the device of this application can also be implemented in other ways. For example, the division of units / modules in the above embodiments is only a logical functional division, and there may be other division methods in actual implementation. For example, multiple units, modules, or components may be combined, or integrated into another system, or some features may be ignored or not executed.
[0175] Figure 5 This is a schematic diagram of the hardware structure of an electronic device provided in an embodiment of the present invention. For example... Figure 5 As shown, the electronic device provided in this embodiment includes at least one processor 501 and a memory 502. The processor 501 and the memory 502 are connected via a bus 503.
[0176] In a specific implementation, at least one processor 501 executes computer execution instructions stored in memory 502, causing at least one processor 501 to execute the method in the above method embodiment.
[0177] The specific implementation process of processor 501 can be found in the above method embodiments, and its implementation principle and technical effect are similar. It will not be repeated here.
[0178] In the above Figure 5In the illustrated embodiments, it should be understood that the processor can be a Central Processing Unit (CPU), or other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), etc. The general-purpose processor can be a microprocessor or any conventional processor. The steps of the method disclosed in this invention can be directly implemented by a hardware processor, or implemented by a combination of hardware and software modules within the processor.
[0179] The memory may include high-speed RAM, and may also include non-volatile storage (NVM), such as at least one disk storage.
[0180] The bus can be an Industry Standard Architecture (ISA) bus, a Peripheral Component Interconnect (PCI) bus, or an Extended Industry Standard Architecture (EISA) bus, etc. Buses can be categorized as address buses, data buses, control buses, etc. For ease of illustration, the buses shown in the accompanying drawings are not limited to a single bus or a single type of bus.
[0181] This invention also provides a computer-readable storage medium storing computer-executable instructions, which, when executed by a processor, implement the method described in the above embodiments.
[0182] This application also provides a computer program product, including a computer program that, when executed by a processor, implements the method described in the above method embodiments.
[0183] The aforementioned computer-readable storage medium can be implemented by any type of volatile or non-volatile storage device or a combination thereof, such as static random access memory (SRAM), electrically erasable programmable read-only memory (EEPROM), erasable programmable read-only memory (EPROM), programmable read-only memory (PROM), read-only memory (ROM), magnetic storage, flash memory, magnetic disk, or optical disk. The readable storage medium can be any available medium accessible to a general-purpose or special-purpose computer.
[0184] An exemplary readable storage medium is coupled to a processor, enabling the processor to read information from and write information to the readable storage medium. Of course, the readable storage medium can also be a component of the processor. The processor and the readable storage medium can reside in an Application Specific Integrated Circuit (ASIC). Alternatively, the processor and the readable storage medium can exist as discrete components in the device.
[0185] It should be noted that, in this document, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Unless otherwise specified, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes that element.
[0186] The sequence numbers of the embodiments in this application are for descriptive purposes only and do not represent the superiority or inferiority of the embodiments.
[0187] Through the above description of the embodiments, those skilled in the art can clearly understand that the methods of the above embodiments can be implemented by means of software plus necessary general-purpose hardware platforms. Of course, they can also be implemented by hardware, but in many cases the former is a better implementation method. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk), and includes several instructions to cause a terminal device (which may be a mobile phone, computer, server, air conditioner, or network device, etc.) to execute the methods described in the various embodiments of this application.
[0188] The above are merely preferred embodiments of this application and do not limit the patent scope of this application. Any equivalent structural or procedural transformations made using the content of this application's specification and drawings, or direct or indirect applications in other related technical fields, are similarly included within the patent protection scope of this application.
Claims
1. A data table cleaning method, characterized in that, include: Each target cleanup requirement is segmented into words to obtain the segmentation results; the target cleanup requirement is the cleanup requirement corresponding to the target complex cleanup data table; the segmentation results include cleanup fields; wherein, the complex cleanup data table refers to a data table that needs to be cleaned based on fields from multiple data tables; For each cleanup field, a first data table containing the cleanup field is determined, and a second data table is filtered from the first data table; the second data table is the original data table related to the cleanup field. Based on the cleanup fields and word segmentation results in the second data table, a cleanup SQL statement is generated, and the target complex cleanup data table is cleaned according to the cleanup SQL statement; Determine the first data table containing the cleanup field, including: Retrieve the first data table containing the cleanup field from the metadata management system; the metadata management system records the field information corresponding to each data table. The metadata management system also records the processing SQL statements corresponding to each data table; the processing SQL statements are used to represent the processing procedure of the data table; filtering the second data table from the first data table includes: For each of the first data tables, a lineage analysis is performed on the processing SQL statement to obtain the lineage analysis results; the lineage analysis results include the source table of the cleanup field; based on the lineage analysis results of each of the first data tables, the source table of the cleanup field is determined to be its own second data table; each cleanup field corresponds to at least one second data table; Based on the cleanup fields and word segmentation results in the second data table, generate cleanup SQL statements, including: Generate a cleanup parameter table; the cleanup parameter table includes the target complex cleanup data table, the word segmentation processing result, the second data table, and the name of the target field; the name of the target field is the field name in the second data table corresponding to the cleanup field; determine the account ID field as the associated field; generate a cleanup SQL statement for the target complex cleanup data table based on the associated field and the cleanup parameter table.
2. The method according to claim 1, characterized in that, The word segmentation processing result also includes: judgment logic and cleanup conditions; generating cleanup SQL statements for the target complex cleanup data table based on the associated fields and the cleanup parameter table, including: Based on the cleanup SQL statement template, the associated fields, and the cleanup parameter table, a cleanup SQL statement is generated for the target complex cleanup data table; the cleanup SQL statement template contains the relationship between the filtering conditions, which are composed of target fields, judgment logic, and cleanup conditions.
3. The method according to claim 1 or 2, characterized in that, The method further includes: Store the cleanup parameter table corresponding to the target complex cleanup data table in the metadata management system; When any of the second data tables in the cleanup parameter table no longer records the target field, the cleanup parameter table is updated, and the cleanup SQL statement is regenerated based on the updated cleanup parameter table.
4. A data table cleaning device, characterized in that, The device includes: The word segmentation processing module is used to perform word segmentation processing on various target cleaning requirements and obtain various word segmentation processing results; the target cleaning requirements are the cleaning requirements corresponding to the target complex cleaning data table; the word segmentation processing results include cleaning fields; wherein, the complex cleaning data table refers to a data table that needs to be cleaned based on fields from multiple data tables; A filtering module is used to determine a first data table containing each cleanup field, and to filter a second data table from the first data table; the second data table is the original data table related to the cleanup field. The processing module is used to generate a cleanup SQL statement based on the cleanup field and the word segmentation result in the second data table, and clean up the target complex cleanup data table according to the cleanup SQL statement; When determining the first data table containing the cleanup field, the filtering module is specifically used for: Retrieve the first data table containing the cleanup field from the metadata management system; the metadata management system records the field information corresponding to each data table. The metadata management system also records the processing SQL statements corresponding to each data table; the processing SQL statements are used to represent the processing procedure of the data table; when the filtering module filters the second data table from the first data table, it is specifically used for: For each of the first data tables, a lineage analysis is performed on the processing SQL statement to obtain the lineage analysis results; the lineage analysis results include the source table of the cleanup field; based on the lineage analysis results of each of the first data tables, the source table of the cleanup field is determined to be its own second data table; each cleanup field corresponds to at least one second data table; When the processing module generates a cleanup SQL statement based on the cleanup field and the word segmentation result in the second data table, it is specifically used for: Generate a cleanup parameter table; the cleanup parameter table includes the target complex cleanup data table, the word segmentation processing result, the second data table, and the name of the target field; the name of the target field is the field name in the second data table corresponding to the cleanup field; determine the account ID field as the associated field; generate a cleanup SQL statement for the target complex cleanup data table based on the associated field and the cleanup parameter table.
5. An electronic device, characterized in that, include: At least one processor and memory; The memory stores computer-executed instructions; The at least one processor executes computer execution instructions stored in the memory, causing the at least one processor to perform the method as described in any one of claims 1 to 3.
6. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer-executable instructions, which, when executed by a processor, implement the method as described in any one of claims 1 to 3.
7. A computer program product, comprising a computer program, characterized in that, When the computer program is executed by a processor, it implements the method as described in any one of claims 1 to 3.
Citation Information
Patent Citations
Automatic cleaning method and device for historical data
CN108319694A
Data cleaning method and device
CN114138767A
