How-To: The IBM Informix REST API on the Raspberry Pi

[AlexKoe]

Hi,

I am sorry that it took some while before I could continue with my How-To series around IBM Informix and sensor data on the Raspberry Pi, but from time to time I need to work on some real and exciting sensor data projects for my employer.

Just in case you might have missed my other posts on how to use IBM Informix for sensor data management on the Raspberry Pi, here are those articles:

viewtopic.php?f=37&t=97199&p=674959
viewtopic.php?f=37&t=97772&p=678497
viewtopic.php?f=37&t=100029&p=693935

Today I would like to show you how to configure and how to enable the REST API for Informix. The current developer edition of Informix for the RPi (12.10.UC6DE) includes a Java based REST and MongoDB compliant listener. Although Informix's MongoDB compatibility is a very interesting topic in itself, I am focusing today on the REST API listener alone.

The Informix REST API provides an open, very flexible and secure interface for developers who would like to utilize Informix's sensor (time series) data and relational capabilities without the need to dig too deep into Informix's other client development APIs. The API supports GET, POST, PUT and DELETE operations.

Through the REST API Informix can be also very easily integrated into JavaScript based frameworks like Node.js, Node-RED or any kind of client tools and libraries which support REST calls. As a side note: I am currently working on a simple Informix TimeSeries plugin for Grafana 2.6 (http://grafana.org/). That plugin also utilizes the Informix REST API.

Ok, so let's get started.

Since the Informix REST listener is java based, we need to make sure that we do have a Java JRE or JDK installed on the Raspberry Pi:

Code: Select all

sudo apt-get install oracle-java8-jdk

Before we can start the REST listener, we need to define some listener settings in a properties file. The easiest way to do this is to create a copy of the existing listener example file ('$INFORMIXDIR/etc/jsonListener-example.properties') and name it e.g. restListener.properties:

Code: Select all

cp $INFORMIXDIR/etc/jsonListener-example.properties $INFORMIXDIR/etc/restListener.properties

Now you need to open the 'restListener.properties' file in an editor of your choice (e.g. vi or nano) and modify a few of the default properties in that file. I am now listing a few properties with values which I am typically using on my Raspberry Pis, but feel free to experiment with those values:

Code: Select all

url=jdbc:informix-sqli://localhost:9088/sysmaster:INFORMIXSERVER=ol_informix1210;USER=<your_db_user>;PASSWORD=<your_db_user_password>       # You shouldn't use user informix credentials here, if your REST listener is accessible from outside the Raspberry Pi due to potential security issues!
listener.hostName=*
database.share.close.enable=true 
listener.idle.timeout=3000
listener.port=<your desired port number for the REST API eg. 8080>
listener.http.accessControlAllowOrigin="*" 
listener.type=rest
pool.size.maximum=2
pool.connections.maximum=4
pool.type=advanced 
response.documents.count.default=10000     # or any desired number
pool.idle.timeout=60
security.sql.passthrough=true   # only set this property to true if you would like to use the SQL pass through feature!

As soon as you have modified your REST listener properties, you can start the REST listener with the following command:

Code: Select all

java -cp $INFORMIXDIR/bin/jsonListener.jar com.ibm.nosql.server.ListenerCLI -config $INFORMIXDIR/etc/restListener.properties -logfile $INFORMIXDIR/tmp/restListener.log -loglevel error -start

If you would like to start the REST listener in the background you could e.g. do the following:

Code: Select all

nohup java -cp $INFORMIXDIR/bin/jsonListener.jar com.ibm.nosql.server.ListenerCLI -config $INFORMIXDIR/etc/restListener.properties -logfile $INFORMIXDIR/tmp/restListener.log -loglevel error -start &> /dev/null & 

As soon as the listener is up and running, you can execute some simple REST GET operations against your sensor data through a web browser which is able to connect to the Informix REST listener port on the Raspberry Pi. In the following examples I assume that the 'listener.port' property has been set to '8080'. Also: in my examples I am using 'localhost' as the hostname. Please adjust that value to your specific Raspberry Pi hostname or IP address.

With the following example URL I am fetching all time series data from the VTI table 'sensor_data':

Code: Select all

http://localhost:8080/sensor_db/sensor_data

Now limit the result set to only one primary key (one time series) with the following URL:

Code: Select all

http://localhost:8080/sensor_db/sensor_data?query={"sensor_id":"Sensor01"}

If you only want to retrieve specific columns from your VTI table (or any other relational table in Informix), just use the fields operator:

Code: Select all

http://localhost:8080/sensor_db/sensor_data?query={"sensor_id":"Sensor01"}&fields={"timestamp":1,"value":1}

BTW, time stamps returned are epoch time stamps (milliseconds since 01/01/1970) in UTC.
Ok now let's provide a slightly more complex query through the REST API by providing a time stamp range:

Code: Select all

http://localhost:8080/sensor_db/sensor_data?query={"$and": [{"sensor_id":"Sensor01"},{"timestamp":{"$gte": {"$date":1289347200000}}},{"timestamp":{"$lt": {"$date":1297122300000}}} ]}&fields={"timestamp":1,"value":1}

Alternatively to the numeric representation of the epoch time stamps in the query conditions, you can also use the following JSON time stamp string format:

Code: Select all

http://localhost:8080/sensor_db/sensor_data?query={"$and": [{"sensor_id":"Sensor01"},{"timestamp":{"$gte": {"$date":"2016-02-24T15:00:00.000Z"}}},{"timestamp":{"$lt": {"$date":"2016-02-24T15:30:00.000Z"}}} ]}&fields={"timestamp":1,"value":1}

The SQL pass-through support in the Informix REST listener
If you have set the listener property 'security.sql.passthrough=true' (again, be careful in doing so!), you can send any SQL command to the Informix database. In the following example I am sending a SELECT statement through the REST API to Informix which returns the last entry of a time series:

Code: Select all

http://localhost:8080/sensor_dbsystem.sql?query={"$sql":"select GetLastElem(sensor_values).value from sensor_ts where sensor_id = 'Sensor01'"}

BTW that Informix built-in time series function 'GetLastElem()' can be quite handy if you would like to display/visualize the most recent stored sensor data value.

I think by now you should hopefully have enough information to get you started with the Informix REST API.

Since Informix's REST API is much more powerful than I could have described in such a short introductionary article, I would recommend that you take a look at the following online documentation:

• REST API Syntax: https://www-01.ibm.com/support/knowledg ... on_051.htm
• REST API and time series data queries: https://www-01.ibm.com/support/knowledg ... on_065.htm
• General information about the Informix listener: https://www-01.ibm.com/support/knowledg ... on_004.htm

In addition to the online documentation there are also some helpful presentations available on slide share:

• http://www.slideshare.net/IBM_Info_Mana ... h-informix
• http://www.slideshare.net/IBM_Info_Mana ... s-solution

Informix TimeSeries Node-RED nodes
Since I briefly mentioned Node-RED in the intro of this post, let me also point you to a set of Informix Node-RED time series nodes which utilize the Informix REST API:
http://flows.nodered.org/node/node-red- ... timeseries

I am using those nodes quite often on my Raspberry Pi to pre-aggregate sensor data before they are sent into the cloud.

And finally: have fun using the Informix REST API in combination with the Informix time series capabilities!

- Alexander

[mikeoakley123]

Having a weather station La Crosse Technology WS2310 for several years, I used the software provided by La Crosse Technology since there were no other real alternatives. Developped for Windows 2000/XP, these software have stopped to work on Windows 7: the drivers for the USB/RS232 adapter provided with the weather station to communicate between the computer and station did not exist.

Moreover it was necessary to connect the station to a computer to transfer the data saved in the buffer of the weather station before this was filled. Capturing the data every 30mn fills the buffer station after one week: a vacation week and data are lost.

Another issue is computer and weather station is not located in the same room. So it was necessary to move the station to connect to the computer. The step to download the data must be done in 30mn else again data will be lost.

With the development of Raspberry Pi, it is now possible to get a real server connected directly to the weather station and so, to download in real time the data. Some API exists on Raspbian to connect the weather station.

Regarding data analysis, it is now possible to upload the data from the Raspberry Pi to some web sites like Weather Underground (www.wunderground.com). On these, it is really easy to print the data but analysis is tricky.

Working at IBM, it is obvious that Informix technology is the answer to handle Timeseries data and to do analysis.
Hardware

I used the following hardware:

Weather station La Crosse Technology WS 2310
Raspberry Pi 1B+ with Raspbian 7.5 (wheezy)
Adapter USB/RS232 (DB9). Like we can read on other articles on internet, there is a compatibility issue between the adapter supplied with the weather station and the Raspberry Pi. To avoid any issue, I use a adapter with USB Prolific PL2303 chipset...OAKLEY Construction