02-workshop-1-data-ingestion/dlt_workshop.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "collapsed_sections": [
        "3RGdjQnN7J8A"
      ]
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "source": [
        "# **Install `dlt`⏳**"
      ],
      "metadata": {
        "id": "3RGdjQnN7J8A"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "What is dlt?\n",
        "\n",
        "* dlt is an open-source library that you can add to your Python scripts to load data from various and often messy data sources into well-structured, live datasets.\n",
        "* You can install it using pip and there's no need to start any backends or containers. You can simply import dlt in your Python script and write a simple pipeline to load data from sources like APIs, databases, files, etc. into a destination of your choice.\n",
        "\n",
        "Here are a few reasons why you should use dlt:\n",
        "\n",
        "* Automated maintenance: With schema inference and evolution and alerts, and with short declarative code, maintenance becomes simple.\n",
        "* Run it where Python runs: You can use dlt on Airflow, serverless functions, notebooks. It doesn't require external APIs, backends or containers, and scales on both micro and large infrastructures.\n",
        "* User-friendly, declarative interface: dlt provides a user-friendly interface that removes knowledge obstacles for beginners while empowering senior professionals.\n",
        "\n",
        "Benefits: As a data engineer, dlt offers several benefits:\n",
        "\n",
        "* Efficient Data Extraction and Loading: dlt simplifies the process of extracting and loading data. It allows you to decorate your data-producing functions with loading or incremental extraction metadata, enabling dlt to extract and load data according to your custom logic. This is particularly useful when dealing with large datasets, as dlt supports scalability through iterators, chunking, and parallelization. Read more\n",
        "\n",
        "* Automated Schema Management: dlt automatically infers a schema from data and loads the data to the destination. It can easily adapt and structure data as it evolves, reducing the time spent on maintenance and development. This ensures data consistency and quality. Read more\n",
        "* Data Governance Support: dlt pipelines offer robust governance support through pipeline metadata utilization, schema enforcement and curation, and schema change alerts. This promotes data consistency, traceability, and control throughout the data processing lifecycle. Read more\n",
        "\n",
        "* Flexibility and Scalability: dlt can be used on Airflow, serverless functions, notebooks, and scales on both micro and large infrastructures. It also offers several mechanisms and configuration options to scale up and fine-tune pipelines. Read more\n",
        "\n",
        "* Post-Loading Transformations: dlt provides several options for transformations after loading the data, including using dbt, the dlt SQL client, or Pandas. This allows you to shape and manipulate the data before or after loading it, allowing you to meet specific requirements and ensure data quality and consistency. Read more\n",
        "\n"
      ],
      "metadata": {
        "id": "Pai77xXpOyhU"
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "7UvUQO__kgUP"
      },
      "outputs": [],
      "source": [
        "%%capture\n",
        "!pip install dlt[duckdb] # Install dlt with all the necessary DuckDB dependencies"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Part 1: Data Extraction\n"
      ],
      "metadata": {
        "id": "QYweO0LGOQjZ"
      }
    },
    {
      "cell_type": "markdown",
      "source": [],
      "metadata": {
        "id": "Awoh5o-Yo_Ve"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Example 1: Extracting API data with a generator\n",
        "\n",
        "Premise:\n",
        "\n",
        "For this example, we created a simple http api that returns json \"page by page\",  1000 records per page.\n",
        "\n",
        "It accepts a parameter called `page`, representing the page number.\n",
        "If we request a larger page number than there is data, we get an empty response.\n",
        "\n",
        "To get the pages, we write a loop that asks for pages starting from 1 and increasing, until we receive an empty page.\n",
        "\n",
        "As we do not know ahead of time how many pages have data and if they fit in memory, yielding the data so it can be handled page by page scales better than first collecting all pages in memory and then returning them."
      ],
      "metadata": {
        "id": "UqFgA2AipT-f"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import requests\n",
        "\n",
        "\n",
        "\n",
        "BASE_API_URL = \"https://us-central1-dlthub-analytics.cloudfunctions.net/data_engineering_zoomcamp_api\"\n",
        "\n",
        "# I call this a paginated getter\n",
        "# as it's a function that gets data\n",
        "# and also paginates until there is no more data\n",
        "# by yielding pages, we \"microbatch\", which speeds up downstream processing\n",
        "\n",
        "def paginated_getter():\n",
        "    page_number = 1\n",
        "\n",
        "    while True:\n",
        "        # Set the query parameters\n",
        "        params = {'page': page_number}\n",
        "\n",
        "        # Make the GET request to the API\n",
        "        response = requests.get(BASE_API_URL, params=params)\n",
        "        response.raise_for_status()  # Raise an HTTPError for bad responses\n",
        "        page_json = response.json()\n",
        "        print(f'got page number {page_number} with {len(page_json)} records')\n",
        "\n",
        "        # if the page has no records, stop iterating\n",
        "        if page_json:\n",
        "            yield page_json\n",
        "            page_number += 1\n",
        "        else:\n",
        "            # No more data, break the loop\n",
        "            break\n",
        "\n",
        "\n",
        "if __name__ == '__main__':\n",
        "    # Use the generator to iterate over pages\n",
        "    for page_data in paginated_getter():\n",
        "        # Process each page as needed\n",
        "        print(page_data)\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "1zBFy18_Sa6d",
        "outputId": "53a0bf27-206b-4fe4-c236-e3314a104e40"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "\u001b[1;30;43mThis cell output is too large and can only be displayed while logged in.\u001b[0m\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Example 2: The \"bad\" way to download a file\n",
        "\n",
        "In this example we download a json lines file.\n",
        "\n",
        "Since the download is text but we want to work with iterable data strutures for loading, we convert the contents to list of jsons.\n",
        "\n",
        "This is a less than ideal approach because if the file size is unknown, we run the risk of running out of memory. In the case of machines that run multiple jobs, an out of memory error runs the risk of killing not just the current jobs but also anything else running on the machine at the time - a situation most data engineers **really really** like to avoid."
      ],
      "metadata": {
        "id": "LgI8VPNCrdGi"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import requests\n",
        "import json\n",
        "\n",
        "\n",
        "def download_and_read_jsonl(url):\n",
        "    response = requests.get(url)\n",
        "    response.raise_for_status()  # Raise an HTTPError for bad responses\n",
        "    data = response.text.splitlines()\n",
        "    parsed_data = [json.loads(line) for line in data]\n",
        "    return parsed_data\n",
        "\n",
        "\n",
        "# time the download\n",
        "import time\n",
        "start = time.time()\n",
        "\n",
        "url = \"https://storage.googleapis.com/dtc_zoomcamp_api/yellow_tripdata_2009-06.jsonl\"\n",
        "downloaded_data = download_and_read_jsonl(url)\n",
        "\n",
        "if downloaded_data:\n",
        "    # Process or print the downloaded data as needed\n",
        "    print(downloaded_data[:5])  # Print the first 5 entries as an example\n",
        "\n",
        "# time the download\n",
        "end = time.time()\n",
        "print(end - start)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "bW4AI4yerkTj",
        "outputId": "886f737e-ed56-4057-ff1f-51576eec5d68"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[{'vendor_name': 'VTS', 'Trip_Pickup_DateTime': '2009-06-14 23:23:00', 'Trip_Dropoff_DateTime': '2009-06-14 23:48:00', 'Passenger_Count': 1, 'Trip_Distance': 17.52, 'Start_Lon': -73.787442, 'Start_Lat': 40.641525, 'Rate_Code': None, 'store_and_forward': None, 'End_Lon': -73.980072, 'End_Lat': 40.742963, 'Payment_Type': 'Credit', 'Fare_Amt': 45.0, 'surcharge': 0.0, 'mta_tax': None, 'Tip_Amt': 9.0, 'Tolls_Amt': 4.15, 'Total_Amt': 58.15}, {'vendor_name': 'VTS', 'Trip_Pickup_DateTime': '2009-06-18 17:35:00', 'Trip_Dropoff_DateTime': '2009-06-18 17:43:00', 'Passenger_Count': 1, 'Trip_Distance': 1.56, 'Start_Lon': -74.009767, 'Start_Lat': 40.722065, 'Rate_Code': None, 'store_and_forward': None, 'End_Lon': -74.005698, 'End_Lat': 40.740187, 'Payment_Type': 'Credit', 'Fare_Amt': 6.5, 'surcharge': 1.0, 'mta_tax': None, 'Tip_Amt': 1.0, 'Tolls_Amt': 0.0, 'Total_Amt': 8.5}, {'vendor_name': 'VTS', 'Trip_Pickup_DateTime': '2009-06-10 18:08:00', 'Trip_Dropoff_DateTime': '2009-06-10 18:27:00', 'Passenger_Count': 5, 'Trip_Distance': 3.37, 'Start_Lon': -73.983038, 'Start_Lat': 40.761945, 'Rate_Code': None, 'store_and_forward': None, 'End_Lon': -74.004745, 'End_Lat': 40.718043, 'Payment_Type': 'Credit', 'Fare_Amt': 12.5, 'surcharge': 1.0, 'mta_tax': None, 'Tip_Amt': 2.0, 'Tolls_Amt': 0.0, 'Total_Amt': 15.5}, {'vendor_name': 'VTS', 'Trip_Pickup_DateTime': '2009-06-14 23:54:00', 'Trip_Dropoff_DateTime': '2009-06-14 23:58:00', 'Passenger_Count': 1, 'Trip_Distance': 1.11, 'Start_Lon': -73.992247, 'Start_Lat': 40.749802, 'Rate_Code': None, 'store_and_forward': None, 'End_Lon': -73.985233, 'End_Lat': 40.739637, 'Payment_Type': 'CASH', 'Fare_Amt': 4.9, 'surcharge': 0.5, 'mta_tax': None, 'Tip_Amt': 0.0, 'Tolls_Amt': 0.0, 'Total_Amt': 5.4}, {'vendor_name': 'VTS', 'Trip_Pickup_DateTime': '2009-06-13 13:01:00', 'Trip_Dropoff_DateTime': '2009-06-13 13:23:00', 'Passenger_Count': 1, 'Trip_Distance': 11.09, 'Start_Lon': -73.949233, 'Start_Lat': 40.776825, 'Rate_Code': None, 'store_and_forward': None, 'End_Lon': -73.852693, 'End_Lat': 40.730032, 'Payment_Type': 'CASH', 'Fare_Amt': 25.7, 'surcharge': 0.0, 'mta_tax': None, 'Tip_Amt': 0.0, 'Tolls_Amt': 4.15, 'Total_Amt': 29.85}]\n",
            "1.9736332893371582\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Example 3: Extracting file data with a generator \"the best practice way\"\n",
        "\n",
        "\"The best practice way\" here refers to the most scalable way to do it, but if you are confident scale will not be an issue, then the right way might be the simplest :)\n",
        "\n",
        "In this example we download a jsonl (like json, but lines) file.\n",
        "Since it's jsonl, it has lines so we can process it line by line.\n",
        "\n",
        "We stream download it and yield the data.\n",
        "\n",
        "If this file were json and not jsonl, we could use ijson library to break it into lines without loading to memory.\n",
        "\n"
      ],
      "metadata": {
        "id": "Rloz9PKQq5-g"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import requests\n",
        "import json\n",
        "\n",
        "url = \"https://storage.googleapis.com/dtc_zoomcamp_api/yellow_tripdata_2009-06.jsonl\"\n",
        "\n",
        "def stream_download_jsonl(url):\n",
        "    response = requests.get(url, stream=True)\n",
        "    response.raise_for_status()  # Raise an HTTPError for bad responses\n",
        "    for line in response.iter_lines():\n",
        "        if line:\n",
        "            yield json.loads(line)\n",
        "\n",
        "# time the download\n",
        "import time\n",
        "start = time.time()\n",
        "\n",
        "# Use the generator to iterate over rows with minimal memory usage\n",
        "row_counter = 0\n",
        "for row in stream_download_jsonl(url):\n",
        "    print(row)\n",
        "    row_counter += 1\n",
        "    if row_counter >= 5:\n",
        "        break\n",
        "\n",
        "# time the download\n",
        "end = time.time()\n",
        "print(end - start)"
      ],
      "metadata": {
        "id": "29-plLIHOPsP",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "e975d26a-4fb2-4a84-956e-d8dccfac2861"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "{'vendor_name': 'VTS', 'Trip_Pickup_DateTime': '2009-06-14 23:23:00', 'Trip_Dropoff_DateTime': '2009-06-14 23:48:00', 'Passenger_Count': 1, 'Trip_Distance': 17.52, 'Start_Lon': -73.787442, 'Start_Lat': 40.641525, 'Rate_Code': None, 'store_and_forward': None, 'End_Lon': -73.980072, 'End_Lat': 40.742963, 'Payment_Type': 'Credit', 'Fare_Amt': 45.0, 'surcharge': 0.0, 'mta_tax': None, 'Tip_Amt': 9.0, 'Tolls_Amt': 4.15, 'Total_Amt': 58.15}\n",
            "{'vendor_name': 'VTS', 'Trip_Pickup_DateTime': '2009-06-18 17:35:00', 'Trip_Dropoff_DateTime': '2009-06-18 17:43:00', 'Passenger_Count': 1, 'Trip_Distance': 1.56, 'Start_Lon': -74.009767, 'Start_Lat': 40.722065, 'Rate_Code': None, 'store_and_forward': None, 'End_Lon': -74.005698, 'End_Lat': 40.740187, 'Payment_Type': 'Credit', 'Fare_Amt': 6.5, 'surcharge': 1.0, 'mta_tax': None, 'Tip_Amt': 1.0, 'Tolls_Amt': 0.0, 'Total_Amt': 8.5}\n",
            "{'vendor_name': 'VTS', 'Trip_Pickup_DateTime': '2009-06-10 18:08:00', 'Trip_Dropoff_DateTime': '2009-06-10 18:27:00', 'Passenger_Count': 5, 'Trip_Distance': 3.37, 'Start_Lon': -73.983038, 'Start_Lat': 40.761945, 'Rate_Code': None, 'store_and_forward': None, 'End_Lon': -74.004745, 'End_Lat': 40.718043, 'Payment_Type': 'Credit', 'Fare_Amt': 12.5, 'surcharge': 1.0, 'mta_tax': None, 'Tip_Amt': 2.0, 'Tolls_Amt': 0.0, 'Total_Amt': 15.5}\n",
            "{'vendor_name': 'VTS', 'Trip_Pickup_DateTime': '2009-06-14 23:54:00', 'Trip_Dropoff_DateTime': '2009-06-14 23:58:00', 'Passenger_Count': 1, 'Trip_Distance': 1.11, 'Start_Lon': -73.992247, 'Start_Lat': 40.749802, 'Rate_Code': None, 'store_and_forward': None, 'End_Lon': -73.985233, 'End_Lat': 40.739637, 'Payment_Type': 'CASH', 'Fare_Amt': 4.9, 'surcharge': 0.5, 'mta_tax': None, 'Tip_Amt': 0.0, 'Tolls_Amt': 0.0, 'Total_Amt': 5.4}\n",
            "{'vendor_name': 'VTS', 'Trip_Pickup_DateTime': '2009-06-13 13:01:00', 'Trip_Dropoff_DateTime': '2009-06-13 13:23:00', 'Passenger_Count': 1, 'Trip_Distance': 11.09, 'Start_Lon': -73.949233, 'Start_Lat': 40.776825, 'Rate_Code': None, 'store_and_forward': None, 'End_Lon': -73.852693, 'End_Lat': 40.730032, 'Payment_Type': 'CASH', 'Fare_Amt': 25.7, 'surcharge': 0.0, 'mta_tax': None, 'Tip_Amt': 0.0, 'Tolls_Amt': 4.15, 'Total_Amt': 29.85}\n",
            "0.08738827705383301\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Loading the generator (any of the above)\n",
        "\n",
        "We have 3 ways to download the same data. Let's use the fast and reliable way to load some data and inspect it in DuckDB.\n",
        "\n",
        "In this example, we are using `dlt` library to do the loading, which will process data from the generators incrementally, following the same memory management paradigm.\n",
        "\n",
        "We will discuss more details about `dlt` or \"data load tool\" later."
      ],
      "metadata": {
        "id": "pM7qqu1dOrop"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import dlt\n",
        "\n",
        "# define the connection to load to.\n",
        "# We now use duckdb, but you can switch to Bigquery later\n",
        "generators_pipeline = dlt.pipeline(destination='duckdb', dataset_name='generators')\n",
        "\n",
        "\n",
        "# we can load any generator to a table at the pipeline destnation as follows:\n",
        "info = generators_pipeline.run(paginated_getter(),\n",
        "\t\t\t\t\t\t\t\t\t\ttable_name=\"http_download\",\n",
        "\t\t\t\t\t\t\t\t\t\twrite_disposition=\"replace\")\n",
        "\n",
        "# the outcome metadata is returned by the load and we can inspect it by printing it.\n",
        "print(info)\n",
        "\n",
        "# we can load the next generator to the same or to a different table.\n",
        "info = generators_pipeline.run(stream_download_jsonl(url),\n",
        "\t\t\t\t\t\t\t\t\t\ttable_name=\"stream_download\",\n",
        "\t\t\t\t\t\t\t\t\t\twrite_disposition=\"replace\")\n",
        "\n",
        "print(info)\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "N9PrR_edOvSw",
        "outputId": "b44bccf2-ea00-45ac-ff4e-9d7d054572fd"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "got page number 1 with 1000 records\n",
            "got page number 2 with 1000 records\n",
            "got page number 3 with 1000 records\n",
            "got page number 4 with 1000 records\n",
            "got page number 5 with 1000 records\n",
            "got page number 6 with 1000 records\n",
            "got page number 7 with 1000 records\n",
            "got page number 8 with 1000 records\n",
            "got page number 9 with 1000 records\n",
            "got page number 10 with 1000 records\n",
            "got page number 11 with 0 records\n",
            "Pipeline dlt_colab_kernel_launcher load step completed in 1.50 seconds\n",
            "1 load package(s) were loaded to destination duckdb and into dataset generators\n",
            "The duckdb destination used duckdb:////content/dlt_colab_kernel_launcher.duckdb location to store data\n",
            "Load package 1707149342.0762298 is LOADED and contains no failed jobs\n",
            "Pipeline dlt_colab_kernel_launcher load step completed in 1.44 seconds\n",
            "1 load package(s) were loaded to destination duckdb and into dataset generators\n",
            "The duckdb destination used duckdb:////content/dlt_colab_kernel_launcher.duckdb location to store data\n",
            "Load package 1707149369.8628578 is LOADED and contains no failed jobs\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# show outcome\n",
        "\n",
        "import duckdb\n",
        "\n",
        "conn = duckdb.connect(f\"{generators_pipeline.pipeline_name}.duckdb\")\n",
        "\n",
        "# let's see the tables\n",
        "conn.sql(f\"SET search_path = '{generators_pipeline.dataset_name}'\")\n",
        "print('Loaded tables: ')\n",
        "display(conn.sql(\"show tables\"))\n",
        "\n",
        "# and the data\n",
        "\n",
        "print(\"\\n\\n\\n http_download table below:\")\n",
        "\n",
        "rides = conn.sql(\"SELECT * FROM http_download\").df()\n",
        "display(rides)\n",
        "\n",
        "print(\"\\n\\n\\n stream_download table below:\")\n",
        "\n",
        "passengers = conn.sql(\"SELECT * FROM stream_download\").df()\n",
        "display(passengers)\n",
        "\n",
        "# As you can see, the same data was loaded in both cases."
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1000
        },
        "id": "QSDNShTI45qC",
        "outputId": "4f8932da-01c2-4189-c519-fde0d1d9e828"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Loaded tables: \n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "┌─────────────────────┐\n",
              "│        name         │\n",
              "│       varchar       │\n",
              "├─────────────────────┤\n",
              "│ _dlt_loads          │\n",
              "│ _dlt_pipeline_state │\n",
              "│ _dlt_version        │\n",
              "│ http_download       │\n",
              "│ stream_download     │\n",
              "└─────────────────────┘"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\n",
            "\n",
            "\n",
            " http_download table below:\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "        end_lat    end_lon  fare_amt  passenger_count payment_type  start_lat  \\\n",
              "0     40.742963 -73.980072      45.0                1       Credit  40.641525   \n",
              "1     40.740187 -74.005698       6.5                1       Credit  40.722065   \n",
              "2     40.718043 -74.004745      12.5                5       Credit  40.761945   \n",
              "3     40.739637 -73.985233       4.9                1         CASH  40.749802   \n",
              "4     40.730032 -73.852693      25.7                1         CASH  40.776825   \n",
              "...         ...        ...       ...              ...          ...        ...   \n",
              "9995  40.783522 -73.970690       5.7                1         CASH  40.778560   \n",
              "9996  40.777200 -73.964197       4.1                1         CASH  40.779800   \n",
              "9997  40.780172 -73.957617       6.1                1         CASH  40.788388   \n",
              "9998  40.777342 -73.957242       5.7                1         CASH  40.773828   \n",
              "9999  40.757122 -73.986293       6.5                1       Credit  40.756313   \n",
              "\n",
              "      start_lon  tip_amt  tolls_amt  total_amt  trip_distance  \\\n",
              "0    -73.787442      9.0       4.15      58.15          17.52   \n",
              "1    -74.009767      1.0       0.00       8.50           1.56   \n",
              "2    -73.983038      2.0       0.00      15.50           3.37   \n",
              "3    -73.992247      0.0       0.00       5.40           1.11   \n",
              "4    -73.949233      0.0       4.15      29.85          11.09   \n",
              "...         ...      ...        ...        ...            ...   \n",
              "9995 -73.953660      0.0       0.00       5.70           1.16   \n",
              "9996 -73.974297      0.0       0.00       4.10           0.89   \n",
              "9997 -73.976758      0.0       0.00       6.10           1.30   \n",
              "9998 -73.956690      0.0       0.00       6.20           0.97   \n",
              "9999 -73.972948      2.0       0.00       8.50           0.92   \n",
              "\n",
              "        trip_dropoff_date_time     trip_pickup_date_time  surcharge  \\\n",
              "0    2009-06-14 23:48:00+00:00 2009-06-14 23:23:00+00:00        0.0   \n",
              "1    2009-06-18 17:43:00+00:00 2009-06-18 17:35:00+00:00        1.0   \n",
              "2    2009-06-10 18:27:00+00:00 2009-06-10 18:08:00+00:00        1.0   \n",
              "3    2009-06-14 23:58:00+00:00 2009-06-14 23:54:00+00:00        0.5   \n",
              "4    2009-06-13 13:23:00+00:00 2009-06-13 13:01:00+00:00        0.0   \n",
              "...                        ...                       ...        ...   \n",
              "9995 2009-06-19 11:28:00+00:00 2009-06-19 11:22:00+00:00        0.0   \n",
              "9996 2009-06-17 07:43:00+00:00 2009-06-17 07:41:00+00:00        0.0   \n",
              "9997 2009-06-19 11:46:00+00:00 2009-06-19 11:39:00+00:00        0.0   \n",
              "9998 2009-06-17 04:19:00+00:00 2009-06-17 04:13:00+00:00        0.5   \n",
              "9999 2009-06-17 08:34:00+00:00 2009-06-17 08:24:00+00:00        0.0   \n",
              "\n",
              "     vendor_name        _dlt_load_id         _dlt_id  store_and_forward  \n",
              "0            VTS  1707149342.0762298  TMFOk/TVI/OneQ                NaN  \n",
              "1            VTS  1707149342.0762298  SJ8vsEQWR9NsvA                NaN  \n",
              "2            VTS  1707149342.0762298  ECr1xFFMENZ49g                NaN  \n",
              "3            VTS  1707149342.0762298  WJ16OJ7NvsTDLA                NaN  \n",
              "4            VTS  1707149342.0762298  mnRSwtipcJYxhA                NaN  \n",
              "...          ...                 ...             ...                ...  \n",
              "9995         VTS  1707149342.0762298  V5+DvuYKgUCyKg                NaN  \n",
              "9996         VTS  1707149342.0762298  3gHUOTiVCzsmlA                NaN  \n",
              "9997         VTS  1707149342.0762298  rRpGUQZZyaGMxA                NaN  \n",
              "9998         VTS  1707149342.0762298  iMC1tk37dQclQg                NaN  \n",
              "9999         VTS  1707149342.0762298  OmS19DDUOBdN/w                NaN  \n",
              "\n",
              "[10000 rows x 18 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-260723bb-df57-45d0-990c-101273bcdded\" 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>end_lat</th>\n",
              "      <th>end_lon</th>\n",
              "      <th>fare_amt</th>\n",
              "      <th>passenger_count</th>\n",
              "      <th>payment_type</th>\n",
              "      <th>start_lat</th>\n",
              "      <th>start_lon</th>\n",
              "      <th>tip_amt</th>\n",
              "      <th>tolls_amt</th>\n",
              "      <th>total_amt</th>\n",
              "      <th>trip_distance</th>\n",
              "      <th>trip_dropoff_date_time</th>\n",
              "      <th>trip_pickup_date_time</th>\n",
              "      <th>surcharge</th>\n",
              "      <th>vendor_name</th>\n",
              "      <th>_dlt_load_id</th>\n",
              "      <th>_dlt_id</th>\n",
              "      <th>store_and_forward</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>40.742963</td>\n",
              "      <td>-73.980072</td>\n",
              "      <td>45.0</td>\n",
              "      <td>1</td>\n",
              "      <td>Credit</td>\n",
              "      <td>40.641525</td>\n",
              "      <td>-73.787442</td>\n",
              "      <td>9.0</td>\n",
              "      <td>4.15</td>\n",
              "      <td>58.15</td>\n",
              "      <td>17.52</td>\n",
              "      <td>2009-06-14 23:48:00+00:00</td>\n",
              "      <td>2009-06-14 23:23:00+00:00</td>\n",
              "      <td>0.0</td>\n",
              "      <td>VTS</td>\n",
              "      <td>1707149342.0762298</td>\n",
              "      <td>TMFOk/TVI/OneQ</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>40.740187</td>\n",
              "      <td>-74.005698</td>\n",
              "      <td>6.5</td>\n",
              "      <td>1</td>\n",
              "      <td>Credit</td>\n",
              "      <td>40.722065</td>\n",
              "      <td>-74.009767</td>\n",
              "      <td>1.0</td>\n",
              "      <td>0.00</td>\n",
              "      <td>8.50</td>\n",
              "      <td>1.56</td>\n",
              "      <td>2009-06-18 17:43:00+00:00</td>\n",
              "      <td>2009-06-18 17:35:00+00:00</td>\n",
              "      <td>1.0</td>\n",
              "      <td>VTS</td>\n",
              "      <td>1707149342.0762298</td>\n",
              "      <td>SJ8vsEQWR9NsvA</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>40.718043</td>\n",
              "      <td>-74.004745</td>\n",
              "      <td>12.5</td>\n",
              "      <td>5</td>\n",
              "      <td>Credit</td>\n",
              "      <td>40.761945</td>\n",
              "      <td>-73.983038</td>\n",
              "      <td>2.0</td>\n",
              "      <td>0.00</td>\n",
              "      <td>15.50</td>\n",
              "      <td>3.37</td>\n",
              "      <td>2009-06-10 18:27:00+00:00</td>\n",
              "      <td>2009-06-10 18:08:00+00:00</td>\n",
              "      <td>1.0</td>\n",
              "      <td>VTS</td>\n",
              "      <td>1707149342.0762298</td>\n",
              "      <td>ECr1xFFMENZ49g</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>40.739637</td>\n",
              "      <td>-73.985233</td>\n",
              "      <td>4.9</td>\n",
              "      <td>1</td>\n",
              "      <td>CASH</td>\n",
              "      <td>40.749802</td>\n",
              "      <td>-73.992247</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.00</td>\n",
              "      <td>5.40</td>\n",
              "      <td>1.11</td>\n",
              "      <td>2009-06-14 23:58:00+00:00</td>\n",
              "      <td>2009-06-14 23:54:00+00:00</td>\n",
              "      <td>0.5</td>\n",
              "      <td>VTS</td>\n",
              "      <td>1707149342.0762298</td>\n",
              "      <td>WJ16OJ7NvsTDLA</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>40.730032</td>\n",
              "      <td>-73.852693</td>\n",
              "      <td>25.7</td>\n",
              "      <td>1</td>\n",
              "      <td>CASH</td>\n",
              "      <td>40.776825</td>\n",
              "      <td>-73.949233</td>\n",
              "      <td>0.0</td>\n",
              "      <td>4.15</td>\n",
              "      <td>29.85</td>\n",
              "      <td>11.09</td>\n",
              "      <td>2009-06-13 13:23:00+00:00</td>\n",
              "      <td>2009-06-13 13:01:00+00:00</td>\n",
              "      <td>0.0</td>\n",
              "      <td>VTS</td>\n",
              "      <td>1707149342.0762298</td>\n",
              "      <td>mnRSwtipcJYxhA</td>\n",
              "      <td>NaN</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",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9995</th>\n",
              "      <td>40.783522</td>\n",
              "      <td>-73.970690</td>\n",
              "      <td>5.7</td>\n",
              "      <td>1</td>\n",
              "      <td>CASH</td>\n",
              "      <td>40.778560</td>\n",
              "      <td>-73.953660</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.00</td>\n",
              "      <td>5.70</td>\n",
              "      <td>1.16</td>\n",
              "      <td>2009-06-19 11:28:00+00:00</td>\n",
              "      <td>2009-06-19 11:22:00+00:00</td>\n",
              "      <td>0.0</td>\n",
              "      <td>VTS</td>\n",
              "      <td>1707149342.0762298</td>\n",
              "      <td>V5+DvuYKgUCyKg</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9996</th>\n",
              "      <td>40.777200</td>\n",
              "      <td>-73.964197</td>\n",
              "      <td>4.1</td>\n",
              "      <td>1</td>\n",
              "      <td>CASH</td>\n",
              "      <td>40.779800</td>\n",
              "      <td>-73.974297</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.00</td>\n",
              "      <td>4.10</td>\n",
              "      <td>0.89</td>\n",
              "      <td>2009-06-17 07:43:00+00:00</td>\n",
              "      <td>2009-06-17 07:41:00+00:00</td>\n",
              "      <td>0.0</td>\n",
              "      <td>VTS</td>\n",
              "      <td>1707149342.0762298</td>\n",
              "      <td>3gHUOTiVCzsmlA</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9997</th>\n",
              "      <td>40.780172</td>\n",
              "      <td>-73.957617</td>\n",
              "      <td>6.1</td>\n",
              "      <td>1</td>\n",
              "      <td>CASH</td>\n",
              "      <td>40.788388</td>\n",
              "      <td>-73.976758</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.00</td>\n",
              "      <td>6.10</td>\n",
              "      <td>1.30</td>\n",
              "      <td>2009-06-19 11:46:00+00:00</td>\n",
              "      <td>2009-06-19 11:39:00+00:00</td>\n",
              "      <td>0.0</td>\n",
              "      <td>VTS</td>\n",
              "      <td>1707149342.0762298</td>\n",
              "      <td>rRpGUQZZyaGMxA</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9998</th>\n",
              "      <td>40.777342</td>\n",
              "      <td>-73.957242</td>\n",
              "      <td>5.7</td>\n",
              "      <td>1</td>\n",
              "      <td>CASH</td>\n",
              "      <td>40.773828</td>\n",
              "      <td>-73.956690</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.00</td>\n",
              "      <td>6.20</td>\n",
              "      <td>0.97</td>\n",
              "      <td>2009-06-17 04:19:00+00:00</td>\n",
              "      <td>2009-06-17 04:13:00+00:00</td>\n",
              "      <td>0.5</td>\n",
              "      <td>VTS</td>\n",
              "      <td>1707149342.0762298</td>\n",
              "      <td>iMC1tk37dQclQg</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9999</th>\n",
              "      <td>40.757122</td>\n",
              "      <td>-73.986293</td>\n",
              "      <td>6.5</td>\n",
              "      <td>1</td>\n",
              "      <td>Credit</td>\n",
              "      <td>40.756313</td>\n",
              "      <td>-73.972948</td>\n",
              "      <td>2.0</td>\n",
              "      <td>0.00</td>\n",
              "      <td>8.50</td>\n",
              "      <td>0.92</td>\n",
              "      <td>2009-06-17 08:34:00+00:00</td>\n",
              "      <td>2009-06-17 08:24:00+00:00</td>\n",
              "      <td>0.0</td>\n",
              "      <td>VTS</td>\n",
              "      <td>1707149342.0762298</td>\n",
              "      <td>OmS19DDUOBdN/w</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>10000 rows × 18 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-260723bb-df57-45d0-990c-101273bcdded')\"\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-260723bb-df57-45d0-990c-101273bcdded 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-260723bb-df57-45d0-990c-101273bcdded');\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-b4da73be-27f7-447d-8cc6-ad8fbb4b8d93\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-b4da73be-27f7-447d-8cc6-ad8fbb4b8d93')\"\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-b4da73be-27f7-447d-8cc6-ad8fbb4b8d93 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "    </div>\n",
              "  </div>\n"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\n",
            "\n",
            "\n",
            " stream_download table below:\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "     vendor_name     trip_pickup_date_time    trip_dropoff_date_time  \\\n",
              "0            VTS 2009-06-14 23:23:00+00:00 2009-06-14 23:48:00+00:00   \n",
              "1            VTS 2009-06-18 17:35:00+00:00 2009-06-18 17:43:00+00:00   \n",
              "2            VTS 2009-06-10 18:08:00+00:00 2009-06-10 18:27:00+00:00   \n",
              "3            VTS 2009-06-14 23:54:00+00:00 2009-06-14 23:58:00+00:00   \n",
              "4            VTS 2009-06-13 13:01:00+00:00 2009-06-13 13:23:00+00:00   \n",
              "...          ...                       ...                       ...   \n",
              "9995         VTS 2009-06-19 11:22:00+00:00 2009-06-19 11:28:00+00:00   \n",
              "9996         VTS 2009-06-17 07:41:00+00:00 2009-06-17 07:43:00+00:00   \n",
              "9997         VTS 2009-06-19 11:39:00+00:00 2009-06-19 11:46:00+00:00   \n",
              "9998         VTS 2009-06-17 04:13:00+00:00 2009-06-17 04:19:00+00:00   \n",
              "9999         VTS 2009-06-17 08:24:00+00:00 2009-06-17 08:34:00+00:00   \n",
              "\n",
              "      passenger_count  trip_distance  start_lon  start_lat    end_lon  \\\n",
              "0                   1          17.52 -73.787442  40.641525 -73.980072   \n",
              "1                   1           1.56 -74.009767  40.722065 -74.005698   \n",
              "2                   5           3.37 -73.983038  40.761945 -74.004745   \n",
              "3                   1           1.11 -73.992247  40.749802 -73.985233   \n",
              "4                   1          11.09 -73.949233  40.776825 -73.852693   \n",
              "...               ...            ...        ...        ...        ...   \n",
              "9995                1           1.16 -73.953660  40.778560 -73.970690   \n",
              "9996                1           0.89 -73.974297  40.779800 -73.964197   \n",
              "9997                1           1.30 -73.976758  40.788388 -73.957617   \n",
              "9998                1           0.97 -73.956690  40.773828 -73.957242   \n",
              "9999                1           0.92 -73.972948  40.756313 -73.986293   \n",
              "\n",
              "        end_lat payment_type  fare_amt  surcharge  tip_amt  tolls_amt  \\\n",
              "0     40.742963       Credit      45.0        0.0      9.0       4.15   \n",
              "1     40.740187       Credit       6.5        1.0      1.0       0.00   \n",
              "2     40.718043       Credit      12.5        1.0      2.0       0.00   \n",
              "3     40.739637         CASH       4.9        0.5      0.0       0.00   \n",
              "4     40.730032         CASH      25.7        0.0      0.0       4.15   \n",
              "...         ...          ...       ...        ...      ...        ...   \n",
              "9995  40.783522         CASH       5.7        0.0      0.0       0.00   \n",
              "9996  40.777200         CASH       4.1        0.0      0.0       0.00   \n",
              "9997  40.780172         CASH       6.1        0.0      0.0       0.00   \n",
              "9998  40.777342         CASH       5.7        0.5      0.0       0.00   \n",
              "9999  40.757122       Credit       6.5        0.0      2.0       0.00   \n",
              "\n",
              "      total_amt        _dlt_load_id         _dlt_id  store_and_forward  \n",
              "0         58.15  1707149369.8628578  JnbfJ0jwLQC7Bg                NaN  \n",
              "1          8.50  1707149369.8628578  GbdfwgfDuU8oVA                NaN  \n",
              "2         15.50  1707149369.8628578  t+dAetvDHmQ8Ow                NaN  \n",
              "3          5.40  1707149369.8628578  wXYEl9kyoOtX7w                NaN  \n",
              "4         29.85  1707149369.8628578  lucova9u0TupBw                NaN  \n",
              "...         ...                 ...             ...                ...  \n",
              "9995       5.70  1707149369.8628578  X+3d+lj/j8TC3Q                NaN  \n",
              "9996       4.10  1707149369.8628578  /XyFq0QnJYuiyw                NaN  \n",
              "9997       6.10  1707149369.8628578  8JK1Q3abvlej+Q                NaN  \n",
              "9998       6.20  1707149369.8628578  42i79YHmGdNYvQ                NaN  \n",
              "9999       8.50  1707149369.8628578  Jy/fWgYImrwu3w                NaN  \n",
              "\n",
              "[10000 rows x 18 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-39f114b5-79a1-424c-a898-16e52015ca4f\" 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>vendor_name</th>\n",
              "      <th>trip_pickup_date_time</th>\n",
              "      <th>trip_dropoff_date_time</th>\n",
              "      <th>passenger_count</th>\n",
              "      <th>trip_distance</th>\n",
              "      <th>start_lon</th>\n",
              "      <th>start_lat</th>\n",
              "      <th>end_lon</th>\n",
              "      <th>end_lat</th>\n",
              "      <th>payment_type</th>\n",
              "      <th>fare_amt</th>\n",
              "      <th>surcharge</th>\n",
              "      <th>tip_amt</th>\n",
              "      <th>tolls_amt</th>\n",
              "      <th>total_amt</th>\n",
              "      <th>_dlt_load_id</th>\n",
              "      <th>_dlt_id</th>\n",
              "      <th>store_and_forward</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>VTS</td>\n",
              "      <td>2009-06-14 23:23:00+00:00</td>\n",
              "      <td>2009-06-14 23:48:00+00:00</td>\n",
              "      <td>1</td>\n",
              "      <td>17.52</td>\n",
              "      <td>-73.787442</td>\n",
              "      <td>40.641525</td>\n",
              "      <td>-73.980072</td>\n",
              "      <td>40.742963</td>\n",
              "      <td>Credit</td>\n",
              "      <td>45.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>9.0</td>\n",
              "      <td>4.15</td>\n",
              "      <td>58.15</td>\n",
              "      <td>1707149369.8628578</td>\n",
              "      <td>JnbfJ0jwLQC7Bg</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>VTS</td>\n",
              "      <td>2009-06-18 17:35:00+00:00</td>\n",
              "      <td>2009-06-18 17:43:00+00:00</td>\n",
              "      <td>1</td>\n",
              "      <td>1.56</td>\n",
              "      <td>-74.009767</td>\n",
              "      <td>40.722065</td>\n",
              "      <td>-74.005698</td>\n",
              "      <td>40.740187</td>\n",
              "      <td>Credit</td>\n",
              "      <td>6.5</td>\n",
              "      <td>1.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>0.00</td>\n",
              "      <td>8.50</td>\n",
              "      <td>1707149369.8628578</td>\n",
              "      <td>GbdfwgfDuU8oVA</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>VTS</td>\n",
              "      <td>2009-06-10 18:08:00+00:00</td>\n",
              "      <td>2009-06-10 18:27:00+00:00</td>\n",
              "      <td>5</td>\n",
              "      <td>3.37</td>\n",
              "      <td>-73.983038</td>\n",
              "      <td>40.761945</td>\n",
              "      <td>-74.004745</td>\n",
              "      <td>40.718043</td>\n",
              "      <td>Credit</td>\n",
              "      <td>12.5</td>\n",
              "      <td>1.0</td>\n",
              "      <td>2.0</td>\n",
              "      <td>0.00</td>\n",
              "      <td>15.50</td>\n",
              "      <td>1707149369.8628578</td>\n",
              "      <td>t+dAetvDHmQ8Ow</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>VTS</td>\n",
              "      <td>2009-06-14 23:54:00+00:00</td>\n",
              "      <td>2009-06-14 23:58:00+00:00</td>\n",
              "      <td>1</td>\n",
              "      <td>1.11</td>\n",
              "      <td>-73.992247</td>\n",
              "      <td>40.749802</td>\n",
              "      <td>-73.985233</td>\n",
              "      <td>40.739637</td>\n",
              "      <td>CASH</td>\n",
              "      <td>4.9</td>\n",
              "      <td>0.5</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.00</td>\n",
              "      <td>5.40</td>\n",
              "      <td>1707149369.8628578</td>\n",
              "      <td>wXYEl9kyoOtX7w</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>VTS</td>\n",
              "      <td>2009-06-13 13:01:00+00:00</td>\n",
              "      <td>2009-06-13 13:23:00+00:00</td>\n",
              "      <td>1</td>\n",
              "      <td>11.09</td>\n",
              "      <td>-73.949233</td>\n",
              "      <td>40.776825</td>\n",
              "      <td>-73.852693</td>\n",
              "      <td>40.730032</td>\n",
              "      <td>CASH</td>\n",
              "      <td>25.7</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>4.15</td>\n",
              "      <td>29.85</td>\n",
              "      <td>1707149369.8628578</td>\n",
              "      <td>lucova9u0TupBw</td>\n",
              "      <td>NaN</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",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9995</th>\n",
              "      <td>VTS</td>\n",
              "      <td>2009-06-19 11:22:00+00:00</td>\n",
              "      <td>2009-06-19 11:28:00+00:00</td>\n",
              "      <td>1</td>\n",
              "      <td>1.16</td>\n",
              "      <td>-73.953660</td>\n",
              "      <td>40.778560</td>\n",
              "      <td>-73.970690</td>\n",
              "      <td>40.783522</td>\n",
              "      <td>CASH</td>\n",
              "      <td>5.7</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.00</td>\n",
              "      <td>5.70</td>\n",
              "      <td>1707149369.8628578</td>\n",
              "      <td>X+3d+lj/j8TC3Q</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9996</th>\n",
              "      <td>VTS</td>\n",
              "      <td>2009-06-17 07:41:00+00:00</td>\n",
              "      <td>2009-06-17 07:43:00+00:00</td>\n",
              "      <td>1</td>\n",
              "      <td>0.89</td>\n",
              "      <td>-73.974297</td>\n",
              "      <td>40.779800</td>\n",
              "      <td>-73.964197</td>\n",
              "      <td>40.777200</td>\n",
              "      <td>CASH</td>\n",
              "      <td>4.1</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.00</td>\n",
              "      <td>4.10</td>\n",
              "      <td>1707149369.8628578</td>\n",
              "      <td>/XyFq0QnJYuiyw</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9997</th>\n",
              "      <td>VTS</td>\n",
              "      <td>2009-06-19 11:39:00+00:00</td>\n",
              "      <td>2009-06-19 11:46:00+00:00</td>\n",
              "      <td>1</td>\n",
              "      <td>1.30</td>\n",
              "      <td>-73.976758</td>\n",
              "      <td>40.788388</td>\n",
              "      <td>-73.957617</td>\n",
              "      <td>40.780172</td>\n",
              "      <td>CASH</td>\n",
              "      <td>6.1</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.00</td>\n",
              "      <td>6.10</td>\n",
              "      <td>1707149369.8628578</td>\n",
              "      <td>8JK1Q3abvlej+Q</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9998</th>\n",
              "      <td>VTS</td>\n",
              "      <td>2009-06-17 04:13:00+00:00</td>\n",
              "      <td>2009-06-17 04:19:00+00:00</td>\n",
              "      <td>1</td>\n",
              "      <td>0.97</td>\n",
              "      <td>-73.956690</td>\n",
              "      <td>40.773828</td>\n",
              "      <td>-73.957242</td>\n",
              "      <td>40.777342</td>\n",
              "      <td>CASH</td>\n",
              "      <td>5.7</td>\n",
              "      <td>0.5</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.00</td>\n",
              "      <td>6.20</td>\n",
              "      <td>1707149369.8628578</td>\n",
              "      <td>42i79YHmGdNYvQ</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9999</th>\n",
              "      <td>VTS</td>\n",
              "      <td>2009-06-17 08:24:00+00:00</td>\n",
              "      <td>2009-06-17 08:34:00+00:00</td>\n",
              "      <td>1</td>\n",
              "      <td>0.92</td>\n",
              "      <td>-73.972948</td>\n",
              "      <td>40.756313</td>\n",
              "      <td>-73.986293</td>\n",
              "      <td>40.757122</td>\n",
              "      <td>Credit</td>\n",
              "      <td>6.5</td>\n",
              "      <td>0.0</td>\n",
              "      <td>2.0</td>\n",
              "      <td>0.00</td>\n",
              "      <td>8.50</td>\n",
              "      <td>1707149369.8628578</td>\n",
              "      <td>Jy/fWgYImrwu3w</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>10000 rows × 18 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-39f114b5-79a1-424c-a898-16e52015ca4f')\"\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-39f114b5-79a1-424c-a898-16e52015ca4f 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-39f114b5-79a1-424c-a898-16e52015ca4f');\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-920e2f4b-33d9-45b8-a8ed-ef2f3201c098\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-920e2f4b-33d9-45b8-a8ed-ef2f3201c098')\"\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-920e2f4b-33d9-45b8-a8ed-ef2f3201c098 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "    </div>\n",
              "  </div>\n"
            ]
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Part 2: Normalisation"
      ],
      "metadata": {
        "id": "tzi0xTYb4Duq"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Load nested data with auto normalisation\n",
        "\n",
        "When converting nested data to tabular formats, to keep fragmentations minimal:\n",
        "* Nested dictionaries can be flattened into the parent row to\n",
        "* Nested lists however need to be expressed as separate tables due to the different granularity (1:n relationship)\n",
        "\n",
        "And of course, when going from JSON to DB, we want some things standardised:\n",
        "* Data types such as timestamps should be detected correctly\n",
        "* Column names should be converted to db-compatible names\n",
        "* Unnested sub-tables should be linked to parent tables via auto generated keys\n",
        "\n",
        "\n",
        "For this work, we will use `dlt` library, which is purpose-made to solve such tasks in a scalable way, for example by using generators.\n",
        "\n"
      ],
      "metadata": {
        "id": "QRwRYRLce9i6"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Introducing dlt\n",
        "\n",
        "dlt is a python library created for the purpose of assisting data engineers to build simpler, faster and more robust pipelines with minimal effort.\n",
        "\n",
        "dlt automates much of the tedious work a data engineer would do, and does it in a way that is robust.\n",
        "\n",
        "dlt can handle things like:\n",
        "\n",
        "- Schema: Inferring and evolving schema, alerting changes, using schemas as data contracts.\n",
        "- Typing data, flattening structures, renaming columns to fit database standards.\n",
        "- Processing a stream of events/rows without filling memory. This includes extraction from generators. In our example we will pass the “data” you can see above.\n",
        "- Loading to a variety of dbs of file formats.\n",
        "\n",
        "Read more about dlt [here](https://dlthub.com/docs/intro).\n",
        "\n",
        "Now let’s use it to load our nested json to duckdb:"
      ],
      "metadata": {
        "id": "ZoTtwE5het4C"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import dlt\n",
        "import duckdb\n",
        "\n",
        "data = [\n",
        "    {\n",
        "        \"vendor_name\": \"VTS\",\n",
        "\t\t\t\t\"record_hash\": \"b00361a396177a9cb410ff61f20015ad\",\n",
        "        \"time\": {\n",
        "            \"pickup\": \"2009-06-14 23:23:00\",\n",
        "            \"dropoff\": \"2009-06-14 23:48:00\"\n",
        "        },\n",
        "        \"Trip_Distance\": 17.52,\n",
        "        # nested dictionaries could be flattened\n",
        "        \"coordinates\": { # coordinates__start__lon\n",
        "            \"start\": {\n",
        "                \"lon\": -73.787442,\n",
        "                \"lat\": 40.641525\n",
        "            },\n",
        "            \"end\": {\n",
        "                \"lon\": -73.980072,\n",
        "                \"lat\": 40.742963\n",
        "            }\n",
        "        },\n",
        "        \"Rate_Code\": None,\n",
        "        \"store_and_forward\": None,\n",
        "        \"Payment\": {\n",
        "            \"type\": \"Credit\",\n",
        "            \"amt\": 20.5,\n",
        "            \"surcharge\": 0,\n",
        "            \"mta_tax\": None,\n",
        "            \"tip\": 9,\n",
        "            \"tolls\": 4.15,\n",
        "\t\t\t\t\t\t\"status\": \"booked\"\n",
        "        },\n",
        "        \"Passenger_Count\": 2,\n",
        "        # nested lists need to be expressed as separate tables\n",
        "        \"passengers\": [\n",
        "            {\"name\": \"John\", \"rating\": 4.9},\n",
        "            {\"name\": \"Jack\", \"rating\": 3.9}\n",
        "        ],\n",
        "        \"Stops\": [\n",
        "            {\"lon\": -73.6, \"lat\": 40.6},\n",
        "            {\"lon\": -73.5, \"lat\": 40.5}\n",
        "        ]\n",
        "    },\n",
        "    # ... more data\n",
        "]\n",
        "\n",
        "\n",
        "# define the connection to load to.\n",
        "# We now use duckdb, but you can switch to Bigquery later\n",
        "pipeline = dlt.pipeline(destination='duckdb', dataset_name='taxi_rides')\n",
        "\n",
        "\n",
        "\n",
        "# run with merge write disposition.\n",
        "# This is so scaffolding is created for the next example,\n",
        "# where we look at merging data\n",
        "\n",
        "info = pipeline.run(data,\n",
        "\t\t\t\t\t\t\t\t\t\ttable_name=\"rides\",\n",
        "\t\t\t\t\t\t\t\t\t\twrite_disposition=\"merge\",\n",
        "                    primary_key=\"record_hash\")\n",
        "\n",
        "print(info)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "2i07XAdb67H5",
        "outputId": "0f35397a-a88f-44c7-ab28-01f93b1cdd73"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Pipeline dlt_colab_kernel_launcher load step completed in 0.48 seconds\n",
            "1 load package(s) were loaded to destination duckdb and into dataset taxi_rides\n",
            "The duckdb destination used duckdb:////content/dlt_colab_kernel_launcher.duckdb location to store data\n",
            "Load package 1707150169.2096095 is LOADED and contains no failed jobs\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Inspecting the nested structure, joining the child tables\n",
        "\n",
        "Let's look at what happened during the load\n",
        "- By looking at the loaded tables, we can see our json document got flattened and sub-documents got split into separate tables\n",
        "- We can re-join those child tables to the parent table by using the generated keys `on parent_table._dlt_id = child_table._dlt_parent_id`.\n",
        "- Data types: If you will pay attention to datatypes, you will note that the timestamps, which in json are of string type, are now of timestamp type in the db.\n"
      ],
      "metadata": {
        "id": "kbORAD3Rhsr1"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# show the outcome\n",
        "\n",
        "conn = duckdb.connect(f\"{pipeline.pipeline_name}.duckdb\")\n",
        "\n",
        "# let's see the tables\n",
        "conn.sql(f\"SET search_path = '{pipeline.dataset_name}'\")\n",
        "print('Loaded tables: ')\n",
        "display(conn.sql(\"show tables\"))\n",
        "\n",
        "\n",
        "print(\"\\n\\n\\n Rides table below: Note the times are properly typed\")\n",
        "rides = conn.sql(\"SELECT * FROM rides\").df()\n",
        "display(rides)\n",
        "\n",
        "print(\"\\n\\n\\n Pasengers table\")\n",
        "passengers = conn.sql(\"SELECT * FROM rides__passengers\").df()\n",
        "display(passengers)\n",
        "print(\"\\n\\n\\n Stops table\")\n",
        "stops = conn.sql(\"SELECT * FROM rides__stops\").df()\n",
        "display(stops)\n",
        "\n",
        "\n",
        "# to reflect the relationships between parent and child rows, let's join them\n",
        "# of course this will have 4 rows due to the two 1:n joins\n",
        "\n",
        "print(\"\\n\\n\\n joined table\")\n",
        "\n",
        "joined = conn.sql(\"\"\"\n",
        "SELECT *\n",
        "FROM rides as r\n",
        "left join rides__passengers as rp\n",
        "  on r._dlt_id = rp._dlt_parent_id\n",
        "left join rides__stops as rs\n",
        "  on r._dlt_id = rs._dlt_parent_id\n",
        "\"\"\").df()\n",
        "display(joined)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1000
        },
        "id": "Irf4XsfcgqQv",
        "outputId": "4520e8ab-31b4-4d07-a968-49b57dc556ca"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Loaded tables: \n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "┌─────────────────────┐\n",
              "│        name         │\n",
              "│       varchar       │\n",
              "├─────────────────────┤\n",
              "│ _dlt_loads          │\n",
              "│ _dlt_pipeline_state │\n",
              "│ _dlt_version        │\n",
              "│ rides               │\n",
              "│ rides__passengers   │\n",
              "│ rides__stops        │\n",
              "└─────────────────────┘"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\n",
            "\n",
            "\n",
            " Rides table below: Note the times are properly typed\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "                        record_hash vendor_name              time__pickup  \\\n",
              "0  b00361a396177a9cb410ff61f20015ad         VTS 2009-06-14 23:23:00+00:00   \n",
              "\n",
              "              time__dropoff  trip_distance  coordinates__start__lon  \\\n",
              "0 2009-06-14 23:48:00+00:00          17.52               -73.787442   \n",
              "\n",
              "   coordinates__start__lat  coordinates__end__lon  coordinates__end__lat  \\\n",
              "0                40.641525             -73.980072              40.742963   \n",
              "\n",
              "  payment__type  payment__amt  payment__surcharge  payment__tip  \\\n",
              "0        Credit          20.5                   0             9   \n",
              "\n",
              "   payment__tolls payment__status  passenger_count        _dlt_load_id  \\\n",
              "0            4.15          booked                2  1707150169.2096095   \n",
              "\n",
              "          _dlt_id  \n",
              "0  NxIknICvlm3HXg  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-3f5301cc-0223-4ba4-bbc1-135185ac5ff4\" 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>record_hash</th>\n",
              "      <th>vendor_name</th>\n",
              "      <th>time__pickup</th>\n",
              "      <th>time__dropoff</th>\n",
              "      <th>trip_distance</th>\n",
              "      <th>coordinates__start__lon</th>\n",
              "      <th>coordinates__start__lat</th>\n",
              "      <th>coordinates__end__lon</th>\n",
              "      <th>coordinates__end__lat</th>\n",
              "      <th>payment__type</th>\n",
              "      <th>payment__amt</th>\n",
              "      <th>payment__surcharge</th>\n",
              "      <th>payment__tip</th>\n",
              "      <th>payment__tolls</th>\n",
              "      <th>payment__status</th>\n",
              "      <th>passenger_count</th>\n",
              "      <th>_dlt_load_id</th>\n",
              "      <th>_dlt_id</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>b00361a396177a9cb410ff61f20015ad</td>\n",
              "      <td>VTS</td>\n",
              "      <td>2009-06-14 23:23:00+00:00</td>\n",
              "      <td>2009-06-14 23:48:00+00:00</td>\n",
              "      <td>17.52</td>\n",
              "      <td>-73.787442</td>\n",
              "      <td>40.641525</td>\n",
              "      <td>-73.980072</td>\n",
              "      <td>40.742963</td>\n",
              "      <td>Credit</td>\n",
              "      <td>20.5</td>\n",
              "      <td>0</td>\n",
              "      <td>9</td>\n",
              "      <td>4.15</td>\n",
              "      <td>booked</td>\n",
              "      <td>2</td>\n",
              "      <td>1707150169.2096095</td>\n",
              "      <td>NxIknICvlm3HXg</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-3f5301cc-0223-4ba4-bbc1-135185ac5ff4')\"\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-3f5301cc-0223-4ba4-bbc1-135185ac5ff4 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-3f5301cc-0223-4ba4-bbc1-135185ac5ff4');\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",
              "    </div>\n",
              "  </div>\n"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\n",
            "\n",
            "\n",
            " Pasengers table\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "   name  rating    _dlt_root_id  _dlt_parent_id  _dlt_list_idx         _dlt_id\n",
              "0  John     4.9  NxIknICvlm3HXg  NxIknICvlm3HXg              0  A9D8DMPP/ptD2w\n",
              "1  Jack     3.9  NxIknICvlm3HXg  NxIknICvlm3HXg              1  xPwvjKWL3RFfuQ"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-2000eaa8-1c94-4b16-9c69-e71ed25a3230\" 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>name</th>\n",
              "      <th>rating</th>\n",
              "      <th>_dlt_root_id</th>\n",
              "      <th>_dlt_parent_id</th>\n",
              "      <th>_dlt_list_idx</th>\n",
              "      <th>_dlt_id</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>John</td>\n",
              "      <td>4.9</td>\n",
              "      <td>NxIknICvlm3HXg</td>\n",
              "      <td>NxIknICvlm3HXg</td>\n",
              "      <td>0</td>\n",
              "      <td>A9D8DMPP/ptD2w</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Jack</td>\n",
              "      <td>3.9</td>\n",
              "      <td>NxIknICvlm3HXg</td>\n",
              "      <td>NxIknICvlm3HXg</td>\n",
              "      <td>1</td>\n",
              "      <td>xPwvjKWL3RFfuQ</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-2000eaa8-1c94-4b16-9c69-e71ed25a3230')\"\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-2000eaa8-1c94-4b16-9c69-e71ed25a3230 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-2000eaa8-1c94-4b16-9c69-e71ed25a3230');\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-e7c342c9-8b50-41c4-b2e6-860cd4540e5b\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-e7c342c9-8b50-41c4-b2e6-860cd4540e5b')\"\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-e7c342c9-8b50-41c4-b2e6-860cd4540e5b button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "    </div>\n",
              "  </div>\n"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\n",
            "\n",
            "\n",
            " Stops table\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "    lon   lat    _dlt_root_id  _dlt_parent_id  _dlt_list_idx         _dlt_id\n",
              "0 -73.6  40.6  NxIknICvlm3HXg  NxIknICvlm3HXg              0  nMaSxPM7j5ER1Q\n",
              "1 -73.5  40.5  NxIknICvlm3HXg  NxIknICvlm3HXg              1  on55pVMrw4OKVA"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-46e8965e-6f24-4b50-af31-f44083c21f59\" 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>lon</th>\n",
              "      <th>lat</th>\n",
              "      <th>_dlt_root_id</th>\n",
              "      <th>_dlt_parent_id</th>\n",
              "      <th>_dlt_list_idx</th>\n",
              "      <th>_dlt_id</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>-73.6</td>\n",
              "      <td>40.6</td>\n",
              "      <td>NxIknICvlm3HXg</td>\n",
              "      <td>NxIknICvlm3HXg</td>\n",
              "      <td>0</td>\n",
              "      <td>nMaSxPM7j5ER1Q</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>-73.5</td>\n",
              "      <td>40.5</td>\n",
              "      <td>NxIknICvlm3HXg</td>\n",
              "      <td>NxIknICvlm3HXg</td>\n",
              "      <td>1</td>\n",
              "      <td>on55pVMrw4OKVA</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-46e8965e-6f24-4b50-af31-f44083c21f59')\"\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-46e8965e-6f24-4b50-af31-f44083c21f59 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-46e8965e-6f24-4b50-af31-f44083c21f59');\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-d79a19d0-533d-41ef-b213-308a5fec08b1\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-d79a19d0-533d-41ef-b213-308a5fec08b1')\"\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-d79a19d0-533d-41ef-b213-308a5fec08b1 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "    </div>\n",
              "  </div>\n"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\n",
            "\n",
            "\n",
            " joined table\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "                        record_hash vendor_name              time__pickup  \\\n",
              "0  b00361a396177a9cb410ff61f20015ad         VTS 2009-06-14 23:23:00+00:00   \n",
              "1  b00361a396177a9cb410ff61f20015ad         VTS 2009-06-14 23:23:00+00:00   \n",
              "2  b00361a396177a9cb410ff61f20015ad         VTS 2009-06-14 23:23:00+00:00   \n",
              "3  b00361a396177a9cb410ff61f20015ad         VTS 2009-06-14 23:23:00+00:00   \n",
              "\n",
              "              time__dropoff  trip_distance  coordinates__start__lon  \\\n",
              "0 2009-06-14 23:48:00+00:00          17.52               -73.787442   \n",
              "1 2009-06-14 23:48:00+00:00          17.52               -73.787442   \n",
              "2 2009-06-14 23:48:00+00:00          17.52               -73.787442   \n",
              "3 2009-06-14 23:48:00+00:00          17.52               -73.787442   \n",
              "\n",
              "   coordinates__start__lat  coordinates__end__lon  coordinates__end__lat  \\\n",
              "0                40.641525             -73.980072              40.742963   \n",
              "1                40.641525             -73.980072              40.742963   \n",
              "2                40.641525             -73.980072              40.742963   \n",
              "3                40.641525             -73.980072              40.742963   \n",
              "\n",
              "  payment__type  ...    _dlt_root_id  _dlt_parent_id  _dlt_list_idx  \\\n",
              "0        Credit  ...  NxIknICvlm3HXg  NxIknICvlm3HXg              1   \n",
              "1        Credit  ...  NxIknICvlm3HXg  NxIknICvlm3HXg              0   \n",
              "2        Credit  ...  NxIknICvlm3HXg  NxIknICvlm3HXg              1   \n",
              "3        Credit  ...  NxIknICvlm3HXg  NxIknICvlm3HXg              0   \n",
              "\n",
              "        _dlt_id_2   lon   lat  _dlt_root_id_2 _dlt_parent_id_2  \\\n",
              "0  xPwvjKWL3RFfuQ -73.5  40.5  NxIknICvlm3HXg   NxIknICvlm3HXg   \n",
              "1  A9D8DMPP/ptD2w -73.5  40.5  NxIknICvlm3HXg   NxIknICvlm3HXg   \n",
              "2  xPwvjKWL3RFfuQ -73.6  40.6  NxIknICvlm3HXg   NxIknICvlm3HXg   \n",
              "3  A9D8DMPP/ptD2w -73.6  40.6  NxIknICvlm3HXg   NxIknICvlm3HXg   \n",
              "\n",
              "  _dlt_list_idx_2       _dlt_id_3  \n",
              "0               1  on55pVMrw4OKVA  \n",
              "1               1  on55pVMrw4OKVA  \n",
              "2               0  nMaSxPM7j5ER1Q  \n",
              "3               0  nMaSxPM7j5ER1Q  \n",
              "\n",
              "[4 rows x 30 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-5cc53ee3-8493-4285-83e0-5027d3433ddb\" 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>record_hash</th>\n",
              "      <th>vendor_name</th>\n",
              "      <th>time__pickup</th>\n",
              "      <th>time__dropoff</th>\n",
              "      <th>trip_distance</th>\n",
              "      <th>coordinates__start__lon</th>\n",
              "      <th>coordinates__start__lat</th>\n",
              "      <th>coordinates__end__lon</th>\n",
              "      <th>coordinates__end__lat</th>\n",
              "      <th>payment__type</th>\n",
              "      <th>...</th>\n",
              "      <th>_dlt_root_id</th>\n",
              "      <th>_dlt_parent_id</th>\n",
              "      <th>_dlt_list_idx</th>\n",
              "      <th>_dlt_id_2</th>\n",
              "      <th>lon</th>\n",
              "      <th>lat</th>\n",
              "      <th>_dlt_root_id_2</th>\n",
              "      <th>_dlt_parent_id_2</th>\n",
              "      <th>_dlt_list_idx_2</th>\n",
              "      <th>_dlt_id_3</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>b00361a396177a9cb410ff61f20015ad</td>\n",
              "      <td>VTS</td>\n",
              "      <td>2009-06-14 23:23:00+00:00</td>\n",
              "      <td>2009-06-14 23:48:00+00:00</td>\n",
              "      <td>17.52</td>\n",
              "      <td>-73.787442</td>\n",
              "      <td>40.641525</td>\n",
              "      <td>-73.980072</td>\n",
              "      <td>40.742963</td>\n",
              "      <td>Credit</td>\n",
              "      <td>...</td>\n",
              "      <td>NxIknICvlm3HXg</td>\n",
              "      <td>NxIknICvlm3HXg</td>\n",
              "      <td>1</td>\n",
              "      <td>xPwvjKWL3RFfuQ</td>\n",
              "      <td>-73.5</td>\n",
              "      <td>40.5</td>\n",
              "      <td>NxIknICvlm3HXg</td>\n",
              "      <td>NxIknICvlm3HXg</td>\n",
              "      <td>1</td>\n",
              "      <td>on55pVMrw4OKVA</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>b00361a396177a9cb410ff61f20015ad</td>\n",
              "      <td>VTS</td>\n",
              "      <td>2009-06-14 23:23:00+00:00</td>\n",
              "      <td>2009-06-14 23:48:00+00:00</td>\n",
              "      <td>17.52</td>\n",
              "      <td>-73.787442</td>\n",
              "      <td>40.641525</td>\n",
              "      <td>-73.980072</td>\n",
              "      <td>40.742963</td>\n",
              "      <td>Credit</td>\n",
              "      <td>...</td>\n",
              "      <td>NxIknICvlm3HXg</td>\n",
              "      <td>NxIknICvlm3HXg</td>\n",
              "      <td>0</td>\n",
              "      <td>A9D8DMPP/ptD2w</td>\n",
              "      <td>-73.5</td>\n",
              "      <td>40.5</td>\n",
              "      <td>NxIknICvlm3HXg</td>\n",
              "      <td>NxIknICvlm3HXg</td>\n",
              "      <td>1</td>\n",
              "      <td>on55pVMrw4OKVA</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>b00361a396177a9cb410ff61f20015ad</td>\n",
              "      <td>VTS</td>\n",
              "      <td>2009-06-14 23:23:00+00:00</td>\n",
              "      <td>2009-06-14 23:48:00+00:00</td>\n",
              "      <td>17.52</td>\n",
              "      <td>-73.787442</td>\n",
              "      <td>40.641525</td>\n",
              "      <td>-73.980072</td>\n",
              "      <td>40.742963</td>\n",
              "      <td>Credit</td>\n",
              "      <td>...</td>\n",
              "      <td>NxIknICvlm3HXg</td>\n",
              "      <td>NxIknICvlm3HXg</td>\n",
              "      <td>1</td>\n",
              "      <td>xPwvjKWL3RFfuQ</td>\n",
              "      <td>-73.6</td>\n",
              "      <td>40.6</td>\n",
              "      <td>NxIknICvlm3HXg</td>\n",
              "      <td>NxIknICvlm3HXg</td>\n",
              "      <td>0</td>\n",
              "      <td>nMaSxPM7j5ER1Q</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>b00361a396177a9cb410ff61f20015ad</td>\n",
              "      <td>VTS</td>\n",
              "      <td>2009-06-14 23:23:00+00:00</td>\n",
              "      <td>2009-06-14 23:48:00+00:00</td>\n",
              "      <td>17.52</td>\n",
              "      <td>-73.787442</td>\n",
              "      <td>40.641525</td>\n",
              "      <td>-73.980072</td>\n",
              "      <td>40.742963</td>\n",
              "      <td>Credit</td>\n",
              "      <td>...</td>\n",
              "      <td>NxIknICvlm3HXg</td>\n",
              "      <td>NxIknICvlm3HXg</td>\n",
              "      <td>0</td>\n",
              "      <td>A9D8DMPP/ptD2w</td>\n",
              "      <td>-73.6</td>\n",
              "      <td>40.6</td>\n",
              "      <td>NxIknICvlm3HXg</td>\n",
              "      <td>NxIknICvlm3HXg</td>\n",
              "      <td>0</td>\n",
              "      <td>nMaSxPM7j5ER1Q</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>4 rows × 30 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-5cc53ee3-8493-4285-83e0-5027d3433ddb')\"\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-5cc53ee3-8493-4285-83e0-5027d3433ddb 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-5cc53ee3-8493-4285-83e0-5027d3433ddb');\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-32036ba3-b059-4a6c-9522-ebf036e31a42\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-32036ba3-b059-4a6c-9522-ebf036e31a42')\"\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-32036ba3-b059-4a6c-9522-ebf036e31a42 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "    </div>\n",
              "  </div>\n"
            ]
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "What are we looking at?\n",
        "- Nested dicts got flattened into the parent row, the structure `{\"coordinates\":{\"start\": {\"lat\": ...}}}` became\n",
        "`coordinates__start__lat`\n",
        "\n",
        "- Nested lists got broken out into separate tables with generated columns that would allow us to join the data back when needed."
      ],
      "metadata": {
        "id": "duR1wFIfbM07"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Part 3: Incremental loading\n",
        "## Update nested data\n",
        "\n",
        "In this example the scores of the 2 passengers changed. Turns out their payment didn't go through for the ride before and they got a bad rating from the driver, so now we have to update their rating.\n",
        "\n",
        "As you can see after running the code, their ratings are now lowered"
      ],
      "metadata": {
        "id": "iDpgADmSBdr9"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import dlt\n",
        "import duckdb\n",
        "\n",
        "data = [\n",
        "    {\n",
        "        \"vendor_name\": \"VTS\",\n",
        "\t\t\t\t\"record_hash\": \"b00361a396177a9cb410ff61f20015ad\",\n",
        "        \"time\": {\n",
        "            \"pickup\": \"2009-06-14 23:23:00\",\n",
        "            \"dropoff\": \"2009-06-14 23:48:00\"\n",
        "        },\n",
        "        \"Trip_Distance\": 17.52,\n",
        "        \"coordinates\": {\n",
        "            \"start\": {\n",
        "                \"lon\": -73.787442,\n",
        "                \"lat\": 40.641525\n",
        "            },\n",
        "            \"end\": {\n",
        "                \"lon\": -73.980072,\n",
        "                \"lat\": 40.742963\n",
        "            }\n",
        "        },\n",
        "        \"Rate_Code\": None,\n",
        "        \"store_and_forward\": None,\n",
        "        \"Payment\": {\n",
        "            \"type\": \"Credit\",\n",
        "            \"amt\": 20.5,\n",
        "            \"surcharge\": 0,\n",
        "            \"mta_tax\": None,\n",
        "            \"tip\": 9,\n",
        "            \"tolls\": 4.15,\n",
        "\t\t\t\t\t\t\"status\": \"cancelled\"\n",
        "        },\n",
        "        \"Passenger_Count\": 2,\n",
        "        \"passengers\": [\n",
        "            {\"name\": \"John\", \"rating\": 4.4},\n",
        "            {\"name\": \"Jack\", \"rating\": 3.6}\n",
        "        ],\n",
        "        \"Stops\": [\n",
        "            {\"lon\": -73.6, \"lat\": 40.6},\n",
        "            {\"lon\": -73.5, \"lat\": 40.5}\n",
        "        ]\n",
        "    },\n",
        "]\n",
        "\n",
        "# define the connection to load to.\n",
        "# We now use duckdb, but you can switch to Bigquery later\n",
        "pipeline = dlt.pipeline(destination='duckdb', dataset_name='taxi_rides')\n",
        "\n",
        "# run the pipeline with default settings, and capture the outcome\n",
        "info = pipeline.run(data,\n",
        "\t\t\t\t\t\t\t\t\t\ttable_name=\"rides\",\n",
        "\t\t\t\t\t\t\t\t\t\twrite_disposition=\"merge\",\n",
        "                    primary_key='record_hash')\n",
        "\n",
        "# show the outcome\n",
        "\n",
        "conn = duckdb.connect(f\"{pipeline.pipeline_name}.duckdb\")\n",
        "\n",
        "# let's see the tables\n",
        "conn.sql(f\"SET search_path = '{pipeline.dataset_name}'\")\n",
        "print('Loaded tables: ')\n",
        "display(conn.sql(\"show tables\"))\n",
        "\n",
        "\n",
        "print(\"\\n\\n\\n Rides table below: Note the times are properly typed\")\n",
        "rides = conn.sql(\"SELECT * FROM rides\").df()\n",
        "display(rides)\n",
        "\n",
        "print(\"\\n\\n\\n Pasengers table\")\n",
        "passengers = conn.sql(\"SELECT * FROM rides__passengers\").df()\n",
        "display(passengers)\n",
        "print(\"\\n\\n\\n Stops table\")\n",
        "stops = conn.sql(\"SELECT * FROM rides__stops\").df()\n",
        "display(stops)\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 725
        },
        "id": "D3W5iXow8cs8",
        "outputId": "c611f12a-f0bb-459b-f6c1-8c982855e91b"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Loaded tables: \n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "┌─────────────────────┐\n",
              "│        name         │\n",
              "│       varchar       │\n",
              "├─────────────────────┤\n",
              "│ _dlt_loads          │\n",
              "│ _dlt_pipeline_state │\n",
              "│ _dlt_version        │\n",
              "│ rides               │\n",
              "│ rides__passengers   │\n",
              "│ rides__stops        │\n",
              "└─────────────────────┘"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\n",
            "\n",
            "\n",
            " Rides table below: Note the times are properly typed\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "                        record_hash vendor_name              time__pickup  \\\n",
              "0  b00361a396177a9cb410ff61f20015ad         VTS 2009-06-14 23:23:00+00:00   \n",
              "\n",
              "              time__dropoff  trip_distance  coordinates__start__lon  \\\n",
              "0 2009-06-14 23:48:00+00:00          17.52               -73.787442   \n",
              "\n",
              "   coordinates__start__lat  coordinates__end__lon  coordinates__end__lat  \\\n",
              "0                40.641525             -73.980072              40.742963   \n",
              "\n",
              "  payment__type  payment__amt  payment__surcharge  payment__tip  \\\n",
              "0        Credit          20.5                   0             9   \n",
              "\n",
              "   payment__tolls payment__status  passenger_count        _dlt_load_id  \\\n",
              "0            4.15       cancelled                2  1707150937.0610921   \n",
              "\n",
              "          _dlt_id  \n",
              "0  SpBPz6CiHyz+1A  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-b25619bb-f6e9-4f4a-89eb-9fc9b87ee2c1\" 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>record_hash</th>\n",
              "      <th>vendor_name</th>\n",
              "      <th>time__pickup</th>\n",
              "      <th>time__dropoff</th>\n",
              "      <th>trip_distance</th>\n",
              "      <th>coordinates__start__lon</th>\n",
              "      <th>coordinates__start__lat</th>\n",
              "      <th>coordinates__end__lon</th>\n",
              "      <th>coordinates__end__lat</th>\n",
              "      <th>payment__type</th>\n",
              "      <th>payment__amt</th>\n",
              "      <th>payment__surcharge</th>\n",
              "      <th>payment__tip</th>\n",
              "      <th>payment__tolls</th>\n",
              "      <th>payment__status</th>\n",
              "      <th>passenger_count</th>\n",
              "      <th>_dlt_load_id</th>\n",
              "      <th>_dlt_id</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>b00361a396177a9cb410ff61f20015ad</td>\n",
              "      <td>VTS</td>\n",
              "      <td>2009-06-14 23:23:00+00:00</td>\n",
              "      <td>2009-06-14 23:48:00+00:00</td>\n",
              "      <td>17.52</td>\n",
              "      <td>-73.787442</td>\n",
              "      <td>40.641525</td>\n",
              "      <td>-73.980072</td>\n",
              "      <td>40.742963</td>\n",
              "      <td>Credit</td>\n",
              "      <td>20.5</td>\n",
              "      <td>0</td>\n",
              "      <td>9</td>\n",
              "      <td>4.15</td>\n",
              "      <td>cancelled</td>\n",
              "      <td>2</td>\n",
              "      <td>1707150937.0610921</td>\n",
              "      <td>SpBPz6CiHyz+1A</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-b25619bb-f6e9-4f4a-89eb-9fc9b87ee2c1')\"\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-b25619bb-f6e9-4f4a-89eb-9fc9b87ee2c1 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-b25619bb-f6e9-4f4a-89eb-9fc9b87ee2c1');\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",
              "    </div>\n",
              "  </div>\n"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\n",
            "\n",
            "\n",
            " Pasengers table\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "   name  rating    _dlt_root_id  _dlt_parent_id  _dlt_list_idx         _dlt_id\n",
              "0  John     4.4  SpBPz6CiHyz+1A  SpBPz6CiHyz+1A              0  j06fU+ylaR47nQ\n",
              "1  Jack     3.6  SpBPz6CiHyz+1A  SpBPz6CiHyz+1A              1  7HZIZPxaw5t4rA"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-44d2da42-e2a9-438a-abed-00ce9bc54b9c\" 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>name</th>\n",
              "      <th>rating</th>\n",
              "      <th>_dlt_root_id</th>\n",
              "      <th>_dlt_parent_id</th>\n",
              "      <th>_dlt_list_idx</th>\n",
              "      <th>_dlt_id</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>John</td>\n",
              "      <td>4.4</td>\n",
              "      <td>SpBPz6CiHyz+1A</td>\n",
              "      <td>SpBPz6CiHyz+1A</td>\n",
              "      <td>0</td>\n",
              "      <td>j06fU+ylaR47nQ</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Jack</td>\n",
              "      <td>3.6</td>\n",
              "      <td>SpBPz6CiHyz+1A</td>\n",
              "      <td>SpBPz6CiHyz+1A</td>\n",
              "      <td>1</td>\n",
              "      <td>7HZIZPxaw5t4rA</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-44d2da42-e2a9-438a-abed-00ce9bc54b9c')\"\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-44d2da42-e2a9-438a-abed-00ce9bc54b9c 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-44d2da42-e2a9-438a-abed-00ce9bc54b9c');\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-77154ef0-ea89-498e-bbd9-77e4af2cdcf2\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-77154ef0-ea89-498e-bbd9-77e4af2cdcf2')\"\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-77154ef0-ea89-498e-bbd9-77e4af2cdcf2 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "    </div>\n",
              "  </div>\n"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\n",
            "\n",
            "\n",
            " Stops table\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "    lon   lat    _dlt_root_id  _dlt_parent_id  _dlt_list_idx         _dlt_id\n",
              "0 -73.6  40.6  SpBPz6CiHyz+1A  SpBPz6CiHyz+1A              0  Dx5IBIyHYS6jLQ\n",
              "1 -73.5  40.5  SpBPz6CiHyz+1A  SpBPz6CiHyz+1A              1  L0pm1MZHY/lJLQ"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-0e06692f-4c1a-4f51-9bff-dd70b4378e86\" 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>lon</th>\n",
              "      <th>lat</th>\n",
              "      <th>_dlt_root_id</th>\n",
              "      <th>_dlt_parent_id</th>\n",
              "      <th>_dlt_list_idx</th>\n",
              "      <th>_dlt_id</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>-73.6</td>\n",
              "      <td>40.6</td>\n",
              "      <td>SpBPz6CiHyz+1A</td>\n",
              "      <td>SpBPz6CiHyz+1A</td>\n",
              "      <td>0</td>\n",
              "      <td>Dx5IBIyHYS6jLQ</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>-73.5</td>\n",
              "      <td>40.5</td>\n",
              "      <td>SpBPz6CiHyz+1A</td>\n",
              "      <td>SpBPz6CiHyz+1A</td>\n",
              "      <td>1</td>\n",
              "      <td>L0pm1MZHY/lJLQ</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-0e06692f-4c1a-4f51-9bff-dd70b4378e86')\"\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-0e06692f-4c1a-4f51-9bff-dd70b4378e86 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-0e06692f-4c1a-4f51-9bff-dd70b4378e86');\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-9ce01b2b-db8f-4c29-b25f-00f4601ced0e\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-9ce01b2b-db8f-4c29-b25f-00f4601ced0e')\"\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-9ce01b2b-db8f-4c29-b25f-00f4601ced0e button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "    </div>\n",
              "  </div>\n"
            ]
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Bonus snippets\n",
        "\n"
      ],
      "metadata": {
        "id": "-XXw1A9QUDUJ"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Load to parquet file"
      ],
      "metadata": {
        "id": "dp1tfRt1S4Gz"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%capture\n",
        "!pip install dlt[parquet] # Install dlt with all the necessary DuckDB dependencies\n",
        "!pip install parquet\n",
        "!mkdir .dlt"
      ],
      "metadata": {
        "id": "Sj2aTU-GUe2J"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "import os\n",
        "import dlt\n",
        "import parquet\n",
        "import json\n",
        "import glob\n",
        "\n",
        "# Set the bucket_url. We can also use a local folder\n",
        "os.environ['DESTINATION__FILESYSTEM__BUCKET_URL'] = 'file:///content/.dlt/my_folder'\n",
        "\n",
        "url = \"https://storage.googleapis.com/dtc_zoomcamp_api/yellow_tripdata_2009-06.jsonl\"\n",
        "# Define your pipeline\n",
        "pipeline = dlt.pipeline(\n",
        "    pipeline_name='my_pipeline',\n",
        "    destination='filesystem',\n",
        "    dataset_name='mydata'\n",
        ")\n",
        "\n",
        "\n",
        "\n",
        "# Run the pipeline with the generator we created earlier.\n",
        "load_info = pipeline.run(stream_download_jsonl(url), table_name=\"users\", loader_file_format=\"parquet\")\n",
        "\n",
        "print(load_info)\n",
        "\n",
        "# Get a list of all Parquet files in the specified folder\n",
        "parquet_files = glob.glob('/content/.dlt/my_folder/mydata/users/*.parquet')\n",
        "\n",
        "# show parquet files\n",
        "print(\"Loaded files: \")\n",
        "for file in parquet_files:\n",
        "  print(file)\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "YZ74NAUoV06Y",
        "outputId": "3ebcf428-fc2b-4255-d846-cd9560d577cf"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Pipeline my_pipeline load step completed in 0.13 seconds\n",
            "1 load package(s) were loaded to destination filesystem and into dataset mydata\n",
            "The filesystem destination used file:///content/.dlt/my_folder location to store data\n",
            "Load package 1707141850.7700968 is LOADED and contains no failed jobs\n",
            "Loaded files: \n",
            "/content/.dlt/my_folder/mydata/users/1707141814.0602813.501ac162ec.parquet\n",
            "/content/.dlt/my_folder/mydata/users/1707141850.7700968.52f0d4d34c.parquet\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Load to bigquery\n",
        "To load to bigquery, we need credentials to bigquery.\n",
        "- dlt looks for credentials in several places as described in the [credential docs.](https://dlthub.com/docs/general-usage/credentials/configuration)\n",
        "- In the case of Bigquery you can read the docs [here](https://dlthub.com/docs/dlt-ecosystem/destinations/bigquery) for how to do it.\n",
        "- If you are running from Colab or a GCP machine, or you are authenticated with the gcp CLI, you can use these already-available local credentials. We will use the Colab Oauth here."
      ],
      "metadata": {
        "id": "CoEk7pYXTBJe"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%capture\n",
        "!pip install dlt[bigquery]"
      ],
      "metadata": {
        "id": "gIKdZERfxOYA"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Authenticate to Google BigQuery\n",
        "from google.colab import auth\n",
        "auth.authenticate_user()"
      ],
      "metadata": {
        "id": "dQ3uWgqZxKS7"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "import os\n",
        "import dlt\n",
        "\n",
        "os.environ['GOOGLE_CLOUD_PROJECT'] = 'dlt-dev-external'\n",
        "\n",
        "\n",
        "# Define your pipeline\n",
        "pipeline = dlt.pipeline(\n",
        "    pipeline_name='my_pipeline',\n",
        "    destination='bigquery',\n",
        "    dataset_name='dtc'\n",
        ")\n",
        "\n",
        "# Run the pipeline\n",
        "load_info = pipeline.run(stream_download_jsonl(url), table_name=\"users\")\n",
        "\n",
        "print(load_info)\n",
        "\n",
        "from google.cloud import bigquery\n",
        "\n",
        "# Construct a BigQuery client object.\n",
        "client = bigquery.Client()\n",
        "\n",
        "query = \"\"\"\n",
        "    SELECT *\n",
        "    FROM `dtc.users`\n",
        "\"\"\"\n",
        "\n",
        "query_job = client.query(query)  # Make an API request.\n",
        "\n",
        "print(\"The query data:\")\n",
        "for row in query_job:\n",
        "    # Row values can be accessed by field name or index.\n",
        "    print(row)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "6bjVf9QXTA8R",
        "outputId": "f3640371-adc9-4832-854a-8b906c399b59"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "\u001b[1;30;43mThis cell output is too large and can only be displayed while logged in.\u001b[0m\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Other demos\n",
        "Find more demos in this repo, or look on our blog for multiple community demos\n",
        "* https://github.com/dlt-hub/dlt_demos\n",
        "* https://dlthub.com/docs/blog"
      ],
      "metadata": {
        "id": "bftPmKJYS_7J"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Docs Links\n",
        "\n",
        "This course was tailored to enable all the cohort to complete it succesfully - so more complex things were left out. We strongly encourage you to keep learning on your own.\n",
        "\n",
        "\n",
        "You will find more info about advanced capabilities of dlt here: https://dlthub.com/docs/build-a-pipeline-tutorial\n",
        "\n",
        "\n",
        "\n",
        "Don't miss the GPT-4 docs helper button - it will help with simple questions.\n",
        "\n",
        "If you get stuck, consider joining our community for help https://dlthub.com/community"
      ],
      "metadata": {
        "id": "5aPjk0O3S_Ag"
      }
    }
  ]
}