CHiSEL: a user-oriented framework for simplifing database evolution

Abstract

In order to conduct research effectively, scientists must be able to access, organize, describe, and produce data as part of their daily research activities. While relational databases are well suited to the tasks of describing and organizing scientific metadata and results, the difficulties of using relational database management systems effectively, have resulted in their limited adoption among scientists. In addition, scientific research is changing steadily with new experimental protocols, instruments, and discoveries that determine what data are generated and how they must be described and organized according to a relational schema. Unfortunately, evolving a schema is one of the most difficult aspects of database usage. The conventional data definition and manipulation languages offer relatively low-level programming abstractions to perform complex database evolution tasks, and therefore require specialized technical skills not possessed by most scientists. A simplified means of expressing database evolution operations would reduce the effort for non-expert users of databases. This paper presents a high-level, user-oriented, schema evolution framework built on a formal algebra of schema modification operators. The approach allows introduction of novel operators as motivated by new requirements and is amenable to well established optimization techniques for efficient planning and execution. We also propose a rigorous evaluation methodology for comparing the user effort of database evolution languages, and we introduce a benchmark for evaluating the execution efficiency of schema evolution expressions. We present the framework and its implementation, and we demonstrate its utility in exemplar use cases and a performance evaluation.

Appendix: Approximating CHiSEL SMOs in SQL

First the SMOs that take the form of conventional relational operators have clear equivalence between CHiSEL and SQL. For select, the CHiSEL expression takes the form of t.select().where(...) where ‘t’ is a table name in the database and ‘...’ a placeholder for the conditional formula to restrict the tuples in an optional where clause. SQL clearly takes the form SELECT * FROM t WHERE .... In both languages, the cost of the operation per Rubric R2 is 1. project also takes a similar form of t.select(a, b, c, ...) and SELECT a, b, c, ... FROM t respectively, where “a, b, c, ...” are the column names of t to be projected. Per Rubric R3, these implicit operations incur a cost of 1. rename is just slightly different as usual t.select(b=a) and SELECT a AS b FROM t respectively, and again per Rubric R3 incur a cost of 1. join in CHiSEL is expressed as t.join(s).where(...) using ‘s’ as another named table in the database, and similarly in SQL it is expressed as SELECT * FROM t, s WHERE ... or SELECT * FROM t JOIN s ON ... and per Rubric R2 these incur cost of 1 in either language. union is expressed as expression1 + expression2 in CHiSEL or expression1 UNION expression2 in SQL, and again applying Rubric R2 both forms incur a cost of 1. Finally, distinct is expressed within CHiSEL composite formulas and the user does not need to directly invoke the operations, while certain expressions formed in SQL will require the use of the DISTINCT keyword at a cost of 1 according to Rubric R2.

The similarityjoin, deduplicate, nest, and unnest operations are not directly exposed in the CHiSEL language as they are only required through the use of composite operators in the language. In order to approximate these operations in SQL expressions, the first three of these would depend on a hypothetical but plausible SIMILAR(...) function that performs non-trivial similarity measures beyond the simple pattern matching LIKE comparison or common regular expression operators available in many database management systems. While this function does not exist in standard SQL, most commercial and open source database management systems provide functions for performing string or word similarity comparisons based on edit distance or other fuzzy matching algorithms. Per Rubric R4, the usage of such functions incurs a cost of 1. Thus similarityjoin could be expressed as:

with a cost of R2 + R4 for a total cost of 2. deduplicate and nest, however, require inherently more complex expressions to approximate in SQL. deduplicate could be approximated in SQL using a similarity self-join, aggregating the resulting relation, and returning the first unique value, as in the following expression:

where the innermost expression costs 3 $\times$ R2 + R3 + R4 for a subtotal of 5, plus the next most inner expression costs 2 $\times$ R2 + R3 + R4 for a subtotal of 4, plus the outer expression consists of R2 + R3 for a subtotal of 2, and therefore the whole expression comes at an overall cost of 11. Likewise, nest is essentially the same expression but with additional projections and aggregations of the nested elements of the inner expressions, such as:

