Column2Vec: Structural Understanding via Distributed Representations of Database Schemas

TL;DR

Column2Vec introduces a method to generate meaningful representations of database columns from metadata, enabling applications like automatic column naming, validated on open-source schemas.

Contribution

This paper presents Column2Vec, a novel approach for embedding database columns based on metadata, facilitating schema understanding and automation tasks.

Findings

Effective column embeddings generated from metadata

Successful automatic column naming demonstrated

Viability shown on open-source schemas

Abstract

We present Column2Vec, a distributed representation of database columns based on column metadata. Our distributed representation has several applications. Using known names for groups of columns (i.e., a table name), we train a model to generate an appropriate name for columns in an unnamed table. We demonstrate the viability of our approach using schema information collected from open source applications on GitHub.

Figures

Tables

Table 1. Table 1. Example table name predictions

Metric value	Original name	Predicted name
0.11	tcardfieldoption	ttextboxsettings
0.13	subscription	oc_product_description
0.20	portfolio	projects
0.25	campaignchain_group	userseries
0.33	comments	content
0.39	mg_tag_properties	forum_articles_tags
0.43	users_clients	osiam_client
0.50	item	symbol
0.56	useridentity	usercredentials
0.67	initializedmodules	module
0.71	artist_tag_raw	release_tag_raw
0.75	sales_order_item	sales_order_line
0.80	arena_team	arena_team_member
0.86	inserttesttablefilter	inserttesttable
1.00	position	positions

Full text

††journalyear: 2019††copyright: none††conference: aiDM ’19: Second International Workshop on Exploiting Artificial Intelligence Techniques for Data Management; July 5, 2019; Amsterdam, NL††booktitle: aiDM ’19: Second International Workshop on Exploiting Artificial Intelligence Techniques for Data Management, July 5, 2019, Amsterdam, NL††price: 15.00

1. Introduction

It has become increasingly common for enterprise data management platforms to soak up as much data as possible from a variety of sources. These “data lakes” are often lacking in metadata which makes the structure of stored data challenging to understand. This limits the usability of this additional data since significant time must be invested by data scientists when adding useful metadata before the data can be analyzed or integrated with other sources.

One common operation is database normalization. When performing normalization, it is common to decompose a single database table into multiple tables based on dependencies which are inferred from the data. The final schema typically more closely represents logical entities in the underlying data. Consider the single table below:

authorID, firstName, lastName, ISBN, title

This table contains information on both authors and books. A standard normalization algorithm to convert this table into Boyce-Codd Normal Form (BCNF) (Codd, 1974) would produce three tables:

authorID, firstName, lastName ISBN, title authorID, ISBN

These tables represent information on authors, books, and the relationship between them. This normal form is useful for data integration tasks but there exists no obvious approach for producing meaningful names for each of these tables. Currently these names are manually assigned by the database designer.

Our work makes the following contributions:

1. (1)

A semantic embedding of database column names 2. (2)

A method for using these embeddings to assign meaningful names to tables containing a given set of column names 3. (3)

A metric for evaluating generated table names which shows the usefulness of our prediction.

2. Related Work

One of the central motivations behind this work comes from recent progress that has been made in the area of natural language processing (NLP) due to the use of distributed representations. Distributed representations, or embeddings, refer to the mapping of input examples to vectors of values, of possibly lower dimensionality than the input itself. These embeddings are usually produced through the use of an artificial neural network (ANN). Each element in one such vector is not necessarily associated with one particular concept, feature, or object, but rather works in tandem with the other elements in the same vector to represent a set of features or concepts that describe the input itself. The Word2vec family of models, i.e., skip-gram and continuous bag of words, have yielded some of the most widely-used embeddings in NLP, where a simple feed-forward ANN language model is trained on a large collection of documents. The internal weight vectors, which each map to a particular token, are then used in some subsequent predictive language task, e.g., text classification or chunking.

Other variants have been proposed since Word2vec’s initial public release, such as GloVe (Pennington et al., 2014), ELMo (Peters et al., 2018), and BERT (Devlin et al., 2018). Interestingly enough, these representations can be composed into representations of phrases and sentences, by averaging, summing, or concatenating the embeddings for each of the constituent words, yielding a possible distributed representation of the phrase/sentence itself. Embeddings have found use in domains even outside of NLP, such as in graph/network representation (Grover and Leskovec, 2016; Narayanan et al., 2017), including entity resolution (Ebraheem et al., 2017), concept modeling (Li et al., 2016; Moody, 2016; Sherkat and Milios, 2017) and data curation (Thirumuruganathan et al., 2018). In short, distributed representations have facilitated the construction of a useful, alternative means of comparing, aggregating, and manipulating the fundamental elements of a data type.

