WO2015052812A1 - Data-linking support device and data-linking support method - Google Patents

Abstract

In order to efficiently, accurately determine, in the context of data linking, the table column from which to extract data to store in a column of a link-destination table, a data-linking support device is provided. From among tables other than the link-destination table, said data-linking support device extracts a combination of two or more tables, connected by a reference relationship or reference relationships, in which columns corresponding to two or more columns that serve as primary keys for the link-destination table are spread across the aforementioned two or more tables as primary keys therefor. On the basis of a result obtained from schema matching performed on the link-destination table and a table that joins the tables in the extracted combination of tables, the data-linking support device determines whether or not to recommend using the extracted combination in data linking for the link-destination table.

Description

Data linkage support apparatus and data linkage support method

The present invention relates to a data linkage support apparatus and a data linkage support method.

In conjunction with inter-system or system integration, data inter-system database cooperation or data inter-table tables within the same database is required. In data linkage, it is necessary to define a data linkage relationship as to which table (that is, the linkage source table) the data stored in the column of the linkage destination table (ie, the linkage destination table) should be extracted. is there.

In order to define the data linkage relationship, it is necessary to detect the column correspondence between tables by some means. Furthermore, it is necessary to create a table layout that is a combination of columns of the cooperation destination table from the cooperation source table by table operation. If such a data structure conversion cannot be performed, data cannot be stored in the cooperation destination table. If there is no linkage source table whose layout matches that of the linkage destination table and data is collected from two or more linkage source tables and the linkage destination table is created, the correspondence relationship is simply determined based on the similarity of the columns between the tables. Is not enough. In fact, it is necessary to be able to perform a join (for example, JOIN) operation on these two or more linkage source tables. Each linked source table needs to have a common column (that is, a join key) for the join operation.

The definition of the data linkage relationship is generally created by referring to the table definition information described in the table definition document of each system database. The work needed to be labor intensive by highly skilled engineers who understood the database technology and work content.

As a technique for automatically extracting a data linkage relationship, there is a technique that uses a record (that is, actual data) in a database table (see, for example, Patent Document 1).

In Patent Document 1, all of the cooperation source table and the cooperation destination table or sampled record data is captured. Analyzing the contents of the data and guessing the correspondence, create a record of the linkage destination table from the record of the linkage source table. However, the use of table definition information is limited.

A so-called schema matching technique exists as a technique for automatically extracting the correspondence between similar columns (for example, see Non-Patent Document 1).

In the schema matching technology, for example, table definition information related to the structure of two schemas or two schemas, each of which is a collection of tables, is received as an input. The column similarity is determined from the name and data type of the table column, the relationship between the tables, and the like. Thereby, the correspondence between columns of different tables is extracted.

Schema matching technology mainly uses table definition information. Records (that is, actual data) may be used, but use is limited. In general, column format information such as whether the date data is “YYMMDD” or “YYYY / MM / DD” is extracted from the record and used as metadata. Here, “Y” is a one-digit value of the year, “M” is a one-digit value of the month, and “D” is a one-digit value of the day. For example, April 1, 2013 is expressed as “2013/04/01” in the format of “YYYY / MM / DD”.

In the schema matching technology, the column name has been most often used as a criterion for similarity. Since column naming rules often differ between systems, it has been considered that schema matching technology is not suitable for processing to automatically extract data linkage relationships between systems. However, the maintenance of data item dictionaries to absorb differences in naming rules makes it possible to extract similar columns between systems with different naming rules. The maintenance of data item dictionaries was previously done manually, but in recent years, automatic dictionary generation technology has been developed. For this reason, schema matching technology can be used even in fields such as automatic extraction of data linkage relationships.

US Patent No. 8082243

In Patent Document 1, the data linkage relationship is automatically extracted using the records of the linkage source table and the linkage destination table. It is necessary to use all records in order to increase the accuracy of extracting data linkages. In that case, it is necessary to process a large number of records. When such a process is executed on a large-scale system, there is a high possibility that the processing time does not fall within a practical time. Inevitably, the system to be processed must be narrowed down. As a result, the automatically extracted data linkage relationship becomes a local solution, and when it is reviewed from an overall viewpoint, unnecessary data linkage is discovered.

Sampling technology can be used to reduce the number of records used. However, it is difficult to perform sampling while considering the reference relationship between tables. As a result of sampling, a table that originally has a reference relationship may appear to have no reference relationship.

As described above, the technique that requires the use of records as in Patent Document 1 has a problem that the range of systems that can be applied is limited by the amount of records.

Also, the technique such as Patent Document 1 has a problem that it is essential to prepare a record in advance. For example, if the linkage destination table is a newly created table, there is no record that can be used, and therefore, the data linkage relationship cannot be automatically extracted.

In the schema matching technique as in Non-Patent Document 1, the record preparation is not essential. Correspondence between similar columns can be extracted only from metadata such as table definition information. Therefore, there is an advantage that the amount of data to be handled can be reduced compared with the technique using records. The entire large-scale system can be processed.

However, the schema matching technique is a technique for finding similar columns. Therefore, there is a problem that not only the data linkage relationship but also the referential integrity constraint between tables and the semantic reference relationship are extracted without distinction. In particular, when there are many synonymous columns in the database, many correspondences that are not data linkage relationships are detected even if the correspondences are correct from the viewpoint of schema matching technology.

Also, the schema matching technique has a problem that it is not considered that the layout of the cooperation destination table needs to be created by the table operation from the cooperation source table in order to perform data cooperation.

The object of the present invention is, for example, to efficiently and accurately determine from which table column the data stored in the column of the table to be linked is extracted in data linkage.

