running_queries_on_omop_data_serena.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "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,
   "metadata": {
    "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",
   "metadata": {},
   "source": [
    "# SQL query - Demographics\n",
    "This is a simple query that provides the total number of participants with a valid VCF path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "omop_query <- \"\n",
    "    select count(distinct o.person_id) as count\n",
    "    from omop_data_serena_v1.observation o\n",
    "    left join omop_data_serena_v1.person p on o.person_id = p.person_id\n",
    "    left join omop_data_serena_v1.concept c on c.concept_id = p.gender_concept_id\n",
    "    inner join omop_data_serena_v1.observation o2 on o.person_id = o2.person_id and o2.observation_source_value = 'VCF_index_file_path_URI' and o2.value_as_string IS NOT NULL\n",
    "    where o.observation_concept_id = '4161164'\n",
    "    limit 10\n",
    "\"\n",
    "\n",
    "all_dataset_demo <- dbGetQuery(connection, glue(omop_query, schema = \"omop_data_serena_v1\"))\n",
    "\n",
    "all_dataset_demo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "This is a simple query that provides the total of the counts stratified by gender"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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_serena_v1.observation o\n",
    "    left join omop_data_serena_v1.person p on o.person_id = p.person_id\n",
    "    left join omop_data_serena_v1.concept c on c.concept_id = p.gender_concept_id\n",
    "    inner join omop_data_serena_v1.observation o2 on o.person_id = o2.person_id and o2.observation_source_value = 'VCF_index_file_path_URI' and o2.value_as_string IS NOT NULL\n",
    "    where o.observation_concept_id = '4161164'\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_serena_v1\"))\n",
    "\n",
    "all_dataset_demo"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is a simple query that provides the total of the counts stratified by gender and organ"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "omop_query <- \"\n",
    "    select count(distinct p.person_id) as count, c.concept_name, c.concept_id, o.value_as_string as ORGAN\n",
    "    from omop_data_serena_v1.observation o\n",
    "    left join omop_data_serena_v1.person p on o.person_id = p.person_id\n",
    "    left join omop_data_serena_v1.concept c on c.concept_id = p.gender_concept_id\n",
    "    inner join omop_data_serena_v1.observation o2 on o.person_id = o2.person_id and o2.observation_source_value = 'VCF_index_file_path_URI' and o2.value_as_string IS NOT NULL\n",
    "    where o.observation_concept_id = '4161164'\n",
    "    group by c.concept_name, c.concept_id, o.value_as_string\n",
    "    order by count desc\n",
    "    limit 10\n",
    "\"\n",
    "\n",
    "all_dataset_demo <- dbGetQuery(connection, glue(omop_query, schema = \"omop_data_serena_v1\"))\n",
    "\n",
    "all_dataset_demo"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SQL query part 2\n",
    "This query pulls all the information needed (ID, ORGAN, VCF) for the mut-sig pipeline."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "omop_query <- \"\n",
    "    select distinct p.person_source_value as ID, o.value_as_string as ORGAN, o2.value_as_string as VCF\n",
    "    from omop_data_serena_v1.observation o\n",
    "    left join omop_data_serena_v1.person p on o.person_id = p.person_id\n",
    "    inner join omop_data_serena_v1.observation o2 on o.person_id = o2.person_id and o2.observation_source_value = 'VCF_file_path_URI' and o2.value_as_string IS NOT NULL\n",
    "    where o.observation_concept_id = '4161164'\n",
    "    and o2.value_as_string like '%.snv.simple.txt.vcf.gz'\n",
    "    limit 10\n",
    "\"\n",
    "\n",
    "all_dataset <- dbGetQuery(connection, glue(omop_query, schema = \"omop_data_serena_v1\"))\n",
    "\n",
    "all_dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SQL query part 3\n",
    "This query filters by organ"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This query filters by organ. Where organ equals to breast."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "omop_query <- \"\n",
    "    select p.person_source_value, c.concept_name, c.concept_id, o.value_as_string as ORGAN\n",
    "    from omop_data_serena_v1.observation o\n",
    "    left join omop_data_serena_v1.person p on o.person_id = p.person_id\n",
    "    left join omop_data_serena_v1.concept c on c.concept_id = p.gender_concept_id\n",
    "    inner join omop_data_serena_v1.observation o2 on o.person_id = o2.person_id and o2.observation_source_value = 'VCF_index_file_path_URI' and o2.value_as_string IS NOT NULL\n",
    "    where o.observation_concept_id = '4161164' and o.value_as_string = 'Breast'\n",
    "    limit 10\n",
    "\"\n",
    "\n",
    "all_dataset_demo <- dbGetQuery(connection, glue(omop_query, schema = \"omop_data_serena_v1\"))\n",
    "\n",
    "all_dataset_demo"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This query filters the participants by the ICD10 code C61 or Primary malignant neoplasm of prostate. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "omop_query <- \"\n",
    "    select p.person_source_value,co.condition_concept_id, co.condition_source_value, c.concept_name\n",
    "    from omop_data_serena_v1.condition_occurrence co\n",
    "    left join omop_data_serena_v1.person p on p.person_id = co.person_id\n",
    "    left join omop_data_serena_v1.concept c on c.concept_id = co.condition_concept_id\n",
    "    where co.condition_source_value = 'C61'\n",
    "    limit 20\n",
    "\"\n",
    "\n",
    "all_dataset_demo <- dbGetQuery(connection, glue(omop_query, schema = \"omop_data_serena_v1\"))\n",
    "\n",
    "all_dataset_demo"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This query filters the participants by the exposure to Nivolumab"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "omop_query <- \"\n",
    "    select p.person_source_value,co.drug_concept_id, co.drug_source_value, c.concept_name\n",
    "    from omop_data_serena_v1.drug_exposure co\n",
    "    left join omop_data_serena_v1.person p on p.person_id = co.person_id\n",
    "    left join omop_data_serena_v1.concept c on c.concept_id = co.drug_concept_id\n",
    "    where drug_concept_id = '45892628'\n",
    "    limit 20\n",
    "\"\n",
    "\n",
    "all_dataset_demo <- dbGetQuery(connection, glue(omop_query, schema = \"omop_data_serena_v1\"))\n",
    "\n",
    "all_dataset_demo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "4.1.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}