In this work, for tables themselves, we hypothesize that the same potential for discovering aggregated embeddings exists as well – for each column name, we could find its particular distributed representation, and, after examining the entire set of column names, we could compose a plausible distributed representation of the entire table itself by applying some aggregation operation to the constituent embeddings. Furthermore, these aggregate table embeddings could be used in the generation of plausible names for the tables themselves. In this work, we will propose two possible ways in which this might be implemented and empirically explore the effects of one of these.

3. Models for Table Name Generation

In this section, we will describe two architectures, one simple and one complex, for generating table names. We will then provide some experimental results for the first model in this study.

3.1. Model # 1

There are two main steps in this approach to generating database table names. First, we produce a word embedding for each table and column name as discussed in Section 3.1.1. Then, as discussed in Section 3.1.2 we use the vectors generated for each column name in a table to predict a meaningful name for the table.

3.1.1. Embeddings for Database Columns

To generate word embeddings for Column2Vec, we make use of fastText (Bojanowski et al., 2017). This is primarily because fastText allows embeddings to be generated for terms which are outside of the original vocabulary the model was trained on. This is important in our setting since table and column names contain significant variation and may not appear as an exact match in any existing data. For example, approximately half of the table names we see in the data set used for our preliminary results in Section 4 appear only once.

To train the fastText model, we first construct documents based on the names of tables and columns of known database schemas. For example, one document might consist of the string "authors authorID firstName lastName". Training on a collection of such documents allows us to generate embeddings, or word vectors for each of the table and column names in our training set. In addition, fastText also enables us to generate word vectors for terms outside of this vocabulary by looking at subword information.

3.1.2. Table Name Generation

For our generation task, we aim to take as input a list of column names and assign the most meaningful table name from tables observed in our training set. Our prediction is based on comparing the similarity of the word vectors for each column vectors. Since we want to incorporate semantic context from all column names, we need to combine the individual word vectors generated for each column. Mikolov et al. (Mikolov et al., 2013) showed that the summation of word vectors is most likely to produce a vector for a term which is semantically similar to the composition of each term. In our case, we expect the sum of the word vectors associated with a set of columns will produce a vector which is close (in vector space) to a word vector representation of a viable table name.

To produce a table name that might be suitable for the given set of columns, we use the nearest neighbor based on cosine similarity. That is, after summing the individual column vectors, we select the table name which corresponds to the word vector most similar to this summed vector. Currently our model considers/examines only the single closest vector. However, in future work, we intend to investigate the case where $k$ nearest closest neighbors are considered for further evaluation.

3.2. Model # 2

Another approach we propose is based on a recurrent neural architecture that learns a generative model of table names, conditioned on a distributed representation of table metadata. A graphical depiction of a basic version of this model can be found in Figure 1.

The generative model can be decomposed into three general modules: 1) a column name encoder/processor, 2) a table representation aggregation function, and 3) a conditional name generator. All three functions could be as simple as feed-forward network models or as complex and powerful as a gated recurrent neural network (RNN), such as Long Short Term Memory (Hochreiter and Schmidhuber, 1997) or $\Delta$-RNN (Ororbia II et al., 2017). We envision the column name encoder and table name generator to operate at the character symbolic level (or the subword level) to circumvent the problem of an incredibly high-dimensional input/output space that word-level models have to face.

The column name encoder can be a recurrent network that builds a stateful representation of a single column by iteratively processing the underlying characters that compose the word or phrase used to label the column name itself. This column name encoder would be shared across column names to drastically reduce the number of parameters required in our model and allow it to easily handle a variable number of columns. A more difficult, but perhaps quite fruitful, extension of this encoder would be to have it process the data values associated with a particular column name as well. This encoder would be applied to each column name and output a set of $K$ column name representations.

The $K$ embeddings produced by the column name encoder would then be run through an aggregation function, which could itself be a nonlinear multilayer perceptron (MLP) or a simple function such as averaging or (weighted) summation. This function’s primarily role is to create a single, fixed-length representation of a set of column names, or rather, the table of interest itself. This table representation would then be used to guide a generative model of text, which could be a simple RNN as depicted in Figure 1. By focusing on a character or subword-level RNN generative model, we can naturally handle names of variable length and even potentially generate non-standard symbols (such as alphanumeric strings). The types of names that would be generated by this model would largely depend on the table dataset used to construct the overall model, however, if large enough, the model might be able to produce some interesting, creative table names or even a set of candidates that the human user would finally select from and/or error-correct (providing additional feedback to the model).