A data linkage support apparatus according to an aspect of the present invention includes:
A device that supports data linkage of a database having a plurality of tables,
Primary key information indicating whether or not each of the plurality of columns included in each of the plurality of tables is a primary key, and attributes of data stored in each of the plurality of columns included in each of the plurality of tables An information storage unit that stores attribute information indicating the reference relationship information indicating the reference relationship between the plurality of tables;
A table selection unit that selects, from the plurality of tables, one table including two or more columns serving as primary keys and serving as a linkage destination of data linkage;
A column specifying unit for specifying the two or more columns from a plurality of columns included in the table selected by the table selection unit based on the primary key information stored in the information storage unit;
Based on the primary key information, the attribute information, and the reference relationship information stored in the information storage unit, two of the plurality of tables connected by a reference relationship from a table other than the table selected by the table selection unit A table extraction unit that is a combination of the above-described tables and extracts a combination in which a column corresponding to the column specified by the column specifying unit is divided into the two or more tables and exists as a primary key of each table;
A table combination unit that combines tables included in the combination extracted by the table extraction unit to create one table;
Schema matching is performed between the table selected by the table selection unit and the table created by the table combination unit, and the combination extracted by the table extraction unit is selected by the table selection unit based on the result of schema matching. And a recommendation determining unit that determines whether or not to recommend the use of the table for data linkage.

According to one aspect of the present invention, the data linkage support apparatus efficiently and accurately determines from which table column the data stored in the column of the table that is the linkage destination is extracted in the data linkage. Judgment can be made.

FIG. 2 is a block diagram showing a configuration of a data cooperation support apparatus according to the first embodiment. 4 is a table showing an example of input information according to the first embodiment. The table | surface which shows the example of the information with which the input information which concerns on Embodiment 1, and reference relationship information were synthesize | combined. 5 is a flowchart showing an example of the operation of the data linkage support apparatus according to the first embodiment. The table | surface which shows the example of the information by which the cooperation origin information which concerns on Embodiment 1, and reference relationship information were synthesize | combined, and temporary virtual table information. The table | surface which shows another example of the information by which the cooperation origin information which concerns on Embodiment 1, and reference relationship information were synthesize | combined. The table | surface which shows another example of the information by which the cooperation origin information which concerns on Embodiment 1, and the reference relationship information were synthesize | combined, and temporary virtual table information. 5 is a flowchart showing an example of the operation of the data linkage support apparatus according to the first embodiment. FIG. 3 is a conceptual diagram showing schema matching between a virtual table and a target table according to the first embodiment. 5 is a flowchart showing an example of the operation of the data linkage support apparatus according to the first embodiment. The conceptual diagram which shows the schema matching with the cooperation origin table and target table which concern on Embodiment 1. FIG. The conceptual diagram which shows the schema matching with the cooperation origin table and virtual table which concern on Embodiment 1. FIG. FIG. 3 is a diagram illustrating an example of a hardware configuration of a data cooperation support apparatus according to the first embodiment.

Hereinafter, embodiments of the present invention will be described with reference to the drawings.

Embodiment 1 FIG.
FIG. 1 is a block diagram showing a configuration of a data cooperation support apparatus 100 according to the present embodiment.

In FIG. 1, a data linkage support apparatus 100 is an apparatus that supports data linkage of a database having a plurality of tables. Data linkage supported by the data linkage support apparatus 100 may be data linkage between different instances of the database, data linkage between different schemas of the same instance, or between different tables of the same schema. Data linkage may be used.

The data cooperation support apparatus 100 includes an information storage unit 101, a table selection unit 102, a column specification unit 103, a table extraction unit 104, a table combination unit 105, and a recommendation determination unit 106.

Although not shown in FIG. 1, the data cooperation support device 100 includes hardware such as a processing device, a storage device, an input device, and an output device. The hardware is used by each unit of the data cooperation support apparatus 100. For example, the processing device is used to perform calculation, processing, reading, writing, or the like of data or information in each unit of the data cooperation support device 100. The storage device is used to store the data or information. The input device is used to input the data or information. The output device is used to output the data or information.

The information storage unit 101 stores cooperation source information 111, cooperation destination information 112, reference relationship information 113, and input information 114 using a storage device.

The collaboration source information 111 is information that defines a plurality of tables (that is, collaboration source tables) of a database (that is, the collaboration source database) that is a collaboration source of data collaboration. The collaboration source information 111 includes primary key information and attribute information of the collaboration source database. The primary key information of the cooperation source database is information indicating whether each of a plurality of columns included in each of the plurality of cooperation source tables is a primary key. The attribute information of the cooperation source database is information indicating attributes (for example, column names, data types) of data stored in the plurality of columns included in the plurality of cooperation source tables.

The cooperation destination information 112 is information that defines a plurality of tables (that is, cooperation destination tables) of a database (that is, a cooperation destination database) that is a cooperation destination of data cooperation within a range in which the above-described data cooperation relationship is already given. It is. When the data stored in each column of one linkage destination table has already been determined from which column (that is, the linkage source table, another linkage destination table, or a combination of these tables) should be extracted The linkage destination table is a linkage destination table to which a data linkage relationship has already been given. The cooperation destination information 112 includes the primary key information and attribute information of the cooperation destination table to which the data cooperation relationship has already been given among the primary key information and attribute information of the cooperation destination database. The primary key information of the cooperation destination database is information indicating whether each of a plurality of columns included in each of the plurality of cooperation destination tables is a primary key. The attribute information of the cooperation destination database is information indicating data attributes (for example, column names and data types) stored in each of a plurality of columns included in each of the plurality of cooperation destination tables.

The reference relationship information 113 is information indicating a reference relationship between tables. In this embodiment, not only the referential integrity constraint between tables but also a semantic reference relationship is regarded as a “reference relationship”. If a column included in one of the two tables corresponds to a column included in the other table and the column of the one table is set as a foreign key, there is a referential integrity constraint. Become. A column included in one of the two tables corresponds to a column included in the other table, but there is a semantic reference relationship when the column of the one table is not set as a foreign key It will be. The reference relationship information 113 can be created by any method.

The input information 114 is created manually by a user of the client device 200 (for example, a personal computer) or automatically created by a client program executed on the client device 200. The input information 114 is input from the client device 200 via the input device. The input information 114 is information that defines a cooperation destination table (that is, a target table) that has not yet been given a data cooperation relationship. The input information 114 includes primary key information and attribute information of the target table.

