Monetdbe Python UDF

Question

Given that monetdbe is a Python package, I'm optimistic that Python user-defined-functions are possible, but I haven't been able to find an example. I've tried this:

drop function every_other_letter;

create function every_other_letter(s string)
returns string
language python {
     letters = "".join(s[::2])
     return letters
};

select every_other_letter('foobarbaz');

I get this error:

ParseException:SQLparser:42000!Internal error while compiling statement: TypeException:user.p2_1[4]:'pyapi3.eval' library error in: Embedded Python 3 has not been enabled. Start server with --set embedded_py=3

Is there any way to set these flags in the embedded version?

Can you share your reasons for wanting to use Python in monetdb in Python? — Yunus King, Jan 26 '22 at 13:57
@ArisKoning, sure. I have a UI that sends some queries to an embedded Monet instance. A user can't run arbitrary Python scripts, but I thought making a UDF would be ok. I'm mostly working with datascientists, so they would easily understand a Python example. — dcc310, Jan 27 '22 at 21:02
Ah, interesting use case. I see why LANGUAGE PYTHON becomes appealing then. You might consider adding a feature request for this on MonetDBe-Python's github. — Yunus King, Jan 31 '22 at 10:08

score 1 · Accepted Answer · answered Jan 26 '22 at 13:55

The LANGUAGE PYTHON UDF's are a nice development feature in MonetDB's server installation but this feature requires an additional Python module to be loaded. And there is currently no way to configure monetdbe to load the required python module.

However assuming you have performance requirement for some production setting that are not met with the out-of-the-box SQL toolset in monetdbe, it makes more sense to implement a custom UDF extension written in C/C++. In regular MonetDB's server installation, the database server mserver5 can load an arbitrary extension module using the --loadmodule=<module> command option. But there is no equivalent monetdbe_option as of yet.

You might consider adding a feature request for this on monetdbe-python's github repository.

However there seems to exist a functioning undocumented workaround for adding UDF extensions to monetdbe. During its initialization, monetdbe attempts to load a set of hard coded modules. One of those is a module named "udf". You can create your own implementation of this module and load it into monetdbe.

Creating a native UDF extension is outside of the scope of this question and answer but there exist a nice up-to-date tutorial for writing UDF extensions for MonetDB here. Following the steps described in that tutorial, you end up with a SQL function revstr which has a user defined native implementation. The following Python script demonstrate its use:

from monetdbe import connect

with connect(autocommit=True) as con:
    cur = cur.execute("select revstr('abcde')")
    result = cur.fetchall()
    print(result)

Make sure that the library containing your UDF extension is in the search path of the dynamic linker:

LD_LIBRARY_PATH=<path to directory containing lib_udf> python test.py

Thanks! My C/C++ skills are pretty weak at the moment, so I will try some of the other languages first, but good to know that there is a workaround if I really need it. Or maybe there is some way to write these things in Python and have a program translate it to C that can be imported so as to work with the tutorial. — dcc310, Jan 27 '22 at 21:08

Monetdbe Python UDF

1 Answers1