How to make ThingWorx data available for external analytics tools

[Do1]

The ThingWorx persistence provider may be a SQL database, but it is most certainly not a good idea to try and access the time series data with a DB tool to run analytics on it.

What would be the best mechanism to make data available for external analytics tools to analyse data acquired by ThingWorx?

[PaiChung · Accepted solution]

If it is large amounts, I would create a service that pulls the data and saves it as a CSV in a file repository which can then be downloaded.

if you want, you could even do things like that on the persistence provider side as well, however the data is all stored as JSON so you'd have to create some transformation somewhere.

If it isn't too much data, your service can just return that data as an infotable and be called using a rest api call, or use the data export widget in a mashup

[Do1]

Not a great solution, but I guess it is better than nothing..

[rjanardan]

Since you have mentioned time series data. I just wanted to add some more information. ThingWorx also supports InfluxDB as a persistence provider for storing time series data. It can handle high write and query loads. Currently only the high read and write throughput of InfluxDB is being exploited directly in ThingWorx.  Implementing more features is in pipeline. 
However, if you are fine with directly interacting with DB, then you could use  InfluxDB API /query HTTP endpoint to take advantage of its other advanced features specific to time series data.

As long as you are careful not to corrupt data, it should be possible to visualize this data using Chronograf or Grafana and even analyse it using Kapacitor. 

[Do1]

Thanks rjanardan,

This seems like an interesting approach, unfortunately corporate guidelines determined that the persistence provider MUST be MSSQL.

Not the best idea as far as I am concerned.