The table selection unit 102 reads the input information 114 stored in the information storage unit 101. The table selection unit 102 selects a target table defined by the read input information 114. In the present embodiment, it is assumed that two or more columns among the plurality of columns included in the target table are primary keys. Therefore, the table selection unit 102 selects one table as a cooperation destination of data cooperation including two or more columns serving as primary keys from a plurality of tables in the cooperation source database and the cooperation destination database. .

The column specifying unit 103 is a primary key from a plurality of columns included in the target table selected by the table selection unit 102 based on the primary key information included in the input information 114 read by the table selection unit 102. Identify a column (ie, two or more columns as described above).

The table extraction unit 104 reads the cooperation source information 111, the cooperation destination information 112, and the reference relationship information 113 stored in the information storage unit 101. The table extraction unit 104, based on the primary key information and attribute information included in the read linkage source information 111 and the linkage destination information 112, and the read reference relationship information 113, a plurality of tables of the linkage source database and the linkage destination database. Among them, combinations of two or more tables that satisfy the following conditions (1) and (2) are extracted from tables other than the target table selected by the table selection unit 102.
(1) The two or more tables are connected by a reference relationship. For example, when the first table of the three tables refers to the second table and the second table refers to the third table, the three tables are connected by a reference relationship. Will be.
(2) The column corresponding to the column specified by the column specifying unit 103 is divided into the two or more tables and exists as a primary key of each table. For example, two columns are specified by the column specifying unit 103, a column similar to one of the two columns exists as a primary key in one of the two tables, and the two columns exist in the other of the two tables. When a column similar to the other of the two exists as a primary key, the columns corresponding to the two columns are divided into the two tables and exist as the primary key of each table.

The table combining unit 105 combines the tables included in the combination extracted by the table extracting unit 104 to create one table (that is, a virtual table).

The recommendation determining unit 106 performs schema matching between the target table selected by the table selecting unit 102 and the virtual table created by the table combining unit 105. Based on the result of the schema matching, the recommendation determination unit 106 determines whether it is recommended to use the combination extracted by the table extraction unit 104 for data linkage of the target table selected by the table selection unit 102. When “to be recommended”, the recommendation determination unit 106 outputs recommendation information 161 that recommends that the combination extracted by the table extraction unit 104 be used for data linkage of the target table selected by the table selection unit 102. The data is output to the client device 200 via the device. If “not recommended”, the recommendation determination unit 106 selects a table indicating that there is no table that can be used for data linkage of the target table selected by the table selection unit 102 or a combination extracted by the table extraction unit 104. Information indicating that use of the target table selected by the unit 102 for data linkage is not recommended is output to the client device 200 via the output device.

In the present embodiment, when two or more combinations are extracted by the table extracting unit 104, the table combining unit 105 combines the tables included in the combination for each combination extracted by the table extracting unit 104 and virtually Create a table. The recommendation determination unit 106 performs schema matching between the target table selected by the table selection unit 102 and the virtual table created by the table combination unit 105 for each combination extracted by the table extraction unit 104. Based on the result of the schema matching, the recommendation determination unit 106 recommends that one combination among the combinations extracted by the table extraction unit 104 be used for data linkage of the target table selected by the table selection unit 102. The recommended information 161 is output.

In the present embodiment, the recommendation determination unit 106 uses the table selection unit 102 to recommend that the combination extracted by the table extraction unit 104 be used for data linkage of the target table selected by the table selection unit 102. From the plurality of columns included in the selected target table, a column for which no corresponding column exists in the virtual table created by the table combining unit 105 is extracted. When the recommendation determining unit 106 extracts such a column, the column corresponding to the column extracted from the table other than the target table selected by the table selecting unit 102 among the plurality of tables of the cooperation source database and the cooperation destination database. And a table that can be joined to the virtual table created by the table joining unit 105 is detected. Specifically, the recommendation determining unit 106 reads the cooperation source information 111 and the cooperation destination information 112 stored in the information storage unit 101. The recommendation determination unit 106 is selected by the table selection unit 102 from among the plurality of tables of the cooperation source database and the cooperation destination database based on the primary key information and the attribute information included in the read cooperation source information 111 and the cooperation destination information 112. A table including a column corresponding to the extracted column is detected from tables other than the target table. When the recommendation determining unit 106 detects such a table, the recommendation determining unit 106 determines whether the detected table can be combined with the virtual table created by the table combining unit 105. In the case of “combinable”, the recommendation determining unit 106 outputs the recommended information 161 after adding the detected table to the combination extracted by the table extracting unit 104. If “cannot be combined”, the recommendation determining unit 106 ignores the detected table and outputs the recommendation information 161. That is, when the recommendation determining unit 106 detects a table that includes a column corresponding to the extracted column and can be combined with the virtual table created by the table combining unit 105, the recommendation determining unit 106 extracts the detected table and the table extracting unit 104. Recommendation information 161 that recommends using the combination with the table for data linkage of the target table selected by the table selection unit 102 is output.

In the present embodiment, a conventional schema matching technique can be implemented in the part of the recommendation determination unit 106 that performs schema matching.

As described above, in the present embodiment, two or more columns among the plurality of columns included in the target table defined by the input information 114 are primary keys. When only one column is a primary key among a plurality of columns included in the target table, the data linkage relationship can be extracted using an arbitrary method for the target table.

Hereinafter, an example of the operation of the data cooperation support apparatus 100 (that is, the data cooperation support method according to the present embodiment) will be described.

FIG. 2 is a table showing an example of the input information 114.

2, input information 114 includes column name, data type, and primary / foreign key information (that is, attribute information and primary key information). As described above, the input information 114 is information that defines the target table. For example, the input information 114 indicates that the “company code”, “order detail ID”, and “order ID” columns of the target table are primary keys.

