JP7495269B2 - Data management system and method - Google Patents

Description

The present invention relates to a data management system and a data management method.

Recent developments in database technology have led to the use of various database models (key-value, graph, etc.) in addition to the traditional relational model, depending on the characteristics of the data. On the other hand, due to the difficulty of treating multiple database models as separate databases, multi-model databases that can handle them in an integrated manner have been developed. For example, in Patent Document 1, a copy of a data group stored in an RDB (relational database) is created in an in-memory key-value store, and external queries are handled using the in-memory key-value store as much as possible, thereby shortening the query processing time.

US2017147664A1

However, it is difficult to migrate a system that is operated using a group of existing databases to a new multi-model database. In order to achieve processing performance, it is necessary to rearrange data in a database of an optimal database model taking into account the contents of queries such as data characteristics and data operations. Patent Document 1 does not mention rearranging data in a database of a database model taking into account such queries. For example, it does not mention analyzing queries for tables held by the database or the schema of the database, and arranging tables with many traversal operations such as list tables and hierarchical tables in a graph model, arranging data that can only be searched by a primary key such as an ID in a key-value model, and arranging tables with many searches by multiple keys, complex searches, and join operations in a relational model. Thus, Patent Document 1 does not disclose determining the database to be arranged according to differences in query contents such as data characteristics and data operations, and shortening the processing time for queries to tables compared to the conventional method.

One aspect of the present invention aims to provide a data management system and method that can determine the database in which data should be placed depending on differences in the content of queries obtained from analysis results, thereby reducing the processing time for queries on the data compared to conventional methods.

A data management system according to one aspect of the present invention is configured as a data management system having an analysis unit that analyzes a query program that queries a table stored in a first database of an existing database unit or a schema of the first database, an arrangement processing unit that arranges a table stored in the first database in a second database other than the first database based on the analysis result of the query program or the schema of the first database, and an arrangement reflection unit that reflects the arrangement in the existing database unit when the processing time of the query program for the table arranged in the second database is shorter than the processing time of the query program for the table stored in the first database.

According to one aspect of the present invention, the database in which data should be placed is determined according to the differences in the query content obtained from the analysis results, and the processing time for queries on the data can be reduced compared to conventional methods.

2 is a block diagram showing an example of a functional configuration of a data management device according to the present embodiment; FIG. FIG. 2 is a schematic diagram of a computer of the data management device. FIG. 13 illustrates an example of an execution result table. FIG. 13 is a diagram showing a trial determination table. FIG. 13 is a diagram illustrating a processing time determination table. FIG. 13 is a diagram illustrating an example of a layout correspondence table. FIG. 2 is a diagram illustrating an example of a first type database. FIG. 11 is a diagram illustrating an example of a second type database. FIG. 11 is a diagram illustrating an example of a third type database. 13 is a diagram illustrating an example in which a query format conversion unit converts a query program in an SQL format into a query program in a non-SQL format. FIG. 11 is a flowchart showing the procedure of a process (execution record accumulation process) for accumulating execution records of an inquiry program performed in the present system; 13 is a flowchart showing a processing procedure for rearranging tables stored in a database unit (rearrangement processing);

Embodiments of the present invention will be described below with reference to the drawings. The following description and drawings are examples for explaining the present invention, and some parts have been omitted or simplified as appropriate for clarity of explanation. The present invention can also be implemented in various other forms. Unless otherwise specified, each component may be singular or plural.

The position, size, shape, range, etc. of each component shown in the drawings may not represent the actual position, size, shape, range, etc., in order to facilitate understanding of the invention. Therefore, the present invention is not necessarily limited to the position, size, shape, range, etc. disclosed in the drawings.

In the following explanation, various types of information may be explained using expressions such as "table" and "list", but the various types of information may be expressed in other data structures. To indicate independence from data structure, "XX table", "XX list", etc. may be referred to as "XX information". When explaining identification information, when expressions such as "identification information", "identifier", "name", "ID", "number", etc. are used, these are interchangeable.

When there are multiple components with the same or similar functions, they may be described using the same reference numerals with different subscripts. However, when there is no need to distinguish between these multiple components, the subscripts may be omitted.

