[opensourcedex]

ibis is a free, open source databases project written in Python and released under Apache-2.0. It has 6,661 GitHub stars, 767 forks and 544 open issues, and was last pushed 30 hours ago. On this registry it ranks #119 of 203 tracked projects in Databases, with 5 head-to-head comparisons available.

What is ibis?

Ibis is the portable Python dataframe library: an Apache-2.0 framework that lets data engineers, analysts, and Python developers write one lazy dataframe expression and run it against more than twenty SQL and analytical backends, from a local DuckDB file to Snowflake, BigQuery, or PySpark.

What it is

Ibis is a Python library for expressing data transformations once and executing them anywhere. Expressions are lazy, meaning a pipeline is built up as an object rather than computed immediately, and fast local dataframes run through DuckDB by default. For most backends, Ibis works by compiling those dataframe expressions into SQL, which means the same code can run against PostgreSQL, MySQL, MSSQL, ClickHouse, Trino, Impala, SQLite, Snowflake, BigQuery, DataFusion, pandas, Polars, PyArrow, and PySpark. It sits in the Python data ecosystem and is distributed as the ibis-framework package.

The concrete problem it solves is that analytical code is normally written twice: once in pandas or Polars to explore a small local extract, and again as hand-written SQL to run the finished job at scale on a warehouse or database. That split forces a rewrite and a re-verification every time the data grows or the storage layer changes. Ibis replaces the rewrite with portability: iterate locally and deploy remotely by changing a single line of code, keeping one expression that compiles to whatever SQL the target engine understands.

Key capabilities

  • Lazy dataframe expressions that build a query plan before any execution occurs.
  • Interactive mode enabled with ibis.options.interactive = True, which renders results as tables during iterative data exploration.
  • One dataframe API across more than twenty backends, including BigQuery, ClickHouse, DuckDB, Impala, MSSQL, MySQL, PostgreSQL, Snowflake, SQLite, and Trino.
  • SQL compilation for inspection, exposed through ibis.to_sql(g), which prints the generated query for a given expression.
  • Mixing of SQL and Python in one pipeline, for example t.sql("SELECT species, island, count(*) AS count FROM penguins GROUP BY 1, 2").
  • Fast local dataframes via DuckDB by default, with no external server required for local work.
  • Bundled example datasets such as ibis.examples.penguins.fetch(), used in the getting started tutorial.

Who uses it and how

  • Analysts and data engineers who prototype against a local DuckDB or SQLite file and then point the same expression at a production warehouse.
  • Teams standardised on a single warehouse such as Snowflake or BigQuery that want one Python codebase rather than warehouse-specific SQL strings scattered through notebooks.
  • Organisations running several engines at once, for example Trino or Impala for the lake and PostgreSQL or MSSQL for operational data, where a common API avoids relearning each dialect.
  • pandas, Polars, and PyArrow users who have outgrown in-memory single-machine processing and need the same operations pushed down to a database.
  • PySpark users who want a dataframe API that also covers non-distributed engines without restructuring their transformation logic.

Getting started

Install from PyPI with pip install 'ibis-framework[duckdb,examples]', which pulls in Ibis, a backend, and example data, then import ibis and set ibis.options.interactive = True. The ibis-framework package is also published on conda-forge, and the installation guide at ibis-project.org covers further backend options.

How it compares

Within the tools it targets, Ibis is the expression layer rather than the execution engine: pandas, Polars, DuckDB, DataFusion, and PySpark each provide their own dataframe API and their own execution model, while Ibis provides one API above them and compiles to SQL for the SQL backends. The trade-off is that Ibis inherits the semantics and limits of whichever engine actually runs the query, so backend parity and dialect differences matter more than they would in a single-engine library.

When to use it — and when not to

A self-hoster relies on the DuckDB default for local work, which needs no server, but deploying against PostgreSQL, MySQL, MSSQL, ClickHouse, Trino, or Impala means operating and maintaining those database services separately, and cloud warehouses require their own accounts and credentials. The project carries 544 open issues and 767 forks on GitHub, so prospective users should weigh that backlog and confirm that the backends they depend on are covered before committing a critical pipeline to it. It is a poor fit for teams that want a hosted dashboarding or business intelligence product, and for purely single-machine pandas workflows that will never need to leave one process.

project readme (upstream, from github) — read inline

Ibis

Documentation status Project chat Anaconda badge PyPI Build status Build status Codecov branch

What is Ibis?

Ibis is the portable Python dataframe library:

See the documentation on "Why Ibis?" to learn more.

Getting started

You can pip install Ibis with a backend and example data:

pip install 'ibis-framework[duckdb,examples]'

💡 Tip

See the installation guide for more installation options.

Then use Ibis:

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.examples.penguins.fetch()
>>> t
┏━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━┓
┃ species ┃ island    ┃ bill_length_mm ┃ bill_depth_mm ┃ flipper_length_mm ┃ body_mass_g ┃ sex    ┃ year  ┃
┡━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━┩
│ string  │ string    │ float64        │ float64       │ int64             │ int64       │ string │ int64 │
├─────────┼───────────┼────────────────┼───────────────┼───────────────────┼─────────────┼────────┼───────┤
│ Adelie  │ Torgersen │           39.1 │          18.7 │               181 │        3750 │ male   │  2007 │
│ Adelie  │ Torgersen │           39.5 │          17.4 │               186 │        3800 │ female │  2007 │
│ Adelie  │ Torgersen │           40.3 │          18.0 │               195 │        3250 │ female │  2007 │
│ Adelie  │ Torgersen │           NULL │          NULL │              NULL │        NULL │ NULL   │  2007 │
│ Adelie  │ Torgersen │           36.7 │          19.3 │               193 │        3450 │ female │  2007 │
│ Adelie  │ Torgersen │           39.3 │          20.6 │               190 │        3650 │ male   │  2007 │
│ Adelie  │ Torgersen │           38.9 │          17.8 │               181 │        3625 │ female │  2007 │
│ Adelie  │ Torgersen │           39.2 │          19.6 │               195 │        4675 │ male   │  2007 │
│ Adelie  │ Torgersen │           34.1 │          18.1 │               193 │        3475 │ NULL   │  2007 │
│ Adelie  │ Torgersen │           42.0 │          20.2 │               190 │        4250 │ NULL   │  2007 │
│ …       │ …         │              … │             … │                 … │           … │ …      │     … │
└─────────┴───────────┴────────────────┴───────────────┴───────────────────┴─────────────┴────────┴───────┘
>>> g = t.group_by("species", "island").agg(count=t.count()).order_by("count")
>>> g
┏━━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━┓
┃ species   ┃ island    ┃ count ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━┩
│ string    │ string    │ int64 │
├───────────┼───────────┼───────┤
│ Adelie    │ Biscoe    │    44 │
│ Adelie    │ Torgersen │    52 │
│ Adelie    │ Dream     │    56 │
│ Chinstrap │ Dream     │    68 │
│ Gentoo    │ Biscoe    │   124 │
└───────────┴───────────┴───────┘

💡 Tip

See the getting started tutorial for a full introduction to Ibis.

Python + SQL: better together

For most backends, Ibis works by compiling its dataframe expressions into SQL:

>>> ibis.to_sql(g)
SELECT
  "t1"."species",
  "t1"."island",
  "t1"."count"
FROM (
  SELECT
    "t0"."species",
    "t0"."island",
    COUNT(*) AS "count"
  FROM "penguins" AS "t0"
  GROUP BY
    1,
    2
) AS "t1"
ORDER BY
  "t1"."count" ASC

You can mix SQL and Python code:

>>> a = t.sql("SELECT species, island, count(*) AS count FROM penguins GROUP BY 1, 2")
>>> a
┏━━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━┓
┃ species   ┃ island    ┃ count ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━┩
│ string    │ string    │ int64 │
├───────────┼───────────┼───────┤
│ Adelie    │ Torgersen │    52 │
│ Adelie    │ Biscoe    │    44 │
│ Adelie    │ Dream     │    56 │
│ Gentoo    │ Biscoe    │   124 │
│ Chinstrap │ Dream     │    68 │
└───────────┴───────────┴───────┘
>>> b = a.order_by("count")
>>> b
┏━━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━┓
┃ species   ┃ island    ┃ count ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━┩
│ string    │ string    │ int64 │
├───────────┼───────────┼───────┤
│ Adelie    │ Biscoe    │    44 │
│ Adelie    │ Torgersen │    52 │
│ Adelie    │ Dream     │    56 │
│ Chinstrap │ Dream     │    68 │
│ Gentoo    │ Biscoe    │   124 │
└───────────┴───────────┴───────┘

This allows you to combine the flexibility of Python with the scale and performance of modern SQL.

Backends

Ibis supports more than 20 backends:

How it works

Most Python dataframes are tightly coupled to their execution engine. And many databases only support SQL, with no Python API. Ibis solves this problem by providing a common API for data manipulation in Python, and compiling that API into the backend’s native language. This means you can learn a single API and use it across any supported backend (execution engine).

Ibis broadly supports two types of backend:

  1. SQL-generating backends
  2. DataFrame-generating backends

Ibis backend types

Portability

To use different backends, you can set the backend Ibis uses:

>>> ibis.set_backend("duckdb")
>>> ibis.set_backend("polars")
>>> ibis.set_backend("datafusion")

Typically, you'll create a connection object:

>>> con = ibis.duckdb.connect()
>>> con = ibis.polars.connect()
>>> con = ibis.datafusion.connect()

And work with tables in that backend:

>>> con.list_tables()
['penguins']
>>> t = con.table("penguins")

You can also read from common file formats like CSV or Apache Parquet:

>>> t = con.read_csv("penguins.csv")
>>> t = con.read_parquet("penguins.parquet")

This allows you to iterate locally and deploy remotely by changing a single line of code.

💡 Tip

Check out the blog on backend agnostic arrays for one example using the same code across DuckDB and BigQuery.

Community and contributing

Ibis is an open source project and welcomes contributions from anyone in the community.

readme truncated — read the full docs on github

Frequently asked questions

Is ibis free to use?

ibis is open source under the Apache-2.0 licence. There is no licence fee and no seat count — you can self-host it or, where the project offers one, pay a vendor for a managed version instead.

What does ibis do?

the portable Python dataframe library

What is ibis written in?

ibis is primarily written in Python. Its source is publicly available at https://github.com/ibis-project/ibis, and it has 6,661 GitHub stars.