JP2015045996A - Distributed query processing apparatus, processing method, and processing program - Google Patents

Abstract

PROBLEM TO BE SOLVED: To obtain a distributed query processing apparatus by which a query processing engine is selected in accordance with characteristics of query processing engines and contents of a query in a shared database system.SOLUTION: A system in which one database is shared among a plurality of query processing engines and processing of a query posted from a client is performed, includes: a multi-database management data 10 which manages database information 11 about the database (shared database 40) and engine information 12 about characteristics of the query processing engines 41; a distributed query generation unit 15 which selects an adequate query processing engine on the basis of the analysis result of a query posted from a client 2 and decomposes and reconstructs the query; and a query execution control unit 16 which executes processing of the query by the adequate query processing engine and receives the final execution result to transfer it to the client 2.

Description

  In a data sharing type database system in which a plurality of query processing engines share one database, the present invention divides a query according to the characteristics of the query processing engine and the content of the query, and distributes the processing automatically. The present invention relates to a query processing device, a processing method, and a processing program.

Many pieces of information handled by organizations such as sales data and customer data are stored in a database system and used by various applications. Ideally, it is desirable that all data be stored in one database system without duplication, and that all applications use one database system.
However, in reality, multiple database systems are built according to the purpose, and the exact same data is stored in multiple database systems, or there are multiple database systems that hold similar data in different data formats and granularities. Built.
As a result, when building a new application, it is necessary to search for a database system that has the necessary data, and to investigate which database system should be connected to it. There was a problem of becoming.

In order to solve such a problem, a multi-database system (or data virtualization system) has been developed in which a plurality of database systems are virtually viewed as one database system.
For example, as disclosed in Patent Document 1, a plurality of hierarchical database systems are integrated into a virtual schema by data mapping, and a query is distributed to a database system that holds data to be processed during query execution. A multi-database system has been proposed.
According to this system, the results of queries executed in each database system are collected in the center, and are integrated into one to output the results. However, it is not considered that a plurality of database systems hold the same data, and only one of the plurality of database systems can be selected in order to avoid duplication of data when generating a virtual schema.

Further, in the multi-database system described above, a technique disclosed in Patent Document 2 has been proposed as a query processing technique when there are a plurality of database systems that hold the same data.
According to this method, when the same table exists in multiple database systems, one virtual table name is assigned to the same table, an appropriate table is selected at the time of query execution, and it is transparently the same as the user The use of different tables is realized.

On the other hand, various database systems have appeared depending on the scale of data and the type of processing due to the development of distributed processing technology as the data scale increases. Along with this, in order to avoid the construction of multiple database systems, a new type of database that separates a database that stores actual data and a query processing engine (DBMS) that processes queries such as SQL (Structured Query Languge) A system (hereinafter referred to as a data sharing database system) has appeared.
In such a system, data is collected in a single database to avoid duplication. This single database is shared by a plurality of query processing engines, and by using different query processing engines according to the type of processing, the scale of the target data, etc., more efficient data processing has become possible.

Japanese Patent Application Laid-Open No. 07-141399 JP 2008-112289 A

  When using a data sharing database system in which one database is shared by a plurality of query processing engines, the user may not know what characteristics each query processing engine has. In such a case, problems such as selecting a query processing engine that cannot execute a specific query or selecting a slow query processing engine that is not good at specific processing may occur.

  In the existing multi-database system as in Patent Document 1, a configuration like the data sharing database system described above is not considered. Therefore, when viewed from the multi-database system, even a shared database is recognized as a plurality of database systems having the same data.

Patent Document 2 considers application to a data sharing database system. However, allocation to a plurality of query processing engines that share one database is a costly process that causes data movement between database systems. In order to recognize that there is, query division across multiple query processing engines is not performed.
In addition, even if the cost is estimated to be low and the query is divided for each query processing engine, in the existing multi-database system, the result of each query processing is displayed for each query even though the data is on the same database. It is necessary to collect from the engine and perform join processing etc. on the management server of the multi-database system, and the shared database cannot be used effectively. Assuming that the distributed query has a dependency relationship, useless data movement occurs in which the processing result on the management server is rewritten in the same original database.

  The present invention has been proposed in view of the above circumstances, and in a data sharing database system in which a plurality of query processing engines share a single database that was not assumed in the conventional multi-database technology, the characteristics of the query processing engine An object of the present invention is to provide a distributed query processing device, a processing method, and a processing program in which a query processing engine is selected according to the contents of a query.