In addition, the following description may describe processing performed by executing a program, but the program is executed by a processor (e.g., a CPU (Central Processing Unit), a GPU (Graphics Processing Unit)) to perform a specified process using storage resources (e.g., memory) and/or interface devices (e.g., communication ports) as appropriate, so the subject of the processing may be the processor. Similarly, the subject of the processing performed by executing a program may be a controller, device, system, computer, or node having a processor. The subject of the processing performed by executing a program may be a calculation unit, and may include a dedicated circuit (e.g., an FPGA (Field-Programmable Gate Array) or an ASIC (Application Specific Integrated Circuit)) that performs a specific process.

The program may be installed in a device such as a computer from a program source. The program source may be, for example, a program distribution server or a computer-readable storage medium. When the program source is a program distribution server, the program distribution server may include a processor and a storage resource that stores the program to be distributed, and the processor of the program distribution server may distribute the program to be distributed to other computers. Also, in the following description, two or more programs may be realized as one program, and one program may be realized as two or more programs.

The following provides a detailed explanation of the repair assistance system and repair assistance method according to this embodiment when applied to an ATM, but the system is not limited to this example and can be applied to a variety of devices and equipment.

1 is a block diagram showing an example of the functional configuration of the data management device 10 in this embodiment. As shown in FIG. 1, the data management device 10 has a query input unit 101 that accepts input of a query input program (first query program) that queries a table held by a database in a query format (first query format) such as SQL (Structured Query Language), a data placement management unit 102 that manages the execution record of the query input program and the database and table used in the program, a query format conversion unit 105 that converts the query format into a program (second query program) of a query format (second query format) different from the query format of non-SQL (Non Structured Query Language), queries a table held by the database using the query program of the converted different query format, and outputs the result, a query result conversion unit 106 that converts the result of the query by the query program of the different query format output from the query format conversion unit 105 from the different query format to the query format, and a query result output unit 111 that outputs the query result converted into the query format as a query result for the query input program.

The data placement management unit 102 includes an execution record table 103 that stores the execution record of the query input program received by the query input unit 101, a trial judgment table 1031 for determining which tables are to be subject to a trial of rearrangement among the tables stored in the data management device 10, a processing time judgment table 1032 that aggregates the processing time of the execution record table 103 for each table, a trial database unit 118 that stores a model database converted from the model database that holds the table determined by the trial judgment table 1031, and a placement correspondence table 104 that stores the correspondence between the table and the model database that holds the table. The data management device 10 also includes a database unit 107 that stores a first type database 108 consisting of a relational model database, a second type database 109 consisting of a key-value model database, and a third type database 110 consisting of a graph model database.

The data management device 10 also includes a query analysis unit 112 that analyzes the query format and query content of the query input program managed by the data placement management unit 102, a data schema analysis unit 113 that analyzes the structure of a database of a model that holds tables used in the query input program managed by the data placement management unit 102, a data placement trial determination unit 114 that determines whether or not to attempt relocation of the table determined to be the target of the relocation attempt, a data placement trial unit 115 that attempts relocation of the table determined by the data placement trial determination unit 114 to be attempted for relocation, a query trial unit 116 that attempts a query to the table for which relocation has been attempted by the data placement trial unit 115, and a processing time determination unit 117 that reflects the table before the relocation attempt in the database of the model that holds the table for which relocation has been attempted when the processing time when a query is made to the table for which relocation has been attempted by the query trial unit 116 is shorter than the average processing time when a query is made to the table before the relocation attempt.

The data management device 10 also has a trial database unit 118 that stores databases rearranged by the data arrangement trial unit 115. In FIG. 1, as an example, a second type database 119 consisting of a key-value model database and a third type database 120 consisting of a graph model database are stored as model databases that hold tables for which rearrangement has been attempted.

The data management device 10 can be realized, for example, by a general computer 200 equipped with a CPU 201, memory 202, an external storage device 203 such as a hard disk drive (HDD), a reading/writing device 207 for reading and writing information from a portable storage medium 208 such as a compact disk (CD) or a digital versatile disk (DVD), an input device 206 such as a keyboard or mouse, an output device 205 such as a display, a communication device 204 such as a network interface card (NIC) for connecting to a communication network, and an internal communication line (called a system bus) 209 such as a system bus that connects these together, as shown in FIG. 2 (schematic diagram of a computer).

