topic Re: to_date not functioning as expected after introduction of arbitrary replaceWhere in Databricks 9.1 LTS in Data Engineering

to_date not functioning as expected after introduction of arbitrary replaceWhere in Databricks 9.1 LTS

kolangareth — Thu, 13 Jan 2022 15:46:18 GMT

I am trying to do a dynamic partition overwrite on delta table using replaceWhere option. This was working fine until I upgraded the DB runtime to 9.1 LTS from 8.3.x. I am concatenating 'year', 'month' and 'day' columns and then using to_date function to define the replaceWhere condition. I could find in the documentation that there was a new feature added as part of 9.1 LTS - Delta now supports arbitrary replaceWhere. Probably this has introduced a bug here. I am using the spark conf setting to fallback to old behaviour.

spark.conf.set("spark.databricks.delta.replaceWhere.dataColumns.enabled", false)

But would be nice to have a better solution for this problem. Here is my write statement with replaceWhere.

dataframeToWrite.write.format("delta").mode("overwrite").partitionBy("year","month","day").option("replaceWhere", s"to_date(concat(year, '-', month, '-', day)) in ($datesList)").save(outputLocation)

Error message:

Caused by: org.apache.spark.SparkException: Job aborted due to stage failure: Task 0 in stage 11.0 failed 4 times, most recent failure: Lost task 0.3 in stage 11.0 (TID 13) (): java.lang.UnsupportedOperationException: Cannot generate code for expression: to_date(concat(cast(year#2810 as string), -, cast(month#2863 as string), -, cast(day#2916 as string)), None)

PS: I've also tried specifying the format in to_date, but it doesn't help either.

Re: to_date not functioning as expected after introduction of arbitrary replaceWhere in Databricks 9.1 LTS

Anonymous — Thu, 13 Jan 2022 17:52:10 GMT

Hi @Prasanth Kolangareth - Welcome and thank you for your question. I'm sorry to hear about the trouble you're having. Let's give the community a chance to respond first. Thanks for your patience.

Re: to_date not functioning as expected after introduction of arbitrary replaceWhere in Databricks 9.1 LTS

-werners- — Fri, 14 Jan 2022 08:10:42 GMT

strange,

have you tried to create a column with to_date in your dataframe, so not in the replaceWhere condition?

df2 = df1.withColumn(to_date(..., "yyyyMMdd")) something like that

Re: to_date not functioning as expected after introduction of arbitrary replaceWhere in Databricks 9.1 LTS

kolangareth — Fri, 14 Jan 2022 09:04:04 GMT

Hi @Werner Stinckens , the to_date function works fine everywhere else like filter, withColumn etc. If you meant to create a new column in the dataframe and then use it in the replaceWhere - it's not something I want to do, because to do that I must add this new column in the target delta table also.

Re: to_date not functioning as expected after introduction of arbitrary replaceWhere in Databricks 9.1 LTS

-werners- — Fri, 14 Jan 2022 10:43:36 GMT

my only guess is that either the replaceWhere cannot take expressions, or the expression is faulty.

Re: to_date not functioning as expected after introduction of arbitrary replaceWhere in Databricks 9.1 LTS

kolangareth — Fri, 14 Jan 2022 11:22:53 GMT

I think it is neither.

replaceWhere can take expressions, because I've tried using concat without to_date and it doesn't fail.
The expression isn't faulty, because it works with the spark conf setting to fallback to old behaviour and also the same expression works in filter, withColumn.

Re: to_date not functioning as expected after introduction of arbitrary replaceWhere in Databricks 9.1 LTS

Hubert-Dudek — Fri, 14 Jan 2022 14:00:04 GMT

Can you try to generate that column earlier:

.withColumn("conditionColumn", to_date...

to simplify code so replaceWhere will be easier to debug as now it is complaining about to_date

Re: to_date not functioning as expected after introduction of arbitrary replaceWhere in Databricks 9.1 LTS

kolangareth — Fri, 14 Jan 2022 14:20:00 GMT

Hi @Hubert Dudek , to do that I must also add this new column in the target delta table, which I don't want to do. Adding this new column only in the dataframe to write will not work because of two reasons,

delta will give a schema mismacth error while writing.
I think the replaceWhere condition is basically applied on the target delta table and not on the input dataframe. So the condition should be on some columns which is already existing in the table.

Re: to_date not functioning as expected after introduction of arbitrary replaceWhere in Databricks 9.1 LTS

Hubert-Dudek — Fri, 14 Jan 2022 14:54:19 GMT

yes but you can at least do it for testing even with small copy of dataframe, this way is easier to find the problem

Re: to_date not functioning as expected after introduction of arbitrary replaceWhere in Databricks 9.1 LTS

Anonymous — Wed, 26 Jan 2022 16:32:41 GMT

@Prasanth Kolangareth - Does Hubert's answer resolve the problem for you? If yes, would you be happy to mark it as best so that other members can find the solution more quickly?

Re: to_date not functioning as expected after introduction of arbitrary replaceWhere in Databricks 9

ltreweek — Tue, 18 Feb 2025 22:01:11 GMT

SELECT TO_DATE('20250217','YYYYMMDD'); gives the error: PARSE_SYNTAX_ERROR syntax error at or near 'select'. sqlstate: 42601. It datagrip, it works no problem and displays the date.

Re: to_date not functioning as expected after introduction of arbitrary replaceWhere in Databricks 9

array-admiral — Thu, 06 Mar 2025 02:49:04 GMT

I'm not sure what runtime you're on, but if you're using a runtime with spark >= 3.0 this is a datetime pattern issue. Databricks SQL uses the following for datetime patterns Datetime patterns | Databricks Documentation in the latest runtimes.

In your case I believe you'd want to change your statement to the following:
SELECT TO_DATE('20250217','yyyyMMdd');