Extract data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "a0b9cbbf-254f-42f3-804d-e34f63e3cfda",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "#Ingesting Data into Azure Databricks\n",
    "\n",
    "##**Payments DataFrame**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "3afdfa62-6dfa-4477-9f7f-ff3a581fecc0",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+------------+------+--------+\n|payment_id|payment_date|amount|rider_id|\n+----------+------------+------+--------+\n|         2|  2019-06-01|   9.0|    1000|\n|         3|  2019-07-01|   9.0|    1000|\n|         4|  2019-08-01|   9.0|    1000|\n|         5|  2019-09-01|   9.0|    1000|\n|         6|  2019-10-01|   9.0|    1000|\n|         7|  2019-11-01|   9.0|    1000|\n|         8|  2019-12-01|   9.0|    1000|\n|         9|  2020-01-01|   9.0|    1000|\n|        10|  2020-02-01|   9.0|    1000|\n|        11|  2020-03-01|   9.0|    1000|\n+----------+------------+------+--------+\nonly showing top 10 rows\n\n"
     ]
    }
   ],
   "source": [
    "df_payments = spark.read.format(\"csv\") \\\n",
    "                  .option('inferSchema', 'false') \\\n",
    "                  .option('header', 'true') \\\n",
    "                  .option('sep',',') \\\n",
    "                  .load('/FileStore/tables/payments.csv') \\\n",
    "                  .toDF('payment_id','payment_date','amount','rider_id')  \n",
    "df_payments.show(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "de7e25c1-0529-442e-973b-3faeec309024",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n |-- payment_id: string (nullable = true)\n |-- payment_date: string (nullable = true)\n |-- amount: string (nullable = true)\n |-- rider_id: string (nullable = true)\n\n"
     ]
    }
   ],
   "source": [
    "df_payments.printSchema()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "01f975cd-4174-45cb-b0dd-6b28833d5e5e",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "### Write payment DataFrame to Delta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "c1306b35-a28b-4139-9562-59b704eb71cd",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "df_payments.write.format('delta')\\\n",
    "        .mode('overwrite')\\\n",
    "        .option(\"overwriteSchema\", \"true\")\\\n",
    "        .save('/delta/payments')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "dca87192-27dd-4065-b63f-21cdbec50d05",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## Riders DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "bf216832-2eb4-4f27-aa96-9d21a37ac942",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+----------+---------+--------------------+----------+------------------+----------------+---------+\n|rider_id|first_name|last_name|             address|  birthday|account_start_date|account_end_date|is_member|\n+--------+----------+---------+--------------------+----------+------------------+----------------+---------+\n|    1001|  Jennifer|    Smith|     397 Diana Ferry|1976-08-10|        2019-11-01|      2020-09-01|     True|\n|    1002|     Karen|    Smith|644 Brittany Row ...|1998-08-10|        2022-02-04|            NULL|     True|\n|    1003|     Bryan|  Roberts|996 Dickerson Tur...|1999-03-29|        2019-08-26|            NULL|    False|\n|    1004|     Jesse|Middleton|7009 Nathan Expre...|1969-04-11|        2019-09-14|            NULL|     True|\n|    1005| Christine|Rodriguez|224 Washington Mi...|1974-08-27|        2020-03-24|            NULL|    False|\n|    1006|    Alicia|   Taylor|   1137 Angela Locks|2004-01-30|        2020-11-27|      2021-12-01|     True|\n|    1007|  Benjamin|Fernandez|   979 Phillips Ways|1988-01-11|        2016-12-11|            NULL|    False|\n|    1008|      John| Crawford|    7691 Evans Court|1987-02-21|        2021-03-28|      2021-07-01|     True|\n|    1009|  Victoria|   Ritter|9922 Jim Crest Ap...|1981-02-07|        2020-06-12|      2021-11-01|     True|\n|    1010|     Tracy|   Austin|    92973 Mary Ville|1996-04-07|        2019-12-27|            NULL|     True|\n+--------+----------+---------+--------------------+----------+------------------+----------------+---------+\nonly showing top 10 rows\n\n"
     ]
    }
   ],
   "source": [
    "df_riders = spark.read.format(\"csv\") \\\n",
    "                  .option('inferSchema', 'false') \\\n",
    "                  .option('header', 'true') \\\n",
    "                  .option('sep',',') \\\n",
    "                  .load('/FileStore/tables/riders.csv') \\\n",
    "                  .toDF('rider_id','first_name','last_name','address', 'birthday','account_start_date', 'account_end_date', 'is_member')  \n",
    "\n",
    "     \n",
    "df_riders.show(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "2b4c053f-75e0-49f2-899a-7fc16f1d1795",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n |-- rider_id: string (nullable = true)\n |-- first_name: string (nullable = true)\n |-- last_name: string (nullable = true)\n |-- address: string (nullable = true)\n |-- birthday: string (nullable = true)\n |-- account_start_date: string (nullable = true)\n |-- account_end_date: string (nullable = true)\n |-- is_member: string (nullable = true)\n\n"
     ]
    }
   ],
   "source": [
    "df_riders.printSchema()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "e0d4248b-60cd-47a5-b2b5-092fb8e8cd66",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## Write Riders DataFrame to Delta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "05dc7f08-66ee-43e9-9745-054dcce9ca46",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "df_riders.write.format('delta')\\\n",
    "        .mode('overwrite')\\\n",
    "        .option(\"overwriteSchema\", \"true\")\\\n",
    "        .save('/delta/riders')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "c12b1923-f872-4c81-9e3a-37858cc75505",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## Stations DataFrame\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "804a7e4c-beff-498d-8e8a-2e1e43f1efac",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------+--------------------+-----------------+------------------+\n|  station_id|                name|         latitude|         longitude|\n+------------+--------------------+-----------------+------------------+\n|KA1503000012|  Clark St & Lake St|41.88579466666667|-87.63110066666668|\n|         637|Wood St & Chicago...|        41.895634|        -87.672069|\n|       13216|  State St & 33rd St|       41.8347335|       -87.6258275|\n|       18003|Fairbanks St & Su...|41.89580766666667|-87.62025316666669|\n|KP1705001026|LaSalle Dr & Huro...|        41.894877|        -87.632326|\n|       13253|Lincoln Ave & Wav...|        41.948797|        -87.675278|\n|KA1503000044|Rush St & Hubbard St|        41.890173|-87.62618499999999|\n|KA1504000140|Winchester Ave & ...|41.92403733333333|-87.67641483333334|\n|TA1305000032|Clinton St & Madi...|        41.882242|-87.64106600000001|\n|TA1306000012| Wells St & Huron St|41.89475366666667|-87.63440200000001|\n+------------+--------------------+-----------------+------------------+\nonly showing top 10 rows\n\n"
     ]
    }
   ],
   "source": [
    "df_stations = spark.read.format(\"csv\") \\\n",
    "                  .option('inferSchema', 'false') \\\n",
    "                  .option('header', 'true') \\\n",
    "                  .option('sep',',') \\\n",
    "                  .load('/FileStore/tables/stations.csv') \\\n",
    "                  .toDF('station_id','name','latitude','longitude') \n",
    "df_stations.show(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "5547a00d-58e6-4103-8372-0631e43b43c3",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n |-- station_id: string (nullable = true)\n |-- name: string (nullable = true)\n |-- latitude: string (nullable = true)\n |-- longitude: string (nullable = true)\n\n"
     ]
    }
   ],
   "source": [
    "df_stations.printSchema()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "6183729b-ba12-4cea-b2fe-eec3974f00dc",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## Write Stations DataFrame to Delta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "a55c96d1-c947-45a1-9b00-f716eab25e33",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "df_stations.write.format('delta')\\\n",
    "        .mode('overwrite')\\\n",
    "        .option(\"overwriteSchema\", \"true\")\\\n",
    "        .save('/delta/stations')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "a5de92f7-2437-41a9-a6b6-440df1c1ccde",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## Trips DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "d1e4eceb-6385-4d1a-912f-0bd5b4a0ffad",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------------+-------------+-------------------+-------------------+----------------+--------------+--------+\n|         trip_id|rideable_type|         started_at|           ended_at|start_station_id|end_station_id|rider_id|\n+----------------+-------------+-------------------+-------------------+----------------+--------------+--------+\n|0FEFDE2603568365| classic_bike|2021-02-14 17:52:38|2021-02-14 18:12:09|             525|         16806|   47854|\n|E6159D746B2DBB91|electric_bike|2021-02-09 19:10:18|2021-02-09 19:19:10|    KA1503000012|  TA1305000029|   70870|\n|B32D3199F1C2E75B| classic_bike|2021-02-02 17:49:41|2021-02-02 17:54:06|             637|  TA1305000034|   58974|\n|83E463F23575F4BF|electric_bike|2021-02-23 15:07:23|2021-02-23 15:22:37|           13216|  TA1309000055|   39608|\n|BDAA7E3494E8D545|electric_bike|2021-02-24 15:43:33|2021-02-24 15:49:05|           18003|  KP1705001026|   36267|\n|A772742351171257| classic_bike|2021-02-01 17:47:42|2021-02-01 17:48:33|    KP1705001026|  KP1705001026|   50104|\n|295476889D9B79F8| classic_bike|2021-02-11 18:33:53|2021-02-11 18:35:09|           18003|         18003|   19618|\n|362087194BA4CC9A| classic_bike|2021-02-27 15:13:39|2021-02-27 15:36:36|    KP1705001026|  KP1705001026|   16732|\n|21630F715038CCB0| classic_bike|2021-02-20 08:59:42|2021-02-20 09:17:04|    KP1705001026|  KP1705001026|   57068|\n|A977EB7FE7F5CD3A| classic_bike|2021-02-20 08:58:16|2021-02-20 08:58:41|    KP1705001026|  KP1705001026|   32712|\n+----------------+-------------+-------------------+-------------------+----------------+--------------+--------+\nonly showing top 10 rows\n\n"
     ]
    }
   ],
   "source": [
    "df_trips = spark.read.format(\"csv\") \\\n",
    "                  .option('inferSchema', 'false') \\\n",
    "                  .option('header', 'true') \\\n",
    "                  .option('sep',',') \\\n",
    "                  .load('/FileStore/tables/trips.csv') \\\n",
    "                  .toDF('trip_id','rideable_type','started_at','ended_at','start_station_id','end_station_id','rider_id') \n",
    "df_trips.show(10)  \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "0c8ab68f-2f23-4924-a381-1c16b4c2ee08",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n |-- trip_id: string (nullable = true)\n |-- rideable_type: string (nullable = true)\n |-- started_at: string (nullable = true)\n |-- ended_at: string (nullable = true)\n |-- start_station_id: string (nullable = true)\n |-- end_station_id: string (nullable = true)\n |-- rider_id: string (nullable = true)\n\n"
     ]
    }
   ],
   "source": [
    "df_trips.printSchema()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "aa36af3f-3b19-4315-bdb2-a1aff77d0e17",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## Write Trips DataFrame to Delta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "45e0f3e3-1414-4b9a-a5fa-835edb1f9707",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "df_trips.write.format('delta')\\\n",
    "        .mode('overwrite')\\\n",
    "        .option(\"overwriteSchema\", \"true\")\\\n",
    "        .save('/delta/trips')"
   ]
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "dashboards": [],
   "language": "python",
   "notebookMetadata": {
    "pythonIndentUnit": 4
   },
   "notebookName": "Extract data",
   "widgets": {}
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}