Efficiently bulk load multiple rows or partitions into a single target table

Abstract

A computer-implemented system and method processes inserts into a target table of a target database system. A single query language INSERT statement associated with a source partition of a source table in a source database system is received, identifying a block of data (BOD) having a partition identifier (PID) to be inserted into the target table. The INSERT statement does not include a literal PID. In response to receiving the INSERT statement, a processor receives the BOD from the source table via a data stream and inserts the BOD into the target table. This reduces the overhead associated with inserting multiple rows of data into a target table using an INSERT statement.

Description

[Technical Field]

[0001] This specification discloses a system and related method for improving the efficiency of bulk loading of multiple rows or partitions into a single target table in a database accelerator. [Background technology]

[0002] The use of partitions in a database allows various database operations to be performed more efficiently because certain operations are performed on parts of the database at once rather than the entire database.

[0003] When executing certain database commands, such as INSERT commands, it can be beneficial to reduce the overhead associated with such commands, especially when the amount of payload data involved is small. A single INSERT statement is used on each logical node of the target database system to process a single partition from the source database system. Each INSERT statement incurs overhead on the target database system as well as on the database accelerator. An SQL connection is required over which the statement is executed. The statement uses named pipes to efficiently transfer data from one process to another (avoiding expensive data conversion steps between different formats). The statement must be initiated, and additional overhead is incurred.

[0004] When the amount of data processed by a single INSERT statement is relatively small, the overhead of all of these steps can account for a significant amount of the overall time to batch load the data. It is desirable to reduce the overhead associated with executing a single INSERT statement to insert a portion / batch of data into a table in the target database system. Each batch of data originates from a specific partition of the source table and may be further decomposed / segregated for individual logical nodes in the target database system. Thus, each batch and / or each row within a batch has an associated identifier for the partition of the source table. Summary of the Invention

[0005] According to one aspect of the disclosure, a computer-implemented method for processing inserts into a target table of a target database system is provided. The method includes receiving, using a processor, a single query language INSERT statement associated with a source partition of a source table in a source database system, the INSERT statement identifying a block of data (BOD) having a partition identifier (PID) to be inserted into the target table. The INSERT statement does not include a literal PID. In response to receiving the INSERT statement, the processor receives the BOD from the source table via a data stream and inserts the BOD into the target table. This reduces overhead associated with inserting multiple rows of data into a target table using an INSERT statement.

[0006] According to another aspect of the present disclosure, the PID of each row of the BOD is inserted into a column of the row after receiving the INSERT statement and before inserting the row into the target table, thereby allowing a single INSERT statement to be used to insert PIDs into multiple rows and insert them into the target table.

[0007] According to another aspect disclosed herein, the BOD is organized into rows, each row is modified to include a partition ID value, and the partition ID value is injected into the data stream, thereby eliminating the overhead associated with sources that include PIDs.

[0008] According to another aspect disclosed herein, the method further includes specifying a shared memory location containing a current PID and writing the current PID to the shared memory location, where the current PID is readable by the target database system, thereby allowing a database accelerator to reference the shared memory location containing the PID.

[0009] According to another aspect of the present disclosure, the PID is specified in the data stream itself such that each batch of the BOD is preceded by a batch PID and the number of rows to which the batch PID applies. The method further includes reading and storing the batch PID and the number of rows, reading n rows corresponding to the number of rows from the data stream, and processing each read row using the batch PID from which it was read. This allows the database accelerator to accurately determine the number of rows to which a PID applies.

[0010] According to another aspect disclosed herein, the data stream comprises multiple data streams that are demultiplexed on the source database system, thereby enabling efficient data transfer over multiple channels.

[0011] According to another aspect disclosed herein, the method further includes performing the demultiplexing of rows in the BODs by a source database system using an unload utility initiated on the source database system to extract the BODs from the source tables and populate the multiple data streams, thereby reducing the amount of resources required by a database accelerator.

[0012] According to another aspect disclosed herein, the method further includes determining, for all logical nodes of the target database system, unload criteria that identify all rows that will enter each logical node, and initiating the unload utility using the unload criteria, thereby enabling better structuring and more efficient processing of the data to be transferred.

[0013] According to another aspect disclosed herein, the method further includes recombining the multiple data streams into a single data stream, where the granularity is at blocks or batches of rows rather than at individual rows, and where each batch has a header indicating which target node the block or batch is for, thereby simplifying the mechanism by which the accelerator inserts PIDs into rows of the data stream.

[0014] According to another aspect of the present disclosure, the method further includes directly transmitting the data stream obtained by demultiplexing data on the source database system to the target node on the target database system, where such direct communication reduces potential points of error in data communication.

[0015] According to another aspect of the present disclosure, there is provided a method for performing a database transfer. The method includes using an UNLOAD utility to open multiple data streams, one for each node in a target database system, and scanning all rows of a source table in a source database system. For each row, read each row from the source table, apply unload criteria to determine the node for each row in the target database system, and write the row to a specific data stream for the target node. This allows for structuring the database rows transferred to the target system.

[0016] According to another aspect disclosed herein, a system for processing inserts into a target table of a target database system is provided. The system includes a memory and a processor, the processor configured to: receive a single query language INSERT statement associated with a source partition of a source table in a source database system, the INSERT statement identifying a block of data (BOD) having a partition identifier (PID) to be inserted into the target table, the INSERT statement not including a literal PID; receive the BOD from the source table via a data stream in response to receiving the INSERT statement; and insert the BOD into the target table. This can reduce overhead associated with inserting multiple rows of data into a target table using an INSERT statement. System features corresponding to related method features are further provided.

