01 - Getting Started with Ibis.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# Getting started with Ibis\n",
        "\n",
        "In the [previous notebook](./00%20-%20Welcome.ipynb), we created a DuckDB database file with the [nycflights13 data](https://github.com/hadley/nycflights13). DuckDB is fast and runs locally, so it's handy for lots of use cases, including tutorials. Let's begin by importing Ibis and connecting to the database."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "import ibis\n",
        "\n",
        "con = ibis.duckdb.connect(\"nycflights13.ddb\", read_only=True)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "**Note**: When you connect to a DuckDB database file, DuckDB creates a WAL file to prevent data corruption. If you see a `nycflights13.ddb.wal` file, you can safely ignore it. It will get cleaned up automatically.\n",
        "\n",
        "Now we have a connection, we can start by looking around. Are there any tables in this database?"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "con.list_tables()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Two, in fact! Let's take a look at the `flights` table first."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights = con.table(\"flights\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "By default, you'll get a printable representation of the table schema, showing the name and data type of each column."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "If we call the `head` method to peek at the data, you'll notice that we don't actually see data (yet); what's going on?"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.head()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Ibis has a deferred execution model. It builds up expressions based on what you ask it to do, and then executes those expressions on request.\n",
        "\n",
        "In this case, our query isn't too involved; we want to see the first few rows of the `flights` table. We can do that by asking for the results of this query as a `pandas.DataFrame`:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.head().to_pandas()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Or a `pyarrow.Table`:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.head().to_pyarrow()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Or a `polars.DataFrame`:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.head().to_polars()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "We'll get into more detail about what Ibis is doing a bit later on. For now, the important point is that Ibis is deferred.\n",
        "\n",
        "## Interactive mode\n",
        "\n",
        "Remember when we said Ibis is deferred? Sometimes you want eager execution so you can explore a dataset. For the rest of this notebook, we'll turn on interactive mode, where Ibis will eagerly execute as much of the query as it needs to in order to show you the first 10 rows of the result."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "ibis.options.interactive = True"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "In interactive mode, we use `rich` to render the output inline:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.head()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Tables and columns\n",
        "\n",
        "`flights` is a table!  A table is a collection of one or more columns, each with a specific datatype."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "type(flights)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "We can look at a single column of that table using the column name as an attribute:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.carrier"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "What kind of column is `carrier`? It's a `StringColumn`!"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "type(flights.carrier)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Ibis \"verbs\", or, stuff you can do to a table\n",
        "\n",
        "The rest of this notebook covers some of the general methods you can use to alter the output of a particular table.\n",
        "\n",
        "We'll cover, in order, `filter`, `select`, `drop`, `mutate`, `order_by`, `aggregate`, and `group_by`. Time to dive in!\n",
        "\n",
        "## Filter\n",
        "\n",
        "A filter allows you to view a subset of the rows in a table, based on some condition.\n",
        "\n",
        "For instance, we might want to only view data for JetBlue flights:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.filter(flights.carrier == \"B6\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "You can also combine multiple filters, across multiple columns.\n",
        "\n",
        "We can subset the data down to JetBlue flights from JFK:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "scrolled": true
      },
      "outputs": [],
      "source": [
        "expr = flights.filter((flights.carrier == \"B6\") & (flights.origin == \"JFK\"))\n",
        "expr"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Above, we combined two filters using `&`. You can also pass them in as individual arguments:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "expr = flights.filter(\n",
        "    flights.carrier == \"B6\",\n",
        "    flights.origin == \"JFK\",\n",
        ")\n",
        "expr"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "ibis.to_sql(expr)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Select\n",
        "\n",
        "Filter filters, Select selects (there's a pattern here).\n",
        "If you only want a subset of the columns in the original table, you can select\n",
        "those columns explicitly.\n",
        "\n",
        "You can refer to the columns using strings:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.select(\"carrier\", \"origin\", \"dest\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Or you can use explicit references to the `Column` objects:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.select(flights.carrier, flights.origin, flights.dest)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Or you can mix and match:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.select(\"carrier\", \"origin\", flights.dest)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Drop\n",
        "\n",
        "Drop is nearly the same as Select, but rather than explicitly choosing the columns to display, we explicitly choose the columns to _not_ display.\n",
        "\n",
        "And as with `select`, you can specify the columns as strings:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.drop(\"flight\", \"tailnum\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Or you can use explicit references to the `Column` objects:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.drop(flights.flight, flights.tailnum)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Or you can mix and match:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.drop(\"flight\", flights.tailnum)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Mutate\n",
        "\n",
        "Everything we've seen so far has been subtractive—removing rows or columns. What about _adding_ columns?\n",
        "\n",
        "That's what `mutate` is for! You can create a new column as a function of other existing columns (for example, converting units):"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.mutate(distance_km=flights.distance * 1.609)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Or you can create a new column and populate it with some literal value:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.mutate(my_favorite_number=ibis.literal(41))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## On immutability\n",
        "\n",
        "We've filtered, selected, dropped, and mutated this `flights` table quite a bit."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "And yet, notice that none of our changes persist—the base table for our query isn't altered. The query (or expression) is a recipe of things to do with the base table (`flights`).\n",
        "\n",
        "If you want to keep an expression around, you can assign it to a variable:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "expr = flights.select(\"carrier\", \"origin\")\n",
        "expr"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "**Note**: Every time you execute an expression (via interactive mode, or `to_pandas`, or similar), the entire expression gets executed, starting from the base table.  DuckDB is very fast and this dataset is very small, so the delay is unnoticeable, but for very large datasets, it might become more pronounced.  There is functionality to `cache` intermediate results that isn't covered in this tutorial, but you can [read more about it in the docs](https://ibis-project.org/reference/expression-tables.html#ibis.expr.types.relations.Table.cache).\n",
        "\n",
        "## Method chaining\n",
        "\n",
        "You can build up complicated queries by chaining together Ibis methods. The output of many Ibis methods is a table (just like `flights`!) and we can continue calling table methods until we're satisfied. Or until we end up with something that _isn't_ a table. More on that later."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.select(\"carrier\", \"origin\", \"dest\").drop(\"carrier\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Not the most complicated (or useful) query, but we'll see more soon.\n",
        "\n",
        "### Exercise 1\n",
        "\n",
        "Convert the `distance` column from miles to kilometers. For an approximate result, multiply by 1.609.\n",
        "\n",
        "Two ways you might accomplish this:\n",
        "\n",
        "- Chaining `.mutate` to create the new column and `.drop` to drop the original imperial column\n",
        "- Using a single `.select` to create the new column as well as select the remaining columns\n",
        "\n",
        "Try both ways below! How do they compare?"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "# Convert the imperial units to metric, and drop the imperial columns.\n",
        "# Try this using a `.mutate` and `.drop` call.\n",
        "flights_metric_mutate_drop = flights"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "# Convert the imperial units to metric, and drop the imperial columns.\n",
        "# Try this using a single `.select` call.\n",
        "flights_metric_select = flights"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "#### Solutions"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "%load solutions/nb01_ex01_mutate_drop.py"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "%load solutions/nb01_ex01_select.py"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "#### Does it matter which method you choose?\n",
        "\n",
        "In this case, no. Sometimes, there might be a small difference in the generated SQL, but they will be semantically equivalent."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "ibis.to_sql(flights_metric_mutate_drop)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "ibis.to_sql(flights_metric_select)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "In practice, small differences in the generated SQL don't make a difference. Any modern SQL execution engine will optimize variations to the same set of operations, and there will be no measurable performance difference.\n",
        "\n",
        "## Order by\n",
        "\n",
        "Want to order your data by a given column or columns?  Use `order_by`!\n",
        "\n",
        "The default ordering direction is ascending:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.order_by(flights.distance)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "We can ask Ibis to sort in descending order, too."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "scrolled": true
      },
      "outputs": [],
      "source": [
        "flights.order_by(flights.distance.desc())"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's select out a subset of the columns to keep this a bit tidier."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.order_by(flights.distance.desc()).select(\n",
        "    \"carrier\", \"origin\", \"dest\", \"distance\"\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "You can also call `ibis.desc` on the column name to set the order direction:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.order_by(ibis.desc(\"distance\")).select(\"carrier\", \"origin\", \"dest\", \"distance\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Aggregate\n",
        "\n",
        "Ibis has several aggregate functions available to help summarize data.  All the old favorites are there: `mean`, `max`, `min`, `count`, `sum`...\n",
        "\n",
        "You can aggregate a column by calling the method on that column:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.distance.mean()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Or you can compute multiple aggregates using the `aggregate` method (also\n",
        "available as `agg` for faster typing):"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.agg([flights.distance.mean(), flights.air_time.min()])"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "If you don't like the column names Ibis generates for you, choose your own!"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.agg(\n",
        "    average_distance=flights.distance.mean(),\n",
        "    shortest_air_time=flights.air_time.min(),\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "But aggregates really shine when paired with a `group_by`!\n",
        "\n",
        "## Group by\n",
        "\n",
        "`group_by` creates groupings of rows that have the same value for one or more columns.\n",
        "\n",
        "But it doesn't do much on its own—you can pair it with `agg` to get a result."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.group_by(\"carrier\").agg()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Without any aggregate function specified, we get the distinct values of the grouped column.\n",
        "\n",
        "We can add a second column to the `group_by` to get the distinct pairs across both columns:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.group_by([\"carrier\", \"origin\"]).agg()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Now, if we add an aggregation function to that, we start to really open things up."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.group_by([\"carrier\", \"origin\"]).agg(flights.distance.mean())"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "By adding that `mean` to the `aggregate`, we now have a concise way to calculate aggregates over each of the distinct groups in the `group_by`. And we can calculate as many aggregates as we need."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.group_by([\"carrier\", \"origin\"]).agg(\n",
        "    [flights.distance.mean(), flights.air_time.min()]\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "If we need more specific groups, we can add to the `group_by`."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.group_by([\"carrier\", \"origin\", \"dest\"]).agg(\n",
        "    [flights.distance.mean(), flights.air_time.min()]\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Cast\n",
        "\n",
        "Sometimes when you parse data, _especially_ from CSVs, the types get a bit messed up. Or you might be loading in a `parquet` file where everything is defined as a `string`. We can clean that up pretty quickly.\n",
        "\n",
        "You can cast from floats to ints:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "(flights.distance * 1.609).cast(\"int32\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "And from ints to floats:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.year.cast(\"float64\")  # this is a terrible idea"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "You can cast numeric columns to strings:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.year.cast(\"str\")  # or \"string\""
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "And numeric strings to numbers:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.year.cast(\"str\").cast(\"int64\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "But Ibis will yell if you try to cast a non-numeric string to a number:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.carrier.cast(\"int32\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "If we know that a column _should_ have a particular data type, but don't want a few bad apples (rows) to spoil the bunch, `try_cast` will fall back to `NULL` or `NaN` for values where the cast fails:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.arr_delay.try_cast(int)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Drop NA\n",
        "\n",
        "Does what it says on the box—drop the `NULL`s from a dataset."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.dropna()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Exercises\n",
        "\n",
        "Time to use what we've learned to answer some flight questions.\n",
        "\n",
        "### Exercise 2\n",
        "\n",
        "Which airlines (`carrier`) had the longest average arrival delays (`arr_delay`) in June 2013?\n",
        "\n",
        "#### Solution\n",
        "\n",
        "Note that there are several ways these queries could be written—it's fine if your solution doesn't look like ours, as long as the results are the same."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "%load solutions/nb01_ex02.py"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "### Exercise 3\n",
        "\n",
        "Which NYC airport has the lowest percentage of outbound flights arriving 30 or more minutes late?\n",
        "\n",
        "#### Solution"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "%load solutions/nb01_ex03.py"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## A brief digression on the SQL Ibis generates\n",
        "\n",
        "Maybe you've heard that SQL has a standard?  This is true, and also misleading. The SQL standard is more of a suggestion, and there are myriad SQL _dialects_.\n",
        "\n",
        "Ibis compiles expressions into the appropriate SQL dialect for the backend you are using. In this case, we started with a DuckDB table, so we get DuckDB SQL:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "ibis.to_sql(flights_metric_mutate_drop)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "But if you want to use a _different_ dialect, you can pass the dialect name:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "ibis.to_sql(flights_metric_mutate_drop, dialect=\"postgres\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Join\n",
        "\n",
        "No dataframe library is complete without joins! Ibis supports several kinds of joins between table expressions: `inner_join`, `cross_join`, `left_join`, `outer_join`, `semi_join`, and `anti_join`. The `join` table method is, by default, the same as `inner_join`.\n",
        "\n",
        "Remember the other table in our database?"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "weather = con.table(\"weather\")\n",
        "weather"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "We can join the two tables on the `origin` column:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.join(weather, \"origin\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Of course, we should only join on the weather at the time corresponding to each flight:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "flights.join(weather, [\"origin\", \"time_hour\"])"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "The `on` condition can also be specified as an expression, which is particularly useful if you have columns with different names or non-equi-join logic."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "renamed = weather.rename(location=\"origin\")\n",
        "flights.join(\n",
        "    renamed,\n",
        "    (flights.origin != renamed.location) & (flights.time_hour == renamed.time_hour),\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "The Ibis `join` syntax is quite expressive, so we won't cover all the variations now; for more examples, read the [docs](https://ibis-project.org/reference/expression-tables#ibis.expr.types.relations.Table.join).\n",
        "\n",
        "Before we move on, let's terminate the DuckDB connection for good measure. If you skip this step, you may run into an error later on in the tutorial:\n",
        "\n",
        "    IO Error: Could not set lock on file \"/workspaces/kedro-ibis-tutorial/nycflights13.ddb\": Conflicting lock is held in /usr/local/bin/python3.11 (PID 1234). However, you would be able to open this database in read-only mode, e.g. by using the -readonly parameter in the CLI. See also https://duckdb.org/docs/connect/concurrency"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "con.disconnect()"
      ]
    }
  ],
  "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.11.9"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 4
}