### Quickstart: Clone, Download, and Load MIMIC-IV

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iv/buildmimic/postgres/README.md

A comprehensive quickstart guide to clone the repository, download the MIMIC-IV data, create the database, and load the schema and data into PostgreSQL.

```bash
git clone https://github.com/MIT-LCP/mimic-code.git
cd mimic-code
# download data
wget -r -N -c -np --user <USERNAME> --ask-password https://physionet.org/files/mimiciv/3.1/
mv physionet.org/files/mimiciv mimiciv && rmdir physionet.org/files && rm physionet.org/robots.txt && rmdir physionet.org
createdb mimiciv
psql -d mimiciv -f mimic-iv/buildmimic/postgres/create.sql
psql -d mimiciv -v ON_ERROR_STOP=1 -v mimic_data_dir=mimiciv/3.1 -f mimic-iv/buildmimic/postgres/load_gz.sql
psql -d mimiciv -v ON_ERROR_STOP=1 -v mimic_data_dir=mimiciv/3.1 -f mimic-iv/buildmimic/postgres/constraint.sql
psql -d mimiciv -v ON_ERROR_STOP=1 -v mimic_data_dir=mimiciv/3.1 -f mimic-iv/buildmimic/postgres/index.sql
```

--------------------------------

### Install Google Cloud SDK

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/buildmimic/bigquery/README.md

Installs the Google Cloud SDK using apt-get. This command configures the repository and installs the necessary packages for interacting with Google Cloud services.

```shell
export CLOUD_SDK_REPO="cloud-sdk-$(lsb_release -c -s)"

echo "deb http://packages.cloud.google.com/apt $CLOUD_SDK_REPO main" | sudo tee -a /etc/apt/sources.list.d/google-cloud-sdk.list

curl https://packages.cloud.google.com/apt/doc/apt-key.gpg | sudo apt-key add -

sudo apt-get update && sudo apt-get install google-cloud-sdk
```

--------------------------------

### Initialize and start MonetDB instances on *nix

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/buildmimic/monetdb/README.md

Commands to create and start a MonetDB database instance and a specific database named 'mimic' on *nix systems.

```bash
$ monetdbd create /path/you/want/to/store/your/monetdbdata
$ monetdbd start /path/you/want/to/store/your/monetdbdata
$ monetdb create mimic
$ monetdb start mimic
```

--------------------------------

### Install and Load R Packages

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/tutorials/using_r_with_jupyter.ipynb

Installs and loads necessary R packages for PostgreSQL connectivity and plotting.

```r
# install.packages("RPostgreSQL")
require("RPostgreSQL")
require("ggplot2")
```

--------------------------------

### Example DuckDB Import

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iv/buildmimic/duckdb/README.md

Example of running the `import_duckdb.sh` script to create a DuckDB database named 'mimic4.db' with MIMIC-IV data.

```shell
$ ./import_duckdb.sh physionet.org/files/mimiciv/2.2

  <... output of script snipped ...>
Successfully finished loading data into mimic4.db.

$ ls -lh mimic4.db
-rw-rw-r--. 1 myuser mygroup 93G May 26 16:11 mimic4.db
```

--------------------------------

### Print First 10 Examples for Comparison

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/notebooks/crrt-notebook.ipynb

This snippet is a placeholder to print out the first 10 examples of the comparison data, useful for a quick review of the combined CRRT events.

```python
# print out the above for 10 examples

```

--------------------------------

### Install PyAthena Library

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/notebooks/aline-aws/aline-awsathena.ipynb

Installs the PyAthena library, which is necessary for connecting to and querying AWS Athena. This command only needs to be run once per notebook instance.

```python
# Install OS dependencies.  This only needs to be run once for each new notebook instance.
!pip install PyAthena
```

--------------------------------

### Upload OK Example

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iv/buildmimic/bigquery/README.md

Indicates a successful table upload to BigQuery, showing the job status and table name.

```bash
Waiting on bqjob_r62d6560c318d991a_00000177f4a22a95_1 ... (4s) Current status: DONE  
OK....ADMISSIONS

```

--------------------------------

### Install and Import WordCloud

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/tutorials/data_viz/01_data_viz_basic.ipynb

Installs the wordcloud library using conda and imports the WordCloud class for generating word cloud visualizations.

```python
# !conda install -c conda-forge wordcloud -y
from wordcloud import WordCloud
```