[0017] A computer program product may be provided that can be used to implement the functionality of the methods and systems described above. The computer program product includes instructions accessible from a computer-usable or computer-readable medium that provides program code for use with, by, or in connection with a computer or any instruction execution system. For purposes of this description, a computer-usable or computer-readable medium may be any apparatus that can include a mechanism for storing, communicating, propagating, or transporting a program for use by or in connection with an instruction execution system, apparatus, or device. [Brief explanation of the drawings]

[0018] Various embodiments are described herein with reference to different subject matter. In particular, some embodiments may be described with reference to methods, and other embodiments may be described with reference to devices and systems. However, as those skilled in the art can infer from the above and following descriptions, unless otherwise noted, any combination of features belonging to one type of subject matter, as well as any combination between features relating to different subject matters (in particular, any combination between method features and device and system features), is considered to be disclosed herein.

[0019] The above-defined aspects, and further aspects disclosed herein, will be apparent from and will be elucidated with reference to one or more example embodiments described hereinafter, to which the invention is not limited. Various embodiments will be described, by way of example only, with reference to the following figures:

[0020] [Figure 1A] FIG. 1 is a block diagram of a data processing system (DPS) according to one or more embodiments disclosed herein. [Figure 1B] FIG. 1 is a schematic diagram illustrating a cloud computing environment according to one embodiment disclosed herein. [Figure 1C] FIG. 2 is a schematic diagram illustrating abstraction model layers according to one embodiment disclosed herein. [Figure 2A] FIG. 1 is a block diagram of a database system having a source database with tables and a target database according to some implementations. [Figure 2B] FIG. 2 is a data structure diagram of a data stream according to some implementations. [Figure 3] 1 is a flowchart of a process for performing an INSERT operation according to some implementations. [Figure 4] FIG. 1 is a block diagram illustrating the forwarding of a database row to a node. DETAILED DESCRIPTION OF THE INVENTION

[0021] (Overview of bulk loading multiple partitions into a single target table) Provided herein are systems and related methods for improving the efficiency of bulk loading multiple partitions into a single target table in a database accelerator. The systems and methods enable the use of a single INSERT statement that does not include a partition ID when inserting a block of data (BOD) that spans multiple partitions. Disclosed herein are various mechanisms that help reduce the overhead associated with issuing multiple INSERT statements. Such overhead can be significant when the amount of payload data involved is small.

[0022] The following abbreviations may be used below: API application programming interface ARM advanced RISC machine BOD block of data CD-ROM compact disc ROM CMS content management system CoD Capacity on Demand CPU central processing unit CUoD Capacity Upgrade on Demand DPS data processing system DVD Digital Versatile Disk EPROM erasable programmable read-only memory FPGA field-programmable gate arrays HA high availability IaaS Infrastructure as a service I / O input / output IPL initial program load IPS Internet service provider ISA Instruction Set Architecture LAN local-area network LPAR logical partition PaaS Platform as a service PDA Personal Digital Assistant PLA Programmable Logic Arrays RAM random access memory RISC reduced instruction set computer ROM read-only memory SaaS Software as a service SLA Service Level Agreement SRAM static random-access memory SQL Structured Query Language WAN wide-area network

[0023] (Data Processing System Overview) 1A is a block diagram of an example DPS according to one or more embodiments. In this illustrative example, DPS 10 may include a communication bus 12. Communication bus 12 may provide communication between a processor unit 14, a memory 16, persistent storage 18, a communication unit 20, an I / O unit 22, and a display 24.

[0024] Processor unit 14 functions to execute instructions for software that can be loaded into memory 16. Processor unit 14 may be any number of processors, a multi-core processor, or other types of processors, depending on the particular implementation. As used herein, "a number" with respect to an item means one or more items. Furthermore, processor unit 14 may be implemented using any number of heterogeneous processor systems in which a main processor coexists with secondary processors on a single chip. As another illustrative example, processor unit 14 may be a symmetric multiprocessor system including multiple processors of the same type.

[0025] Memory 16 and persistent storage 18 are examples of storage 26. A storage device is any piece of hardware that can store information, such as, but not limited to, data, functional form of program code, or other suitable information, or a combination thereof, either temporarily or persistently, or both. Memory 16, in these examples, may be, for example, random access memory or any other suitable volatile or non-volatile storage device. Persistent storage 18 may take various forms, depending on the particular implementation.

[0026] For example, persistent storage 18 may include one or more components or devices. For example, persistent storage 18 may be a hard drive, a flash memory, a rewritable optical disk, a rewritable magnetic tape, or some combination of the above. Additionally, the media used by persistent storage 18 may be removable. For example, persistent storage 18 may be a removable hard drive.

[0027] In these examples, the communication unit 20 can realize communication with other DPSs or devices. In these examples, the communication unit 20 is a network interface card. The communication unit 20 may realize communication using either or both of a physical communication link and a wireless communication link.

[0028] Input / output unit 22 may allow for the input and output of data with other devices connectable to DPS 10. For example, input / output unit 22 may provide a connection for user input via a keyboard, mouse, or other suitable input device, or a combination thereof. Additionally, input / output unit 22 may send output to a printer. Display 24 may provide a mechanism for displaying information to a user.

