Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Machbase provides high-speed tag data extraction, especially for the time range of a specific tag.

Sample Schema


In the following example, we created a TAG table and created two tags as shown below.

For each tag, data from January 1, 2018 to February 10, 2018 were inserted.

Code Block
languagesql
create tagdata table TAG (name varchar(20) primary key, time datetime basetime, value double summarized);

insert into tag metadata values ('TAG_0001');
insert into tag metadata values ('TAG_0002');

insert into tag values('TAG_0001', '2018-01-01 01:00:00 000:000:000', 1);
insert into tag values('TAG_0001', '2018-01-02 02:00:00 000:000:000', 2);
insert into tag values('TAG_0001', '2018-01-03 03:00:00 000:000:000', 3);
insert into tag values('TAG_0001', '2018-01-04 04:00:00 000:000:000', 4);
insert into tag values('TAG_0001', '2018-01-05 05:00:00 000:000:000', 5);
insert into tag values('TAG_0001', '2018-01-06 06:00:00 000:000:000', 6);
insert into tag values('TAG_0001', '2018-01-07 07:00:00 000:000:000', 7);
insert into tag values('TAG_0001', '2018-01-08 08:00:00 000:000:000', 8);
insert into tag values('TAG_0001', '2018-01-09 09:00:00 000:000:000', 9);
insert into tag values('TAG_0001', '2018-01-10 10:00:00 000:000:000', 10);

insert into tag values('TAG_0002', '2018-02-01 01:00:00 000:000:000', 11);
insert into tag values('TAG_0002', '2018-02-02 02:00:00 000:000:000', 12);
insert into tag values('TAG_0002', '2018-02-03 03:00:00 000:000:000', 13);
insert into tag values('TAG_0002', '2018-02-04 04:00:00 000:000:000', 14);
insert into tag values('TAG_0002', '2018-02-05 05:00:00 000:000:000', 15);
insert into tag values('TAG_0002', '2018-02-06 06:00:00 000:000:000', 16);
insert into tag values('TAG_0002', '2018-02-07 07:00:00 000:000:000', 17);
insert into tag values('TAG_0002', '2018-02-08 08:00:00 000:000:000', 18);
insert into tag values('TAG_0002', '2018-02-09 09:00:00 000:000:000', 19);
insert into tag values('TAG_0002', '2018-02-10 10:00:00 000:000:000', 20);



UI Text Box

Index


Table of Contents
maxLevel3
indent30px
exclude목차
classtoc


Extract all TAG data


Code Block
languagesql
Mach> select * from tag;
NAME TIME VALUE 
--------------------------------------------------------------------------------------
TAG_0001 2018-01-01 01:00:00 000:000:000 1 
TAG_0001 2018-01-02 02:00:00 000:000:000 2 
TAG_0001 2018-01-03 03:00:00 000:000:000 3 
TAG_0001 2018-01-04 04:00:00 000:000:000 4 
TAG_0001 2018-01-05 05:00:00 000:000:000 5 
TAG_0001 2018-01-06 06:00:00 000:000:000 6 
TAG_0001 2018-01-07 07:00:00 000:000:000 7 
TAG_0001 2018-01-08 08:00:00 000:000:000 8 
TAG_0001 2018-01-09 09:00:00 000:000:000 9 
TAG_0001 2018-01-10 10:00:00 000:000:000 10 
TAG_0002 2018-02-01 01:00:00 000:000:000 11 
TAG_0002 2018-02-02 02:00:00 000:000:000 12 
TAG_0002 2018-02-03 03:00:00 000:000:000 13 
TAG_0002 2018-02-04 04:00:00 000:000:000 14 
TAG_0002 2018-02-05 05:00:00 000:000:000 15 
TAG_0002 2018-02-06 06:00:00 000:000:000 16 
TAG_0002 2018-02-07 07:00:00 000:000:000 17 
TAG_0002 2018-02-08 08:00:00 000:000:000 18 
TAG_0002 2018-02-09 09:00:00 000:000:000 19 
TAG_0002 2018-02-10 10:00:00 000:000:000 20 
[20] row(s) selected.

