Data restoration method and device for data warehouse, equipment, storage medium and program product

Abstract

The invention relates to a data restoration method and device for a data warehouse, equipment, a storage medium and a program product. Comprising the steps of receiving structured data input by a user; comprising to-be-repaired field information and screening conditions; generating an SQL statement according to the structured data and a preset SQL template; carrying out pre-verification on the SQL statement; after the pre-verification is passed, determining a to-be-repaired data magnitude corresponding to the SQL statement; if the magnitude of the to-be-repaired data is smaller than or equal to the preset magnitude, calling a first number repairing strategy, and executing ACID transaction operation according to the SQL statement so as to realize data repair for the data warehouse; and if the magnitude of the to-be-repaired data is greater than the preset magnitude, calling a second repair strategy, and performing data repair on the data warehouse by adopting an operation of overwriting data according to the SQL statement. By adopting the method, the number correction logic is automatically generated, misoperation is avoided, the labor cost can be saved, the repeated data correction probability can be reduced, and the situation that supervision early warning is triggered due to frequent rereporting is avoided.

Description

Technical Field

[0001] This application relates to the field of data warehouse technology, and in particular to a data repair method, apparatus, computer equipment, computer-readable storage medium, and computer program product for a data warehouse. Background Technology

[0002] The current data repair process is as follows: Business personnel identify which data requires maintenance based on the error details provided by the project team and submit corresponding data maintenance requests to the project team. The project team then analyzes the data and develops data maintenance scripts, manually writes SQL (Structured Query Language) processing logic, performs manual maintenance, and verifies the resulting data.

[0003] This approach has the following problems: the manual review process leads to an excessively long data repair process; the need to repair large amounts of data in a short period of time requires a high investment of manpower, which cannot meet the needs of high-frequency and rapid business operations; and manual SQL writing is prone to logical errors (such as missing partition conditions or field mapping deviations) that can lead to data distortion and regulatory issues. Summary of the Invention

[0004] Therefore, it is necessary to provide a data repair method, apparatus, computer equipment, computer-readable storage medium, and computer program product for data warehouses to address the above-mentioned technical problems. This method can automatically generate data repair logic, avoid misoperation, save more labor costs, and reduce the probability of repeated data correction.

[0005] Firstly, this application provides a data recovery method for a data warehouse, including:

[0006] Receive structured data input from the user; the structured data includes information about the fields to be repaired and filtering conditions;

[0007] Generate SQL statements based on structured data and preset SQL templates;

[0008] Perform pre-validation on the SQL statement;

[0009] After the pre-verification is passed, the amount of data to be repaired corresponding to the SQL statement is determined;

[0010] If the amount of data to be repaired is less than or equal to the preset amount, the first data repair strategy is invoked, and ACID transaction operations are executed according to the SQL statement to achieve data repair for the data warehouse.

[0011] If the amount of data to be repaired exceeds the preset amount, the second data repair strategy is invoked, and the data warehouse is repaired by overwriting the data according to the SQL statement.

[0012] In one embodiment, a first data repair strategy is invoked to execute ACID transaction operations based on SQL statements to achieve data repair for the data warehouse, including:

[0013] The first data revision strategy is invoked by configuring data warehouse properties, enabling concurrency support, and the transaction manager;

[0014] Create a Hive table with ACID transactions enabled; the Hive table is a bucketed table using the ORC storage format.

[0015] Based on Hive tables, ACID transaction operations are executed according to SQL statements to achieve data repair for the data warehouse.

[0016] In one embodiment, a second data repair strategy is invoked to perform data repair on the data warehouse based on the SQL statement using an overwrite operation, including:

[0017] The second data manipulation strategy is invoked to read data from the target table and write it to the temporary table;

[0018] Based on the data repair logic defined by the SQL statement, the data in the temporary table is processed, and the repaired data in the temporary table is written back to the target table.

[0019] In one embodiment, the method further includes:

[0020] Enable the distributed computing engine to read data or write the repaired data back to the target table.

[0021] In one embodiment, SQL statements are generated based on structured data and a preset SQL template, including:

[0022] Analyze the structured data to determine the current repair type; the current repair type includes at least one of the following: field value repair, number of newly added data rows, repair of fields related to other reports, and data deletion;

[0023] Get the SQL template corresponding to the current repair type;

[0024] Generate SQL statements based on structured data and the obtained SQL template.

[0025] In one embodiment, pre-validation of the SQL statement includes:

[0026] The SQL statement is sequentially subjected to syntax checking, data sample verification, performance verification, data impact pre-analysis, and consistency verification to obtain the pre-verification results.

[0027] Secondly, this application also provides a data recovery apparatus for a data warehouse, comprising:

[0028] The receiving module is used to receive structured data input by the user; the structured data includes information on the fields to be repaired and filtering conditions.

[0029] The generation module is used to generate SQL statements based on structured data and preset SQL templates;

[0030] The validation module is used to pre-validate SQL statements;

[0031] The data repair module is used to determine the amount of data to be repaired corresponding to the SQL statement after the pre-validation passes. If the amount of data to be repaired is less than or equal to the preset amount, the first data repair strategy is invoked to perform ACID transaction operations according to the SQL statement to achieve data repair for the data warehouse. If the amount of data to be repaired is greater than the preset amount, the second data repair strategy is invoked to perform data repair for the data warehouse by using the "overwrite data" operation according to the SQL statement.

[0032] Thirdly, this application also provides a computer device, including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to implement the steps of the method described in the first aspect above.

[0033] Fourthly, this application also provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps of the method described in the first aspect above.

[0034] Fifthly, this application also provides a computer program product, including a computer program that, when executed by a processor, implements the steps of the method described in the first aspect above.

[0035] The aforementioned data repair method, apparatus, computer equipment, computer-readable storage medium, and computer program product for data warehouses receive structured data input by users. This structured data includes information about fields to be repaired and filtering conditions. Based on the structured data and a preset SQL template, an SQL statement is generated. The SQL statement is pre-validated. After successful pre-validation, the volume of data to be repaired corresponding to the SQL statement is determined. If the volume of data to be repaired is less than or equal to the preset volume, a first data repair strategy is invoked, executing ACID transaction operations based on the SQL statement to repair the data warehouse. If the volume of data to be repaired is greater than the preset volume, a second data repair strategy is invoked, using an overwrite operation based on the SQL statement to repair the data warehouse. Through this method, users can directly initiate the data repair process without requiring approval or handover from other personnel, quickly completing data maintenance. Data repair logic is automatically generated based on user-input data and a preset template, avoiding erroneous operations. Selecting appropriate data repair strategies based on different volumes of data to be repaired for automated data maintenance can save significant labor costs. Pre-validating the SQL statement before execution reduces the probability of repeated data corrections, avoiding regulatory warnings triggered by frequent re-reporting. Attached Figure Description

[0036] To more clearly illustrate the technical solutions in the embodiments of this application or related technologies, the drawings used in the description of the embodiments of this application or related technologies will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other related drawings can be obtained based on these drawings without creative effort.

[0037] Figure 1 This is an application environment diagram of a data warehouse data repair method in one embodiment;

[0038] Figure 2 This is a flowchart illustrating a data repair method for a data warehouse in one embodiment;

[0039] Figure 3 This is a flowchart illustrating a data recovery method for a data warehouse in another embodiment;

[0040] Figure 4 This is a structural block diagram of a data repair device for a data warehouse in one embodiment;

[0041] Figure 5 This is an internal structural diagram of a computer device in one embodiment. Detailed Implementation

[0042] To make the objectives, technical solutions, and advantages of this application clearer, the following detailed description is provided in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the scope of this application.

[0043] It should be noted that the terms "first," "second," etc., used in this application can be used to describe various elements, but these elements are not limited by these terms. These terms are only used to distinguish the first element from the second element. The terms "comprising" and "having," and any variations thereof, used in this application, are intended to cover non-exclusive inclusion. The term "multiple" used in this application refers to two or more. The term "and / or" used in this application refers to one of the embodiments, or any combination of multiple embodiments.

[0044] The data repair method for data warehouses provided in this application embodiment can be applied to, for example... Figure 1 In the application environment shown, terminal 102 communicates with server 104 via a network. A data storage system can store the data that server 104 needs to process. The data storage system can be integrated onto server 104 or placed on a cloud or other network server. Terminal 102 submits structured data, and server 104, based on the data warehouse data repair method provided in this application embodiment, repairs the data warehouse based on the data submitted by terminal 102, obtaining the data repair result, and returning the result to terminal 102 for viewing. Terminal 102 can be, but is not limited to, various personal computers, laptops, smartphones, tablets, drones, low-altitude aircraft, IoT devices, and portable wearable devices. IoT devices can be smart speakers, smart TVs, smart air conditioners, smart vehicle devices, projection devices, etc. Portable wearable devices can be smartwatches, smart bracelets, head-mounted devices, etc. Head-mounted devices can be virtual reality (VR) devices, augmented reality (AR) devices, smart glasses, etc. Server 104 can be a standalone physical server, a server cluster or distributed system consisting of multiple physical servers, or a cloud server that provides cloud computing services.

[0045] In one exemplary embodiment, such as Figure 2 As shown, a data repair method for a data warehouse is provided, which can be applied to... Figure 1 Taking server 104 as an example, the explanation includes:

[0046] Step 202: Receive structured data input by the user; the structured data includes information on the fields to be repaired and filtering conditions.

[0047] In this system, users actively provide structured data to the system through a graphical interface. This data can be text, numbers, options, etc., and is used to trigger data repair in the data warehouse. For example, in a data warehouse management system, a user might enter the data to be modified by filling out a form. In this embodiment, the data warehouse can be a Hive data warehouse.

[0048] Structured data refers to data organized in a predefined format. Fields to be modified information refers to the specific content of which fields (or attributes) in structured data need to be modified. Filter criteria refer to the conditions used to select or filter data; these conditions determine which records or data items will be selected for modification.

[0049] For example, the system receives user input through a front-end interface or API endpoint, parses the input data into an internally usable format, and obtains structured data.

[0050] Step 204: Generate SQL statements based on structured data and preset SQL templates.

[0051] The preset SQL template refers to a pre-written SQL statement framework. In the specific implementation, the system parses the user-input information of the fields to be repaired, the filtering conditions, and the target value, and combines the preset SQL template with the repair logic generated based on business rules to automatically convert it into Hive SQL statements, thereby achieving automated processing of various data repair scenarios.

[0052] Step 206: Perform pre-validation on the SQL statement.

[0053] The pre-validation mechanism detects risks such as SQL syntax conflicts and missing partition fields. If pre-validation fails, the user is prompted to re-enter new structured data.

[0054] Step 208: After the pre-verification passes, determine the amount of data to be repaired corresponding to the SQL statement.

[0055] The "data volume to be repaired" refers to the quantity or scale of the data to be repaired, such as the number of records, thousands, tens of thousands, or millions. Optionally, a query statement is constructed based on the core part of the SQL statement, and this query statement is used to query the data warehouse to obtain the data volume to be repaired.

[0056] Step 210: If the amount of data to be repaired is less than or equal to the preset amount, the first data repair strategy is invoked, and ACID transaction operations are executed according to the SQL statement to achieve data repair for the data warehouse.

[0057] The preset data level refers to a pre-set data volume threshold used to determine which data repair strategy to adopt. For example, if the preset data level is in the thousands, and the current data volume to be repaired is less than or equal to the thousands, then the first data repair strategy will be invoked.

[0058] If the amount of data to be repaired is less than or equal to the preset amount, the first data repair strategy is invoked, and the repair SQL is executed under the protection of transactions to ensure safe and reliable operation and successfully repair the problematic data in the data warehouse.

[0059] Step 212: If the amount of data to be repaired is greater than the preset amount, the second data repair strategy is invoked, and the data warehouse is repaired by overwriting data according to the SQL statement.

[0060] If the amount of data to be repaired is greater than the preset amount, the second data repair strategy is invoked to overwrite the data, thereby achieving batch reading and writing and improving processing efficiency.

[0061] For example, the method further includes: implementing access control for partitions to avoid full table overwriting. For example, if the amount of data to be repaired is greater than a preset limit and involves multiple partitions, tasks should be submitted in batches (controlled by `hive.msck.repair.batch.size`) to prevent Out Of Memory (OOM) errors. For instance, the "INSERT OVERWRITE TABLE...PARTITION" statement can be used to rewrite data in only one specific partition at a time, and the script can be executed repeatedly. For example, if the amount of data to be repaired is less than or equal to a preset limit and involves data in multiple partitions, Hive dynamic partitioning (`hive.exec.dynamic.partition.mode=nonstrict`) can be enabled to accelerate writing.

[0062] The data repair method for the aforementioned data warehouse involves receiving structured data input from the user. This structured data includes information about the fields to be repaired and filtering conditions. Based on the structured data and a preset SQL template, an SQL statement is generated. The SQL statement is pre-validated. After successful pre-validation, the volume of data to be repaired corresponding to the SQL statement is determined. If the volume of data to be repaired is less than or equal to the preset volume, a first data repair strategy is invoked, executing ACID transactions based on the SQL statement to repair the data warehouse. If the volume of data to be repaired is greater than the preset volume, a second data repair strategy is invoked, using an overwrite operation based on the SQL statement to repair the data warehouse. This method allows users to directly initiate the data repair process without requiring approval or handover from other personnel, enabling rapid data maintenance. The data repair logic is automatically generated based on the user-input data and the preset template, preventing erroneous operations. Selecting appropriate data repair strategies based on different volumes of data to be repaired for automated data maintenance saves significant manpower costs. Pre-validating the SQL statement before execution reduces the probability of repeated data corrections, preventing regulatory warnings triggered by frequent re-reporting.

[0063] In one exemplary embodiment, a first data repair strategy is invoked to perform ACID transaction operations based on SQL statements to achieve data repair for the data warehouse. This includes: invoking the first data repair strategy to enable concurrency support and transaction manager by configuring data warehouse properties; creating a Hive table with ACID transactions enabled; the Hive table is a bucketed table using ORC storage format; and performing ACID transaction operations based on the Hive table according to SQL statements to achieve data repair for the data warehouse.

[0064] For example, if the amount of data to be repaired is less than or equal to a preset amount, generate relevant transaction operation SQL, use an ACID table, and enable Hive ACID v2 (ORC format + enabled transactions). The relevant configuration steps are as follows: Configure Hive properties to enable concurrency support and the transaction manager; create an ACID transaction table, which needs to be bucketed and stored in ORC format, setting 'transactional'='true' in the table properties; execute ACID transaction operations (insert, delete, update). 'transactional'='true' is a Hive property used to enable transactional tables, indicating that the table supports ACID operations (atomicity, consistency, isolation, durability).

[0065] In an exemplary embodiment, a second data repair strategy is invoked to perform data repair on the data warehouse using an overwrite operation based on the SQL statement. This includes: invoking the second data repair strategy to read data from the target table and write it to a temporary table; processing the data in the temporary table based on the data repair logic defined by the SQL statement; and writing the repaired data in the temporary table back to the target table.

[0066] For large-scale repairs where the amount of data to be repaired exceeds a preset limit, a "read-modify-write" mode is adopted. For example, traditional data maintenance SQL is automatically generated, and the data in the table is re-injected through INSERT OVERWRITE. A temporary staging table is used to carry out the cleaning logic, avoiding direct operation on the production table.

[0067] In one exemplary embodiment, a distributed computing engine is enabled to read data or write repaired data back to the target table.

[0068] In large-scale repair scenarios, a distributed computing engine is enabled for parallel execution to improve throughput. The distributed computing engine can be the Tez engine.

[0069] In an exemplary embodiment, step 204 includes: analyzing structured data to determine the current repair type; the current repair type includes at least one of field value repair, adding data entries, repairing fields associated with other reports, and data deletion; obtaining the SQL template corresponding to the current repair type; and generating SQL statements based on the structured data and the obtained SQL template.

[0070] Among them, field value repair: locate the problematic record by filtering by conditions, directly modify the specific field value, parse the field name, new value and filtering conditions entered by the user, and use CASE WHEN condition judgment to construct the field assignment logic.

[0071] Add new data rows: Insert new data records into the target table, allowing users to fill in the information based on the configured report and fields. Construct an "INSERT INTO...SELECT" query to retrieve data from the source table or assemble an "INSERT INTO...VALUES" statement to insert data row by row and automatically handle data type conversion and default value filling.

[0072] Repair fields in other reports: Correct field values ​​in the target table based on data from related tables, parse multi-table join conditions (JOIN relationships), construct complex queries to map source table fields to the target table, use "LEFT JOIN / INNER JOIN" to connect data sources, and implement cross-table field value replacement logic in the SELECT clause.

[0073] Data deletion and cleanup: Remove invalid, duplicate, or test data, parse deletion conditions to generate WHERE clauses, and for deduplication scenarios, use the "ROW_NUMBER()" window function to identify duplicate records and use the "INSERT OVERWRITE" method to exclude the data to be deleted or directly generate a DELETE FROM statement (when ACID tables are supported).

[0074] In this embodiment, different SQL templates and repair logic are set for different repair types. The current repair type is determined by analyzing the user input data, and an appropriate SQL template is selected to generate SQL statements, thereby improving the accuracy of the SQL statements.

[0075] In an exemplary embodiment, step 206 includes: sequentially performing syntax checks, data sample verification, performance verification, data impact pre-analysis, and consistency verification on the SQL statement to obtain a pre-verification result.

[0076] Among them, syntax checking: parses modified SQL to ensure correct syntax.

[0077] Data sample verification: Execute the data location SQL (in a test environment or with a limited number of rows) to ensure it accurately identifies the expected dirty data. If the returned result is empty, but the user claims there is a data problem, the system will issue a warning.

[0078] Performance verification: Execute the data manipulation SQL using Hive's dry-run feature, but do not actually commit it. Check if the execution plan is reasonable and whether there are potential risks such as full table scans or data skew.

[0079] Data Impact Pre-analysis: Row-level impact: Estimates how many rows of data this data correction will affect. If the number of affected rows far exceeds expectations (e.g., correcting one bad record but affecting 1 million rows), the correction will be automatically rejected and an alert will be issued. Value range validation: Checks whether the new values ​​after correction conform to business rules. For example, whether the age field is between 0 and 150, whether the amount field is positive, and whether the date format is correct, etc. This requires pre-configuration of data quality rules.

[0080] Consistency check: By comparing data snapshots before and after the SQL modification execution, check whether the business primary key is duplicated.

[0081] Understandably, if syntax checking, data sample validation, performance validation, data impact pre-analysis, and consistency checks all pass, the pre-validation is considered successful, and the subsequent data editing process is executed.

[0082] In one exemplary embodiment, refer to Figure 3The data repair method for a data warehouse includes the following steps: The user inputs key data repair information, including the fields to be repaired and filtering conditions, as prompted by the system. This information is then dynamically assembled and generated using a pre-set SQL template, metadata validation, and business rule verification. The accuracy of the entered information, including field and partition information, is checked, and the generated repair SQL is pre-validated. If validation fails, the user is prompted to re-enter the key data repair information. If validation passes, an appropriate data repair strategy is selected based on the volume of data to be repaired, and the corresponding strategy is executed to obtain the data repair result. The data repair result is then returned to the user for review.

[0083] It should be understood that although the steps in the flowcharts of the embodiments described above are shown sequentially according to the arrows, these steps are not necessarily executed in the order indicated by the arrows. Unless explicitly stated herein, there is no strict order restriction on the execution of these steps, and they can be executed in other orders. Moreover, at least some steps in the flowcharts of the embodiments described above may include multiple steps or multiple stages. These steps or stages are not necessarily completed at the same time, but can be executed at different times. The execution order of these steps or stages is not necessarily sequential, but can be performed alternately or in turn with other steps or at least some of the steps or stages in other steps. It is understood that the steps in different embodiments can be freely combined as needed, and all non-contradictory solutions formed by such combinations are within the scope of protection of this application.

[0084] Based on the same inventive concept, this application also provides a data warehouse data repair apparatus for implementing the data warehouse data repair method described above. The solution provided by this apparatus is similar to the implementation described in the above method; therefore, the specific limitations in one or more data warehouse data repair apparatus embodiments provided below can be found in the limitations of the data warehouse data repair method described above, and will not be repeated here.

[0085] In one exemplary embodiment, such as Figure 4 As shown, a data repair device for a data warehouse is provided, comprising:

[0086] The receiving module 402 is used to receive structured data input by the user; the structured data includes information on the fields to be repaired and filtering conditions;

[0087] The generation module 404 is used to generate SQL statements based on structured data and preset SQL templates;

[0088] Verification module 406 is used to pre-validate SQL statements;

[0089] The data repair module 408 is used to determine the amount of data to be repaired corresponding to the SQL statement after the pre-verification passes. If the amount of data to be repaired is less than or equal to the preset amount, the first data repair strategy is invoked to perform ACID transaction operations according to the SQL statement to achieve data repair for the data warehouse. If the amount of data to be repaired is greater than the preset amount, the second data repair strategy is invoked to perform data repair for the data warehouse by using the "overwrite data" operation according to the SQL statement.

[0090] In the data repair device of the aforementioned data warehouse, the user directly initiates the data repair process without the need for approval or handover by other personnel, quickly completing data maintenance. Based on the user-input data and preset templates, repair logic is automatically generated, avoiding erroneous operations. Automated data maintenance, by selecting appropriate repair strategies according to different volumes of data to be repaired, can save significant labor costs. Pre-validation of SQL statements before execution reduces the probability of repeated data corrections, preventing regulatory warnings from being triggered by frequent re-reporting.