For example, the databases and tables stored in the data management device 10 can be realized by the CPU 201 reading them from the memory 202 or the external storage device 203 and using them. In addition, the query input unit 101, data placement management unit 102, query format conversion unit 105, query result conversion unit 106, query result output unit 111, query analysis unit 112, data schema analysis unit 113, data placement trial determination unit 114, data placement trial unit 115, query trial unit 116, and processing time determination unit 117 of the data management device 10 can be realized by the CPU 201 loading a predetermined program stored in the external storage device 203 into the memory 202 and executing it. In the data management device 10, the CPU 201 may operate the input device 206 to realize the input function of the query input unit 101. In the data management device 10, the CPU 201 may operate the output device 205 to realize the output function of the query result output unit 111. The data management device 10 may also have a communication unit in which the CPU 201 can operate the communication device 204 to realize a communication function.

The above-mentioned predetermined program may be stored (downloaded) in the external storage device 203 from the storage medium 208 via the reading/writing device 207 or from the network via the communication device 204, and then loaded onto the memory 202 and executed by the CPU 201. It may also be loaded directly onto the memory 202 from the storage medium 208 via the reading/writing device 207 or from the network via the communication device 204, and executed by the CPU 201.

In the following, each unit of the data management device 10 is provided in a general computer as hardware, but all or part of these may be provided in one or more computers, such as a cloud, in a distributed manner, and similar functions may be realized by communicating with each other. The operation of each unit of the data management device 10 and examples of the data it holds will be explained using flowcharts.

Figure 3 is a diagram showing an example of the execution record table 103. As shown in Figure 3, the execution record table 103 stores a correspondence between a query ID for identifying a query program and a query that is the specific content of the query identified by the query ID. In Figure 3, for example, queries with query IDs "1" to "6" indicate that queries were made to "Table A" using "Part ID" as the primary key. As will be described later, for tables that have a certain number of searches using a primary key such as an ID like "Part ID", an attempt is made to place them in the key-value model database if they satisfy certain conditions.

In addition, in Figure 3, the query with query ID "7" begins with "WITH RECURSIVE", indicating that a recursive operation was performed on "Table B" as shown below under "UNION ALL". As will be described later, for tables that have a certain number of traversal operations, such as recursive operations like "WITH RECURSIVE", such as list data or hierarchical data, an attempt is made to place them in a graph model if certain conditions are met.

Furthermore, the query with query ID "8" indicates that a join operation between "Table B" and "Table C" was performed, as indicated by "JOIN." As will be described later, an attempt is made to place tables in a relational model when there are searches using at least multiple keys, complex searches combining a certain number of conditions, or tables with a certain number of join operations.

In FIG. 3, the processing time of each query program identified by the query ID is also stored. For example, the query with query ID "1" takes 0.51 seconds to process. The data placement management unit 102 receives the processing time included in the query result from the query format conversion unit 105 and writes the processing time into the execution record table 103.

Figure 4 is a diagram showing the attempt judgment table 1031. As shown in Figure 4, the attempt judgment table 1031 stores the table name and the access method for the table obtained by analyzing the database in association with a threshold value for determining whether or not to attempt to rearrange the table. For example, Figure 4 shows that as a result of the data schema analysis unit 113 analyzing the database, 60% of queries to table A were "primary key search", 25% were "recursive search", and 15% were "other". It also shows that the condition for attempting to rearrange table A is when any of the access methods exceeds "55%".

In addition, in FIG. 4, the access method "primary key search" for table A is 60%, exceeding the threshold, so an attempt is made to relocate table A to a key-value model database (second type database 119). Also, the access method "recursive search" for table B is 75%, exceeding the threshold, so an attempt is made to relocate table B to a graph model database (third type database 120).

Figure 5 is a diagram showing the processing time determination table 1032. As shown in Figure 5, the processing time determination table 1032 stores the table names obtained by analyzing the database in association with the average processing time, which is the average value of the processing time for the table. Figure 5 shows, for example, that the average processing time for table A was 0.55 seconds. The query analysis unit 112 aggregates the processing times in the execution record table 103 for each table, calculates the average value, and writes this into the average processing time.