If there is no special condition as described above, data can be extracted for each tag arranged in each time order.


Extract data for a specific tag name


Below is an example of data with TAG name TAG_0002.

Code Block
Mach> select * from tag where name='TAG_0002';
NAME                  TIME                            VALUE                       
--------------------------------------------------------------------------------------
TAG_0002              2018-02-01 01:00:00 000:000:000 11                          
TAG_0002              2018-02-02 02:00:00 000:000:000 12                          
TAG_0002              2018-02-03 03:00:00 000:000:000 13                          
TAG_0002              2018-02-04 04:00:00 000:000:000 14                          
TAG_0002              2018-02-05 05:00:00 000:000:000 15                          
TAG_0002              2018-02-06 06:00:00 000:000:000 16                          
TAG_0002              2018-02-07 07:00:00 000:000:000 17                          
TAG_0002              2018-02-08 08:00:00 000:000:000 18                          
TAG_0002              2018-02-09 09:00:00 000:000:000 19                          
TAG_0002              2018-02-10 10:00:00 000:000:000 20                          
[10] row(s) selected.


Query for time range


The following is a query of a time range for TAG_0002 and receives data.

UI Text Box
sizemedium
typetip

It is common practice to use the between clause to give a time range. Of course, you can get the same result by typing the time range with time  <  or   >.


Code Block
languagesql
Mach> select * from tag where name = 'TAG_0002' and time between to_date('2018-02-01') and to_date('2018-02-05');
NAME                  TIME                            VALUE                       
--------------------------------------------------------------------------------------
TAG_0002              2018-02-01 01:00:00 000:000:000 11                          
TAG_0002              2018-02-02 02:00:00 000:000:000 12                          
TAG_0002              2018-02-03 03:00:00 000:000:000 13                          
TAG_0002              2018-02-04 04:00:00 000:000:000 14                          
[4] row(s) selected.

Mach> select * from tag where name = 'TAG_0002' and time > to_date('2018-02-01') and time < to_date('2018-02-05');
NAME                  TIME                            VALUE                       
--------------------------------------------------------------------------------------
TAG_0002              2018-02-01 01:00:00 000:000:000 11                          
TAG_0002              2018-02-02 02:00:00 000:000:000 12                          
TAG_0002              2018-02-03 03:00:00 000:000:000 13                          
TAG_0002              2018-02-04 04:00:00 000:000:000 14                          
[4] row(s) selected.


Time range search for multiple tags


Below is an example of retrieving the same time range data for two or more tags.

If you want to get fast results for a large number of tags at the same time, it is preferable to perform the following type of query.

Code Block
Mach> select * from tag where name in ('TAG_0002', 'TAG_0001') and time between to_date('2018-01-05') and to_date('2018-02-05');
NAME                  TIME                            VALUE                       
--------------------------------------------------------------------------------------
TAG_0001              2018-01-05 05:00:00 000:000:000 5                           
TAG_0001              2018-01-06 06:00:00 000:000:000 6                           
TAG_0001              2018-01-07 07:00:00 000:000:000 7                           
TAG_0001              2018-01-08 08:00:00 000:000:000 8                           
TAG_0001              2018-01-09 09:00:00 000:000:000 9                           
TAG_0001              2018-01-10 10:00:00 000:000:000 10                          
TAG_0002              2018-02-01 01:00:00 000:000:000 11                          
TAG_0002              2018-02-02 02:00:00 000:000:000 12                          
TAG_0002              2018-02-03 03:00:00 000:000:000 13                          
TAG_0002              2018-02-04 04:00:00 000:000:000 14                          
[10] row(s) selected.


Search data over a certain value


The conditions for the tag value can also be given as follows.

Filtering was performed for those values greater than 12 and less than 15 among the values of TAG_0002.

Code Block
Mach> select * from tag where name = 'TAG_0002' and value > 12 and value < 15 and time between to_date('2018-02-01') and to_date('2018-02-05');
NAME                  TIME                            VALUE                       
--------------------------------------------------------------------------------------
TAG_0002              2018-02-03 03:00:00 000:000:000 13                          
TAG_0002              2018-02-04 04:00:00 000:000:000 14                          
[2] row(s) selected.