[0091] In an exemplary embodiment, the data repair module 408 is further configured to invoke a first data repair strategy to enable concurrency support and a transaction manager by configuring data warehouse properties; create a Hive table with ACID transactions enabled; the Hive table is a bucketed table using ORC storage format; and perform ACID transaction operations based on the Hive table according to SQL statements to achieve data repair for the data warehouse.

[0092] In an exemplary embodiment, the data repair module 408 is further configured to call a second data repair strategy to read data from the target table and write it to a temporary table; based on the data repair logic defined by the SQL statement, the data in the temporary table is processed, and the repaired data in the temporary table is written back to the target table.

[0093] In one exemplary embodiment, the data repair module 408 is also configured to enable a distributed computing engine to read data or write the repaired data back to the target table.

[0094] In an exemplary embodiment, the generation module 404 is further configured to analyze the structured data and determine the current repair type; the current repair type includes at least one of field value repair, number of new data entries, repair of fields associated with other reports, and data deletion; obtain the SQL template corresponding to the current repair type; and generate SQL statements based on the structured data and the obtained SQL template.

[0095] In an exemplary embodiment, the verification module 406 is further configured to sequentially perform syntax checking, data sample verification, performance verification, data impact pre-analysis, and consistency verification on the SQL statement to obtain a pre-verification result.

[0096] The modules in the data repair device of the aforementioned data warehouse can be implemented entirely or partially through software, hardware, or a combination thereof. These modules can be embedded in the processor of a computer device in hardware form or independent of it, or stored in the memory of the computer device in software form, so that the processor can call and execute the operations corresponding to each module.

[0097] In one exemplary embodiment, a computer device is provided, which may be a server, and its internal structure diagram may be as follows: Figure 5 As shown, this computer device includes a processor, memory, input / output interfaces (I / O), and a communication interface. The processor, memory, and I / O interfaces are connected via a system bus, and the communication interface is also connected to the system bus via the I / O interfaces. The processor provides computational and control capabilities. The memory includes non-volatile storage media and internal memory. The non-volatile storage media stores the operating system, computer programs, and a database. The internal memory provides the environment for the operating system and computer programs stored in the non-volatile storage media. The database stores data. The I / O interfaces allow the processor to exchange information with external devices. The communication interface allows communication with external terminals via a network connection. When executed by the processor, the computer program implements a data warehouse data repair method.

[0098] Those skilled in the art will understand that Figure 5 The structure shown is merely a block diagram of a portion of the structure related to the present application and does not constitute a limitation on the computer device to which the present application is applied. Specific computer devices may include more or fewer components than those shown in the figure, or combine certain components, or have different component arrangements.

[0099] In one exemplary embodiment, a computer device is provided, including a memory and a processor. The memory stores a computer program, and the processor executes the computer program to perform the following steps: receiving structured data input by a user; the structured data includes information about fields to be repaired and filtering conditions; generating an SQL statement based on the structured data and a preset SQL template; pre-validating the SQL statement; after the pre-validation passes, determining the amount of data to be repaired corresponding to the SQL statement; if the amount of data to be repaired is less than or equal to a preset amount, invoking a first data repair strategy and executing an ACID transaction operation based on the SQL statement to achieve data repair for the data warehouse; if the amount of data to be repaired is greater than the preset amount, invoking a second data repair strategy and performing a data repair operation on the data warehouse using an overwrite operation based on the SQL statement.

[0100] In one embodiment, when the processor executes the computer program, it also performs the following steps: invoking a first data repair strategy to enable concurrency support and a transaction manager by configuring data warehouse properties; creating a Hive table with ACID transactions enabled; the Hive table is a bucketed table using ORC storage format; and performing ACID transaction operations based on the Hive table and SQL statements to achieve data repair for the data warehouse.

[0101] In one embodiment, when the processor executes the computer program, it also performs the following steps: invoking a second data repair strategy to read data from the target table and write it to a temporary table; processing the data in the temporary table based on the data repair logic defined by the SQL statement, and writing the repaired data in the temporary table back to the target table.

[0102] In one embodiment, the processor, while executing a computer program, also performs the following steps: enabling a distributed computing engine to read data or write the repaired data back to the target table.

[0103] In one embodiment, when the processor executes the computer program, it further performs the following steps: analyzing structured data to determine the current repair type; the current repair type includes at least one of field value repair, adding data entries, repairing fields associated with other reports, and data deletion; obtaining the SQL template corresponding to the current repair type; and generating SQL statements based on the structured data and the obtained SQL template.

