KR102038529B1 - System for processing real-time data modification of in-memory database - Google Patents

Abstract

The present invention relates to a real-time data change processing system of an in-memory database. The present invention relates to a real-time data of an in-memory database that provides a changed data capture (CDC) function for managing changed data in an in-memory database. A change processing system comprising: a metadata manager for managing metadata in the form of a system table for providing the CDC function, and providing a CDC setting function and a restoration function for previous information upon restart; And checking the changed data by using the log file recorded by the database when the database value is changed, and then determines an insert / update / delete transaction according to the changed data and writes it to the change table, and creates a subscription view for access to the changed data. It includes a log capture manager provided to the user. Therefore, the present invention reads the log file recorded in the in-memory database without any processing and dynamically checks the changed data, and provides metadata and related procedures so that other applications can easily manage the changed data. This enables data synchronization tools and ETL tools to take advantage of the changed data while significantly reducing the load on the in-memory database compared to traditional trigger methods, and improves overall system performance and facilitates management of changed data. Development costs can be reduced.

Description

Real-time data change processing system of in-memory database {SYSTEM FOR PROCESSING REAL-TIME DATA MODIFICATION OF IN-MEMORY DATABASE}

The present invention relates to a real-time data change processing system of an in-memory database, and more particularly, to a real-time data change of an in-memory database that can efficiently check and manage data changed by another application in the in-memory database. To a processing system.

As the demand for large data real-time processing increases, the demand for I / O on a large amount of data as well as faster data operation increases, which emphasizes low latency beyond the conventional disk-based method. The demand for in-memory DBMS is increasing.

In order to minimize the data latency between systems, the use of CDC has increased rapidly. In order to improve the data synchronization performance, the time stamp method has been changed from the timestamp method to the archive log-based technique. In order to reduce, it is necessary to shorten the data synchronization time, and processing requirements are generated for the unstructured data as well as the processing of the existing DBMS information.

As such, as the IT environment becomes more complex and the demand for data integration of multiple systems increases, the demand for change data management (CDC) to synchronize data in multiple databases in real time is increasing.

Change data capture (CDC) is a technology or design technique and structure that identifies changes to data in a database and automates the necessary subsequent processing (data transfer / sharing, etc.). CDC is widely used to build data warehouses and other data repositories based on real-time or near real-time data integration.

CDC can be implemented through a variety of technologies at various layers, from the storage hardware layer to the application layer.

In order to confirm the data change in the in-memory DBMS, the changed data was confirmed using the trigger function of the DBMS. Since the in-memory DBMS checks the changed data every time by using the trigger function, when processing a large amount of data, the load on the DBMS itself may be applied, resulting in overall performance degradation such as insert / delete / update operations of the DBMS. There is a problem. Therefore, it also affects the performance of other applications using the corresponding DBMS, lowers the performance of the overall system, and has difficulty in managing changed data in other applications, making it difficult to use.

As a prior art, Korean Patent No. 10-1605455 discloses a redo log generated by a transaction occurring in an operation in a database in real time to a remote location to replicate, thereby causing abnormal operation and abnormal termination that may occur during operation of a database system. It describes the method and system to minimize the network load and the replication load by transmitting only the log in which the change of data is recorded in real time in advance in case of a failure such as the real time.

Since the database redo log duplication technique uses a network, problems of data collision and delay may occur. In other words, unlike the disk sharing method, the database redo log duplication does not take into account changes to the data of other nodes when local transactions occur, but does not interfere with each other. Conflict) is a problem that occurs.

In addition, the database redo log duplication technique has a problem in that when the cumulative speed of the change transaction log of the sending node is faster than the transfer rate to the opposite node, the query result of the data in the sending node and the receiving node may be different at a specific point in time. In other words, this means that transaction logs that could not be transmitted to the opposite node through the network are accumulated in the sending node, which is called a gap of redundancy.

Korean Patent No. 10-1605455 "Data Redo Log Redundancy Method without Data Loss and System for It"

The present invention provides a CDC processing technique to dynamically check the changed data in the in-memory database from the log file and write it to the change table, reduce the load on the database by checking the data change, and easily change it in other applications. It provides a real-time data change processing system of an in-memory database that supports metadata and related procedures to manage data.

Among embodiments, a real-time data change processing system of an in-memory database may include real-time data of an in-memory database that provides a Changed Data Capture (CDC) function for managing changed data in the in-memory database. A change processing system comprising: a metadata manager for managing metadata in the form of a system table for providing the CDC function, and providing a CDC setting function and a restoration function for previous information upon restart; And checking the changed data by using the log file recorded by the database when the database value is changed, and then determines an insert / update / delete transaction according to the changed data and writes it to the change table, and creates a subscription view for access to the changed data. It characterized in that it comprises a log capture manager to provide to the user.

The metadata manager may include: change set metadata for generating at least one change table when the CDC function is executed, and storing and managing information on a change set for managing the change table; Change table metadata for storing and managing information about the change table; Source table metadata for storing and managing information of a source table in which the CDC function is being performed and information of an associated change table; Published column metadata that stores and manages information about the changed data in a column unit; Subscription metadata that stores information about a subscription object used by the CDC; A subscription table metadata for storing and managing information of the subscription and subscription view and a management number of the changed data; And subscription column metadata for storing and managing information on columns for confirming real-time data change through the CDC function.

The subscription entity of the subscription metadata may generate a thread for confirming the change of data, and insert a value into the change table by confirming the change of data according to the subscription state.

 In order to confirm the changed data, the user calls one of an extended procedure for projecting data from the change table to a subscription view and a purge procedure for deleting data in the change table. And determining a value of the management number in the database according to the called procedure.

The log capture manager checks the changed data by using a first log file recorded when the new value is inserted or updated to perform the CDC function and a second log file recorded when the existing value is deleted or updated. It is characterized by.

The log capture manager reads the log file sequentially and inserts the corresponding transaction into the change table after determining that the transaction is an insert transaction when there is no second log file in the following log file of the first log file. .

The log capture manager reads the log file sequentially and subtypes stored in the first log file when the second log file exists and the log file following the second log file is the first log file. (subtype) is characterized in that the type of the transaction is determined.

If the log capture manager determines that the subtype stored in the first log file is an update, then the information recorded in the second log file is a value corresponding to an update condition, and is recorded in the first log file. The information is inserted into the change table with the updated new value.

The log capture manager, when the subtype stored in the first log file is a delete, determines that the log capture manager deletes the change from the change table.

The log capture manager,

In the case where the second log file is continuously present while reading the log file sequentially, the log file is determined to be a deletion transaction and then deleted from the change table.

The real-time data change processing system of the in-memory database of the present invention can read the log file recorded in the in-memory database without any processing and dynamically check the changed data, and easily manage the changed data in other applications. By supporting metadata and related procedures, data synchronization tools and ETL tools can take advantage of the changed data while significantly reducing the load on the in-memory database compared to traditional triggering methods. The management can be easily performed, and the overall system development cost can be reduced.

1 is a view for explaining the overall flow of the real-time data change processing system of the in-memory database according to an embodiment of the present invention.
2 is a block diagram illustrating a configuration of a real-time data change processing system of an in-memory database according to an embodiment of the present invention.
FIG. 3 is a view for explaining change set metadata managed by a metadata manager, which is a part of FIG. 2.
FIG. 4 is a diagram illustrating change table metadata managed by a metadata manager, which is a part of FIG. 2.
FIG. 5 is a diagram illustrating source table metadata managed by a metadata manager, which is a part of FIG. 2.
FIG. 6 is a diagram illustrating published column metadata managed by a metadata manager, which is a part of FIG. 2.
FIG. 7 is a diagram illustrating subscription metadata managed by a metadata manager, which is a part of FIG. 2.
FIG. 8 is a diagram illustrating subscription table metadata managed by a metadata manager, which is a part of FIG. 2.
FIG. 9 is a diagram illustrating subscription column metadata managed by a metadata manager, which is a part of FIG. 2.
FIG. 10 is a diagram illustrating a process of confirming an insert transaction by a log capture manager, which is a part of FIG. 2.
FIG. 11 is a diagram illustrating an update transaction checking process by a log capture manager, which is a part of FIG. 2.
FIG. 12 is a diagram illustrating an embodiment of a delete transaction confirmation process of a log capture manager, which is a part of FIG. 2.
FIG. 13 is a diagram illustrating another embodiment of a delete transaction confirmation process of a log capture manager, which is a part of FIG. 2.

