Parallel load operations with unified post-processing of ETLs

Abstract

The present disclosure relates to computer-implemented systems and methods that perform parallel load operations during extract, transform, load (ETL) operations. The system generates a single transaction with multiple processes, where each process loads into one target table. The processes are executed in parallel, and the system performs validation on the single transaction.

Description

TECHNICAL FIELD

[0001] The present disclosure relates to database systems, and more particularly to extract, transform, load (ETL) processes in analytical database systems. BACKGROUND

[0002] Unless otherwise indicated herein, the methods described in this section are not prior art to the claims in this application and are not admitted to be prior art by inclusion in this section.

[0003] There are various types of database systems, including online transaction processing (OLTP) systems, online analytic processing (OLAP) systems, and the like. OLTP systems can be used to manage transactional data, including insert operations, update operations, and delete operations on the transactional data. OLAP systems can be used for analysis of the transactional data; as such, OLAP systems can perform read operations on a reduced set of transactional data. For example, an OLTP system can handle transactional data for purchase orders, including purchase order identifiers, dates, product identifiers, purchaser identifiers, prices, discounts, and the like; an OLAP system can handle aggregations of the purchase order data, such as aggregations by date (e.g., within a given month), by product type, by purchaser, and the like.

[0004] OLAP systems typically use extract, transform, load (ETL) processes to import transactional data managed by OLTP systems. Extract generally refers to the selection of a subset of the transactional data. Transform generally refers to the application of a set of rules or functions to the extracted data to conform to the expected input of the OLAP system. Load generally refers to the process of providing the transformed data from the OLTP system to the OLAP system.

[0005] SUMMARY

[0006] In view of the foregoing, several problems are presented. One problem with existing systems is that customers often want to perform analytics on transactions from multiple heterogeneous OLTP systems (or OLTP systems that store transactional data using heterogeneous cloud storage systems). Each OLTP system or each cloud storage system typically involves its own ETL operations. In this case, it is cumbersome to coordinate and verify success of each ETL operation. For example, when data needs to be loaded to a cloud database and post-processing needs to be performed after loading, many existing systems use separate processes for each table (e.g., load data and post-process each table as a table-specific transaction). This results in lack of transaction control when different types of data need to be loaded and post-processed as one transaction. Attempting to load / post-process data in multiple tables using the same process and single transaction results in poor system performance due to serial execution.

[0007] In this case, there is a need to improve ETL processes.

[0008] As described further herein, embodiments aim to perform ETL from multiple heterogeneous OLTP systems. Generally, embodiments create an overall ETL process as a single transaction and spawn multiple operating system (OS) processes within the single transaction. Each OS process corresponds to an ETL operation with a single target table. The overall ETL process can execute the OS processes in parallel. Once the operating system processes are complete, the overall ETL process will verify success of each OS process.

[0009] In one embodiment, a method of performing data processing. The method includes generating, by an ETL system, a single target system transaction having a single transaction identifier as part of performing an ETL operation. The method also includes generating, by the ETL system, a single target system transaction having the single transaction identifier as part of performing the ETL operation. The method further includes generating, by the ETL system, a plurality of operating system (OS) processes, wherein the plurality of OS processes correspond to a plurality of load operations of the ETL operation, wherein each load operation of the plurality of load operations is associated with a target table of a plurality of target tables. The method also includes executing, by the ETL system, the plurality of OS processes in parallel, wherein each OS process of the plurality of OS processes is associated with the single transaction identifier, including transmitting, by the ETL system, configuration information for each load operation of the plurality of load operations to an OLAP system. The method further includes loading, by the OLAP system, each target table of the plurality of target tables according to the configuration information for each load operation of the plurality of load operations as part of the single target system transaction in the OLAP system. The method also includes transmitting, by the OLAP system, a plurality of results of loading the plurality of target tables to the ETL system. The method further includes performing, by the ETL system, a verification of the single target system transaction using the plurality of results from the OLAP system.

[0010] Multiple tables can be stored by multiple heterogeneous cloud data storage systems. The ETL system can use multiple remote system calls to transmit configuration information to the OLAP system, wherein each remote system call of the multiple remote system calls corresponds to one OS process of the multiple OS processes, one load operation corresponding to the multiple load operations, and one target table corresponding to the multiple target tables. The ETL system can divide the load operations into multiple batches. The ETL system can perform validation at the transaction level or the data flow level. The transaction can be implemented as multiple data flows, at least some of which can be executed in parallel and at least some of which can be executed sequentially. The OLAP system may include an in-memory database system.

[0011] The computer-readable medium may store a computer program for controlling a computer to implement one or more steps of the above-described method.

[0012] The system may implement one or more steps of the above-described method using one or more computer systems (eg, server computers, database systems, client computers, etc.) to perform one or more of the above-described method steps.

[0013] The following detailed description and accompanying drawings provide a better understanding of the nature and advantages of the present disclosure. BRIEF DESCRIPTION OF THE DRAWINGS

[0014] Figure 1 is a block diagram of database environment 100 .

[0015] Figure 2 is a block diagram showing additional details of database environment 100 .

[0016] Figure 3 is a flow chart of a method 300 of data processing.

[0017] Figure 4 is a table 400 illustrating six different post-processing scenarios according to an embodiment.

[0018] Figure 5 is a block diagram of an example computer system 500 for implementing the various embodiments described above.

[0019] Figure 6 is a block diagram of a cloud computing system 600 for implementing the various embodiments described above.

[0020] Figure 7 is a block diagram illustrating an example of a task 700 that performs three data flows 702 , 704 , and 706 . DETAILED DESCRIPTION

[0021] Techniques for parallel load operations are described herein. In the following description, for the purposes of explanation, numerous examples and specific details are set forth in order to provide a thorough understanding of the systems and methods described herein. However, it will be apparent to one skilled in the art that the disclosure defined by the claims can include some or all features of the examples alone or in combination with other features described below, and can further include modifications and equivalents of the features and concepts described herein.

[0022] The present application details various methods, processes, and procedures. Although specific steps can be described in a particular order, such order is mainly for convenience and clarity. Specific steps can be repeated more than once, can occur before or after other steps (even if those steps are otherwise described in another order), and can occur in parallel with other steps. Only when a first step must be completed before a second step can begin is the second step required to follow the first step. Such situations will be specifically noted when the context is unclear.

