JP6328078B2 - Database system and database system program - Google Patents

Description

  Embodiments described herein relate generally to a database system and a database system program.

  In general, in a client-server type database system that performs a combined search on data sources connected to different types of databases, search results of the databases are combined in a database integration server and transferred to the client side.

  From each data source, for example, a large amount of memory in which tabular search results are recorded is copied to the database integration server via communication. Along with this, a large amount of memory is collected in the database integration server, and it takes a lot of time to integrate search results.

JP 2007-26296 A

  The problem to be solved by the present invention is to provide a database system and a database system program capable of further speeding up the integration processing of join search results.

The database system of the embodiment interprets a query transmitted from a client and an application that accepts a query requested from a client, generates an execution plan for a search in a plurality of data sources, and transmits the execution plan to each of them. A database system having a DB integration server and at least two different types of data sources that are objects for retrieving data in accordance with the execution plan, wherein each data source calls different data to manage different types of data APIs for different types of data sources, which are contracts that define procedures and data formats for common use among data sources, and communicate with the DB integration server, and the APIs for data sources for different types of data sources An integrated server with the ability to perform searches using And a use data conversion unit, the integration server data converting unit to each other is configured to communicate with each other, with exchanges search, the DB integration server execution plan for retrieval of each data source, It is generated so as to include communication between the integrated server data converters , holds the query, schema information, and data source information, and refers to the query, schema information, and data source information, and the data source A planner that creates an execution plan for the data source, a first transmission / reception unit that transmits and receives instructions and data between the DB integration server and the data source, and the planner. And receiving an execution plan for the data source from the first transmission / reception unit and the first transmission / reception unit. A plan execution unit that performs a process of integrating search results in the data source received via the reception unit and returns the result to the application, and the data source includes the DB via the first transmission / reception unit. Second transmission / reception for receiving an execution plan from the integrated server, transferring the execution plan within the execution plan, executing a reception command, and transmitting the execution result of the execution plan to the DB integrated server via the first transmission / reception unit And a plan execution unit that receives the execution plan from the DB integration server received by the second transmission / reception unit and executes the execution plan using the data source API .

