ELT_delta_to_duckdb_file.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "301a703f-f5e9-483a-a8b3-d5b4433f290c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: duckdb in ./jupyterlab-venv/lib/python3.10/site-packages (1.1.2)\n"
     ]
    }
   ],
   "source": [
    "#!pip install duckdb\n",
    "#!pip install 'polars[pyarrow]'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9bc4c7e7-5607-4b4e-b236-b4cd07123174",
   "metadata": {},
   "source": [
    "## Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "8df2f00e-1aa0-4d5e-bbe1-7406beb7e645",
   "metadata": {},
   "outputs": [],
   "source": [
    "import duckdb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "5f702c4a-6d7e-4fbf-b05c-81d841cf8df8",
   "metadata": {},
   "outputs": [],
   "source": [
    "duckLocalFileCon = duckdb.connect(\"/your/file/path/nyc.db\")\n",
    "\n",
    "aws_access_key_id=\"\"\n",
    "aws_secret_access_key=\"\"\n",
    "aws_session_token=\"\"\n",
    "\n",
    "aws_region=\"\"\n",
    "src_s3_uri=\"s3://\"\n",
    "target_s3_uri=\"s3://\"\n",
    "table_name = \"tbl_nyc_taxi_trips\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "787e6514-cc4e-4aa0-a264-a6dd952a1b5b",
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_aws_sso_secrets(duckdb_obj, key_id, secret, token, region):\n",
    "    create_secret_ddl = f\"\"\"\n",
    "    CREATE OR REPLACE SECRET my_ephem_sts_token \n",
    "        ( \n",
    "            TYPE S3,\n",
    "            KEY_ID '{key_id}',\n",
    "            SECRET '{secret}',\n",
    "            SESSION_TOKEN '{token}',\n",
    "            REGION '{region}'\n",
    "        );\"\"\"\n",
    "\n",
    "    return duckdb_obj.sql(create_secret_ddl).fetchall()\n",
    "\n",
    "def init_reader(duckdb_obj, db_url, table_name):\n",
    "    \n",
    "    def read_nyc_table(cab_type, trip_month, trip_year):\n",
    "        query = f\"\"\"\n",
    "            SELECT * \n",
    "            FROM delta_scan('{db_url}/{table_name}') nyc\n",
    "            WHERE nyc.cab_type=? and nyc.trip_month=? and nyc.trip_year=?\n",
    "        \"\"\"\n",
    "        return duckdb_obj.execute(query, [cab_type, trip_month, trip_year]).pl()\n",
    "    return read_nyc_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "3b2b21e3-1c64-4ced-a716-b83ce8ebcb89",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(True,)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "create_aws_sso_secrets(duckLocalFileCon, aws_access_key_id, aws_secret_access_key, aws_session_token, aws_region)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "440e3ddb-46a0-44ce-91a4-d6485e188b81",
   "metadata": {},
   "outputs": [],
   "source": [
    "nycCabTripsReader = init_reader(duckLocalFileCon, target_s3_uri, table_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "85ee3ca8-847d-4d4c-8d87-51b7b9131a9b",
   "metadata": {},
   "source": [
    "We query the delta table now, for green taxi trips in January 2024\n",
    "\n",
    "**expected result**:\n",
    "```\n",
    "shape: (56_551, 23)\n",
    "┌──────────┬────────────┬───────────┬───────────┬───┬───────────┬──────────┬───────────┬───────────┐\n",
    "│ VendorID ┆ pickup_dat ┆ dropoff_d ┆ RatecodeI ┆ … ┆ airport_f ┆ cab_type ┆ trip_year ┆ trip_mont │\n",
    "│ ---      ┆ etime      ┆ atetime   ┆ D         ┆   ┆ ee        ┆ ---      ┆ ---       ┆ h         │\n",
    "│ i32      ┆ ---        ┆ ---       ┆ ---       ┆   ┆ ---       ┆ str      ┆ str       ┆ ---       │\n",
    "│          ┆ datetime[μ ┆ datetime[ ┆ f64       ┆   ┆ f64       ┆          ┆           ┆ str       │\n",
    "│          ┆ s]         ┆ μs]       ┆           ┆   ┆           ┆          ┆           ┆           │\n",
    "╞══════════╪════════════╪═══════════╪═══════════╪═══╪═══════════╪══════════╪═══════════╪═══════════╡\n",
    "│ 2        ┆ 2024-01-01 ┆ 2024-01-0 ┆ 1.0       ┆ … ┆ 0.0       ┆ green    ┆ 2024      ┆ January   │\n",
    "│          ┆ 00:46:55   ┆ 1         ┆           ┆   ┆           ┆          ┆           ┆           │\n",
    "│          ┆            ┆ 00:58:25  ┆           ┆   ┆           ┆          ┆           ┆           │\n",
    "│ 2        ┆ 2024-01-01 ┆ 2024-01-0 ┆ 1.0       ┆ … ┆ 0.0       ┆ green    ┆ 2024      ┆ January   │\n",
    "│          ┆ 00:31:42   ┆ 1         ┆           ┆   ┆           ┆          ┆           ┆           │\n",
    "│          ┆            ┆ 00:52:34  ┆           ┆   ┆           ┆          ┆           ┆           │\n",
    "│ 2        ┆ 2024-01-01 ┆ 2024-01-0 ┆ 1.0       ┆ … ┆ 0.0       ┆ green    ┆ 2024      ┆ January   │\n",
    "│          ┆ 00:30:21   ┆ 1         ┆           ┆   ┆           ┆          ┆           ┆           │\n",
    "│          ┆            ┆ 00:49:23  ┆           ┆   ┆           ┆          ┆           ┆           │\n",
    "│ 1        ┆ 2024-01-01 ┆ 2024-01-0 ┆ 1.0       ┆ … ┆ 0.0       ┆ green    ┆ 2024      ┆ January   │\n",
    "│          ┆ 00:30:20   ┆ 1         ┆           ┆   ┆           ┆          ┆           ┆           │\n",
    "│          ┆            ┆ 00:42:12  ┆           ┆   ┆           ┆          ┆           ┆           │\n",
    "│ 2        ┆ 2024-01-01 ┆ 2024-01-0 ┆ 1.0       ┆ … ┆ 0.0       ┆ green    ┆ 2024      ┆ January   │\n",
    "│          ┆ 00:32:38   ┆ 1         ┆           ┆   ┆           ┆          ┆           ┆           │\n",
    "│          ┆            ┆ 00:43:37  ┆           ┆   ┆           ┆          ┆           ┆           │\n",
    "│ …        ┆ …          ┆ …         ┆ …         ┆ … ┆ …         ┆ …        ┆ …         ┆ …         │\n",
    "│ 2        ┆ 2024-01-31 ┆ 2024-01-3 ┆ null      ┆ … ┆ 0.0       ┆ green    ┆ 2024      ┆ January   │\n",
    "│          ┆ 20:46:00   ┆ 1         ┆           ┆   ┆           ┆          ┆           ┆           │\n",
    "│          ┆            ┆ 20:55:00  ┆           ┆   ┆           ┆          ┆           ┆           │\n",
    "│ 2        ┆ 2024-01-31 ┆ 2024-01-3 ┆ null      ┆ … ┆ 0.0       ┆ green    ┆ 2024      ┆ January   │\n",
    "│          ┆ 21:06:00   ┆ 1         ┆           ┆   ┆           ┆          ┆           ┆           │\n",
    "│          ┆            ┆ 21:11:00  ┆           ┆   ┆           ┆          ┆           ┆           │\n",
    "│ 2        ┆ 2024-01-31 ┆ 2024-01-3 ┆ null      ┆ … ┆ 0.0       ┆ green    ┆ 2024      ┆ January   │\n",
    "│          ┆ 21:36:00   ┆ 1         ┆           ┆   ┆           ┆          ┆           ┆           │\n",
    "│          ┆            ┆ 21:40:00  ┆           ┆   ┆           ┆          ┆           ┆           │\n",
    "│ 2        ┆ 2024-01-31 ┆ 2024-01-3 ┆ null      ┆ … ┆ 0.0       ┆ green    ┆ 2024      ┆ January   │\n",
    "│          ┆ 22:45:00   ┆ 1         ┆           ┆   ┆           ┆          ┆           ┆           │\n",
    "│          ┆            ┆ 22:51:00  ┆           ┆   ┆           ┆          ┆           ┆           │\n",
    "│ 2        ┆ 2024-01-31 ┆ 2024-01-3 ┆ null      ┆ … ┆ 0.0       ┆ green    ┆ 2024      ┆ January   │\n",
    "│          ┆ 22:28:00   ┆ 1         ┆           ┆   ┆           ┆          ┆           ┆           │\n",
    "│          ┆            ┆ 22:59:00  ┆           ┆   ┆           ┆          ┆           ┆           │\n",
    "└──────────┴────────────┴───────────┴───────────┴───┴───────────┴──────────┴───────────┴───────────┘\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "ef0e8ea6-84d5-4ac3-b9f5-d0ab2366f24b",
   "metadata": {},
   "outputs": [],
   "source": [
    "greenTripsJan2024 = nycCabTripsReader('green', 'January', '2024')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "020265d6-dffb-4d4c-a295-a51d31b8e030",
   "metadata": {},
   "source": [
    "`greenTripsJan2024` is a [polars](https://pola.rs/) dataframe\n",
    "\n",
    "At this point you may execute a sql query on the `greenTripsJan2024` variable to check its data, like so:\n",
    "\n",
    "```python\n",
    "duckLocalFileCon.sql(\"\"\"\n",
    "    SELECT *\n",
    "    FROM greenTripsJan2024\n",
    "\"\"\")\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4946b6e1-f802-4341-a52e-ee8a9109f974",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true
   },
   "source": [
    "## Loading data into DuckDB"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1a5c50e7-a809-4cf4-9e3d-88cac84471b2",
   "metadata": {},
   "source": [
    "### Table creation & Initial data load\n",
    "\n",
    "\n",
    "We are now ready to create our duckdb table `nyc_taxi_trips` with this initial data set - trip data from Jan 2024, for green cabs \n",
    "\n",
    "> ***convinience query*** for dropping the existing table (***for demo purposes***)\n",
    ">\n",
    "> ```python\n",
    "duckLocalFileCon.execute(\"\"\"\n",
    "    DROP TABLE NYC_TAXI_TRIPS\n",
    "\"\"\").fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "7bfb0f93-bed9-41d8-846c-a8b303c758d0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(56551,)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "duckLocalFileCon.execute(\"\"\"\n",
    "    CREATE OR REPLACE TABLE NYC_TAXI_TRIPS AS \n",
    "        SELECT *\n",
    "        FROM greenTripsJan2024\n",
    "\"\"\").fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4ae8f3ac-26a1-4f18-835f-03a2d7389117",
   "metadata": {},
   "source": [
    "`CHECKPOINT` will save the in-memory WAL to the duckdb file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "8fe21dca-8a45-4806-a691-8e398aa382ed",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "duckLocalFileCon.execute(\"CHECKPOINT nyc\").fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "65d6eba1-ae63-479c-b0db-f2658384de65",
   "metadata": {},
   "source": [
    "#### Checks\n",
    "\n",
    "Check the number of rows loaded into the table with\n",
    "\n",
    "```sql\n",
    "    SELECT count(*) as num_of_trips\n",
    "    FROM NYC_TAXI_TRIPS\n",
    "```\n",
    "\n",
    "**expected result**\n",
    "\n",
    "```\n",
    "┌──────────────┐\n",
    "│ num_of_trips │\n",
    "│    int64     │\n",
    "├──────────────┤\n",
    "│        56551 │\n",
    "└──────────────┘\n",
    "```\n",
    "\n",
    "Also check the columns in the table with:\n",
    "\n",
    "```sql\n",
    "DESCRIBE TABLE NYC_TAXI_TRIPS\n",
    "\n",
    "```\n",
    "\n",
    "**expected output**\n",
    "\n",
    "```\n",
    "┌───────────────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐\n",
    "│      column_name      │ column_type │  null   │   key   │ default │  extra  │\n",
    "│        varchar        │   varchar   │ varchar │ varchar │ varchar │ varchar │\n",
    "├───────────────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤\n",
    "│ VendorID              │ INTEGER     │ YES     │ NULL    │ NULL    │ NULL    │\n",
    "│ pickup_datetime       │ TIMESTAMP   │ YES     │ NULL    │ NULL    │ NULL    │\n",
    "│ dropoff_datetime      │ TIMESTAMP   │ YES     │ NULL    │ NULL    │ NULL    │\n",
    "│ RatecodeID            │ DOUBLE      │ YES     │ NULL    │ NULL    │ NULL    │\n",
    "│ pickup_location_id    │ INTEGER     │ YES     │ NULL    │ NULL    │ NULL    │\n",
    "│ drop_off_location_id  │ INTEGER     │ YES     │ NULL    │ NULL    │ NULL    │\n",
    "│ passenger_count       │ DOUBLE      │ YES     │ NULL    │ NULL    │ NULL    │\n",
    "│ trip_distance         │ DOUBLE      │ YES     │ NULL    │ NULL    │ NULL    │\n",
    "│ fare_amount           │ DOUBLE      │ YES     │ NULL    │ NULL    │ NULL    │\n",
    "│ extra                 │ DOUBLE      │ YES     │ NULL    │ NULL    │ NULL    │\n",
    "│ mta_tax               │ DOUBLE      │ YES     │ NULL    │ NULL    │ NULL    │\n",
    "│ tip_amount            │ DOUBLE      │ YES     │ NULL    │ NULL    │ NULL    │\n",
    "│ tolls_amount          │ DOUBLE      │ YES     │ NULL    │ NULL    │ NULL    │\n",
    "│ ehail_fee             │ DOUBLE      │ YES     │ NULL    │ NULL    │ NULL    │\n",
    "│ improvement_surcharge │ DOUBLE      │ YES     │ NULL    │ NULL    │ NULL    │\n",
    "│ total_amount          │ DOUBLE      │ YES     │ NULL    │ NULL    │ NULL    │\n",
    "│ payment_type          │ DOUBLE      │ YES     │ NULL    │ NULL    │ NULL    │\n",
    "│ trip_type             │ DOUBLE      │ YES     │ NULL    │ NULL    │ NULL    │\n",
    "│ congestion_surcharge  │ DOUBLE      │ YES     │ NULL    │ NULL    │ NULL    │\n",
    "│ airport_fee           │ DOUBLE      │ YES     │ NULL    │ NULL    │ NULL    │\n",
    "│ cab_type              │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │\n",
    "│ trip_year             │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │\n",
    "│ trip_month            │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │\n",
    "├───────────────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┤\n",
    "│ 23 rows                                                           6 columns │\n",
    "└─────────────────────────────────────────────────────────────────────────────┘\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5921579f-55ca-4cc1-a1c2-12c24a74b3e2",
   "metadata": {},
   "source": [
    "### Incremental Data Load"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "82f8f760-f79e-4100-a025-7d1ba9518930",
   "metadata": {},
   "source": [
    "#### Load 1\n",
    "\n",
    "Let us now load yellow cab data for jan & feb into the same duck table"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "50846c0c-9059-4242-a54c-69f209a3f198",
   "metadata": {},
   "source": [
    "##### Fetch data from delta table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "74edd763-55a5-40a0-9d3c-3621994ba72f",
   "metadata": {},
   "outputs": [],
   "source": [
    "yellowTripsJan2024 = nycCabTripsReader('yellow', 'January', '2024')\n",
    "yellowTripsFeb2024 = nycCabTripsReader('yellow', 'February', '2024')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "0fbf0337-082d-4877-b4ea-43698368697c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(2964624,)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "duckLocalFileCon.execute(\"\"\"\n",
    "    INSERT INTO NYC_TAXI_TRIPS\n",
    "        SELECT *\n",
    "        FROM yellowTripsJan2024\n",
    "\"\"\").fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "e75566d8-21b3-45ea-98bb-8800208f32c9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(3007525,)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "duckLocalFileCon.execute(\"\"\"\n",
    "    INSERT INTO NYC_TAXI_TRIPS\n",
    "        SELECT *\n",
    "        FROM yellowTripsFeb2024\n",
    "\"\"\").fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "f4a86f77-a5f4-458c-9c87-fef8fe1879bc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "duckLocalFileCon.execute(\"CHECKPOINT nyc\").fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f403e96e-3815-47cb-ab7b-57fb2fd2b51f",
   "metadata": {},
   "source": [
    "##### Checks\n",
    "\n",
    "Check the number of rows loaded into the table at this point,\n",
    "\n",
    "```sql\n",
    "    SELECT count(*) as num_of_trips\n",
    "    FROM NYC_TAXI_TRIPS\n",
    "```\n",
    "\n",
    "**expected result**\n",
    "\n",
    "> Initial, green for jan 2024 (56,551) + delta yellow jan 2024 (2,964,624) + delta yellow feb 2024 (3,007,525) = 6,028,700\n",
    "\n",
    "```\n",
    "┌──────────────┐\n",
    "│ num_of_trips │\n",
    "│    int64     │\n",
    "├──────────────┤\n",
    "│      6028700 │\n",
    "└──────────────┘\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "19ed5b80-d1d0-4059-a875-bf1cc40deb85",
   "metadata": {},
   "source": [
    "#### Load 2\n",
    "\n",
    "Let us now load green cab data for feb into the same duck table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "010b3a88-2c6c-4221-816d-507b3992a035",
   "metadata": {},
   "outputs": [],
   "source": [
    "greenTripsFeb2024 = nycCabTripsReader('green', 'February', '2024')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "1cb6a39e-1fa6-459a-8c02-586675625993",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(53577,)]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "duckLocalFileCon.execute(\"\"\"\n",
    "    INSERT INTO NYC_TAXI_TRIPS\n",
    "        SELECT *\n",
    "        FROM greenTripsFeb2024\n",
    "\"\"\").fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "e520a322-f53d-4e57-8627-758cd7fc3c43",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "duckLocalFileCon.execute(\"CHECKPOINT nyc\").fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bec4d0e1-5053-4275-a8a6-63fe2956bb44",
   "metadata": {},
   "source": [
    "The `nyc.db` duckdb file would be `352.01 mb` in size at this point  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1a2ca39c-c87a-4622-bd9c-157c013d3a25",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}