JPWO2016132433A1 - Data management apparatus, data management method, and data management program - Google Patents

Abstract

The data storage unit (111) stores an external table operation rule (130) in which operations that can be executed in the external database (150) are defined. The search condition receiving unit (1031) receives the search condition (301). The query statement generation unit (105), when a search of data stored in the external database (150) by the search condition (301) and an operation using the searched data are requested, Based on (130), it is determined whether or not the operation requested in the search condition (301) can be executed in the external database (150), and the operation can be executed in the external database (150). Then, an extended query statement, which is a query statement for causing the external database (150) to execute the search and operation requested by the search condition (301), is generated. The query execution unit (1032) transmits the extended query statement to the external database (150).

Description

  The present invention relates to a technique for searching a database.

Patent Document 1 discloses a technique for searching an external database that manages data that is not managed by the data management device from the data management device.
The data management device is, for example, a DBMS (Database Management System) that conforms to SQL '99, and the external database is, for example, a DBMS that does not conform to SQL '99.
In the technique of Patent Document 1, a part of information held in the external database is stored in the data management apparatus in order to speed up the search of the external database from the data management apparatus.
In the technique of Patent Document 1, when a search condition for an external database reaches the data management apparatus, the data management apparatus uses information stored in the data management apparatus (part of information held in the external database). Rewrite the search conditions to the external database.
In the technique of Patent Document 1, the external database can be searched more efficiently by rewriting the search condition.

Patent Document 2 discloses a technique for improving the efficiency of generating search conditions in an external database.
In the technique of Patent Literature 2, the search condition of the external database created by the data management apparatus is held in the data management apparatus.
In the technique disclosed in Patent Document 2, when a search condition for an external database newly arrives at the data management apparatus, the data management apparatus newly starts from the search condition for the external database held by the data management apparatus. Search for search conditions corresponding to the received search conditions.
Then, the data management device searches the external database using the searched search condition.

Japanese Patent No. 4483034 Japanese Patent Laid-Open No. 2003-316811

In the prior art, it is possible to efficiently generate a search condition from the data management device to the external database, and it is possible to obtain an optimal search condition to the external database from a search request to the data management device.
However, in order for the data management device to search the external database using the search condition for the external database generated by the data management device, it is necessary to use the search result information of the external database held in advance in the data management device. is there.
The search result information is information that defines the format of the search result from the external database.
The search result information indicates, for example, the column name of the external database from which the data included in the search result is extracted and the data type of the data included in the search result.
In the prior art, only search conditions that can obtain a search result in the same format as the search result information can be used for searching an external database.
Therefore, when search of data stored in the external database and calculation using the searched data are requested by the search condition, the data type indicated in the search result information is the calculation of the calculation requested by the search condition. If the data type is different from the data type of the operation result, the data management device cannot request the operation from the external database. Based on the search result information, the data management device requests only the data search from the external database. Will be received.
Then, the data management apparatus uses the search result received from the external database to perform the calculation requested by the search condition.
However, when the data management apparatus receives the search result from the external database and performs the calculation using the received search result, the processing speed is remarkably reduced as compared with the case where the external database executes both data search and calculation. .

  The present invention has been made in view of such circumstances, and a main object of the present invention is to improve the processing efficiency when processing a search condition for which a search and calculation of an external database are required.

The data management apparatus according to the present invention is
A data storage unit for storing external table operation rules in which operations that can be executed in an external database are defined;
A search condition receiver for receiving search conditions;
When a search for data stored in the external database and an operation using the searched data are requested by the search condition received by the search condition receiving unit, based on the external table operation rule, It is determined whether or not the operation requested in the search condition can be executed in the external database, and the operation requested in the search condition can be executed in the external database. A query statement generation unit that generates an extended query statement that is a query statement that causes the external database to execute the requested search and operation;
A query execution unit that transmits the extended query statement generated by the query statement generation unit to the external database.

In the present invention, it is determined whether or not the operation requested in the search condition can be executed in the external database. If the operation requested in the search condition can be executed in the external database, the request is made in the search condition. An extended query statement that causes the external database to execute the retrieved search and operation is generated, and the extended query statement is transmitted to the external database.
For this reason, according to the present invention, it is possible to cause the external database to execute both the search and the calculation requested in the search condition, and to process the search condition for which the search and the calculation of the external database are requested. Processing efficiency can be improved.

FIG. 3 is a diagram illustrating a functional module configuration example of the data management apparatus according to the first embodiment. FIG. 3 is a diagram illustrating an example of a system configuration according to the first embodiment. FIG. 3 is a diagram illustrating a hardware configuration example of a data management apparatus according to the first and second embodiments. FIG. 4 is a flowchart showing an example of external table definition processing according to the first embodiment. FIG. 3 is a flowchart showing an example of data search processing according to the first embodiment. FIG. 6 is a diagram showing an example of external table definition according to the first embodiment. FIG. 5 shows an example of an external table according to the first embodiment. FIG. 4 is a diagram showing an example of a query statement generation rule according to the first embodiment. FIG. 6 is a diagram showing an example of an external table calculation rule according to the first embodiment. FIG. 6 is a diagram showing an example of an external database definition according to the first embodiment. FIG. 4 is a diagram illustrating an example of an execution plan before update according to the first embodiment. The figure which shows the example of the execution plan after the update which concerns on Embodiment 1. FIG. FIG. 6 is a diagram illustrating a functional module configuration example of a data management apparatus according to a second embodiment. FIG. 9 is a flowchart showing an example of data search processing according to the second embodiment. FIG. 9 is a flowchart showing an example of extended query statement generation processing according to the second embodiment.

Embodiment 1 FIG.
In the present embodiment and the subsequent embodiments, when an aggregation operation is specified in the search condition, even if the data type of the search result of the external database is different from the search result information of the external database stored in advance, the external database A configuration will be described in which an aggregation operation is push-downed as a database search condition so that an external database can be efficiently searched.

