JP2012212212A - Data retrieval device, data retrieval method and program - Google Patents

Abstract

PROBLEM TO BE SOLVED: To realize a configuration which reflects a role designated to a column in a retrieval condition to perform data retrieval.SOLUTION: A retrieval server device 3 performs following steps: inputting table definition 2 in which two or more column names of retrieval objects are designated and a role such as a main key and an external key is designated to each column name, as a retrieval condition; adding a weighted value to table data including a column name to which a specific role is designated (for example, a column name to which the main key is designated) in the table definition 2 to calculate an evaluation value; performing ranking of the table data based on the evaluation value of each piece of the calculated table data; and outputting a result of the ranking to an input source of the table definition 2.

Description

  The present invention relates to a data search technique, and more specifically, to a data search technique for data including a plurality of items such as table data (hereinafter also simply referred to as a table).

The database integration process required for system integration requires data migration between tables in different databases. At this time, the process of reflecting the data contents from one to the other according to the correspondence of each column Occurs.
In order to carry out such data migration in real time, a method (Change Data Capture) in which only change difference data is detected and data migration is used.
In such a migration method, it is necessary that the columns of the primary key of the linkage source / destination table match or that one-to-one correspondence is possible.

In order to realize the data linkage processing, it is necessary to detect the correspondence between columns of each table by some means.
In order to automate the operation of detecting such a correspondence relationship, a technique called a schema matching technique is used.
The schema matching technique receives, as an input, a structure such as two tables for detecting correspondence or a schema that is a collection of tables, and outputs the correspondence between these two structures.
As an example, the case of comparing a database table and its child element column will be described below.

In schema matching technology, when detecting the correspondence between columns in each table, multiple determination results are generated by multiple similarity determination methods using column names, data types, and other information. A method of determining the final similarity based on comprehensive judgment is common.
For this reason, the processing is generally large and time-consuming.
In the case of large-scale systems such as social infrastructure (assuming about several thousand tables), it is common to store the database table information in a dedicated database, etc., and perform unified management of schema information. If the information about the linkage source table that can be linked to the table is not obtained in advance, it is necessary to perform schema matching on all the registered tables, which takes a lot of time.

In the technique described in Non-Patent Document 1, a full text search engine is used to avoid schema matching for all tables, and a table group having a column similar to the comparison target table column given as an input is schema unit. Are provided as output together.
Here, the table definition information is given in the data definition language (DDL: Data Definition Language) and the like, and is not a character string such as a search keyword or a search query sentence as in a normal full-text search, but input data. The characteristic is that the data with the structure is passed as it is.
In Non-Patent Document 1, a search keyword is created by extracting table names, column names, and the like included in the table structure and listing them.
Also, an index for full text search is generated in advance for the table information in the schema information DB, and the search keyword generated as described above is given as an input to the full text search engine.
By performing aggregation processing on the output comparison candidate table group and presenting it to the user through a devised GUI (Graphical User Interface), the user selects a table for performing schema matching.

Kuang Chen and Jayt Madhavan and Alon Halevy: Schemr: a Schema Search Engine for Information Integration, ACM SIGMOD '09

In the conventional schema search function, column names and the like are extracted from input table information, a series of search keywords are created, and provided to the full-text search engine.
For example, when the order table has columns of “order ID, customer name, product code, number of orders”, keywords given to the full-text search engine are as follows.
“Order table OR Order ID OR Customer name OR Product code OR Number of orders”
For this reason, there is a problem in that each column in the table to be input for the search is only written together and does not have a function of determining the importance.
When data linkage is assumed, it is considered that the importance of the primary key column is high, but generally only about several primary key columns are included in one table.
Although it is desirable to uniquely identify a table record with one column, there are many cases in which a plurality of columns are actually unique, and there are cases where a plurality of columns become the primary key. It cannot be said that it is composed of columns.
There are cases where the number of columns in a table used in a large-scale system is 100 columns or more, but when the full-text search keyword is indicated by enumeration of column names, the number of columns is 100 columns or more, etc., the primary key column is relatively There is a problem that the importance of becomes low.
As a result, it is assumed that a table in which the comparison target table and the attribute column have many matches but the key column does not match is returned as a search result.
In addition, since the full-text search engine generally uses the number of times the keyword appears as information for determining the search ranking, for example, if a key that is likely to have the same name is included in a plurality of tables such as an external key, the search ranking There is a problem that the foreign key has a great influence on the judgment.

  One of the main objects of the present invention is to solve the above-described problems, and to realize a configuration for performing a data search reflecting the roles specified in items such as a primary key and a foreign key. Main purpose.

The data search apparatus according to the present invention
A data search device that includes a plurality of items, and performs a search for item data in which any of the plurality of items is assigned one of a plurality of roles for data processing,
A coefficient value definition information storage unit for storing coefficient value definition information in which coefficient values for each of the plurality of roles are defined;
A search condition input unit for inputting a search condition in which two or more items to be searched are specified as search target items and a role is specified for each search target item;
A search target item in which a specific role is specified in the search condition is selected as a priority search target item, and a predetermined weight is used as a coefficient value for weight target item data including the priority search target item as the specific role. A priority search target item selection unit that generates a weighting rule for weighting and calculating an evaluation value;
For each search target item, search the item data including the search target item, and if the searched item data does not correspond to the weighted target item data, determine the role of the search target item in the searched item data, and An evaluation value is calculated using a coefficient value corresponding to the role of the item, and when the searched item data corresponds to the weighted item data, the role of the searched item in the searched item data is determined, and the weighting rule A search execution unit that calculates an evaluation value by weighting the weight value to a coefficient value corresponding to the role of the search target item,
Based on the evaluation value calculated by the search execution unit, the item data searched by the search execution unit is ranked, and the search result indicating the result of ranking the item data is output to the input source of the search condition And a search result output unit.

  According to the present invention, the evaluation value is calculated by weighting the item data including the item for which the specific role is specified in the search condition as the specific role, and thus the specific role is specified. It is possible to increase the evaluation value of item data including a certain item, and to perform a data search reflecting the role of the item.