which adds an additional aggregate function and per R4 raises the total cost for the expression to 12. The unnest operation would require a function to unpack an array into separate tuples (e.g., unnest function in PostgreSQL) and a built-in or user-defined function to take unstructured input and convert it into an array (e.g., string_to_array also from PostgreSQL could convert strings to arrays but UDFs would be required to unpack messier values into discrete units) in an expression such as:

and by applying Rubric R2, R3, R4, and R5 comes at a cost of 4. These are of course only approximations, because there may be many ways to achieve the same operations in different but equivalent expressions. We have attempted here to provide the relatively straightforward implementation one might use to achieve the respective SMOs.

Next, we turn our attention to the SMOs that in the CHiSEL formulation are “composites” of other operations. copycol is expressed by joining the target table t with the source table s and then projecting all of the columns t.* along with the addition of the desired column s.a from the source table s. We can use the following expression for this:

and it has a cost of 2 $\times$ R2 + R3 for a total of 3. reify is expressed by forcing set semantics on a table t based on the distinct columns a1, ..., aN to be projected as the key columns of the new relation along with a subset of desired columns b1, ..., bM from a table t.

and by 2 × R2 + R3 has a total cost of 3. $\texttt {Reify}^{\texttt {Sub}}$ is expressed by projecting the key of a table t along with a subset of desired columns a1, ..., aN. To approximate the respective SMO, the expression would depend on a user-defined function KEY() to infer the primary key columns from the given table. The following example:

consists of R2 + R3 + R5 for a total cost of 3. To approximate align, we begin by assuming a table t with a column aN that we wish to replace with a “canonical” term in column b of table s based on a fuzzy similarity match between the target and canonical columns. To do so, we would perform a similarity join on t.aN and s.b and then project all of the columns in t without aN (denoting the column just before the Nth term as aN_minus_1, for illustration but of course the formula need not be limited to such a sequence of columns) and instead project out s.b. We get the following example expression:

and it requires 2 $\times$ R2 + 2 $\times$ R3 + R4 for a total cost of 5.

The final set of SMOs to be discussed can be formulated mostly from combinations of the expressions described above. atomize is a composite of unnest and $\texttt {Reify}^{\texttt {Sub}}$ and by combining their respective SQL expressions comes at a combined cost of 7. domainify builds on deduplicate by deduplicating a single column and renaming it, adding cost per R3, therefore raising the total cost of the expression to 12. canonicalize begins with a sub-expression to project a target column twice, such as SELECT a AS term, a AS synonyms FROM t and then nests synonyms. Thus the cost is that of nest plus 1 per R3 for the renaming to bring the total cost to 12. tagify is a composite of align and atomize and by combining their respective SQL expressions yields a total cost of 12.

Finally, we return to the cost evaluation for the usage of CHiSEL expressions beyond the basic operations evaluated above. In general, the CHiSEL syntax exposes methods corresponding to SMOs in idioms of its host language. For example, to atomize a column, the CHiSEL expression is t.columns[ ‘a’ ].to_atoms(). Similarly, domainify, canonicalize, align, and tagify are expressed through methods to_domain(), to_vocabulary(), align( domain ) and tagify( domain ), respectively. These operations per R2 each come at a cost of 1. reify and $\texttt {Reify}^{\texttt {Sub}}$ are exposed via table-level methods. reify as in t.reify({ ... key columns ...}, { ... non-key columns ... }) and $\texttt {Reify}^{\texttt {Sub}}$ as in t.reify_sub( ... non-key columns ... ). The expressions include an additional expense for the projections per R3 and therefore have total cost of 2 each. The costs of the CHiSEL expressions is summarized along with the corresponding expressions in SQL in Table 5.