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
