JP4902863B2 - Table sorter - Google Patents

Description

  The present invention relates to a table classification device that classifies a plurality of tables.

  There is an increasing need to integrate multiple systems for corporate mergers, branch consolidations, and company-wide business analysis. Also, when adding a system, a new system is often created by duplicating an existing database or data warehouse. With these integrations and additions, redundant and unnecessary data and processing have increased, causing problems such as deterioration in maintenance efficiency, inefficiency in business processing, quality deterioration due to inconsistencies, excessive capital investment, and so on. Furthermore, manual integration (analysis / design) work has become very difficult due to the complexity of the system and the black box. However, the necessity / unnecessity of data is determined by various technical, human, cost, and political factors in addition to the contents of the data. It becomes important.

  Conventionally, in order to perform data integration, the same column is found based on the data type match and the data match degree (for example, Patent Document 1).

  Also, when determining similarity using a plurality of column attributes, as shown in FIG. 5 of Japanese Patent Laid-Open No. 2005-63332 (Patent Document 2), each column (or field, category, document, information) Only a pair of corresponding columns is obtained using a distance in a multidimensional space with only an attribute common to (element) as an axis (for example, Patent Document 2).

Many of the prior arts are aimed at table joins and data synchronization, so the data contents are compared to find almost the same column. For this reason, the column attribute is mainly used only for type information for matching determination, and the column name is used for others.
JP 2004-86782 A, heterogeneous database integration support device JP 2005-63332 A, Information system associating device and associating method

  Conventional consistency determination methods for integration support are based on distances in a multidimensional space with multiple evaluation criteria, so comparisons between the same evaluation axes are hidden, and it is difficult to understand the cause of the results obtained. There was a problem to say. Furthermore, when adjusting the weight of each axis in order to change the evaluation criteria, there is a problem that it is difficult to predict and weight the result.

  Further, the conventional similarity calculation method has a problem that the similarity between tables, which is a set of similar columns, cannot be obtained. It was not possible to simultaneously compare attributes with different levels of structure, such as column attributes and table attributes.

  In addition, since the conventional method is intended to obtain a completely matching column, there is a problem that it is difficult to compare when data and attributes are greatly different. Furthermore, the attribute used concretely was limited.

  In addition, the conventional hierarchical classification often depends on the structure of the classification, and it is not suitable for interactive operation because classification cannot be performed by changing the hierarchy or all calculations must be recalculated. There was a problem.

  As an integration support in data integration, the present invention classifies tables in a form that is easy for the user to understand based on the similarity between tables in order to determine the necessity / unnecessity of data in a database or data warehouse. It is aimed.

The table classification device of the present invention
Accepts a predetermined input, and on the basis of the received input, attribute set information including predetermined table attributes for each attribute set number of 1 to N (N is an integer of 2 or more), and priority of classification when a table is classified A setting unit that sets classification hierarchy information associated with any one of the attribute set numbers that is not duplicated for each of the hierarchy numbers 1 to M (M is an integer of 2 or more and N or less) indicating the order; ,
An attribute acquisition unit that acquires the table attribute indicated by the attribute set information for each table from a database that stores a plurality of tables;
The table attribute determined from the attribute set number corresponding to the hierarchy number of the classification hierarchy information is fetched from the table attributes acquired by the attribute acquisition unit, and the hierarchy of the classification hierarchy information is based on the fetched table attribute A level-by-table similarity generation unit that generates a level-by-level table similarity indicating the degree of similarity between the respective tables stored in the database for each number;
And a classification unit that classifies a plurality of tables stored in the database using the inter-table similarity between the tables generated by the hierarchical table generation unit for each hierarchical number.

  According to the present invention, the tables can be classified in a form that is easy for the user to understand based on the similarity between the tables.