In order to achieve the above object, the distributed query processing apparatus of the present invention (Claim 1) is a data sharing type database system in which a plurality of query processing engines share a single database and process a query posted from a client. It is characterized by including the following configuration.
Multi-database management data for managing database information related to the database and engine information related to characteristics of the query processing engine.
A distributed query generation unit that selects an appropriate query processing engine based on an analysis result of a query posted from the client, and decomposes / reconstructs the query.
A query execution control unit that executes processing of the query by the appropriate query processing engine, and receives a final execution result and transfers it to the client.

  According to a second aspect of the present invention, in the distributed query processing device according to the first aspect, the query execution control unit creates an intermediate table of the decomposed query on the database when executing the query processing.

Claim 3 is a distributed query processing method for queries posted from clients in a data sharing database system in which a plurality of query processing engines share one database.
Generate or obtain a query plan indicating the execution plan of the query based on the query posted from the client, and analyze the query plan to execute the processing flow executed in the query processing engine, the table to be read and the data flow of the intermediate data Query plan generation / acquisition / analysis procedure to be created,
Using the processing flow and data flow generated in the query plan generation / acquisition / analysis procedure and the database information of the multi-database management data, the table size, the data size of the intermediate data in each processing on the processing flow, each processing A calculation procedure for calculating the resources required for
An engine selection procedure for selecting a query processing engine for each process based on the calculated data size and necessary resources, and engine information of multi-database management data;
A query generation procedure for generating a query based on the information of the query processing engine selected for each process;
It is characterized by performing.

Claim 4 is the distributed query processing method of claim 3,
The query generation procedure includes:
If the process spans multiple query processing engines,
Temporary table creation procedure to generate a temporary table at the beginning of a series of query processing,
A write procedure for the temporary table which is the last process in each query processing engine;
At the end of the series of query processing, the temporary table deletion procedure,
It is characterized by including.

  A fifth aspect of the present invention is a distributed query processing program that can be executed by a computer for each procedure of the distributed query processing method according to the third or fourth aspect.

  According to the present invention, a query for a data sharing database system in which a single database is shared by a plurality of query processing engines is considered in consideration of characteristics of each query processing engine, processing status, processing data size of the query, and query processing content. Divide the query into a query that uses the temporary table created on the shared database and distribute it to the query processing engine.

  As a result, in a data sharing type database system in which a plurality of query processing engines share a single database that has not been assumed in the conventional multi-database technology, without considering the characteristics of the query processing engine transparently from the user, A more suitable query processing engine can be selected.

  In addition, the query is divided in consideration of the characteristics of each query processing engine and the processing contents of the query, and the intermediate data is stored in a temporary table created on a shared database, so that high-speed queries that eliminate unnecessary data movement Processing is possible.

It is a block diagram which shows the whole structure of the distributed query processing system containing the distributed query processing apparatus of this invention. It is a block diagram for demonstrating the process in the distributed query process production | generation part of a distributed query processing apparatus. It is a block diagram which shows the whole structure of a distributed query processing system. It is a table | surface which shows the memory size in a query engine. It is a table | surface which shows the structure of the table a. It is a table | surface which shows the structure of the table b. This is a sample SQL query. It is a processing flow figure of a SQL query. It is a data flow figure of a SQL query. 10 is a table showing data sizes in the data flow of FIG. 9. FIG. 6 is a processing flow diagram in the query processing engine X. FIG. 6 is a processing flow diagram in a query processing engine Y. It is an example of the SQL query created by dividing the SQL query of FIG. It is an example of the SQL query created by dividing the SQL query of FIG.

