topic Re: Did any one in the community create Permanent Functions using using python script ? I tried but i am getting the below error, Please advise in Data Engineering

Did any one in the community create Permanent Functions using using python script ? I tried but i am getting the below error, Please advise

Manoj — Thu, 18 Nov 2021 02:37:27 GMT

Hi Team, When i am trying to register a permanant function i am getting the below error.

%sql

CREATE FUNCTION simple_udf AS 'SimpleUdf'

USING JAR '/tmp/SimpleUdf.jar';

%sql

select simple_udf(2)

Error Details :

com.databricks.backend.common.rpc.DatabricksExceptions$SQLExecutionException: org.apache.spark.sql.AnalysisException: Can not load class 'SimpleUdf' when registering the function 'default.simple_udf', please make sure it is on the classpath;

Re: Did any one in the community create Permanent Functions using using python script ? I tried but i am getting the below error, Please advise

-werners- — Thu, 18 Nov 2021 08:14:00 GMT

it seems the cluster cannot find the jar. My guess is your path is incorrect/incomplete or as the error says it: you have to let the cluster know where the jar can be found (classpath)

Re: Did any one in the community create Permanent Functions using using python script ? I tried but i am getting the below error, Please advise

Hubert-Dudek — Thu, 18 Nov 2021 11:19:37 GMT

probably correct path is /dbfs/tmp/SimpleUdf.jar

Re: Did any one in the community create Permanent Functions using using python script ? I tried but i am getting the below error, Please advise

jose_gonzalez — Thu, 18 Nov 2021 16:19:32 GMT

hi @Manoj Kumar Rayalla ,

Like @Hubert Dudek mentioned, make sure to check what is the exact location for your Jar file. Make sure to list your path first to check if the file it located in the right location.

Re: Did any one in the community create Permanent Functions using using python script ? I tried but i am getting the below error, Please advise

Manoj — Thu, 18 Nov 2021 16:41:42 GMT

Thanks for the Suggestions @Werner Stinckens @Jose Gonzalez

@Hubert Dudek . No luck in changing the path

Did anyone else got the same error while creating permanent functions using python script? I see java and scala has documentation but not python.

Thanks,

Manoj

Re: Did any one in the community create Permanent Functions using using python script ? I tried but i am getting the below error, Please advise

Manoj — Thu, 18 Nov 2021 16:44:10 GMT

Hi @Kaniz Fatma , Nice meeting you! Have you got a chance to create permanent functions in data bricks using python scripts, I am trying to anonymize the data using permanent views and permanent functions.. but i am having trouble in creating permanent functions

Re: Did any one in the community create Permanent Functions using using python script ? I tried but i am getting the below error, Please advise

-werners- — Thu, 18 Nov 2021 17:15:13 GMT

ok now I see the issue: you want to use python, but provide a jar (for java/scala).

I remember another post asking for sql functions in python:

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.

Re: Did any one in the community create Permanent Functions using using python script ? I tried but i am getting the below error, Please advise

Manoj — Thu, 18 Nov 2021 23:21:49 GMT

Hi @Werner Stinckens , Yes i followed the same instructions and was trying to solve this with a java program then later planning to convert python script to a jar file.

Below is the program that i used , Using Eclipse i was able to generate the .jar file successfully by adding "org.apache.hadoop.hive.ql.exec.UDF" this class related jar into the project,

Code :

import org.apache.hadoop.hive.ql.exec.UDF;

public class SimpleUdf extends UDF {

public int evaluate(int value) {

return value + 10;

}

Jar file Link :

http://www.java2s.com/Code/Jar/h/Downloadhive041jar.htm

And then tried the below commands

%sql

CREATE FUNCTION simple_udf AS 'SimpleUdf'

USING JAR '/tmp/SimpleUdf.jar';

AND

%sql

CREATE FUNCTION simple_udf AS 'SimpleUdf'

USING JAR '/dbfs/tmp/SimpleUdf.jar';

%sql

select simple_udf(2)

I placed it on the File Store of data bricks cluster and it kept throwing the same error, please make sure the class path is correct.

Re: Did any one in the community create Permanent Functions using using python script ? I tried but i am getting the below error, Please advise

Manoj — Thu, 18 Nov 2021 23:23:06 GMT

Hi @Werner Stinckens , Yes i followed the same instructions and was trying to solve this first for java program then later planning to convert python script to a jar file.

Below is the program that i used , Using Eclipse i was able to generate the .jar file successfully by adding "org.apache.hadoop.hive.ql.exec.UDF" this class file related jar into the project,

Code :

import org.apache.hadoop.hive.ql.exec.UDF;

public class SimpleUdf extends UDF {

public int evaluate(int value) {

return value + 10;

}

Jar file Link :

http://www.java2s.com/Code/Jar/h/Downloadhive041jar.htm

I placed it on the File Store of data bricks cluster and kept throwing the same error, please make sure the class path is correct.

I see you are from Data Bricks did you guys any documentation that helps me understand with an example...

Below is there but its not clear

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

Re: Did any one in the community create Permanent Functions using using python script ? I tried but i am getting the below error, Please advise

-werners- — Fri, 19 Nov 2021 07:15:06 GMT

Ok, so I tried your example myself.

The issue lies within your jar.

the jar you provide is not called 'SimpleUdf.jar' but 'hive_0_4_1.jar'.
your jar should contain a class called 'SimpleUdf', but there is no such function in there.

So I used your jar and tried myself, using a class which is in the jar:

What this function does, I have no idea. But it is important that you point to an existing class, and take the complete name, not only the last part (so in this case org.apache.hadoop.hive.ql.udf.UDFLog and not just UDFLog).

Hope this helps.

Re: Did any one in the community create Permanent Functions using using python script ? I tried but i am getting the below error, Please advise

Manoj — Wed, 26 Jan 2022 21:45:16 GMT

hi @Werner Stinckens @Jose Gonzalez @Hubert Dudek @Kaniz Fatma

Thanks for all the help, Appreciate it. I was able to create permanent functions and use eclipse to create the runnable jar. However, Does anyone have any idea on how to deploy the jar on SQL End Point Cluster.

Re: Did any one in the community create Permanent Functions using using python script ? I tried but i am getting the below error, Please advise

-werners- — Thu, 27 Jan 2022 08:14:11 GMT

I doubt if that is possible at the moment, it would also go against the use of photon (which is C++).

Re: Did any one in the community create Permanent Functions using using python script ? I tried but i am getting the below error, Please advise

Hubert-Dudek — Thu, 27 Jan 2022 13:10:13 GMT

As SQL End Point is serverless I doubt it is possible

Re: Did any one in the community create Permanent Functions using using python script ? I tried but i am getting the below error, Please advise

Sandy126 — Tue, 14 Jun 2022 18:24:29 GMT

Hi @Manoj Kumar Rayalla Would you please share how did you manage to resolve the issue at your side. I am facing the same error. I already had tried the above suggested details but no luck so far.

Thanks in advance!