Swift Provenance Database

Swift can be configured to add both prospective and retrospective provenance information to its log files. The provenance extraction mechanism processes these log files, filters the entries that contain provenance data, and exports this information to a relational database. Each application execution is started by a wrapper script that sets up the execution environment. We modified these scripts to also gather runtime information, such as memory consumption and processor load. Additionally, one can define a script that generates annotations in the form of key-value pairs, to be executed immediately before the actual application. These annotations can be exported to the provenance database and associated with the respective application execution. The data logged by each wrapper is processed to extract both the runtime information and the annotations, storing them in the provenance database. Additional annotations can be generated per script run using ad-hoc annotator scripts. In addition to retrospective provenance, Swift Provenance Database keeps prospective provenance by recording the Swift script source code, the application catalog, and the site catalog used in each script run.

During the Third Provenance Challenge, we observed that expressing provenance queries in SQL is often cumbersome. For example, such queries require extensive use of complex relational joins which are beyond the level of complexity that most domain scientists are willing, or have the time, to master and write. Swift Provenance Query Language, SPQL for short, was designed to allow for easier design of provenance queries for common query patterns than can be accomplished with general purpose query languages, such as SQL. In the query interface, every SPQL query is translated into a SQL query that is processed by the underlying relational database. While the syntax of SPQL is by design similar to SQL, it does not require detailed knowledge of the underlying database schema for designing queries, but rather only of the entities in a simpler, higher-level abstract provenance schema, and their respective attributes.

The basic building block of a SPQL query consists of a selection query with the following format:

Where the optional parts are within parentheses. As in the relational data model, every query or built-in function results in a table, to preserve the power of SQL in querying results of another query. Selection queries can be composed using the usual set operations: union, intersection, and difference. A select clause is a list with elements of the form <entity set name>(.<attribute name>) or <built-in function name>(.<return attribute name>). If attribute names are omitted, the query returns all the existing attributes of the entity set. SPQL supports the same aggregation, grouping, set operation and ordering constructs provided by SQL.

To simplify the schema that the user needs to understand to design queries, we used database views to define the higher-level schema presentation shown in [provdb_schema_summary]. This abstract, OPM-compliant provenance schema, is a simplified view of the physical database schema detailed in section. It groups information related to a provenance entity set in a single relation. The annotation entity set shown is the union of the annotation entity sets of the underlying database, presented in Figure. To avoid defining one annotation table per data type, we use dynamic expression evaluation in the SPQL to SQL translator to determine the required type-specific annotation table of the underlying provenance database.

To simplify query design, we included in SPQL the following built-in functions to make these common provenance queries easier to express:

The underlying SQL implementation of the ancestor built-in function, below, uses recursive Common Query Expressions, which are supported in the SQL:1999 standard. It uses the prov\_graph database view, which is derived from the dataset\_in and dataset_out tables, resulting in a table containing the edges of the provenance graph.

To further simplify query specification, SPQL uses a generic mechanism for computing the {em from} clauses and the join expressions of the where clause for the target SQL query. The SPQL to SQL query translator first scans all the entities present in the SPQL query. A shortest path containing all these entities is computed in the graph defined by the schema of the provenance database. All the entities present in this shortest path are listed in the from clause of the target SQL query. The join expressions of the where clause of the target query are computed using the edges of the shortest path, where each edge derives an expression that equates the attributes involved in the foreign key constraint of the entities that define the edge. While this automated join computation facilitates query design, it does somewhat reduce the expressivity of SPQL, as one is not able to perform other types of joins, such as self-joins, explicitly. However, many such queries can be expressed using subqueries, which are supported by SPQL. While some of the expressive power of SQL is thus lost, we show in the sections that follow that SPQL is able to express, with far less effort and complexity, most important and useful queries that provenance query patterns require. For example, this SPQL query returns the value of the parameter proteinId per script run that consumed a file named nr:

This SPQL query is translated by Swift Provenance Database to the following SQL query:

Further queries are illustrated by example in the next section. We note here that the SPQL query interface also lets the user submit standard SQL statements to query the database.

Swift Provenance Database depends on PostgreSQL, version 9.0 or later, due to the use of Common Table Expressions for computing transitive closures of data derivation relationships, supported only on these versions. The file prov-init.sql contains the database schema, and the file pql_functions.sql contain the function and stored procedure definitions. If the user has not created a provenance database yet, this can be done with the following commands (one may need to add "-U username" and "-h hostname" before the database name "provdb", depending on the database server configuration):

The file etc/provenance.config should be edited to define the database configuration. The location of the directory containing the log files should be defined in the variable LOGREPO. For instance:

The command used for connecting to the database should be defined in the variable SQLCMD. For example, to connect to CI’s PostgreSQL? database:

The script ./swift-prov-import-all-logs will import provenance information from the log files in $LOGREPO into the database. One can use ./swift-prov-import-all-logs rebuild to reinitialize database before importing provenance information.

To enable the generation of provenance information in Swift’s log files and to trasfer wrapper logs back to the submitting machine for runtime behavior information extraction the options provenance.log and wrapperlog.always.transfer=true should be set to true in etc/swift.properties:

If Swift’s SVN revision is 3417 or greater, the following options should be set in etc/log4j.properties:

Run MODIS:

After running MODIS, one can import provenenace information into the database using:

Connect to the provenance database:

List runs that were imported to the database:

List the datasets and function calls from which the dataset dataset:20121026-1146-jng6bir4:720000001604 was derived: