Distributed file merging

Abstract

Method for operating a distributed database (100), wherein the method comprises: Execution of an instruction set (118) for stream-oriented merging of uninterrupted, continuous data streams from multiple sensors of a sensor network to merge a source element with a target element, wherein the execution is performed by multiple data processing nodes (102, 102', 102'', 102''') in the distributed database, wherein the multiple data processing nodes serve to convert storage and computation for a database management system (114), wherein the multiple data processing nodes are connected for exchanging data over a network, and the database management system (114) for managing at least one source file (110) and one target file (112), wherein the target file (112) is stored distributed across the multiple data processing nodes (102, 102', 102'', 102'''), the source file has multiple source elements, the target file has multiple target elements, wherein the execution comprises: Check (200) for a match condition (116) between a source element selected from the multiple source elements and one of the multiple target elements; Computing (202) an instruction set (118) of a merged instruction list, wherein the instruction list comprises a list of instructions for stream-oriented merging of uninterrupted, continuous data streams of the source element with the target element using the matching condition (116); and Repeated execution (204) of the instruction set (118) for stream-oriented merging (204) of uninterrupted, continuous data streams of the source element with the target element before checking (200) the match condition (116) between a next source element selected from several source elements and one of the several target elements, where the repeated execution (204) of the instruction set (118) further includes: Reducing the amount of computing resources required to merge the source file (110) with the target file (112), and based on data from one sensor in the sensor network, adapting the data from another sensor in the sensor network, where the distributed database (100) is a distributed relational database, the source file (110) is a source table, the source table is at least one continuous data stream provided by a sensor of the sensor network, the target file (112) is a target table, the multiple source elements are multiple source rows, the multiple target elements are multiple target rows, the source element is a source row, and the instruction set (118) is an instruction column.

Description

BACKGROUND

[0001] The present invention relates to the field of distributed databases and, in particular, to distributed data set merging.

[0002] The document US 2012 / 0 265 890 A1 describes a method, an apparatus and a program product that transfer data between a host element and a target element in a parallel computing system.

[0003] Document US 8 996 463 B2 describes database systems and methods that implement a data aggregation framework. The framework can be configured to optimize aggregation operations across non-relational distributed databases, such as data access, retrieval, writing, indexing, etc.

[0004] The document US 2015 / 0 186 490 A1 describes an architecture that offers the possibility to automatically (e.g. dynamically) reorganize (repartition) an existing partition by splitting (splitting) or recombining (merging) logical databases.

[0005] Merging files in distributed database systems can be computationally expensive or involve moving large amounts of data between different computing nodes in the distributed database system. SUMMARY

[0006] In one aspect of the invention, a method for operating a distributed database is provided. The distributed database comprises a plurality of computing nodes for implementing storage and computing for a database management system. The plurality of computing nodes are connected via a network to exchange data. The distributed database further comprises a database management system for managing at least one source file and one target file. The target file is stored distributed across the plurality of computing nodes. The source file comprises a plurality of source elements. The target file comprises a plurality of target elements.

[0007] The method comprises repeatedly using the plurality of compute nodes to check for a match condition between a source element selected from the plurality of source elements and one of the plurality of target elements. The method further comprises repeatedly using the plurality of compute nodes to calculate an instruction set from a merged instruction list. The instruction list comprises a list of instructions for merging the source element with the target element using the match condition. The merged instruction list may be a large number of instruction sets or a collection of instruction sets used to merge the source file with the target file.The method further comprises repeatedly using the plurality of computing nodes to execute the set of instructions to merge the source item with the target file before checking for the match condition between a next source item selected from the plurality of source items and one of the plurality of target items.

[0008] This embodiment potentially has the advantage that the entire merged instruction list is not computed before executing the individual instruction sets. This can reduce the amount of machine or processor resources required to merge the source file with the target file.

[0009] In a further aspect, the invention provides a distributed database arranged or programmed to implement the above-mentioned method.

[0010] In a further aspect, the invention provides a computer program product for operating a distributed database according to the above-mentioned method.

[0011] In another aspect, the invention may provide a method for merging a target data object and a source data object stored in multiple compute nodes. The target data object includes first data records distributed across the multiple compute nodes. The source data object includes second data records distributed across the multiple compute nodes. The method includes executing a join instruction to merge the source data object with the target data object. Executing the join instruction includes repeatedly checking for a match condition between a second record selected from the second records and one of the first records. Executing the join instruction further includes calculating an instruction list for merging the second record with one of the first records using the match condition.The method further comprises executing the command list before calculating a next command list. The advantages of this embodiment have been explained previously.

[0012] In another aspect, the invention provides a method for merging a data stream with a static data object. The static data object is stored on multiple compute nodes. The static data object comprises multiple data records distributed across the multiple compute nodes. The method comprises executing a merge instruction to merge the data stream with the static data object. Executing the merge instruction comprises repeatedly checking for a match condition between a portion of the data stream and a selected record selected from the static data object. The method further comprises repeatedly computing an instruction list for merging the portion of the data stream and the selected record using the match condition.The method further includes repeatedly executing the command list before calculating a next command list. This embodiment may have the advantage of providing an efficient means for merging a static file with a data stream. This embodiment may also have the benefit that the same system for merging two static files can be used to merge a data stream with a static file. BRIEF DESCRIPTION OF THE MULTIPLE DRAWING VIEWS

[0013] Embodiments of the invention are explained in more detail below by way of example only, with reference to the drawings in which: Fig. 1 illustrates an example of a distributed database. Fig. 2 a method for operating the distributed database of Fig. 1 represents. Fig.3 a method for calculating summary statistics when carrying out the method of Fig. 2 represents. Fig. 4 integrating a data stream into the distributed database of Fig. 1 illustrates. DETAILED DESCRIPTION

[0014] The descriptions of the various embodiments of the present invention have been presented for purposes of illustration, but are not intended to be exhaustive or limiting of the disclosed embodiments. Many modifications and variations will be apparent to those skilled in the art without departing from the scope and spirit of the described embodiments. The terminology used herein has been chosen to best explain the principles of the embodiments, practical application, or technical improvement over existing technologies in the marketplace, or to enable others skilled in the art to understand the disclosed embodiments.