Figure 6 is a diagram showing an example of the arrangement correspondence table 104. Figure 6 (a) shows the arrangement correspondence table 104a before the table rearrangement. Also, Figure 6 (b) shows the arrangement correspondence table 104b after the table rearrangement. As shown in these figures, the arrangement correspondence table 104 before the table rearrangement stores the table name of the table stored in the database unit 107, the database name of the database in which the table with the table name is stored, and the model of the database with the database name in association with each other. Figure 6 (a) shows, for example, that a table with the table name "Table C" is stored in a relational model database with the database name "Database X". Also, Figure 6 (b) shows, for example, that a table with the table name "Table C" has been rearranged and stored in a key-value model database with the database name "Database Y". The processing time determination unit 117 compares the average processing time calculated by the query analysis unit 112 with the processing time when the query attempt unit 116 made the query, and if the latter is shorter than the former, it reflects the rearrangement of the tables and updates the database name in the layout correspondence table 104.

Figure 7 is a diagram showing an example of a first type database. As shown in Figure 7, the first type database 701 stores a relational database with a "part ID" as the primary key. Also, Figure 8 is a diagram showing an example of a second type database. As shown in Figure 8, the second type database 801 stores a key-value database with a tree structure with department ID "1" and department name "Company A" at the apex. Also, Figure 9 is a diagram showing an example of a third type database. As shown in Figure 9, the third type database 901 stores a graph database with nodes identified by "employee ID" at the apex.

Figure 10 is a diagram showing an example of the query format conversion unit 105 converting a query input program in SQL format into a query input program in non-SQL format. Figure 10(a) shows an example of a query when a query input program in SQL format is converted into a key-value model, and Figure 10(b) shows an example of a query when a query input program in SQL format is converted into a graph model. The query format conversion unit 105 queries the database in the database unit 107 using the query input program converted from the SQL format into the non-SQL format. The query result conversion unit 106 converts the query results into SQL format, and the query result output unit 111 outputs the query results for the query input program.

Figure 11 is a flowchart showing the procedure for accumulating execution records of the query program 101 (execution record accumulation process) performed in this system. In the execution record accumulation process, the query input unit 101 first accepts input of the query program 101 (S1101). The data placement management unit 102 analyzes the query program and registers the analyzed query program in the execution record table 103 shown in Figure 3 (S1102).

The query format conversion unit 105 converts the query input program in SQL format into a query input program in non-SQL format, executes a query, and outputs the query result including the query processing time (S1103). The query result conversion unit 106 converts the query result from non-SQL format into SQL format, and outputs it to the query result output unit 111. The query format conversion unit 105 also outputs the query result including the above-mentioned processing time to the data placement management unit 102, and the data placement management unit 102 writes and registers the processing time in the execution record table 103 (S1104).

Figure 12 is a flowchart showing the procedure for rearranging tables stored in the database unit (rearrangement process). The rearrangement process is executed when a certain number of programs or more are registered in the execution record table 103 on a regular or irregular basis.

First, the query analysis unit 112 analyzes the query format and query contents of the query input program, aggregates the processing times of the execution record table 103 for each table, calculates the average value, and writes the average processing time (S1201). Next, the data schema analysis unit 113 analyzes the structure and access method of the database that holds the tables used in the query input program, calculates the proportion of the access method, and writes the proportion of the access method in the access method of the trial judgment table 1031 (S1202). The data placement trial judgment unit 114 refers to the threshold value of the trial judgment table 1031 and judges whether or not to attempt to rearrange the table. The data placement trial unit 115 reads the table for which the data placement trial judgment unit 114 has determined that the table rearrangement should be attempted, for example, a table for which the proportion of the access method exceeds the threshold value, and rearranges the read table from the database of the model that holds the table to a database of another model different from the model (S1203).

The query trial unit 116 makes a query to the table relocated by the data allocation trial unit 115 (S1204). For example, the query trial unit 116 reads out a query input program in a non-SQL format that has been converted from SQL format by the query format conversion unit 105, and makes a query.

If the processing time taken when the query attempt unit 116 makes a query is shorter than the average processing time taken when a query is made to the table before the relocation is attempted, the processing time determination unit 117 reflects the table before the relocation is attempted in the model database that holds the table for which the relocation is attempted (S1205).

As described above, the present system includes an inquiry program for making an inquiry to a table stored in a first database (e.g., a first type database 108) in an existing database unit (e.g., a database unit 107) or an analysis unit (e.g., an inquiry analysis unit 112, a data schema analysis unit 113) for analyzing the schema of the first database; an arrangement processing unit (e.g., a data arrangement trial unit 115) for allocating a table stored in the first database to a second database (e.g., a trial database unit 118) other than the first database based on the analysis result of the inquiry program or the schema of the first database; and an arrangement reflection unit (e.g., a processing time determination unit 117) for reflecting the arrangement in the existing database unit when the processing time of the inquiry program for the table allocated in the second database is shorter than the processing time of the inquiry program for the table stored in the first database.
For example, in the rearrangement process, the query (inquiry content) issued from a program such as a user application and the data schema (data characteristics) stored in the existing database unit are analyzed, and in the data rearrangement trial unit, tables can be rearranged to shorten the processing time. Depending on the differences in the inquiry content obtained from the analysis results, the database in which the data should be placed is determined, making it possible to shorten the processing time for inquiries regarding the data compared to the conventional method.

The placement processing unit places the table in the second database of a graph model if a query to a table stored in the first database includes a certain number of tracing operations, places the table in the second database of a key-value model if a query to a table stored in the first database includes a certain number of searches on primary keys, and places the table in the second database of a relational model if a query to a table stored in the first database includes at least one of a search on multiple keys, a complex search combining a certain number of conditions, or a certain number of join operations. Therefore, the database in which data should be placed can be determined depending on the difference in the query content, and the processing time for queries on data can be reduced compared to conventional methods.

10 Data management device 101 Query input unit 102 Data placement management unit 103 Execution record table 1031 Attempt judgment table 1032 Processing time judgment table 104 Placement correspondence table 105 Query format conversion unit 106 Query result conversion unit 107 Database unit 111 Query result output unit 112 Query analysis unit 113 Data schema analysis unit 114 Data placement attempt judgment unit 115 Data placement attempt unit 116 Query attempt unit 117 Processing time judgment unit 118 Attempt database unit

Claims (6)

a query program for querying a table stored in a first database included in the existing database unit or an analysis unit for analyzing a schema of the first database;
an arrangement processing unit that arranges a table stored in the first database in a second database other than the first database based on an analysis result of the query program or a schema of the first database;
an arrangement reflecting unit that reflects the arrangement in the existing database unit when a processing time of the inquiry program for the table arranged in the second database is shorter than a processing time of the inquiry program for the table stored in the first database ,
the placement processing unit places the table in the second database of a graph model when a query to a table stored in the first database includes a certain number of traversal operations or more.
A data management system comprising: the placement processing unit places the table in the second database of a key-value model when a query to a table stored in the first database includes a search using a certain number or more of primary keys;
2. The data management system according to claim 1. the placement processing unit places the table in the second database of a relational model when a query to a table stored in the first database includes at least one of a search using a plurality of keys, a complex search combining a certain number of conditions, and a certain number of join operations;
2. The data management system according to claim 1. 1. A computer-implemented data management system, comprising:
an analysis unit that analyzes a query program that queries a table stored in a first database included in the existing database unit or a schema of the first database;
a placement processing unit places a table stored in the first database in a second database other than the first database based on an analysis result of the query program or a schema of the first database;
when a processing time of the query program for the table arranged in the second database is shorter than a processing time of the query program for the table stored in the first database, the arrangement reflecting unit reflects the arrangement in the existing database unit ,
the placement processing unit places the table in the second database of a graph model when a query to a table stored in the first database includes a certain number of traversal operations or more.
A data management method comprising: the placement processing unit places the table in the second database of a key-value model when a query to a table stored in the first database includes a search using a certain number or more of primary keys;
5. The data management method according to claim 4 . the placement processing unit places the table in the second database of a relational model when a query to a table stored in the first database includes at least one of a search using a plurality of keys, a complex search combining a certain number of conditions, and a certain number of join operations;
5. The data management method according to claim 4 .

Images