--------------------------------

### Run Dash Server

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/tutorials/data_viz/02_dashwebapp.ipynb

Starts the Dash development server to host the web application. Ensure the port is available before running.

```python
app.run_server(port = 8050)
```

--------------------------------

### Start Apache Drill in Embedded Mode

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/buildmimic/apache-drill/README.md

Navigate to your Drill installation directory and start Drill in embedded mode. This command initiates the Drill query engine.

```bash
cd /path/to/drill
$ bin/drill-embedded
```

--------------------------------

### Create Conda Environment

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/tutorials/data_viz/README.md

Installs all necessary Python packages from the environment.yml file. Ensure you have conda installed.

```bash
conda env create -f environment.yml
```

--------------------------------

### SQL Notice Example

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iv-ed/buildmimic/postgres/README.md

This is an example of a normal SQL NOTICE indicating that a table does not exist, which occurs when the script attempts to delete tables before rebuilding them.

```sql
NOTICE:  table "XXXXXX" does not exist, skipping
```

--------------------------------

### Database Connection Setup

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/benchmark/benchmark-concepts.ipynb

Configures and establishes a connection to the PostgreSQL database using psycopg2. It sets the SQL user, password (optional), database name, and schema search path.

```python
import os
import re
import psycopg2
import getpass

from collections import OrderedDict

# database config
sqluser=getpass.getuser()
# keep sqlpass blank if using peer authentication
sqlpass=''
# database
sqldb='mimic'
sqlschema='public,mimiciii'

query_schema = 'set search_path to ' + sqlschema + ';'
```

--------------------------------

### SQL Notice Example

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iv/buildmimic/postgres/README.md

This is an example of a normal SQL NOTICE that may appear during script execution, indicating that a materialized view did not exist and was skipped. It is not an error.

```sql
NOTICE:  materialized view "XXXXXX" does not exist, skipping
```

--------------------------------

### Example MIMIC-III Query

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/buildmimic/apache-drill/README.md

An example SQL query to retrieve data from the CHARTEVENTS table within the MIMIC-III dataset loaded in Drill.

```sql
SELECT * FROM dfs.mimiciii.`CHARTEVENTS` LIMIT 10;
```

--------------------------------

### Set Search Path for Schema

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/tutorials/cohort-selection.ipynb

Prepends a statement to SQL queries to ensure they select from the correct schema. This is a common setup step for database interactions.

```python
query_schema = 'set search_path to ' + schema_name + ';'
```

--------------------------------

### Connect to MIMIC-III Database

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/tutorials/dplyr-frontend/intro.md

Establish a connection to the local MIMIC-III PostgreSQL database. Adjust connection parameters (dbname, host, port, user, password) if your setup differs.

```r
mimic <- dbConnect(
  PostgreSQL(),
  dbname = "mimic",
  host = "localhost",
  port = 5432,
  user = "mimicuser",
  password = "mimic"
)
```

--------------------------------

### Install Dash Packages

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/tutorials/data_viz/02_dashwebapp.ipynb

Installs necessary Dash and Plotly packages using conda. Ensure you have conda installed and are in the correct environment.

```bash
# # Dash packages installation
# !conda install -c conda-forge dash-renderer -y
# !conda install -c conda-forge dash -y
# !conda install -c conda-forge dash-html-components -y
# !conda install -c conda-forge dash-core-components -y
# !conda install -c conda-forge plotly -y
```

--------------------------------

### Create MIMIC-IV Database and Load Data

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iv-note/buildmimic/postgres/README.md

Creates the 'mimiciv' database, creates the schema using 'create.sql', and loads compressed data using 'load_gz.sql'. Adjust 'mimic_data_dir' if your data is located elsewhere.

```shell
# if mimiciv not exists
# createdb mimiciv
psql -d mimiciv -f mimic-iv-note/buildmimic/postgres/create.sql
psql -d mimiciv -v ON_ERROR_STOP=1 -v mimic_data_dir=mimic-iv/mimic-iv-note/2.2/note -f mimic-iv-note/buildmimic/postgres/load_gz.sql
```

--------------------------------

### Initialize AWS Athena Connection and Setup

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/notebooks/aline-aws/aline-awsathena.ipynb

Initializes the AWS SDK (boto3) and PyAthena connection. It sets up the S3 bucket for Athena query results and establishes a cursor for executing queries against the specified MIMIC-III Glue database.