It is a block diagram which shows schematic structure of the database system which concerns on embodiment of this invention. It is a figure which shows the detailed structural example of DB integrated server and a data source. It is a figure explaining the execution plan by contrasting the conventional and this embodiment. It is a figure which shows the relationship between DB integration server and a data source in execution plan creation, execution plan transmission, and execution plan execution. It is a flowchart which shows the flow of a process in DB integrated server. It is a figure explaining the processing content along the flowchart shown in FIG. It is a figure explaining preparation of the execution plan of a small table. It is a figure explaining preparation of the execution plan of a big table.

  Hereinafter, an embodiment of the present invention will be described with reference to the drawings. In the drawings, the same portions are denoted by the same reference numerals, and redundant description is omitted.

  First, main terms used in the present embodiment will be described.

  “Query” refers to an “inquiry” for retrieving data as a search result from a database in which data is stored.

  In the present embodiment, a client-server type database system that performs a join search on a data source connected to a heterogeneous database, and a database integration server (hereinafter referred to as a DB integration server) is requested from the client side. This analysis is performed to generate a search execution plan on the data source side so that less data is transferred from the data source side, and to speed up the integration processing of join search results on the DB integration server. is there.

  FIG. 1 is a block diagram showing a schematic configuration of a database system according to an embodiment of the present invention. This apparatus is realized using a general-purpose computer (for example, a personal computer (PC) or the like) and software operating on the computer. The computer includes an engineering workstation (EWS) suitable for CAD (Computer Aided Design) and CAE (Computer Aided Engineering). In this embodiment, in such a computer, a database system having at least two different types of data sources, which are objects for retrieving data in accordance with an execution plan, and a function for receiving a query requested by a client, and interpreting the query And a function for generating an execution plan for a search in a plurality of data sources and transmitting the execution plan to each of the data sources, and for each data source to call data managed by each data source and share it among different data sources A function for defining procedures and data formats, a function for executing searches using procedures and data formats for dissimilar data sources, and a structure that can communicate with each other. The execution plan of the search at the source generates a function including communication. It can also be implemented as a program database system for.

  As shown in FIG. 1, the database system 1 according to this embodiment mainly includes an application 10, a DB integration server 20, a plurality of data sources 30, a plurality of integrated server data conversion units 40, and a plurality of data. It consists of a source API 50. The data source 30, the integrated server data conversion unit 40, and the data source API 50 are in pairs.

  The application 10 receives a query requested from a client. The query received by the application 10 is transmitted to the DB integration server 20.

  The DB integration server 20 parses (interprets) the query transmitted from the application 10 and generates a search execution plan in each data source 30 so that less data is transferred from the data source side. Send to 30. A detailed configuration example of the DB integration server 20 and details of the execution plan will be described later.

  The data source 30 is an object for connecting to a database to be searched, for example, a relational database, and retrieving data according to the execution plan. The database may be any system that has a data source API and returns a result according to a condition input through the API. For example, KVS (Key Value Store) database or EXCEL.

  In order to set up the data source 30, for example, the type and size of the database, the storage location, the logon and the password are specified as the driver name for connecting to the database and the information of the named database. A detailed configuration example of the data source 30 will be described later.

  The integrated server data conversion unit 40 communicates with the DB integration server 20 and has a function of causing the data source 30 to execute a search using the data source API 50. The integrated server data conversion units 40 are configured to communicate with each other and exchange search conditions. The integrated server data converter 40 is installed in the same system as the data source 30 so that it can be accessed without requiring network communication, and functions to absorb differences in specifications between the data sources 30.

  The data source API 50 is a rule that defines a procedure and data format for calling and using data managed by the data source 30.

  FIG. 2 is a diagram illustrating a detailed configuration example of the DB integration server 20 and the data source 30. As illustrated in FIG. 2, the DB integration server 20 includes a planner 21, a plan execution unit 22, and a transmission / reception unit 23. The planner 21 holds SQL, schema information, and data source information. The schema information is, for example, data table specifications. The data source information is information such as specifications of each data source. The planner 21 refers to the SQL, schema information, and data source information, and creates an execution plan for each data source so that less data is transferred from the data source side.

  The transmission / reception unit 23 transmits / receives commands and data between the DB integration server 20 and the data source 30.

  The plan execution unit 22 receives an execution plan for each data source 30 from the planner 21 and transmits it to the transmission / reception unit 23. Furthermore, the search result integration processing for each data source received via the transmission / reception unit 23 is executed and returned to the application 10. As a result of analyzing the query, if the data table is singular, the data source is singular, or the configuration of the data source 30 does not include the integrated server data conversion unit 40 or the data source API 50, the plan execution unit 22 It is preferable to execute the conventional search result integration processing and return it to the application 10.

  As shown in FIG. 2, the data source 30 includes a transmission / reception unit 31 and a plan execution unit 32. The transmission / reception unit 31 receives the execution plan from the DB integration server 20 via the transmission / reception unit 23. Further, the transmission / reception unit 31 performs transfer and reception commands within the execution plan. Further, the transmission / reception unit 31 transmits the execution plan execution result to the DB integration server 20 via the transmission / reception unit 23.

  The plan execution unit 32 receives the execution plan from the DB integration server 20 received by the transmission / reception unit 31 and executes the execution plan. As described above, the data source API 50 is used to execute the execution plan.