Configurations shown in the embodiments and drawings described in the present invention are merely preferred embodiments of the present invention, and do not represent all of the technical idea of the present invention, the scope of the invention is the embodiments and drawings described in the text It should not be construed as limited by That is, since the embodiments may be variously modified and may have various forms, the scope of the present invention should be understood to include equivalents capable of realizing the technical idea. In addition, the objects or effects presented in the present invention does not mean that a specific embodiment should include all or only such effects, the scope of the present invention should not be understood as being limited thereby.

All terms used herein have the same meaning as commonly understood by one of ordinary skill in the art unless otherwise defined. The terms defined in the commonly used dictionary should be interpreted to correspond with the meanings in the context of the related art, and should not be interpreted as having an ideal or excessively formal meaning not explicitly defined in the present invention.

The real-time data change processing system of an in-memory database according to an embodiment of the present invention provides various DBMS services including a publication / subscription model based distribution technology and a change data capture technology.

First, the publishing / subscription distribution technology is a data distribution technology in which a user who participates in a data communication domain becomes a publisher (data production) or a subscriber (data consumption) to produce and consume data desired by the user without a connection orientation of a service. The publication / subscription distribution technology has a feature of connectionless-oriented service based on message, and provides a suitable communication environment in real time by enabling access to data regardless of information (location, time, synchronization) of the service.

Next, Changed Data Capture (CDC) technology for managing changed data within the in-memory database, which identifies the changed data through the information recorded by the database when the user checks the changed data for a particular table. Technology.

1 is a view for explaining the overall flow of the real-time data change processing system of the in-memory database according to an embodiment of the present invention.

Referring to FIG. 1, the real-time data change processing system of an in-memory database reads a redo log recorded by the database 100 in the data capturing process 110 and changes a user-specified change table only for the changed data. 112). At this time, the redo log is a log file automatically recorded by the database 100 when the value of the database 100 is changed.

The data capturing process 110 records the modification of all data performed in the source table 111 in the transaction redo log of the database, reads the transaction redo log, and writes in the changed data designated change table where change data capture is set, Extract data from change tables and update data based on change data capture query functions using a separate extraction, transformation and loading (ETL) related data capture control process (140) do.

Through the data capturing process 110, the publisher identifies the source table 111 to which the change data should be captured, captures the change data, stores it in the designated change table (), and then the subscriber can access the change data. Allow The database 100 generates a subscription view 121 to allow the user to access the changed data, and the user checks the changed data in the designated change table through the generated subscription view 121.

The real-time data change processing system of the in-memory database of the present invention processes through the SP (Service Pack) package 130 to efficiently use the CDC functions. Here, an SP package is a collection of related PL / SQL procedures and functions stored in a database. In this case, the SP package may be designed by defining a specification and defining what functions are required for each procedure and what should be implemented.

2 is a block diagram illustrating a configuration of a real-time data change processing system of an in-memory database according to an embodiment of the present invention.

Referring to FIG. 2, the in-memory database 100 includes, but is not limited to, a log manager 210, a transaction manager 220, and a CDC module 230.

The log manager 210 checks the Redo Log recorded by the database 100 and checks the changed data from the Redo Log.

When changing the values of the change table and the system table, the transaction manager 220 manages information such as lock information and transaction ID for the table to which the data is reflected.

The CDC module 230 includes a metadata manager 231 and a log capture manager 232.

The metadata manager 231 manages metadata in the form of a system table for providing a CDC function, and provides a CDC setting function and a restoration function for previous information when restarting.

When the database value is changed, the log capture manager 232 checks the changed data using the log file recorded by the database, determines the insert / update / delete transaction according to the changed data, and records it in the change table, and accesses the changed data. Create a subscription view and provide it to the user.