[0029] Instructions for the operating system, applications, and / or programs may be located on storage device 26, which communicates with processor unit 14 via communication bus 12. In these illustrative examples, the instructions reside in functional form on persistent storage 18. These instructions may be loaded into memory 16 for execution by processor unit 14. The processes of the different embodiments may be performed by processor unit 14 using computer-executable instructions that may be located in a memory, such as memory 16. These instructions are referred to as program code 38 (described below), computer-usable program code, or computer-readable program code, which may be read and executed by a processor within processor unit 14. The program code in the different embodiments may be embodied on different physical or tangible computer-readable media, such as memory 16 or persistent storage 18.

[0030] DPS 10 may further include an interface for network 29. The interface may include hardware, drivers, software, etc. to enable communication over wired and wireless network 29 and may implement any number of communication protocols, such as those at various levels of the Open Systems Interconnection (OSI) seven-layer model.

[0031] FIG. 1A further illustrates a computer program product 30 that may include program code 38. The program code 38 may be disposed in a functional form on a selectively removable computer-readable medium 32 and loaded or transferred to the DPS 10 for execution by the processor unit 14. The program code 38 and the computer-readable medium 32 may form the computer program product 30 in these examples. As an example, the computer-readable medium 32 may be a computer-readable storage medium 34 or a computer-readable signal medium 36. The computer-readable storage medium 34 may include, for example, an optical or magnetic disk. The optical or magnetic disk is inserted or placed into a drive or other device that is part of persistent storage 18 for transfer onto a storage device, such as a hard drive that is part of persistent storage 18. The computer-readable storage medium 34 may also take the form of persistent storage, such as a hard drive, thumb drive, or flash memory that is connected to the DPS 10. In some cases, the computer-readable storage medium 34 may not be removable from the DPS 10.

[0032] Alternatively, program code 38 may be transmitted to DPS 10 using computer-readable signal medium 36. Computer-readable signal medium 36 may be, for example, a propagated data signal containing program code 38. For example, computer-readable signal medium 36 may be an electromagnetic signal, an optical signal, or any other suitable type of signal or combination thereof. These signals may be transmitted over communications links, such as wireless communications links, fiber optic cable, coaxial cable, an electrical wire, or any other suitable type of communications link or combination thereof. In other words, communications links and / or connections may be physical and / or wireless in illustrative examples.

[0033] In some exemplary embodiments, program code 38 may be downloaded over a network from another device or DPS via computer-readable signal medium 36 to persistent storage 18 for use within DPS 10. For example, program code stored on a computer-readable storage medium in a server DPS may be downloaded over a network from the server to DPS 10. The DPS providing program code 38 may be a server computer, a client computer, or other device capable of storing and transmitting program code 38.

[0034] The different components illustrated for DPS 10 are not meant to provide architectural limitations to the manner in which different embodiments may be implemented. Different illustrative embodiments may be implemented by a DPS including components in addition to or instead of the components illustrated for DPS 10.

[0035] (Cloud Computing Overview) Although this disclosure includes detailed descriptions of cloud computing, it should be understood that implementation of the teachings described herein is not limited to cloud computing environments. Rather, embodiments of the present invention may be practiced in conjunction with any other type of computing environment now known or later developed.

[0036] Cloud computing is a service delivery model for enabling convenient, on-demand network access to a shared pool of configurable computing resources (e.g., networks, network bandwidth, servers, processing, memory, storage, applications, virtual machines, and services) that can be rapidly provisioned and released with minimal management effort or interaction with a service provider. This cloud model includes at least five characteristics, at least three service models, and at least four deployment models.

[0037] The characteristics are as follows: On-Demand Self-Service: Cloud consumers can unilaterally provision computing capacity, such as server time or network storage, automatically as needed, without the need for human interaction with the service provider.

[0038] Broad network access: Computing power is available over the network and can be accessed through standard mechanisms, facilitating use by heterogeneous thin or thick client platforms (e.g., cell phones, laptops, PDAs).

[0039] Resource Pooling: Computing resources from a provider are pooled and offered to multiple consumers using a multi-tenant model. Various physical and virtual resources are dynamically allocated and reallocated based on demand. Consumers generally have no control or knowledge of the exact location of the resources they are provided with, resulting in a sense of location independence. However, consumers may be able to determine location at a higher level of abstraction (e.g., country, state, data center).

[0040] Rapid Elasticity: Computing capacity can be provisioned quickly and elastically, sometimes automatically, to instantly scale out and quickly release to instantly scale in. To the consumer, the computing power available for provisioning often appears unlimited, and can be purchased at any time and in any quantity.

[0041] Metered Services: Cloud systems leverage measurement capabilities at a level of abstraction appropriate to the type of service (e.g., storage, processing, bandwidth, active user accounts) to automatically control and optimize resource usage. Resource usage can be monitored, controlled, and reported to provide transparency to both providers and consumers of utilized services.

[0042] The service model is as follows: Software as a Service (SaaS): The functionality offered to the consumer is the availability of a provider's applications running on a cloud infrastructure that can be accessed from a variety of client devices through a thin client interface such as a web browser (e.g., webmail). The consumer does not manage or control the underlying cloud infrastructure, including the network, servers, operating systems, storage, or even individual application functionality, except for limited user-specific application configuration settings.

[0043] Platform as a Service (PaaS): The capability offered to consumers is to deploy applications they create or acquire using programming languages ​​and tools supported by the provider onto a cloud infrastructure. The consumer does not manage or control the underlying cloud infrastructure, including the network, servers, operating systems, or storage, but does have control over the deployed applications and, in some cases, the configuration of their hosting environment.

