Data cleaning method and device, equipment, storage medium and program product

Abstract

The invention provides a data cleaning method and device, equipment, a storage medium and a program product, and can be applied to the technical field of big data and financial science and technology. The method comprises the steps that in response to received multiple candidate database requests, screening is conducted in the multiple candidate database requests, target database requests matched with batch job characteristics are obtained, and the batch job characteristics indicate operation conditions of database requests to be screened; based on the target database request and a global view, screening is carried out in the multiple candidate temporary tables to obtain a target temporary table, and the global view indicates the dependency relationship of the multiple candidate temporary tables; and in response to a received completion instruction of the target database request, executing a cleaning operation on the target temporary table.

Description

Technical Field

[0001] This application relates to the fields of big data and fintech, and more specifically to a data cleaning method, apparatus, device, storage medium, and program product. Background Technology

[0002] In databases, as data operations are performed frequently, a large number of dead tuples are generated that are marked as deleted or invalid but still occupy storage space. The existence of these dead tuples will cause the tablespace to expand continuously, increase the overhead of data scanning, and reduce query performance.

[0003] In related technologies, an automatic cleanup mechanism is introduced, which uses a background thread to periodically scan the database tables, detect the number of dead tuples, and automatically perform cleanup operations when a preset threshold condition is met, so as to reclaim the space occupied by dead tuples.

[0004] However, relying on preset threshold conditions to trigger cleanup operations, while the initial data volume of temporary tables is 0 and the growth pattern is predictable but the total amount is uncertain, traditional automatic cleanup mechanisms cannot trigger the cleanup of dead tuples generated by temporary tables in a timely and accurate manner when the usage period of temporary tables ends. Summary of the Invention

[0005] In view of the above problems, embodiments of this application provide a data cleaning method, apparatus, device, storage medium, and program product.

[0006] According to a first aspect of this application, a data cleaning method is provided, the method comprising: in response to receiving a plurality of candidate database requests, filtering among the plurality of candidate database requests to obtain a target database request that matches batch job characteristics, wherein the batch job characteristics indicate the running conditions of the database requests to be filtered; filtering among a plurality of candidate temporary tables based on the target database request and a global view to obtain a target temporary table, wherein the global view indicates the dependencies between the plurality of candidate temporary tables; and in response to receiving a completion instruction for the target database request, performing a cleaning operation on the target temporary table.

[0007] According to an embodiment of this application, the method further includes: in response to a transaction status triggering instruction indicated by a query request, extracting the calculation result from a temporary table matching the query request, and storing the calculation result in the application layer; in response to the application layer receiving the calculation result, executing a clear table command on the temporary table, and performing a rollback operation on the transaction indicated by the query request.

[0008] According to an embodiment of this application, filtering from multiple candidate database requests to obtain a target database request that matches the characteristics of a batch job includes: extracting keywords from multiple candidate database requests to obtain multiple candidate job features, wherein the candidate job features include at least one of source, application identifier, duration, and database mode; filtering from multiple candidate job features based on operating conditions to obtain target job features, wherein the operating conditions include at least one of source matching conditions, application identifier matching conditions, duration matching conditions, and database mode matching conditions; and using the candidate database request corresponding to the target job feature as the target database request.

[0009] According to an embodiment of this application, keyword extraction is performed on multiple candidate database requests to obtain multiple candidate job features, including: performing word segmentation on multiple candidate database requests based on a preset business dictionary to obtain multiple sets of terms, wherein the business dictionary indicates the word segmentation benchmark in the database domain; and filtering from multiple sets of terms based on multiple preset recognition strategies to obtain multiple candidate job features, wherein each of the multiple recognition strategies has a matching feature type, and the multiple recognition strategies indicate multiple judgment conditions corresponding to multiple feature types.

[0010] According to an embodiment of this application, obtaining a target temporary table by filtering in a global view based on a target database request includes: filtering in a global view based on a request identifier indicated by the target database request to obtain multiple candidate temporary tables, wherein the global view includes multiple temporary tables, each of which has a matching request identifier; and retrieving from the multiple temporary tables based on the dependencies between the multiple candidate temporary tables to obtain the target temporary table.

[0011] According to an embodiment of this application, the global view is obtained in the following way: a global view is generated by using multiple temporary tables as nodes, the basic information of the temporary tables as node attributes, and the dependencies between the multiple temporary tables as edges; wherein, the basic information of the temporary tables includes at least one of the following: temporary table creation time, request identifier, table name, and table type, and the dependencies include at least one of the following: direct dependency, indirect dependency, and structural dependency.

[0012] According to embodiments of this application, the cleanup operation includes cleanup and analysis, full cleanup, standard cleanup, automatic cleanup, or cleanup of specified objects.

[0013] According to a second aspect of this application, a data cleaning apparatus is provided, comprising: a matching module, configured to, in response to receiving a plurality of candidate database requests, filter among the plurality of candidate database requests to obtain a target database request matching batch job characteristics, the batch job characteristics indicating the running conditions of the database requests to be filtered; a filtering module, configured to, based on the target database request and a global view, filter among a plurality of candidate temporary tables to obtain a target temporary table, the global view indicating the dependencies between the plurality of candidate temporary tables; and a cleaning module, configured to, in response to receiving a completion instruction from the target database request, perform a cleaning operation on the target temporary table.

[0014] According to a third aspect of this application, an electronic device is provided, comprising: one or more processors; and a memory for storing one or more computer programs, wherein the one or more processors execute the one or more computer programs to implement the steps of the method described above.

[0015] According to a fourth aspect of this application, a computer-readable storage medium is also provided, on which a computer program or instructions are stored, wherein the computer program or instructions, when executed by a processor, implement the steps of the above-described method.

[0016] According to a fifth aspect of this application, a computer program product is also provided, including a computer program or instructions that, when executed by a processor, implement the steps of the above-described method.

[0017] According to the data processing and cleaning method, apparatus, device, storage medium, and program product provided in the embodiments of this application, the target database request in the batch job scenario is obtained by filtering multiple candidate databases using the running conditions indicated by the characteristics of the batch job, filtering the target temporary table associated with the target database request using the dependency relationship indicated by the global view, and cleaning the data in the target temporary table when the target database request is completed. Thus, the temporary table is cleaned up in a timely and accurate manner when the usage period of the temporary table ends (the data is invalid), effectively controlling the expansion of the temporary table and minimizing the impact on the performance of normal transactions. Attached Figure Description

[0018] The above-mentioned contents, other objects, features and advantages of this application will become clearer from the following description of embodiments with reference to the accompanying drawings, in which:

[0019] Figure 1 The illustrations depict application scenarios of data cleaning methods, apparatus, devices, media, and program products according to embodiments of this application.

[0020] Figure 2 A flowchart illustrating a data cleaning method according to an embodiment of this application is shown schematically;

[0021] Figure 3 A flowchart illustrating the process of obtaining a target database request according to an embodiment of this application is shown schematically;

[0022] Figure 4 A flowchart illustrating the data cleanup operation performed on a target temporary table according to an embodiment of this application is shown in the schematic diagram.

[0023] Figure 5 This illustration shows a flowchart of a data cleaning operation performed in response to a query request according to an embodiment of this application;

[0024] Figure 6 This schematically illustrates a complete flowchart of performing data cleaning operations in response to a query request according to an embodiment of this application;

[0025] Figure 7 This schematic diagram illustrates a structural block diagram of a data cleaning apparatus according to an embodiment of the present application;

[0026] Figure 8 A block diagram schematically illustrates an electronic device suitable for implementing a data cleaning method according to an embodiment of this application. Detailed Implementation

[0027] The embodiments of this application will now be described with reference to the accompanying drawings. However, it should be understood that these descriptions are exemplary only and are not intended to limit the scope of this application. In the following detailed description, numerous specific details are set forth to provide a thorough understanding of the embodiments of this application for ease of explanation. However, it will be apparent that one or more embodiments may be implemented without these specific details. Furthermore, descriptions of well-known structures and technologies are omitted in the following description to avoid unnecessarily obscuring the concepts of this application.

[0028] The terminology used herein is for the purpose of describing particular embodiments only and is not intended to limit the scope of this application. The terms “comprising,” “including,” etc., as used herein indicate the presence of the stated features, steps, operations, and / or components, but do not exclude the presence or addition of one or more other features, steps, operations, or components.

[0029] All terms used herein (including technical and scientific terms) have the meanings commonly understood by those skilled in the art, unless otherwise defined. It should be noted that the terms used herein are to be interpreted in a manner consistent with the context of this specification, and not in an idealized or overly rigid way.

[0030] When using expressions such as "at least one of A, B and C", they should generally be interpreted in accordance with the meaning that is commonly understood by those skilled in the art (e.g., "a system having at least one of A, B and C" should include, but is not limited to, a system having A alone, a system having B alone, a system having C alone, a system having A and B, a system having A and C, a system having B and C, and / or a system having A, B and C, etc.).

[0031] In the technical solution of this application, the user information (including but not limited to user personal information, user image information, user device information, such as location information) and data (including but not limited to data used for analysis, stored data, and displayed data) involved are all information and data authorized by the user or fully authorized by all parties. Furthermore, the collection, storage, use, processing, transmission, provision, disclosure, and application of related data all comply with relevant laws, regulations, and standards, take necessary confidentiality measures, do not violate public order and good morals, and provide corresponding operation entry points for users to choose to authorize or refuse.

[0032] In scenarios where personal information is used for automated decision-making, the methods, devices, and systems provided in this application all provide users with corresponding operation entry points for users to choose to agree to or reject the automated decision results; if the user chooses to reject, the process enters the expert decision-making process.

[0033] Dead tuples exhibit a clustered distribution and continuous growth in space usage. The clustered distribution indicates that frequently updated hot tables generate a large number of consecutive dead tuples. The continuous growth refers to the fact that the space occupied by dead tuples increases linearly with the number of transactions before space reclamation is performed. Due to the large data volume of batch transactions and the high concurrency of online transactions, dead tuples in global temporary tables can form a considerable amount of invalid storage area in a short period of time, causing changes in statement execution plans and leading to an exponential decrease in database efficiency.

[0034] Traditional databases use automatic cleanup mechanisms to manage dead tuples. These mechanisms primarily rely on statistical analysis of changes in table data and preset threshold conditions to trigger cleanup operations. While this mechanism is effective for cleaning up regular tables with relatively stable data volumes, temporary tables exhibit strong data dynamism, with data volumes changing drastically within a period and fixed patterns. Therefore, using automatic cleanup mechanisms to clean up temporary tables has the following drawbacks:

[0035] Trigger thresholds based on a fixed percentage or a fixed number (threshold) are difficult to adapt to the characteristics of temporary tables, which have an initial data volume of zero and a predictable growth pattern but an uncertain total amount. They cannot trigger the cleanup of dead tuples generated by temporary tables in a timely and accurate manner when the usage period of the temporary table ends.

[0036] Furthermore, in batch transaction scenarios, temporary tables (especially global temporary tables) will quickly accumulate a large number of dead tuples, causing table and index expansion, ineffectively occupying a large amount of storage space, and wasting storage resources. At the same time, the query optimizer in the automatic cleanup mechanism may be affected by the distortion of statistical information caused by dead tuples, or the scanning efficiency may be reduced due to the need to read additional data blocks containing dead tuples during physical scanning, thereby generating a suboptimal execution plan, ultimately causing the database response time to increase exponentially and reducing the database execution efficiency.

[0037] To address at least one of the aforementioned technical problems, embodiments of this application provide a data cleaning method. This method filters out target database requests for batch operations from multiple candidate database requests by indicating batch operation conditions and identifies their associated temporary tables, thereby achieving precise location of the temporary tables to be cleaned. The method also promptly executes data cleaning operations in response to completion instructions, ensuring accurate cleaning at the end of the temporary table's usage period. This avoids long-term occupation of invalid space and reduces the impact on database transaction execution.

[0038] Figure 1 The diagram illustrates an application scenario of the data cleaning method according to an embodiment of this application.

[0039] like Figure 1 As shown, application scenario 100 according to an embodiment of this application may include a first terminal device 101, a second terminal device 102, a third terminal device 103, a network 104, and a server 105. The network 104 serves as a medium for providing a communication link between the first terminal device 101, the second terminal device 102, the third terminal device 103, and the server 105. The network 104 may include various connection types, such as wired or wireless communication links or fiber optic cables. For example, a user can use the first terminal device 101, the second terminal device 102, and the third terminal device 103 to interact with the server 105 through the network 104 to receive or send information, etc.

[0040] The first terminal device 101, the second terminal device 102, and the third terminal device 103 can be electronic devices such as smartphones, wearable devices, personal computers, intelligent voice interaction devices, smart home appliances, intelligent vehicles, in-vehicle terminals, aircraft, unmanned vending terminals, and extended reality devices. Extended reality devices can include virtual reality devices, augmented reality devices, and mixed reality devices. A client application for the target application can be installed and run on the terminal devices. This target application can include, but is not limited to, financial transaction applications, payment applications, shopping applications, web browser applications, search applications, instant messaging tools, email clients, and social media platform software (these are just examples). Furthermore, this application embodiment does not limit the form of the target application, and it can include, but is not limited to, applications, mini-programs, etc., installed on the terminal devices, and can also be in the form of web pages.

[0041] Server 105 can be a server providing various services, such as a backend management server supporting websites browsed by users using the first terminal device 101, the second terminal device 102, and the third terminal device 103 (this is just an example). The backend management server can analyze and process received user requests and other data, and feed back the processing results (such as web pages, information, or data obtained or generated according to user requests) to the terminal devices. The server can be a standalone physical server, a server cluster or distributed system composed of multiple physical servers, or a cloud server providing cloud computing services such as cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communication, middleware services, domain name services, security services, content delivery networks, and basic cloud computing services such as big data. The server can be the backend server of the aforementioned target application, used to provide backend services to the clients of the target application.

[0042] It should be noted that the data cleaning method provided in this application embodiment can generally be executed by server 105 and / or terminal devices 101-103. Accordingly, the data cleaning device provided in this application embodiment can generally be installed in server 105 and / or terminal devices 101-103.

[0043] It should be understood that Figure 1 The number of terminal devices, networks, and servers shown is merely illustrative. Depending on implementation needs, any number of terminal devices, networks, and servers can be included.

[0044] Figure 2 A flowchart illustrating a data cleaning method according to an embodiment of this application is shown schematically. Figure 2 As shown, the data cleaning method 200 according to an embodiment of this application may include steps S210 to S230.

[0045] In step S210, in response to receiving multiple candidate database requests, the system filters among the multiple candidate database requests to obtain the target database request that matches the characteristics of the batch job.

[0046] According to embodiments of this application, candidate database requests may include database connection requests and / or database transactions. A database connection request may refer to a request initiated by a client program to a database server to establish a communication channel. A database transaction request refers to a request to execute business operations within an established database request, such as transaction requests involved in batch transactions, such as query requests. Batch job characteristics indicate the operating conditions of the database requests to be screened. The operating conditions characterize the operating features in the batch job scenario, such as a duration greater than a preset time threshold.

[0047] For example, multiple candidate database requests each have matching candidate job characteristics. The database requests can be filtered based on the running conditions of the database requests to be filtered indicated by the batch job characteristics and the running conditions indicated by the multiple candidate job characteristics to obtain the target database request that matches the batch job characteristics.

[0048] In step S220, based on the target database request and the global view, the target temporary table is obtained by filtering among multiple candidate temporary tables.

[0049] According to embodiments of this application, the global view indicates the dependencies between multiple candidate temporary tables. Candidate temporary tables refer to temporary tables awaiting filtering; these temporary tables are primarily used to store intermediate results during session execution.

[0050] For example, the target temporary table is obtained by filtering through multiple temporary tables contained in the global view based on the request identifier indicated by the target database request and the dependencies between multiple candidate temporary tables.

[0051] In step S230, in response to receiving a completion instruction from the target database, a cleanup operation is performed on the target temporary table.

[0052] According to embodiments of this application, the completion instruction indicates the status of the target database request, such as the database connection request ending or the transaction being successfully committed.

[0053] For example, after the target database connection request ends or the transaction is successfully committed, a cleanup (VACUUM) operation, such as VACUUM ANALYZE, is proactively triggered.

[0054] The VACUUM operation is used to clean up "dead tuples" in the database. Dead tuples are old data rows that still exist in the table after an update or delete operation is performed. It helps to reclaim the storage space occupied by old data rows so that it can be used by subsequent operations, instead of being immediately released to the operating system.

[0055] Through the above steps S210~S230, the target database request under the batch job scenario is obtained by filtering multiple candidate databases using the running conditions indicated by the batch job characteristics. The target temporary table associated with the target database request is filtered by the dependency relationship indicated by the global view. When the target database request is completed, the data in the target temporary table is cleaned up. Then, when the temporary table usage period ends (the data is invalid), it is cleaned up in a timely and accurate manner, effectively controlling the expansion of the temporary table and minimizing the impact on the performance of normal transactions.

[0056] Figure 3 A flowchart illustrating the process of obtaining a target database request according to an embodiment of this application is shown.

[0057] Combination Figure 3 The above step S210 will be further explained. In some embodiments, filtering among multiple candidate database requests to obtain a target database request that matches the characteristics of the batch job includes:

[0058] First, keywords are extracted from multiple candidate database requests 301 to obtain multiple candidate job features 302.

[0059] According to embodiments of this application, candidate job characteristics include at least one of source, application identifier, duration, and database pattern.

[0060] For example, when the candidate database request is a database connection request, the source can refer to the connection source of the database connection request, such as the location information (e.g., network address, hostname, etc.) of the client or application that initiated the database connection request, and the application identifier can be the application name.

[0061] When the candidate database request is a database transaction, the duration can be the transaction duration, which refers to the length of time a transaction takes from start to finish (commit or rollback), reflecting the time window during which the transaction holds resources and may affect system concurrency and consistency.

[0062] A database schema can be a database schema, such as strict mode for transactional tables, strict mode for all tables, prohibiting zero values ​​in dates, etc. Large data operations that include a certain schema can be regarded as batch jobs.

[0063] For example, a word segmentation tool can be used to extract keywords from multiple candidate database requests to obtain multiple candidate job features.

[0064] Then, based on the operating conditions, the target job feature 303 is obtained by filtering among multiple candidate job features 302.

[0065] According to embodiments of this application, the operating conditions include at least one of source matching conditions, application identifier matching conditions, duration matching conditions, and database pattern matching conditions.

[0066] For example, the source matching condition can be that the connection source is in a preset source configuration table, and the source configuration characterizes multiple connection sources used for batch jobs. The application identifier matching condition can be that the application identifier is in a preset application configuration table, and the application configuration table includes multiple application identifiers used for batch jobs. The duration matching condition can be that the transaction duration is greater than a preset threshold. The database schema matching condition can be a large number of data operations including a certain database schema, such as an execution plan containing temporary table scans. Candidate job features that meet any of the above conditions are used as target job features, and there can be one or more target job features.

[0067] Finally, the candidate database request corresponding to the target job feature 303 is used as the target database request 304.

[0068] For example, the database connection request or database transaction corresponding to the target job characteristics is taken as the target database request.

[0069] In the embodiments of this application, by using preset operating conditions, connections or transactions that meet the characteristics of batch jobs are identified from different dimensions such as source, application identifier, duration, and database mode, which makes it easier to finally lock the target database request and can greatly improve the identification accuracy of batch job sessions, avoiding misjudgment and omission.

[0070] In some embodiments, keyword extraction is performed on multiple candidate database requests to obtain multiple candidate job features, including:

[0071] First, based on a pre-defined business dictionary, multiple candidate database requests are segmented to obtain multiple sets of terms.

[0072] According to embodiments of this application, the business dictionary indicates a word segmentation benchmark in the database domain, which may include known application name abbreviations, server role prefixes, etc., so that the word segmentation tool can accurately identify technical terms in candidate database requests.

[0073] For example, based on the business dictionary, multiple candidate database requests are processed using a general natural language processing (NLP) word segmentation tool to obtain multiple sets of entries.

[0074] Then, based on multiple preset recognition strategies, multiple candidate job features are obtained by filtering from multiple sets of terms.

[0075] According to embodiments of this application, multiple identification strategies each have matching feature types, and the multiple identification strategies indicate multiple judgment conditions corresponding to multiple feature types, that is, the correspondence between feature types and judgment conditions.

[0076] For example, the feature type may include at least one of the following: source and application identification features, duration features, and database schema features.

[0077] When the feature type is source and application identifier features, the identification strategy can be to filter multiple sets of terms based on predefined mapping rules to obtain source and / or application identifier features; the mapping rules indicate the correspondence between terms and source and / or application identifiers. For example, if the term 'api' is included and the IP belongs to a certain network segment, then the application identifier is 'API-GROUP-A'.

[0078] When the feature type is duration, the identification strategy is to use the duration index provided by the database monitoring tool as the duration.

[0079] When the feature type is a database schema feature, the identification strategy is to query a predefined schema classification mapping table based on the event field in multiple sets of terms, such as wait_class and event fields, to obtain the database schema. The schema classification mapping table includes the correspondence between different fields and different database schemas.

[0080] In the embodiments of this application, word segmentation based on a preset business dictionary can ensure the professionalism and accuracy of word segmentation, providing reliable data support for subsequent matching of target database requests; at the same time, the job features contained in the candidate database requests can be accurately identified by using an identification strategy that matches the feature type.

[0081] In some embodiments, the target temporary table is obtained by filtering in the global view based on the target database request, including:

[0082] First, based on the request identifier indicated by the target database request, multiple candidate temporary tables are obtained by filtering in the global view.

[0083] According to embodiments of this application, the global view includes multiple temporary tables. Each temporary table has a matching request identifier to ensure that filtering accurately associates with all temporary tables corresponding to the target database request.

[0084] For example, a dependency-based global view is maintained by the database system and includes information such as all currently active database requests, relationships between transactions and temporary tables. For instance, when the target database request's request identifier is "SID_20250701_1020", searching the global view using this identifier can quickly filter out all relevant temporary tables created by that session, such as tmp_batch_user, tmp_batch_order, and the referenced global temporary table global_tmp_calc_result, forming a candidate temporary table set. Then, based on the dependencies between these candidate temporary tables, a search is performed within these temporary tables to obtain the target temporary table.

[0085] According to embodiments of this application, the dependency relationship of multiple candidate temporary tables refers to the data dependency relationship between the candidate temporary tables and other temporary tables.

[0086] For example, starting from the temporary tables directly created by the target database request in multiple candidate temporary tables, forward tracing is performed based on the dependency edges of the global view to find all other temporary tables that directly or indirectly depend on these tables. Reverse tracing is then triggered from the temporary tables referenced in the multiple candidate temporary tables to identify the temporary tables referenced in the target database request and to find the source temporary tables of these tables; these temporary tables are then used together with the multiple candidate temporary tables as the target temporary table.

[0087] According to the embodiments of this application, each temporary table in the global view is pre-bound to its corresponding request identifier, which can ensure that the filtering is accurately associated with all temporary tables corresponding to the target database request, avoiding cross-session mismatch or omission, and improving the accuracy and efficiency of target temporary table identification; at the same time, temporary table lookup is performed based on dependency relationships to ensure the comprehensiveness of target temporary table identification.

[0088] In some embodiments, the global view is obtained by generating a global view using multiple temporary tables as nodes, the basic information of the temporary tables as node attributes, and the dependencies between the multiple temporary tables as edges.

[0089] According to embodiments of this application, the basic information of the temporary table includes at least one of the following: temporary table creation time, request identifier, table name, and table type; and the dependency relationship includes at least one of the following: direct dependency, indirect dependency, and structural dependency.

[0090] For example, a direct dependency can refer to a statement creating temporary table B referencing data from temporary table A; an indirect dependency refers to a dependency chain formed through multi-level queries; and a structural dependency refers to relationships established between temporary tables through foreign keys, synonyms, or views. This information and these relationships can be obtained using database monitoring tools.

[0091] Based on this, a global view is constructed in the form of a graph structure, which makes it easier to intuitively display the dependencies between temporary tables.

[0092] In some embodiments, the cleanup operation may include cleanup and analysis, full cleanup, standard cleanup, automatic cleanup, or cleanup of specified objects.

[0093] Based on this, multiple options are provided for clearing temporary tables.

[0094] Figure 4 A flowchart illustrating a data cleanup operation performed on a target temporary table according to an embodiment of this application is shown.

[0095] Next, taking a candidate database request as an example of a database operation request, we will combine... Figure 4 To further explain the practical application of the above steps, firstly, a database operation request is initiated, a database connection is obtained through the connection pool, and the monitor determines whether it is a batch job. If the database operation request is a batch job, the temporary tables involved are retrieved, and a VACUUM cleanup operation is performed. If the database operation request is not a batch job, the database operation is executed, and the operation result is returned.

[0096] Figure 5 This illustration schematically shows a flowchart of a data cleaning operation performed in response to a query request according to an embodiment of this application. Figure 6 The diagram illustrates a complete flowchart of performing data cleaning operations in response to a query request according to an embodiment of this application.

[0097] In high-concurrency online transaction scenarios, temporary tables can quickly accumulate a large number of dead tuples. To fundamentally avoid the generation of dead tuples during temporary table operations in high-concurrency online transaction scenarios, in some embodiments, when the candidate database request is a high-concurrency online query request, the method further includes steps S510 to S520.

[0098] In step 510, in response to the transaction status trigger instruction indicated by the query request, the calculation result in the temporary table matching the query request is extracted and stored in the application layer.

[0099] According to an embodiment of this application, a transaction status trigger instruction can refer to a trigger instruction issued when a transaction has been completed but not yet committed, and all calculations and data processing have been completed, resulting in a calculation result.

[0100] Combination Figure 6For example, in practical applications, firstly, the application server receives a query request, i.e., a query statement, executes the query statement containing temporary table operations normally, starts a transaction, processes the data according to predefined business processing logic, and obtains a result set containing calculation results; then, before the transaction is completed (committed), the result set is obtained, and the calculation results are extracted / saved to the application layer, for example, transferred to the application server memory or persisted to the result table.

[0101] In step S520, in response to the application layer receiving the calculation result, a command to clear the temporary table is executed, and a rollback operation is performed on the transaction indicated by the query request.

[0102] For example, after the application layer successfully obtains the calculation result, a TRUNCATE operation (quickly clearing the table data) is performed on the temporary table used in this transaction. Then, a ROLLBACK operation is performed on the transaction to roll back the transaction.

[0103] TRUNCATE operations are generally faster than DELETE operations and do not produce dead tuples, thus directly reclaiming space. ROLLBACK operations undo all data modifications within the transaction (including TRUNCATE) without acquiring database locks.

[0104] In the embodiments of this application, the TRUNCATE operation physically clears the temporary table data before rollback. Since the transaction is ultimately rolled back, all dead tuples that would have been generated on the temporary table during the transaction due to INSERT / UPDATE / DELETE operations are completely avoided. After the transaction "ends" (actually rolls back), the temporary table is restored to its state before the transaction began (empty table), with no dead tuples remaining.

[0105] Based on the above data cleaning method, embodiments of this application also provide a data cleaning apparatus. The following will be combined with... Figure 7 The device is described in detail.

[0106] Figure 7 A schematic block diagram of a data cleaning apparatus according to an embodiment of this application is shown.

[0107] like Figure 7 As shown, the data cleaning device 700 of this embodiment includes a matching module 710, a filtering module 720 and a cleaning module 730.

[0108] The matching module 710 is used to filter multiple candidate database requests in response to receiving such requests, and to obtain target database requests that match the characteristics of the batch job. The batch job characteristics indicate the operating conditions of the database requests to be filtered. In one embodiment, the matching module 710 may be used to perform step S210 as described above, which will not be repeated here.

[0109] The filtering module 720 is used to filter multiple candidate temporary tables based on the target database request and a global view to obtain the target temporary table. The global view indicates the dependencies between the multiple candidate temporary tables. In one embodiment, the filtering module 720 can be used to perform step S220 described above, which will not be repeated here.

[0110] The cleanup module 730 is used to perform a cleanup operation on the target temporary table in response to receiving a completion instruction from the target database. In one embodiment, the cleanup module 730 can be used to perform step S230 described above, which will not be repeated here.

[0111] According to an embodiment of this application, the device further includes a data clearing module, which is used to extract the calculation results from the temporary table matching the query request in response to the transaction status triggering instruction indicated by the query request, and store the calculation results in the application layer; in response to the application layer receiving the calculation results, execute a table clearing command on the temporary table and execute a rollback operation on the transaction indicated by the query request.

[0112] According to an embodiment of this application, the matching module 710 includes an extraction submodule and a filtering submodule. The extraction submodule is used to extract keywords from multiple candidate database requests to obtain multiple candidate job features. The candidate job features include at least one of source, application identifier, duration, and database mode. The filtering submodule is used to filter among the multiple candidate job features based on operating conditions to obtain target job features. The operating conditions include at least one of source matching conditions, application identifier matching conditions, duration matching conditions, and database mode matching conditions. The candidate database request corresponding to the target job feature is taken as the target database request.

[0113] According to an embodiment of this application, the extraction submodule is specifically used to perform word segmentation on multiple candidate database requests based on a preset business dictionary to obtain multiple sets of terms. The business dictionary indicates the word segmentation benchmark in the database domain. Based on multiple preset recognition strategies, multiple candidate job features are selected from the multiple sets of terms to obtain multiple candidate job features. Each of the multiple recognition strategies has a matching feature type, and the multiple recognition strategies indicate multiple judgment conditions corresponding to multiple feature types.

[0114] According to an embodiment of this application, the filtering module 720 includes a first filtering submodule and a second filtering submodule. The first filtering submodule is used to filter in a global view based on the request identifier indicated by the target database request to obtain multiple candidate temporary tables. The global view includes multiple temporary tables, each of which has a matching request identifier. The second filtering submodule is used to retrieve in the multiple temporary tables based on the dependency relationship between the multiple candidate temporary tables to obtain the target temporary table.

[0115] According to an embodiment of this application, the global view is obtained in the following way: a global view is generated by using multiple temporary tables as nodes, the basic information of the temporary tables as node attributes, and the dependencies between the multiple temporary tables as edges; wherein, the basic information of the temporary tables includes at least one of the following: temporary table creation time, request identifier, table name, and table type, and the dependencies include at least one of the following: direct dependency, indirect dependency, and structural dependency.

[0116] According to embodiments of this application, the cleanup operation includes cleanup and analysis, full cleanup, standard cleanup, automatic cleanup, or cleanup of specified objects.

[0117] According to embodiments of this application, any plurality of modules among the matching module 710, filtering module 720, and cleaning module 730 can be combined into one module, or any one of these modules can be split into multiple modules. Alternatively, at least part of the functionality of one or more of these modules can be combined with at least part of the functionality of other modules and implemented in one module. According to embodiments of this application, at least one of the matching module 710, filtering module 720, and cleaning module 730 can be at least partially implemented as hardware circuitry, such as field-programmable gate arrays, programmable logic arrays, systems-on-a-chip, systems-on-a-substrate, systems-on-package, application-specific integrated circuits, or any other reasonable means of integrating or packaging circuitry, or implemented in software, hardware, or firmware, or in any appropriate combination of any of these three implementation methods. Alternatively, at least one of the matching module 710, filtering module 720, and cleaning module 730 can be at least partially implemented as a computer program module, which, when run, can perform corresponding functions.

[0118] Figure 8 A block diagram schematically illustrates an electronic device suitable for implementing a data cleaning method according to an embodiment of this application.

[0119] like Figure 8 As shown, an electronic device 800 according to an embodiment of this application includes a processor 801, which can perform various appropriate actions and processes according to a program stored in a read-only memory 802 or a program loaded from a storage portion 808 into a random access memory 803. The processor 801 may include, for example, a general-purpose microprocessor, an instruction set processor and / or an associated chipset and / or a dedicated microprocessor. The processor 801 may also include onboard memory for caching purposes. The processor 801 may include a single processing unit or multiple processing units for executing different steps of the method flow according to an embodiment of this application.

[0120] Random access memory 803 stores various programs and data required for the operation of electronic device 800. Processor 801, read-only memory 802, and random access memory 803 are interconnected via bus 804. Processor 801 executes various steps of the method flow according to embodiments of this application by executing programs stored in read-only memory 802 and / or random access memory 803. It should be noted that the programs may also be stored in one or more memories other than read-only memory 802 and random access memory 803. Processor 801 may also execute various steps of the method flow according to embodiments of this application by executing programs stored in said one or more memories.

[0121] According to embodiments of this application, the electronic device 800 may further include an input / output interface 805, which is also connected to a bus 804. The electronic device 800 may also include one or more of the following components connected to the input / output interface 805: an input section 806 including a keyboard, mouse, etc.; an output section 807 including a cathode ray tube, liquid crystal display, etc., and a speaker, etc.; a storage section 808 including a hard disk, etc.; and a communication section 809 including a network interface card, such as a local area network card, modem, etc. The communication section 809 performs communication processing via a network such as the Internet. A drive 810 is also connected to the input / output interface 805 as needed. A removable medium 811, such as a disk, optical disk, magneto-optical disk, semiconductor memory, etc., is installed on the drive 810 as needed so that computer programs read from it can be installed into the storage section 808 as needed.

[0122] Embodiments of this application also provide a computer-readable storage medium, which may be included in the device / apparatus / system described in the above embodiments; or it may exist independently and not assembled into the device / apparatus / system. The computer-readable storage medium carries one or more programs, which, when executed, implement the method according to the embodiments of this application.

[0123] According to embodiments of this application, the computer-readable storage medium can be a non-volatile computer-readable storage medium, such as including but not limited to: portable computer disks, hard disks, random access memory, read-only memory, erasable programmable read-only memory, portable compact disk read-only memory, optical storage devices, magnetic storage devices, or any suitable combination thereof. In embodiments of this application, the computer-readable storage medium can be any tangible medium containing or storing a program that can be used by or in conjunction with an instruction execution system, apparatus, or device. For example, according to embodiments of this application, the computer-readable storage medium may include the read-only memory 802 described above, and / or random access memory 803, and / or one or more memories other than read-only memory 802 and random access memory 803.

[0124] Embodiments of this application also include a computer program product comprising a computer program containing program code for performing the methods shown in the flowchart. When the computer program product is run on a computer system, the program code is used to cause the computer system to implement the methods provided in the embodiments of this application.

[0125] In one embodiment, the computer program may rely on a tangible storage medium such as an optical storage device or a magnetic storage device. In another embodiment, the computer program may also be transmitted and distributed in the form of signals over a network medium, and may be downloaded and installed via the communication section 809, and / or installed from a removable medium 811. The program code contained in the computer program can be transmitted using any suitable network medium, including but not limited to: wireless, wired, etc., or any suitable combination thereof.

[0126] In embodiments of this application, the computer program can be downloaded and installed from a network via communication section 809, and / or installed from removable medium 811. When the computer program is executed by processor 801, it performs the functions defined in the system of embodiments of this application. According to embodiments of this application, the systems, devices, apparatuses, modules, units, etc., described above can be implemented by computer program modules.

[0127] According to embodiments of this application, program code for executing the computer programs provided in the embodiments of this application can be written in any combination of one or more programming languages. Specifically, these computational programs can be implemented using high-level procedural and / or object-oriented programming languages, and / or assembly / machine languages. The program code can be executed entirely on the user's computing device, partially on the user's device, partially on a remote computing device, or entirely on a remote computing device or server. In cases involving remote computing devices, the remote computing device can be connected to the user's computing device via any type of network, including a local area network (LAN) or a wide area network (WAN), or it can be connected to an external computing device (e.g., via the Internet using an Internet service provider).

