pyspark_speedTest_and_spark_SQL_magic.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "toc_visible": true,
      "authorship_tag": "ABX9TyO4Zy/YuTDlCNqlU8/peBvA",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/Ellen-Gu/jupyter-colab/blob/main/pyspark_speedTest_and_spark_SQL_magic.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "##**Some Stuff about pyspark :**\n",
        "*   **Part 1. pyspark speed test (using MC to get approximate value of  π)**\n",
        "*   **Part 2. Spark SQL Magic (akin to what's available in Databricks)**"
      ],
      "metadata": {
        "id": "P2uYQEjorsct"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "_______________________________________________________________________"
      ],
      "metadata": {
        "id": "L_SWYe0VtJc1"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### **Part 1. pyspark speed test, using MC to get approximate value of $\\pi$**"
      ],
      "metadata": {
        "id": "43e2Pbu8qPv6"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# To use pyspark it is better to install pyspark and all its extra modules, such as the pyarrow\n",
        "# pyspark webpage has a very clear installation instruction.\n",
        "# To run pyspark, java (version>=8, 11, or 17) must be installed, and JAVA_HOME shall be set as env variable\n",
        "# Google colab use java 11, while in my local running env java 17 can be installed"
      ],
      "metadata": {
        "id": "bdc9NKQs82k-"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Below is a modification of PySpark's example that employs the Monte Carlo (MC) method to estimate the value of $\\pi$. While it's feasible to execute these operations on the Google Colab computing environment, the outcomes leave something to be desired, especially when considering Spark's reputation for rapid execution. I conducted a speed test on Google Colab (utilizing a free account with 12.7GB RAM) to gauge the potential time savings. The results are as follows and they seems not suggest that spark is very fast, or, more precisly, google colab free account may not powerful enough to make spark's benefit exceeds much of its overhead effect. The first entry indicates the time taken for conventional Python computation without Spark. The second entry represents the time PySpark took to calculate $\\pi$.\n",
        "Both tests employed the MC method to estimate $\\pi$, a technique that leverages mean approximation to expectation, with weights sourced from random numbers drawn from the underlying joint distribution.\n",
        "\n",
        "* 9.74 s ± 1.49 s per loop (mean ± std. dev. of 7 runs, 1 loop each)\n",
        "* 9.48 s ± 899 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n",
        "\n",
        "Then I switch the running environment to my local one. In my local environment, the time zone is set to be UTC+0. Since Java is using port 4040 and 4041, spark has to use 4042."
      ],
      "metadata": {
        "id": "C4z032rpvFtQ"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark.sql import SparkSession\n",
        "from numpy import random\n",
        "import pandas as pd, numpy as np\n",
        "spark = SparkSession.builder.getOrCreate()\n",
        "sc = spark.sparkContext"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "QjSiwjbpgO71",
        "outputId": "6ca6ac05-a6c5-447a-89fc-037999969ec7"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "23/08/17 23:08:25 WARN Utils: Service 'SparkUI' could not bind on port 4040. Attempting port 4041.\n",
            "23/08/17 23:08:25 WARN Utils: Service 'SparkUI' could not bind on port 4041. Attempting port 4042.\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "def sample(p):\n",
        "    x, y = random.random(), random.random()\n",
        "    return 1 if x*x + y*y < 1 else 0\n",
        "\n",
        "# Number of samples\n",
        "n = 5000000\n",
        "\n",
        "pi_mc=pd.Series(np.arange(n)).apply(sample).sum()/n*4\n",
        "print(\"Pi is roughly %f\" % (pi_mc))\n",
        "%timeit pi=pd.Series(np.arange(n)).apply(sample).sum()/n*4\n",
        "\n",
        "count = sc.parallelize(range(0, n)).map(sample).reduce(lambda a, b: a + b)\n",
        "print(\"Pi is roughly %f\" % (4.0 * count / n))\n",
        "%timeit count = sc.parallelize(range(0, n)).map(sample).reduce(lambda a, b: a + b)\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "m_s6hBo4nWkI",
        "outputId": "d6b30396-450d-4af4-837d-534465024a8b"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Pi is roughly 3.142033\n",
            "5.76 s ± 230 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": []
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Pi is roughly 3.141728\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": []
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "2.29 s ± 40.4 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "\r                                                                                \r"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "when using pyspark in the above test on my local environment, the results in the above show that spark's speed is between 2X ~ 3X of general python computing. This is just a mini test, data are small. For large data sets and other file system settings, the speed up of pyspark could reach 10X to 100X and even more as per openAI.\n",
        "\n",
        "In the test I did see that all 4 cores which allocated to my local docker environment work at 399% (4 threads) at peak time. The general python calculating generally only use 1 thread (at 100% so one thread is used). The overhead of pyspark may drag down some speed. When data is large the effect of overhead is diminishing. Later I may consider to allocate 6 cores to docker, and also compare numba's speed. Numba is another module which can speed up. So how speedy it can be on my local environment is waiting to be tested."
      ],
      "metadata": {
        "id": "U_wrVVy0mBom"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### **Part 2. Spark SQL Magic (akin to what's available in Databricks)**"
      ],
      "metadata": {
        "id": "8xyrgCDnquMC"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Another intriguing aspect is the use of IPython magic. Spark supports SQL syntax, and there are several IPython extensions, as well as line and cell magics available for use. Databricks also offers its %%sql magic that integrates seamlessly into Jupyter notebooks.\n",
        "\n",
        "In my local environment, I have both R and Python kernels. The rpy2 package in R introduces the %%R magic, making it incredibly convenient to integrate R and Python. There is also %%SAS magic when saspy module is available in the system.\n",
        "\n",
        "The magics is very convenient when working with jupyter notebook or jupyterlab. I have no interest to make a Spark server or install spark kernel, and do not want to install those magic extensions just for using %%sql instead of spark.sql(). Then can I still enjoy a home made %%sql or %sql magic like the one in databricks? With the vast number of individuals passionately working with Python, the answer is undoubtedly a 'YES'.\n",
        "\n",
        "In Ipython documentation webpage, https://ipython.readthedocs.io/en/stable/config/custommagics.html, there's a comprehensive list of features. Meanwhile, at https://notebook.community/LucaCanali/Miscellaneous/Pyspark_SQL_Magic_Jupyter/IPython_Pyspark_SQL_Magic , Luca Canali presents a succinct, clear, and easily understandable code snippet to create a line/cell SQL magic. I'm particularly fond of this one because the entire process of creating the magic is transparent and evident to users. It gives me more confidence in its use compared to other SQL magic extensions, even those available in PyPI collections.\n",
        "\n",
        "The %%sql magic from Luca Canali mirrors the functionality of %%SQL in Databricks. However, since the example code was written in September 2016, parts of it seem incompatible with today's Spark versions. I've made some updates and enhancements to the example to ensure it aligns with the recently installed PySpark (as of August 16, 2023) in my local Docker environment. Now, thanks to Luca Canali, the SQL examples run seamlessly using both %%sql and %sql magics. I'm thrilled that I can now enjoy the convenience of %%sql and %%R magics in my Jupyter setup. It's reminiscent of Databricks, yet even more convenient and customizable under my guidance.\n"
      ],
      "metadata": {
        "id": "U0J0HTrU381a"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# IPython magic functions to use with Pyspark and Spark SQL\n",
        "# The following code is intended as examples of shorcuts to simplify the use of SQL in pyspark\n",
        "# The defined functions are:\n",
        "#\n",
        "# %sql <statement>          - return a Spark DataFrame for lazy evaluation of the SQL\n",
        "# %sql_show <statement>     - run the SQL statement and show max_show_lines (50) lines\n",
        "# %sql_display <statement>  - run the SQL statement and display the results using a HTML table\n",
        "#                           - this is implemented passing via Pandas and displays up to max_show_lines (50)\n",
        "# %sql_explain <statement>  - display the execution plan of the SQL statement\n",
        "#\n",
        "# Use: %<magic> for line magic or %%<magic> for cell magic.\n",
        "#\n",
        "# Author: Luca.Canali@cern.ch\n",
        "# September 2016\n",
        "#\n",
        "\n",
        "from IPython.core.magic import register_line_cell_magic\n",
        "\n",
        "# Configuration parameters\n",
        "max_show_lines = 50         # Limit on the number of lines to show with %sql_show and %sql_display\n",
        "detailed_explain = True     # Set to False if you want to see only the physical plan when running explain\n",
        "\n",
        "\n",
        "@register_line_cell_magic\n",
        "def sql(line, cell=None):\n",
        "    \"Return a Spark DataFrame for lazy evaluation of the sql. Use: %sql or %%sql\"\n",
        "    val = cell if cell is not None else line\n",
        "    return spark.sql(val)\n",
        "\n",
        "@register_line_cell_magic\n",
        "def sql_show(line, cell=None):\n",
        "    \"Execute sql and show the first max_show_lines lines. Use: %sql_show or %%sql_show\"\n",
        "    val = cell if cell is not None else line\n",
        "    return spark.sql(val).show(max_show_lines)\n",
        "\n",
        "@register_line_cell_magic\n",
        "def sql_display(line, cell=None):\n",
        "    \"\"\"Execute sql and convert results to Pandas DataFrame for pretty display or further processing.\n",
        "    Use: %sql_display or %%sql_display\"\"\"\n",
        "    val = cell if cell is not None else line\n",
        "    return spark.sql(val).limit(max_show_lines).toPandas()\n",
        "\n",
        "@register_line_cell_magic\n",
        "def sql_explain(line, cell=None):\n",
        "    \"Display the execution plan of the sql. Use: %sql_explain or %%sql_explain\"\n",
        "    val = cell if cell is not None else line\n",
        "    return spark.sql(val).explain(detailed_explain)"
      ],
      "metadata": {
        "id": "5gaSf_osgD2u"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Now we have all line/cell sql magic defined. Let's happily run spark sql with them!"
      ],
      "metadata": {
        "id": "n6T72ZQE-E9W"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "To begin with PySpark, one must first initialize a Spark session. This concept may remind some of the SAS CAS session, doesn't it? To fully harness the capabilities of Spark, magics, and other features, we're left with two options: find an all-encompassing environment (which can be challenging and expensive) or learn to construct our own."
      ],
      "metadata": {
        "id": "dkMVOw6h-Zn6"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark.sql import SparkSession\n",
        "from numpy import random\n",
        "import pandas as pd, numpy as np\n",
        "spark = SparkSession.builder.getOrCreate()\n",
        "sc = spark.sparkContext"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "qZv2AuzY-Dmb",
        "outputId": "1857f1bd-7968-455a-e586-4bc3bbbefeab"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "23/08/17 19:48:28 WARN Utils: Service 'SparkUI' could not bind on port 4040. Attempting port 4041.\n",
            "23/08/17 19:48:28 WARN Utils: Service 'SparkUI' could not bind on port 4041. Attempting port 4042.\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Once set up, we can execute examples within this session. PySpark facilitates a two-way conversion between Spark DataFrames and Pandas DataFrames, enabling easy transitioning between the two. Once all tasks are completed, it's essential to terminate the Spark session. Contrary to the SAS CAS session, typically only one Spark session is permitted per kernel. However, with the right configurations, it's possible to operate multiple sessions concurrently."
      ],
      "metadata": {
        "id": "dV4Qw6ja_5_g"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Define test data and register it as tables\n",
        "# This is a classic example of employee and department relational tables\n",
        "# Test data will be used in the examples later in this notebook\n",
        "\n",
        "from pyspark.sql import Row\n",
        "\n",
        "Employee = Row(\"id\", \"name\", \"email\", \"manager_id\", \"dep_id\")\n",
        "df_emp = spark.createDataFrame([\n",
        "        Employee(1234, 'John', 'john@mail.com', 1236, 10),\n",
        "        Employee(1235, 'Mike', 'mike@mail.com', 1237, 10),\n",
        "        Employee(1236, 'Pat', 'pat@mail.com', 1237, 20),\n",
        "        Employee(1237, 'Claire', 'claire@mail.com', None, 20),\n",
        "        Employee(1238, 'Jim', 'jim@mail.com', 1236, 30)\n",
        "        ])\n",
        "df_emp.createOrReplaceTempView(\"employee\")\n",
        "#df_emp.registerTempTable(\"employee\")\n",
        "\n",
        "Department = Row(\"dep_id\", \"dep_name\")\n",
        "df_dep = spark.createDataFrame([\n",
        "        Department(10, 'Engineering'),\n",
        "        Department(20, 'Head Quarter'),\n",
        "        Department(30, 'Human resources')\n",
        "        ])\n",
        "df_dep.createOrReplaceTempView(\"department\")\n",
        "#df_dep.registerTempTable(\"department\")"
      ],
      "metadata": {
        "id": "k3a6M4HsgPxM"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "df = %sql select * from employee\n",
        "df"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "FBXH6R7igP4g",
        "outputId": "52691ead-2fc0-4ef9-fed5-9a1c596bdcca"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "DataFrame[id: bigint, name: string, email: string, manager_id: bigint, dep_id: bigint]"
            ]
          },
          "metadata": {},
          "execution_count": 10
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql_show\n",
        "select emp.id, emp.name, emp.email, emp.manager_id, dep.dep_name\n",
        "from employee emp, department dep\n",
        "where emp.dep_id=dep.dep_id"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "r1Y7ocUTgP9u",
        "outputId": "fe82eae2-7190-4ea5-d0cf-ad07c81a89bb"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": []
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----+------+---------------+----------+---------------+\n",
            "|  id|  name|          email|manager_id|       dep_name|\n",
            "+----+------+---------------+----------+---------------+\n",
            "|1234|  John|  john@mail.com|      1236|    Engineering|\n",
            "|1235|  Mike|  mike@mail.com|      1237|    Engineering|\n",
            "|1236|   Pat|   pat@mail.com|      1237|   Head Quarter|\n",
            "|1237|Claire|claire@mail.com|      null|   Head Quarter|\n",
            "|1238|   Jim|   jim@mail.com|      1236|Human resources|\n",
            "+----+------+---------------+----------+---------------+\n",
            "\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "\r                                                                                \r"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql_display\n",
        "select emp.id, emp.name, emp.email, emp2.name as manager_name, dep.dep_name\n",
        "from employee emp\n",
        "     left outer join employee emp2 on emp2.id=emp.manager_id\n",
        "     join department dep on emp.dep_id=dep.dep_id"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "lKAwiKCTgQCd",
        "outputId": "651aff12-72a5-46b5-ce71-c189e8d4ad6d"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "\r[Stage 6:============================================>              (3 + 1) / 4]\r\r                                                                                \r"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "     id    name            email manager_name         dep_name\n",
              "0  1235    Mike    mike@mail.com       Claire      Engineering\n",
              "1  1234    John    john@mail.com          Pat      Engineering\n",
              "2  1237  Claire  claire@mail.com         None     Head Quarter\n",
              "3  1236     Pat     pat@mail.com       Claire     Head Quarter\n",
              "4  1238     Jim     jim@mail.com          Pat  Human resources"
            ],
            "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>name</th>\n",
              "      <th>email</th>\n",
              "      <th>manager_name</th>\n",
              "      <th>dep_name</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1235</td>\n",
              "      <td>Mike</td>\n",
              "      <td>mike@mail.com</td>\n",
              "      <td>Claire</td>\n",
              "      <td>Engineering</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1234</td>\n",
              "      <td>John</td>\n",
              "      <td>john@mail.com</td>\n",
              "      <td>Pat</td>\n",
              "      <td>Engineering</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>1237</td>\n",
              "      <td>Claire</td>\n",
              "      <td>claire@mail.com</td>\n",
              "      <td>None</td>\n",
              "      <td>Head Quarter</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>1236</td>\n",
              "      <td>Pat</td>\n",
              "      <td>pat@mail.com</td>\n",
              "      <td>Claire</td>\n",
              "      <td>Head Quarter</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>1238</td>\n",
              "      <td>Jim</td>\n",
              "      <td>jim@mail.com</td>\n",
              "      <td>Pat</td>\n",
              "      <td>Human resources</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ]
          },
          "metadata": {},
          "execution_count": 11
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "spark.sql('''CREATE OR REPLACE TEMPORARY VIEW emp as select * from employee''')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "HbAbjyFJgQHc",
        "outputId": "64a623c0-2ba3-40de-ff2c-5346a4b47c13"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "DataFrame[]"
            ]
          },
          "metadata": {},
          "execution_count": 12
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "%sql_show select * from emp"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "H4HLRAeGiVX2",
        "outputId": "50f2764e-b90a-4b31-90ac-169b64ddd3d0"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----+------+---------------+----------+------+\n",
            "|  id|  name|          email|manager_id|dep_id|\n",
            "+----+------+---------------+----------+------+\n",
            "|1234|  John|  john@mail.com|      1236|    10|\n",
            "|1235|  Mike|  mike@mail.com|      1237|    10|\n",
            "|1236|   Pat|   pat@mail.com|      1237|    20|\n",
            "|1237|Claire|claire@mail.com|      null|    20|\n",
            "|1238|   Jim|   jim@mail.com|      1236|    30|\n",
            "+----+------+---------------+----------+------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "psdf = %sql select * from emp\n",
        "psdf  #spark dataframe"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "YY9qwzIxighZ",
        "outputId": "8e5d0987-3c8f-47a3-8d6e-7e35386dd9d3"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "DataFrame[id: bigint, name: string, email: string, manager_id: bigint, dep_id: bigint]"
            ]
          },
          "metadata": {},
          "execution_count": 14
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "pddf=psdf.toPandas()\n",
        "pddf  #pandas dataframe"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "Njn8wfb9ijjd",
        "outputId": "cf4af943-90b7-4479-c68f-b5a6cc9337fb"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "     id    name            email  manager_id  dep_id\n",
              "0  1234    John    john@mail.com      1236.0      10\n",
              "1  1235    Mike    mike@mail.com      1237.0      10\n",
              "2  1236     Pat     pat@mail.com      1237.0      20\n",
              "3  1237  Claire  claire@mail.com         NaN      20\n",
              "4  1238     Jim     jim@mail.com      1236.0      30"
            ],
            "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>name</th>\n",
              "      <th>email</th>\n",
              "      <th>manager_id</th>\n",
              "      <th>dep_id</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1234</td>\n",
              "      <td>John</td>\n",
              "      <td>john@mail.com</td>\n",
              "      <td>1236.0</td>\n",
              "      <td>10</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1235</td>\n",
              "      <td>Mike</td>\n",
              "      <td>mike@mail.com</td>\n",
              "      <td>1237.0</td>\n",
              "      <td>10</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>1236</td>\n",
              "      <td>Pat</td>\n",
              "      <td>pat@mail.com</td>\n",
              "      <td>1237.0</td>\n",
              "      <td>20</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>1237</td>\n",
              "      <td>Claire</td>\n",
              "      <td>claire@mail.com</td>\n",
              "      <td>NaN</td>\n",
              "      <td>20</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>1238</td>\n",
              "      <td>Jim</td>\n",
              "      <td>jim@mail.com</td>\n",
              "      <td>1236.0</td>\n",
              "      <td>30</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ]
          },
          "metadata": {},
          "execution_count": 15
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "pddf2 = %sql_display select * from emp\n",
        "pddf2  #use %sql_display to get spark temp data and then turn to pandas dataframe in one shot, only first 50 rows are fetched"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "6fTkZ_6LjR29",
        "outputId": "f6540e41-ee20-4200-877b-18cae500925f"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "     id    name            email  manager_id  dep_id\n",
              "0  1234    John    john@mail.com      1236.0      10\n",
              "1  1235    Mike    mike@mail.com      1237.0      10\n",
              "2  1236     Pat     pat@mail.com      1237.0      20\n",
              "3  1237  Claire  claire@mail.com         NaN      20\n",
              "4  1238     Jim     jim@mail.com      1236.0      30"
            ],
            "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>name</th>\n",
              "      <th>email</th>\n",
              "      <th>manager_id</th>\n",
              "      <th>dep_id</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1234</td>\n",
              "      <td>John</td>\n",
              "      <td>john@mail.com</td>\n",
              "      <td>1236.0</td>\n",
              "      <td>10</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1235</td>\n",
              "      <td>Mike</td>\n",
              "      <td>mike@mail.com</td>\n",
              "      <td>1237.0</td>\n",
              "      <td>10</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>1236</td>\n",
              "      <td>Pat</td>\n",
              "      <td>pat@mail.com</td>\n",
              "      <td>1237.0</td>\n",
              "      <td>20</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>1237</td>\n",
              "      <td>Claire</td>\n",
              "      <td>claire@mail.com</td>\n",
              "      <td>NaN</td>\n",
              "      <td>20</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>1238</td>\n",
              "      <td>Jim</td>\n",
              "      <td>jim@mail.com</td>\n",
              "      <td>1236.0</td>\n",
              "      <td>30</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ]
          },
          "metadata": {},
          "execution_count": 16
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Stop the SparkContext\n",
        "spark.stop()"
      ],
      "metadata": {
        "id": "53e0QFw3npDB"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}