An example of an embodiment of a distributed query processing system including the distributed query processing device 1 of the present invention will be described with reference to FIG.
The distributed query processing device 1, which is a main part of the distributed query processing device system, processes a query processing request from the client 2 in the database system (data sharing database system) 4 via the network 3. , A system that divides queries according to the characteristics of the query processing engine and the contents of the query, and automatically distributes the queries to process them. Software via a recording medium storing the distributed query processing program or the Internet It is built on the computer by installing it by downloading.

A computer in which the distributed query processing device 1 is constructed includes a ROM that stores a basic program including an operating system (OS) and various basic devices, and a hard disk drive (HDD) that stores various programs and data. Media drive device that reads programs and data from storage media such as CR-ROM and DVD, CPU that executes programs, RAM that provides a work area for this CPU, and input / output interface (I / F) A general configuration mainly includes a connected display device such as a display, a keyboard, and a mouse, and a parallel / serial I / F that communicates with an external device.
In the distributed query processing device 1 of the present embodiment, the distributed query processing program is input from the serial / parallel I / F or read by the media drive device and stored in advance in the HDD. The distributed query processing program is stored in a storage medium, read by the media drive device, and installed in the HDD.

  The distributed query processing system includes a distributed query processing device 1, a client 2 that requests query processing from the distributed query processing device 1, and a database system 4 connected via a network 3. The database system 4 includes one shared database 40 and a plurality of query processing engines 41, and the shared database 40 is shared by the plurality of query processing engines 41.

  The client 2 refers to software that requests query processing from the database system 4, such as a Web application server or a BI tool, and hardware that implements the software.

The distributed query processing device 1 includes multi-database management data 10, a data update unit 13, an access management unit 14, a distributed query generation unit 15, and a query execution control unit 16.
The multi-database management data 10 stores database information 11 in which various information of the shared database 40 is stored, and information on a plurality of query processing engines 41 as a database management system that manages the shared database 40 of the database system 4. By having the engine information 12, various information is held and managed.

  The database information 11 includes a configuration of a schema group in the shared database 40, a size of each schema, an access frequency, an attribute list in each schema, a name, a type, a size, additional information of each attribute, and a configuration of an intermediate table in query processing to be described later. And information on the shared database 40 including the history of the size, the structure of the output result in the query processing and the history of the size.

  The engine information 12 includes the number of nodes, the number of CPU clocks, the number of CPU cores, the memory size, the network bandwidth, the disk size, the disk access bandwidth, the query processing engine name, the presence / absence of metadata and database sharing, the number of simultaneously executable queries, Characteristics of each query processing engine 41, including non-executable queries, query algorithm information, resource usage (CPU usage, memory usage, network traffic, disk usage, disk swap usage, disk I / O) Information about is managed.

  The data update unit 13 updates the multi-database management data 10 by collecting the engine information 12 of the query processing engine 41 and the database information 11 of the shared database 40 in the database system 4 periodically or upon user request. Alternatively, the data updating unit 13 updates the multi-database management data 10 by receiving data input from the user via the client 2.

  The access management unit 14 manages the access status from the client 2 and controls to output the query to the distributed query generation unit 15 when a query is posted.

  The distributed query generation unit 15 analyzes the query posted from the client 2, selects an appropriate query processing engine 41 and decomposes / reconstructs the query based on the database information 11 and the engine information 12 of the multi-database management data 10. I do. Detailed procedures for query decomposition / reconstruction (creation of a distributed query) and engine selection will be described later.

  The query execution control unit 16 executes query processing with an appropriate query processing engine 41 and receives a final execution result and transfers it to the client 2. Further, when executing the query processing, an intermediate table of the decomposed query is created on the shared database 40 as necessary.

A plurality of database query processing engines 41 through a plurality of deworkers of the database system 4 process a query posted to the shared database 40.
The shared database 40 provides data necessary for query processing to the query processing engine 41.

  The distributed query processing device 1 and each query processing engine 41 are connected via the network 3, and the distributed query generated by the distributed query processing device 1 is transferred to each designated query processing engine 41 through the network 3, and each query Similarly, the processing result is transferred from the query processing engine 41 to the distributed query processing device 1 through the network 3.