Extracting Data via RESTful API


Preparations for the RESTful API

You must run Machbase Web Analyzer (MWA) to make the Web service available, and then do the following:

Code Block
languagebash
titleMWA의 수행
$ MWAserver start
SERVER STARTED, PID : 27307
     Connection URL : http://192.168.0.148:5001

RESTful API calling conventions

Code Block
languagexml
titleSELECT FORM
{MWA URL}/machiot-rest-api/datapoints/raw/{TagName}/{Start}/{End}/{Direction}/{Count}/{Offset}/  


Parameter

Description

TagNameName of tag, If you want to specify multiple TagName, They should separated by comma(',').
Start, EndDuration. Formats supported are YYYY-MM-DD HH24:MI:SS or YYYY-MM-DD or YYYY-MM-DD HH24:MI:SS,mmm (mmm: millisecond, if it is omitted start is 000, End is 999, micro second and nano seconds are also 999).If you specify as a string, add 'T' between date and time to remove space.
DirectionCurrently, only 0 (ascending) is supported.
CountLimit number of rows. if you specify 0, all of the rows are retrieved.
OffsetStarting offset (default : 0)

A sample of importing single tag data via CURL

If you make a call to Machbase installed on 192.168.0.148 as shown below, you can import the data from the web.

Code Block
languagexml
titleSingle Tag
$ curl -G "http://192.168.0.148:5001/machiot-rest-api/v1/datapoints/raw/TAG_0001/2018-01-01T00:00:00/2018-01-06T00:00:00"

{"ErrorCode": 0, 
 "ErrorMessage": "", 
 "Data": [{"DataType": "DOUBLE", 
 "ErrorCode": 0, 
 "TagName": "TAG_0001", 
 "CalculationMode": "raw", 
 "Samples": [{"TimeStamp": "2018-01-01 01:00:00 000:000:000", "Value": 1.0, "Quality": 1}, 
             {"TimeStamp": "2018-01-02 02:00:00 000:000:000", "Value": 2.0, "Quality": 1}, 
             {"TimeStamp": "2018-01-03 03:00:00 000:000:000", "Value": 3.0, "Quality": 1}, 
             {"TimeStamp": "2018-01-04 04:00:00 000:000:000", "Value": 4.0, "Quality": 1}, 
             {"TimeStamp": "2018-01-05 05:00:00 000:000:000", "Value": 5.0, "Quality": 1}]}]
}

Importing multiple tag data via CURL

Below is a sample example that gets the values for the two tags.

Code Block
$ curl -G "http://192.168.0.148:5001/machiot-rest-api/datapoints/raw/TAG_0001,TAG_0002/2018-01-05T00:00:00/2018-02-05T00:00:00"
{"ErrorCode": 0, 
 "ErrorMessage": "", 
 "Data": [{"DataType": "DOUBLE", 
           "ErrorCode": 0, 
           "TagName": "TAG_0001,TAG_0002", 
           "CalculationMode": "raw", 
           "Samples": [{"TimeStamp": "2018-01-05 05:00:00 000:000:000", "Value": 5.0, "Quality": 1}, 
                       {"TimeStamp": "2018-01-06 06:00:00 000:000:000", "Value": 6.0, "Quality": 1}, 
                       {"TimeStamp": "2018-01-07 07:00:00 000:000:000", "Value": 7.0, "Quality": 1}, 
                       {"TimeStamp": "2018-01-08 08:00:00 000:000:000", "Value": 8.0, "Quality": 1}, 
                       {"TimeStamp": "2018-01-09 09:00:00 000:000:000", "Value": 9.0, "Quality": 1}, 
                       {"TimeStamp": "2018-01-10 10:00:00 000:000:000", "Value": 10.0, "Quality": 1}, 
                       {"TimeStamp": "2018-02-01 01:00:00 000:000:000", "Value": 11.0, "Quality": 1}, 
                       {"TimeStamp": "2018-02-02 02:00:00 000:000:000", "Value": 12.0, "Quality": 1}, 
                       {"TimeStamp": "2018-02-03 03:00:00 000:000:000", "Value": 13.0, "Quality": 1}, 
                       {"TimeStamp": "2018-02-04 04:00:00 000:000:000", "Value": 14.0, "Quality": 1}
]}]}