[0023] In this application, the terms “and”, “or”, and “and / or” are used. These terms are to be interpreted in an inclusive sense as though these terms are followed by the term “at least”. For example, “A and B” can mean at least the following: “both A and B”, “at least A and at least B”. As another example, “A or B” can mean at least the following: “at least A”, “at least B”, “both A and B”, “at least both A and B”. As another example, “A and / or B” can mean at least the following: “A and B”, “A or B”. If it is intended to use the exclusive or, it will be specifically noted (e.g., “A or B”, “at most one of A and B”).

[0024] In this application, the term “server” is used. Generally, a server is a hardware device, and the descriptor “hardware” can be omitted when discussing a hardware server. A server can implement or execute a computer program that controls server functionality. Such a computer program can also be referred to functionally as a server, or described as implementing server functionality; however, it will be understood that a computer program that implements server functionality or controls a hardware server is more accurately referred to as a “software server”, “server component”, or “server computer program”.

[0025] In this application, the term "database" is used. Generally, a database is a data structure that can facilitate the organization, storage, and retrieval of large amounts of data. A database can also be referred to as a data store. The term database is often used to refer to a relational database, where data is stored in tables and relationships between data are stored in tables as well. A database management system (DBMS) generally refers to a hardware computer system (e.g., persistent memory such as disk drives or flash drives, volatile memory such as random access memory, processors, etc.) that implements a database.

[0026] In this application, the terms "storing," "stored," and "store" are used. Generally, these terms can be used to refer to an active verb (e.g., a storing process, or a change from an un-stored state to a stored state), or to a state of existence (e.g., a stored state), or both. For example, "storing a data record" can be used to describe a storing process (e.g., a data record transitioning from an un-stored state to a stored state). As another example, "storing a data record" can be used to describe a current state of a data record (e.g., a data record currently exists in a stored state as a result of a prior storing). When only a single interpretation makes sense, such meaning will be apparent from the context.

[0027] Figure 1 is a block diagram of a database environment 100. The database environment 100 illustrates a typical arrangement of a computer system that implements embodiments described herein. An example of the database environment 100 is the SAP Cloud Platform from SAP SE. The database environment 100 includes an online transaction processing (OLTP) system 102, an online analytical processing (OLAP) system 104, a plurality of cloud storage systems 106 (two are shown, 106a and 106b), and a network 108.

[0028] The OLTP system 102 generally processes transactional data. Examples of OLTP systems include order entry systems, retail sales systems, financial transaction systems, and the like. OLTP processing generally involves collecting input information, processing data, and updating existing data to reflect the collected and processed information. The OLTP system 102 can be implemented by a database system, such as a relational database system. The database system can be a row-oriented database system, a column-oriented database system, and the like. An example of the OLTP system 102 is the SAP S / 4HANA TM Enterprise Resource Planning (ERP) system.

[0029] OLAP systems 104 typically process analytical data. Typical applications of OLAP systems include sales business reporting, marketing, management reporting, business process management (BPM), budgeting and forecasting, financial reporting, and the like. OLAP includes three basic analytical operations: roll-up, drill-down, and slicing and dicing. Roll-up involves aggregation of data that can be accumulated and computed in one or more dimensions. Drill-down is a technique that allows users to navigate through details. Slicing and dicing is a functionality in which users can take out (slice) a particular data set of an OLAP cube from different perspectives and view (dice) the slice. These perspectives are sometimes referred to as dimensions (such as viewing the same sales by salesperson, or by date, or by customer, or by product, or by region, etc.). An example of an OLAP system 104 is SAP BW / 4HANA TM data warehouse solution.

[0030] Cloud storage systems 106 typically store data processed by OLTP systems 102 and OLAP systems 104. Examples of cloud storage systems 106 include SAP TM data centers, Microsoft Azure TM data centers, Amazon Web Services TM data centers, Alibaba Cloud TM data centers, Google Cloud Platform TM data centers, and the like.

[0031] Networks 108 typically connect components of database environment 100. An example of a network 108 is the Internet.

[0032] According to embodiments, customers arrange for access to database environment 100 (including OLTP systems 102, OLAP systems 104, and cloud storage systems 106) via software as a service (SaaS), platform as a service (PaaS), or infrastructure as a service (IaaS). In such cases, these systems can provide services to many customers.

[0033] Database environment 100 can include other systems (not shown for brevity). For example, users can interact with OLTP systems 102 or OLAP systems 104 via end user devices (such as personal computers, mobile devices, and the like) connected via network 108. As another example, customers can have a local database system that processes transactional data and connects to OLAP systems 104 via network 108, and can store transactional data locally on cloud storage systems 106, and the like.

[0034] An example use case for database environment 100 is that the customer is a parent company with two subsidiaries; one subsidiary accesses cloud storage system 106a and the other subsidiary accesses cloud storage system 106b. In such a case, coordinating ETL processes into OLAP system 104 is complex, and embodiments described in greater detail herein provide improvements over existing approaches.

[0035] Figure 2 is a block diagram showing additional details of database environment 100 (see Figure 1 ). Database environment 100 includes transaction processing system 202 and ETL processing system 204. These components are shown as components of OLTP system 102. OLTP system 102 can include other components not described in detail for brevity. Alternatively, ETL processing system 204 can be located in a system separate from OLTP system 102.

[0036] Transaction processing system 202 generally performs transaction processing. Transaction processing generally includes insert, update, delete, read, and query operations on data. Transaction processing system 202 can implement a row-oriented DBMS, a column-oriented DBMS, etc. Transaction processing system 202 can interact with one or more storage devices to store transactional data. These storage devices can be cloud storage devices accessed via the Internet (e.g., cloud storage devices 106 of Figure 1 ), local storage devices accessed via a local area network (LAN), etc.

[0037] ETL processing system 204 generally coordinates extract, transform, and load operations related to transactional data processed by transaction processing system 202 to produce analytic data processed by OLAP system 104. In the extract operation, a subset of transactional data is selected. For example, transactional data for purchase orders can include purchase order identifiers, dates, product identifiers, purchaser identifiers, prices, discounts, etc., and the subset can be purchase order data for a given date range (e.g., 1 day).

