Reference built-in functions

Scalar functions

Functions that aren’t exposed in ibis directly can be accessed using the @ibis.udf.scalar.builtin decorator.

Ibis APIs may already exist for your function.

Builtin scalar UDFs are designed to be an escape hatch when Ibis doesn’t have a defined API for a built-in database function.

See the reference documentation for existing APIs.

DuckDB

Ibis doesn’t directly expose many of the DuckDB text similarity functions. Let’s expose the mismatches API.

from ibis import udf

@udf.scalar.builtin
def mismatches(left: str, right: str) -> int:
    ...

The ... is a visual indicator that the function definition is unknown to Ibis.

Ibis will not execute the function body or otherwise inspect it. Any code you write in the function body will be ignored.

We can now call this function on any ibis expression:

import ibis

con = ibis.duckdb.connect()
1
Connect to an in-memory DuckDB database 
expr = mismatches("duck", "luck")
con.execute(expr)
1

Like any other ibis expression you can inspect the SQL:

import ibis

ibis.to_sql(expr, dialect="duckdb")
1
The dialect keyword argument must be passed, because we constructed a literal expression which has no backend attached. 
SELECT
  MISMATCHES('duck', 'luck') AS "mismatches('duck', 'luck')"

Because built-in UDFs are ultimately Ibis expressions, they compose with the rest of the library:

ibis.options.interactive = True

@udf.scalar.builtin
def jaro_winkler_similarity(a: str, b: str) -> float:
   ...

pkgs = ibis.read_parquet(
   "https://storage.googleapis.com/ibis-tutorial-data/pypi/packages.parquet"
)
pandas_ish = pkgs[jaro_winkler_similarity(pkgs.name, "pandas") >= 0.9]
pandas_ish
┏━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━┓
┃ name     ┃ version           ┃ requires_python ┃ yanked  ┃ has_binary_wheel ┃ has_vulnerabilities ┃ first_uploaded_at   ┃ last_uploaded_at    ┃ recorded_at         ┃ downloads ┃ scorecard_overall ┃ in_google_assured_oss ┃
┡━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━┩
│ string   │ string            │ string          │ boolean │ boolean          │ boolean             │ timestamp           │ timestamp           │ timestamp           │ int32     │ float64           │ boolean               │
├──────────┼───────────────────┼─────────────────┼─────────┼──────────────────┼─────────────────────┼─────────────────────┼─────────────────────┼─────────────────────┼───────────┼───────────────────┼───────────────────────┤
│ bcpandas │ 2.4.1             │ >=3.8.1         │ True    │ False            │ False               │ 2023-07-12 06:14:22 │ 2023-07-12 06:14:23 │ 2023-07-12 14:31:41 │         0 │               nan │ False                 │
│ espandas │ 1.0.4             │ ~               │ False   │ False            │ False               │ 2018-12-22 20:52:30 │ 2018-12-22 20:52:30 │ 2023-07-12 14:58:47 │         0 │               3.6 │ False                 │
│ fpandas  │ 0.5               │ ~               │ False   │ False            │ False               │ 2020-03-09 02:35:31 │ 2020-03-09 02:35:31 │ 2023-07-12 15:04:23 │         0 │               nan │ False                 │
│ h3pandas │ 0.2.4             │ >=3.6           │ False   │ False            │ False               │ 2023-03-19 17:58:16 │ 2023-03-19 17:58:16 │ 2023-07-12 15:10:06 │         0 │               nan │ False                 │
│ ipandas  │ 0.0.1             │ ~               │ False   │ False            │ False               │ 2019-05-29 18:46:12 │ 2019-05-29 18:46:12 │ 2023-07-12 15:15:34 │         0 │               3.6 │ False                 │
│ kpandas  │ 0.0.1             │ >=3.6,<4.0      │ False   │ False            │ False               │ 2019-05-02 18:00:29 │ 2019-05-02 18:00:31 │ 2023-07-12 15:20:21 │         0 │               nan │ False                 │
│ mpandas  │ 0.0.2.1           │ ~               │ False   │ False            │ False               │ 2022-07-03 16:21:21 │ 2022-07-03 16:21:23 │ 2023-07-12 15:30:35 │         0 │               nan │ False                 │
│ mtpandas │ 1.14.202306141807 │ >=3.6           │ False   │ False            │ False               │ 2023-06-14 18:08:01 │ 2023-06-14 18:08:01 │ 2023-07-12 15:31:04 │         0 │               4.6 │ False                 │
│ mypandas │ 0.1.6             │ >=3.10          │ False   │ False            │ False               │ 2022-10-24 21:01:10 │ 2022-10-24 21:01:12 │ 2023-07-12 15:32:04 │         0 │               nan │ False                 │
│ paandas  │ 0.0.3             │ ~               │ False   │ False            │ False               │ 2022-11-24 06:11:15 │ 2022-11-24 06:11:17 │ 2023-07-12 15:43:31 │         0 │               nan │ False                 │
│ …        │ …                 │ …               │ …       │ …                │ …                   │ …                   │ …                   │ …                   │         … │                 … │ …                     │
└──────────┴───────────────────┴─────────────────┴─────────┴──────────────────┴─────────────────────┴─────────────────────┴─────────────────────┴─────────────────────┴───────────┴───────────────────┴───────────────────────┘