Embodiment 1 FIG.
FIG. 1 is a diagram showing an example of the appearance of a database classification device 30 (table classification device) that is a computer. In FIG. 1, a database classification device 30 includes a system unit 830, a display device 813 having a CRT (Cathode / Ray / Tube) or LCD (liquid crystal) display screen, a keyboard 814 (Key / Board: K / B), and a mouse 815. , FDD 817 (Flexible Disk Drive), compact disk device 818 (CDD: Compact Disk Drive), printer device 819, and other hardware resources, which are connected by cables and signal lines.

  The system unit 830 is a computer and is connected to a network. A database management apparatus 10 connected to the database 20 and a visualization apparatus 40 are connected to the network. The database classification device 30 can communicate with the database management device 10 and the visualization device 40 via a network. The database 20 stores a plurality of tables. The database classification device 30 can acquire data relating to the table from the database 20.

  FIG. 2 is a diagram illustrating an example of hardware resources of the database classification device 30 according to the first embodiment. 2, the database classification device 30 includes a CPU 810 (also referred to as a central processing unit, a processing unit, an arithmetic unit, a microprocessor, a microcomputer, or a processor) that executes a program. The CPU 810 is connected to a ROM (Read Only Memory) 811, a RAM (Random Access Memory) 812, a display device 813, a keyboard 814, a mouse 815, a communication board 816, an FDD 817, a CDD 818, a printer device 819, and a magnetic disk device 820 via a bus 825. And control these hardware devices. Instead of the magnetic disk device 820, a storage device such as an optical disk device or a flash memory may be used.

  The RAM 812 is an example of a volatile memory. Storage media such as the ROM 811, the FDD 817, the CDD 818, and the magnetic disk device 820 are examples of nonvolatile memories. These are examples of a storage device, a storage unit, or a storage unit. The communication board 816, the keyboard 814, the FDD 817, and the like are examples of an input unit and an input device. The communication board 816, the display device 813, the printer device 819, and the like are examples of an output unit and an output device.

  The communication board 816 is connected to a network (such as a LAN). The communication board 816 may be connected not only to the LAN but also to a WAN (wide area network) such as the Internet or ISDN.

  The magnetic disk device 820 stores an operating system 821 (OS), a window system 822, a program group 823, and a file group 824. The programs in the program group 823 are executed by the CPU 810, the operating system 821, and the window system 822.

  The program group 823 stores a program that executes a function described as “˜unit” in the description of the embodiment described below. The program is read and executed by the CPU 810.

  The file group 824 includes “determination result”, “calculation result”, “extraction result”, “generation result”, and “processing result” in the description of the embodiment described below. Information, data, signal values, variable values, parameters, and the like are stored as items of “˜file” and “˜database”. 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 810 via a read / write circuit, and extracted, searched, referenced, compared, and calculated. Used for CPU operations such as calculation, processing, output, printing, and display. Information, data, signal values, variable values, and parameters are temporarily stored in the main memory, cache memory, and buffer memory during the CPU operations of extraction, search, reference, comparison, operation, calculation, processing, output, printing, and display. Is remembered.

  In the description of the embodiments described below, data and signal values are stored in RAM 812 memory, FDD 817 flexible disk, CDD 818 compact disk, magnetic disk device 820 magnetic disk, other optical disks, mini disks, DVD (Digital). -It records on recording media, such as Versatile and Disk. Data and signals are transmitted on-line via the bus 825, signal lines, cables, and other transmission media.

  In addition, what is described as “to part” in the description of the embodiment described below may be “to circuit”, “to device”, “to device”, “means”, and “to step”. ”,“ ˜procedure ”, or“ ˜processing ”. That is, what is described as “˜unit” may be realized by firmware stored in the ROM 811. 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 810 and executed by the CPU 810. That is, the program causes the computer to function as “to part” described below. Alternatively, the procedure or method of “to part” described below is executed by a computer.

  FIG. 3 is an overall configuration diagram of the data integration support system according to the first embodiment. The data integration support system includes a database management device 10, a database 20 managed by the database management device 10, a database classification device 30, and a visualization device 40. Finally, a user 50 resembles a table for the purpose of data integration. It supports to make sex identifiable. The database 20 stores a plurality of tables.

  The feature of the first embodiment resides in the database classification device 30 in the data integration support system. FIG. 4 is a configuration diagram of the database classification device 30. The database classification device 30 includes a metadata extraction unit 31, a data analysis unit 32, a classification structure input unit 33, a classification structure management unit 34, a column similarity calculation unit 35, a table similarity calculation unit 36, and a classification. And a determination unit 37. The metadata extraction unit 31 and the data analysis unit 32 constitute an attribute acquisition unit. The classification structure input unit 33 and the classification structure management unit 34 constitute a setting unit. The column similarity calculation unit 35 and the table similarity calculation unit 36 constitute an inter-table similarity generation unit.

(1) The metadata extraction unit 31 acquires table attributes and column attributes from the database management apparatus 10.
(2) The data analysis unit 32 acquires data from the database management device 10 and generates statistical information.
(3) The classification structure input unit 33 receives a set of attributes (information for setting the attribute set information 70) and the order of classification (information for setting the classification hierarchy information 80) from the user 50. Is set, and attribute set information 70 (FIG. 11 described later) and classification hierarchy information 80 (FIG. 12 described later) are set.
(4) The classification structure management unit 34 stores the attribute set information 70 and the classification hierarchy information 80 set by the classification structure input unit 33, and outputs them as necessary.
(5) The column similarity calculation unit 35 acquires column attributes from the metadata extraction unit 31, acquires data statistical information from the data analysis unit 32, and acquires attribute set information 70 from the classification structure management unit 34. And calculate the similarity between the columns.
(6) The table similarity calculation unit 36 acquires a table attribute from the metadata extraction unit 31, acquires a column similarity from the column similarity calculation unit, calculates a matched column pair from the column similarity, The attribute set information 70 is acquired from the classification structure management unit from the attribute and matching column pair, and the table similarity is calculated.
(7) The classification determination unit 37 acquires the similarity between tables from the table similarity calculation unit 36, acquires the classification hierarchical structure from the classification structure management unit 34, and calculates (generates) the classification hierarchical structure of the table. .

Next, a table classification result obtained by the database classification device 30 will be described with reference to FIG. FIG. 5 is an image diagram of a result obtained by the database classification device 30. T1 to T17 are all tables to be classified.
In this example,
(1) C1, C2, and C3 are the results of the database classification device 30 first classifying by table name and grouping highly similar tables together.
(2) C11, C12, and C13 are the results of grouping tables having similar data contents with respect to the tables classified as C1.
(3) C21 and C22 are the results of grouping tables with similar data contents with respect to the tables classified as C2.
(4) C31 and C33 are the results of grouping the tables closer to each other with respect to the tables classified as C3.

  In addition, a group hierarchical structure including similarity information is referred to as a classification hierarchical structure so that it can be displayed with a distance (arrangement) according to the similarity between tables and groups as compared to the normal tree structure classification. However, the display method and the display format are not included in the present invention.

Next, an outline of the operation of the database classification device 30 will be described with reference to FIG. FIG. 6 is a diagram showing the overall flow of the database classification method realized by the database classification device 30. In the database classification method, classification structure input processing S1, attribute acquisition processing S2, similarity calculation processing S3, and classification hierarchy calculation processing S4 are performed in this order.
(1) The classification structure input process S1 accepts input of information for setting “attribute set information 70” and “classification hierarchy information 80” from the user, and receives “attribute set information 70” and “classification hierarchy information 80”. It is a process to set.
(2) The attribute acquisition process S2 is a process of acquiring necessary attributes from the metadata extraction unit 31 and the data analysis unit 32 in accordance with the attribute set information 70.
(3) The similarity calculation process S3 is a process of calculating the column similarity and the table similarity for the attribute set information 70.
(4) The classification hierarchy calculation process S4 is a process for calculating a group of tables from the table similarity hierarchically in accordance with the priority order of classification (classification hierarchy information 80).

