Processing_data_using_SQl_and_local_database.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "toc_visible": true,
      "authorship_tag": "ABX9TyN9XXc4yQwlMS7KTgDwR4z4",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/Indranil0603/versatile-data-kit/blob/Indranil%2FIndranil0603%2FColab-notebook-processing-data-using-SQL-and-local-database/Processing_data_using_SQl_and_local_database.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Processing data using SQl and local database"
      ],
      "metadata": {
        "id": "SiXBOvrboX9e"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "The notebook provides a guide on how to read data from a local SQLite database, process it,and write the result to the same database using [Versatile Data Kit (VDK)](https://github.com/vmware/versatile-data-kit)\n"
      ],
      "metadata": {
        "id": "FpCoerAj0hau"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "<a name=\"prerequisites\"></a>\n",
        "## 1. Prerequisites"
      ],
      "metadata": {
        "id": "f9XXCBpI1eMW"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### 1.1 Good to Know Before Your Start"
      ],
      "metadata": {
        "id": "q_pbljVH2cFr"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "This tutorial can be easily understood if you are familiar with:\n",
        "\n",
        "- **Python and SQL**: Basic commands and queries\n",
        "- **Tools**: Comfort with command line and Jupyter Notebook"
      ],
      "metadata": {
        "id": "jYmcEhgE2zNn"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### 1.2 Useful notebook shortcuts"
      ],
      "metadata": {
        "id": "CKYPWV8-3XE8"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "* Click the **Play icon** in the left gutter of the cell;\n",
        "* Type **Cmd/Ctrl+Enter** to run the cell in place;\n",
        "* Type **Shift+Enter** to run the cell and move focus to the next cell (adding one if none exists); or\n",
        "* Type **Alt+Enter** to run the cell and insert a new code cell immediately below it.\n",
        "\n",
        "There are additional options for running some or all cells in the **Runtime** menu on top."
      ],
      "metadata": {
        "id": "Mh2kLwz73mk3"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### 1.3 Install Versatile Data Kit and required plugins\n",
        "\n",
        "\n"
      ],
      "metadata": {
        "id": "5s_z56EF3v3o"
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "1tN44vxJoTLa"
      },
      "outputs": [],
      "source": [
        "!pip install vdk-ipython vdk-sqlite"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "The relevant Data Job code is in the upcoming cells.\n",
        "<br>Alternatively, you can see the implementation of the data job <a href=\"https://github.com/vmware/versatile-data-kit/tree/main/examples/sqlite-processing-example/sqlite-example-job\">here</a>"
      ],
      "metadata": {
        "id": "Orkqbxmq6b8V"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 2. Database"
      ],
      "metadata": {
        "id": "9QrNfgmB7Xul"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "We will be using the chinook SQLite database. Here we can download it using the following commands."
      ],
      "metadata": {
        "id": "LU5Dl2ZS7e0t"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "!curl https://www.sqlitetutorial.net/wp-content/uploads/2018/03/chinook.zip >> chinook.zip\n",
        "!unzip chinook.zip\n",
        "!rm -r chinook.zip"
      ],
      "metadata": {
        "id": "DzZpnw3Zp7F8"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "chinook.db' should now be located in the same directory where the original zip file was downloaded."
      ],
      "metadata": {
        "id": "wmbZGGZp754v"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 3. Configuration"
      ],
      "metadata": {
        "id": "S9YY589K8KTh"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "We have previously installed Versatile Data Kit and the plugins required for the example. Now the path to the database we just downloaded must be declared as an environment variable.\n"
      ],
      "metadata": {
        "id": "isMdKlfv8RjJ"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%env VDK_SQLITE_FILE=chinook.db"
      ],
      "metadata": {
        "id": "bVt_UlegyLQE"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "To load the extension in Collab notebook run the following command"
      ],
      "metadata": {
        "id": "e3Ty8lAITGeZ"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%reload_ext vdk.plugin.ipython"
      ],
      "metadata": {
        "id": "rrtSp0uLTWQ6"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "And load the VDK (Job controll object)"
      ],
      "metadata": {
        "id": "Pq_1zkMETamc"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%reload_VDK"
      ],
      "metadata": {
        "id": "qpUSh2kLTYV7"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 4. Data Job"
      ],
      "metadata": {
        "id": "wDPpw8xe8wBP"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "The structure of our Data Job in following cells is as follows:<br><br>\n",
        "**sqlite-example-job**<br>\n",
        "├── 1-Drop Table<br>\n",
        "├── 2-Create Table<br>\n",
        "├── 3-Do the processing<br><br>\n",
        "\n",
        "The purpose of our Data Job ***sqlite-example-job*** is to extract the EmployeeId and names of employees who work with customers, and the number of customers they work with, and insert them into a newly-created table called ***customer_count_per_employee***.<br><br>\n",
        "\n",
        "Our Data Job consists of three SQL steps. Using ***%%vdksql*** cell magic command we will be running each query in our notebook.<br><br>\n",
        "\n",
        "**Each SQL step is a separate query:**\n",
        "\n",
        "- The first step deletes the new table if it exists. This query only serves to make the Data Job repeatable;\n",
        "- The second step creates the table we will be inserting data;\n",
        "- The third step performs the described processing and inserts the new data into the customer_count_per_employee table.\n",
        "\n",
        "<br>\n",
        "Run each of the following cells in order to observe the job in action.\n"
      ],
      "metadata": {
        "id": "CFrCNMbL80lX"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Step 1: Drop Table"
      ],
      "metadata": {
        "id": "k6DvRJegVFBB"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%vdksql\n",
        "DROP TABLE IF EXISTS customer_count_per_employee;"
      ],
      "metadata": {
        "id": "ox6Y6rYHUna2"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Step 2: Create Table"
      ],
      "metadata": {
        "id": "4R4Hlkn9VWv7"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%vdksql\n",
        "CREATE TABLE customer_count_per_employee (EmployeeId, EmployeeFirstName, EmployeeLastName, CustomerCount);"
      ],
      "metadata": {
        "id": "IFGyO8VQU3Rp"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Step 3: Do the processing"
      ],
      "metadata": {
        "id": "Eit_CLguVd71"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%vdksql\n",
        "INSERT INTO customer_count_per_employee\n",
        "SELECT SupportRepId, employees.FirstName, employees.LastName, COUNT(CustomerId)\n",
        "FROM (customers INNER JOIN employees ON customers.SupportRepId = employees.EmployeeId)\n",
        "GROUP BY SupportRepId;"
      ],
      "metadata": {
        "id": "jWVMp4GtU7xS"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## 5. Results"
      ],
      "metadata": {
        "id": "MGbX1PnhAWMb"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "After running the Data Job, we can check whether the new table was populated correctly by using the **sqlite-query** command afforded to us by the **vdk-sqlite** plugin, which we can use to execute queries against the configured SQLite database without having to set up a Data Job:"
      ],
      "metadata": {
        "id": "S7RiHPv7AcvJ"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%vdksql\n",
        "SELECT * FROM customer_count_per_employee"
      ],
      "metadata": {
        "id": "HXIoXqknz9Lh"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}