[0038] In the transform operation, the extracted data is transformed according to defined operations. For example, purchase order data can include prices in various currencies (e.g., Euros, Canadian dollars, etc.), and the transform operation can apply exchange rates to the given currency prices to convert them to a single currency price (e.g., U.S. dollars) for each extracted record.

[0039] In the load operation, the transformed data is loaded into OLAP system 104. As Figure 2As shown, the ETL processing system 204 uses remote system calls (also referred to as remote function calls, RFCs) 206 to coordinate loading with the OLAP system 104. For example, the ETL processing system 204 uses the RFCs 206 to obtain configuration information, such as remote function names, from the OLAP system 104.

[0040] More specifically, the process of transferring data in a load operation includes five general steps. First, the ETL processing system 204 generates a transaction identifier. Second, the ETL processing system 204 performs a handshake with the OLAP system 104. Third, the ETL processing system 204 writes the transformed data to one or more staging tables in a package. (The staging tables can be stored by the cloud storage system 106.) Fourth, the ETL processing system 204 triggers post-processing of the OLAP system 104. Fifth, the ETL processing system 204 checks the post-processing status of each load (or each batch of loads) in a loop.

[0041] As a specific example, to perform the handshake, the ETL processing system 204 calls a handshake function on the OLAP system 104, with an operation name of "write," a requested version number, an identifier for the task, a name for the task, and so on. The handshake / versioning function returns a list of function modules for the steps in the given version process. For example, a given version can have three steps (three function names): a function to write the data, a function to schedule post-processing, and a function to check the post-processing status. These functions are then called in subsequent steps to perform their respective operations.

[0042] In this way, the ETL processing system 204 extracts a subset of transactional data from a transactional database table (e.g., stored by the cloud storage system 106), applies transformations, and uses RFCs to send the data (transformed transactional data and configuration information) to be loaded to the OLAP system 104. As discussed in more detail below, the ETL processing system 204 generates a single transaction identifier for the ETL process. The ETL processing system 204 also stores configuration information that defines the operations to be performed in each ETL process. The ETL processing system 204 can trigger each transaction manually (e.g., according to user interaction) or automatically (e.g., according to a scheduler).

[0043] OLAP system 104 (e.g., via RFC 206) receives data from ETL processing system 204 and uses the unique transaction identifier received as part of the load (e.g., in configuration information) to perform the loading and post-processing of the data. The RFC that performs the load contains both business data (e.g., transformed transactional data, such as purchase order data, etc.) and control data (e.g., transaction identifier, target table, package number, etc.). The OLAP system 104 then passes the results of the load back to the ETL processing system 204, as further detailed below. Configuration information is sent in both directions: the OLAP system 104 sends the function name for the load operation to the ETL processing system 204 in response to the RFC 206; and the ETL processing system 204 uses another RFC 206 to send the business data and configuration information (e.g., transaction identifier, target table, etc.) to the OLAP system 104 to perform the load and post-processing.

[0044] As an example, transaction processing system 202 performs transaction processing on purchase order data; the purchase order data is stored by cloud storage 106a (see Figure 1 ) Finally, ETL processing system 204 performs the items (discussed in more detail below) to perform ETL operations on the purchase order data, including the extract, transform, and load operations discussed above. ETL processing system 204 uses an RFC 206 to configure OLAP system 104 to perform the load operation. The RFC 206 includes configuration information for the load operation, such as the transaction identifier, the target table stored by one of the cloud storage systems 106, etc. OLAP system 104 performs the load operation and reports the results of the load operation to ETL processing system 204.

[0045] Figure 3 is a flowchart of a method 300 of data processing. Method 300 generally describes an ETL process implemented, for example, by systems in database environment 100 (see Figure 1 ) by executing one or more computer programs.

[0046] At 302, a single transaction having a single transaction identifier is generated as part of performing an extract, transform, load (ETL) operation. The single transaction refers to a single target system transaction to be used by the target system. For example, ETL processing system 204 (see Figure 2 ) can generate a single transaction identifier for a given ETL operation for use by OLAP system 104 as part of the overall transaction.

[0047] At 304, a plurality of operating system (OS) processes are spawned. The plurality of OS processes correspond to a plurality of load operations of the ETL operation, and each load operation of the plurality of load operations is associated with one target table of the plurality of target tables. For example, the ETL processing system 204 can execute the transaction (see 302) of the ETL operation as a collection of data flows executed by OS processes. The data flows are described in more detail below.

[0048] At 306, the plurality of OS processes are executed in parallel. (The plurality of OS processes can also include processes that are executed sequentially, as discussed in more detail below.) Each OS process of the plurality of OS processes is associated with a single transaction identifier and includes communicating configuration information for each load operation of the plurality of load operations to the OLAP system. For example, the ETL processing system 204 can execute OS processes in parallel to execute each data flow. The ETL processing system 204 uses remote system calls to communicate the configuration information to the OLAP system 104, as described in more detail below.

[0049] At 308, each target table of the plurality of target tables is loaded as part of a single target system transaction in the OLAP system according to the configuration information for each load operation of the plurality of load operations. For example, the OLAP system 104 uses the configuration information to perform the loading of the target tables. Because each load operation corresponds to a remote system call (see 306) and each target table is separate, the OLAP system 104 can perform the load operations (see 306) in parallel according to the execution of the parallel OS processes.

[0050] At 310, a plurality of results of loading the plurality of target tables are communicated. For example, the OLAP system 104 can communicate to the ETL processing system 204 the results (see 308) for each load operation corresponding to each OS process and corresponding to each remote system call (see 306).

[0051] At 312, a verification of the single transaction is performed using the plurality of results from the OLAP system. For example, the ETL processing system 204 can perform a verification of the results (see 310) from the OLAP system 104. The single transaction identifier enables better verification control than performing the load in sequence or without using a single overall transaction.

[0052] The following sections provide additional details of the ETL processes described herein.

[0053] Transactions, tasks, data flows, and processes

