Hi,

What are you getting from a query, and which query are you performing?

Note that you should probably not FDM as a time series database, but rather use the time series database we do provide.

Hi Anders,

Thanks for your reply. In the Graphql query, I am using getDeviceById, giving externalId for specific device and referring particular sensor through filtering  present in that device and selecting timestamp and value. That gives me all timestamps and values. The expectation is to get only latest timestamp and value.

sample query:

query MyQuery {
  getDeviceById(
    instance: {spaceExternalId: "data_model_new", externalId: "aa21ba4e2a804aeaa3b954fa057b8990"}
  ) {
    edges {
      node {
        name
        externalId
        sensors(filter: {name: {eq: "s1"}}) {
          edges {
            node {
              externalId
              name
              timestamp
              value
            }
          }
        }
      }
    }
  }
}

On this comment- “Note that you should probably not FDM as a time series database, but rather use the time series database we do provide” . Can you please elaborate more. Can we use CDF native TimeSeries object in FDM data model type? 

As of now, I am extracting data from source and ingesting into RAW tables through DB extractor. Then using transformation, populating the data in FDM model. I have created user defined type for timeseries (as Sensor) as I thought FDM doesn’t support native TimeSeries directly. Please let me know if I am missing anything 

I see! So it is possible to use TimeSeries as a type in FDM, but we are finalizing the details, so it is up for change still. However, if you today use TimeSeries as a type, for instance

type Device {
  name: String
  sensor: TimeSeries
}

you can ingest data where you would use the externalId for the time series as a string field in your SQL transformation. Then you can query it and get the latest data point with

{
  getDeviceById(instance: {externalId:"abc", spaceExternalId: "abc"}) {
    items {
      name
      sensor {
        dataPoints(start: ...) {
          timestamp
          value
        }
      }
    }
  }
}

We haven’t exposed lastDataPoint, but that will come in H1 2023. With the new APIs, the story might be slightly different, but will keep this thread up to date on the topic.

Hi Anders,

Thanks for your reply. when we do the sql transformation for Device, in the transformation window I see sensor type as Array<String>: nullable 

sql query for CDF timeseries destination type as follows for creating timeseries records:

select concat(ID,":",SensorName,":",key) as name, concat(ID,":",SensorName,":",key) as externalId,false as isString, 248572714504585 as dataSetId from `devicedb`.`devices`

This has created timeseries records having externalId like:

device1:s1:row1

device1:s2:row2

sql query for Device type transformation as follows:

select array(cdftimeseries.externalId) as sensor, device.NAME as name, device.ID as externalId from `devicedb`.`devices` device left join _cdf.timeseries as cdftimeseries on device .ID=substring_index(cdftimeseries.externalId, ':', 1) where cdftimeseries.dataSetId=248572714504585

Do you think this looks ok? I wanted to confirm if I am referring timeseries externalId correctly in device type transformation.

Hi Anders,

Please ignore above query of device type and see below query for Device type as for above I was getting duplicate external id error:

sql query for Device type transformation as follows:

select array(cdftimeseries.externalId) as sensor, device.NAME as name, concat(device.ID, ":",substring_index(cdftimeseries.externalId, ':', -1)) as externalId from `devicedb`.`devices` device left join _cdf.timeseries as cdftimeseries on device .ID=substring_index(cdftimeseries.externalId, ':', 1) where cdftimeseries.dataSetId=248572714504585

considering externalId for Device is for example : device1:row1

Not sure I follow the question here, but as long as you put in the externalId of a time series, it should be available for querying in your data model. 

Hi Anders,

For this query:

query MyQuery {
  getEntityById(
    instance: {spaceExternalId: "device_data_model_new", externalId: "4b7a198df8b14a52b7f0386d595a18f9:8881e9bc3bb24b62895526e4d0c7a9b1"}
  ) {
    edges {
      node {
        name
        sensors{
          dataPoints {
            timestamp
            value
          }         

         externalId
        }
      }
    }
  }
}

