A method for loading data into a target database system

Abstract

The present disclosure relates to a computer-implemented method for loading data into a target database system, the method including determining a source table loading predicted to occur in the target database system. The future target table may be prepared according to a defined table schema, after which a load request for loading the source table may be received. Data from the source table may be loaded into the future target table.

Description

Background Art

[0001] The present invention relates to the field of digital computer systems, and more particularly to a method for loading data in a target database system.

[0002] Data loading is one of the most frequently performed operations in a database system. Therefore, the data loading can improve the overall performance of the database system. However, controlling the time required to perform such data loading can be a difficult task.

Summary of the Invention

Means for Solving the Problems

[0003] Various embodiments provide a method for loading data in a target database system, a computer system, and a computer program product, as described by the subject matter of the independent claims. Advantageous embodiments are described in the dependent claims. Embodiments of the present invention can be freely combined with each other if they are not mutually exclusive.

[0004] In one aspect, the present invention relates to a computer-implemented method for loading data into a target database system. The method includes determining that loading a source table is predicted to occur in the target database system; preparing a future target table in advance according to a defined table schema; and then receiving a load request for loading the source table; loading the data of the source table into the future target table including.

[0005] In another aspect, the present invention relates to a computer program product, wherein the computer program product comprises a computer-readable storage medium having computer-readable program code stored therein, the computer-readable program code being configured to implement all of the steps of the method according to the embodiments described above.

[0006] In another aspect, the present invention relates to a computer system for loading data into a target database system. The computer system determines that it is predicted that loading a source table will occur in the target database system; prepares a future target table in advance according to a defined table schema; and then, receives a load request for loading the source table; loads the data of the source table into the future target table is configured to.

[0007] In the following embodiments, embodiments of the present invention are described in more detail by way of example only with reference to the drawings.

Brief Description of the Drawings

[0008]

Figure 1

Figure 2

Figure 3

Figure 4

Figure 5

Figure 6

Figure 7

Figure 8

Figure 9

Figure 10

Figure 11

DETAILED DESCRIPTION OF THE INVENTION

[0009] The descriptions of the various embodiments of the present disclosure are presented for illustrative purposes and are not intended to be exhaustive or limited to 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 terms used herein are chosen to best explain the principles of the embodiments, practical applications, or technical improvements found in the marketplace, or to enable those skilled in the art to understand the embodiments disclosed herein.

[0010] Loading data into a target database system can include extracting data from a table (named the "source table") and copying the data into a target table of the target database system. The source table may be, for example, a source table in a source database system or an existing table in the target database system. The source table may have a table schema named the source table schema, and the target table may have a target table schema. The target table schema can be obtained from the source table schema using a defined unique schema mapping associated with the source table. In one example, the unique schema mapping may be a 1:1 mapping. That is, the source table and the target table may have the same table schema. The table schema of a table T can indicate the number of columns in the table T. This definition may be sufficient to create a target table and perform a reliable load of data. In another example, the table schema can further indicate the type of attributes of table T. This allows for accurately creating or allocating the resources of the target table as storage resources are allocated differently depending on the type of data, such as floating point and integer types. In another example, the table schema can further indicate the name of the table. This can prevent overwriting of data in the target database system as multiple tables can have the same column definition but different table names.

[0011] The data load may involve different types of data load methods, depending on the context where, for example, the target database system is used. For example, the data load method may include the process of the data load and additional processes depending on the type of the data load method. One example of the data load method may be a data rearrangement method. The data rearrangement method may rearrange the data in the source table in different ways. The data rearrangement may distribute the data differently across the logical nodes of the target database system according to a distribution criterion, or may change the physical sort order of the rows in the source table according to different sort criteria (e.g., sorting by name instead of by social security number), or a combination thereof. For example, the data rearrangement method may, in response to receiving a request for data rearrangement, include that a target table may be newly created within the target database system, and that all rows may be copied from the source table to the target table, and that new distribution / sort criteria may be applied in the copy process. In this case, the source table and the target table may belong to the target database system. Another example of the data load method may be a data synchronization method between the source database system and the target database system. Data synchronization may be a process of establishing data consistency between the source table of the source database system and the target table of the target database system, or vice versa. To that end, the data synchronization method may be able to detect changes in the source table and, in response, trigger the creation of a new target table to move all the contents (including the changes) of the source table into the newly created table. That is, some changes in the source table may each result in the creation of some target tables. This enables continuous reconciliation of data over time. The data synchronization method may include an initial load method or a full table reload method.The initial loading method refers to loading the data of the source table into the target database system for the first time. The table full reload method refers to continuously loading the source table into the target database system in response to changes in the source table.

[0012] Thus, regardless of the data loading method used, the data loading may include a sequence of operations or steps of s1) creating a target table, s2) extracting all rows from the source table and inserting them into the target table. In another example, the data loading may further include an operation of s3) adapting an application (e.g., a view) that accesses the content of the initial table in the target database system to refer to the target table. The time required to complete the data loading (loading time) may include the time to execute each of the three steps s1 - s3. However, in order to prevent inconsistent access to the data even in a short time, it is ideally desirable to make the loading time as short as possible. For example, when the number of rows in the source table is very small, not only is the overhead for creating the target table in step s1) prominent, but it may even be large. It may dominate the entire process. This is the same even if the table creation only takes 30 milliseconds. The present subject matter can shorten the loading time and thus speed up the execution of the data loading method. To that end, the target table can be created early in the target database system for subsequent use. In this way, an existing target table can be used when necessary without creating a target table on demand. The present subject matter can, for example, configure an existing data loading method not to execute step s1) or to conditionally execute step s1) based on the existence of an appropriate target table. This is because step s1) of the data loading may be executed independently of the execution of the data loading method.

[0013] The target table can be prepared in advance in response to determining, for example, that loading a source table is predicted to occur in the target database system. In one example, the target table schema of the target table can be obtained from the source table schema of the source table using a unique schema mapping, and then the target table can be created using the target table schema. In another example, an existing table may be prepared as the target table, and thus the target table schema of the target table can be the table schema of the existing table. In another example, the target table schema of the target table can be a user-defined table schema. For example, the user may be prompted to prepare the target table schema. Thus, the created target table is named the "future target table". The method for determining that the load may occur at a future time may depend on the data loading method used to load data in the target database system. For example, the data loading method can be analyzed or processed to derive how the data load (steps s1 to s3) is triggered, or to determine the frequency at which data is loaded for which table schema, or to perform a combination thereof. The result of the analysis can be used to define a method for determining whether the load may occur at a future time. For example, knowing that a certain source table T s is date-based and is loaded, for example, at 12:00 AM, the method of the present invention can determine that at a certain time, for example, 9:00 AM, loading data in the target database system, for example, for the table schema T of the source table sIn a table having [the relevant content], it can be determined that it is predicted to occur. In another example, in the case of a data synchronization method, creating a new source table within the source database system can indicate that it is predicted to occur in a table having, for example, the table schema of the source table in the target database system. This is because the created source table can necessarily store new data that needs to be propagated to the target database system during the initial load. After the initial load, it is also predicted that the source table will be changed again, and thus a new table may be required to propagate the change. Therefore, each time an operation such as "table addition" or "initial load" or "full table reload" ends, a new target table can be created asynchronously for the next potential execution of the data loading method.

[0014] Therefore, according to one embodiment, determining that it is predicted that loading the source table will occur is performed in response to creating the source table within the source database system, where the source database system and the target database system are configured to synchronize data with each other, and the source table has a source table schema that maps to the defined table schema of the future target table according to the unique schema mapping. This embodiment can enable, for example, a hybrid transaction and analytics processing environment that allows the data of the source database system to be used almost in real time.

[0015] According to one embodiment, determining that it is predicted that the loading will occur is performed by loading the source table into the current target table of the target database system from the source database system, where the current target table has the defined table schema.

[0016] For example, assume that the source database system has a source table T s In response to creating the source table T s The present subject matter can pre-create a target table T g 0 having a target table schema. The target table schema can be obtained, for example, using the unique schema mapping from the source table schema of the source table T s Target table T g 0 can be the current target table associated with the source table T s The source table can receive initial content at time t0, and the initial content of the source table T s can be loaded into the current target table T g 0 This can be referred to as the first load or initial load. In response to the first load, the present subject matter can pre-create another target table T g 0 having the table schema of the target table T g 1 Target table T g 1 can be the current target table associated with the source table T s for the second load of the source table T s When the content of the source table T s changes at time t1, the current content of the source table T s can be loaded into the created current target table T g 1 In response to the second load, the present problem can pre-create another future target table T g 1 having the table schema of the target table T g 2 The future target table T g 2 can be the source table T sFor the third load, it can be the current target table associated with source table T s If the content of source table T s changes at time t2, the current content of source table T s can be loaded into the currently created target table T g 2 (subsequent).

[0017] According to one embodiment, the method further includes repeatedly executing the method where the future target table of the current iteration becomes the current target table for the next iteration.

[0018] Source table T s According to the above example of source table T, and in the initial load, target table T g 0 was the current target table. After the initial load, the created future target table T g 1 becomes the current target table for the second load of source table T s (first iteration). After the second load of the source table (subsequent to the initial load), the created future target table T g 2 becomes the current target table for the next load of source table T s (subsequent). This means, for example, multiple target tables T g 0 , T g 1 , T g 2...results, and the plurality of target tables correspond to the number of times the source table has been loaded into the target database system. This can be advantageous as it allows tracking of different versions of the source table in the target database system. These versions can be useful, for example, in time-dependent analysis and the like. However, this can require storage resources in the target database system. The present subject matter can conserve the storage resources used by the target tables by using the following embodiments. According to one embodiment, the current target table for the current iteration becomes the future target table for the next iteration. According to the above example, each load (initial load or subsequent load) is associated with two tables, namely the current target table and the future target table created. For example, the first load is associated with the current target table T g 0 and the future target table T g 1 . The second load is associated with the current target table T g 1 and the future target table T g 2 . The third load is associated with the current target table T g 2 and the future target table T g 3 (and so on). In this embodiment, the future target table T g 2 associated with the second load may be prepared as the current target table T g 0 of the first load, and the future target table T g 3 may be prepared as the current target table T g 1 of the second load (and so on). In this case, two tables, namely T g 0 and Tg 1 Only can be used to load the source table in the target database system. In other words, table T g 0 and T g 1 exchange roles at the end of the load: that is, T g 0 becomes T g 1 while, on the other hand, T g 1 becomes T g 0 Thus, only two tables can be created, and subsequently, only swapping is performed.