```python
from pyathena import connect
from pyathena.util import as_pandas
from __future__ import print_function

# Import libraries
import datetime
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import os
import boto3
from botocore.client import ClientError
# below is used to print out pretty pandas dataframes
from IPython.display import display, HTML
%matplotlib inline


s3 = boto3.resource('s3')
client = boto3.client("sts")
account_id = client.get_caller_identity()["Account"]
my_session = boto3.session.Session()
region = my_session.region_name
athena_query_results_bucket = 'aws-athena-query-results-'+account_id+'-'+region

try:
    s3.meta.client.head_bucket(Bucket=athena_query_results_bucket)
except ClientError:
    bucket = s3.create_bucket(Bucket=athena_query_results_bucket)
    print('Creating bucket '+athena_query_results_bucket)
cusor = connect(s3_staging_dir='s3://'+athena_query_results_bucket+'/athena/temp').cursor()


# The Glue database name of your MIMIC-III parquet data
gluedatabase="mimiciii"

# location of the queries to generate aline specific materialized views
aline_path = './'

# location of the queries to generate materialized views from the MIMIC code repository
concepts_path = './concepts/'
```

--------------------------------

### Install and Import Missingno

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/tutorials/data_viz/01_data_viz_basic.ipynb

Installs the missingno library using conda and imports it for visualizing missing data.

```python
# !conda install -c conda-forge missingno -y
import missingno as msno
msno.matrix(a)
```

--------------------------------

### Display Makefile Help

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/buildmimic/postgres/README.md

Run this command in the buildmimic/postgres/ directory to see available Makefile targets and instructions.

```bash
$ make help
```

--------------------------------

### Launch MonetDB server on Windows

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/buildmimic/monetdb/README.md

Launch the MonetDB server on Windows, optionally specifying a custom data path and running as a daemon.

```bash
.\M5server.bat --dbpath=/path/you/want/to/store/your/monetdbdata --daemon=yes
```

--------------------------------

### Install and Import Pandas-Profiling

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/tutorials/data_viz/01_data_viz_basic.ipynb

Installs the pandas-profiling library using conda and imports it for use in data analysis.

```python
# !conda install -c conda-forge pandas-profiling -y
import pandas_profiling
```

--------------------------------

### Download MIMIC-IV-NOTE Data

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iv-note/buildmimic/duckdb/README.md

Use wget to recursively download MIMIC-IV-NOTE files from PhysioNet. Replace YOURUSERNAME with your actual username.

```shell
wget -r -N -c -np --user YOURUSERNAME --ask-password https://physionet.org/files/mimic-iv-note/2.2/
```

--------------------------------

### Initialize Benchmark Variables

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/benchmark/benchmark-concepts.ipynb

Sets up variables for benchmarking, including dictionaries to store query plans and times, and initializes parallel worker count to zero.

```python
query_plans_single_core = OrderedDict()
query_times_single_core = OrderedDict()
parallel_workers = 0

base_path = '/home/alistairewj/git/mimic-code/concepts'
```

--------------------------------

### Download MIMIC-IV Data

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iv/buildmimic/duckdb/README.md

Use wget to recursively download MIMIC-IV CSV files from PhysioNet. Replace YOURUSERNAME with your actual PhysioNet username.

```shell
wget -r -N -c -np --user YOURUSERNAME --ask-password https://physionet.org/files/mimiciv/2.2/
```

--------------------------------

### Initialize Google Cloud SDK

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/buildmimic/bigquery/README.md

Initializes the Google Cloud SDK, prompting the user to log in and select a default project. This step is crucial for authenticating and configuring the SDK for use.

```shell
gcloud init
```

--------------------------------

### Filter and Sort Prescriptions by Admission Date

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/notebooks/emergency-department-exploration.ipynb

Filters prescription records to include only those that started before a patient's hospital admission date and sorts them by start date.

```python
# truncate the admittime to day
admitdate = pd.Timestamp(ed.loc[143, 'admittime'].date())

# look for rows before this day
idx = (pr['subject_id'] == subject_id) & (pr['startdate'] < admitdate)
pr.loc[idx, :].sort_values('startdate')
```

--------------------------------

### Peer Authentication Failed Error Example

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iv-ed/buildmimic/postgres/README.md

This is an example of a 'Peer authentication failed' error encountered when trying to connect to the PostgreSQL database. It indicates an issue with user authentication.