[0015] Embodiments of the invention may provide a method further comprising deleting the instruction set before calculating a next match condition between the next source element selected from the plurality of source elements and one of the plurality of target elements. This example may have the advantage of reducing the computational burden of merging the source file and the target file. This embodiment may also have the advantage of executing the instruction set before the source file or the target file may be modified. It is executed immediately.

[0016] In another embodiment, each of the multiple nodes has a main memory. The instruction set is computed by a selected node of the multiple nodes. The instruction set remains in the main memory of the selected node. This embodiment may have the advantage that the instruction set does not need to be transferred between the multiple computing nodes before it is executed. This may increase the speed at which the instruction set is executed.

[0017] In another embodiment, the instruction set includes insert operations, update operations, and / or delete operations. The instruction set further includes a storage location of a designated data item selected from the source item and the destination item. The instruction set is configured to be applied to the designated data item. This embodiment potentially has the advantage that the instruction set can be used to merge two data items.

[0018] In another embodiment, the command set further includes attribute values ​​to be entered for the target data element when merging the source element with the target file. This embodiment can be advantageous because the attribute or value to be entered can be copied directly using the command set.

[0019] In another embodiment, the method further comprises separately counting the number of inserts, updates, and deletes performed for a complete merge command list. This embodiment may have the advantage of providing accurate statistics on the operations performed to merge the source file and the target file.

[0020] In another embodiment, the distributed database is a distributed shared-nothing database. This embodiment may be advantageous because the distributed nature of the multiple computing nodes and the method may provide a particularly efficient method for merging a source file and a target file stored in a distributed shared-nothing database.

[0021] In another embodiment, the distributed database is a distributed relational database. The source file is a source table. The target file is a target table. The plurality of source elements is a plurality of source rows. The plurality of target elements is a plurality of target rows. The source element is a source row. The command set is a command column. This embodiment may be advantageous because the method described here can be particularly effective when merging source files and target files of relational databases.

[0022] In another embodiment, the source file and / or the target file are modified during execution of the instruction set. This embodiment may be advantageous because it can provide a means for efficiently updating the source file and / or the target file.

[0023] In another embodiment, the source file contains multiple copies of the source element, and / or the target file contains multiple copies of the target element. This embodiment may be advantageous even if the data is stored in multiple locations on different computing nodes. The method is executed on several or all of the multiple computing nodes, while the method can still be executed quickly and efficiently.

[0024] In another embodiment, repeated execution of the instruction set results in a link set distributed across the set of computing nodes. In this embodiment, the source file and the target file are replaced or copied into a link set. This method can provide a particularly efficient method for generating the link set.

[0025] In another embodiment, the link set may be a link table. The link table may, for example, be a table in a distributed relational database.

[0026] In another embodiment, the merged command list is a list of commands for merging the source file into the target file. This method may be advantageous because it can provide a particularly efficient means for merging the source file into the target file.

[0027] In another embodiment, the source file or the target file is a static file. This embodiment may be advantageous if the data being merged includes a portion that is stored statically across multiple compute nodes.

[0028] In another embodiment, the source file is stored distributed across the multiple compute nodes. This embodiment may be advantageous for merging two files that are both stored across multiple compute nodes. The multiple compute nodes may be used to rapidly perform the merge in parallel.

[0029] The source file is at least one data stream. This can be advantageous because the data stream may contain data from several different sources. When the data is merged into the target file, which is static, the multiple data processing nodes can be used to merge the at least one data stream in parallel and to store it.

[0030] In another embodiment, the instructions further cause the processors to clear the instruction set before calculating a next match condition between a next source element selected from the plurality of source elements and one of the plurality of target elements. The advantages of this embodiment were previously discussed.

[0031] In another embodiment, each of the multiple computing nodes has a main memory. The instruction set is computed by a selected node of the multiple nodes. The instruction set remains in the main memory of the selected node. The advantages of this embodiment were previously explained.

[0032] The data stream is provided by a sensor network. The sensor network used here can contain data from multiple sensors. This potentially has the advantage of effectively merging the data stream with the target file.

[0033] In another embodiment, the method further comprises deleting the command list before calculating a next match condition between a next second data record selected from the second data records and one of the plurality of target elements. The advantages of this embodiment were previously explained.

[0034] Fig.1 shows an example of a distributed database 100. The distributed database includes computing nodes 102, 102', 102'', and 102'''. The various computing nodes 102, 102', 102'', 102''' are connected by a network connection 104. The network connection 104 enables the computing nodes 102, 102', 102'', 102''' to exchange data. Each computing node 102, 102', 102'', 102''' includes a processor 106 and a memory 108. The memory 108 can be either main memory or long-term storage such as a hard disk drive or flash memory. Computing node 102 has a portion of source file 110 and a portion of a destination file 112 in memory 108. Computing node 102' contains another portion of source file 110 and another portion of destination file 112' in its memory.

[0035] The data processing node 102'' contains in its memory 108 another section of the source file 110'' and another section of the target file 112''. The data processing node 102'' contains in its memory 108 yet another section of the source file 110'' and yet another section of the target file 112''. The sections of the target files 112, 112', 112'', 102''' can, for example, contain data in the form of one or more target elements. The sections of the source files 110, 110', 110'', 110''' can, for example, contain data in the form of one or more source data elements.

[0036] Computing node 102''' is illustrated as including, in its memory 108, an implementation 114 of a database management system. The implementation of database management system 114 enables computing node 102''' to control the database system formed by computing nodes 102, 102', 102'', 102'''. In other examples, database management system 114 may be implemented in a separate computer or computing node and, in certain cases, may be separate from distributed database 100.

[0037] For example, the database management system 114 may receive an SQL or other command instructing the database management system to concatenate the source file and the destination file. The database management system 114 may then issue commands to the various compute nodes 102, 102', 102'', or 102''' to concatenate the various sections of the source and destination files. The compute node 102 displays in its memory 108 a match condition 116 identified for the section of the source file 110 and the section of the destination file 112. This may result in the processor 106 calculating an instruction set 118 that enables the processor 106 to merge the section of the source file 110 and the section of the destination file 112.

[0038] Fig. 2 shows a flowchart illustrating an example of a method for operating the distributed database of Fig. 1. The computing nodes 102, 102', 102'', 102''' first check for a match condition 116 between a source element 110 and a target element 112 in step 200. Then, the processor 106 of the computing node 102 computes an instruction set 118 of a merged instruction list in step 202. The instruction list includes a list of instructions for merging the source element 110 with the target element 112. Subsequently, the processor 106 executes the instruction list 118 in step 204 to merge the source element 110 with the target file 112. This occurs before checking for the match condition between the next source element selected from the plurality of source elements and one of the plurality of target elements.

