running_queries_on_omop_data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "506e96f2",
   "metadata": {},
   "source": [
    "# Running queries on the OMOP data\n",
    "\n",
    "## Install required packages\n",
    "\n",
    "Before executing the cells below, in a terminal session install the following required R packages:\n",
    "\n",
    "`conda create -n omop-source r-glue r-tidyverse r-data.table r-dbi  r-rpostgres r-irkernel -y`\n",
    "\n",
    "### Connect to the SQL database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "873ec437",
   "metadata": {
    "lines_to_next_cell": 2,
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "library(tidyverse)\n",
    "library(data.table)\n",
    "library(glue)\n",
    "library(DBI)\n",
    "library(RPostgres)\n",
    "\n",
    "DBNAME <- #\n",
    "HOST <- #\n",
    "PORT <- #\n",
    "PASSWORD <-  #\n",
    "USER <- 'jupyter_notebook'\n",
    "\n",
    "connection <- DBI::dbConnect(\n",
    "    RPostgres::Postgres(),\n",
    "    dbname = DBNAME,\n",
    "    host = HOST,\n",
    "    port = PORT,\n",
    "    password = PASSWORD,\n",
    "    user = USER,\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1929a57c",
   "metadata": {},
   "source": [
    "# Query 1 - Database count\n",
    "This is a query that provides the total number of participants"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "47c1802f",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "omop_query <- \"\n",
    "    select count(distinct o.person_id) as count\n",
    "    from omop_data_100kv13_covidv4.person o\n",
    "    limit 10\n",
    "\"\n",
    "\n",
    "all_dataset_demo <- dbGetQuery(connection, glue(omop_query, schema = \"omop_data_100kv13_covidv4\"))\n",
    "\n",
    "all_dataset_demo"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "769ed08e",
   "metadata": {},
   "source": [
    "# Query 2 - Total count stratified by gender"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "081ab2dc",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "omop_query <- \"\n",
    "    select count(distinct p.person_id) as count, c.concept_name, c.concept_id\n",
    "    from omop_data_100kv13_covidv4.person p\n",
    "    left join omop_data_100kv13_covidv4.concept c on c.concept_id = p.gender_concept_id\n",
    "    group by c.concept_name, c.concept_id\n",
    "    order by count desc\n",
    "    limit 10\n",
    "\"\n",
    "\n",
    "all_dataset_demo <- dbGetQuery(connection, glue(omop_query, schema = \"omop_data_100kv13_covidv4\"))\n",
    "\n",
    "all_dataset_demo"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6269f04b",
   "metadata": {},
   "source": [
    "# Query 3 - Number of participants in the database with Acure Renal Failure Syndrome"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5e8852ab",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "omop_query <- \"\n",
    "    select count(distinct o.person_id) as count, o.condition_concept_id\n",
    "    from omop_data_100kv13_covidv4.condition_occurrence o\n",
    "    where o.condition_concept_id = '197320'\n",
    "    group by o.condition_concept_id\n",
    "    order by count desc\n",
    "\"\n",
    "\n",
    "all_dataset_demo <- dbGetQuery(connection, glue(omop_query, schema = \"omop_data_100kv13_covidv4\"))\n",
    "\n",
    "all_dataset_demo"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d0bd473a",
   "metadata": {},
   "source": [
    "# Query 4 - Comorbidities\n",
    "This is a simple query that provides the comorbidities with participants diagnosed with \"Acute renal failure syndrome\" OMOP concept_id = 197320. (30 days window before the first diagnosis with 197320)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "536627b5",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "source_query <- \"\n",
    "With CTE_index as (\n",
    "select min(co.condition_start_date) as index_date,co.person_id\n",
    "from omop_data_100kv13_covidv4.condition_occurrence co\n",
    "left join omop_data_100kv13_covidv4.concept c on co.condition_concept_id = c.concept_id\n",
    "where c.concept_id = 197320\n",
    "group by co.person_id\n",
    "),\n",
    "CTE_comorbidities as (\n",
    "select count(distinct co.person_id) as comorbities_num, c.concept_name, c.concept_id, co.condition_source_value\n",
    "from CTE_index as fe\n",
    "left join omop_data_100kv13_covidv4.condition_occurrence co on co.person_id = fe.person_id and co.condition_start_date between fe.index_date - 30 and fe.index_date -1\n",
    "left join omop_data_100kv13_covidv4.concept c on c.concept_id = co.condition_concept_id\n",
    "where c.concept_id != '0'\n",
    "group by c.concept_name, c.concept_id, co.condition_source_value\n",
    "order by comorbities_num desc\n",
    ")\n",
    "select * from CTE_comorbidities\n",
    "limit 10\n",
    "\"\n",
    "\n",
    "query <- dbGetQuery(connection, glue(source_query, schema = \"source_data_100kv13_covidv4\"))\n",
    "\n",
    "query\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1c42ad27",
   "metadata": {},
   "source": [
    "# Query 5 - Adverse Events\n",
    "This is a query that provides the adverse events of participants initiating 'Carboplatin' with OMOP concept_id = 1344905 (30 days window after the first exposure to 1344905)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2e7b13b1",
   "metadata": {
    "lines_to_next_cell": 0,
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "source_query <- \"\n",
    "With CTE_index as (\n",
    "select min(co.drug_exposure_start_date) as index_date,co.person_id\n",
    "from omop_data_100kv13_covidv4.drug_exposure co\n",
    "left join omop_data_100kv13_covidv4.concept c on co.drug_concept_id = c.concept_id\n",
    "where c.concept_id = 1344905\n",
    "group by co.person_id\n",
    "),\n",
    "CTE_adverse_events as (\n",
    "select count(distinct co.person_id) as comorbities_num, c.concept_name, c.concept_id, co.condition_source_value\n",
    "from CTE_index as fe\n",
    "left join omop_data_100kv13_covidv4.condition_occurrence co on co.person_id = fe.person_id and co.condition_start_date between fe.index_date +1 and fe.index_date + 30\n",
    "left join omop_data_100kv13_covidv4.concept c on c.concept_id = co.condition_concept_id\n",
    "where c.concept_id != '0'\n",
    "group by c.concept_name, c.concept_id, co.condition_source_value\n",
    "order by comorbities_num desc\n",
    ")\n",
    "select * from CTE_adverse_events\n",
    "limit 10\n",
    "\"\n",
    "\n",
    "query <- dbGetQuery(connection, glue(source_query, schema = \"source_data_100kv13_covidv4\"))\n",
    "\n",
    "query\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d163b7be",
   "metadata": {
    "lines_to_next_cell": 2
   },
   "source": [
    "# Query 6 - Write All OMOP tables to files\n",
    "\n",
    "If you would like to inspect further the OMOP tables, you can use this command to export the OMOP tables and write them to file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "113e0846",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "# list available database tables from the Schema omop_data_100kv13_covidv4.person\n",
    "\n",
    "SCHEMA = 'omop_data_100kv13_covidv4' \n",
    "\n",
    "connection %>%\n",
    "    DBI::dbListObjects(DBI::Id(schema = SCHEMA)) %>%\n",
    "    dplyr::pull(table) %>%\n",
    "    purrr::map(~slot(.x, 'name')) %>%\n",
    "    dplyr::bind_rows() %>% \n",
    "    head"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "04c2b2b6",
   "metadata": {},
   "source": [
    "## Write the desired OMOP table to inspect in a flat file\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9c945620",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "# Write the desired OMOP table to inspect in a flat file\n",
    "\n",
    "OMOP_TABLE_NAME = 'drug_exposure'\n",
    "OMOP_SCHEMA = 'omop_data_100kv13_covidv4'\n",
    "\n",
    "source_query <- paste0(\"SELECT * FROM \", OMOP_SCHEMA, \".\", OMOP_TABLE_NAME, \";\")\n",
    "query <- dbGetQuery(connection, glue(source_query, schema = \"omop_data_100kv13_covidv4\"))\n",
    "\n",
    "head(query)\n",
    "write.table(query, sep = \",\", row.names = FALSE, quote = FALSE, file = paste0(OMOP_TABLE_NAME, '.csv'))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2cb3c28d",
   "metadata": {},
   "source": [
    "## Inspect the generated table\n",
    "\n",
    "You can see the generated table in the results section in the file browser"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4eaf803c",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "OMOP_TABLE_NAME = 'drug_exposure'\n",
    "\n",
    "list.files(pattern = \".csv\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "name": "R"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}