[0104] In one embodiment, when the processor executes the computer program, it also performs the following steps: sequentially performs syntax checking, data sample verification, performance verification, data impact pre-analysis, and consistency verification on the SQL statement to obtain the pre-verification result.

[0105] In one embodiment, a computer-readable storage medium is provided, on which a computer program is stored. When executed by a processor, the computer program performs the following steps: receiving structured data input by a user; the structured data includes information about fields to be repaired and filtering conditions; generating an SQL statement based on the structured data and a preset SQL template; pre-validating the SQL statement; after the pre-validation passes, determining the amount of data to be repaired corresponding to the SQL statement; if the amount of data to be repaired is less than or equal to a preset amount, invoking a first data repair strategy and executing an ACID transaction operation based on the SQL statement to achieve data repair for the data warehouse; if the amount of data to be repaired is greater than the preset amount, invoking a second data repair strategy and performing data repair on the data warehouse by overwriting data based on the SQL statement.

[0106] In one embodiment, when the computer program is executed by the processor, it further performs the following steps: invoking a first data repair strategy by configuring data warehouse properties, enabling concurrency support and a transaction manager; creating a Hive table with ACID transactions enabled; the Hive table is a bucketed table using ORC storage format; and performing ACID transaction operations based on the Hive table and SQL statements to achieve data repair for the data warehouse.

[0107] In one embodiment, when the computer program is executed by the processor, it further performs the following steps: invoking a second data repair strategy to read data from the target table and write it to a temporary table; processing the data in the temporary table based on the data repair logic defined by the SQL statement, and writing the repaired data in the temporary table back to the target table.

[0108] In one embodiment, when the computer program is executed by the processor, it also performs the following steps: enabling a distributed computing engine to read data or write the repaired data back to the target table.

[0109] In one embodiment, when the computer program is executed by the processor, it further performs the following steps: analyzing structured data to determine the current repair type; the current repair type includes at least one of field value repair, adding data entries, repairing fields associated with other reports, and data deletion; obtaining the SQL template corresponding to the current repair type; and generating SQL statements based on the structured data and the obtained SQL template.

[0110] In one embodiment, when the computer program is executed by the processor, it further performs the following steps: sequentially performs syntax checking, data sample verification, performance verification, data impact pre-analysis, and consistency verification on the SQL statement to obtain the pre-verification result.

[0111] In one embodiment, a computer program product is provided, including a computer program that, when executed by a processor, performs the following steps: receiving structured data input by a user; the structured data includes information about fields to be repaired and filtering conditions; generating an SQL statement based on the structured data and a preset SQL template; pre-validating the SQL statement; after passing the pre-validation, determining the amount of data to be repaired corresponding to the SQL statement; if the amount of data to be repaired is less than or equal to a preset amount, invoking a first data repair strategy and executing an ACID transaction operation based on the SQL statement to achieve data repair for the data warehouse; if the amount of data to be repaired is greater than the preset amount, invoking a second data repair strategy and performing data repair on the data warehouse by overwriting data based on the SQL statement.

[0112] In one embodiment, when the computer program is executed by the processor, it further performs the following steps: invoking a first data repair strategy by configuring data warehouse properties, enabling concurrency support and a transaction manager; creating a Hive table with ACID transactions enabled; the Hive table is a bucketed table using ORC storage format; and performing ACID transaction operations based on the Hive table and SQL statements to achieve data repair for the data warehouse.

[0113] In one embodiment, when the computer program is executed by the processor, it further performs the following steps: invoking a second data repair strategy to read data from the target table and write it to a temporary table; processing the data in the temporary table based on the data repair logic defined by the SQL statement, and writing the repaired data in the temporary table back to the target table.

[0114] In one embodiment, when the computer program is executed by the processor, it also performs the following steps: enabling a distributed computing engine to read data or write the repaired data back to the target table.

[0115] In one embodiment, when the computer program is executed by the processor, it further performs the following steps: analyzing structured data to determine the current repair type; the current repair type includes at least one of field value repair, adding data entries, repairing fields associated with other reports, and data deletion; obtaining the SQL template corresponding to the current repair type; and generating SQL statements based on the structured data and the obtained SQL template.

[0116] In one embodiment, when the computer program is executed by the processor, it further performs the following steps: sequentially performs syntax checking, data sample verification, performance verification, data impact pre-analysis, and consistency verification on the SQL statement to obtain the pre-verification result.

[0117] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, data stored, data displayed, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of the relevant data must comply with relevant regulations.

