ch2_SparkSql.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.8.3"
    },
    "colab": {
      "name": "ch2-SparkSql.ipynb",
      "provenance": [],
      "collapsed_sections": []
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_eFggHFJHHr3"
      },
      "source": [
        "## **Installation java et spark**\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7FjUUbdlHLBC"
      },
      "source": [
        "!apt-get install openjdk-8-jdk-headless -qq > /dev/null"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "T4TyF_qFHTZC"
      },
      "source": [
        "!wget -q https://dlcdn.apache.org/spark/spark-3.2.0/spark-3.2.0-bin-hadoop3.2.tgz"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "EEQNALvHHWGJ"
      },
      "source": [
        "!tar xf spark-3.2.0-bin-hadoop3.2.tgz"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "XlPg7YQDHWtD"
      },
      "source": [
        "!pip install -q findspark"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MilHpzg0HYpD"
      },
      "source": [
        "import os\n",
        "os.environ[\"JAVA_HOME\"] = \"/usr/lib/jvm/java-8-openjdk-amd64\"\n",
        "os.environ[\"SPARK_HOME\"] = \"/content/spark-3.2.0-bin-hadoop3.2\""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "UNGXl5n4HbU9"
      },
      "source": [
        "import findspark\n",
        "findspark.init()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "pH1lcBsWHiyu"
      },
      "source": [
        "# **Connection google drive**\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "qA3ds0AFHkWa",
        "outputId": "5294dce7-f877-41ea-c9a3-fbcc3c5244a3"
      },
      "source": [
        "from google.colab import drive\n",
        "drive.mount('/content/drive')"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Mounted at /content/drive\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YQHYbuDRHq4-"
      },
      "source": [
        "# **Exemples Spark Sql**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Xe7PTl1gIgMJ",
        "outputId": "4fa58312-1876-452c-c65c-c56879cc9bea"
      },
      "source": [
        "!wget https://bin.equinox.io/c/4VmDzA7iaHb/ngrok-stable-linux-amd64.zip\n",
        "!unzip ngrok-stable-linux-amd64.zip\n",
        "get_ipython().system_raw('./ngrok http 4050 &')\n",
        "!curl -s http://localhost:4040/api/tunnels"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "--2021-11-16 13:33:22--  https://bin.equinox.io/c/4VmDzA7iaHb/ngrok-stable-linux-amd64.zip\n",
            "Resolving bin.equinox.io (bin.equinox.io)... 18.205.222.128, 54.237.133.81, 52.202.168.65, ...\n",
            "Connecting to bin.equinox.io (bin.equinox.io)|18.205.222.128|:443... connected.\n",
            "HTTP request sent, awaiting response... 200 OK\n",
            "Length: 13832437 (13M) [application/octet-stream]\n",
            "Saving to: ‘ngrok-stable-linux-amd64.zip.2’\n",
            "\n",
            "ngrok-stable-linux- 100%[===================>]  13.19M  53.1MB/s    in 0.2s    \n",
            "\n",
            "2021-11-16 13:33:23 (53.1 MB/s) - ‘ngrok-stable-linux-amd64.zip.2’ saved [13832437/13832437]\n",
            "\n",
            "Archive:  ngrok-stable-linux-amd64.zip\n",
            "replace ngrok? [y]es, [n]o, [A]ll, [N]one, [r]ename: y\n",
            "  inflating: ngrok                   \n",
            "{\"tunnels\":[{\"name\":\"command_line\",\"uri\":\"/api/tunnels/command_line\",\"public_url\":\"https://ce9f-34-73-41-245.ngrok.io\",\"proto\":\"https\",\"config\":{\"addr\":\"http://localhost:4050\",\"inspect\":true},\"metrics\":{\"conns\":{\"count\":2,\"gauge\":0,\"rate1\":0.000014973308430835861,\"rate5\":0.0014268438287786864,\"rate15\":0.001329256938616008,\"p50\":2028762.5,\"p90\":2144549,\"p95\":2144549,\"p99\":2144549},\"http\":{\"count\":2,\"rate1\":0.000014973308430835861,\"rate5\":0.0014268438287786864,\"rate15\":0.001329256938616008,\"p50\":941527,\"p90\":1512769,\"p95\":1512769,\"p99\":1512769}}},{\"name\":\"command_line (http)\",\"uri\":\"/api/tunnels/command_line%20%28http%29\",\"public_url\":\"http://ce9f-34-73-41-245.ngrok.io\",\"proto\":\"http\",\"config\":{\"addr\":\"http://localhost:4050\",\"inspect\":true},\"metrics\":{\"conns\":{\"count\":0,\"gauge\":0,\"rate1\":0,\"rate5\":0,\"rate15\":0,\"p50\":0,\"p90\":0,\"p95\":0,\"p99\":0},\"http\":{\"count\":0,\"rate1\":0,\"rate5\":0,\"rate15\":0,\"p50\":0,\"p90\":0,\"p95\":0,\"p99\":0}}}],\"uri\":\"/api/tunnels\"}\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "j_NX2dG-94YL"
      },
      "source": [
        "**Exemple 1 : Bank**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "xv06ADptK2X9",
        "outputId": "20f474de-b1a4-4e38-da72-f3cff0df4f47"
      },
      "source": [
        "## Spark Context\n",
        "from pyspark import SparkContext\n",
        "from pyspark.sql import SQLContext\n",
        "\n",
        "sc =SparkContext.getOrCreate()\n",
        "sqlContext = SQLContext(sc)\n",
        "df_b = sqlContext.read.csv('drive/My Drive/Colab_data/cours_spark/bank.csv',header=True)\n"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/content/spark-3.2.0-bin-hadoop3.2/python/pyspark/sql/context.py:79: FutureWarning: Deprecated in 3.0.0. Use SparkSession.builder.getOrCreate() instead.\n",
            "  FutureWarning\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "GLf0qcK57vS8",
        "outputId": "4dc59a06-7324-4370-ac43-90b5a8c4fd33"
      },
      "source": [
        "df_b.printSchema()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- age: string (nullable = true)\n",
            " |-- job: string (nullable = true)\n",
            " |-- marital: string (nullable = true)\n",
            " |-- education: string (nullable = true)\n",
            " |-- balance: string (nullable = true)\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "EA_gP3-2IJfU"
      },
      "source": [
        "## Spark Session\n",
        "from pyspark.sql import SparkSession\n",
        "\n",
        "spark = SparkSession.builder\\\n",
        "        .master(\"local\")\\\n",
        "        .appName(\"Colab\")\\\n",
        "        .config('spark.ui.port', '4050')\\\n",
        "        .getOrCreate()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 216
        },
        "id": "S2keLeCZ8EuM",
        "outputId": "4d5fb3e6-1e9d-4a3c-e2a2-9853e66f4360"
      },
      "source": [
        "spark"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "\n",
              "            <div>\n",
              "                <p><b>SparkSession - in-memory</b></p>\n",
              "                \n",
              "        <div>\n",
              "            <p><b>SparkContext</b></p>\n",
              "\n",
              "            <p><a href=\"http://d40cc901f496:4042\">Spark UI</a></p>\n",
              "\n",
              "            <dl>\n",
              "              <dt>Version</dt>\n",
              "                <dd><code>v3.2.0</code></dd>\n",
              "              <dt>Master</dt>\n",
              "                <dd><code>local[*]</code></dd>\n",
              "              <dt>AppName</dt>\n",
              "                <dd><code>pyspark-shell</code></dd>\n",
              "            </dl>\n",
              "        </div>\n",
              "        \n",
              "            </div>\n",
              "        "
            ],
            "text/plain": [
              "<pyspark.sql.session.SparkSession at 0x7ffb8ec9a190>"
            ]
          },
          "metadata": {},
          "execution_count": 13
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "scrolled": false,
        "id": "x6Z1IWFF94YY",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "9d5c225c-863f-49e1-aa8e-bb9da0c955a4"
      },
      "source": [
        "bank = spark.read.csv('drive/My Drive/Colab_data/cours_spark/bank.csv',header=True)\n",
        "#sqlContext = SQLContext(sc) # Initialize SQLContext\n",
        "bank.printSchema()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- age: string (nullable = true)\n",
            " |-- job: string (nullable = true)\n",
            " |-- marital: string (nullable = true)\n",
            " |-- education: string (nullable = true)\n",
            " |-- balance: string (nullable = true)\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "3xxcyM8A94Yg",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "30619ac9-6177-4000-8eff-2767d9efe06e"
      },
      "source": [
        "bank.createOrReplaceTempView(\"bank_data\") # Enregistrement sous forme de table\n",
        "affich_bank = spark.sql(\"SELECT count(*) nbr,job FROM bank_data group by job\") # Utilisation du SQL classique\n",
        "affich_bank.show(4)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---+-------------+\n",
            "|nbr|          job|\n",
            "+---+-------------+\n",
            "|969|   management|\n",
            "|230|      retired|\n",
            "| 38|      unknown|\n",
            "|183|self-employed|\n",
            "+---+-------------+\n",
            "only showing top 4 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "OJtNIy2O94Yi",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 282
        },
        "outputId": "2c512393-9bee-4ef0-84f6-b70c2cd3db56"
      },
      "source": [
        "import pandas as pd\n",
        "import matplotlib.pyplot as plt\n",
        "plt.clf()\n",
        "pdDF = affich_bank.toPandas()\n",
        "pdDF.plot(x='job', y='nbr', kind='pie')#bar, hist"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<matplotlib.axes._subplots.AxesSubplot at 0x7ffb8c86a4d0>"
            ]
          },
          "metadata": {},
          "execution_count": 22
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<Figure size 432x288 with 0 Axes>"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "display_data",
          "data": {
            "image/png": "iVBORw0KGgoAAAANSUhEUgAAAPUAAADnCAYAAADGrxD1AAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4yLjIsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+WH4yJAAAgAElEQVR4nO2dd3wU553/P9+Z2aLeC6qrBkI00TFgYyCOTcBx4oTgkjsnvvh+zjk590TJnRP5Esfc5ZzOnePY6bF9dlyxjDHFgAGDAdMkBAIhCfWC6mql3Z2Z5/fHSlgCSdtmd7bM+/Xal9Fq5nm+mP3s88zzbcQYg4aGRujAqW2AhoaGsmii1tAIMTRRa2iEGJqoNTRCDE3UGhohhiZqDY0QQxO1hkaIoYlaQyPE0EStoRFiaKLW0AgxBH9NdOzYsVRBEJ4HMBvB92UiA6gURfEbCxcu7FDbGA2NqfCbqAVBeD49PX1mSkpKD8dxQRVwLssydXZ2lrS1tT0P4PNq26OhMRX+XDFnp6Sk9AeboAGA4ziWkpLSB8cuQ0MjoPGnqLlgFPQoI7YH22ODRhiifUg1nEJEvyeiDiKqVNsWDef47Zn6akxlFQuVHK9+8/pjrlz397//Pfaxxx7LkWUZX/3qV7t+8pOftClphxoQUTaAPwNIA8AAPMcY+6WCU/wRwG9G5tAIcMJqpRZFEQ8//HDOu+++W1NTU1P12muvJR47dsyotl0KIAJ4lDFWAmAZgAeIqESpwRlj+wB0KzWehm8JK1Hv2bMnKjc311pSUmIzGo3s9ttv7/773/8er7Zd3sIYa2WMfTLy5wEA1QAy1bVKQy3CStSNjY36zMxM2+jPWVlZtubmZr2aNo1CREYi+piIThJRFRE96eE4JgDzARxW0j6N4EG1Z2qNa7ACWMMYMxORDsB+ItrGGDvk6gBEFA3gNQAPMcb6fWWoRmATVqLOzs4etzI3NTWNW7nVhDkqQJpHftSNvFx2AY58EbwG4G+MsdeVt1AjWAir7feqVasG6+vrjWfPntUPDw/T66+/nvilL32pV227RiEinohOAOgAsIMx5tIWmogIwAsAqhljP/OBXS8B+AjADCJqIqJ/UnoODeVQbaV21QWlJDqdDs8888ylW265ZbokSbjrrru6Fi1aNOxvOyaDMSYBKCWieABvENFsxpgrvuEVAP4BwOmRLwUA+D5j7F2F7LpTiXE0/ENYbb8BYNOmTX2bNm3qU9uOqWCM9RLRBwBuAeBU1Iyx/QDI54ZpBAVhtf32ByNb6ONE9I6b96WMrNAgoggANwE46wsbNUKbsFup/cCDcPiJY928bxqAPxERD8eX7SuMMbe+GDQ0AE3UikJEWQDWA3gKwCPu3MsYOwWHf1lDwys0USvLLwB8B0CMmkbM+dMcHYBcACYAOassQ1G/ae+cDiBl5BUDh8tMmOAlAhgA0D/y6gbQBaATQCOA8wDOo7yvy49/JQ030EStEES0AUAHY+wYEd3ojzmri2cKAIrgyPO+8kq7nze0J1Du6HVn9LqjABYpOnl5XC9GBQ7UADgB4DDK+4I+QSbY0UStHCsAfJ6IPgfACCCWiP7KGPuqUhNUF8/k4NiirwWwBsBKAFFXX1fQyo6NFfUAx/li5xAPYPHI61PK4xpvs/6o4iQrqAKwF0Bl/eb1QZtHH4yoJ+ryOEVTL1He59TvvXHjRtOuXbvikpKSxPPnz1cpOT1j7HsAvgcAIyv1Y0oIurp4ZiqAWwF8DsBqAAnO7ilqZoMHx+Ro2YgSvbXDVRhD4mmWdx8AfuStblNZxTYArwN4r37zeou/bAlXwmqlvvfee7sefPDBjq9//et5atsyFdXFM0vgqIV2G4AlcNP1mN8+fmGUgSQJkPhPheYzuhFzXgZXOuatRAB3j7yGTGUV2+EQ+Nb6zesDJpovlAgrUa9bt8587tw5n2dlMcb2ANjjzj3VxTNj4fjg/z8A87yZP737qi05EdfF8x1pkpTqzbiucFSeMZVQIwB8YeRlN5VV7IajAMNr9ZvX231tW7gQVqIORKqLZy6GQ8h3YILnY0+IHUL61e+1Cny3P0S9TVoS7eKlOgA3j7zaTGUVzwP4bf3m9U0+My5M0EStAlvu360D8BUw+f7rhYg5OnEoTsnxORnTdCIbtgt0parLJZ0wUGr1bUIaY2C75dIiD25NB/DvAL5nKqvYCmALgF3aAZtnaGGifmTL/bsjtty/+0EAdQD+CuJW1ubddsLZfe5CAJfdiXErXqOgsyo9z9VYoavtR7Q3X1A8HFvzHQCqTGUVd5jKKrSYdjfRRO0HRsT8CBxi/gXGlBpqnXZdHgPJSs9Z0MrGBYc06gRR6Tmu5hzLblVwuJkAXgJw0lRWcZuC44Y8Krq0nLuglObWW2/NO3ToUExPT4+QlpY2t6ysrOXhhx/2WWTUlvt38wDuA/BD4NrnXABgnJDTlr7042lth5YoOff0ZmbdseDTn1sE3udf4DulBb6YYw6AN01lFUcAPFG/ef12H8wRUoTVM/XWrVvr/DXXz+66b60h9s5fAXBa1bM27/P8tDaXqxa5RG4HG+e+6uR5n5/6vycv8WWxw8UA3jOVVXwI4JH6zeuP+nCuoEbbfivMM5s25D+zacNbTGrdKdnODrlyj00fu8AclaHoF05KH8Y92/ZxvCIn65MhM+o6z7JMvpxjhOsBHDaVVfzSVFbh6kl7WKGJWiGe2bRBeGbThicAVGGkiZ59cHscY7Lk9GYiOle06ZKS9kRYx5cIHuJI0RP2q2lB0kVfjn8VHIB/BVCtPW9fiyZqBXhm04ZiOGp4/Qcccd8jSIXi0IcHXBmjL65ggcgbBpSyiQMSoy2sZ/RnEUhWauyJOCDNUiP8MwuO5+03TGUVWp3zETRRe8EzmzbQM5s2PATgE0ySBSVZj81m8rDz8klEMRfzbv1ESftMHaxlzPiRA0Q+Kxv8rrw0yVdju8AX4Fi1v6GiDQGDJmoPeWbThhwAuwD8HI7wx8lItA9udckX3ZxxfQ5zoyywM4qa0TP25zZBuKzU2GNhDLZDcoknQSdKEgPgd6ayir+ayip8en4Q6Gii9oBnNm34CoDTcGRNOUUWG5fLUqfTgzDGCXkdqQsVc/UVtrJxz/PNOsEnBRcHEFFjhT5QepLdDeCoqaxijtqGqIVqLq05f5qjaOrl6XtOOxXDhQsXdHfffXdeV1eXjohwzz33dD7xxBMd7szzzKYNTwMoc9M8nW3gjS5j/D87zQ67kP9FltahjK6zuphh7M+XBMEnz70n5QKf7AC8oBiOE/J/rd+8/nm1jfE3YbVSj9T9bqqtra06cuRI9QsvvJDqatdLWZI426A5Fe4L2gEzL5ZsZ536Vq2G+IWDEWkNHs1xFYkDGJdHfUkn+CQTaru82OD8Kr8TAcd2/M+msopItY3xJ2El6tzcXPvKlSstAJCQkCAXFBQMXbp0yWlQhmizGi43N86URXGqZ2en2Ae3Jzh1cRFxNdM31Xszzyh6EVlwtPMBADQLgk8SJHZIC/N9Ma5C/AOA3aayCp+e/gcSYSXqsZw7d05/5syZyFWrVpmnum540Bx7ublppmS3K/DMKBWIQ/ucurh64qeXSpx+0NvZCIhM7cOVeOx2gdd5O+bV2Bnf2I5En6d0eslSAAdMZRUBXRxDKcJS1H19fdztt99esHnz5sbExMRJkyks/X2Jfe1thUyWFasYIlk/mc3koakrfhDF1Zk+p4h7K7+NtY/+uZvjFT/MusimKRo040OmA/jIVFaxwOmVQU7YidpqtdL69esLNm7c2H3PPfdMKq7B3p6k/s6OPMaY0ql/ifbBrSedXdSUeWOGEpMVNrMrAS1mjtxtMOCUPXKp4hlmPiQNwF5TWcVnvR2IiG4honNEdIGIPDtn8RFhJWpZlnHHHXfkTp8+fbi8vLx9suvMPd0pA5e7TD6zQ2xaIYudU4ZVyryuoCO51OvVuqDtU7+3LwoQbpOWpCk9po+JBvCOqazibk8HGOmisgXAOjgSdu4kIqeJO/5CNZeWKy4opdmxY0f0m2++mVRUVDRUXFxcAgBPPvlk89iGeebuy2nmnu4sH5si2MxvXDbG//OUB0wXCm+3p3Z5V0NhWje7EojBgEQ7YNc5Sgl5DWPoP8nyC5UYy8/oAPzJVFZhq9+8/lUP7l8C4AJj7CIAENHLcBSJPKOgjR4TVqmXN998s5kxNumXycDlzmmDvb2KbHudMuLi4vXFkxbZHzYkLrZEJDdFDnV5/CUTa8Gnh1hE1CnwXRmiNM3T8cbShbgLDFywPqPyAP5mKqsw129ev83NezPh6FYyShMch3EBQVhtv6fC3H051W+CHmHExTV5RRIirqZo0wVv5uBlZAoSu1KcrFkQeqa63h0+losVS0BRCR2A10xlFavUNkRJNFEDGOrvSzD3dGf7f2apQBzad3CqK7oTZs6TOJ1LedkTQQCf1fnpqtIkCFO68NzhXWlpKOQzRwDYaiqrcKfyTDOAsZ+XrJH3AoKwF7XVMhjd39Wpmv/SqYuLKKEh52avqnwUtLIrYZyXdIIiBQgZg7xHnjddibECgBgA29yIFz8CoIiI8ohID0d557d9Zp2bhLWo7Varsbe9rdAHbit3SLQNbj011QWXstd6dcJc1MKurPSNgqCIC2oY+guDiFC1u6fCJMIh7AlryY2FMSYC+BaA7XD0In+FMaZoGydvCFtRS6Jd6G1rKVIysMRTmNi0fCoXl8zrp3clzXbq254MUwe78u/cKgiKfIGdYbmh2N0yE46iC05j2Rlj7zLGpjPGChhjT/nBNpcJS1EzWaae1pZCSRR9XozPRQSb+fXuqS44X/hljzOsUns/rVfWxSsTVbZDWhiqnpOlAJ5T2whvUO0fprp4pqKplzPPVjv1e1ssFlq6dGmxaLfrZUkSNtxyMx5/6EElzfAcNrhIslYf5Q0zJ3RxDRmTFw8ZElsjrN1uu6Mirbhyqt/Hc4oUENguL1bhYNFv/KOprOJI/eb1v1HbEE8Iq5XaaDSyne9ta/ugYquwc+tb+ODDD3HsuOINMjzGbnl/chcXkXC+aOM5T8blGJIjh1kfAAwTxXthIgBAYtRRx6aFsqgB4BlTWcUytY3whLAStSyKetisuQBgF0XY7SKIAqmri1QgDu2d1MXVlTR7tkyenV6b2h0uF0mBAoRNLMWflUPVQg/g1WBM2QwbUTPGqLejLV+02/nP3Pp5zFl6HVatXIEFpV51jVUcyXp8DpOHJg4QIS65IeczRzwZt6iFOdxmRIZejvOqL/SH8hyf9+UKELIA/I/aRrhL2Ih6oKszU7Rao3iex86tb+OT/ftw/OQpnK2pUdu0q0mwDb59erJfXsq+yaOqnYWt7ErVk1aB96r80DZ5adCtXl6w0VRWsVFtI9whLERttViiLP1943y9cbGxWLFsKT7Y96FaZk0KE5sndXFJgnFmd0LxpKKfjOxOdiWJo0kQPC4VzBiGP5aL1a4c6m+2mMoqUtQ2wlVCXtSMMerv6jABQNflbvT1Oz7PQ8PD2HvgAArzA7ISz5QurprCjW7HXCcO4MoK36jzvABhH6Jq7BACxRXoL1IQRNtw1VxarriglMDcfTlttBRRR2cHHnz8u5BkGbIs4/OfW4eb1rhU5df/TOHiskSmLR7Wx7cbbb0uR5oZ7LiS6XVJ0Hnc1va4XKhYQkiQ8WVTWcVGD1M1/UqoBhAAcBQMtPR9mnlVUlyMHVvfUtMkt7Bb3k/g9DNEIm78vxOR7nzRl6vnVD3vsqgJiErqY22X4yi9Wed5EN178mKvii8GOf9jKqvYU795fafahkxFSG+/+zs7clSO6/aSyV1cncnzZsnE2yb63WTkt7FWAOjgBY+/zHcGduVQX5MM4MdqG+GMkBW1pa8v0TY8rHhNLn8zqYuLuJTGrNVuubemtzAzAPR4GFVmY0L9ZcSF08n3RPyTqawiYEoXTURIiprJMpl7Lvu6JJG/SLANvj1hFld97i1uRYflt0IGgEHiPMquusAyAiZnWEV4AP+lthFTEZKiHuztSZUlSfEa12rBxOYVsthRe/X7khAxqyeuyOW6WBndLBIAbORZVNlueX4wVQ71JetNZRUBesIaoKI+efLknNOnT5dUVlaWVFZWznTnXlmWOUt/r9Oc2CBDsJnfmPDUuaboKy6fRscOwuFrJYqzEobdNWKbtMSv5Z4CnP82lVUE5HlNwJ5+FxcX1+h07rteBnu6U2VJDti/l8dM4uIajJq22KqL6TTYB5wGRwgysniJ2SWedO28cDlHFF1u1M4Y+qqYKZwPya5mARwdNv+qtiFXo9qHf8v9u6dMvdyPD90Kyn7g2TXHZEniLf19TldpSZJwyxduR3p6Gv7yu+BJnbVb3k+8xsVFpL9Q+KWqWdV/vNHZ/QQIGZdR15iKvBaB73FH1O1IOA/QpJVPw5QfmcoqXqrfvH7q/mh+JiC3355i7ulOc6WSye/++CcUFRb4wySFkfLFoT3X9OJqT104QybOpV1NQSvrBIBLOp1bvboOyTO97u0VgpgAfEltI64mZEQtSxI/NNDvNBijpbUNu/bswV1fCaoY/StI1hPzrnFxETetOeMGl9xb00fqlTUK7qVwbpOWxjm/Kix5RG0DriZkRG3p601msuz07/ODHz+Ff//ud8BR0P7V4yfK4qozfc4l37Op3VGvrFHnegFCxiB+KM8JtyQOV1lqKqtYrrYRYwnaT/bVDA30O22numP3B0hOSsK82bP9YZLPcGRxjXdxibqouX2xeU4ro6T2IhYA2gTe5VhRCwwXLDAqUgYpRHlUbQPGEjKidqWI4MfHjuH9XbuweNVq3P/Qw9j/0SE88Mhj/jBPaSZ0cZ0r2tTh7MboYaQDQBfPO62YOUoVMzkdN8z5gqmsImA8AyEjalf4t8cfwycHPsSRvR/g2V/8HCuvW4YtP/tvtc3yDDa4SLKeGfccbY7OWmzTRU1ZlZRjSIuwsoEBzvWosu3S4tBzESoLB+AhtY0YRbV/rAeeXaNI6qVos+m7Ghtc7awQUtgtO5I5ffGnLi4iY23+Fw/NPPfXG6e6L7cDzTVZrhcgfF9eZPLK0PDgH0xlFY/Vb17vVpKNLwj6ldrS3+dRyOPyZUuDykc9MVKeOLRnXBZXW9qSIgaa0m9a2MK6ZSCF4dPe1ZMhMq61kaVqkWTOiYejX7XqBL2orYNmj2p2hQqS9cRcJluubLkZx2e2TFsxpXursIXZQCR0c5zTWmWXWGq9AmaGC3epbQAQ5KK2DQ1FBlCXDbWItw2+XTn2jYt5G6Y8BMvpZHoAaHGhre0+ea7d2TUaV7jVVFaheifQoBb18OBAgto2BAJMbBnn4rLrY+b3R2efn+z6pH4kAECTznkBwnelsKoc6i0RAL6gthFBLWqrxeJ1t4kQQbCZXx9Xy/vc9DsmbWBntCMTAC4JwpSZWozBcoxND5V2tf7iTrUNCFpR263DxtGCghoAmGWhZK268iw9EJO70C5ETli0n4DYhAHW0aQTptxa9yD6vARec2e5x2dNZRWqnvMEraiHzWZt630VdsvOZMYkh1CJImvzb5u0/W1+G2ttdtLW9pg8PVwrh3qDAOAmtQ1QhWc2bVC06+XdP/m5S9ctXrUa0VFR4HkOPC9g+5uvK2mGykh54tCefbrItTcAQGv6dfkzal6WCeyaL++iFtb/kYmf8pBxm7RECw31jLUAXlZr8rDcWv39r39GUmKi2mb4BMl6cq5gvK6buMhExvHZrelLP85oO7Tk6usKWiFv47jIycZhDGy3PL/Qt9aGLGvUnDxot98akxJvM3/q4rqYf9uEiRsZl1mEheMmTae0QXexFzHaI45n5JvKKnLVmjzsRE1EuONr9+Kzt30Rf3lZtR2ST2FSywpZbL8AADZdzAJzVMY1fbniB5FiByY90KlhWa2+tDEMUG21DjtRv/Xyi9jx9pt48ffP449//Rs++tijzrCBDm8zv9EHACCic9PvaLr6AkFCFscQaSGasKLJLnm+j00MeTRR+4tp6Y4SZslJSVh30004cWrCktrBD7MsFEdcXH2x+QtE3jgu0IQA3bRuNLULfNdEt2+Tlrhcv0xjQjRR+wOLxQKz2Xzlz3v3H8CMotAt6CGOuriIoi/m3Xr86t8XtLKOidrayoy6z7GcPP9YGbJkmMoqpqkxsWqn34/+3zsepV4OXO5KH+zt8WgV6ezqwr3/8gAAQBQlfPHzt2LNqhs8GSpIkPLEoT17dZFrVzVnrMwtuvAqI+CKb7qohVkapwnXFCxsQ2ItgNB0D/iX2QD8fjYRdC4t0WbzuOtibk4Odr2zVUlzAh7JerJUMF7XDS7S1J666Gh6x9ErZX7z2hh3doKosoPyLK1yqDLMArDD35MG3fZbErXQUDeJG3Vx1RZ8cVz+dFovopuEawsQvist0VZpZVClGF4Qilp0ubaWhoNRF5dVH7doMDKtYfT96CFMaxPGt7VlDPaD8qzQPWjwL5qonSGJdsGVYv0a1+BwcRHRuaJN9aNvcgxpNjs37jNgRkTNMAzh3FheSUrU6LcVVKIWbTZt6+0pDhfXx73x00slTj8IAARQegfG1dQ6LedN6OLS8IgYADn+nlQTdRghWnakMMiRdabPXfE8ZLZh3DP1dnmx9nijLH7v7xRUopbsdu0D5xVynji052BT5o1XXIIZHcRJwJVChe9LWuVQhXHajVRpVHNpNZV9qGjqpXCfay2p+/r78ej3/g1nz9eAiPDzp5/GogXhExIpWU+WSsbrxI6U+cdTO4/Pz+5kui6ev5wmSaki45pbkaRFkimL084xShNUK7USPPGjH2P1Dddj//vbsWvr20Ha/dIr4mzmt6rOF9xuB4DkfsS3Cnw3ANSx9HpVLQtNNFH7kv6BARw6cvRKx0u9Xo+42FiVrfI/TGpdMcTb4i0RKY0RNmQ1CYIZAPbIpQHVZzlECDxRExFPRH/zhzG+5lJjI5ISE/DQd8tw06234dHvfR8Wi0Vts9SAtw2+aa4p+spFAuJsFn4QALZJS5y2AtZwG78/UzsVNWNMApBLREFfX1uUJJyuOoN77roLO7a+hYjISPz6t8HepcNDmGVBV8QwSZzeEtPBDTMG8wlWqFU6UZ7AW6lHuAjgABE9QUSPjL58aZgvyEhPx7T0dCwonQcA2HDLzThdVaWyVephG96XU5+95khUBy9dRux5GZwW2KM8fg+5dVXUtQDeGbk+ZswrqEhNSUHGtHRcuOgoBLL/4EeYHtaLk2xqiLdaIi7zuiPyDKeF/TU8QufvCV1yaTHGngQAIop1/MgGvJ04a/P1bqVeyrLEddRd9Nr39NQPnsADjzwGu92OnOws/OI/N3s7ZFAzjEvLuyKnf/CuNF+rR+Yb/O42dmlCIloE4A8YWZ2JqA/AvYwxRdrRumQDyGmHRleYXVISYmWBvSbuQiRF75HnaUkcvsHvjzSufov8HsC/MMY+BAAiWgmHyOf6yrBrIGVEreGAI344M7KoShed1tFdcMpWnN62t6C5hS3tPWWYg7qoLOpM00NMwpiiChruI4P6Af/2RHBV1NKooAGAMbafiK6pmOFLiLTPlpewJEPmufyYuW0ZkQWxdp5L2qk/bblMA59dXHTi2B3GM3nfTnlO/KO8KZFvsZzg6wcuJ1u6oxZzNZ0ruMq+hVwNb6K25EhYTUTQYvBdhAPz+1nFlKImogUjf9xLRL8F8BIcjco3AdjjW9MmtIcxxjR1u0ikENeSFz27NidqJh+jS5xORMVW2Kft0VWdaOQuzwQhNyKir8FgsCwywsI9jqdO/yf3RLKUHbVUyo5Ci5hqfqcxu/69hmWRsEqlBOg4yNIMarx4HXem/TrujG0WVx+Zip4MgWQtvHRi/B7Q42ylfuaqn38w8l+CQ9waAYRA+oHsqOLqvJjZQ4mGaTk8CXkAMgBAgmw7JJzbW803zwFh1eg9hUWH64mQCwBzcXLOTXhv3w6scxRuE7hoKS9mhZQXA9ikHqHBXMk3WmLO2HPnVku5+b+X1l2ZOwaDfYu4moblXFXvYu4c5VNLQgyGTERQvV+zyvh1Rws4ETVjbDUAEJERwJcAmMbc439RExiY9ow3CoGkVGPumfyYud3pEXkJOs5QQkTjWuwwMHaKv3TwqFCby4itGvs7nrcNxMW1Lxj73tfw/A2n2byP2ijjunGT6fkEsSjuerEoDhiW2oW6gbN8iyWZRDYLAAYQFfeBPH/uB+PqhTOWT62XlnFnWpdzZ4bn0EXDNLo8TQcpmyhsQpTNrlxERL8HsAFAB2Ns9sh7GwGUA5gJYAlj7KgrY7n6TP0mgF4AnwAY7Wnsd1ETcRKDFC4fhgmJ1SXV58XMvZQdOd0QKcQVE9Gcya69yLUf26c7EyWSvGKi35tMJz6hMav2KD/G43PuZ3+4KJI+f8KBjXyaODM+TZwZDxoUG4WL/bVc23AmyeyqE3Siiywj56KUkfOi9Jkr70ZieHAeV1u/gqvsXsKdZUXUHBcPs4kIk7YBCmJcLTrxRwC/AfDnMe9VArgdwG/dmdBVUWcxxm5xZ2BnlJeXK5p6ef8/3O30mgsXL+L+Bx+68nPDpUY8/tCD+Oevf01JUxRFz0V050aXnDNFzxLj9al5HPEmOHZMk9JOvWd36k9bhsg2xf9jJqdPOz/hOBEYjn4S32//N/bTQRBN2fmSRQnZ9jmJ2ZgDUL/tglA70MR1DucRw6S9pCwwRn0kz5r1kTxr3PvZ1NGylKtuXs5VWeZRrT6LulL1sJuI/O8WUpDLrlzEGNtHRKar3qsG3D8kdlXUB4loDmPstFujBxiF+fnYufVtAIAkSZi/4nqs+6yqrYSvgQNvnRZZcCY/Zk5fijEnTSDdDCK6zvmdQB9ZmnboTjb0kmU5aOrHlLS02qMcJ1/TDXMUE+oK7sKfD76Ie5a7ajuL1Rfa5ycVAgB1W88ItQOdXLd1OgEuFbVvZKkZjVJqxt+lTzcPetitc+ji+eVc1eVlXLU4g2uMTcRANkcs2VW7VMYlUSuJq6JeCeBrRFQHwIqRgzLGmP/81Arz4cGPYMrJQXam6oe2LFE/7Xx+zNzWjMjCKCMfNW2u+CwAABmvSURBVIuI3IqcG4Lt8m5dZVUr17MMhCxX7jHlHXf6b78eby8/xhbvO0clbnc8YImGEnuiAWBM5jqHTwgXBwaoz15CUzTlmwgbdIZjbEbxMWkGfj3mHDkV3Z1LuHONy7mqgQXceSGH2lMiYDMRIdASj9r8PaGrol7n/JLg4q2KCnxhw3pV5o7gY9ryYmZfyIkqoVhd4nQibjqA6e6OY4dkOaA7+/EFrm0BCC4LLzr68nm9fniB8yuB76P8uvvZHyqHKMqzcrdEnJwaUWpLjQBkJnJtQ0eFugErmcW55EX+QAcSU96Rr0t5R/50E8NDEmdSw4Xl3JmOZdwZewnXEJWCviyeZNfK4viGZn9P6Grsd4Pzq4IHm82G7bt24fuPPeqX+QTSmbOiZlTnRc+2JBkys3hOKADg8QdNBpM+ES4ePMnXFzHCje7eX1h0uA2AS2GhAiTd03g06WH2P5cZcW6tstfAkSBnRC6yZUQCEhvmmwcP8fVm0JBUSvA+oEUCL1Sy/MJKKb/wOWnDlffjMdCziDt3aQVX1beIq+HyqDUxCsMmIkR6O6cLtPhhjnEEXdsdJdi9dx/mlMxCSrJvHssIJKUYs8/mx8zrTI/IS9BzxhIiWqzE2Gf55sMfCTXJEsnXe3K/IAx3R0dfXuT8yk9JQee0b+Nnn/yKPZoAImW8DzwZpZzoZVJONCDKA/ylwWPCJbMBVrmUFP5c9iImYae8KGGn/OlfmyDL06mpbhlX3X4dV2WdzdVHpKEnQ4CUSU7OI9zkmjbCE0FELwG4EUAyETUB+CGAbgC/hqPQQgURnWCM3exsrLAU9ZvvvIMv3rrB+YVuEKNLbMiLntOQHVWsjxLiZhLRLOd3uU4Td/n0bl0ls5G41Jtx8guOnSIPVvel+GjBcny49yBuuMYF5jUCFyPlx6yQ8mMAm9Qt1Jsr+SZLLOzyXPJRyS0GjjvHcvLOSTl5f5I+1Uk0LP0LuPMNK7iq3sXcWRRQS3wsLCYijx4VJDjSlp3bw9idk/zqDXcnJcb8424+efJk/bx587wqFM8Yo4662vnehIpaLBYsuuFGHPpgF2Jj3Pt3amhuwf4tPwUA6Dljb05USbUpZpaYoE8zccRne2rTVHSTue593cl2Mze8zPvRZHHl9S92EjGPWqwygP0rfnu0m5IV2XU4ZUhsFerMNXyLJYUkVuKXOSeEMRO1NS/lqluWc1VD8+iiIYMup+kg5joJoqlBed8Mv5k5QlCJGgC6GhtKvOl86SlExJraOu3iS9UHUyNyUgXSF5NSW9EJGIS1Y6f+1NlO6l8OUmZHlZFZfbCg4KjLLqqJMCO695t4wSyT4NIpu1KQ2d4gXByo59qHMklGQFS2iIDVMpdq65fzVd1LubPydGqKjcdALkcYzU1/A+V9t/vbrqDbfgs6vcVfotbrjFaDLsoukI4jCcYODOjToopu9OWcNogDe3VnjjVwnYvdOdF2hdzck15Xq4mGOf7f8cPW/2A/toLIb80VWLQu1z43MRcAqM92XqgdaOG6hvOJwSc7JFcYgiHyMCspOSyO30RkoKttCXe2aQbXuP+bKtgVfKLW64fgo+7JPK8TjfqoYR1nACdzBsjMAAl++eDKkO2HhQsHq/jGWfDgmdcZcXFtZwTBPmlIqTvMwNmZt+G1D9/Clz06rPMWFqcvsi9IKgIA7vJwFV870MX12IoJCIhqqC1ITn9TXpkOGU9ronYBwWBQrKYvx3GyQR81rOcjZJ7xOkjMABnRju5S/nksYWCskm88dES4kCFflXChJIWFH/cqOd5X8NL1x9nC/Zcob6WS47qLnGScJScZHUEuHcPHhYsDZuq3zyYgEMozHVdj0qATtc5gHPLmfoM+ctigi7TzEHgSEQGGSEdynP+Tzuq5juN7dWcMdpJcCgP1FL1+sC0isk/xw61yfH/h/ewP52xk9Pth0DUQcXJaxHxbWgQgMzvXajki1JltNCjOI6iS/tlbv3l9nQrzBp+oeUEQeUGwSaLoUjigTtDbDfooq470RBJnBGNGiOpW7uigvpqd+lP9FrK55S/2lILCI2c9cWM5wwBbxFN43Pg4+1UfiAInw4ojnZwZtdiWGQVIbIhvGvyIbzATDUnzCf55nAJwwE/zXEPQiRoAdAbjgCSaJ4xu4jheMhqih/WcgXGM10NiekijZVrVrevQT0PNO3Qn63to8Dr4KZ+YSLImJTV6FuLpAhloyf0Gnj38PLt/SUDWnOIpQsqNvk7KjQbsch9/yXxUuDRogE35IJer2OvKRZPkUScC+D84svHqAXyFMeZyoTPVRL1rd4GiqZfLS44N8BAEiCwCMqImey7+5e9+gz+8/GcQCLOLS/C7Z/4XRqNvF+5h2Ho+0FWdaua6l4Hg1wyS7OzKI0Tw6XPvauxcegRL957EAp+dCSiCjouTCmJXSAWxgFXqEurNZ/imwXiIbA4pX2Bxj4vX/RHX5lGXAdjFGNtMRGUjP3/X1YlDpuAAL/IxENmUrq7m1hZs+cNv8dE7e3F812FIsoxX3n7NZzaJkIb3CWf2/NXwIdfMd68C+W3rd4Ws7Cq/9HJ6DE+vjGb9J/wxlyIY+GRxRtwN1rUZc203pLWKWZF7GU9nFRp9tKCIUxhj++AIBx3LbQD+NPLnPwH4gjuTB+X22xskUcTQ8BB0Oh0sQxZMS1M+gYeBycf5uoPHhbp8TxIulCIxqfEkz0vz/DEXB5nfjIczv82ea2PEq5kV5TYsQsgQZyVkiLMSQAP2OuHiQAPXMZRDMiau/OKcXfWb13tTcDCNMdY68uc2uOmqCytRZ07LwEP/79soXDYLEUYjPnPDGty0aq2ic9RwrUcO6M7GSySr6uoBgIKCI8POr1KOBPSmPIanT/2U/VsyiILys8VidHn2eYl5AEC9tnNCbX8rd9laSMy1PPUR3lfMHsYYuVnzPmS2367Q09uDd95/F+cOnkb90RoMWix48fWXFRm7heuu+oth34l9+jOLJZJV73ZhjOhvNBgG/ROjPYZSHJ+7Fu+rdvKrJCxeP8O+MPlG600ZmbaFSZVygn4vAzpduHWbl1O3E9E0ABj5b4c7N4eVqHfv3wNTdi5SkpKh0+nwhXW34qOjh70as4fM9a/oDx56V398lpXspQqZ6jWFhYcvqlWx8148tyqNtX6kxtw+gYjkZONs25KUVdbPZiTa5iUel2N1HzKgb4KrD9dvXt/o5YxvA7hn5M/3AHjLnZuDcovkKdmZWTh8/AgsQxZEGCPwwYG9WDDXs557Flg7d+pPV3dQ33LQ1IUA/Q3P283x8W2qfsE8hcdn38/+UCeSLk9NOxSHiJfTI+bb0iMAmdn4FsvHfL1ZHAlyiQLwqnvDTZhHvRnAK0T0TwAaAHzFnTFVE/XaNbVeN9eTLfY4sXvY5YydJfMX4/bP3Yal666HwAsonT0X37jr627NaYdo3qerPlbHdSxUOuFCKXJyT35CKtsWgaGYcnyv/d9dqEgatHCkl7KilkhZUYAoW/imwQNCw+Ar7gwxRR61x4c9QZd6ORbGGOytg/MgM398ObHK5nO2t999px8Ev7iJPIOxFStfrOc4OSBWyHdw24GX6B8nrDseguxvW12qSpLLWIL6mZqIwEUIPi/BatPLgz3CoN0q2w2BLWggNbXuWKAIGgA24K0V09nZfWrb4Sf+qrYBQACKWhRFvqamJv/UqVOzTp8+Pau/v3/KrRsXpVN09R9ni44N9eqGhvvFwShJlgOt9OyE5OUfC7hQzX/DD5dFMEuV2nb4mGEAbm29fUXAHZTV19dnx8bG9k+fPv2iLMsky/KUXzycnh8mHTfI7LJiz20Sz6yDvFWyiXZ/VJtUjKio7lq9fljR8FslECDqn8YjCYpUJA1cXmxbXerfRtSTEFArtSiK/ODgYExaWloXAHAcxwRBcBqZw0Xp3PLjTYbMMbtZbzP3MLMh2AQNAIVFh/1eY9pVUtCZ8QB+UQ/GZLVt8RG/UtuAUQJK1MPDw3pBEMTa2lpTZWVlSW1tba4kOW+Ix0XpesCRxy1DGUGyGOwD3TDzw6I1KFuvCoK1Nyamyy+pnJ5yHQ4sXIYDH6pthw/Y17a69KTaRowSUKJmjNHQ0FBkampq5+zZs89wHCc3Nzc7jSMmIsZFCm4/WzMCG9KL5m7ODIt9OAYB9v/DHfLyPjnhp+L0XvEt/PyGBNbtUkvWICJgVmlAxWfq9A9OTPLsJwDHLhaPe6u222lJ29br556SzfY0OEmh+/UL/4Pfv/gniEyS77r7LnbfffcF5co8HllKS69VPTTVFQigp/FIwb+wF5pk4v1akdRHXIKj1XPAELQr09WQwNm5COHqFLZxVJ09g+df/AOreK9ieMfOHdzOnTv5ujpVKs4oyrSMmo+JmOqd/lwlBgMJ30d5Pxizqm2LAvyibXWpNxlZihMyogYAPtbQgknKm4gCGz568ZS1dMF80hsMRkEQsGzZMmzb5m3svfrk5p4MuoitmThTcive+FhtO7ykGcD/qm3E1YSUqEnH2a5+tpZ4ZhvQWwd7JbNx+ozphsOHD6O7uxtDQ0PYvXs3Wlr83r9MUWJjO6p1OltQthS+A3+7Pps17FfbDi94qm11qV/TW10h4PzU3sLHGlrlITFZJkhDOvvwkH04CiL0IKCoqAgPPPAA7rrrLkRGRmLWrFnguOD+XissPOz3puZK8iTKFt7P/lhjI4PbrXxVpg7A82obMRHB/YmeABI4uxjNtfSQmYbsw9G46uDszjvvxHvvvYfXX38dcXFxyM/3tLiF+uj0ls7IqN4latvhDQbYIn6Mxw1gbKI0xkDmybbVpXa1jZiIkBM1ABiijZMmsnd1OXbnzc3N2LZtG774xS/6zS6lKSg4WkWEoAhfnYpMNOfei+eq1bbDDc4iQOK8J0K17Xfb6lKvUy8ng+d5KTo6umVgYOCaPkv33Xcfenp6IAgCnnrqKcTFBU65ancgkmzJyQ2KtstVk7V4f9kRtmTvaZof2BVJHTwUaCfeYwm5Z+pRoqOjOy0WS6okSeMqeL7xhtvtfgOSrKwzH/u69K+/+Q5+svKb7IUTZooNmAoyE/BK2+rS7WobMRUhuf0GHFFmcXFx9Wrb4SuycypDLjHCUZH0kQxicrvatkzCAICH1TbCGSEragAwGo3miIgIVwrFBRUJCc2neF6cqbYdviABPamP4uk2MOZxLL8PeaJtdWnA+0BDWtQAEBcX18RxnE1tO5SkoPCIj5r5Bgbz8cm81dgZaBVJj8PRSSPgCXlRcxwnx8XFNahth1IYjQPNRuNAULuxXOEbeHZVKms7pLYdI4gA7gvkw7GxhLyoASAiIqLfaDQGdZDGKAWFH18gAq+2Hf7gKTxeIjB7IATnP+lLb43ShIWoASAuLq6R47iADBZwFY4TLQkJLX5poxMIRMIS+0N8XwRjFhXNOAjgaRXndxvVXFqmsgpFy+7Ub14/5Tcpz/PSt7/9bcsHH3wQl5ycjN27dwMAenp68M1vfhONjY3Izs7Gs88+i/j4eCVNU4yc3FNH1S7962/ycbFoE/524P/wVTUqkvYCuCtYtt2jhM1KDQD33ntv2yuvvNI29r0tW7Zg5cqVOHDgAFauXIktW7aoZZ4TGMvMrA6a9Eol+TzeWFHIzqlRMeWf2laXOj2PIaIHiaiSiKqI6CF/GDYVYSXqdevWmTMzMzsZY1e+ebdv346NGzcCADZu3Ij33ntPNfumIjml4ROOkwvUtkMtnsAPlhqZ5Ywfp/xV2+rS151dRESzAdwHYAmAeQA2EJHLDSZ8QViJehRZlu08zw8DjljwtDRHp9DU1NQrseGBRn7+0VAt2OcSjoqkj8YRk6cshKEQ2wE84uK1MwEcZoxZmMO3vhfA7T6zzAXCUtQAkJiYeIGIxj0rERGIAq5sNiIje+v0+qGALiroD1LRkfkv+OVFH1ckPQtgkxvP0ZUArieiJCKKBPA5ANfkHPiTsBW1TqezxsXF1SUnJ6O93RGV2N7ejqSkwIu+LCw61Eg0de21cGE59i9aio989XzdDWBD2+pSl9NAGWPVAP4Tjp7U7wE4AUDVg7WwFTUAREZG9n3mM5/pe/VVR6PCV199FTfffLPKVo2H5219sbGdC9S2I5D4Fn52fTzrUboiqR3Al9pWl9a6eyNj7AXG2ELG2A0AegDUKGybWwR1gzx3ufXWW/MOHToU09PTIyQlJYllZWUtd955Z8/tt98+s7W11ZCVlYVnn30WCQkJE97f0NCA7dv9m6BTWHho77SM88GQjuhX+hHT/QBeGJKJV8IjIAP4Wtvq0r94cjMRpTLGOogoB44VexljrFcBuzwiZFMvJ2Lr1q0TRicdPHiwsre3N9NisTitMe5fmJyWfiF4S7P4kFgMJH4PT555ij2ZAiJvCkUwAPd7KugRXiOiJDhW+wfUFDQQ5tvvscTHxzcbjcaAOvpOTz9/hOOYqocugUwJqkrW463DXg7zYNvq0t95MwBj7HrGWAljbB5jbJeX9niNJuoxJCQkNBgMhoBocgYAprwTBudXhTd34S/XZ7FLnmZ0fadtdemvFTUoANBEPQYiQmJi4sVASP6Iiek8p9NZA7kCSMDwHyibr2PW827e9oO21aU/9YlBKqOJ+iqICAkJCfVqF1coLDoccsUdfIUB1sgf4bsCGOt34XIZwLfaVpf+yNd2qYUm6gkYEfalqKioVjXm1+mGuqKieharMXewko3GvK/jd87CSK0AvtK2ujRQA/wVQRP1FMTFxbXExsb6vcBCfsHRSiJoz9Nu8hlsXzabndw7ya97Adzctrr0NX/apAbqubTK4xRNvUR5n9Mk9o0bN5p27doVn5iYyL355pvDAFBRUWF47rnnpLq6Ot2ePXuqb7jhhnG5u9HR0V0cx9n7+vry4YcvQSLZnpLSEJL1x/zBd/DjFd9kvz85SDFj886bAdzStrq0Ui27/ElYrdT33ntv19tvv11DRNbZs2efmTVr1pmioiL55Zdfrlu0aJF5svsiIyP7kpOTq/1RZCEjs/pjIpbm63lCFR6ysBmPpBOTO0beOghgcbgIGggzUa9bt86ckpJypUplb29vbHFxsXXx4sUDzu7V6XTDRqOxFcDLvrQxJ+d0YFZoCCIS0Z32CDa3gLEtAG5sW12qytmIWoSVqK+mu7s7MSEhwR33FSsvL78TwL/CET2kKPHxLZWCYA+ZrhsqYl6AYz9tWzP/W4Ha78qXhK2oZVmm/v7+uKSkJLeDTcrLy38NYBUcz2qKUVB4xBWXjMbUnAKwaO2a2hfVNkQtwlbUPT09cRERERa9Xu9R0fjy8vKPAMwF4E3M8BUMBnNrRER/yJf+9SEigB/DIehzahujJmGV0DGWka23V1U0ysvLuwH8Y3l5+YsAfgsgx9OxCgo/PkeEad7YE8ZUAvja2jW1QVPG15eEa+qlLikpCd/97ncvJScn2x9//PGcnp4eISYmRpo5c6Zl//79E4Ycnjx5MnnevHmmiX5XXl4eA2AzgG8C7hU04DhxaPmKl4aIkOju3ynMkQD8F4DytWtqQ6oLizeElai9ZSpRj1JeXr4SwPMAZrg6bm7uiQ9zck9f76V54cZOAA+tXVNbpbYhgUbYPlP7ivLy8v1wPGs/CMCl+O3MrDMBlscd0NQC+MLaNbU3aYKeGE3UPqC8vNxWXl7+KwAFAP4DwKSBLUnJDZ/wvFTkN+OCl34A3wMwa+2a2rfUNiaQ8edBmSzLMnEc55/9vsLIskxwZPi4THl5+QCAH5aXl28B8AMA/wxAN/aa/PyjgdiyNZDoBfBLAL9cu6Y2YHLdAxl/rtSVnZ2dcSPiCCpkWabOzs44OE5Z3aa8vLyjvLz8W3DUiH4ejmwhRET0NRgMlrAv/TsJ3QCeAJC7dk1tuSZo1/HbQdmxY8dSBUF4HsBsBN+2XwZQKYriNxYuXNjh9GonlJeXpwJ4YM7c9xfHx7ev8968kOICgP8F8Lu1a2qdhu9qXIvfRK1xLbt2FxgA3AHgWwDCecWWAGyFQ8w71q6p1T6UXqCJOkDYtbtgLoC7AdwFIEtlc/zFBQAvwrEqN6ltTKigiTrA2LW7gANwAxwC/zKAUMvaugjgVQCvrF1T+4naxoQimqgDmF27CwQAywGsA3ALHF0Vg+2gUQRwBMAuAG+tXVOrdGcNjavQRB1E7NpdkA6HuFcBWAqgGIEnchlANRwi3glg79o1tVr2mR/RRB3E7NpdEAtgMRy9kRfD4TLLB+BNxwp3sAM4A+A4gE9GXifXrqmdNNhGw/doog4xdu0u4OHIFisaeeUBSAWQBCB5zH9jMfkqbwHQB0cUV9/IqxVA48irDo5wzYa1a2q14JkAQxN1mDPyJcABGP2vTRNqcKOJWkMjxAi2yC4NDQ0naKLW0AgxNFFreAwRPUxEVURUSUQvEZFRbZs0NFFreAgRZcJRKnkRY2w2HAdtd6hrlQagiVrDOwQAEUQkAIgE0KKyPRrQRK3hIYyxZgD/DeASHD7sPsbY++papQFootbwECJKAHAbHMEtGQCiiOir6lqlAWii1vCczwCoY4x1MsbsAF6HI/lEQ2U0UWt4yiUAy4gokogIwFo4Ejk0VEYTtYZHMMYOA/g7HEkcp+H4LD2nqlEaALQwUQ2NkENbqTU0QgxN1BoaIYYmag2NEEMTtYZGiKGJWkMjxNBEraERYmii1tAIMTRRa2iEGJqoNTRCDE3UGhohhiZqDY0QQxO1hkaIoYlaQyPE0EStoRFiaKLW0AgxNFFraIQY/x/2bEODZUy4/gAAAABJRU5ErkJggg==\n",
            "text/plain": [
              "<Figure size 432x288 with 1 Axes>"
            ]
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wzzWfbey94Yk"
      },
      "source": [
        "**Exemple 2 : black friday**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lngFcPoTL2g4"
      },
      "source": [
        "#inferSchema permet de detecter les types des données\n",
        "dfs = spark.read.csv('drive/My Drive/Colab_data/cours_spark/black_friday.csv',header=True, inferSchema=True)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "hJYzYi0V94Yp",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "b7f83ccc-c135-42e1-9769-de99db9624b2"
      },
      "source": [
        "dfs.printSchema() # afficher le schema "
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- User_ID: integer (nullable = true)\n",
            " |-- Product_ID: string (nullable = true)\n",
            " |-- Gender: string (nullable = true)\n",
            " |-- Age: string (nullable = true)\n",
            " |-- Occupation: integer (nullable = true)\n",
            " |-- City_Category: string (nullable = true)\n",
            " |-- Stay_In_Current_City_Years: string (nullable = true)\n",
            " |-- Marital_Status: integer (nullable = true)\n",
            " |-- Product_Category_1: integer (nullable = true)\n",
            " |-- Product_Category_2: integer (nullable = true)\n",
            " |-- Product_Category_3: integer (nullable = true)\n",
            " |-- Purchase: integer (nullable = true)\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7LaIHdQh94Yn",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "040308b5-af29-402d-c910-f5842d2168fc"
      },
      "source": [
        "dfs.show(3)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------+----------+------+----+----------+-------------+--------------------------+--------------+------------------+------------------+------------------+--------+\n",
            "|User_ID|Product_ID|Gender| Age|Occupation|City_Category|Stay_In_Current_City_Years|Marital_Status|Product_Category_1|Product_Category_2|Product_Category_3|Purchase|\n",
            "+-------+----------+------+----+----------+-------------+--------------------------+--------------+------------------+------------------+------------------+--------+\n",
            "|1000001| P00069042|     F|0-17|        10|            A|                         2|             0|                 3|              null|              null|    8370|\n",
            "|1000001| P00248942|     F|0-17|        10|            A|                         2|             0|                 1|                 6|                14|   15200|\n",
            "|1000001| P00087842|     F|0-17|        10|            A|                         2|             0|                12|              null|              null|    1422|\n",
            "+-------+----------+------+----+----------+-------------+--------------------------+--------------+------------------+------------------+------------------+--------+\n",
            "only showing top 3 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "KAujG_CuPxbW",
        "outputId": "c25c02c3-2253-4bed-97e5-dfa4d77144bc"
      },
      "source": [
        "dfs.count()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "550068"
            ]
          },
          "metadata": {},
          "execution_count": 32
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "kj03dwr994ZB",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "e965a7e2-b261-45cb-c11f-81ec3db1e796"
      },
      "source": [
        "dfs.select(\"User_ID\",\"Gender\",\"Age\",\"Occupation\").show(5)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------+------+----+----------+\n",
            "|User_ID|Gender| Age|Occupation|\n",
            "+-------+------+----+----------+\n",
            "|1000001|     F|0-17|        10|\n",
            "|1000001|     F|0-17|        10|\n",
            "|1000001|     F|0-17|        10|\n",
            "|1000001|     F|0-17|        10|\n",
            "|1000002|     M| 55+|        16|\n",
            "+-------+------+----+----------+\n",
            "only showing top 5 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "uQwoxvtjQBia",
        "outputId": "7a7e3054-04d4-4f5e-dd68-c4d6afcc9b60"
      },
      "source": [
        "dfs.select(\"City_Category\").distinct().show()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------------+\n",
            "|City_Category|\n",
            "+-------------+\n",
            "|            B|\n",
            "|            C|\n",
            "|            A|\n",
            "+-------------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "56kA2Al094ZD",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "b6e7d365-9193-4f2b-d538-9f4d6cb2c2a1"
      },
      "source": [
        "#Les categories de produit achetées par les femmes\n",
        "dfs.filter(dfs.Gender == 'F').select(\"Product_Category_1\").distinct().show()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+------------------+\n",
            "|Product_Category_1|\n",
            "+------------------+\n",
            "|                12|\n",
            "|                 1|\n",
            "|                13|\n",
            "|                 6|\n",
            "|                16|\n",
            "|                 3|\n",
            "|                20|\n",
            "|                 5|\n",
            "|                19|\n",
            "|                15|\n",
            "|                 9|\n",
            "|                17|\n",
            "|                 4|\n",
            "|                 8|\n",
            "|                 7|\n",
            "|                10|\n",
            "|                11|\n",
            "|                14|\n",
            "|                 2|\n",
            "|                18|\n",
            "+------------------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "MYUDIGfJQ0YB",
        "outputId": "a3b0ed35-0cae-448d-ea96-858997048b63"
      },
      "source": [
        "#Total des ventes par city_category\n",
        "dfs.groupBy(\"City_Category\").sum(\"Purchase\").show()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------------+-------------+\n",
            "|City_Category|sum(Purchase)|\n",
            "+-------------+-------------+\n",
            "|            B|   2115533605|\n",
            "|            C|   1663807476|\n",
            "|            A|   1316471661|\n",
            "+-------------+-------------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lAaYrUZG94ZF",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "2b0b85fb-06a9-455b-9930-78eda1293b89"
      },
      "source": [
        "#Total des ventes par city_category pour les femmes\n",
        "dfs.filter(dfs.Gender == 'F').groupBy(\"City_Category\").sum(\"Purchase\").show()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------------+-------------+\n",
            "|City_Category|sum(Purchase)|\n",
            "+-------------+-------------+\n",
            "|            B|    493617008|\n",
            "|            C|    386285719|\n",
            "|            A|    306329915|\n",
            "+-------------+-------------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "5jWz0ENp94ZH",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "307444e9-4239-47b7-8314-8f9f9b1ab7ee"
      },
      "source": [
        "#Remplacer les valeurs nulles\n",
        "dfs_modif = dfs.fillna({'Product_Category_2':0, 'Product_Category_3':0})\n",
        "dfs_modif.show(3)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------+----------+------+----+----------+-------------+--------------------------+--------------+------------------+------------------+------------------+--------+\n",
            "|User_ID|Product_ID|Gender| Age|Occupation|City_Category|Stay_In_Current_City_Years|Marital_Status|Product_Category_1|Product_Category_2|Product_Category_3|Purchase|\n",
            "+-------+----------+------+----+----------+-------------+--------------------------+--------------+------------------+------------------+------------------+--------+\n",
            "|1000001| P00069042|     F|0-17|        10|            A|                         2|             0|                 3|                 0|                 0|    8370|\n",
            "|1000001| P00248942|     F|0-17|        10|            A|                         2|             0|                 1|                 6|                14|   15200|\n",
            "|1000001| P00087842|     F|0-17|        10|            A|                         2|             0|                12|                 0|                 0|    1422|\n",
            "+-------+----------+------+----+----------+-------------+--------------------------+--------------+------------------+------------------+------------------+--------+\n",
            "only showing top 3 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "e1vup4NcSakK"
      },
      "source": [
        "dfs_modif.write.csv(\"drive/My Drive/Colab_data/cours_spark/dfs_modif\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 163
        },
        "id": "J05VHVA2TAMw",
        "outputId": "1a2c9aa8-6751-4fd9-f710-3aca97a11b1d"
      },
      "source": [
        "dfs_modif.rdd.getNumPartitions()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "error",
          "ename": "NameError",
          "evalue": "ignored",
          "traceback": [
            "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
            "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
            "\u001b[0;32m<ipython-input-28-5ed9ae101580>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mdfs_modif\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrdd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mgetNumPartitions\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
            "\u001b[0;31mNameError\u001b[0m: name 'dfs_modif' is not defined"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "nn9uXBqJ94ZI"
      },
      "source": [
        "**Exemple 3 : Fichier sans entête**\n",
        "\n",
        "\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1854SK1t94ZL",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "36d6bcbf-60d0-406f-9aae-e9666faed869"
      },
      "source": [
        "DFpeople = sqlContext.read.csv('drive/My Drive/Colab_data/cours_spark/people.csv')\n",
        "DFpeople.printSchema()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- _c0: string (nullable = true)\n",
            " |-- _c1: string (nullable = true)\n",
            " |-- _c2: string (nullable = true)\n",
            " |-- _c3: string (nullable = true)\n",
            " |-- _c4: string (nullable = true)\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "FwsGJSsh94ZR"
      },
      "source": [
        "from pyspark.sql import Row\n",
        "\n",
        "myDF = sc.textFile(\"drive/My Drive/Colab_data/cours_spark/people.csv\") \\\n",
        "         .map(lambda l: l.split(\",\")) \\\n",
        "         .map(lambda l: Row(ID=int(l[0]), name=l[1], age=int(l[2]), sex=l[3], height=int(l[4]))) \\\n",
        "         .toDF()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2sUea1SD94ZT",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "ee72ed1b-4b70-4fa2-8420-7a7e3f608a3b"
      },
      "source": [
        "myDF.printSchema()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- ID: long (nullable = true)\n",
            " |-- name: string (nullable = true)\n",
            " |-- age: long (nullable = true)\n",
            " |-- sex: string (nullable = true)\n",
            " |-- height: long (nullable = true)\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_Ubza0M794ZU",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 197
        },
        "outputId": "467f3792-98df-4b56-9a18-40785a5d4444"
      },
      "source": [
        "myDF.createOrReplaceTempView(\"myDF_tab\")\n",
        "\n",
        "sqlContext.sql(\"SELECT sex, AVG(height) AS avgheight FROM myDF_tab WHERE age > 21 GROUP BY sex\").show()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "error",
          "ename": "NameError",
          "evalue": "ignored",
          "traceback": [
            "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
            "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
            "\u001b[0;32m<ipython-input-30-11c30acda00c>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mmyDF\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcreateOrReplaceTempView\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"myDF_tab\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      2\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      3\u001b[0m \u001b[0msqlContext\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msql\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"SELECT sex, AVG(height) AS avgheight FROM myDF_tab WHERE age > 21 GROUP BY sex\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mshow\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;31mNameError\u001b[0m: name 'myDF' is not defined"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "P9T8IN0294ZV",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "a3062ded-8c92-4eb6-aaf7-198fcd16bfbe"
      },
      "source": [
        "myDF.filter('age > 21').groupBy('sex').mean('height').show()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---------+-----------+\n",
            "|      sex|avg(height)|\n",
            "+---------+-----------+\n",
            "| \"female\"|      169.0|\n",
            "|   \"male\"|      170.0|\n",
            "+---------+-----------+\n",
            "\n"
          ]
        }
      ]
    }
  ]
}