When the input information 114 shown in FIG. 2 is input by the client device 200 and stored in the information storage unit 101, the table selection unit 102 reads the input information 114. The table selection unit 102 selects a target table defined by the read input information 114.

FIG. 3 is a table showing an example of information obtained by combining the input information 114 and the reference relation information 113.

In FIG. 3, the main / foreign key information included in the input information 114 is also reference relationship information 113 of the target table. As described above, the reference relationship information 113 is information indicating a reference relationship between tables. For example, the reference relationship information 113 indicates that the “company code”, “order detail ID”, and “order ID” columns, which are primary keys of the target table, are also external keys. The reference relationship information 113 indicates that the “store ID”, “product code”, and “brand ID” columns are also external keys.

The items in black in FIG. 3 are columns that have corresponding columns in the foreign key reference table. For example, a table corresponding to the “store name”, “store address”, “store manager_employee ID”, and “store manager name” columns exists in the reference table of the “store ID” column.

The reference relationship information 113 can be created by an arbitrary method as described above. For example, a function for creating the reference relationship information 113 can be implemented in the column specifying unit 103. In that case, whether or not the column specifying unit 103 can refer to columns of other tables for each of a plurality of columns included in the target table selected by the table selection unit 102 based on the input information 114 shown in FIG. to decide. For example, the column specifying unit 103 performs schema matching between the target table and other tables. As a result of the schema matching, the column corresponding to the “store ID” column is included as a primary key, and the columns corresponding to the “store name”, “store address”, “store manager_employee ID”, and “store manager name” columns are not included. When the table including the primary key is extracted, the column specifying unit 103 refers to the reference relationship information 113 for the “store ID”, “store name”, “store address”, “store manager_employee ID”, and “store manager name” columns. Create or update In the reference relationship information 113, the “store ID” column is set as the external key. In the reference relationship information 113, the “store name”, “store address”, “store manager_employee ID”, and “store manager name” columns are linked to the “store ID” column.

FIG. 4 is a flowchart showing an example of the operation of the data cooperation support apparatus 100. FIG. 5 is a table showing an example of information obtained by combining the cooperation source information 111 and the reference relationship information 113 and provisional virtual table information 301 described later.

In step S11, the column specifying unit 103 refers to main / foreign key information (that is, main key information of the target table) included in the input information 114. The column specifying unit 103 specifies the column that is the primary key of the target table selected by the table selecting unit 102 based on the referenced information. The column specifying unit 103 generates key item information 302 indicating a list of specified columns.

For example, the column specifying unit 103 generates key item information 302 as shown in FIG. 5 from the main / foreign key information included in the input information 114 shown in FIG. The key item information 302 indicates that the “company code”, “order detail ID”, and “order ID” columns in the target table are primary keys.

In step S12, the table extraction unit 104 acquires the cooperation source information 111, the cooperation destination information 112, and the reference relationship information 113 from the information storage unit 101. Further, the table extraction unit 104 acquires the key item information 302 from the column specifying unit 103.

For example, the table extraction unit 104 acquires information obtained by combining the cooperation source information 111 and the reference relationship information 113 as illustrated in FIG. The collaboration source information 111 is information that defines each collaboration source table in the same format as the input information 114 shown in FIG. The reference relationship information 113 is information indicating a reference relationship between tables in the same format as the reference relationship information 113 shown in FIG. The order table linkage source information 111 indicates that the “order ID” column of the order table is the primary key. The order table reference relation information 113 indicates that the “company code” column of the order table is a foreign key. The reference relationship information 113 of the order table includes a reference relationship record 303 indicating that the “company code” column of the order table refers to the “company code” column of the company table. The company table linkage source information 111 indicates that the “company code” column of the company table is the primary key. The company table reference relationship information 113 indicates that there is no foreign key in the company table. The cooperation source information 111 of the order detail table indicates that the “order detail ID” and “order ID” columns of the order detail table are the primary keys. The reference relationship information 113 in the order detail table indicates that the “order ID” and “store ID” columns in the order detail table are external keys. The reference relationship information 113 of the order detail table includes a reference relationship record 304 indicating that the “order ID” column of the order detail table refers to the “order ID” column of the order table.

In step S13, the table extraction unit 104 refers to the cooperation source information 111, the cooperation destination information 112, and the key item information 302 acquired in step S12, and all or part of the columns that are the primary keys of the target table. Select the table that contains the corresponding column. The table extraction unit 104 regards the selected table as the current virtual table.

For example, the table extraction unit 104 determines that the “order ID” column indicated by the key item information 302 is similar to the “order ID” column indicated by the link source information 111 of the order table, and the order table Select. The table extraction unit 104 regards the order table as a current virtual table. In this example, the order table is one of cooperation source tables, but may be one of cooperation destination tables different from the target table.

In step S14, the table extraction unit 104 refers to the cooperation source information 111, the cooperation destination information 112, and the key item information 302 acquired in step S12, and among the columns that are the primary keys of the target table, Determine if there are any columns not included in the virtual table. If there is a column not included in the current virtual table, the table extraction unit 104 proceeds to step S15. If all the columns that are the primary keys of the target table are included in the current virtual table, the table extraction unit 104 proceeds to step S17.

For example, the table extraction unit 104 determines that the “company code” and “order detail ID” columns indicated by the key item information 302 are not included in the current virtual table.

In step S15, the table extraction unit 104 refers to the cooperation source information 111, the cooperation destination information 112, the reference relationship information 113, and the key item information 302 acquired in step S12, and directly or indirectly with the table selected in step S13. Therefore, it is determined whether there is a table including a column corresponding to a column that is connected by a reference relationship (ie, via another selected table) and is not included in the current virtual table. If there is such a table, the table extraction unit 104 selects the table and proceeds to step S16. If there is no such table, the table extraction unit 104 cancels the current virtual table and proceeds to step S18.

