Versions Compared

Version Old Version 15 New Version 16
Changes made by

Former user

Ryan Park (Unlicensed)

Saved on

Key

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

마크베이스는 고속의 태그 데이터 추출 성능을 제공하며, 특히 특정 태그의 시간 범위에 대한 탁월한 성능을 제공한다.

샘플 스키마

이후의 샘플은 아래와 같이  TAG 테이블이 생성되고, 두개의 태그를 생성하였다.

각 태그는 태그에 대해 각각 2018년 1월 1일 부터 각각 1일부터 2월 10일까지 10일까지의 데이터를 입력하였다.

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

목차

Table of Contents
maxLevel3
indent30px
exclude목차
classtoc



전체 TAG 데이터 추출

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.
Elapsed time: 0.002

위와 같이 특별한 조건이 없으면, 각 시간 순으로 정렬된 태그별로 데이터를 추출할 수 있다.


임의 TAG명에 대한 데이터 추출

아래는 TAG 이름이 TAG_0002 인 데이터를 출력하는 예제이다. WHERE 절에 주어진 name에 대한 조건을 설정한다.

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.
Elapsed time: 0.001
Mach>


시간 범위에 대한 쿼리

아래는 TAG_0002에 대한 시간 범위를 주고, 데이터를 받아오는 쿼리이다.

Ui text box
sizemedium
typetip

between 절을 활용해서 시간 범위를 주는 것이 일반적인 방법이다. 물론, time을 < 혹은 > 기호로 시간 범위를 입력해도 같은 결과를 얻을 수 있다.


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.
Elapsed time: 0.001

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.
Elapsed time: 0.001


다중 태그에 대한 시간 범위 검색

아래는 2개 이상의 태그에 대해서 동일한 시간 범위 데이터를 검색하는 예제이다.

만일 한번의 질의 수행으로 다수의 태그에 대해 한꺼번에 빠른 결과를 받고 싶을 경우에는 아래와 같은 형태의 수행이 바람직하다.

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.
Elapsed time: 0.001


특정 값 이상의 태그만 출력하기

간단한 예제이긴 하지만, 태그 값에 대한 조건도 함께 아래와 같이 줄 수 있다.

TAG_0002의 값 중에 12보다 크고, 15보다 작을 것들에 대해 필터링을 수행했다.

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.
Elapsed time: 0.001
Rest 

RESTful API를 통한 추출
RestAPI를 
RESTful API를 위한 준비 사항
반드시 MWA (Machbase Web Analyzer) 를 수행해서 웹 서비스가 가능한 상태로 만든 이후에 아래를 수행해야 한다.
 Code Block
languagebash
titleMWA의 수행 
$ MWAserver start
SERVER STARTED, PID : 27307
     Connection URL : http://192.168.0.148:5001
RestAPI 
RESTful API 호출 규약 
 Code Block
languagexml
titleSELECT FORM
{MWA URL}/machiot-rest-api/datapoints/raw/{TagName}/{Start}/{End}/{Direction}/{Count}/{Offset}/  

TagName    : Tag Name. 복수의 Tag 지원(,로 구분하여 사용)
Start, End : 기간, YYYY-MM-DD HH24:MI:SS 또는 YYYY-MM-DD 또는 YYYY-MM-DD HH24:MI:SS,mmm (mmm: millisecond, 생략시 start는 000, End는 999이며, 마이크로와 나노도 모두 999임)
실제 스트링으로 지정할 때는 날짜와 시간 사이에 T를 넣어서 빈공간을 없애준다. 
Direction  : 0(ascending), 추후 지원 (시간이 증가하는 방향)
Count      : LIMIT, 0이면 전체
Offset     : offset (기본값 = 0)
CURL을 통한 단일 태그 데이터 가져오기 샘플 
아래와 같이 192.168.0.148에 설치된 마크베이스에 대한 호출을 수행하면, 해당 데이터를 웹으로 부터 가져올 수 있다.
 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}]}]
}

CURL을 통한 다중 태그 데이터 가져오기
아래는 두개의 태그에 대한 값을 가져오는 샘플 예제이다.
 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}
]}]}
$