[0019] According to one embodiment, loading the next iteration includes considering the content of the current target table of the current iteration to be invisible. Following the above example and as described above, the future target table T g 2 associated with the second load may be prepared as the current target table T g 0 However, T g 0 may still have some data. In this embodiment, when loading data into the target table T g 2 (which is T g 0 ), the content of table T g 0 may be considered invisible. This is performed, for example, by defining different individual ranges of rows of the target table for each load of the source table. Thus, the rows in which the content is stored are different from the rows where the loading is performed (and thus invisible to the loading). Alternatively, according to one embodiment, loading the next iteration includes purging the content of the target table of the current iteration. According to the above example, data is loaded into the target table T g 2Before loading into table T g 0 The contents of the table T can be purged. For example, an SQL statement such as TRUNCATE g 0 This can be advantageous because the TRUNCATE operation is a very fast operation since the target database system simply frees all pages associated with the table and does not delete individual rows. Another advantage of the operation can be that metadata in the target database system's catalog does not need to be modified. This can improve concurrency in the metadata catalog.

[0020] According to one embodiment, preparing the future target table includes creating an empty table using an asynchronous job, which is asynchronous with respect to the execution time of the data loading method used.

[0021] According to one embodiment, determining that loading is predicted to occur includes processing a historical dataset representing a history of data loading into the target database system, wherein the historical dataset comprises: including an entry, where the entry is , the loaded source table and The time that the load was performed and Show 、 and determining, based on the processing, that the loading is predicted to occur. In other words, the historical dataset may track how often a table with a particular schema is needed. That history is consulted and projected into the future to determine when the next table will likely be needed, so it can be created in advance. Following the example above, an entry in the historical dataset would be a tuple (T s , t0), and another entry may contain (T s , t1), and another entry may contain a tuple (T s , t2) (continued). Based on times t0, t1, and t2, the source table Ts The frequency of loading can be derived. This frequency can be used to determine that the loading of the source table T s is predicted to occur.

[0022] According to one embodiment, the processing includes grouping the plurality of entries for each table schema, and using the time behavior of data loading for each group of the plurality of groups to determine that the loading is predicted to occur. The defined table schema can be one of the schemas of the group that indicates that the time behavior indicates that the loading is predicted to occur. Also, the source table expected to be loaded can be the source table of one of the aforementioned groups.

[0023] For example, assume that a plurality of source tables of a source database system

Number

Number

Number

Number

Number

[0024] According to one embodiment, the defined table schema of the future target table is obtained from the existing table schema of the source table using a unique mapping. For example, the computer system can obtain a request for arranging the data in the source table in different ways. It may distribute the data to different logical nodes of the target database system in different ways, or it may also change the physical sorting order of the rows in the source table according to different criteria (for example, sorting by name and sorting by social security number). Therefore, a future target table is created within the target database system, and all rows are copied from the source table to the future target table, and new distribution / sorting criteria are applied in the process. Therefore, the rearrangement process requires a new table, and creating that table within the target database system takes a certain amount of time. This embodiment can speed up the process by preparing the target table in advance, for example, before a request for data rearrangement comes.

[0025] FIG. 1 is a block diagram of a data processing system 100 suitable for implementing the method steps involved in the present disclosure. The data processing system 100 may include, for example, IBM Db2 Analytics Accelerator for z / OS (IDAA). The data processing system 100 includes a source database system 101 connected to a target database system 121. The source database system 101 may include, for example, IBM Db2 for z / OS. The target database system 121 may include, for example, IBM Db2 Warehouse (Db2 LUW).

[0026] The source database system 101 includes a processor 102, a memory 103, an I / O circuit 104, and a network interface 105 connected to each other by a bus 106.

[0027] The processor 102 may represent one or more processors (e.g., microprocessors). The memory 103 can include any one or a combination of volatile memory elements (e.g., random access memory (RAM), such as DRAM, SRAM, SDRAM, etc.) and non-volatile memory elements (e.g., ROM, erasable programmable read only memory (EPROM), electronically erasable programmable read only memory (EEPROM), programmable read only memory (PROM)). It should be noted that the memory 103 can have a distributed architecture where various components are located separately from each other but can be accessed by the processor 102.

[0028] In combination with the persistent memory device 107, the memory 103 can be used for local data and instruction storage. The memory device 107 includes one or more persistent memory devices and media controlled by the I / O circuit 104. The memory device 107 can include magnetic, optical, magneto-optical, or solid-state devices for digital data storage, such as magnetic, optical, magneto-optical, or solid-state devices with fixed or removable media. Sample devices include hard disk drives, optical disk drives, and floppy disk drives. Sample media include hard disk platters, CD-ROMs, DVD-ROMs, BD-ROMs, floppy disks, etc.