Details of each process will be described below. FIG. 7 is a diagram showing the procedure of the classification structure input process S1 in FIG. The classification structure input process S1 is processed in the order of attribute set selection S11, classification hierarchy setting S12, classification hierarchy accumulation S13.
(1) The attribute set selection S11 is executed by the classification structure input unit 33 in the database classification device 30. The attribute set selection S11 is a process of setting a set of attributes (attribute set information 70) as a unit of classification according to a user designation.
(2) The classification hierarchy setting S12 is executed by the classification structure input unit 33 in the database classification apparatus 30. The classification hierarchy setting S12 is a process of setting “classification hierarchy information 80” which is the priority order of classification according to the designation of the user.
(3) The classification hierarchy storage S13 is executed by the classification structure management unit 34 in the database classification apparatus 30. The classification hierarchy accumulation S13 accumulates “attribute set information 70” and “classification hierarchy information 80” and presents these information in response to requests for other processes.

  FIG. 8 shows column attributes 61 that are elements of a set of attributes. The column name A 1, type A 2, accuracy A 3, size A 4, and NULL possible flag A 5 are column metadata that the metadata extraction unit 31 acquires from the database 20. The data analysis unit 32 acquires the unique rate A6, the NULL rate A7, the maximum value / maximum date / maximum number of characters A8, the minimum value / minimum date / minimum number of characters A9, and the average value / intermediate date / average number of characters A10 from the database 20. This is statistical information of data in one column. About A8-A10, the kind of value changes with kinds of type.

  The metadata extraction unit 31 may extract the column metadata by the database management device (10) before the classification structure input processing is executed, or the database management device according to the request for the classification structure input processing. Metadata may be extracted from 10. The data analysis unit 32 may extract column data from the database management apparatus 10 and calculate statistical information before the classification structure input process is executed, or may perform database management in response to a request for the classification structure input process. Statistical information may be calculated by extracting data from the apparatus 10. Also, attributes other than A1 to A10 and statistical information may be used.

  FIG. 9 shows table attributes 62 that are elements of a set of attributes. The table name A11, the column number A12, the VARCHAR type column number A13, the numeric type column number A14, the date type column number A15, and the record length A16 are table metadata that the metadata extraction unit 31 acquires from the database 20. The record number A17 is statistical information of data of one table acquired by the data analysis unit 32 from the database 20.

  FIG. 10 shows attributes of a table used for normalization for calculating similarity using attributes. The maximum column number A18, the maximum record length A19, and the maximum record number A20 are statistical information of data of all tables that the data analysis unit 32 acquires from the database 20. The metadata extraction unit 31 may extract table metadata from the database management apparatus 10 before the classification structure input process is executed, or from the database management apparatus 10 in response to a request for the classification structure input process. Metadata may be extracted. The data analysis unit 32 may extract table data from the database management apparatus 10 and calculate statistical information before the classification structure input process is executed, or may perform database management in response to a request for the classification structure input process. Statistical information may be calculated by extracting data from the apparatus 10. Further, attributes other than A11 to A20 and statistical information may be used.

FIG. 11 is an example of “attribute set information 70” that is a unit of classification set by the user in the attribute set selection S11 in the classification structure input process S1 of FIG.
In this example,
Set 1 includes a table name A11 and a column name A1.
The set 2 includes a column number A12, a record length A16, a size A4, an accuracy A3, and a NULL enable flag A5.
Set 3 consists of the number of records A17.
The set 4 includes a unique rate A6 and a NULL rate A7.
The set 5 includes a maximum value / maximum date / maximum number of characters A8, a minimum value / minimum date / minimum number of characters A9, an average value, and an intermediate date / average number of characters A10.
The attribute and statistical information included in the set may be single or plural. Further, table attributes and statistical information may be mixed with column attributes and statistical information.

FIG. 12 is an example of the classification hierarchy information 80 in the classification priority order set by the user in the classification hierarchy setting S12 in the classification structure input process S1 of FIG.
In this example,
Tier 1 is set 1,
Tier 2 is set 2,
Tier 3 is set 3,
Tier 4 is set 4,
Tier 5 is set 5
Is specified.
Of course, the classification evaluation order may be arbitrarily specified.
The classification hierarchy storage S13 in the classification structure input process S1 is a process for outputting and storing the attribute set information 70 and the classification hierarchy information 80 specified by the attribute set selection S11 and the classification hierarchy setting S12.

  FIG. 13 is a diagram showing the procedure of the attribute acquisition process S2 and the similarity calculation process S3 in FIG. The attribute acquisition process S2 includes an initialization process S21, classification hierarchy information acquisition S22, target table selection S23, table attribute acquisition S24, column attribute acquisition S25, and data statistical information acquisition S26. S21, S22, S23, S24, S26, S32, S33, and S34 are executed by the table similarity calculation unit 36 in the database classification device 30. S25 and S31 are executed by the column similarity calculation unit 35 in the database classification device 30. The similarity calculation process S3 includes a column similarity calculation S31, a column pair calculation S32, a table similarity calculation S33, a table group loop determination S34, a classification hierarchy loop determination S35, and a hierarchy-specific similarity information output S36. S35 and S36 are executed by the classification determination unit 37 in the database classification device 30.

  Details of each of the attribute acquisition process S2 and the similarity calculation process S3 in FIG. 6 will be described below with reference to FIG.

(1) In the initialization process S21, the table similarity calculation unit 36 performs initialization such as specifying all target tables.
(2) In the classification hierarchy information acquisition S22, the table similarity calculation unit 36 acquires the classification hierarchy information 80 and the attribute set information 70 accumulated by the classification structure management unit 34 from the classification structure management unit 34.
(3) In the target table selection S23, the table similarity calculation unit 36 first designates the attribute set information 70 corresponding to the set number of the hierarchy No1 of the classification hierarchy information 80 and the initialization process S21 as the first classification hierarchy loop. One set of tables whose similarity should be calculated is extracted from all the tables.
(4) In the table attribute acquisition S24, the table similarity calculation unit 36 uses the attribute information (meta) of the two tables selected in the target table selection S23 for the attribute of the table included in the attribute set information 70 of the hierarchy No1. Data) is acquired from the metadata extraction unit 31.
(5) In the column attribute acquisition S25, the column similarity calculation unit 35, for the attribute whose column type is included in the attribute set information 70 of the hierarchy No1 is the column included in the two tables selected in the target table selection S23. Attribute information (metadata) is acquired from the metadata extraction unit 31.
(6) In the data statistical information acquisition S26, the table similarity calculation unit 36 selects the two tables selected in the target table selection S23 for the statistical information whose type is the attribute of the table included in the attribute set information 70 of the hierarchy No1. Attribute information (statistical information) is acquired from the data analysis unit 32. In addition, for the statistical information whose type is the column attribute included in the attribute set information 70 of the hierarchy No. 1, the attribute information (statistical information) of the column included in the two tables selected in the target table selection S23 is converted into the data analysis unit 32. Get more.

  Through S24, S25, and S26 described above, necessary information for calculating the similarity is prepared for the target table selected in S23.

(1) In the column similarity calculation S31, the column similarity calculation unit 35 uses the column metadata acquired in the column attribute acquisition S25 and the column statistical information acquired in the data statistical information acquisition S26. Find the similarity of. A technique for obtaining a similarity at the same time by combining a plurality of attributes can be realized by a known technique such as distance calculation in a multidimensional space. For example, methods such as inner product, cosine scale, Euclidean distance, Hamming distance, etc. are used.

  Hereinafter, an example of calculating the similarity in the n-dimensional space between the column 1 and the column 2 using the Euclidean distance will be shown. In the following example, the column metadata acquired in the column attribute acquisition S25 and the column statistical information acquired in the data statistical information acquisition S26 are all included in A1 to A10 shown in FIG.

If the column types do not match, the column similarity e can be determined by the following equation, for example. h1 is a weight for tuning.
e ² = h1 ÷ (number of column name characters in column 1 + number of column name characters in column 2)
X (number of column name characters in column 1 + number of column name characters in column 2-maximum number of consecutive matching characters between column names in column 1 and column 2 x 2)
When both the column types are numerical types, the column similarity e can be obtained by the following equation, for example. h1 to h9 are tuning weights.
e ² = h1 ÷ (number of column name characters in column 1 + number of column name characters in column 2)
X (number of column name characters in column 1 + number of column name characters in column 2-maximum number of consecutive matching characters between column names in column 1 and column 2 x 2)
+ H2 × (0 if the accuracy matches, 1 if the accuracy does not match)
+ H3 × (column 1 size−column 2 size) ²
+ H4 × (0 if NULL is possible / impossible, 1 if not)
+ H5 × (column 1 unique rate−column 2 unique rate) ²
+ H6 × (Column 1 NULL rate−Column 2 NULL rate) ²
+ H7 × (maximum value of column 1−maximum value of column 2) ²
+ H8 × (minimum value of column 1−minimum value of column 2) ²
+ H9 × (average value of column 1−average value of column 2) ²

  When both the column types are date types, the column similarity e can be obtained by the following equation, for example. h1 to h9 are tuning weights.

e ² = h1 ÷ (number of column name characters in column 1 + number of column name characters in column 2)
× (Number of column name characters in column 1 + number of column name characters in column 2
-Maximum number of consecutive matching characters between the column name of column 1 and the column name of column 2 x 2)
+ H3 × (column 1 size−column 2 size) ²
+ H4 × (0 if NULL is possible / impossible, 1 if not)
+ H5 × (column 1 unique rate−column 2 unique rate) ²
+ H6 × (Column 1 NULL rate−Column 2 NULL rate) ²
+ H7 × (maximum date in column 1−number of days in maximum date in column 2) ²
+ H8 × (minimum date in column 1−number of days in minimum date in column 2) ²
+ H9 × (intermediate date in column 1−number of days in intermediate date in column 2) ²

  When both the column types are VARCHAR types, the column similarity e can be obtained by the following equation, for example. h1 to h9 are tuning weights.

e ² = h1 ÷ (number of column name characters in column 1 + number of column name characters in column 2)
× (Number of column name characters in column 1 + number of column name characters in column 2
-Maximum number of consecutive matching characters between the column name of column 1 and the column name of column 2 x 2)
+ H3 × (column 1 size−column 2 size) ²
+ H4 × (0 if NULL is possible / impossible, 1 if not)
+ H5 × (column 1 unique rate−column 2 unique rate) ²
+ H6 × (Column 1 NULL rate−Column 2 NULL rate) ²
+ H7 × (maximum number of characters in column 1−maximum number of characters in column 2) ²
+ H8 × (minimum number of characters in column 1−minimum number of characters in column 2) ²
+ H9 × (average number of characters in column 1−average number of characters in column 2) ²

  As described above, the distance (similarity) between the columns can be obtained using various column attributes in accordance with the type match. In the above example, the calculation of similarity is performed by evaluating the attributes of all the columns. However, the distance (similarity) may be calculated for a subset of the attributes for each attribute set.

  (2) Subsequently, in the column pair calculation S32, the table similarity calculation unit 36 uses the similarity between columns obtained in S31 to obtain a column pair between tables. FIG. 14 is a conceptual diagram for obtaining a column pair from the distance in the n-dimensional space. A black circle is a column of a table, and a white circle is a column of another table. This is a method in which columns that are closest in the n-dimensional space and that are equal to or smaller than a certain threshold are regarded as matching columns and are paired. About this method, it is realizable by a well-known technique (for example, Unexamined-Japanese-Patent No. 2006-63332).

Further, in S32, the similarity is calculated for columns other than the matching columns. FIG. 15 shows an example of a method of calculating the similarity between tables for the matching column pair and all other columns.
(Matching column pair)
The previous matching column pair is referred to as a “matching column pair”. The columns are connected by a solid line in FIG. In the example of FIG. 15, the column U11 and the column U23, the column U12 and the column U21, and the column U14 and the column U22 are matching column pairs.
(Similar column pair)
Next, columns that are not considered coincident but are similar are referred to as “similar column pairs”. The columns are connected by dotted lines in FIG. In the example of FIG. 15, the column U13 and the column U24 are similar column pairs. The condition for the similar column pair is a set other than the matching column pair and the sum of the distances between the columns is the closest. That is, the similar column pair is a column pair having a distance equal to or greater than the threshold.
(Unmatched column pair)
Finally, unpaired columns may remain due to differences in the number of columns between tables. This column is paired assuming the corresponding column. This temporary column is referred to as “NULL column”, and the columns that are paired with the NULL column are referred to as “mismatched column pairs”. The columns connected by the one-point difference line in FIG. 15 are a mismatched column pair. In the example of FIG. 15, the column U15 and the column U25 are mismatched column pairs. The NULL column is a virtual column of the same type as the paired column and zero data.

  (3) In the table similarity calculation S33, the table similarity calculation unit 36 uses the table metadata acquired in the table attribute acquisition S24 and the table statistical information acquired in the data statistical information acquisition S26, and the column similarity calculated in S31. The similarity between the tables selected in S23 is calculated using the degree and the column pair obtained in S32.

  First, a similarity calculation method using the table metadata acquired in the table attribute acquisition S24 and the table statistical information acquired in the data statistical information acquisition S26 will be described. Similar to the calculation of similarity between columns, a technique for obtaining a similarity simultaneously by combining a plurality of attributes can be realized by a known technique such as distance calculation in a multidimensional space. For example, methods such as inner product, cosine scale, Euclidean distance, Hamming distance, etc. are used. An example of calculating similarity in the n-dimensional space between Table 1 and Table 2 using the Euclidean distance is shown below. The following example shows a case where the metadata acquired in the table attribute acquisition S24 and the statistical information acquired in the data statistical information acquisition S26 are all included in A11 to A20 shown in FIG.

  The similarity r of the table can be obtained by the following formula, for example. k1 to k7 are weights for tuning.

r ² = k1 ÷ (number of table name characters in table 1 + number of table name characters in table 2)
X (number of table name characters in table 1 + number of table name characters in table 2-maximum number of consecutive matching characters between table name in table 1 and table name in table 2 x 2)
+ K2 ÷ maximum number of columns ² × (number of columns in table 1−number of columns in table 2) ²
+ K3 ÷ maximum number of columns ² × (number of VARCHAR columns in table 1−number of VARCHAR columns in table 2) ²
+ K4 ÷ maximum number of columns ² × (number of numeric columns in table 1−number of numeric columns in table 2) ²
+ K5 ÷ maximum number of columns ² × (number of date-type columns in table 1−number of date columns in table 2) ²
+ K6 ÷ maximum number of records ² × (number of records in table 1−number of records in table 2) ²
+ K7 ÷ maximum record length ² × (record length of table 1−record length of table 2) ²

  Subsequently, a method of calculating the similarity between the tables selected in S23 will be described using the similarity r, the column similarity calculated in S31, and the column pair determined in S32.

  The similarity R of the table can be obtained by the following equation, for example. K1 to K4 are weights for tuning.

R ² = K1 × r ²
+ (K2 × ((distance between column U11 and column U23) ² + (distance between column U12 and column U21) ² + (distance between column U14 and column U22) ² )
+ K3 × (distance between column U13 and column U24) ²
+ K4 × (distance between column U15 and column U25) ² ) ÷ number of all column pairs

Also, when evaluating only matching column pairs (ignoring unpaired columns)
K3 = K4 = 0
As a result, the corresponding distance may not be calculated.

  (4) In the table grouping loop S34, the table similarity calculation unit 36 determines whether there is a table group for which similarity calculation has not been performed. It is a loop for a pair.

  (5) In the classification hierarchy loop S35, the classification determination unit 37 determines whether or not there is a hierarchy for which the similarity calculation has not been performed, and if it remains, the classification hierarchy information 80 for the next hierarchy is acquired. , A loop for returning to S22.

(Output of similarity between tables)
As described above, in S21 to S35 shown in FIG. 13, the similarity calculated for all table sets for all classification layers (hierarchy No1 to No5 in FIG. 12) is obtained in the similarity information output by layer S36. The classification determination unit 37 outputs. FIG. 16 shows an example of the level-to-table similarity 90 (may be referred to as the level-to-table similarity) output in S36. For each layer, the similarity between tables shown in the following equation is held as a two-dimensional array of the total number N of tables.
Inter-table similarity = Ri [N] [N],
i: Indicates the hierarchy number in the classification hierarchy information 80.
N: Indicates a table number.

  FIG. 17 is a diagram showing a detailed procedure of the classification hierarchy calculation process S4 in FIG. In the classification hierarchy calculation S4, the classification determination unit 37 classifies the tables into a hierarchical structure based on the inter-table similarity Ri [N] [N] obtained in the similarity calculation S3. In the classification hierarchy calculation process S4, the classification determination unit 37 performs an initialization process S41, a classification hierarchy table set acquisition S42, a classification hierarchy inter-table similarity acquisition S43, a current group table classification S44, a child group output S45, and the like. The hierarchical loop S46, the lower hierarchical loop S47, and the classification hierarchical structure result output S48 are processed in this order.

  In the initialization process S41, the classification determination unit 37 performs an initialization process necessary for a loop, such as designating a root of a hierarchical structure. For example, in the example of FIG. 5, name similarity is performed first (first layer) in the initial state value.

  In the classification hierarchy table set acquisition S42, the classification determination unit 37 acquires a table group included in the group in the hierarchy to be classified. In the first loop, all tables are targeted. In the second and subsequent loops, the child group generated in the previous loop is used as the population. For example, in the example of FIG. 5, T1 to T17 are set as the parent group (current group) in the first loop.

  In the inter-table similarity acquisition S43, the similarity 90 between tables classified by classification hierarchy shown in the example of FIG. 16 output as a result of the similarity calculation processing S3 in FIG. 6 is acquired. In this case, R1 is acquired.

  In the current group table classification S44, table clustering is performed on the set of tables acquired in the classification hierarchy table set acquisition S42, based on the inter-table hierarchy similarity 90 acquired in the inter-table similarity acquisition S43. The clustering method is realized by a known technique. For example, in the example of FIG. 5, T1 to T17 are classified.

  In the child group output S45, the result classified in the current group table classification S44 is output. For example, in the example of FIG. 5, C1 to C3 are classified.

  The same hierarchy loop S46 is a loop that determines whether another group in the same classification hierarchy remains, and if it remains, returns to the classification hierarchy table set acquisition S42 in order to set the next group as a classification target. S46 In the first loop, since there is no sibling (same hierarchy) of the parent group, the process proceeds to S47.

  The lower hierarchy loop S47 determines whether or not the lower hierarchy of the classification hierarchy information 80 remains after the classification for all the groups of the current hierarchy is completed, and in order to classify the lower hierarchy if it remains. This is a loop for returning to the classification hierarchy table set acquisition S42. In order to perform the processing of the second lower layer, the process returns to S42.

