tutorial/03-Expressions-Lazy-Mode-Logging.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Lazy Mode and Logging\n",
    "\n",
    "So far, we have seen Ibis in interactive mode. Interactive mode (also known as eager mode) makes Ibis return the\n",
    "results of an operation immediately.\n",
    "\n",
    "In most cases, instead of using interactive mode, it makes more sense to use the default lazy mode.\n",
    "In lazy mode, Ibis won't be executing the operations automatically, but instead, will generate an\n",
    "expression to be executed at a later time.\n",
    "\n",
    "Let's see this in practice, starting with the same database as in previous tutorials."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from tutorial_utils import setup\n",
    "\n",
    "setup()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import ibis\n",
    "\n",
    "connection = ibis.sqlite.connect(\"geography.db\")\n",
    "countries = connection.table(\"countries\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In previous tutorials, we set interactive mode to `True`, and we obtained the result\n",
    "of every operation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ibis.options.interactive = True\n",
    "\n",
    "countries[\"name\", \"continent\", \"population\"].limit(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But now let's see what happens if we leave the `interactive` option to `False` (the default),\n",
    "and we operate in lazy mode."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ibis.options.interactive = False\n",
    "\n",
    "countries[\"name\", \"continent\", \"population\"].limit(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What we find is the graph of the expressions that would return the desired result instead of the result itself.\n",
    "\n",
    "Let's analyze the expressions in the graph:\n",
    "\n",
    "- We query the `countries` table (all rows and all columns)\n",
    "- We select the `name`, `continent` and `population` columns\n",
    "- We limit the results to only the first `3` rows\n",
    "\n",
    "Now consider that the data is in a database, possibly in a different host than the one executing Ibis.\n",
    "Also consider that the results returned to the user need to be moved to the memory of the host executing Ibis.\n",
    "\n",
    "When using interactive (or eager) mode, if we perform one operation at a time, we would do:\n",
    "\n",
    "- We would move all the rows and columns from the backend (database, big data system, etc.) into memory\n",
    "- Once in memory, we would discard all the columns but `name`, `continent` and `population`\n",
    "- After that, we would discard all the rows, except the first `3`\n",
    "\n",
    "This is not very efficient. If you consider that the table can have millions of rows, backed by a\n",
    "big data system like Spark or Impala, this may not even be possible (not enough memory to load all the data).\n",
    "\n",
    "The solution is to use lazy mode. In lazy mode, instead of obtaining the results after each operation,\n",
    "we build an expression (a graph) of all the operations that need to be done. After all the operations\n",
    "are recorded, the graph is sent to the backend which will perform the operation in an efficient way - only\n",
    "moving to memory the required data.\n",
    "\n",
    "You can think of this as writing a shopping list and requesting someone to go to the supermarket and buy\n",
    "everything you need once the list is complete. As opposed as getting someone to bring all the products of\n",
    "the supermarket to your home and then return everything you don't want.\n",
    "\n",
    "Let's continue with our example, save the expression in a variable `countries_expression`, and check its type."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "countries_expression = countries[\"name\", \"continent\", \"population\"].limit(3)\n",
    "type(countries_expression)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The type is an Ibis `TableExpr`, since the result is a table (in a broad way, you can consider it a dataframe).\n",
    "\n",
    "Now we have our query instructions (our expression, fetching only 3 columns and 3 rows) in the variable `countries_expression`.\n",
    "\n",
    "At this point, nothing has been requested from the database. We have defined what we want to extract, but we didn't\n",
    "request it from the database yet. We can continue building our expression if we haven't finished yet. Or once we\n",
    "are done, we can simply request it from the database using the method `.to_pandas()`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "countries_expression.to_pandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can build other types of expressions, for example, one that instead of returning a table,\n",
    "returns a columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "population_in_millions = (countries[\"population\"] / 1_000_000).name(\n",
    "    \"population_in_millions\"\n",
    ")\n",
    "population_in_millions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we check its type, we can see how it is a `FloatingColumn` expression."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "type(population_in_millions)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can combine the previous expression to be a column of a table expression."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "countries[\"name\", \"continent\", population_in_millions].limit(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since we are in lazy mode (not interactive), those expressions don't request any data from the database\n",
    "unless explicitly requested with `.to_pandas()`.\n",
    "\n",
    "## Logging queries\n",
    "\n",
    "For SQL backends (and for others when it makes sense), the query sent to the database can be logged.\n",
    "This can be done by setting the `verbose` option to `True`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ibis.options.verbose = True\n",
    "\n",
    "countries[\"name\", \"continent\", population_in_millions].limit(3).to_pandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By default, the logging is done to the terminal, but we can process the query with a custom function.\n",
    "This allows us to save executed queries to a file, save to a database, send them to a web service, etc.\n",
    "\n",
    "For example, to save queries to a file, we can write a custom function that given a query, saves it to a\n",
    "log file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pathlib import Path\n",
    "\n",
    "\n",
    "def log_query_to_file(query: str) -> None:\n",
    "    \"\"\"Log queries to `./tutorial_queries.log`.\"\"\"\n",
    "    fname = Path() / \"tutorial_queries.log\"\n",
    "    query = query.replace(\"\\n\", \" \")\n",
    "    with fname.open(mode=\"a\") as f:\n",
    "        # log on a single line\n",
    "        f.write(f\"{query}\\n\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then we can set the `verbose_log` option to the custom function, execute one query,\n",
    "wait one second, and execute another query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ibis.options.verbose_log = log_query_to_file\n",
    "\n",
    "countries.to_pandas()\n",
    "countries[\"name\", \"continent\", population_in_millions].limit(3).to_pandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This has created a log file in `$PWD/tutorial_queries.log` where the executed queries have been logged."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!cat -n $PWD/tutorial_queries.log"
   ]
  }
 ],
 "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}