CODEWITHSUNDEEP
fiwarefiware-orionfiware-cygnus
Vrankela
Vrankela

Reputation: 1202

How to balance out row mode and column mode in cygnus?

I have a weather-station that transmits data each hour. During that hour it makes four recordings (one every 15 minutes). In my current situation I am using attr_persistence=row to store data in my MySql database.

With row mode I get the default generated columns: 

recvTimeTs | recvTime | entityId | entityType | attrName | attrType | attrValue | attrMd

But my weather station sends me the following data:

 |   attrName    | attrValue

   timeRecorded   14:30:0,22.5.2015
   measurement1   18.799
   measurement2   94.0
   measurement3   1.19

These attrValue are represented in the database as string.

Is there a way to leave the three measurements in row mode and switch the timeRecorded to column mode? And if not, then what is my alternative?

The point of all this is to query the time recorded value, but I cannot query date as long as it is string.

As a side note: having the weather station send the data as soon as it is recorded (every 15 minutes) is out of the question, firstly because I need to conserve battery power and more importantly because in the case of a problem with the post, it will send all of the recordings at once.

So if an entire day went without sending any data, the weather station will send all 24*4 readings at once...

Upvotes: 2

Views: 68

Answers (1)

frb
frb

Reputation: 3798

The proposed solution is to use the STR_TO_DATE function of MySQL in order to translate the stored string-based "timeRecorded" attribute into a real MySQL Timestamp type.

Nevertheless, "timeRecorded" attribute appears every 4 rows in the table due to the "row" attribute persistence mode of OrionMySQLSink. In this case I think you can use the ROWNUM keyword from MySQL in order to get only every 4 rows, something like (not an expert on MySQL):

SELECT STR_TO_DATE( attrValue, '%m/%d/%Y' ) FROM def_servpath_0004_weatherstation where (ROWNUM / 4 = 0);

The alternative is to move to "column" mode (in this case you have to provision de table by yourself). By using this mode you will have a single row with all the 4 attributes, being one of these attributes the "timeRecorded" one. In this case, you can provision the table by directly specifying the type of the "timeRecorded" column as Timestamp, instead of Text. That way, you will avoid the STR_TO-DATE part.

Upvotes: 1

Related Questions