Hive table repeatable and traceable data processing method and device

Abstract

The invention provides a Hive table repeatable and traceable data processing method and device, and relates to the technical field of big data processing. Comprising the following steps: setting a source table, a result table, a verification table and a version table of batch processing tasks; starting to call the batch processing task, and reading call parameters; when the execution mode is traceability statistics, the partition data corresponding to the task date in the version table is migrated, covered and written into the result table and the check table. And when the execution mode is repeated statistics, directly reading the original data corresponding to the task date from the source table, and executing the batch processing task corresponding to the task date. Under the condition that the batch processing task is executed successfully, the processing result data is written into the result table and the verification table in an overlay mode, and when the task date is the version management date, the processing result data is written into the partition table corresponding to the task date in the version table in an overlay mode. Repeatable execution of batch processing tasks and traceable management of data versions are achieved, and meanwhile the method is suitable for the unified processing flow of partition tables and non-partition tables.

Description

Technical Field

[0001] The present invention relates to the technical field of big data processing, and in particular to a method and device for processing repeatable and traceable data of Hive tables. Background Art

[0002] In the field of big data technology components, Hive, as a widely used data warehouse processing tool, is mainly used for large-scale data batch processing task scenarios. Hive data tables usually include partitioned tables and non-partitioned tables. Generally, when processing large data sets in actual application scenarios, due to changes in business requirements or technical defect anomalies, it is usually necessary to reprocess the current batch processing task or re-execute historical batch processing tasks. For example, due to changes in business requirements, the original processed data results are no longer applicable, and it is necessary to trace the data back to a specific time point, adjust the processing logic, and then reprocess it. Another example is that during the data batch processing process, an anomaly occurs, and after targeted repair, the affected tasks need to be reprocessed.

[0003] At this time, for Hive partitioned tables, the task repetition processing can be better supported through its partition coverage rewrite feature; however, it is not convenient for data tracing. For Hive non-partitioned tables, since the underlying big data component Hadoop Distributed File System (HDFS) follows the principle of "write once, read multiple times", Hive non-partitioned tables do not support repeat processing (similar to update operations) and cannot perform data tracing. Summary of the Invention

[0004] The present invention provides a method and device for processing repeatable and traceable data of Hive tables to solve the problem in the prior art that data of Hive tables (especially non-partitioned tables) cannot be repeatedly updated and version tracing is difficult due to the "write once" feature of HDFS, and to realize the repeatable execution of batch processing tasks and the backtracking management of data versions, and at the same time apply to the unified processing flow of partitioned tables and non-partitioned tables. The technical solutions proposed by the present invention are as follows: In a first aspect, the present invention provides a method for processing repeatable and traceable data of Hive tables, including: Setting a source table, a result table, a verification table, and a version table for a batch processing task, where the result table is a Hive table, and the structures of the verification table and the version table are the same as the structure of the result table; the source table stores the original data to be processed; When the batch processing task call starts, reading the call parameters; where the call parameters include an execution method and a task date, and the execution method includes standard statistics, trace statistics, and repeat statistics; When the execution method is retrospective statistics, migrate and overwrite the partition data corresponding to the task date in the version table into the result table and the verification table; When the execution method is repeated statistics, directly read the original data corresponding to the task date from the source table and execute the batch task corresponding to the task date; When the execution method is standard statistics, verify and compare the data consistency between the result table and the verification table. If they are consistent, read the original data corresponding to the task date from the source table and execute the batch task corresponding to the task date; In the case where the batch task is successfully executed, obtain the corresponding processing result data, overwrite and write the processing result data into the result table and the verification table, and when the task date is the version management date, overwrite and write the processing result data into the partition table corresponding to the task date in the version table.

[0005] Optionally, the result table stores all processing result data with a data volume of n; the verification table only retains the processing result data of the previous processing with a data volume of 1; the version table stores the processing result data sampled and archived as needed with a data volume of m, and 1 < m < n.

[0006] Optionally, the result table is a partitioned table or a non-partitioned table, the verification table is a non-partitioned table, and the version table is a partitioned table.

[0007] Optionally, the verification of the data consistency between the result table and the verification table includes: If the result table is a partitioned table, verify the data consistency between the partition table corresponding to the day before the task date in the result table and the verification table; If the result table is a non-partitioned table, directly verify the data consistency between the result table and the verification table.

[0008] Optionally, the version management date is set by week, month, or year, and the partitioning of the version table is set to be partitioned by week, month, or year according to version management requirements.

[0009] Optionally, the method further includes: In the case where the batch task fails or the data in the result table is inconsistent with the data in the verification table, enter the exception process for handling. After repairing the exception, re-execute the task; where the exception process for handling includes re-executing the task after repairing the data or adjusting the resources.

[0010] In a second aspect, the present invention further provides a repeatable and traceable data processing device for Hive tables, including the following modules: A table setting module for setting the source table, result table, verification table, and version table of a batch processing task. Among them, the result table is a Hive table, and the structures of the verification table and the version table are the same as the structure of the result table; the source table stores the original data to be processed; A parameter reading module for reading call parameters when the batch processing task is called; among them, the call parameters include an execution method and a task date, and the execution method includes standard statistics, traceability statistics, and duplicate statistics; A traceability processing module for migrating and overwriting the partition data corresponding to the task date in the version table into the result table and the verification table when the execution method is traceability statistics; A duplicate processing module for directly reading the original data corresponding to the task date from the source table and executing the batch processing task corresponding to the task date when the execution method is duplicate statistics; A standard processing module for verifying and comparing the data consistency between the result table and the verification table when the execution method is standard statistics. If they are consistent, it reads the original data corresponding to the task date from the source table and executes the batch processing task corresponding to the task date; A task execution module for obtaining the corresponding processed result data when the batch processing task is executed successfully, overwriting and writing the processed result data into the result table and the verification table, and overwriting and writing the processed result data into the partition table corresponding to the task date in the version table when the task date is the version management date.

