Search code examples
cloudantdashdbcloudant-sdp

how to 'flatten' the table in dashDB created by the schema discovery process (SDP)?

I've used the Cloudant schema discovery process (SDP) to create and populate a table in dashDB. The data in Cloudant is time series in nature:

...
{ "date": "20150101T00:00:00", "type": "temperature", "reading": "21" }
{ "date": "20150101T00:00:00", "type": "windspeed", "reading": "15" }
{ "date": "20150101T00:00:10", "type": "airhumidity", "reading": "51" }
{ "date": "20150101T00:00:10", "type": "temperature", "reading": "22" }
...

When this data is pushed into dashDB, it maintains a similar structure, i.e.

DATE              | TYPE          | READING
------------------+---------------+---------
20150101T00:00:00 | temperature   | 21
20150101T00:00:00 | windspeed     | 15
20150101T00:00:10 | airhumidity   | 51
20150101T00:00:10 | temperature   | 22

However, I would like this data to be in a 'flatter' structure, i.e.

DATE              | TEMPERATURE   | WINDSPEED    | AIRHUMIDITY
------------------+---------------+--------------+-------------
20150101T00:00:00 | 21            | 15           | -
20150101T00:00:10 | 22            | -            | 51

How can I flatten the SDP populated data?

Solution

  • One option is to create a Bluemix service that runs SQL code on a cron timer to move the data around into your desired structure.

    See here for an example project using spring boot and groovy.