03 - Switching Backends.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# Switching Backends\n",
        "\n",
        "One use case for Ibis's portable API is the ability to develop a query locally\n",
        "(using `duckdb`, for example) on a subset of the data, then rerun that same\n",
        "query on the full dataset (using `bigquery`, for example) without rewriting\n",
        "your code.\n",
        "\n",
        "In this notebook we'll develop some queries locally on a subset of a real world\n",
        "dataset, then rerun those same queries on the full remote dataset.\n",
        "\n",
        "## IMDB Dataset\n",
        "\n",
        "For this section, we'll make use of the IMDB dataset, which provides a\n",
        "[snapshot](https://datasets.imdbws.com/) of the films and ratings on IMDB. This\n",
        "dataset was downloaded at the start of the tutorial, and is available as a set of\n",
        "parquet files in the `data/imdb/` directory:\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "!ls data/imdb/"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Here we'll load both the `imdb_title_ratings` and `imdb_title_basics_sample_5`\n",
        "files to work with a sample of the data. Once we have a query we're happy with\n",
        "we'll run the same query on the full dataset.\n",
        "\n",
        "### Parquet loading\n",
        "\n",
        "Here we use the `read_parquet` method discussed in the previous notebook to\n",
        "load some data into DuckDB. Note that DuckDB treats this as a \"view\", meaning\n",
        "that the data isn't copied or loaded into memory - it still only exists on disk\n",
        "in the `parquet` files, meaning you can happily operate on parquet datasets\n",
        "that are much larger than the RAM on your laptop."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "import ibis\n",
        "from ibis import _\n",
        "\n",
        "ibis.options.interactive = True"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "con = ibis.duckdb.connect()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "basics = con.read_parquet(\n",
        "    \"data/imdb/imdb_title_basics_sample_5.parquet\", table_name=\"imdb_title_basics\"\n",
        ")\n",
        "\n",
        "basics"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "ratings = con.read_parquet(\n",
        "    \"data/imdb/imdb_title_ratings.parquet\", table_name=\"imdb_title_ratings\"\n",
        ")\n",
        "\n",
        "ratings"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Exercises\n",
        "\n",
        "### Exercise 1\n",
        "\n",
        "Join `basics` with `ratings` on the `tconst` column.\n",
        "\n",
        "\n",
        "#### Solution"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%load solutions/nb03_ex01.py"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "### Exercise 2\n",
        "\n",
        "Join `basics` with `ratings` on `tconst`, and select out only the `titleType`,\n",
        "`primaryTitle`, `numVotes`, `averageRating`, and `isAdult`  columns.\n",
        "\n",
        "\n",
        "#### Solution"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%load solutions/nb03_ex02.py"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "### Exercise 3\n",
        "\n",
        "Those `camelCase` column names aren't [PEP\n",
        "8](https://peps.python.org/pep-0008/) compliant, and feel a bit clunky to use.\n",
        "Modify the above to change them to `snake_case` (for example, rename\n",
        "`titleType` to `title_type`).\n",
        "\n",
        "There are two ways you might achieve this:\n",
        "\n",
        "- Using the `Table.rename` method\n",
        "- Or by modifying the `.select` used above to do the relabeling in one step.\n",
        "\n",
        "\n",
        "#### Solution"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%load solutions/nb03_ex03_rename.py"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%load solutions/nb03_ex03_select.py"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "### Exercise 4\n",
        "\n",
        "Using the above joined table, compute the 10 non-adult movies with the highest\n",
        "average rating having received at least 100,000 votes.\n",
        "\n",
        "\n",
        "#### Solution"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%load solutions/nb03_ex04.py"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Expression portability\n",
        "\n",
        "Let's turn off interactive mode for a moment."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "ibis.options.interactive = False"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "We've built up an Ibis expression that returns the top 10 highest rated films on\n",
        "IMDB for certain criteria.  The data we used is only a 5% sample, but the query\n",
        "should work no matter what size the data are.\n",
        "\n",
        "Now we're going to call a special `unbind` method."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "expr = sol4.unbind()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "What does `unbind` do?  It takes an Ibis expression that was created using one\n",
        "backend (in our case, `DuckDB`) and generalizes it into an \"unbound\" expression.\n",
        "\n",
        "Let's look at the `repr` of the unbound expression (this will error if you have\n",
        "interactive mode on, that's why we turned it off)."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "expr"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "The only visible difference is that we see \"UnboundTable\" a bunch of times, and\n",
        "that's the point.  The _expression_ is independent of the backend that created\n",
        "it.\n",
        "\n",
        "So, if we had a _different_ backend connection, that had the full IMDB ratings\n",
        "data loaded into it, could we run our locally developed query against the\n",
        "\"production\" data?\n",
        "\n",
        "(With caveats that the table names and schema need to match).\n",
        "\n",
        "Let's find out!"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "pg_con = ibis.postgres.connect()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "pg_con.list_tables()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "That seems promising.\n",
        "\n",
        "Now, we can't call `expr.to_pandas()` because the expression isn't _bound_ to a\n",
        "backend.  Ibis doesn't know (anymore) where to execute it. Instead, we can use\n",
        "the `to_pandas` (or similar) method on the backend connection object itself:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "pg_con.to_pandas(expr, limit=10)"
      ],
      "execution_count": null,
      "outputs": []
    }
  ],
  "metadata": {
    "kernelspec": {
      "name": "python3",
      "language": "python",
      "display_name": "Python 3 (ipykernel)"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 4
}