CODEWITHSUNDEEP
jsontimestampazure-synapse
siddarfer
siddarfer

Reputation: 262

unable to load from timestamp json field to synapse data lake table timestamp column

I've been trying and trying in Azure Synapse Studio to load my json data from ADLS to an Azure data lake table, and the String and Int columns load fine, but not the Timestamp column. It previews as NULL in the data flow output stream, if I let the pipeline run it writes it back out to ADLS as an empty string, and if I then query the table it shows up as (NULL). If I change the column data type to String it works fine, so it's specific to the timestamp datatype.

A sample JSON file looks like:

[{
    "mold_id": "sensor1",
    "time_utc": "2022-04-08T15:34:16Z",
    "temperature": 80
}, {
    "mold_id": "sensor1",
    "time_utc": "2022-04-10T15:34:16Z",
    "temperature": 85
}]

and the column is defined as "Data type: timestamp" and "Format/Length: yyyy-MM-ddTHH:mm:ssZ"

Any ideas on how to get this to work?

Upvotes: 1

Views: 2241

Answers (1)

Saideep Arikontham
Saideep Arikontham

Reputation: 6124

Azure synapse does not support the timestamp datatype. It works when the column is defined as date (yyyy-mm-dd) because it is a supported type. However, when you have a timestamp column, you can use cast the column to datetime type which is supported by azure synapse.

There are several other date and time datatypes that are supported by Azure synapse analytics. You can refer to the following link to know more about these types.

https://learn.microsoft.com/en-us/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql?view=sql-server-ver15

The datatype that might work for you would be datetime or datetime2. You can refer to the following links to know more about these datatypes.

datetime:

https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver15

datetime2:

https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql?view=sql-server-ver15

Here is a document which references the unsupported datatypes (Refer section- Workarounds for unsupported datatypes).

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-data-types

Upvotes: 2

Related Questions