topic Re: IDENTIFIER not working in UPDATE in Data Engineering

IDENTIFIER not working in UPDATE

marcuskw — Wed, 10 Jul 2024 11:50:42 GMT

The following code works perfectly fine:

df = spark.createDataFrame([('A', 1), ('B', 2)]) df.createOrReplaceTempView('temp') spark.sql(""" SELECT IDENTIFIER(:col) FROM temp """, args={ "col": "_1" } ).display()

However if we instead want to do an UPDATE the same logic no longer functions:

spark.sql(""" UPDATE temp SET IDENTIFIER(:col) = "C" """, args={ "col": "_1" } )

Here we get the following error:
[PARSE_SYNTAX_ERROR] Syntax error at or near '(': missing EQ. SQLSTATE: 42601

Updating a temp table is not allowed, but used this quickly for a debug example.
When running the following UPDATE we get the correct error message "UPDATE TABLE is not supported temporarily.":

spark.sql(""" UPDATE temp SET _1 = "C" """)

Re: IDENTIFIER not working in UPDATE

marcuskw — Fri, 12 Jul 2024 12:13:40 GMT

Hi,

I wrote it in the bottom of my post that I know that updating a Temp table isn't allowed.
I used it as a quick example to illustrate the error message, but you get the same error message when updating a Delta Lake table.

The problem is the IDENTIFIER() syntax doesn't seem to function properly in an UPDATE statement. But works perfectly fine in a SELECT.

"Updating a temp table is not allowed, but used this quickly for a debug example.
When running the following UPDATE we get the correct error message "UPDATE TABLE is not supported temporarily.""

Re: IDENTIFIER not working in UPDATE

Witold — Fri, 12 Jul 2024 14:57:51 GMT

Hey @marcuskw the docs state that this is actually not supported. You can only use table names in an update statement:

Table name of a MERGE, UPDATE, DELETE, INSERT, COPY INTO

Re: IDENTIFIER not working in UPDATE

marcuskw — Fri, 12 Jul 2024 20:10:52 GMT

Doesn't the docs say it is supported? (DML)

"A column or view referenced in a query. This includes queries embedded in a DDL or DML statement.
When using the identifier clause it may not be embedded within an identifier."

Here I'm referencing a column in an UPDATE statement.

spark.sql(""" UPDATE temp SET IDENTIFIER(:col) = "C" """, args={ "col": "_1" } )

Re: IDENTIFIER not working in UPDATE

Witold — Mon, 22 Jul 2024 07:50:51 GMT

Hey @marcuskw!

Sorry for the late response. IMHO the docs are not clear on that, but IDENTIFIER's are not supported in `SET` clauses. It works in `WHERE` clauses though (which of course will not help in your case):

spark.sql(""" UPDATE temp SET _1 = "C" WHERE IDENTIFIER(:col) <> "C" """, args={ "col": "_1" } )

@Retired_mod You should update the docs accordingly.

Re: IDENTIFIER not working in UPDATE

marcuskw — Fri, 16 Aug 2024 06:55:32 GMT

If it helps anyone else I found this article that described a few limitations:
https://community.databricks.com/t5/technical-blog/how-not-to-build-an-execute-immediate-demo/ba-p/82167