[0029] The memory 103 may include one or more distinct programs, such as the database management system DBMS1 109, each of which includes an ordered list of executable instructions for implementing logical functions, particularly functions involved in embodiments of this invention. The software in the memory 103 will also typically include a suitable operating system (OS). The OS essentially controls the execution of other computer programs for implementing at least part of the methods described herein. The DBMS1 109 includes a DB application 111 and a query optimizer 110. The DB application 111 can be configured to process data stored in the memory device 107. The query optimizer 110 can be configured to create or define a query plan for executing a query, for example, on a source database 112. The source database 112 may include, for example, a source table 190.

[0030] The target database system 121 includes a processor 122, a memory 123, an I / O circuit 124, and a network interface 125 connected to each other by a bus 126.

[0031] Processor 122 may represent one or more processors (e.g., microprocessors). Memory 123 can include either volatile memory elements (e.g., random access memory (RAM), such as DRAM, SRAM, SDRAM, etc.) and non-volatile memory elements (e.g., ROM, erasable programmable read-only memory (EPROM), electronically erasable programmable read-only memory (EEPROM), programmable read-only memory (PROM)) or a combination thereof. It should be noted that although the various components of memory 123 are arranged separately from each other, it can have a distributed architecture that can be accessed by processor 122.

[0032] In combination with persistent storage device 127, memory 123 can be used for local data and instruction storage. Storage device 127 includes one or more persistent storage devices and media controlled by I / O circuit 124. Storage device 127 can include magnetic, optical, magneto-optical, or solid-state devices for digital data storage, e.g., magnetic, optical, magneto-optical, or solid-state devices having fixed or removable media, such as hard disk drives, optical disk drives, and floppy disk drives. Sample media includes hard disk platters, CD-ROMs, DVD-ROMs, BD-ROMs, floppy disks, etc.

[0033] Memory 123 may include one or more distinct programs, such as database management system DBMS2 129, each of which includes an ordered list of executable instructions for implementing logical functions, particularly functions involved in embodiments of this invention. The software in memory 123 will also typically include a suitable OS 128. OS 128 essentially controls the execution of other computer programs for implementing at least part of the methods described herein. DBMS2 129 includes a DB application 131 and a query optimizer 130. DB application 131 may be configured to process data stored in storage device 127. Query optimizer 130 may be configured to create or define a query plan for executing a query, for example, on target database 132.

[0034] Source database system 101 and target database system 121 may be independent computer hardware platforms that communicate via network interfaces 105 and 125 over high-speed connection 142 or network 141. Network 141 may include, for example, a local area network (LAN), a general wide area network (WAN), or a public network (e.g., the Internet) or combinations thereof. Each of source database system 101 and target database system 121 may have the responsibility of managing its own copy of the data.

[0035] Although shown as separate systems in FIG. 1, the source database system and the target database system may belong to a single system, such as a single system that shares the same memory and processor hardware. On the other hand, each of the source database system and the target database system is associated with an individual DBMS and dataset. For example, two DBMSs may be stored in a shared memory. In another example, two database management systems DBMS1 and DBMS2 may form part of a single DBMS that enables the communications and methods executed by DBMS1 and DBMS2 as described herein. The first dataset and the second dataset may be stored in the same storage or in separate storages.

[0036] The database engine 155 may be configured to synchronize data between the source database system 101 and the target database system 121. The database engine 155 may be configured to perform data migration or data transfer according to the present subject matter. In another example, the database engine 155 may be configured to manage the data of one of the two database systems 101 and 121 independently from the other database system. In this case, the data processing system 100 may be composed of only one of the two database systems. Accordingly, the database engine 155 may be part of the source database system 101 or the target database system 121 or a combination thereof. For example, the database engine 155 may be composed of at least a part of DBMS1 109 or DBMS2 129 or a combination thereof. In another example, the database engine 155 may be a separate computer system configured to connect to the data processing system 100 or may form it, and the database engine 155 may be configured to control the data processing system 100 to execute at least a part of the present method.

[0037] FIG. 2 is a flowchart diagram showing a method for loading data into a target database system, such as target database system 121. For purposes of explanation, the method described in FIG. 2 may be implemented in the system shown in FIG. 1, but is not limited to this implementation. The method of FIG. 2 may be executed, for example, by database engine 155.

[0038] It may be determined in step 201 that it is predicted that at least one source table will be loaded in target database system 121. Step 201 may determine whether loading the one or more source tables into target database system 121 may occur at a future point in time. This determination may be made in different ways, such as different ways described in FIG. 5. In one example, step 201 may determine whether user input has been received, where the user input indicates whether data is to be loaded into or not loaded into the target database system.