FIG. 3 is a diagram illustrating an example of a system configuration according to the first embodiment. FIG. 3 shows a configuration example of a search server device according to the first embodiment. FIG. 6 is a diagram illustrating an example of inputting a table definition according to the first embodiment. FIG. 4 is a diagram showing a structure of an input table definition according to the first embodiment. FIG. 4 is a flowchart showing an operation example of a query decomposition unit according to the first embodiment. FIG. 4 is a diagram showing an example of an importance determination table according to the first embodiment. FIG. 4 shows an output image of a query decomposition unit according to the first embodiment. FIG. 4 is a flowchart of search processing according to the first embodiment. FIG. 10 is a diagram illustrating an example of inputting a table definition according to the second embodiment. The figure which shows the example of the importance determination table and additional element determination table which concern on Embodiment 2. FIG. FIG. 3 shows an example of a search index according to the first embodiment. FIG. 6 is a diagram showing an example of search index weight data according to the first embodiment. FIG. 6 is a diagram showing an example of calculating an evaluation value according to the first embodiment. FIG. 6 is a diagram showing an example of calculating an evaluation value according to the first embodiment. The figure which shows the output image of the query decomposition | disassembly part which concerns on Embodiment 2. FIG. The figure which shows the hardware structural example of the search server apparatus which concerns on Embodiment 1 or 2. FIG.

Embodiment 1 FIG.
In the present embodiment, an apparatus and method for receiving a structure such as a database table as an input to search processing and returning a search result considering the importance of items included in the structure will be described.
In this embodiment, the weight addition method at the time of creating the search index, the ON / OFF operation of the search index weight at the time of executing the search, the weight given to the search itself, and the weight at the time of collecting the search results By controlling the item, it is possible to reflect the importance of the item in the search result.
Hereinafter, the present embodiment will be described with reference to the drawings.

FIG. 1 shows a system configuration example according to the present embodiment.
FIG. 1 illustrates an example in which table data is searched in the search server device 3 and the search result is presented to the search client device 1.
That is, the search client device 1 gives the table definition 2 to the search server device 3 as a search condition, the search server device 3 searches for one or more tables similar to the table definition 2, and sets the search result as the table group 4. Return to the search client device 1.
The schema matching processing device 5 executes schema matching between one table selected by the user from the table group 4 selected by the search client device 1 and the table definition 2.
The schema matching processing device 5 is not directly related to the operation of the search server device 3 according to the present embodiment, but is represented as a system that uses the output of the search server device 3 according to the present embodiment.

The search client device 1 requests the search server device 3 to search the table.
The table definition 2 is search condition data that the search client device 1 gives to the search server device 3.

The search server device 3 receives a search request from the search client device 1 and returns a search result.
More specifically, the search server device 3 inputs, as a search condition, a table definition 2 in which two or more column names to be searched are specified and a role such as a primary key and a foreign key is specified for each column name. .
Then, the search server device 3 evaluates by weighting the table data including the column name (for example, the column name for which the primary key is specified) for which a specific role is specified in the table definition 2. A value is calculated, the table data is ranked based on the calculated evaluation value for each table data, and the ranking result is output to the search client device 1 that is the input source of the table definition 2.
As shown in FIG. 1, the search server device 3 is roughly divided into a table search unit 31 and a table registration unit 32.
Details of the table search unit 31 and the table registration unit 32 will be described later.
The search server device 3 is an example of a data search device.

The table group 4 is a search result output by the search server device 3.
The schema matching processing device 5 executes schema matching.

The target table data in this embodiment includes a plurality of columns (items), and any one of a plurality of types of roles for data processing is assigned to an arbitrary column among the plurality of columns. It has been.
As described above, the plurality of roles for data processing include, for example, a primary key, a foreign key, an index column, and the like.
The target table data in this embodiment corresponds to an example of item data.

FIG. 2 shows an internal configuration example of the search server device 3.
The search server device 3 includes a table search unit 31 and a table registration unit 32.

The table search unit 31 includes a query reception unit 310, a query decomposition unit 311, a search instruction unit 312, an index weighting ON / OFF unit, a search execution unit 314, a result aggregation unit 315, a query return unit 316, and a search index storage unit 317. Is done.
The table registration unit 32 includes a table information registration unit 320, a duplication detection unit 321, a schema information DB 322, a search index creation unit 323, and an index weight setting unit 324.
The table data is stored in the schema information DB 322.
Below, it demonstrates from the table search part 31. FIG.

In the table search unit 31, the search index storage unit 317 stores a search index 3140 and search index weight data 3141.
The search index 3140 is information indicating which column name is included in which table data.
FIG. 11 is an example of the search index 3140.
As shown in FIG. 11, in the search index 3140, for each column name, a table identifier including the column name and which column type (role) the column name is included in the table are displayed. Is done.
For example, the column name “order ID” is included as a primary key in the table A-A, and is included as an attribute column in the table A-C.
The search index weight data 3141 is information in which coefficient values for each of a plurality of types of columns (role) are defined as index weights.
FIG. 12 is an example of search index weight data 3141.
For example, a coefficient value “2.0” is defined as the index weight for the primary key, and a coefficient value “1.0” is defined as the index weight for the foreign key.
A column type having a large index weight value is preferentially handled in table search.
The index weight value for each column type in the search index weight data 3141 matches the index weight value shown in the importance determination table 33 described later.
The duplicate column will be described later.
The search index weight data 3141 corresponds to an example of coefficient value definition information, and the search index storage unit 317 that stores the search index weight data 3141 that is coefficient value definition information corresponds to an example of a coefficient value definition information storage unit.
The operation in which the search index storage unit 317 stores the search index weight data 3141 corresponds to an example of a coefficient value definition information storage step.

  Below, operation | movement of each element when a search is implemented in the table search part 31 is demonstrated.

