Connect to an OMOP DuckDB Database with the Central Vocabulary Attached Transparently

Attaches the central Athena vocabulary database read-only as central_vocab and sets the connection's search_path to 'main,central_vocab.main', so concept, concept_ancestor, concept_relationship, ... can be queried with no schema prefix while a 10M-concept vocabulary is stored once instead of inside every site database. (A search_path you already set on a connection you pass in is kept, with central_vocab.main appended; see Details.) The shared SQL macros (descendants_of(), ancestors_of(), clamp_physiologic(), map_to_standard_concept_id(), the cohort macros, ...) are loaded as session-scoped TEMP macros, so connecting never writes to a database file and also works on read-only databases.

Usage

omop_connect(
  db_path,
  vocab_db = NULL,
  read_only = FALSE,
  auto_attach_vocab = TRUE,
  load_macros = TRUE
)

Arguments

db_path: Path of the primary OMOP DuckDB database (":memory:" for an in-memory one; a leading ~ is expanded), or an existing DBI connection. A path that does not exist is created as an empty database unless read_only = TRUE (then it is an error).
vocab_db: Path of the vocabulary database to attach (a leading ~ is expanded). Must exist. If NULL and auto_attach_vocab is TRUE, a sibling vocabulary database is auto-discovered (see Details).
read_only: Logical; open the primary database read-only (default FALSE). An in-memory database cannot be read-only (it has no file to open and starts out empty), so read_only = TRUE with ":memory:" is an error. Ignored, with a warning if TRUE, for an existing connection.
auto_attach_vocab: Logical; auto-discover a sibling vocabulary database when vocab_db is NULL (default TRUE). An explicit vocab_db is always attached; one that holds none of the vocabulary tables (concept, concept_ancestor, ...) draws a warning. With FALSE and no vocabulary tables in the database at all, the vocabulary macros are skipped without a warning, since you opted out (load_macros = FALSE skips all macros).
load_macros: Logical; load the shared SQL macros as TEMP macros (default TRUE).

Value

A DBI connection to the primary database (the same object when db_path was a connection), with central_vocab attached and search_path configured when a vocabulary was found. Close it with DBI::dbDisconnect(con, shutdown = TRUE) when omop_connect() opened it.

Details

Closing. A connection opened by omop_connect() must be closed by the caller with DBI::dbDisconnect(con, shutdown = TRUE). A connection passed in as db_path is used as is and is never closed by this function, nor are its read-only/read-write mode or existing contents changed (read_only = TRUE is ignored, with a warning, for a connection that is open read-write). If setup fails after omop_connect() opened the database, it closes it again before raising.

Vocabulary discovery. An explicit vocab_db must exist (otherwise an error is raised) and is always attached. Without one, and with auto_attach_vocab = TRUE, central_vocabulary.duckdb, vocabulary.duckdb or vocab.duckdb is looked up (in that order) in the directory that holds the primary database – nowhere else, and never the primary database itself. If nothing is found and the database has no local vocabulary (concept missing or empty), a warning explains how to supply one; the connection is still returned. Calling omop_connect() again, or on a connection that already has the same vocabulary attached as central_vocab, does not attach it twice; a different database already attached under that name is an error.

Search path. On a fresh connection (empty search_path) it becomes 'main,central_vocab.main'. A search_path already set on a connection passed in as db_path is not replaced: central_vocab.main is appended to it (unless it already lists central_vocab), so schemas you put first keep taking precedence.

Precedence. It is per table: a vocabulary table that holds rows in the primary database wins over the attached one. A local table that is merely empty – as every vocabulary table is after build_schema() – does not shadow the attached vocabulary: omop_connect() adds a session-only TEMP view over the central table for it (so INSERT into that table through this connection is refused; use a separate connection to load a local vocabulary).

Macros. DuckDB validates the body of some macros when it creates them, so map_to_standard_concept_id() and source_concept_id() are created only if the vocabulary tables (with the columns they read) are visible at connect time; otherwise they are skipped and named in the warning. descendants_of() and ancestors_of() are always created and look concept_ancestor up when they are used, raising a catalog error if it is not visible then.

Per-connection state. search_path, the session-only TEMP views and the TEMP macros belong to the connection omop_connect() configured; the attachment itself belongs to the database instance. A new connection to the same database (for example DBI::dbConnect(con@driver), a connection from a pool, or a connection a parallel worker opens) starts without any of them. On a database built with build_schema() such a connection sees the empty local concept table rather than the central vocabulary and has no descendants_of(), so a count of concepts there is silently zero. Pass that connection through omop_connect() itself (it accepts an existing connection), or give every worker its own omop_connect() connection.

Relation to attach_central_vocabulary(). That function is the ETL-time helper: it attaches the vocabulary and creates a zero-copy view per vocabulary table (temporary, or persistent in the database), and fails if central_vocab is already attached. omop_connect() is the analysis-time helper and recognises an attachment made earlier by attach_central_vocabulary() (same file; no second ATTACH, its views are reused). Call attach_central_vocabulary() first when using both on one connection, since the other order fails at its ATTACH.

Examples

db <- tempfile(fileext = ".duckdb")
build_schema(db_path = db)
#> duckdb keeps downloaded extensions and secrets in a temporary directory:
#> ℹ /tmp/RtmpFV3zps/duckdb
#> This is removed when the R session ends.
#> • Extensions are re-downloaded each session.
#> • Secrets are lost.
#> ℹ Run duckdb(shared_home = TRUE) (or create ~/.duckdb) to keep them (suitable for most users).
#> ℹ Run duckdb(shared_home = FALSE) to accept the temporary directory (and silence this message).
#> ℹ See ?duckdb_storage for details and alternatives.
#> Schema built at /tmp/RtmpFV3zps/file1c3a6887bf2c.duckdb 
con <- omop_connect(db, auto_attach_vocab = FALSE)
#> duckdb keeps downloaded extensions and secrets in a temporary directory:
#> ℹ /tmp/RtmpFV3zps/duckdb
#> This is removed when the R session ends.
#> • Extensions are re-downloaded each session.
#> • Secrets are lost.
#> ℹ Run duckdb(shared_home = TRUE) (or create ~/.duckdb) to keep them (suitable for most users).
#> ℹ Run duckdb(shared_home = FALSE) to accept the temporary directory (and silence this message).
#> ℹ See ?duckdb_storage for details and alternatives.
DBI::dbExecute(con, "INSERT INTO concept_ancestor VALUES (1, 1, 0, 0), (1, 2, 1, 1), (2, 2, 0, 0)")
#> [1] 3
# Every concept at or below concept 1, concept 1 included
DBI::dbGetQuery(con, "SELECT concept_id FROM descendants_of(1) ORDER BY concept_id")
#>   concept_id
#> 1          1
#> 2          2
DBI::dbGetQuery(con, "SELECT clamp_physiologic(250, 0, 100) AS clamped")
#>   clamped
#> 1     100
DBI::dbDisconnect(con, shutdown = TRUE)
unlink(db)

if (FALSE) { # \dontrun{
# A site database plus a shared Athena vocabulary database (the vocabulary file is
# auto-discovered when it sits next to the site database)
con <- omop_connect(
  "site_cdm.duckdb",
  vocab_db = "central_vocabulary.duckdb",
  read_only = TRUE
)
# 201826 = SNOMED 'Type 2 diabetes mellitus'
DBI::dbGetQuery(con, "
  SELECT COUNT(*) AS n FROM condition_occurrence
  WHERE condition_concept_id IN (SELECT concept_id FROM descendants_of(201826))
")
DBI::dbDisconnect(con, shutdown = TRUE)
} # }