[0011] In a third aspect, the present invention also provides an electronic device, including a memory, a processor, and a computer program stored on the memory and running on the processor. When the processor executes the computer program, it implements the Hive table repeatable and traceable data processing method as described in the first aspect above.

[0012] In a fourth aspect, the present invention also provides a non-transitory computer-readable storage medium, on which a computer program is stored. When the computer program is executed by a processor, it implements the Hive table repeatable and traceable data processing method as described in the first aspect above.

[0013] In a fifth aspect, the present invention also provides a computer program product, including a computer program. When the computer program is executed by a processor, it implements the Hive table repeatable and traceable data processing method as described in the first aspect above.

[0014] Based on the above technical solutions, the beneficial effects of the present invention compared with the prior art are: The Hive table repeatable and traceable data processing method and device provided by the present invention simplify the data processing logic through the structural consistency design of the result table, verification table, and version table. As an intermediate result storage layer, the verification table not only ensures the convenience of data consistency verification but also provides a reliable basis for troubleshooting exceptions during task execution. Through the historical data backtracking ability of the version table partitioned by date and combined with the direct reading mechanism of the original data in the source table, it not only meets the requirements of data auditing and historical data reproduction but also ensures the repeatable execution of tasks after changes in the original data, supporting standard statistics, traceability statistics, and repeat statistics modes. By synchronously writing the result data to the verification table and result table when the task is successful and overwriting and writing the data to the corresponding partition of the version table on the version management day, atomic update and rapid backtracking of data versions are achieved, avoiding the version overwrite problem caused by the "write once only" feature of traditional HDFS. The method of the present invention is applicable to both the efficient version management of partitioned tables and seamlessly supports the repeated update of data in non-partitioned tables, significantly reducing the system complexity and improving the flexibility and scalability of data processing.

[0015] Other features and advantages of the present invention will be described in the following specification, and in part, will be obvious from the specification, or will be understood by implementing the present invention. The objectives and other advantages of the present invention are realized and obtained by the structures specifically pointed out in the specification and the drawings.

[0016] To make the above objectives, features, and advantages of the present invention more obvious and understandable, the following specific preferred embodiments are given in conjunction with the accompanying drawings and are described in detail as follows. Description of the Drawings

[0017] To more clearly illustrate the technical solutions in the present invention or the prior art, the following will briefly introduce the drawings required for use in the description of the embodiments or the prior art. Obviously, the following drawings are some embodiments of the present invention. For those of ordinary skill in the art, other drawings can be obtained based on these drawings without creative efforts.

[0018] Figure 1 It is a schematic flow chart of the Hive table repeatable and traceable data processing method provided by the present invention.

[0019] Figure 2 It is a schematic structural diagram of the Hive table repeatable and traceable data processing device provided by the present invention.

[0020] Figure 3 It is a schematic structural diagram of the electronic device provided by the present invention. Detailed Embodiments

[0021] To make the objectives, technical solutions and advantages of the present invention clearer, the technical solutions in the present invention will be clearly and completely described below in conjunction with the accompanying drawings in the present invention. Obviously, the described embodiments are some but not all of the embodiments of the present invention. All other embodiments obtained by those of ordinary skill in the art based on the embodiments in the present invention without creative efforts shall fall within the protection scope of the present invention.

[0022] Figure 1 It is a schematic flowchart of the repeatable and traceable data processing method for the Hive table provided by the present invention. By introducing a verification table (t2 table) and a version table (t3 table) and combining specific task execution logics, the present invention method realizes the repeatable processing and version tracing functions of the data in the Hive table (including partitioned tables and non-partitioned tables). Refer to Figure 1 As shown, the method includes the following: S110. Set the source table, result table, verification table and version table of the batch processing task. Among them, the result table is a Hive table, and the structures of the verification table and the version table are the same as the structure of the result table; the source table stores the original data to be processed.

[0023] First, perform table structure setting: Source table (tmp table): It is the original data input source of the batch processing task in this method and stores the original data to be processed (such as business detail data, dimension data, etc.). Business detail data refers to business transaction flows (such as orders, payment records), log data (such as user behavior logs), etc. Dimension data refers to code tables (such as product categories, area codes), etc.

[0024] Result table (t1 table): Stores the processing result data of the batch processing task. The t1 table is the Hive table described in the present invention and can be a partitioned table or a non-partitioned table.

[0025] Verification table (t2 table): It has the same structure as the t1 table (including the same fields), but the partition characteristics can be inconsistent. In the present invention, the t2 table is set as a non-partitioned table and is used to store the data copy of the last successful execution for data consistency verification.

[0026] Version table (t3 table): It has the same structure as the t1 table (including the same fields), but the partition characteristics can be inconsistent. In the present invention, the t3 table is set as a partitioned table and stores historical version data according to business requirements (such as weekly, monthly, yearly) for data backtracking.

[0027] S120. When the batch processing task call starts, read the call parameters; among them, the call parameters include the execution method and the task date, and the execution method includes standard statistics, traceability statistics and repeat statistics.

[0028] When the batch task is started, call parameters are read, including: Execution method: standard statistics (normal task execution), retrospective statistics (restoring data from historical versions), or repeated statistics (re-executing the task).

