main.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "from dags.data_engineering_pipeline import extract_from_postgres, extract_from_csv, load_into_final_database, execute_query\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Step 1\n",
    "Write data to local disk"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data extracted from table 'suppliers' and saved to /home/njoppi2/projetos/pessoal/data-pipeline/data/postgres/suppliers/2023-08-22/suppliers.csv\n",
      "Data extracted from table 'employees' and saved to /home/njoppi2/projetos/pessoal/data-pipeline/data/postgres/employees/2023-08-22/employees.csv\n",
      "Data extracted from table 'shippers' and saved to /home/njoppi2/projetos/pessoal/data-pipeline/data/postgres/shippers/2023-08-22/shippers.csv\n",
      "Data extracted from table 'categories' and saved to /home/njoppi2/projetos/pessoal/data-pipeline/data/postgres/categories/2023-08-22/categories.csv\n",
      "Data extracted from table 'employee_territories' and saved to /home/njoppi2/projetos/pessoal/data-pipeline/data/postgres/employee_territories/2023-08-22/employee_territories.csv\n",
      "Data extracted from table 'region' and saved to /home/njoppi2/projetos/pessoal/data-pipeline/data/postgres/region/2023-08-22/region.csv\n",
      "Data extracted from table 'customer_demographics' and saved to /home/njoppi2/projetos/pessoal/data-pipeline/data/postgres/customer_demographics/2023-08-22/customer_demographics.csv\n",
      "Data extracted from table 'us_states' and saved to /home/njoppi2/projetos/pessoal/data-pipeline/data/postgres/us_states/2023-08-22/us_states.csv\n",
      "Data extracted from table 'products' and saved to /home/njoppi2/projetos/pessoal/data-pipeline/data/postgres/products/2023-08-22/products.csv\n",
      "Data extracted from table 'territories' and saved to /home/njoppi2/projetos/pessoal/data-pipeline/data/postgres/territories/2023-08-22/territories.csv\n",
      "Data extracted from table 'customer_customer_demo' and saved to /home/njoppi2/projetos/pessoal/data-pipeline/data/postgres/customer_customer_demo/2023-08-22/customer_customer_demo.csv\n",
      "Data extracted from table 'customers' and saved to /home/njoppi2/projetos/pessoal/data-pipeline/data/postgres/customers/2023-08-22/customers.csv\n",
      "Data extracted from table 'orders' and saved to /home/njoppi2/projetos/pessoal/data-pipeline/data/postgres/orders/2023-08-22/orders.csv\n",
      "Data extracted from CSV file '/home/njoppi2/projetos/pessoal/data-pipeline/data/order_details.csv' and saved to /home/njoppi2/projetos/pessoal/data-pipeline/data/csv/2023-08-22/order_details.csv\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['/home/njoppi2/projetos/pessoal/data-pipeline/data/csv/2023-08-22/order_details.csv']"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "extract_from_postgres()\n",
    "extract_from_csv()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Step 2\n",
    "Load data to final database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Didn't create database: database \"output_northwind\" already exists\n",
      "\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data loaded into table 'suppliers' in the target database.\n",
      "Data loaded into table 'employees' in the target database.\n",
      "Data loaded into table 'shippers' in the target database.\n",
      "Data loaded into table 'categories' in the target database.\n",
      "Data loaded into table 'employee_territories' in the target database.\n",
      "Data loaded into table 'region' in the target database.\n",
      "Data loaded into table 'customer_demographics' in the target database.\n",
      "Data loaded into table 'us_states' in the target database.\n",
      "Data loaded into table 'products' in the target database.\n",
      "Data loaded into table 'territories' in the target database.\n",
      "Data loaded into table 'customer_customer_demo' in the target database.\n",
      "Data loaded into table 'customers' in the target database.\n",
      "Data loaded into table 'orders' in the target database.\n",
      "Data loaded into table 'order_details' in the target database.\n"
     ]
    }
   ],
   "source": [
    "load_into_final_database()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Final query\n",
    "Show the orders and its details"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>order_id</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>employee_id</th>\n",
       "      <th>order_date</th>\n",
       "      <th>required_date</th>\n",
       "      <th>shipped_date</th>\n",
       "      <th>ship_via</th>\n",
       "      <th>freight</th>\n",
       "      <th>ship_name</th>\n",
       "      <th>ship_address</th>\n",
       "      <th>ship_city</th>\n",
       "      <th>ship_region</th>\n",
       "      <th>ship_postal_code</th>\n",
       "      <th>ship_country</th>\n",
       "      <th>order_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>unit_price</th>\n",
       "      <th>quantity</th>\n",
       "      <th>discount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10248</td>\n",
       "      <td>VINET</td>\n",
       "      <td>5</td>\n",
       "      <td>1996-07-04</td>\n",
       "      <td>1996-08-01</td>\n",
       "      <td>1996-07-16</td>\n",
       "      <td>3</td>\n",
       "      <td>32.38</td>\n",
       "      <td>Vins et alcools Chevalier</td>\n",
       "      <td>59 rue de l'Abbaye</td>\n",
       "      <td>Reims</td>\n",
       "      <td>None</td>\n",
       "      <td>51100</td>\n",
       "      <td>France</td>\n",
       "      <td>10248</td>\n",
       "      <td>11</td>\n",
       "      <td>14.00</td>\n",
       "      <td>12</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10248</td>\n",
       "      <td>VINET</td>\n",
       "      <td>5</td>\n",
       "      <td>1996-07-04</td>\n",
       "      <td>1996-08-01</td>\n",
       "      <td>1996-07-16</td>\n",
       "      <td>3</td>\n",
       "      <td>32.38</td>\n",
       "      <td>Vins et alcools Chevalier</td>\n",
       "      <td>59 rue de l'Abbaye</td>\n",
       "      <td>Reims</td>\n",
       "      <td>None</td>\n",
       "      <td>51100</td>\n",
       "      <td>France</td>\n",
       "      <td>10248</td>\n",
       "      <td>42</td>\n",
       "      <td>9.80</td>\n",
       "      <td>10</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10248</td>\n",
       "      <td>VINET</td>\n",
       "      <td>5</td>\n",
       "      <td>1996-07-04</td>\n",
       "      <td>1996-08-01</td>\n",
       "      <td>1996-07-16</td>\n",
       "      <td>3</td>\n",
       "      <td>32.38</td>\n",
       "      <td>Vins et alcools Chevalier</td>\n",
       "      <td>59 rue de l'Abbaye</td>\n",
       "      <td>Reims</td>\n",
       "      <td>None</td>\n",
       "      <td>51100</td>\n",
       "      <td>France</td>\n",
       "      <td>10248</td>\n",
       "      <td>72</td>\n",
       "      <td>34.80</td>\n",
       "      <td>5</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10249</td>\n",
       "      <td>TOMSP</td>\n",
       "      <td>6</td>\n",
       "      <td>1996-07-05</td>\n",
       "      <td>1996-08-16</td>\n",
       "      <td>1996-07-10</td>\n",
       "      <td>1</td>\n",
       "      <td>11.61</td>\n",
       "      <td>Toms Spezialitäten</td>\n",
       "      <td>Luisenstr. 48</td>\n",
       "      <td>Münster</td>\n",
       "      <td>None</td>\n",
       "      <td>44087</td>\n",
       "      <td>Germany</td>\n",
       "      <td>10249</td>\n",
       "      <td>14</td>\n",
       "      <td>18.60</td>\n",
       "      <td>9</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10249</td>\n",
       "      <td>TOMSP</td>\n",
       "      <td>6</td>\n",
       "      <td>1996-07-05</td>\n",
       "      <td>1996-08-16</td>\n",
       "      <td>1996-07-10</td>\n",
       "      <td>1</td>\n",
       "      <td>11.61</td>\n",
       "      <td>Toms Spezialitäten</td>\n",
       "      <td>Luisenstr. 48</td>\n",
       "      <td>Münster</td>\n",
       "      <td>None</td>\n",
       "      <td>44087</td>\n",
       "      <td>Germany</td>\n",
       "      <td>10249</td>\n",
       "      <td>51</td>\n",
       "      <td>42.40</td>\n",
       "      <td>40</td>\n",
       "      <td>0.00</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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2150</th>\n",
       "      <td>11077</td>\n",
       "      <td>RATTC</td>\n",
       "      <td>1</td>\n",
       "      <td>1998-05-06</td>\n",
       "      <td>1998-06-03</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>8.53</td>\n",
       "      <td>Rattlesnake Canyon Grocery</td>\n",
       "      <td>2817 Milton Dr.</td>\n",
       "      <td>Albuquerque</td>\n",
       "      <td>NM</td>\n",
       "      <td>87110</td>\n",
       "      <td>USA</td>\n",
       "      <td>11077</td>\n",
       "      <td>64</td>\n",
       "      <td>33.25</td>\n",
       "      <td>2</td>\n",
       "      <td>0.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2151</th>\n",
       "      <td>11077</td>\n",
       "      <td>RATTC</td>\n",
       "      <td>1</td>\n",
       "      <td>1998-05-06</td>\n",
       "      <td>1998-06-03</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>8.53</td>\n",
       "      <td>Rattlesnake Canyon Grocery</td>\n",
       "      <td>2817 Milton Dr.</td>\n",
       "      <td>Albuquerque</td>\n",
       "      <td>NM</td>\n",
       "      <td>87110</td>\n",
       "      <td>USA</td>\n",
       "      <td>11077</td>\n",
       "      <td>66</td>\n",
       "      <td>17.00</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2152</th>\n",
       "      <td>11077</td>\n",
       "      <td>RATTC</td>\n",
       "      <td>1</td>\n",
       "      <td>1998-05-06</td>\n",
       "      <td>1998-06-03</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>8.53</td>\n",
       "      <td>Rattlesnake Canyon Grocery</td>\n",
       "      <td>2817 Milton Dr.</td>\n",
       "      <td>Albuquerque</td>\n",
       "      <td>NM</td>\n",
       "      <td>87110</td>\n",
       "      <td>USA</td>\n",
       "      <td>11077</td>\n",
       "      <td>73</td>\n",
       "      <td>15.00</td>\n",
       "      <td>2</td>\n",
       "      <td>0.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2153</th>\n",
       "      <td>11077</td>\n",
       "      <td>RATTC</td>\n",
       "      <td>1</td>\n",
       "      <td>1998-05-06</td>\n",
       "      <td>1998-06-03</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>8.53</td>\n",
       "      <td>Rattlesnake Canyon Grocery</td>\n",
       "      <td>2817 Milton Dr.</td>\n",
       "      <td>Albuquerque</td>\n",
       "      <td>NM</td>\n",
       "      <td>87110</td>\n",
       "      <td>USA</td>\n",
       "      <td>11077</td>\n",
       "      <td>75</td>\n",
       "      <td>7.75</td>\n",
       "      <td>4</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2154</th>\n",
       "      <td>11077</td>\n",
       "      <td>RATTC</td>\n",
       "      <td>1</td>\n",
       "      <td>1998-05-06</td>\n",
       "      <td>1998-06-03</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>8.53</td>\n",
       "      <td>Rattlesnake Canyon Grocery</td>\n",
       "      <td>2817 Milton Dr.</td>\n",
       "      <td>Albuquerque</td>\n",
       "      <td>NM</td>\n",
       "      <td>87110</td>\n",
       "      <td>USA</td>\n",
       "      <td>11077</td>\n",
       "      <td>77</td>\n",
       "      <td>13.00</td>\n",
       "      <td>2</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2155 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      order_id customer_id  employee_id  order_date required_date  \\\n",
       "0        10248       VINET            5  1996-07-04    1996-08-01   \n",
       "1        10248       VINET            5  1996-07-04    1996-08-01   \n",
       "2        10248       VINET            5  1996-07-04    1996-08-01   \n",
       "3        10249       TOMSP            6  1996-07-05    1996-08-16   \n",
       "4        10249       TOMSP            6  1996-07-05    1996-08-16   \n",
       "...        ...         ...          ...         ...           ...   \n",
       "2150     11077       RATTC            1  1998-05-06    1998-06-03   \n",
       "2151     11077       RATTC            1  1998-05-06    1998-06-03   \n",
       "2152     11077       RATTC            1  1998-05-06    1998-06-03   \n",
       "2153     11077       RATTC            1  1998-05-06    1998-06-03   \n",
       "2154     11077       RATTC            1  1998-05-06    1998-06-03   \n",
       "\n",
       "     shipped_date  ship_via  freight                   ship_name  \\\n",
       "0      1996-07-16         3    32.38   Vins et alcools Chevalier   \n",
       "1      1996-07-16         3    32.38   Vins et alcools Chevalier   \n",
       "2      1996-07-16         3    32.38   Vins et alcools Chevalier   \n",
       "3      1996-07-10         1    11.61          Toms Spezialitäten   \n",
       "4      1996-07-10         1    11.61          Toms Spezialitäten   \n",
       "...           ...       ...      ...                         ...   \n",
       "2150         None         2     8.53  Rattlesnake Canyon Grocery   \n",
       "2151         None         2     8.53  Rattlesnake Canyon Grocery   \n",
       "2152         None         2     8.53  Rattlesnake Canyon Grocery   \n",
       "2153         None         2     8.53  Rattlesnake Canyon Grocery   \n",
       "2154         None         2     8.53  Rattlesnake Canyon Grocery   \n",
       "\n",
       "            ship_address    ship_city ship_region ship_postal_code  \\\n",
       "0     59 rue de l'Abbaye        Reims        None            51100   \n",
       "1     59 rue de l'Abbaye        Reims        None            51100   \n",
       "2     59 rue de l'Abbaye        Reims        None            51100   \n",
       "3          Luisenstr. 48      Münster        None            44087   \n",
       "4          Luisenstr. 48      Münster        None            44087   \n",
       "...                  ...          ...         ...              ...   \n",
       "2150     2817 Milton Dr.  Albuquerque          NM            87110   \n",
       "2151     2817 Milton Dr.  Albuquerque          NM            87110   \n",
       "2152     2817 Milton Dr.  Albuquerque          NM            87110   \n",
       "2153     2817 Milton Dr.  Albuquerque          NM            87110   \n",
       "2154     2817 Milton Dr.  Albuquerque          NM            87110   \n",
       "\n",
       "     ship_country  order_id  product_id  unit_price  quantity  discount  \n",
       "0          France     10248          11       14.00        12      0.00  \n",
       "1          France     10248          42        9.80        10      0.00  \n",
       "2          France     10248          72       34.80         5      0.00  \n",
       "3         Germany     10249          14       18.60         9      0.00  \n",
       "4         Germany     10249          51       42.40        40      0.00  \n",
       "...           ...       ...         ...         ...       ...       ...  \n",
       "2150          USA     11077          64       33.25         2      0.03  \n",
       "2151          USA     11077          66       17.00         1      0.00  \n",
       "2152          USA     11077          73       15.00         2      0.01  \n",
       "2153          USA     11077          75        7.75         4      0.00  \n",
       "2154          USA     11077          77       13.00         2      0.00  \n",
       "\n",
       "[2155 rows x 19 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "execute_query('''\n",
    "    SELECT *\n",
    "    FROM public.orders AS o\n",
    "    LEFT JOIN public.order_details AS od ON o.order_id = od.order_id\n",
    "''')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}