Getting Cohorts Using WebAPI

Exercise

Using the OHDSI WebAPI

1. Get cohort definition for Acute Myocardial Infraction
2. Populate Eunomia GiBleed cohort table with patients satisfying this definition

We defined this cohort using R and sql in the tutorial here.

I have already defined this cohort on the Atlas demo site, but it could potentially be deleted in the future so the links below could be broken at some point. To be safe, I have preserved the cohort definition and concept sets used in

resources/ami-cohort

Cohort Definition

• Myocardial Infraction Cohort

Concept Sets Used

• Myocardial Infraction
• In-patient/ER

Packages

HADES packages used

• Eunomia for connecting to a synthetic OMOP CDM database.
• DatabaseConnector for connecting to our Eunomia dataset and populating the cohort table.
• ROhdsiWebApi for interfacing with the OHDSI WebAPI to get a cohort definition from the Atlas demo site.

Installation

install.packages("remotes")

# Hades packages
install.packages("Eunomia")
remotes::install_github("OHDSI/ROhdsiWebApi")
install.packages("DatabaseConnector")

Get AMI Cohort Definition

base_url <- "https://api.ohdsi.org/WebAPI"
cohort_id <- 1790272
generate_stats <- F

# If webapi is down or our cohort was deleted, we will just load the sql from our local file
sql <- tryCatch({
  # First, get the cohort definition
  cohort_definition <- ROhdsiWebApi::getCohortDefinition(
    cohortId = cohort_id,
    baseUrl = base_url
  )
  
  # Pretty complicated looking. Have a look if you want
  #str(cohort_definition)
  
  # Next, get the OHDSI sql using the cohort definition
  ROhdsiWebApi::getCohortSql(
    cohortDefinition = cohort_definition,
    baseUrl = base_url,
    generateStats = generate_stats
  )
}, error = function() {
  readLines("resources/ami-cohort/cohort-definition/ami-cohort.sql")
})

Populate Cohort Table

Connect to Database

datasetName = "GiBleed"
dbms = "sqlite"

datasetLocation <- Eunomia::getDatabaseFile(
  datasetName = datasetName, 
  dbms = dbms, 
  databaseFile = tempfile(fileext = ".sqlite")
)

attempting to download GiBleed

attempting to extract and load: C:\Users\mccul\AppData\Local\Temp\RtmpqARFGu/GiBleed_5.3.zip to: C:\Users\mccul\AppData\Local\Temp\RtmpqARFGu/GiBleed_5.3.sqlite

connectionDetails <- DatabaseConnector::createConnectionDetails(dbms = dbms, server = datasetLocation)
connection = DatabaseConnector::connect(connectionDetails = connectionDetails)

Connecting using SQLite driver

DatabaseConnector::querySql(connection = connection, sql = "select count(*) from cohort;")

Add Visits

I’m adding some visits so we get some results in the cohort. Fudging the data a bit…

source("util.R")
insert_visits(connection)

[1] "Adding visits..."


insert into concept (
  concept_id,
  concept_name,
  domain_id,
  vocabulary_id,
  concept_class_id,
  standard_concept,
  concept_code,
  valid_start_date,
  valid_end_date,
  invalid_reason
)
values (
  262--concept_id,
  ,'Emergency Room and Inpatient Visit'--concept_name,
  ,'Visit'--domain_id,
  ,'Visit'--vocabulary_id,
  ,'Visit'--concept_class_id,
  ,'S'--standard_concept,
  ,'ERIP'--concept_code,
  ,strftime('%s', '1970-01-01 00:00:00')--valid_start_date,
  ,strftime('%s', '2099-12-31 00:00:00')--valid_end_date,
  ,null--invalid_reason
);