[0039] The command is then executed spontaneously, without waiting until the complete merged command list has been calculated. After step 204, the method proceeds to step 206. This is a question box asking "Are all source elements selected?" If the answer is yes, the method proceeds to step 208, and the method ends. If the answer is no, the method returns to step 200, and the next source element is checked for a match condition.

[0040] The present invention may be a system, a method, and / or a computer program product. The computer program product may include a computer-readable storage medium (or media) having computer-readable program instructions embodied thereon that cause a processor to perform aspects of the present invention.

[0041] The computer-readable storage medium may be a tangible device capable of holding and storing instructions for use by an instruction execution device. The computer-readable storage medium may be, for example, but is not limited to, an electronic storage device, a magnetic storage device, an optical storage device, an electromagnetic storage device, a semiconductor storage device, or any suitable combination of the foregoing.A non-exhaustive list of more specific examples of the computer-readable storage medium includes the following: a portable computer diskette, a hard disk, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), static random access memory (SRAM), portable compact disk read-only memory (CD-ROM), digital versatile disk (DVD), memory stick, floppy disk, a mechanically encoded device such as punched cards or raised structures in a groove in which instructions are recorded, or any suitable combination of the foregoing.A computer-readable storage medium used here should not be interpreted as containing per se transient signals such as radio waves or other freely propagating electromagnetic waves, electromagnetic waves propagating through a waveguide or other transmission media (e.g., light pulses traveling through a fiber optic cable), or electrical signals transmitted through a wire.

[0042] Computer-readable program instructions described herein may be downloaded from a computer-readable storage medium to respective computing / processing units or across a network, e.g., the Internet, a local area network, a wide area network, and / or a wireless network to an external computer or external storage unit. The network may include copper transmission cables, fiber optic cables, wireless transmissions, routers, firewalls, switches, gateway computers, and / or edge servers. A network adapter card or network interface in each computing / processing unit receives computer-readable program instructions from the network and forwards the computer-readable program instructions for storage in a computer-readable storage medium in the respective computing / processing unit.

[0043] Computer-readable program instructions for performing operations of the present invention may be assembly language instructions, instruction set architecture (ISA) instructions, machine instructions, machine-dependent instructions, microcode, firmware instructions, state-setting data, or source or object code written in any combination of one or more programming languages, including an object-oriented programming language such as Smalltalk, C++, or the like, and conventional procedural programming languages ​​such as the "C" programming language or similar programming languages. The computer-readable program instructions may be executed only on a user's computer, partially on a user's computer, as a standalone software package, partially on a user's computer and partially on a remote computer, or only on the remote computer or server.

[0044] In the latter scenario, the remote computer may be connected to the user's computer through any type of network, including a local area network (LAN) or a wide area network (WAN), or the connection may be made to an external computer (e.g., over the Internet using an Internet service provider). In some embodiments, electronic circuitry, including, for example, programmable logic circuitry, field programmable gate arrays (FPGAs), or programmable logic arrays (PLAs), may execute the computer-readable program instructions by utilizing state information of the computer-readable program instructions to personalize the electronic circuitry to carry out aspects of the present invention.

[0045] Aspects of the present invention are described herein with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be appreciated that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, may be implemented by computer-readable program instructions.

[0046] These computer-readable program instructions may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing apparatus to form a machine such that instructions executing via the processor of the computer or other programmable data processing apparatus generate means for implementing the functions / effects specified in the flowchart and / or block diagram block or blocks.These computer-readable program instructions may also be stored in a computer-readable storage medium that can instruct a computer, other programmable data processing apparatus, and / or other devices to function in a particular manner, such that the computer-readable storage medium having instructions stored therein comprises an article of manufacture that includes instructions that implement aspects of the function / effect specified in the flowchart and / or block diagram block or blocks.

[0047] The computer-readable program instructions may also be loaded into a computer, other programmable data processing apparatus, or other device to cause a series of functional steps to be performed on the computer, other programmable data processing apparatus, or other device to produce a computer-implemented process such that the instructions executing on the computer, other programmable device, or other device implement the functions / effects specified in the block or blocks of the flowchart and / or block diagram.

[0048] The flowchart 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 the flowchart or block diagrams may represent a module, segment, or section of instructions comprising one or more executable instructions for implementing the specified logical function(s). In some alternative implementations, the functions specified in the block may not occur in the order indicated in the figures. For example, two blocks shown sequentially may actually execute substantially concurrently, or the blocks may occasionally execute in the reverse order depending on the functionality involved.It should also be noted that each block in the block diagram and / or flowchart illustrations, and combinations of blocks in the block diagram and / or flowchart illustrations, may be implemented by systems based on special purpose hardware that perform the specified functions or effects, or combinations of special purpose hardware and computer instructions.

[0049] The merge command list used here can also be described as a merge action table. The command set used here can also be referred to as a command column. Tables used here can refer to a source file or a target file, depending on the context. A column from a table used here can refer to a source element of the source file or a target element of the target file, depending on the context. The following discussion describes merging data from a relational database using a SQL MERGE command. However, the general procedure can be applied to other types of data storage and also to merging data streams with static data.

[0050] Synchronizing or merging different files is a task that occurs in some form in almost all information systems. In particular, merging changes made to a first file with a second file based on (1) the presence of a specific data object (e.g., row) in the second file and (2) possibly other conditions is a common task. In the SQL language, the MERGE statement was defined for this task in the context of relational databases. The following Code Listing 1 is an example of an SQL MERGE statement in the sense of IBM DB2 and others: a so-called target table (emp) is modified using a source table (SELECT * FROM worldwide_emp...).The source and target tables are compared using a match condition that checks whether each source row has a corresponding row in the target table. Modification steps (insert, update, or delete) are defined based on the match condition and other conditions. Code Listing 1: Example of a complex statement MERGE SQL MERGE INTO emp AS t USING (SELECT * FROM worldwide_emp WHERE country = 'GER') AS s ON t.id = s.id WHEN MATCHED AND s.isFired = 1 THEN DELETE WHEN MATCHED AND t.salary < s.salary THEN UPDATE SET t.salary = s.salary, t.bonus = s.bonus / 2 WHEN MATCHED THEN UPDATE SET t.bonus = 0 WHEN NOT MATCHED AND s.bonus > (SELECT AVG(salary) FROM emp) THEN INSERT (id, name, salary, bonus) VALUES (s.id, s.name, s.salary, s.bonus / 2) WHEN NOT MATCHED THEN INSERT (id, name, salary, bonus) VALUES (s.id, s.name, s.salary, s.bonus).