[0029] Task date (D1): Specify the data date to be processed.

[0030] S130. When the execution method is retrospective statistics, migrate and overwrite the partition data corresponding to the task date in the version table to the result table and the verification table.

[0031] When the execution method is retrospective statistics, read the partition data corresponding to D1 from the version table t3, and overwrite and write it to the result table t1 and the verification table t2. The applicable scenario is: when business requirements change or data is incorrect, it is necessary to roll back to a certain historical version for recalculation.

[0032] S140. When the execution method is repeated statistics, directly read the original data corresponding to the task date from the source table, and execute the batch task corresponding to the task date.

[0033] When the execution method is repeated statistics, directly read the original data of D1 from the source table tmp, and re-execute the batch task. The applicable scenario is: when the data processing logic is adjusted or the task execution fails, it is necessary to recalculate.

[0034] S150. When the execution method is standard statistics, verify and compare the data consistency between the result table and the verification table. If they are consistent, read the original data corresponding to the task date from the source table, and execute the batch task corresponding to the task date.

[0035] When the execution method is standard statistics, verify and compare the data consistency between the result table t1 and the verification table t2; in particular, if the result table t1 processed by the current task is a partition table, verify the data consistency between the partition table corresponding to D0, the day before the task date D1, in the result table t1 and the verification table t2. The applicable scenario is daily standard daily data batch processing.

[0036] The above retrospective statistics refers to re-statistics by tracing historical data, and repeated statistics refers to repeated execution multiple times. Relative to these two, standard statistics refers to standardized statistics with a fixed process. Standard statistics is a data statistics method that takes preset rules and consistency verification as the core and executes according to a fixed logic and standard process in the data processing flow. Under normal circumstances, standard statistics is adopted. If standard statistics goes wrong or has defects, repeated statistics can be adopted after fixing the problem; when re-calculating the data of a certain day in history, the data in the version table t3 can be used to adopt retrospective statistics, which can trace back to a certain day, a certain month, or from a historical point in time all the way to the present.

[0037] When the execution mode is standard statistics, check the data consistency between the comparison result table (Table t1) and the verification table (Table t2). The verification logic includes but is not limited to: data volume verification, field value verification, verification rule verification, etc. Data volume verification refers to whether the number of records in Table t1 and Table t2 is consistent. Field value verification refers to whether the values of key fields (such as amount, quantity, timestamp) are exactly matched. Verification rule verification refers to verifying the data consistency according to business rules (such as data range, format, association relationship).

[0038] If the verification passes (i.e., the data in Table t1 and Table t2 is consistent), then perform the subsequent steps, that is, read the data in the tmp table corresponding to D1 and execute the D1 batch task. If the verification fails, terminate the task and generate a verification report, indicating the specific location and reason for the data inconsistency.

[0039] Read the data in the tmp table corresponding to D1 and execute the D1 batch task, including: Read the original data corresponding to the task date (D1) from the source table. The original data needs to be preprocessed (such as data cleaning, format conversion) to ensure compatibility with the task logic. Use the read original data to execute the batch task corresponding to D1. Synchronize the task results to write into the result table (Table t1) and the verification table (Table t2) to ensure the data consistency of the two tables.

[0040] S160. In the case where the batch task is executed successfully, obtain the corresponding processing result data, overwrite and write the processing result data into the result table and the verification table, and when the task date is the version management date, overwrite and write the processing result data into the partition table corresponding to the task date in the version table.

[0041] If the batch task is executed successfully, then: 1. Update the result table Table t1 and the verification table Table t2: Overwrite and write the processing result data into Table t1 and Table t2.

[0042] 2. Version management: If D1 is the set version management date (such as every Monday or the beginning of each month), then write the processing result data into the D1 partition of the version table Table t3 to form a new data snapshot.

[0043] If the task execution fails or the data verification is inconsistent, enter the exception handling process (such as data repair or resource adjustment) and re-execute the task.

[0044] The check table, result table, and version table in the present invention adopt exactly the same table structure, avoiding the overhead of data conversion and mapping, and ensuring that no additional processing is required when data flows between tables. The check table, as a copy of the result table, can be quickly used for data consistency verification, with unified and efficient verification logic. The version table is isomorphic to the result table, enabling the reading and tracing back of historical version data without complex data conversion, and the version management logic is clear and easy to maintain. The version table stores historical data partitioned by date, supporting quick positioning and tracing back of any version according to the time dimension, meeting the requirements of data auditing and historical data reproduction. Whether it is a partitioned table or a non-partitioned table, tasks are processed through the unified process of "result table → check table → version table", reducing the system complexity and enhancing the code reusability and maintainability.

[0045] The method for processing repeatable and traceable data of the Hive table provided by the present invention simplifies the data processing logic through the consistent structure design of the result table, check table, and version table. The check table, as an intermediate result storage layer, not only ensures the convenience of data consistency verification but also provides a reliable basis for troubleshooting exceptions during task execution. The support for the modes of standard statistics, traceability statistics, and duplicate statistics, through the ability to trace back historical data partitioned by date in the version table, combined with the direct reading mechanism of the original data in the source table, not only meets the requirements of data auditing and historical data reproduction but also ensures the repeatable execution of tasks after changes in the original data. Moreover, the refined design of the version management strategy realizes the atomic update and quick traceback of data versions by synchronously writing the result data to the check table and the result table when the task is successful and overwriting the data to the corresponding partition of the version table on the version management day, avoiding the version overwrite problem caused by the "write-once" characteristic of traditional HDFS. The method of the present invention is applicable to the efficient version management of partitioned tables and can seamlessly support the repeated update of data in non-partitioned tables, significantly reducing the system complexity and enhancing the flexibility and scalability of data processing. The present invention provides a comprehensive and efficient solution for the data repeatability and traceability of Hive tables.