The processing in FIG. 17 will be described in comparison with FIG. 5 as follows. FIG. 17 shows an operation by the classification determination unit 37.
(1) In S41, in FIG. 5, name similarity that is an initial state value is performed first (first layer).
(2) In S42, in FIG. 5, in the first loop, T1 to T17 are set as the parent group (current group).
In (3) S43, in obtaining a hierarchical table Similarity _{R 1} in FIG. 16 (4) S44, in FIG. 5, it classifies the T1～T17.
(5) In S45, the child groups C1 to C3 are output in FIG.
(6) In S46, since there is no sibling (same hierarchy) of the parent group in the first loop, the process proceeds to S47.
(7) In S47, the process returns to S42 to perform the processing of the second hierarchy.
(8) In S42, in FIG. 5, tables T1 to T6 of C1 are set as a parent group (current group).
(9) In S43, the acquired a hierarchical table Similarity _{R 2} in FIG. 16.
(10) In S44, the tables T1 to T6 are classified in FIG.
(11) In S45, the child groups C11 to C13 are output in FIG.
(12) In S46, the process proceeds to S42 in order to perform the process of C2 which is the sibling (same hierarchy) of the parent group.
(13) In S42 to S45, C2 is similarly processed.
(14) In order to perform the process of C3 which is a sibling (same hierarchy) of the parent group, the process proceeds to S42.
(15) In S42 to S45, C3 is similarly processed.
(16) In S46, since there is no sibling (same hierarchy) of the parent group, the process proceeds to S47.
(17) In S47, the process returns to S42 to perform the processing of the third hierarchy.
(18) In S42, in FIG. 5, the tables T1 to T3 of C11 are set as the parent group (current group).
(19) In S43, the acquired a hierarchical table Similarity _{R 3} in FIG. 16.
(20) In S44, the tables T1 to T3 are classified in FIG.
(21) In S45, in FIG. 5, the child groups C11 to C13 are output as described above.
(22) In S46, the process proceeds to S42 in order to perform the process of C12 which is a sibling (same hierarchy) of the parent group.
(23) In S42 to S45, C12 to C33 are similarly processed.
(24) In S46, since there is no sibling (same hierarchy) of the parent group, the process proceeds to S47.
(25) In S47, the process returns to S42 in order to perform the fourth layer process. The same process is repeated, and the loop is terminated when the lowest hierarchy is reached.
(26) In S48, the hierarchically classified result shown in FIG. 5 is obtained.

  As described above, the results classified in all the classification hierarchies through S41 to S47 are output in the classification hierarchical structure result output S48. The classification hierarchical structure result is hierarchically clustered, becomes a graph structure with a similarity added, and is visualized by the visualization device 40 in the data integration support system of FIG.

  As described above, since the database classification device 30 designates the priority order of attributes that serve as a criterion for similarity determination, the similarity can be confirmed in order from the important comparison given priority by the user. Becomes clear.

  Furthermore, since it is possible to specify a plurality of attributes as criteria for similarity determination, classification accuracy can be improved according to the user's intention.

  In addition, since the table is classified using the similarity of the set of columns collected in a certain meaning of the table, the similarity of the tables that are semantically closer than the flatness of the similarity between the columns Can be identified.

  In addition, because table attributes and column attributes are mixed and specified as classification criteria, users can freely specify priorities to adjust classification accuracy and work efficiency. it can.

  Furthermore, since similar columns and mismatched columns other than the pair of columns regarded as matching are included in the similarity evaluation target, the accuracy of determining the similarity of the tables can be improved.

  In addition, since the statistical information of tables and columns is used as a part of attributes for determining similarity, it is possible to compare tables with different numbers of records and columns with less attribute value matches.

  In addition, since a lot of database-specific attribute information is used, the table can be classified even if the information that can be acquired is incomplete.

Embodiment 2. FIG.
In the first embodiment described above, rather, the classification of a fixed hierarchical structure is performed, but the user interactively changes the classification priority in order to grasp similar factors from the classification result. Can be effective. Of course, even in the first embodiment, if all the processes are restarted from 1, it is possible to perform classification with the changed priority order. However, similarity calculation and clustering are processes with a large amount of calculation, and the target When the number of tables and columns becomes huge, recalculation time may become a problem.

  The second embodiment shows an embodiment in which the calculation amount is reduced in such a case. FIG. 18 is an example of an image of a classification result obtained by changing the order of hierarchy by changing the classification priority from the classification result of FIG. In the example of FIG. 5, classification is first performed by name similarity, and then classification is performed by data similarity. In the example of FIG. 18, the priority order of similarity is changed, and classification based on data similarity is performed first, and then classification based on name similarity is performed.

  In FIG. 18, T1 to T17 are all tables to be classified. In this example, D1, D2, and D3 are the results of first grouping data having similar data contents and grouping highly similar data. D11, D12, and D13 are results obtained by further classifying the tables classified as D1 by name. D21 and D22 are results obtained by further classifying the table classified as D2 by name. D31 and D33 are results obtained by further classifying the table classified as D3 by name. However, the display method and the display format are not included in the present invention.

  Also in the second embodiment, the overall configuration diagram of the database integration system is the same as FIG. In the second embodiment, the configuration diagram of the database classification device 30 is the same as that in FIG.

  Next, the operation will be described. The second embodiment differs from the first embodiment in the processing operation of the classification determination unit 37. Others are the same as in the first embodiment.

In the second embodiment, the overall flow of the database classification method is almost the same as that in FIG. 6, but the processing content of the classification hierarchy calculation S4 is different, resulting in the classification hierarchy calculation S5 shown in FIG.
(1) Also in the second embodiment, the classification structure input processing procedure is the same as in FIG.
(2) Also in the second embodiment, the same column attributes as those shown in FIG.
(3) Also in the second embodiment, the same attributes as in FIG. 9 are used as the attributes of the table that is the element of the attribute set.
(4) Also in the second embodiment, the table attributes used for normalization for calculating the similarity using the attributes are the same as those in FIG.
(5) Also in the second embodiment, an example of the attribute set information 70 as a classification unit is the same as that in FIG.
(6) Also in the second embodiment, an example of the classification hierarchy information 80 that is the priority order of classification is the same as that in FIG.
(7) Also in the second embodiment, the procedure of the attribute acquisition process and the similarity calculation process is the same as in FIG.
(8) Also in the second embodiment, the similarity 90 between tables classified by hierarchy obtained by the similarity calculation S3 is the same as that in FIG.

  FIG. 20 is a diagram showing a detailed procedure of the classification hierarchy calculation processing S5 in FIG. In the first embodiment, clustering is performed in order from the upper hierarchy according to the classification hierarchy information 80, and the final classification hierarchy structure result is output. In Embodiment 2, when the hierarchical classification according to the classification hierarchy information 80 and the clustering process of the table group according to the attribute set information 70 are separated, the hierarchy that is the priority order of the classification is replaced. The final classification hierarchy structure result can be obtained without recalculating the clustering process.

  In the classification hierarchy calculation process S5 by the classification determination unit 37 in the second embodiment, the table is classified for each attribute set in the first half based on the inter-table similarity obtained in the similarity calculation S3. The classification hierarchy calculation process S5 includes a classification initialization process S51, an acquisition of all table sets S52, an inter-table similarity acquisition S53, a table classification S54, an attribute set loop S55, an attribute set classification information output S56, and a table attribute set conversion S57. Process in this order. In the latter half, the processing is performed in the order of the classification hierarchy order multiple sort S61, the classification similarity calculation S62, and the classification hierarchy structure result output S63.

(1) In the classification initialization process S51, an initialization process necessary for examining the attribute sets in order is performed.
(2) In all table set acquisition S52, all tables to be classified are acquired. Even in the second and subsequent loops, all tables are targeted.
(3) In the inter-table similarity acquisition S53, the inter-table hierarchy similarity 90 shown in the example of FIG. 16 that is output as a result of the similarity calculation processing S3 in FIG. 19 is acquired.
(4) In the table classification S54, the table clustering process is performed on the set of tables acquired in the all table set acquisition S52 based on the inter-table similarity 90 acquired in the inter-table similarity acquisition S53. The clustering method is realized by a known technique.

  In the second embodiment, clustering is not advanced hierarchically, but all clustering results are acquired in advance for each attribute set.

(5) In the attribute set loop S55, it is determined whether there is a remaining attribute set that has not been classified. If there is a remaining attribute set, the process returns to S52 to classify the next attribute set.
(6) At the time when the table classification is finished for all attribute sets, the attribute set classification information 201 is output in the attribute set classification information output S56.
FIG. 21 is an example of the attribute set classification information 201. For each attribute set, as many table subsets as the number classified by clustering are obtained. At this time, the similarity (distance) between classifications is not calculated.
(7) In attribute-by-table conversion S57, the attribute-by-attribute-class classification information 201 obtained in attribute-by-attribute-classification information output S56 is converted into attribute-by-table attribute set information 202. FIG. 22 shows an example in which the attribute set classification information 201 of FIG. 21 is converted into table attribute set information 202. A set of tables for each attribute set is converted into a table format in which the classification numbers in each attribute set are arranged for each table.

(8) The classification hierarchy order multiple sort S61 sets the attribute set classification information 201, which is the output result of the table attribute set conversion S57, in the order of the hierarchy number of the classification accumulation information stored in the classification hierarchy accumulation S13 of FIG. Sorting by No is performed. FIG. 23 shows a result 203 of sorting the table-specific set information in the order of set Nos. 1, 2, 3, 4, and 5 according to the classification hierarchy information 80 shown in the example of FIG. In this example, the order of the set numbers and the order of the classification hierarchies are the same, but of course it is easy to sort in any order according to the user's specification. The sorting method can be realized by a known technique.

  FIG. 24 is a diagram in which the classification hierarchy order multiple sort result of FIG. At the time of S61, a classification hierarchical structure excluding similarity (distance) between classifications is generated.