[0051] However, the general problem is not limited to relational databases. For simplicity, this document refers to managed data objects (elements of files) as "rows" and files as "tables," although the example presented could also be used in systems managing non-relational data. The same problem exists, for example, in highly distributed storage devices for non-SQL key / value data, where this functionality is not provided.

[0052] Furthermore, the role of the source table is taken over by a continuous data stream provided by a sensor. This requires a solution capable of processing the merge operation in a stream-oriented manner, thus generating an uninterrupted stream of insertion, update, and deletion actions. Furthermore, data stream systems also contain such requirements when merging uninterrupted data streams from multiple sensors. Sensors contain an available data point that adapts the data from another sensor. Therefore, the merge operations must consider all the different cases of inserting a new data point, modifying an existing data point, or removing further data under various conditions.

[0053] Finally, in the general case (not supported by the MERGE SQL statement), modifications in both directions might be required, thus modifying not only the target table but also the source table. Existing systems don't support this and are only capable of modifying the target table.

[0054] To manage very large tables, distributed databases distribute the rows of a table across many computing nodes. The rows can be distributed randomly or using a deterministic distribution function based on one or more columns of the table, where such columns are called distribution keys. In the former case, the location of a particular row is unknown. In the latter case, the position of a row can be calculated if the corresponding attributes of the data are known; otherwise, it must be assumed that it is located somewhere. Two examples can be distinguished: shared-nothing databases, where the rows are distributed randomly, whereas others allow multiple copies of a row to exist in different nodes.

[0055] Merging data (described above) is difficult in such systems for the following reasons: 1. To check the match condition, the position of the potentially matching target row (1) must be known and can (2) be in the same compute node as the source row or in a different one. 2. For efficient execution, it is desirable to use the position of the rows to avoid unnecessary data movement. 3. The data processing node where the match condition is checked may not be the same node that contains the rows that require manipulation (insert / update / delete). 4. Update actions of a merge operation can force a row to be moved to a new location. This happens when the location is deterministic and the update action changes a distribution key. In this case, the old version of the row must be deleted from the old location and inserted into the new location, thus avoiding data movement. It should also be noted that this requires that updating distribution keys in the first location is allowed. Some systems disallow this to avoid row movement during update operations. 5. Finally, it would be desirable to report the number of rows inserted, updated, or deleted individually in each table. This is not straightforward, especially in the case mentioned above, where an update involves insertions and deletions from different data processing nodes, since the nodes performing the data manipulation lack a complete overview. 6. In non-shared-nothing systems, multiple copies of a row can exist, and all of them must be manipulated. Existing systems don't currently support this.

[0056] Distributed shared-nothing databases are typically capable of performing distributed joins. As the following examples demonstrate, such systems are also capable of determining the ideal approach for performing a particular join.

[0057] External join: The joined tables are both deterministically distributed, and the join statement is based on the distribution keys of both tables. For equivalent joins, this guarantees that matching rows can only be located in the same partition (and thus in the same data processing node). The join can be computed locally without any data movement.

[0058] Send: If one of the tables is sufficiently small, it can be sent to all data processing nodes. The join can then be computed locally. This works for all join types and even with a sufficiently small window of uninterrupted data flow.

[0059] Distribute: If one of the tables is deterministically distributed and the join statement is based on the distribution keys, the rows of the other table can be distributed in the same way. After this distribution step, the requirements for an external equal join are met.

[0060] Double distribution: The rows of both tables can be deterministically distributed across all data processing nodes according to the column values ​​of the equivalent join statement. This creates a completely new partitioning spontaneously. After this distribution step, the requirements for an external equivalent join are met.

[0061] Centralized execution: For small row counts, all rows from both tables can be sent to a specific data processing node, which calculates the join locally. This can be considered a special case of double distribution. This works for all join types and even with a sufficiently small window of uninterrupted data flow.

[0062] Note that in all of these examples, except for an external join, rows are moved to other compute nodes. Therefore, a row processed in one compute node x during a join is potentially stored in another node.

[0063] An example of how to perform merge operations is based on distributed joins and subsequent optimized processing of the join results. The main results are that each row is processed only once and data manipulation is performed as locally as possible.

[0064] First, the match condition can be translated into an outer join between the tables. The actual match condition becomes the join condition. For each table in which a match is to be checked for, a non-deletable column is designated as a match indicator. In a typical SQL MERGE command, this is only the target table.

[0065] However, in the general case, matching could also occur with values ​​that are missing in the source table, as with the WHEN MATCHED BY SOURCE syntax extension of Microsoft SQL Server.

[0066] Furthermore, for each table in which modifications are to be made, the location of each row is provided as an additional special column. Again, with the usual SQL MERGE command, only the target table can be modified, but in the general case, this can be extended, and examples described here can support it. The location indicates at least which compute node the corresponding row is stored in; it could (and should) be more detailed, even including the exact memory address. Details vary between implementations; if the location is not deletable, the location can also be used as a match indicator column.

[0067] The join type is generally a full outer join. However, it can be narrowed down to a more effective join type depending on the specific join operation being performed. The influencing factors are whether both tables can be matched and which specific match clauses are used. Table 1 lists possible join reduction options. All of these join types have the common property of setting the match indicator columns to a NULL value if the corresponding table does not contain a match for the specified match condition. Table 1: The link types used for the match condition Supported Match Conformity clauses Link type all all complete external Link only target all left outer Link all only if there is agreement internal link all only if no right no Agreement exists Link exists all only if source left no there is no agreement Link exists

[0068] Thus, using the various methods for performing distributed joins highlighted above, the matching condition can be effectively calculated.

[0069] By running the example in Code Listing 1, which only supports matches with the target table and uses the two clauses WHEN MATCHED and WHEN NOT MATCHED, the following join query is generated: SELECT ..., t.storageLocation FROM emp AS t LEFT OUTER JOIN (SELECT * FROM worldwide_emp WHERE country = 'GER') AS s ON t.id = s.id

