Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
5,123 questions
Synapse - spark table over CSV - date not matching file
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(224.00000000000003, 77%, 31%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ERA%3C/text%3E%3C/svg%3E)
Ryan Abbey
1,181
Reputation points
We are seeing a really odd issue... we have a Spark table configured to read a CSV file, for a particular row in the file, we have a date column which is set to "2023-05-02T21:27:34.3299026Z". If we query that row via the table via Synapse Serverless, we get "2023-05-02 21:27:34", however, when querying that exact same table from within a Notebook session is giving us "2023-05-02T22:22:33Z"
The time discrepancy appears to vary across the records, anyone with any ideas as to how they are being read differently?
{count} votes
-
AnnuKumari-MSFT 33,986 Reputation points Microsoft Employee2023-05-04T07:15:26.4133333+00:00 Hi Ryan Abbey ,
Thankyou for using Microsoft Q&A platform and thanks for posting your query.
I understand you are seeing discrepancies in the way date column is storing the data in notebook and in serverless table in synapse. Please let me know if that is not the correct understanding.
Could you please share the query you are using to fetch the data from serverless sql? Are you using openrowset or creating an external table?
It might be occuring due to the difference in default timezone settings in Synapse Serverless and the Notebook session.
Please try to match the timestamp explicitly by casting it to the required format. You can use to_timestamp() function from the pyspark library in your notebook and define the format .
df.select(to_timestamp(lit(column),'yyyy-MM-dd HH:mm:ss.SSSS')) \ .show()For more details, kindly checkout the below posts:
-
Ryan Abbey 1,181 Reputation points
2023-05-04T21:58:11.6033333+00:00 It's a Spark table so created via
using CSV LOCATIONThe field itself is defined as a timestamp
However, we are currently going through a Spark 3.3 upgrade and this does not appear to be showing for Spark 3.3 (we are currently on 2.4) so looks to be an issue in the older version
-
AnnuKumari-MSFT 33,986 Reputation points Microsoft Employee
2023-05-05T07:42:50.02+00:00 Hi Ryan Abbey ,
Thanks for sharing the updates. As you mentioned the issue might be cause due to the older version of spark being used.
Kindly checkout the following thread to know How to upgrade Spark version in Synaspe?
Please let us know if this helps in resolving the issue . Thanks
Sign in to comment
Sign in to answer