[0039] Step 201 may be executed on a pre - defined source table. Step 201 may be executed in an existing system or in a newly (from scratch) created system. Step 201 may determine that it is predicted that data from one or more of a plurality of source tables will be loaded into target database system 121. The pre - defined source table may be, for example, a source table that already exists at a time t when step 201 is executed s201 (e.g., the creation date of the pre - defined source table is older than time t s201 and step 201 may determine that the data is at time t s201It can be determined that it is predicted to be loaded at a point in time newer than. The predefined source table may be composed of a table that has not yet been loaded into the target database system, a table that has been loaded at least once into the target database system, or a combination thereof.

[0040] If it is predicted that loading the source table will not occur in the target database system 121, the method may end, or step 201 may be repeated until it is predicted that loading one or more source tables will occur, or until the number of repetitions of step 201 exceeds a threshold. If it is predicted that loading at least one source table will occur in the target database system 121, step 203 may be executed. For example, the source table

Number

Number

Number

Number

Number

Number

Number

[0041] After executing step 203, in step 205, a load request for loading data can be received (for example, at time t s205 ). The load request may request to load at least a part (for example, all) of the source tables

Number

Number

Number

Number

Number

Number

[0042] However, when the initial load is started in step 207, it may happen that the table creation in step 203 has not been completed yet. In one example, the initial load in step 207 can be synchronized with the asynchronous table creation in step 203. In another example, the asynchronous process can create a future target table. If it has not been completed yet when the initial load is started, the initial load can create its own independent future target table and use it. Therefore, after the initial load, a new future target table (created asynchronously) will already exist and can be used.

[0043] In one example, the method of FIG. 2 can be executed in a system having a source database system and a target database system where the source table is part of the source database system. The method of FIG. 2 can be implemented in an application or in the database kernel of the system. In the latter case, it can be beneficial to all applications that cooperate with the database. In another example, the method of FIG. 2 can be executed in a system that only includes the target database system, where the source table can be part of the target database system. Therefore, the method of FIG. 2 can be used as a data synchronization method or a data reorganization method.

[0044] FIG. 3 is a flowchart diagram showing a method for loading data into a target database system, such as target database system 121. For purposes of explanation, the method described in FIG. 3 may be implemented in the system shown in FIG. 1, but is not limited to this implementation. The method of FIG. 2 may be performed, for example, by database engine 155. For purposes of explanation, the method described in FIG. 3 may be implemented in the system shown in FIG. 1, but is not limited to this implementation. The method of FIG. 3 may be performed, for example, by database engine 155.

[0045] Steps 301-305 are steps 201-205 of the method of FIG. 2. The requested source table

Number

Number

Number

Number

Number

Number

Number

Number

Number

Number

Number

Number

[0046] In addition, method steps 305 to 307 can be repeated. This repetition can be automatically executed in response to the receipt of a load request.

[0047] In one example, in step 305, if the same source table

Number

Number

Number

Number

Number

Number

Number

[0048] In one example, in step 305 of the first iteration, the source table to be (should be loaded) required

Number

Number

Number

Number

Number

Number

Number

[0049] For example, the repetition of steps 305 to 307 can be executed until a stop criterion is satisfied. The stop criterion may require, for example, that the number of repetitions is less than a predetermined reload threshold. The load requests received in each repetition of step 305 may or may not require that the same source table be loaded. Source table

Number

[0050] In the example of the first load, the future target table

Number

Number

[0051] In step 305, for each required source table, the following two types of target tables can be prepared. That is, the current target table into which the source table is loaded in step 307, and the future target table prepared in step 307 for continuously loading the source table into the future target table. In the example of the third load, regardless of the number of times of reloading the source table, only these two tables can be associated with the source table. For this purpose, a swapping method can be used. For example, if the source table

Number

Number

Number

Number

Number

Number

Number

Number

Number

Number

Number

Number

[0052] After each iteration of steps 305 to 307, and for each source table, when the source table is reloaded into its new target table, the view described with reference to FIG. 2 may be updated so that the reference to the target table associated with the source table is updated to the new target table.

[0053] FIG. 4 is a flowchart diagram showing a method of loading data into a target database system, such as target database system 121. For the purpose of illustration, the method described in FIG. 4 may be implemented in the system shown in FIG. 1, but is not limited to this implementation. The method of FIG. 4 may be executed, for example, by database engine 155.

[0054] Steps 401 to 407 of the method in FIG. 4 are respectively steps 201 to 207 in FIG. 2. In addition, steps 401 to 407 of the method may be repeated. The repetition may be automatically executed, for example, in response to the reception of the load request. The repetition may be executed, for example, periodically, for example, every hour. The repetition may be executed until a stop criterion is met. The stop criterion may require, for example, that the number of repetitions is less than a predetermined reload threshold.

[0055] In the i-th repetition of step 401, the source table

Number

Number

Number

Number

Number

[0056] For each source table identified in step 401, the provision of the corresponding future target table in step 403 may be performed as described with reference to FIG. 2. Alternatively, the target table may be prepared using a swapping method. That is, for each source table to be loaded, only two target tables may be prepared. The two target tables may be prepared in the first two executions of step 403 for the source table. And subsequent executions of step 403 for the source table may use one of the two target tables. For example, if it is required in step 405 that the source table

Number

Number

Number

Number

Number

Number

Number

Number

Number

[0057] FIG. 5 is a flowchart diagram of an exemplary method for implementing step 201 of FIG. 2. For purposes of explanation, the method described in FIG. 5 may be implemented in the system shown in FIG. 1, but is not limited to this implementation. The method of FIG. 5 may be executed, for example, by the database engine 155.

[0058] A history data set representing the history of data to be loaded into the target database system 121 may be prepared in step 501. The history data set including an entry, the entry is , the loaded source table and 、 the time when the loading was executed, indicating 。 For example, an entry in the history data set may include a tuple (T s , t0), where T s is the source table loaded at time t0, and another entry may include (T s , t1), and another entry may include the tuple (T s , t2) (subsequent).

[0059] The history data set may be processed or analyzed in step 503. For example, the time behavior of the loading of the source table T s can be determined. The time behavior indicates, for example, the frequency of loading the source table T s .

[0060] Using the result of the processing, at least one source table is loaded, and the source table

Number

[0061] FIG. 6 is a flowchart diagram of a method for loading data into a target database system, such as target database system 121. For purposes of illustration, the method described in FIG. 6 may be implemented in the system shown in FIG. 1, but is not limited to this implementation. The method of FIG. 6 may be executed, for example, by database engine 155.

[0062] Steps 601 to 605 are respectively steps 501 to 505 of FIG. 5. The following n0 source tables

Number

Number

Number

[0063] In step 609, a data reorganization request may be received. The data reorganization request is for the source table

Number

[0064] In step 611, the source table

Number

[0065] FIG. 7 is a flowchart diagram of a method for synchronizing data between a source database system 101 and a target database system 121. For purposes of illustration, the method described in FIG. 7 may be implemented in the system shown in FIG. 1, but is not limited to this implementation. The method of FIG. 7 may be executed, for example, by a database engine 155.

[0066] Whether a source table is created within the source database system can be determined in step 701. The source table may be created by an "Add Table" operation. In that case, in step 703, an initial target table may be created within the target database system. If the source table is not created within the source database system, the method may end, or step 701 may be repeated until the source table is created or the number of repetitions of step 701 exceeds a threshold. In step 705, a load request for loading the source table may be received. And in step 707, the content of the source table may be loaded into the initial target table. Thus, the idea herein is to create an empty table after the source table is added. An asynchronous job may process it, which can be started based on some timer or by an "Add Table" operation. The target table in step 703 can now be used for the "initial load" in step 707, which means that step s1) for the initial load can be skipped. Note that when the initial load starts in step 707 and the table creation in step 703 is not yet complete, in one example, the initial load may itself have to be synchronized with the asynchronous table creation. Since the table creation can be executed (at least started) within a time window between the "Add Table" and the start of the "initial load", an improvement in the load time may already be possible. In another example, an asynchronous process may create the future target table. If it is not yet complete when the initial load starts, the initial load can create its own independent future target table and use it. Thus, after the initial load, a new future target table (created asynchronously) will already exist and can be used.