delete from visit_occurrence where visit_occurrence_id >= 1000000;
insert into visit_occurrence(
  visit_occurrence_id
  ,person_id
  ,visit_concept_id
  ,visit_start_date
  ,visit_start_datetime
  ,visit_end_date
  ,visit_end_datetime
  ,visit_type_concept_id
  ,provider_id
  ,care_site_id
  ,visit_source_value
  ,visit_source_concept_id
  ,admitting_source_concept_id
  ,admitting_source_value
  ,discharge_to_concept_id
  ,discharge_to_source_value
  ,preceding_visit_occurrence_id
)
values(
  1000000--visit_occurrence_id
  ,1491--person_id
  ,9201--visit_concept_id
  ,strftime('%s', '2011-07-11 12:34:56')--visit_start_date
  ,strftime('%s', '2011-07-11 12:34:56')--visit_start_datetime
  ,strftime('%s', '2011-07-13 12:34:56')--visit_end_date
  ,strftime('%s', '2011-07-13 12:34:56')--visit_end_datetime
  ,0--visit_type_concept_id
  ,null--provider_id
  ,null--care_site_id
  ,null--visit_source_value
  ,0--visit_source_concept_id
  ,0--admitting_source_concept_id
  ,null--admitting_source_value
  ,0--discharge_to_concept_id
  ,null--discharge_to_source_value
  ,null--preceding_visit_occurrence_id
), 
(
  1000001--visit_occurrence_id
  ,105--person_id
  ,262--visit_concept_id
  ,strftime('%s', '2005-11-10 12:34:56')--visit_start_date
  ,strftime('%s', '2005-11-10 12:34:56')--visit_start_datetime
  ,strftime('%s', '2005-11-13 12:34:56')--visit_end_date
  ,strftime('%s', '2005-11-13 12:34:56')--visit_end_datetime
  ,0--visit_type_concept_id
  ,null--provider_id
  ,null--care_site_id
  ,null--visit_source_value
  ,0--visit_source_concept_id
  ,0--admitting_source_concept_id
  ,null--admitting_source_value
  ,0--discharge_to_concept_id
  ,null--discharge_to_source_value
  ,null--preceding_visit_occurrence_id
),
(
  1000002--visit_occurrence_id
  ,96--person_id
  ,262--visit_concept_id
  ,strftime('%s', '1997-01-24 12:34:56')--visit_start_date
  ,strftime('%s', '1997-01-24 12:34:56')--visit_start_datetime
  ,strftime('%s', '1997-01-26    12:34:56')--visit_end_date
  ,strftime('%s', '1997-01-26 12:34:56')--visit_end_datetime
  ,0--visit_type_concept_id
  ,null--provider_id
  ,null--care_site_id
  ,null--visit_source_value
  ,0--visit_source_concept_id
  ,0--admitting_source_concept_id
  ,null--admitting_source_value
  ,0--discharge_to_concept_id
  ,null--discharge_to_source_value
  ,null--preceding_visit_occurrence_id
),
(
  1000003--visit_occurrence_id
  ,3049--person_id
  ,9203--visit_concept_id
  ,strftime('%s', '1986-12-04 12:34:56')--visit_start_date
  ,strftime('%s', '1986-12-04 12:34:56')--visit_start_datetime
  ,strftime('%s', '1986-12-06    12:34:56')--visit_end_date
  ,strftime('%s', '1986-12-06 12:34:56')--visit_end_datetime
  ,0--visit_type_concept_id
  ,null--provider_id
  ,null--care_site_id
  ,null--visit_source_value
  ,0--visit_source_concept_id
  ,0--admitting_source_concept_id
  ,null--admitting_source_value
  ,0--discharge_to_concept_id
  ,null--discharge_to_source_value
  ,null--preceding_visit_occurrence_id
),
(
  1000004--visit_occurrence_id
  ,2692--person_id
  ,9201--visit_concept_id
  ,strftime('%s', '1998-06-28 12:34:56')--visit_start_date
  ,strftime('%s', '1998-06-28 12:34:56')--visit_start_datetime
  ,strftime('%s', '1998-06-30 12:34:56')--visit_end_date
  ,strftime('%s', '1998-06-30 12:34:56')--visit_end_datetime
  ,0--visit_type_concept_id
  ,null--provider_id
  ,null--care_site_id
  ,null--visit_source_value
  ,0--visit_source_concept_id
  ,0--admitting_source_concept_id
  ,null--admitting_source_value
  ,0--discharge_to_concept_id
  ,null--discharge_to_source_value
  ,null--preceding_visit_occurrence_id
)
;

  |                                                                            
  |                                                                      |   0%
  |                                                                            
  |=======================                                               |  33%
  |                                                                            
  |===============================================                       |  67%
  |                                                                            
  |======================================================================| 100%

Executing SQL took 0.00916 secs

Add Subjects

DatabaseConnector::renderTranslateExecuteSql(connection = connection, 
                                             sql = sql, 
                                             vocabulary_database_schema="main", 
                                             cdm_database_schema="main", 
                                             target_database_schema="main",
                                             target_cohort_table = "cohort",
                                             target_cohort_id = "1")

Executing SQL took 0.0567 secs

Check Results

We should get the same subjects as we did in the defining cohorts table. Let’s check

Subjects in original: (1491, 105, 96, 3049, 2692)

sql <- "
  select * from @cdm.cohort;
"
cdm <- "main"
DatabaseConnector::renderTranslateQuerySql(
  connection = connection,
  sql = sql,
  cdm = cdm
)

Great, we got the same subjects.

See the characterization tutorial for how to get some interesting stats on these subjects.