[0054] The ETL processing system 204 (see Figure 2 spawns transactions with single transaction identifiers to coordinate the ETL operation. See also Figure 3302 in. As further described herein, using a single transaction identifier allows for better transaction control of parallel load operations compared to validation of multiple consecutive transactions. The transaction may also be referred to as a project, and the transaction identifier may be referred to as a project identifier. The transaction identifier may be a 32-character unique identifier generated by the ETL processing system 204.

[0055] A project can contain one or more tasks. Typically, a task has a source data store (e.g., one or more source tables in a cloud storage system 106) and a target data store (e.g., a target table in the same or another cloud storage system 106).

[0056] A task can contain one or more data flows. Each data flow has a target table. Typically, a data flow defines the movement and transformation of data from one or more sources to a single target. Within a data flow, transformations can be used to define the changes to the data required at the target. Data flows can be arranged in a list within a task, and the data flows can be executed sequentially based on the order defined in the list at the task level. A single data flow can have multiple table sources from the same data store, but can have only a single target table. Data flows that do not depend on other data flows (for example, data flows that are not arranged sequentially) can be executed in parallel.

[0057] The data flows at runtime are converted into a single operating system (OS) process. If the data flows are in series, they are executed sequentially. If two or more data flows are not connected, they can be executed in parallel (e.g., concurrently). Data flows can also be grouped, such as executing two data flows in parallel and then executing a third data flow sequentially. When executing parallel data flows, the ETL processing system 204 can coordinate the parallel data flows and then wait for each predecessor data flow to complete before starting the next sequential step. The number of data flows may be as many as the number of operating system processes supported by the operating system.

[0058] Remote system calls

[0059] ETL processing system 204 (see Figure 2 ) Use remote system calls 206 to configure ETL operations of the OLAP system 104. See also Figure 3 306 of . Generally, a remote system call occurs when a computer program on one computer system causes a process to be executed in a different address space (typically, on another computer system), which is coded as if it were a local process call, without the programmer explicitly coding the details for the remote interaction. That is, a remote system call invokes a function to be executed by the remote system. In database environment 100 (see Figure 2In the example shown in FIG. 7, OLTP system 102 uses remote system calls 206 to invoke functions to be performed by OLAP system 104.

[0060] Remote system calls can communicate via a hypertext transfer protocol (HTTP) connector. Thus, the remote system can be located anywhere. Such an operation contrasts with database environments that use other technologies, such as Java Database Connectivity (JDBC), to perform load operations. JDBC requires that the OLTP system and the OLAP system be in the same environment and as close to each other as possible for best performance. TM

[0061] In general, ETL processing system 204 uses remote system calls for each data stream to perform ETL operations. (All data streams in a task are then associated with a single transaction identifier.) The remote system call includes configuration information for the ETL operation of that data stream, including parameters. The parameters can include a unique identifier, a batch size, a remote system call identifier, and a post-processing flag. The unique identifier provides an identifier for the data stream, e.g., for validation or other post-processing activities. The batch size details how to subdivide the data load for the ETL operation; batch size and batching are discussed in more detail below. The remote system call identifier identifies the function to be executed remotely, including any parameters for that function. For example, the parameters can include a source memory, a source table (or tables), a target memory, a target table, etc. The post-processing flag indicates what post-processing or other validation processes to perform; post-processing and validation are discussed in more detail below.

[0062] Figure 7 FIG. 7 is a block diagram showing an example of a task 700 that performs three data streams 702, 704, and 706. Data stream 702 loads table 1, data stream 704 loads table 2, and data stream 706 loads table 3. The load of table 2 depends on the load of table 1, so data streams 702 and 704 are linked in sequence. The load of table 3 is independent of the loads of table 1 and table 2, so data stream 706 is performed in parallel. Each stream is initialized using the Set Env (set environment) 710 and Set Env 712 functions to set system environment variables to the desired values.

[0063] As described above, all three data streams 702, 704, and 706 share the same transaction identifier in OLAP system 104. Each data stream runs as a separate OS process, and each data stream uses the RFC function names for each of the steps described above (e.g., write data, schedule post-processing, and check post-processing status) to perform the steps described above.

[0064] Batching

[0065] ​Generally, the batch size indicates that the ETL processing system 204 divides the ETL load operation (e.g., for a given data stream) into multiple sub-operations according to the batch size. The batch size can be dynamically calculated based on the row length of a given target table. The batch size can be set in megabytes (e.g., 50 MB) and can be converted to a batch size in records by dividing the batch size in MB by the maximum size of a row (the maximum size of a row is the sum of the maximum sizes of all columns in the table).

[0066] For example, assume that the load batch size in the data store is set to 400 Mb, the data stream loads a table with four columns of sizes 36, 10, 5000, and 27, making the row size 5073. The load batch size in records is then the load batch size in Mb (400000000) divided by the row size (5073), which is 78848.8, which can be rounded to the nearest 100 records, which is 78800 records. Thus, if the data stream loads 200000 records into the table, there will be three batches: two batches of 78800 records, and the remaining 42400 records in a third batch.

[0067] Validation and Post-Processing

[0068] The ETL processing system 204 can perform validation and post-processing of transactions at various levels. For example, the ETL processing system 204 can validate the success of an entire transaction, each task in a transaction, each data stream in a given transaction, each batch in a given data stream, etc., can set a level of validation to perform for a given transaction, and can communicate the level of validation to the OLAP system 104 via configuration information in each remote system call. Performing validation on an entire transaction (including performing any needed rollbacks and retries) is easier to verify data commit than coordinating multiple individual transactions without a single transaction identifier.

[0069] According to embodiments, the ETL processing system 204 performs post-processing at one or more levels. For example, post-processing can be performed at a data stream level, a transaction level, etc. The level of post-processing can be set using configuration information in each remote system call (e.g., by using one or more flags). The validation can include an overall state of a single target system transaction in the OLAP system (e.g., the overall transaction can be committed or rolled back).

[0070] Figure 4is a table 400 illustrating six different post-processing scenarios according to embodiments. The ETL processing system 204 implementing the post-processing scenarios of the table 400 can perform post-processing at the data stream level and at the transaction level. Post-processing is sent in response to receiving the results of ETL operations performed by the OLAP system 104 according to the indication of the remote system call 206. The result (D, RD, RE, or E) depends on the status reported by the OLAP system 104 and the “treat errors as success” flag.

[0071] In scenario 1, the OLAP system 104 reports a status of “processed” and the flag to treat errors as success is “true”. As a result, the ETL processing system 204 treats the result as a success (“D”). The ETL processing system 204 continues processing the subsequent data streams regardless of whether the post-processing is performed at the data stream level or at the transaction level.

[0072] In scenario 2, the OLAP system 104 reports a status of “processed” and the flag to treat errors as success is “false”. This operation is identical to the operation in scenario 1.

[0073] In scenario 3, the OLAP system 104 reports a status of “processed with error” and the flag to treat errors as success is “true”. As a result, the ETL processing system 204 treats the result as a success with error (“RD”), also referred to as a warning. This operation is identical to the operations in scenarios 1-2.

[0074] In scenario 4, the OLAP system 104 reports a status of “processed with error” and the flag to treat errors as success is “false”. As a result, the ETL processing system 204 treats the result as a failure with warning (“RE”) or error (“E”) according to the post-processing level.

[0075] If post-processing is performed at the data stream level, the result is “E” and the data stream sequence stops. This is because the data stream has an error and the flag to treat errors as success is “false”, so the subsequent data streams will not be executed.

[0076] If post-processing is performed at the transaction level, the result is “RE” and the data loading from all data streams to staging will be completed first before all post-processing is triggered at the same time. If one of the post-processing results returns as “processed with error”, the entire transaction is marked as “RE” and the subsequent data streams will continue because all post-processing is executed together at the end in sequence.

[0077] In scenario 5, the OLAP system 104 reports a status of “error” and the flag to treat errors as success is “true”. As a result, the ETL processing system 204 treats the result as an error and stops the subsequent processing at both the data stream level and the transaction level. The OLAP system 104 also performs a rollback.

[0078] In scenario 6, the OLAP system 104 reports a status of "error" and treats the error as a success flag of "false." This operation is the same as that in scenario 5.

[0079] As mentioned above, when data flows are not orchestrated to operate sequentially, they can be executed in parallel. In addition, data flows can be grouped to execute in parallel within a sequence. In this case, when one of them stops (for example, in scenario 4 above), all parallel running data flows will continue to run, but subsequent data flows will not run. For example, consider the following transaction:

[0080] DF1->Group (DF2->DF2a, DF3->DF3a)->DF4

[0081] In the above, data flows DF2 and DF3 can be executed in parallel as members of a group. If DF2 is stopped due to a post-processing error, DF3 and DF3a will continue, but DF4 will be stopped and will not run.

[0082] As a result of the embodiments described herein, loading and post-processing are performed by different processes, allowing data to be loaded into different tables on separate CPUs or even separate computers, but still be linked together and post-processed as a single transaction based on the same ID. Customers gain the benefits of faster processing and easier monitoring.

[0083] Figure 5 5 is a block diagram of an example computer system 500 for implementing various embodiments described above. For example, the computer system 500 may be used to implement the OLTP system 102, the OLAP system 104, the cloud storage system 106, and other devices that access the database environment 100 (see Figure 1 ) etc. The computer system 500 may be a desktop computer, a laptop computer, a server computer, or any other type of computer system or combination thereof. The transaction processing system 202, the ETL processing system 204 (see Figure 2 ) or some or all of the elements of a combination thereof may be included or implemented in the computer system 500. In addition, the computer system 500 may implement many of the operations, methods, and / or processes described above (e.g., Figure 3 Method 300, etc.). Figure 5 As shown, computer system 500 includes a processing subsystem 502 in communication with an input / output (I / O) subsystem 508 , a storage subsystem 510 , and a communication subsystem 524 via a bus subsystem 526 .

[0084] The bus subsystem 526 is configured to facilitate communications between the various components and subsystems of the computer system 500.Figure 5 The bus subsystem 526 is illustrated as a single bus, but can be implemented as multiple buses. The bus subsystem 526 can be any of various types of bus structures including a memory bus or memory controller, a peripheral bus, a local bus, and the like, using any of a variety of bus architectures (e.g., Industry Standard Architecture (ISA), Micro Channel Architecture (MCA), Enhanced ISA (EISA), Video Electronics Standards Association (VESA) local bus, Peripheral Component Interconnect (PCI), Universal Serial Bus (USB), advanced microcontroller bus architecture (AMBA), and the like).

[0085] The processing subsystem 502, which can be implemented as one or more integrated circuits (e.g., a conventional microprocessor or microcontroller), controls the operation of the computer system 500. The processing subsystem 502 can include one or more processors 504. Each processor 504 can include a single processing unit 506 (e.g., processor 504a) or several processing units 506 (e.g., processor 504b), as shown in FIG. 5. In some embodiments, a processor 504 of the processing subsystem 502 can be implemented as a single processor, while in other embodiments, a processor 504 of the processing subsystem 502 can be implemented as multiple processors integrated on a single chip or multiple chips. However, in some embodiments, a processor 504 of the processing subsystem 502 can be implemented as a combination of a single processor and multiple processors integrated on a single chip or multiple chips.

[0086] In some embodiments, the processing subsystem 502 can execute a variety of programs or processes in response to program code and can maintain multiple simultaneously executing programs or processes. At any given time, some or all of the program code to be executed can be resident in the processing subsystem 502 or in storage subsystem 510. Through suitable programming, the processing subsystem 502 can provide various functionalities of the present disclosure, such as the functionalities described above by reference to the method 300 (see FIG. 4), and the like. Figure 3 ) described above by reference to the method 300 (see FIG. 4), and the like.

[0087] The I / O subsystem 508 may include any number of user interface input devices and / or user interface output devices. User interface input devices may include a keyboard, a pointing device (e.g., a mouse, a trackball, etc.), a touchpad, a touch screen incorporated into a display, a scroll wheel, a click wheel, a dial, buttons, switches, a keypad, an audio input device with a voice recognition system, a microphone, an image / video capture device (e.g., a webcam, an image scanner, a barcode reader, etc.), a motion sensing device, a gesture recognition device, an eye gesture (e.g., blink) recognition device, a biometric input device, or other types of input devices.

[0088] User interface output devices may include visual output devices (e.g., display subsystems, indicator lights, etc.), audio output devices (e.g., speakers, headphones, etc.), etc. Examples of display subsystems may include cathode ray tubes (CRTs), flat panel devices (e.g., liquid crystal displays (LCDs), plasma displays, etc.), projection devices, touch screens, or other types of devices and mechanisms for outputting information from the computer system 500 to a user or other devices (e.g., printers).

[0089] like Figure 5 As shown, the storage subsystem 510 includes a system memory 512, a computer-readable storage medium 520, and a computer-readable storage medium reader 522. The storage subsystem 510 can implement the storage performed by the cloud storage system 106 (see Figure 1). The system memory 512 can be configured to store software in the form of program instructions that can be loaded and executed by the processing subsystem 502, as well as data generated during the execution of the program instructions. In some embodiments, the system memory 512 may include volatile memory (e.g., random access memory (RAM)) and / or non-volatile memory (e.g., read-only memory (ROM), programmable read-only memory (PROM), erasable programmable read-only memory (EPROM), electrically erasable programmable read-only memory (EEPROM), flash memory, etc.). The system memory 512 may include different types of memory, such as static random access memory (SRAM) and / or dynamic random access memory (DRAM). In some embodiments, system memory 512 may include a basic input / output system (BIOS) configured to store the basic routines to facilitate the transfer of information (e.g., during startup) between elements within computer system 500. Such BIOS may be stored in ROM (e.g., a ROM chip), flash memory, or another type of memory configured to store BIOS.

[0090] like Figure 5 As shown, system memory 512 includes application programs 514 (e.g., Figure 2 The ETL processing system 204 includes an application program), program data 516, and an operating system (OS) 518. The OS 518 can be one of the following versions: Microsoft Windows TM , Apple Mac OS TM , Apple OS X TM , Apple macOS TM , and / or Linux TM Operating systems, various commercial UNIX TM or UNIX-like operating systems (including but not limited to various GNU / Linux operating systems, Google Chrome TM OS, etc.) and / or such as Apple iOS TM , Windows Mobile TM , AndroidTM BlackBerry OS TM Palm OS TM mobile operating systems, and WebOS TM operating systems.

[0091] The computer-readable storage media 520 can be a non-transitory computer-readable medium configured to store software (e.g., programs, code modules, data constructs, instructions, etc.). Many of the above-described components (e.g., the ETL processing system 204 of the Figure 2 processes (e.g., the method 300 of the Figure 3 may be implemented as software that, when executed by a processor or processing unit (e.g., a processor or processing unit of the processing subsystem 502), performs the operations of such components and / or processes. The storage subsystem 510 can also store data used in

[0092] The storage subsystem 510 can also include a computer- readable storage media reader 522 that is configured to read from and write to the computer-readable storage media 520. The computer-readable storage media 520 can be transitory or non-transitory computer- readable storage medium collectively or individually configured to store data, instructions, programs, code moduies, and / or other information. The computer-readable storage media 520 can be together and optionally in combination with the system memory 512 to comprehensively represent remote, local, fixed, and / or removable storage devices plus storage media for temporarily and / or more permanently containing, storing, transmitting, and retrieving computer-readable information.

[0093] The computer-readable storage media 520 can be any appropriate media known or used in the art, including storage media such as volatile, nonvolatile, removable, and non-removable media implemented in any method or technology for storage and / or transmission of information. Examples of such storage media include RAM, ROM, EEPROM, flash memory or other memory technology, compact disc read-only memory (CD-ROM), digital versatile disks (DVD), Blu-ray Discs (BD), cassette tape, magnetic tape, magnetic disk storage or other magnetic storage devices, optical disk storage (e.g., hard drives), compact discs, solid-state drives (SSDs), flash memory cards (e.g., secure digital (SD) cards, compact flash cards, etc.), USB flash drives, or any other type of computer-readable storage media or device.

[0094] The communication subsystem 524 serves as an interface for receiving data from and sending data to other devices, computer systems, and networks. For example, the communication subsystem 524 may allow the computer system 500 to connect to one or more devices via a network (e.g., a personal area network (PAN), a local area network (LAN), a storage area network (SAN), a campus area network (CAN), a metropolitan area network (MAN), a wide area network (WAN), a global area network (GAN), an intranet, the Internet, various different types of networks, etc.). The communication subsystem 524 may include any number of different communication components. Examples of such components may include a radio frequency (RF) transceiver component for accessing a wireless voice and / or data network (e.g., using a cellular technology such as 2G, 3G, 4G, 5G, such as Wi-Fi, Bluetooth, etc.). TM , Purple Bee TM In some embodiments, the communication subsystem 524 may include components configured for wired communication (e.g., Ethernet) and components configured for wireless communication or components configured for wireless communication instead of components configured for wireless communication.

[0095] Those skilled in the art will appreciate that Figure 5 The architecture shown in FIG. 5 is merely an example architecture for computer system 500 , and computer system 500 may have more or fewer components than shown, or a different configuration of components. Figure 5 The various components shown may be implemented in hardware, software, firmware, or any combination thereof, including one or more signal processing and / or application specific integrated circuits.

[0096] Figure 6 6 is a block diagram of a cloud computing system 600 for implementing various embodiments described above. For example, one of the client devices 602-608 may be used to implement a client device for accessing the database environment 100 (see Figure 1), and cloud computing system 612 of system 600 can be used to implement OLTP system 102, OLAP system 104, cloud storage system 106, ETL processing system 204, etc. As shown, system 600 includes client devices 602-608, one or more networks 610, and cloud computing system 612. Cloud computing system 612 is configured to provide resources and data to client devices 602-608 via network(s) 610. In some embodiments, cloud computing system 600 provides resources to any number of different users (e.g., customers, tenants, organizations, etc.). Cloud computing system 612 can be implemented by one or more computer systems (e.g., servers), virtual machines operating on computer systems, or a combination thereof.

[0097] As shown, cloud computing system 612 includes one or more applications 614, one or more services 616, and one or more databases 618. Cloud computing system 600 can provide applications 614, services 616, and databases 618 to any number of different customers in a self-service, subscription-based, elastically scalable, reliable, highly available, and secure manner.

[0098] In some embodiments, cloud computing system 600 can be adapted to automatically provision, manage and track customer subscriptions for services provided by cloud computing system 600. Cloud computing system 600 can provide cloud services via different deployment models. For example, cloud services can be provided under a public cloud model in which cloud computing system 600 is owned by an organization selling cloud services and cloud services are made available to the general public or different industry enterprises. As another example, cloud services can be provided under a private cloud model in which cloud computing system 600 is operated for the single organization and can provide cloud services for one or more entities within the organization. Cloud services can also be provided under a community cloud model in which cloud computing system 600 and cloud services provided by cloud computing system 600 are shared by several organizations in a related community. Cloud services can also be provided under a hybrid cloud model, which is a combination of two or more of the different models mentioned above.

[0099] In some instances, any of applications 614, services 616, and databases 618 provided to client devices 602-608 from cloud computing system 600 via network(s) 610 are referred to as “cloud services.” Typically, the servers and systems that make up cloud computing system 600 are different from a customer’s on-premises servers and systems. For example, cloud computing system 600 can host an application and a user of one of client devices 602-608 can subscribe and use the application via network(s) 610.

[0100] The applications 614 can include software applications configured to execute on the cloud computing system 612 (e.g., computer systems or virtual machines operating on computer systems) and accessed, controlled, managed, etc. via the client devices 602-608. In some embodiments, the applications 614 can include server applications and / or middleware applications (e.g., HTTP (HyperText Transfer Protocol) server applications, FTP (File Transfer Protocol) server applications, CGI (Common Gateway Interface) server applications, Java TM The services 616 are software components, modules, applications, etc. configured to execute on the cloud computing system 612 and provide functionality to the client devices 602-608 via the network 610. The services 616 can be web-based services or on-demand services.

[0101] The databases 618 are configured to store and / or manage data accessed by the applications 614, the services 616, or the client devices 602-608. For example, transactional data processed by the OLTP system 102, analytical data processed by the OLAP system 104, data stored by the cloud storage system 106 (see Figure 1 ), etc. can be stored in the databases 618. The databases 618 can reside in non-transitory storage media local to (and / or resident on) the cloud computing system 612, in a storage area network (SAN), or on non-transitory storage media remote from the cloud computing system 612. In some embodiments, the databases 618 can be relational databases managed by a relational database management system (RDBMS) or the like. The databases 618 can be column-oriented databases, row-oriented databases, or a combination thereof. In some embodiments, some or all of the databases 618 are in-memory databases. That is, in some such embodiments, data for the databases 618 is stored and managed in memory (e.g., random access memory (RAM)).

[0102] The client devices 602-608 are configured to execute and operate client applications (e.g., web browsers, proprietary client applications, etc.) that communicate with the applications 614, the services 616, or the databases 618 via the network 610. In this manner, when the applications 614, the services 616, and the databases 618 operate (e.g., are hosted) on the cloud computing system 600, the client devices 602-608 can access various functionality provided by the applications 614, the services 616, and the databases 618. The client devices 602-608 can be computer systems 500 (see Figure 5 ). Although a system 600 with four client devices is shown, any number of client devices can be supported.

[0103] The network 610 can be any type of network configured to facilitate communication of data between the client devices 602-608 and the cloud computing system 612 using any of a variety of network protocols. The network 610 can be a personal area network (PAN), a local area network (LAN), a storage area network (SAN), a campus area network (CAN), a metropolitan area network (MAN), a wide area network (WAN), a global area network (GAN), an intranet, the Internet, any number of different types of networks, etc.

[0104] The above description illustrates various embodiments of the present disclosure and examples of how to implement various aspects of the present disclosure. The above examples and embodiments should not be considered to be exclusive embodiments and are presented in order to illustrate the flexibility and advantages of the present disclosure as defined by the following claims. Other arrangements, embodiments, implementations and equivalents will be apparent to those skilled in the art based on the foregoing disclosure and teachings, and can be employed without departing from the spirit and scope of the present disclosure defined by the following claims.

Claims

1. A computer-implemented data processing method for implementing an extract, transform, load (ETL) process in a database environment, the database environment comprising an ETL system and an online analytical processing (OLAP) system, the method comprising: generating, by the ETL system, a single target system transaction having a single transaction identifier as part of performing an ETL operation, the single transaction identifier for an overall ETL operation used by the OLAP system; extracting, by the ETL system, a subset of transaction data stored using a plurality of heterogeneous cloud data storage systems, each of the cloud storage systems involving its own ETL operation, and transforming, by the ETL system, the extracted data; generating, by the ETL system, a plurality of operating system (OS) processes within the single transaction, wherein the plurality of OS processes correspond to a plurality of load operations of the ETL operation for loading the transformed transaction data into the OLAP system, wherein each load operation of the plurality of load operations is associated with only one target table of a plurality of target tables, wherein the plurality of target tables are stored by the plurality of heterogeneous cloud data storage systems; executing, by the ETL system, the plurality of OS processes in parallel, wherein each OS process of the plurality of OS processes is associated with the single transaction identifier, including: communicating, by the ETL system to the OLAP system, configuration information for each load operation of the plurality of load operations, the configuration information including the transaction identifier, wherein the ETL system communicates the configuration information to the OLAP system using a plurality of remote system calls, wherein each remote system call of the plurality of remote system calls corresponds to one OS process of the plurality of OS processes, to one load operation of the plurality of load operations, and to one target table of the plurality of target tables; loading, by the OLAP system, each target table of the plurality of target tables according to the configuration information for each load operation of the plurality of load operations as part of the single target system transaction in the OLAP system, wherein the ETL system configures the ETL operation for the OLAP system using the remote system calls, and wherein the OLAP system performs the loading using the unique transaction identifier; communicating, by the OLAP system to the ETL system, a plurality of results of loading the plurality of target tables; and performing, by the ETL system, a verification of the single target system transaction using the plurality of results from the OLAP system.

2. The method of claim 1, wherein, the configuration information for a given load operation of the plurality of load operations includes a unique identifier, a batch size, a remote system call identifier, and a post-processing flag.

3. The method of claim 1, wherein, the ETL system communicates the configuration information to the OLAP system by utilizing a plurality of remote system calls using a plurality of hypertext transfer protocol (HTTP) connectors.

4. The method of claim 1, wherein, the configuration information for each load operation of the plurality of load operations includes a batch size, wherein the ETL system divides a given load operation of the plurality of load operations into a plurality of batches according to the batch size.

5. The method of claim 4, wherein, the batch size is dynamically calculated based on a row length of a given target table of the plurality of target tables, wherein the given table is specified in the configuration information for the given load operation.

6. The method of claim 1, wherein, A single transaction includes multiple data streams, wherein validation of the single target system transaction can be performed at least one of at a transaction level and at a data stream level.

7. The method of claim 1, wherein, Validation of the single target system transaction includes validating an overall state of the single target system transaction in the OLAP system, which is one of a committed state and a rolled back state.

8. The method of claim 1, wherein, A single transaction contains multiple tasks, wherein a given task of the multiple tasks includes multiple data streams.

9. The method of claim 1, wherein, A given OS process of the multiple OS processes contains multiple data streams, wherein a first set of the multiple data streams are executed in parallel and a second set of the multiple data streams are executed sequentially.

10. The method of claim 1, wherein, The OLAP system includes an in-memory database system.

11. A non-transitory computer-readable medium storing instructions that, when executed by a processor of a computer system, control the computer system to perform a method of data processing for implementing an extract, transform, load (ETL) process in a database environment, the database environment including an ETL system and an online analytical processing (OLAP) system, the method comprising: generating, by the ETL system, a single target system transaction having a single transaction identifier as part of performing an ETL operation, the single transaction identifier for an overall ETL operation used by the OLAP system; extracting, by the ETL system, a subset of transaction data stored using a plurality of heterogeneous cloud data storage systems, each of the cloud storage systems involving its own ETL operation, and transforming, by the ETL system, the extracted data; generating, by the ETL system, a plurality of operating system (OS) processes within the single transaction, wherein the plurality of OS processes correspond to a plurality of load operations of the ETL operation for loading the transformed transaction data into the OLAP system, wherein each load operation of the plurality of load operations is associated with only one target table of a plurality of target tables, wherein the plurality of target tables are stored by the plurality of heterogeneous cloud data storage systems; executing, by the ETL system, the plurality of OS processes in parallel, wherein each OS process of the plurality of OS processes is associated with the single transaction identifier, including: communicating, by the ETL system to the OLAP system, configuration information for each load operation of the plurality of load operations, the configuration information including the transaction identifier, wherein the ETL system communicates the configuration information to the OLAP system using a plurality of remote system calls, wherein each remote system call of the plurality of remote system calls corresponds to one OS process of the plurality of OS processes, to one load operation of the plurality of load operations, and to one target table of the plurality of target tables; loading, by the OLAP system, each target table of the plurality of target tables according to the configuration information for each load operation of the plurality of load operations as part of the single target system transaction in the OLAP system, wherein the ETL system configures the ETL operation for the OLAP system using the remote system calls, and wherein the OLAP system performs the loading using the unique transaction identifier; communicating, by the OLAP system to the ETL system, a plurality of results of loading the plurality of target tables; and The ETL system is configured to use the plurality of results from the OLAP system to perform validation of the single target system transaction.

12. A computer system for performing data processing for implementing an extract, transform, load (ETL) process in a database environment, the computer system comprising: a database environment comprising: an extract, transform, load (ETL) system; and an online analytical processing (OLAP) system, wherein the ETL system is configured to generate a single target system transaction having a single transaction identifier as part of performing an ETL operation, the single transaction identifier for an overall ETL operation used by the online analytical processing (OLAP) system; wherein the ETL system is configured to extract a subset of transaction data stored using a plurality of heterogeneous cloud data storage systems, each of the cloud storage systems involving its own ETL operation, and to transform the extracted data; wherein the ETL system is configured to spawn a plurality of operating system (OS) processes within the single transaction, wherein the plurality of OS processes correspond to a plurality of load operations of the ETL operation for loading the transformed transaction data into the OLAP system, wherein each load operation of the plurality of load operations is associated with only one target table of a plurality of target tables, wherein the plurality of target tables are stored by the plurality of heterogeneous cloud data storage systems, wherein the ETL system is configured to execute the plurality of OS processes in parallel, wherein each OS process of the plurality of OS processes is associated with the single transaction identifier, including: transmitting, by the ETL system to the OLAP system, configuration information for each load operation of the plurality of load operations, the configuration information including the transaction identifier, wherein the ETL system is configured to transmit the configuration information to the OLAP system using a plurality of remote system calls, wherein each remote system call of the plurality of remote system calls corresponds to one OS process of the plurality of OS processes, to one load operation of the plurality of load operations, and to one target table of the plurality of target tables, wherein the ETL system is configured to use the remote system calls to configure the ETL operation for the OLAP system, wherein the OLAP system is configured to load each target table of the plurality of target tables according to the configuration information for each load operation of the plurality of load operations as part of the single target system transaction in the OLAP system, wherein the OLAP system is configured to use the unique transaction identifier to perform the loading, wherein the OLAP system is configured to transmit a plurality of results of loading the plurality of target tables to the ETL system, and wherein the ETL system is configured to use the plurality of results from the OLAP system to perform validation of the single target system transaction.

13. The computer system of claim 12, wherein, The configuration information for each load operation of the plurality of load operations includes a batch size, wherein the ETL system divides a given load operation of the plurality of load operations into a plurality of batches according to the batch size.

14. The computer system of claim 12, wherein, The single transaction includes a plurality of data streams, wherein the validation of the single target system transaction is performed on at least one of a transaction level and a data stream level. The ETL system is configured to use the plurality of results from the OLAP system to perform validation of the single target system transaction.

15. The computer system of claim 12, wherein, Verification of the single-target system transaction includes verifying an overall state of the single-target system transaction in the OLAP system, which is one of a committed state and a rolled-back state.

16. The computer system of claim 12, wherein, A given OS process of the plurality of OS processes includes a plurality of data flows, wherein a first set of the plurality of data flows are executed in parallel and a second set of the plurality of data flows are executed sequentially.

Images