```bash
psql "dbname=mimic user=postgres options=--search_path=mimic_ed" -v ON_ERROR_STOP=1 -f create.sql
psql: FATAL:  Peer authentication failed for user "postgres"
```

--------------------------------

### Load SQL script into MonetDB

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/buildmimic/monetdb/README.md

Execute a SQL script to create tables in the 'mimic' MonetDB database.

```bash
mclient -d mimic < monetdb_create_tables.sql
```

--------------------------------

### BigQuery JSON Schema Field Example

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/buildmimic/bigquery/README.md

An example of a single field definition within a BigQuery JSON schema file. It specifies the column name, data type, and nullability mode.

```json
{
    "mode": "NULLABLE",
    "name": "ROW_ID",
    "type": "INTEGER"
}
```

--------------------------------

### Configure PostgreSQL Passwordless Authentication

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/Makefile.md

Create a .pgpass file in your home directory to avoid password prompts. Replace 'mimic' and 'password' with your actual username and password. This is recommended for installation only due to clear-text password storage.

```pgpass
localhost:5432:*:mimic:password
```

--------------------------------

### Setup Chunk for R Markdown

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/tutorials/dplyr-frontend/intro.rmd

This R chunk is used to set up R Markdown options, specifically to control the display of code and results. It is typically set to include=FALSE to hide the setup code itself from the final document.

```r
knitr::opts_chunk$set(echo = TRUE)
```

--------------------------------

### Download MIMIC-IV-ED Data

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iv-ed/buildmimic/duckdb/README.md

Use wget to recursively download MIMIC-IV-ED data files from PhysioNet. Replace YOURUSERNAME with your actual PhysioNet username.

```shell
wget -r -N -c -np --user YOURUSERNAME --ask-password https://physionet.org/files/mimic-iv-ed/2.2/
```

--------------------------------

### Install R Packages and Set knitr Options

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/notebooks/aline-aws/aline_propensity_score.ipynb

Installs the 'pROC' and 'Matching' R packages, which are commonly used for ROC analysis and matching in statistical modeling. It also sets knitr chunk options to display code during report generation.

```r
install.packages('pROC')
install.packages('Matching')
knitr::opts_chunk$set(echo = TRUE)
```

--------------------------------

### SQL Query for CRRT Events

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/notebooks/crrt-notebook.ipynb

This SQL query extracts CRRT-related events from the CHARTEVENTS table, identifying RRT start, RRT, and RRT end flags. It groups events by icustay_id and episode number, filtering for episodes with distinct start and end times.

```sql
SELECT
    icustay_id,
    num,
    min(charttime) as starttime,
    max(charttime) as endtime
FROM
(
  SELECT
    icustay_id,
    CASE
      WHEN RRT_start = 1 THEN 1
      WHEN RRT_end = 1 THEN 1
      WHEN RRT = 1 THEN 1
      ELSE 0
    END AS num,
    charttime
  FROM
    mimiciii.chartevents
  WHERE
    itemid IN (225754, 225755, 225756) -- RRT status flags
    -- RRT_start = 1 or RRT = 1 or RRT_end = 1
) AS vd
WHERE
  num = 1
GROUP BY
  icustay_id,
  num
HAVING
  min(charttime) != max(charttime)
ORDER BY
  icustay_id,
  num
```

--------------------------------

### Download MIMIC-III CSV Files

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/buildmimic/duckdb/README.md

Use wget to download MIMIC-III CSV files recursively. Replace YOURUSERNAME with your PhysioNet username and ensure the correct path is specified.

```shell
wget -r -N -c -np -nH --cut-dirs=1 --user YOURUSERNAME --ask-password https://physionet.org/files/mimiciii/1.4/
```

--------------------------------

### Select Start and End Times Grouped by Linkorderid

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/tutorials/octreotide-intake.md

This SQL query selects the minimum start time and maximum end time for Octreotide intake (itemid = 225155), grouped by icustay_id and linkorderid. It filters out 'Rewritten' status descriptions and orders the results by icustay_id and starttime.

```sql
SELECT icustay_id,
       linkorderid,
       MIN(starttime) AS starttime,
       MAX(endtime)   AS endtime
FROM   inputevents_mv
WHERE  itemid = 225155
       AND statusdescription != 'Rewritten'
GROUP  BY icustay_id,
          linkorderid
ORDER  BY icustay_id,
          starttime

```

--------------------------------

### DuckDB Import Script Help

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/buildmimic/duckdb/README.md

Display the usage instructions for the import_duckdb.sh script, showing required arguments for the data directory and optional output database file.

```shell
$ ./import_duckdb.sh -h
./import_duckdb.sh:
USAGE: ./import_duckdb.sh mimic_data_dir [output_db]
WHERE:
    mimic_data_dir        directory that contains csv.gz or csv files
    output_db: optional   filename for duckdb file (default: mimic3.db)
$
```

--------------------------------

### AWS CloudFormation Template for MIMIC-III Athena Setup

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/buildmimic/aws-athena/README.md

This CloudFormation template deploys an AWS Glue Data Catalog database for MIMIC-III tables and a SageMaker Jupyter Notebook instance pre-configured to access the data via AWS Athena. It simplifies the setup process for analyzing MIMIC-III on AWS.

```yaml
AWSTemplateFormatVersion: '2010-09-09'
Description: Deploy a MIMIC-III database in AWS Glue Data Catalog and a SageMaker Notebook instance.

Parameters:
  # Parameters for SageMaker Notebook Instance
  NotebookInstanceType:
    Type: String
    Default: ml.t2.medium
    AllowedValues:
      - ml.t2.medium
      - ml.m4.xlarge
      - ml.m5.large
      - ml.m5.xlarge
      - ml.m5.4xlarge
      - ml.m5.12xlarge
      - ml.c4.xlarge
      - ml.c4.2xlarge
      - ml.c4.4xlarge
      - ml.c4.8xlarge
      - ml.c5.xlarge
      - ml.c5.2xlarge
      - ml.c5.4xlarge
      - ml.c5.9xlarge
      - ml.c5.18xlarge
      - ml.p2.xlarge
      - ml.p2.8xlarge
      - ml.p2.16xlarge
      - ml.p3.2xlarge
      - ml.p3.8xlarge
      - ml.p3.16xlarge
      - ml.g4dn.xlarge
      - ml.g4dn.2xlarge
      - ml.g4dn.4xlarge
      - ml.g4dn.8xlarge
      - ml.g4dn.12xlarge
      - ml.g4dn.16xlarge
    Description: EC2 instance type for the SageMaker notebook instance.

  NotebookInstanceVolumeSizeInGB:
    Type: Integer
    Default: 50
    Description: Size in GB of the EBS volume for the SageMaker notebook instance.

  NotebookInstanceSubnetId:
    Type: AWS::EC2::Subnet::Id
    Description: Subnet ID for the SageMaker notebook instance.

  NotebookInstanceSecurityGroupIds:
    Type: List
    Description: Security group IDs for the SageMaker notebook instance.
    ItemType: AWS::EC2::SecurityGroup::Id

Resources:
  # Glue Database for MIMIC-III
  MimicIII_Glue_Database:
    Type: AWS::Glue::Database
    Properties:
      CatalogId: !Ref AWS::AccountId
      DatabaseInput:
        Name: mimic3
        Description: MIMIC-III database hosted on AWS Open Data

  # SageMaker Notebook Instance
  MimicIII_SageMaker_Notebook_Instance:
    Type: AWS::SageMaker::NotebookInstance
    Properties:
      NotebookInstanceName: !Sub "mimic3-notebook-${AWS::AccountId}"
      InstanceType: !Ref NotebookInstanceType
      VolumeSizeInGB: !Ref NotebookInstanceVolumeSizeInGB
      SubnetId: !Ref NotebookInstanceSubnetId
      SecurityGroupIds: !Ref NotebookInstanceSecurityGroupIds
      RoleArn: !GetAtt MimicIII_SageMaker_Execution_Role.Arn
      DirectInternetAccess: Enabled
      LifecycleConfigName: !Ref MimicIII_SageMaker_Lifecycle_Config

  # IAM Role for SageMaker Notebook Instance
  MimicIII_SageMaker_Execution_Role:
    Type: AWS::IAM::Role
    Properties:
      AssumeRolePolicyDocument:
        Version: '2012-10-17'
        Statement:
          - Effect: Allow
            Principal:
              Service: !Sub "sagemaker.amazonaws.com"
            Action: sts:AssumeRole
      Path: "/"
      Policies:
        - PolicyName: !Sub "mimic3-notebook-policy-${AWS::AccountId}"
          PolicyDocument:
            Version: '2012-10-17'
            Statement:
              - Effect: Allow
                Action:
                  - "s3:GetObject"
                  - "s3:PutObject"
                  - "s3:ListBucket"
                Resource:
                  - !Sub "arn:aws:s3:::*"
              - Effect: Allow
                Action:
                  - "glue:GetDatabase"
                  - "glue:GetTable"
                  - "glue:GetPartitions"
                  - "glue:BatchGetPartition"
                Resource:
                  - !Sub "arn:aws:glue:${AWS::Region}:${AWS::AccountId}:catalog"
                  - !Sub "arn:aws:glue:${AWS::Region}:${AWS::AccountId}:database/mimic3"
                  - !Sub "arn:aws:glue:${AWS::Region}:${AWS::AccountId}:table/mimic3/*"
              - Effect: Allow
                Action:
                  - "logs:CreateLogGroup"
                  - "logs:CreateLogStream"
                  - "logs:PutLogEvents"
                Resource: !Sub "arn:aws:logs:${AWS::Region}:${AWS::AccountId}:log-group:/aws/sagemaker/NotebookInstances:log-stream-name:*/notebook.log"

  # SageMaker Lifecycle Configuration
  MimicIII_SageMaker_Lifecycle_Config:
    Type: AWS::SageMaker::NotebookInstanceLifecycleConfig
    Properties:
      NotebookInstanceLifecycleConfigName: !Sub "mimic3-lifecycle-${AWS::AccountId}"
      OnCreate:
        - Content: !Base64 |
            #!/bin/bash
            set -eux
            # Install necessary packages
            pip install boto3 pandas awswrangler

            # Configure AWS CLI
            aws configure set default.region us-east-1

            # Create a sample notebook (optional)
            mkdir -p /home/ec2-user/SageMaker/sample-notebooks
            cat <<EOF > /home/ec2-user/SageMaker/sample-notebooks/mimic3-query-example.ipynb
            {
             "cells": [
              {
               "cell_type": "code",
               "execution_count": null,
               "metadata": {},
               "outputs": [],
               "source": [
                "import awswrangler as wr"
                "import pandas as pd"
                "\n"
                "# Define the MIMIC-III database and table name\n"
                "database_name = 'mimic3'\n"
                "table_name = 'admissions' # Example table\n"
                "\n"
                "# Construct the SQL query\n"
                "query = f"SELECT * FROM {database_name}.{table_name} LIMIT 10"\n"
                "\n"
                "# Execute the query using AWS Athena via awswrangler\n"
                "try:\n"
                "    df = wr.athena.read_sql_query(query, database=database_name)\n"
                "    print(f'Successfully queried {table_name}. Displaying first 5 rows:\n')\n"
                "    print(df.head().to_markdown())\n"
                "except Exception as e:\n"
                "    print(f'Error querying {table_name}: {e}')\n"
               ]
              }
             ],
             "metadata": {
              "kernelspec": {
               "display_name": "Python 3",
               "language": "python",
               "name": "python3"
              },
              "language_info": {
               "codemirror_mode": {
                "name": "ipython",
                "version": 3
               },
               "file_extension": ".py",
               "mimetype": "text/x-python",
               "name": "python",
               "nbconvert_exporter": "python",
               "pygments_lexer": "ipython3",
               "version": "3.7.10"
              }
             },
             "nbformat": 4,
             "nbformat_minor": 4
            }
            EOF

```

