Data warehouse batch job parallel processing method and system

Abstract

This invention provides a method and system for parallel processing of batch jobs in a data warehouse, comprising: after each processing job ETL on the first business date, adding a corresponding slice job SLC to form a daily slice table; and directly inserting the data generated by the processing job ETL on the second business date into the daily slice table with the corresponding date suffix, thus completing the daily slice for each job. This invention can ensure the parallel processing of large-scale batch jobs over multiple days, effectively improving the stability and timeliness of data services, and avoiding data space redundancy.

Description

Technical Field

[0001] This invention relates to the field of data processing technology, specifically to a method and system solution for parallel processing of batch jobs in data warehouses. In particular, it provides a technical solution for solving the problem of parallel processing of large-scale batch jobs over multiple days in data warehouses. Background Technology

[0002] In mainstream commercial banking, data warehouses are crucial large-scale data processing systems that provide parallel processing capabilities for massive amounts of data. Interaction with an enterprise-level data warehouse involves unloading data files from business systems and transferring them to the data warehouse. From receiving data and loading it into the warehouse to completing data processing and transmission, the overall workload of large-scale batch operations in a data warehouse ranges from 5,000-6,000 to over 20,000, with high dependency complexity between operations and hundreds of coupling relationships within a single operation.

[0003] Patent document CN111369333A provides a distributed system for daily switching collaborative processing, including: receiving daily switching completion notification from the host server through a general gateway, forming daily switching control information for the platform server and transmitting it to the platform daily switching controller; receiving the daily switching control information from the platform server through the platform daily switching controller, forming a daily switching instruction for the platform server and transmitting it to the distributed collaborative server; and transmitting the daily switching instruction from the platform server to each platform server through the distributed collaborative server so that each platform server completes the daily switching.

[0004] However, the industry standard currently employs a daily job switching solution, where all jobs are completed on the current business day and the process switches to the next business day. Because the job system involves multiple source systems, it's common for some jobs to be abnormal due to late arrival of data from a single source or problematic data. Such situations can lead to a large number of jobs being delayed or unable to run the following day, consequently causing the entire system to be unable to provide data services on time or normally. Summary of the Invention

[0005] To address the shortcomings of existing technologies, the purpose of this invention is to provide a method and system for parallel processing of batch jobs in data warehouse applications.

[0006] A data warehouse batch job parallel processing method provided by the present invention includes:

[0007] After each processing job ETL on the first business day, add a corresponding slice job SLC to form a daily slice table;

[0008] The data generated by the ETL process on the second business day is directly inserted into the daily cut table with the corresponding date suffix, thus completing the daily cut for each job.

[0009] Preferably, the SLC slicing operation refers to copying a table with the same table structure as the previous day's ETL processing operation result table, and using the data date as a suffix to form a daily slicing table. At the same time, it is determined whether to retain the data based on the data processing algorithm corresponding to the ETL processing operation.

[0010] Preferably, the ETL processing job depends on the SLC slicing job corresponding to this job, and the ETL processing job for the next business day requires the SLC slicing job for the previous business day to be completed.

[0011] Preferably, each FLD loading job depends on its own CHK check job; each ETL processing job depends on one or more FLD loading jobs or multiple other ETL processing jobs; the ETL processing job includes the model and intermediate layers; each SLC slicing job depends on its own ETL processing job to generate the result table for the next business date; each EXP export job depends on at least one FLD loading job or ETL processing job; each FTP upload job depends on one EXP export job; ETL processing jobs and EXP export jobs can have a one-to-many relationship, that is, the completion of an ETL processing job or EXP export job must first complete all of its dependent prerequisite jobs.

[0012] Preferably, daily slicing tables are set for data tables with different data processing algorithms in the data warehouse.

[0013] Preferably, the data processing algorithms for the data warehouse include:

[0014] Data processing algorithm I: Accepts incremental data, appends records with selected fields to the end of the data table, and includes an audit field;

[0015] Data processing algorithm F1: Each time a job runs, all data in the current data table is deleted, and then the calculation result of the current business date is retained without an audit field;

[0016] Data processing algorithm F2: Accepts incremental data and full data, and updates records in the data table based on the primary key;

