Query-language reference
A RhyDB query is a pipeline of operations that starts from a table name and produces a table of result rows.
Query structure
data
.filter(country = 'Switzerland')
.group(by := {pangoLineage}, aggs := {count := count()})
Method syntax is equivalent to passing the value on the left as the first function argument. Named arguments use :=. After a named argument, all remaining arguments must also be named.
Tables
The table holding the sequences and their metadata is named data. Further tables are queried like any other table:
reference_genomesis a built-in table that every database contains. It holds the reference sequences, one row per sequence, with the string columnsname,type(nucleotideoramino_acid) andsequence.lineage_definitionsis a built-in table with one row per lineage column. Its string columns arecolumn_nameanddefinition, which holds the column’s lineage definition file verbatim.- Every lineage column gets a lineage relation table named after the column. It holds one row per direct edge of the lineage tree, with the child in
lineageand its direct parent inparent(null for roots), as well asid,is_recombinant_edgeandrecombinant_clade_ancestor. Only canonical lineage names appear in it. - Every lineage column also gets an alias table named after the column with the suffix
_aliases, such aspangoLineage_aliases. It holds one row per alias, with the alias inaliasand the canonical lineage it stands for inlineage.
tables() lists the tables of an instance.
Literals
| Type | Syntax | Example |
|---|---|---|
| String | single-quoted | ‘Switzerland’ |
| Integer | bare number | 42 |
| Float | decimal | 3.14 |
| Boolean |
| true |
| Null | null | null |
| Date | ‘YYYY-MM-DD’::date | ‘2024-05-15’::date |
| Set | {value, …} | {‘A’, ‘B’} |
| Record | {name := value, …} | {label := ‘A’, n := 3} |
Operators
Boolean expressions use && (and), || (or), and ! (not). Parentheses control grouping.
A comparison takes a column identifier on one side and a literal on the other. The column may be on either side, so age > 30 and 30 < age are the same filter.
| Operator | Meaning | Column types |
|---|---|---|
= | equals | all |
<> | not equals | all |
| ordering | int32, int64, float, date, and string (lexicographic) |
country = 'Germany' && age >= 18
!(date < '2024-01-01'::date)
main[23403] = 'G'
A boolean column can serve as a predicate on its own. data.filter(isHuman) means isHuman = true, and data.filter(!isHuman) is its complement. Only boolean columns may be written this way; a bare reference to a column of another type is rejected.
A null cell never matches a comparison, <> included, so country <> 'Germany' leaves out the rows whose
country is null. ! is a set complement rather than SQL’s NOT, so !(country = 'Germany') does return those
rows. Comparing against the null literal is an error; use
isNull() or isNotNull()
to test for missing values.
Pipeline operations
filter(condition)
Keep the rows of the preceding result for which the condition is true. A filter can follow any operation,
such as a group, a map that derives the filtered column, a join, or
schema().
A filter that applies to the rows of a table, directly or through project, a deterministic
order or unionall, supports every predicate. On other results, a predicate can
combine comparisons, boolean columns, and isNull() with && and
||.
data.filter(country = 'USA' && age >= 18)
data.group(by := {country}, aggs := {count := count()}).filter(count > 1000)group(by, aggs)
Aggregate rows. by is the set of grouping expressions, and the empty set
{} aggregates all rows into a single group. aggs is a record of named
aggregates. The result has one row per group with the grouping columns and the aggregates; a grouping
expression other than a column is named after itself, and later operations refer to it with a double-quoted
identifier, such as “S[501]”. Rows with a null grouping value form their own group.
| Aggregate | Result |
|---|---|
count() | The number of rows in the group, as an int64. |
count(column) | The number of rows in the group whose value in the column is not null, as an int64. |
sum(column) | The sum of a numeric column. Integer columns sum to an int64 and float columns to a float. Null values are skipped; a group without any non-null value sums to null. |
| The smallest or largest value of a numeric, date, string, or boolean column, with the type of the column. Null values are skipped; a group without any non-null value is null. |
data.group(by := {}, aggs := {count := count()})
data.group(by := {country, pangoLineage}, aggs := {count := count()})
data.group(by := {country}, aggs := {count := count(), totalAge := sum(age)})
data.group(by := {country}, aggs := {withDate := count(date), first := min(date), last := max(date)})
data.group(by := {S[501], date.isoWeek()}, aggs := {count := count()})
.order(by := {"isoWeek(date)", "S[501]"})project(expressions)
data.project({primaryKey, country, date})projectout(remove)
Return all columns except the ones in the given set, in their original order. Every named column must exist, and at least one column must remain.
data.projectout({main, unaligned_main})map(expressions)
Add or replace columns using name-and-value assignments. Values may be literals, columns, or scalar functions.
data.map({cohort := 'A', copiedCountry := country, week := date.isoWeek()})order(by)
Sort by a set of keys, each a bare column name (ascending) or an asc/desc expression.
data.order(by := {date.desc(), primaryKey})limit(count)
data.limit(100)offset(count)
data.order(by := {primaryKey}).offset(100).limit(100)randomize([seed := n])
data.randomize(seed := 42).limit(10)join(left, right, on [, type := kind])
Combine two pipelines by equality between columns. Multiple equalities may be joined with
&&. The default type is inner. The two inputs must use disjoint column
names. A filter after a join applies to the joined rows; apply sequence predicates such as
hasMutation() to an input pipeline instead.
| Type | Kept rows | Output columns |
|---|---|---|
inner | Matching pairs only (default) | Left and right |
left | All left rows; right columns are null-filled when unmatched | Left and right |
right | All right rows; left columns are null-filled when unmatched | Left and right |
full | All rows from both sides; the unmatched side is null-filled | Left and right |
leftSemi | Left rows with a match | Left only |
rightSemi | Right rows with a match | Right only |
leftAnti | Left rows without a match | Left only |
rightAnti | Right rows without a match | Right only |
data.group(by := {pangoLineage}, aggs := {countWorld := count()})
.join(
data
.filter(country = 'Spain')
.group(by := {pangoLineage}, aggs := {countSpain := count()})
.map({pangoLineage2 := pangoLineage})
.project({pangoLineage2, countSpain}),
pangoLineage = pangoLineage2,
type := left
)unionall(left, right)
Concatenate two results with identical column names, types, and order. Duplicate rows are retained.
data.filter(country = 'Germany').project({country})
.unionall(data.filter(country = 'France').project({country}))schema()
Describe the schema of a table or of any pipeline result without reading its rows. Returns
fieldName and type, such as STRING, INT32,
INT64, FLOAT, BOOL, DATE32, or
DICTIONARY_ENCODED(STRING) for an indexed column. Sequence columns are reported as
STRING.
data.schema()
data.group(by := {country}, aggs := {count := count()}).schema()tables()
List the tables of the instance as rows with a single tableName column. It takes no arguments and
starts a pipeline instead of following a table.
tables()Sequence aggregations
These operations aggregate changes across the input rows. They apply to a table or to filters of a table. A
preceding filter chooses the records to analyze; it does not restrict which changes the aggregation returns.
mutations(minProportion := p [, sequenceNames := {...}] [, fields := {...}])
Aggregate nucleotide substitutions and deletions above a frequency threshold. Returns source and observed symbols, position, sequence name, proportion, coverage, and count.
data.filter(country = 'Switzerland')
.mutations(minProportion := 0.05, sequenceNames := {main})aminoAcidMutations(minProportion := p [, ...])
data.aminoAcidMutations(minProportion := 0.1, sequenceNames := {S})insertions([sequenceNames := {...}])
Aggregate every nucleotide insertion in the input rows by sequence, position, and inserted symbols.
data.insertions(sequenceNames := {main})aminoAcidInsertions([sequenceNames := {...}])
Aggregate every amino-acid insertion in the input rows by sequence, position, and inserted symbols.
data.aminoAcidInsertions(sequenceNames := {S})Graph operations
transitiveClosure(input, from, to [, includeVertices := bool] [, startingFrom := {...}])
Treat each input row as an edge from its from column to its to column, both of
type string and written as column names, and return a from and to row for every
pair connected by one or more edges. Rows with a null vertex are ignored.
includeVertices := true adds the pair (v, v) for every vertex.
startingFrom, a set of constant strings that do not refer to columns, restricts the result to
paths that start at the given vertices. The row order is unspecified.
On a lineage relation table, which lists each lineage with its parent, the closure from parent
to lineage pairs every lineage with each of its descendants. The example counts the sequences
of a lineage and all its sublineages.
pangoLineage
.transitiveClosure(parent, lineage, includeVertices := true, startingFrom := {'B.1.1.7'})
.join(data.map({lineageName := pangoLineage}).project({lineageName}), to = lineageName)
.group(by := {from}, aggs := {count := count()})Phylogenetic operations
mostRecentCommonAncestor(column [, printNodesNotInTree := bool])
Find the most recent common ancestor of the filtered records in a configured phylogenetic-tree column.
data.filter(country = 'Germany').mostRecentCommonAncestor('usherTree')phyloSubtree(column [, printNodesNotInTree := bool] [, contractUnaryNodes := bool])
data.filter(country = 'Germany').phyloSubtree('usherTree')Write statements
insertInto(query, table)
Run a query and insert its rows into an existing table, given by its name. Output columns are matched to the target columns by name: every target column must be produced with a matching type, and additional columns are ignored. Sequence columns cannot be inserted.
insertInto must be the outermost operation of a query. It is sent to the
POST /admin/query endpoint, which saves the result as a new data version. It returns a single insertedRows row
instead of table rows.
source.filter(country = 'Switzerland').project({primaryKey, country}).insertInto(archive)update(table, set [, where := condition])
Assign new values to columns of the rows of a table, like SQL’s UPDATE … SET … WHERE ….
set is a record that maps each column to update to its new value. A value can be anything a
map assignment computes, such as a literal, another column of the row, or a scalar function, and
null clears the cells. Each column can be assigned once.
where is a filter condition over the columns of the table and can only be passed by name;
without it, every row is updated. RhyDB selects the rows and computes all values from the table as it was
before the update, so {division := country, country := division} swaps two columns,
and a value that cannot be assigned rejects the whole statement without changing the table.
A value must have the type of its column. Integers can also be assigned to float columns, and any string can be assigned to any string column, indexed and compressed ones included. Sequence columns and phylogenetic-tree columns cannot be updated.
Like insertInto, update must be the whole query and is sent to the
POST /admin/query endpoint. It returns a single updatedRows row instead of table rows.
update(data, set := {division := 'Basel-Stadt'}, where := country = 'Switzerland' && division = 'Basel')
data.update({qcPassed := false, note := null}, where := coverage < 0.5)delete(table [, where := condition])
Remove the rows of a table, like SQL’s DELETE FROM … WHERE …. where is a
filter condition over the columns of the table and can only be passed by name; without it, every row is
removed. RhyDB selects all rows before removing any, so a condition that cannot be evaluated rejects the
statement without changing the table.
The rows are removed from every column and index of the table, sequence and phylogenetic-tree columns included, and a deleted primary key can be inserted again. Some memory of the deleted rows is not released, such as the dictionary entries of values that no longer occur in an indexed string column.
Like insertInto, delete must be the whole query and is sent to the
POST /admin/query endpoint. It returns a single deletedRows row instead of table rows.
delete(data, where := primaryKey = 'key1')
data.delete(where := isNull(date) || main[23403] = 'N')createTable(table, columns [, primaryKey := column])
Create a new, empty table. table is a name made of letters, digits, _ and
- that no existing table uses, built-in tables included, and is neither
database_schema nor data_version. columns is a record that maps each
column name to its type. A type is written as a bare name, such as int32, or with named
options, such as string(generateIndex := true). Column names that are not plain identifiers are
written as double-quoted identifiers, such as “S:ORF1a”. The optional primaryKey
names a string column without an index.
| Type | Options | Column |
|---|---|---|
string | generateIndex := bool | String; dictionary-encoded with a bitmap index when indexed |
| 32-bit or 64-bit integer | |
float | Floating-point number | |
boolean | Boolean | |
date | generateIndex := bool | Date; dictionary-encoded with a bitmap index when indexed |
nucleotideSequence |
| Aligned nucleotide sequence |
aminoAcidSequence |
| Aligned amino-acid sequence |
zstdCompressedString |
| String compressed with a zstd dictionary, such as an unaligned sequence |
A sequence column takes its reference from the row of reference_genomes with the given
name and a matching type. The dictionary query of a
zstdCompressedString column must return exactly one row with one string column, usually a
reference sequence. References and dictionaries are copied into the new table when it is created. Lineage
indexes and phylogenetic-tree columns cannot be declared with createTable.
Like insertInto, createTable must be the whole query and is sent to the
POST /admin/query endpoint. It returns a single createdTable row instead of table rows.
createTable(covid, {
primaryKey := string,
country := string(generateIndex := true),
age := int32,
date := date(generateIndex := true),
main := nucleotideSequence(reference := main),
"S" := aminoAcidSequence(reference := "S"),
unaligned_main := zstdCompressedString(
dictionary := reference_genomes.filter(name = 'main' && type = 'nucleotide').project({sequence})
)
}, primaryKey := primaryKey)