Search code examples
azure-data-factory

Epoch time child in json file detected as Integer (in Azure Data Factory) instead of long

I have a json file contining a unix time field which needs to be converted to a timestamp. However the issue I am having in Dataflow is that the field is automatically detected as INTEGER type instead of LONG type. This means that any subsequent calculation (to the Source) will basically fail since it will be performed on an integer instead of a long.

SAMPLE JSON

    {
  "summaryInfo": {
    "expectedReadings": 25,
    "totalReadings": 25,
    "missingReadings": 0,
    "filteredReadings": 0,
    "statusCounts": {
      "OK": 25,
      "ESTIMATED": 0,
      "INTERPOLATED": 0,
      "INCOMPLETE": 0,
      "MISSING": 0,
      "FILTERED": 0
    },
    "sum": 425.83689444450192,
    "minReading": {
      "time": 1698418800000,
      "value": 2.427360000025041,
      "status": "OK"
    },
    "maxReading": {
      "time": 1698404400000,
      "value": 37.6868022222566,
      "status": "OK"
    },
    "standardDeviation": 16.233979824375712,
    "mean": 17.033475777780076
  },
  "units": {
    "tag": "KWH",
    "name": "Kilowatt hour",
    "prefix": "",
    "suffix": "kWh"
  },
  "resolution": "HOUR",
  "readings": [
    {
      "time": 1698364800000,
      "value": 2.5600888889447218,
      "status": "OK"
    },
    {
      "time": 1698368400000,
      "value": 2.67688222213105,
      "status": "OK"
    },
    {
      "time": 1698372000000,
      "value": 2.6339666667448669,
      "status": "OK"
    },
    {
      "time": 1698375600000,
      "value": 7.609269999917208,
      "status": "OK"
    }
    
  ],
  "_links": {
    "meter": {
      "href": "https://api.xxxx.com/24863"
    }
  }
}

The field I am trying to extract is in the array "readings" - field "time". However no matter what I do it's always detected as INTEGER instead of LONG.

Edit: this is the field I want changed - overwrite schema snapshot

Thank you!

Solution

  • You can change the type of time subfield manually. Below are the steps to do.

    • Click Projection tab in Source transformation
    • Click Import Projection

    gif1

    • The data type of time subfield under readings will be changed to long type. Initial assumption of type integer is based on first few rows only. Thus importing projection will change the type to long