running_queries_on_source_data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "fcb44a1e",
   "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": "markdown",
   "id": "825efb41",
   "metadata": {},
   "source": [
    "### Connect to the SQL database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "75141075",
   "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": "code",
   "execution_count": null,
   "id": "1d55d26b",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "all_schemas <- DBI::dbGetQuery(connection, \"SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA\")\n",
    "all_schemas"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b381bec7",
   "metadata": {},
   "source": [
    "### List all tables in the source_data_100kv13_covidv4 schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "96874023",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "sql <- \"\n",
    "SELECT table_schema, table_name\n",
    "FROM information_schema.tables\n",
    "WHERE table_schema = 'source_data_100kv13_covidv4'\n",
    "\"\n",
    "\n",
    "dbGetQuery(connection, sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "156619db",
   "metadata": {},
   "source": [
    "### Query 1: Normalised Rare Disease Terms Cohort "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "efd81fb2",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "sql <- \"\n",
    "SELECT DISTINCT participant_id, normalised_specific_disease\n",
    "FROM source_data_100kv13_covidv4.rare_diseases_participant_disease\n",
    "WHERE normalised_specific_disease IN (\n",
    "    'Familial pulmonary fibrosis','Familial primary spontaneous pneumothorax',\n",
    "    'Familial and multiple pulmonary arteriovenous malformations','Hereditary haemorrhagic telangiectasia'\n",
    ")\n",
    "\"\n",
    "\n",
    "sql_to_run <- sql\n",
    "\n",
    "dbGetQuery(connection, sql_to_run)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "82bbd625",
   "metadata": {},
   "source": [
    "### Query 2: Normalised Rare Disease Terms and Participant Phenotypic Sex Cohort "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "579ec374",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "sql <- \"\n",
    "SELECT DISTINCT rd.participant_id, rd.normalised_specific_disease, par.participant_phenotypic_sex\n",
    "FROM source_data_100kv13_covidv4.rare_diseases_participant_disease as rd\n",
    "LEFT JOIN source_data_100kv13_covidv4.participant AS par\n",
    "    ON rd.participant_id = par.participant_id\n",
    "WHERE rd.normalised_specific_disease IN (\n",
    "    'Familial pulmonary fibrosis','Familial primary spontaneous pneumothorax',\n",
    "    'Familial and multiple pulmonary arteriovenous malformations','Hereditary haemorrhagic telangiectasia')\n",
    "AND par.participant_phenotypic_sex IN ('Female')\n",
    "\"\n",
    "\n",
    "sql_to_run <- sql\n",
    "\n",
    "dbGetQuery(connection, sql_to_run)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d06b26c9",
   "metadata": {},
   "source": [
    "### Query 3: Normalised Rare Disease Terms, Participant Phenotypic Sex Cohort, Age of Onset, and Predicted Ancestry"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6c50fbf7",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "sql <- \"\n",
    "SELECT DISTINCT rd.participant_id, rd.normalised_specific_disease, par.participant_phenotypic_sex, rd.normalised_age_of_onset,\n",
    "                agg.pred_european_ancestries\n",
    "FROM source_data_100kv13_covidv4.rare_diseases_participant_disease as rd\n",
    "LEFT JOIN source_data_100kv13_covidv4.participant AS par\n",
    "    ON rd.participant_id = par.participant_id\n",
    "LEFT JOIN source_data_100kv13_covidv4.aggregate_gvcf_sample_stats AS agg\n",
    "    ON rd.participant_id = agg.participant_id\n",
    "WHERE rd.normalised_specific_disease IN (\n",
    "    'Familial pulmonary fibrosis','Familial primary spontaneous pneumothorax',\n",
    "    'Familial and multiple pulmonary arteriovenous malformations','Hereditary haemorrhagic telangiectasia'\n",
    ")\n",
    "AND par.participant_phenotypic_sex IN ('Female')\n",
    "AND rd.normalised_age_of_onset >= 50\n",
    "AND agg.pred_european_ancestries >= 0.95\n",
    "\"\n",
    "\n",
    "sql_to_run <- sql\n",
    "\n",
    "dbGetQuery(connection, sql_to_run)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "568649df",
   "metadata": {},
   "source": [
    "### Query 4: HPO terms cohort"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c953742a",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "sql <- \"\n",
    "SELECT DISTINCT participant_id\n",
    "FROM source_data_100kv13_covidv4.rare_diseases_participant_phenotype\n",
    "WHERE normalised_hpo_id IN ('HP:0002206','HP:0006530','HP:0002094')\n",
    "AND hpo_present IN ('Yes')\n",
    "\"\n",
    "\n",
    "sql_to_run <- sql\n",
    "\n",
    "dbGetQuery(connection, sql_to_run)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5f350ed9",
   "metadata": {},
   "source": [
    "### Query 5: ICD10 terms cohort"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8d453ad5",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "sql <- \"\n",
    "SELECT DISTINCT participant_id\n",
    "FROM source_data_100kv13_covidv4.hes_apc\n",
    "WHERE diag01 LIKE 'J841'\n",
    "OR diag02 LIKE 'J841'\n",
    "OR diag03 LIKE 'J841'\n",
    "\"\n",
    "\n",
    "sql_to_run <- sql\n",
    "\n",
    "dbGetQuery(connection, sql_to_run)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da55a7f1",
   "metadata": {},
   "source": [
    "### Query 6: ICD10 terms cohort - Part 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6fe04be8",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "sql <- \"\n",
    "SELECT DISTINCT apc.participant_id\n",
    "FROM source_data_100kv13_covidv4.hes_apc AS apc\n",
    "LEFT JOIN source_data_100kv13_covidv4.hes_op AS op\n",
    "    ON apc.participant_id = op.participant_id\n",
    "WHERE apc.diag01 IN ('J841')\n",
    "OR apc.diag02 IN ('J841')\n",
    "OR apc.diag03 IN ('J841')\n",
    "OR op.diag01 IN ('J841')\n",
    "OR op.diag02 IN ('J841')\n",
    "OR op.diag03 IN ('J841')\n",
    "\"\n",
    "\n",
    "sql_to_run <- sql\n",
    "\n",
    "dbGetQuery(connection, sql_to_run)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8264144f",
   "metadata": {},
   "source": [
    "### Query 7: Cancer type and status"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cae429a3",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "sql <- \"\n",
    "SELECT DISTINCT ca.participant_id\n",
    "FROM source_data_100kv13_covidv4.cancer_analysis AS ca\n",
    "LEFT JOIN source_data_100kv13_covidv4.av_tumour AS av\n",
    "    ON ca.participant_id = av.participant_id\n",
    "WHERE ca.disease_type IN ('BREAST')\n",
    "AND av.er_status = 'P'\"\n",
    "\n",
    "sql_to_run <- sql\n",
    "\n",
    "dbGetQuery(connection, sql_to_run)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5acd66a7",
   "metadata": {},
   "source": [
    "### Query 8: Cancer type and status - Part 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "17824268",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "sql <- \"\n",
    "SELECT DISTINCT participant_id\n",
    "FROM source_data_100kv13_covidv4.sact\n",
    "WHERE analysis_group IN ('NIVOLUMAB','PEMBROLIZUMAB')\n",
    "\"\n",
    "\n",
    "sql_to_run <- sql\n",
    "\n",
    "dbGetQuery(connection, sql_to_run)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ae64c935",
   "metadata": {},
   "source": [
    "### Query 9: Cancer type and status - Part 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65da209d",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "sql <- \"\n",
    "SELECT DISTINCT sa.participant_id\n",
    "FROM source_data_100kv13_covidv4.sact AS sa\n",
    "LEFT JOIN source_data_100kv13_covidv4.cancer_analysis AS ca\n",
    "    ON sa.participant_id = ca.participant_id\n",
    "WHERE sa.analysis_group IN ('NIVOLUMAB','PEMBROLIZUMAB')\n",
    "AND ca.disease_type = 'BLADDER'\n",
    "\"\n",
    "\n",
    "sql_to_run <- sql\n",
    "\n",
    "dbGetQuery(connection, sql_to_run)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "name": "R"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}