--------------------------------

### Create MIMIC Database with Custom Parameters

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/buildmimic/postgres/README.md

Modify Makefile parameters like database name, password, and host when creating the MIMIC-III database.

```bash
$ make create-user mimic datadir="/path/to/data/" DBNAME="my_db" DBPASS="my_pass" DBHOST="192.168.0.1"
```

--------------------------------

### Search for String Starting with Pattern

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/tutorials/sql-intro.md

Selects data where the first_careunit string begins with 'ICU'.

```sql
SELECT *
FROM icustays
WHERE first_careunit LIKE 'ICU%';
```

--------------------------------

### Create MIMIC Database User with OS Username

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/buildmimic/postgres/README.md

Use the 'make create-user' command to create a database user, specifying the operating system username for DBUSER.

```bash
make create-user mimic-gz datadir="$datadir" DBUSER="$USER"
```

--------------------------------

### Database Connection Parameters

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/notebooks/ipynb_example/icu_length_of_stay.ipynb

Defines connection parameters for a PostgreSQL database. Ensure these match your database setup.

```python
# Create a database connection
user = 'postgres'
host = 'localhost'
dbname = 'mimic'
schema = 'mimiciii_demo'
```

--------------------------------

### Create a New Database

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iv/buildmimic/postgres/README.md