[0067] FIG. 8 is a flowchart diagram of a method for synchronizing data between a source database system 101 and a target database system 121. For purposes of explanation, the method described in FIG. 8 may be implemented in the system shown in FIG. 1, but is not limited to this implementation. The method of FIG. 8 may be executed, for example, by a database engine 155.

[0068] Steps 801 to 805 are the same as steps 701 to 705 in FIG. 7. Step 807 in FIG. 8 is a modification of step 707 in FIG. 7 to further include the preparation of a future target table. For example, the future target table may be prepared simultaneously with loading the data in step 807. In another example, the future target table may be prepared immediately after loading the data in step 807. In addition, method steps 805 to 807 may be repeated. The repetition may be automatically executed in response to receiving the load request. For example, the repetition may be executed until a stop criterion is met. The stop criterion may require, for example, that the number of repetitions is less than a predefined reload threshold. Reloading the source table in step 807 in the current iteration may be executed, for example, in the future last target table prepared in step 807 in the previous iteration for the source table.

[0069] The method of FIG. 8 may be advantageous because it may enable continuous monitoring of the source table identified in step 801 to continuously load its content according to the present subject matter.

[0070] FIG. 9 is a flowchart diagram of a method for loading data in a target database system according to one example of the present subject matter. For purposes of explanation, the method described in FIG. 9 may be implemented in the system shown in FIG. 1, but is not limited to this implementation. The method of FIG. 9 may be executed, for example, by a database engine 155.

[0071] The method of FIG. 9 includes repeating the method of FIG. 8 to detect new source tables. The repetition can be performed, for example, periodically, such as every hour. The repetition can be performed until a stop criterion is met. The stop criterion can require, for example, that the number of iterations is less than a predetermined threshold. In this case, the load request received in step 805 can be a load request for one or more source tables determined in step 801, where the loading can be performed in the corresponding target table, and the creation of future target tables can be performed for each of the requested source tables. Also, reloading each source table in step 807 in the current iteration can be performed, for example, in the future last target table prepared in step 807 in the previous iteration for each of the source tables.