<Execution plan>
Explain the execution plan that interpreted the query. FIG. 3 is a diagram for explaining the execution plan by comparing the conventional example with the present embodiment. FIG. 3A is a diagram for explaining conventional query interpretation. FIG. 3B is a diagram for explaining an execution plan in the present embodiment. The SELECT statement shown in Fig. 3 (a) is a query that extracts x1 from the data source X.ttt and y1 from the data source Y.sss on condition that x2 = y2 and y3 = 1 are satisfied. Represents. The query execution plan acquires y3 that satisfies the condition of y3 = 1, acquires y2 and x2 that satisfy the condition of x2 = y2, acquires x1, acquires y1, and results in x1 and y1 The process is to check all data. On the other hand, the query execution plan shown in FIG. 3B changes the instruction to transmit and receive data. That is, y3 satisfying the condition of y3 = 1 is obtained, y1 and y2 are obtained, y1 and y2 are transferred to the data source X, and all data in the data source Y are checked. Data source X rewrites received y1 and y2 to r1 and r2, obtains x2 that satisfies the condition of x2 = r2, obtains x1, transfers the execution results x1 and r1 to the DB integration server, Check all data in X.

  In creating an execution plan, the size relationship between tables is determined with reference to data source information and metadata related to the number of data items of table data managed by each data source. The SQL statement is decomposed for each table, and an execution plan for a small table and an execution plan for a large table are created. In order to reduce the transfer amount of the execution result, it is planned to transfer the execution result in the data source of the small table to the data source side of the large table.

  FIG. 4 is a diagram illustrating a relationship between the DB integration server 20 and the data source 30 in execution plan creation, execution plan transmission, and execution plan execution. As shown in FIG. 4, the DB integration server 20 creates an execution plan based on the SQL statement sent from the application 10. The DB integration server sends the created execution plan to each data source. The data source Y executes the execution plan and transfers the execution results y1 and y2 to the data source X. The data source X rewrites the transferred y1 and y2 to r1 and r2, and executes the execution plan. The data source X transfers the execution results x1 and r1 to the DB integration server.

  Next, the flow of processing in the DB integrated server 20 configured as described above will be described in detail. FIG. 5 is a flowchart showing the flow of processing in the DB integration server 20. FIG. 6 is a diagram for explaining the processing contents along the flowchart shown in FIG.

  First, an SQL sentence is input and parsed. Table information included in the SQL sentence is acquired (step S51). X.ttt and Y.sss in FIG. 6 (a) are used to explain table information. Data source X has a table named ttt, and data source Y has a table named sss. Yes.

  Next, the data source of all table information is analyzed (step S52). If some data sources of the plurality of data sources 30 do not include the integrated server data conversion unit 40 or the data source API 50 and are not configured as the data source according to the present embodiment, the conventional data source It is preferable to work with the mechanism of the integrated engine.

  Next, the magnitude relationship between the tables is determined based on the table and the conditional statement (step S53). In the example shown in FIG. 6A, it is determined that the data source Y is small, and x2 = y2 and y3 = 1 are extracted as the portions related to the data source X and the data source Y.

  Subsequently, the SQL sentence is decomposed for each table (step S54). First, the data source Y determined to be small is decomposed. In the example shown in FIG. 6B, since y2 = x2 is not completed only by the data source Y, in order to send y2 to the data source X, y2 is changed from a condition to a column. If only the portion related to the data source Y is extracted, it becomes as shown in FIG. Subsequently, the data source X determined to be large is decomposed. Here, as shown in FIG. 6D, the condition y3 = 1 relating only to y in the condition part is excluded. Further, as shown in FIG. 6E, for the data source Y, the result of receiving the table Y.sss is RES, and y1 and y2 are r1 and r2 in the meaning of receiving from the RES.

  Next, an execution plan for a small table is created (step S55). The execution plan here is configured so that the execution result of the execution plan of the small table is sent to the data source X of the large table. FIG. 7 is a diagram for explaining the creation of an execution plan for a small table.

  Next, an execution plan for a large table is created (step S56). Here, we plan to send an execution plan for a large table to the DB integrated server as the final result. FIG. 8 is a diagram illustrating creation of an execution plan for a large table.

  Next, the execution plan is transferred to each data source (step S57).

  Subsequently, the execution result is received from the data source X (step S58).

  Next, integration processing of search results in each received data source is executed and returned to the application 10 (step S59), and the processing in the DB integration server 20 is terminated.

  As described above, according to the present embodiment, it is possible to further speed up the combined search result integration process.

  Although several embodiments of the present invention have been described, these embodiments are presented by way of example and are not intended to limit the scope of the invention. These novel embodiments can be implemented in various other forms, and various omissions, replacements, and changes can be made without departing from the scope of the invention. These embodiments and modifications thereof are included in the scope and gist of the invention, and are included in the invention described in the claims and the equivalents thereof.