[0070] The storage location of the target table is provided as an additional field. Since it is non-deletable, it can be used as a match indicator. It can only contain a NULL value if no match was found in the left outer join.

[0071] The joining of the two tables explained above can be merely a basic requirement. It allows the match condition to be evaluated and provides the memory location of all matching rows that require manipulation. The core of the examples described here can also compute the merge table. The merge table is the list of commands that must be executed to perform the merge operation. It can be computed on the fly in temporary storage and processed as a single stream of commands. Each row of the merge action table comes from a consistency check between the source and target tables, but it can result in multiple actions that must be performed.

[0072] The merge action table contains a command column that contains the actions to be performed. It also contains the location of rows to be deleted and new values ​​to be inserted for each table requiring modification. The command can be INSERT, UPDATE, and DELETE for each table. Furthermore, if multiple tables can be modified, the command can be NONE for one table. However, the command can never be NONE for all tables. Note that for each table, an alternative UPDATE can be represented as the tuple (INSERT, DELETE). Furthermore, if multiple tables are modified, the command can be represented using a command column per table, or all possible combinations of commands across all modified tables can be encoded in a single command column.

[0073] For each table, the storage location must be specified if the command is an UPDATE or DELETE. Since INSERT is only supported with WHEN NOT MATCHED clauses, there can never be a non-null value as the storage location for an INSERT command. Similarly, if the command is an INSERT or UPDATE, the new values ​​to be inserted into the table must be specified, whereas these columns contain logically NULL values ​​for DELETE commands.

[0074] Table 2 below illustrates the merge action table for the merge statement in Listing 1. This example is applicable to any SQL MERGE statement (the table's schema must, of course, be adapted to the columns containing the new values). The general case, where modifications are allowed in both tables, is shown in Table 3 below. Table 2: Merge action table for a merge operation that modifies a table (see Listing 1) command Storage position ID ... bonus INSERT - 183750128 ... 3000.00 UPDATE 123 183750129 ... 6000.00 DELETE 124 - - - action old line to be deleted new row values ​​to be inserted Table 3: Merge action table for a merge operation where two tables A and B are modified command A. Storage position B.Col1 ... B.Storage position A.Col1 ... INSERT A , - - - 'foo' ... - - UPDATE A , DELETE B 123 345 ,bear' ... - - DELETE A , INSERT B 124 - - - 183750129 ... Actions Delete line A Delete line B Insert A Insert B

[0075] To calculate the merge action table, the match condition must of course be evaluated, as explained above. However, extended match conditions in the form of WHEN [NOT] MATCHED AND extra conditions should also be supported. These must also be evaluated. To take these extra conditions into account, it is proposed to calculate the merge action table using a multi-column CASE-WHEN expression. Each branch of the CASE expression corresponds to a WHEN clause in the merge operation. The conditions in the CASE expressions must first check the match indicator column of the corresponding table for NULL values. As explained above, a non-NULL value indicates a match in the table being checked for a match (in a typical SQL merge, this is only the target table).The (non-)null value check is then simply ANDed with the extra condition to calculate the final protection expression for the corresponding When clause.

[0076] Each branch of the case expression returns a complete row from the merge action table. This is where the multi-column case expression proves useful, as it is capable of returning a complete row as the result value. The following code listing 2 illustrates this for the SQL merge statement in code listing 1. Alternatively, each column of the merge action table can be calculated independently using a scalar case-when expression. In this case, all when conditions can be repeated across the individual case expressions for each column.

[0077] Code-Listing 2: Abfrage unter Verwendung eines Mehrspalten-Case-When-Ausdrucks zum Berechnen der Zusammenführungs-AktionstabelleSELECT * FROM ( SELECT CASE WHEN t.storageLocation IS NOT NULL AND s.isFired = 1 THEN (DELETE, t.storageLocation, NULL, NULL, NULL, NULL) -- emit row WHEN t.storageLocation IS NOT NULL AND t.salary < s.salary THEN (UPDATE, t.storageLocation, t.id, t.name, s.salary, s.bonus / 2) WHEN t.storageLocation IS NOT NULL THEN (UPDATE, t.storageLocation, t.id, t.name, t.salary, 0) WHEN t.storageLocation IS NULL AND s.bonus > (SELECT AVG(salary) FROM emp) THEN (INSERT, NULL, s.id, s.name, s.salary, s.bonus / 2) WHEN t.storageLocation IS NULL THEN (INSERT, NULL, s.id, s.name, s.salary, s.bonus) ELSE (NULL, NULL, NULL, NULL, NULL, NULL) END FROM emp AS t LEFT OUTER JOIN (SELECT * FROM worldwide_emp WHERE country = 'GER') AS s ON t.id = s.id ) AS mergeActionTable (command, storageLocation, id, name, salary, bonus) WHERE command IS NOT NULL

[0078] Before the merge action table is complete, a restriction is required that eliminates all rows that do not cause modifications in any of the tables. Such rows are not needed for further processing.

[0079] A row (an instruction set) of the merge action table (the merge instruction list) is computed in main memory immediately when the join returns a result row. Similarly, a row of the merge action table can be further processed to perform the action it describes. The actions are executed by decomposing it into a set of delete elements and a set of insert elements for each modified table. While it is technically possible (and reasonable) to buffer the actions and execute them in batches, conceptually they are executed once the row of its merge action table has been computed. Note that the join is most likely computed in a distributed manner, i.e., concurrently across many data processing nodes. Similarly, the merge action table is processed. Fig.3, described below, illustrates the processing steps.

[0080] Fig.3 shows a flowchart illustrating a method for calculating statistics by linking the source file and the destination file, which can be performed spontaneously when individual instruction sets are executed. Step 300 represents the execution of an instruction set. After an instruction set has been executed, the statistics are calculated 302. After the statistics have been calculated 302, the statistics are summarized 318, and the statistics are finally returned in step 320. The calculation of the statistics 302 is performed by boxes 303 and 311. Box 303 corresponds to deleting the row or data element for the source file or the destination file. Box 311 corresponds to inserting a new row or element into the source file or the destination file.

[0081] Commands in box 303 are executed by a first question box, which asks, "Does the file contain a delete or update operation?" If the answer is no, the method proceeds to block 312. If the answer is yes, the method proceeds to block 310. If the answer is no, the method proceeds to block 308. When block 308 is executed, the location is sent to the only node to allow the node to delete the data. If the answer is yes, and the method proceeds to 310, the deletion is performed locally. After steps 308 and 310, the method executes step 32. The question in block 312 is, "Is the action an insert or update operation?" If the answer is no, the method proceeds to step 318. If the answer is yes, the method proceeds to question block 313.The question in block 313 is "Is the row located in the local node?" If the answer is no, the method proceeds to step 314, and the address of the row is sent to the controlling node for insertion. If the answer is yes, the row or data element is inserted locally. This occurs in block 316. After executing blocks 314 and 316, the method proceeds to step 318. The flowchart of . Fig. 3 allows very accurate statistics to be collected while executing the merge command list.

[0082] Executing the corresponding actions of the command sets decomposes the merge action table and occurs in a distributed manner. Thus, the time calculated immediately after the merge action table is the last time at which accurate statistics and counter values ​​can be collected. For this reason, the first thing performed for each row in the merge action table is to increment the individual insert / update / delete counters for each modified table. In addition, some systems maintain the minimum and maximum values ​​of each column in the database catalog (e.g., for optimizer statistical information) and update them for each new row. This must also be done at this time.

[0083] To perform the prescribed actions, the command column is examined after each modified table. If the command for the current table is UPDATE or DELETE, the memory location of the old row in the current table contains a valid value. A check is made to see if the memory location happens to point to the current processing node—that is, if the node that computed this merge action table also contains the row to be deleted. This may be the case, depending on how the distributed join was computed. If the row exists locally, it is deleted immediately. Otherwise, the memory location is sent to the processing node that contains the row. Once the controlling node receives the row, it is responsible for deleting it.If the command for the current table is an INSERT or DELETE, the corresponding values ​​of the merge action table must be inserted into the current table. The position of the new row is calculated using the distribution strategy of the current table. If the position happens to be the local compute node, the new row is inserted directly into the current table. Otherwise, the row is sent to the compute node that will contain the row. After the controlling node receives the row, it is responsible for inserting it into the current row.

[0084] Once all rows in the merge action table have been processed, the statistics and counter readings are summarized across all data processing nodes. Finally, they are returned as a result and possibly displayed to the user.

[0085] Distributed shared-nothing databases can calculate the match condition precisely as explained in Section 2.2.1. However, some systems maintain multiple (usually three) copies of the same row at different nodes for robustness and effectiveness. Performing the join between the source and target tables in the manner explained results in a cross product of the redundant copies. A naive execution of an example described here (as described so far) may result in multiple rows in the merge action table, requiring the same copy of a row to be modified multiple times.

[0086] First, deleting a row multiple times is not a problem per se, since it can be assumed that the deletion implementation can handle it. Furthermore, if it is known that exactly n copies of each row exist, the deletion implementation can be tuned to simply expect n deletion commands for each deleted row.

[0087] Inserting a row, on the other hand, should be performed in a more controlled manner, as certain constraints regarding the position of copies of a row are often expected. It is therefore preferable to generate only a single insert command and allow the insert implementation to create the copies at the appropriate positions. For this reason, a naive cross product of redundant copies is unfavorable for insert commands in the merge action table.

[0088] The solution to this problem lies in creating specialized join transformations. As explained above, when performing distributed joins, it is safe to assume that all copies of the same (logical) row of at least one table are joined in the same compute node. This means that the join transformation is capable of detecting a cross product locally. If a cross product is detected—in fact, every match in a system holding n copies of each row generates a cross product—the resulting rows of the merge action table are computed differently: the join marks one of the resulting rows of the cross product (i.e., the first row, for example, using a counter). This marking is then taken into account in the case statements for calculating the final rows of the merge action table. For insert actions, only marked rows are considered.For delete actions, all rows result in a DELETE command, since all old copies must be removed. For update actions, only the selected row results in an UPDATE command, whereas the remaining rows only generate DELETE commands.

[0089] When merging files, several different situations can be considered. In one example, two files to be merged are referred to as the source and target files. In one example, the source and target files are static files distributed across multiple compute nodes. It is possible for one of the source and target files to be static and distributed across the same nodes. The data can be stored in a central location. The data can also be stored in one or more continuous data streams.

[0090] Examples of this might include providing distributed matching when merging files. This might involve calculating for each source file, object, or record whether there is a match in the target file. This might be done using global knowledge, for example. If the value is greater than a target maximum value, for example, it might mean that there is no match. This might also be done using a distribution step that moves rows or locations of one or both files to different nodes to ensure that, if any match exists for a particular row, it will be available in the same node as the row after the distribution step. Thus, the match condition can then be checked in that node.In other examples, this can only be done locally in the nodes if the data from both files has already been distributed this way. In some examples, rows or files are appended to their storage location when moved.

[0091] In any case, after distributed matching, various pieces of information may be known. For example, it may be known which source rows have a match. This knowledge may have been computed at a different node than the one where the data was previously located. The original position of the rows may still be known, however. This can be useful for later deletion of the data. It should be noted that if it is necessary to insert data, the new row may, in certain cases, be inserted at yet another node. This additional node may not represent the original memory location, nor may it represent the memory location where the match was computed.

[0092] The entire merged command list, which can also be referred to as the merge action table, can be computed based on the match condition and possibly additional conditions that specify the actions to be performed. The merged action table might have three tuples for one modified file, five tuples for two modified files, or can be described as an action, a location, and new values. The new values ​​can, for example, be specified as a tuple. Alternatively, the merged action table can also be described here as insert text. The merged action table can also, in theory, be computed in a different node than the node where the match was computed. For example, if another node previously held some of the old data, this can be a powerful way to compute it.

[0093] Processing the merged command list or the merged action table can comprise several different steps. This processing can optionally include inserts and / or updates and / or deletes separately for each modified table. Several steps can also be executed in parallel. The process deletes and process inserts can be executed in parallel. The process deletes can be executed locally or can be sent to delete messages to the controlling nodes. The process inserts can also be computed locally, or insert messages can be sent to the controlling node(s). The controlling node may vary depending on the merged process.

[0094] Fig.Figure 4 illustrates merging a continuous data stream 400, 400', 400'' with static data. The three data streams 400, 400', 400'' may, for example, represent data coming from individual sensors 401, 401', 402''. The data streams 400, 400', 400'' are then sent to a matchmaker 402. The matchmaker 402 may, for example, be a script or other code that identifies relevant data to be merged or collects data that is subsequently merged. A distribution step 404 is then executed in step 404, in which the data is copied to various data processing nodes 102, 102', 102''. If static data is deterministically distributed, the data may be distributed according to rules. This allows the data to be copied to the correct node for merging.

[0095] If the data is non-deterministically distributed among nodes 102, 102', 102'', the data from the data streams can be stored pseudo-randomly or using another distribution algorithm to distribute the data among the nodes. For example, if static data is distributed deterministically and according to the matching conditions, the static data does not need to be moved. If static data is non-deterministically distributed or according to the matching condition, the data is distributed in the data stream and stored in the various nodes, and this data from the data stream is checked for merging, and the merging is performed distributed and in parallel.

[0096] Regardless of how the data is distributed from the data streams to the data processing nodes 102, 102', 102''', after the data has been stored in the nodes 102, 102', 102'', the data can then be processed using the Fig. 1 and Fig. 2 described methods and procedures are merged with the static data. The methods and procedures described in Fig. 4 described characteristics can be Fig. 1. The data stream can be, for example, as in Fig. 1 shown as sections of the source file 110, 110', 110'', 110'''. The static data with which the data stream is merged can be represented as in Fig.1 shown as a section of the target file 112, 112', 112'', 112'''. For a continuous joining of the data streams 400, 400', 400'' with the target file 112, 112', 112'', 112''', the uninterrupted data streams 400, 400', 400'' are repeatedly stored as sections of the source file 110, 110', 110'', 110''' and then joined with the target file 112, 112', 112'', 112''' using the Fig. 2 are repeatedly merged.

[0097] The invention can be described by the following combinations of features. 1. A method for operating a distributed database, the distributed database comprising: multiple data processing nodes for implementing storage and Computing for a database management system, wherein the plurality of data processing nodes are connected to exchange data via a network, the database management system for managing at least one source file and one target file, wherein the target file is stored distributed across the plurality of data processing nodes, the source file has a plurality of source elements, the target file has a plurality of target elements, the method comprising repeatedly using the plurality of computing nodes: checking for a match condition between a source element selected from the plurality of source elements and one of the plurality of target elements; Calculating an instruction set of a merged instruction list, the instruction list comprising a list of instructions for merging the source element with the target element using the match condition; and Executing the set of instructions to merge the source element with the target element before checking the match condition between a next source element selected from multiple source elements and one of the multiple target elements. 2. The method of feature combination 1, the method further comprising deleting the instruction set before calculating a next match condition between the next source element selected from the plurality of source elements and one of the plurality of target elements. 3. Method according to feature combination 1 or 2, wherein each of the plurality of nodes has a main memory, the instruction set is calculated by a selected node of the plurality of nodes, the instruction set remains in the main memory of the selected node. 4. The method according to feature combination 1, 2, or 3, wherein the instruction set comprises insert operations, update operations, and delete operations, the instruction set further comprises a storage location of a targeted data element selected from the source element and the target element; and the instruction set is configured to be applied to the targeted data element. 5. The method according to feature combination 4, wherein the instruction set further comprises attribute values ​​to be entered for the target data element when merging the source element with the target file. 6. The method of feature combination 4 or 5, further comprising separately counting a number of insert operations, a number of update operations, and / or a number of delete operations performed for the complete merge command list. 7. Method according to one of the combinations of features 1 to 6, wherein the distributed database is a distributed shared-nothing database. 8. The method according to any one of feature combinations 1 to 7, wherein the distributed database is a distributed relational database, wherein the source file is a source table, the target file is a target table, the plurality of source elements is a plurality of source rows, the plurality of target elements is a plurality of target rows, the source element is a source row, and the instruction set is an instruction column. 9. Method according to one of the combinations of features 1 to 8, wherein the source file and / or the target file are modified during the execution of the instruction set. 10. Method according to one of the feature combinations 1 to 9, wherein the source file contains multiple copies of the source element and / or the target file contains multiple copies of the target element. 11. The method according to any one of feature combinations 1 to 10, wherein repeated execution of the instruction set results in a link set distributed in the set of data processing nodes. 12. Method according to feature combination 11, wherein the link set is a link table. 13. The method according to any one of feature combinations 1 to 12, wherein the merge command list is a list of commands for merging the source file into the target file. 14. Method according to one of the combinations of features 1 to 13, wherein the source file or the target file is a static file. 15. Method according to one of the feature combinations 1 to 14, wherein the source file is stored distributed across the plurality of data processing nodes. 16. Method according to one of the feature combinations 1 to 14, wherein the source file is at least one data stream.

[0098] The descriptions of the various embodiments of the present invention have been presented for purposes of illustration, but are not intended to be exhaustive or limiting of the disclosed embodiments. Many modifications and variations will be apparent to those skilled in the art without departing from the scope and spirit of the described embodiments. The terminology used herein has been chosen to best explain the principles of the embodiments, practical application, or technical improvement over existing technologies in the marketplace, or to enable others skilled in the art to understand the disclosed embodiments.

Claims

[1] A method for operating a distributed database (100), the method comprising: Executing a set of instructions (118) for stream-oriented merging of uninterrupted, continuous data streams from a plurality of sensors of a sensor network in order to merge a source element with a target element, wherein the execution is carried out by a plurality of data processing nodes (102, 102', 102'', 102''') in the distributed database, wherein the plurality of data processing nodes are used to implement storage and computing for a database management system (114), wherein the plurality of data processing nodes are connected via a network to exchange data, and the database management system (114) is used to manage at least one source file (110) and one target file (112), wherein the target file (112) is stored distributed across the plurality of data processing nodes (102, 102', 102'', 102'''), the source file has a plurality of source elements, the target file has a plurality of target elements, wherein the execution includes: Checking (200) for a match condition (116) between a source element selected from the plurality of source elements and one of the plurality of target elements; Calculating (202) an instruction set (118) of a merged instruction list, the instruction list comprising a list of instructions for stream-oriented merging of uninterrupted, continuous data streams of the source element with the target element using the match condition (116); and repeatedly executing (204) the instruction set (118) for stream-oriented merging (204) of uninterrupted, continuous data streams of the source element with the target element before checking (200) the match condition (116) between a next source element selected from a plurality of source elements and one of the plurality of target elements, wherein the repeated execution (204) of the instruction set (118) further comprises: Reducing an amount of computing resources required to merge the source file (110) with the target file (112), and based on data from one sensor in the sensor network, adapting the data from another sensor in the sensor network, wherein the distributed database (100) is a distributed relational database, the source file (110) is a source table, the source table is at least one continuous data stream provided by a sensor of the sensor network, the target file (112) is a target table, the plurality of source elements are a plurality of source rows, the plurality of target elements are a plurality of target rows, the source element is a source row, and the instruction set (118) is an instruction column. [2] The method of claim 1, further comprising deleting the instruction set (118) before calculating a next match condition (116) between the next source element selected from the plurality of source elements and one of the plurality of target elements. [3] The method of claim 1, wherein each of the plurality of nodes has a main memory (108), the instruction set (118) is computed by a selected node of the plurality of nodes, the instruction set remains in the main memory of the selected node. [4] The method of claim 1, wherein the instruction set (118) comprises insert operations, update operations, and delete operations, the instruction set further comprises a storage location of a targeted data item selected from the source item and the target item, and the instruction set is configured to be applied to the targeted data item. [5] The method of claim 4, wherein the instruction set (118) further comprises attribute values to be entered for the target data element when merging the source element with the target file (112). [6] The method of claim 4, further comprising separately counting a number of insert operations, update operations, and / or delete operations performed on the complete merge command list. [7] The method of claim 1, wherein the distributed database (100) is a distributed shared-nothing database. [8] The method of claim 1, wherein the source file (110) and / or the target file (112) are modified during execution of the instruction set (118). [9] The method of claim 1, wherein the source file (110) contains multiple copies of the source element and / or the target file (112) contains multiple copies of the target element. [10] The method of claim 1, wherein repeated execution (204) of the instruction set (118) results in a link set distributed among the set of computing nodes (102, 102', 102'', 102'''). [11] The method of claim 1, wherein the merge command list includes a list of commands for merging the source file (110) into the target file (112). [12] The method of claim 1, wherein the source file (110) is stored distributed across the plurality of data processing nodes (102, 102', 102'', 102'''). [13] Distributed database (100), wherein the distributed database comprises: a plurality of nodes for implementing storage and computing for a database management system (114), the plurality of nodes being connected via a network for exchanging data; and a database management system (114) for managing at least one source file (110) and one target file (112), wherein the target file is stored distributed across the plurality of data processing nodes (102, 102', 102'', 102'''), the source file (110) has a plurality of source elements, the target file (112) has a plurality of target elements; a memory (108) with computer-readable instructions; a plurality of processors (106) located on the plurality of nodes to execute the computer-readable instructions, the computer-readable instructions comprising: Executing a linking command for stream-oriented merging of uninterrupted, continuous data streams from multiple sensors of a sensor network to merge the source file (110) with the target file (112), wherein the execution comprises: Checking (200) for a match condition (116) between a source element selected from the plurality of source elements and one of the plurality of target elements; Calculating (202) an instruction set (118) from a merge instruction list, the instruction set (118) comprising a list of instructions for stream-oriented merging of the source element with the target element using the match condition (116); and repeatedly executing (204) the instruction set (118) for stream-oriented merging of the source element with the target element before checking (200) the match condition (116) between a source element selected from the plurality of source elements and one of the plurality of target elements, wherein the repeated execution (204) of the instruction set (118) reduces an amount of computing resources required to merge the source file (110) with the target file (112), and wherein the repeated execution (204) of the instruction set (118) adapts data from another sensor of the sensor network based on data from one sensor of the sensor network, and wherein the distributed database (100) is a distributed relational database, the source file (110) is a source table, the source table is at least one continuous data stream provided by a sensor of the sensor network, the target file (112) is a target table, the plurality of source elements are a plurality of source rows, the plurality of target elements are a plurality of target rows, the source element is a source row, and the instruction set (118) is an instruction column. [14] A computer program product for operating a distributed database, the computer program product comprising a computer-readable storage medium having program instructions embodied therein, the computer-readable storage medium not being a transient signal per se, the program instructions being executed by processors (106) of a plurality of data processing nodes (102, 102', 102'', 102'''), the instructions causing the processors (106) to perform the following: Linking a source file (110) to a target file (112) by merging uninterrupted, continuous data streams from multiple sensors of a sensor network, wherein the target file (112) is stored distributed across the multiple data processing nodes, the source file (110) has multiple source elements, the target file (112) has multiple target elements, wherein the linking comprises: Checking (200) for a match condition (116) between a source element selected from the plurality of source elements and one of the plurality of target elements; Calculating (202) an instruction set (118) of a merge instruction list, the instruction set comprising a list of instructions for stream-oriented merging of the source element with the target element using the match condition (116); and Executing the instruction set (118) for stream-oriented merging of the source element with the target file (112) before checking (200) for the match condition (116) between a next source element selected from the plurality of source elements and one of the plurality of target elements, wherein repeatedly executing the instruction set (118) reduces an amount of computing resources required to merge the source file (110) with the target file (112), and wherein the repeated execution of the instruction set based on data from one sensor of the sensor network adapts data from another sensor of the sensor network, and wherein the distributed database (100) is a distributed relational database, the source file (110) is a source table, the source table is at least one continuous data stream provided by a sensor of the sensor network, the target file (112) is a target table, the plurality of source elements are a plurality of source rows, the plurality of target elements are a plurality of target rows, the source element is a source row, and the instruction set (118) is an instruction column. [15] The computer program product of claim 14, wherein the program instructions further cause the processor (106) to delete the instruction set (118) before calculating a next match condition (116) between the next source element selected from the plurality of source elements and one of the plurality of target elements. [16] The computer program product of claim 14, wherein each of the plurality of nodes has a main memory (108), the instruction set (118) is computed by a selected node of the plurality of nodes, the instruction set remains in the main memory (108) of the selected node. [17] The computer program product of claim 14, wherein the instruction set (118) comprises insert operations, update operations, and delete operations, the instruction set (118) further comprises a storage location of a targeted data item selected from the source item and the target item, and the instruction set is configured to be applied to the targeted data item.

Images