Parameters of the entire end-to-end model could be learned using reverse-mode differentiation to optimize an objective such as the negative log likelihood of the name text in the training set. Since the entire system is soft, or rather, makes use of differentiable nonlinear transformations, calculating gradients would not be difficult, though the path of credit assignment could potentially be long depending on how long some table names and column names are (since in RNN structures, the model parameters are shared over time-steps, so in the case of characters, we apply the RNN parameters once per character).

4. Preliminary results

To examine the effectiveness of our first model, we used a set of table schemas (column and table names) collected from a crawl of open source repositories on GitHub (Hoffa, 2016). Files in all repositories were checked for the syntactically valid CREATE TABLE statements and the table and column names were extracted from these statements. This resulted in a total of $436,545$ tables combined with the names of each column in the table. We leave implementation and evaluation of our second model as future work.

4.1. Data cleaning

The dataset pulled from GitHub contains a significant amount of test and dummy data which is not useful for our problem. For example, a table with the name bb and columns col1 and col2 would be discarded. We implemented a simple set of rules to filter the extracted information: (1) trigrams appearing in table or column names appearing only once, eliminating random entries, (2) names with special characters, (3) names with a large number of digits, and (4) names which consist of only two repeated characters (e.g., bb).

4.2. Model training

The data was split into 90% training data with 10% reserved for testing. Word vectors were trained using the fastText skip-gram model with each document consisting of the table name and associated column names. We performed hyper-parameter optimization using the tree-structured Parzen estimator approach described by Bergstra et al. (Bergstra et al., 2011) on a subset of the data. A k-nearest neighbors model was then trained using the table name word vectors (in the training set) to generate table names as described in Section 3.

4.3. Table name quality

Table names may consist of multiple words concatenated together. A table storing blog posts may be called blogposts, but we might consider a suggestion of the name posts to be a reasonable substitute. We want to be able to identify how well the components of our predicted names match the original. To identify the components of a name, we first attempt to split names into words via a dynamic programming approach that aims to infer the position of spaces by attempting to maximize the probability of individual word frequencies (Anderson, 2018). This is based off of a sample of words from the English Wikipedia corpus.

To evaluate the quality of the generated table names, we define a metric based on the $F_{1}$ score (Chinchor, 1992) which combines precision and recall. We use the words split from the table names as mentioned above when calculating the $F_{1}$ score. However, we also want words which are semantically similar to rank high. For example, the name books may a suitable alternative to the name library. To capture these relationships, we use the path similarity from WordNet::Similarity (Pedersen et al., 2004). When computing precision and recall, instead of using intersection between the predicted and original names, we calculate fuzzy precision and recall using this WordNet similarity metric.

Figure 2 shows the cumulative distribution of the evaluation metric. Approximately one third of the predicted table names evaluate to zero. Of these, roughly 60% have the same two predicted tables, vtp and defaultrequiredlengthncharcolumns. Neither of these are semantically meaningful. Further preprocessing to remove names with limited semantic value will likely improve this result. We also see that approximately 1% of the values have the highest possible score of 1.0. In this case, names differed just in pluralization and punctuation, such as recipe_ingredients and recipeingredient. We provide examples of results which fall between these two extremes in the following section.

4.4. Table Name Samples

Table 1 gives examples of the original names assigned to tables, i.e., the ground truth, as well as the predicted name and the value for our evaluation metric.

Although the examples above suggest that our metric is useful, in the future we intend to perform a more thorough evaluation to determine whether this metric correlates with human judgment.

To better demonstrate our generation/evaluation process, consider predicting a name for a table with columns id, calendarid, name, eventdate, and locutionid. We generate word vectors for each of these columns and sum them. When searching for the table name with the closest word vector, we find eventdates.

The original name given to this table was holidaydates. To evaluate this according to our metric we first split each name into words giving [holiday, dates] and event, dates]. Based on the WordNet path similarity, holiday and event have a similarity of 0.14. Since dates matches exactly, we end up with precision and recall values of $P=R=(1.14/2)=0.57$. Our final metric value is then $2PR/(P+R)=0.57$. Note that in this case, we have no indication based on column names that the table specifically refers to holidays. In the future, we will explore other sources of contextual information which may help produce more accurate results.

5. Conclusions and Future Work

Generating meaningful names for tables given only constituent column names is a challenging problem. Our approach, based on distributed representations, is able to generate meaningful table names given the names of the columns contained in the table. While our metric does prove useful, additional work is needed to compare it properly against human judgment.

We believe that incorporating information on the data stored in these columns (e.g. data type and value distribution) will make this representation even more useful. In addition to generating tables names, such a representation would likely benefit other data integration tasks, e.g., deciding which tables in a large set may be meaningfully joined together.