Command to create a new PostgreSQL database named 'mimiciv'. This is an optional step if the database does not already exist.

```bash
createdb mimiciv
```

--------------------------------

### Initialize Data Structures for Benchmarking

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/benchmark/benchmark-concepts.ipynb

Initializes ordered dictionaries to store query plans and execution times for benchmarking multiple concepts.

```python
query_plans = OrderedDict()
query_times = OrderedDict()

base_path = '/home/alistairewj/git/mimic-code/concepts'

```

--------------------------------

### Upload FAIL Example

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iv/buildmimic/bigquery/README.md

Indicates a failed table upload to BigQuery, providing BigQuery error details and the table name.

```bash
Waiting on bqjob_r3c23bb4d717cd8a9_000001620e9d5f6d_1 ... (496s) Current status: DONE
BigQuery error in load operation: Error processing job
'sandbox-nlp:bqjob_r3c23bb4d717cd8a9_000001620e9d5f6d_1': Error while reading data, error message: CSV table encountered too many errors, giving up. Rows: 63349; errors: 1. Please look into the error stream for more details.
Failure details:
- gs://mimiciv-1.0.physionet.org/chartevents.csv.gz: Error while reading data,
error message: Could not parse 'No' as double for field VALUE
(position 8) starting at location 3353598526
FAIL..chartevents

```

--------------------------------

### Connect to MIMIC Database and Set Search Path

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/buildmimic/postgres/README.md

Commands to connect to the PostgreSQL database named 'mimic' and set the default schema to 'mimiciii'.

```bash
# connect to database mimic
$ psql -d mimic
# set default schema to mimiciii
mimic=# SET search_path TO mimiciii;
```

--------------------------------

### Get Unique Admission Types

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/tutorials/sql-crosstab.md

Retrieve distinct admission types from the admissions table. This helps identify unique column categories for crosstabulation.

```sql
select distinct admission_type from admissions order by admission_type;
```

--------------------------------

### Establish Database Connection

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/notebooks/aline/aline.ipynb

Connects to the PostgreSQL database using specified credentials. Sets the search path to include the public schema and the MIMIC-III schema for accessing tables and creating materialized views.

```python
# connect to the database
con = psycopg2.connect(dbname=dbname, user=sqluser, password=sqlpass, host=host)

# all queries are prepended by this statement to ensure we use the correct schema
query_schema = 'SET SEARCH_PATH TO public,' + schema_name + ';'
# note that by placing 'public' first, we create materialized views on the public schema
# ... but can still access tables on the `schema_name` table (usually mimiciii)
```

--------------------------------

### Set MonetDB data directory on Windows

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/buildmimic/monetdb/README.md

Modify M5server.bat to specify a custom directory for MonetDB data storage on Windows.

```bash
rem ------- Change DB path ---------
rem We move the database directory to a local folder
set MONETDBDIR=C:\\path\you\want
set MONETDBFARM="--dbpath=%MONETDBDIR%\dbfarm\demo"
```

--------------------------------

### Get Unique Admission Locations

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/tutorials/sql-crosstab.md

Retrieve distinct admission locations from the admissions table. This helps identify unique row categories for crosstabulation.

```sql
select distinct admission_location from admissions order by admission_location;
```

--------------------------------

### Import Libraries for MIMIC-IV Analysis

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iv/notebooks/tableone.ipynb

Imports necessary Python libraries for data manipulation, visualization, and statistical analysis. Ensure these libraries are installed before running.

```python
from collections import OrderedDict

import matplotlib.pyplot as plt
import numpy as np
import pandas as pd
import seaborn as sns
from tableone import TableOne
```

--------------------------------

### Create MIMIC Database with Custom Data Directory

Source: https://github.com/mit-lcp/mimic-code/blob/main/mimic-iii/buildmimic/postgres/README.md

This Makefile command creates the user, database, and schema for MIMIC-III, specifying the location of your zipped CSV data files.

```bash
$ make create-user mimic datadir="/path/to/data/"
```