[0044] Infrastructure as a Service (IaaS): The functionality offered to consumers is the provisioning of processors, storage, networking, and other basic computing resources on which they can deploy and run any software, including operating systems and applications. The consumer does not manage or control the underlying cloud infrastructure, but has control over the operating system, storage, and deployed applications, and in some cases partial control over some network components (e.g., host firewalls).

[0045] The deployment model is as follows: Private Cloud: This cloud infrastructure is dedicated to a specific organization and can be managed by that organization or a third party, and can exist on-premise or off-premise.

[0046] Community Cloud: This cloud infrastructure is shared by multiple organizations to support a specific community with common concerns (e.g., mission, security requirements, policies, and compliance). This cloud infrastructure can be managed by those organizations or a third party and can exist on-premises or off-premises.

[0047] Public cloud: This cloud infrastructure is available to the general public or large industry organizations and is owned by an organization that sells cloud services.

[0048] Hybrid cloud: This cloud infrastructure combines two or more cloud models (private, community, or public), each of which retains its inherent nuances but is bound by standards or specific technologies that enable data and application portability (e.g., cloud bursting for load balancing between clouds).

[0049] A cloud computing environment is a service-oriented environment that emphasizes statelessness, low coupling, modularity, and semantic interoperability. At the core of cloud computing is an infrastructure that includes a network of interconnected nodes.

[0050] FIG. 1B illustrates an exemplary cloud computing environment 52. As shown, the cloud computing environment 52 includes one or more cloud computing nodes 50, with which local computing devices used by cloud consumers (e.g., PDAs or cell phones 54A, desktop computers 54B, laptop computers 54C, or automobile computer systems 54N, or combinations thereof) can communicate. The nodes 50 can communicate with each other. The nodes 50 can be physically or virtually grouped (not shown) in one or more networks, such as the private, community, public, or hybrid clouds described above, or combinations thereof. This enables the cloud computing environment 52 to provide infrastructure, platform, or software as a service, or combinations thereof, for which cloud consumers are not required to maintain resources on their local computing devices. It should be understood that the types of computing devices 54A-N illustrated in FIG. 1B are merely exemplary, and that the computing nodes 50 and the cloud computing environment 52 can communicate with any type of electronic device via any type of network or network-addressable connection (e.g., using a web browser), or both.

[0051] Here, a set of functional abstraction layers provided by cloud computing environment 52 (FIG. 1B) is shown in FIG. 1C. It should be understood in advance that the components, layers, and functions shown in FIG. 1C are merely exemplary, and embodiments of the present invention are not limited thereto. As shown, the following layers and corresponding functions are provided:

[0052] Hardware and software layer 60 includes hardware and software components. Examples of hardware components include mainframe 61, reduced instruction set computer (RISC) architecture-based server 62, server 63, blade server 64, storage device 65, and network and network components 66. In some embodiments, software components include network application server software 67 and database software 68.

[0053] The virtualization layer 70 provides an abstraction layer from which virtual entities such as virtual servers 71, virtual storage 72, virtual networks including virtual private networks 73, virtual applications and operating systems 74, and virtual clients 75 can be provided.

[0054] By way of example, the management layer 80 may provide the following functionality: Resource provisioning 81 enables dynamic procurement of computing and other resources utilized to execute tasks within the cloud computing environment. Metering and pricing 82 enables cost tracking as resources are utilized within the cloud computing environment and billing or invoicing for the consumption of these resources. By way of example, these resources may include application software licenses. Security enables identification and verification of cloud consumers and tasks, as well as protection for data and other resources. User portal 83 provides consumers and system administrators with access to the cloud computing environment. Service level management 84 enables allocation and management of cloud computing resources so that requested service levels are met. Service level agreement (SLA) planning and fulfillment 85 enables advance arrangement and procurement of anticipated future cloud computing resources required in accordance with SLAs.

[0055] The workload layer 90 provides examples of functionality that can be leveraged in a cloud computing environment. Examples of workloads and functionality that can be provided from this layer include mapping and navigation 91, software development and lifecycle management 92, virtual classroom instruction delivery 93, data analytics processing 94, transaction processing 95, and mobile desktop 96.

[0056] Any of the nodes 50 in the computing environment 52, as well as the computing devices 54A-N, may be a DPS 10.

[0057] As described in more detail herein, it is contemplated that some or all of the operations in some of the method embodiments described herein may be performed in a different order, or not at all. Additionally, multiple operations may occur simultaneously or as part of a larger process.

[0058] (efficient bulk loading of multiple rows or partitions into a single target table) 2A is a block diagram illustrating a database transfer system 200. The database transfer system includes a source database 212 that can run on one or more cloud computing nodes 50 in a cloud computing environment 52. The one or more cloud computing nodes 50 may be configured with one or more DPSs 10. A user may wish to perform various operations on the source database 212 or on tables 214 of the source database (e.g., queries to retrieve various records that meet a particular set of criteria). These records that meet the criteria may then be transferred to a target table 240. The queries may utilize a standardized query language such as SQL.

