Query

Discovering the schema and querying the provenance database with Cypher or read-only SQL.

Discover the schema

List the database’s tables and columns, or validate a reference, with query catalog before writing a query.

APIs: query:catalog

Before writing a query you need to know what is there. query catalog lists every table in the experiment’s database and its columns — the same schema both query graph and query sql see:

$ ./exp.sh query catalog
table jobs (command: submit)
  column id (TEXT)
  column state (TEXT)
  column hostnames (TEXT)
  column nodes (INTEGER)
table runs
  column id (TEXT)
  column app (TEXT)
  column procs (INTEGER)
table metadata
  column key (TEXT)
  column value (TEXT)
table __provenance__
  column source_id (TEXT)
  column target_id (TEXT)
  column edge_type (TEXT)

Each column is annotated with its SQL storage type (TEXT/INTEGER/ REAL), and every table that a command records under a different name is annotated with its owning command — here jobs is written by submit, so a query may label the node either way (see Query the provenance graph).

Pass a reference with --ref to narrow the listing to one table, or to validate a single TABLE.COLUMN:

$ ./exp.sh query catalog --ref jobs
$ ./exp.sh query catalog --ref jobs.procs

A TABLE.COLUMN reference exits non-zero when the table or column does not exist, which makes query catalog --ref a cheap way to check a column name from a script before building a larger query. query catalog is read-only and, like the rest of query, needs a bootstrapped experiment.

Query the provenance graph

Run read-only Cypher over the provenance graph with query graph — nodes are tables, edges are call and used_by relationships.

APIs: query:graph, knit_as

query graph runs a read-only Cypher query against the provenance database (transpiled to SQL by the bundled knit-cypher-to-sql). A node is a row, labelled by its table; an edge is a __provenance__ relationship — call (a command invoked another) or used_by (a setup was consumed by a later command). Pass the statement with --exec:

$ ./exp.sh query graph --exec \
    "MATCH (m:montecarlo)-[:call]->(r:runs) RETURN m.id, r.procs"

Labels are the table names from Discover the schema; a command that records under a different table name can be written either way (submit or jobs), because knit hands the transpiler the live name map. Backtick-quote a label that contains a colon: (s:`setup:libs`).

Edges carry the columns from __provenance__, including the optional alias set with knit_as (see Distinguish repeated calls), so repeated calls can be told apart:

$ ./exp.sh query graph --exec \
    "MATCH (m:montecarlo)-[e]->(r:runs) WHERE e.alias = 'fast' RETURN r.procs"

Two flags help while you build a query: --explain prints the transpiled SQL instead of running it (hand it to query sql to tweak), and --ast prints the parsed syntax tree without touching the database. Shape the result with the shared --format / --header / --separator options described in Run raw SQL; anything after a trailing -- is forwarded to knit-cypher-to-sql verbatim. When a construct falls outside knit-cypher-to-sql’s Cypher subset, drop to query sql.

Run raw SQL

Run a read-only SQL query with query sql, and shape any query’s output with the shared –format, –header, and –separator options.

APIs: query:sql

When Cypher is more than you need — or less than you need — query sql runs a read-only SQL statement directly against the experiment database. Every recorded table is a plain SQL table (see Discover the schema), so joining across the __provenance__ edges is just a join:

$ ./exp.sh query sql --exec "SELECT id, procs FROM runs ORDER BY procs DESC"
$ ./exp.sh query sql --exec \
    "SELECT m.pi FROM montecarlo m
       JOIN __provenance__ e ON e.source_id = m.id AND e.edge_type = 'call'
       JOIN mcrank a ON a.id = e.target_id WHERE a.rank = 0"

Only read-only statements are accepted — the query must start with SELECT/WITH/EXPLAIN/PRAGMA and contain no write keyword — so a query can never mutate the provenance database.

Shaping the output is the same for query sql and query graph. Three options control it:

$ ./exp.sh query sql --format json  --exec "SELECT id, procs FROM runs"
$ ./exp.sh query sql --format csv --header --exec "SELECT id, procs FROM runs"
$ ./exp.sh query sql --separator $'\t' --exec "SELECT id, procs FROM runs"

• --format picks the output mode: list (the default, one script-friendly row per line), json, csv, box, markdown, column, and the other sqlite modes.

• --header adds a header row. It is off by default, because query output is most often piped into another tool where a header is noise.

• --separator sets the column separator (defaults to the backend’s own).

list output with no header makes query sql easy to capture in a script: procs="$(./exp.sh query sql --exec 'SELECT procs FROM runs LIMIT 1')". Like all of query, it needs a bootstrapped experiment.

Query across platforms

Query the current database together with other platforms’ databases at read time with –extra, and tag every row by the platform it ran on.

APIs: query:graph, query:sql

Running the same experiment on several platforms leaves one single-platform .knit/knit.db per machine. --extra queries the current database together with those other databases at read time, without merging any of them: knit assembles a throwaway read-only union (a lens), runs the query, and discards it. Each source database stays single-platform, and so does any bundle made from it.

--extra takes a comma-separated list; each source is a directory (its .knit/knit.db is used), a database file, or a bundle (a .tar.gz from knit bundle, whose database is extracted to a temporary directory):

$ ./exp.sh query graph --extra ../run-on-pbs --exec \
    "MATCH (p:platform)-[:executed]->(j:jobs) RETURN p.id, j.id, j.state"