[0072] FIG. 10 is a flowchart diagram of a method for loading data in a target database system according to one example of the present subject matter. For purposes of illustration, the method described in FIG. 10 may be implemented in the system shown in FIG. 1, but is not limited to this implementation. The method of FIG. 10 may be performed, for example, by a database engine 155.

[0073] The method of FIG. 10 can be executed, for example, in response to receiving a data load request for loading a source table in a target database system 121. It can be determined whether a target table associated with the source table exists (step 1001). If a target table associated with the source table exists, it can be determined whether the target table has the same table schema as the table schema of the source table (step 1003). If the target table has the same table schema as the table schema of the source table, the target table can be used in step 1007. If the target table does not have the same table schema as the table schema of the source table or if the target table does not exist, a new target table can be created in step 1005, and the new target table can be used in step 1009. In step 1009, the data of the source table can be inserted into an existing target table or a newly created target table. The insertion can be committed in step 1010. And a view can be modified in step 1011, where the view is modified to refer to the target table used in step 1009. The view is configured to process the content of the source table in the target database system.

[0074] FIG. 11 is a flowchart diagram of a method for preparing a target table in accordance with the present subject matter. For illustrative purposes, the method described in FIG. 11 may be implemented in the system shown in FIG. 1, but is not limited to this implementation. The method of FIG. 11 may be executed, for example, by a database engine 155. The method of FIG. 11 provides an exemplary implementation of step 307.

[0075] The method of FIG. 11 can be executed, for example, in response to receiving a data load request for loading the source table. It can be determined in step 1101 whether there exists a target table having a table schema that maps to the table schema of the source table. For example, it can be determined whether the schema of the target table is the same as the table schema of the source table. If so, the existing target table can be purged in step 1103 and prepared as the future target table such that the source table is loaded into that target table. If not, a new target table can be created in step 1105. The purging can be executed, for example, advantageously, using a TRUNCATE operation.

[0076] The present invention can be a system, method, or computer program product, or a combination thereof, at any technical detail level that can be integrated. The computer program product can include one or more computer-readable storage media having computer-readable program instructions for causing a processor to execute aspects of the present invention.

[0077] The computer-readable storage medium can be a tangible device that can hold and store instructions for use by an instruction execution device. The computer-readable storage medium can be, for example, an electronic storage device, a magnetic storage device, an optical storage device, an electromagnetic storage device, a semiconductor storage device, or any suitable combination thereof, but is not limited thereto. A non-exhaustive list of more specific examples of the computer-readable storage medium includes the following: portable computer diskettes (registered trademark), hard disks, random access memory (RAM), read only memory (ROM), erasable programmable read-only memory (EPROM) or flash memory), static random access memory (SRAM), portable compact disc read-only memory (CD-ROM), digital versatile disk (DVD), memory stick, floppy disk, mechanically encoded devices such as punch cards or raised structures in grooves in which instructions are recorded, or any suitable combination thereof. As used herein, a computer-readable storage medium should not be construed to be a transient signal itself, such as a radio wave or other freely propagating electromagnetic wave, an electromagnetic wave propagating through a waveguide or other transmission medium (e.g., an optical pulse passing through an optical fiber cable), or an electrical signal transmitted through a wire.

[0078] The computer-readable program instructions described herein can be downloaded from a computer-readable storage medium to individual computing devices / processing devices or to an external computer or external storage device via a network, such as the Internet, a local area network, a wide area network, or a wireless network, or a combination thereof. The network can be composed of copper transmission cables, optical transmission fibers, wireless transmission, routers, firewalls, switches, gateway computers, or edge servers, or a combination thereof. A network adapter card or network interface in each computing device / processing device receives the computer-readable program instructions from the network and transfers the computer-readable program instructions for storage in a computer-readable storage medium within the individual computing devices / processing devices.

[0079] Computer-readable program instructions for executing the operation of the present invention can be any of assembly instructions, instruction-set-architecture (ISA) instructions, machine instructions, machine-dependent instructions, microcode, firmware instructions, state setting data, configuration data for integrated circuits, or source code or object code written in any combination of one or more programming languages, such as object-oriented programming languages, such as Smalltalk, C++, etc., conventional procedural programming languages (e.g., the "C" programming language or similar programming languages). The computer-readable program instructions can be executed entirely on the user's computer, partially on the user's computer, partially as a stand-alone software package 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 scenario, the remote computer can be connected to the user's computer via any type of network, such as a local area network (LAN) or a wide area network (WAN), or the connection can be made to an external computer (e.g., via the Internet using an Internet service provider). In some embodiments, an electronic circuit, such as a programmable logic circuit, a field-programmable gate array (FPGA), or a programmable logic array (PLA), can execute the computer-readable program instructions by utilizing the state information of the computer-readable program instructions, by personalizing the electronic circuit.

[0080] Aspects of the invention are described herein with reference to methods, apparatus (systems), and computer program products or flowcharts or block diagrams of computer programs or combinations thereof according to embodiments of the invention. It will be understood that each block of the flowchart diagrams or block diagrams or combinations thereof, as well as combinations of multiple blocks in the flowchart diagrams or block diagrams or combinations thereof, can be implemented by computer-readable program instructions.

