Automated creation of join graphs for unrelated data sets among relational databases
A computer system processes a plurality of unrelated database tables to create a join graph where each node in the join graph represents a table. The nodes in the join graph are connected by weighted, directed edges, where each directed edge represents a join from a first column in a first table to a second column in a second table and where the weight of the directed edge represents a predicted level of success in performing the join. The edge weights can be based on a likelihood of finding a value from the first column in the second column. A user selects a subset of the tables, and the system creates a join tree with recommended joins between the tables selected by the user. The recommended joins are used to create a structured query language statement which is executed to return a result to the user.
1. A method implemented on a computer system comprising a processor and a memory, the method comprising, the computer system:
for each table of a plurality of database tables and for each column of a plurality of columns within the each table:
determining a data type for the each column by accessing and analyzing a subset of values stored in the each column,
determining a cardinality of the each column, and
storing a profile for the each column, wherein the profile for the each column includes the determined data type and the determined cardinality;
establishing a join graph comprising a plurality of nodes, wherein each node of the plurality of nodes represents one of the plurality of database tables;
for each pair of a plurality of pairs of a first table and a second table from the plurality of database tables:
for each pair of at least one pair of a first column from the first table and a second column from the second table:
comparing the data type included in the profile for the first column with the data type included in the profile for the second column,
determining that the data type included in the profile for the first column matches the data type included in the profile for the second column,
calculating a joinability score based upon the profile for the first column and the profile for the second column, wherein calculating the joinability score comprises:
retrieving the determined cardinality of the first column from the stored profile for the first column,
retrieving the determined cardinality of the second column from the stored profile for the second column,
determining a cardinality of a union between the first column and the second column,
determining a cardinality of an intersection between the first column and the second column by subtracting the determined cardinality of the union from the sum of the determined cardinality of the first column and the determined cardinality of the second column, and
dividing the determined cardinality of the intersection by the determined cardinality of the first column, and
adding a directed weighted edge to the join graph from a node representing the first table to a node representing the second table, wherein the weight of the added edge is based on the joinability score;
receiving a selection of a subset of the plurality of database tables;
creating a join tree comprising a maximum weight subset of edges in the join graph that spans a subset of nodes in the join graph corresponding to the selected subset of the plurality of database tables;
extracting a set of joins represented by the subset of edges;
creating a database query based on the extracted set of joins; and
executing the created database query to produce a query result.
2. The method of claim 1 , wherein for each instance of determining a cardinality, the determining is performed by at least one of calculating an estimated cardinality and calculating an exact cardinality.
3. The method of claim 2 , further comprising, for each table of the plurality of database tables and for each column of the plurality of columns within the each table:
processing the each column to create a set of m observables, with m being a positive integer constant greater than one, wherein each observable is a function of a set of elements in the each column, independent of replications;
including the set of m observables in the stored profile for the each column; and
using the set of m observables in calculating an estimated cardinality of the each column.
4. The method of claim 3 , wherein determining a cardinality of an intersection between the first column and the second column comprises:
combining the set of m observables included in the profile for the first column and the set of m observables included in the profile for the second column to create a combined set of m observables, wherein each observable in the combined set of m observables is a function of a set of elements in a union between the first column and the second column, independent of replications;
computing an estimated cardinality of a union between the first column and the second column based on the combined set of m observables without creating a union between the first column and the second column,
subtracting the estimated cardinality of the union from the sum of the estimated cardinality of the first column and the estimated cardinality of the second column.
5. The method of claim 4 , wherein combining the set of m observables included in the profile for the first column and the set of m observables included in the profile for the second column to create a combined set of m observables comprises selecting a maximum of each pair of corresponding observables and including the maximum of each pair in the combined set of m observables.
6. The method of claim 1 , wherein the profile for the each column further comprises a field name, and wherein the joinability score is further based on a comparison between the field name of the first column and the field name of the second column.
7. The method of claim 1 , further comprising for the each column of a plurality of columns within the each table, determining that a density of the each column exceeds a minimum density.
8. The method of claim 1 , wherein the adding a directed weighted edge to the join graph is performed in response to determining that the joinability score exceeds a threshold.
9. The method of claim 1 , further comprising modifying the weight of an edge in the join graph based on received user input.
10. The method of claim 1 , further comprising manually adding a directed weighted edge to the join graph based on received user input.
11. The method of claim 1 , wherein the joinability score represents an estimation-based statistical likelihood that values in the first column match values in the second column.
12. The method of claim 1 , wherein the adding a directed weighted edge to the join graph is performed in response to, for one pair of a first column from the first table and a second column from the second table, determining that the one pair has a maximum joinability score of a plurality of pairs of a first column from the first table and a second column from the second table.
13. A computer system comprising:
at least one storage medium storing instruction code; and
at least one processing unit configured to execute the instruction code to perform a method comprising:
for each table of a plurality of database tables and for each column of a plurality of columns within the each table:
determining a data type for the each column by accessing and analyzing a subset of values stored in the each column,
determining a cardinality of the each column, and
storing a profile for the each column, wherein the profile for the each column includes the determined data type and the determined cardinality;
establishing a join graph comprising a plurality of nodes, wherein each node of the plurality of nodes represents one of the plurality of database tables;
for each pair of a plurality of pairs of a first table and a second table from the plurality of database tables:
for each pair of at least one pair of a first column from the first table and a second column from the second table:
comparing the data type included in the profile for the first column with the data type included in the profile for the second column,
determining that the data type included in the profile for the first column matches the data type included in the profile for the second column,
calculating a joinability score based upon the profile for the first column and the profile for the second column, wherein calculating the joinability score comprises:
retrieving the determined cardinality of the first column from the stored profile for the first column,
retrieving the determined cardinality of the second column from the stored profile for the second column,
determining a cardinality of a union between the first column and the second column,
determining a cardinality of an intersection between the first column and the second column by subtracting the determined cardinality of the union from the sum of the determined cardinality of the first column and the determined cardinality of the second column, and
dividing the determined cardinality of the intersection by the determined cardinality of the first column, and
adding a directed weighted edge to the join graph from a node representing the first table to a node representing the second table, wherein the weight of the added edge is based on the joinability score;
receiving a selection of a subset of the plurality of database tables;
creating a join tree comprising a maximum weight subset of edges in the join graph that spans a subset of nodes in the join graph corresponding to the selected subset of the plurality of database tables;
extracting a set of joins represented by the subset of edges;
creating a database query based on the extracted set of joins; and
executing the created database query to produce a query result.
14. The computer system of claim 13 , wherein for each instance of determining a cardinality, the determining is performed by at least one of calculating an estimated cardinality and calculating an exact cardinality.
15. The computer system of claim 14 , wherein the method further comprises, for each table of the plurality of database tables and for each column of the plurality of columns within the each table:
processing the each column to create a set of m observables, with m being a positive integer constant greater than one, wherein each observable is a function of a set of elements in the each column, independent of replications;
including the set of m observables in the stored profile for the each column; and
using the set of m observables in calculating an estimated cardinality of the each column.
16. The computer system of claim 13 , wherein the adding a directed weighted edge to the join graph is performed in response to, for one pair of a first column from the first table and a second column from the second table, determining that the one pair has a maximum joinability score of a plurality of pairs of a first column from the first table and a second column from the second table.
17. A non-transitory computer-readable medium storing instruction code that when executed by a general purpose computer system causes the general purpose computer system to perform a method comprising:
for each table of a plurality of database tables and for each column of a plurality of columns within the each table:
determining a data type for the each column by accessing and analyzing a subset of values stored in the each column,
determining a cardinality of the each column, and
storing a profile for the each column, wherein the profile for the each column includes the determined data type and the determined cardinality;
establishing a join graph comprising a plurality of nodes, wherein each node of the plurality of nodes represents one of the plurality of database tables;
for each pair of a plurality of pairs of a first table and a second table from the plurality of database tables:
for each pair of at least one pair of a first column from the first table and a second column from the second table:
comparing the data type included in the profile for the first column with the data type included in the profile for the second column,
determining that the data type included in the profile for the first column matches the data type included in the profile for the second column,
calculating a joinability score based upon the profile for the first column and the profile for the second column, wherein calculating the joinability score comprises:
retrieving the determined cardinality of the first column from the stored profile for the first column,
retrieving the determined cardinality of the second column from the stored profile for the second column,
determining a cardinality of a union between the first column and the second column,
determining a cardinality of an intersection between the first column and the second column by subtracting the determined cardinality of the union from the sum of the determined cardinality of the first column and the determined cardinality of the second column, and
dividing the determined cardinality of the intersection by the determined cardinality of the first column, and
adding a directed weighted edge to the join graph from a node representing the first table to a node representing the second table, wherein the weight of the added edge is based on the joinability score;
receiving a selection of a subset of the plurality of database tables;
creating a join tree comprising a maximum weight subset of edges in the join graph that spans a subset of nodes in the join graph corresponding to the selected subset of the plurality of database tables;
extracting a set of joins represented by the subset of edges;
creating a database query based on the extracted set of joins; and
executing the created database query to produce a query result.
18. The computer-readable medium of claim 17 , wherein for each instance of determining a cardinality, the determining is performed by at least one of calculating an estimated cardinality and calculating an exact cardinality.
19. The computer-readable medium of claim 18 , wherein the method further comprises, for each table of the plurality of database tables and for each column of the plurality of columns within the each table:
processing the each column to create a set of m observables, with m being a positive integer constant greater than one, wherein each observable is a function of a set of elements in the each column, independent of replications;
including the set of m observables in the stored profile for the each column; and
using the set of m observables in calculating an estimated cardinality of the each column.
20. The computer-readable medium of claim 17 , wherein the adding a directed weighted edge to the join graph is performed in response to, for one pair of a first column from the first table and a second column from the second table, determining that the one pair has a maximum joinability score of a plurality of pairs of a first column from the first table and a second column from the second table.