(9) Interclass similarity calculation S62 is a process for obtaining the similarity between groups in each hierarchy based on the output of the classification hierarchy order multiple sort S61. The group for which the similarity is calculated is calculated only between groups having the same parent group. In the example of FIG. 24, a total of six similarities between classification Nos. 1, 2, 3, and 4 are calculated in the hierarchy No. 1 (set # 1). In the hierarchy of hierarchy No. 2 (set # 2), a total of four similarities of the combinations of classification No. 1 and 2 are calculated for the same upper hierarchy. A known technique is used as a method for calculating the similarity between classifications. For example, the distance of the nearest data pair in the single link method, the distance of the farthest data pair in the complete link method, the distance average of all data pairs in the group average method, or the WARD method The error sum of squares with the average vector (centroid) may be used. The similarity between tables necessary for calculating the similarity between classifications uses the similarity 90 between tables classified by hierarchy shown in FIG. 16, which is the result of the similarity information output S36 classified by hierarchy shown in FIG.

(10) As described above, the results classified in all the classification hierarchies by S51 to S62 are output in the classification hierarchical structure result output S63. The classification virtual structure result is hierarchically clustered, becomes a graph structure with a similarity added, and is visualized by the visualization device (4) in the data integration support system of FIG.

  Then, when the classification priority is interactively changed by the user's operation and the classification is obtained again, the attribute acquisition S2 and the similarity calculation S3 of FIG. 19 do not need to be executed, and the classification hierarchy calculation S5 is as follows. From S51 to S57 in FIG. 20, it is sufficient to execute only S61 to S63 by reusing the result of the previous execution.

  As described above, in the second embodiment, clustering is not performed in a hierarchical manner, but clustering and generation of a hierarchical structure are performed separately. When the reclassification is performed with the change, the result of clustering can be reused to reduce the calculation time, and the user can easily perform a comparative study in which the priority is freely changed.

FIG. 3 shows an appearance of a database classification device 30 in the first embodiment. FIG. 3 is a hardware configuration diagram of the database classification device 30 according to the first embodiment. 1 is a configuration diagram of a data integration support system in Embodiment 1. FIG. FIG. 3 is a block diagram of the database classification device 30 according to the first embodiment. The figure which shows the image of the result obtained by the database classification | category apparatus 30 in Embodiment 1. FIG. 5 is a flowchart showing an outline of the operation of the database classification device 30 according to the first embodiment. 5 is a flowchart showing classification structure input processing of the database classification device 30 according to the first embodiment. FIG. 6 is a diagram illustrating an example of a column attribute 61 in the first embodiment. FIG. 5 is a diagram showing an example of a table attribute 62 in the first embodiment. FIG. 5 shows normalization table data statistical information in the first embodiment. FIG. 5 is a diagram showing attribute set information in the first embodiment. FIG. 6 shows classification hierarchy information in the first embodiment. 5 is a flowchart showing attribute acquisition processing and similarity calculation processing of the database classification device 30 according to the first embodiment. FIG. 3 is a conceptual diagram for obtaining a column pair from an n-dimensional space distance in the first embodiment. 6 is a diagram illustrating a method for calculating a similarity between tables from a column pair according to Embodiment 1. FIG. The figure which shows the similarity between tables according to classification hierarchy in Embodiment 1. FIG. 5 is a flowchart showing classification hierarchy calculation processing by the database classification device 30 according to the first embodiment. The figure which shows the image of the result obtained by the database classification | category apparatus 30 in Embodiment 2. FIG. 9 is a flowchart showing an outline of the operation of the database classification device 30 according to the second embodiment. 10 is a flowchart showing details of classification hierarchy calculation processing of the database classification device 30 according to the second embodiment. The figure which shows the classification information 201 by attribute set in Embodiment 2. FIG. The figure which shows the attribute set information 202 according to table in Embodiment 2. FIG. The figure which shows the classification hierarchy order multiple sort result 203 in Embodiment 2. FIG. The figure which shows the tree structure of the classification | category hierarchy order multiple sort result in Embodiment 2. FIG.

Explanation of symbols

  10 database management device, 20 database, 30 database classification device, 40 visualization device, 50 user, 31 metadata extraction unit, 32 data analysis unit, 33 classification structure input unit, 34 classification structure management unit, 35 column similarity calculation unit, 36 Table similarity calculation unit, 37 Classification determination unit, 61 Column attribute, 62 Table attribute, 63 Normalization table data statistical information, 70 Attribute set information, 80 Classification hierarchy information, 90 Hierarchical table similarity, 201 Attribute set Separate classification information, 202 Table-specific attribute set information, 203 Classification hierarchy order multiple sort result, 204 Tree structure, 800 Computer system, 810 CPU, 811 ROM, 812 RAM, 813 Display device, 814 K / B, 815 Mouse, 816 Communication Board, 817 DD, 818 CDD, 819 Printer device, 820 a magnetic disk device, 821 OS, 822 Window system, 823 Program group, 824 File group, 825 bus, 830 system unit.

Claims (5)

Accepts a predetermined input, and on the basis of the received input, attribute set information including predetermined table attributes for each attribute set number of 1 to N (N is an integer of 2 or more), and priority of classification when a table is classified A setting unit that sets classification hierarchy information associated with any one of the attribute set numbers that is not duplicated for each of the hierarchy numbers 1 to M (M is an integer of 2 or more and N or less) indicating the order; ,
An attribute acquisition unit that acquires the table attribute indicated by the attribute set information for each table from a database that stores a plurality of tables;
The table attribute determined from the attribute set number corresponding to the hierarchy number of the classification hierarchy information is fetched from the table attributes acquired by the attribute acquisition unit, and the hierarchy of the classification hierarchy information is based on the fetched table attribute A level-by-table similarity generation unit that generates a level-by-level table similarity indicating the degree of similarity between the respective tables stored in the database for each number;
A table comprising: a classification unit that classifies a plurality of tables stored in the database using the inter-table similarity between tables generated by the hierarchical table similarity generation unit for each hierarchical number Classification device. The attribute set information set by the setting unit is:
At least one of the predetermined table attributes for each attribute set number includes a column attribute indicating a column attribute that is a component of the table,
The attribute acquisition unit
Obtaining the column attribute indicated by the attribute set information for each table from a database storing a plurality of tables;
The table-to-table similarity generation unit
When the table attribute determined from the attribute set number corresponding to the hierarchy number of the classification hierarchy information includes the column attribute, the column attribute is captured from the column attribute acquired by the attribute acquisition unit 2. The table classification device according to claim 1, further comprising a column attribute to generate the inter-table similarity between the hierarchy numbers. The hierarchy-level table similarity generation unit includes:
When the similarity between the tables according to hierarchy is generated for each hierarchy number of the classification hierarchy information, a column pair between two tables is generated according to a predetermined criterion, and the table according to hierarchy is generated based on the generated column pair. The table classification apparatus according to claim 2, wherein the similarity between the tables is generated. The hierarchy-level table similarity generation unit includes:
A matching column pair that is considered to match each other as the column pair;
Similar column pairs that are considered similar to each other;
4. The table classification apparatus according to claim 3, wherein a pair of mismatching columns that are regarded as not matching or similar to each other is generated. The attribute set information set by the setting unit is:
2. The table classification device according to claim 1, wherein at least one of the predetermined table attributes for each attribute set number is statistical information.

Images