Azure Data Factory (ETL) from MongoDB to Azure Synapse Anlytics - Date Conversion

user_10096169 0
Viewed 123

I am building ETL pipeline using Azure DataFactory from MongoDB to Azure Synapse Anlytics.

This is my sample document

{
    "_id" : ObjectId("62b6f74ce3a7b8ee664c675d"),
    "event_name" : "click_search",
    "screen_name" : "home",
    "product_sku" : "",
    "screen_id" : "5005",
    "action_id" : "1023",
    "created_on" : ISODate("2022-06-25T16:53:48.000+05:00")
}

I used simple Copy Data activity and then preview data for source (MongoDB) is showed me like this

{
    "_id": {
      "$oid": "62b6f74ce3a7b8ee664c675d"
      },
    "event_name": "click_search",
    "screen_name": "home",
    "product_sku": "",
    "screen_id": "5005",
    "action_id": "1023",
    "created_on": {
      "$date": 1656158028000
       }
}

My First question is why date is coming in different format in datafactory?

Secondly

while mapping it show me like this enter image description here

due to this when I ran pipeline it gives this error

    Message=Column 'created_on' contains an invalid value '1656158028000'. Cannot convert 
    '1656158028000'to type 'DateTime'.,Source=Microsoft.DataTransfer.Common,
    ''Type=System.FormatException,Message=String was not recognized as a valid 
    DateTime.,Source=mscorlib,'

I tried to find this issue in documentation Copy data from or to MongoDB using Azure Data Factory or Synapse Analytics

but didn't get any luck, please let me know what's happening under the hood? Why MongoDB send date like this and how can we resolve it. Thank you.

azure-synapse azure-data-factory mongodb etl azure
1 Answers

MongoDB stores date as BSON date

BSON Date is a 64-bit integer that represents the number of milliseconds since the Unix epoch (Jan 1, 1970). This results in a representable date range of about 290 million years into the past and future.

For your case, you can use toUTC to convert UNIX timestamp to proper date in ADF. One thing to note is that MongoDB stores unix timestamp in milliseconds, so you may need to divide by 1000 before converting to UTC date.

user_147326690
Related