*** Explanation of configuration ***
FIG. 1 shows a functional module configuration example of the data management apparatus 101 according to the present embodiment.

The data management apparatus 101 receives the external table definition 201 defined by the external table definition application 200, converts the format of the received external table definition 201, and stores it as the external table definition 120 in the data storage unit 111 described later.
The data management apparatus 101 is connected to the external database 150, receives the search condition 301 from the data search application 300, and transmits the received search condition 301 to the external database 150.
In addition, the data management apparatus 101 receives a search result for the search condition 301 from the external database 150 and transmits the received search result to the data search application 300.

  The data management apparatus 101 includes an external table definition unit 102, a search control unit 103, a search condition optimization unit 104, a query statement generation unit 105, and a data storage unit 111.

The external table definition unit 102 receives the external table definition 201 from the external table definition application 200 and stores the received external table definition 201 as the external table definition 120 in the data storage unit 111.
The external table definition 120 is information obtained by converting the format of the external table definition 201.
Since the external table definition 201 and the external table definition 120 differ only in format, the external table definition 120 will be described below. However, the following description also applies to the external table definition 201.
The external table definition 120 is information in which the format of the external table is defined.
The external database 150 transmits a tabular response to the search condition to the data management apparatus 101.
A table transmitted as a response from the external database 150 to the data management apparatus 101 is referred to as an external table.
The external table definition 120 defines the format of this external table.
The data search application 300 requests a search condition for requesting only the search of data stored in the external database 150, and a search condition for requesting a search for data stored in the external database and an operation using the searched data. Two types of search conditions are transmitted.
The former is called a standard search condition, and the latter is called an extended search condition.
An external table transmitted as a response to the standard search condition is called a standard external table.
An external table transmitted as a response to the extended search condition is called an extended external table.
The external table definition 120 defines a standard external table format.
The external table definition 120 is also called a standard external table definition.
A standard external table format defined in the external table definition 120 is referred to as a standard format.
On the other hand, the query statement generation unit 105 to be described later generates an external table definition in which the format of the extended external table is defined.
An external table definition in which the format of the extended external table generated by the query statement generation unit 105 is defined is called an extended external table definition.
The format of the extended external table defined in the extended external table definition is called an extended format.
The external database 150 transmits a tabular response to the standard search condition and the extended search condition to the data management apparatus 101.
A response from the external database 150 to the standard search condition is called a standard response. A response from the external database 150 to the extended search condition is referred to as an extended response.
Details of the external table definition 120 and details of the extended external table definition will be described later.

  The search control unit 103 includes a search condition reception unit 1031, an inquiry execution unit 1032, a response reception unit 1033, and a response transmission unit 1034.

The search condition receiving unit 1031 receives the search condition 301 from the data search application 300.
The search condition 301 is a standard search condition or an extended search condition.
Although the search condition 301 may request a search for data in the data management apparatus 101, the search condition 301 is directly related to the characteristic part of the present embodiment. Suppose that it is a search condition.
The search condition receiving unit 1031 outputs the received search condition 301 to the search condition optimization unit 104 described later.

The query execution unit 1032 executes a query statement to the external database 150 generated by the query statement generation unit 105 described later.
That is, the query execution unit 1032 transmits the query statement to the search execution unit 151 of the external database 150.

The response receiving unit 1033 receives a response (standard external table or extended external table) to the query statement from the search execution unit 151 of the external database 150.
If the response from the search execution unit 151 is a standard external table, the response reception unit 1033 receives the response from the search execution unit 151 with reference to the standard external table definition (external table definition 120).
If the response from the search execution unit 151 is an extended external table, the response receiving unit 1033 receives the response from the search execution unit 151 with reference to the extended external table definition.

The response transmission unit 1034 transmits the response from the search execution unit 151 received by the response reception unit 1033 to the data search application 300.
The response returned from the response transmission unit 1034 to the data search application 300 is used as the search result 302 in the data search application 300.

The search condition optimization unit 104 interprets the search condition 301 acquired from the search condition receiving unit 1031 and instructs the query statement generation unit 105 to generate a query statement to the external database 150.
Further, the search condition optimization unit 104 outputs the query statement generated by the query statement generation unit 105 to the query execution unit 1032.

When the search condition 301 is an extended search condition, the query statement generation unit 105 requests the search of the data stored in the external database 150 and the operation using the searched data. In this case, a query statement that causes the external database 150 to execute the search and calculation requested by the search condition 301 is generated.
A query statement that causes the external database 150 to execute the search and calculation requested by the search condition 301 is referred to as an extended query statement.
More specifically, the query statement generator 105 causes the external database 150 to execute only the search requested by the search condition 301 based on the standard format defined in the standard external table definition (external table definition 120). A standard query statement that is a query statement is generated.
Then, the query statement generation unit 105 determines whether or not the operation requested by the search condition 301 can be executed in the external database 150 based on the external table operation rule 130 described later.
When the operation requested by the search condition 301 can be executed in the external database 150, the query statement generation unit 105 rewrites the standard query statement and generates an extended query statement.
When the search condition 301 is a standard search condition, the query statement generation unit 105 does not rewrite the standard query statement.
The query statement generation unit 105 includes a standard query statement generation unit 1051 and an extended query statement generation unit 1052.
The standard query statement generation unit 1051 generates a standard query statement.
The extended query statement generation unit 1052 rewrites the standard query statement and generates an extended query statement.

  The data storage unit 111 stores an external table definition 120, an external table calculation rule 130, an external database definition 140, an execution plan 145, and a query statement generation rule 147.

The external table definition 120 is the external table definition 201.
The external table definition 120 is information shown in FIG.
FIG. 6 defines the format of the external table shown in FIG.
The external table definition 120 includes an external table ID 121, a column name 122, and a data type 123.
In the external table ID 121, an ID (Identifier) of the external table is described.
“Ft0001” in FIG. 6 is the ID of the external table in FIG.
The column name 122 describes the name of the column included in the external table.
In FIG. 6, columns “fta_1”, “fta_2”, and “fta — 3” of the external table of FIG. 7 are described.
The data type 123 describes the data type of the data in each column shown in the column name column 122.
In FIG. 6, the data type of the data “fta_1” in FIG. 7 is integer, the data type of the data “fta_2” in FIG. 7 is integer, and the data type of the data “fta_3” in FIG. 7 is text. It is described that there is.