[0046] Specifically, when the execution mode is retrospective statistics, the task reads the partition data corresponding to the specified task date from the version table, ensuring that the task can be rerun based on historical data at any point in time, meeting the requirements of data auditing, compliance checking, and historical data reproduction. Through the historical version management of the version table, the problem of irreproducibility of historical task results caused by data source changes is avoided, ensuring the accuracy and credibility of audit results. Support for rerunning and repairing historical tasks. For example, for incorrect results caused by data quality problems, they can be recalculated through the retrospective statistics mode and overwritten and written to the result table and the verification table. The method of the present invention can be applied to the financial audit scenario. For example, if it is necessary to retrieve transaction data for the past six months, through the retrospective statistics mode, the full volume of historical data can be rerun within 3 hours, and a report meeting the audit requirements can be generated. It can also be applied to the e-commerce order replenishment scenario. For example, if an e-commerce platform loses order data due to system upgrade, through the retrospective statistics mode, order information can be regenerated based on the historical data in the version table, avoiding business losses.

[0047] When the execution mode is repeated statistics, the task directly reads the original data of the specified task date from the source table, avoiding calculation deviations caused by inconsistent data in the version table or the result table, ensuring the timeliness and accuracy of the task results. Support for repeated execution of the task after the original data changes, such as data supplementation, correction, or update of the data source, ensuring that the task results are always based on the latest data. Skip reading the version table and the verification table and directly obtain data from the source table, reducing the data flow link and improving the task execution efficiency.

[0048] The standard statistics, retrospective statistics, and repeated statistics modes can be used independently or in coordination to meet the requirements of different business scenarios. For example, the standard statistics mode is used for daily tasks to ensure data timeliness; the retrospective statistics mode is used for audit tasks to ensure data traceability. When the task fails due to data quality problems, it can be switched to the retrospective statistics mode and rerun based on historical data; when the data source is repaired, it can be switched to the repeated statistics mode to recalculate the latest results. The support for multiple modes reduces the task failure rate caused by data inconsistency and improves system stability.

[0049] Before the task execution, by comparing the result table t1 and the verification table t2, the correctness of the historical task results is ensured. If there are problems with the historical data, they can be discovered in advance and the task can be terminated to avoid error propagation. After the task execution, the consistency between the result table t1 and the verification table t2 is verified again to ensure the accuracy of the current task results. The synchronous update mechanism of the result table t1 and the verification table t2 ensures that the data in the two tables always remains consistent, avoiding subsequent task failures caused by data drift. If the verification of the result table t1 and the verification table t2 fails, the task is immediately terminated to avoid calculation deviations caused by incorrect data. The verification report provides detailed error information, facilitating quick problem location and repair. After verification fails, it is possible to switch to the traceability statistics mode or the repeated statistics mode, and re-execute the task based on historical data or original data to ensure the ultimate success of the task. The verification logic of the result table t1 and the verification table t2 is encapsulated uniformly, reducing code redundancy and improving maintainability. The verification process is automated, reducing manual intervention and operational risks. According to the verification results, the task execution mode (standard statistics, traceability statistics, repeated statistics) is dynamically selected to meet the requirements of different business scenarios.

[0050] In the present invention, the result table t1 stores all the processed result data with a data volume of n; the verification table t2 only retains the processed result data of the previous processing with a data volume of 1; the version table t3 stores the processed result data sampled and archived as needed with a data volume of m, and 1 < m < n is satisfied.

[0051] The result table t1 stores all the processed result data, ensuring that all processed result data can be completely saved, providing a comprehensive data basis for subsequent data analysis. The verification table t2 retains the processed result data of the previous time. By comparing the current processed results with the data in the verification table, errors in the data processing process can be discovered in time to ensure data accuracy. The version table t3 stores the sampled and archived processed result data, enabling quick access to the corresponding version of the data when historical data needs to be reviewed, meeting the data traceability and analysis requirements at different time points and business needs. Through the way of hierarchical storage and sampling archiving, the present invention not only ensures the integrity and traceability of the data, but also avoids storing too much redundant data, improves the efficiency of data storage, and reduces the storage cost.

[0052] In some embodiments, the result table t1 is a partitioned table or a non-partitioned table, the verification table t2 is a non-partitioned table, and the version table t3 is a partitioned table.

[0053] When the result table (Table t1) is a partitioned table, it is partitioned by task date, and each partition stores the task results for the corresponding date. When the result table (Table t1) is a non-partitioned table, all task results are stored in a single table and differentiated by the task date field (such as dt). When the verification table (Table t2) is a non-partitioned table, it stores the verification results of the previous task date for global data consistency verification. When the version table (Table t3) is a partitioned table, it is partitioned by version number (such as the version_id field), and each partition stores the historical version data of task execution.

[0054] The result table, Table t1, stores task results by date partition, and the verification table, Table t2, stores global verification results. The verification logic is as follows: Check a certain partition of the result table, Table t1 (such as dt = 20231001). Compare the data in this partition with the data records in the verification table, Table t2. Through partition pruning, only read the relevant partition data in the result table, Table t1, to reduce I / O overhead.

