Connecting to OCHIN DB - R.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "693c3fd5",
   "metadata": {},
   "source": [
    "# Connect to the OCHIN DB through R"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "061d0d32",
   "metadata": {},
   "source": [
    "This notebook will walk you through how to connect to the OCHIN DB using R. \n",
    "Before you begin, make sure that you have access to the data and check to make sure the `db-credentials.txt` file is located in your home directory. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fb117fee",
   "metadata": {},
   "source": [
    "## Install ODBC drivers as necessary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1ff40ccd",
   "metadata": {},
   "outputs": [],
   "source": [
    "system(\"sudo su -c 'curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo && exit'\")\n",
    "system(\"sudo yum remove unixODBC-utf16 unixODBC-utf16-devel\")\n",
    "system(\"sudo ACCEPT_EULA=Y yum install -y msodbcsql17\")\n",
    "system(\"sudo ACCEPT_EULA=Y yum install -y mssql-tools\")\n",
    "system(\"echo 'export PATH='$PATH:/opt/mssql-tools/bin'' >> ~/.bashrc\")\n",
    "system(\"source ~/.bashrc\")\n",
    "system(\"sudo yum install -y unixODBC-devel\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "34e8a7f1",
   "metadata": {},
   "source": [
    "## Install and load R packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "913371e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "install.packages(\"odbc\")\n",
    "library(odbc)\n",
    "install.packages(\"DBI\")\n",
    "install.packages(\"rstudioapi\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d0487e29",
   "metadata": {},
   "source": [
    "## Read and parse your DB credentials"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9ed64e3e",
   "metadata": {},
   "outputs": [],
   "source": [
    "file_path <- '/home/ec2-user/SageMaker/db-credentials.txt'\n",
    "\n",
    "db_creds_df <- read.table(file_path)\n",
    "db_creds <- db_creds_df$V3\n",
    "names(db_creds) <- db_creds_df$V1\n",
    "\n",
    "db_creds <- trimws(db_creds)\n",
    "#db_creds"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c5a2de97",
   "metadata": {},
   "source": [
    "## Connect to the database\n",
    "Remember that you may have access to multiple databases. \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4580a921",
   "metadata": {},
   "source": [
    "### Connecting to the \"raw\" data\n",
    "The raw database contains the patient data. You have read-only access to this database.\n",
    "\n",
    "- For AIM AHEAD Year 1 programs, the Raw database is named FellowsSample.\n",
    "- For AIM AHEAD Year 2 programs, the Raw database is named AAOCHIN2023.\n",
    "\n",
    "Within the Raw database, you must open the **View** specific to your project. Your OCHIN Project ID is provided to you when you receive confirmation of your OCHIN DB Login Activation. \n",
    "\n",
    "Once you have opened your View, you will see the Tables which contain the subset of data specific to your project. \n",
    "\n",
    "- Tables containing patient-level data specific to your project will be prefixed with your OCHIN Project ID.\n",
    "- Tables that contain metadata shared amongst all projects are prefixed with \"common\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "40b34cfd",
   "metadata": {},
   "outputs": [],
   "source": [
    "### This example shows how to view tables in the AAOCHIN2023 database. \n",
    "raw_db <- 'AAOCHIN2023' \n",
    "raw_connection_string = paste0(\"DRIVER={ODBC Driver 17 for SQL Server};\",\n",
    "                          \"SERVER=\", db_creds['host'], ',', db_creds['port'], ';',\n",
    "                          \"DATABASE=\", raw_db, ';',\n",
    "                          \"UID=\", db_creds['username'], ';',\n",
    "                          \"PWD={\", db_creds['password'], \"};\")\n",
    "raw_db_conn <- DBI::dbConnect(odbc::odbc(), .connection_string = raw_connection_string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5898a8a3",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "### We can print all tables available in the database views we have access to like so: \n",
    "df1 <- dbGetQuery(raw_db_conn, \"SELECT name AS TABLE_NAME FROM sys.views;\")\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0a2cc533",
   "metadata": {},
   "outputs": [],
   "source": [
    "### We can view the top 10 entries in the CONCEPT_DIMENSION table like so:\n",
    "### Note that the CONCEPT_DIMENSION table contains metadata shared amongst all projects, \n",
    "### and therefore must be prefixed with \"common\"\n",
    "df2 <- dbGetQuery(raw_db_conn, \"SELECT TOP 10 * FROM common.CONCEPT_DIMENSION;\")\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b48fdb6",
   "metadata": {},
   "source": [
    "### Connecting to your project database\n",
    "Your project database is unique to your project and named after your OCHIN Project ID. You have read-write access to this database.\n",
    "\n",
    "Your project database is initially empty. All temporary tables or aggregate results for your project should be saved here. You can store up to 50GB in your project database. This is not a lot! The full Raw database has over 1TB of data. If you copy data directly from the Raw database to your project database, you will quickly run out of space.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fb2710b8",
   "metadata": {},
   "outputs": [],
   "source": [
    "### This example shows how to view tables in your project specific database. \n",
    "proj_db <- 'S000' # Change this to your project ID\n",
    "proj_connection_string = paste0(\"DRIVER={ODBC Driver 17 for SQL Server};\",\n",
    "                          \"SERVER=\", db_creds['host'], ',', db_creds['port'], ';',\n",
    "                          \"DATABASE=\", db, ';',\n",
    "                          \"UID=\", db_creds['username'], ';',\n",
    "                          \"PWD={\", db_creds['password'], \"};\")\n",
    "proj_db_conn <- DBI::dbConnect(odbc::odbc(), .connection_string = raw_connection_string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fb2f60c3",
   "metadata": {},
   "outputs": [],
   "source": [
    "### We can print all tables in the project database like so:\n",
    "### Note that this database will be empty unless you have saved tables here. \n",
    "df3 <- dbGetQuery(raw_db_conn, \"SELECT name AS TABLE_NAME FROM sys.tables;\")\n",
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f2319f35",
   "metadata": {},
   "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.3.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}