The external table operation rule 130 defines operations that can be executed by the external database 150.
The external table calculation rule 130 is information shown in FIG.
The external table calculation rule 130 includes a rule ID 131, a calculation content 132, a calculation result 133, a calculation target 1 (134), and a calculation target 2 (135).
In the rule ID 131, the ID of the external table calculation rule is described.
In the operation content 132, an operation (for example, an operator) that can be executed in the external database 150 is described.
The calculation result 133 describes the data type of the calculation result.
In the operation target 1 (134) and the operation target 2 (135), the data type of the operand is described.
When there is no operand, “-” is described in the calculation target 1 (134) and the calculation target 2 (135).

In the external database definition 140, an external table is defined for each external database.
The external database definition 140 is information shown in FIG.
The external database definition 140 includes an external table ID 141, an external table name 142, and an external database ID 143.
The external database ID 143 describes the ID of the external database 150.
FIG. 10 assumes that there are a plurality of external databases 150.
In the external table ID 141, the ID of the external table of the external database 150 whose ID is described in the external database ID 143 is described.
The external table ID 141 is the same as the external table ID 121 of the external table definition in FIG.
In the external table name 142, the name of the external table of the external database 150 whose ID is described in the external database ID 143 is described.

The execution plan 145 is an analysis result of the search condition 301.
The execution plan 145 is data having a tree structure as illustrated in FIG.
Details of the execution plan 145 will be described later.

In the query statement generation rule 147, calculation contents to be included in the query statement are defined.
The query statement generation rule 147 is information shown in FIG.
The query statement generation rule 147 includes operation contents 148 and a query 149.
In the calculation contents 148, calculation contents included in the search condition 301 are defined.
In the query 149, calculation contents to be included in the query statement are defined.

  In FIG. 1, an external table definition application 200 is a higher-level application program that makes an external table definition request to the data management apparatus 101.

  The data search application 300 is a higher-level application program that requests the data management apparatus 101 to search for data.

  FIG. 2 shows a system configuration example including the data management apparatus 101.

The data collection device 251 includes an external table definition application 200 and makes an external table definition request to the data management device 101.
The data collection device 251 holds the external table definition 201.

The data search device 252 includes a data search application 300 and requests data search from the data management device 101.
The data search device 252 executes the data search application 300 to generate a search condition 301 and receives the search result 302 from the data management device 101.

The external data management device 253 includes an external database 150.
The external database 150 includes a search execution unit 151 that processes a query sentence transmitted from the data management apparatus 101, and a data storage area 152 in which data is stored.

  The data collection device 251, the data search device 252, and the external data management device 253 may each be one or more.

FIG. 3 shows a hardware configuration example of the data management apparatus 101.
Details of FIG. 3 will be described later.

*** Explanation of operation ***
Next, the operation of the data management apparatus 101 according to this embodiment will be described.
First, the external table definition process of the data management apparatus 101 will be described based on the flowchart of FIG.

  In step S <b> 101, the external table definition unit 102 receives the external table definition 201 from the external table definition application 200.

Next, in step S102, the external table definition unit 102 interprets the external table definition 201 and converts the format of the external table definition 201.
As described above, the external table definition 201 after the format conversion is the external table definition 120.

  Next, in step S <b> 103, the external table definition unit 102 stores the external table definition 120 in the data storage unit 111.

Next, data search processing of the data management apparatus 101 will be described based on the flowchart of FIG.
The procedure shown in FIG. 5 corresponds to an example of the data management method and data management program of the present application.
In the following description, the external table definition 120 of FIG. 6, the external table calculation rule 130 of FIG. 9, and the query statement generation rule 147 of FIG. 8 are stored in the data storage unit 111 before the start of FIG. To do.
Since the execution plan 145 of FIG. 11 is generated in step S202 described later, it is not stored in the data storage unit 111 before the start of FIG.

In step S201, the search condition receiving unit 1031 of the search control unit 103 receives the search condition 301 from the data search application 300 (search condition receiving process).
Here, it is assumed that the search condition receiving unit 1031 has received “select sum ((fta — 1 + 100) * 0.15) from ft where fta — 1 between 1 and 1000000” as the search condition 301, which is an SQL query statement.
The search condition 301 includes a search of data in the external database 150 and a “+” operation, a “*” operation, and a “sum ()” operation that are operations using the searched data.
For this reason, the search condition 301 is an extended search condition.

In step S202, the search condition optimization unit 104 analyzes the search condition 301 and generates an execution plan 145.
The execution plan 145 is a processing plan that is optimized so that the processing required by the search condition 301 is executed in an efficient order.
As a result of analyzing the search condition 301, the search condition optimization unit 104 determines that the search condition 301 is an extended search condition, and generates an execution plan 145 shown in FIG.
In addition, the search condition optimization unit 104 instructs the query statement generation unit 105 to generate a standard query statement in the process of generating the execution plan 145.
In the query statement generation unit 105, the standard query statement generation unit 1051 generates a standard query statement for causing the external database 150 to perform a search for “fta_1” based on the external table definition 120 (FIG. 6).
More specifically, the standard query statement generation unit 1051 generates the standard query statement 1121 in FIG. 11 and outputs the generated standard query statement 1121 to the search condition optimization unit 104.
The search condition optimization unit 104 generates the execution plan 145 in FIG. 11 including the standard query statement 1121.

