templates/etl/README.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "6ff6acaa",
   "metadata": {
    "papermill": {
     "duration": 0.010757,
     "end_time": "2021-10-28T01:27:00.902996",
     "exception": false,
     "start_time": "2021-10-28T01:27:00.892239",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "To run this locally, [install Ploomber](https://docs.ploomber.io/en/latest/get-started/quick-start.html) and execute: `ploomber examples -n templates/etl`\n",
    "\n",
    "Found an issue? [Let us know.](https://github.com/ploomber/projects/issues/new?title=templates/etl%20issue)\n",
    "\n",
    "Questions? [Ask us on Slack.](https://ploomber.io/community/)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "acefe3ae",
   "metadata": {
    "papermill": {
     "duration": 0.00961,
     "end_time": "2021-10-28T01:27:00.922964",
     "exception": false,
     "start_time": "2021-10-28T01:27:00.913354",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# ETL SQL pipeline\n",
    "\n",
    "<!-- start description -->\n",
    "Download a data file, upload it to a database, process it, and plot with Python and R.\n",
    "<!-- end description -->\n",
    "\n",
    "**Note:** This pipeline requires the `p7zip` package. It is installed if using `conda` (`environment.yml`). If using `pip`, you must install it yourself.\n",
    "\n",
    "## Data\n",
    "\n",
    "Subset of the [Stack Exchange dataset](https://archive.org/details/stackexchange).\n",
    "It gets the data from the original source, converts it from XML to CSV, uploads it to a database, aggregates it, dumps it and generates a few plots. See the diagram below (generated using `ploomber plot`):\n",
    "\n",
    "![pipeline](pipeline.png)\n",
    "\n",
    "The ``pipeline.yaml`` file contains a few comments to understand what's going on at each step."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd2ffe3e",
   "metadata": {
    "papermill": {
     "duration": 0.009853,
     "end_time": "2021-10-28T01:27:00.941866",
     "exception": false,
     "start_time": "2021-10-28T01:27:00.932013",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "## Build"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "c1ce02da",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-10-28T01:27:00.969829Z",
     "iopub.status.busy": "2021-10-28T01:27:00.968963Z",
     "iopub.status.idle": "2021-10-28T01:30:33.540415Z",
     "shell.execute_reply": "2021-10-28T01:30:33.539286Z"
    },
    "papermill": {
     "duration": 212.589891,
     "end_time": "2021-10-28T01:30:33.540724",
     "exception": false,
     "start_time": "2021-10-28T01:27:00.950833",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "name                 Ran?      Elapsed (s)    Percentage\n",
      "-------------------  ------  -------------  ------------\n",
      "download             True       154.889      74.8738\n",
      "convert2csv          True        22.4383     10.8468\n",
      "upload-users         True         2.30646     1.11495\n",
      "upload-comments      True         4.60881     2.22792\n",
      "upload-posts         True         4.28594     2.07184\n",
      "upvotes-by-location  True         0.12399     0.0599373\n",
      "comments-by-post     True         0.069218    0.0334603\n",
      "posts-by-length      True         0.664741    0.321339\n",
      "upvotes-dump         True         0.005649    0.00273075\n",
      "comments-dump        True         0.064557    0.0312071\n",
      "posts-dump           True         0.119351    0.0576948\n",
      "plot-upvotes         True         5.65937     2.73576\n",
      "comments-plot        True         5.90999     2.85691\n",
      "posts-plot           True         5.72109     2.7656\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/Edu/dev/ploomber/src/ploomber/dag/dag.py:390: UserWarning: \n",
      "=========================== DAG render with warnings ===========================\n",
      "-------- SQLUpload: upload-users -> SQLiteRelation(('users', 'table')) ---------\n",
      "The following upstream dependencies in task \"upload-users\" were not used {'convert2csv'}\n",
      "----- SQLUpload: upload-comments -> SQLiteRelation(('comments', 'table')) ------\n",
      "The following upstream dependencies in task \"upload-comments\" were not used {'convert2csv'}\n",
      "-------- SQLUpload: upload-posts -> SQLiteRelation(('posts', 'table')) ---------\n",
      "The following upstream dependencies in task \"upload-posts\" were not used {'convert2csv'}\n",
      "============================== Summary (3 tasks) ===============================\n",
      "SQLUpload: upload-users -> SQLiteRelation(('users', 'table'))\n",
      "SQLUpload: upload-comments -> SQLiteRelation(('comments', 'table'))\n",
      "SQLUpload: upload-posts -> SQLiteRelation(('posts', 'table'))\n",
      "=========================== DAG render with warnings ===========================\n",
      "\n",
      "  warnings.warn(str(warnings_))\n",
      "Building task 'download':   0%|          | 0/14 [00:00<?, ?it/s]\n",
      "Executing:   0%|          | 0/5 [00:00<?, ?cell/s]\u001b[A\n",
      "Executing:  20%|██        | 1/5 [00:01<00:06,  1.57s/cell]\u001b[A\n",
      "Executing:  80%|████████  | 4/5 [00:01<00:00,  3.06cell/s]\u001b[A\n",
      "Executing:  80%|████████  | 4/5 [00:19<00:00,  3.06cell/s]\u001b[A\n",
      "Executing: 100%|██████████| 5/5 [02:34<00:00, 30.97s/cell]\u001b[A\n",
      "Building task 'convert2csv':   7%|▋         | 1/14 [02:34<33:33, 154.90s/it]\n",
      "Executing:   0%|          | 0/5 [00:00<?, ?cell/s]\u001b[A\n",
      "Executing:  20%|██        | 1/5 [00:01<00:07,  1.96s/cell]\u001b[A\n",
      "Executing:  80%|████████  | 4/5 [00:02<00:00,  2.45cell/s]\u001b[A\n",
      "Executing:  80%|████████  | 4/5 [00:15<00:00,  2.45cell/s]\u001b[A\n",
      "Executing: 100%|██████████| 5/5 [00:22<00:00,  4.48s/cell]\u001b[A\n",
      "Building task 'plot-upvotes':  79%|███████▊  | 11/14 [03:09<00:09,  3.28s/it]      \n",
      "Executing:   0%|          | 0/5 [00:00<?, ?cell/s]\u001b[A\n",
      "Executing:  20%|██        | 1/5 [00:01<00:07,  1.91s/cell]\u001b[A\n",
      "Executing:  40%|████      | 2/5 [00:02<00:02,  1.17cell/s]\u001b[A\n",
      "Executing:  60%|██████    | 3/5 [00:02<00:01,  1.94cell/s]\u001b[A\n",
      "Executing: 100%|██████████| 5/5 [00:04<00:00,  1.00cell/s]\u001b[A\n",
      "Building task 'comments-plot':  86%|████████▌ | 12/14 [03:15<00:07,  3.75s/it]\n",
      "Executing:   0%|          | 0/5 [00:00<?, ?cell/s]\u001b[A\n",
      "Executing:  20%|██        | 1/5 [00:04<00:18,  4.53s/cell]\u001b[A\n",
      "Executing: 100%|██████████| 5/5 [00:05<00:00,  1.09s/cell]\u001b[A\n",
      "Building task 'posts-plot':  93%|█████████▎| 13/14 [03:21<00:04,  4.22s/it]   \n",
      "Executing:   0%|          | 0/7 [00:00<?, ?cell/s]\u001b[A\n",
      "Executing:  14%|█▍        | 1/7 [00:02<00:16,  2.78s/cell]\u001b[A\n",
      "Executing:  57%|█████▋    | 4/7 [00:03<00:02,  1.42cell/s]\u001b[A\n",
      "Executing: 100%|██████████| 7/7 [00:04<00:00,  1.48cell/s]\u001b[A\n",
      "Building task 'posts-plot': 100%|██████████| 14/14 [03:26<00:00, 14.78s/it]\n"
     ]
    }
   ],
   "source": [
    "%%sh\n",
    "ploomber build"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e92da28f",
   "metadata": {
    "papermill": {
     "duration": 0.026142,
     "end_time": "2021-10-28T01:30:33.604278",
     "exception": false,
     "start_time": "2021-10-28T01:30:33.578136",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "Output stored in the ``output/`` directory."
   ]
  }
 ],
 "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.1"
  },
  "papermill": {
   "default_parameters": {},
   "duration": 214.32228,
   "end_time": "2021-10-28T01:30:33.953041",
   "environment_variables": {},
   "exception": null,
   "input_path": "templates/etl/_build/readme_preprocessed.ipynb",
   "output_path": "templates/etl/README.ipynb",
   "parameters": {},
   "start_time": "2021-10-28T01:26:59.630761",
   "version": "2.3.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}