[0081] These computer-readable program instructions are provided to a computer processor or other programmable data processing apparatus to create means for implementing the functions / operations specified in one or more blocks of the flowchart diagrams or block diagrams or combinations thereof by causing instructions executed via the processor of the computer or other programmable data processing apparatus to implement the specified functions / operations, thereby creating a machine. These computer-readable program instructions may also be stored in a computer-readable storage medium that contains instructions for implementing the functions / operations specified in one or more blocks of the flowchart diagrams or block diagrams or combinations thereof, including a manufactured article that includes a computer-readable storage medium having stored thereon instructions for implementing the functions / operations.

[0082] These computer-readable program instructions may also be loaded onto a computer, other programmable data processing apparatus, or other device to cause instructions executed on the computer, other programmable data processing apparatus, or other device to implement the functions / operations specified in one or more blocks of the flowchart diagrams or block diagrams or combinations thereof, thereby creating a process implemented on the computer by executing a series of operational steps on the computer, other programmable apparatus, or other device.

[0083] The flowcharts and block diagrams in the drawings illustrate the architecture, functionality, and operation of a system, method, and computer program product or possible implementation of a computer program according to various embodiments of the present invention. In this regard, each block in the flowchart or block diagram may represent a module, segment, or portion of instructions, which includes one or more executable instructions for implementing a specified logical function. In some alternative implementations, the functions shown in the block may occur in a different order than shown in the drawings. For example, two blocks shown in succession may, in fact, be accomplished as one step that is executed simultaneously, substantially simultaneously, partially, or wholly in a temporally overlapping manner, depending on the functions involved, or the blocks may be executed in the reverse order. It should be noted that each block of the block diagram or flowchart or combination thereof, as well as combinations of multiple blocks of the block diagram or flowchart or combination thereof, can be implemented by a special-purpose hardware-based system that performs a specified function or operation, or can execute a combination of special-purpose hardware and computer instructions.

Claims

1. A computer-implemented method for loading data into a target database system, comprising: processing a history data set indicating a history of data loading into the target database system, and based on the processing, determining that loading a source table is predicted to occur in the target database system, wherein the history data set includes a plurality of entries, each of the plurality of entries indicating a source table and a time when the loading was performed; preparing a future target table in advance according to a defined table schema; and then, receiving a load request for loading the source table; loading the data of the source table into the future target table The method as described above.

2. The determining that loading is predicted to occur is performed in response to creating the source table in a source database system, wherein the source database system and the target database system are configured to synchronize data with each other, and the source table has the defined table schema; or, loading the data of the source table from the source database system into the current target table of the target database system, wherein the current target table has the defined table schema, The method according to claim 1.

3. The processing includes grouping the plurality of entries by table schema and using the time behavior of data loading for each of a plurality of groups to determine that loading is predicted to occur, wherein the defined table schema is the table schema of one of the plurality of groups. The method according to claim 1 or 2.

4. The method according to any one of claims 1 to 3, wherein the defined table schema of the future target table is obtained from the table schema of the source table using a unique mapping.

5. The method further includes repeating the method, wherein a future target table of the current iteration becomes the current target table for the next iteration, the method according to claim 2.

6. The method according to claim 5, wherein the current target table of the current iteration becomes the future target table of the next iteration.

7. The method according to claim 6, wherein the loading for the next iteration includes considering the content of the current target table of the current iteration as invisible.

8. The method according to claim 6, wherein the loading for the next iteration includes purging the content of the current target table of the current iteration.

9. The method according to any one of claims 1 to 8, wherein preparing the future target table includes creating an empty table using an asynchronous job.

10. The method according to claim 9, wherein the asynchronous job is started based on a predefined timer or is started immediately after the determining.

11. The method according to any one of claims 1 to 10, wherein loading the data of the source table into the future target table includes extracting data from the source table, inserting the extracted data into the target table, performing a commit operation, and modifying the view in the target database system to refer to the future target table, the view being configured to process the content of the source table in the target database system.

12. A computer program for causing one or more processors to execute each step of the method according to any one of claims 1 to 11, the computer program.

13. A computer system for loading data into a target database system, the computer system comprising a memory and a processor connected to the memory, and using the processor, Process a history data set indicating the history of data loading into the target database system, and based on the processing, determine that it is predicted that loading a source table will occur in the target database system, where the history data set includes a plurality of entries, and each of the plurality of entries indicates a source table and the time when the loading was executed; Prepare a future target table in advance according to a defined table schema; then, Receive a load request for loading the source table; Load the data of the source table into the future target table The computer system configured as described above.

14. Determining that it is predicted that the loading will occur is Creating the source table in the source database system, where the source database system and the target database system are configured to synchronize data with each other, and the source table has the defined table schema; or, Loading the data of the source table from the source database system into the current target table of the target database system, where the current target table has the defined table schema, The computer system according to claim 13, which is executed in response to.

Images