November 30, 2015

Coming up with a Hybrid Database Implementation model to fulfil a TimeSeries + NoSQL requirement


One of the previous blog works 'Simple JSON / BSON Implementation' demonstrated how simple it is to call JSON / BSON datatypes as part of the schema definition and implement a NoSQL setup to handle unstructured datasets.

Let us take another step to help understand the implementation of JSON / BSON types in a TimeSeries environment, that deals with time series datasets that are both structured and unstructured in nature. 

Consider a scenario in Sensor Data Management, where the setup has 'n' number of sensors, sending data at various intervals ( fixed or irregular intervals ). It's straight forward to create a TimeSeries table, provided we have all relevant information in hand. Now, if the number of sensors and the number of values / data elements that each sensor shares, varies over time ( either increase or decrease ), then, having a fixed schema will pose good amount of challenges. 

That's where, having a JSON / BSON implementation within TimeSeries, enables IBM Informix to tackle the challenges of Sensor Data Management, with relative ease and prove that IBM Informix a True Hybrid Database Server.

The following shell script has been designed to showcase the flow, that helps understand the basic objects of TimeSeries viz. Calendar, Container, TimeSeries, Virtual Table Interface (VTI) and SQL API, while working with Hybrid Database model. It takes no more than 10min to run the shell script and understand Informix TimeSeries syntax.