[0128] The flowcharts and block diagrams in the accompanying drawings illustrate the architecture, functionality, and operation of possible implementations of systems, methods, and computer program products according to various embodiments of this application. In this regard, each block in a flowchart or block diagram may represent a module, segment, or portion of code containing one or more executable instructions for implementing a specified logical function. It should also be noted that in some alternative implementations, the functions indicated in the blocks may occur in a different order than those indicated in the drawings. For example, two consecutively indicated blocks may actually be executed substantially in parallel, and they may sometimes be executed in reverse order, depending on the functions involved. It should also be noted that each block in a block diagram or flowchart, and combinations of blocks in a block diagram or flowchart, may be implemented using a dedicated hardware-based system that performs the specified function or operation, or using a combination of dedicated hardware and computer instructions.

[0129] Those skilled in the art will understand that the features described in the various embodiments of this application can be combined and / or combined in various ways, even if such combinations or combinations are not explicitly described in this application. In particular, the features described in the various embodiments of this application can be combined and / or combined in various ways without departing from the spirit and teachings of this application. All such combinations and / or combinations fall within the scope of this application.

Claims

1. A data cleaning method, characterized in that, The method includes: In response to receiving multiple candidate database requests, the system filters among the multiple candidate database requests to obtain target database requests that match the characteristics of the batch job, wherein the characteristics of the batch job indicate the running conditions of the database requests to be filtered. Based on the target database request and the global view, the target temporary table is obtained by filtering among multiple candidate temporary tables, and the global view indicates the dependency relationship between the multiple candidate temporary tables. In response to receiving a completion instruction from the target database request, a cleanup operation is performed on the target temporary table.

2. The method according to claim 1, characterized in that, The method further includes: In response to a transaction status trigger instruction indicated by a query request, the calculation result is extracted from a temporary table that matches the query request, and the calculation result is stored in the application layer; In response to the application layer receiving the calculation result, a table clearing command is executed on the temporary table, and a rollback operation is performed on the transaction indicated by the query request.

3. The method according to claim 1, characterized in that, The step of filtering among the multiple candidate database requests to obtain a target database request that matches the characteristics of the batch job includes: Keyword extraction is performed on the multiple candidate database requests to obtain multiple candidate job features, which include at least one of the following: source, application identifier, duration, and database mode. Based on the operating conditions, target job features are obtained by filtering from the plurality of candidate job features. The operating conditions include at least one of source matching conditions, application identifier matching conditions, duration matching conditions, and database pattern matching conditions. The candidate database request corresponding to the target job feature is used as the target database request.

4. The method according to claim 3, characterized in that, The keyword extraction from the multiple candidate database requests yields multiple candidate job features, including: Based on a preset business dictionary, the multiple candidate database requests are segmented into multiple sets of terms, and the business dictionary indicates the segmentation benchmark in the database domain. Multiple candidate job features are obtained by filtering from multiple sets of terms based on multiple preset recognition strategies. Each of the multiple recognition strategies has a matching feature type, and the multiple recognition strategies indicate multiple judgment conditions corresponding to multiple feature types.

5. The method according to claim 1, characterized in that, The process of filtering the target database request in the global view to obtain the target temporary table includes: Based on the request identifier indicated by the target database request, multiple candidate temporary tables are obtained by filtering in the global view. The global view includes multiple temporary tables, each of which has a matching request identifier. The target temporary table is obtained by searching through the multiple candidate temporary tables based on their dependencies.

6. The method according to claim 5, characterized in that, The global view is obtained using the following method: A global view is generated using the multiple temporary tables as nodes, the basic information of the temporary tables as node attributes, and the dependencies between the multiple temporary tables as edges. The temporary table basic information includes at least one of the following: temporary table creation time, request identifier, table name, and table type; the dependency relationship includes at least one of the following: direct dependency, indirect dependency, and structural dependency.

7. The method according to claim 1, characterized in that, The cleanup operations include cleanup and analysis, full cleanup, standard cleanup, automatic cleanup, or cleanup of specified objects.

8. A data cleaning device, characterized in that, The device includes: A matching module is used to respond to receiving multiple candidate database requests, filter among the multiple candidate database requests, and obtain target database requests that match the characteristics of the batch job, wherein the characteristics of the batch job indicate the running conditions of the database requests to be filtered. A filtering module is used to filter multiple candidate temporary tables based on the target database request and a global view to obtain the target temporary table, wherein the global view indicates the dependencies between the multiple candidate temporary tables; and The cleanup module is used to perform cleanup operations on the target temporary table in response to a completion instruction received from the target database request.

9. An electronic device, comprising: One or more processors; Memory, used to store one or more computer programs. The characteristic feature is that the one or more processors execute the one or more computer programs to implement the steps of the method according to any one of claims 1 to 7.

10. A computer-readable storage medium having a computer program or instructions stored thereon, characterized in that, When the computer program or instructions are executed by a processor, they implement the steps of the method according to any one of claims 1 to 7.

11. A computer program product, comprising a computer program or instructions, characterized in that, When the computer program or instructions are executed by a processor, they implement the steps of the method according to any one of claims 1 to 7.

Images