FIG. 3 is a diagram illustrating change set metadata managed by a metadata manager which is a component of FIG. 2, and FIG. 4 is a diagram illustrating change table metadata managed by a metadata manager which is a component of FIG. 2. 5 is a diagram illustrating source table metadata managed by a metadata manager, which is a component of FIG. 2, and FIG. 6 is a diagram illustrating published column metadata managed by a metadata manager, which is a component of FIG. FIG. 7 is a diagram illustrating subscription metadata managed by a metadata manager, which is a component of FIG. 2, and FIG. 8 is a diagram illustrating subscription table metadata managed by a metadata manager, which is a component of FIG. 2. 9 is a diagram illustrating subscription column metadata managed by a metadata manager, which is a part of FIG. 2. A figure.

3 to 9, since the metadata manager 231 cannot perform the CDC function simply by reading log information of the database, metadata in the form of a system table in the database to store information related to the CDC function. Manage it.

First, as shown in FIG. 3, the change set metadata stores and manages information about a change set, which is an object used by the CDC, in a database. The change set is a set that manages change tables. Multiple change tables can be created in one change set. If a change set does not exist, a change table cannot be created.

This change set metadata table includes the name of the change set, the type of change source, a description of the change set that you set, the publisher name, and a unique ID for the change set.

Next, referring to FIG. 4, the change table metadata stores and manages information on a change table for storing changed data after checking the change data. The change table metadata table includes the change table owner name, change table name, source table owner name, source table name, publication ID, and change set ID.

Referring to FIG. 5, the source table metadata stores and manages information of a source table and an associated change table in which a CDC service is performed. The source table metadata table includes an owner name, a source table name, and a change table of a source table. Contains the owner name and change table name.

Referring to FIG. 6, the published column metadata stores and manages information on changed data in columns. The published column metadata table includes a change set name, an owner name of a change table, a name of a change table, Owner name of the source table, name of the source table, source column name (data capture target column), data type, data length, data total length (precision), data scale (decimal point), data nullable flag (data nullable) flag), including the data publication ID.

Referring to FIG. 7, the subscription metadata stores information about a subscription object used by the CDC. The subscription object creates a thread for performing change confirmation of the actual data, confirms the change of data according to the state of the subscription, Insert a value into the change table. This subscription metadata table contains the change set name, the owner of the change set, the subscription status, and the subscription name.

Referring to FIG. 8, the subscription table metadata stores and manages information of a subscription and a subscription view, and a management number (Control_number) of changed data. At this time, the management number is information for confirming how far the changed data has been read, and is not a value manipulated by the user, but a value managed inside the database. These subscription table metadata tables include the owner of the source table, the name of the source table, the subscription view name, the subscription name, the publication ID, the minimum and maximum values for extension / removal.

Therefore, the user can check the changed data through the subscription view, and the data shown in the subscription view is determined according to the management number existing in the subscription table metadata table. In addition, either the extended procedure for projecting the data from the change table to the subscription view to confirm the changed data, and the purge procedure for deleting the data in the change table. Call and determine the value of the management number in the database according to the procedure called by the user.

Referring to FIG. 9, the subscription column metadata stores and manages information on a column confirming real-time data change through a CDC function. The subscription column metadata table includes an owner of a source table, a source table name, a column name, Contains the subscription name.

FIG. 10 is a diagram illustrating a process of confirming an insert transaction by a log capture manager, which is a component of FIG. 2. FIG. 11 is a diagram illustrating a process of confirming an update transaction, by a log capture manager, which is a component of FIG. 2 is a view illustrating an embodiment of a process of confirming a deletion transaction by the log capture manager, which is a part of FIG. 2, and FIG. 13 is a diagram of another embodiment of a process of confirming the deletion transaction, by the log capture manager, which is a part of FIG. 2. It is a figure explaining.

10 to 13, the log capture manager must process insert transactions, update transactions, and delete transactions, respectively, in order to check the changed data from the log file and insert the changed data into the change table.

The log capture manager 232 checks the changed data by using the first log file (InsrtRecLog) and the second log file (LogicalVersioningLog) for CDC processing. At this time, InsrtRecLog is a log file recorded when inserting and updating as a log of new values, and LogicalVersioningLog is a log file recorded when updating and deleting as a log of existing values. If 1 is updated to 2, 2 (UN) is recorded in InsrtRecLog, and 1 (UO) is recorded in LogicalVersioningLog.

As shown in FIG. 10, the log capture manager 232 sequentially inserts a transaction into the change table after determining that the transaction is an insert transaction when there is no LogicalVersioningLog in a subsequent log file of InsertRecLog while sequentially reading the log file.