Here, the details of the execution plan 145 will be described.
The execution plan 145 is tree-structured data as shown in FIG.
The execution plan 145 includes nodes and selection conditions.
In FIG. 11, elements 1101 and 1102 are nodes, and elements 1103, 1104, and 1131-1135 are selection conditions.
The selection conditions also have a tree structure, and the selection conditions 1104, 1133, 1134, and 1135 in the lowest hierarchy describe the constants included in the search conditions 301 and the columns included in the column name 122 of the external table definition 120. Yes.
Operators included in the search condition 301 are described in the selection conditions 1103, 1131, and 1132 other than the lowest hierarchy.
The execution plan 145 is data for controlling the execution order of processes corresponding to the search condition 301.
The execution plan 145 defines a processing procedure for performing an operation using the operators described in the selection conditions 1103, 1131, 1132.
In the execution plan 145, the selection conditions whose operation order is earlier are arranged in the lower hierarchy.
Therefore, as will be described later, the extended query statement generation unit 1052 searches the execution plan 145 by a depth-first search.
Further, the execution plan 145 defines a processing procedure in which the processing of the lower node 1102 is executed, the execution result of the processing of the lower node 1102 is used for the upper node 1101, and the processing of the upper node 1101 is performed. Has been.
The tree structure of selection conditions may be composed of one selection condition or a plurality of selection conditions.
Further, the maximum number of selection conditions in the tree structure of selection conditions is not limited by the number of columns in the external table definition 120.
In the execution plan 145, the external table definition 120 is associated with the node 1102.
The external table definition 120 indicates column names and data types included in the response (external table) from the external database 150 to the standard query statement 1121.

Since the search condition 301 is an extended search condition, the search condition optimization unit 104 outputs the execution plan 145 to the query statement generation unit 105 in step S203 and instructs the query statement generation unit 105 to generate an extended query statement. .
Note that if the search condition 301 is a standard search condition, the processing of steps S203 to S207 is not necessary.

  In the query statement generation unit 105 instructed by the search condition optimization unit 104 to generate an extended query statement, the extended query statement generation unit 1052 performs the processes of steps S204 to S207.

In step S <b> 204, the extended query statement generation unit 1052 determines whether there is a selection condition for which the data type is not determined among the selection conditions included in the execution plan 145 by the depth-first search.
If there is a selection condition for which the data type is not determined, the extended query statement generation unit 1052 performs step S205.
If the data types of all the selection conditions are confirmed, the extended query statement generation unit 1052 performs step S207.

  In step S <b> 205, the extended query statement generation unit 1052 extracts one selection condition whose data type is undetermined from the execution plan 145.

In step S206, the extended query statement generation unit 1052 determines the data type of the selection condition extracted in step S205 with reference to the external table operation rule 130 (FIG. 9), and the external database 150 determines the data type from the determined data type. It is determined whether or not the operation requested by the search condition 301 can be executed (query generation process).
That is, the extended query statement generation unit 1052 determines whether or not the external database 150 can execute the operation “sum ((fta — 1 + 100) * 0.15)” in the search condition 301.

In step S207, the extended query statement generation unit 1052 rewrites the standard query statement generated by the standard query statement generation unit 1051 to generate an extended query statement (query statement generation process).
The extended query statement generation unit 1052 generates an extended external table definition.
The extended external table definition is information that defines the format of a response from the external database 150 to the extended query statement.
When the external database 150 can execute the operation “sum ((fta — 1 + 100) * 0.15)” in the search condition 301, the extended query statement generation unit 1052 and the extended query statement 1221 in FIG. 1210 is generated.
The extended query statement 1221 is a query statement that causes the external database 150 to execute the search for the column “fta_1” and the operation “sum ((fta_1 + 100) * 0.15)”.
In the extended external table definition 1210, the external table ID 1211 defines a new external table ID “t_ft001”, the column name 1212 defines only “sum”, and the data type 1213 defines the data type “sum”. “Decimal” is defined.
“Decimal”, which is a data type of “sum”, is obtained from the rule ID: 0001 of the external table calculation rule 130 (FIG. 9).
As shown in FIG. 12, the extended external table definition 1210 includes an extended query statement 1221: “select sum ((fta_1 + 100) * 0.15) from ft where where1_1 between 1 and 1000000”. Only (sum and data type: decimal) are defined.
The extended query statement generation unit 1052 generates the execution plan 146 of FIG. 12 using the generated extended query statement 1221 and the extended external table definition 1210.
That is, the extended query statement generation unit 1052 deletes the aggregate 1101 pushed-down by the external database search 1102 of FIG. 11 from the execution plan 145, and replaces the standard query statement 1121 and the standard external table definition 120 with the extended query. An execution plan 146 in which the statement 1221 and the extended external table definition 1210 are associated with the external database search 1102 is generated.
Then, the extended query statement generation unit 1052 outputs the execution plan 146 to the search condition optimization unit 104.
The search condition optimization unit 104 outputs the execution plan 146 to the search control unit 103.
In the search control unit 103, the query execution unit 1032 transmits the extended query statement 1221 of the execution plan 146 to the external database 150 (query execution process).

  In step S208, the response receiving unit 1033 of the search control unit 103 receives the response from the external database 150 using the extended external table definition 1210.

  Finally, in step S209, the response transmission unit 1034 of the search control unit 103 transmits a response from the external database 150 to the data search application 300.

  The data type determination process (S204 to S206) will be described using the execution plan 145 of FIG. 11 as an example.