[0118] Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. The computer program can be stored in a non-volatile computer-readable storage medium, and when executed, it can include the processes of the embodiments of the above methods. Any references to memory, databases, or other media used in the embodiments provided in this application can include at least one of non-volatile memory and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetic random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory can include random access memory (RAM) or external cache memory, etc. By way of illustration and not limitation, RAM can take many forms, such as Static Random Access Memory (SRAM) or Dynamic Random Access Memory (DRAM). The databases involved in the embodiments provided in this application may include at least one type of relational database and non-relational database. Non-relational databases may include, but are not limited to, blockchain-based distributed databases. The processors involved in the embodiments provided in this application may be general-purpose processors, central processing units, graphics processing units, digital signal processors, programmable logic devices, quantum computing-based data processing logic devices, artificial intelligence (AI) processors, etc., and are not limited to these.

[0119] The technical features of the above embodiments can be combined in any way. For the sake of brevity, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this application.

[0120] The embodiments described above are merely illustrative of several implementation methods of this application, and while the descriptions are specific and detailed, they should not be construed as limiting the scope of this patent application. It should be noted that those skilled in the art can make various modifications and improvements without departing from the concept of this application, and these all fall within the protection scope of this application. Therefore, the protection scope of this application should be determined by the appended claims.

Claims

1. A data repair method for a data warehouse, characterized in that, The method includes: Receive structured data input by the user; the structured data includes information on the fields to be repaired and filtering conditions; Based on the structured data and the preset SQL template, generate SQL statements; Perform pre-validation on the SQL statement; After the pre-verification is passed, the amount of data to be repaired corresponding to the SQL statement is determined; If the amount of data to be repaired is less than or equal to a preset amount, the first data repair strategy is invoked, and ACID transaction operations are executed according to the SQL statement to achieve data repair for the data warehouse. If the amount of data to be repaired is greater than the preset amount, the second data repair strategy is invoked, and the data warehouse is repaired by overwriting data according to the SQL statement.

2. The method according to claim 1, characterized in that, The invocation of the first data repair strategy, which executes ACID transaction operations based on the SQL statement to achieve data repair for the data warehouse, includes: The first data revision strategy is invoked by configuring data warehouse properties, enabling concurrency support, and the transaction manager; Create a Hive table with ACID transactions enabled; the Hive table is a bucketed table using the ORC storage format; Based on the Hive table, ACID transaction operations are executed according to the SQL statement to achieve data repair for the data warehouse.

3. The method according to claim 1, characterized in that, The invocation of the second data repair strategy, based on the SQL statement using an overwrite data operation, performs data repair on the data warehouse, including: The second data manipulation strategy is invoked to read data from the target table and write it to the temporary table; Based on the data repair logic defined by the SQL statement, the data in the temporary table is processed, and the repaired data in the temporary table is written back to the target table.

4. The method according to claim 3, characterized in that, The method further includes: Enable the distributed computing engine to read data or write the repaired data back to the target table.

5. The method according to claim 1, characterized in that, The step of generating SQL statements based on the structured data and a preset SQL template includes: Analyze the structured data to determine the current repair type; the current repair type includes at least one of the following: field value repair, number of new data entries, repair of fields associated with other reports, and data deletion; Retrieve the SQL template corresponding to the current repair type; Based on the structured data and the obtained SQL template, generate SQL statements.

6. The method according to any one of claims 1 to 5, characterized in that, The pre-validation of the SQL statement includes: The SQL statement is sequentially subjected to syntax checking, data sample verification, performance verification, data impact pre-analysis, and consistency verification to obtain the pre-verification results.

7. A data repair device for a data warehouse, characterized in that, The device includes: The receiving module is used to receive structured data input by the user; the structured data includes information on the fields to be repaired and filtering conditions. The generation module is used to generate SQL statements based on the structured data and the preset SQL template; The verification module is used to pre-verify the SQL statement; The data repair module is used to determine the amount of data to be repaired corresponding to the SQL statement after the pre-verification passes; if the amount of data to be repaired is less than or equal to a preset amount, a first data repair strategy is invoked to perform ACID transaction operations according to the SQL statement to achieve data repair for the data warehouse; if the amount of data to be repaired is greater than the preset amount, a second data repair strategy is invoked to perform data repair on the data warehouse by using an overwrite operation according to the SQL statement.

8. A computer device comprising a memory and a processor, wherein the memory stores a computer program, characterized in that, When the processor executes the computer program, it implements the steps of the method according to any one of claims 1 to 6.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 6.

10. A computer program product, comprising a computer program, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 6.

Images