The search server device 3 receives the table definition 2 from the search client device 1 as a search input.
The table definition 2 is received by the query receiving unit 310 in the table search unit 31.
The table definition 2 is information having a structure, and the database table definition is assumed in the first embodiment.
The table definition 2 of the database may be passed in a data definition language (DDL) as shown in FIG. 3, for example.
FIG. 3 shows an example of the table definition 2 of the order table (description example by DDL), where “order ID”, “customer name”, “product code”, and “number of orders” exist as columns, and “ "Order ID" is a primary key alone, "Product code" is an external key that refers to the product code in the product table, and an index that is a database function is assigned to "Customer name" Represents.
The table definition 2 can be expressed in the data definition language (DDL) as shown in FIG. 3, but any format can be used as long as the structure can be expressed. For example, an XML (Extensible Markup Language) format may be used. Good.
The query receiving unit 310 receives, for example, the table definition 2 in the format of FIG. 3, interprets it, and acquires the structure information as shown in FIG.
FIG. 4 represents the result of interpreting the structure of the table definition 2 (DDL) in FIG. 3 as a diagram.
In this way, the query receiving unit 310 searches the table definition 2 in which two or more columns (items) to be searched are specified and a role (primary key, foreign key, etc.) is specified for each column to be searched. It is input as a condition and corresponds to an example of a search condition input unit.
The operation in which the query receiving unit 310 receives the table definition 2 corresponds to an example of a search condition input step.

  The query decomposition unit 311 groups the column information included in the structure information extracted by the query receiving unit 310 according to the importance according to the importance determination table 33 set in advance, and performs a search when searching each group. Set weights and weights for search results.

FIG. 5 shows a flowchart of the query decomposition unit 311.
An example of the importance determination table 33 is shown in FIG.
In the example of FIG. 6, a primary key, a foreign key, an index column, and an attribute column are designated in order of importance.
The importance determination table 33 in FIG. 6 will be described below.

The order of importance 331 indicates the importance of the roles described in the column type 332, and is set by serial numbers starting from 1.
The index weight 333 is used to specify a weight to be set for the role of the column name appearing in the table when calculating the evaluation value for each table according to the order of the designated importance order 331.
As described above, the value of the index weight 3141 b of the search index weight data 3141 (FIG. 12) matches the value of the index weight 333 of the importance determination table 33.

A description will be given of the duplicate columns described in FIG.
When registering table information in the schema information DB 322, if a column with the same name is detected as the same role in the same schema, the column is determined to be a duplicate column.
For the column determined to be a duplicate column, the index weight is set to 1.0 or less according to the table of FIG. 6 (0.5 in the example of FIG. 6).
For example, when the schema design is inefficient, the same column name may appear as the same role in multiple tables of the same schema.
In such a case, if the evaluation value is counted each time the same column name appears in the same role, the evaluation value of the table group of such a schema becomes higher than the actual value, and there is a possibility that the appropriate search result is not obtained. is there.
Therefore, when columns with the same name are detected as the same role in the same schema, the coefficient values for the second and subsequent detections are suppressed to be lower than other coefficient values.

The contents of the importance determination table 33 are set for the following reason, and the contents of FIG. 6 are set as default values, but can be changed.
In the first embodiment, the table definition 2 is assumed to be a database table, and it is assumed that data migration between different databases is performed by using a change difference data extraction technique.
For this reason, in order to be able to link data, it is necessary that the columns of the primary key of the link source database table and the link destination database table match or have a one-to-one relationship.
The most important column in this situation is the primary key column.
The primary key column is a column having a value that uniquely identifies record data of each row included in a database table.
The primary key may generally consist of multiple columns.
Since the foreign key refers to the primary key of another database table, the existence of the foreign key means that the corresponding primary key exists.
Therefore, the importance of the foreign key column is set next to the primary key column.
The index column corresponds to the customer name in the table definition 2 (DDL) in FIG.
The reason for setting an index in the database is to improve the search speed for a frequently used column.
That is, it can be determined that the index column is the next most important column after the primary key and the foreign key.
In addition to this, there is a so-called unique key, which is determined to be equivalent to the primary key in that it uniquely identifies a record in a table similar to the primary key. I do not distinguish.
Other column types are expressed as attribute columns in FIG. 6, and columns that do not correspond to any of the primary key, foreign key, and index column are classified as the attribute columns.

  The flowchart of FIG. 5 will be described below.

The query decomposition unit 311 receives the structure information of FIG. 4 provided by the query reception unit 310 (S3111).
Next, the query decomposing unit 311 refers to the importance determination table 33 as shown in FIG. 6 (S3112), and assigns each column (order ID, customer name, etc.) to the importance based on the importance determination table 33. Separately grouped (S3113).
In the example of FIG. 6, the data is classified into a primary key, a foreign key, an index column, and an attribute column.
In order to perform a search process for each column group classified according to importance, information necessary for each search process is created by the query decomposing unit 311 repeatedly executing steps S3114 and S3115 for the number of column groups.
Hereinafter, the details of the repeated processing will be described.
The table shown in FIG. 4 is used as search input data.
In the first loop of S3114, the primary key whose importance order 331 is “1” is processed, and the column group classified as the primary key in the structure information of FIG. 4 is processed.
In order to handle the search result of the primary key as a reference, in S3114, an index weight value “1.0” is assigned to the primary key.
That is, no weighting is performed.
In S3115, a search keyword is created, but only the column names included in the primary key column group are listed for the primary key.
In the second loop of S3114, the foreign key whose importance order 331 is “2” is processed, and the column group classified as the foreign key in the structure information of FIG. 4 is processed.
For columns other than the primary key, the reciprocal of the number of columns is assigned as a search aggregation weight.
Here, the reciprocal of the number of columns of the foreign key is assigned as the search aggregation weight in S3114.
When all the columns other than the primary key are detected in the search, this weighting is performed in order to obtain the same effect as when one primary key is detected.
In S3115, a search keyword is generated.
Here, a search keyword including a weighting rule for weighting the index weight with a weight value is generated for the table in which the primary key is detected.
Specifically, when a foreign key column is included in the same table as the table including the primary key, the search score is an index weight 333 in which the value of the importance order 331 in the importance determination table 33 is “1”. The search keyword is to multiply.
The index weight 333 to be weighted here is a weight at the time of index creation assigned to the primary key (that is, “2.0”).
However, since the search is not performed at the time of executing S3115, the table name including the primary key is designated as a parameter, and is replaced with the actual table name when the search is executed.
The second and subsequent loop processes are the same as the second process as far as S3114 and S3115 are concerned.
The query decomposition unit 311 confirms that there is no unprocessed column group in S3116, and returns the processing result in S3117.
The processing result has the contents shown in FIG.
The search execution content information in FIG. 7 will be described below.

