Calling a .py Function using DF from another file

[Amit_Garg]

I have created a file NBF_TextTranslation

spark = SparkSession.builder.getOrCreate()

df_TextTranslation = spark.read.format('delta').load(textTranslation_path)

def getMediumText(TextID, PlantName):
   df1 = spark.sql("SELECT TextID, PlantName, LanguageID, Short, Medium, Extended, Active FROM delta.`{0}` tt".format(textTranslation_path))
   df2 = df1.filter((df1.PlantName == PlantName) & (df1.TextID == TextID) & (df1.LanguageID == '1033') & (df1.Active == "True"))
    medium_Value = df2.select('Medium').first()[0]
   return medium_Value

I am calling my function from file DimDepartment_Amit

%run "../Functions/NBF_TextTranslation"
TextID = '106905303'
PlantName = 'BKIZ'
LanguageID = 1033

medium_value = getMediumText(TextID, PlantName)
print(medium_value)

This part is working but when I am calling it from SQL.

sqlStatement =  "\
    SELECT\
        PlantName as BK_PlantName,\
        TextId,\
        getMediumText(TextId, PlantName) as DepartmentName,\
    FROM delta.`{0}`".format(src_path_Department)

df_stgDimDepartment = spark.sql(sqlStatement)

df_stgDimDepartment.show()

I am getting below error

Cannot resolve function getMediumText on search path [system.builtin, system.session, spark_catalog.default]. SQLSTATE: 42883; line 1 pos 167

[Lakshay]

You should create a udf on top of getMediumText function and then use the udf in the sql statement.