batch-example.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import sys\n",
    "from pyspark import SparkContext, SparkConf\n",
    "from batch.encounter_job import EncounterJob\n",
    "from common.utils import PipelineUtils"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('spark.sql.repl.eagerEval.enabled', 'True'), ('spark.sql.shuffle.partitions', '1000'), ('spark.submit.pyFiles', '/Users/akimaina/.ivy2/jars/mysql_mysql-connector-java-8.0.19.jar,/Users/akimaina/.ivy2/jars/io.delta_delta-core_2.11-0.5.0.jar,/Users/akimaina/.ivy2/jars/com.google.protobuf_protobuf-java-3.6.1.jar,/Users/akimaina/.ivy2/jars/org.antlr_antlr4-4.7.jar,/Users/akimaina/.ivy2/jars/org.antlr_antlr4-runtime-4.7.jar,/Users/akimaina/.ivy2/jars/org.antlr_antlr-runtime-3.5.2.jar,/Users/akimaina/.ivy2/jars/org.antlr_ST4-4.0.8.jar,/Users/akimaina/.ivy2/jars/org.abego.treelayout_org.abego.treelayout.core-1.0.3.jar,/Users/akimaina/.ivy2/jars/org.glassfish_javax.json-1.0.4.jar,/Users/akimaina/.ivy2/jars/com.ibm.icu_icu4j-58.2.jar'), ('spark.app.name', 'Openmrs ELT Pipeline'), ('spark.executor.memory', '5g'), ('spark.executor.heartbeatInterval', '10000000'), ('spark.sql.crossJoin.enabled', 'True'), ('spark.driver.maxResultSize', '20g'), ('spark.repl.local.jars', 'file:///Users/akimaina/.ivy2/jars/mysql_mysql-connector-java-8.0.19.jar,file:///Users/akimaina/.ivy2/jars/io.delta_delta-core_2.11-0.5.0.jar,file:///Users/akimaina/.ivy2/jars/com.google.protobuf_protobuf-java-3.6.1.jar,file:///Users/akimaina/.ivy2/jars/org.antlr_antlr4-4.7.jar,file:///Users/akimaina/.ivy2/jars/org.antlr_antlr4-runtime-4.7.jar,file:///Users/akimaina/.ivy2/jars/org.antlr_antlr-runtime-3.5.2.jar,file:///Users/akimaina/.ivy2/jars/org.antlr_ST4-4.0.8.jar,file:///Users/akimaina/.ivy2/jars/org.abego.treelayout_org.abego.treelayout.core-1.0.3.jar,file:///Users/akimaina/.ivy2/jars/org.glassfish_javax.json-1.0.4.jar,file:///Users/akimaina/.ivy2/jars/com.ibm.icu_icu4j-58.2.jar'), ('spark.driver.memory', '20g'), ('spark.master', 'local[*]'), ('spark.sql.autoBroadcastJoinThreshold', '0'), ('spark.submit.deployMode', 'client'), ('spark.files', 'file:///Users/akimaina/.ivy2/jars/mysql_mysql-connector-java-8.0.19.jar,file:///Users/akimaina/.ivy2/jars/io.delta_delta-core_2.11-0.5.0.jar,file:///Users/akimaina/.ivy2/jars/com.google.protobuf_protobuf-java-3.6.1.jar,file:///Users/akimaina/.ivy2/jars/org.antlr_antlr4-4.7.jar,file:///Users/akimaina/.ivy2/jars/org.antlr_antlr4-runtime-4.7.jar,file:///Users/akimaina/.ivy2/jars/org.antlr_antlr-runtime-3.5.2.jar,file:///Users/akimaina/.ivy2/jars/org.antlr_ST4-4.0.8.jar,file:///Users/akimaina/.ivy2/jars/org.abego.treelayout_org.abego.treelayout.core-1.0.3.jar,file:///Users/akimaina/.ivy2/jars/org.glassfish_javax.json-1.0.4.jar,file:///Users/akimaina/.ivy2/jars/com.ibm.icu_icu4j-58.2.jar'), ('spark.network.timeout', '10000000'), ('spark.executor.cores', '8'), ('spark.cores.max', '32'), ('spark.ui.showConsoleProgress', 'true'), ('spark.jars', 'file:///Users/akimaina/.ivy2/jars/mysql_mysql-connector-java-8.0.19.jar,file:///Users/akimaina/.ivy2/jars/io.delta_delta-core_2.11-0.5.0.jar,file:///Users/akimaina/.ivy2/jars/com.google.protobuf_protobuf-java-3.6.1.jar,file:///Users/akimaina/.ivy2/jars/org.antlr_antlr4-4.7.jar,file:///Users/akimaina/.ivy2/jars/org.antlr_antlr4-runtime-4.7.jar,file:///Users/akimaina/.ivy2/jars/org.antlr_antlr-runtime-3.5.2.jar,file:///Users/akimaina/.ivy2/jars/org.antlr_ST4-4.0.8.jar,file:///Users/akimaina/.ivy2/jars/org.abego.treelayout_org.abego.treelayout.core-1.0.3.jar,file:///Users/akimaina/.ivy2/jars/org.glassfish_javax.json-1.0.4.jar,file:///Users/akimaina/.ivy2/jars/com.ibm.icu_icu4j-58.2.jar')]\n"
     ]
    }
   ],
   "source": [
    "spark =PipelineUtils.getSpark()\n",
    "print(SparkConf().getAll()) # check of all packages are loaded"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Encounter Batch Started at = 11:25:28.201481\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table border='1'>\n",
       "<tr><th>patient_id</th><th>location_id</th><th>visit_id</th><th>encounter_datetime</th><th>encounter_type</th><th>dead</th><th>gender</th><th>death_date</th><th>patient_uuid</th><th>visit_type_id</th><th>birthdate</th><th>encounter_id</th><th>obs</th><th>orders</th><th>analysis_date</th></tr>\n",
       "<tr><td>45</td><td>1</td><td>212</td><td>2017-01-02 10:11:27</td><td>5</td><td>false</td><td>F</td><td>null</td><td>b93fe5b8-9d85-454...</td><td>1</td><td>1953-11-21</td><td>496</td><td>[{&quot;obs_id&quot;:4120,&quot;...</td><td>null</td><td>2020-03-04 11:27:...</td></tr>\n",
       "<tr><td>74</td><td>7</td><td>353</td><td>2015-04-18 09:26:12</td><td>2</td><td>false</td><td>F</td><td>null</td><td>c977b8e6-7953-412...</td><td>1</td><td>1990-12-08</td><td>833</td><td>[{&quot;obs_id&quot;:6858,&quot;...</td><td>null</td><td>2020-03-04 11:27:...</td></tr>\n",
       "<tr><td>117</td><td>1</td><td>568</td><td>2016-03-07 10:31:26</td><td>5</td><td>false</td><td>F</td><td>null</td><td>b4ae857a-2d2d-47a...</td><td>1</td><td>1960-06-01</td><td>1342</td><td>[{&quot;obs_id&quot;:11055,...</td><td>null</td><td>2020-03-04 11:27:...</td></tr>\n",
       "<tr><td>143</td><td>7</td><td>676</td><td>2015-09-13 09:48:32</td><td>2</td><td>false</td><td>F</td><td>null</td><td>f8465d06-df4d-419...</td><td>1</td><td>1945-10-11</td><td>1591</td><td>[{&quot;obs_id&quot;:13125,...</td><td>null</td><td>2020-03-04 11:27:...</td></tr>\n",
       "<tr><td>199</td><td>1</td><td>901</td><td>2017-01-08 10:33:51</td><td>5</td><td>false</td><td>M</td><td>null</td><td>fbe65acb-1fb4-4c4...</td><td>1</td><td>1960-01-16</td><td>2122</td><td>[{&quot;obs_id&quot;:17454,...</td><td>null</td><td>2020-03-04 11:27:...</td></tr>\n",
       "<tr><td>226</td><td>1</td><td>1005</td><td>2016-10-25 10:30:56</td><td>5</td><td>false</td><td>M</td><td>null</td><td>580613c5-280e-4e4...</td><td>1</td><td>1935-06-14</td><td>2366</td><td>[{&quot;obs_id&quot;:19433,...</td><td>null</td><td>2020-03-04 11:27:...</td></tr>\n",
       "<tr><td>351</td><td>1</td><td>1659</td><td>2015-06-20 11:16:37</td><td>5</td><td>false</td><td>F</td><td>null</td><td>6ba84150-ac8a-45e...</td><td>1</td><td>1979-11-11</td><td>3918</td><td>[{&quot;obs_id&quot;:32249,...</td><td>null</td><td>2020-03-04 11:27:...</td></tr>\n",
       "<tr><td>373</td><td>7</td><td>1737</td><td>2015-02-12 09:51:04</td><td>2</td><td>false</td><td>F</td><td>null</td><td>a71be44b-d351-4c1...</td><td>1</td><td>1975-07-18</td><td>4101</td><td>[{&quot;obs_id&quot;:33713,...</td><td>null</td><td>2020-03-04 11:27:...</td></tr>\n",
       "<tr><td>406</td><td>7</td><td>1913</td><td>2015-09-02 09:39:31</td><td>2</td><td>false</td><td>F</td><td>null</td><td>4fd4987f-b0a7-47d...</td><td>1</td><td>1969-03-05</td><td>4519</td><td>[{&quot;obs_id&quot;:37194,...</td><td>null</td><td>2020-03-04 11:27:...</td></tr>\n",
       "<tr><td>24</td><td>7</td><td>103</td><td>2016-08-30 09:57:19</td><td>2</td><td>false</td><td>F</td><td>null</td><td>c4f0a856-5abe-47f...</td><td>1</td><td>1990-02-07</td><td>243</td><td>[{&quot;obs_id&quot;:1983,&quot;...</td><td>null</td><td>2020-03-04 11:27:...</td></tr>\n",
       "<tr><td>49</td><td>1</td><td>229</td><td>2016-03-29 10:06:47</td><td>5</td><td>false</td><td>M</td><td>null</td><td>261c0746-ddff-437...</td><td>1</td><td>1980-11-04</td><td>540</td><td>[{&quot;obs_id&quot;:4455,&quot;...</td><td>null</td><td>2020-03-04 11:27:...</td></tr>\n",
       "<tr><td>294</td><td>1</td><td>1368</td><td>2015-05-09 11:22:02</td><td>5</td><td>false</td><td>M</td><td>null</td><td>950850df-a77b-451...</td><td>1</td><td>1967-02-18</td><td>3226</td><td>[{&quot;obs_id&quot;:26529,...</td><td>null</td><td>2020-03-04 11:27:...</td></tr>\n",
       "<tr><td>381</td><td>1</td><td>1774</td><td>2015-08-04 10:46:49</td><td>5</td><td>false</td><td>M</td><td>null</td><td>41d874f3-1d34-4e6...</td><td>1</td><td>1980-07-18</td><td>4190</td><td>[{&quot;obs_id&quot;:34467,...</td><td>null</td><td>2020-03-04 11:27:...</td></tr>\n",
       "<tr><td>148</td><td>1</td><td>701</td><td>2016-06-13 11:06:01</td><td>5</td><td>false</td><td>M</td><td>null</td><td>44a2b724-da4f-48c...</td><td>1</td><td>1993-12-09</td><td>1650</td><td>[{&quot;obs_id&quot;:13617,...</td><td>null</td><td>2020-03-04 11:27:...</td></tr>\n",
       "<tr><td>254</td><td>7</td><td>1153</td><td>2015-07-25 10:21:49</td><td>2</td><td>false</td><td>F</td><td>null</td><td>71055de8-3c7c-421...</td><td>1</td><td>1990-09-06</td><td>2711</td><td>[{&quot;obs_id&quot;:22283,...</td><td>null</td><td>2020-03-04 11:27:...</td></tr>\n",
       "<tr><td>301</td><td>1</td><td>1420</td><td>2015-09-22 11:23:04</td><td>5</td><td>false</td><td>F</td><td>null</td><td>4b714699-433e-4d3...</td><td>1</td><td>1987-07-03</td><td>3352</td><td>[{&quot;obs_id&quot;:27578,...</td><td>null</td><td>2020-03-04 11:27:...</td></tr>\n",
       "<tr><td>339</td><td>7</td><td>1593</td><td>2015-04-10 09:43:22</td><td>2</td><td>false</td><td>F</td><td>null</td><td>b6d9789f-d93b-475...</td><td>1</td><td>1958-07-11</td><td>3761</td><td>[{&quot;obs_id&quot;:30926,...</td><td>null</td><td>2020-03-04 11:27:...</td></tr>\n",
       "<tr><td>233</td><td>7</td><td>1039</td><td>2016-05-04 10:05:35</td><td>2</td><td>false</td><td>F</td><td>null</td><td>2953a957-aee5-4f6...</td><td>1</td><td>1931-03-11</td><td>2443</td><td>[{&quot;obs_id&quot;:20081,...</td><td>null</td><td>2020-03-04 11:27:...</td></tr>\n",
       "<tr><td>235</td><td>1</td><td>1058</td><td>2016-04-11 11:01:57</td><td>5</td><td>false</td><td>F</td><td>null</td><td>be6310b3-5036-4bc...</td><td>1</td><td>1940-04-15</td><td>2488</td><td>[{&quot;obs_id&quot;:20456,...</td><td>null</td><td>2020-03-04 11:27:...</td></tr>\n",
       "<tr><td>256</td><td>1</td><td>1168</td><td>2015-02-19 11:15:06</td><td>5</td><td>false</td><td>M</td><td>null</td><td>7cc2ca9c-90db-458...</td><td>1</td><td>1963-10-12</td><td>2748</td><td>[{&quot;obs_id&quot;:22587,...</td><td>null</td><td>2020-03-04 11:27:...</td></tr>\n",
       "</table>\n",
       "only showing top 20 rows\n"
      ],
      "text/plain": [
       "+----------+-----------+--------+-------------------+--------------+-----+------+----------+--------------------+-------------+----------+------------+--------------------+------+--------------------+\n",
       "|patient_id|location_id|visit_id| encounter_datetime|encounter_type| dead|gender|death_date|        patient_uuid|visit_type_id| birthdate|encounter_id|                 obs|orders|       analysis_date|\n",
       "+----------+-----------+--------+-------------------+--------------+-----+------+----------+--------------------+-------------+----------+------------+--------------------+------+--------------------+\n",
       "|        45|          1|     212|2017-01-02 10:11:27|             5|false|     F|      null|b93fe5b8-9d85-454...|            1|1953-11-21|         496|[{\"obs_id\":4120,\"...|  null|2020-03-04 11:25:...|\n",
       "|        74|          7|     353|2015-04-18 09:26:12|             2|false|     F|      null|c977b8e6-7953-412...|            1|1990-12-08|         833|[{\"obs_id\":6858,\"...|  null|2020-03-04 11:25:...|\n",
       "|       117|          1|     568|2016-03-07 10:31:26|             5|false|     F|      null|b4ae857a-2d2d-47a...|            1|1960-06-01|        1342|[{\"obs_id\":11055,...|  null|2020-03-04 11:25:...|\n",
       "|       143|          7|     676|2015-09-13 09:48:32|             2|false|     F|      null|f8465d06-df4d-419...|            1|1945-10-11|        1591|[{\"obs_id\":13125,...|  null|2020-03-04 11:25:...|\n",
       "|       199|          1|     901|2017-01-08 10:33:51|             5|false|     M|      null|fbe65acb-1fb4-4c4...|            1|1960-01-16|        2122|[{\"obs_id\":17454,...|  null|2020-03-04 11:25:...|\n",
       "|       226|          1|    1005|2016-10-25 10:30:56|             5|false|     M|      null|580613c5-280e-4e4...|            1|1935-06-14|        2366|[{\"obs_id\":19433,...|  null|2020-03-04 11:25:...|\n",
       "|       351|          1|    1659|2015-06-20 11:16:37|             5|false|     F|      null|6ba84150-ac8a-45e...|            1|1979-11-11|        3918|[{\"obs_id\":32249,...|  null|2020-03-04 11:25:...|\n",
       "|       373|          7|    1737|2015-02-12 09:51:04|             2|false|     F|      null|a71be44b-d351-4c1...|            1|1975-07-18|        4101|[{\"obs_id\":33713,...|  null|2020-03-04 11:25:...|\n",
       "|       406|          7|    1913|2015-09-02 09:39:31|             2|false|     F|      null|4fd4987f-b0a7-47d...|            1|1969-03-05|        4519|[{\"obs_id\":37194,...|  null|2020-03-04 11:25:...|\n",
       "|        24|          7|     103|2016-08-30 09:57:19|             2|false|     F|      null|c4f0a856-5abe-47f...|            1|1990-02-07|         243|[{\"obs_id\":1983,\"...|  null|2020-03-04 11:25:...|\n",
       "|        49|          1|     229|2016-03-29 10:06:47|             5|false|     M|      null|261c0746-ddff-437...|            1|1980-11-04|         540|[{\"obs_id\":4455,\"...|  null|2020-03-04 11:25:...|\n",
       "|       294|          1|    1368|2015-05-09 11:22:02|             5|false|     M|      null|950850df-a77b-451...|            1|1967-02-18|        3226|[{\"obs_id\":26529,...|  null|2020-03-04 11:25:...|\n",
       "|       381|          1|    1774|2015-08-04 10:46:49|             5|false|     M|      null|41d874f3-1d34-4e6...|            1|1980-07-18|        4190|[{\"obs_id\":34467,...|  null|2020-03-04 11:25:...|\n",
       "|       148|          1|     701|2016-06-13 11:06:01|             5|false|     M|      null|44a2b724-da4f-48c...|            1|1993-12-09|        1650|[{\"obs_id\":13617,...|  null|2020-03-04 11:25:...|\n",
       "|       254|          7|    1153|2015-07-25 10:21:49|             2|false|     F|      null|71055de8-3c7c-421...|            1|1990-09-06|        2711|[{\"obs_id\":22283,...|  null|2020-03-04 11:25:...|\n",
       "|       301|          1|    1420|2015-09-22 11:23:04|             5|false|     F|      null|4b714699-433e-4d3...|            1|1987-07-03|        3352|[{\"obs_id\":27578,...|  null|2020-03-04 11:25:...|\n",
       "|       339|          7|    1593|2015-04-10 09:43:22|             2|false|     F|      null|b6d9789f-d93b-475...|            1|1958-07-11|        3761|[{\"obs_id\":30926,...|  null|2020-03-04 11:25:...|\n",
       "|       233|          7|    1039|2016-05-04 10:05:35|             2|false|     F|      null|2953a957-aee5-4f6...|            1|1931-03-11|        2443|[{\"obs_id\":20081,...|  null|2020-03-04 11:25:...|\n",
       "|       235|          1|    1058|2016-04-11 11:01:57|             5|false|     F|      null|be6310b3-5036-4bc...|            1|1940-04-15|        2488|[{\"obs_id\":20456,...|  null|2020-03-04 11:25:...|\n",
       "|       256|          1|    1168|2015-02-19 11:15:06|             5|false|     M|      null|7cc2ca9c-90db-458...|            1|1963-10-12|        2748|[{\"obs_id\":22587,...|  null|2020-03-04 11:25:...|\n",
       "+----------+-----------+--------+-------------------+--------------+-----+------+----------+--------------------+-------------+----------+------------+--------------------+------+--------------------+\n",
       "only showing top 20 rows"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "job = EncounterJob()\n",
    "flat_obs = job.run()\n",
    "flat_obs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 12 ms, sys: 12.7 ms, total: 24.7 ms\n",
      "Wall time: 2min 3s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "flat_obs.coalesce(100).write.format(\"delta\").mode(\"overwrite\").save(\"flat_obs_orders.delta\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.7.4 64-bit ('anaconda3': virtualenv)",
   "language": "python",
   "name": "python37464bitanaconda3virtualenvefdffc192b144e2b98dfc27892fa181f"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}