For example, the table extraction unit 104 refers to the cooperation source information 111 of the company table indicated as the reference destination of the order table in the reference relationship record 303. The table extraction unit 104 determines that the “company code” column indicated by the key item information 302 is similar to the “company code” column indicated by the cooperation source information 111 of the company table, and selects the company table. To do. In this example, the company table is one of cooperation source tables, but may be one of cooperation destination tables different from the target table.

In step S16, the table combining unit 105 combines the current virtual table and the table selected in step S15 to create one table. The table combining unit 105 regards the created table as the current virtual table (that is, updates the current virtual table), and returns to step S14.

For example, the table combining unit 105 combines the current virtual table, which is an order table, and the company table to create a new current virtual table. In subsequent step S <b> 14, the table extraction unit 104 determines that the “order detail ID” column indicated by the key item information 302 is not included in the current virtual table. In step S <b> 15, the table extraction unit 104 refers to the cooperation source information 111 of the order detail table shown as the reference source of the order table in the reference relationship record 304. The table extraction unit 104 determines that the “order detail ID” column indicated by the key item information 302 is similar to the “order detail ID” column indicated by the link source information 111 of the order detail table, and the order Select a detail table. In step S <b> 16, the table combination unit 105 combines the current virtual table obtained by combining the order table and the company table with the order detail table to create a new current virtual table. Further, in the subsequent step S14, the table extraction unit 104 determines that all the columns indicated by the key item information 302 are included in the current virtual table, and proceeds to step S17.

In step S17, the table combining unit 105 creates provisional virtual table information 301. The temporary virtual table information 301 is information that defines the current virtual table. The provisional virtual table information 301 includes primary key information, attribute information, and reference relationship information of the virtual table. The primary key information of the virtual table is information indicating whether each of a plurality of columns included in the virtual table is a primary key. The primary key information of the virtual table must match the primary key information of the target table. The attribute information of the virtual table is information indicating data attributes (for example, column names and data types) stored in each of a plurality of columns included in the virtual table. The virtual table reference relationship information is information indicating a reference relationship between the virtual table and another table.

For example, the table combining unit 105 creates provisional virtual table information 301 as shown in FIG. The provisional virtual table information 301 is information that defines a virtual table in the same format as the information obtained by combining the cooperation source information 111 and the reference relationship information 113 illustrated in FIG. The provisional virtual table information 301 indicates that the “company code”, “order detail ID”, and “order ID” columns of the virtual table are primary keys. The provisional virtual table information 301 indicates that the “order ID” column of the virtual table is a foreign key.

In step S18, the table extraction unit 104 determines whether or not the processing in steps S14 to S17 has been completed for all the table combinations including the table selected in step S13. If the process has been completed, the table extraction unit 104 proceeds to step S19. If the process has not been completed, the table extraction unit 104 regards the table selected in step S13 as a new virtual table at the current time, and returns to step S14. The table joining unit 105 creates new provisional virtual table information 301 each time the process of step S17 is executed (that is, for each virtual table).

In step S19, the table extraction unit 104 determines whether there is another table that can be selected in step S13. If there is such a table, the table extraction unit 104 returns to step S13. If there is no such table, the table extraction unit 104 ends the process.

Below, the concept of scope will be explained.

In the example of FIG. 5, it is assumed that the tables to be joined belong to the same schema of the same database (that is, the same instance). However, data linkage is usually performed between a plurality of systems. Therefore, it is necessary to consider a reference relationship between different schemas or a reference relationship between different databases.

FIG. 6 is a table showing another example of information obtained by combining the cooperation source information 111 and the reference relationship information 113.

In the example of FIG. 6, the same table as the example of FIG. 5 is divided into a plurality of schemas or a plurality of databases. In the schema A of the database DB-1, there are an order table and an order detail table similar to the example of FIG. A company table similar to the example of FIG. 5 exists in the schema B of the same database. Another company table exists in schema 1 of another database called DB-2.

The order table reference relationship information 113 includes a reference relationship record 303 indicating that the “company code” column of the order table refers to the “company code” column of the company table belonging to the schema B of DB-1. . The reference relationship information 113 of the order table further includes a reference relationship record 305 indicating that the “company code” column of the order table refers to the “company code” column of the company table belonging to the schema 1 of DB-2. ing.

The reference relationship records 303, 304, and 305 include information related to the scope. In step S15, the table extraction unit 104 extracts a table based on this information. That is, the table extraction unit 104 determines whether a table having a reference relationship with the selected table exists in the same schema, the same database, or a different database as the selected table. Extract while distinguishing.

FIG. 7 is a table showing another example of information obtained by combining the cooperation source information 111 and the reference relationship information 113 and the provisional virtual table information 301.

In step S15, the table extracting unit 104 can refer to the cooperation source information 111 of the company table belonging to another database indicated as the reference destination of the order table in the reference relationship record 305. In the table extraction unit 104, the “company code” column indicated by the key item information 302 is similar to the “company code” column indicated by the linkage source information 111 of the company table belonging to the schema 1 of DB-2. Then, it is determined and this company table is selected.

In step S17, the table combining unit 105 can create temporary virtual table information 301 as shown in FIG. 7 separately from the temporary virtual table information 301 shown in FIG. The virtual table in the example of FIG. 7 has a “brand ID” column added to the virtual table in the example of FIG. The input information 114 shown in FIG. 3 indicates that the “brand ID” column is included in the target table. Therefore, the virtual table in the example of FIG. 7 is superior to the virtual table in the example of FIG. 5 in that the number of columns corresponding to the target table is large.

In step S15, a scope (for example, in the same database, in the same schema) may be designated. In that case, the table extraction unit 104 extracts a table within the designated scope.

When a plurality of virtual tables having similar target table and primary key columns are created by the operation of FIG. 4, a virtual table having as many non-primary key columns as possible as the target table among these virtual tables. The table is suitable for data linkage. Schema matching techniques can be used to detect such virtual tables.

FIG. 8 is a flowchart showing an example of the operation of the data cooperation support apparatus 100 after the operation of FIG. FIG. 9 is a conceptual diagram showing schema matching between a virtual table and a target table.