The shared database 40 includes not only a case where the shared database 40 is physically one, but also a case where a plurality of databases are completely synchronized or distributed to store the data in a virtual manner. For example, when multiple databases that are physically separated are written to one database and the update is instantly applied to all other databases, one shared database configuration As applied.
The database here includes physical data in a database system stored in an information storage medium such as a hard disk drive (HDD) or memory, and logical information on the data structure of the database system such as physical data location and table schema ( Metadata).

Next, details of query distribution processing in the distributed query processing device 1 will be described.
When a query is posted from the client 2 to a data source data sharing type database that is shared by a plurality of query processing engines 41 that share a single shared database 40, the access management unit 14 receives the query and sequentially receives it. The query is delivered to the distributed query generation unit 15.

At this time, the distributed query generation unit 15 converts the query posted by the client 2 into a distributed query to the appropriate query processing engine 41 in the procedure shown in FIG.
In the query plan generation / acquisition / analysis, a flow of processing executed in the query processing engine by generating or acquiring a query plan indicating a query execution plan based on the query posted from the client 2 and analyzing the query plan. Then, a data flow of the table to be read and the intermediate data is created (step 21).

  In the data size and resource calculation, the processing flow and data flow generated by the query plan generation / acquisition / analysis, and the database information 11 of the multi-database management data 10 are used. The data size and other resources necessary for each process are calculated (step 22).

  In the engine selection, a target query processing engine for each process is selected. At this time, a query processing engine is selected on the basis of a predetermined query processing engine selection criterion based on the data size and necessary resources calculated above and the engine information 12 of the multi-database management data 10 ( Step 23).

In the query generation, a query is generated for each process based on information of the selected query processing engine (step 24).
At this time, a query is generated along the processing flow, but if the processing flow spans multiple query processing engines, a query statement that generates a temporary table is inserted at the beginning of a series of query processing, and each query processing engine The last process is changed to an insertion process for the temporary table, and a deletion process for the temporary table is inserted at the end of the series of query processes.
After selecting a query engine, if the query cannot be divided as it is, the engine-specific query is reconstructed (recreated) from the processing flow or data flow.

Next, a method for performing query processing will be described with reference to a specific example.
FIG. 3 shows an example of a system configuration in actual query processing. The query processing engine X and the query processing engine Y use a shared database A. With this configuration, the client 2 can see two databases having the same data.

For example, the query processing engine X can always execute stably in proportion to the data size, while the query processing engine Y performs processing for tables up to the physical memory size (32 GB) compared to the query processing engine X. Assume that you can't run queries on data that is fast but beyond this. Each physical memory of the query processing engine X and the query processing engine Y is as shown in FIG.
Based on this assumption, if the processing target data exceeds the physical memory size of the query processing engine Y as a distribution condition, it is distributed to the query processing engine X, and the processing for the processing target data less than the physical memory size of the query processing engine Y is processed by query processing. Distribute to engine Y.

Query processing will be described by taking an SQL query (FIG. 7) for table a (FIG. 5) and table b (FIG. 6) as an example.
This SQL query performs join processing (JOIN processing) on tables a and b under the condition that the attribute id contained in table a is equal to the attribute aid contained in table b, and the result is "included in table a. The records with the same attribute date ”, and calculate the total value of the sales of table b linked to this (AGGREGATE process (GROUP BY + SUM)), and sort this record in descending order by date value (ORDER BY process) The content is to do.

When the SQL query of FIG. 7 is posted from the client 2, the distributed query generation unit 15 generates a query plan, and in accordance with the processing flow of FIG. Create a data flow for.
Here, “reading table a” (step 81a) outputs “a” (step 91a), “reading table b” (step 81b) outputs “b” (step 91b), and “JOIN processing” ( In step 82), “temp_join” is output (step 92), “AGGREGATE processing (GROUP BY + SUM)” (step 85) is output “temp_aggr” (step 95), and “ORDER BY processing” (step 86) is output. Is output (step 96).

