duckdb/tutorial/04-writing-data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "959f612e-92e3-4bde-8f48-22ca66604625",
   "metadata": {},
   "source": [
    "# Tutorial 4: Saving Your Results"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ded602f3-a3db-47b3-a1c8-62fad126c494",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\"> <b>Before we get started: </b> \n",
    "    <ul style=\"list-style-type: none;margin: 0;padding: 0;\">\n",
    "        <li>✍️ To run this notebook, you need to have Ponder installed and set up on your machine. If you have not done so already, please refer to our <a href=\"https://docs.ponder.io/getting_started/quickstart.html\">Quickstart guide</a> to get started.</li> \n",
    "        <li>📁 This tutorial makes use of the <code>ponder.db</code> database that we created in <a href=\"https://github.com/ponder-org/ponder-notebooks/blob/main/duckdb/tutorial/01-getting-started.ipynb\">Tutorial #1</a>. You can also download the file <a href=\"https://github.com/ponder-org/ponder-datasets/raw/main/ponder.db\">here</a>.</li> \n",
    "        <li>📖 Otherwise, if you're just interested in browsing through the tutorial, keep reading below!</li>\n",
    "    </ul>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "643150ee-2715-4508-8b77-18801ca4c1c9",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import ponder; ponder.init()\n",
    "import modin.pandas as pd\n",
    "import duckdb\n",
    "duckdb_con = duckdb.connect(\"../ponder.db\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "267738c2-7b12-4e77-8097-929e77e9dea5",
   "metadata": {},
   "source": [
    "Let's say that you used Ponder to run some analysis and you want to store the results back to the database. In this tutorial, we will show how you can use the ``to_sql`` command to write your dataframe to table in your database. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "f62f06a0-7270-4ab7-a5a8-339bb313537f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_sql(\"PONDER_CITIBIKE\",con=duckdb_con)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "55bffaf2-1840-48c9-9261-27b8a8e30ec6",
   "metadata": {},
   "source": [
    "After connecting to our `PONDER_CITIBIKE` table, we see that there are a lot of records with missing values. So we drop these rows to clean up our dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "758b69fb-e1d8-415d-a28f-0348c8470fba",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cleaned = df.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "2b34d144-91a8-4c2e-ab73-f16a94320fb5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>tripduration</th>\n",
       "      <th>starttime</th>\n",
       "      <th>stoptime</th>\n",
       "      <th>start_station_id</th>\n",
       "      <th>start_station_name</th>\n",
       "      <th>start_station_latitude</th>\n",
       "      <th>start_station_longitude</th>\n",
       "      <th>end_station_id</th>\n",
       "      <th>end_station_name</th>\n",
       "      <th>end_station_latitude</th>\n",
       "      <th>end_station_longitude</th>\n",
       "      <th>bikeid</th>\n",
       "      <th>usertype</th>\n",
       "      <th>birth_year</th>\n",
       "      <th>gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>730</td>\n",
       "      <td>2/9/15 8:37</td>\n",
       "      <td>2/9/15 8:49</td>\n",
       "      <td>72</td>\n",
       "      <td>W 52 St &amp; 11 Ave</td>\n",
       "      <td>40.767272</td>\n",
       "      <td>-73.993929</td>\n",
       "      <td>520</td>\n",
       "      <td>W 52 St &amp; 5 Ave</td>\n",
       "      <td>40.759923</td>\n",
       "      <td>-73.976485</td>\n",
       "      <td>18809</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1975.0</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>704</td>\n",
       "      <td>11/20/13 20:21</td>\n",
       "      <td>11/20/13 20:33</td>\n",
       "      <td>72</td>\n",
       "      <td>W 52 St &amp; 11 Ave</td>\n",
       "      <td>40.767272</td>\n",
       "      <td>-73.993929</td>\n",
       "      <td>470</td>\n",
       "      <td>W 20 St &amp; 8 Ave</td>\n",
       "      <td>40.743453</td>\n",
       "      <td>-74.000040</td>\n",
       "      <td>20515</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1981.0</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>425</td>\n",
       "      <td>1/6/16 17:01</td>\n",
       "      <td>1/6/16 17:08</td>\n",
       "      <td>72</td>\n",
       "      <td>W 52 St &amp; 11 Ave</td>\n",
       "      <td>40.767272</td>\n",
       "      <td>-73.993929</td>\n",
       "      <td>469</td>\n",
       "      <td>Broadway &amp; W 53 St</td>\n",
       "      <td>40.763441</td>\n",
       "      <td>-73.982681</td>\n",
       "      <td>17116</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1947.0</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>373</td>\n",
       "      <td>11/9/15 12:50</td>\n",
       "      <td>11/9/15 12:56</td>\n",
       "      <td>72</td>\n",
       "      <td>W 52 St &amp; 11 Ave</td>\n",
       "      <td>40.767272</td>\n",
       "      <td>-73.993929</td>\n",
       "      <td>469</td>\n",
       "      <td>Broadway &amp; W 53 St</td>\n",
       "      <td>40.763441</td>\n",
       "      <td>-73.982681</td>\n",
       "      <td>20892</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1947.0</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1149</td>\n",
       "      <td>8/3/13 17:14</td>\n",
       "      <td>8/3/13 17:33</td>\n",
       "      <td>72</td>\n",
       "      <td>W 52 St &amp; 11 Ave</td>\n",
       "      <td>40.767272</td>\n",
       "      <td>-73.993929</td>\n",
       "      <td>325</td>\n",
       "      <td>E 19 St &amp; 3 Ave</td>\n",
       "      <td>40.736245</td>\n",
       "      <td>-73.984738</td>\n",
       "      <td>17711</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1981.0</td>\n",
       "      <td>female</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10884</th>\n",
       "      <td>172</td>\n",
       "      <td>1/2/16 21:29</td>\n",
       "      <td>1/2/16 21:32</td>\n",
       "      <td>116</td>\n",
       "      <td>W 17 St &amp; 8 Ave</td>\n",
       "      <td>40.741776</td>\n",
       "      <td>-74.001497</td>\n",
       "      <td>212</td>\n",
       "      <td>W 16 St &amp; The High Line</td>\n",
       "      <td>40.743349</td>\n",
       "      <td>-74.006818</td>\n",
       "      <td>20945</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1961.0</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10885</th>\n",
       "      <td>692</td>\n",
       "      <td>7/20/16 9:29</td>\n",
       "      <td>7/20/16 9:40</td>\n",
       "      <td>116</td>\n",
       "      <td>W 17 St &amp; 8 Ave</td>\n",
       "      <td>40.741776</td>\n",
       "      <td>-74.001497</td>\n",
       "      <td>2021</td>\n",
       "      <td>W 45 St &amp; 8 Ave</td>\n",
       "      <td>40.759291</td>\n",
       "      <td>-73.988597</td>\n",
       "      <td>19345</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1983.0</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10886</th>\n",
       "      <td>432</td>\n",
       "      <td>8/26/15 10:14</td>\n",
       "      <td>8/26/15 10:21</td>\n",
       "      <td>116</td>\n",
       "      <td>W 17 St &amp; 8 Ave</td>\n",
       "      <td>40.741776</td>\n",
       "      <td>-74.001497</td>\n",
       "      <td>509</td>\n",
       "      <td>9 Ave &amp; W 22 St</td>\n",
       "      <td>40.745497</td>\n",
       "      <td>-74.001971</td>\n",
       "      <td>23512</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1962.0</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10887</th>\n",
       "      <td>827</td>\n",
       "      <td>9/2/14 21:38</td>\n",
       "      <td>9/2/14 21:52</td>\n",
       "      <td>116</td>\n",
       "      <td>W 17 St &amp; 8 Ave</td>\n",
       "      <td>40.741776</td>\n",
       "      <td>-74.001497</td>\n",
       "      <td>439</td>\n",
       "      <td>E 4 St &amp; 2 Ave</td>\n",
       "      <td>40.726281</td>\n",
       "      <td>-73.989780</td>\n",
       "      <td>21211</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1985.0</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10888</th>\n",
       "      <td>279</td>\n",
       "      <td>5/6/16 18:38</td>\n",
       "      <td>5/6/16 18:43</td>\n",
       "      <td>116</td>\n",
       "      <td>W 17 St &amp; 8 Ave</td>\n",
       "      <td>40.741776</td>\n",
       "      <td>-74.001497</td>\n",
       "      <td>3230</td>\n",
       "      <td>Penn Station Valet</td>\n",
       "      <td>40.751284</td>\n",
       "      <td>-73.996924</td>\n",
       "      <td>14946</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1973.0</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9576 rows x 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       tripduration       starttime        stoptime  start_station_id  \\\n",
       "0               730     2/9/15 8:37     2/9/15 8:49                72   \n",
       "1               704  11/20/13 20:21  11/20/13 20:33                72   \n",
       "2               425    1/6/16 17:01    1/6/16 17:08                72   \n",
       "3               373   11/9/15 12:50   11/9/15 12:56                72   \n",
       "4              1149    8/3/13 17:14    8/3/13 17:33                72   \n",
       "...             ...             ...             ...               ...   \n",
       "10884           172    1/2/16 21:29    1/2/16 21:32               116   \n",
       "10885           692    7/20/16 9:29    7/20/16 9:40               116   \n",
       "10886           432   8/26/15 10:14   8/26/15 10:21               116   \n",
       "10887           827    9/2/14 21:38    9/2/14 21:52               116   \n",
       "10888           279    5/6/16 18:38    5/6/16 18:43               116   \n",
       "\n",
       "      start_station_name  start_station_latitude  start_station_longitude  \\\n",
       "0       W 52 St & 11 Ave               40.767272               -73.993929   \n",
       "1       W 52 St & 11 Ave               40.767272               -73.993929   \n",
       "2       W 52 St & 11 Ave               40.767272               -73.993929   \n",
       "3       W 52 St & 11 Ave               40.767272               -73.993929   \n",
       "4       W 52 St & 11 Ave               40.767272               -73.993929   \n",
       "...                  ...                     ...                      ...   \n",
       "10884    W 17 St & 8 Ave               40.741776               -74.001497   \n",
       "10885    W 17 St & 8 Ave               40.741776               -74.001497   \n",
       "10886    W 17 St & 8 Ave               40.741776               -74.001497   \n",
       "10887    W 17 St & 8 Ave               40.741776               -74.001497   \n",
       "10888    W 17 St & 8 Ave               40.741776               -74.001497   \n",
       "\n",
       "       end_station_id         end_station_name  end_station_latitude  \\\n",
       "0                 520          W 52 St & 5 Ave             40.759923   \n",
       "1                 470          W 20 St & 8 Ave             40.743453   \n",
       "2                 469       Broadway & W 53 St             40.763441   \n",
       "3                 469       Broadway & W 53 St             40.763441   \n",
       "4                 325          E 19 St & 3 Ave             40.736245   \n",
       "...               ...                      ...                   ...   \n",
       "10884             212  W 16 St & The High Line             40.743349   \n",
       "10885            2021          W 45 St & 8 Ave             40.759291   \n",
       "10886             509          9 Ave & W 22 St             40.745497   \n",
       "10887             439           E 4 St & 2 Ave             40.726281   \n",
       "10888            3230       Penn Station Valet             40.751284   \n",
       "\n",
       "       end_station_longitude  bikeid    usertype  birth_year  gender  \n",
       "0                 -73.976485   18809  Subscriber      1975.0    male  \n",
       "1                 -74.000040   20515  Subscriber      1981.0    male  \n",
       "2                 -73.982681   17116  Subscriber      1947.0    male  \n",
       "3                 -73.982681   20892  Subscriber      1947.0    male  \n",
       "4                 -73.984738   17711  Subscriber      1981.0  female  \n",
       "...                      ...     ...         ...         ...     ...  \n",
       "10884             -74.006818   20945  Subscriber      1961.0    male  \n",
       "10885             -73.988597   19345  Subscriber      1983.0    male  \n",
       "10886             -74.001971   23512  Subscriber      1962.0  female  \n",
       "10887             -73.989780   21211  Subscriber      1985.0    male  \n",
       "10888             -73.996924   14946  Subscriber      1973.0    male  \n",
       "\n",
       "[9576 rows x 15 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cleaned"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb7fd65b-23d2-4f57-af56-19045d84edb9",
   "metadata": {},
   "source": [
    "Like in pandas, when you are working with a dataframe, you are always working on a temporary copy of the data. \n",
    "\n",
    "By default, all pandas operations returns a copy of the dataframe that was operated on. Ponder never make any modifications to your original data. This is especially important in the data warehouse context as the tables are often regarded as the “source of truth” and can be shared across many teams."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4c9556ad-40d4-4596-a739-eb515413ee42",
   "metadata": {},
   "source": [
    "This also means that the reference to your dataframe df is only accessible throughout the session. That means that once you exit the session, the dataframe is no longer accessible. \n",
    "\n",
    "So if we plan to use the cleaned up data for our analysis later, we can persist it in a table via `to_sql`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "955d8fb3-6f3c-4a4c-83bd-3fcb25ffa3d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cleaned.to_sql(\"PONDER_CITIBIKE_CLEANED\",con=duckdb_con, index=False, if_exists='replace')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8c765dbf-88f5-429f-a4a4-7c765b92ca9f",
   "metadata": {},
   "source": [
    "Now we can access new new table and continue our analysis whereever we want:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "e4eb4377-f6c8-4bbe-b0bd-9bbe19292ff8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>tripduration</th>\n",
       "      <th>starttime</th>\n",
       "      <th>stoptime</th>\n",
       "      <th>start_station_id</th>\n",
       "      <th>start_station_name</th>\n",
       "      <th>start_station_latitude</th>\n",
       "      <th>start_station_longitude</th>\n",
       "      <th>end_station_id</th>\n",
       "      <th>end_station_name</th>\n",
       "      <th>end_station_latitude</th>\n",
       "      <th>end_station_longitude</th>\n",
       "      <th>bikeid</th>\n",
       "      <th>usertype</th>\n",
       "      <th>birth_year</th>\n",
       "      <th>gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>730</td>\n",
       "      <td>2/9/15 8:37</td>\n",
       "      <td>2/9/15 8:49</td>\n",
       "      <td>72</td>\n",
       "      <td>W 52 St &amp; 11 Ave</td>\n",
       "      <td>40.767272</td>\n",
       "      <td>-73.993929</td>\n",
       "      <td>520</td>\n",
       "      <td>W 52 St &amp; 5 Ave</td>\n",
       "      <td>40.759923</td>\n",
       "      <td>-73.976485</td>\n",
       "      <td>18809</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1975.0</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>704</td>\n",
       "      <td>11/20/13 20:21</td>\n",
       "      <td>11/20/13 20:33</td>\n",
       "      <td>72</td>\n",
       "      <td>W 52 St &amp; 11 Ave</td>\n",
       "      <td>40.767272</td>\n",
       "      <td>-73.993929</td>\n",
       "      <td>470</td>\n",
       "      <td>W 20 St &amp; 8 Ave</td>\n",
       "      <td>40.743453</td>\n",
       "      <td>-74.000040</td>\n",
       "      <td>20515</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1981.0</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>425</td>\n",
       "      <td>1/6/16 17:01</td>\n",
       "      <td>1/6/16 17:08</td>\n",
       "      <td>72</td>\n",
       "      <td>W 52 St &amp; 11 Ave</td>\n",
       "      <td>40.767272</td>\n",
       "      <td>-73.993929</td>\n",
       "      <td>469</td>\n",
       "      <td>Broadway &amp; W 53 St</td>\n",
       "      <td>40.763441</td>\n",
       "      <td>-73.982681</td>\n",
       "      <td>17116</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1947.0</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>373</td>\n",
       "      <td>11/9/15 12:50</td>\n",
       "      <td>11/9/15 12:56</td>\n",
       "      <td>72</td>\n",
       "      <td>W 52 St &amp; 11 Ave</td>\n",
       "      <td>40.767272</td>\n",
       "      <td>-73.993929</td>\n",
       "      <td>469</td>\n",
       "      <td>Broadway &amp; W 53 St</td>\n",
       "      <td>40.763441</td>\n",
       "      <td>-73.982681</td>\n",
       "      <td>20892</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1947.0</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1149</td>\n",
       "      <td>8/3/13 17:14</td>\n",
       "      <td>8/3/13 17:33</td>\n",
       "      <td>72</td>\n",
       "      <td>W 52 St &amp; 11 Ave</td>\n",
       "      <td>40.767272</td>\n",
       "      <td>-73.993929</td>\n",
       "      <td>325</td>\n",
       "      <td>E 19 St &amp; 3 Ave</td>\n",
       "      <td>40.736245</td>\n",
       "      <td>-73.984738</td>\n",
       "      <td>17711</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1981.0</td>\n",
       "      <td>female</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9571</th>\n",
       "      <td>172</td>\n",
       "      <td>1/2/16 21:29</td>\n",
       "      <td>1/2/16 21:32</td>\n",
       "      <td>116</td>\n",
       "      <td>W 17 St &amp; 8 Ave</td>\n",
       "      <td>40.741776</td>\n",
       "      <td>-74.001497</td>\n",
       "      <td>212</td>\n",
       "      <td>W 16 St &amp; The High Line</td>\n",
       "      <td>40.743349</td>\n",
       "      <td>-74.006818</td>\n",
       "      <td>20945</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1961.0</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9572</th>\n",
       "      <td>692</td>\n",
       "      <td>7/20/16 9:29</td>\n",
       "      <td>7/20/16 9:40</td>\n",
       "      <td>116</td>\n",
       "      <td>W 17 St &amp; 8 Ave</td>\n",
       "      <td>40.741776</td>\n",
       "      <td>-74.001497</td>\n",
       "      <td>2021</td>\n",
       "      <td>W 45 St &amp; 8 Ave</td>\n",
       "      <td>40.759291</td>\n",
       "      <td>-73.988597</td>\n",
       "      <td>19345</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1983.0</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9573</th>\n",
       "      <td>432</td>\n",
       "      <td>8/26/15 10:14</td>\n",
       "      <td>8/26/15 10:21</td>\n",
       "      <td>116</td>\n",
       "      <td>W 17 St &amp; 8 Ave</td>\n",
       "      <td>40.741776</td>\n",
       "      <td>-74.001497</td>\n",
       "      <td>509</td>\n",
       "      <td>9 Ave &amp; W 22 St</td>\n",
       "      <td>40.745497</td>\n",
       "      <td>-74.001971</td>\n",
       "      <td>23512</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1962.0</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9574</th>\n",
       "      <td>827</td>\n",
       "      <td>9/2/14 21:38</td>\n",
       "      <td>9/2/14 21:52</td>\n",
       "      <td>116</td>\n",
       "      <td>W 17 St &amp; 8 Ave</td>\n",
       "      <td>40.741776</td>\n",
       "      <td>-74.001497</td>\n",
       "      <td>439</td>\n",
       "      <td>E 4 St &amp; 2 Ave</td>\n",
       "      <td>40.726281</td>\n",
       "      <td>-73.989780</td>\n",
       "      <td>21211</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1985.0</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9575</th>\n",
       "      <td>279</td>\n",
       "      <td>5/6/16 18:38</td>\n",
       "      <td>5/6/16 18:43</td>\n",
       "      <td>116</td>\n",
       "      <td>W 17 St &amp; 8 Ave</td>\n",
       "      <td>40.741776</td>\n",
       "      <td>-74.001497</td>\n",
       "      <td>3230</td>\n",
       "      <td>Penn Station Valet</td>\n",
       "      <td>40.751284</td>\n",
       "      <td>-73.996924</td>\n",
       "      <td>14946</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1973.0</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9576 rows x 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      tripduration       starttime        stoptime  start_station_id  \\\n",
       "0              730     2/9/15 8:37     2/9/15 8:49                72   \n",
       "1              704  11/20/13 20:21  11/20/13 20:33                72   \n",
       "2              425    1/6/16 17:01    1/6/16 17:08                72   \n",
       "3              373   11/9/15 12:50   11/9/15 12:56                72   \n",
       "4             1149    8/3/13 17:14    8/3/13 17:33                72   \n",
       "...            ...             ...             ...               ...   \n",
       "9571           172    1/2/16 21:29    1/2/16 21:32               116   \n",
       "9572           692    7/20/16 9:29    7/20/16 9:40               116   \n",
       "9573           432   8/26/15 10:14   8/26/15 10:21               116   \n",
       "9574           827    9/2/14 21:38    9/2/14 21:52               116   \n",
       "9575           279    5/6/16 18:38    5/6/16 18:43               116   \n",
       "\n",
       "     start_station_name  start_station_latitude  start_station_longitude  \\\n",
       "0      W 52 St & 11 Ave               40.767272               -73.993929   \n",
       "1      W 52 St & 11 Ave               40.767272               -73.993929   \n",
       "2      W 52 St & 11 Ave               40.767272               -73.993929   \n",
       "3      W 52 St & 11 Ave               40.767272               -73.993929   \n",
       "4      W 52 St & 11 Ave               40.767272               -73.993929   \n",
       "...                 ...                     ...                      ...   \n",
       "9571    W 17 St & 8 Ave               40.741776               -74.001497   \n",
       "9572    W 17 St & 8 Ave               40.741776               -74.001497   \n",
       "9573    W 17 St & 8 Ave               40.741776               -74.001497   \n",
       "9574    W 17 St & 8 Ave               40.741776               -74.001497   \n",
       "9575    W 17 St & 8 Ave               40.741776               -74.001497   \n",
       "\n",
       "      end_station_id         end_station_name  end_station_latitude  \\\n",
       "0                520          W 52 St & 5 Ave             40.759923   \n",
       "1                470          W 20 St & 8 Ave             40.743453   \n",
       "2                469       Broadway & W 53 St             40.763441   \n",
       "3                469       Broadway & W 53 St             40.763441   \n",
       "4                325          E 19 St & 3 Ave             40.736245   \n",
       "...              ...                      ...                   ...   \n",
       "9571             212  W 16 St & The High Line             40.743349   \n",
       "9572            2021          W 45 St & 8 Ave             40.759291   \n",
       "9573             509          9 Ave & W 22 St             40.745497   \n",
       "9574             439           E 4 St & 2 Ave             40.726281   \n",
       "9575            3230       Penn Station Valet             40.751284   \n",
       "\n",
       "      end_station_longitude  bikeid    usertype  birth_year  gender  \n",
       "0                -73.976485   18809  Subscriber      1975.0    male  \n",
       "1                -74.000040   20515  Subscriber      1981.0    male  \n",
       "2                -73.982681   17116  Subscriber      1947.0    male  \n",
       "3                -73.982681   20892  Subscriber      1947.0    male  \n",
       "4                -73.984738   17711  Subscriber      1981.0  female  \n",
       "...                     ...     ...         ...         ...     ...  \n",
       "9571             -74.006818   20945  Subscriber      1961.0    male  \n",
       "9572             -73.988597   19345  Subscriber      1983.0    male  \n",
       "9573             -74.001971   23512  Subscriber      1962.0  female  \n",
       "9574             -73.989780   21211  Subscriber      1985.0    male  \n",
       "9575             -73.996924   14946  Subscriber      1973.0    male  \n",
       "\n",
       "[9576 rows x 15 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\"PONDER_CITIBIKE_CLEANED\",con=duckdb_con)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c1e3c156-6230-432d-9e5a-1fdf6bbb06ae",
   "metadata": {},
   "source": [
    "# Summary\n",
    "\n",
    "In this tutorial, we learned how to leverage the same pandas API for `pd.to_sql` to save your dataframe to your database. This is often useful if you want to persist the work done on the dataframe beyond your current session. \n",
    "\n",
    "In our [next tutorial](https://github.com/ponder-org/ponder-notebooks/blob/main/snowflake/tutorial/05-advanced-sql.ipynb), we will discuss how you can easily move between using Ponder and using SQL when developing your data workflows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "49a7cf8e-5718-42d0-b149-9a47576eb71f",
   "metadata": {},
   "outputs": [],
   "source": [
    "duckdb_con.close()"
   ]
  }
 ],
 "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}