In step S21, the recommendation determining unit 106 acquires the input information 114 and the provisional virtual table information 301 created in step S17 from the information storage unit 101.

For example, as illustrated in FIG. 9, the recommendation determining unit 106 includes the information illustrated in FIG. 3 (that is, information obtained by combining the input information 114 and the reference relationship information 113) and the provisional virtual table information illustrated in FIG. 7. 301 is acquired.

In step S22, the recommendation determining unit 106 performs schema matching between the target table defined by the input information 114 acquired in step S21 and the virtual table defined by the provisional virtual table information 301 acquired in step S21. The primary key matches as much as possible between the target table and the virtual table. Therefore, the similarity of the non-primary key column is confirmed. Existing schema matching techniques can be used to determine column similarity.

For example, the recommendation determining unit 106 performs schema matching between the target table defined by the information illustrated in FIG. 3 and the virtual table defined by the provisional virtual table information 301 illustrated in FIG. As described above, items in black in FIG. 3 are columns that can be acquired by referring to other tables. Therefore, the result of schema matching for these columns is ignored. Alternatively, schema matching is omitted for these columns.

In step S23, the recommendation determining unit 106 totals the results of the schema matching performed in step S22 (that is, the matching score), and determines which column of the virtual table is similar to the non-primary key column of the target table. To do.

For example, based on the result of schema matching, the recommendation determining unit 106 determines that the “customer code”, “store ID”, and “brand ID” columns of the virtual table are similar to the columns of the same name in the target table.

When a plurality of virtual tables are created by the operation of FIG. 4, the processes in steps S21 to S23 are executed for each virtual table.

FIG. 10 is a flowchart showing an example of the operation of the data cooperation support apparatus 100 after the operation of FIG. FIG. 11 is a conceptual diagram showing schema matching between the cooperation source table and the target table. FIG. 12 is a conceptual diagram illustrating schema matching between a cooperation source table and a virtual table.

In step S31, the recommendation determination unit 106 acquires the cooperation source information 111, the cooperation destination information 112, and the reference relationship information 113 from the information storage unit 101.

For example, the recommendation determining unit 106 acquires information obtained by combining the cooperation source information 111 and the reference relationship information 113 as illustrated in FIG. The order table linkage source information 111 indicates that the “employee ID” column of the employee table is the primary key. The employee table reference relationship information 113 indicates that the employee table has no external key. The discount table linkage source information 111 indicates that the “order details ID” and “order ID” columns of the discount table are primary keys. The discount table reference relationship information 113 indicates that the “order ID”, “store ID”, and “product code” columns of the discount table are external keys.

In step S32, the recommendation determining unit 106 converts the plurality of columns included in the target table defined by the input information 114 acquired in step S21 into the virtual table defined by the provisional virtual table information 301 acquired in step S21. Extract columns for which no corresponding column exists. That is, the recommendation determining unit 106 extracts columns other than the columns determined to be similar to the virtual table columns in step S23 from the non-primary key columns of the target table.

The items in black in FIG. 11 are columns in which columns corresponding to either the primary key column, the foreign key reference destination table, or the virtual table exist.

In step S33, the recommendation determining unit 106 performs schema matching between the column extracted in step S32 and the cooperation source table and the cooperation destination table defined by the information acquired in step S31.

For example, the recommendation determining unit 106 performs schema matching between the target table defined by the information shown in FIG. 3 and the employee table and discount table defined by the information shown in FIG. As described above, items in black in FIG. 11 are primary key columns or columns that can be acquired by referring to other tables. Therefore, the result of schema matching for these columns is ignored. Alternatively, schema matching is omitted for these columns.

In step S34, the recommendation determining unit 106 aggregates the results of the schema matching performed in step S33 (that is, the matching score), and any column of any linkage source table or any linkage destination table is the target table. Determine if it is similar to the rest of the columns.

For example, the recommendation determination unit 106 determines that the “employee name” column of the employee table is similar to the “in charge of sales” column of the target table based on the result of schema matching. The recommendation determining unit 106 determines that the “discount” and “product code” columns in the discount table are similar to the columns having the same names in the target table.

In step S35, the recommendation determining unit 106 is defined by the cooperation source table or the cooperation destination table including the columns determined to be similar to the remaining columns of the target table in step S34, and the provisional virtual table information 301 acquired in step S21. Perform schema matching with virtual table.

For example, as shown in FIG. 12, the recommendation determining unit 106 includes an employee table and a discount table defined by the information shown in FIG. 11, and a virtual table defined by the provisional virtual table information 301 shown in FIG. Perform schema matching.

In step S36, the recommendation determining unit 106 totals the results of the schema matching performed in step S35 (that is, the matching score), and includes a cooperation source table including columns determined to be similar to the remaining columns of the target table in step S34. It is determined whether or not the cooperation destination table can be combined with the virtual table defined by the provisional virtual table information 301 acquired in step S21. If both tables can be combined, the recommendation determining unit 106 proceeds to step S37. If both tables cannot be combined, the recommendation determining unit 106 regards the virtual table defined by the temporary virtual table information 301 acquired in step S21 as the final virtual table, and proceeds to step S38. However, the recommendation determining unit 106 excludes columns that do not have corresponding columns in the target table from the final virtual table.

In step S37, the recommendation determining unit 106 is defined by the cooperation source table or the cooperation destination table including the columns determined to be similar to the remaining columns of the target table in step S34, and the provisional virtual table information 301 acquired in step S21. Join one virtual table to create one table. The recommendation determining unit 106 regards the created table as a final virtual table, and proceeds to step S38. However, the recommendation determining unit 106 excludes columns that do not have corresponding columns in the target table from the final virtual table.

For example, the recommendation determining unit 106 combines the virtual table defined by the provisional virtual table information 301 illustrated in FIG. 12 with the employee table and the discount table defined by the information illustrated in FIG. Create a virtual table.

