topic CREATE FUNCTION from Python file in Data Engineering

CREATE FUNCTION from Python file

gbrueckl — Thu, 14 Oct 2021 20:12:48 GMT

Is it somehow possible to create an SQL external function using Python code?

the examples only show how to use JARs

https://docs.databricks.com/spark/latest/spark-sql/language-manual/sql-ref-syntax-ddl-create-function.html

something like:

CREATE TEMPORARY FUNCTION simple_temp_udf AS 'SimpleUdf' USING FILE '/tmp/SimpleUdf.py';

Re: CREATE FUNCTION from Python file

-werners- — Fri, 15 Oct 2021 11:55:06 GMT

I would think the USING FILE would work.

As long as you follow the class_name requirements.

The implementing class should extend one of the base classes as follows:

Should extend UDF or UDAF in org.apache.hadoop.hive.ql.exec package.
Should extend AbstractGenericUDAFResolver, GenericUDF, or GenericUDTF in org.apache.hadoop.hive.ql.udf.generic package.
Should extend UserDefinedAggregateFunction in org.apache.spark.sql.expressions package.

Also the docs literally state python is possible:

In addition to the SQL interface, Spark allows you to create custom user defined scalar and aggregate functions using Scala, Python, and Java APIs. See User-defined scalar functions (UDFs) and User-defined aggregate functions (UDAFs) for more information.

So it should be possible, maybe your python class does not meet the requirements?

Re: CREATE FUNCTION from Python file

Mumu — Thu, 27 Jan 2022 22:31:41 GMT

For python which class to extend then? All of the listed parent classes are java

Re: CREATE FUNCTION from Python file

Anonymous — Mon, 31 Jan 2022 23:20:16 GMT

@Wugang Xu - My name is Piper, and I'm a moderator here for Databricks. Thanks for coming to us with your question. We'll give the members a bit longer to respond and come back if we need to. Thanks in advance for your patience. 🙂

Re: CREATE FUNCTION from Python file

-werners- — Tue, 01 Feb 2022 07:08:04 GMT

for pyspark you can use udf().

Here is an example on how to do this.

Re: CREATE FUNCTION from Python file

Mumu — Tue, 01 Feb 2022 14:43:13 GMT

Thanks for your response. What I am looking for is to define a view with the UDF. However, a session level UDF as described in this example you provided does not seem to allow that. Maybe I should clarify my question as to define a external UDF like those Hive ones.

Re: CREATE FUNCTION from Python file

pts — Sat, 05 Feb 2022 02:11:28 GMT

As a user of your code, I'd find it a less pleasant API because I'd have to some_module.some_func.some_func() rather than just some_module.some_func()

No reason to have "some_func" exist twice in the hierarchy. It's kind of redundant. If some_func is so large that adding any more ocde to the file seems crazy, maybe some_func is too large and you want to refactor it and simplify it.

Having one file serve one purpose makes sense. Having it literally have only a single function and nothing else is pretty unusual.