query/queries_and_views.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Queries and views for FHIR Data Pipes\n",
    "This notebook includes examples for how to query data-warehouse files created\n",
    "by FHIR Data Pipes. For the query engine, it is assumed that Spark SQL is\n",
    "being used (see section below). But hopefully the SQL queries show the generic\n",
    "pattern that can be used for any query engine that supports SQL on Parquet\n",
    "files.\n",
    "\n",
    "The main recommended pattern is to create simple and flat views out of the\n",
    "complex nested/repeated schema that SQL-on-FHIR has. We first show how to do\n",
    "this with SQL (Spark flavor of it). At the end, we also show how do to this with\n",
    "[FHIR-views](https://github.com/google/fhir-py/tree/main/google-fhir-views).\n",
    "Note that FHIR-views runner for Spark is still an experimental feature."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Prerequisites:\n",
    "\n",
    "**Note**: All commands need to be run from the root directory of the\n",
    "fhir-data-pipes repo. We are setting up fhir-data-pipes controller and\n",
    "a single node Spark process. The input synthetic data is uploaded to a local\n",
    "HAPI server.\n",
    "\n",
    "1. Bring up a Hive ThriftServer and the pipeline controller (note that by\n",
    "   default, the pipeline only fetches Patient, Encounter, Observation, and\n",
    "   Condition resources. If you want other resources to be included as well,\n",
    "   you can edit\n",
    "   [this line](https://github.com/google/fhir-data-pipes/blob/055ecaa043bfaa9736d857ac20d142f67c67fa61/docker/config/application.yaml#L34)\n",
    "   before running the controller):\n",
    "    ```\n",
    "    docker-compose  -f ./docker/compose-controller-spark-sql-single.yaml up --build  --force-recreate -d\n",
    "    ```\n",
    "\n",
    "3. Bring up a HAPI FHIR server:\n",
    "    ```\n",
    "    docker-compose  -f ./docker/hapi-compose.yml up --force-recreate -d\n",
    "    ```\n",
    "\n",
    "4. Load data into the HAPI FHIR server:\n",
    "    ```\n",
    "    python3 ./synthea-hiv/uploader/main.py HAPI http://localhost:8091/fhir --input_dir ./synthea-hiv/sample_data\n",
    "    ```\n",
    "\n",
    "5. In the FHIR Pipelines Control Panel page, http://localhost:8090, click on the\n",
    "   “Run Full” button to convert the FHIR data to Parquet files with SQL-on-FHIR\n",
    "   schema. These files will be stored under the `docker/dwh` directory."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Installation\n",
    "\n",
    "**NOTE: If you are running this notebook inside the corresponding docker\n",
    "container, you can skip the installation section as the requirements are\n",
    "already included in that docker image.**\n",
    "\n",
    "It's recommended to set up a virtual environment before starting the notebook\n",
    "kernel to avoid any dependency version issues with your native environment.\n",
    "\n",
    "This can be done with the following commands in a terminal, if you have Conda\n",
    "installed (you can also use `virtualenv` instead of Conda):\n",
    "\n",
    "```\n",
    "    conda create -n python310 python=3.10 -y\n",
    "    conda activate python310\n",
    "    conda install ipykernel -y\n",
    "    ipython kernel install --user --name=python310\n",
    "    pip install -r requirements.txt\n",
    "```"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Environment setup\n",
    "The cell below sets up a Spark client and creates a Spark view \"runner\",\n",
    "which is used to apply declarative views of FHIR in Spark. Note you may\n",
    "need to do adjustments for the Thrift Sever address (`hive://`).\n",
    "The value below assumes you are running this notebook in a docker container\n",
    "on the same network as [this config](https://github.com/google/fhir-data-pipes/blob/3694f1394d0b9011ab480ca61f0bd0568bca2f53/docker/compose-controller-spark-sql-single.yaml#L71)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas\n",
    "\n",
    "from sqlalchemy import dialects\n",
    "from sqlalchemy import engine\n",
    "\n",
    "dialects.registry.register(\"hive\", \"pyhive.sqlalchemy_hive\", \"HiveDialect\")\n",
    "\n",
    "# The endpoint of the Hive ThriftServer to connect to; you may need to\n",
    "# adjust this if you are not running this through the default docker container.\n",
    "#query_engine = engine.create_engine(\"hive://localhost:10001/default\")\n",
    "query_engine = engine.create_engine(\"hive://spark-thriftserver:10000/default\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The cell below loads the parquet files we've created into our Spark ThriftServer.\n",
    "\n",
    "**Note 1**: These tables are automatically created if `createHiveResourceTables`\n",
    "is set to `true` like\n",
    "[here](https://github.com/google/fhir-data-pipes/blob/3694f1394d0b9011ab480ca61f0bd0568bca2f53/docker/config/application.yaml#L34).\n",
    "\n",
    "**Note 2**: If you choose to run the following cell and recreate the tables,\n",
    "you probably need to override `destination_directory_path` to point to the\n",
    "specific path of the last pipeline run, e.g.,\n",
    "`/dwh/controller_DWH_TIMESTAMP_2023_09_07T18_11_10_988888411Z`.\n",
    "The default value below, i.e., `/dwh/controller_*`, assigns _all_ Parquet files\n",
    "in _all_ of such directories into a _single_ table for each resource.\n",
    "\n",
    "**Note 3**: The full name of tables have a database name as well which is\n",
    "`default` but default; so instead of `default.Patient` we simply use `Patient`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Please read the note above before recreating resource tables!\n",
    "\n",
    "destination_directory_path = \"/dwh/controller_*\"\n",
    "with query_engine.connect() as con:\n",
    "    # The following lines are commented out to prevent unintentional deletion.\n",
    "    # con.execute(f\"DROP TABLE IF EXISTS Encounter;\")\n",
    "    # con.execute(f\"DROP TABLE IF EXISTS Observation;\")\n",
    "    # con.execute(f\"DROP TABLE IF EXISTS Patient;\")\n",
    "    con.execute(\n",
    "        f\"CREATE TABLE IF NOT EXISTS Encounter USING\"\n",
    "        f\" PARQUET LOCATION '{destination_directory_path}/Encounter/*.parquet';\"\n",
    "    )\n",
    "    con.execute(\n",
    "        f\"CREATE TABLE IF NOT EXISTS Observation USING PARQUET\"\n",
    "        f\" LOCATION '{destination_directory_path}/Observation/*.parquet';\"\n",
    "    )\n",
    "    con.execute(\n",
    "        f\"CREATE TABLE IF NOT EXISTS Patient USING PARQUET\"\n",
    "        f\" LOCATION '{destination_directory_path}/Patient/*.parquet';\"\n",
    "    )"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Explore the data with SQL\n",
    "At this point you should be able to run any SQL query against the resource\n",
    "tables. The following subsections show some sample queries. If you want, you can\n",
    "skip directly to the [FHIR-vewis section](#FHIR-Views)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count(1)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>79</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count(1)\n",
       "0        79"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pandas.read_sql_query(\n",
    "    sql=f\"SELECT COUNT(*) FROM Patient\",\n",
    "    con=query_engine,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count(1)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5680</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count(1)\n",
       "0      5680"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pandas.read_sql_query(\n",
    "    sql=(f\"SELECT COUNT(*) FROM Observation AS O\"\n",
    "      \" WHERE O.effective.DateTime > '2010-01-01'\"),\n",
    "    con=query_engine,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exploring Observation codes\n",
    "The [SQL-on-FHIR schema](https://github.com/FHIR/sql-on-fhir/blob/master/sql-on-fhir.md)\n",
    "resembles the JSON structure of FHIR resources and hence has many nested and\n",
    "repeated structures. It usually makes sense to flatten the columns we need to\n",
    "deal with. This flattening process varies depending on the SQL dialect.\n",
    "Here is an example for Observation codes.\n",
    "\n",
    "_Note_: The actual codes and systems should be ignored as this is for a\n",
    "synthetic datasets that has not gone through concept mapping."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code_sys</th>\n",
       "      <th>code</th>\n",
       "      <th>display</th>\n",
       "      <th>num_obs</th>\n",
       "      <th>avg_val</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1271AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>TESTS ORDERED</td>\n",
       "      <td>4949</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1088AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>CURRENT ANTIRETROVIRAL DRUGS USED FOR TREATMENT</td>\n",
       "      <td>2387</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1111AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>PATIENT REPORTED CURRENT TB TREATMENT</td>\n",
       "      <td>2043</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1250AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>ANTIRETROVIRALS STARTED</td>\n",
       "      <td>1805</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1270AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>TUBERCULOSIS TREATMENT STARTED</td>\n",
       "      <td>1121</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>159800AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>REVIEW OF TUBERCULOSIS SCREENING QUESTIONS</td>\n",
       "      <td>738</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>5085AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>LOINC Code</td>\n",
       "      <td>530</td>\n",
       "      <td>163.291043</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>159911AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>PATIENT REPORTED CURRENT ANTIRETROVIRAL TREATMENT</td>\n",
       "      <td>408</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1261AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>PCP PROPHYLAXIS PLAN</td>\n",
       "      <td>340</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1265AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>TUBERCULOSIS PROPHYLAXIS PLAN</td>\n",
       "      <td>340</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1255AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>ANTIRETROVIRAL PLAN</td>\n",
       "      <td>340</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1268AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>TUBERCULOSIS TREATMENT PLAN</td>\n",
       "      <td>340</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1040AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>HIV RAPID TEST, QUALITATIVE</td>\n",
       "      <td>265</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>5088AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>TEMPERATURE</td>\n",
       "      <td>265</td>\n",
       "      <td>36.962125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>5092AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>BLOOD OXYGEN SATURATION</td>\n",
       "      <td>265</td>\n",
       "      <td>84.206551</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>5087AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>PULSE</td>\n",
       "      <td>265</td>\n",
       "      <td>124.562355</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1030AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>HIV DNA POLYMERASE CHAIN REACTION, QUALITATIVE</td>\n",
       "      <td>265</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>856AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>HIV viral load</td>\n",
       "      <td>265</td>\n",
       "      <td>484166.081132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>5096AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>Return visit date</td>\n",
       "      <td>190</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>5090AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>Height</td>\n",
       "      <td>79</td>\n",
       "      <td>170.123797</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            code_sys                                  code  \\\n",
       "0   http://loinc.org  1271AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "1   http://loinc.org  1088AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "2   http://loinc.org  1111AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "3   http://loinc.org  1250AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "4   http://loinc.org  1270AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "5   http://loinc.org  159800AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "6   http://loinc.org  5085AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "7   http://loinc.org  159911AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "8   http://loinc.org  1261AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "9   http://loinc.org  1265AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "10  http://loinc.org  1255AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "11  http://loinc.org  1268AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "12  http://loinc.org  1040AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "13  http://loinc.org  5088AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "14  http://loinc.org  5092AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "15  http://loinc.org  5087AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "16  http://loinc.org  1030AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "17  http://loinc.org  856AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "18  http://loinc.org  5096AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "19  http://loinc.org  5090AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "\n",
       "                                              display  num_obs        avg_val  \n",
       "0                                       TESTS ORDERED     4949            NaN  \n",
       "1     CURRENT ANTIRETROVIRAL DRUGS USED FOR TREATMENT     2387            NaN  \n",
       "2               PATIENT REPORTED CURRENT TB TREATMENT     2043            NaN  \n",
       "3                             ANTIRETROVIRALS STARTED     1805            NaN  \n",
       "4                      TUBERCULOSIS TREATMENT STARTED     1121            NaN  \n",
       "5          REVIEW OF TUBERCULOSIS SCREENING QUESTIONS      738            NaN  \n",
       "6                                          LOINC Code      530     163.291043  \n",
       "7   PATIENT REPORTED CURRENT ANTIRETROVIRAL TREATMENT      408            NaN  \n",
       "8                                PCP PROPHYLAXIS PLAN      340            NaN  \n",
       "9                       TUBERCULOSIS PROPHYLAXIS PLAN      340            NaN  \n",
       "10                                ANTIRETROVIRAL PLAN      340            NaN  \n",
       "11                        TUBERCULOSIS TREATMENT PLAN      340            NaN  \n",
       "12                        HIV RAPID TEST, QUALITATIVE      265            NaN  \n",
       "13                                        TEMPERATURE      265      36.962125  \n",
       "14                            BLOOD OXYGEN SATURATION      265      84.206551  \n",
       "15                                              PULSE      265     124.562355  \n",
       "16     HIV DNA POLYMERASE CHAIN REACTION, QUALITATIVE      265            NaN  \n",
       "17                                     HIV viral load      265  484166.081132  \n",
       "18                                  Return visit date      190            NaN  \n",
       "19                                             Height       79     170.123797  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pandas.read_sql_query(\n",
    "    sql=\"\"\"\n",
    "      SELECT OCC.`system` AS code_sys, OCC.code, OCC.display,\n",
    "        COUNT(*) AS num_obs, AVG(O.value.quantity.value) AS avg_val\n",
    "      FROM Observation AS O LATERAL VIEW explode(code.coding) AS OCC\n",
    "      GROUP BY OCC.`system`, OCC.code, OCC.display\n",
    "      ORDER BY num_obs DESC\n",
    "      LIMIT 20; \"\"\",\n",
    "    con=query_engine,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Indicator example\n",
    "The following query counts number of patients that have had an observation\n",
    "with a specific code (HIV viral load), with a value below a certain threshold\n",
    "(400000), during a specific reporting period (year 2010 in this example).\n",
    "This is a useful pattern in many cases, e.g., calculating TX_CURR or TX_PVLS\n",
    "indicators of [PEPFAR](https://www.state.gov/pepfar-fy-2023-mer-indicators/)\n",
    "(for TX_CURR we need to look at `O.value.codeableConcept.coding`)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>num_patients</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   num_patients\n",
       "0             5"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pandas.read_sql_query(\n",
    "    sql=\"\"\"\n",
    "      SELECT COUNT(DISTINCT O.subject.PatientId) AS num_patients\n",
    "      FROM Observation AS O LATERAL VIEW explode(code.coding) AS OCC\n",
    "      WHERE OCC.code LIKE '856%%'\n",
    "        AND OCC.`system` = 'http://loinc.org'\n",
    "        AND O.value.quantity.value < 400000\n",
    "        AND YEAR(O.effective.dateTime) = 2010; \"\"\",\n",
    "    con=query_engine,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating flat views\n",
    "It is usually helpful to create flat views and build other queries\n",
    "on top of those views. Here is an example for Observation:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>obs_id</th>\n",
       "      <th>patient_id</th>\n",
       "      <th>code_sys</th>\n",
       "      <th>code</th>\n",
       "      <th>val_quantity</th>\n",
       "      <th>val_code</th>\n",
       "      <th>val_sys</th>\n",
       "      <th>obs_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10030</td>\n",
       "      <td>9040</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>159800AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>140238AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>2009-12-19T03:22:24+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10063</td>\n",
       "      <td>9040</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>5088AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>36.921</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2011-03-21T03:22:24+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10075</td>\n",
       "      <td>9040</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1271AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1107AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>2011-03-21T03:22:24+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10083</td>\n",
       "      <td>9040</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1271AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>305AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>2011-03-21T03:22:24+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10197</td>\n",
       "      <td>9040</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>159911AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1652AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>2013-04-30T03:22:24+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  obs_id patient_id          code_sys                                  code  \\\n",
       "0  10030       9040  http://loinc.org  159800AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "1  10063       9040  http://loinc.org  5088AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "2  10075       9040  http://loinc.org  1271AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "3  10083       9040  http://loinc.org  1271AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "4  10197       9040  http://loinc.org  159911AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "\n",
       "   val_quantity                              val_code                 val_sys  \\\n",
       "0           NaN  140238AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA  http://snomed.info/sct   \n",
       "1        36.921                                  None                    None   \n",
       "2           NaN  1107AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA  http://snomed.info/sct   \n",
       "3           NaN  305AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA  http://snomed.info/sct   \n",
       "4           NaN  1652AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA  http://snomed.info/sct   \n",
       "\n",
       "                    obs_date  \n",
       "0  2009-12-19T03:22:24+00:00  \n",
       "1  2011-03-21T03:22:24+00:00  \n",
       "2  2011-03-21T03:22:24+00:00  \n",
       "3  2011-03-21T03:22:24+00:00  \n",
       "4  2013-04-30T03:22:24+00:00  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with query_engine.connect() as con:\n",
    "    con.execute(\"\"\"\n",
    "      CREATE OR REPLACE VIEW flat_observation AS\n",
    "      SELECT O.id AS obs_id, O.subject.PatientId AS patient_id,\n",
    "        OCC.`system` AS code_sys, OCC.code,\n",
    "        O.value.quantity.value AS val_quantity,\n",
    "        OVCC.code AS val_code, OVCC.`system` AS val_sys,\n",
    "        O.effective.dateTime AS obs_date\n",
    "      FROM Observation AS O LATERAL VIEW OUTER explode(code.coding) AS OCC\n",
    "        LATERAL VIEW OUTER explode(O.value.codeableConcept.coding) AS OVCC\n",
    "      ;\n",
    "      \"\"\"\n",
    "    )\n",
    "\n",
    "pandas.read_sql_query(\n",
    "    sql=\"SELECT * FROM flat_observation LIMIT 5;\",\n",
    "    con=query_engine,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Repeating the same TX_CURR query with the new view is much more readable:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>num_patients</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   num_patients\n",
       "0             5"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pandas.read_sql_query(\n",
    "    sql=\"\"\"SELECT COUNT(DISTINCT patient_id) AS num_patients\n",
    "      FROM flat_observation\n",
    "      WHERE code LIKE '856%%'\n",
    "        AND code_sys = 'http://loinc.org'\n",
    "        AND val_quantity < 400000\n",
    "        AND YEAR(obs_date) = 2010\n",
    "      LIMIT 100; \"\"\",\n",
    "    con=query_engine,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Sample flat views\n",
    "In this section we create sample flat views for common resources. Flat views\n",
    "are usually lossy transformations, i.e., we drop some fields of the original\n",
    "FHIR resource and flatten those we need. So each subsection has a short\n",
    "description of how each flat view is created. These are provided as examples\n",
    "to be used as the basis of other purpose-built flat views."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Observation\n",
    "We already saw an example flat view for the Observation resource. Here we expand\n",
    "that example by including a few more fields, e.g., `status` and `category`. We\n",
    "also add `encounter_id` as it is usually useful for joining Observation and\n",
    "Encounter tables/views.\n",
    "\n",
    "The main idea of \"flattening\" is to \"explode\" each row based on its repeated\n",
    "fields to eliminate the array. For example, `Observation.code` has a `coding`\n",
    "array. So if an Observation has 3 `code.coding`, conceptually, the\n",
    "`LATERAL VIEW OUTER explode(code.coding)` expression below creates 3 copies\n",
    "of that row where each have one of those `code.coding`s. When `explode` is\n",
    "done on multiple fields, it is conceptually like the cartesian product of those\n",
    "arrays. In practical scenarios, usually there are also constrains in the `WHERE`\n",
    "clause to limit the number of rows in the view to those relevant to the\n",
    "use-case (for example only pick rows with certain codes)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>obs_id</th>\n",
       "      <th>patient_id</th>\n",
       "      <th>encounter_id</th>\n",
       "      <th>status</th>\n",
       "      <th>code</th>\n",
       "      <th>code_sys</th>\n",
       "      <th>val_quantity</th>\n",
       "      <th>val_code</th>\n",
       "      <th>val_sys</th>\n",
       "      <th>obs_date</th>\n",
       "      <th>category_sys</th>\n",
       "      <th>category_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10030</td>\n",
       "      <td>9040</td>\n",
       "      <td>10021</td>\n",
       "      <td>final</td>\n",
       "      <td>159800AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>NaN</td>\n",
       "      <td>140238AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>2009-12-19T03:22:24+00:00</td>\n",
       "      <td>http://terminology.hl7.org/CodeSystem/observat...</td>\n",
       "      <td>survey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10063</td>\n",
       "      <td>9040</td>\n",
       "      <td>10062</td>\n",
       "      <td>final</td>\n",
       "      <td>5088AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>36.921</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2011-03-21T03:22:24+00:00</td>\n",
       "      <td>http://terminology.hl7.org/CodeSystem/observat...</td>\n",
       "      <td>survey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10075</td>\n",
       "      <td>9040</td>\n",
       "      <td>10062</td>\n",
       "      <td>final</td>\n",
       "      <td>1271AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1107AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>2011-03-21T03:22:24+00:00</td>\n",
       "      <td>http://terminology.hl7.org/CodeSystem/observat...</td>\n",
       "      <td>survey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10083</td>\n",
       "      <td>9040</td>\n",
       "      <td>10062</td>\n",
       "      <td>final</td>\n",
       "      <td>1271AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>NaN</td>\n",
       "      <td>305AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>2011-03-21T03:22:24+00:00</td>\n",
       "      <td>http://terminology.hl7.org/CodeSystem/observat...</td>\n",
       "      <td>survey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10197</td>\n",
       "      <td>9040</td>\n",
       "      <td>10166</td>\n",
       "      <td>final</td>\n",
       "      <td>159911AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1652AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>2013-04-30T03:22:24+00:00</td>\n",
       "      <td>http://terminology.hl7.org/CodeSystem/observat...</td>\n",
       "      <td>survey</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  obs_id patient_id encounter_id status                                  code  \\\n",
       "0  10030       9040        10021  final  159800AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "1  10063       9040        10062  final  5088AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "2  10075       9040        10062  final  1271AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "3  10083       9040        10062  final  1271AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "4  10197       9040        10166  final  159911AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "\n",
       "           code_sys  val_quantity                              val_code  \\\n",
       "0  http://loinc.org           NaN  140238AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "1  http://loinc.org        36.921                                  None   \n",
       "2  http://loinc.org           NaN  1107AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "3  http://loinc.org           NaN  305AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "4  http://loinc.org           NaN  1652AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "\n",
       "                  val_sys                   obs_date  \\\n",
       "0  http://snomed.info/sct  2009-12-19T03:22:24+00:00   \n",
       "1                    None  2011-03-21T03:22:24+00:00   \n",
       "2  http://snomed.info/sct  2011-03-21T03:22:24+00:00   \n",
       "3  http://snomed.info/sct  2011-03-21T03:22:24+00:00   \n",
       "4  http://snomed.info/sct  2013-04-30T03:22:24+00:00   \n",
       "\n",
       "                                        category_sys category_code  \n",
       "0  http://terminology.hl7.org/CodeSystem/observat...        survey  \n",
       "1  http://terminology.hl7.org/CodeSystem/observat...        survey  \n",
       "2  http://terminology.hl7.org/CodeSystem/observat...        survey  \n",
       "3  http://terminology.hl7.org/CodeSystem/observat...        survey  \n",
       "4  http://terminology.hl7.org/CodeSystem/observat...        survey  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with query_engine.connect() as con:\n",
    "    con.execute(\"\"\"\n",
    "      CREATE OR REPLACE VIEW Observation_flat AS\n",
    "      SELECT O.id AS obs_id, O.subject.patientId AS patient_id,\n",
    "        O.encounter.encounterId as encounter_id,\n",
    "        O.status, OCC.code, OCC.`system` AS code_sys,\n",
    "        O.value.quantity.value AS val_quantity,\n",
    "        OVCC.code AS val_code, OVCC.`system` AS val_sys,\n",
    "        O.effective.dateTime AS obs_date,\n",
    "        OCatC.`system` AS category_sys,\n",
    "        OCatC.code AS category_code\n",
    "      FROM Observation AS O LATERAL VIEW OUTER explode(code.coding) AS OCC\n",
    "        LATERAL VIEW OUTER explode(O.value.codeableConcept.coding) AS OVCC\n",
    "        LATERAL VIEW OUTER explode(O.category) AS OCat\n",
    "        LATERAL VIEW OUTER explode(OCat.coding) AS OCatC\n",
    "      ;\n",
    "      \"\"\"\n",
    "    )\n",
    "\n",
    "pandas.read_sql_query(\n",
    "    sql=\"SELECT * FROM Observation_flat LIMIT 5;\",\n",
    "    con=query_engine,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                                 category_sys\n",
      "0  http://terminology.hl7.org/CodeSystem/observation-category\n",
      "1  http://terminology.hl7.org/CodeSystem/observation-category\n",
      "2  http://terminology.hl7.org/CodeSystem/observation-category\n",
      "3  http://terminology.hl7.org/CodeSystem/observation-category\n",
      "4  http://terminology.hl7.org/CodeSystem/observation-category\n"
     ]
    }
   ],
   "source": [
    "with pandas.option_context('display.max_colwidth', None):\n",
    "    print(pandas.read_sql_query(\n",
    "        sql=\"SELECT category_sys FROM Observation_flat LIMIT 5;\",\n",
    "        con=query_engine,\n",
    "    ))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As mentioned above, because of the explode effect, the number of rows in the\n",
    "view can be much larger than the original number of resources (for example if\n",
    "therea are multiple codes for an Observation). So care should be taken with\n",
    "duplicated rows. But that's not the case in our synthetics observations and\n",
    "the above view:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count(1)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>17279</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count(1)\n",
       "0     17279"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pandas.read_sql_query(\n",
    "    sql=\"SELECT COUNT(*) FROM Observation_flat;\",\n",
    "    con=query_engine,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count(DISTINCT obs_id)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>17279</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count(DISTINCT obs_id)\n",
       "0                   17279"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pandas.read_sql_query(\n",
    "    sql=\"SELECT COUNT(DISTINCT obs_id) FROM Observation_flat;\",\n",
    "    con=query_engine,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Patient\n",
    "We follow the same pattern for Patient resources. Here we are picking some\n",
    "of the commonly used fields and some of the reference IDs, e.g.,\n",
    "`generalPractitioner` and `managingOrganization`. Note that in this example\n",
    "we also have a computed field `age` which is derived from `birthDate`. This is\n",
    "only provided as an example as the logic is incomplete without taking into\n",
    "account the `deceased` field:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>pat_id</th>\n",
       "      <th>active</th>\n",
       "      <th>family</th>\n",
       "      <th>given</th>\n",
       "      <th>gender</th>\n",
       "      <th>deceased</th>\n",
       "      <th>age</th>\n",
       "      <th>country</th>\n",
       "      <th>practitioner_id</th>\n",
       "      <th>organization_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>27113</td>\n",
       "      <td>None</td>\n",
       "      <td>Rogahn59</td>\n",
       "      <td>Heriberto162</td>\n",
       "      <td>male</td>\n",
       "      <td>None</td>\n",
       "      <td>61</td>\n",
       "      <td>US</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>38462</td>\n",
       "      <td>None</td>\n",
       "      <td>Weissnat378</td>\n",
       "      <td>Lanie389</td>\n",
       "      <td>female</td>\n",
       "      <td>None</td>\n",
       "      <td>62</td>\n",
       "      <td>US</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>39570</td>\n",
       "      <td>None</td>\n",
       "      <td>Gutmann970</td>\n",
       "      <td>Gregg522</td>\n",
       "      <td>male</td>\n",
       "      <td>None</td>\n",
       "      <td>108</td>\n",
       "      <td>US</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9040</td>\n",
       "      <td>None</td>\n",
       "      <td>Turner526</td>\n",
       "      <td>Isaura563</td>\n",
       "      <td>female</td>\n",
       "      <td>None</td>\n",
       "      <td>66</td>\n",
       "      <td>US</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9040</td>\n",
       "      <td>None</td>\n",
       "      <td>Vandervort697</td>\n",
       "      <td>Isaura563</td>\n",
       "      <td>female</td>\n",
       "      <td>None</td>\n",
       "      <td>66</td>\n",
       "      <td>US</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  pat_id active         family         given  gender deceased  age country  \\\n",
       "0  27113   None       Rogahn59  Heriberto162    male     None   61      US   \n",
       "1  38462   None    Weissnat378      Lanie389  female     None   62      US   \n",
       "2  39570   None     Gutmann970      Gregg522    male     None  108      US   \n",
       "3   9040   None      Turner526     Isaura563  female     None   66      US   \n",
       "4   9040   None  Vandervort697     Isaura563  female     None   66      US   \n",
       "\n",
       "  practitioner_id organization_id  \n",
       "0            None            None  \n",
       "1            None            None  \n",
       "2            None            None  \n",
       "3            None            None  \n",
       "4            None            None  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with query_engine.connect() as con:\n",
    "    con.execute(\"\"\"\n",
    "      CREATE OR REPLACE VIEW Patient_flat AS\n",
    "      SELECT P.id AS pat_id, P.active, PN.family, PNG AS given, P.gender,\n",
    "        P.deceased.Boolean AS deceased,\n",
    "        YEAR(current_date()) - YEAR(P.birthDate) AS age,\n",
    "        PA.country, PG.practitionerId AS practitioner_id,\n",
    "        P.managingOrganization.organizationId AS organization_id\n",
    "      FROM Patient AS P LATERAL VIEW OUTER explode(name) AS PN\n",
    "        LATERAL VIEW OUTER explode(PN.given) AS PNG\n",
    "        LATERAL VIEW OUTER explode(P.address) AS PA\n",
    "        LATERAL VIEW OUTER explode(P.generalPractitioner) AS PG\n",
    "      ;\n",
    "      \"\"\"\n",
    "    )\n",
    "\n",
    "pandas.read_sql_query(\n",
    "    sql=\"SELECT * FROM Patient_flat LIMIT 5;\",\n",
    "    con=query_engine,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count(1)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>106</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count(1)\n",
       "0       106"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pandas.read_sql_query(\n",
    "    sql=\"SELECT COUNT(*) FROM Patient_flat;\",\n",
    "    con=query_engine,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count(DISTINCT pat_id)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>79</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count(DISTINCT pat_id)\n",
       "0                      79"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pandas.read_sql_query(\n",
    "    sql=\"SELECT COUNT(DISTINCT pat_id) FROM Patient_flat;\",\n",
    "    con=query_engine,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Encounter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>enc_id</th>\n",
       "      <th>status</th>\n",
       "      <th>type_sys</th>\n",
       "      <th>type_code</th>\n",
       "      <th>patient_id</th>\n",
       "      <th>practitioner_id</th>\n",
       "      <th>location_id</th>\n",
       "      <th>service_org_id</th>\n",
       "      <th>start</th>\n",
       "      <th>end</th>\n",
       "      <th>EpisodeOfCareId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10357</td>\n",
       "      <td>finished</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>162673000</td>\n",
       "      <td>9040</td>\n",
       "      <td>2791</td>\n",
       "      <td>410</td>\n",
       "      <td>409</td>\n",
       "      <td>2019-01-03T03:22:24+00:00</td>\n",
       "      <td>2019-01-03T03:37:24+00:00</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10372</td>\n",
       "      <td>finished</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>162673000</td>\n",
       "      <td>9040</td>\n",
       "      <td>2791</td>\n",
       "      <td>410</td>\n",
       "      <td>409</td>\n",
       "      <td>2021-01-14T03:22:24+00:00</td>\n",
       "      <td>2021-01-14T03:37:24+00:00</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10523</td>\n",
       "      <td>finished</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>410620009</td>\n",
       "      <td>10392</td>\n",
       "      <td>3445</td>\n",
       "      <td>1064</td>\n",
       "      <td>1063</td>\n",
       "      <td>1963-12-17T03:03:25+00:00</td>\n",
       "      <td>1963-12-17T03:18:25+00:00</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10571</td>\n",
       "      <td>finished</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>162673000</td>\n",
       "      <td>10392</td>\n",
       "      <td>3445</td>\n",
       "      <td>1064</td>\n",
       "      <td>1063</td>\n",
       "      <td>1980-01-22T03:03:25+00:00</td>\n",
       "      <td>1980-01-22T03:18:25+00:00</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11846</td>\n",
       "      <td>finished</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>162673000</td>\n",
       "      <td>10669</td>\n",
       "      <td>4409</td>\n",
       "      <td>2028</td>\n",
       "      <td>2027</td>\n",
       "      <td>2017-12-29T20:07:11+00:00</td>\n",
       "      <td>2017-12-29T20:22:11+00:00</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  enc_id    status                type_sys  type_code patient_id  \\\n",
       "0  10357  finished  http://snomed.info/sct  162673000       9040   \n",
       "1  10372  finished  http://snomed.info/sct  162673000       9040   \n",
       "2  10523  finished  http://snomed.info/sct  410620009      10392   \n",
       "3  10571  finished  http://snomed.info/sct  162673000      10392   \n",
       "4  11846  finished  http://snomed.info/sct  162673000      10669   \n",
       "\n",
       "  practitioner_id location_id service_org_id                      start  \\\n",
       "0            2791         410            409  2019-01-03T03:22:24+00:00   \n",
       "1            2791         410            409  2021-01-14T03:22:24+00:00   \n",
       "2            3445        1064           1063  1963-12-17T03:03:25+00:00   \n",
       "3            3445        1064           1063  1980-01-22T03:03:25+00:00   \n",
       "4            4409        2028           2027  2017-12-29T20:07:11+00:00   \n",
       "\n",
       "                         end EpisodeOfCareId  \n",
       "0  2019-01-03T03:37:24+00:00            None  \n",
       "1  2021-01-14T03:37:24+00:00            None  \n",
       "2  1963-12-17T03:18:25+00:00            None  \n",
       "3  1980-01-22T03:18:25+00:00            None  \n",
       "4  2017-12-29T20:22:11+00:00            None  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with query_engine.connect() as con:\n",
    "    con.execute(\"\"\"\n",
    "      CREATE OR REPLACE VIEW Encounter_flat AS\n",
    "      SELECT E.id AS enc_id, E.status, ETC.system AS type_sys,\n",
    "        ETC.code AS type_code, E.subject.PatientId AS patient_id,\n",
    "        EP.individual.practitionerId AS practitioner_id,\n",
    "        EL.location.locationId AS location_id,\n",
    "        E.serviceProvider.organizationId AS service_org_id,\n",
    "        E.period.start, E.period.end, E.episodeOfCare.EpisodeOfCareId\n",
    "      FROM Encounter AS E LATERAL VIEW OUTER explode(type) AS ET\n",
    "        LATERAL VIEW OUTER explode(ET.coding) AS ETC\n",
    "        LATERAL VIEW OUTER explode(E.participant) AS EP\n",
    "        LATERAL VIEW OUTER explode(E.location) AS EL\n",
    "      ;\n",
    "      \"\"\"\n",
    "    )\n",
    "\n",
    "pandas.read_sql_query(\n",
    "    sql=\"SELECT * FROM Encounter_flat LIMIT 5;\",\n",
    "    con=query_engine,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Condition"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cond_id</th>\n",
       "      <th>patient_id</th>\n",
       "      <th>encounter_id</th>\n",
       "      <th>system</th>\n",
       "      <th>code</th>\n",
       "      <th>clinical_status</th>\n",
       "      <th>verification_status</th>\n",
       "      <th>onset_datetime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>17639</td>\n",
       "      <td>16947</td>\n",
       "      <td>17637</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>112141AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>active</td>\n",
       "      <td>confirmed</td>\n",
       "      <td>2012-09-06T04:45:23+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20074</td>\n",
       "      <td>19393</td>\n",
       "      <td>20073</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>112141AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>active</td>\n",
       "      <td>confirmed</td>\n",
       "      <td>1992-03-15T02:48:07+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>23763</td>\n",
       "      <td>23396</td>\n",
       "      <td>23762</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>112141AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>active</td>\n",
       "      <td>confirmed</td>\n",
       "      <td>1996-11-06T16:21:43+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>28638</td>\n",
       "      <td>26670</td>\n",
       "      <td>28636</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>230690007</td>\n",
       "      <td>active</td>\n",
       "      <td>confirmed</td>\n",
       "      <td>2001-05-16T19:45:21+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5485</td>\n",
       "      <td>4766</td>\n",
       "      <td>5480</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>112141AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>active</td>\n",
       "      <td>confirmed</td>\n",
       "      <td>1995-02-03T06:21:13+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  cond_id patient_id encounter_id                  system  \\\n",
       "0   17639      16947        17637  http://snomed.info/sct   \n",
       "1   20074      19393        20073  http://snomed.info/sct   \n",
       "2   23763      23396        23762  http://snomed.info/sct   \n",
       "3   28638      26670        28636  http://snomed.info/sct   \n",
       "4    5485       4766         5480  http://snomed.info/sct   \n",
       "\n",
       "                                   code clinical_status verification_status  \\\n",
       "0  112141AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA          active           confirmed   \n",
       "1  112141AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA          active           confirmed   \n",
       "2  112141AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA          active           confirmed   \n",
       "3                             230690007          active           confirmed   \n",
       "4  112141AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA          active           confirmed   \n",
       "\n",
       "              onset_datetime  \n",
       "0  2012-09-06T04:45:23+00:00  \n",
       "1  1992-03-15T02:48:07+00:00  \n",
       "2  1996-11-06T16:21:43+00:00  \n",
       "3  2001-05-16T19:45:21+00:00  \n",
       "4  1995-02-03T06:21:13+00:00  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with query_engine.connect() as con:\n",
    "    con.execute(\"\"\"\n",
    "      CREATE OR REPLACE VIEW Condition_flat AS\n",
    "      SELECT C.id AS cond_id, C.subject.patientId AS patient_id,\n",
    "        C.encounter.encounterId AS encounter_id, CCC.system, CCC.code,\n",
    "        CClC.code AS clinical_status, CVC.code AS verification_status,\n",
    "        C.onset.DateTime AS onset_datetime\n",
    "      FROM Condition AS C LATERAL VIEW OUTER explode(C.code.coding) AS CCC\n",
    "        LATERAL VIEW OUTER explode(C.category) AS CCat\n",
    "        LATERAL VIEW OUTER explode(CCat.coding) AS CCatC\n",
    "        LATERAL VIEW OUTER explode(C.clinicalStatus.coding) AS CClC\n",
    "        LATERAL VIEW OUTER explode(C.verificationStatus.coding) AS CVC\n",
    "      ;\n",
    "      \"\"\"\n",
    "    )\n",
    "\n",
    "pandas.read_sql_query(\n",
    "    sql=\"SELECT * FROM Condition_flat LIMIT 5;\",\n",
    "    con=query_engine,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DiagnosticReport"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dr_id</th>\n",
       "      <th>patient_id</th>\n",
       "      <th>encounter_id</th>\n",
       "      <th>system</th>\n",
       "      <th>code</th>\n",
       "      <th>result_obs_id</th>\n",
       "      <th>status</th>\n",
       "      <th>practitioner_id</th>\n",
       "      <th>category_sys</th>\n",
       "      <th>category_code</th>\n",
       "      <th>conclusion_sys</th>\n",
       "      <th>conclusion_code</th>\n",
       "      <th>conclusion</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11216</td>\n",
       "      <td>11149</td>\n",
       "      <td>11207</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>34117-2</td>\n",
       "      <td>None</td>\n",
       "      <td>final</td>\n",
       "      <td>4285</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>34117-2</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11216</td>\n",
       "      <td>11149</td>\n",
       "      <td>11207</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>34117-2</td>\n",
       "      <td>None</td>\n",
       "      <td>final</td>\n",
       "      <td>4285</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>51847-2</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11216</td>\n",
       "      <td>11149</td>\n",
       "      <td>11207</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>51847-2</td>\n",
       "      <td>None</td>\n",
       "      <td>final</td>\n",
       "      <td>4285</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>34117-2</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11216</td>\n",
       "      <td>11149</td>\n",
       "      <td>11207</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>51847-2</td>\n",
       "      <td>None</td>\n",
       "      <td>final</td>\n",
       "      <td>4285</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>51847-2</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11633</td>\n",
       "      <td>10669</td>\n",
       "      <td>11625</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>34117-2</td>\n",
       "      <td>None</td>\n",
       "      <td>final</td>\n",
       "      <td>4409</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>34117-2</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   dr_id patient_id encounter_id            system     code result_obs_id  \\\n",
       "0  11216      11149        11207  http://loinc.org  34117-2          None   \n",
       "1  11216      11149        11207  http://loinc.org  34117-2          None   \n",
       "2  11216      11149        11207  http://loinc.org  51847-2          None   \n",
       "3  11216      11149        11207  http://loinc.org  51847-2          None   \n",
       "4  11633      10669        11625  http://loinc.org  34117-2          None   \n",
       "\n",
       "  status practitioner_id      category_sys category_code conclusion_sys  \\\n",
       "0  final            4285  http://loinc.org       34117-2           None   \n",
       "1  final            4285  http://loinc.org       51847-2           None   \n",
       "2  final            4285  http://loinc.org       34117-2           None   \n",
       "3  final            4285  http://loinc.org       51847-2           None   \n",
       "4  final            4409  http://loinc.org       34117-2           None   \n",
       "\n",
       "  conclusion_code conclusion  \n",
       "0            None       None  \n",
       "1            None       None  \n",
       "2            None       None  \n",
       "3            None       None  \n",
       "4            None       None  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with query_engine.connect() as con:\n",
    "    con.execute(\"\"\"\n",
    "      CREATE OR REPLACE VIEW DiagnosticReport_flat AS\n",
    "      SELECT D.id AS dr_id, D.subject.patientId AS patient_id,\n",
    "        D.encounter.EncounterId AS encounter_id,\n",
    "        DCC.system, DCC.code, DR.observationId AS result_obs_id,\n",
    "        D.status, DP.PractitionerId AS practitioner_id,\n",
    "        DCatC.system AS category_sys, DCatC.code AS category_code,\n",
    "        DConC.system AS conclusion_sys, DConC.code AS conclusion_code,\n",
    "        D.conclusion\n",
    "      FROM DiagnosticReport AS D LATERAL VIEW OUTER explode(D.result) AS DR\n",
    "        LATERAL VIEW OUTER explode(D.code.coding) AS DCC\n",
    "        LATERAL VIEW OUTER explode(D.performer) AS DP\n",
    "        LATERAL VIEW OUTER explode(D.category) AS DCat\n",
    "        LATERAL VIEW OUTER explode(DCat.coding) AS DCatC\n",
    "        LATERAL VIEW OUTER explode(D.conclusionCode) AS DCon\n",
    "        LATERAL VIEW OUTER explode(DCon.coding) AS DConC\n",
    "      ;\n",
    "      \"\"\"\n",
    "    )\n",
    "\n",
    "pandas.read_sql_query(\n",
    "    sql=\"SELECT * FROM DiagnosticReport_flat LIMIT 5;\",\n",
    "    con=query_engine,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Immunization"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>imm_id</th>\n",
       "      <th>patient_id</th>\n",
       "      <th>encounter_id</th>\n",
       "      <th>status</th>\n",
       "      <th>statusReason_sys</th>\n",
       "      <th>statusReason_code</th>\n",
       "      <th>vaccine_sys</th>\n",
       "      <th>vaccine_code</th>\n",
       "      <th>DateTime</th>\n",
       "      <th>location_id</th>\n",
       "      <th>PractitionerId</th>\n",
       "      <th>OrganizationId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11638</td>\n",
       "      <td>10669</td>\n",
       "      <td>11637</td>\n",
       "      <td>completed</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>http://hl7.org/fhir/sid/cvx</td>\n",
       "      <td>140</td>\n",
       "      <td>2005-10-21T20:07:11+00:00</td>\n",
       "      <td>2028</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12880</td>\n",
       "      <td>12348</td>\n",
       "      <td>12879</td>\n",
       "      <td>completed</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>http://hl7.org/fhir/sid/cvx</td>\n",
       "      <td>140</td>\n",
       "      <td>1994-05-17T20:51:22+00:00</td>\n",
       "      <td>506</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13136</td>\n",
       "      <td>12988</td>\n",
       "      <td>13135</td>\n",
       "      <td>completed</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>http://hl7.org/fhir/sid/cvx</td>\n",
       "      <td>140</td>\n",
       "      <td>1972-04-04T08:42:20+00:00</td>\n",
       "      <td>736</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>13800</td>\n",
       "      <td>13173</td>\n",
       "      <td>13799</td>\n",
       "      <td>completed</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>http://hl7.org/fhir/sid/cvx</td>\n",
       "      <td>140</td>\n",
       "      <td>1997-01-19T06:45:44+00:00</td>\n",
       "      <td>1344</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>14158</td>\n",
       "      <td>13173</td>\n",
       "      <td>14157</td>\n",
       "      <td>completed</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>http://hl7.org/fhir/sid/cvx</td>\n",
       "      <td>140</td>\n",
       "      <td>2012-04-15T06:45:44+00:00</td>\n",
       "      <td>1344</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  imm_id patient_id encounter_id     status statusReason_sys  \\\n",
       "0  11638      10669        11637  completed             None   \n",
       "1  12880      12348        12879  completed             None   \n",
       "2  13136      12988        13135  completed             None   \n",
       "3  13800      13173        13799  completed             None   \n",
       "4  14158      13173        14157  completed             None   \n",
       "\n",
       "  statusReason_code                  vaccine_sys vaccine_code  \\\n",
       "0              None  http://hl7.org/fhir/sid/cvx          140   \n",
       "1              None  http://hl7.org/fhir/sid/cvx          140   \n",
       "2              None  http://hl7.org/fhir/sid/cvx          140   \n",
       "3              None  http://hl7.org/fhir/sid/cvx          140   \n",
       "4              None  http://hl7.org/fhir/sid/cvx          140   \n",
       "\n",
       "                    DateTime location_id PractitionerId OrganizationId  \n",
       "0  2005-10-21T20:07:11+00:00        2028           None           None  \n",
       "1  1994-05-17T20:51:22+00:00         506           None           None  \n",
       "2  1972-04-04T08:42:20+00:00         736           None           None  \n",
       "3  1997-01-19T06:45:44+00:00        1344           None           None  \n",
       "4  2012-04-15T06:45:44+00:00        1344           None           None  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with query_engine.connect() as con:\n",
    "    con.execute(\"\"\"\n",
    "      CREATE OR REPLACE VIEW Immunization_flat AS\n",
    "      SELECT I.id AS imm_id, I.patient.patientId AS patient_id,\n",
    "        I.encounter.encounterId AS encounter_id, I.status,\n",
    "        ISC.system AS statusReason_sys, ISC.code AS statusReason_code,\n",
    "        IVC.system AS vaccine_sys, IVC.code AS vaccine_code,\n",
    "        I.occurrence.DateTime, I.location.LocationId AS location_id,\n",
    "        IP.actor.PractitionerId, IP.actor.OrganizationId\n",
    "      FROM Immunization AS I\n",
    "        LATERAL VIEW OUTER explode(I.statusReason.coding) AS ISC\n",
    "        LATERAL VIEW OUTER explode(I.vaccineCode.coding) AS IVC\n",
    "        LATERAL VIEW OUTER explode(I.performer) AS IP\n",
    "      ;\n",
    "      \"\"\"\n",
    "    )\n",
    "\n",
    "pandas.read_sql_query(\n",
    "    sql=\"SELECT * FROM Immunization_flat LIMIT 5;\",\n",
    "    con=query_engine,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Location"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>loc_id</th>\n",
       "      <th>status</th>\n",
       "      <th>name</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>org_id</th>\n",
       "      <th>longitude</th>\n",
       "      <th>latitude</th>\n",
       "      <th>altitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1048</td>\n",
       "      <td>active</td>\n",
       "      <td>PCP83180</td>\n",
       "      <td>EVERETT</td>\n",
       "      <td>US</td>\n",
       "      <td>None</td>\n",
       "      <td>-71.054649</td>\n",
       "      <td>42.405938</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1064</td>\n",
       "      <td>active</td>\n",
       "      <td>PHYSICAL THERAPY AND FITNESS CENTER OF RAYNHAM...</td>\n",
       "      <td>RAYNHAM</td>\n",
       "      <td>US</td>\n",
       "      <td>None</td>\n",
       "      <td>-71.046214</td>\n",
       "      <td>41.930477</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>112</td>\n",
       "      <td>active</td>\n",
       "      <td>PCP77</td>\n",
       "      <td>PITTSFIELD</td>\n",
       "      <td>US</td>\n",
       "      <td>None</td>\n",
       "      <td>-73.260685</td>\n",
       "      <td>42.451840</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1236</td>\n",
       "      <td>active</td>\n",
       "      <td>PCP129774</td>\n",
       "      <td>REVERE</td>\n",
       "      <td>US</td>\n",
       "      <td>None</td>\n",
       "      <td>-70.990360</td>\n",
       "      <td>42.421005</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1292</td>\n",
       "      <td>active</td>\n",
       "      <td>PCP144578</td>\n",
       "      <td>BILLERICA</td>\n",
       "      <td>US</td>\n",
       "      <td>None</td>\n",
       "      <td>-71.260947</td>\n",
       "      <td>42.559673</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  loc_id  status                                               name  \\\n",
       "0   1048  active                                           PCP83180   \n",
       "1   1064  active  PHYSICAL THERAPY AND FITNESS CENTER OF RAYNHAM...   \n",
       "2    112  active                                              PCP77   \n",
       "3   1236  active                                          PCP129774   \n",
       "4   1292  active                                          PCP144578   \n",
       "\n",
       "         city country org_id  longitude   latitude altitude  \n",
       "0     EVERETT      US   None -71.054649  42.405938     None  \n",
       "1     RAYNHAM      US   None -71.046214  41.930477     None  \n",
       "2  PITTSFIELD      US   None -73.260685  42.451840     None  \n",
       "3      REVERE      US   None -70.990360  42.421005     None  \n",
       "4   BILLERICA      US   None -71.260947  42.559673     None  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with query_engine.connect() as con:\n",
    "    con.execute(\"\"\"\n",
    "      CREATE OR REPLACE VIEW Location_flat AS\n",
    "      SELECT L.id AS loc_id, L.status, L.name, L.address.city,\n",
    "        L.address.country, L.managingOrganization.organizationId AS org_id,\n",
    "        L.position.longitude, L.position.latitude, L.position.altitude\n",
    "      FROM Location AS L\n",
    "      ;\n",
    "      \"\"\"\n",
    "    )\n",
    "\n",
    "pandas.read_sql_query(\n",
    "    sql=\"SELECT * FROM Location_flat LIMIT 5;\",\n",
    "    con=query_engine,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## MedicationRequest"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>med_req_id</th>\n",
       "      <th>patient_id</th>\n",
       "      <th>status</th>\n",
       "      <th>statusReason_sys</th>\n",
       "      <th>statusReason_code</th>\n",
       "      <th>system</th>\n",
       "      <th>code</th>\n",
       "      <th>intent</th>\n",
       "      <th>doNotPerform</th>\n",
       "      <th>practitioner_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10592</td>\n",
       "      <td>10392</td>\n",
       "      <td>stopped</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>http://www.nlm.nih.gov/research/umls/rxnorm</td>\n",
       "      <td>834357</td>\n",
       "      <td>order</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10594</td>\n",
       "      <td>10392</td>\n",
       "      <td>stopped</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>http://www.nlm.nih.gov/research/umls/rxnorm</td>\n",
       "      <td>1190795</td>\n",
       "      <td>order</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>29022</td>\n",
       "      <td>26670</td>\n",
       "      <td>stopped</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>http://www.nlm.nih.gov/research/umls/rxnorm</td>\n",
       "      <td>1804799</td>\n",
       "      <td>order</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>29277</td>\n",
       "      <td>29122</td>\n",
       "      <td>stopped</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>http://www.nlm.nih.gov/research/umls/rxnorm</td>\n",
       "      <td>834357</td>\n",
       "      <td>order</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>29279</td>\n",
       "      <td>29122</td>\n",
       "      <td>stopped</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>http://www.nlm.nih.gov/research/umls/rxnorm</td>\n",
       "      <td>1190795</td>\n",
       "      <td>order</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  med_req_id patient_id   status statusReason_sys statusReason_code  \\\n",
       "0      10592      10392  stopped             None              None   \n",
       "1      10594      10392  stopped             None              None   \n",
       "2      29022      26670  stopped             None              None   \n",
       "3      29277      29122  stopped             None              None   \n",
       "4      29279      29122  stopped             None              None   \n",
       "\n",
       "                                        system     code intent doNotPerform  \\\n",
       "0  http://www.nlm.nih.gov/research/umls/rxnorm   834357  order         None   \n",
       "1  http://www.nlm.nih.gov/research/umls/rxnorm  1190795  order         None   \n",
       "2  http://www.nlm.nih.gov/research/umls/rxnorm  1804799  order         None   \n",
       "3  http://www.nlm.nih.gov/research/umls/rxnorm   834357  order         None   \n",
       "4  http://www.nlm.nih.gov/research/umls/rxnorm  1190795  order         None   \n",
       "\n",
       "  practitioner_id  \n",
       "0            None  \n",
       "1            None  \n",
       "2            None  \n",
       "3            None  \n",
       "4            None  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with query_engine.connect() as con:\n",
    "    con.execute(\"\"\"\n",
    "      CREATE OR REPLACE VIEW MedicationRequest_flat AS\n",
    "      SELECT M.id AS med_req_id, M.subject.patientId AS patient_id, M.status,\n",
    "        MSC.system AS statusReason_sys, MSC.code AS statusReason_code,\n",
    "        MMCC.system, MMCC.code, M.intent, M.doNotPerform,\n",
    "        M.performer.practitionerId AS practitioner_id\n",
    "      FROM MedicationRequest AS M\n",
    "        LATERAL VIEW OUTER explode(M.statusReason.coding) AS MSC\n",
    "        LATERAL VIEW OUTER explode(M.medication.codeableConcept.coding) AS MMCC\n",
    "      ;\n",
    "      \"\"\"\n",
    "    )\n",
    "\n",
    "pandas.read_sql_query(\n",
    "    sql=\"SELECT * FROM MedicationRequest_flat LIMIT 5;\",\n",
    "    con=query_engine,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Organization"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>org_id</th>\n",
       "      <th>active</th>\n",
       "      <th>name</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>type_sys</th>\n",
       "      <th>type_code</th>\n",
       "      <th>partOf_org_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1003</td>\n",
       "      <td>True</td>\n",
       "      <td>ASSOCIATES OF SOUTH SHORE DERMATOLOGY LLC</td>\n",
       "      <td>MILTON</td>\n",
       "      <td>US</td>\n",
       "      <td>http://terminology.hl7.org/CodeSystem/organiza...</td>\n",
       "      <td>prov</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1055</td>\n",
       "      <td>True</td>\n",
       "      <td>HELLER EYECARE INC</td>\n",
       "      <td>WILMINGTON</td>\n",
       "      <td>US</td>\n",
       "      <td>http://terminology.hl7.org/CodeSystem/organiza...</td>\n",
       "      <td>prov</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1079</td>\n",
       "      <td>True</td>\n",
       "      <td>PCP90763</td>\n",
       "      <td>RAYNHAM</td>\n",
       "      <td>US</td>\n",
       "      <td>http://terminology.hl7.org/CodeSystem/organiza...</td>\n",
       "      <td>prov</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1211</td>\n",
       "      <td>True</td>\n",
       "      <td>PCP124160</td>\n",
       "      <td>HYDE PARK</td>\n",
       "      <td>US</td>\n",
       "      <td>http://terminology.hl7.org/CodeSystem/organiza...</td>\n",
       "      <td>prov</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1295</td>\n",
       "      <td>True</td>\n",
       "      <td>EYE ASSOCIATES OF SOMERVILLE INC.</td>\n",
       "      <td>SOMERVILLE</td>\n",
       "      <td>US</td>\n",
       "      <td>http://terminology.hl7.org/CodeSystem/organiza...</td>\n",
       "      <td>prov</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  org_id  active                                       name        city  \\\n",
       "0   1003    True  ASSOCIATES OF SOUTH SHORE DERMATOLOGY LLC      MILTON   \n",
       "1   1055    True                         HELLER EYECARE INC  WILMINGTON   \n",
       "2   1079    True                                   PCP90763     RAYNHAM   \n",
       "3   1211    True                                  PCP124160   HYDE PARK   \n",
       "4   1295    True          EYE ASSOCIATES OF SOMERVILLE INC.  SOMERVILLE   \n",
       "\n",
       "  country                                           type_sys type_code  \\\n",
       "0      US  http://terminology.hl7.org/CodeSystem/organiza...      prov   \n",
       "1      US  http://terminology.hl7.org/CodeSystem/organiza...      prov   \n",
       "2      US  http://terminology.hl7.org/CodeSystem/organiza...      prov   \n",
       "3      US  http://terminology.hl7.org/CodeSystem/organiza...      prov   \n",
       "4      US  http://terminology.hl7.org/CodeSystem/organiza...      prov   \n",
       "\n",
       "  partOf_org_id  \n",
       "0          None  \n",
       "1          None  \n",
       "2          None  \n",
       "3          None  \n",
       "4          None  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with query_engine.connect() as con:\n",
    "    con.execute(\"\"\"\n",
    "      CREATE OR REPLACE VIEW Organization_flat AS\n",
    "      SELECT O.id AS org_id, O.active, O.name, OA.city, OA.country,\n",
    "        OTC.system AS type_sys, OTC.code AS type_code,\n",
    "        O.partOf.OrganizationId AS partOf_org_id\n",
    "      FROM Organization AS O LATERAL VIEW OUTER explode(O.address) AS OA\n",
    "         LATERAL VIEW OUTER explode(O.type) AS OT\n",
    "         LATERAL VIEW OUTER explode(OT.coding) AS OTC\n",
    "      ;\n",
    "      \"\"\"\n",
    "    )\n",
    "\n",
    "pandas.read_sql_query(\n",
    "    sql=\"SELECT * FROM Organization_flat LIMIT 5;\",\n",
    "    con=query_engine,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Practitioner"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>prac_id</th>\n",
       "      <th>active</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>gender</th>\n",
       "      <th>qualification_system</th>\n",
       "      <th>qualification_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2455</td>\n",
       "      <td>True</td>\n",
       "      <td>GARDNER</td>\n",
       "      <td>US</td>\n",
       "      <td>female</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2557</td>\n",
       "      <td>True</td>\n",
       "      <td>MIDDLETON</td>\n",
       "      <td>US</td>\n",
       "      <td>female</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2563</td>\n",
       "      <td>True</td>\n",
       "      <td>BROOKLINE</td>\n",
       "      <td>US</td>\n",
       "      <td>female</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2617</td>\n",
       "      <td>True</td>\n",
       "      <td>GRAFTON</td>\n",
       "      <td>US</td>\n",
       "      <td>male</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2635</td>\n",
       "      <td>True</td>\n",
       "      <td>MARSHFIELD</td>\n",
       "      <td>US</td>\n",
       "      <td>male</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  prac_id  active        city country  gender qualification_system  \\\n",
       "0    2455    True     GARDNER      US  female                 None   \n",
       "1    2557    True   MIDDLETON      US  female                 None   \n",
       "2    2563    True   BROOKLINE      US  female                 None   \n",
       "3    2617    True     GRAFTON      US    male                 None   \n",
       "4    2635    True  MARSHFIELD      US    male                 None   \n",
       "\n",
       "  qualification_code  \n",
       "0               None  \n",
       "1               None  \n",
       "2               None  \n",
       "3               None  \n",
       "4               None  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with query_engine.connect() as con:\n",
    "    con.execute(\"\"\"\n",
    "      CREATE OR REPLACE VIEW Practitioner_flat AS\n",
    "      SELECT P.id AS prac_id, P.active, PA.city, PA.country, P.gender,\n",
    "        PQCC.system AS qualification_system, PQCC.code AS qualification_code\n",
    "      FROM Practitioner AS P LATERAL VIEW OUTER explode(P.address) AS PA\n",
    "        LATERAL VIEW OUTER explode(P.qualification) AS PQ\n",
    "        LATERAL VIEW OUTER explode(PQ.code.coding) AS PQCC\n",
    "      ;\n",
    "      \"\"\"\n",
    "    )\n",
    "\n",
    "pandas.read_sql_query(\n",
    "    sql=\"SELECT * FROM Practitioner_flat LIMIT 5;\",\n",
    "    con=query_engine,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## PractitionerRole"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>pr_id</th>\n",
       "      <th>practitioner_id</th>\n",
       "      <th>active</th>\n",
       "      <th>organization_id</th>\n",
       "      <th>system</th>\n",
       "      <th>code</th>\n",
       "      <th>specialty_sys</th>\n",
       "      <th>specialty_code</th>\n",
       "      <th>LocationId</th>\n",
       "      <th>HealthcareServiceId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2588</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>http://nucc.org/provider-taxonomy</td>\n",
       "      <td>208D00000X</td>\n",
       "      <td>http://nucc.org/provider-taxonomy</td>\n",
       "      <td>208D00000X</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2624</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>http://nucc.org/provider-taxonomy</td>\n",
       "      <td>208D00000X</td>\n",
       "      <td>http://nucc.org/provider-taxonomy</td>\n",
       "      <td>208D00000X</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2682</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>http://nucc.org/provider-taxonomy</td>\n",
       "      <td>208D00000X</td>\n",
       "      <td>http://nucc.org/provider-taxonomy</td>\n",
       "      <td>208D00000X</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2688</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>http://nucc.org/provider-taxonomy</td>\n",
       "      <td>208D00000X</td>\n",
       "      <td>http://nucc.org/provider-taxonomy</td>\n",
       "      <td>208D00000X</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2702</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>http://nucc.org/provider-taxonomy</td>\n",
       "      <td>208D00000X</td>\n",
       "      <td>http://nucc.org/provider-taxonomy</td>\n",
       "      <td>208D00000X</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  pr_id practitioner_id active organization_id  \\\n",
       "0  2588            None   None            None   \n",
       "1  2624            None   None            None   \n",
       "2  2682            None   None            None   \n",
       "3  2688            None   None            None   \n",
       "4  2702            None   None            None   \n",
       "\n",
       "                              system        code  \\\n",
       "0  http://nucc.org/provider-taxonomy  208D00000X   \n",
       "1  http://nucc.org/provider-taxonomy  208D00000X   \n",
       "2  http://nucc.org/provider-taxonomy  208D00000X   \n",
       "3  http://nucc.org/provider-taxonomy  208D00000X   \n",
       "4  http://nucc.org/provider-taxonomy  208D00000X   \n",
       "\n",
       "                       specialty_sys specialty_code LocationId  \\\n",
       "0  http://nucc.org/provider-taxonomy     208D00000X       None   \n",
       "1  http://nucc.org/provider-taxonomy     208D00000X       None   \n",
       "2  http://nucc.org/provider-taxonomy     208D00000X       None   \n",
       "3  http://nucc.org/provider-taxonomy     208D00000X       None   \n",
       "4  http://nucc.org/provider-taxonomy     208D00000X       None   \n",
       "\n",
       "  HealthcareServiceId  \n",
       "0                None  \n",
       "1                None  \n",
       "2                None  \n",
       "3                None  \n",
       "4                None  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with query_engine.connect() as con:\n",
    "    con.execute(\"\"\"\n",
    "      CREATE OR REPLACE VIEW PractitionerRole_flat AS\n",
    "      SELECT P.id AS pr_id, P.practitioner.practitionerId as practitioner_id,\n",
    "        P.active, P.organization.organizationId AS organization_id,\n",
    "        PCC.system, PCC.code,\n",
    "        PSC.system AS specialty_sys, PSC.code AS specialty_code,\n",
    "        PL.LocationId, PH.HealthcareServiceId\n",
    "      FROM PractitionerRole AS P\n",
    "        LATERAL VIEW OUTER explode(P.code) AS PC\n",
    "        LATERAL VIEW OUTER explode(PC.coding) AS PCC\n",
    "        LATERAL VIEW OUTER explode(P.specialty) AS PS\n",
    "        LATERAL VIEW OUTER explode(PS.coding) AS PSC\n",
    "        LATERAL VIEW OUTER explode(P.location) AS PL\n",
    "        LATERAL VIEW OUTER explode(P.healthcareService) AS PH\n",
    "      ;\n",
    "      \"\"\"\n",
    "    )\n",
    "\n",
    "pandas.read_sql_query(\n",
    "    sql=\"SELECT * FROM PractitionerRole_flat LIMIT 5;\",\n",
    "    con=query_engine,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Procedure"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>proc_id</th>\n",
       "      <th>patient_id</th>\n",
       "      <th>encounter_id</th>\n",
       "      <th>system</th>\n",
       "      <th>code</th>\n",
       "      <th>practitioner_id</th>\n",
       "      <th>period_start</th>\n",
       "      <th>location_id</th>\n",
       "      <th>status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>29019</td>\n",
       "      <td>26670</td>\n",
       "      <td>29017</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>433112001</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-02-20T19:45:21+00:00</td>\n",
       "      <td>56</td>\n",
       "      <td>completed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>29272</td>\n",
       "      <td>29122</td>\n",
       "      <td>29268</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>447365002</td>\n",
       "      <td>None</td>\n",
       "      <td>1972-10-14T09:12:43+00:00</td>\n",
       "      <td>24</td>\n",
       "      <td>completed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>28867</td>\n",
       "      <td>26670</td>\n",
       "      <td>28866</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>180325003</td>\n",
       "      <td>None</td>\n",
       "      <td>2008-05-14T19:45:21+00:00</td>\n",
       "      <td>1640</td>\n",
       "      <td>completed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>29085</td>\n",
       "      <td>26670</td>\n",
       "      <td>29084</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>180325003</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-07-28T19:45:21+00:00</td>\n",
       "      <td>1640</td>\n",
       "      <td>completed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10344</td>\n",
       "      <td>9040</td>\n",
       "      <td>10343</td>\n",
       "      <td>http://snomed.info/sct</td>\n",
       "      <td>18286008</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-12-22T03:22:24+00:00</td>\n",
       "      <td>410</td>\n",
       "      <td>completed</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  proc_id patient_id encounter_id                  system       code  \\\n",
       "0   29019      26670        29017  http://snomed.info/sct  433112001   \n",
       "1   29272      29122        29268  http://snomed.info/sct  447365002   \n",
       "2   28867      26670        28866  http://snomed.info/sct  180325003   \n",
       "3   29085      26670        29084  http://snomed.info/sct  180325003   \n",
       "4   10344       9040        10343  http://snomed.info/sct   18286008   \n",
       "\n",
       "  practitioner_id               period_start location_id     status  \n",
       "0            None  2019-02-20T19:45:21+00:00          56  completed  \n",
       "1            None  1972-10-14T09:12:43+00:00          24  completed  \n",
       "2            None  2008-05-14T19:45:21+00:00        1640  completed  \n",
       "3            None  2021-07-28T19:45:21+00:00        1640  completed  \n",
       "4            None  2016-12-22T03:22:24+00:00         410  completed  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with query_engine.connect() as con:\n",
    "    con.execute(\"\"\"\n",
    "      CREATE OR REPLACE VIEW Procedure_flat AS\n",
    "      SELECT P.id AS proc_id, P.subject.patientId AS patient_id,\n",
    "        P.encounter.encounterId AS encounter_id, PCC.system, PCC.code,\n",
    "        PP.actor.practitionerId AS practitioner_id,\n",
    "        P.performed.period.start AS period_start,\n",
    "        P.performed.period.`end` AS period_end,\n",
    "        P.location.locationId AS location_id, P.status\n",
    "      FROM Procedure AS P LATERAL VIEW OUTER explode(P.code.coding) AS PCC\n",
    "        LATERAL VIEW OUTER explode(P.performer) AS PP\n",
    "      ;\n",
    "      \"\"\"\n",
    "    )\n",
    "\n",
    "pandas.read_sql_query(\n",
    "    # TODO: check why including period_end fails, while it can be selected separately!\n",
    "    sql=\"\"\"\n",
    "      SELECT proc_id, patient_id, encounter_id, system, code, practitioner_id,\n",
    "        period_start, location_id, status FROM Procedure_flat LIMIT 5;\n",
    "      \"\"\",\n",
    "    con=query_engine,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# FHIR Views\n",
    "\n",
    "**Note**: The Spark-runner for FHIR-views is in experimental mode and is not\n",
    "ready for production use.\n",
    "\n",
    "[FHIR Views](https://github.com/google/fhir-py/tree/main/google-fhir-views)\n",
    "is an abstraction layer to simplify the above view creation pattern. It helps\n",
    "creating flat views for FHIR resources using\n",
    "[FHIRPath](http://hl7.org/fhirpath/N1/) statements. It separates\n",
    "view definition from view creation, the latter being dependent on the\n",
    "underlying data representation. For example, there is a BigQuery-runner,\n",
    "Spark-runner, etc. each of which transforms the view definition into the\n",
    "corresponding SQL dialect.\n",
    "\n",
    "The FHIR-Views pattern is being standardized in\n",
    "[SQL-on-FHIR v2](https://build.fhir.org/ig/FHIR/sql-on-fhir-v2/)\n",
    "as a language independent FHIR spec (i.e., JSON instead of Python)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# In addition to the sqlalchemy imports we did above, we also need the\n",
    "# FHIR-views library for the rest of this notebook.\n",
    "\n",
    "from google.fhir.views import r4\n",
    "from google.fhir.views import spark_runner\n",
    "\n",
    "# The Spark dataset containing FHIR data. This may be read-only to the user.\n",
    "fhir_dataset = \"default\"\n",
    "\n",
    "# The Spark dataset where we will create views, value sets, and other derived tables\n",
    "# as needed. This must be writeable by the user.\n",
    "analysis_dataset = \"demo_example\"\n",
    "\n",
    "# Create a runner to execute the views over Spark.\n",
    "runner = spark_runner.SparkRunner(\n",
    "    query_engine=query_engine,\n",
    "    fhir_dataset=fhir_dataset,\n",
    "    view_dataset=analysis_dataset,\n",
    "    snake_case_resource_tables=True,\n",
    ")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Explore the data with FHIR Views\n",
    "Similar to the SQL examples above, first we try to get a general feeling of\n",
    "the data. So let's create some views of FHIR resources and take a look at\n",
    "common fields."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load views based on the base FHIR R4 profile definitions.\n",
    "views = r4.base_r4()\n",
    "\n",
    "# Create shorthand names for resources we will work with.\n",
    "obs = views.view_of(\"Observation\")\n",
    "pats = views.view_of(\"Patient\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's take a look at some observation resources by creating FHIRPath expressions to select the items we're interested in.\n",
    "\n",
    "Notice that tab completion works for FHIR fields and FHIRPath functions, so users don't need to switch back and forth to FHIR documentation as much."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>category</th>\n",
       "      <th>code_display</th>\n",
       "      <th>status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10929</td>\n",
       "      <td>[\"survey\"]</td>\n",
       "      <td>[\"CURRENT ANTIRETROVIRAL DRUGS USED FOR TREATM...</td>\n",
       "      <td>final</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10063</td>\n",
       "      <td>[\"survey\"]</td>\n",
       "      <td>[\"TEMPERATURE\"]</td>\n",
       "      <td>final</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10798</td>\n",
       "      <td>[\"survey\"]</td>\n",
       "      <td>[\"TEMPERATURE\"]</td>\n",
       "      <td>final</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10030</td>\n",
       "      <td>[\"survey\"]</td>\n",
       "      <td>[\"REVIEW OF TUBERCULOSIS SCREENING QUESTIONS\"]</td>\n",
       "      <td>final</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10803</td>\n",
       "      <td>[\"survey\"]</td>\n",
       "      <td>[\"REVIEW OF TUBERCULOSIS SCREENING QUESTIONS\"]</td>\n",
       "      <td>final</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>10880</td>\n",
       "      <td>[\"survey\"]</td>\n",
       "      <td>[\"PATIENT REPORTED CURRENT TB TREATMENT\"]</td>\n",
       "      <td>final</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>10075</td>\n",
       "      <td>[\"survey\"]</td>\n",
       "      <td>[\"TESTS ORDERED\"]</td>\n",
       "      <td>final</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>10083</td>\n",
       "      <td>[\"survey\"]</td>\n",
       "      <td>[\"TESTS ORDERED\"]</td>\n",
       "      <td>final</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>10825</td>\n",
       "      <td>[\"survey\"]</td>\n",
       "      <td>[\"TESTS ORDERED\"]</td>\n",
       "      <td>final</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10197</td>\n",
       "      <td>[\"survey\"]</td>\n",
       "      <td>[\"PATIENT REPORTED CURRENT ANTIRETROVIRAL TREA...</td>\n",
       "      <td>final</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id    category                                       code_display status\n",
       "0  10929  [\"survey\"]  [\"CURRENT ANTIRETROVIRAL DRUGS USED FOR TREATM...  final\n",
       "1  10063  [\"survey\"]                                    [\"TEMPERATURE\"]  final\n",
       "2  10798  [\"survey\"]                                    [\"TEMPERATURE\"]  final\n",
       "3  10030  [\"survey\"]     [\"REVIEW OF TUBERCULOSIS SCREENING QUESTIONS\"]  final\n",
       "4  10803  [\"survey\"]     [\"REVIEW OF TUBERCULOSIS SCREENING QUESTIONS\"]  final\n",
       "5  10880  [\"survey\"]          [\"PATIENT REPORTED CURRENT TB TREATMENT\"]  final\n",
       "6  10075  [\"survey\"]                                  [\"TESTS ORDERED\"]  final\n",
       "7  10083  [\"survey\"]                                  [\"TESTS ORDERED\"]  final\n",
       "8  10825  [\"survey\"]                                  [\"TESTS ORDERED\"]  final\n",
       "9  10197  [\"survey\"]  [\"PATIENT REPORTED CURRENT ANTIRETROVIRAL TREA...  final"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "runner.to_dataframe(\n",
    "    obs.select(\n",
    "        {\n",
    "            \"id\": obs.id,\n",
    "            \"category\": obs.category.coding.code,\n",
    "            \"code_display\": obs.code.coding.display,\n",
    "            \"status\": obs.status,\n",
    "        }\n",
    "    ),\n",
    "    limit=10,\n",
    ")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Of course just a sample of codes isn't too useful -- we really want a summary of what codes exist in a field and how many there are. Fortunately, the Spark runner supports a `summarize_codes` method that accepts a view and a field name and does exactly that. Note that we needed a non-trivial SQL query to do something similar."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>system</th>\n",
       "      <th>code</th>\n",
       "      <th>display</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1271AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>TESTS ORDERED</td>\n",
       "      <td>4949</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1088AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>CURRENT ANTIRETROVIRAL DRUGS USED FOR TREATMENT</td>\n",
       "      <td>2387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1111AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>PATIENT REPORTED CURRENT TB TREATMENT</td>\n",
       "      <td>2043</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1250AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>ANTIRETROVIRALS STARTED</td>\n",
       "      <td>1805</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1270AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>TUBERCULOSIS TREATMENT STARTED</td>\n",
       "      <td>1121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>159800AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>REVIEW OF TUBERCULOSIS SCREENING QUESTIONS</td>\n",
       "      <td>738</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>5085AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>LOINC Code</td>\n",
       "      <td>530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>159911AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>PATIENT REPORTED CURRENT ANTIRETROVIRAL TREATMENT</td>\n",
       "      <td>408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1268AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>TUBERCULOSIS TREATMENT PLAN</td>\n",
       "      <td>340</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1265AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>TUBERCULOSIS PROPHYLAXIS PLAN</td>\n",
       "      <td>340</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1255AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>ANTIRETROVIRAL PLAN</td>\n",
       "      <td>340</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1261AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>PCP PROPHYLAXIS PLAN</td>\n",
       "      <td>340</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>5092AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>BLOOD OXYGEN SATURATION</td>\n",
       "      <td>265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1040AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>HIV RAPID TEST, QUALITATIVE</td>\n",
       "      <td>265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>5087AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>PULSE</td>\n",
       "      <td>265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>5088AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>TEMPERATURE</td>\n",
       "      <td>265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1030AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>HIV DNA POLYMERASE CHAIN REACTION, QUALITATIVE</td>\n",
       "      <td>265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>856AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>HIV viral load</td>\n",
       "      <td>265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>5096AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>Return visit date</td>\n",
       "      <td>190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>5089AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>Weight</td>\n",
       "      <td>79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>5090AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>Height</td>\n",
       "      <td>79</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              system                                  code  \\\n",
       "0   http://loinc.org  1271AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "1   http://loinc.org  1088AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "2   http://loinc.org  1111AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "3   http://loinc.org  1250AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "4   http://loinc.org  1270AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "5   http://loinc.org  159800AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "6   http://loinc.org  5085AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "7   http://loinc.org  159911AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "8   http://loinc.org  1268AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "9   http://loinc.org  1265AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "10  http://loinc.org  1255AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "11  http://loinc.org  1261AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "12  http://loinc.org  5092AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "13  http://loinc.org  1040AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "14  http://loinc.org  5087AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "15  http://loinc.org  5088AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "16  http://loinc.org  1030AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "17  http://loinc.org  856AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "18  http://loinc.org  5096AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "19  http://loinc.org  5089AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "20  http://loinc.org  5090AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "\n",
       "                                              display  count  \n",
       "0                                       TESTS ORDERED   4949  \n",
       "1     CURRENT ANTIRETROVIRAL DRUGS USED FOR TREATMENT   2387  \n",
       "2               PATIENT REPORTED CURRENT TB TREATMENT   2043  \n",
       "3                             ANTIRETROVIRALS STARTED   1805  \n",
       "4                      TUBERCULOSIS TREATMENT STARTED   1121  \n",
       "5          REVIEW OF TUBERCULOSIS SCREENING QUESTIONS    738  \n",
       "6                                          LOINC Code    530  \n",
       "7   PATIENT REPORTED CURRENT ANTIRETROVIRAL TREATMENT    408  \n",
       "8                         TUBERCULOSIS TREATMENT PLAN    340  \n",
       "9                       TUBERCULOSIS PROPHYLAXIS PLAN    340  \n",
       "10                                ANTIRETROVIRAL PLAN    340  \n",
       "11                               PCP PROPHYLAXIS PLAN    340  \n",
       "12                            BLOOD OXYGEN SATURATION    265  \n",
       "13                        HIV RAPID TEST, QUALITATIVE    265  \n",
       "14                                              PULSE    265  \n",
       "15                                        TEMPERATURE    265  \n",
       "16     HIV DNA POLYMERASE CHAIN REACTION, QUALITATIVE    265  \n",
       "17                                     HIV viral load    265  \n",
       "18                                  Return visit date    190  \n",
       "19                                             Weight     79  \n",
       "20                                             Height     79  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "runner.summarize_codes(obs, obs.code)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating Views\n",
    "\n",
    "As discussed above, to handle the complexity of FHIR data, we can usually create\n",
    "a flat, tabular form of a resource type that can satisfy a given use case.\n",
    "\n",
    "For example, imagine we need a simple table of patients with their current\n",
    "address. This isn't trivial to query since the address is a nested repeated\n",
    "field. Fortunately we can build a FHIRPath expression to find the current\n",
    "address and create a flattened view using that.\n",
    "\n",
    "(This can vary by dataset, but in this case we determine the current address\n",
    "by finding the first address that does not have a period attached to it.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>gender</th>\n",
       "      <th>birthdate</th>\n",
       "      <th>street</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>zip</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>38462</td>\n",
       "      <td>female</td>\n",
       "      <td>1961-10-01</td>\n",
       "      <td>[\"376 Mohr Annex Suite 88\"]</td>\n",
       "      <td>[\"Northampton\"]</td>\n",
       "      <td>[\"MA\"]</td>\n",
       "      <td>[\"01060\"]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9040</td>\n",
       "      <td>female</td>\n",
       "      <td>1957-10-31</td>\n",
       "      <td>[\"751 Kessler Divide Unit 49\"]</td>\n",
       "      <td>[\"Brookline\"]</td>\n",
       "      <td>[\"MA\"]</td>\n",
       "      <td>[\"02445\"]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>39570</td>\n",
       "      <td>male</td>\n",
       "      <td>1915-04-20</td>\n",
       "      <td>[\"988 Ryan Burg Apt 59\"]</td>\n",
       "      <td>[\"North Reading\"]</td>\n",
       "      <td>[\"MA\"]</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>27113</td>\n",
       "      <td>male</td>\n",
       "      <td>1962-04-23</td>\n",
       "      <td>[\"609 Steuber Crossroad Unit 49\"]</td>\n",
       "      <td>[\"Westfield\"]</td>\n",
       "      <td>[\"MA\"]</td>\n",
       "      <td>[\"01085\"]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>30404</td>\n",
       "      <td>male</td>\n",
       "      <td>1941-10-24</td>\n",
       "      <td>[\"617 D'Amore Course\"]</td>\n",
       "      <td>[\"Brewster\"]</td>\n",
       "      <td>[\"MA\"]</td>\n",
       "      <td>[\"02631\"]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id  gender  birthdate                             street  \\\n",
       "0  38462  female 1961-10-01        [\"376 Mohr Annex Suite 88\"]   \n",
       "1   9040  female 1957-10-31     [\"751 Kessler Divide Unit 49\"]   \n",
       "2  39570    male 1915-04-20           [\"988 Ryan Burg Apt 59\"]   \n",
       "3  27113    male 1962-04-23  [\"609 Steuber Crossroad Unit 49\"]   \n",
       "4  30404    male 1941-10-24             [\"617 D'Amore Course\"]   \n",
       "\n",
       "                city   state        zip  \n",
       "0    [\"Northampton\"]  [\"MA\"]  [\"01060\"]  \n",
       "1      [\"Brookline\"]  [\"MA\"]  [\"02445\"]  \n",
       "2  [\"North Reading\"]  [\"MA\"]         []  \n",
       "3      [\"Westfield\"]  [\"MA\"]  [\"01085\"]  \n",
       "4       [\"Brewster\"]  [\"MA\"]  [\"02631\"]  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# For this dataset we interpret the current address as one where period is empty.\n",
    "\n",
    "current = pats.address.where(pats.address.period.empty())\n",
    "\n",
    "simple_pats = pats.select(\n",
    "    {\n",
    "        \"id\": pats.id,\n",
    "        \"gender\": pats.gender,\n",
    "        \"birthdate\": pats.birthDate,\n",
    "        \"street\": current.line,\n",
    "        \"city\": current.city,\n",
    "        \"state\": current.state,\n",
    "        \"zip\": current.postalCode,\n",
    "    }\n",
    ")\n",
    "\n",
    "runner.to_dataframe(simple_pats, limit=5)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That's nice, but suppose we want to create it as an actual Spark view, i.e.,\n",
    "a virtual table that can be easily used by any application that uses Spark.\n",
    "This is basically what we did in the previous SQL section using `CREATE VIEW`.\n",
    "We can simply turn the above definition into a Spark/Hive view as well:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "runner.create_database_view(simple_pats, \"patient_current_address\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can run simple SQL queries against this view.\n",
    "\n",
    "_Note_: Since we set `view_dataset='demo_example'` when creating the Spark\n",
    "runner in the [Environment setup section](#Environment-setup), we need to\n",
    "include that in the SQL queries we write against these views."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>gender</th>\n",
       "      <th>birthdate</th>\n",
       "      <th>street</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>zip</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>38462</td>\n",
       "      <td>female</td>\n",
       "      <td>1961-10-01</td>\n",
       "      <td>[\"376 Mohr Annex Suite 88\"]</td>\n",
       "      <td>[\"Northampton\"]</td>\n",
       "      <td>[\"MA\"]</td>\n",
       "      <td>[\"01060\"]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9040</td>\n",
       "      <td>female</td>\n",
       "      <td>1957-10-31</td>\n",
       "      <td>[\"751 Kessler Divide Unit 49\"]</td>\n",
       "      <td>[\"Brookline\"]</td>\n",
       "      <td>[\"MA\"]</td>\n",
       "      <td>[\"02445\"]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>39570</td>\n",
       "      <td>male</td>\n",
       "      <td>1915-04-20</td>\n",
       "      <td>[\"988 Ryan Burg Apt 59\"]</td>\n",
       "      <td>[\"North Reading\"]</td>\n",
       "      <td>[\"MA\"]</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>27113</td>\n",
       "      <td>male</td>\n",
       "      <td>1962-04-23</td>\n",
       "      <td>[\"609 Steuber Crossroad Unit 49\"]</td>\n",
       "      <td>[\"Westfield\"]</td>\n",
       "      <td>[\"MA\"]</td>\n",
       "      <td>[\"01085\"]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>30404</td>\n",
       "      <td>male</td>\n",
       "      <td>1941-10-24</td>\n",
       "      <td>[\"617 D'Amore Course\"]</td>\n",
       "      <td>[\"Brewster\"]</td>\n",
       "      <td>[\"MA\"]</td>\n",
       "      <td>[\"02631\"]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id  gender  birthdate                             street  \\\n",
       "0  38462  female 1961-10-01        [\"376 Mohr Annex Suite 88\"]   \n",
       "1   9040  female 1957-10-31     [\"751 Kessler Divide Unit 49\"]   \n",
       "2  39570    male 1915-04-20           [\"988 Ryan Burg Apt 59\"]   \n",
       "3  27113    male 1962-04-23  [\"609 Steuber Crossroad Unit 49\"]   \n",
       "4  30404    male 1941-10-24             [\"617 D'Amore Course\"]   \n",
       "\n",
       "                city   state        zip  \n",
       "0    [\"Northampton\"]  [\"MA\"]  [\"01060\"]  \n",
       "1      [\"Brookline\"]  [\"MA\"]  [\"02445\"]  \n",
       "2  [\"North Reading\"]  [\"MA\"]         []  \n",
       "3      [\"Westfield\"]  [\"MA\"]  [\"01085\"]  \n",
       "4       [\"Brewster\"]  [\"MA\"]  [\"02631\"]  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pandas.read_sql_query(\n",
    "    sql=\"SELECT * FROM demo_example.patient_current_address LIMIT 5\",\n",
    "    con=query_engine,\n",
    ")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we have a nice, flattened patients table that meets the needs of our system and data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Observation view for indicators\n",
    "Here we try to recreate a view for calculating some PEPFAR indicators.\n",
    "This is similar to what we did with direct SQL above; but now we use\n",
    "FHIR-Views for the heavy lifting."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>obs_id</th>\n",
       "      <th>patient_id</th>\n",
       "      <th>code_sys</th>\n",
       "      <th>code</th>\n",
       "      <th>val_quantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10929</td>\n",
       "      <td>10392</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1088AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11327</td>\n",
       "      <td>10669</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1088AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12026</td>\n",
       "      <td>11149</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>5085AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>188.660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10063</td>\n",
       "      <td>9040</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>5088AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>36.921</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10798</td>\n",
       "      <td>10392</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>5088AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>36.833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>10030</td>\n",
       "      <td>9040</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>159800AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>10803</td>\n",
       "      <td>10392</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>159800AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>11995</td>\n",
       "      <td>11149</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>159800AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>11554</td>\n",
       "      <td>10669</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1261AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10880</td>\n",
       "      <td>10392</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1111AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10075</td>\n",
       "      <td>9040</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1271AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>10083</td>\n",
       "      <td>9040</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1271AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>10825</td>\n",
       "      <td>10392</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1271AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>11002</td>\n",
       "      <td>10392</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1271AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>11310</td>\n",
       "      <td>10669</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1271AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>11961</td>\n",
       "      <td>11149</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1271AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>11432</td>\n",
       "      <td>10669</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1250AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>11561</td>\n",
       "      <td>10669</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1250AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>11913</td>\n",
       "      <td>11149</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>1265AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>10197</td>\n",
       "      <td>9040</td>\n",
       "      <td>http://loinc.org</td>\n",
       "      <td>159911AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   obs_id patient_id          code_sys                                  code  \\\n",
       "0   10929      10392  http://loinc.org  1088AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "1   11327      10669  http://loinc.org  1088AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "2   12026      11149  http://loinc.org  5085AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "3   10063       9040  http://loinc.org  5088AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "4   10798      10392  http://loinc.org  5088AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "5   10030       9040  http://loinc.org  159800AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "6   10803      10392  http://loinc.org  159800AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "7   11995      11149  http://loinc.org  159800AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "8   11554      10669  http://loinc.org  1261AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "9   10880      10392  http://loinc.org  1111AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "10  10075       9040  http://loinc.org  1271AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "11  10083       9040  http://loinc.org  1271AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "12  10825      10392  http://loinc.org  1271AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "13  11002      10392  http://loinc.org  1271AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "14  11310      10669  http://loinc.org  1271AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "15  11961      11149  http://loinc.org  1271AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "16  11432      10669  http://loinc.org  1250AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "17  11561      10669  http://loinc.org  1250AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "18  11913      11149  http://loinc.org  1265AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "19  10197       9040  http://loinc.org  159911AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   \n",
       "\n",
       "    val_quantity  \n",
       "0            NaN  \n",
       "1            NaN  \n",
       "2        188.660  \n",
       "3         36.921  \n",
       "4         36.833  \n",
       "5            NaN  \n",
       "6            NaN  \n",
       "7            NaN  \n",
       "8            NaN  \n",
       "9            NaN  \n",
       "10           NaN  \n",
       "11           NaN  \n",
       "12           NaN  \n",
       "13           NaN  \n",
       "14           NaN  \n",
       "15           NaN  \n",
       "16           NaN  \n",
       "17           NaN  \n",
       "18           NaN  \n",
       "19           NaN  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# TODO: fix the `where` clause Spark SQL issues.\n",
    "\n",
    "obs_code_val = obs.select(\n",
    "    {\n",
    "        \"obs_id\": obs.id,\n",
    "        \"patient_id\": obs.subject.idFor('Patient'),\n",
    "        \"code_sys\": obs.code.coding.system.first(),\n",
    "        \"code\": obs.code.coding.code.first(),\n",
    "        \"val_quantity\": obs.value.ofType('quantity').value,\n",
    "    }).where(\n",
    "        # obs.code.coding.system=='http://loinc.org'\n",
    "        # obs.code.coding.code=='1088AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'\n",
    ")\n",
    "\n",
    "runner.to_dataframe(obs_code_val, limit=20)\n",
    "#print(runner.to_sql(obs_code_val))"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Checking the underlying SQL statements\n",
    "It is easy to expose the underlying SQL query that the FHIR-views library generates. This is done using the `to_sql` method that is part of the runner API. We show below the SQL queries generated of the two examples we used above:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT (SELECT id) AS id,(SELECT COLLECT_LIST(code)\n",
      "FROM (SELECT coding_element_.code\n",
      "FROM (SELECT category_element_\n",
      "FROM (SELECT EXPLODE(category_element_) AS category_element_ FROM (SELECT category AS category_element_))) LATERAL VIEW POSEXPLODE(category_element_.coding) AS index_coding_element_, coding_element_)\n",
      "WHERE code IS NOT NULL) AS category,(SELECT COLLECT_LIST(display)\n",
      "FROM (SELECT coding_element_.display\n",
      "FROM (SELECT code) LATERAL VIEW POSEXPLODE(code.coding) AS index_coding_element_, coding_element_)\n",
      "WHERE display IS NOT NULL) AS code_display,(SELECT status) AS status,(SELECT subject.patientId AS idFor_) AS __patientId__ FROM `default`.observation LIMIT 10\n"
     ]
    }
   ],
   "source": [
    "print(runner.to_sql(\n",
    "    obs.select(\n",
    "        {\n",
    "            \"id\": obs.id,\n",
    "            \"category\": obs.category.coding.code,\n",
    "            \"code_display\": obs.code.coding.display,\n",
    "            \"status\": obs.status,\n",
    "        }\n",
    "    ),\n",
    "    limit=10,\n",
    "))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT (SELECT id) AS id,(SELECT gender) AS gender,(SELECT CAST(birthDate AS TIMESTAMP) AS birthDate) AS birthdate,(SELECT COLLECT_LIST(line_element_)\n",
      "FROM (SELECT line_element_\n",
      "FROM (SELECT address_element_\n",
      "FROM (SELECT EXPLODE(address_element_) AS address_element_ FROM (SELECT address AS address_element_))\n",
      "WHERE (SELECT CASE WHEN COUNT(*) = 0 THEN TRUE ELSE FALSE END AS empty_\n",
      "FROM (SELECT address_element_.period)\n",
      "WHERE period IS NOT NULL)) LATERAL VIEW POSEXPLODE(address_element_.line) AS index_line_element_, line_element_)\n",
      "WHERE line_element_ IS NOT NULL) AS street,(SELECT COLLECT_LIST(city)\n",
      "FROM (SELECT address_element_.city\n",
      "FROM (SELECT EXPLODE(address_element_) AS address_element_ FROM (SELECT address AS address_element_))\n",
      "WHERE (SELECT CASE WHEN COUNT(*) = 0 THEN TRUE ELSE FALSE END AS empty_\n",
      "FROM (SELECT address_element_.period)\n",
      "WHERE period IS NOT NULL))\n",
      "WHERE city IS NOT NULL) AS city,(SELECT COLLECT_LIST(state)\n",
      "FROM (SELECT address_element_.state\n",
      "FROM (SELECT EXPLODE(address_element_) AS address_element_ FROM (SELECT address AS address_element_))\n",
      "WHERE (SELECT CASE WHEN COUNT(*) = 0 THEN TRUE ELSE FALSE END AS empty_\n",
      "FROM (SELECT address_element_.period)\n",
      "WHERE period IS NOT NULL))\n",
      "WHERE state IS NOT NULL) AS state,(SELECT COLLECT_LIST(postalCode)\n",
      "FROM (SELECT address_element_.postalCode\n",
      "FROM (SELECT EXPLODE(address_element_) AS address_element_ FROM (SELECT address AS address_element_))\n",
      "WHERE (SELECT CASE WHEN COUNT(*) = 0 THEN TRUE ELSE FALSE END AS empty_\n",
      "FROM (SELECT address_element_.period)\n",
      "WHERE period IS NOT NULL))\n",
      "WHERE postalCode IS NOT NULL) AS zip,(SELECT id) AS __patientId__ FROM `default`.patient LIMIT 5\n"
     ]
    }
   ],
   "source": [
    "current = pats.address.where(pats.address.period.empty())\n",
    "\n",
    "simple_pats = pats.select(\n",
    "    {\n",
    "        \"id\": pats.id,\n",
    "        \"gender\": pats.gender,\n",
    "        \"birthdate\": pats.birthDate,\n",
    "        \"street\": current.line,\n",
    "        \"city\": current.city,\n",
    "        \"state\": current.state,\n",
    "        \"zip\": current.postalCode,\n",
    "    }\n",
    ")\n",
    "\n",
    "print(runner.to_sql(simple_pats, limit=5))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Appendix\n",
    "Here are some more helper queries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pandas.read_sql_query(\n",
    "    sql=\"SHOW TABLES;\",\n",
    "    con=query_engine,\n",
    ")\n",
    "\n",
    "df[~df.tableName.str.contains('_2023_')]"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}