The log capture manager 232 determines the type of transaction according to the subtype stored in the InsertRecLog when the logicalVersioningLog exists while the log file is sequentially read and the log file following the LogicalVersioningLog is the InsertRecLog.

As shown in FIG. 11, when the log capture manager 232 determines that the update is a subtype stored in the InsertRecLog as an update transaction, the information recorded in the LogicalVersioningLog is a value (UO) corresponding to the update condition, and the information is recorded in the InsertRecLog. The recorded information is inserted into the change table with the updated new value.

As shown in FIG. 12, when the subtype stored in InsertRecLog is a delete, the log capture manager 232 deletes the change from the change table after determining that the delete transaction is a delete transaction.

On the other hand, as shown in Figure 13, the log capture manager 232 reads the log file in sequence, if there is a continuous LogicalVersioningLog, the log recorded in the LogicalVersioningLog to determine the transaction is deleted after deleting the transaction in the change table.

As described above, the present invention reads the log file recorded by the in-memory database without additional processing to confirm the changed data and dynamically confirms the changed data, and checks the metadata manager 231 and the CDC module 230 in the CDC module 230. Through the log capture manager 232, it is possible to efficiently manage the changed data.

Although described above with reference to a preferred embodiment of the present invention, those skilled in the art will be variously modified and changed within the scope of the invention without departing from the spirit and scope of the invention described in the claims below I can understand that you can.

100: in-memory database 210: log manager
220: transaction manager 230: CDC module
231: metadata manager 232: log capture manager

Claims (10)

In a real-time data change processing system of an in-memory database that provides a change data capture (CDC) function for managing changed data in an in-memory database,
A metadata manager that manages metadata in the form of a system table for providing the CDC function, and provides a CDC setting function and a restoration function for previous information upon restart; And
When changing the database value, check the changed data using the log file recorded by the database, determine the insert / update / delete transaction according to the changed data, write it to the change table, and create a subscription view for access to the changed data. Real-time data change processing system of the in-memory database comprising a log capture manager for providing a user.
The method of claim 1,
The metadata manager,
Change set metadata for generating at least one change table when performing the CDC function and storing and managing information on a change set managing the change table;
Change table metadata for storing and managing information about the change table;
Source table metadata for storing and managing information of a source table in which the CDC function is being performed and information of an associated change table;
Published column metadata that stores and manages information about the changed data in a column unit;
Subscription metadata that stores information about a subscription object used by the CDC;
A subscription table metadata for storing and managing information of the subscription and subscription view and a management number of the changed data; And
And a subscription column metadata for storing and managing information on a column for confirming a real-time data change through the CDC function.
The method of claim 2,
The subscription object of the subscription metadata creates a thread for performing data change check, and checks the data change according to the subscription state and inserts a value into the change table. Processing system.
The method of claim 2,
In order to confirm the changed data, the user calls one of an extended procedure for projecting data from the change table to a subscription view and a purge procedure for deleting data in the change table. And determining a value of a management number in the database according to the called procedure.
The method of claim 1,
The log capture manager checks the changed data by using a first log file recorded when the new value is inserted or updated to perform the CDC function and a second log file recorded when the existing value is deleted or updated. Real-time data change processing system of the in-memory database, characterized in that.
The method of claim 5,
The log capture manager,
When the log file is sequentially read while the second log file does not exist in the subsequent log file of the first log file, the transaction is determined as an insert transaction and inserted into the change table. Data change processing system.
The method of claim 5,
The log capture manager,
If the second log file is present while the log file is sequentially read, and a subsequent log file of the second log file is the first log file, the transaction is performed according to the subtype stored in the first log file. And determine the type of the real-time data change processing system of the in-memory database.
The method of claim 7, wherein
If the log capture manager determines that the subtype stored in the first log file is an update, then the information recorded in the second log file is a value corresponding to an update condition, and is recorded in the first log file. And inserting information into the change table with the updated new value.
The method of claim 7, wherein
And the log capture manager deletes the change table from the change table after determining that the subtype stored in the first log file is a delete transaction.
The method of claim 5,
The log capture manager,
And sequentially reading the log file while the second log file is present, and deleting the log file after deleting the log file from the change table.

Images