[0059] As can be seen in FIG. 2A , target table 240 of target database system 230 may span multiple logical nodes (e.g., nodes 0 through 4 (222A.0 through 222A.1, 222B.2 through 222B.4)) (target nodes may be referred to collectively or representatively as 222). The nodes may then span multiple machines (e.g., machine 1 (220A) and machine 2 (220B) (collectively or representatively referred to as 220). Queries may be executed using database accelerator 232, which retrieves data from source database table 214 in multiple data streams 215. In some embodiments, the system on which source database 212 executes may separate or demultiplex rows of source database 212.

[0060] In some embodiments, the row separation / demuxing is performed by the database accelerator 232, as described herein. In some embodiments (see also the block diagram of FIG. 4 , which illustrates the transfer of database rows to nodes), all or part of the separation / demuxing is performed by the source database system 210, resulting in multiple data streams 215, 410.1, 410.2, 410.3 (the latter collectively or representatively referred to as 410). In this case, row separation may be achieved using a variety of techniques. Common to all is that an UNLOAD (or EXPORT) utility 405 is initiated on the source database system 210 to extract data from relational source database tables 214 within the system 210. The UNLOAD utility in this case is a component for populating the multiple data streams 215, 410 with data.

[0061] Database systems typically provide such utilities for LOADING and UNLOADING (or EXPORTING) data from tables. The utilities directly access data pages in the system, bypassing all layers in the database engine that handle relational processing of the data. Therefore, such utilities may operate on raw data storage to provide the best possible performance. A single execution of the UNLOAD utility 405 provides only a single data stream. Execution of the utility is often configurable so that only a subset of the data is unloaded and written to the data stream. This can be exploited as follows: For every logical node in the target database system, a) determine the unload criteria that identify all rows going to that node, and b) initiate the UNLOAD utility with the unload criteria. The result is a strict 1:1 mapping between the UNLOAD utility running on the source database system 210 and the logical node 222 in the target database system. Orchestration and correlation may be handled by the database accelerator component 232. Although this approach requires the execution of multiple such utilities (which consumes resources and may also imply synchronization between utilities, since they all scan the same data (even if they extract a discontinuous subset of rows)), the UNLOAD utility has the advantage of being able to perform the task with no or only minor changes to the utilities.

[0062] Running multiple UNLOAD utilities 405 scanning the same data in the source database system 210 can be avoided if the utility 405 itself does not apply filters to extract data, but rather separates rows into different data streams 215, 410. A novel feature is that the UNLOAD utility 405: 1) opens “n” data streams 410, one for each node 22 in the target database system 230; and 2) scans all rows of a table in the source database system 210. For each row, the system a) reads the row, b) applies criteria to determine the row's node 222 in the target database system 230, and c) writes the row to the specific data stream for the target node 222. This approach results in a significantly smaller memory footprint than running multiple UNLOAD utilities. Each utility determines the target node 222 for each row. Therefore, instead of filtering rows that use that target node 222 and discarding all others, each row is immediately consumed and written to a different data stream 215. The multiple data streams 215 (created via the approaches described above) may be forwarded to the database accelerator 232 and then forwarded to the target database 240 system in separate batches 420.1, 420.2, 420.3 (collectively or collectively referred to as 420). They may also be forwarded in partitions (FIG. 2B), which can be thought of as batches of rows. A partition may be divided into further batches, as indicated by partition IDs 42 (255A, 255B). Separate connections may be used accordingly (one connection for each data stream 215, 410).

[0063] In some embodiments, multiple data streams 215 are combined into a single data stream, but the granularity here may be in blocks or batches of rows 420 rather than individual rows. In this case, a process (such as a procedure stored in the database accelerator, which starts the UNLOAD utility 405 and reads from pipes to which various utilities and procedures are writing) creates batches 420. In this approach, each batch 420 may have a header that indicates which target node 222 the batch 420 is intended for. In this way, the database accelerator 232 server can receive a single data stream and require only a single TCP / IP connection. However, by simply looking at the header of each batch and passing the complete batch to that target node 222, rows can be very easily separated when sending them to the target node 222.

[0064] In some embodiments (not shown), demultiplexing of data on source database system 210 provides another advantage: data stream 215 can be sent directly to target node 222, thus bypassing the physical machine on which database accelerator 232 server is running. Thus, the computational overhead on that machine is reduced to zero (except for some minor organization that may be necessary to coordinate the initial connection establishment from the procedures stored in database accelerator 232 to target node 222).

[0065] 2A, database accelerator 232 resides on the same machine 220A as two of the nodes (node ​​0 (222A.0) and node 1 (222A.1)). However, the database accelerator need not share machine 220A with any of the nodes 222 and may run on a separate machine 220A.

[0066] A source database table 214 may be divided into individual partitions 216, each of which acts as a unit subjected to database actions. Partitioning is a database process in which very large tables are divided into multiple smaller pieces. By dividing a large table into smaller individual tables, queries that access only a portion of the data can run faster because there is less data to scan. The primary purpose of partitioning is to aid in the maintenance of large tables and to improve the overall response time of data reads and loads for certain SQL operations.

[0067] In some situations where a query is required, a user can use the SQL INSERT statement to insert new database table records into a table. A basic INSERT statement may be of the form: TIFF0007795267000001.tif17147This contains the value to be inserted into the specified column of the table.

[0068] An INSERT INTO SELECT statement requiring that the data types of the source and target tables match may be of the form: TIFF0007795267000002.tif16141 (Copy all (matching) columns from source table 214 to target table 240) Alternatively, it may be in the following format: TIFF0007795267000003.tif25141 (Copy only some columns from source table 214 to target table 240)

