04 - Playing with PyPI.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# Playing with PyPI (data)\n",
        "\n",
        "Many thanks to Seth M. Larson for making these available: https://github.com/sethmlarson/pypi-data"
      ]
    },
    {
      "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": "markdown",
      "metadata": {},
      "source": [
        "We earlier downloaded some PyPI maintainer data as parquet files, now let's load\n",
        "them into DuckDB:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "from pathlib import Path\n",
        "\n",
        "filenames = [\n",
        "    \"deps.parquet\",\n",
        "    \"maintainers.parquet\",\n",
        "    \"package_urls.parquet\",\n",
        "    \"packages.parquet\",\n",
        "    \"scorecard_checks.parquet\",\n",
        "    \"wheels.parquet\",\n",
        "]\n",
        "\n",
        "folder = Path(\"data/pypi\")\n",
        "\n",
        "for filename in filenames:\n",
        "    path = folder / filename\n",
        "    con.read_parquet(path, table_name=filename.split(\".\")[0])"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "There should be several tables available -- we'll be focusing on using the\n",
        "`deps`, `maintainers`, and `packages` tables:\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "con.list_tables()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "deps = con.tables.deps\n",
        "\n",
        "deps"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "maintainers = con.tables.maintainers\n",
        "\n",
        "maintainers"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "packages = con.tables.packages\n",
        "\n",
        "packages"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Looking for typo squatters\n",
        "\n",
        "We've all done it, you typo in your `pip install` line and you end up with...\n",
        "something else. Most of the time, these are honest mistakes.  There are a LOT of\n",
        "packages.\n",
        "\n",
        "Sometimes, it is... less honest.\n",
        "\n",
        "There is a `levenshtein` method for calculating string similarity against a string column.\n",
        "Let's try that out and see if there are any packages similarly named to, say, `pandas`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "packages.select(\"name\", sim=packages.name.levenshtein(\"pandas\")).order_by(_.sim)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Those seem like they are probably legitimate projects -- maybe we can try out another text similarity search?\n",
        "\n",
        "There's a `jaccard` search function in DuckDB, but there's no `jaccard` method on Ibis' string columns...\n",
        "\n",
        "What's an enterprising Ibis user to do?\n",
        "\n",
        "## UDF builtins\n",
        "\n",
        "Modern SQL engines have a _lot_ of features and while Ibis provides a consistent\n",
        "interface, it can't always expose every option of _every_ backend.\n",
        "\n",
        "One easy way to enable extra backend functionality is with a udf builtin!\n",
        "\n",
        "If a function exists on a particular backend (this is backend-specific by\n",
        "definition), you can create a dummy function that maps the name, the inputs, and\n",
        "the output type for Ibis to use.\n",
        "\n",
        "The Jaccard function on DuckDB takes in two strings and returns the similarity\n",
        "measure as a number between 0 and 1. To enable this in Ibis, we need to make use\n",
        "of a handy decorator, make sure the function name matches, and add a return type\n",
        "hint (this is required)."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "@ibis.udf.scalar.builtin\n",
        "def jaccard(left: str, right: str) -> float:\n",
        "    ..."
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Now that that's defined, we can use our newly exposed `jaccard` function -- not\n",
        "as a method on the `name` column, but by passing the column as one of the\n",
        "arguments:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "packages.select(\"name\", sim=jaccard(packages.name, \"pandas\")).order_by(_.sim.desc())"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "### Exercise 1\n",
        "\n",
        "Try swapping out `jaccard` for a different similarity function available in\n",
        "DuckDB (https://duckdb.org/docs/sql/functions/char#text-similarity-functions).\n",
        "\n",
        "### Solution\n",
        "\n",
        "One possible solution:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%load solutions/nb04_ex01_jaro.py"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Exploring the PyPI maintainers dataset further\n",
        "\n",
        "### Exercise 2: Which maintainers have the most downloads?\n",
        "\n",
        "Which package maintainers on PyPI have the most downloads across all the\n",
        "packages they maintain?\n",
        "\n",
        "\n",
        "### Solution"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "scrolled": true
      },
      "source": [
        "%load solutions/nb04_ex02.py"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "### Exercise 3: What packages depend on `ibis-framework`?\n",
        "\n",
        "Or fill in your package name of choice\n",
        "\n",
        "(You can use the `distinct` method to return a table with duplicate entries removed.)\n",
        "\n",
        "\n",
        "### Solution"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%load solutions/nb04_ex03.py"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Using `.sql` as an escape hatch\n",
        "\n",
        "Ibis is pretty good about abstracting away the underlying execution engine, but\n",
        "sometimes you _want_ to talk directly to the engine.\n",
        "\n",
        "Maybe you:\n",
        "- have an existing SQL query from a data engineer that you want to build on\n",
        "- need to access a backend-specific function that isn't exposed in Ibis\n",
        "- really want to write some SQL\n",
        "\n",
        "If you do, then you can call `.sql` on a connection (or an existing expression!)\n",
        "and write out your SQL query.\n",
        "\n",
        "Let's try out a few straightforward examples:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "con.sql(\"SELECT 1 + 1\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "This isn't a necessarily a _good_ way to do math, but it's available!"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "con.sql(\"SELECT 1 + 1 as f\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Now that the column of our simple addition has a name, we can chain on the\n",
        "expression, the same as any other Ibis expression."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "con.sql(\"SELECT 1 + 1 as f\").mutate(f=_.f + 2)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "You can (should you so desire) continue to hop back-and-forth from Ibis land to SQL land:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "con.sql(\"SELECT 1 + 1 as f\").mutate(f=_.f + 2).alias(\"t\").sql(\"SELECT f, f^2 AS fsquared FROM t\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "That... doesn't look right. What happened?\n",
        "\n",
        "Postgres uses the caret symbol (`^`) for `pow`, but DuckDB doesn't. But we don't\n",
        "have to rewrite it, just let Ibis know that it's written in a different dialect:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "con.sql(\"SELECT 1 + 1 as f\").mutate(f=_.f + 2).alias(\"t\").sql(\"SELECT f, f^2 AS fsquared FROM t\", dialect=\"postgres\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "### Exercise 4: Find the top 20 most transitively depended on packages\n",
        "\n",
        "#### Are there limits on what what you can pass to `.sql`?\n",
        "\n",
        "There are, but they are generous limits. Call to `.sql` are also deferred so we\n",
        "need to know the `schema` of the result. This allows you to continue chaining\n",
        "methods after a `.sql` call without executing the full query.\n",
        "\n",
        "Because of this, the results of `.sql` need to be a table.\n",
        "\n",
        "But you can do some pretty interesting things with this -- here's a _recursive\n",
        "common table expression_ that looks up transitive dependencies."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "expr = con.sql(\"\"\"WITH RECURSIVE\n",
        "direct_deps(package, dependency) AS (\n",
        "  SELECT\n",
        "    package_name,\n",
        "    dep_name\n",
        "  FROM deps\n",
        "  WHERE\n",
        "    extra IS NULL\n",
        "),\n",
        "transitive_deps(package, intermediate, dependency) AS (\n",
        "  SELECT\n",
        "    package,\n",
        "    package,\n",
        "    dependency\n",
        "  FROM direct_deps\n",
        "  UNION\n",
        "  SELECT\n",
        "    transitive_deps.package,\n",
        "    direct_deps.package,\n",
        "    direct_deps.dependency\n",
        "  FROM direct_deps\n",
        "  JOIN transitive_deps\n",
        "    ON direct_deps.package = transitive_deps.dependency\n",
        ")\n",
        "SELECT\n",
        "*\n",
        "FROM transitive_deps\n",
        "\"\"\")\n",
        "expr"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Now, given this recursive CTE, can we determine the top 20 most transitively\n",
        "depended on packages?\n",
        "\n",
        "### Solution"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%load solutions/nb04_ex04.py"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Additional exercises\n",
        "\n",
        "### Histogram of maintainer count\n",
        "\n",
        "Start by computing the number of packages that have `n` maintainers, as\n",
        "defined by having write permissions on PyPI."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "maintainer_counts = (\n",
        "    maintainers.group_by(\"package_name\")\n",
        "    .agg(maintainers=_.count())\n",
        "    .group_by(\"maintainers\")\n",
        "    .agg(count=_.count())\n",
        "    .order_by(_.maintainers)\n",
        ")\n",
        "\n",
        "maintainer_counts"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "We'll plot a histogram of the maintainer counts using `altair`."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "import altair as alt"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "chart = (\n",
        "    alt.Chart(maintainer_counts)\n",
        "    .mark_bar()\n",
        "    .encode(x=\"maintainers\", y=alt.Y(\"count\", scale=alt.Scale(type=\"log\")))\n",
        ")\n",
        "\n",
        "chart"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "### Exercise 05\n",
        "\n",
        "What's the deal with that spike at 12 maintainers?\n",
        "\n",
        "**Note**: if you would like to return more rows in interactive mode, you can set\n",
        "  the row-count e.g. `ibis.options.repr.interactive.max_rows = 20`\n",
        "\n",
        "\n",
        "### Solution"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%load solutions/nb04_ex05_counts.py"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%load solutions/nb04_ex05_ftw.py"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%load solutions/nb04_ex05_ftw_maintainers.py"
      ],
      "execution_count": null,
      "outputs": []
    }
  ],
  "metadata": {
    "kernelspec": {
      "name": "python3",
      "language": "python",
      "display_name": "Python 3 (ipykernel)"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 4
}