FIG. 7 shows a data structure for passing information necessary for executing the search to the search instruction unit 312.
The search execution content information in FIG. 7 is generated by executing the flow shown in FIG. 5 based on the structure information in FIG.

Searches are performed by the search execution unit 314 in the order described in “Search order”.
“Search column list” indicates a column group grouped according to the type of “column type”.
“Column type” is not essential information for search execution, but is described to indicate which column type each line in FIG. 7 corresponds to.

“Index weight ON / OFF” indicates whether the weight described in the search index weight data 3141 is validated (ON) or invalidated (OFF) at the time of retrieval.
In principle, the index weight is set to ON for the primary key, foreign key, and index column, and is set to OFF for the attribute column.
In addition, the weighting rule of the evaluation value indicated by “search keyword” is not applied to the column type for which the index weight is OFF.

“Search total weight” is for controlling the weight of the number of cases detected in the search process.
When one column of the primary key is detected and the other columns are detected, the importance level is different. Therefore, when all the columns other than the primary key are detected, it is equivalent to the primary key 1 column. The value divided by the number of columns so as to be evaluated is used as the “search aggregation weight”.
The search result for each column type is weighted by multiplying this weight by each search point value for the search result returned by the search execution unit 314.
For example, in any one table, if any two columns described in the search column list are extracted as foreign keys, there are two foreign keys, so the search aggregation weight is “0.5”. The evaluation value is calculated by multiplying the value of the index weight of each column by 0.5.

“Search keyword” holds the search keyword created in S3115 of FIG.
The “search keyword” is a keyword in which each column name is separated by OR for the primary key. In the example of FIG. 4, only “order ID” is specified. Therefore, only “order ID” is specified as the search keyword.
However, when a plurality of columns are designated as primary keys in the table definition 2, each column is a search keyword.
In the “search keyword” of the foreign key, an index weight (“1.5” in the example of FIG. 12) is used for a column that is a foreign key in a table that includes “order ID” as a primary key. Is specified to be multiplied by 2.0.
Similarly, in the index column “search keyword”, an index weight (“1.2” in the example of FIG. 12) is used for a column that is an index column in a table including “order ID” as a primary key. ) Is multiplied by 2.0.
Similarly, in the “search keyword” of the attribute column, the index weight (“1.0” in the example of FIG. 12) is used for the column that is the attribute column in the table including “order ID” as the primary key. ) Is multiplied by 2.0.

As described above, the query decomposition unit 311 selects the column “order ID” in which the specific role is specified in the table definition 2, specifically, the column “order ID” in which the role of the primary key is specified. An evaluation value is calculated by weighting a table (corresponding to weighted item data) with “order ID” as a primary key (weighted by “2.0” in the example of FIG. 7) to the index weight coefficient value. A search keyword including a weighting rule to generate the search is generated.
In the present embodiment, the column “order ID” in which the role of the primary key is specified in the table definition 2 corresponds to an example of the priority search target item.
The query decomposition unit 311 corresponds to an example of a priority search target item selection unit.
The operation of the query decomposition unit 311 corresponds to an example of a priority search target item selection step.

The search instruction unit 312 receives the search execution content information of FIG. 7 provided by the query decomposition unit 311 and instructs the search execution unit 314 to execute search according to the content of FIG.
The search instructing unit 312 issues an instruction to the index weighting ON / OFF unit 313 according to the information given by the query decomposing unit 311, and changes the validation and invalidation of the search index weight data 3141 used by the search execution unit 314.

  FIG. 8 is a flowchart for explaining the operations of the search instruction unit 312, the index weighting ON / OFF unit 313, and the search execution unit 314.

In step S3121, the search instruction unit 312 interprets the search execution content information in FIG.
In step S312, the search instruction unit 312 determines the search execution order in the order specified in the “search order” in FIG.
That is, the search instruction unit 312 selects a search process having the smallest “search order” among the unprocessed search processes.
In S 3123, the search instruction unit 312 validates or invalidates the search index weight data 3141 via the index weight ON / OFF unit 313 according to “index weight ON / OFF” of FIG.
When the search index weight data 3141 is validated and the index weight is 1.0 or less, a setting for ignoring this is also performed at the same time.
The search index weight data 3141 is set by an index weight setting unit 324 in the table registration unit 32 (to be described later). When columns with the same name exist in the same schema with the same role, the second and subsequent columns are searched. The index weight is set to 1.0 or less, which is performed in order to prevent an influence on the search result when there are many columns having the same name in the same schema such as a foreign key.
In S3124, the search instruction unit 312 gives the “search keyword” of FIG. 7 to the search execution unit 314, and instructs the search execution.
In the second and subsequent searches, the table name detected in the first search, that is, the table name detected as the table including the primary key when the primary key is searched is included in the “search keyword”.
That is, the part (the same table as the primary key) in the “search keyword” in FIG. 7 is replaced with the actual table name.
For this reason, S3124 has an effect of narrowing down the search target from the first search result.
In the second and subsequent searches, the search instruction unit 312 gives the “search column list” together with the “search keyword” in FIG. 7 to the search execution unit 314 to search the columns in the “search column list”. .
In S3125, the search instruction unit 312 confirms an unprocessed column group, and in S3126, returns the search result and the value of “search aggregation weight” in FIG. 7 corresponding to the search result.

Here, an example of the operation of the search execution unit 314 will be specifically described with reference to FIG.
The operation of the search execution unit 314 corresponds to an example of a search execution step.

The contents of the search index 3140 in FIG. 13 are the same as those in FIG.
Here, the search execution unit 314 uses the search index 3140 in FIG. 13 to calculate an evaluation value for each table according to the search execution content information in FIG. 7 and the search index weight data 3141 in FIG.
Table A-A, table AB, and table AC are tables having the same schema.

The table AA includes a column “order ID” as a primary key.
According to the search execution content information of FIG. 7, since the index weight is ON for the primary key, the evaluation value “2.0” is recorded in the table AA according to the search index weight data 3141.
The table A-C includes a column “order ID”, but is an attribute column.
According to the search execution content information in FIG. 7, the index weight is OFF for the attribute column, and therefore the evaluation value “1.0” is recorded in the table AC.