First, the extended query statement generation unit 1052 extracts “fta_1” denoted by reference numeral 1134 (step S205).
Since “fta_1” is defined as data type “integer” in the external table definition 120, the extended query statement generation unit 1052 calculates the operation result “integer” from the rule ID: 0004 of the external table calculation rule 130 (FIG. 9). ”Is extracted, and the data type“ fta_1 ”denoted by reference numeral 1134 is determined as“ integer ”(step S206).
Next, the extended query statement generation unit 1052 extracts “100 :: integer” denoted by reference numeral 1135 (step S205).
The extended query generation unit 1052 extracts the calculation result “integer” from the rule ID: 0006 of the external table calculation rule 130 (FIG. 9), and confirms the data type “100 :: integer” of reference numeral 1135 as “integer”. (Step S206).
Next, the extended query statement generation unit 1052 extracts “0.15 :: float” of reference numeral 1133 (step S205).
The extended query statement generation unit 1052 extracts the operation result “float” from the rule ID: 0007 of the external table operation rule 130 (FIG. 9), and sets the data type of “0.15 :: float” of reference numeral 1133 to “float”. (Step S206).
Next, the extended query statement generation unit 1052 extracts “+” of reference numeral 1132 (step S205).
The data types of “fta_1” and “100 :: integer”, which are the calculation targets of “+” of reference numeral 1132, are “integer”.
For this reason, it matches the rule ID: 0002 of the external table calculation rule 130 (FIG. 9), and the extended query statement generation unit 1052 extracts the calculation result “integer” from the rule ID: 0002, The data type is determined as “integer” (step S206).
Next, the extended query statement generation unit 1052 extracts “*” of reference numeral 1131 (step S205).
The data type of “+” that is the operation target of “*” of reference numeral 1131 is “integer”, and the data type of “0.15 :: float” is “float”.
For this reason, it matches the rule ID: 0003 of the external table calculation rule 130 (FIG. 9), and the extended query statement generation unit 1052 extracts the calculation result “decimal” from the rule ID: 0003, The data type is determined as “decimal” (step S206).
Next, the extended query statement generation unit 1052 extracts “fta_1 ′” denoted by reference numeral 1104 (step S205).
“Fta — 1 ′” of the reference numeral 1104 means the calculation result of the selection conditions of reference numerals 1131 to 1135.
For this reason, the data type “fta_1 ′” of reference numeral 1104 is the data type “decimal” of “*” of reference numeral 1131.
Next, the extended query statement generation unit 1052 extracts “sum ()” denoted by reference numeral 1103 (step S205).
The data type of “fta — 1 ′” that is the operation target of “sum ()” denoted by reference numeral 1103 is “decimal”.
Therefore, the extended query statement generation unit 1052 extracts the operation result “decimal” from the rule ID: 0001 of the external table operation rule 130 (FIG. 9), and sets the data type of “sum ()” denoted by reference numeral 1103 to “decimal”. (Step S206).

In this way, when the data types of all the selection conditions of the execution plan 145 have been determined, the extended query statement generation unit 1052 determines that the external database 150 can execute all the operations of the search conditions 301. .
For this reason, as described above, the extended query statement generation unit 1052 rewrites the standard query statement 1121 (FIG. 11) and causes the external database 150 to execute all operations of the search condition 301 (FIG. 11). 12) is generated.
The extended query statement generation unit 1052 rewrites the standard query statement 1121 to the extended query statement 1221 according to the query statement generation rule 147 (FIG. 8).
The extended query statement generation unit 1052 generates an extended external table definition 1210 (FIG. 12) using the data type “decimal” of “sum ()” denoted by reference numeral 1103.

*** Explanation of effects ***
As described above, in this embodiment, the data type of the external database query result is estimated from the selection condition, and in the case of the selection condition composed only of the rules existing in the external table operation rule, the standard query statement is rewritten and expanded. Generate a query statement.
In a conventional external database search using an external table, the search result is restricted to the standard external table definition.
For this reason, the conventional data management apparatus acquires a search result that conforms to the standard external table definition from an external database, and processes the acquired search result to obtain a search result that matches the search condition.
That is, when the conventional data management apparatus receives the above-described search condition “select sum ((fta_1 + 100) * 0.15) from ft where fta_1 between 1 and 1000000”, the standard query statement 1121 in FIG. As shown in the standard external table definition 120, the search result of “fta_1”, “fta_2”, “fta — 3” is acquired from the external database.
Then, the conventional data management apparatus executes “sum ((fta_1 + 100) * 0.15)” on “fta_1” from the external database, and “select sum ((fta_1 + 100) * 0.15) from ft The operation result conforming to “where fta — 1 between 1 and 1000000” is obtained.
On the other hand, when the operation of the search condition 301 can be executed in the external database 150, the data management apparatus 101 according to the present embodiment transmits the extended query statement 1221 in FIG.
Then, the data management apparatus 101 obtains an operation result that conforms to “select sum ((fta_1 + 100) * 0.15) from ft where fta_1 between 1 and 1000000” from the external database 150.
As described above, the data management apparatus 101 according to the present embodiment can acquire a response after the calculation of “sum ((fta_1 + 100) * 0.15)” from the external database 150. sum ((fta_1 + 100) * 0.15) "need not be executed, the processing efficiency during the search can be improved, and the search speed can be increased.

In the above, an example in which all the operations included in the search condition 301 can be executed in the external database 150 has been described.
If some of the operations included in the search condition 301 cannot be executed in the external database 150, the extended query statement generation unit 1052 generates an extended query statement that causes the external database 150 to execute operations other than the part of the operation.
For example, if “sum ()” other than “sum ()” in the execution plan 145 of FIG. 11 can be calculated in the external database 150, but “sum ()” cannot be executed in the external database 150, the extended query statement generation unit 1052 performs the search. An extended query statement that causes the external database 150 to execute an operation other than “sum ()” among a plurality of operations included in the condition 301 is generated.
Then, when a response to the extended query statement is received from the external database 150, the search condition optimization unit 104 performs a “sum ()” operation on the response from the external database 150 and corresponds to the search condition 301. To obtain the result of the operation.

Embodiment 2. FIG.
In the first embodiment, after the standard query statement generation unit 1051 generates a standard query statement, the extended query statement generation unit 1052 determines the data type of the selection condition by a determination process, so that the calculation of the search condition 301 is performed in the external database 150. When the external database 150 can execute the operation, the standard query statement is rewritten to generate an extended query statement.
In the present embodiment, an example will be described in which the determination type data type determination process and the extended query statement generation process are performed in parallel without generating a standard query sentence.

