Aula de Spark.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Aula de Spark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "            <div>\n",
       "                <p><b>SparkSession - hive</b></p>\n",
       "                \n",
       "        <div>\n",
       "            <p><b>SparkContext</b></p>\n",
       "\n",
       "            <p><a href=\"http://jupyter-spark:4042\">Spark UI</a></p>\n",
       "\n",
       "            <dl>\n",
       "              <dt>Version</dt>\n",
       "                <dd><code>v2.4.1</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 0x7f1687176fd0>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Lendo arquivos"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### RDD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "rdd = sc.textFile('/bases/hadoop.txt')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "291"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rdd.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Apache Hadoop ( /həˈduːp/) is a collection of open-source software utilities that facilitate using a network of many computers to solve problems involving massive amounts of data and computation. It provides a software framework for distributed storage and processing of big data using the MapReduce programming model. Originally designed for computer clusters built from commodity hardware[3]—still the common use—it has also found use on clusters of higher-end hardware.[4][5] All the modules in Hadoop are designed with a fundamental assumption that hardware failures are common occurrences and should be automatically handled by the framework.[2]']"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rdd.take(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "rdd2 = sc.textFile('/bases/message.txt')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Bem Vindo ao curso de Fast Data com Spark',\n",
       " 'Este curso foi feito para iniciantes que desejam trabalhar com Fast Data',\n",
       " 'Todos exercios sao feitos de forma simples e focada na teoria',\n",
       " 'Inicialemnte teremos exemplos que focan no nucleo',\n",
       " 'e posteriomente focamos na principais funcoes de mapper e reducer',\n",
       " 'Espero que todos gostem do curso e ate proximo.']"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rdd2.take(6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Bem Vindo ao curso de Fast Data com Spark']"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rdd2.take(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_csv = spark.read.csv(path='/bases/base.csv',header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+----+---------+\n",
      "|usuario|item|avaliacao|\n",
      "+-------+----+---------+\n",
      "|    109|   9|        3|\n",
      "|    174| 412|        1|\n",
      "|      7| 208|        5|\n",
      "|    371|  97|        5|\n",
      "|    296| 255|        2|\n",
      "|    280|  82|        2|\n",
      "|    271| 275|        4|\n",
      "|    110| 791|        2|\n",
      "|     59| 926|        1|\n",
      "|    217| 576|        1|\n",
      "|    145| 665|        5|\n",
      "|    334| 204|        4|\n",
      "|     42| 568|        4|\n",
      "|    200| 143|        5|\n",
      "|     89| 387|        5|\n",
      "|    311| 588|        4|\n",
      "|    235| 269|        4|\n",
      "|    287| 156|        5|\n",
      "|    344| 204|        4|\n",
      "|     43| 289|        4|\n",
      "+-------+----+---------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_csv.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### JSON"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_json = spark.read.json('/bases/pessoas.json')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---+-------------+\n",
      "|age| id|         name|\n",
      "+---+---+-------------+\n",
      "| 28|  1|       Helena|\n",
      "| 44|  2|       Miguel|\n",
      "| 97|  3|        Alice|\n",
      "| 28|  4|        Laura|\n",
      "| 94|  5|      Manuela|\n",
      "| 71|  6|    Valentina|\n",
      "| 48|  7|       Sophia|\n",
      "| 98|  8|     Isabella|\n",
      "| 60|  9|      Heloísa|\n",
      "| 46| 10|        Luiza|\n",
      "| 47| 11|        Júlia|\n",
      "| 35| 12|       Lorena|\n",
      "| 32| 13|        Lívia|\n",
      "| 90| 14|  Maria Luiza|\n",
      "| 88| 15|      Cecília|\n",
      "| 36| 16|         Eloá|\n",
      "| 57| 17|     Giovanna|\n",
      "| 75| 18|  Maria Clara|\n",
      "| 97| 19|Maria Eduarda|\n",
      "| 65| 20|      Mariana|\n",
      "+---+---+-------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_json.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pyspark.sql.dataframe.DataFrame"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(df_csv)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pyspark.sql.dataframe.DataFrame"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(df_json)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Help on method filter in module pyspark.sql.dataframe:\n",
      "\n",
      "filter(condition) method of pyspark.sql.dataframe.DataFrame instance\n",
      "    Filters rows using the given condition.\n",
      "    \n",
      "    :func:`where` is an alias for :func:`filter`.\n",
      "    \n",
      "    :param condition: a :class:`Column` of :class:`types.BooleanType`\n",
      "        or a string of SQL expression.\n",
      "    \n",
      "    >>> df.filter(df.age > 3).collect()\n",
      "    [Row(age=5, name='Bob')]\n",
      "    >>> df.where(df.age == 2).collect()\n",
      "    [Row(age=2, name='Alice')]\n",
      "    \n",
      "    >>> df.filter(\"age > 3\").collect()\n",
      "    [Row(age=5, name='Bob')]\n",
      "    >>> df.where(\"age = 2\").collect()\n",
      "    [Row(age=2, name='Alice')]\n",
      "    \n",
      "    .. versionadded:: 1.3\n",
      "\n"
     ]
    }
   ],
   "source": [
    "help(df_json.filter)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "p = df_json.toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(p)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_json2 = df_json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---+-------------+\n",
      "|age| id|         name|\n",
      "+---+---+-------------+\n",
      "| 28|  1|       Helena|\n",
      "| 44|  2|       Miguel|\n",
      "| 97|  3|        Alice|\n",
      "| 28|  4|        Laura|\n",
      "| 94|  5|      Manuela|\n",
      "| 71|  6|    Valentina|\n",
      "| 48|  7|       Sophia|\n",
      "| 98|  8|     Isabella|\n",
      "| 60|  9|      Heloísa|\n",
      "| 46| 10|        Luiza|\n",
      "| 47| 11|        Júlia|\n",
      "| 35| 12|       Lorena|\n",
      "| 32| 13|        Lívia|\n",
      "| 90| 14|  Maria Luiza|\n",
      "| 88| 15|      Cecília|\n",
      "| 36| 16|         Eloá|\n",
      "| 57| 17|     Giovanna|\n",
      "| 75| 18|  Maria Clara|\n",
      "| 97| 19|Maria Eduarda|\n",
      "| 65| 20|      Mariana|\n",
      "+---+---+-------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_json.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---+-------------+\n",
      "|age| id|         name|\n",
      "+---+---+-------------+\n",
      "| 28|  1|       Helena|\n",
      "| 44|  2|       Miguel|\n",
      "| 97|  3|        Alice|\n",
      "| 28|  4|        Laura|\n",
      "| 94|  5|      Manuela|\n",
      "| 71|  6|    Valentina|\n",
      "| 48|  7|       Sophia|\n",
      "| 98|  8|     Isabella|\n",
      "| 60|  9|      Heloísa|\n",
      "| 46| 10|        Luiza|\n",
      "| 47| 11|        Júlia|\n",
      "| 35| 12|       Lorena|\n",
      "| 32| 13|        Lívia|\n",
      "| 90| 14|  Maria Luiza|\n",
      "| 88| 15|      Cecília|\n",
      "| 36| 16|         Eloá|\n",
      "| 57| 17|     Giovanna|\n",
      "| 75| 18|  Maria Clara|\n",
      "| 97| 19|Maria Eduarda|\n",
      "| 65| 20|      Mariana|\n",
      "+---+---+-------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_json2.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DataFrame[age: bigint, id: bigint, name: string]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+-----------------+------------------+------+\n",
      "|summary|              age|                id|  name|\n",
      "+-------+-----------------+------------------+------+\n",
      "|  count|              100|               100|   100|\n",
      "|   mean|            52.61|              50.5|  null|\n",
      "| stddev|29.66373666157508|29.011491975882016|  null|\n",
      "|    min|                1|                 1|Agatha|\n",
      "|    max|               98|               100|Yasmin|\n",
      "+-------+-----------------+------------------+------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_json.describe().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_maior = df_json.filter(df_json.age >= 18)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---+-------------+\n",
      "|age| id|         name|\n",
      "+---+---+-------------+\n",
      "| 28|  1|       Helena|\n",
      "| 44|  2|       Miguel|\n",
      "| 97|  3|        Alice|\n",
      "| 28|  4|        Laura|\n",
      "| 94|  5|      Manuela|\n",
      "| 71|  6|    Valentina|\n",
      "| 48|  7|       Sophia|\n",
      "| 98|  8|     Isabella|\n",
      "| 60|  9|      Heloísa|\n",
      "| 46| 10|        Luiza|\n",
      "| 47| 11|        Júlia|\n",
      "| 35| 12|       Lorena|\n",
      "| 32| 13|        Lívia|\n",
      "| 90| 14|  Maria Luiza|\n",
      "| 88| 15|      Cecília|\n",
      "| 36| 16|         Eloá|\n",
      "| 57| 17|     Giovanna|\n",
      "| 75| 18|  Maria Clara|\n",
      "| 97| 19|Maria Eduarda|\n",
      "| 65| 20|      Mariana|\n",
      "+---+---+-------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_maior.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_menor = df_json.where(df_json.age < 18)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---+------------+\n",
      "|age| id|        name|\n",
      "+---+---+------------+\n",
      "|  4| 26|     Isadora|\n",
      "|  7| 38|         Liz|\n",
      "| 12| 39|      Yasmin|\n",
      "|  3| 48|Maria Helena|\n",
      "| 13| 50|    Gabriela|\n",
      "| 17| 53|      Heitor|\n",
      "|  6| 54|    Bernardo|\n",
      "| 11| 59|       Pedro|\n",
      "| 12| 60|    Benjamin|\n",
      "|  6| 61|     Matheus|\n",
      "|  6| 69|        Enzo|\n",
      "|  3| 76|Enzo Gabriel|\n",
      "|  1| 85|    Leonardo|\n",
      "| 16| 87|     Eduardo|\n",
      "|  1| 88|        Gael|\n",
      "|  1| 89|     Antônio|\n",
      "|  8| 95|        Cauã|\n",
      "| 13| 96|  João Lucas|\n",
      "+---+---+------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_menor.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "100"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_json.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "82"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_maior.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "18"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_menor.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "100\n"
     ]
    }
   ],
   "source": [
    "print(82+18)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_union = df_maior.union(df_menor)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "100"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_union.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_duplicado = df_union.unionAll(df_json)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "200"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_duplicado.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---+---------+\n",
      "|age| id|     name|\n",
      "+---+---+---------+\n",
      "| 28|  1|   Helena|\n",
      "| 28|  1|   Helena|\n",
      "| 44|  2|   Miguel|\n",
      "| 44|  2|   Miguel|\n",
      "| 97|  3|    Alice|\n",
      "| 97|  3|    Alice|\n",
      "| 28|  4|    Laura|\n",
      "| 28|  4|    Laura|\n",
      "| 94|  5|  Manuela|\n",
      "| 94|  5|  Manuela|\n",
      "| 71|  6|Valentina|\n",
      "| 71|  6|Valentina|\n",
      "| 48|  7|   Sophia|\n",
      "| 48|  7|   Sophia|\n",
      "| 98|  8| Isabella|\n",
      "| 98|  8| Isabella|\n",
      "| 60|  9|  Heloísa|\n",
      "| 60|  9|  Heloísa|\n",
      "| 46| 10|    Luiza|\n",
      "| 46| 10|    Luiza|\n",
      "+---+---+---------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_duplicado.orderBy('id',ascending=True).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_limpo = df_duplicado.dropDuplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---+-------------+\n",
      "|age| id|         name|\n",
      "+---+---+-------------+\n",
      "| 28|  1|       Helena|\n",
      "| 44|  2|       Miguel|\n",
      "| 97|  3|        Alice|\n",
      "| 28|  4|        Laura|\n",
      "| 94|  5|      Manuela|\n",
      "| 71|  6|    Valentina|\n",
      "| 48|  7|       Sophia|\n",
      "| 98|  8|     Isabella|\n",
      "| 60|  9|      Heloísa|\n",
      "| 46| 10|        Luiza|\n",
      "| 47| 11|        Júlia|\n",
      "| 35| 12|       Lorena|\n",
      "| 32| 13|        Lívia|\n",
      "| 90| 14|  Maria Luiza|\n",
      "| 88| 15|      Cecília|\n",
      "| 36| 16|         Eloá|\n",
      "| 57| 17|     Giovanna|\n",
      "| 75| 18|  Maria Clara|\n",
      "| 97| 19|Maria Eduarda|\n",
      "| 65| 20|      Mariana|\n",
      "+---+---+-------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_limpo.orderBy('id',ascending=True).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_name = df_json.select('name','age')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+---+\n",
      "|     name|age|\n",
      "+---------+---+\n",
      "|   Agatha| 81|\n",
      "|    Alice| 97|\n",
      "|Ana Laura| 93|\n",
      "|Ana Luiza| 92|\n",
      "|  Anthony| 81|\n",
      "+---------+---+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_json.select('name','age').filter(df_json.age > 72).orderBy('name',ascending=True).show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Row(age=28, id=1, name='Helena')"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_json.first()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---+-------+\n",
      "|age| id|   name|\n",
      "+---+---+-------+\n",
      "| 28|  1| Helena|\n",
      "| 44|  2| Miguel|\n",
      "| 97|  3|  Alice|\n",
      "| 28|  4|  Laura|\n",
      "| 94|  5|Manuela|\n",
      "+---+---+-------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_json.limit(5).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- age: long (nullable = true)\n",
      " |-- id: long (nullable = true)\n",
      " |-- name: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_json.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---+-------+\n",
      "|age| id|   name|\n",
      "+---+---+-------+\n",
      "| 97|  3|  Alice|\n",
      "| 36| 16|   Eloá|\n",
      "|  7| 38|    Liz|\n",
      "| 78| 45|Rafaela|\n",
      "| 44| 67| Rafael|\n",
      "| 68| 92|   Caio|\n",
      "+---+---+-------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_json.sample(0.1).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = df_maior.join(df_json,df_maior.id == df_json.id,'right')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_maior1 = df_maior.withColumn('age1',df_maior.age) \\\n",
    "    .withColumn('id1',df_maior.id) \\\n",
    "    .withColumn('name1',df_maior.name) \\\n",
    "    .drop('age','id','name')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = df_maior1.join(df_json,df_maior1.id1 == df_json.id,'right')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+---+-------------+---+---+-------------+\n",
      "|age1|id1|        name1|age| id|         name|\n",
      "+----+---+-------------+---+---+-------------+\n",
      "|  28|  1|       Helena| 28|  1|       Helena|\n",
      "|  44|  2|       Miguel| 44|  2|       Miguel|\n",
      "|  97|  3|        Alice| 97|  3|        Alice|\n",
      "|  28|  4|        Laura| 28|  4|        Laura|\n",
      "|  94|  5|      Manuela| 94|  5|      Manuela|\n",
      "|  71|  6|    Valentina| 71|  6|    Valentina|\n",
      "|  48|  7|       Sophia| 48|  7|       Sophia|\n",
      "|  98|  8|     Isabella| 98|  8|     Isabella|\n",
      "|  60|  9|      Heloísa| 60|  9|      Heloísa|\n",
      "|  46| 10|        Luiza| 46| 10|        Luiza|\n",
      "|  47| 11|        Júlia| 47| 11|        Júlia|\n",
      "|  35| 12|       Lorena| 35| 12|       Lorena|\n",
      "|  32| 13|        Lívia| 32| 13|        Lívia|\n",
      "|  90| 14|  Maria Luiza| 90| 14|  Maria Luiza|\n",
      "|  88| 15|      Cecília| 88| 15|      Cecília|\n",
      "|  36| 16|         Eloá| 36| 16|         Eloá|\n",
      "|  57| 17|     Giovanna| 57| 17|     Giovanna|\n",
      "|  75| 18|  Maria Clara| 75| 18|  Maria Clara|\n",
      "|  97| 19|Maria Eduarda| 97| 19|Maria Eduarda|\n",
      "|  65| 20|      Mariana| 65| 20|      Mariana|\n",
      "+----+---+-------------+---+---+-------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df1.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 243,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----+-----+-------------+\n",
      "|Idade|Chave|         Nome|\n",
      "+-----+-----+-------------+\n",
      "|   28|    1|       Helena|\n",
      "|   44|    2|       Miguel|\n",
      "|   97|    3|        Alice|\n",
      "|   28|    4|        Laura|\n",
      "|   94|    5|      Manuela|\n",
      "|   71|    6|    Valentina|\n",
      "|   48|    7|       Sophia|\n",
      "|   98|    8|     Isabella|\n",
      "|   60|    9|      Heloísa|\n",
      "|   46|   10|        Luiza|\n",
      "|   47|   11|        Júlia|\n",
      "|   35|   12|       Lorena|\n",
      "|   32|   13|        Lívia|\n",
      "|   90|   14|  Maria Luiza|\n",
      "|   88|   15|      Cecília|\n",
      "|   36|   16|         Eloá|\n",
      "|   57|   17|     Giovanna|\n",
      "|   75|   18|  Maria Clara|\n",
      "|   97|   19|Maria Eduarda|\n",
      "|   65|   20|      Mariana|\n",
      "+-----+-----+-------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_maior.withColumnRenamed('age','Idade') \\\n",
    "    .withColumnRenamed('id','Chave') \\\n",
    "    .withColumnRenamed('Name','Nome') \\\n",
    "    .show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---+-------+\n",
      "|age| id|   name|\n",
      "+---+---+-------+\n",
      "| 28|  1| Helena|\n",
      "| 44|  2| Miguel|\n",
      "| 97|  3|  Alice|\n",
      "| 28|  4|  Laura|\n",
      "| 94|  5|Manuela|\n",
      "+---+---+-------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_json.show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---+---------+\n",
      "|age| id|     name|\n",
      "+---+---+---------+\n",
      "| 97|  3|    Alice|\n",
      "| 91| 23|Antonella|\n",
      "| 55| 27|Ana Clara|\n",
      "| 92| 29|Ana Luiza|\n",
      "| 23| 30|Ana Júlia|\n",
      "| 25| 41|   Alícia|\n",
      "| 93| 47|Ana Laura|\n",
      "| 81| 49|   Agatha|\n",
      "| 67| 52|   Arthur|\n",
      "| 81| 81|  Anthony|\n",
      "|  1| 89|  Antônio|\n",
      "+---+---+---------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_json.filter(df_json.name.like('A%')).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---+---------+\n",
      "|age| id|     name|\n",
      "+---+---+---------+\n",
      "| 97|  3|    Alice|\n",
      "| 91| 23|Antonella|\n",
      "| 55| 27|Ana Clara|\n",
      "| 92| 29|Ana Luiza|\n",
      "| 23| 30|Ana Júlia|\n",
      "| 25| 41|   Alícia|\n",
      "| 93| 47|Ana Laura|\n",
      "| 81| 49|   Agatha|\n",
      "| 67| 52|   Arthur|\n",
      "| 81| 81|  Anthony|\n",
      "|  1| 89|  Antônio|\n",
      "+---+---+---------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_json.filter(df_json.name.startswith(\"A\")).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---+-------------+\n",
      "|age| id|         name|\n",
      "+---+---+-------------+\n",
      "| 28|  1|       Helena|\n",
      "| 28|  4|        Laura|\n",
      "| 94|  5|      Manuela|\n",
      "| 71|  6|    Valentina|\n",
      "| 48|  7|       Sophia|\n",
      "| 98|  8|     Isabella|\n",
      "| 60|  9|      Heloísa|\n",
      "| 46| 10|        Luiza|\n",
      "| 47| 11|        Júlia|\n",
      "| 35| 12|       Lorena|\n",
      "| 32| 13|        Lívia|\n",
      "| 90| 14|  Maria Luiza|\n",
      "| 88| 15|      Cecília|\n",
      "| 57| 17|     Giovanna|\n",
      "| 75| 18|  Maria Clara|\n",
      "| 97| 19|Maria Eduarda|\n",
      "| 65| 20|      Mariana|\n",
      "| 89| 21|         Lara|\n",
      "| 91| 23|    Antonella|\n",
      "| 77| 24|  Maria Júlia|\n",
      "+---+---+-------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_json.filter(df_json.name.endswith(\"a\")).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---+--------------+\n",
      "|age| id|          name|\n",
      "+---+---+--------------+\n",
      "| 23| 30|     Ana Júlia|\n",
      "| 24| 74|   João Miguel|\n",
      "| 25| 41|        Alícia|\n",
      "| 25| 77|Pedro Henrique|\n",
      "| 26| 90|         Vitor|\n",
      "| 28|  4|         Laura|\n",
      "| 28|  1|        Helena|\n",
      "| 29| 66|      Henrique|\n",
      "| 30| 55|          Davi|\n",
      "| 30| 98|        Enrico|\n",
      "| 32| 71|       Benício|\n",
      "| 32| 13|         Lívia|\n",
      "| 32| 56|          Théo|\n",
      "| 35| 33|         Maitê|\n",
      "| 35| 12|        Lorena|\n",
      "| 36| 40|      Isabelly|\n",
      "| 36| 70|        Murilo|\n",
      "| 36| 16|          Eloá|\n",
      "| 44|  2|        Miguel|\n",
      "| 44| 28|       Melissa|\n",
      "+---+---+--------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_json.filter(df_json.age.between(18,50)).orderBy(\"age\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+-----+\n",
      "|age|count|\n",
      "+---+-----+\n",
      "| 29|    1|\n",
      "| 26|    1|\n",
      "| 65|    3|\n",
      "| 77|    1|\n",
      "|  7|    1|\n",
      "| 94|    1|\n",
      "| 57|    2|\n",
      "| 32|    3|\n",
      "| 84|    1|\n",
      "| 98|    1|\n",
      "| 25|    2|\n",
      "| 95|    1|\n",
      "| 71|    1|\n",
      "|  6|    3|\n",
      "| 68|    1|\n",
      "| 51|    2|\n",
      "| 56|    1|\n",
      "| 52|    1|\n",
      "| 17|    1|\n",
      "| 28|    2|\n",
      "+---+-----+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_json.groupBy(\"age\").count().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+---+-------------+---+---+-------------+\n",
      "|age1|id1|        name1|age| id|         name|\n",
      "+----+---+-------------+---+---+-------------+\n",
      "|  28|  1|       Helena| 28|  1|       Helena|\n",
      "|  44|  2|       Miguel| 44|  2|       Miguel|\n",
      "|  97|  3|        Alice| 97|  3|        Alice|\n",
      "|  28|  4|        Laura| 28|  4|        Laura|\n",
      "|  94|  5|      Manuela| 94|  5|      Manuela|\n",
      "|  71|  6|    Valentina| 71|  6|    Valentina|\n",
      "|  48|  7|       Sophia| 48|  7|       Sophia|\n",
      "|  98|  8|     Isabella| 98|  8|     Isabella|\n",
      "|  60|  9|      Heloísa| 60|  9|      Heloísa|\n",
      "|  46| 10|        Luiza| 46| 10|        Luiza|\n",
      "|  47| 11|        Júlia| 47| 11|        Júlia|\n",
      "|  35| 12|       Lorena| 35| 12|       Lorena|\n",
      "|  32| 13|        Lívia| 32| 13|        Lívia|\n",
      "|  90| 14|  Maria Luiza| 90| 14|  Maria Luiza|\n",
      "|  88| 15|      Cecília| 88| 15|      Cecília|\n",
      "|  36| 16|         Eloá| 36| 16|         Eloá|\n",
      "|  57| 17|     Giovanna| 57| 17|     Giovanna|\n",
      "|  75| 18|  Maria Clara| 75| 18|  Maria Clara|\n",
      "|  97| 19|Maria Eduarda| 97| 19|Maria Eduarda|\n",
      "|  65| 20|      Mariana| 65| 20|      Mariana|\n",
      "+----+---+-------------+---+---+-------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df1.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1 = df_json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2 = df_1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_3 = df_2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---+-------------+\n",
      "|age| id|         name|\n",
      "+---+---+-------------+\n",
      "| 28|  1|       Helena|\n",
      "| 44|  2|       Miguel|\n",
      "| 97|  3|        Alice|\n",
      "| 28|  4|        Laura|\n",
      "| 94|  5|      Manuela|\n",
      "| 71|  6|    Valentina|\n",
      "| 48|  7|       Sophia|\n",
      "| 98|  8|     Isabella|\n",
      "| 60|  9|      Heloísa|\n",
      "| 46| 10|        Luiza|\n",
      "| 47| 11|        Júlia|\n",
      "| 35| 12|       Lorena|\n",
      "| 32| 13|        Lívia|\n",
      "| 90| 14|  Maria Luiza|\n",
      "| 88| 15|      Cecília|\n",
      "| 36| 16|         Eloá|\n",
      "| 57| 17|     Giovanna|\n",
      "| 75| 18|  Maria Clara|\n",
      "| 97| 19|Maria Eduarda|\n",
      "| 65| 20|      Mariana|\n",
      "+---+---+-------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_3.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DataFrame[age: bigint, id: bigint, name: string]"
      ]
     },
     "execution_count": 175,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_3.persist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---+-------------+\n",
      "|age| id|         name|\n",
      "+---+---+-------------+\n",
      "| 28|  1|       Helena|\n",
      "| 44|  2|       Miguel|\n",
      "| 97|  3|        Alice|\n",
      "| 28|  4|        Laura|\n",
      "| 94|  5|      Manuela|\n",
      "| 71|  6|    Valentina|\n",
      "| 48|  7|       Sophia|\n",
      "| 98|  8|     Isabella|\n",
      "| 60|  9|      Heloísa|\n",
      "| 46| 10|        Luiza|\n",
      "| 47| 11|        Júlia|\n",
      "| 35| 12|       Lorena|\n",
      "| 32| 13|        Lívia|\n",
      "| 90| 14|  Maria Luiza|\n",
      "| 88| 15|      Cecília|\n",
      "| 36| 16|         Eloá|\n",
      "| 57| 17|     Giovanna|\n",
      "| 75| 18|  Maria Clara|\n",
      "| 97| 19|Maria Eduarda|\n",
      "| 65| 20|      Mariana|\n",
      "+---+---+-------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_3.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "100"
      ]
     },
     "execution_count": 180,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_3.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DataFrame[age: bigint, id: bigint, name: string]"
      ]
     },
     "execution_count": 181,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_3.unpersist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---+-------------+\n",
      "|age| id|         name|\n",
      "+---+---+-------------+\n",
      "| 28|  1|       Helena|\n",
      "| 44|  2|       Miguel|\n",
      "| 97|  3|        Alice|\n",
      "| 28|  4|        Laura|\n",
      "| 94|  5|      Manuela|\n",
      "| 71|  6|    Valentina|\n",
      "| 48|  7|       Sophia|\n",
      "| 98|  8|     Isabella|\n",
      "| 60|  9|      Heloísa|\n",
      "| 46| 10|        Luiza|\n",
      "| 47| 11|        Júlia|\n",
      "| 35| 12|       Lorena|\n",
      "| 32| 13|        Lívia|\n",
      "| 90| 14|  Maria Luiza|\n",
      "| 88| 15|      Cecília|\n",
      "| 36| 16|         Eloá|\n",
      "| 57| 17|     Giovanna|\n",
      "| 75| 18|  Maria Clara|\n",
      "| 97| 19|Maria Eduarda|\n",
      "| 65| 20|      Mariana|\n",
      "+---+---+-------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_maior.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 190,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_maior.write.csv('/export/csv',header=True,sep=';',mode='append')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 194,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_maior.write.json('/export/json',mode='overwrite')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 195,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_maior.write.parquet('/export/parquet',mode='append')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 197,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_maior.write.orc('/export/orc',mode='append',compression='ZLIB')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 198,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_parquet = spark.read.parquet('/export/parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 199,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---+-------------+\n",
      "|age| id|         name|\n",
      "+---+---+-------------+\n",
      "| 28|  1|       Helena|\n",
      "| 44|  2|       Miguel|\n",
      "| 97|  3|        Alice|\n",
      "| 28|  4|        Laura|\n",
      "| 94|  5|      Manuela|\n",
      "| 71|  6|    Valentina|\n",
      "| 48|  7|       Sophia|\n",
      "| 98|  8|     Isabella|\n",
      "| 60|  9|      Heloísa|\n",
      "| 46| 10|        Luiza|\n",
      "| 47| 11|        Júlia|\n",
      "| 35| 12|       Lorena|\n",
      "| 32| 13|        Lívia|\n",
      "| 90| 14|  Maria Luiza|\n",
      "| 88| 15|      Cecília|\n",
      "| 36| 16|         Eloá|\n",
      "| 57| 17|     Giovanna|\n",
      "| 75| 18|  Maria Clara|\n",
      "| 97| 19|Maria Eduarda|\n",
      "| 65| 20|      Mariana|\n",
      "+---+---+-------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_parquet.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 203,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_orc = spark.read.orc('/export/orc')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 201,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---+-------------+\n",
      "|age| id|         name|\n",
      "+---+---+-------------+\n",
      "| 28|  1|       Helena|\n",
      "| 44|  2|       Miguel|\n",
      "| 97|  3|        Alice|\n",
      "| 28|  4|        Laura|\n",
      "| 94|  5|      Manuela|\n",
      "| 71|  6|    Valentina|\n",
      "| 48|  7|       Sophia|\n",
      "| 98|  8|     Isabella|\n",
      "| 60|  9|      Heloísa|\n",
      "| 46| 10|        Luiza|\n",
      "| 47| 11|        Júlia|\n",
      "| 35| 12|       Lorena|\n",
      "| 32| 13|        Lívia|\n",
      "| 90| 14|  Maria Luiza|\n",
      "| 88| 15|      Cecília|\n",
      "| 36| 16|         Eloá|\n",
      "| 57| 17|     Giovanna|\n",
      "| 75| 18|  Maria Clara|\n",
      "| 97| 19|Maria Eduarda|\n",
      "| 65| 20|      Mariana|\n",
      "+---+---+-------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_orc.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 205,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "164"
      ]
     },
     "execution_count": 205,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_orc.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 206,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DataFrame[age: bigint, id: bigint, name: string]"
      ]
     },
     "execution_count": 206,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_parquet.unionAll(df_orc)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 207,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_maior.write.orc('/export/orc/1',mode='append',compression='ZLIB')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 208,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_maior.write.orc('/export/orc/2',mode='append',compression='ZLIB')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 220,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_orc = spark.read.orc('/export/orc/*')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 221,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "328"
      ]
     },
     "execution_count": 221,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_orc.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 223,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+---------+-----------+\n",
      "|database|tableName|isTemporary|\n",
      "+--------+---------+-----------+\n",
      "+--------+---------+-----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sql('show tables in default').show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 230,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_maior.write.mode('overwrite').format('hive').saveAsTable('default.maior')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 233,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_maior.write.mode('append').insertInto('default.maior')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 234,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---+-------------+\n",
      "|age| id|         name|\n",
      "+---+---+-------------+\n",
      "| 28|  1|       Helena|\n",
      "| 44|  2|       Miguel|\n",
      "| 97|  3|        Alice|\n",
      "| 28|  4|        Laura|\n",
      "| 94|  5|      Manuela|\n",
      "| 71|  6|    Valentina|\n",
      "| 48|  7|       Sophia|\n",
      "| 98|  8|     Isabella|\n",
      "| 60|  9|      Heloísa|\n",
      "| 46| 10|        Luiza|\n",
      "| 47| 11|        Júlia|\n",
      "| 35| 12|       Lorena|\n",
      "| 32| 13|        Lívia|\n",
      "| 90| 14|  Maria Luiza|\n",
      "| 88| 15|      Cecília|\n",
      "| 36| 16|         Eloá|\n",
      "| 57| 17|     Giovanna|\n",
      "| 75| 18|  Maria Clara|\n",
      "| 97| 19|Maria Eduarda|\n",
      "| 65| 20|      Mariana|\n",
      "+---+---+-------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sql('select * from default.maior').show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 235,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_hive = sql('select * from default.maior')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 236,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---+-------------+\n",
      "|age| id|         name|\n",
      "+---+---+-------------+\n",
      "| 28|  1|       Helena|\n",
      "| 44|  2|       Miguel|\n",
      "| 97|  3|        Alice|\n",
      "| 28|  4|        Laura|\n",
      "| 94|  5|      Manuela|\n",
      "| 71|  6|    Valentina|\n",
      "| 48|  7|       Sophia|\n",
      "| 98|  8|     Isabella|\n",
      "| 60|  9|      Heloísa|\n",
      "| 46| 10|        Luiza|\n",
      "| 47| 11|        Júlia|\n",
      "| 35| 12|       Lorena|\n",
      "| 32| 13|        Lívia|\n",
      "| 90| 14|  Maria Luiza|\n",
      "| 88| 15|      Cecília|\n",
      "| 36| 16|         Eloá|\n",
      "| 57| 17|     Giovanna|\n",
      "| 75| 18|  Maria Clara|\n",
      "| 97| 19|Maria Eduarda|\n",
      "| 65| 20|      Mariana|\n",
      "+---+---+-------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_hive.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 239,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_hive_table = spark.read.table('default.maior')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 240,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---+-------------+\n",
      "|age| id|         name|\n",
      "+---+---+-------------+\n",
      "| 28|  1|       Helena|\n",
      "| 44|  2|       Miguel|\n",
      "| 97|  3|        Alice|\n",
      "| 28|  4|        Laura|\n",
      "| 94|  5|      Manuela|\n",
      "| 71|  6|    Valentina|\n",
      "| 48|  7|       Sophia|\n",
      "| 98|  8|     Isabella|\n",
      "| 60|  9|      Heloísa|\n",
      "| 46| 10|        Luiza|\n",
      "| 47| 11|        Júlia|\n",
      "| 35| 12|       Lorena|\n",
      "| 32| 13|        Lívia|\n",
      "| 90| 14|  Maria Luiza|\n",
      "| 88| 15|      Cecília|\n",
      "| 36| 16|         Eloá|\n",
      "| 57| 17|     Giovanna|\n",
      "| 75| 18|  Maria Clara|\n",
      "| 97| 19|Maria Eduarda|\n",
      "| 65| 20|      Mariana|\n",
      "+---+---+-------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_hive_table.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### JSON x CSV x Parquet x ORC x Hive"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 242,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---+-------------+\n",
      "|age| id|         name|\n",
      "+---+---+-------------+\n",
      "| 28|  1|       Helena|\n",
      "| 44|  2|       Miguel|\n",
      "| 97|  3|        Alice|\n",
      "| 28|  4|        Laura|\n",
      "| 94|  5|      Manuela|\n",
      "| 71|  6|    Valentina|\n",
      "| 48|  7|       Sophia|\n",
      "| 98|  8|     Isabella|\n",
      "| 60|  9|      Heloísa|\n",
      "| 46| 10|        Luiza|\n",
      "| 47| 11|        Júlia|\n",
      "| 35| 12|       Lorena|\n",
      "| 32| 13|        Lívia|\n",
      "| 90| 14|  Maria Luiza|\n",
      "| 88| 15|      Cecília|\n",
      "| 36| 16|         Eloá|\n",
      "| 57| 17|     Giovanna|\n",
      "| 75| 18|  Maria Clara|\n",
      "| 97| 19|Maria Eduarda|\n",
      "| 65| 20|      Mariana|\n",
      "+---+---+-------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_json.union(df_csv.union(df_parquet.union(df_orc.union(df_hive)))).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 251,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_rename = df_maior.withColumnRenamed('age','Idade') \\\n",
    "    .withColumnRenamed('id','Chave') \\\n",
    "    .withColumnRenamed('Name','Nome')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 253,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----+-----+-------------+\n",
      "|Idade|Chave|         Nome|\n",
      "+-----+-----+-------------+\n",
      "|   28|    1|       Helena|\n",
      "|   44|    2|       Miguel|\n",
      "|   97|    3|        Alice|\n",
      "|   28|    4|        Laura|\n",
      "|   94|    5|      Manuela|\n",
      "|   71|    6|    Valentina|\n",
      "|   48|    7|       Sophia|\n",
      "|   98|    8|     Isabella|\n",
      "|   60|    9|      Heloísa|\n",
      "|   46|   10|        Luiza|\n",
      "|   47|   11|        Júlia|\n",
      "|   35|   12|       Lorena|\n",
      "|   32|   13|        Lívia|\n",
      "|   90|   14|  Maria Luiza|\n",
      "|   88|   15|      Cecília|\n",
      "|   36|   16|         Eloá|\n",
      "|   57|   17|     Giovanna|\n",
      "|   75|   18|  Maria Clara|\n",
      "|   97|   19|Maria Eduarda|\n",
      "|   65|   20|      Mariana|\n",
      "+-----+-----+-------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_rename.union(df_csv.union(df_parquet.union(df_orc.union(df_hive.union(df_json))))).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 248,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DataFrame[age: bigint, id: bigint, name: string]"
      ]
     },
     "execution_count": 248,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_hive"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 255,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_torto = df_json.select('name','age','id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 256,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------+---+---+\n",
      "|         name|age| id|\n",
      "+-------------+---+---+\n",
      "|       Helena| 28|  1|\n",
      "|       Miguel| 44|  2|\n",
      "|        Alice| 97|  3|\n",
      "|        Laura| 28|  4|\n",
      "|      Manuela| 94|  5|\n",
      "|    Valentina| 71|  6|\n",
      "|       Sophia| 48|  7|\n",
      "|     Isabella| 98|  8|\n",
      "|      Heloísa| 60|  9|\n",
      "|        Luiza| 46| 10|\n",
      "|        Júlia| 47| 11|\n",
      "|       Lorena| 35| 12|\n",
      "|        Lívia| 32| 13|\n",
      "|  Maria Luiza| 90| 14|\n",
      "|      Cecília| 88| 15|\n",
      "|         Eloá| 36| 16|\n",
      "|     Giovanna| 57| 17|\n",
      "|  Maria Clara| 75| 18|\n",
      "|Maria Eduarda| 97| 19|\n",
      "|      Mariana| 65| 20|\n",
      "+-------------+---+---+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_torto.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 261,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_retorn = df_rename.union(df_torto)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 262,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DataFrame[Idade: string, Chave: bigint, Nome: string]"
      ]
     },
     "execution_count": 262,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_retorn "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 263,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_mysql = spark.read \\\n",
    "    .format(\"jdbc\") \\\n",
    "    .option(\"url\",\"jdbc:mysql://database/employees\") \\\n",
    "    .option(\"driver\",\"com.mysql.jdbc.Driver\") \\\n",
    "    .option(\"dbtable\",\"employees\") \\\n",
    "    .option(\"user\",\"root\") \\\n",
    "    .option(\"password\",\"secret\") \\\n",
    "    .load()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 265,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "300024"
      ]
     },
     "execution_count": 265,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_mysql.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 266,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+----------+----------+-----------+------+----------+\n",
      "|emp_no|birth_date|first_name|  last_name|gender| hire_date|\n",
      "+------+----------+----------+-----------+------+----------+\n",
      "| 10001|1953-09-02|    Georgi|    Facello|     M|1986-06-26|\n",
      "| 10002|1964-06-02|   Bezalel|     Simmel|     F|1985-11-21|\n",
      "| 10003|1959-12-03|     Parto|    Bamford|     M|1986-08-28|\n",
      "| 10004|1954-05-01| Chirstian|    Koblick|     M|1986-12-01|\n",
      "| 10005|1955-01-21|   Kyoichi|   Maliniak|     M|1989-09-12|\n",
      "| 10006|1953-04-20|    Anneke|    Preusig|     F|1989-06-02|\n",
      "| 10007|1957-05-23|   Tzvetan|  Zielinski|     F|1989-02-10|\n",
      "| 10008|1958-02-19|    Saniya|   Kalloufi|     M|1994-09-15|\n",
      "| 10009|1952-04-19|    Sumant|       Peac|     F|1985-02-18|\n",
      "| 10010|1963-06-01| Duangkaew|   Piveteau|     F|1989-08-24|\n",
      "| 10011|1953-11-07|      Mary|      Sluis|     F|1990-01-22|\n",
      "| 10012|1960-10-04|  Patricio|  Bridgland|     M|1992-12-18|\n",
      "| 10013|1963-06-07| Eberhardt|     Terkki|     M|1985-10-20|\n",
      "| 10014|1956-02-12|     Berni|      Genin|     M|1987-03-11|\n",
      "| 10015|1959-08-19|  Guoxiang|  Nooteboom|     M|1987-07-02|\n",
      "| 10016|1961-05-02|  Kazuhito|Cappelletti|     M|1995-01-27|\n",
      "| 10017|1958-07-06| Cristinel|  Bouloucos|     F|1993-08-03|\n",
      "| 10018|1954-06-19|  Kazuhide|       Peha|     F|1987-04-03|\n",
      "| 10019|1953-01-23|   Lillian|    Haddadi|     M|1999-04-30|\n",
      "| 10020|1952-12-24|    Mayuko|    Warwick|     M|1991-01-26|\n",
      "+------+----------+----------+-----------+------+----------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_mysql.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 268,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_mysql.write.mode('overwrite').format('hive').saveAsTable('default.employees')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 269,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_mysql2 = df_mysql.filter(df_mysql.emp_no > 10017)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 271,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "300007"
      ]
     },
     "execution_count": 271,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_mysql2.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 272,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_mysql_union = df_mysql2.unionAll(df_mysql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 282,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "600031"
      ]
     },
     "execution_count": 282,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_mysql_union.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 278,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DataFrame[emp_no: int, birth_date: date, first_name: string, last_name: string, gender: string, hire_date: date]"
      ]
     },
     "execution_count": 278,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_mysql_union.cache()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 283,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+----------+----------+----------+------+----------+\n",
      "|emp_no|birth_date|first_name| last_name|gender| hire_date|\n",
      "+------+----------+----------+----------+------+----------+\n",
      "| 10018|1954-06-19|  Kazuhide|      Peha|     F|1987-04-03|\n",
      "| 10019|1953-01-23|   Lillian|   Haddadi|     M|1999-04-30|\n",
      "| 10020|1952-12-24|    Mayuko|   Warwick|     M|1991-01-26|\n",
      "| 10021|1960-02-20|     Ramzi|      Erde|     M|1988-02-10|\n",
      "| 10022|1952-07-08|    Shahaf|    Famili|     M|1995-08-22|\n",
      "| 10023|1953-09-29|     Bojan|Montemayor|     F|1989-12-17|\n",
      "| 10024|1958-09-05|   Suzette|    Pettey|     F|1997-05-19|\n",
      "| 10025|1958-10-31| Prasadram|    Heyers|     M|1987-08-17|\n",
      "| 10026|1953-04-03|  Yongqiao|  Berztiss|     M|1995-03-20|\n",
      "| 10027|1962-07-10|    Divier|   Reistad|     F|1989-07-07|\n",
      "| 10028|1963-11-26|  Domenick|  Tempesti|     M|1991-10-22|\n",
      "| 10029|1956-12-13|     Otmar|    Herbst|     M|1985-11-20|\n",
      "| 10030|1958-07-14|     Elvis|   Demeyer|     M|1994-02-17|\n",
      "| 10031|1959-01-27|   Karsten|    Joslin|     M|1991-09-01|\n",
      "| 10032|1960-08-09|     Jeong|   Reistad|     F|1990-06-20|\n",
      "| 10033|1956-11-14|      Arif|     Merlo|     M|1987-03-18|\n",
      "| 10034|1962-12-29|     Bader|      Swan|     M|1988-09-21|\n",
      "| 10035|1953-02-08|     Alain| Chappelet|     M|1988-09-05|\n",
      "| 10036|1959-08-10|Adamantios| Portugali|     M|1992-01-03|\n",
      "| 10037|1963-07-22|   Pradeep|  Makrucki|     M|1990-12-05|\n",
      "+------+----------+----------+----------+------+----------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_mysql_union.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 286,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_maior.write.json('file:/mnt/notebooks/json')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 287,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_maior.write.json('hdfs:/mnt/notebooks/json')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "PySpark",
   "language": "python",
   "name": "pyspark"
  },
  "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.6.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}