The table “A-A” includes the column “product code” as an external key.
According to the search execution content information of FIG. 7, the index weight is ON for the foreign key.
In addition, since the column “order ID” is included as a primary key in the table A-A, the evaluation value becomes 2.0 times according to the weighting rule of the evaluation point of “search keyword” in FIG.
Therefore, according to the search index weight data 3141, the evaluation value “1.5 × 2.0 = 3.0” is recorded in the table A-A.
The table “AB” includes a column “product code” as a primary key.
According to the search execution content information in FIG. 7, the index weight is ON for the primary key.
Since the table “AB” does not include the column “order ID” as the primary key, the evaluation point is not weighted and the evaluation value “2.0” is recorded.
The table “A-C” includes a column “product code” as an external key.
According to the search execution content information of FIG. 7, the index weight is ON for the foreign key.
Since the table “AC” does not include the column “order ID” as the primary key, there is no weighting of evaluation points.
In addition, since the column “product code” is included as a foreign key in both the table AA and the table AC in the same schema, the column “product code” in the table A-C becomes a duplicate column and is duplicated. The column evaluation value “0.5” is counted.

The table “A-A” includes a column “customer name” as an index column.
According to the search execution content information in FIG. 7, the index weight is ON for the index column.
In addition, since the column “order ID” is included as a primary key in the table A-A, the evaluation value becomes 2.0 times according to the weighting rule of the evaluation point of “search keyword” in FIG.
Therefore, in accordance with the search index weight data 3141, the evaluation value “1.2 × 2.0 = 2.4” is recorded in the table A-A.
Table AB includes a column “customer name”, but is an attribute column.
According to the search execution content information in FIG. 7, the index weight is OFF for the attribute column, and therefore the evaluation value “1.0” is recorded in the table AB.

The table A-A includes a column “number of orders” but is an attribute column.
According to the search execution content information in FIG. 7, the index weight is OFF for the attribute column, and therefore the evaluation value “1.0” is recorded in the table A-A.
In the table A-C, the column “number of orders” is included as an index column.
According to the search execution content information in FIG. 7, the index weight is ON for the index column.
The table “A-C” does not include the column “order ID” as the primary key, so the evaluation point is not weighted and the evaluation value “1.2” is recorded.

In the description using FIG. 13, the table A-A in which the column “order ID” is included as the primary key includes the “product code” specified as the external key in FIG. 7 as the external key. In the above example (and the case where “customer name” designated as the index column in FIG. 7 is included as the index column), the example in which the evaluation value is multiplied by 2.0 has been described.
On the other hand, the condition interpretation of the “search keyword” may be relaxed.
An example of relaxation of condition interpretation will be described with reference to FIG.

FIG. 14 shows the search index 3140. Unlike FIG. 13, the table A-A shows an example in which the column “product code” is included as an index column.
In FIG. 7, a foreign key is designated for “product code”.
Therefore, when the strict condition interpretation described with reference to FIG. 13 is applied to FIG. 14, the column “product code” is not included as a foreign key, so the evaluation value is not weighted and the table A -A includes the index weight “1.2” of the index column.
On the other hand, in the relaxed condition interpretation, the column type is different, but the table “A-A” includes the column “product code” defined in FIG. Is weighted with a weighting factor of “2.0” and “1.2 × 2.0 = 2.4” is recorded.
As described above, any one of the two methods for the weighting rule of “search keyword” may be adopted.

For example, after the search execution unit 314 calculates an evaluation value for each table according to the procedure illustrated in FIG. 13 or FIG. 14, the result aggregation unit 315 acquires the evaluation value for each table from the search execution unit 314, and the evaluation value On the other hand, the weighted aggregation is performed by the “search aggregation weight” in FIG. 7, and the final determination of the ranking of the search results is performed.
That is, the search execution unit 314 adds the evaluation values after the weighted aggregation by the “search aggregation weight” is performed in units of tables, ranks the tables in descending order of the total values, and displays the list according to the ranking. The list to be returned to the query return unit 316.
The query return unit 316 outputs the list from the result aggregation unit 315 as the table group 4 and passes it to the search client device 1.
Note that the result aggregation unit 315 and the query return unit 316 correspond to an example of a search result output unit.
In addition, the operations of the result aggregation unit 315 and the query return unit 316 correspond to an example of a search result output step.

In the search client device 1, a database table for performing schema matching is selected from the table group 4 received by the user of this system, and is passed to the schema matching processing device 5.
The schema matching processing device 5 performs schema matching processing with the table definition 2 and the database table selected by the user as inputs.

  Hereinafter, the operation of each element when a database table is registered in the system by the table registration unit 32 and a search index is created will be described.

The table information registration unit 320 is a starting point for the work of registering the database table in the schema information DB 322.
The table information registration unit 320 receives the database table information as an input by a data definition language (DDL) or an XML file, and passes the database table information to the duplication detection unit 321.

The duplicate detection unit 321 confirms the contents of the schema information DB 322 and confirms whether or not information that overlaps the database table information has already been registered.
At this time, the duplication detection unit 321 confirms the duplication of the table indicated by the database table information in the same schema.
It doesn't matter if there are duplicate tables in different schemas.
The schema information DB 322 stores database table information.
The search index creation unit 323 receives a notification from the table information registration unit 320 that the database table information has been stored in the schema information DB 322, and adds an index weight setting unit 324 to add the database table information to the search index 3140. Pass information to.

The index weight setting unit 324 writes the weight data into the search index weight data 3141 according to the importance determination table 33 given in advance as shown in FIG. 6 from the contents of the registered database table information.
At this time, if the duplicate detection unit 321 detects duplicate database tables in the same schema, the weight is set to “1.0” or less according to the table of FIG.

As described above, according to the present embodiment, when an entity having a structure like a database table is given as an input of the search system, a search reflecting the importance of each item in the entity can be performed.
As a specific example, for the primary key column of the table that is the search input, it is also the primary key column, and a table containing many foreign key columns and index target columns that are important items in the table is returned as the search result. It becomes possible to do.
In addition, even when there is no such table in the table group to be searched, the primary key of the search input table is detected as a foreign key column that is an important column or a table that is an index target column, Furthermore, it is possible to return a table group including as many other columns as possible in the same schema for the other columns of the search input table.

In the present embodiment, the search client device 1 requests the search server device 3 to search for table data composed of a plurality of columns, and the search server device 3 receives one or more table data as a search result. The example presented to the search client device 1 has been described.
However, in addition to the table data, if the item data includes a plurality of items, and any item of the plurality of items can be assigned any one of a plurality of roles for data processing, The search server device 3 according to the present embodiment is applicable.

In this embodiment,
An entity having a structure such as a database table is received as input, a similarity is calculated from a set of entities registered in advance by a specified method, and a set having high similarity to the entity given as input is obtained. A search system that returns items in order of similarity ranking,
A schema information DB for storing definition information of the set of entities;
A search index creating unit for creating a search index for realizing a search process for the entity in the schema information DB;
A search execution unit that performs a search using the search index;
From the inputted definition information having the structure of the entity, the structure is interpreted to extract important items based on the importance information of the column type of the entity given in advance, and input to the search execution unit A query decomposition unit that provides input items to a plurality of preset search tasks, and provides a weight value to be given to the search tasks;
A search instruction unit for instructing the search unit to perform a search operation provided by the query decomposition unit;
A search weighting ON / OFF unit that switches between enabling and disabling weighting on the search index during search execution;
A result aggregator that aggregates search results;
A schema search apparatus and method having a query return unit that returns aggregated results to a search request source has been described.

In the present embodiment,
It has been described that the search index creation unit creates a search index by adding weighting information to important items of the entity according to a preset weighting table.

In the present embodiment,
The search index creation unit detects when there is already an item to be weighted in the search index creation and an item determined to be matched by the search unit, and sets the lowest weight according to a preset weighting table. Explained.

In the present embodiment,
The search weighting ON / OFF unit receives information indicating whether the search process to be executed is a search for an important item or a search for other items, and switches between adopting or not adopting a search index weight according to this information. Explained what to do.

In the present embodiment,
The search weighting ON / OFF unit receives information indicating whether the search process to be executed is a search for an important item or a search for other items, and according to this information, the search index weight is equal to or less than a preset threshold value. When I explained that I ignored the search target.

In the present embodiment,
The query decomposition unit explained that, when the input is database table information, in addition to the primary key constraint and the foreign key constraint, the column included in the table index constraint and the column having the unique constraint are determined as the important columns.

Embodiment 2. FIG.
In the first embodiment, the example in which the search condition is given using the column name and the role (primary key, foreign key, etc.) assigned to the column name has been described.
In this embodiment, an example in which a search condition is given using a data element other than a column name, specifically, a comment, a logical name, or the like will be described.

FIG. 9 is an example of the table definition 2 according to the present embodiment, and shows an example provided in the data definition language (DDL).
In the example of FIG. 9, comment information is added to the column in the COMMENT statement of the data definition language, and information “customer name” is added to the customer name column.
When additional information is obtained in this way, information can be added and described in the importance determination table 33 as shown in FIG.
In the additional element determination table of FIG. 10, in addition to comments, index weights and order of importance when using logical names and table names as column search information are specified.
Further, an additional weight for adding a search point to the importance determination table 33 is defined.
In the example of FIG. 10, “0.6” is set.
It is assumed that the importance level of the additional information is set according to the use environment as a user judgment.

The importance determination table 33 is used when the index weight setting unit 324 creates the search index weight data 3141. Therefore, the importance determination table 33 is searched by treating the additional element determination table in FIG. The index weight data 3141 is created.
In addition, since the importance determination table 33 is referred to by the query decomposition unit 311 at the time of executing the search, the search including the additional elements can be performed according to the additional element determination table of FIG.

In the present embodiment, when a comment or the like is associated with a column name in the table definition 2 (in the example of FIG. 9, the comment “customer name” is associated with the column name “customer name”). The query decomposition unit 311 handles comments and the like equivalent to the associated column names.
That is, in the case of the table definition 2 in FIG. 9, the query decomposition unit 311 generates search execution content information shown in FIG.
In the search execution content information of FIG. 15, a record of a comment “customer name” is provided, and “column type” is “index column” similarly to “customer name”.
In addition, “index weight ON / OFF”, “search aggregation weight”, and “search keyword” are the same as the record of “customer name”.
The search instruction unit 312, the index weighting ON / OFF unit 313, and the search execution unit 314 perform the same operation as in the first embodiment according to the search execution content information of FIG.

On the other hand, when a comment or the like is not associated with any column name in the table definition 2, the search execution unit 314 uses the index weight value and the additional weight of the additional element determination table in the search index weight data 3141. The evaluation value for each table is calculated.
That is, the search index weight data 3141 according to the present embodiment reflects the index weight value and the additional weight of the additional element determination table in FIG. 10, and the search execution unit 314 is specified in the table definition 2. If a comment or the like is included in the table, an evaluation score for the comment or the like is added to the table.
For example, if a certain table includes the logical name specified in the table definition 2, the search execution unit 314 performs the search on the table regardless of the position and column where the logical name is included. Record “2.0 × 0.6 = 1.2”.
In the case of comments and table names, they are “0.9” (1.5 × 0.6 = 0.9) and “0.72” (1.2 × 0.6 = 0.72), respectively. Since it is less than “1.0”, the comment and the table name may be moved up to “1.0”.
In addition, when a comment that is specified in the same table definition 2 is included in a table in which the column name specified as the primary key in the table definition 2 is included as the primary key, the evaluation value is “2”. .0 ”times.

  As described above, according to the present embodiment, it is possible to add consideration to additional information in addition to the match or similarity of column names in the table to be searched. It is possible to extend without limiting to column name comparison.

In this embodiment,
When the input database table information includes a comment, a logical name, etc., a schema search device and method for creating a search query and creating a search index using the query decomposition unit and the search index creation unit as information for matching items Explained.

Finally, a hardware configuration example of the search server device 3 shown in the first and second embodiments will be described.
FIG. 16 is a diagram illustrating an example of hardware resources of the search server device 3 illustrated in the first and second embodiments.
The configuration in FIG. 16 is merely an example of the hardware configuration of the search server device 3, and the hardware configuration of the search server device 3 is not limited to the configuration shown in FIG. Also good.

In FIG. 16, the search server device 3 includes a CPU 911 (also referred to as a central processing unit, a central processing unit, a processing unit, a processing unit, a microprocessor, a microcomputer, and a processor) that executes a program.
The CPU 911 is connected to, for example, a ROM (Read Only Memory) 913, a RAM (Random Access Memory) 914, a communication board 915, a display device 901, a keyboard 902, a mouse 903, and a magnetic disk device 920 via a bus 912. Control hardware devices.
Further, the CPU 911 may be connected to an FDD 904 (Flexible Disk Drive), a compact disk device 905 (CDD), a printer device 906, and a scanner device 907. Further, instead of the magnetic disk device 920, a storage device such as an SSD (Solid State Drive), an optical disk device, or a memory card (registered trademark) read / write device may be used.
The RAM 914 is an example of a volatile memory. The storage media of the ROM 913, the FDD 904, the CDD 905, and the magnetic disk device 920 are an example of a nonvolatile memory. These are examples of the storage device.
The “search index storage unit 317” described in the first and second embodiments is realized by the RAM 914 and the magnetic disk device 920, for example.
The “schema information DB 322” described in the first and second embodiments is realized by the magnetic disk device 920, for example.
A communication board 915, a keyboard 902, a mouse 903, a scanner device 907, an FDD 904, and the like are examples of input devices.
The communication board 915, the display device 901, the printer device 906, and the like are examples of output devices.

The communication board 915 is connected to the network.
For example, the communication board 915 is connected to a LAN (local area network), the Internet, a WAN (wide area network), a SAN (storage area network), and the like.

The magnetic disk device 920 stores an operating system 921 (OS), a window system 922, a program group 923, and a file group 924.
The programs in the program group 923 are executed by the CPU 911 using the operating system 921 and the window system 922.

The RAM 914 temporarily stores at least part of the operating system 921 program and application programs to be executed by the CPU 911.
The RAM 914 stores various data necessary for processing by the CPU 911.

The ROM 913 stores a BIOS (Basic Input Output System) program, and the magnetic disk device 920 stores a boot program.
When the search server device 3 is activated, the BIOS program in the ROM 913 and the boot program in the magnetic disk device 920 are executed, and the operating system 921 is activated by the BIOS program and the boot program.

  The program group 923 stores programs that execute the functions described as “˜parts” (except for “search index storage unit 317” in the following) in the description of the first and second embodiments. The program is read and executed by the CPU 911.

In the file group 924, in the description of the first and second embodiments, “determination of”, “retrieval of”, “extraction of”, “calculation of”, “calculation of”, “accounting of” ”,“ Specify ”,“ evaluate ”,“ generate ”,“ create ”,“ update ”,“ set ”,“ register ”,“ select ”, Information, data, signal values, variable values, and parameters indicating the results of the processing described as "~ input", "~ output", etc. are stored as elements of "~ file" and "~ database". ing.
The “˜file” and “˜database” are stored in a recording medium such as a disk or a memory.
Information, data, signal values, variable values, and parameters stored in a storage medium such as a disk or memory are read out to the main memory or cache memory by the CPU 911 via a read / write circuit.
The read information, data, signal value, variable value, and parameter are used for CPU operations such as extraction, search, reference, comparison, calculation, calculation, processing, editing, output, printing, and display.
Information, data, signal values, variable values, and parameters are stored in the main memory, registers, cache memory, and buffers during the CPU operations of extraction, search, reference, comparison, calculation, processing, editing, output, printing, and display. It is temporarily stored in a memory or the like.
In addition, the arrows in the flowcharts described in the first and second embodiments mainly indicate input / output of data and signals.
Data and signal values are recorded on a recording medium such as a memory of the RAM 914, a flexible disk of the FDD 904, a compact disk of the CDD 905, a magnetic disk of the magnetic disk device 920, other optical disks, a mini disk, and a DVD.
Data and signals are transmitted online via a bus 912, signal lines, cables, or other transmission media.

In addition, what is described as “˜unit” in the description of the first and second embodiments may be “˜circuit”, “˜device”, “˜device”, and “˜step”, It may be “˜procedure” or “˜processing”.
That is, the “data search method” according to the present invention can be realized by the steps, procedures, and processes described in the first and second embodiments.
Further, what is described as “˜unit” may be realized by firmware stored in the ROM 913.
Alternatively, it may be implemented only by software, or only by hardware such as elements, devices, substrates, and wirings, by a combination of software and hardware, or by a combination of firmware.
Firmware and software are stored as programs in a recording medium such as a magnetic disk, a flexible disk, an optical disk, a compact disk, a mini disk, and a DVD.
The program is read by the CPU 911 and executed by the CPU 911.
That is, the program causes the computer to function as “to part” in the first and second embodiments. Alternatively, the computer executes the procedure and method of “to unit” in the first and second embodiments.

As described above, the search server device 3 shown in the first and second embodiments includes a CPU as a processing device, a memory as a storage device, a magnetic disk, a keyboard as an input device, a mouse, a communication board, a display device as an output device, a communication device, and the like. A computer including a board or the like.
Then, as described above, the functions indicated as “˜units” are realized using these processing devices, storage devices, input devices, and output devices.

  DESCRIPTION OF SYMBOLS 1 Search client apparatus, 2 Table definition, 3 Search server apparatus, 4 Table group, 5 Schema matching processing apparatus, 31 Table search part, 32 Table registration part, 33 Importance judgment table, 310 Query reception part, 311 Query decomposition part, 312 Search instruction unit, 313 Index weighting ON / OFF unit, 314 Search execution unit, 315 Result aggregation unit, 316 Query return unit, 317 Search index storage unit, 320 Table information registration unit, 321 Duplicate detection unit, 322 Schema information DB, 323 Search index creation unit, 324 Index weight setting unit, 3140 Search index, 3141 Search index weight data.

Claims (12)