*** Explanation of configuration ***
FIG. 13 shows a functional module configuration example of the data management apparatus 101 according to the second embodiment.
In the data management apparatus 101 of FIG. 13, unlike the data management apparatus 101 of FIG. 1, the query statement generation unit 105 is not divided into a standard query statement generation unit 1051 and an extended query statement generation unit 1052.
Further, in the data management apparatus 101 of FIG. 13, the query statement generation rule 147 is not stored in the data storage unit 111.
Other elements in FIG. 13 are the same as those in FIG.
Hereinafter, differences from the first embodiment will be mainly described.
Matters not described below are the same as those in the first embodiment.

In the present embodiment, when the search condition 301 is an extended search condition, the query statement generation unit 105 executes each of the operations in the order of execution from the previous operation among a plurality of operations requested by the search condition 301. Based on the external table calculation rule 130, a determination process for determining whether or not an operation can be executed in the external database 150, and an operation that is determined to be executable in the external database 150 by the determination process are stored in the external database 150. A description generation process for generating a description to be executed as an extended query description is repeated, and an extended query statement is generated by a combination of extended query descriptions for each operation.
When the execution plan 145 of FIG. 11 shown in the first embodiment is generated, the query statement generation unit 105 determines the data type for each selection condition in the depth-first search, as in the first embodiment. (Judgment process).
Then, an extended query description for causing the external database 150 to execute the operation indicated by the selection condition for which the data type has been determined is generated.
The extended query description is a partial description of the extended query statement.
For example, when the data type of the code 1132 “+” in FIG. 11 is determined, the query statement generation unit 105 generates an extended query description for causing the external database 150 to execute the “+” operation.
In addition, when the data type of the code 1131 “*” in FIG. 11 is determined, the query statement generation unit 105 generates an extended query description for causing the external database 150 to execute the “*” operation.
In addition, when the data type of reference numeral 1103 “sum ()” in FIG. 11 is determined, the query statement generation unit 105 generates an extended query description for causing the external database 150 to execute the “sum ()” operation.
Then, the query statement generation unit 105 combines the extended query description of “+” operation, the extended query description of “*” operation, and the extended query description of “sum ()” operation to obtain the extended query statement 1221 of FIG. Generate.
In the present embodiment, since the standard query statement 1121 is not generated, the execution plan 145 according to the present embodiment corresponds to the one obtained by excluding the standard query statement 1121 from FIG.
Other operations of the data management apparatus 101 are as described in the first embodiment.
Hereinafter, an operation example of the data management apparatus 101 according to the present embodiment will be described in detail with reference to a flowchart.

*** Explanation of operation ***
Hereinafter, based on the flowcharts of FIG. 14 and FIG. 15, the data search processing of the data management apparatus 101 according to the present embodiment will be described.
FIG. 15 shows a detailed flow of step S1403 of FIG.
The procedure shown in FIGS. 14 and 15 corresponds to an example of the data management method and data management program of the present application.
In the following description, it is assumed that the external table definition 120 of FIG. 6 and the external table calculation rule 130 of FIG. 8 are stored in the data storage unit 111 before the start of FIG.

In step S1401, the search condition receiving unit 1031 of the search control unit 103 receives the search condition 301 from the data search application 300 (search condition receiving process).
Here, it is assumed that the search condition receiving unit 1031 has received “select sum ((fta — 1 + 100) * 0.15) from ft where fta — 1 between 1 and 1000000” as the search condition 301, which is an SQL query statement.

In step S1402, the search condition optimization unit 104 analyzes the search condition 301 and generates an execution plan 145.
In the first embodiment, the query statement generation unit 105 generates a standard query statement. However, in this embodiment, the query statement generation unit 105 does not generate a standard query statement 1121.
For this reason, the execution plan 145 according to the present embodiment is equivalent to the execution plan 145 excluding the standard query statement 1121 from FIG.

Since the search condition 301 is an extended search condition, the search condition optimization unit 104 outputs the execution plan 145 to the query statement generation unit 105 and instructs the query statement generation unit 105 to generate an extended query statement.
In step S1403, the query statement generation unit 105 generates an extended query statement according to the procedure shown in FIG. 15 (query statement generation process).

As will be described later, in the procedure of FIG. 15, the process of determining the data type of the selection condition, the generation process of the extended query statement, and the generation process of the extended external table definition are performed. Generates the execution plan 146 of FIG. 12 using the extended query statement and the extended external table definition generated in FIG.
The procedure for generating the execution plan 146 is the same as that described in the first embodiment.
The query statement generation unit 105 outputs the execution plan 146 to the search condition optimization unit 104.
The search condition optimization unit 104 outputs the execution plan 146 to the search control unit 103.
In the search control unit 103, the query execution unit 1032 transmits the extended query statement 1221 of the execution plan 146 to the external database 150 (query execution process).

  In step S1405, the response receiving unit 1033 of the search control unit 103 receives the response from the external database 150 using the extended external table definition 1210.

  Finally, in step S1406, the response transmission unit 1034 of the search control unit 103 transmits the response from the external database 150 to the data search application 300.

  Next, the extended query statement generation process (S1403 in FIG. 14) of the query statement generator 105 will be described with reference to the flowchart of FIG.

In step S1501, the query statement generation unit 105 acquires a node for external database search from the execution plan 145.
Here, the query statement generation unit 105 acquires the node of the external database search 1102 in FIG.

In step S1502, the query statement generation unit 105 determines whether there is a selection condition for which the data type is not fixed among the selection conditions of the external database search 1102.
If there is a selection condition for which the data type does not exist, the query statement generation unit 105 performs the process of step S1503.
When the data types of all the selection conditions are fixed, the query statement generation unit 105 performs the process of step S1505.

  In step S1503, the query statement generation unit 105 extracts one selection condition whose data type is not fixed.

In step S1504, the query statement generation unit 105 determines the data type of the selection condition extracted in step S1503.
The data type determination procedure is the same as that described in the first embodiment.

