docs: Scalar UDF Documentation

[RAbraham]

Please describe the issue

Hi,
At https://ibis-project.org/reference/scalar-udfs#ibis.expr.operations.udf.scalar.pandas, it's mentioned that I should be able to see some output when I run str_cap(t.str_col)

>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable(dict(int_col=[1, 2, 3], str_col=["a", "b", "c"]))
>>> t
┏━━━━━━━━━┳━━━━━━━━━┓
┃ int_col ┃ str_col ┃
┡━━━━━━━━━╇━━━━━━━━━┩
│ int64   │ string  │
├─────────┼─────────┤
│       1 │ a       │
│       2 │ b       │
│       3 │ c       │
└─────────┴─────────┘
>>> @ibis.udf.scalar.pandas
... def str_cap(x: str) -> str:
...     # note usage of pandas `str` method
...     return x.str.capitalize()
>>> str_cap(t.str_col)  # doctest: +SKIP
┏━━━━━━━━━━━━━━━━━━━━━━━┓
┃ string_cap_0(str_col) ┃
┡━━━━━━━━━━━━━━━━━━━━━━━┩
│ string                │
├───────────────────────┤
│ A                     │
│ B                     │
│ C                     │
└───────────────────────┘

But when I run it, I get just an expression. which when I execute with

r = str_cap(t.str_col)
r.execute()

I get an error: NotImplementedError: pandas UDFs are not supported in the <class 'sqlglot.dialects.duckdb.DuckDB'> backend

How can I test out the pandas udf? If you can show me a snippet of code, perhaps with other backends (e.g. sqlite), that'd be great.

Code of Conduct

• I agree to follow this project's Code of Conduct

[NickCrews]

The reason you don't see anything in the preview is because ipython tries to execute the expression, gets that error, and then chooses to show nothing instead of showing the error. That might be ibis code somewhere hiding the error from us, not sure. Not great UX, could be considered a bug. Anyway, onto the real issue:

as it says, the duckdb backed doesn't support pandas UDFs. See here, particularly the kind param: only 'python' and 'arrow' are supported. Here in ibis, we could add in some compat code so that the user can supply a pandas udf, and then we wrap it in something that turns it into a pyarrow udf, and then actually pass that to duckdb. But that is a feature we haven't implemented yet. If you want to submit a PR for that, it would be awesome!

Otherwise, what you should do is instead implement your UDF with pyarrow. Can you do that? I bet there is a pyarrow.compute function for capitalization

[RAbraham]

Thanks @NickCrews

re: duckdb backend, I wasn't clear, sorry. I didn't mean that we should be able to run pandas UDF on duckdb. I just wanted to investigate running a pandas UDF on any backend (or in memory which is not duckdb). I just was looking for some code to play with and investigate the extent to which I can use a pandas UDF. So it could be sqlite for e.g.

[NickCrews]

Ah I see, sorry I had bad reading comprehension the first time.

Unfortunately it looks like all of our "easy" local backends ("duckdb", "datafusion", "polars", "sqlite") don't support pandas UDFs.

Maybe running a postgres db in a docker container would be easiest? Assuming you have that, this should work:

import ibis
ibis.options.interactive = True

con = ibis.postgres.connect("postgres:postgres@localhost:5432/postgres")
d = dict(int_col=[1, 2, 3], str_col=["a", "b", "c"])
t = ibis.memtable(d)
t = con.create_table("t", t)
# could also directly do
# t = con.create_table("t", d)

@ibis.udf.scalar.pandas
def str_cap(x: str) -> str:
    return x.str.capitalize()

str_cap(t.str_col)
┏━━━━━━━━━━━━━━━━━━━━━━━┓
┃ string_cap_0(str_col) ┃
┡━━━━━━━━━━━━━━━━━━━━━━━┩
│ string                │
├───────────────────────┤
│ A                     │
│ B                     │
│ C                     │
└───────────────────────┘

[RAbraham]

This is great. I'll try it out. Thanks!

[NickCrews]

Also see that linked PR. If you can check that out and use it, then you should be able to test on duckdb and datafusion.