[0055] The version table, Table t3, stores the historical versions of task execution as a partitioned table, and each partition corresponds to a version number. When the data in Table t1 is abnormal, historical version data can be read from Table t3, the task can be re-executed, and Table t1 can be overwritten. The partition storage of Table t3 ensures the traceability of historical version data and meets compliance requirements. It also supports quick data repair and reduces business interruption time. For example: If the data for September 2023 in Table t1 is lost due to system upgrade in the bank, the data in the partition with version_id = 202309_backup (version number = backup for September 2023) in Table t3 can be read. The batch processing task for September can be re-executed, and the results can be written into Table t1.

[0056] The verification table, Table t2, as a non-partitioned table, stores the verification results of the previous task date, facilitating global query and statistics. There is no need to query by date partition, and all verification records can be directly read from Table t2.

[0057] The partition storage of Table t3 stores historical version data, and Table t2 records the verification results of the most recent version.

[0058] The result table, Table t1, is a Hive table; the version table, Table t3, is a Hive table, a plain text file, or a table that supports partitioned tables of database types (such as the range partitioned table in PostgreSQL); the verification table, Table t2, is a Hive table or a relational database table (such as the clustered index table in SQLServer). If the verification table, Table t2, is a relational database table, then feature sampling extraction can be performed on the result table, Table t1, to retain the verification data for the processed result data in the full volume of Table t1. Thus, the function of verifying the data for any task date in Table t1 on demand can be achieved.

[0059] In some embodiments, the verification of the data consistency between the result table t1 and the verification table t2 includes: If the result table t1 is a partitioned table, verify the data consistency between the partitioned table corresponding to the day D0 before the task date D1 in the result table t1 and the verification table t2. By comparing the historical partition (D0) of the t1 table with the full-table data of the t2 table, capture incremental changes.

[0060] If the result table t1 is a non-partitioned table, directly verify the data consistency between the result table t1 and the verification table t2. Compare the full-table data of the two tables to verify overall consistency. This differential design for the partitioned table scenario and the non-partitioned table scenario effectively adapts to the characteristics of different table types in Hive.

[0061] When the t1 table is a partitioned table, the t2 table stores the latest valid data in a non-partitioned structure to form a logical mirror in a cross-storage form. Select the D0 partition as the reference point because it represents the stable state before batch processing. By comparing with the full-table data of the t2 table, ensure the correctness of historical data processing, and be able to verify the continuity of the data processing chain, that is, the data evolution from D0 to D1 meets expectations.

[0062] When the result table t1 is a partitioned table, it is necessary to verify the data consistency of a specific partition. According to the task date D1, determine the partition corresponding to the day D0 before it (such as partition_date='D0'). Only compare the data in the D0 partition of the t1 table with the full-table data of the verification table t2. Or compare whether the number of records in the D0 partition matches the number of records in the t2 table. Or verify whether the core fields (such as amount, status) of the D0 partition are consistent with those of the t2 table. Further verify the data accuracy according to the business logic (such as the total amount, status distribution). Example: Assume the task date D1 = 2023-10-15, then verify the data consistency between the partition data with partition_date='2023-10-14' in the t1 table and the t2 table. If the number of records in the D0 partition is 1000, while the number of records in the t2 table is 980, trigger a data inconsistency alarm.

[0063] When the result table t1 is a non-partitioned table, it is necessary to directly compare the full-table data. Compare the full-table data of the t1 table with the full-table data of the t2 table. Verify whether the total number of records in the t1 table is consistent with that in the t2 table. Compare the data values of the t1 table and the t2 table field by field to ensure no differences. Analyze whether the data distributions (such as status ratio, amount range) of the t1 table and the t2 table meet expectations. Example: If the number of records in the t1 table is 5000, while the number of records in the t2 table is 5020, record the difference and trigger a repair process.

[0064] For the partition table verification of the present invention, only the data of specific partitions is verified, avoiding full table scans and reducing the consumption of computing resources. Through partition isolation, the time range of problem data can be quickly located, facilitating repair. Only the data of the previous day's partition is verified to ensure that the verification result is consistent with the latest business data. Through daily partition verification, data anomalies (such as data loss, duplication) can be detected in a timely manner. For non-partition table verification, a full table verification is performed to ensure that all data is verified and problems are not missed. Through field-level and data distribution verification, the correctness of business logic is verified. Those skilled in the art schedule verification tasks regularly through scheduling tools (such as Airflow, DolphinScheduler) to reduce manual intervention. When the verification fails, an exception process (such as data repair, resource adjustment) is automatically triggered to shorten the fault recovery time. Example: After the verification fails, the task of partition D0 is automatically rerun to repair the data difference. Through partition table verification, the present invention can isolate problem partitions and prevent the problems of a single partition from affecting the overall data accuracy. Non-partition table verification ensures the consistency of all data and prevents the accumulation of historical data problems.

[0065] For example, in the case of data warehouse ETL, batch processing tasks are frequent and the data volume is large, and it is necessary to verify the data accuracy according to partitions. Data is extracted from the business system to the data warehouse every day, and the consistency between the data of the previous day's partition and the verification table is verified. For the financial system settlement, it is necessary to strictly ensure data consistency, and failure may lead to financial losses. After the daily settlement task is completed, the data consistency between the settlement result table and the verification table is verified to ensure that the funds are correct.

[0066] In some embodiments, in response to challenges such as data volume growth, query efficiency, storage optimization, and operation and maintenance complexity in the version management scenario, the present invention sets the version management day on a weekly, monthly, or annual basis, and the partitions of the version table t3 are set as weekly, monthly, or annual partitions according to version management requirements.

[0067] The version table usually needs to be queried according to a time range (such as "versions in the last week", "all versions in a certain month"). After partitioning by week / month / year, the query engine can directly locate the target partition, avoiding a full table scan. Example: When querying "all versions in October 2023", only the 202310 partition needs to be scanned instead of the entire table. The database can scan multiple partitions in parallel, making full use of multi-core CPUs and distributed computing resources. In a concurrent scenario, the read and write operations of different partitions in a partitioned table do not interfere with each other, reducing the waiting time. Specific partitions can be backed up or restored individually instead of the entire table, reducing I / O and time costs. Partitioning by week, month, and year highly matches common version management cycles (such as weekly releases, monthly settlements, and annual archiving), reducing the complexity of developers' handling of time-related logic. Version data is usually generated incrementally by time. Partitioning by time can avoid data concentration in a single partition and reduce read and write hotspots. When the data volume in a certain partition is too large, the partition strategy can be adjusted (such as changing from monthly to weekly) for smooth expansion.

[0068] In some embodiments, in a batch task, if the execution fails or the data in the result table t1 is inconsistent with the data in the verification table t2, an exception process needs to be entered for handling. This mechanism ensures the ultimate success of the task through data repair or resource adjustment, improving system reliability and data accuracy. The method further includes: In the case where the batch task execution fails or the data in the result table t1 is inconsistent with the data in the verification table t2, enter the exception process for handling, and re-execute the task after repairing the exception; wherein, the exception process for handling includes re-executing the task after repairing the data or adjusting the resources.

[0069] The exception trigger conditions are task execution failure or data inconsistency. Task execution failure means that the batch task is interrupted due to reasons such as code errors, insufficient resources, and system failures. Example: The data warehouse Extract-Transform-Load (ETL) task fails due to a database connection timeout. Data inconsistency means that the key fields (such as the number of records, checksum) in the result table t1 do not match those in the verification table t2. Example: The number of records in t1 is 10 less than that in t2, triggering a data consistency verification failure.

[0070] In the process of data consistency verification, exception handling is a key link to ensure system stability and data accuracy. When the verification result shows inconsistency, a hierarchical response mechanism needs to be adopted: 1. Quick alarm and notification: After the verification fails, the system should immediately trigger the alarm mechanism and notify the relevant responsible persons through channels such as emails, text messages, or enterprise WeChat to ensure that the problem is perceived in the first place. The alarm content should include the reason for failure (such as record number difference, field value inconsistency), the scope of the problem data (such as a specific partition, key field), and the verification time to assist in quickly locating the problem.

[0071] 2. Problem Isolation and Impact Assessment: In case of partition table checksum failure, the problematic partition needs to be immediately isolated (e.g., freezing data writing to partition D0) to prevent the problem from spreading to subsequent partitions; for non-partition tables, it is necessary to evaluate the scope of impact on the entire table data and determine whether it is necessary to roll back or correct all data.

