Ask HN: Python UDFs in SQL. Who is using?

6 points by scratchbuffer 3 years ago | 2 comments

Who is using Python UDFs in SQL?

Lately I've seen many of the data-warehouses/lakes providers rolling out features that let's you run Python UDFs as part of your SQL command. And, runs it close to the data.

judell 3 years ago |

I'm a fan, and documented my journey in a series of posts including:

https://blog.jonudell.net/2021/07/24/pl-pgsql-versus-pl-pyth...

https://blog.jonudell.net/2021/08/13/pl-python-metaprogrammi...

https://blog.jonudell.net/2021/08/21/postgres-functional-sty...

timfi 3 years ago |

It may be "close[r] to the data", but be ware Python UDFs usually still have context switches between the python interpreter and the query executor. If these pile up — as is the case if you fire off queries in, say, a loop — you will still see lousy performance. There is currently research under way, to optimize UDFs and other forms of computations over database resident data. For Python, for example, you can checkout the python to SQL compiler [0] and associated demo-paper [1] we produced for this years Sigmod. (Disclaimer: as you may have noticed thanks to that " we", I'm an author on that paper.) Though it may only support a limited subset of Python, one can already do alot with just that.

[0]: https://apfel-db.informatik.uni-tuebingen.de [1]: https://db.inf.uni-tuebingen.de/publications/2022/Hirn-grust...