Let’s count the results:

pandas_ish.count()








178








There are a good number of packages that look similar to pandas!






Snowflake


Similarly we can expose Snowflake’s jarowinkler_similarity function.


Let’s alias it to jw_sim to illustrate some more of the Ibis udf API:




@udf.scalar.builtin(name="jarowinkler_similarity")
def jw_sim(left: str, right: str) -> float:
    ...






1



target is the name of the function in the backend. This argument is required in this because the function name is different than the name of the function in ibis.









Now let’s connect to Snowflake and call our jw_sim function:




import os

con = ibis.connect(os.environ["SNOWFLAKE_URL"])






expr = jw_sim("snow", "shoe")
con.execute(expr)




66.0






And let’s take a look at the SQL




ibis.to_sql(expr, dialect="snowflake")




SELECT
  JAROWINKLER_SIMILARITY('snow', 'shoe') AS "jw_sim('snow', 'shoe')"










Input types


Sometimes the input types of builtin functions are difficult to spell.


Consider a function that computes the length of any array: the elements in the array can be floats, integers, strings and even other arrays. Spelling that type is difficult.


Fortunately the udf.scalar.builtin decorator doesn’t require you to specify input types in these cases:




@udf.scalar.builtin(name="array_size")
def cardinality(arr) -> int:
   ...














The return type annotation is always required.












We can pass arrays with different element types to our cardinality function:




con.execute(cardinality([1, 2, 3]))




3








con.execute(cardinality(["a", "b"]))




2






When you bypass input types the errors you get back are backend dependent:




con.execute(cardinality("foo"))




ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001044 (42P13): SQL compilation error: error line 1 at position 7
Invalid argument types for function 'ARRAY_SIZE': (VARCHAR(3))
[SQL: SELECT array_size(%(param_1)s) AS "cardinality('foo')"]
[parameters: {'param_1': 'foo'}]
(Background on this error at: https://sqlalche.me/e/14/f405)






Here, Snowflake is informing us that the ARRAY_SIZE function does not accept strings as input.








Aggregate functions


Aggregate functions that aren’t exposed in ibis directly can be accessed using the @ibis.udf.agg.builtin decorator.












Ibis APIs may already exist for your function.







Builtin aggregate UDFs are designed to be an escape hatch when Ibis doesn’t have a defined API for a built-in database function.


See the reference documentation for existing APIs.






Let’s the use the DuckDB backend to demonstrate how to access an aggregate function that isn’t exposed in ibis: kurtosis.




DuckDB


First, define the builtin aggregate function:




@udf.agg.builtin
def kurtosis(x: float) -> float:
   ...






1



Both the input and return type annotations indicate the element type of the input, not the shape (column or scalar). Aggregations can only be called on column expressions.









One of the powerful features of this API is that you can define your UD(A)Fs at any point during your analysis. You don’t need to connect to the database to define your functions.


Let’s compute the kurtosis of the number of votes across all movies:




from ibis import _

expr = (
   ibis.examples.imdb_title_ratings.fetch()
   .rename("snake_case")
   .agg(kurt=lambda t: kurtosis(t.num_votes))
)
expr




┏━━━━━━━━━━━━━┓
┃ kurt        ┃
┡━━━━━━━━━━━━━┩
│ float64     │
├─────────────┤
│ 4545.349906 │
└─────────────┘







Since this is an aggregate function, it has the same capabilities as other, builtin aggregates like sum: it can be used in a group by as well as in a window function expression.


Let’s compute kurtosis for all the different types of productions (shorts, movies, TV, etc):




basics = (
   ibis.examples.imdb_title_basics.fetch()
   .rename("snake_case")
   .filter(_.is_adult == 0)
)
ratings = ibis.examples.imdb_title_ratings.fetch().rename("snake_case")

basics_ratings = ratings.join(basics, "tconst")

expr = (
   basics_ratings.group_by("title_type")
   .agg(kurt=lambda t: kurtosis(t.num_votes))
   .order_by(_.kurt.desc())
   .head()
)
expr




┏━━━━━━━━━━━━━━┳━━━━━━━━━━━━━┓
┃ title_type   ┃ kurt        ┃
┡━━━━━━━━━━━━━━╇━━━━━━━━━━━━━┩
│ string       │ float64     │
├──────────────┼─────────────┤
│ tvEpisode    │ 8043.838209 │
│ tvSeries     │ 4030.938238 │
│ short        │ 3645.730119 │
│ tvMiniSeries │ 1901.614316 │
│ tvMovie      │ 1316.403908 │
└──────────────┴─────────────┘







Similarly for window functions:




expr = (
   basics_ratings.mutate(
      kurt=lambda t: kurtosis(t.num_votes).over(group_by="title_type")
   )
   .relocate("kurt", after="tconst")
   .filter(
      [
         _.original_title.lower().contains("godfather"),
         _.title_type == "movie",
         _.genres.contains("Crime") & _.genres.contains("Drama"),
      ]
   )
)
expr




┏━━━━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━┓
┃ tconst     ┃ kurt        ┃ average_rating ┃ num_votes ┃ title_type ┃ primary_title          ┃ original_title         ┃ is_adult ┃ start_year ┃ end_year ┃ runtime_minutes ┃ genres             ┃
┡━━━━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━┩
│ string     │ float64     │ float64        │ int64     │ string     │ string                 │ string                 │ int64    │ int64      │ string   │ int64           │ string             │
├────────────┼─────────────┼────────────────┼───────────┼────────────┼────────────────────────┼────────────────────────┼──────────┼────────────┼──────────┼─────────────────┼────────────────────┤
│ tt13130308 │ 1090.363856 │            5.2 │      7303 │ movie      │ Godfather              │ Godfather              │        0 │       2022 │ NULL     │             157 │ Action,Crime,Drama │
│ tt0458027  │ 1090.363856 │            3.7 │        27 │ movie      │ Mumbai Godfather       │ Mumbai Godfather       │        0 │       2005 │ NULL     │            NULL │ Action,Crime,Drama │
│ tt0068646  │ 1090.363856 │            9.2 │   1945537 │ movie      │ The Godfather          │ The Godfather          │        0 │       1972 │ NULL     │             175 │ Crime,Drama        │
│ tt0071562  │ 1090.363856 │            9.0 │   1321642 │ movie      │ The Godfather Part II  │ The Godfather Part II  │        0 │       1974 │ NULL     │             202 │ Crime,Drama        │
│ tt0074412  │ 1090.363856 │            5.2 │      1733 │ movie      │ Disco Godfather        │ Disco Godfather        │        0 │       1979 │ NULL     │              98 │ Action,Crime,Drama │
│ tt0099674  │ 1090.363856 │            7.6 │    412936 │ movie      │ The Godfather Part III │ The Godfather Part III │        0 │       1990 │ NULL     │             162 │ Crime,Drama        │
│ tt0250404  │ 1090.363856 │            6.5 │       244 │ movie      │ Godfather              │ Godfather              │        0 │       1992 │ NULL     │            NULL │ Crime,Drama        │
└────────────┴─────────────┴────────────────┴───────────┴────────────┴────────────────────────┴────────────────────────┴──────────┴────────────┴──────────┴─────────────────┴────────────────────┘













 Back to top