topic Re: Unable to read a table with invalid dates using SQL endpoint in Data Warehouse

Unable to read a table with invalid dates using SQL endpoint

Anonymous — Mon, 20 May 2024 04:48:15 GMT

Hi There,
We have some source system which treat NULL values as '0001-01-01' and some dates are before '1900-01-01'. When we try to read such data written using lakehouse notebook via SQL endpoint, we are getting unable to run query error. Has anyone faced this issue and overcome it.

I have attached a sample notebook which reproduces the issue.

from pyspark.sql.functions import * from pyspark.sql.types import DateType spark.conf.set("spark.sql.parquet.int96RebaseModeInRead", "LEGACY") spark.conf.set("spark.sql.parquet.int96RebaseModeInWrite", "LEGACY") spark.conf.set("spark.sql.parquet.datetimeRebaseModeInRead", "LEGACY") spark.conf.set("spark.sql.parquet.datetimeRebaseModeInWrite", "LEGACY") new_df = spark.createDataFrame([('0065-04-01',),('0001-01-01',),('1993-03-11',)], ['COVER_END_DATE']) new_df.select(col('COVER_END_DATE')).show() spark.sql(f"CREATE TABLE IF NOT EXISTS test_date_issue2(COVER_END_DATE DATE) using DELTA LOCATION 'Tables/test_date_issue2'") new_df.createOrReplaceTempView("TARGET_VIEW") spark.sql(f"INSERT INTO test_date_issue2 SELECT * FROM TARGET_VIEW")

Thanks,

Chetna

Re: Unable to read a table with invalid dates using SQL endpoint

Anonymous — Mon, 20 May 2024 09:14:32 GMT

Hi Anonymous ,

Thanks for using Fabric Community.
At this time, we are reaching out to the internal team to get some help on this .
We will update you once we hear back from them.

Re: Unable to read a table with invalid dates using SQL endpoint

Anonymous — Tue, 21 May 2024 06:07:08 GMT

Hi Anonymous ,

Apologize for the issue you are facing. We can confirm that we are also observing the same issue at our end.

The best course of action is to open a support ticket and have our support team take a closer look at it.

Please reach out to our support team so they can do a more thorough investigation on why this it is happening: Link

After creating a Support ticket please provide the ticket number as it would help us to track for more information.

Hope this helps. Please let us know if you have any other queries.

Re: Unable to read a table with invalid dates using SQL endpoint

Anonymous — Wed, 22 May 2024 09:17:17 GMT

Hi Anonymous ,

We haven’t heard from you on the last response and was just checking back to see if you got a chance to open a support ticket.
After creating a Support ticket please provide the ticket number as it would help us to track for more information.

Thanks

Re: Unable to read a table with invalid dates using SQL endpoint

Cosmic_Panda — Mon, 23 Sep 2024 14:20:24 GMT

Hi,

Did you manage to get this resolved?

I'm having the same issue.

Thanks