scripts/transform_data_databricks.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "47a715dd-0660-447a-b647-6a2a8bce45d2",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "#importar as bibliotecas necessárias\n",
    "from pyspark.sql.types import IntegerType, DoubleType, BooleanType #para transformar string/integer\n",
    "from pyspark.sql.functions import concat, col, to_date,lpad #para transformar datas\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "95191eb7-2727-43c9-ac54-a5837bea74d5",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "#Configurações necessárias para o acesso aos arquivos\n",
    "configs = {\"fs.azure.account.auth.type\": \"OAuth\",\n",
    "\"fs.azure.account.oauth.provider.type\": \"org.apache.hadoop.fs.azurebfs.oauth2.ClientCredsTokenProvider\",\n",
    "\"fs.azure.account.oauth2.client.id\": \"999ca713-67f1-4e1a-b5d2-d236fb92acff\",\n",
    "\"fs.azure.account.oauth2.client.secret\": 'Joj8Q~Yemo1g._jMUrxploH3pir3K9QtDMGyqaKW', \n",
    "\"fs.azure.account.oauth2.client.endpoint\": \"https://login.microsoftonline.com/7977b21e-6bc1-4c8f-a881-1b47a52a27cd/oauth2/token\"}\n",
    "\n",
    "\n",
    "\n",
    "dbutils.fs.mount( source = \"abfss://amazonia-data@micursode.dfs.core.windows.net\", # contrainer@storageacc \n",
    "mount_point = \"/mnt/amazondb\", \n",
    "extra_configs = configs)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "741d9724-c32b-4c74-970a-30e5265c03d3",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Out[4]: [FileInfo(path='dbfs:/mnt/amazonsp/raw-data/', name='raw-data/', size=0, modificationTime=1707695409000),\n FileInfo(path='dbfs:/mnt/amazonsp/transformed-data/', name='transformed-data/', size=0, modificationTime=1707695418000)]"
     ]
    }
   ],
   "source": [
    "#verificando o acesso aos diretórios montados\n",
    "\n",
    "dbutils.fs.ls(\"/mnt/amazonsp\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "5233050d-35bc-4f44-afe0-af048a6df7de",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "#Carregando os arquivos. Obs: InferSchema = True faz com que na leitura, já exista alguma nível de transformação dos datatypes.\n",
    "\n",
    "amazon_fires = spark.read.format(\"csv\").option(\"header\",\"true\").option(\"inferSchema\",\"true\").load(\"/mnt/amazondb/raw-data/amazon_fires.csv\") \n",
    "amazon_deforestation = spark.read.format(\"csv\").option(\"header\",\"true\").option(\"inferSchema\",\"true\").option(\"dateFormat\", \"yyyy-MM-dd\").load(\"/mnt/amazondb/raw-data/deforestation.csv\") \n",
    "elnino = spark.read.format(\"csv\").option(\"header\",\"true\").option(\"inferSchema\",\"true\").option(\"dateFormat\", \"yyyy-MM-dd\").load(\"/mnt/amazondb/raw-data/elnino.csv\") "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "706749e7-a78d-4bf5-8fde-c49eda6673f3",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+-----+-----------+-------------------+-------------------+---------+\n|year|month|      state|           latitude|          longitude|firespots|\n+----+-----+-----------+-------------------+-------------------+---------+\n|1999|    1|   AMAZONAS| -2.371113333333333| -59.89993333333334|        3|\n|1999|    1|   MARANHAO| -2.257394722222222|-45.487830555555554|       36|\n|1999|    1|MATO GROSSO|-12.660633333333333|-55.057988888888886|       18|\n|1999|    1|       PARA| -2.474820459770115| -48.54696666666667|       87|\n|1999|    1|   RONDONIA|           -12.8617|           -60.5131|        1|\n|1999|    1|    RORAIMA| 3.4032246666666666| -60.62285333333333|       15|\n|1999|    2|      AMAPA|             -0.155|           -52.6831|        1|\n|1999|    2|   AMAZONAS| -2.763166976744186| -63.42978139534884|       43|\n|1999|    2|MATO GROSSO|        -12.6199875|        -55.3753625|        8|\n|1999|    2|       PARA| -2.150617438596491| -53.50991052631579|      285|\n|1999|    2|   RONDONIA|             -9.875|           -62.5617|        1|\n|1999|    2|    RORAIMA|          2.2562095|          -61.07694|       20|\n|1999|    3|      AMAPA|           1.138335|          -50.46155|        2|\n|1999|    3|   AMAZONAS| -4.104805714285714| -66.10141428571428|        7|\n|1999|    3|   MARANHAO|          -3.504865|           -45.6928|        4|\n|1999|    3|MATO GROSSO|          -12.31615|           -55.9678|        4|\n|1999|    3|       PARA|-5.0887714285714285| -52.33334285714286|        7|\n|1999|    3|   RONDONIA|        -12.7341125|        -62.1526625|        8|\n|1999|    3|    RORAIMA|  2.176589693877551| -60.68420714285714|       98|\n|1999|    4|   AMAZONAS|           -3.73306|          -63.64585|        2|\n+----+-----+-----------+-------------------+-------------------+---------+\nonly showing top 20 rows\n\n+-----------+---+----+---+----+-----+----+----+---+---+---------+\n|Ano/Estados| AC|  AM| AP|  MA|   MT|  PA|  RO| RR| TO|AMZ LEGAL|\n+-----------+---+----+---+----+-----+----+----+---+---+---------+\n|       2004|728|1232| 46| 755|11814|8870|3858|311|158|    27772|\n|       2005|592| 775| 33| 922| 7145|5899|3244|133|271|    19014|\n|       2006|398| 788| 30| 674| 4333|5659|2049|231|124|    14286|\n|       2007|184| 610| 39| 631| 2678|5526|1611|309| 63|    11651|\n|       2008|254| 604|100|1271| 3258|5607|1136|574|107|    12911|\n|       2009|167| 405| 70| 828| 1049|4281| 482|121| 61|     7464|\n|       2010|259| 595| 53| 712|  871|3770| 435|256| 49|     7000|\n|       2011|280| 502| 66| 396| 1120|3008| 865|141| 40|     6418|\n|       2012|305| 523| 27| 269|  757|1741| 773|124| 52|     4571|\n|       2013|221| 583| 23| 403| 1139|2346| 932|170| 74|     5891|\n|       2014|309| 500| 31| 257| 1075|1887| 684|219| 50|     5012|\n|       2015|264| 712| 25| 209| 1601|2153|1030|156| 57|     6207|\n|       2016|372|1129| 17| 258| 1489|2992|1376|202| 58|     7893|\n|       2017|257|1001| 24| 265| 1561|2433|1243|132| 31|     6947|\n|       2018|444|1045| 24| 253| 1490|2744|1316|195| 25|     7536|\n|       2019|688|1421|  8| 215| 1685|3862|1245|617| 21|     9762|\n+-----------+---+----+---+----+-----+----+----+---+---+---------+\n\n+----------+--------+----------+-----------+\n|start year|end year|phenomenon|   severity|\n+----------+--------+----------+-----------+\n|      2004|    2005|   El Nino|       Weak|\n|      2006|    2007|   El Nino|       Weak|\n|      2014|    2015|   El Nino|       Weak|\n|      2018|    2019|   El Nino|       Weak|\n|      2002|    2003|   El Nino|   Moderate|\n|      2009|    2010|   El Nino|   Moderate|\n|      2015|    2016|   El Nino|Very Strong|\n|      2000|    2001|   La Nina|       Weak|\n|      2005|    2006|   La Nina|       Weak|\n|      2008|    2009|   La Nina|       Weak|\n|      2016|    2017|   La Nina|       Weak|\n|      2017|    2018|   La Nina|       Weak|\n|      2011|    2012|   La Nina|   Moderate|\n|      1999|    2000|   La Nina|     Strong|\n|      2007|    2008|   La Nina|     Strong|\n|      2010|    2011|   La Nina|     Strong|\n+----------+--------+----------+-----------+\n\n"
     ]
    }
   ],
   "source": [
    "#Monstrando os dados carregados\n",
    "amazon_fires.show()\n",
    "amazon_deforestation.show()\n",
    "elnino.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "8561344e-350d-4d3f-9bcd-c7a5f80c1e1c",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n |-- Ano/Estados: integer (nullable = true)\n |-- AC: integer (nullable = true)\n |-- AM: integer (nullable = true)\n |-- AP: integer (nullable = true)\n |-- MA: integer (nullable = true)\n |-- MT: integer (nullable = true)\n |-- PA: integer (nullable = true)\n |-- RO: integer (nullable = true)\n |-- RR: integer (nullable = true)\n |-- TO: integer (nullable = true)\n |-- AMZ LEGAL: integer (nullable = true)\n\nroot\n |-- year: integer (nullable = true)\n |-- month: integer (nullable = true)\n |-- state: string (nullable = true)\n |-- latitude: double (nullable = true)\n |-- longitude: double (nullable = true)\n |-- firespots: integer (nullable = true)\n\nroot\n |-- start year: integer (nullable = true)\n |-- end year: integer (nullable = true)\n |-- phenomenon: string (nullable = true)\n |-- severity: string (nullable = true)\n\n"
     ]
    }
   ],
   "source": [
    "#Verificando os data types\n",
    "amazon_deforestation.printSchema()\n",
    "amazon_fires.printSchema()\n",
    "elnino.printSchema()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "08d48dcc-f8a5-48be-b7b6-5861a37e6c8a",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "#Procedendo com as alterações\n",
    "\n",
    "amazon_deforestation = amazon_deforestation.withColumn(\"Ano/Estados\", to_date(col(\"Ano/Estados\"), \"yyyy\"))\\\n",
    ".withColumnRenamed(\"Ano/Estados\",\"Year\")\\\n",
    "\n",
    "amazon_deforestation = amazon_deforestation.drop(\"AMZ LEGAL\")\n",
    "\n",
    "amazon_fires = amazon_fires.withColumn('date', concat(amazon_fires['year'],lpad(amazon_fires['month'], 2, '0')))\\\n",
    ".withColumn(\"date\", to_date(col(\"date\"), \"yyyyMM\"))\n",
    "\n",
    "\n",
    "elnino = elnino.withColumn(\"start year\", to_date(col(\"start year\"), \"yyyy\"))\\\n",
    ".withColumn(\"end year\", to_date(col(\"end year\"), \"yyyy\"))\\\n",
    ".withColumnRenamed(\"start year\",\"start_year\")\\\n",
    ".withColumnRenamed(\"end year\",\"end_year\")\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "3d8bef73-834c-4e8f-bf78-7bf31c988b76",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n |-- Year: date (nullable = true)\n |-- AC: integer (nullable = true)\n |-- AM: integer (nullable = true)\n |-- AP: integer (nullable = true)\n |-- MA: integer (nullable = true)\n |-- MT: integer (nullable = true)\n |-- PA: integer (nullable = true)\n |-- RO: integer (nullable = true)\n |-- RR: integer (nullable = true)\n |-- TO: integer (nullable = true)\n\nroot\n |-- year: integer (nullable = true)\n |-- month: integer (nullable = true)\n |-- state: string (nullable = true)\n |-- latitude: double (nullable = true)\n |-- longitude: double (nullable = true)\n |-- firespots: integer (nullable = true)\n |-- date: date (nullable = true)\n\nroot\n |-- start_year: date (nullable = true)\n |-- end_year: date (nullable = true)\n |-- phenomenon: string (nullable = true)\n |-- severity: string (nullable = true)\n\n"
     ]
    }
   ],
   "source": [
    "#Verificando as alterações\n",
    "\n",
    "amazon_deforestation.printSchema()\n",
    "amazon_fires.printSchema()\n",
    "elnino.printSchema()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "3e780c87-2f82-448b-b027-6e8cbda3de21",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+-----+-----------+-------------------+-------------------+---------+----------+\n|year|month|      state|           latitude|          longitude|firespots|      date|\n+----+-----+-----------+-------------------+-------------------+---------+----------+\n|1999|    1|   AMAZONAS| -2.371113333333333| -59.89993333333334|        3|1999-01-01|\n|1999|    1|   MARANHAO| -2.257394722222222|-45.487830555555554|       36|1999-01-01|\n|1999|    1|MATO GROSSO|-12.660633333333333|-55.057988888888886|       18|1999-01-01|\n|1999|    1|       PARA| -2.474820459770115| -48.54696666666667|       87|1999-01-01|\n|1999|    1|   RONDONIA|           -12.8617|           -60.5131|        1|1999-01-01|\n|1999|    1|    RORAIMA| 3.4032246666666666| -60.62285333333333|       15|1999-01-01|\n|1999|    2|      AMAPA|             -0.155|           -52.6831|        1|1999-02-01|\n|1999|    2|   AMAZONAS| -2.763166976744186| -63.42978139534884|       43|1999-02-01|\n|1999|    2|MATO GROSSO|        -12.6199875|        -55.3753625|        8|1999-02-01|\n|1999|    2|       PARA| -2.150617438596491| -53.50991052631579|      285|1999-02-01|\n|1999|    2|   RONDONIA|             -9.875|           -62.5617|        1|1999-02-01|\n|1999|    2|    RORAIMA|          2.2562095|          -61.07694|       20|1999-02-01|\n|1999|    3|      AMAPA|           1.138335|          -50.46155|        2|1999-03-01|\n|1999|    3|   AMAZONAS| -4.104805714285714| -66.10141428571428|        7|1999-03-01|\n|1999|    3|   MARANHAO|          -3.504865|           -45.6928|        4|1999-03-01|\n|1999|    3|MATO GROSSO|          -12.31615|           -55.9678|        4|1999-03-01|\n|1999|    3|       PARA|-5.0887714285714285| -52.33334285714286|        7|1999-03-01|\n|1999|    3|   RONDONIA|        -12.7341125|        -62.1526625|        8|1999-03-01|\n|1999|    3|    RORAIMA|  2.176589693877551| -60.68420714285714|       98|1999-03-01|\n|1999|    4|   AMAZONAS|           -3.73306|          -63.64585|        2|1999-04-01|\n+----+-----+-----------+-------------------+-------------------+---------+----------+\nonly showing top 20 rows\n\n+----------+---+----+---+----+-----+----+----+---+---+-----+\n|      Year| AC|  AM| AP|  MA|   MT|  PA|  RO| RR| TO|Total|\n+----------+---+----+---+----+-----+----+----+---+---+-----+\n|2004-01-01|728|1232| 46| 755|11814|8870|3858|311|158|27772|\n|2005-01-01|592| 775| 33| 922| 7145|5899|3244|133|271|19014|\n|2006-01-01|398| 788| 30| 674| 4333|5659|2049|231|124|14286|\n|2007-01-01|184| 610| 39| 631| 2678|5526|1611|309| 63|11651|\n|2008-01-01|254| 604|100|1271| 3258|5607|1136|574|107|12911|\n|2009-01-01|167| 405| 70| 828| 1049|4281| 482|121| 61| 7464|\n|2010-01-01|259| 595| 53| 712|  871|3770| 435|256| 49| 7000|\n|2011-01-01|280| 502| 66| 396| 1120|3008| 865|141| 40| 6418|\n|2012-01-01|305| 523| 27| 269|  757|1741| 773|124| 52| 4571|\n|2013-01-01|221| 583| 23| 403| 1139|2346| 932|170| 74| 5891|\n|2014-01-01|309| 500| 31| 257| 1075|1887| 684|219| 50| 5012|\n|2015-01-01|264| 712| 25| 209| 1601|2153|1030|156| 57| 6207|\n|2016-01-01|372|1129| 17| 258| 1489|2992|1376|202| 58| 7893|\n|2017-01-01|257|1001| 24| 265| 1561|2433|1243|132| 31| 6947|\n|2018-01-01|444|1045| 24| 253| 1490|2744|1316|195| 25| 7536|\n|2019-01-01|688|1421|  8| 215| 1685|3862|1245|617| 21| 9762|\n+----------+---+----+---+----+-----+----+----+---+---+-----+\n\n+----------+----------+----------+-----------+\n|start_year|  end_year|phenomenon|   severity|\n+----------+----------+----------+-----------+\n|2004-01-01|2005-01-01|   El Nino|       Weak|\n|2006-01-01|2007-01-01|   El Nino|       Weak|\n|2014-01-01|2015-01-01|   El Nino|       Weak|\n|2018-01-01|2019-01-01|   El Nino|       Weak|\n|2002-01-01|2003-01-01|   El Nino|   Moderate|\n|2009-01-01|2010-01-01|   El Nino|   Moderate|\n|2015-01-01|2016-01-01|   El Nino|Very Strong|\n|2000-01-01|2001-01-01|   La Nina|       Weak|\n|2005-01-01|2006-01-01|   La Nina|       Weak|\n|2008-01-01|2009-01-01|   La Nina|       Weak|\n|2016-01-01|2017-01-01|   La Nina|       Weak|\n|2017-01-01|2018-01-01|   La Nina|       Weak|\n|2011-01-01|2012-01-01|   La Nina|   Moderate|\n|1999-01-01|2000-01-01|   La Nina|     Strong|\n|2007-01-01|2008-01-01|   La Nina|     Strong|\n|2010-01-01|2011-01-01|   La Nina|     Strong|\n+----------+----------+----------+-----------+\n\n"
     ]
    }
   ],
   "source": [
    "#Monstrando os dados corrigidos\n",
    "amazon_fires.show()\n",
    "amazon_deforestation.show()\n",
    "elnino.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "1c3c69bf-7090-480d-a229-cf116646c7c9",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "#Salvando os dados após as transformações\n",
    "#Repartition = indica em quantas partes o arquivo deve estar dividido\n",
    "elnino.repartition(1).write.mode(\"overwrite\").option(\"header\",\"true\").csv(\"/mnt/amazondb/transformed-data/elnino\")\n",
    "amazon_deforestation.repartition(1).write.mode(\"overwrite\").option(\"header\",\"true\").csv(\"/mnt/amazondb/transformed-data/amazon_deforestation\")\n",
    "amazon_fires.repartition(1).write.mode(\"overwrite\").option(\"header\",\"true\").csv(\"/mnt/amazondb/transformed-data/amazon_fires\")"
   ]
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "dashboards": [],
   "language": "python",
   "notebookMetadata": {
    "mostRecentlyExecutedCommandWithImplicitDF": {
     "commandId": -1,
     "dataframes": [
      "_sqldf"
     ]
    },
    "pythonIndentUnit": 4
   },
   "notebookName": "Notebook sem título 2024-02-12 20_23_47 (3) (2)",
   "widgets": {}
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}