DESCRIPTION OF SYMBOLS 1 ... Database system 10 ... Application 20 ... DB integration server 21 ... Planner 22 ... Plan execution part 23 ... Transmission / reception part 30 ... Data source 31 ... Transmission / reception part 32- ..Plan execution unit 40 ... Integrated server data conversion unit 50 ... Data source API

Claims (6)

An application that accepts queries requested by clients,
A DB integration server that interprets a query sent from the application, generates a search execution plan in a plurality of data sources, and sends the execution plan to each of the DB integration server;
A database system having at least two disparate data sources that are objects for retrieving data in accordance with the execution plan;
Each data source is an API for different types of data sources, which is a protocol that defines procedures and data formats for calling data managed by each of the data sources and sharing them among different types of data sources,
An integrated server data conversion unit that communicates with the DB integration server and has a function of executing a search with respect to different data sources using the data source API. They are configured to communicate with each other, exchange search conditions,
The DB integration server generates the search execution plan in each data source so as to include communication between the data conversion units for the integration server, holds the query, schema information, and data source information, A planner that creates an execution plan for the data source by referring to the query, schema information, and data source information so that less data is transferred from the data source.
A first transmission / reception unit for transmitting / receiving commands and data between the DB integration server and the data source;
An execution plan for the data source is received from the planner and transmitted to the first transmission / reception unit, and an integration process of search results in the data source received via the first transmission / reception unit is executed to the application. A plan execution unit that performs the function of returning,
The data source is
Receives an execution plan from the DB integration server via the first transmission / reception unit, performs transfer within the execution plan, executes a reception command, and executes the execution plan to the DB integration server via the first transmission / reception unit A second transmission / reception unit for transmitting the execution result of the plan;
A database system comprising a plan execution unit that receives an execution plan from the DB integration server received by the second transmission / reception unit and executes the execution plan by using the data source API.   The database system according to claim 1, wherein the schema information is a specification of a data table, and the data source information is information of a specification of each data source.   The database system according to claim 1, wherein the database is a relational database.   The execution plan determines the magnitude relationship between the tables based on the information on the data source and metadata on the number of data of the table data managed by the data source, and the execution result of the small table on the data source is determined. The database system according to any one of claims 1 to 3, wherein the database is planned to be transferred to the data source side of the large table. The data source, the integrated server data conversion unit, and the data source API are each in pairs,
The database according to any one of claims 1 to 4, wherein the integrated server data conversion unit is installed in the same system as the data source and functions to absorb a difference in specifications between the data sources. system. A database system having at least two disparate data sources that are objects for retrieving data along an execution plan,
The application accepts queries requested by clients,
A function of the DB integration server interpreting the query, generating a search execution plan in a plurality of data sources, and transmitting the execution plan to each;
A function for each data source to call up data managed by each data source and to define a procedure and data format for common use among different data sources,
A function for each data source to communicate with the DB integration server, and to perform a search for the heterogeneous data source using the procedure or data format;
Each data source is configured to be able to communicate with each other, and a function for exchanging search conditions.
A function of causing the DB integration server to generate a search execution plan in each data source so as to include the communication ;
The DB integration server holds the query, schema information, and data source information, and refers to the query, schema information, and data source information so that less data is transferred from the data source side. The ability to create an execution plan for the data source,
The DB integration server receives the execution plan, executes a search result integration process in the received data source, and returns it to the application;
A function for each data source to receive the execution plan from the DB integration server, perform transfer within the execution plan, execute a reception command, and transmit the execution result of the execution plan to the DB integration server; ,
Each data source receives the execution plan from the DB integration server, and executes the execution plan using a data source API;
A database system program for realizing

Images