QuestNotebook.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![logo](img/QuestDB_Logo_GitHub.png)\n",
    "# QuestDB Tutorial for Python"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Getting Started\n",
    "\n",
    "To get started, you'll need a few things installed and set up. This should be quick. \n",
    "\n",
    "1. QuestDB: To install Questdb you can see [Installation](https://questdb.io/getstarted) for complete instructions in case you want to use Docker, or `brew` on MacOS, but the easiest way is to download the binaries and run it directly. Instructions for that are [Here](https://questdb.io/docs/guideBinaries).\n",
    "2. Jupyter Notebooks: That's what this is. To run it, you should:\n",
    "    1. make **sure** you are running Python 3.x and _not_ Python 2.7. If you're in doubt, `python --version` will tell you.\n",
    "    2. install Jupyter Notebooks with `pip3 install --upgrade ipython jupyter`\n",
    "    3. make sure that the libraries we use in this tutorial are also installed with `pip3 install requests urlib matplotlib pandas`\n",
    "    4. clone this repository (`git clone https://github.com/davidgs/QuestNotebook`)\n",
    "    5. in the repository directory run `jupyter notebook`\n",
    "   \n",
    "That will get you right back to a page like this that is interactive, allowing you to run the code and interact with the database yourself.\n",
    "\n",
    "If you get errors like `ModuleNotFoundError: No module named 'requests'` for any of the libraries you installed above, double-check to make sure that you are actually using Python 3.x `jupytper --path` will let you know if Jupyter is using 2.7 or 3.x"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create A Database\n",
    "\n",
    "We will need someplace to store our data, so let's create a test database where we can put some random data.\n",
    "\n",
    "We will create a simple table with 5 columns, one of which is a `timestamp` \n",
    "\n",
    "The Create operation in QuestDB appends records to bottom of a table. If the table has a designated `timestamp`, new record timestamps must be superior or equal to the latest timestamp. Attempts to add a timestamp in middle of a table will result in a timestamp out of order error.\n",
    "\n",
    "* `cust_id` is the customer identifier. It uniquely identifies customer.\n",
    "* `balance_ccy` balance currency. We use `SYMBOL` here to avoid storing text against each record to save space and increase database performance.\n",
    "* `balance` is the current balance for customer and currency tuple.\n",
    "* `inactive` is used to flag deleted records.\n",
    "* `timestamp` timestamp in microseconds of the record. Note that if you receive the timestamp data as a string, it could also be inserted using `to_timestamp`.\n",
    "\n",
    "This should return a `200` status the first time you run it. If you run it more than once, subsequent runs will return `400` because the database already exists."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-06-12T11:15:01.331Z",
     "iopub.status.busy": "2020-06-12T11:15:01.314Z",
     "iopub.status.idle": "2020-06-12T11:15:01.527Z",
     "shell.execute_reply": "2020-06-12T11:15:01.542Z"
    }
   },
   "outputs": [],
   "source": [
    "import requests\n",
    "import urllib.parse as par\n",
    "\n",
    "q = 'create table balances'\\\n",
    "    '(cust_id int,'\\\n",
    "    ' balance_ccy char,'\\\n",
    "    'balance double,'\\\n",
    "    'inactive boolean,'\\\n",
    "    'timestamp timestamp)'\\\n",
    "    'timestamp(timestamp)'\n",
    "r = requests.get(\"http://localhost:9000/exec?query=\" + q)\n",
    "print(r.status_code)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Generate some Data\n",
    "\n",
    "Since we have a new setup, we should add some data to QuestDB so that we can have something to query. We will add some random data, for now. \n",
    "\n",
    "You can re-run this section as many times as you want to add 100 entries at a time, or simply change the `range(100)` to add as many datapoints as you wish.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-06-12T11:15:05.254Z",
     "iopub.status.busy": "2020-06-12T11:15:05.244Z",
     "iopub.status.idle": "2020-06-12T11:15:05.991Z",
     "shell.execute_reply": "2020-06-12T11:15:06.004Z"
    }
   },
   "outputs": [],
   "source": [
    "import requests\n",
    "import random\n",
    "from datetime import datetime\n",
    "success = 0\n",
    "fail = 0\n",
    "currency = [\"$\", \"€\", \"£\", \"¥\"]\n",
    "random.seed()\n",
    "for x in range(1000):\n",
    "    cust = random.randint(20, 42)\n",
    "    cur = random.choice(currency)\n",
    "    bal = round(random.uniform(10.45, 235.15), 2)\n",
    "    act = bool(random.getrandbits(1))\n",
    "    query = \"insert into balances values(\"\\\n",
    "        + str(cust) + \",'\"\\\n",
    "        + cur + \"',\" \\\n",
    "        + str(bal) + \",\" \\\n",
    "        + str(act) + \",systimestamp())\"\n",
    "    r = requests.get(\"http://localhost:9000/exec?query=\" + query)\n",
    "    if r.status_code == 200:\n",
    "        success += 1\n",
    "    else:\n",
    "        fail += 1\n",
    "    \n",
    "print(\"Rows inserted: \" + str(success))\n",
    "if fail > 0:\n",
    "    print(\"Rows Failed: \" + str(fail))\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Query Data from QuestDB\n",
    "\n",
    "Now that we have data available, let's try querying some of it to see what we get back!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-06-12T11:15:09.812Z",
     "iopub.status.busy": "2020-06-12T11:15:09.800Z",
     "iopub.status.idle": "2020-06-12T11:15:09.830Z",
     "shell.execute_reply": "2020-06-12T11:15:09.894Z"
    }
   },
   "outputs": [],
   "source": [
    "import requests\n",
    "import io\n",
    "\n",
    "r = requests.get(\"http://localhost:9000/exp?query=select * from  balances\")\n",
    "rawData = r.text\n",
    "print(rawData)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Read the content into Pandas Dataframe\n",
    "\n",
    "So you'll notice that the returned data is just a massive `csv` string. If you'd rather have `json` data, then you would change the endpoint to `http://localhost:9000/exec ...` But since we're going to use Pandas to frame our data, we'll stick with `csv`.\n",
    "\n",
    "We are also telling pandas to parse the `timestamp` field as a date. This is important since we're dealing with Time Series data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-06-12T11:15:18.092Z",
     "iopub.status.busy": "2020-06-12T11:15:18.087Z",
     "iopub.status.idle": "2020-06-12T11:15:21.215Z",
     "shell.execute_reply": "2020-06-12T11:15:21.225Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "pData = pd.read_csv(io.StringIO(rawData), parse_dates=['timestamp'])\n",
    "print(pData)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "## Narrow the search\n",
    "\n",
    "That's just getting us *all* the data, but let's narrow the search using some SQL clauses. Let's look for a specific `cust_id` and only balances of that customer that are in $s. We are also only interested in times the customer was `active` Since this is SQL, you can make this query as simple, or as complex, as you'd like.\n",
    "\n",
    "Since all of the data was generated randomly, this exact query may return no results, so you may have to adjust the `cust_id` below until you get results back. \n",
    "\n",
    "***Note:** The query string _must_ be URL-encoded before it is sent."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-06-12T11:15:27.567Z",
     "iopub.status.busy": "2020-06-12T11:15:27.557Z",
     "iopub.status.idle": "2020-06-12T11:15:27.578Z",
     "shell.execute_reply": "2020-06-12T11:15:27.588Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import urllib.parse\n",
    "\n",
    "q = \"select cust_id,\"\\\n",
    "    \" balance,\"\\\n",
    "    \" balance_ccy,\"\\\n",
    "    \" inactive,\"\\\n",
    "    \" timestamp\"\\\n",
    "    \" from balances\"\\\n",
    "    \" where cust_id = 26\"\\\n",
    "    \" and balance_ccy = '$'\"\\\n",
    "    \" and not inactive\"\n",
    "query = urllib.parse.quote(q)\n",
    "r = requests.get(\"http://localhost:9000/exp?query=\" + query)\n",
    "queryData = r.content\n",
    "rawData = pd.read_csv(io.StringIO(queryData.decode('utf-8')), parse_dates=['timestamp'])\n",
    "print(rawData)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Plot the data\n",
    "\n",
    "We will use `matplotlib` to plot the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-06-12T11:15:32.233Z",
     "iopub.status.busy": "2020-06-12T11:15:32.225Z",
     "iopub.status.idle": "2020-06-12T11:15:32.735Z",
     "shell.execute_reply": "2020-06-12T11:15:32.741Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "from matplotlib import pyplot as plt\n",
    "\n",
    "rawData.plot(\"timestamp\", [\"balance\"], subplots=True)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Clean up\n",
    "\n",
    "Now we will clean everything up for the next time. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "r = requests.get(\"http://localhost:9000/exec?query=drop table balances\")\n",
    "if r.status_code == 200:\n",
    "    print(\"Database Table dropped\")\n",
    "else:\n",
    "        print(\"Database Table not Dropped: \" + str(r.status_code))"
   ]
  }
 ],
 "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.7.6"
  },
  "nteract": {
   "version": "0.23.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}