I see below error:

{
  "errors": >
    {
      "message": "Cannot read properties of undefined (reading 'split')",
      "stack": "TypeError: Cannot read properties of undefined (reading 'split')\n    at splitLinesAuto (https://slb.fusion.cognite.com/apps/cdf-solutions-ui/v.eedfcff5a199bd41b940646351c42488cae36052/vendors-node_modules_graphiql_react_dist_codemirror_es_js.js:773:21)\n    at Object.splitLines (https://slb.fusion.cognite.com/apps/cdf-solutions-ui/v.eedfcff5a199bd41b940646351c42488cae36052/vendors-node_modules_graphiql_react_dist_codemirror_es_js.js:7071:16)\n    at Object.<anonymous> (https://slb.fusion.cognite.com/apps/cdf-solutions-ui/v.eedfcff5a199bd41b940646351c42488cae36052/vendors-node_modules_graphiql_react_dist_codemirror_es_js.js:6611:22)\n    at Object.setValue (https://slb.fusion.cognite.com/apps/cdf-solutions-ui/v.eedfcff5a199bd41b940646351c42488cae36052/vendors-node_modules_graphiql_react_dist_codemirror_es_js.js:4420:20)\n    at CodeMirror2.setValue (https://slb.fusion.cognite.com/apps/cdf-solutions-ui/v.eedfcff5a199bd41b940646351c42488cae36052/vendors-node_modules_graphiql_react_dist_codemirror_es_js.js:10946:27)\n    at setResponse (https://slb.fusion.cognite.com/apps/cdf-solutions-ui/v.eedfcff5a199bd41b940646351c42488cae36052/vendors-node_modules_graphiql_plugin-explorer_dist_graphiql-plugin-explorer_es_js-node_module-0c6fd2.js:21405:22)\n    at handleResponse (https://slb.fusion.cognite.com/apps/cdf-solutions-ui/v.eedfcff5a199bd41b940646351c42488cae36052/vendors-node_modules_graphiql_plugin-explorer_dist_graphiql-plugin-explorer_es_js-node_module-0c6fd2.js:21499:11)\n    at https://slb.fusion.cognite.com/apps/cdf-solutions-ui/v.eedfcff5a199bd41b940646351c42488cae36052/vendors-node_modules_graphiql_plugin-explorer_dist_graphiql-plugin-explorer_es_js-node_module-0c6fd2.js:21547:9"
    }
  ]
}

If I don’t refer timeseries in query, the query works fine, for example:

query MyQuery {
  getEntityById(
    instance: {spaceExternalId: "device_data_model_new", externalId: "4b7a198df8b14a52b7f0386d595a18f9:8881e9bc3bb24b62895526e4d0c7a9b1"}
  ) {
    edges {
      node {
        name
      }
    }
  }
}

This gives me correct result. Looks something is missing while transformation to timeseries record. 

This is how I am doing the transformation.

select array(cdftimeseries.externalId) as sensors from _cdf.timeseries as cdftimeseries where cdftimeseries.dataSetId=248572714504585     (removed other columns just to show you only mapping to sensors)

Model:

type Device

{

name: String,

sensors: tTimeSeries]

}

Can you please let me know what is missing?

When I use following model, I can get data points: (TimeSeries is not List). When I use TimeSeries as a List in Device, then I see above error. May be I need to create my own type which has property as CDF TimeSeries resource type and refer that type as List in Device. I am checking that if that works. 

type Device

{

name: String,

sensors: TimeSeries

}

Hi,

If I create following model then List of timeseries work. Looks like, we can’t probably refer List of CDF native TimeSeries directly and has to be wrapped in another type. Hope my understanding is correct.  

type Device

{

name: String,

sensors: oNewTimeSeries]

}

type NewTimeSeries

{

timeseriesrecord: TimeSeries

}

You will be able to list native time series directly as if it was your own type, so unless you want to add extra metadata, you shouldn’t have to do that.