In step S1505, the query statement generation unit 105 generates an extended external table definition and generates an extended query description based on the data type determined in step S1504.
In step S1501, when the query statement generation unit 105 acquires the external database search 1102 of FIG. 11, the query statement generation unit 105 is derived at this stage with selection conditions 1131 to 1135 subordinate to the external database search 1102. An extended external table definition and an extended query description corresponding to the operation result of the operation are generated.

In step S <b> 1506, the query statement generation unit 105 determines whether there is a node higher than the node of the external database search 1102.
When there is an upper node, the query statement generation unit 105 performs the process of step S1507, and when there is no upper node, the query statement generation unit 105 ends the process of FIG.

  In step S <b> 1507, the query statement generation unit 105 extracts a higher order node from the execution plan 145.

In step S1508, the query statement generation unit 105 determines whether there is a selection condition whose data type is not fixed among the selection conditions for the node extracted in step S1507.
If there is a selection condition for which the data type is not fixed, the query statement generation unit 105 performs the process of step S1508.
If the data types of all the selection conditions are fixed, the query statement generation unit 105 performs the process of step S1511.

  In step S1509, the query statement generation unit 105 extracts one selection condition whose data type is not fixed.

In step S1510, the query statement generation unit 105 determines the data type of the selection condition extracted in step S1509.
The data type determination procedure is the same as that described in the first embodiment.

In step S1511, the query statement generation unit 105 updates the extended external table definition and generates an extended query description based on the data type determined in step S1510.
In step S1507, when the query statement generation unit 105 acquires the node of the aggregation 1101 in FIG. 11, the query statement generation unit 105 calculates at this stage the selection conditions 1103 and 1104 that are subordinate to the aggregation 1101. An extended query description corresponding to the operation result of is generated.
Further, the query statement generating unit 105 generates a final extended query statement by combining the extended query description generated in step S1505 and the extended query description generated in step S1511.
Further, the query statement generation unit 105 generates an extended external table definition corresponding to the calculation result of the calculation derived in the selection conditions 1103 and 1104 subordinate to the aggregation 1101, and the extended external table definition generated in step S1505 Update with the extended external table definition generated in S1511.
At this stage, the extended query statement 1221 and the extended external table definition 1210 in FIG. 12 are obtained.

*** Explanation of effects ***
As described above, in this embodiment, the data type of the selection condition is determined, whether or not the operation can be executed in the external database 150, and the extended query description is generated.
In the method of the first embodiment, a standard query statement is generated, the data type of the selection condition is determined, and the extended query statement is generated by rewriting the standard query statement based on the determination result of the data type of the selection condition.
For this reason, it is necessary to search the execution plan a plurality of times, but according to the present embodiment, generation of the extended query statement and expansion of the extended external table definition corresponding to the extended query statement can be performed by searching the execution plan only once. Generation is possible, and the search speed can be improved.

In the present embodiment, the example in which all the operations included in the search condition 301 can be executed by the external database 150 has been described.
When some of the operations included in the search condition 301 cannot be executed in the external database 150, the query statement generation unit 105 generates an extended query statement that causes the external database 150 to execute operations other than the part of the operations.
For example, if “sum ()” other than “sum ()” in the execution plan 145 of FIG. 11 can be calculated in the external database 150, but “sum ()” cannot be executed in the external database 150, the query statement generation unit 105 uses the search condition. An extended query statement that causes the external database 150 to execute an operation other than “sum ()” among a plurality of operations included in 301 is generated.
Then, when a response to the extended query statement is received from the external database 150, the search condition optimization unit 104 performs a “sum ()” operation on the response from the external database 150 and corresponds to the search condition 301. To obtain the result of the operation.

*** Annotation ***
While the embodiments of the present invention have been described above, one of these two embodiments may be partially implemented.
Or you may implement combining the specific part of each embodiment partially.
In addition, this invention is not limited to these embodiment, A various change is possible as needed.

*** Explanation of hardware configuration ***
Finally, an example of the hardware configuration of the data management apparatus 101 will be described with reference to FIG.
The data management apparatus 101 is a computer.
The data management apparatus 101 includes hardware such as a processor 901, an auxiliary storage device 902, a memory 903, a communication device 904, an input interface 905, and a display interface 906.
The processor 901 is connected to other hardware via the signal line 910, and controls these other hardware.
The input interface 905 is connected to the input device 907.
The display interface 906 is connected to the display 908.

The processor 901 is an IC (Integrated Circuit) that performs processing.
The processor 901 is, for example, a CPU (Central Processing Unit), a DSP (Digital Signal Processor), or a GPU (Graphics Processing Unit).
The auxiliary storage device 902 is, for example, a ROM (Read Only Memory), a flash memory, or an HDD (Hard Disk Drive).
The memory 903 is, for example, a RAM (Random Access Memory).
The communication device 904 includes a receiver 9041 that receives data and a transmitter 9042 that transmits data.
The communication device 904 is, for example, a communication chip or a NIC (Network Interface Card).
The input interface 905 is a port to which the cable 911 of the input device 907 is connected.
The input interface 905 is, for example, a USB (Universal Serial Bus) terminal.
The display interface 906 is a port to which the cable 912 of the display 908 is connected.
The display interface 906 is, for example, a USB terminal or an HDMI (registered trademark) (High Definition Multimedia Interface) terminal.
The input device 907 is, for example, a mouse, a keyboard, or a touch panel.
The display 908 is, for example, an LCD (Liquid Crystal Display).

The auxiliary storage device 902 includes the external table definition unit 102, the search control unit 103, the search condition optimization unit 104, and the query statement generation unit 105 (hereinafter, the external table definition unit 102, the search control unit 103) shown in FIGS. , The search condition optimization unit 104 and the query statement generation unit 105 are collectively referred to as “parts”).
This program is loaded into the memory 903, read into the processor 901, and executed by the processor 901.
Further, the auxiliary storage device 902 also stores an OS (Operating System).
Then, at least a part of the OS is loaded into the memory 903, and the processor 901 executes a program that realizes the function of “unit” while executing the OS.
In FIG. 3, one processor 901 is illustrated, but the data management apparatus 101 may include a plurality of processors 901.
A plurality of processors 901 may execute a program for realizing the function of “unit” in cooperation with each other.
In addition, information, data, signal values, and variable values indicating the processing results of “unit” are stored in the memory 903, the auxiliary storage device 902, or a register or cache memory in the processor 901.

The “part” may be provided as “circuitry”.
Further, “part” may be read as “circuit”, “process”, “procedure”, or “processing”.
“Circuit” and “Circuitry” include not only the processor 901 but also other types of processing circuits such as a logic IC or GA (Gate Array) or ASIC (Application Specific Integrated Circuit) or FPGA (Field-Programmable Gate Array). It is a concept to include.

  DESCRIPTION OF SYMBOLS 101 Data management apparatus, 102 External table definition part, 103 Search control part, 104 Search condition optimization part, 105 Query sentence generation part, 111 Data storage part, 120 External table definition, 130 External table calculation rule, 140 External database definition, 145 Execution Plan, 146 Execution Plan, 147 Query Statement Generation Rule, 150 External Database, 151 Search Execution Unit, 152 Data Storage Area, 200 External Table Definition Application, 201 External Table Definition, 300 Data Search Application, 301 Search Condition, 302 Search Result: 1031 Search condition reception unit, 1032 Query execution unit, 1033 Response reception unit, 1034 Response transmission unit, 1051 Standard query statement generation unit, 1052 Extended query statement generation unit.

Claims (10)

A data storage unit for storing external table operation rules in which operations that can be executed in an external database are defined;
A search condition receiver for receiving search conditions;
When a search for data stored in the external database and an operation using the searched data are requested by the search condition received by the search condition receiving unit, based on the external table operation rule, It is determined whether or not the operation requested in the search condition can be executed in the external database, and the operation requested in the search condition can be executed in the external database. A query statement generation unit that generates an extended query statement that is a query statement that causes the external database to execute the requested search and operation;
A data management apparatus comprising: a query execution unit that transmits the extended query statement generated by the query statement generation unit to the external database. The data storage unit
Storing external table operation rules in which operators of operations that can be executed in the external database, data types of operands, and data types of operation results are defined;
The query statement generation unit
Analyzes the operator and operand data types required by the search condition and the data type of the operation result, and calculates the operator and operand data types required by the search condition. The data management device according to claim 1, wherein when the data type of the operation result is defined in the external table operation rule, it is determined that the operation requested by the search condition can be executed in the external database. . The data storage unit
Storing external table operation rules in which a plurality of operations that can be executed by the external database are defined;
The query statement generation unit
When a plurality of operations are requested in the search condition received by the search condition receiving unit, and an execution order of the plurality of operations is specified,
In the order of execution from the previous operation, for each operation, based on the external table operation rule, determine whether or not the operation can be executed in the external database, the search requested by the search condition, The data management apparatus according to claim 1, wherein an extended query statement is generated that causes the external database to execute an operation that can be executed in the external database among the plurality of operations requested by a search condition. The data storage unit
Storing a standard external table definition in which a standard format of a response from the external database is defined;
The query statement generation unit
When a search for data stored in the external database and an operation using the searched data are requested by the search condition received by the search condition receiving unit,
Based on the standard format defined in the standard external table definition, generate a standard query statement that is a query statement that causes the external database to execute a search requested by the search condition,
Based on the external table operation rule, it is determined whether or not the operation requested in the search condition can be executed in the external database, and the operation requested in the search condition can be executed in the external database. The data management apparatus according to claim 1, wherein in some cases, the standard query statement is rewritten to generate the extended query statement. The data storage unit
Storing external table operation rules in which a plurality of operations that can be executed by the external database are defined;
The query statement generation unit
When a plurality of operations are requested in the search condition received by the search condition receiving unit, and an execution order of the plurality of operations is specified,
In the order of execution from the previous operation, for each operation, based on the external table operation rule, determine whether or not the operation can be executed in the external database, the search requested by the search condition, The extended query statement that causes the external database to execute an operation determined to be executable in the external database among the plurality of operations requested by a search condition is generated by rewriting the standard query statement. 4. The data management device according to 4. The data storage unit
Storing external table operation rules in which a plurality of operations that can be executed by the external database are defined;
The query statement generation unit
When a plurality of operations are requested in the search condition received by the search condition receiving unit, and an execution order of the plurality of operations is specified,
A determination process for determining whether or not an operation can be executed in the external database based on the external table calculation rule for each calculation in order from the previous calculation, and executing in the external database by the determination process A description generation process for generating, as an extended query description, a description for causing the external database to execute an operation determined to be possible is repeated, and the extended query statement is generated by a combination of extended query descriptions for each operation. The data management apparatus according to 1. The query statement generation unit
The data management apparatus according to claim 1, wherein an extended external table definition that defines a format of a response from the external database to the extended query statement is generated. The data management device further includes:
The data management apparatus according to claim 7, further comprising a response reception unit that receives a response from the external database with reference to the extended external table definition generated by the query statement generation unit. A computer that stores external table operation rules that define operations that can be executed in an external database.
Receive search criteria,
When the search of the data stored in the external database and the calculation using the searched data are requested by the received search condition, the search condition is requested based on the external table calculation rule. If the operation requested in the external database is executable in the external database, the search and operation requested in the search condition are performed. Generating an extended query statement that is a query statement that causes the external database to execute
A data management method for transmitting the generated extended query statement to the external database. A computer that stores external table operation rules that define operations that can be executed in an external database.
Search condition reception processing for receiving search conditions;
When a search for data stored in the external database with the search condition received by the search condition reception process and an operation using the searched data are requested, based on the external table calculation rule, It is determined whether or not the operation requested in the search condition can be executed in the external database, and the operation requested in the search condition can be executed in the external database. A query statement generation process for generating an extended query statement that is a query statement for causing the external database to execute the requested search and operation;
A data management program that executes query execution processing for transmitting the extended query statement generated by the query statement generation processing to the external database.

Images