In step S <b> 38, the recommendation determination unit 106 uses the output device to provide the recommended information 161 that recommends that the table combination that is the basis of the final virtual table is used for data linkage of the target table. Output to.

When a plurality of virtual tables are created by the operation of FIG. 4, the processes of steps S31 to S37 are executed for each virtual table. In step S38, the recommendation determining unit 106 performs schema matching between the target table and the final virtual table for each virtual table. The recommendation determination unit 106 selects the final virtual table having the highest schema matching score (or the highest n (n> 1) in the schema matching score). The recommendation determination unit 106 outputs the recommended information 161 that recommends using the combination of the tables that is the basis of the selected final virtual table to the target table data linkage to the client device 200 via the output device. To do.

カ ラ ム Some columns corresponding to some columns of the target table may not be included in the final virtual table. That is, the target table may not be realized by data linkage using only an existing table. In such a case, it can be considered that information for joining the virtual table and the linkage source table is manually created as a related table. In this case, it is necessary to investigate what kind of related table should be created. However, providing the recommended information 161 to the client device 200 can contribute to a reduction in the amount of investigation.

In this embodiment, record data is not essential for automatic recommendation of data linkage relations, and only metadata such as table definition information may be used. Therefore, it is possible to set a wide range of systems for searching for data linkage relationships. As a result, it is possible to find an overall optimal solution of the data linkage relationship considering the entire system. That is, according to the present embodiment, in data linkage, it is possible to efficiently and accurately determine from which table column the data stored in the column of the table to be linked is extracted.

In this embodiment, since the data linkage relationship can be searched with a wide scope of the entire system, an effect that detection omissions can be minimized can be obtained.

In automatic recommendation of data linkage relationship, if the search scope becomes narrow, there is a possibility that the table that should originally be the linkage source may be excluded. There is a possibility that a copy or deformation of a table that should originally be a linkage source is erroneously detected as a linkage source. If there is such a false detection, in the defined data linkage relationship, a dependency relationship with another data linkage process that creates a copy or transformation from the table that should originally be the linkage source occurs. According to the present embodiment, it is possible to avoid such a situation (that is, a partially optimal solution) that a data linkage relationship that is not necessary originally is defined.

In this embodiment, in order to meet the requirement that the record layout of the linkage destination table can be reproduced from the linkage source table group by a join operation, which is a unique requirement for the purpose of automatically recommending the data linkage relationship, Create and apply schema matching technology to the virtual table and linked table. For this reason, correspondences that cannot be used for data linkage are automatically omitted from the output. In the present embodiment, only correspondence relationships between columns that can be used for data linkage are extracted and provided from candidates for data linkage relationships that occur in large quantities with a normal schema matching technique. For this reason, when the automatic recommendation result is confirmed by a human, the amount of human work is greatly reduced.

Furthermore, in this embodiment, not only the scope is expanded, but also a plurality of scopes are explicitly divided in the database schema, in the database, and outside the database, and a virtual table is created for each. For this reason, it is possible to check the data linkage relationship candidates by scope. For example, it can be confirmed that data items that cannot be acquired within the database schema can be acquired by expanding the scope outside the database. Therefore, it is possible to define the data linkage relationship according to the situation while comparing the data linkage cost outside the database with the data linkage cost in the database schema.

FIG. 13 is a diagram illustrating an example of a hardware configuration of the data cooperation support apparatus 100 according to the present embodiment.

In FIG. 13, the data linkage support apparatus 100 is a computer, and includes an LCD 901 (Liquid / Crystal / Display), a keyboard 902 (K / B), a mouse 903, an FDD 904 (Flexible / Disk / Drive), and a CDD 905 (Compact / Disc / Drive). Drive) and a hardware device such as a printer 906 are provided. These hardware devices are connected by cables and signal lines. Instead of the LCD 901, a CRT (Cathode / Ray / Tube) or other display device may be used. Instead of the mouse 903, a touch panel, a touch pad, a trackball, a pen tablet, or other pointing devices may be used.

The data linkage support apparatus 100 includes a CPU 911 (Central Processing Unit) that executes a program. The CPU 911 is an example of a processing device. The CPU 911 includes a ROM 913 (Read / Only / Memory), a RAM 914 (Random / Access / Memory), a communication board 915, an LCD 901, a keyboard 902, a mouse 903, an FDD 904, a CDD 905, a printer 906, and an HDD 920 (Hard / Disk) via a bus 912. Connected with Drive) to control these hardware devices. Instead of the HDD 920, a flash memory, an optical disk device, a memory card reader / writer, or other recording medium may be used.

The RAM 914 is an example of a volatile memory. The ROM 913, the FDD 904, the CDD 905, and the HDD 920 are examples of nonvolatile memories. These are examples of the storage device. The communication board 915, the keyboard 902, the mouse 903, the FDD 904, and the CDD 905 are examples of input devices. The communication board 915, the LCD 901, and the printer 906 are examples of output devices.

The communication board 915 is connected to a LAN (Local / Area / Network) or the like. The communication board 915 is not limited to a LAN, but includes an IP-VPN (Internet / Protocol / Virtual / Private / Network), a wide area LAN, an ATM (Asynchronous / Transfer / Mode) network, a WAN (Wide / Area / Network), or the Internet. It does not matter if it is connected to. LAN, WAN, and the Internet are examples of networks.

The HDD 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, the operating system 921, and the window system 922. The program group 923 includes programs that execute the functions described as “unit” in the description of the embodiment of the present invention. The program is read and executed by the CPU 911. The file group 924 includes data and information described as “˜data”, “˜information”, “˜ID (identifier)”, “˜flag”, and “˜result” in the description of the embodiment of the present invention. Signal values, variable values, and parameters are included as "~ file", "~ database", and "~ table" items. “˜file”, “˜database”, and “˜table” are stored in a recording medium such as the RAM 914 and the HDD 920. Data, information, signal values, variable values, and parameters stored in a recording medium such as the RAM 914 and the HDD 920 are read out to the main memory and the cache memory by the CPU 911 via a read / write circuit, and extracted, searched, referenced, compared, and calculated. It is used for processing (operation) of the CPU 911 such as calculation, control, output, printing and display. During the processing of the CPU 911 such as extraction, search, reference, comparison, calculation, calculation, control, output, printing, and display, data, information, signal values, variable values, and parameters are temporarily stored in the main memory, cache memory, and buffer memory. Remembered.