[0069] SQL requests may be directed to or routed to a database accelerator 232. The database accelerator 232 may be used to attempt to make SQL requests execute faster and / or more efficiently. However, one potential problem is that a single INSERT statement may be used on each logical node 222 of the target database system 240 to process a single partition 216 of the source database table 214. Even if a solution were provided in which a single INSERT statement is used for each physical machine 220 of the target database system 230, such a solution would still use separate INSERT statements for different partitions in the source table.

[0070] Each INSERT statement incurs overhead not only on the database accelerator 232 but also on the target database system 230. Such overhead includes, for example, establishing an SQL connection over which the statement is executed, using named pipes for the statement to efficiently transfer data from one process to another (thereby avoiding expensive data conversion steps between different formats), starting the statement, etc. If the amount of data processed by a single INSERT statement is relatively small, the overhead of all these steps can account for a significant amount of the overall time to batch load the data.

[0071] It would be advantageous to reduce the overhead associated with executing a single INSERT statement to insert a portion / batch of data into a table in the target table 214. The overhead reduction may be achieved based on each batch of data originating from a particular partition 216 in the source table 214, with each batch possibly being further decomposed / segregated for individual logical nodes 222 of the target table 240 system. Thus, each batch or each row within a batch may have a partition identifier for its associated source table partition 216.

[0072] In database accelerator 232, the identifier may be stored in a dedicated column in target table 240, meaning that the identifier becomes an integral part of the stored row. In older versions of database accelerator 232, the partition ID is provided to the target database system in an SQL statement such as: TIFF0007795267000004.tif25141

[0073] This allows a literal in the INSERT statement to specify the partition ID value. However, such a literal cannot be changed during the execution of the SQL statement. Furthermore, it is possible to specify the value of the partition ID column in the definition of the foreign table itself. Thus, this value is also immutable for the duration of the INSERT statement.

[0074] Various embodiments allow for a different value to be injected into the partition ID column for a currently executing INSERT statement. In a first embodiment, the data stream 215 from which the foreign table is read is modified. Thus, the INSERT statement becomes: TIFF0007795267000005.tif25147 In this embodiment, <partition-id>There is no representation for it, nor is it present in the specification of the foreign (source) table. No special handling is defined for columns containing the partition ID column; the target database system treats it like any other column. Rows in data stream 215 must actually provide a value for this column. Therefore, each row must be modified in transit to include the partition ID value (e.g., as the first value in the row), followed by the value of the row as it existed on the source database system.

[0075] One problem with this approach is that the database accelerator 232 must inject a partition ID value for each row in the data stream 215. While this allows for numeric adjustment and therefore processing of rows from different partitions, it adds significant overhead to the database accelerator 232, thereby reducing throughput.

[0076] In a second embodiment, instead of using a literal in the SQL statement to specify the partition ID, a parameter marker (or other mechanism) may be used to specify a shared memory location 234. The shared memory location 234 is written by the database accelerator 232 and read by the target database system 230. The following query illustrates such an approach: TIFF0007795267000006.tif26147 Using a similar technique, it is also possible to include parameter markers / shared memory locations 234 in the specification of the external (source) table 214 .

[0077] After a batch of rows for a partition ID has been processed and the next batch is started, the value of the parameter marker (or the value in the shared memory location 234) may be modified. The target database system 230 then picks up the modified value and uses it thereafter. The advantage of this approach is that the data stream 215 originating from the source database system 210 does not need to be manipulated for each row. However, this approach requires synchronization and a dedicated synchronization mechanism. The following timeline of events describes this synchronization:

[0078] First, database accelerator 232 writes a batch of rows, the last batch for a particular partition ID, to the named pipe. Next, database accelerator 232 writes the new partition ID to a parameter marker / shared memory location. While FIG. 2A shows database accelerator 232 communicating directly with node 222, in some embodiments, target database server 236 reads data from the named pipe and processes the data using a known partition ID value. Target database server 236 can detect the change to the partition ID and store the changed partition ID for all data subsequently read from the named pipe. It is useful to use a dedicated synchronization mechanism to synchronize the exact point in time when the new partition ID is used and applied with the rows in data stream 215. In this configuration, database accelerator 232 offloads queries to target database server 232 to perform SQL requests, as described above. This offloading includes receiving a query from the source database 212, rewriting the query statement to match the data layout (e.g., including the partition ID) on the target database server 236, forwarding the query to the target database server 236, receiving the query results, and forwarding the query results to the source database 212.

[0079] In a third embodiment, run-length encoding of the partition ID takes advantage of the fact that batches of rows are being processed. The partition ID is specified in the data stream 215 itself, but only once for each batch. As with run-length encoding (RLE), each batch is prefixed with the partition ID and the number of rows that the partition ID applies to.

[0080] FIG. 2B is a data structure diagram illustrating the structure of data stream 250, according to some embodiments. In this embodiment, a first partition ID 255A and row number 260A are located at the beginning of a first section of the data stream, followed by a specified number of rows 265A. Similarly, a second partition ID 255B and row number 260B are located at the beginning of a second section of the data stream, followed by a specified number of rows 265B. As shown in FIG. 2B, second partition ID 255B has the same value as first partition ID 255A, but operates on a separate BOD. Finally, as shown, a third partition ID 255C and row number 260C are located at the beginning of a third section of the data stream, followed by a specified number of rows 265C. Because a partition 214 in source table 214 may consist of more rows than a single batch processed on logical node 222 of target database system 230, multiple batches (as BODs) can use the same partition ID.

[0081] The INSERT statement in this embodiment is a simple one that requires the partition ID details in the specification of the foreign (source) table 214 itself. TIFF0007795267000007.tif25147

[0082] The processing of the data stream 215 read by the target database system 230 in this illustrative example is as follows: read the first 4 bytes containing the partition ID, store the partition ID, read the number of rows "n" that the partition ID falls into, read "n" rows from the data stream, process them normally using the stored partition ID, and return to the first step (i.e., read the next partition ID to use). In this embodiment, no synchronization between different threads / processes / applications is required and is inherently synchronized.

[0083] Various embodiments described herein may combine, where possible, the treatment of data as an opaque byte (data) stream 215 while augmenting each row in the data stream 215 with its explicit partition number. This may be done in a way that rows from multiple different partitions 214 in a source table 214 are processed together on a target table 214 via a single INSERT statement to reduce overhead.

[0084] 3 is a flowchart of an example process 300 for performing an INSERT operation according to some implementations. At operation 305, database accelerator 232 receives a single query language INSERT statement associated with a source partition of source table 214 in source database system 210, identifying data blocks spanning multiple partitions, each having a partition ID to be inserted into the target table. Here, the INSERT statement does not include a literal partition ID. Database accelerator 232 translates the INSERT statement for the target database system to inject partition information using one of the three methods described above.

[0085] At operation 310, database accelerator 232 receives partition ID information from source database 212. Then, at operation 315, in some embodiments, database accelerator 232, in response to receiving the INSERT statement, injects the partition ID into a column of a row in the partition data stream before inserting the row into the target table. Operation 315 may utilize a shared memory location 234 that holds the particular partition ID for the row that database accelerator 232 is currently operating on. At operation 320, database accelerator 232 sends the partition with the injected partition ID to the target database.

[0086] (Technical Applications) One or more embodiments disclosed herein therefore provide improvements in computer technology, such as improving operations in database systems, thereby enabling more efficient and effective utilization of computer resources in database operations.

[0087] (Computer-readable medium) The present invention may be a system, method, or computer-readable medium, or a combination thereof, integrated at any possible level of technical detail. A computer program product may include a computer-readable storage medium having stored thereon computer-readable program instructions for causing a processor to perform aspects of the present invention.

[0088] A computer-readable storage medium may be a tangible device capable of retaining and storing instructions for use by an instruction execution device. The computer-readable storage medium may be, by way of example, an electronic storage device, a magnetic storage device, an optical storage device, an electromagnetic storage device, a semiconductor storage device, or a suitable combination thereof. More specific examples of computer-readable storage media include portable computer diskettes, hard disks, RAM, ROM, EPROM (or flash memory), SRAM, CD-ROMs, DVDs, memory sticks, floppy disks, mechanically encoded devices having instructions recorded on punch cards or ridge-in-groove structures, or the like, and suitable combinations thereof. As used herein, a computer-readable storage medium should not be construed as a transitory signal per se, such as an electric wave or other freely propagating electromagnetic wave, an electromagnetic wave propagating through a waveguide or other transmission medium (e.g., light pulses passing through a fiber optic cable), or an electrical signal transmitted over a wire.

[0089] The computer-readable program instructions described herein can be downloaded from a computer-readable storage medium to each computer / processing device. Alternatively, they can be downloaded to an external computer or external storage device via a network (e.g., the Internet, a LAN, a WAN, or a wireless network, or a combination thereof). The network can include copper transmission cables, optical fiber transmissions, wireless transmissions, routers, firewalls, switches, gateway computers, or edge servers, or a combination thereof. A network adapter card or network interface within each computer / processing device receives the computer-readable program instructions from the network and transfers the computer-readable program instructions to a computer-readable storage medium in the respective computer / processing device for storage.

[0090] The computer-readable program instructions for carrying out the operations of the present invention can be either assembler instructions, instruction set architecture (ISA) instructions, machine instructions, machine-dependent instructions, microcode, firmware instructions, state setting data, configuration data for integrated circuits, or source or object code written in any combination of one or more programming languages, including object-oriented programming languages ​​such as Smalltalk and C++, and procedural programming languages ​​such as the "C" programming language and similar programming languages. The computer-readable program instructions can execute entirely on the user's computer as a stand-alone software package, partially on the user's computer, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the latter case, the remote computer may be connected to the user's computer via any type of network, including a LAN or WAN, or may be connected to an external computer (e.g., via the Internet using an Internet Service Provider). In some embodiments, electronic circuitry, including, for example, programmable logic circuits, field programmable gate arrays (FPGAs), programmable logic arrays (PLAs), can execute computer-readable program instructions by utilizing state information of the computer-readable program instructions to customize the electronic circuitry for carrying out aspects of the present invention.

[0091] Aspects of the present invention are described herein with reference to flowchart and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. Each block of the flowchart and / or block diagrams, and combinations of blocks in the flowchart and / or block diagrams, can be implemented by computer-readable program instructions.

[0092] These computer-readable program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, or other programmable data processing apparatus to produce a machine, whereby the instructions, executed by the processor of such computer or other programmable data processing apparatus, create means for performing the functions / acts specified in one or more blocks of the flowcharts and / or block diagrams. These computer-readable program instructions can also be stored on a computer-readable storage medium that can instruct a computer, programmable data processing apparatus, or other device, or combination thereof, to function in a particular manner. The computer-readable storage medium having the instructions stored thereon thereby constitutes an article of manufacture including instructions for performing aspects of the functions / acts specified in one or more blocks of the flowcharts and / or block diagrams.