[0017] Data processing algorithms F3 and F5 are divided into full historical chaining algorithm and ordinary historical chaining algorithm. Based on the primary key, when there is an update to the data other than the primary key, the end date of the original record is updated to the day before the current business date. Then, a new record is added with the start date set to the current business date and the status value of the corresponding field is recorded. The end date field is set to the special date DATE_X that represents the chain not being closed. Historical data is saved and has an audit field.

[0018] Data processing algorithm Custom: Keep data within a certain time period and delete data outside that time period.

[0019] Preferably, the daily cutoff table corresponding to data processing algorithm I includes a date suffix and retains historical data;

[0020] The daily cutoff table corresponding to data processing algorithm F1 includes a date suffix and does not retain historical data.

[0021] The daily cutoff table corresponding to data processing algorithm F2 includes a date suffix and retains historical data;

[0022] The daily slicing tables corresponding to data processing algorithms F3 and F5 both include a date suffix to retain historical data.

[0023] The data processing algorithm Custom corresponds to a daily cutoff table with a date suffix, retaining historical data.

[0024] Preferably, different algorithms employ different data storage methods, establishing storage spaces and corresponding storage structures for data of different frequencies. Data from the previous year is designated as cold data; cold data is offline and stored in a cold database using row compression; data from the current year is designated as hot data; for hot data, only processing jobs ETL operations that use more data than the set data volume are subject to reverse dependency, and only the reverse dependency of the first-level processing job ETL is required, and daily table slicing is set for the first-level tables.

[0025] According to the present invention, a computer-readable storage medium storing a computer program is provided, wherein when the computer program is executed by a processor, the steps of the data warehouse-type batch job parallel processing method are implemented.

[0026] An electronic device system according to the present invention includes a memory, a processor, and a computer program stored in the memory and executable on the processor. When the computer program is executed by the processor, it implements the steps of the data warehouse-type batch job parallel processing method.

[0027] Compared with the prior art, the present invention has the following beneficial effects:

[0028] 1. This invention solves the predicament of untimely data service provision and problematic data caused by daily data slicing in existing technologies. The solution provided by this invention includes: setting daily slicing tables for data tables of different algorithms; establishing storage spaces and corresponding storage structures for data of different frequencies; and configuring daily slicing job dependencies to simultaneously ensure the normal operation of both daily and non-daily slicing jobs. Thus, this invention can ensure large-scale batch operations run concurrently over multiple days, effectively improving the stability and timeliness of data services and avoiding data space redundancy.

[0029] 2. In traditional data warehouse batch data processing, jobs are interdependent and mutually inversely dependent, resulting in very high coupling. An anomaly or delay in any link can reduce or disrupt the overall data warehouse's service capabilities. This invention addresses this high coupling by reshaping the data warehouse's operational scheme and eliminating inverse dependencies. This allows the system to move beyond daily cutoffs and support multi-day parallel batch data processing, preventing the overall system data service from being affected by delays or anomalies in a single upstream data supply. However, if a single upstream data supply delay or anomaly cannot be resolved the following day, the inverse dependency mechanism causes the impact to spread daily, affecting the overall system data service after several days. Therefore, this invention further proposes a daily cutoff job scheme, resolving the defect of the daily spread of impact caused by the inverse dependency mechanism. This ensures that enterprise-level data integration systems like data warehouses can still achieve large-scale batch operations in parallel over multiple days, even when a stable data supply is unavailable due to a large number of upstream systems, guaranteeing the continuous provision of system data services.

[0030] 3. In batch data processing in data warehouses, processing algorithms are diverse and data storage methods are complex. This invention summarizes algorithms suitable for daily data warehouse processing and designs corresponding slicing schemes for these algorithms. Based on these slicing schemes, all jobs and tables can be completed in parallel and daily slicing within the same job dependency environment. However, daily slicing of all tables leads to a sharp increase in stored data, with a large amount of data consuming high-availability space in the heap and increasing the probability of node crashes. To address this, this invention further proposes cold and hot data partitioning. Cold data older than a set period, such as two years ago, is compressed to reduce database storage pressure, while hot data is partitioned using rolling partitions to improve the efficiency of database queries and job processing. Attached Figure Description

[0031] Other features, objects, and advantages of the present invention will become more apparent from the following detailed description of non-limiting embodiments with reference to the accompanying drawings:

[0032] Figure 1 This is a diagram illustrating the daily slicing relationship of parallel data warehouse operations (not multi-day parallelism).

[0033] Figure 2 This is a diagram illustrating the dependencies in a parallel data warehouse (not multi-day parallelism).

[0034] Figure 3 This is a diagram illustrating the daily slicing relationship of parallel data warehouse operations (multi-day parallelism).

[0035] Figure 4 This is a diagram illustrating the dependencies in a parallel data warehouse (multi-day parallelism). Detailed Implementation

[0036] The present invention will now be described in detail with reference to specific embodiments. These embodiments will help those skilled in the art to further understand the present invention, but do not limit the invention in any way. It should be noted that those skilled in the art can make several changes and improvements without departing from the concept of the present invention. These all fall within the protection scope of the present invention.

[0037] To address the high coupling between multiple jobs and unpredictable anomalies in source data in existing technologies, and to improve the timeliness and stability of data services in the banking sector, it is necessary to propose a solution that supports large-scale parallel operations over multiple days. This invention achieves decoupling of dependencies between jobs by reshaping the job dependency scheme. A scheme for slicing each table in the database is designed based on the job processing algorithm, thereby supporting independent job execution. Delayed upstream data or problematic data caused by upstream system anomalies only affects the jobs actually used in the current batch, without affecting the overall system's jobs and data. This enables parallel multi-day operation of jobs, maximizing the stability and timeliness of data services.

[0038] In the industry, widely used parallel computing data warehouses typically consist of a check job (CHK), a load job (FLD), a process job (ETL), an export job (EXP), and an upload job (FTP). The specific steps are as follows: The data warehouse receives data files unloaded from multiple source systems. A file dependency check job (CHK) compares the latest data format with the agreed-upon data format. After ensuring data consistency, the data is loaded into the database through the load job (FLD), forming an upstream table with a date suffix (the table name is appended with a date). Subsequently, based on job dependencies, the corresponding process job (ETL) script is executed once all prerequisite conditions are met. This type of ETL script can update and record data for different types of data processing jobs. This type of job generates and saves data tables without date suffixes to the database. Finally, the export job (EXP) and the upload job (FTP) complete the data export and transfer to the file exchange system.

[0039] In this context, job dependencies typically involve each FLD loading job depending on its own CHK check job, each ETL job depending on one or more FLD loading jobs or multiple other ETL jobs, each EXP export job depending on at least one FLD loading or ETL job, and each FTP upload job depending on one EXP export job. There may be a one-to-many relationship between ETL and EXP export jobs, meaning that the completion of an ETL processing job or EXP export job requires the completion of all its dependent prerequisite jobs. For examples of parallel processing of such job dependencies, please refer to [reference needed]. Figure 1 A diagram illustrating the dependencies between parallel data warehouse jobs.

[0040] Based on this, to accommodate multi-day parallel operations, all subsequent jobs of each ETL are inversely dependent on this ETL, ensuring that data is processed in the correct date order. That is, an ETL or EXP export job must wait for all its subsequent jobs from the previous day to complete before it can run. See the reference for such inverse dependencies. Figure 2 A diagram illustrating the reverse dependency relationships in a parallel data warehouse.

[0041] The present invention will now be described.

[0042] The parallel daily slicing scheme involves adding a corresponding slice job (SLC) after each ETL (Extract, Transform, Load) process. A slice job copies a table with the same structure as the previous day's (T-1) ETL result table (e.g., A1_20221018), using the data date as a suffix (e.g., A1_20221019). The algorithm corresponding to the ETL process determines whether to retain data (except for the full delete / insert algorithm, all other algorithms retain data). Data generated by the ETL process on the second business day is directly inserted into the slice table with the corresponding date suffix, thus completing the daily slicing for each job. Therefore, each job does not need to wait for the previous day's job to complete. When the preconditions are met, the corresponding downstream job can complete. This type of job can be referenced. Figure 3 A schematic diagram of the daily cutting relationship of parallel data warehouse operations.

[0043] In terms of dependency configuration, the ETL processing job depends on the corresponding slice job (SLC). The ETL processing job for the next business date requires the slice job (SLC) of the previous day to be completed (after the table for the next day is created). Job dependencies can be found in [reference needed]. Figure 4 A diagram illustrating the dependencies in a parallel data warehouse is shown below:

[0044] 1. Each loading job (FLD) depends on its own check job (CHK);

[0045] 2. Each processing job ETL (including model and intermediate layer) depends on one or more loading jobs FLD loading or multiple other processing job ETLs;

[0046] 3. Each slice job (SLC) depends on its own processing job (ETL processing) or model / intermediate layer job, with the aim of generating a result table for the next business date;

[0047] 4. Each export job (EXP) depends on at least one loading job (FLD) or processing job (ETL).

[0048] 5. Each upload job FTP depends on an export job EXP;

[0049] 6. Among them, the processing job ETL and the export job EXP will have a one-to-many relationship, that is, the completion of a processing job ETL or export job EXP must first complete all the multiple preceding jobs that it depends on.

[0050] The present invention will now be described in more detail.

[0051] Data processing algorithm I: Accepts incremental data, appends records with selected fields to the end of the data table, and includes an audit field.

[0052] Data processing algorithm F1: Each time a job runs, all data in the current data table is deleted, and then the calculation result of the current business date is retained without the audit field.

[0053] Data processing algorithm F2: Accepts incremental / full data and updates records in a data table based on the primary key. This type of data processing job features storing historical data and retaining its latest state except for the primary key, and includes an audit field.

[0054] Data processing algorithms F3 / F5: Full historical chaining and ordinary historical chaining algorithms, suitable for slowly changing dimensions. Based on the primary key, when data other than the primary key is updated, the end_date of the original record is updated to the day before the current business date. Subsequently, a new record is added with the start_date set to the current business date, and the status value of the corresponding field is recorded. The END_DATE field is set to a special date DATE_X (representing that the chain is not closed). This algorithm saves historical data and includes an audit field.

[0055] Data processing algorithm Custom: Retains data within a certain time period and deletes data outside of that period. Common examples include retaining month-end data from the past two years and data from the past three days.

[0056] The audit fields mentioned above include: Source_Table, First_Etl_Date, ETL_Tx_Date, and Last_Update_Time. The algorithm described above can be summarized as follows:

[0057] Data processing algorithms I F1 F2 F3 / F5 Custom Should historical data be retained? yes no yes yes yes Does it have an audit field? yes no yes yes yes Should cold data be archived? 2 years ago no 2 years ago no 2 years ago Should cold data be compressed? yes no yes no yes

[0058] The present invention will now be described in further detail.

[0059] Slicing operations (SLC) are mainly divided into the following two categories: Empty table: Creates a table for the next business date based on the table for the current business date. Copy table: Creates a table for the next business date based on the table for the current business date, and copies the contents of the current table to the table for the first business date.

[0060] Daily slicing tables for different data processing algorithms:

[0061]

[0062]

[0063] Cold and hot data storage. Based on statistical analysis of the scripts, the data processing algorithms used in the data warehouse generally do not involve data older than two years. Data older than two years is taken offline and stored in a cold database using row compression. For tables with large amounts of data in the hot data, a reverse dependency approach is adopted. That is, reverse dependencies are only applied to processing jobs ETL using tables with more than 300GB of data, and only the reverse dependencies of the first-level processing jobs ETL are required. Daily table splitting is also set for the first-level tables.

[0064] In time-sensitive interest rate reporting and regulatory submission scenarios, data warehouses need to meet T+3 data processing requirements, involving hundreds of upstream data files and complex processing logic, typically requiring nested multi-level processing ETL jobs. In the original job dependency relationships, all nested processing ETL jobs had reverse dependencies. In one instance, a 3-day delay in an upstream data file prevented a certain ETL job from completing, all reverse dependencies failed, and data processing within those 3 days could not be completed, ultimately leading to reporting delays. After using the innovative solution of this invention, time-sensitive interest rate reporting data processing is no longer affected by a single data file anomaly; a single data file only affects the corresponding processing ETL job. The batch data previously required for T+3 regulatory submissions can now generally be submitted within T+1, improving overall data service speed by 60%.

[0065] According to the present invention, a computer-readable storage medium storing a computer program is provided, wherein when the computer program is executed by a processor, the steps of the data warehouse-type batch job parallel processing method are implemented.