Specifying Scan Direction with Hint

In general, TAG table query retrieves the oldest record first. When the newest record is to be retrieved, the scan direction can be specified with SELECT HINT.


Forward scanning.

Forward scanning is possible with /*+ SCAN_BACKWARD(table_name) */ hint.

Code Block
languagesql
Mach> SELECT * FROM tag WHERE t_name='TAG_99' LIMIT 10;
T_NAME                T_TIME                          T_VALUE                     
--------------------------------------------------------------------------------------
TAG_99                2017-01-01 00:00:49 500:000:000 0                           
TAG_99                2017-01-01 00:01:39 500:000:000 1                           
TAG_99                2017-01-01 00:02:29 500:000:000 2                           
TAG_99                2017-01-01 00:03:19 500:000:000 3                           
TAG_99                2017-01-01 00:04:09 500:000:000 4                           
TAG_99                2017-01-01 00:04:59 500:000:000 5                           
TAG_99                2017-01-01 00:05:49 500:000:000 6                           
TAG_99                2017-01-01 00:06:39 500:000:000 7                           
TAG_99                2017-01-01 00:07:29 500:000:000 8                           
TAG_99                2017-01-01 00:08:19 500:000:000 9                           
[10] row(s) selected.
Elapsed time: 0.001

Mach> SELECT /*+ SCAN_FORWARD(tag) */  * FROM tag WHERE t_name='TAG_99' LIMIT 10;
T_NAME                T_TIME                          T_VALUE                     
--------------------------------------------------------------------------------------
TAG_99                2017-01-01 00:00:49 500:000:000 0                           
TAG_99                2017-01-01 00:01:39 500:000:000 1                           
TAG_99                2017-01-01 00:02:29 500:000:000 2                           
TAG_99                2017-01-01 00:03:19 500:000:000 3                           
TAG_99                2017-01-01 00:04:09 500:000:000 4                           
TAG_99                2017-01-01 00:04:59 500:000:000 5                           
TAG_99                2017-01-01 00:05:49 500:000:000 6                           
TAG_99                2017-01-01 00:06:39 500:000:000 7                           
TAG_99                2017-01-01 00:07:29 500:000:000 8                           
TAG_99                2017-01-01 00:08:19 500:000:000 9                           
[10] row(s) selected.
Elapsed time: 0.001
Mach> 



Backward scanning.

Backward scanning is possible with /*+ SCAN_BACKWARD(table_name) */ hint.

Code Block
languagesql
Mach> SELECT /*+ SCAN_BACKWARD(tag) */ * FROM tag WHERE t_name='TAG_99' LIMIT 10;
T_NAME                T_TIME                          T_VALUE                     
--------------------------------------------------------------------------------------
TAG_99                2017-02-27 20:53:19 500:000:000 9                           
TAG_99                2017-02-27 20:52:29 500:000:000 8                           
TAG_99                2017-02-27 20:51:39 500:000:000 7                           
TAG_99                2017-02-27 20:50:49 500:000:000 6                           
TAG_99                2017-02-27 20:49:59 500:000:000 5                           
TAG_99                2017-02-27 20:49:09 500:000:000 4                           
TAG_99                2017-02-27 20:48:19 500:000:000 3                           
TAG_99                2017-02-27 20:47:29 500:000:000 2                           
TAG_99                2017-02-27 20:46:39 500:000:000 1                           
TAG_99                2017-02-27 20:45:49 500:000:000 0                           
[10] row(s) selected.
Elapsed time: 0.001
Mach>


Property to Set Default Direction

The defualt direction of scan can be set with DISK_SCAN_DIRECTION property. The value can be 0 or 1. Default value is 1.

The scan direction is forward with 1, and backward with 0, when no hint was given in SELECT phrase.