[0072] 3. Combination of Automated Repair and Manual Intervention: For common problems that can be reproduced (such as data latency, field format errors), the system should preset repair scripts (such as rerunning the previous day's partition tasks, filling in missing fields) and automatically execute the repair process after the alarm is triggered. For complex problems (such as data logic errors, cross-table association anomalies), data engineers need to intervene, locate the root cause through means such as log analysis and data sampling, and formulate a repair plan.

[0073] 4. Repair Verification and Closed-loop Management: After the repair is completed, it is necessary to re-execute the checksum process to verify whether the problem is solved. If the checksum passes, close the problem work order and record the repair log; if the checksum still fails, upgrade the problem level and trigger a higher-level response mechanism (such as convening a cross-departmental meeting).

[0074] 5. Continuous Monitoring and Prevention Mechanism: By analyzing historical abnormal data, identify high-frequency problem types (such as abnormal data in specific partitions, missing fields), optimize the data production process accordingly (such as adding field check rules, adjusting partition strategies), and regularly organize data quality backtracking to form a closed-loop management of checksum - repair - prevention.

[0075] Through the above hierarchical response mechanism, the automation and manual collaboration of exception handling can be realized, ensuring the efficiency and reliability of data consistency verification, and reducing the impact of data problems on the business.

[0076] When the batch processing task fails or data is inconsistent, the present invention automatically triggers an exception process and quickly recovers the task through preset repair scripts (such as rerunning the failed task, filling in missing fields) or resource adjustment (such as adding computing nodes, optimizing the SQL execution plan). For example, for tasks that fail due to network fluctuations, the system can automatically retry; for performance bottlenecks caused by data skew, the system can dynamically adjust the partition strategy, thus significantly improving the task retry success rate and reducing the need for manual intervention.

[0077] Regarding the data inconsistency problem, a data repair strategy is adopted to ensure the consistency between the result table (Table t1) and the verification table (Table t2). For example, if a missing value is found in a certain field of Table t1 during verification, it can be automatically backfilled from the original data source; if a difference is found in the aggregation results between Table t1 and Table t2, the range of problem data can be located based on log analysis and repaired accordingly. This precise repair mechanism effectively avoids the spread of data pollution and ensures data accuracy.

[0078] The abnormal process supports dynamic resource adjustment and can optimize resource allocation in real time according to the task load. For example, when a task fails due to timeout caused by a sharp increase in data volume, computing resources can be automatically expanded; when the performance of a task decreases due to resource contention, the system can give priority to ensuring the resource allocation of critical tasks. This flexible resource management strategy significantly improves the stability of batch processing tasks and reduces the failure risk caused by insufficient resources.

[0079] After repairing the anomaly, the task is automatically re-executed and a secondary verification is triggered to ensure that the problem is completely solved. If the verification passes, the task is completed in a closed loop; if the verification still fails, an escalation response mechanism (such as manual intervention, problem escalation) is entered. In addition, the system analyzes historical anomaly data to identify high-frequency problem types (such as data anomalies in specific partitions, resource bottlenecks), and optimizes the task configuration accordingly (such as adjusting partition keys, optimizing SQL logic), thereby forming a closed-loop management of anomaly detection - repair - verification - optimization to continuously improve the reliability of the system.

[0080] The above mechanism significantly reduces the operation and maintenance costs of batch processing tasks. Automated repair and resource adjustment reduce the time for manual troubleshooting and repair. At the same time, by quickly restoring tasks, the system avoids business decision-making mistakes caused by data delays and reduces the impact of data problems on the business. For example, in the financial risk control scenario, it can ensure the timely output of daily risk indicators and guarantee business continuity.

[0081] The batch processing process of the method of the present invention is as follows: Process 1: The batch processing task call starts, and the call parameters are read (execution method: standard statistics, trace statistics, repeated statistics; task date: YYYYMMDD); If it is trace statistics, the partition data corresponding to the task date D1 in Table t3 is migrated and overwritten into Table t1 and Table t2; in particular, if Table t1 being processed by the current task is a partitioned table, it is overwritten into the partitioned table in Table t1 corresponding to the task date D1; go to Process 5; If it is standard statistics, go to Process 2; If it is repeated statistics, go to Process 3; Process 2: Verify and compare the data consistency between Table t1 and Table t2; specifically, if Table t1 processed by the current task is a partitioned table, verify the data consistency between the partitioned table corresponding to the day D0 before the task date D1 in Table t1 and Table t2; If the verification is consistent, go to Process 3; otherwise, if it is inconsistent, go to Exception Process 4; Process 3: Read the original data of the tmp table corresponding to D1 and execute the batch processing task for D1; If the execution is successful, overwrite and write the processed result data to Table t1 and Table t2; specifically, if Table t1 processed by the current task is a partitioned table, overwrite and write it to the partitioned table corresponding to the task date D1 in Table t1; If D1 is the set version management day (Monday or the beginning of the month), overwrite and write the processed result data to the partitioned table corresponding to the task date D1 in Table t3; otherwise, go to Process 5; If the execution fails, go to Exception Process 4; Process 4: Exception process handling. After repairing the data according to the exception information, or performing technical interventions such as adjusting resources, go back to Process 1 to reprocess the D1 task; for example, when the data verification between Table t1 and Table t2 is inconsistent, specific problem analysis is required. After the technical intervention makes it consistent, continue to reprocess; Process 5: The D1 batch processing task ends.

[0082] The verification table of the present invention only saves the previous batch of processed data once, achieving minimized data management. The version table is flexibly set for storage according to business needs (weekly, monthly, or annually), and the storage period can be set as required. When supporting data batch processing, the data can be traced back to any point in time as needed, and the data can also be reprocessed as needed. It is applicable to both Hive partitioned tables and non-partitioned tables, and the process is unified.

[0083] Next, the Hive table repeatable and traceable data processing device provided by the present invention will be described. The Hive table repeatable and traceable data processing device described below can be correspondingly referred to the Hive table repeatable and traceable data processing method described above.

[0084] The Hive table repeatable and traceable data processing device provided by the present invention, as shown in Figure 2 includes: Table setting module 210, used to set the source table, result table, verification table, and version table of the batch processing task. Among them, the result table is a Hive table, and the structures of the verification table and the version table are the same as the structure of the result table; the source table stores the original data to be processed; A parameter reading module 220, configured to read call parameters at the start of a batch task call; wherein, the call parameters include an execution mode and a task date, and the execution mode includes standard statistics, traceability statistics, and repeated statistics; A traceability processing module 230, configured to, when the execution mode is traceability statistics, migrate and overwrite the partition data corresponding to the task date in the version table to the result table and the verification table; A repeated processing module 240, configured to, when the execution mode is repeated statistics, directly read the original data corresponding to the task date from the source table and execute the batch task corresponding to the task date; A standard processing module 250, configured to, when the execution mode is standard statistics, verify and compare the data consistency between the result table and the verification table. If they are consistent, read the original data corresponding to the task date from the source table and execute the batch task corresponding to the task date; A task execution module 260, configured to, when the batch task is executed successfully, obtain the corresponding processed result data, overwrite and write the processed result data to the result table and the verification table, and when the task date is the version management date, overwrite and write the processed result data to the partition table corresponding to the task date in the version table.

[0085] Figure 3 An entity structure diagram of an electronic device is exemplified, as Figure 3 shown, the electronic device may include: a processor 310, a communication interface 320, a memory 330, and a communication bus 340. Among them, the processor 310, the communication interface 320, and the memory 330 complete mutual communication through the communication bus 340. The processor 310 may call the logical instructions in the memory 330 to execute the Hive table repeatable and traceable data processing method.

[0086] In addition, when the logical instructions in the above-mentioned memory 330 are implemented in the form of software functional units and sold or used as independent products, they can be stored in a computer-readable storage medium. Based on such an understanding, the technical solution of the present invention, in essence, or the part that contributes to the prior art, or a part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions for causing a computer device (which can be a personal computer, a server, or a network device, etc.) to execute all or part of the steps of the methods described in various embodiments of the present invention. The aforementioned storage medium includes: various media such as USB flash drives, mobile hard disks, read-only memories (ROM, Read-Only Memory), random access memories (RAM, Random Access Memory), magnetic disks, or optical discs that can store program codes.

[0087] On the other hand, the present invention also provides a computer program product. The computer program product includes a computer program that can be stored on a non-transitory computer-readable storage medium. When the computer program is executed by a processor, the computer can execute the repeatable and traceable data processing method for Hive tables provided by the above-mentioned various methods.

[0088] On another aspect, the present invention also provides a non-transitory computer-readable storage medium, on which a computer program is stored. When the computer program is executed by a processor, it is implemented to execute the repeatable and traceable data processing method for Hive tables provided by the above-mentioned various methods.

[0089] The device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed to multiple network units. Some or all of the modules can be selected according to actual needs to achieve the purpose of the solution of this embodiment. Those of ordinary skill in the art can understand and implement it without creative labor.

[0090] Through the description of the above embodiments, those skilled in the art can clearly understand that each embodiment can be implemented by means of software plus a necessary general hardware platform, and of course, it can also be implemented by hardware. Based on such an understanding, the technical solution, in essence, or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, magnetic disks, optical discs, etc., and includes several instructions for causing a computer device (which can be a personal computer, a server, or a network device, etc.) to execute the methods described in various embodiments or some parts of the embodiments.

[0091] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than limiting it; although the present invention has been described in detail with reference to the foregoing embodiments, those of ordinary skill in the art should understand that they can still modify the technical solutions described in the foregoing embodiments, or perform equivalent replacements on some of the technical features; and these modifications or replacements do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of the present invention.

Claims

1. A method for processing repeatable and traceable data of a Hive table, characterized in that, Including: Set the source table, result table, verification table, and version table for the batch processing task. Among them, the result table is a Hive table, and the structures of the verification table and the version table are the same as the structure of the result table; the source table stores the raw data to be processed; When the batch processing task is called, read the call parameters; among them, the call parameters include the execution method and the task date, and the execution method includes standard statistics, traceability statistics, and duplicate statistics; When the execution method is traceability statistics, migrate and overwrite the partition data corresponding to the task date in the version table into the result table and the verification table; When the execution method is duplicate statistics, directly read the raw data corresponding to the task date from the source table and execute the batch processing task corresponding to the task date; When the execution method is standard statistics, verify and compare the data consistency between the result table and the verification table. If they are consistent, read the raw data corresponding to the task date from the source table and execute the batch processing task corresponding to the task date; In the case where the batch processing task is executed successfully, obtain the corresponding processing result data, overwrite and write the processing result data into the result table and the verification table, and overwrite and write the processing result data into the partition table corresponding to the task date in the version table when the task date is the version management date.

2. The method for processing repeatable and traceable data of the Hive table according to claim 1, wherein The result table stores the full amount of processing result data, with a data volume of n; the verification table only retains the processing result data of the previous processing, with a data volume of 1; the version table stores the processing result data sampled and archived as needed, with a data volume of m, and satisfies 1 < m < n.

3. The method for processing repeatable and traceable data of the Hive table according to claim 1, wherein, The result table is a partitioned table or a non-partitioned table, the verification table is a non-partitioned table, and the version table is a partitioned table.

4. The method for processing repeatable and traceable data of the Hive table according to claim 3, wherein The verification and comparison of the data consistency between the result table and the verification table includes: If the result table is a partitioned table, verify the data consistency between the partitioned table corresponding to the previous day of the task date in the result table and the verification table; If the result table is a non-partitioned table, directly verify the data consistency between the result table and the verification table.

5. The method for processing repeatable and traceable data of the Hive table according to claim 1, characterized in that, The version management date is set by week, month, or year, and the partitions of the version table are set to be partitioned by week, month, or year according to the version management requirements.

6. The method for processing repeatable and traceable data of the Hive table according to claim 1, wherein The method further includes: In the case where the batch processing task fails or the data in the result table is inconsistent with the data in the verification table, enter the exception process for processing, and re-execute the task after repairing the exception; among them, the exception process for processing includes re-executing the task after repairing the data or adjusting the resources.

7. A repeatable and traceable data processing device for Hive tables, characterized in that, Including: A table setting module for setting the source table, result table, verification table, and version table of the batch processing task. Among them, the result table is a Hive table, and the structures of the verification table and the version table are the same as the structure of the result table; the source table stores the raw data to be processed; A parameter reading module for reading the call parameters when the batch processing task is called; among them, the call parameters include the execution method and the task date, and the execution method includes standard statistics, traceability statistics, and duplicate statistics; A trace processing module, configured to, when the execution mode is trace statistics, migrate and overwrite the partition data corresponding to the task date in the version table to the result table and the verification table; A repeated processing module, configured to, when the execution mode is repeated statistics, directly read the original data corresponding to the task date from the source table and execute the batch processing task corresponding to the task date; A standard processing module, configured to, when the execution mode is standard statistics, verify and compare the data consistency between the result table and the verification table. If they are consistent, read the original data corresponding to the task date from the source table and execute the batch processing task corresponding to the task date; A task execution module, configured to, when the batch processing task is successfully executed, obtain the corresponding processing result data, overwrite and write the processing result data to the result table and the verification table, and overwrite and write the processing result data to the partition table corresponding to the task date in the version table when the task date is the version management date.

8. An electronic device, comprising a memory, a processor, and a computer program stored on the memory and running on the processor, characterized in that, When the processor executes the computer program, it implements the Hive table repeatable and traceable data processing method according to any one of claims 1 to 6.

9. A non-transitory computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by the processor, it implements the Hive table repeatable and traceable data processing 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 the processor, it implements the Hive table repeatable and traceable data processing method according to any one of claims 1 to 6.

Images