Creating a reference to the OMOP CDM
Typically OMOP CDM datasets are stored in a database and can range in size from hundreds of patients with thousands of records to hundreds of millions of patients with billions of records. The Observational Health Data Science and Informatics (OHDSI) community supports a selection of popular database platforms including Postgres, Microsoft SQL Server, Oracle, as well as cloud data platforms suchs as Amazon Redshift, Google Big Query, Databricks, and Snowflake. The first step in using CDMConnector is to create a connection to your database from R. This can take some effort the first time you set up drivers. See the “Database Connection Examples” vignette or check out the Posit’s database documentation.
In our example’s we will use some synthetic data from the Synthea project
that has been mapped to the OMOP CDM format. We’ll use the duckdb database which is a file based
database similar to SQLite but with better date type support. To see all
the example datasets available run exampleDatasets().
library(CDMConnector)
exampleDatasets()
#> [1] "GiBleed" "synthea-allergies-10k"
#> [3] "synthea-anemia-10k" "synthea-breast_cancer-10k"
#> [5] "synthea-contraceptives-10k" "synthea-covid19-10k"
#> [7] "synthea-covid19-200k" "synthea-dermatitis-10k"
#> [9] "synthea-heart-10k" "synthea-hiv-10k"
#> [11] "synthea-lung_cancer-10k" "synthea-medications-10k"
#> [13] "synthea-metabolic_syndrome-10k" "synthea-opioid_addiction-10k"
#> [15] "synthea-rheumatoid_arthritis-10k" "synthea-snf-10k"
#> [17] "synthea-surgery-10k" "synthea-total_joint_replacement-10k"
#> [19] "synthea-veteran_prostate_cancer-10k" "synthea-veterans-10k"
#> [21] "synthea-weight_loss-10k" "synpuf-1k"
#> [23] "empty_cdm"
con <- DBI::dbConnect(duckdb::duckdb(), eunomiaDir("GiBleed"))
DBI::dbListTables(con)
#> [1] "care_site" "cdm_source" "concept"
#> [4] "concept_ancestor" "concept_class" "concept_relationship"
#> [7] "concept_synonym" "condition_era" "condition_occurrence"
#> [10] "cost" "death" "device_exposure"
#> [13] "domain" "dose_era" "drug_era"
#> [16] "drug_exposure" "drug_strength" "fact_relationship"
#> [19] "location" "measurement" "metadata"
#> [22] "note" "note_nlp" "observation"
#> [25] "observation_period" "payer_plan_period" "person"
#> [28] "procedure_occurrence" "provider" "relationship"
#> [31] "source_to_concept_map" "specimen" "visit_detail"
#> [34] "visit_occurrence" "vocabulary"If you’re using CDMConnector for the first time you may get a message
about adding an enviroment vairable EUNOMIA_DATA_FOLDER .
To do this simply create a new text file in your home directory called
.Renviron and add the line
EUNOMIA_DATA_FOLDER="path/to/folder/where/we/can/store/example/data".
If you run usethis::edit_r_environ() this file will be
created and opened for you and opened in RStudio.
After connecting to a database containing data mapped to the OMOP
CDM, use cdmFromCon to create a CDM reference. This CDM
reference is a single object that contains dplyr table references to
each CDM table along with metadata about the CDM instance.
The cdmSchema is the schema in the database that
contains the OMOP CDM tables and is required. The
writeSchema is a schema in the database where the user has
the ability to create tables. Both cdmSchema and
writeSchema are required to create a cdm object.
Every cdm object needs a cdmName that is used to
identify the CDM in output files.
cdm <- cdmFromCon(con, cdmName = "eunomia", cdmSchema = "main", writeSchema = "main")
cdm
#>
#> ── # OMOP CDM reference (duckdb) of eunomia ────────────────────────────────────
#> • omop tables: person, observation_period, visit_occurrence, visit_detail,
#> condition_occurrence, drug_exposure, procedure_occurrence, device_exposure,
#> measurement, observation, death, note, note_nlp, specimen, fact_relationship,
#> location, care_site, provider, payer_plan_period, cost, drug_era, dose_era,
#> condition_era, metadata, cdm_source, concept, vocabulary, domain,
#> concept_class, concept_relationship, relationship, concept_synonym,
#> concept_ancestor, source_to_concept_map, drug_strength
#> • cohort tables: -
#> • achilles tables: -
#> • other tables: -
cdm$observation_period
#> # Source: table<observation_period> [?? x 5]
#> # Database: DuckDB v1.2.1 [root@Darwin 23.1.0:R 4.3.3//private/var/folders/2j/8z0yfn1j69q8sxjc7vj9yhz40000gp/T/Rtmp0DVg9A/file16ae91df2c8e3.duckdb]
#> observation_period_id person_id observation_period_s…¹ observation_period_e…²
#> <int> <int> <date> <date>
#> 1 6 6 1963-12-31 2007-02-06
#> 2 13 13 2009-04-26 2019-04-14
#> 3 27 27 2002-01-30 2018-11-21
#> 4 16 16 1971-10-14 2017-11-02
#> 5 55 55 2009-05-30 2019-03-23
#> 6 60 60 1990-11-21 2019-01-23
#> 7 42 42 1909-11-03 2019-03-13
#> 8 33 33 1986-05-12 2018-09-10
#> 9 18 18 1965-11-17 2018-11-07
#> 10 25 25 2007-03-18 2019-04-07
#> # ℹ more rows
#> # ℹ abbreviated names: ¹observation_period_start_date,
#> # ²observation_period_end_date
#> # ℹ 1 more variable: period_type_concept_id <int>Individual CDM table references can be accessed using `$`.
cdm$person %>%
dplyr::glimpse()
#> Rows: ??
#> Columns: 18
#> Database: DuckDB v1.2.1 [root@Darwin 23.1.0:R 4.3.3//private/var/folders/2j/8z0yfn1j69q8sxjc7vj9yhz40000gp/T/Rtmp0DVg9A/file16ae91df2c8e3.duckdb]
#> $ person_id <int> 6, 123, 129, 16, 65, 74, 42, 187, 18, 111,…
#> $ gender_concept_id <int> 8532, 8507, 8507, 8532, 8532, 8532, 8532, …
#> $ year_of_birth <int> 1963, 1950, 1974, 1971, 1967, 1972, 1909, …
#> $ month_of_birth <int> 12, 4, 10, 10, 3, 1, 11, 7, 11, 5, 8, 3, 3…
#> $ day_of_birth <int> 31, 12, 7, 13, 31, 5, 2, 23, 17, 2, 19, 13…
#> $ birth_datetime <dttm> 1963-12-31, 1950-04-12, 1974-10-07, 1971-…
#> $ race_concept_id <int> 8516, 8527, 8527, 8527, 8516, 8527, 8527, …
#> $ ethnicity_concept_id <int> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, …
#> $ location_id <int> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA…
#> $ provider_id <int> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA…
#> $ care_site_id <int> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA…
#> $ person_source_value <chr> "001f4a87-70d0-435c-a4b9-1425f6928d33", "0…
#> $ gender_source_value <chr> "F", "M", "M", "F", "F", "F", "F", "M", "F…
#> $ gender_source_concept_id <int> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, …
#> $ race_source_value <chr> "black", "white", "white", "white", "black…
#> $ race_source_concept_id <int> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, …
#> $ ethnicity_source_value <chr> "west_indian", "italian", "polish", "ameri…
#> $ ethnicity_source_concept_id <int> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, …You can then use dplyr to query the cdm tables just as you would an R
dataframe. The difference is that the data stays in the database and SQL
code is dynamically generated and set to the database backend. The goal
is to allow users to not think too much about the database or SQL and
instead use familiar R syntax to work with these large tables.
collect will bring the data from the database into R. Be
careful not to request a gigantic result set! In general it is better to
aggregate data in the database, if possible, before bringing data into
R.
library(dplyr)
#>
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#>
#> filter, lag
#> The following objects are masked from 'package:base':
#>
#> intersect, setdiff, setequal, union
library(ggplot2)
cdm$person %>%
group_by(year_of_birth, gender_concept_id) %>%
summarize(n = n(), .groups = "drop") %>%
collect() %>%
mutate(sex = case_when(
gender_concept_id == 8532 ~ "Female",
gender_concept_id == 8507 ~ "Male"
)) %>%
ggplot(aes(y = n, x = year_of_birth, fill = sex)) +
geom_histogram(stat = "identity", position = "dodge") +
labs(x = "Year of birth",
y = "Person count",
title = "Age Distribution",
subtitle = cdmName(cdm),
fill = NULL) +
theme_bw()