$ ./exp.sh query sql --extra ../run-on-pbs/.knit/knit.db,polaris-bundle.tar.gz \
    --exec "SELECT id, state FROM jobs"

The current experiment’s own database is always part of the lens; --extra adds the others. With no --extra the lens spans just the current database, so results match a single-database query — and the platform node below is still present, so (p:platform) works even against one database.

The platform is a node. Inside the lens every database contributes a platform node whose properties are that machine’s fingerprint (arch, scheduler, launcher, profile, knit_version), and an executed edge from that platform to every row that ran on it. So the platform is one flat hop from any command, and machine attributes filter and project like any other column:

$ ./exp.sh query graph --extra ../run-on-pbs --exec \
    "MATCH (p:platform)-[:executed]->(j:jobs)-[:used_by]->(s:setups)
       WHERE p.arch = 'aarch64' RETURN p.id, j.id"

Nothing about the platform is stored in any database for this — it is synthesized from each database’s own metadata at query time (the platform name comes from knit bootstrap --platform), so the platform node works on existing databases and even on a single-database query.

Because the query spans one lens, aggregation, ORDER BY, DISTINCT, and count are correct across every platform at once — which a shell loop that ran the query per database and concatenated the output would get wrong:

$ ./exp.sh query sql --extra ../run-on-pbs --format csv --header --exec \
    "SELECT p.id AS platform, count(*) AS runs
       FROM platforms p
       JOIN __provenance__ e
         ON e.edge_type='executed' AND e.source_id=p.id
       JOIN runs r ON r.id=e.target_id
       GROUP BY p.id"

If two databases claim the same platform name with a different fingerprint, knit warns and keeps both (nothing is dropped). The project name is deliberately not checked — a platform may be named differently per machine. All of --extra is read-only and, like the rest of query, needs a bootstrapped experiment. Shape the output with the shared --format / --header / --separator options from Run raw SQL; write labels as either the table or command name as in Query the provenance graph.

Trace an artifact back to its producer

Recover which invocation produced a file by walking the produced edge from its artifacts row back to the producing command.

APIs: query:graph, query:sql, knit_with_output_artifact

Each artifact is recorded as one row in the artifacts table (its path, name, type, checksum, and result), linked to the invocation that made it by a produced provenance edge — not as a column of the producing command’s own table (see Declare and bind an artifact). So which invocation produced this file? is a reverse walk of that edge, keyed on the artifacts-relative path.

With query graph the walk is a Cypher match. The producing node needs no label — read the producer off the edge, so the same query works no matter which command made the file:

$ ./exp.sh query graph --format column --header --exec \
    "MATCH (t)-[e:produced]->(a:artifacts)
       WHERE a.path = 'table.csv'
       RETURN e.source_name, e.source_id"

e.source_name is the producing command and e.source_id is its invocation id, which joins back to that command’s own recorded row for the rest of the provenance (its parameters, its call edge to a parent, and so on).

The same lookup in query sql joins the artifacts row to the __provenance__ edge on the artifact id:

$ ./exp.sh query sql --format column --header --exec \
    "SELECT p.source_name, p.source_id
       FROM artifacts a
       JOIN __provenance__ p ON p.target_id = a.id AND p.edge_type = 'produced'
      WHERE a.path = 'table.csv'"

Both directions are open: forward (RETURN a.path for a given producer, or a join the other way) lists every file an invocation produced. See Query the provenance graph for the Cypher subset and Run raw SQL for the shared --format / --header options.

Walk an artifact’s full lineage

Walk producer –produced–> artifact –used_by–> consumer in one query by joining the two provenance edges on the artifact’s row.

APIs: query:graph, query:sql, knit_with_input_artifact, knit_with_output_artifact

An artifact sits between the command that made it and the commands that read it. The producer leaves a produced edge into the artifact’s row (see Trace an artifact back to its producer); each consumer leaves a used_by edge out of it (see Consume an artifact by kind). Because both edges meet at the same artifacts node, one query walks the whole chain producer --produced--> artifact --used_by--> consumer.

With query graph the chain is a single Cypher path through the artifact node, keyed on its artifacts-relative path:

$ ./exp.sh query graph --format column --header --exec \
    "MATCH (p)-[pr:produced]->(a:artifacts)-[ub:used_by]->(c)
       WHERE a.path = 'table.csv'
       RETURN pr.source_name, a.kind, ub.target_name"

The producing and consuming nodes need no label — name them off the edges (pr.source_name, ub.target_name), so the same query works whatever commands sit at the ends. To list everything that read a given artifact, keep only the used_by half (RETURN ub.target_name); to list every artifact a command consumed, match the used_by edge into it.

The same walk in query sql joins the two __provenance__ edges to the artifacts row on the artifact id:

$ ./exp.sh query sql --format column --header --exec \
    "SELECT pr.source_name AS producer, ub.target_name AS consumer
       FROM artifacts a
       JOIN __provenance__ pr ON pr.target_id = a.id AND pr.edge_type = 'produced'
       JOIN __provenance__ ub ON ub.source_id = a.id AND ub.edge_type = 'used_by'
      WHERE a.path = 'table.csv'"

See Query the provenance graph for the Cypher subset and Run raw SQL for the shared --format / --header options.