From this data flow and the corresponding processing and database information, the size of each data in the flow is estimated as shown in FIG. By comparing this data size with the physical memory of FIG. 4 and determining whether the input data or the output data exceeds the physical memory size, the query processing engine that performs each process is determined.
In other words, since all of the processing up to “JOIN processing” exceeds the physical memory size 32GB of query processing engine Y, “reading table a”, “reading table b”, and “JOIN processing” are processed by query processing engine X. Then, the remaining “AGGREGATE processing (GROUP BY + SUM)” and “ORDER BY processing” are performed by the query processing engine Y.

In the case of the query processing described above, since processing is performed across multiple query processing engines, in order to exchange data between the query processing engine X and the query processing engine Y, intermediate data (temporary table) is stored on the shared database A. temp) is created and used.
In other words, the temporary table creation process (inserts a query statement that generates a temporary table) at the beginning of a series of query processes, and the last process in each query processing engine is changed to a write process (insert process) to the temporary table Then, a temporary table deletion process is added at the end of a series of query processes.

  FIG. 11 shows a processing flow in the query processing engine X. In addition to the distributed processing, first, the temporary table temp creation processing (step 80), and the processing result writing to the table temp after the “JOIN processing” ( Step 83) has been added.

  Further, FIG. 12 shows a processing flow in the query processing engine Y. In addition to the distributed processing, processing for reading temp data first created by the query processing engine X (step 84), and deleting temp after the processing ends A process (step 87) is added.

  The SQL queries created by dividing the SQL query of FIG. 7 based on the processing flow of FIGS. 11 and 12 are as shown in FIGS. That is, the SQL query in FIG. 13 corresponds to the processing in the query processing engine X in FIG. 11, and the SQL query in FIG. 14 corresponds to the processing in the query processing engine Y in FIG.

  According to the query processing described above, it is possible to execute a query at a higher speed without dividing the query and using the same shared database 40, without causing an extra overhead in data movement. .

  DESCRIPTION OF SYMBOLS 1 ... Distributed query processing apparatus, 2 ... Client, 3 ... Network, 4 ... Database system, 10 ... Multi database management data, 11 ... Database information, 12 ... Engine information, 13 ... Data update part, 14 ... Access management part, 15 ... distributed query generation unit, 16 ... query execution control unit, 40 ... shared database, 41 ... query processing engine.

Claims (5)

In a data sharing database system where multiple query processing engines share a single database and process queries submitted by clients,
Multi-database management data for managing database information related to the database and engine information related to characteristics of the query processing engine;
A distributed query generation unit that selects an appropriate query processing engine based on the analysis result of the query posted from the client, and decomposes / reconstructs the query;
A query execution control unit that executes processing of the query by the appropriate query processing engine and receives a final execution result and transfers the result to the client;
A distributed query processing apparatus comprising: The distributed query processing device according to claim 1, wherein the query execution control unit creates an intermediate table of decomposed queries on the database when executing query processing. In a method of processing a query posted from a client in a data sharing database system in which a plurality of query processing engines share one database,
Generate or obtain a query plan indicating the execution plan of the query based on the query posted from the client, and analyze the query plan to execute the processing flow executed in the query processing engine, the table to be read and the data flow of the intermediate data Query plan generation / acquisition / analysis procedure to be created,
Using the processing flow and data flow generated in the query plan generation / acquisition / analysis procedure and the database information of the multi-database management data, the table size, the data size of the intermediate data in each processing on the processing flow, each processing A calculation procedure for calculating the resources required for
An engine selection procedure for selecting a query processing engine for each process based on the calculated data size and necessary resources, and engine information of multi-database management data;
A query generation procedure for generating a query based on the information of the query processing engine selected for each process;
The distributed query processing method characterized by performing. The query generation procedure includes:
If the process spans multiple query processing engines,
Temporary table creation procedure to generate a temporary table at the beginning of a series of query processing,
A write procedure for the temporary table which is the last process in each query processing engine;
At the end of the series of query processing, the temporary table deletion procedure,
The distributed query processing method according to claim 3, comprising:   A distributed query processing program which enables each procedure of the distributed query processing method according to claim 3 or 4 to be executed by a computer.