getArgument works fine in interactive cluster 10.4 LTS, raises error in interactive cluster 10.4 LTS

[llvu]

Hello,

I am trying to use the getArgument() function in a spark.sql query. It works fine if I run the notebook via an interactive cluster, but gives an error when executed via a job run in an instance Pool.

query:

OPTIMIZE <table>

where date = replace(regexp_extract(getArgument('folderPath'), '=\\\d{8}', 0), '=', '')

Error:

Exception: Undefined function: getArgument. This function is neither a built-in/temporary function, nor a persistent function that is qualified as spark_catalog.default.getargument.; line 2 pos 49

I can't seem to figure out why this error occurs and how I can solve the issue. As the notebook used for the query is a common notebook used by multiple jobs, I would like to resolve the issue without changing anything to the notebook. A change to the query executed by spark.sql() is possible. Could someone help me out?

Interactive cluster settings:

Driver: Standard_DS3_v2

Workers: Standard_DS3_v2

10.4LTS (includes Apache Spark 3.2.1, Scala 2.12)

Pool settings:

Driver: Standard_DS3_v2

Workers: Standard_DS3_v2

10.4LTS (includes Apache Spark 3.2.1, Scala 2.12)

[Kaniz_Fatma]

Hi @llvu, The error you are encountering is that the getArgument() function is not recognized when running your notebook as a job. This is because the getArgument() function is a widget function, and widget values are treated as string literals that can be used in queries when you execute notebooks interactively. However, when you run the notebook as a job, you cannot use widgets to pass arguments between different languages within a notebook.

[llvu]

Hi @Kaniz_Fatma, thank you for your response. Would there be a way to still use the widget value? Or the pyspark parameter if this line is part of the code:

folder_name = dbutils.widgets.get('folderName')

Without rewriting spark.sql("""query""") into spark.sql(f"""query""")?

[Kaniz_Fatma]

Hi @llvu , Yes, you can use the widget value in your PySpark code without having to rewrite spark.sql("""query""") into spark.sql(f"""query"""). You can achieve this by using the getArgument function in your SQL query within the spark.sql() call.

[llvu]

Hi @Kaniz_Fatma, thank you for the suggestion. I tried calling the parameter instead of the widget value. However, as I expected, this didn’t resolve the original problem of getArgument() not being recognised as a function.

query:

folder_path = dbutils.widgets.get('folderPath')

Spark.sql(“””

OPTIMIZE <table>

where date = replace(regexp_extract(getArgument('folder_path'), '=\\\d{8}', 0), '=', '') “””)

error:

Exception: Undefined function: getArgument. This function is neither a built-in/temporary function, nor a persistent function that is qualified as spark_catalog.default.getargument.; line 2 pos 49

[llvu]

Hi @Kaniz_Fatma,

Would you be able to respond to my last comment? I couldn't manage to get it working yet.

Thank you in advance.