07-10-2024 04:50 AM
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"
""")
4 weeks ago
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/8...
07-12-2024 04:56 AM - edited 07-12-2024 04:57 AM
Hi @marcuskw, In Spark SQL, updating temporary tables is not directly supported. A few alternatives are:-
Let me know if you need further assistance or have any other questions!
07-12-2024 05:13 AM
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.""
07-12-2024 07:57 AM
07-12-2024 01:10 PM
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"
}
)
07-22-2024 12:50 AM
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"
}
)
@Kaniz_Fatma You should update the docs accordingly.
4 weeks ago
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/8...
Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.
If there isn’t a group near you, start one and help create a community that brings people together.
Request a New Group