In the description of the embodiment of the present invention, the arrow portions in the block diagrams and flowcharts mainly indicate input / output of data and signals. Data and signals are recorded in memory such as RAM 914, FDD904 flexible disk (FD), CDD905 compact disk (CD), HDD920 magnetic disk, optical disk, DVD (Digital Versatile Disc), or other recording media Is done. Data and signals are transmitted by a bus 912, a signal line, a cable, or other transmission media.

In the description of the embodiments of the present invention, what is described as “to part” may be “to circuit”, “to device”, and “to device”, and “to step” and “to process”. , “˜procedure”, and “˜processing”. That is, what is described as “˜unit” may be realized by firmware stored in the ROM 913. Alternatively, what is described as “˜unit” may be realized only by software or only by hardware such as an element, a device, a board, and wiring. Alternatively, what is described as “˜unit” may be realized by a combination of software and hardware, or a combination of software, hardware and firmware. Firmware and software are stored as programs in a recording medium such as a flexible disk, a compact disk, a magnetic disk, an optical disk, and a DVD. The program is read by the CPU 911 and executed by the CPU 911. In other words, the program causes the computer to function as “˜unit” described in the description of the embodiment of the present invention. Alternatively, the program causes a computer to execute the procedures and methods of “unit” described in the description of the embodiment of the present invention.

As mentioned above, although embodiment of this invention was described, this invention is not limited to this embodiment, A various change is possible as needed.

100 data linkage support device, 101 information storage unit, 102 table selection unit, 103 column specification unit, 104 table extraction unit, 105 table combination unit, 106 recommendation judgment unit, 111 linkage source information, 112 linkage destination information, 113 reference relation information , 114 input information, 161 recommended information, 200 client devices, 301 provisional virtual table information, 302 key item information, 303, 304, 305 reference relation record, 901 LCD, 902 keyboard, 903 mouse, 904 FDD, 905 CDD, 906 printer , 911 CPU, 912 bus, 913 ROM, 914 RAM, 915 communication board, 920 HDD, 921 operating system, 922 window system, 923 programs, 24 file group.

Claims (4)

1. In a data linkage support device that supports data linkage of a database having a plurality of tables,
   Primary key information indicating whether or not each of the plurality of columns included in each of the plurality of tables is a primary key, and attributes of data stored in each of the plurality of columns included in each of the plurality of tables An information storage unit that stores attribute information indicating the reference relationship information indicating the reference relationship between the plurality of tables;
   A table selection unit that selects, from the plurality of tables, one table including two or more columns serving as primary keys and serving as a linkage destination of data linkage;
   A column specifying unit for specifying the two or more columns from a plurality of columns included in the table selected by the table selection unit based on the primary key information stored in the information storage unit;
   Based on the primary key information, the attribute information, and the reference relationship information stored in the information storage unit, two of the plurality of tables connected by a reference relationship from a table other than the table selected by the table selection unit A table extraction unit that is a combination of the above-described tables and extracts a combination in which a column corresponding to the column specified by the column specifying unit is divided into the two or more tables and exists as a primary key of each table;
   A table combination unit that combines tables included in the combination extracted by the table extraction unit to create one table;
   Schema matching is performed between the table selected by the table selection unit and the table created by the table combination unit, and the combination extracted by the table extraction unit is selected by the table selection unit based on the result of schema matching. A data cooperation support apparatus comprising: a recommendation determination unit that determines whether or not it is recommended to use the table for data cooperation.
2. When two or more combinations are extracted by the table extraction unit,
   The table combining unit creates one table by combining the tables included in the combination for each combination extracted by the table extracting unit,
   The recommendation determining unit performs schema matching between the table selected by the table selecting unit and the table created by the table combining unit for each combination extracted by the table extracting unit,
   The recommendation determining unit recommends using one combination among the combinations extracted by the table extracting unit for data linkage of the table selected by the table selecting unit based on a result of schema matching. The data linkage support apparatus according to claim 1, wherein:
3. The recommendation determination unit is included in the table selected by the table selection unit when recommending that the combination extracted by the table extraction unit is used for data linkage of the table selected by the table selection unit. Columns that do not have a corresponding column in the table created by the table linking unit are extracted from a plurality of columns, and extracted from tables other than the table selected by the table selection unit among the plurality of tables And a table that can be joined to the table created by the table joining unit, and the detected table is added to the combination extracted by the table extracting unit. Data linkage support device.
4. In a data linkage support method for supporting data linkage of a database having a plurality of tables,
   Primary key information indicating whether or not each of the plurality of columns included in each of the plurality of tables is a primary key, and attributes of data stored in each of the plurality of columns included in each of the plurality of tables Using a computer including an information storage unit that stores attribute information indicating the reference relationship information indicating the reference relationship between the plurality of tables,
   From one of the plurality of tables, select one table that includes two or more columns that are primary keys and is a linkage destination of data linkage,
   Based on the primary key information stored in the information storage unit, identify the two or more columns from a plurality of columns included in the table selected by the selection,
   Based on the primary key information, the attribute information, and the reference relationship information stored in the information storage unit, two or more items connected by a reference relationship from tables other than the table selected by the selection among the plurality of tables. A combination of tables, wherein a column corresponding to the column identified by the identification is divided into the two or more tables and a combination existing as a primary key of each table is extracted;
   Combining tables included in the combination extracted by the extraction to create one table,
   Schema matching between the table selected by the selection and the table created by the join is performed, and the combination extracted by the extraction is used for data linkage of the table selected by the selection based on the result of the schema matching A data linkage support method characterized by determining whether or not to recommend.

Images