[0066] An electronic device system according to the present invention includes a memory, a processor, and a computer program stored in the memory and executable on the processor. When the computer program is executed by the processor, it implements the steps of the data warehouse-type batch job parallel processing method.

[0067] Specific embodiments of the present invention have been described above. It should be understood that the present invention is not limited to the specific embodiments described above, and those skilled in the art can make various changes or modifications within the scope of the claims, which do not affect the essence of the present invention. Unless otherwise specified, the embodiments and features described in this application can be arbitrarily combined with each other.

Claims

1. A method for parallel processing of batch jobs in a data warehouse, characterized in that, include: After each processing job ETL on the first business day, add a corresponding slice job SLC to form a daily slice table; The data generated by the ETL of the processing operation on the second business date is directly inserted into the daily cut table with the corresponding date suffix to complete the daily cut for each operation; The Slicing Operation (SLC) refers to copying a table with the same structure as the previous day's ETL processing operation result table, and using the data date as a suffix to form a daily slicing table. At the same time, it is determined whether to retain the data based on the data processing algorithm corresponding to the ETL processing operation. ETL processing jobs depend on the slice job SLC corresponding to this job. The ETL processing job for the next business day needs to be completed by the slice job SLC of the previous business day. Each FLD loading job depends on its own CHK check job; each ETL processing job depends on one or more FLD loading jobs or multiple other ETL processing jobs; the ETL processing jobs include models and intermediate layers; each slice job (SLC) depends on its own ETL processing job to generate a result table for the next business date; each EXP export job depends on at least one FLD loading job or ETL processing job; each FTP upload job depends on one EXP export job; ETL processing jobs and EXP export jobs can have a one-to-many relationship, that is, the completion of an ETL processing job or EXP export job must first complete all of its dependent prerequisite jobs.

2. The data warehouse batch job parallel processing method according to claim 1, characterized in that, Set up daily table splitting for data tables using different data processing algorithms in the data warehouse.

3. The data warehouse batch job parallel processing method according to claim 2, characterized in that, Data warehouse data processing algorithms include: Data processing algorithm I: Accepts incremental data, appends records with selected fields to the end of the data table, and includes an audit field; Data processing algorithm F1: Each time a job runs, all data in the current data table is deleted, and then the calculation result of the current business date is retained without an audit field; Data processing algorithm F2: Accepts incremental data and full data, and updates records in the data table based on the primary key; Data processing algorithms F3 and F5 are divided into full historical chaining algorithm and ordinary historical chaining algorithm. Based on the primary key, when there is an update to the data other than the primary key, the end date of the original record is updated to the day before the current business date. Then, a new record is added with the start date set to the current business date and the status value of the corresponding field is recorded. The end date field is set to the special date DATE_X that represents the chain not being closed. Historical data is saved and has an audit field. Data processing algorithm Custom: Keep data within a certain time period and delete data outside that time period.

4. The data warehouse batch job parallel processing method according to claim 3, characterized in that, Data processing algorithm I corresponds to a daily cutoff table with a date suffix, retaining historical data; The daily cutoff table corresponding to data processing algorithm F1 includes a date suffix and does not retain historical data. The daily cutoff table corresponding to data processing algorithm F2 includes a date suffix and retains historical data; The daily slicing tables corresponding to data processing algorithms F3 and F5 both include a date suffix to retain historical data. The data processing algorithm Custom corresponds to a daily cutoff table with a date suffix, retaining historical data.

5. The data warehouse batch job parallel processing method according to claim 1, characterized in that, Different algorithms use different data storage methods: Establish storage space and corresponding storage structure for data of different frequencies, and set data from the previous year as cold data; cold data is offline and stored in the cold database with row compression; set data from the current year as hot data; for hot data, only the processing ETL of tables whose data volume exceeds the set data volume is reversed, and only the reverse dependency of the first-level processing ETL is required, and daily table splitting is set for the first-level tables.

6. A computer-readable storage medium storing a computer program, characterized in that, When the computer program is executed by the processor, it implements the steps of the data warehouse type batch job parallel processing method as described in any one of claims 1 to 5.

7. An electronic device system, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the computer program is executed by the processor, it implements the steps of the data warehouse type batch job parallel processing method as described in any one of claims 1 to 5.

Images