A data search device that includes a plurality of items, and performs a search for item data in which any of the plurality of items is assigned one of a plurality of roles for data processing,
A coefficient value definition information storage unit for storing coefficient value definition information in which coefficient values for each of the plurality of roles are defined;
A search condition input unit for inputting a search condition in which two or more items to be searched are specified as search target items and a role is specified for each search target item;
A search target item in which a specific role is specified in the search condition is selected as a priority search target item, and a predetermined weight is used as a coefficient value for weight target item data including the priority search target item as the specific role. A priority search target item selection unit that generates a weighting rule for weighting and calculating an evaluation value;
For each search target item, search the item data including the search target item, and if the searched item data does not correspond to the weighted target item data, determine the role of the search target item in the searched item data, and An evaluation value is calculated using a coefficient value corresponding to the role of the item, and when the searched item data corresponds to the weighted item data, the role of the searched item in the searched item data is determined, and the weighting rule A search execution unit that calculates an evaluation value by weighting the weight value to a coefficient value corresponding to the role of the search target item,
Based on the evaluation value calculated by the search execution unit, the item data searched by the search execution unit is ranked, and the search result indicating the result of ranking the item data is output to the input source of the search condition And a search result output unit. The priority search target item selection unit
When the role of the search target item included in the weight target item data matches the role specified for the search target item in the search condition, the weight value is weighted to the coefficient value corresponding to the role and evaluated Generate a weight rule to calculate the value,
The search execution unit
When the searched item data corresponds to the weighted item data, it is determined whether the role of the search item in the searched item data matches the role specified for the search item in the search condition. 2. The data search apparatus according to claim 1, wherein when the roles match, the evaluation value is calculated by weighting the weight value to a coefficient value corresponding to the role. The priority search target item selection unit
Among the roles specified for each search target item in the search condition, specify a role that does not apply the weighting rule as a rule non-applicable role,
The search execution unit
Even if the searched item data corresponds to the weighted item data, if the role of the search target item in the searched item data corresponds to the role not applicable to the rule, the coefficient value corresponding to the role 3. The data search apparatus according to claim 1, wherein the weight value is not weighted. The coefficient value definition information storage unit
According to the priority order provided for the plurality of types of roles, storing coefficient value definition information in which the higher the priority order, the higher the coefficient value,
The priority search target item selection unit
The data search device according to any one of claims 1 to 3, wherein a search target item for which a role having the highest priority is specified in the search condition is selected as a priority search target item. The search execution unit
The evaluation value is calculated without using the coefficient value for the retrieved item data if the coefficient value used for calculation of the evaluation value is less than a predetermined threshold value. Data retrieval device. The coefficient value definition information storage unit
The coefficient value when the same search target item is included in the same role in two or more item data that are related to each other is defined as a duplicate coefficient value, and the duplicate coefficient value is defined as a value lower than other coefficient values. Stores coefficient value definition information,
The search execution unit
When the search target item included in the searched item data is included in other searched item data related to the item data, and the role of the search target item in both item data is the same, 6. The data search apparatus according to claim 1, wherein an evaluation value is calculated for the retrieved item data by applying the duplicate coefficient value to the role. The search execution unit
7. The data search device according to claim 6, wherein if the overlap coefficient value is less than a predetermined threshold, an evaluation value is calculated for the searched item data without using the overlap coefficient value. The search condition input unit
Enter search conditions that specify data elements other than items as search data elements,
The search execution unit
Search for item data including the search data element, and if the searched item data does not correspond to the weighted item data, determine the role of the search data element in the searched item data and correspond to the role of the search data element If the retrieved item data corresponds to the weighted item data, the role of the retrieved data element in the retrieved item data is determined, and the retrieval is performed according to the weighting rule. The data search apparatus according to claim 1, wherein an evaluation value is calculated by weighting the weight value to a coefficient value corresponding to a role of a data element. The data search device includes:
The table data including a plurality of columns, wherein any one of the plurality of roles for data processing is assigned to any column of the plurality of columns is searched. The data search device according to any one of 1 to 8. The search condition input unit
Enter two or more search target columns as search target columns, and enter a search condition that specifies the role as the primary key in one of the search target columns.
The priority search target item selection unit
A search target item whose role as a primary key is specified in the search condition is selected as a priority search target item, and a predetermined weight value is used as a coefficient value for weight target item data including the priority search target item as a primary key. The data search apparatus according to claim 9, wherein a weighting rule for calculating an evaluation value by weighting is generated. Computer
A data search method including a plurality of items, and performing a search for item data in which any of the plurality of items is assigned one of a plurality of roles for data processing,
A coefficient value definition information storage step in which the computer stores coefficient value definition information in which coefficient values for each of the plurality of roles are defined;
A search condition input step in which the computer inputs a search condition in which two or more items to be searched are specified as search target items and a role is specified for each search target item;
The computer selects, as a priority search target item, a search target item for which a specific role is specified in the search condition, and a predetermined weight value for weight target item data including the priority search target item as the specific role A priority search target item selection step for generating a weighting rule for calculating an evaluation value by weighting
The computer searches the item data including the search target item for each search target item, and when the searched item data does not correspond to the weighted target item data, determines the role of the search target item in the searched item data. The evaluation value is calculated using the coefficient value corresponding to the role of the search target item, and when the searched item data corresponds to the weighted target item data, the role of the search target item in the searched item data is determined. In accordance with the weight rule, a search execution step of calculating an evaluation value by weighting the weight value to a coefficient value corresponding to the role of the search target item;
The computer ranks the item data retrieved by the search execution step based on the evaluation value calculated by the search execution step, and obtains a search result indicating the result of ranking the item data as the search condition. And a search result output step of outputting to an input source. A computer that includes a plurality of items, and searches for item data in which any one of the plurality of roles for data processing is assigned to any of the plurality of items.
A coefficient value definition information storage step for storing coefficient value definition information in which coefficient values for each of the plurality of roles are defined;
A search condition input step for inputting a search condition in which two or more items to be searched are specified as search target items and a role is specified for each search target item;
A search target item in which a specific role is specified in the search condition is selected as a priority search target item, and a predetermined weight is used as a coefficient value for weight target item data including the priority search target item as the specific role. A priority search target item selection step for generating a weighting rule for weighting and calculating an evaluation value;
For each search target item, search the item data including the search target item, and if the searched item data does not correspond to the weighted target item data, determine the role of the search target item in the searched item data, and An evaluation value is calculated using a coefficient value corresponding to the role of the item, and when the searched item data corresponds to the weighted item data, the role of the searched item in the searched item data is determined, and the weighting rule A search execution step of calculating an evaluation value by weighting the weight value to a coefficient value corresponding to the role of the search target item,
Based on the evaluation value calculated by the search execution step, the item data searched by the search execution step is ranked, and the search result indicating the result of ranking the item data is output to the input source of the search condition A search result output step is executed.

Images