[0093] Computer-readable program instructions may also be loaded into a computer, other programmable device, or other device and a series of operational steps executed on the computer, other programmable device, or other device to create a computer-implemented process, whereby the instructions executing on the computer, other programmable device, or other device perform the functions / operations identified in one or more blocks in the flowcharts and / or block diagrams.

[0094] The flowcharts and block diagrams in the figures illustrate the architecture, functionality, and operation of possible implementations of systems, methods, and computer program products according to various embodiments of the present invention. In this regard, each block in a flowchart or block diagram may represent a module, segment, or portion of instructions, including one or more executable instructions for performing specific logical functions. In some other implementations, the functions shown in the blocks may be executed in an order different from that shown in the figures. For example, two blocks shown in succession may in fact be executed substantially concurrently, or the blocks may be executed in reverse order, depending on the functionality involved. Note that each block in the block diagrams and / or flowcharts, and combinations of blocks in the block diagrams and / or flowcharts, may be implemented by a dedicated hardware-based system that performs specific functions or operations or executes a combination of dedicated hardware and computer instructions.

Claims

1. 1. A method for processing inserts into a target table of a target database system, comprising: receiving a single INSERT statement associated with a source partition of a source table in a source database system requesting insertion of a data block (BOD) having a partition identifier (PID) into the target table, the INSERT statement not including a literal PID; receiving the BOD via a data stream from the source table in response to receiving the INSERT statement; inserting the BOD into the target table; The method, wherein the PID is obtained from the source database system separately from the INSERT statement.

2. The PID of each row of the BOD is inserted into a column of the row after receiving the INSERT statement and before inserting the row into the target table. The method of claim 1.

3. The BOD is organized into a plurality of rows, each row is modified to include a partition ID value, and the partition ID value is injected into the data stream. The method of claim 2.

4. Specifying a shared memory location containing the current PID; writing the current PID to the shared memory location, the current PID being readable by the target database system. The method of claim 1.

5. the PIDs are specified in the data stream itself such that each batch in the BOD is preceded by a batch PID and the number of rows to which that batch PID applies; The method comprises: reading and storing the batch PID and the row quantity; reading n rows corresponding to the number of rows from the data stream and processing each read row using the batch PID from which it was read; The method of claim 1 further comprising:

6. the data stream comprises a plurality of data streams demultiplexed on the source database system; The method of claim 1.

7. and performing the demultiplexing of rows in the BOD by the source database system using an unload utility initiated on the source database system to extract the BOD from the source table and populate the plurality of data streams. The method of claim 6.

8. determining, for all logical nodes of the target database system, unload criteria that identify all rows that will enter each logical node; and initiating the unload utility using the unload criteria. The method of claim 7.

9. further comprising recombining the multiple data streams into a single data stream; In the single data stream, the granularity is a block or batch of rows rather than a single row; Each batch has a header that indicates which target node the block or batch is for. The method of claim 6.

10. and further comprising: transmitting the data stream obtained by demultiplexing the data on the source database system directly to a target node on the target database system. The method of claim 6.

11. 1. A system for processing inserts into a target table of a target database system, comprising: Memory and a processor, the processor comprising: receiving a single INSERT statement associated with a source partition of a source table in a source database system requesting insertion of a data block (BOD) having a partition identifier (PID) into the target table, the INSERT statement not including a literal PID; receiving the BOD via a data stream from the source table in response to receiving the INSERT statement; inserting the BOD into the target table; obtaining the PID from the source database system separately from the INSERT statement. system.

12. the processor is further configured to insert a PID of each row of the BOD into a column of each row after receiving the INSERT statement and before inserting the row into the target table. The system of claim 11.

13. the BOD is organized into a plurality of rows, and the processor is configured to modify each row to include a partition ID value, which partition ID value is injected into the data stream; The system of claim 12.

14. The processor: Specifying a shared memory location containing the current PID; writing the current PID to the shared memory location, wherein the current PID is readable by the target database system. The system of claim 11.

15. the PIDs are specified in the data stream itself such that each batch in the BOD is preceded by a batch PID and the number of rows to which that batch PID applies; The processor: reading and storing the batch PID and the row quantity; and reading n rows from the data stream corresponding to the number of rows, and processing each read row using the batch PID from which it was read. The system of claim 11.

16. the data stream comprises a plurality of data streams demultiplexed on the source database system; The system of claim 11.

17. A processor of the source database system different from the processor is configured to extract the BOD from the source table and demultiplex rows in the BOD using an unload utility initiated on the source database system to populate the plurality of data streams.

17. The system of claim 16.

18. a processor of the source database system, determining, for all logical nodes of the target database system, unload criteria that identify all rows that will enter each logical node; and initiating the unload utility using the unload criteria.

20. The system of claim 17.

19. 1. A computer program for processing inserts into a target table of a target database system, the computer program comprising: receiving a single INSERT statement associated with a source partition of a source table in a source database system requesting insertion of a data block (BOD) having a partition identifier (PID) into the target table, the INSERT statement not including a literal PID; receiving the BOD via a data stream from the source table in response to receiving the INSERT statement; inserting the BOD into the target table; obtaining the PID from the source database system separately from the INSERT statement; Computer program.

20. Further, causing the processor to insert a PID of each row of the BOD into a column of each row after receiving the INSERT statement and before inserting each row into the target table.

20. A computer program according to claim 19.

Images