Identifying Joins Of Tables Of A Database
Identifying join candidate includes identifying pairs of columns within a data source. Each pair includes a first column in a first table of the data source and a second column of a second table of the data source. For each pair, a casting similarity index is assigned from a predefined scale that includes a first casting similarity level indicative of a very low casting similarity level, where the casting similarity index is determined based on an extent to which data values from the first column are convertable to match a data type of the second column within the each pair, and where the very low casting similarity level is assigned to the each pair in a case that the first column has a boolean type and the second column has a float type. The join candidate is presented on a device of a user for selection by the user.
1 . A method, comprising:
identifying a join candidate in a data source, wherein identifying the join candidate comprises:
identifying pairs of columns within the data source, wherein each pair includes a first column in a first table of the data source and a second column of a second table of the data source; and
for the each pair, assigning a casting similarity index from a predefined scale that includes a first casting similarity level indicative of a “very low” casting similarity level,
wherein the casting similarity index is determined based on an extent to which data values from the first column are convertable to match a data type of the second column within the each pair, and
wherein the “very low” casting similarity level is assigned to the each pair in a case that the first column has a boolean type and the second column has a float type;
presenting the join candidate on a device of a user;
receiving, from the device of the user, a command to perform a data query of the data source, wherein the data query is based on the join candidate;
querying the data source based on the data query to obtain tabular data; and
outputting the tabular data.
2 . The method of claim 1 , wherein the predefined scale further includes a second casting similarity level indicative of a “moderate” casting similarity level, and wherein assigning the casting similarity index further comprises:
assigning the second casting similarity level in a case that the first column has a STRING type and the second column has a DATE type.
3 . The method of claim 1 , wherein the predefined scale further includes a second casting similarity level indicative of a “high” casting similarity level, wherein assigning the casting similarity index further comprises:
assigning the second casting similarity level in a case that the first column has a STRING type and the second column has a VARCHAR type.
4 . The method of claim 1 , wherein presenting the join candidate on the device of the user comprises:
displaying identified join candidates that include the join candidate on the device of the user in a ranked order based on respective casting similarity indexes of the identified join candidates.
5 . The method of claim 1 , wherein assigning the casting similarity index comprises:
assigning the casting similarity index based on a comparison of respective metadata associated with the first column and the second column.
6 . The method of claim 5 , wherein the respective metadata include respective data types, lengths, or precision attributes.
7 . The method of claim 1 , wherein identifying the pairs of the columns within the data source comprises:
comparing column names using a lexical similarity algorithm.
8 . The method of claim 1 , wherein each casting similarity level of the predefined scale is associated with respective criteria relating to at least one of data type compatibility of conversion feasibility.
9 . The method of claim 1 , wherein identifying the pairs of the columns within the data source comprises:
excluding pairs of columns where a first data type of the first column is incompatible with a second data type of the second column.
10 . The method of claim 1 , wherein identifying the pairs of the columns within the data source comprises:
calculating an edit distance between a first name of the first column and a second name of the second column.
11 . The method of claim 1 , wherein identifying the pairs of the columns comprises:
identifying synonyms among column names to reduce effective edit distances based on semantic similarity rather than character-level changes.
12 . A system, comprising:
a memory; and
a processor, the processor configured to execute instructions stored in the memory to:
identify a join candidate in a data source by instructions to:
identify pairs of columns across tables within the data source; and
assign a casting similarity index to each pair, wherein a first casting similarity level indicative of a “very low” casting similarity level is assigned to the each pair in a case that the each pair includes a BOOLEAN type column and a FLOAT type column;
perform a data query based on the join candidate to obtain result data; and
output the result data.
13 . The system of claim 12 , wherein a second casting similarity level indicative of a “moderate” casting similarity level is assigned to the each pair in a case that the each pair includes a STRING type column and a DATE type column.
14 . The system of claim 12 , wherein a second casting similarity level indicative of a “high” casting similarity level is assigned to the each pair in a case that the each pair includes a STRING type column and a VARCHAR type column.
15 . The system of claim 12 , wherein to identify the pairs of the columns across the tables within the data source comprises to:
identify a pair of columns based on calculating an edit distance between respective names of the columns.
16 . The system of claim 12 , wherein the processor is further configured to execute instructions stored in the memory to:
assign the casting similarity index based on a comparison of respective metadata associated with columns of the each pair.
17 . A non-transitory computer-readable storage medium, comprising executable instructions that, when executed by a processor, facilitate performance of operations, comprising:
identifying pairs of columns across tables within a data source;
assigning respective casting similarity indexes to the pairs of column, wherein a first casting similarity level indicative of a “very low” casting similarity level is assigned to a pair of columns in a case that the pair of columns includes a BOOLEAN type column and a FLOAT type column;
identifying a join candidate based on the respective casting similarity indexes; and
performing a data query based on the join candidate to obtain result data.
18 . The non-transitory computer-readable storage medium of claim 17 , wherein a second casting similarity level indicative of a “moderate” casting similarity level is assigned to the pair of columns in a case that the pair of columns includes a STRING type column and a DATE type column.
19 . The non-transitory computer-readable storage medium of claim 17 , wherein a second casting similarity level indicative of a “high” casting similarity level is assigned to the pair of columns in a case that the pair of columns includes a STRING type column and a VARCHAR type column.
20 . The non-transitory computer-readable storage medium of claim 17 , wherein identifying the pairs of the columns across the tables within the data source comprises to:
identifying one of the pairs of the columns based on calculating an edit distance between respective names of the columns of the one of the pairs of the columns.