Big-Data_ETL-AWS.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# Big Data - ETL AWS\n",
        "Caderno de Desenvolvimento <br>\n",
        "Curadoria de [Rafael Barbosa](https://www.linkedin.com/in/barbosa89/)\n",
        "\n",
        "---"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Particionamento e orientação à coluna.\n",
        "\n",
        "Comparação dos métodos de armazenamento, acesso e quantidade de dados escaneados de tabelas de dados não particionada e particionada, utilizando Pandas, parquet e AWS S3, IAM e Athena."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "E69nBHvAa_O5"
      },
      "source": [
        "### Download do arquivo `crime.csv` (fonte [Kaggle](https://www.kaggle.com/yamqwe/chicago-crimee?select=chicago_crime_2014.csv))."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "qylvNY1EcHDr",
        "outputId": "cafeaaf0-14dc-45bc-f132-6a104b8994b9"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "crime.csv pronto\n"
          ]
        }
      ],
      "source": [
        "!wget https://raw.githubusercontent.com/andre-marcos-perez/ebac-course-utils/main/dataset/crime.csv -q -O crime.csv\n",
        "print(\"crime.csv pronto\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "R9Ils6eccOLS"
      },
      "source": [
        "### Carrega os dados em um DataFrame **Pandas** e cria a coluna `reference_date`."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 860
        },
        "id": "_13A2y77ccU6",
        "outputId": "1e31fb18-e7ed-422b-827a-6194dd3914a2"
      },
      "outputs": [
        {
          "data": {
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "dataframe",
              "variable_name": "df"
            },
            "text/html": [
              "\n",
              "  <div id=\"df-406b4883-510b-4cee-838e-61e0ec38fee2\" class=\"colab-df-container\">\n",
              "    <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>index</th>\n",
              "      <th>ID</th>\n",
              "      <th>Case Number</th>\n",
              "      <th>Date</th>\n",
              "      <th>Block</th>\n",
              "      <th>IUCR</th>\n",
              "      <th>Primary Type</th>\n",
              "      <th>Description</th>\n",
              "      <th>Location Description</th>\n",
              "      <th>Arrest</th>\n",
              "      <th>Domestic</th>\n",
              "      <th>Beat</th>\n",
              "      <th>District</th>\n",
              "      <th>Ward</th>\n",
              "      <th>Community Area</th>\n",
              "      <th>FBI Code</th>\n",
              "      <th>Latitude</th>\n",
              "      <th>Longitude</th>\n",
              "      <th>reference_date</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>0</td>\n",
              "      <td>9446824</td>\n",
              "      <td>HX100141</td>\n",
              "      <td>01/01/2014 02:00:00 AM</td>\n",
              "      <td>0000X W ILLINOIS ST</td>\n",
              "      <td>0460</td>\n",
              "      <td>BATTERY</td>\n",
              "      <td>SIMPLE</td>\n",
              "      <td>STREET</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>1831</td>\n",
              "      <td>18</td>\n",
              "      <td>42.0</td>\n",
              "      <td>8.0</td>\n",
              "      <td>08B</td>\n",
              "      <td>41.890828</td>\n",
              "      <td>-87.630235</td>\n",
              "      <td>2014-01-01</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1</td>\n",
              "      <td>9446748</td>\n",
              "      <td>HX100020</td>\n",
              "      <td>01/01/2014 12:00:00 AM</td>\n",
              "      <td>006XX N DEARBORN ST</td>\n",
              "      <td>0890</td>\n",
              "      <td>THEFT</td>\n",
              "      <td>FROM BUILDING</td>\n",
              "      <td>BAR OR TAVERN</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>1832</td>\n",
              "      <td>18</td>\n",
              "      <td>42.0</td>\n",
              "      <td>8.0</td>\n",
              "      <td>06</td>\n",
              "      <td>41.893542</td>\n",
              "      <td>-87.629702</td>\n",
              "      <td>2014-01-01</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2</td>\n",
              "      <td>9446758</td>\n",
              "      <td>HX100030</td>\n",
              "      <td>01/01/2014 12:30:00 AM</td>\n",
              "      <td>052XX W RACE AVE</td>\n",
              "      <td>1310</td>\n",
              "      <td>CRIMINAL DAMAGE</td>\n",
              "      <td>TO PROPERTY</td>\n",
              "      <td>APARTMENT</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>1523</td>\n",
              "      <td>15</td>\n",
              "      <td>28.0</td>\n",
              "      <td>25.0</td>\n",
              "      <td>14</td>\n",
              "      <td>41.890046</td>\n",
              "      <td>-87.756333</td>\n",
              "      <td>2014-01-01</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>3</td>\n",
              "      <td>9446760</td>\n",
              "      <td>HX100027</td>\n",
              "      <td>01/01/2014 12:30:00 AM</td>\n",
              "      <td>053XX W WELLINGTON AVE</td>\n",
              "      <td>0460</td>\n",
              "      <td>BATTERY</td>\n",
              "      <td>SIMPLE</td>\n",
              "      <td>APARTMENT</td>\n",
              "      <td>True</td>\n",
              "      <td>False</td>\n",
              "      <td>2514</td>\n",
              "      <td>25</td>\n",
              "      <td>31.0</td>\n",
              "      <td>19.0</td>\n",
              "      <td>08B</td>\n",
              "      <td>41.935011</td>\n",
              "      <td>-87.759739</td>\n",
              "      <td>2014-01-01</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>4</td>\n",
              "      <td>9446764</td>\n",
              "      <td>HX100054</td>\n",
              "      <td>01/01/2014 12:10:00 AM</td>\n",
              "      <td>014XX W LEXINGTON ST</td>\n",
              "      <td>0460</td>\n",
              "      <td>BATTERY</td>\n",
              "      <td>SIMPLE</td>\n",
              "      <td>STREET</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>1231</td>\n",
              "      <td>12</td>\n",
              "      <td>25.0</td>\n",
              "      <td>28.0</td>\n",
              "      <td>08B</td>\n",
              "      <td>41.872509</td>\n",
              "      <td>-87.663069</td>\n",
              "      <td>2014-01-01</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>274418</th>\n",
              "      <td>274418</td>\n",
              "      <td>10362481</td>\n",
              "      <td>HY554205</td>\n",
              "      <td>08/15/2014 09:00:00 PM</td>\n",
              "      <td>048XX N CLARK ST</td>\n",
              "      <td>1130</td>\n",
              "      <td>DECEPTIVE PRACTICE</td>\n",
              "      <td>FRAUD OR CONFIDENCE GAME</td>\n",
              "      <td>APARTMENT</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>2032</td>\n",
              "      <td>20</td>\n",
              "      <td>46.0</td>\n",
              "      <td>3.0</td>\n",
              "      <td>11</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2014-08-15</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>274419</th>\n",
              "      <td>274419</td>\n",
              "      <td>10362558</td>\n",
              "      <td>HY554336</td>\n",
              "      <td>08/31/2014 03:00:00 AM</td>\n",
              "      <td>028XX W 19TH ST</td>\n",
              "      <td>0281</td>\n",
              "      <td>CRIM SEXUAL ASSAULT</td>\n",
              "      <td>NON-AGGRAVATED</td>\n",
              "      <td>HOSPITAL BUILDING/GROUNDS</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>1022</td>\n",
              "      <td>10</td>\n",
              "      <td>12.0</td>\n",
              "      <td>30.0</td>\n",
              "      <td>02</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2014-08-31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>274420</th>\n",
              "      <td>274420</td>\n",
              "      <td>10363233</td>\n",
              "      <td>HY554972</td>\n",
              "      <td>02/20/2014 12:00:00 PM</td>\n",
              "      <td>046XX W NORTH AVE</td>\n",
              "      <td>1153</td>\n",
              "      <td>DECEPTIVE PRACTICE</td>\n",
              "      <td>FINANCIAL IDENTITY THEFT OVER $ 300</td>\n",
              "      <td>OTHER</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>2533</td>\n",
              "      <td>25</td>\n",
              "      <td>37.0</td>\n",
              "      <td>25.0</td>\n",
              "      <td>11</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2014-02-20</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>274421</th>\n",
              "      <td>274421</td>\n",
              "      <td>10363368</td>\n",
              "      <td>HY555019</td>\n",
              "      <td>09/02/2014 09:00:00 AM</td>\n",
              "      <td>047XX N BEACON ST</td>\n",
              "      <td>1153</td>\n",
              "      <td>DECEPTIVE PRACTICE</td>\n",
              "      <td>FINANCIAL IDENTITY THEFT OVER $ 300</td>\n",
              "      <td>APARTMENT</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>1913</td>\n",
              "      <td>19</td>\n",
              "      <td>46.0</td>\n",
              "      <td>3.0</td>\n",
              "      <td>11</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2014-09-02</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>274422</th>\n",
              "      <td>274422</td>\n",
              "      <td>10364473</td>\n",
              "      <td>HY556366</td>\n",
              "      <td>12/04/2014 09:00:00 AM</td>\n",
              "      <td>012XX W LUNT AVE</td>\n",
              "      <td>1153</td>\n",
              "      <td>DECEPTIVE PRACTICE</td>\n",
              "      <td>FINANCIAL IDENTITY THEFT OVER $ 300</td>\n",
              "      <td>APARTMENT</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>2431</td>\n",
              "      <td>24</td>\n",
              "      <td>49.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>11</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2014-12-04</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>274423 rows × 19 columns</p>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-406b4883-510b-4cee-838e-61e0ec38fee2')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-406b4883-510b-4cee-838e-61e0ec38fee2 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-406b4883-510b-4cee-838e-61e0ec38fee2');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-18ccfbfd-1d42-40f9-84d2-6bbe90cc8f86\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-18ccfbfd-1d42-40f9-84d2-6bbe90cc8f86')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-18ccfbfd-1d42-40f9-84d2-6bbe90cc8f86 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "\n",
              "  <div id=\"id_427410b4-918e-4efc-ba60-5c6c63fdca58\">\n",
              "    <style>\n",
              "      .colab-df-generate {\n",
              "        background-color: #E8F0FE;\n",
              "        border: none;\n",
              "        border-radius: 50%;\n",
              "        cursor: pointer;\n",
              "        display: none;\n",
              "        fill: #1967D2;\n",
              "        height: 32px;\n",
              "        padding: 0 0 0 0;\n",
              "        width: 32px;\n",
              "      }\n",
              "\n",
              "      .colab-df-generate:hover {\n",
              "        background-color: #E2EBFA;\n",
              "        box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "        fill: #174EA6;\n",
              "      }\n",
              "\n",
              "      [theme=dark] .colab-df-generate {\n",
              "        background-color: #3B4455;\n",
              "        fill: #D2E3FC;\n",
              "      }\n",
              "\n",
              "      [theme=dark] .colab-df-generate:hover {\n",
              "        background-color: #434B5C;\n",
              "        box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "        filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "        fill: #FFFFFF;\n",
              "      }\n",
              "    </style>\n",
              "    <button class=\"colab-df-generate\" onclick=\"generateWithVariable('df')\"\n",
              "            title=\"Generate code using this dataframe.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M7,19H8.4L18.45,9,17,7.55,7,17.6ZM5,21V16.75L18.45,3.32a2,2,0,0,1,2.83,0l1.4,1.43a1.91,1.91,0,0,1,.58,1.4,1.91,1.91,0,0,1-.58,1.4L9.25,21ZM18.45,9,17,7.55Zm-12,3A5.31,5.31,0,0,0,4.9,8.1,5.31,5.31,0,0,0,1,6.5,5.31,5.31,0,0,0,4.9,4.9,5.31,5.31,0,0,0,6.5,1,5.31,5.31,0,0,0,8.1,4.9,5.31,5.31,0,0,0,12,6.5,5.46,5.46,0,0,0,6.5,12Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "    <script>\n",
              "      (() => {\n",
              "      const buttonEl =\n",
              "        document.querySelector('#id_427410b4-918e-4efc-ba60-5c6c63fdca58 button.colab-df-generate');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      buttonEl.onclick = () => {\n",
              "        google.colab.notebook.generateWithVariable('df');\n",
              "      }\n",
              "      })();\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "text/plain": [
              "         index        ID Case Number                    Date  \\\n",
              "0            0   9446824    HX100141  01/01/2014 02:00:00 AM   \n",
              "1            1   9446748    HX100020  01/01/2014 12:00:00 AM   \n",
              "2            2   9446758    HX100030  01/01/2014 12:30:00 AM   \n",
              "3            3   9446760    HX100027  01/01/2014 12:30:00 AM   \n",
              "4            4   9446764    HX100054  01/01/2014 12:10:00 AM   \n",
              "...        ...       ...         ...                     ...   \n",
              "274418  274418  10362481    HY554205  08/15/2014 09:00:00 PM   \n",
              "274419  274419  10362558    HY554336  08/31/2014 03:00:00 AM   \n",
              "274420  274420  10363233    HY554972  02/20/2014 12:00:00 PM   \n",
              "274421  274421  10363368    HY555019  09/02/2014 09:00:00 AM   \n",
              "274422  274422  10364473    HY556366  12/04/2014 09:00:00 AM   \n",
              "\n",
              "                         Block  IUCR         Primary Type  \\\n",
              "0          0000X W ILLINOIS ST  0460              BATTERY   \n",
              "1          006XX N DEARBORN ST  0890                THEFT   \n",
              "2             052XX W RACE AVE  1310      CRIMINAL DAMAGE   \n",
              "3       053XX W WELLINGTON AVE  0460              BATTERY   \n",
              "4         014XX W LEXINGTON ST  0460              BATTERY   \n",
              "...                        ...   ...                  ...   \n",
              "274418        048XX N CLARK ST  1130   DECEPTIVE PRACTICE   \n",
              "274419         028XX W 19TH ST  0281  CRIM SEXUAL ASSAULT   \n",
              "274420       046XX W NORTH AVE  1153   DECEPTIVE PRACTICE   \n",
              "274421       047XX N BEACON ST  1153   DECEPTIVE PRACTICE   \n",
              "274422        012XX W LUNT AVE  1153   DECEPTIVE PRACTICE   \n",
              "\n",
              "                                Description       Location Description  \\\n",
              "0                                    SIMPLE                     STREET   \n",
              "1                             FROM BUILDING              BAR OR TAVERN   \n",
              "2                               TO PROPERTY                  APARTMENT   \n",
              "3                                    SIMPLE                  APARTMENT   \n",
              "4                                    SIMPLE                     STREET   \n",
              "...                                     ...                        ...   \n",
              "274418             FRAUD OR CONFIDENCE GAME                  APARTMENT   \n",
              "274419                       NON-AGGRAVATED  HOSPITAL BUILDING/GROUNDS   \n",
              "274420  FINANCIAL IDENTITY THEFT OVER $ 300                      OTHER   \n",
              "274421  FINANCIAL IDENTITY THEFT OVER $ 300                  APARTMENT   \n",
              "274422  FINANCIAL IDENTITY THEFT OVER $ 300                  APARTMENT   \n",
              "\n",
              "        Arrest  Domestic  Beat  District  Ward  Community Area FBI Code  \\\n",
              "0        False     False  1831        18  42.0             8.0      08B   \n",
              "1        False     False  1832        18  42.0             8.0       06   \n",
              "2        False     False  1523        15  28.0            25.0       14   \n",
              "3         True     False  2514        25  31.0            19.0      08B   \n",
              "4        False     False  1231        12  25.0            28.0      08B   \n",
              "...        ...       ...   ...       ...   ...             ...      ...   \n",
              "274418   False     False  2032        20  46.0             3.0       11   \n",
              "274419   False     False  1022        10  12.0            30.0       02   \n",
              "274420   False     False  2533        25  37.0            25.0       11   \n",
              "274421   False     False  1913        19  46.0             3.0       11   \n",
              "274422   False     False  2431        24  49.0             1.0       11   \n",
              "\n",
              "         Latitude  Longitude reference_date  \n",
              "0       41.890828 -87.630235     2014-01-01  \n",
              "1       41.893542 -87.629702     2014-01-01  \n",
              "2       41.890046 -87.756333     2014-01-01  \n",
              "3       41.935011 -87.759739     2014-01-01  \n",
              "4       41.872509 -87.663069     2014-01-01  \n",
              "...           ...        ...            ...  \n",
              "274418        NaN        NaN     2014-08-15  \n",
              "274419        NaN        NaN     2014-08-31  \n",
              "274420        NaN        NaN     2014-02-20  \n",
              "274421        NaN        NaN     2014-09-02  \n",
              "274422        NaN        NaN     2014-12-04  \n",
              "\n",
              "[274423 rows x 19 columns]"
            ]
          },
          "execution_count": 4,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "import pandas as pd\n",
        "from datetime import datetime\n",
        "\n",
        "filename = './crime'\n",
        "df = pd.read_csv(f'./{filename}.csv')\n",
        "\n",
        "df['reference_date'] = df['Date'].apply(lambda date: datetime.strptime(date.split(sep=' ')[0], '%m/%d/%Y').strftime('%Y-%m-%d'))\n",
        "\n",
        "df"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8oTn1Z1Md2GQ"
      },
      "source": [
        "### Persiste o dado no DataFrame em um arquivo **csv** e em arquivos **parquet**, estes últimos comprimidos e particionados pela coluna `reference_date`."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "background_save": true
        },
        "id": "tan0z69ud2Gb",
        "outputId": "a411e10f-d7a7-4022-e3dd-8765ffcc1447"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "pronto\n"
          ]
        }
      ],
      "source": [
        "### CSV\n",
        "df.to_csv('./crime_enriched.csv', sep=',', index=False)\n",
        "\n",
        "### parquet\n",
        "df.to_parquet('./crime', engine='pyarrow', compression='gzip', partition_cols='reference_date')\n",
        "\n",
        "print(\"pronto\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "LzXWWUWzeNxt"
      },
      "source": [
        "### Cria recursos no **AWS S3 e AWS IAM** e persista os dados em seus respectivos *buckets* utilizando Python.\n",
        "\n",
        "- `Buckets` no `AWS S3` para armazenar os arquivos.\n",
        "![](https://github.com/rafie-b/Big-Data-Analytics/blob/main/repo-Big-Data_ETL-AWS/s3-buckets.png?raw=true)\n",
        "\n",
        "\n",
        "- Usuário no `AWS IAM` para fazer o *upload* dos arquivos e suas partições\n",
        "![](https://github.com/rafie-b/Big-Data-Analytics/blob/main/repo-Big-Data_ETL-AWS/iam-user.png?raw=true)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "91TxSnV-eNx7",
        "outputId": "99b40c7b-ff4d-43e6-ab16-82e8be524660"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "access_key:\n",
            "··········\n",
            "secret_key:\n",
            "··········\n",
            "chaves salvas\n"
          ]
        }
      ],
      "source": [
        "### inserir as credenciais de usuário AWS IAM no Python\n",
        "\n",
        "from getpass import getpass\n",
        "\n",
        "print(\"access_key:\")\n",
        "aws_access_key_id = getpass()\n",
        "\n",
        "print(\"secret_key:\")\n",
        "aws_secret_access_key = getpass()\n",
        "\n",
        "print(\"chaves salvas\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "5DW5rim-QJeW",
        "outputId": "b18d7989-ab30-4cc5-9be3-441613606888"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Collecting boto3\n",
            "  Downloading boto3-1.34.60-py3-none-any.whl (139 kB)\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m139.3/139.3 kB\u001b[0m \u001b[31m4.0 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25hCollecting botocore<1.35.0,>=1.34.60 (from boto3)\n",
            "  Downloading botocore-1.34.60-py3-none-any.whl (12.0 MB)\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m12.0/12.0 MB\u001b[0m \u001b[31m34.5 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25hCollecting jmespath<2.0.0,>=0.7.1 (from boto3)\n",
            "  Downloading jmespath-1.0.1-py3-none-any.whl (20 kB)\n",
            "Collecting s3transfer<0.11.0,>=0.10.0 (from boto3)\n",
            "  Downloading s3transfer-0.10.0-py3-none-any.whl (82 kB)\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m82.1/82.1 kB\u001b[0m \u001b[31m12.4 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25hRequirement already satisfied: python-dateutil<3.0.0,>=2.1 in /usr/local/lib/python3.10/dist-packages (from botocore<1.35.0,>=1.34.60->boto3) (2.8.2)\n",
            "Requirement already satisfied: urllib3<2.1,>=1.25.4 in /usr/local/lib/python3.10/dist-packages (from botocore<1.35.0,>=1.34.60->boto3) (2.0.7)\n",
            "Requirement already satisfied: six>=1.5 in /usr/local/lib/python3.10/dist-packages (from python-dateutil<3.0.0,>=2.1->botocore<1.35.0,>=1.34.60->boto3) (1.16.0)\n",
            "Installing collected packages: jmespath, botocore, s3transfer, boto3\n",
            "Successfully installed boto3-1.34.60 botocore-1.34.60 jmespath-1.0.1 s3transfer-0.10.0\n"
          ]
        }
      ],
      "source": [
        "### instala SDK Python da AWS\n",
        "!pip install boto3"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "7n0DcK2zQM4u",
        "outputId": "e5257c1d-ce0d-43d7-c8f6-7ca83bc1bd54"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "acesso garantido\n"
          ]
        }
      ],
      "source": [
        "### conecta o usuário AWS\n",
        "\n",
        "import boto3\n",
        "\n",
        "client = boto3.client(\n",
        "  's3',\n",
        "  aws_access_key_id=aws_access_key_id,\n",
        "  aws_secret_access_key=aws_secret_access_key\n",
        ")\n",
        "\n",
        "print(\"acesso garantido\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "s-Sy7Ev-QjX8",
        "outputId": "a4665f5a-5612-4149-df88-8ebc30b03181"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "0.0 %\n",
            "0.27 %\n",
            "0.55 %\n",
            "0.82 %\n",
            "1.1 %\n",
            "1.37 %\n",
            "1.64 %\n",
            "1.92 %\n",
            "2.19 %\n",
            "2.47 %\n",
            "2.74 %\n",
            "3.01 %\n",
            "3.29 %\n",
            "3.56 %\n",
            "3.84 %\n",
            "4.11 %\n",
            "4.38 %\n",
            "4.66 %\n",
            "4.93 %\n",
            "5.21 %\n",
            "5.48 %\n",
            "5.75 %\n",
            "6.03 %\n",
            "6.3 %\n",
            "6.58 %\n",
            "6.85 %\n",
            "7.12 %\n",
            "7.4 %\n",
            "7.67 %\n",
            "7.95 %\n",
            "8.22 %\n",
            "8.49 %\n",
            "8.77 %\n",
            "9.04 %\n",
            "9.32 %\n",
            "9.59 %\n",
            "9.86 %\n",
            "10.14 %\n",
            "10.41 %\n",
            "10.68 %\n",
            "10.96 %\n",
            "11.23 %\n",
            "11.51 %\n",
            "11.78 %\n",
            "12.05 %\n",
            "12.33 %\n",
            "12.6 %\n",
            "12.88 %\n",
            "13.15 %\n",
            "13.42 %\n",
            "13.7 %\n",
            "13.97 %\n",
            "14.25 %\n",
            "14.52 %\n",
            "14.79 %\n",
            "15.07 %\n",
            "15.34 %\n",
            "15.62 %\n",
            "15.89 %\n",
            "16.16 %\n",
            "16.44 %\n",
            "16.71 %\n",
            "16.99 %\n",
            "17.26 %\n",
            "17.53 %\n",
            "17.81 %\n",
            "18.08 %\n",
            "18.36 %\n",
            "18.63 %\n",
            "18.9 %\n",
            "19.18 %\n",
            "19.45 %\n",
            "19.73 %\n",
            "20.0 %\n",
            "20.27 %\n",
            "20.55 %\n",
            "20.82 %\n",
            "21.1 %\n",
            "21.37 %\n",
            "21.64 %\n",
            "21.92 %\n",
            "22.19 %\n",
            "22.47 %\n",
            "22.74 %\n",
            "23.01 %\n",
            "23.29 %\n",
            "23.56 %\n",
            "23.84 %\n",
            "24.11 %\n",
            "24.38 %\n",
            "24.66 %\n",
            "24.93 %\n",
            "25.21 %\n",
            "25.48 %\n",
            "25.75 %\n",
            "26.03 %\n",
            "26.3 %\n",
            "26.58 %\n",
            "26.85 %\n",
            "27.12 %\n",
            "27.4 %\n",
            "27.67 %\n",
            "27.95 %\n",
            "28.22 %\n",
            "28.49 %\n",
            "28.77 %\n",
            "29.04 %\n",
            "29.32 %\n",
            "29.59 %\n",
            "29.86 %\n",
            "30.14 %\n",
            "30.41 %\n",
            "30.68 %\n",
            "30.96 %\n",
            "31.23 %\n",
            "31.51 %\n",
            "31.78 %\n",
            "32.05 %\n",
            "32.33 %\n",
            "32.6 %\n",
            "32.88 %\n",
            "33.15 %\n",
            "33.42 %\n",
            "33.7 %\n",
            "33.97 %\n",
            "34.25 %\n",
            "34.52 %\n",
            "34.79 %\n",
            "35.07 %\n",
            "35.34 %\n",
            "35.62 %\n",
            "35.89 %\n",
            "36.16 %\n",
            "36.44 %\n",
            "36.71 %\n",
            "36.99 %\n",
            "37.26 %\n",
            "37.53 %\n",
            "37.81 %\n",
            "38.08 %\n",
            "38.36 %\n",
            "38.63 %\n",
            "38.9 %\n",
            "39.18 %\n",
            "39.45 %\n",
            "39.73 %\n",
            "40.0 %\n",
            "40.27 %\n",
            "40.55 %\n",
            "40.82 %\n",
            "41.1 %\n",
            "41.37 %\n",
            "41.64 %\n",
            "41.92 %\n",
            "42.19 %\n",
            "42.47 %\n",
            "42.74 %\n",
            "43.01 %\n",
            "43.29 %\n",
            "43.56 %\n",
            "43.84 %\n",
            "44.11 %\n",
            "44.38 %\n",
            "44.66 %\n",
            "44.93 %\n",
            "45.21 %\n",
            "45.48 %\n",
            "45.75 %\n",
            "46.03 %\n",
            "46.3 %\n",
            "46.58 %\n",
            "46.85 %\n",
            "47.12 %\n",
            "47.4 %\n",
            "47.67 %\n",
            "47.95 %\n",
            "48.22 %\n",
            "48.49 %\n",
            "48.77 %\n",
            "49.04 %\n",
            "49.32 %\n",
            "49.59 %\n",
            "49.86 %\n",
            "50.14 %\n",
            "50.41 %\n",
            "50.68 %\n",
            "50.96 %\n",
            "51.23 %\n",
            "51.51 %\n",
            "51.78 %\n",
            "52.05 %\n",
            "52.33 %\n",
            "52.6 %\n",
            "52.88 %\n",
            "53.15 %\n",
            "53.42 %\n",
            "53.7 %\n",
            "53.97 %\n",
            "54.25 %\n",
            "54.52 %\n",
            "54.79 %\n",
            "55.07 %\n",
            "55.34 %\n",
            "55.62 %\n",
            "55.89 %\n",
            "56.16 %\n",
            "56.44 %\n",
            "56.71 %\n",
            "56.99 %\n",
            "57.26 %\n",
            "57.53 %\n",
            "57.81 %\n",
            "58.08 %\n",
            "58.36 %\n",
            "58.63 %\n",
            "58.9 %\n",
            "59.18 %\n",
            "59.45 %\n",
            "59.73 %\n",
            "60.0 %\n",
            "60.27 %\n",
            "60.55 %\n",
            "60.82 %\n",
            "61.1 %\n",
            "61.37 %\n",
            "61.64 %\n",
            "61.92 %\n",
            "62.19 %\n",
            "62.47 %\n",
            "62.74 %\n",
            "63.01 %\n",
            "63.29 %\n",
            "63.56 %\n",
            "63.84 %\n",
            "64.11 %\n",
            "64.38 %\n",
            "64.66 %\n",
            "64.93 %\n",
            "65.21 %\n",
            "65.48 %\n",
            "65.75 %\n",
            "66.03 %\n",
            "66.3 %\n",
            "66.58 %\n",
            "66.85 %\n",
            "67.12 %\n",
            "67.4 %\n",
            "67.67 %\n",
            "67.95 %\n",
            "68.22 %\n",
            "68.49 %\n",
            "68.77 %\n",
            "69.04 %\n",
            "69.32 %\n",
            "69.59 %\n",
            "69.86 %\n",
            "70.14 %\n",
            "70.41 %\n",
            "70.68 %\n",
            "70.96 %\n",
            "71.23 %\n",
            "71.51 %\n",
            "71.78 %\n",
            "72.05 %\n",
            "72.33 %\n",
            "72.6 %\n",
            "72.88 %\n",
            "73.15 %\n",
            "73.42 %\n",
            "73.7 %\n",
            "73.97 %\n",
            "74.25 %\n",
            "74.52 %\n",
            "74.79 %\n",
            "75.07 %\n",
            "75.34 %\n",
            "75.62 %\n",
            "75.89 %\n",
            "76.16 %\n",
            "76.44 %\n",
            "76.71 %\n",
            "76.99 %\n",
            "77.26 %\n",
            "77.53 %\n",
            "77.81 %\n",
            "78.08 %\n",
            "78.36 %\n",
            "78.63 %\n",
            "78.9 %\n",
            "79.18 %\n",
            "79.45 %\n",
            "79.73 %\n",
            "80.0 %\n",
            "80.27 %\n",
            "80.55 %\n",
            "80.82 %\n",
            "81.1 %\n",
            "81.37 %\n",
            "81.64 %\n",
            "81.92 %\n",
            "82.19 %\n",
            "82.47 %\n",
            "82.74 %\n",
            "83.01 %\n",
            "83.29 %\n",
            "83.56 %\n",
            "83.84 %\n",
            "84.11 %\n",
            "84.38 %\n",
            "84.66 %\n",
            "84.93 %\n",
            "85.21 %\n",
            "85.48 %\n",
            "85.75 %\n",
            "86.03 %\n",
            "86.3 %\n",
            "86.58 %\n",
            "86.85 %\n",
            "87.12 %\n",
            "87.4 %\n",
            "87.67 %\n",
            "87.95 %\n",
            "88.22 %\n",
            "88.49 %\n",
            "88.77 %\n",
            "89.04 %\n",
            "89.32 %\n",
            "89.59 %\n",
            "89.86 %\n",
            "90.14 %\n",
            "90.41 %\n",
            "90.68 %\n",
            "90.96 %\n",
            "91.23 %\n",
            "91.51 %\n",
            "91.78 %\n",
            "92.05 %\n",
            "92.33 %\n",
            "92.6 %\n",
            "92.88 %\n",
            "93.15 %\n",
            "93.42 %\n",
            "93.7 %\n",
            "93.97 %\n",
            "94.25 %\n",
            "94.52 %\n",
            "94.79 %\n",
            "95.07 %\n",
            "95.34 %\n",
            "95.62 %\n",
            "95.89 %\n",
            "96.16 %\n",
            "96.44 %\n",
            "96.71 %\n",
            "96.99 %\n",
            "97.26 %\n",
            "97.53 %\n",
            "97.81 %\n",
            "98.08 %\n",
            "98.36 %\n",
            "98.63 %\n",
            "98.9 %\n",
            "99.18 %\n",
            "99.45 %\n",
            "99.73 %\n",
            "100.0 %\n"
          ]
        }
      ],
      "source": [
        "### upload das partições parquet no AWS S3\n",
        "\n",
        "import os\n",
        "\n",
        "BUCKET = 'ebac-m41-parquet'\n",
        "\n",
        "i = 0\n",
        "\n",
        "for root, dirs, files in os.walk('./crime'):\n",
        "  elapsed = f'{round(100*i/365, 2)} %'\n",
        "  print(elapsed)\n",
        "  for file in files:\n",
        "    path = os.path.join(root, file)\n",
        "    bucket_path = '/'.join(path.split(sep='/')[2:])\n",
        "    client.upload_file(path, BUCKET, bucket_path)\n",
        "  i = i + 1\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_D2WT_iueaRr"
      },
      "source": [
        "### Cria recursos no **AWS Athena** para as tabelas dos arquivos `csv` e `parquet`."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "T9KL1sBWrCq5"
      },
      "source": [
        "- Cria tabela a partir de `csv`\n",
        "\n",
        "Código SQL utilizadono Athena:\n",
        "```sql\n",
        "CREATE EXTERNAL TABLE `crime_csv`(\n",
        "  `index` bigint,\n",
        "  `id` string,\n",
        "  `case number` string,\n",
        "  `date` string,\n",
        "  `block` string,\n",
        "  `iucr` string,\n",
        "  `primary type` string,\n",
        "  `description` string,\n",
        "  `location description` string,\n",
        "  `arrest` string,\n",
        "  `domestic` string,\n",
        "  `beat` string,\n",
        "  `district` string,\n",
        "  `ward` string,\n",
        "  `community area` string,\n",
        "  `fbi code` string,\n",
        "  `latitude` string,\n",
        "  `longitude` string,\n",
        "  `reference_date` string)\n",
        "ROW FORMAT SERDE\n",
        "  'org.apache.hadoop.hive.serde2.OpenCSVSerde'\n",
        " WITH SERDEPROPERTIES (\n",
        "   'separatorChar' = ',',\n",
        "   'quoteChar' = '\\\"',\n",
        "   'escapeChar' = '\\\\'\n",
        "   )\n",
        "STORED AS INPUTFORMAT\n",
        "  'org.apache.hadoop.mapred.TextInputFormat'\n",
        "OUTPUTFORMAT\n",
        "  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'\n",
        "LOCATION\n",
        "  's3://modulo-42-ebac-csv/'\n",
        "TBLPROPERTIES (\n",
        "  \"skip.header.line.count\"=\"1\")\n",
        "```\n",
        "\n",
        "![](https://github.com/rafie-b/Big-Data-Analytics/blob/main/repo-Big-Data_ETL-AWS/Athena-create-csv.png?raw=true)\n",
        "![](https://github.com/rafie-b/Big-Data-Analytics/blob/main/repo-Big-Data_ETL-AWS/Athena-csv.png?raw=true)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3jbJIETM2UaO"
      },
      "source": [
        "- Cria tabela a partir de `parquet`\n",
        "\n",
        "Código SQL utilizadono Athena:\n",
        "```sql\n",
        "CREATE EXTERNAL TABLE `crime_parquet`(\n",
        "  `index` bigint,\n",
        "  `id` bigint,\n",
        "  `case number` string,\n",
        "  `date` string,\n",
        "  `block` string,\n",
        "  `iucr` string,\n",
        "  `primary type` string,\n",
        "  `description` string,\n",
        "  `location description` string,\n",
        "  `arrest` boolean,\n",
        "  `domestic` boolean,\n",
        "  `beat` bigint,\n",
        "  `district` bigint,\n",
        "  `ward` double,\n",
        "  `community area` double,\n",
        "  `fbi code` string,\n",
        "  `latitude` double,\n",
        "  `longitude` double)\n",
        "PARTITIONED BY (\n",
        "  `reference_date` string)\n",
        "ROW FORMAT SERDE\n",
        "  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'\n",
        "STORED AS INPUTFORMAT\n",
        "  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'\n",
        "OUTPUTFORMAT\n",
        "  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'\n",
        "LOCATION\n",
        "  's3://modulo-42-ebac-parquet/'\n",
        "```\n",
        "\n",
        "![](https://github.com/rafie-b/Big-Data-Analytics/blob/main/repo-Big-Data_ETL-AWS/Athena-create-parquet.png?raw=true)\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "KmyLS6po2Zzh"
      },
      "source": [
        "- Carrega partições `parquet`\n",
        "```sql\n",
        "MSCK REPAIR TABLE `crime_parquet`;\n",
        "```\n",
        "\n",
        "![](https://github.com/rafie-b/Big-Data-Analytics/blob/main/repo-Big-Data_ETL-AWS/Athena-load-parquet.png?raw=true)\n",
        "![](https://github.com/rafie-b/Big-Data-Analytics/blob/main/repo-Big-Data_ETL-AWS/Athena-parquet.png?raw=true)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EXkC9Kq54BgZ"
      },
      "source": [
        "### Executa consultas SQL em ambas as tabelas e observa a **diferença na quantidade de dados escaneados**."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "OzyPLSLf4QJp"
      },
      "source": [
        "- Efeito da **orientação a coluna**:\n",
        "\n",
        "![](https://github.com/rafie-b/Big-Data-Analytics/blob/main/repo-Big-Data_ETL-AWS/Athena-select-locationdescript-csv.png?raw=true)\n",
        "\n",
        "![](https://github.com/rafie-b/Big-Data-Analytics/blob/main/repo-Big-Data_ETL-AWS/Athena-select-locationdescript-parquet.png?raw=true)\n",
        "\n",
        "A consulta escaneou 47.34 MB para a tabela `crime_csv`, que é o mesmo tamanho do arquivo, logo um *full scan*. Já para a tabeka `crime_parquet`, a consulta escaneou 0.61 MB. Ou seja, a tabela com o dado **orientado a coluna** escaneou **77 vezes** menos dados para a consulta SQL que seu par em `csv`."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "k8xt2fJk-luW"
      },
      "source": [
        " - Efeito do **particionamento**:\n",
        "\n",
        "![](https://github.com/rafie-b/Big-Data-Analytics/blob/main/repo-Big-Data_ETL-AWS/Athena-select-all-date-csv.png?raw=true)\n",
        "\n",
        "![](https://github.com/rafie-b/Big-Data-Analytics/blob/main/repo-Big-Data_ETL-AWS/Athena-select-all-date-parquet.png?raw=true)\n",
        "\n",
        "A consulta escaneou 47.34 MB para a tabela `crime_csv`, que é o mesmo tamanho do arquivo, logo um *full scan*. Já para a tabeka `crime_parquet`, a consulta escaneou 0.98 MB. Ou seja, a tabela com o dado **particionado** escaneou **48 vezes** menos dados para a consulta SQL que seu par em `csv`."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "v5Sb5h8i-nUb"
      },
      "source": [
        " - Efeito da **orientação a coluna** e do **particionamento**:\n",
        "\n",
        " ![](https://github.com/rafie-b/Big-Data-Analytics/blob/main/repo-Big-Data_ETL-AWS/Athena-select-location-date-csv.png?raw=true)\n",
        "\n",
        " ![](https://github.com/rafie-b/Big-Data-Analytics/blob/main/repo-Big-Data_ETL-AWS/Athena-select-location-date-parquet.png?raw=true)\n",
        "\n",
        "A consulta escaneou 47.34 MB para a tabela `crime_csv`, que é o mesmo tamanho do arquivo, logo um *full scan*. Já para a tabeka `crime_parquet`, a consulta escaneou 0.03 MB. Ou seja, a tabela com o dado **particionado** e **orientado a coluna** escaneou **1337 vezes** menos dados para a consulta SQL que seu par em `csv`."
      ]
    }
  ],
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}