1.      The shell script is well tested on Linux and might need minor changes on Unix platform. It won’t work on Windows platform. However, you can copy paste SQL statements high lighted in blue in following section and execute on windows manually.
2.      Ensure that Informix engine is online. You can use “onstat –“  command
3.      Download the shell script available in the zip file ( Or Copy - Paste the following link on to the Browser:
https://drive.google.com/open?id=0B4mxiMcgUauaMENkNVdxZXJRQlU )
4.      Unzip the file on linux/Unix machine under some test directory
5.      You might need to execute dos2unix command for each file before execution
6.      Ensure that 'main.sh' and 'func.sh' have Execute Privileges
7.      Execute main.sh and just follow the instructions.

Output of shell script should look like this :

################################################################
Welcome to TimeSeries demo.
This program has sections that demonstrate capabilities of TimeSeries.
Flow in each section is Setup, Data Load & Data Retrieval
please enter your choice of TimeSeries demo
 1      : TimeSeries Demo
 2      : JSON Demo
 3      : TimeSeries with JSON Demo
 4      : TimeSeries with Rolling Window Concept
 5      : TimeSeries with Hertz Frequency Concept
 6      : TimeSeries with Swinging Door Compression Algorithm

3

################################################################
 You have chosen a Demo on Informix TimeSeries with NoSQL
################################################################

Please read the options carefully :-
If you are executing the TimeSeries Demo for the first time, then
        Press '1'       : To Perform the Setup and create the Demo Environment

If you have already performed the setup for TimeSeries Demo, then
        Press ANY NUMBER: To Execute Queries against the prepared Setup

1

Cleaning up old files...
Clean up is over. Please presss Enter to continue

################################################################
Creating dbspace dbspace1

execute function admin('create dbspace','dbspace1',
'/home/amprasan/IDS1210/storage/dbspace1','100 MB','0')
45107280         6        0x60001    6        1        2048     N  BA    informix dbspace1
471ba028         6      6      0          10000      7787                  PO-B-D /home/amprasan/IDS1210/storage/dbspace1

dbspace creation .. passed

Please press Enter to continue

################################################################
Creating database demo_db

drop database if exists demo_db;

create database demo_db in dbspace1 with buffered log

################################################################
Creating row type row1

create row type row1
(
 time_stamp datetime year to fraction(5),
 value bson
);

Row creation .. passed
Please press Enter to continue

################################################################
Creating calendars calsec, cal1min and cal15min

execute procedure ifx_allow_newline('t');
insert into calendarpatterns values
(
 'sec','{1 on},second'
);
insert into calendarpatterns values
(
 'min','{1 on},minute'
);
insert into calendarpatterns values
(
 'min15','{1 on ,14 off},minute'
);
insert into CalendarTable
(
 c_name,c_calendar
)
values ('calmin','startdate(2015-07-01 00:00:00.00000),
pattstart(2015-07-01 00:00:00.0000),pattname(min)');

insert into CalendarTable
(
 c_name,c_calendar
)
values ('calsec','startdate(2015-07-01 00:00:00.00000),
pattstart(2015-07-01 00:00:00.0000),pattname(sec)');

insert into CalendarTable
(
 c_name,c_calendar
)
values ('cal15min','startdate(2015-07-01 00:00:00.00000),
pattstart(2015-07-01 00:00:00.0000),pattname(min15)');

Calendar creation .. passed
Please press Enter to continue

################################################################
Creating table test_ts

create table test_ts
 (
   tagid char(20),
   series timeseries(row1),
   PRIMARY KEY (tagid)
) in dbspace1;
alter table test_ts
lock mode(row);

Table creation .. passed
Please press Enter to continue

################################################################
Creating container tscont1

execute procedure tscontainercreate('tscont1',
'dbspace1','row1',1024,1024);

Container creation .. passed
Please press Enter to continue

################################################################
Creating virtual table test_v

execute procedure TSCreateVirtualTab('test_v','test_ts');

Virtual table creation .. passed
Please press Enter to continue

################################################################
Initializing all tags tag1 to tag5

BEGIN;
insert into test_ts values('tag1','calendar(calsec),origin(2015-07-01 00:00:00.00000),container(tscont1),threshold(0),irregular,[]');
insert into test_ts values('tag2','calendar(calsec),origin(2015-07-01 00:00:00.00000),container(tscont1),threshold(0),irregular,[]');
insert into test_ts values('tag3','calendar(calsec),origin(2015-07-01 00:00:00.00000),container(tscont1),threshold(0),irregular,[]');
insert into test_ts values('tag4','calendar(calsec),origin(2015-07-01 00:00:00.00000),container(tscont1),threshold(0),irregular,[]');
insert into test_ts values('tag5','calendar(calsec),origin(2015-07-01 00:00:00.00000),container(tscont1),threshold(0),irregular,[]');
COMMIT;

Tags initialization .. passed
Please press Enter to continue

################################################################
Load data for Tags 1 to 5 through VTI

dbaccess demo_db load_bson.sql

Loading data through VTI .. passed
Please press Enter to continue

Congratulations!!! Your setup is successful. Please press Enter to refresh the screen and be ready to see certain data retrieval queries

################################################################
Simple select on VTI table to see range of data

select * from test_v
where tagid='tag1'
and time_stamp between '2015-07-01 01:00:00.00000' and
'2015-07-01 01:10:00.00000';

Press enter to see output

Database selected.

tagid       tag1
time_stamp  2015-07-01 01:00:00.00000
value       Q
            sure

tagid       tag1
time_stamp  2015-07-01 01:01:00.00000
value       Q
            sure

tagid       tag1
time_stamp  2015-07-01 01:02:00.00000
value       Q
            sure

tagid       tag1
time_stamp  2015-07-01 01:03:00.00000
value       Q
            sure

tagid       tag1
time_stamp  2015-07-01 01:04:00.00000
value       Q
            sure

tagid       tag1
time_stamp  2015-07-01 01:05:00.00000
value       Q
            sure

tagid       tag1
time_stamp  2015-07-01 01:06:00.00000
value       Q
            sure

tagid       tag1
time_stamp  2015-07-01 01:07:00.00000
value       Q
            sure

tagid       tag1
time_stamp  2015-07-01 01:08:00.00000
value       Q
            sure

tagid       tag1
time_stamp  2015-07-01 01:09:00.00000
value       Q
            sure

tagid       tag1
time_stamp  2015-07-01 01:10:00.00000
value       Q
            sure

11 row(s) retrieved.

Database closed.

Press enter to continue

################################################################
Unable to read through the JSON / BSON Document? Let's try again!
Simple select on VTI table to see range of data

select tagid, time_stamp, value::json series from test_v
where tagid='tag1'
and time_stamp between '2015-07-01 00:02:00.00000' and
'2015-07-01 00:04:00.00000';

Press enter to see output

Database selected.

tagid       tag1
time_stamp  2015-07-01 00:02:00.00000
series      {"lon":50.23487,"lat":4.87345,"temp":38.453,"humidity":20.235,"pressure":100}

tagid       tag1
time_stamp  2015-07-01 00:03:00.00000
series      {"lon":50.36746,"lat":5.12643,"temp":37.237,"humidity":21.344,"pressure":101}

tagid       tag1
time_stamp  2015-07-01 00:04:00.00000
series      {"lon":51.46758,"lat":4.87345,"temp":38.947,"humidity":22.137,"pressure":102}

3 row(s) retrieved.

Database closed.

Press enter to continue

################################################################
Simple select on TimeSeries table to see range of data

select 'tag1',
clip(series,'2015-07-01 00:02:00.00000'::datetime year to fraction(5) ,
'2015-07-01 00:08:00.00000'::datetime year to fraction(5))
from test_ts where tagid='tag1';

Press enter to see output

Database selected.

(constant)    tag1
(expression)  origin(2015-07-01 00:02:00.00000), calendar(calsec), container(tscont1), threshold(0), irregular, [('{"lon":50.23487,"lat":4.87345,"temp":38.453,"humidity":20.235,"pressure":100}')@2015-07-01 00:02:00.00000, ('{"lon":50.36746,"lat":5.12643,"temp":37.237,"humidity":21.344,"pressure":101}')@2015-07-01 00:03:00.00000, ('{"lon":51.46758,"lat":4.87345,"temp":38.947,"humidity":22.137,"pressure":102}')@2015-07-01 00:04:00.00000, ('{"lon":51.98765,"lat":4.36746,"temp":37.846,"humidity":20.936,"pressure":103}')@2015-07-01 00:05:00.00000, ('{"lon":52.12643,"lat":4.98727,"temp":38.376,"humidity":21.049,"pressure":104}')@2015-07-01 00:06:00.00000, ('{"lon":52.38765,"lat":4.76534,"temp":37.365,"humidity":22.387,"pressure":105}')@2015-07-01 00:07:00.00000, ('{"lon":53.98727,"lat":4.46758,"temp":38.376,"humidity":20.182,"pressure":106}')@2015-07-0100:08:00.00000]

1 row(s) retrieved.

Database closed.

Press enter to continue

################################################################
Select on TimeSeries table and output in traditional relational form

select 'tag1',time_stamp,value::json from
table (( select tssettolist(clip(series,
'2015-07-01 01:00:00.00000'::datetime year to fraction(5),
'2015-07-01 01:10:00.00000'::datetime year to fraction(5)))
::list(row1 not null)
from test_ts
where tagid='tag1'));

Press enter to see output

Database selected.

(constant)    tag1
time_stamp    2015-07-01 01:00:00.00000
(expression)  {"lon":54.37846,"lat":4.87345,"temp":38.387,"humidity":22.387,"pressure":108}

(constant)    tag1
time_stamp    2015-07-01 01:01:00.00000
(expression)  {"lon":54.48765,"lat":5.38765,"temp":37.145,"humidity":20.378,"pressure":109}

(constant)    tag1
time_stamp    2015-07-01 01:02:00.00000
(expression)  {"lon":50.23487,"lat":4.87345,"temp":38.453,"humidity":20.235,"pressure":100}

(constant)    tag1
time_stamp    2015-07-01 01:03:00.00000
(expression)  {"lon":50.36746,"lat":5.12643,"temp":37.237,"humidity":21.344,"pressure":101}

(constant)    tag1
time_stamp    2015-07-01 01:04:00.00000
(expression)  {"lon":51.46758,"lat":4.87345,"temp":38.947,"humidity":22.137,"pressure":102}

(constant)    tag1
time_stamp    2015-07-01 01:05:00.00000
(expression)  {"lon":51.98765,"lat":4.36746,"temp":37.846,"humidity":20.936,"pressure":103}

(constant)    tag1
time_stamp    2015-07-01 01:06:00.00000
(expression)  {"lon":52.12643,"lat":4.98727,"temp":38.376,"humidity":21.049,"pressure":104}

(constant)    tag1
time_stamp    2015-07-01 01:07:00.00000
(expression)  {"lon":52.38765,"lat":4.76534,"temp":37.365,"humidity":22.387,"pressure":105}

(constant)    tag1
time_stamp    2015-07-01 01:08:00.00000
(expression)  {"lon":53.98727,"lat":4.46758,"temp":38.376,"humidity":20.182,"pressure":106}

(constant)    tag1
time_stamp    2015-07-01 01:09:00.00000
(expression)  {"lon":53.76534,"lat":5.48765,"temp":37.298,"humidity":21.398,"pressure":107}

(constant)    tag1
time_stamp    2015-07-01 01:10:00.00000
(expression)  {"lon":54.37846,"lat":4.87345,"temp":38.387,"humidity":22.387,"pressure":108}

11 row(s) retrieved.

Database closed.

Press enter to continue

################################################################
Create & Load a Relational Table

create table test_rel
 (
   tag_id char(20),
   vendor varchar(20),
   PRIMARY KEY (tag_id)
) in dbspace1;
alter table test_ts
lock mode(row);

insert into test_rel values('tag1','IBM');
insert into test_rel values('tag2','ABC');
insert into test_rel values('tag3','XYZ');
insert into test_rel values('tag4','IBM');
insert into test_rel values('tag5','ABC');


Table creation .. passed
Please press Enter to continue

################################################################
Perform JOIN operation of Relational Table against TS + JSON Table

SELECT vendor, tagid, value::json sensors from test_rel tr, test_v ts
where ts.tagid = tr.tag_id and vendor = 'IBM'
and time_stamp between '2015-07-01 00:02:00.00000' and '2015-07-01 00:03:00.00000';

Press enter to see output

Database selected.

vendor   IBM
tagid    tag1
sensors  {"lon":50.23487,"lat":4.87345,"temp":38.453,"humidity":20.235,"pressure":100}

vendor   IBM
tagid    tag1
sensors  {"lon":50.36746,"lat":5.12643,"temp":37.237,"humidity":21.344,"pressure":101}

vendor   IBM
tagid    tag4
sensors  {"lon":50.23487,"lat":4.87345,"temp":38.453,"humidity":20.235,"pressure":100}

vendor   IBM
tagid    tag4
sensors  {"lon":50.36746,"lat":5.12643,"temp":37.237,"humidity":21.344,"pressure":101}

4 row(s) retrieved.

Database closed.

Press enter to continue

Congratulations!!! Your Demo is completed Successfully.
We hope you had a pleasant learning experience

Click Here to read more about IBM Informix JSON Compatibility

Click Here to read through an example that illustrates implementing TimeSeries through Informix Wire Listener

No comments: