TESTEPYSPARK_Confitec.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "collapsed_sections": [],
      "authorship_tag": "ABX9TyPlZoGWBnoUCVrxRhCgpu/D",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/rwurdig/TESTEPYSPARK-Confitec/blob/main/TESTEPYSPARK_Confitec.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "!pip install pyspark py4j"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "om23sIBnpND-",
        "outputId": "44f1ecfd-3526-40dc-eb5f-5d1619128f2a"
      },
      "execution_count": 3,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/\n",
            "Collecting pyspark\n",
            "  Downloading pyspark-3.3.1.tar.gz (281.4 MB)\n",
            "\u001b[K     |████████████████████████████████| 281.4 MB 44 kB/s \n",
            "\u001b[?25hCollecting py4j\n",
            "  Downloading py4j-0.10.9.7-py2.py3-none-any.whl (200 kB)\n",
            "\u001b[K     |████████████████████████████████| 200 kB 35.9 MB/s \n",
            "\u001b[?25h  Downloading py4j-0.10.9.5-py2.py3-none-any.whl (199 kB)\n",
            "\u001b[K     |████████████████████████████████| 199 kB 36.1 MB/s \n",
            "\u001b[?25hBuilding wheels for collected packages: pyspark\n",
            "  Building wheel for pyspark (setup.py) ... \u001b[?25l\u001b[?25hdone\n",
            "  Created wheel for pyspark: filename=pyspark-3.3.1-py2.py3-none-any.whl size=281845513 sha256=d3e450a1c7208a05f7439c6c32a79173a84cdeec356ebe0c7ff6bba9f8574a7d\n",
            "  Stored in directory: /root/.cache/pip/wheels/42/59/f5/79a5bf931714dcd201b26025347785f087370a10a3329a899c\n",
            "Successfully built pyspark\n",
            "Installing collected packages: py4j, pyspark\n",
            "Successfully installed py4j-0.10.9.5 pyspark-3.3.1\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 173,
      "metadata": {
        "id": "KE43TdYkntyz"
      },
      "outputs": [],
      "source": [
        "# Instalando Bibliotecas\n",
        "\n",
        "from pyspark.sql import SparkSession\n",
        "from  pyspark.sql.functions import *\n",
        "from pyspark.sql import types\n",
        "from pyspark.sql.functions import when, col\n",
        "from pyspark.sql.functions import udf, to_timestamp, lower, regexp_replace, lit, concat, col\n",
        "from pyspark.sql.types import TimestampType\n",
        "from pyspark.sql.types import *\n",
        "from datetime import datetime\n",
        "from tqdm import tqdm\n",
        "import pandas as pd\n",
        "import os\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Setup"
      ],
      "metadata": {
        "id": "C-zs3ZnCrrbv"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "\n",
        "\n",
        "#Definicacao do Spark Session\n",
        "spark = SparkSession \\\n",
        "    .builder \\\n",
        "    .appName(\"Confitec\") \\\n",
        "    .enableHiveSupport() \\\n",
        "    .config(\"spark.executor.heartbeatInterval\",\"8000\") \\\n",
        "    .getOrCreate()\n",
        "\t\n",
        " "
      ],
      "metadata": {
        "id": "exL6u-K29zXI"
      },
      "execution_count": 167,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#Leitura do arquivo Parquet\n",
        "\n",
        "df = spark.read.parquet(\"OriginaisNetflix.parquet\")\n",
        "df.printSchema()\n",
        "df.first().asDict()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "vgYxsiqQrpWl",
        "outputId": "495c4a2c-6380-4f0f-dbd7-160aa1351c01"
      },
      "execution_count": 168,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- Title: string (nullable = true)\n",
            " |-- Genre: string (nullable = true)\n",
            " |-- GenreLabels: string (nullable = true)\n",
            " |-- Premiere: string (nullable = true)\n",
            " |-- Seasons: string (nullable = true)\n",
            " |-- SeasonsParsed: long (nullable = true)\n",
            " |-- EpisodesParsed: long (nullable = true)\n",
            " |-- Length: string (nullable = true)\n",
            " |-- MinLength: long (nullable = true)\n",
            " |-- MaxLength: long (nullable = true)\n",
            " |-- Status: string (nullable = true)\n",
            " |-- Active: long (nullable = true)\n",
            " |-- Table: string (nullable = true)\n",
            " |-- Language: string (nullable = true)\n",
            " |-- dt_inclusao: string (nullable = true)\n",
            "\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "{'Title': 'House of Cards',\n",
              " 'Genre': 'Political drama',\n",
              " 'GenreLabels': 'political,drama',\n",
              " 'Premiere': '1-Feb-13',\n",
              " 'Seasons': '6 seasons, 73 episodes',\n",
              " 'SeasonsParsed': 6,\n",
              " 'EpisodesParsed': 73,\n",
              " 'Length': '42–59 min.',\n",
              " 'MinLength': 42,\n",
              " 'MaxLength': 59,\n",
              " 'Status': 'Ended',\n",
              " 'Active': 0,\n",
              " 'Table': 'Drama',\n",
              " 'Language': 'English',\n",
              " 'dt_inclusao': '2021-03-16T21:20:24.167-03:00'}"
            ]
          },
          "metadata": {},
          "execution_count": 168
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Questão 01 - Transformar os campos \"Premiere\" e \"dt_inclusao\" de string para datetime."
      ],
      "metadata": {
        "id": "rf0Tjd6fn1wc"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "df = df.withColumn('mes', substring('Premiere', -6,3)).withColumn('ano', substring('Premiere', -2,3)).withColumn('dia', substring('Premiere', 1,2))\n",
        "df = df.withColumn(\"dia\", regexp_replace(col(\"dia\"), \"\"\"-\"\"\", \"\"))\n",
        "\n",
        "meses = {'Jan': '01',\n",
        "         'Feb': '02',\n",
        "         'Mar': '03',\n",
        "         'Apr': '04',\n",
        "         'May': '05',\n",
        "         'Jun': '06',\n",
        "         'Jul': '07',\n",
        "         'Aug': '08',\n",
        "         'Sep': '09',\n",
        "         'Oct': '10',\n",
        "         'Nov': '11',\n",
        "         'Dec': '12'}\n",
        "\n",
        "df = df.replace(meses,subset=[\"mes\"])\n",
        "df  = df.withColumn(\"Premiere\",concat(lit(\"20\"),col(\"ano\"),lit(\"-\"),col(\"mes\"),lit(\"-\"),col(\"dia\")))\n",
        "\n",
        "df = df.withColumn(\"Premiere\",date_format('Premiere', 'yyy-MM-dd'))\n",
        "df.withColumn(\"dt_inclusao\",to_date(col(\"dt_inclusao\")))\n",
        "df =  df.withColumn(\"Premiere\",to_date(col(\"dt_inclusao\")))"
      ],
      "metadata": {
        "id": "H7B7ujjErCBN"
      },
      "execution_count": 170,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "df.printSchema()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "JpyrHep9n10K",
        "outputId": "4b9eac0a-cd9f-4e62-a908-0d4591e3d7e0"
      },
      "execution_count": 171,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- Title: string (nullable = true)\n",
            " |-- Genre: string (nullable = true)\n",
            " |-- GenreLabels: string (nullable = true)\n",
            " |-- Premiere: date (nullable = true)\n",
            " |-- Seasons: string (nullable = true)\n",
            " |-- SeasonsParsed: long (nullable = true)\n",
            " |-- EpisodesParsed: long (nullable = true)\n",
            " |-- Length: string (nullable = true)\n",
            " |-- MinLength: long (nullable = true)\n",
            " |-- MaxLength: long (nullable = true)\n",
            " |-- Status: string (nullable = true)\n",
            " |-- Active: long (nullable = true)\n",
            " |-- Table: string (nullable = true)\n",
            " |-- Language: string (nullable = true)\n",
            " |-- dt_inclusao: string (nullable = true)\n",
            " |-- mes: string (nullable = true)\n",
            " |-- ano: string (nullable = true)\n",
            " |-- dia: string (nullable = true)\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Questão 02 - Ordenar os dados por ativos e gênero de forma decrescente, 0 = inativo e 1 = ativo, todos com número 1 devem aparecer primeiro.\n",
        "\n",
        "df_02 = df.sort(col(\"Active\").desc(),col(\"Genre\").asc())\n",
        "df_02.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "DWoN9DMVprDP",
        "outputId": "2b5715e4-5b21-448a-8cef-3f471651f008"
      },
      "execution_count": 174,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------+------------------+------------------+----------+--------------------+-------------+--------------+----------+---------+---------+-------+------+---------------+--------+--------------------+---+---+---+\n",
            "|               Title|             Genre|       GenreLabels|  Premiere|             Seasons|SeasonsParsed|EpisodesParsed|    Length|MinLength|MaxLength| Status|Active|          Table|Language|         dt_inclusao|mes|ano|dia|\n",
            "+--------------------+------------------+------------------+----------+--------------------+-------------+--------------+----------+---------+---------+-------+------+---------------+--------+--------------------+---+---+---+\n",
            "|Knights of the Zo...|            Action|            action|2021-03-16|                 TBA|            0|             0|       TBA|        0|        0|Pending|     1|          Anime| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|       Kengan Ashura|            Action|            action|2021-03-16|                 TBA|            0|             0|       TBA|        0|        0|Pending|     1|          Anime| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|Beyond Stranger T...|         Aftershow|         aftershow|2021-03-16|1 season, 7 episodes|            1|             7|15–25 min.|       15|       25|Pending|     1|    VarietyTalk| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|Love, Death & Robots|         Anthology|         anthology|2021-03-16|1 volume, 18 epis...|            0|            18| 6–17 min.|        6|       17|Renewed|     1|      Animation| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|          Sugar Rush|Baking competition|baking,competition|2021-03-16|1 season, 8 episodes|            1|             8|51–55 min.|       51|       55|Renewed|     1|        Reality| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|     BoJack Horseman|Black comedy-drama|black,comedy-drama|2021-03-16|5 seasons, 61 epi...|            5|            61|25–26 min.|       25|       26|Renewed|     1|      Animation| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|       Dancing Queen|         Celebrity|         celebrity|2021-03-16|1 season, 8 episodes|            1|             8|38–46 min.|       38|       46|Pending|     1|        Reality| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|      The Politician|            Comedy|            comedy|2021-03-16|                 TBA|            0|             0|       TBA|        0|        0|Renewed|     1|         Comedy| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|          After Life|            Comedy|            comedy|2021-03-16|1 season, 6 episodes|            1|             6|25–31 min.|       25|       31|Renewed|     1|         Comedy| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|                GLOW|            Comedy|            comedy|2021-03-16|2 seasons, 20 epi...|            2|            20|27–46 min.|       27|       46|Renewed|     1|         Comedy| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "| The Kominsky Method|            Comedy|            comedy|2021-03-16|1 season, 8 episodes|            1|             8|23–34 min.|       23|       34|Renewed|     1|         Comedy| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|         It's Bruno!|            Comedy|            comedy|2021-03-16|1 season, 8 episodes|            1|             8|12–21 min.|       12|       21|Pending|     1|         Comedy| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|YG Future Strateg...|            Comedy|            comedy|2021-03-16|1 season, 8 episodes|            1|             8|20–29 min.|       20|       29|Pending|     1|ForeignLanguage|  Korean|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|       Tuca & Bertie|            Comedy|            comedy|2021-03-16|1 season, 10 epis...|            1|            10|25–26 min.|       25|       26|Pending|     1|      Animation| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|  Family Reunion[64]|            Comedy|            comedy|2021-03-16|                 TBA|            0|             0|       TBA|        0|        0|Pending|     1|         Comedy| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|        Russian Doll|            Comedy|            comedy|2021-03-16|1 season, 8 episodes|            1|             8|25–30 min.|       25|       30|Renewed|     1|         Comedy| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|Malibu Rescue: Th...|            Comedy|            comedy|2021-03-16|1 season, 8 episodes|            1|             8|22–26 min.|       22|       26|Pending|     1|       Children| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|     Turn Up Charlie|            Comedy|            comedy|2021-03-16|1 season, 8 episodes|            1|             8|25–29 min.|       25|       29|Pending|     1|         Comedy| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|          Aggretsuko|            Comedy|            comedy|2021-03-16|2 seasons, 20 epi...|            2|            20|15–20 min.|       15|       20|Pending|     1|          Anime| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|      Huge in France|            Comedy|            comedy|2021-03-16|1 season, 8 episodes|            1|             8|28–34 min.|       28|       34|Pending|     1|         Comedy| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "+--------------------+------------------+------------------+----------+--------------------+-------------+--------------+----------+---------+---------+-------+------+---------------+--------+--------------------+---+---+---+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Questão 03 - #Remover linhas duplicadas e trocar o resultado das linhas que tiverem a coluna \"Seasons\" de \"TBA\" para \"a ser anunciado\".\n",
        "\n",
        "print(f'Dataframe before drop duplicates: {df_02.toPandas().shape}')\n",
        "df_03 = df_02.dropDuplicates()\n",
        "print(f'Dataframe after drop duplicates {df_02.toPandas().shape}')\n",
        "df_03 = df_02.withColumn(\"Seasons\", when(col(\"Seasons\") == \"TBA\", \"a ser anunciado\").otherwise(col(\"Seasons\")))\n",
        "print(f'Number rows \"A ser anunciado:\" {df_02.filter(col(\"Seasons\") == \"a ser anunciado\").count()}')\n",
        "df_03.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "BtEJhEAEwCQ6",
        "outputId": "3cb4368f-68e0-452f-8be3-b0128b2e7076"
      },
      "execution_count": 175,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Dataframe before drop duplicates: (358, 18)\n",
            "Dataframe after drop duplicates (358, 18)\n",
            "Number rows \"A ser anunciado:\" 0\n",
            "+--------------------+------------------+------------------+----------+--------------------+-------------+--------------+----------+---------+---------+-------+------+---------------+--------+--------------------+---+---+---+\n",
            "|               Title|             Genre|       GenreLabels|  Premiere|             Seasons|SeasonsParsed|EpisodesParsed|    Length|MinLength|MaxLength| Status|Active|          Table|Language|         dt_inclusao|mes|ano|dia|\n",
            "+--------------------+------------------+------------------+----------+--------------------+-------------+--------------+----------+---------+---------+-------+------+---------------+--------+--------------------+---+---+---+\n",
            "|Knights of the Zo...|            Action|            action|2021-03-16|     a ser anunciado|            0|             0|       TBA|        0|        0|Pending|     1|          Anime| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|       Kengan Ashura|            Action|            action|2021-03-16|     a ser anunciado|            0|             0|       TBA|        0|        0|Pending|     1|          Anime| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|Beyond Stranger T...|         Aftershow|         aftershow|2021-03-16|1 season, 7 episodes|            1|             7|15–25 min.|       15|       25|Pending|     1|    VarietyTalk| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|Love, Death & Robots|         Anthology|         anthology|2021-03-16|1 volume, 18 epis...|            0|            18| 6–17 min.|        6|       17|Renewed|     1|      Animation| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|          Sugar Rush|Baking competition|baking,competition|2021-03-16|1 season, 8 episodes|            1|             8|51–55 min.|       51|       55|Renewed|     1|        Reality| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|     BoJack Horseman|Black comedy-drama|black,comedy-drama|2021-03-16|5 seasons, 61 epi...|            5|            61|25–26 min.|       25|       26|Renewed|     1|      Animation| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|       Dancing Queen|         Celebrity|         celebrity|2021-03-16|1 season, 8 episodes|            1|             8|38–46 min.|       38|       46|Pending|     1|        Reality| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|      The Politician|            Comedy|            comedy|2021-03-16|     a ser anunciado|            0|             0|       TBA|        0|        0|Renewed|     1|         Comedy| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|          After Life|            Comedy|            comedy|2021-03-16|1 season, 6 episodes|            1|             6|25–31 min.|       25|       31|Renewed|     1|         Comedy| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|                GLOW|            Comedy|            comedy|2021-03-16|2 seasons, 20 epi...|            2|            20|27–46 min.|       27|       46|Renewed|     1|         Comedy| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "| The Kominsky Method|            Comedy|            comedy|2021-03-16|1 season, 8 episodes|            1|             8|23–34 min.|       23|       34|Renewed|     1|         Comedy| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|         It's Bruno!|            Comedy|            comedy|2021-03-16|1 season, 8 episodes|            1|             8|12–21 min.|       12|       21|Pending|     1|         Comedy| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|YG Future Strateg...|            Comedy|            comedy|2021-03-16|1 season, 8 episodes|            1|             8|20–29 min.|       20|       29|Pending|     1|ForeignLanguage|  Korean|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|       Tuca & Bertie|            Comedy|            comedy|2021-03-16|1 season, 10 epis...|            1|            10|25–26 min.|       25|       26|Pending|     1|      Animation| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|  Family Reunion[64]|            Comedy|            comedy|2021-03-16|     a ser anunciado|            0|             0|       TBA|        0|        0|Pending|     1|         Comedy| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|        Russian Doll|            Comedy|            comedy|2021-03-16|1 season, 8 episodes|            1|             8|25–30 min.|       25|       30|Renewed|     1|         Comedy| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|Malibu Rescue: Th...|            Comedy|            comedy|2021-03-16|1 season, 8 episodes|            1|             8|22–26 min.|       22|       26|Pending|     1|       Children| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|     Turn Up Charlie|            Comedy|            comedy|2021-03-16|1 season, 8 episodes|            1|             8|25–29 min.|       25|       29|Pending|     1|         Comedy| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|          Aggretsuko|            Comedy|            comedy|2021-03-16|2 seasons, 20 epi...|            2|            20|15–20 min.|       15|       20|Pending|     1|          Anime| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "|      Huge in France|            Comedy|            comedy|2021-03-16|1 season, 8 episodes|            1|             8|28–34 min.|       28|       34|Pending|     1|         Comedy| English|2021-03-16T21:20:...|-03| 16| 20|\n",
            "+--------------------+------------------+------------------+----------+--------------------+-------------+--------------+----------+---------+---------+-------+------+---------------+--------+--------------------+---+---+---+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Questão 04 - Criar uma coluna nova chamada \"Data de Alteração\" e dentro dela um timestamp.\n",
        "\n",
        "df_04 = df_03.withColumn('Data de Alteração',current_timestamp())\n",
        "df_04.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "7nTbv9KDprL8",
        "outputId": "32559345-257e-4ec8-dd2a-ff88ca684601"
      },
      "execution_count": 176,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------+------------------+------------------+----------+--------------------+-------------+--------------+----------+---------+---------+-------+------+---------------+--------+--------------------+---+---+---+--------------------+\n",
            "|               Title|             Genre|       GenreLabels|  Premiere|             Seasons|SeasonsParsed|EpisodesParsed|    Length|MinLength|MaxLength| Status|Active|          Table|Language|         dt_inclusao|mes|ano|dia|   Data de Alteração|\n",
            "+--------------------+------------------+------------------+----------+--------------------+-------------+--------------+----------+---------+---------+-------+------+---------------+--------+--------------------+---+---+---+--------------------+\n",
            "|Knights of the Zo...|            Action|            action|2021-03-16|     a ser anunciado|            0|             0|       TBA|        0|        0|Pending|     1|          Anime| English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:03:...|\n",
            "|       Kengan Ashura|            Action|            action|2021-03-16|     a ser anunciado|            0|             0|       TBA|        0|        0|Pending|     1|          Anime| English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:03:...|\n",
            "|Beyond Stranger T...|         Aftershow|         aftershow|2021-03-16|1 season, 7 episodes|            1|             7|15–25 min.|       15|       25|Pending|     1|    VarietyTalk| English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:03:...|\n",
            "|Love, Death & Robots|         Anthology|         anthology|2021-03-16|1 volume, 18 epis...|            0|            18| 6–17 min.|        6|       17|Renewed|     1|      Animation| English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:03:...|\n",
            "|          Sugar Rush|Baking competition|baking,competition|2021-03-16|1 season, 8 episodes|            1|             8|51–55 min.|       51|       55|Renewed|     1|        Reality| English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:03:...|\n",
            "|     BoJack Horseman|Black comedy-drama|black,comedy-drama|2021-03-16|5 seasons, 61 epi...|            5|            61|25–26 min.|       25|       26|Renewed|     1|      Animation| English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:03:...|\n",
            "|       Dancing Queen|         Celebrity|         celebrity|2021-03-16|1 season, 8 episodes|            1|             8|38–46 min.|       38|       46|Pending|     1|        Reality| English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:03:...|\n",
            "|      The Politician|            Comedy|            comedy|2021-03-16|     a ser anunciado|            0|             0|       TBA|        0|        0|Renewed|     1|         Comedy| English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:03:...|\n",
            "|          After Life|            Comedy|            comedy|2021-03-16|1 season, 6 episodes|            1|             6|25–31 min.|       25|       31|Renewed|     1|         Comedy| English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:03:...|\n",
            "|                GLOW|            Comedy|            comedy|2021-03-16|2 seasons, 20 epi...|            2|            20|27–46 min.|       27|       46|Renewed|     1|         Comedy| English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:03:...|\n",
            "| The Kominsky Method|            Comedy|            comedy|2021-03-16|1 season, 8 episodes|            1|             8|23–34 min.|       23|       34|Renewed|     1|         Comedy| English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:03:...|\n",
            "|         It's Bruno!|            Comedy|            comedy|2021-03-16|1 season, 8 episodes|            1|             8|12–21 min.|       12|       21|Pending|     1|         Comedy| English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:03:...|\n",
            "|YG Future Strateg...|            Comedy|            comedy|2021-03-16|1 season, 8 episodes|            1|             8|20–29 min.|       20|       29|Pending|     1|ForeignLanguage|  Korean|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:03:...|\n",
            "|       Tuca & Bertie|            Comedy|            comedy|2021-03-16|1 season, 10 epis...|            1|            10|25–26 min.|       25|       26|Pending|     1|      Animation| English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:03:...|\n",
            "|  Family Reunion[64]|            Comedy|            comedy|2021-03-16|     a ser anunciado|            0|             0|       TBA|        0|        0|Pending|     1|         Comedy| English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:03:...|\n",
            "|        Russian Doll|            Comedy|            comedy|2021-03-16|1 season, 8 episodes|            1|             8|25–30 min.|       25|       30|Renewed|     1|         Comedy| English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:03:...|\n",
            "|Malibu Rescue: Th...|            Comedy|            comedy|2021-03-16|1 season, 8 episodes|            1|             8|22–26 min.|       22|       26|Pending|     1|       Children| English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:03:...|\n",
            "|     Turn Up Charlie|            Comedy|            comedy|2021-03-16|1 season, 8 episodes|            1|             8|25–29 min.|       25|       29|Pending|     1|         Comedy| English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:03:...|\n",
            "|          Aggretsuko|            Comedy|            comedy|2021-03-16|2 seasons, 20 epi...|            2|            20|15–20 min.|       15|       20|Pending|     1|          Anime| English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:03:...|\n",
            "|      Huge in France|            Comedy|            comedy|2021-03-16|1 season, 8 episodes|            1|             8|28–34 min.|       28|       34|Pending|     1|         Comedy| English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:03:...|\n",
            "+--------------------+------------------+------------------+----------+--------------------+-------------+--------------+----------+---------+---------+-------+------+---------------+--------+--------------------+---+---+---+--------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df_04.printSchema()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "bjCFHkTGp8Gj",
        "outputId": "66c64707-899b-4cb8-dbc4-4fea8113869e"
      },
      "execution_count": 177,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- Title: string (nullable = true)\n",
            " |-- Genre: string (nullable = true)\n",
            " |-- GenreLabels: string (nullable = true)\n",
            " |-- Premiere: date (nullable = true)\n",
            " |-- Seasons: string (nullable = true)\n",
            " |-- SeasonsParsed: long (nullable = true)\n",
            " |-- EpisodesParsed: long (nullable = true)\n",
            " |-- Length: string (nullable = true)\n",
            " |-- MinLength: long (nullable = true)\n",
            " |-- MaxLength: long (nullable = true)\n",
            " |-- Status: string (nullable = true)\n",
            " |-- Active: long (nullable = true)\n",
            " |-- Table: string (nullable = true)\n",
            " |-- Language: string (nullable = true)\n",
            " |-- dt_inclusao: string (nullable = true)\n",
            " |-- mes: string (nullable = true)\n",
            " |-- ano: string (nullable = true)\n",
            " |-- dia: string (nullable = true)\n",
            " |-- Data de Alteração: timestamp (nullable = false)\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Questão 05 - Trocar os nomes das colunas de inglês para português, exemplo: \"Title\" para \"Título\" (com acentuação).\n",
        "\n",
        "df_05 = df_04.withColumnRenamed(\"Title\", \"Título\") \\\n",
        "            .withColumnRenamed(\"Genre\",\"Gênero\") \\\n",
        "            .withColumnRenamed(\"GenreLabels\", \"Rótulos do Gênero\") \\\n",
        "            .withColumnRenamed(\"Premiere\", \"Pré-estreia\") \\\n",
        "            .withColumnRenamed(\"Seasons\", \"Temporadas\") \\\n",
        "            .withColumnRenamed(\"SeasonsParsed\", \"Temporadas Analisadas\") \\\n",
        "            .withColumnRenamed(\"EpisodesParsed\", \"Episódios Analisados\") \\\n",
        "            .withColumnRenamed(\"Length\", \"Tamanho\") \\\n",
        "            .withColumnRenamed(\"MinLength\", \"Tamanho Mínimo\") \\\n",
        "            .withColumnRenamed(\"MaxLength\", \"Tamanho Máximo\") \\\n",
        "            .withColumnRenamed(\"Status\", \"Status\") \\\n",
        "              .withColumnRenamed(\"Active\", \"Ativo\") \\\n",
        "            .withColumnRenamed(\"Table\", \"Tabela\") \\\n",
        "            .withColumnRenamed(\"Language\", \"Linguagem\") \\\n",
        "            .withColumnRenamed(\"dt_inclusao\", \"Data de Inclusão\") "
      ],
      "metadata": {
        "id": "jMetDK8Mp8Ie"
      },
      "execution_count": 178,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "df_05.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "3MluXU8y3s_8",
        "outputId": "8ac6cd97-0a86-4553-9972-82680a69c1ec"
      },
      "execution_count": 179,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------+------------------+------------------+-----------+--------------------+---------------------+--------------------+----------+--------------+--------------+-------+-----+---------------+---------+--------------------+---+---+---+--------------------+\n",
            "|              Título|            Gênero| Rótulos do Gênero|Pré-estreia|          Temporadas|Temporadas Analisadas|Episódios Analisados|   Tamanho|Tamanho Mínimo|Tamanho Máximo| Status|Ativo|         Tabela|Linguagem|    Data de Inclusão|mes|ano|dia|   Data de Alteração|\n",
            "+--------------------+------------------+------------------+-----------+--------------------+---------------------+--------------------+----------+--------------+--------------+-------+-----+---------------+---------+--------------------+---+---+---+--------------------+\n",
            "|Knights of the Zo...|            Action|            action| 2021-03-16|     a ser anunciado|                    0|                   0|       TBA|             0|             0|Pending|    1|          Anime|  English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:04:...|\n",
            "|       Kengan Ashura|            Action|            action| 2021-03-16|     a ser anunciado|                    0|                   0|       TBA|             0|             0|Pending|    1|          Anime|  English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:04:...|\n",
            "|Beyond Stranger T...|         Aftershow|         aftershow| 2021-03-16|1 season, 7 episodes|                    1|                   7|15–25 min.|            15|            25|Pending|    1|    VarietyTalk|  English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:04:...|\n",
            "|Love, Death & Robots|         Anthology|         anthology| 2021-03-16|1 volume, 18 epis...|                    0|                  18| 6–17 min.|             6|            17|Renewed|    1|      Animation|  English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:04:...|\n",
            "|          Sugar Rush|Baking competition|baking,competition| 2021-03-16|1 season, 8 episodes|                    1|                   8|51–55 min.|            51|            55|Renewed|    1|        Reality|  English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:04:...|\n",
            "|     BoJack Horseman|Black comedy-drama|black,comedy-drama| 2021-03-16|5 seasons, 61 epi...|                    5|                  61|25–26 min.|            25|            26|Renewed|    1|      Animation|  English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:04:...|\n",
            "|       Dancing Queen|         Celebrity|         celebrity| 2021-03-16|1 season, 8 episodes|                    1|                   8|38–46 min.|            38|            46|Pending|    1|        Reality|  English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:04:...|\n",
            "|      The Politician|            Comedy|            comedy| 2021-03-16|     a ser anunciado|                    0|                   0|       TBA|             0|             0|Renewed|    1|         Comedy|  English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:04:...|\n",
            "|          After Life|            Comedy|            comedy| 2021-03-16|1 season, 6 episodes|                    1|                   6|25–31 min.|            25|            31|Renewed|    1|         Comedy|  English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:04:...|\n",
            "|                GLOW|            Comedy|            comedy| 2021-03-16|2 seasons, 20 epi...|                    2|                  20|27–46 min.|            27|            46|Renewed|    1|         Comedy|  English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:04:...|\n",
            "| The Kominsky Method|            Comedy|            comedy| 2021-03-16|1 season, 8 episodes|                    1|                   8|23–34 min.|            23|            34|Renewed|    1|         Comedy|  English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:04:...|\n",
            "|         It's Bruno!|            Comedy|            comedy| 2021-03-16|1 season, 8 episodes|                    1|                   8|12–21 min.|            12|            21|Pending|    1|         Comedy|  English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:04:...|\n",
            "|YG Future Strateg...|            Comedy|            comedy| 2021-03-16|1 season, 8 episodes|                    1|                   8|20–29 min.|            20|            29|Pending|    1|ForeignLanguage|   Korean|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:04:...|\n",
            "|       Tuca & Bertie|            Comedy|            comedy| 2021-03-16|1 season, 10 epis...|                    1|                  10|25–26 min.|            25|            26|Pending|    1|      Animation|  English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:04:...|\n",
            "|  Family Reunion[64]|            Comedy|            comedy| 2021-03-16|     a ser anunciado|                    0|                   0|       TBA|             0|             0|Pending|    1|         Comedy|  English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:04:...|\n",
            "|        Russian Doll|            Comedy|            comedy| 2021-03-16|1 season, 8 episodes|                    1|                   8|25–30 min.|            25|            30|Renewed|    1|         Comedy|  English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:04:...|\n",
            "|Malibu Rescue: Th...|            Comedy|            comedy| 2021-03-16|1 season, 8 episodes|                    1|                   8|22–26 min.|            22|            26|Pending|    1|       Children|  English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:04:...|\n",
            "|     Turn Up Charlie|            Comedy|            comedy| 2021-03-16|1 season, 8 episodes|                    1|                   8|25–29 min.|            25|            29|Pending|    1|         Comedy|  English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:04:...|\n",
            "|          Aggretsuko|            Comedy|            comedy| 2021-03-16|2 seasons, 20 epi...|                    2|                  20|15–20 min.|            15|            20|Pending|    1|          Anime|  English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:04:...|\n",
            "|      Huge in France|            Comedy|            comedy| 2021-03-16|1 season, 8 episodes|                    1|                   8|28–34 min.|            28|            34|Pending|    1|         Comedy|  English|2021-03-16T21:20:...|-03| 16| 20|2022-11-08 02:04:...|\n",
            "+--------------------+------------------+------------------+-----------+--------------------+---------------------+--------------------+----------+--------------+--------------+-------+-----+---------------+---------+--------------------+---+---+---+--------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df_05.printSchema()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "5ziPnc_Np8L7",
        "outputId": "a0bb1721-603d-4e7f-eb87-5df9e37b924b"
      },
      "execution_count": 180,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- Título: string (nullable = true)\n",
            " |-- Gênero: string (nullable = true)\n",
            " |-- Rótulos do Gênero: string (nullable = true)\n",
            " |-- Pré-estreia: date (nullable = true)\n",
            " |-- Temporadas: string (nullable = true)\n",
            " |-- Temporadas Analisadas: long (nullable = true)\n",
            " |-- Episódios Analisados: long (nullable = true)\n",
            " |-- Tamanho: string (nullable = true)\n",
            " |-- Tamanho Mínimo: long (nullable = true)\n",
            " |-- Tamanho Máximo: long (nullable = true)\n",
            " |-- Status: string (nullable = true)\n",
            " |-- Ativo: long (nullable = true)\n",
            " |-- Tabela: string (nullable = true)\n",
            " |-- Linguagem: string (nullable = true)\n",
            " |-- Data de Inclusão: string (nullable = true)\n",
            " |-- mes: string (nullable = true)\n",
            " |-- ano: string (nullable = true)\n",
            " |-- dia: string (nullable = true)\n",
            " |-- Data de Alteração: timestamp (nullable = false)\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Questão 06 - Testar e verificar se existe algum erro de processamento do spark e identificar onde pode ter ocorrido o erro.\n",
        "\n",
        "\n",
        "# - Entrar no prompt de comando (cmd) e executar o comando a seguir: spark-submit analise_pyspark.py"
      ],
      "metadata": {
        "id": "hr5F6R38p8OP"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Questão 07 - Criar apenas 1 . csv com as seguintes colunas que foram nomeadas anteriormente \"Title, Genre, Seasons, Premiere, Language, Active, Status, dt_inclusao, Data de Alteração\" as colunas devem estar em português com header e separadas por \";\".\n",
        "\n",
        "\n",
        "df_05.select(\n",
        "    \"Título\",\n",
        "    \"Gênero\",\n",
        "    \"Temporadas\",\n",
        "    \"Pré-estreia\",\n",
        "    \"Linguagem\",\n",
        "    \"Ativo\",\n",
        "    \"Status\",\n",
        "    \"Data de Inclusão\",\n",
        "    \"Data de Alteração\"\n",
        ").repartition(1).write.csv(\"export.csv\", sep=\";\", header=True, encoding='utf-8')\n"
      ],
      "metadata": {
        "id": "vKOba31g84SD"
      },
      "execution_count": 185,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Questão 08 - Inserir esse . csv dentro de um bucket do AWS s3"
      ],
      "metadata": {
        "id": "gKEsraKkprV1"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "import boto3\n",
        "\n",
        "# A chave foi mascarada por questões de segurança\n",
        "\n",
        "ACCESS_ID = \"AXXXXXXXXN7I5HIZ\"\n",
        "ACCESS_KEY = \"98ICVax0WAa9QXCVXCVn+CDSDFS65QY2h3A53v5SveXtG\"\n",
        "\n",
        "s3 = boto3.resource(\"s3\", aws_access_key_id=ACCESS_ID, aws_secret_access_key=ACCESS_KEY)"
      ],
      "metadata": {
        "id": "yA4z5XdzqS7V"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "for bucket in s3.buckets.all():\n",
        "    print(bucket.name)"
      ],
      "metadata": {
        "id": "0SDvgUBgqS-C"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "from glob import glob\n",
        "\n",
        "data = open(glob(\"export.csv/*.csv\")[0], \"rb\")\n",
        "s3.Bucket(\"Confitec\").put_object(Key=\"export.csv\", Body=data)"
      ],
      "metadata": {
        "id": "LaVRSxxWqTD6"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "uZF3kVmhqTGT"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}