안녕하세요^^
산업용 터치패널 HMI, OPC UA 기반 통신 프로토콜게이트웨이(GATEWAY)
이지뷰코리아 웨인텍 공식 대리점
알비솔루션
T :02-3667-8027 E-mai : bsk0916@naver.com
입니다.
오늘은 최근 4차 산업혁명 시대에
IIoT 사물인터넷을 기반으로 한
스마트팩토리(Smart Factory) 구축에 반드시 동반되는
HMI 기반의 데이터 통합 관리 솔루션
cMT 시리즈의
cMT 데이터 베이스 설정 방법과 SQL 쿼리(Query) 사용 방법
에 대해서 공유하겠습니다.
매뉴얼 형식이다보니 스크롤의 압박이 있을 수 있습니다만,
사용자 분들에게는 반드시 필요한 필수 절차이오니,
많은 도움이 되실 거라 생각합니다.
이지뷰(EasyView) 코리아 공식 대리점으로서
언제나 최선을 다하는 알비솔루션이 되겠습니다.
자!!
심호흡 한번 하시고 시작합니다~
cMT 데이터 베이스 & SQL 쿼리
(HMI 기반의 데이터 통합 솔루션)
목차
데이터 베이스 소개
cMT 에서의 데이터 베이스 설정
SQL 쿼리 사용 방법
데이터 베이스 소개
데이터 관리의 문제
-
Excel Table? :
-
기록된 데이터가 각각 개별의 파일로 되어 있다면?
만약…
30초마다 한 번씩 기록되는 1일 1파일이 사용하는데,
클라이언트가 주간 별 통계를 요구한다면?
각 파일을 하나하나 열어 확인할 수 밖에 없습니다!!
상당히 번거로운 일이 아닐 수 없죠?
-
각 파일의 데이터 테이블(파일)을 합치면 데이터 베이스(DB)가 됩니다.
데이터 베이스
-
데이터 베이스(DB): 데이터를 저장하는 파일
-
시스템 관리를 통해 여러 사용자가 연결할 수 있어 보다 효율적인 데이터 중앙 관리 시스템을 제공합니다.
데이터 베이스 시스템
-
데이터 베이스 시스템(Database System) : 사용자가 다양한 응용프로그램을 사용하여 데이터 베이스의 데이터를 엑세스 합니다.
데이터 베이스 언어
-
데이터 베이스 언어 : SQL (Structural Query Language)은 데이터 베이스를 사용할 때, 데이터베이스에 접근할 수 있는 프로그래밍 언어입니다.
-
데이터 베이스의 기본 4대 동작은 CRUD 입니다.
- Create(생성), Read(읽기), Update(갱신), Delete(삭제)
데이터 베이스와의 연결
-
DB 사용 전: HMI 데이터를 USB/SD카드 또는 PC에 다운로드 후 수동으로 표 정리
-
DB 사용 후: 데이터를 신속하게 집계하고 보고서/표를 자동으로 생성
cMT + MySQL 통합 구축
-
1대의 MySQL Server 는 여러 대의 cMT에 데이터 전송
-
사용자는 MySQL를 이용하여 필요한 데이터를 읽고 처리
cMT + MySQL 통합 구축 (예제)
Siemens, OMRON, Rockwell 생산자료 -> MySQL DB
데이터 베이스
cMT 데이터 베이스 연결 응용
- Excel ODBC
- SQL 명령을 입력하면 엑셀 표에서 결과를 바로 볼 수 있음
- 각종 MySQL Client 수동으로 데이터 조회(MySQL workbench, HMI)
- 기타 프로그램을 통한 데이터 읽기
- SQL은 IT 종사자들의 제일 기초적인 부분이라 비교적 쉽게 핸들링 할 수 있습니다.
MySQL Server 설정
MySQL Server 설정
-
cMT 시리즈만 데이터 베이스 지원
-
PC에 프로그램 설치
- MySQL WorkBench (MySQL Server + Workbench)
-
cMT시리즈에 연결할 데이터 베이스 생성
-
MySQL 사용자 계정 추가
1.MySQL Community Server
•SQL Server 설치 후 PC를 재부팅하면 SQL server가 자동 실행됨
2. MySQL Server 상태 확인
•응용프로그램 리스트 확인 → mservices.msc 실행
•또는 작업 관리자에서 확인 (mysqld.exe MySQL Daemon)
•또는 작업 관리자 » 성능 » 리소스 모니터 » 네트워크에서 확인 (mysqld.exe)
EBPRO 설정
-
HMI와 데이터 베이스 연결
-
데이터 베이스와 데이터 샘플링의 동기화
-
이벤트 이력과의 동기화
EBPRO 설정 : 1. HMI와 데이터 베이스 연결
-
cMT 시리즈만 데이터 베이스 지원
EBPRO 설정 : 1. HMI와 데이터 베이스 연결 – [새로 만들기]
-
[데이터/기록] » [데이터 베이스 서버] » [새로 만들기]
EBPRO 설정 : 1. HMI와 데이터 베이스 연결 – [일반]
-
데이터 베이스 서버 시스템 설정 (IP, 연결 포트, 사용자 이름, 패스워드, 데이터 베이스 명칭)
EBPRO 설정 : 1. HMI와 데이터 베이스 연결 – [상태/제어]
-
연결이 되지 않을 때, 상태 주소를 통해 현재 연결 상태 확인 가능
-
사용하지 않는 HMI 로컬 주소를 상태 주소로 사용 (HMI 로컬 주소의 실행 효율이 높음)
-
사용한 주소가 다른 오브젝트 주소와 중복되면 데이터 베이스와의 연결 불가능
EBPRO 설정 : 2. 데이터 샘플링과의 동기화
-
제어 주소로 설정된 오브젝트 추가
-
[데이터베이스 서버에 동기화] »[사용] 선택 후 연결할 데이터 베이스 선택
-
여러 개의 데이터 샘플링을 각각의 다른 데이터 베이스에 연결 가능
-
데이터 샘플링 서버 동기화는 반드시 제어 주소를 사용하여 실행
-
USB 또는 SD카드에 동시 동기화 가능
EBPRO 설정 : 3. 이벤트 이력과의 동기화
-
[데이터베이스 서버에 동기화] »[사용] 선택 후 연결할 데이터 베이스 선택
-
모든 알람을 같은 데이터 베이스 서버에 동기화 (데이터 샘플링과 다름. 나누어 동기화 불가능)
-
데이터 샘플링 서버 동기화는 반드시 제어 주소를 사용하여 실행
-
USB 또는 SD카드에 동시 동기화 가능
cMT 시리즈와 데이터 베이스
-
HMI와 데이터 베이스 서버 연결 후 생성된 테이블명
-
시스템 구성: 한 개의 MySQL 서버로 여러 대의 cMT 시리즈 HMI와 동시 연결 가능
1)공용 MySQL 서버
•데이터 샘플링/이벤트 이력을 동일한 서버에 연결 가능.
즉, 같은 DB 사용 가능. DB에 각각의 테이블 생성.
•연결 설정
1. MySQL 서버와 연결 (포트: 3306)
2. 테이블 생성/인증 완료 후 연결
•DB는 우선 MySQL에 생성. HMI는 DB 생성 불가능.
2)데이터 샘플링/이벤트 이력을 MySQL 서버에 동기화
•SQL 명령을 통해 사용자는 다양한 형식으로 데이터를 읽어올 수 있음.
•HMI의 SQL 쿼리 구현 가능
cMT 시리즈의 예전 DB 저장 방식
1. cMT시리즈의 데이터 원본을 DB형식으로 저장 (*.db)
2. SQL DB 기능 출시 전 cMT시리즈 데이터는 USB/SD에만 동기화 가능
•자동 동기화: 1만개 데이터 동기화, HMI는 데이터를 1만개만 저장 가능. 초과 시 삭제
•수동 동기화: 제어 주소 + 매크로
cMT 시리즈의 현재 DB 저장 방식
1. DB서버는 USB/SD와 같은 동기화 체계 사용 (버퍼 저장 데이터)
2. HMI 데이터 1만개 초과 시, USB/SD 또는 MySQL DB 서버에 동기화
SQL 쿼리
SQL 쿼리
1. HMI에서 SQL 명령을 이용하여 추가, 읽기, 삭제 등의 기본 기능 대체 가능
2. 고급 명령을 통해 DB데이터를 필터링하거나 연산 가능
3. 심플 모드:
•온라인에서 레시피 DB로 사용 가능. HMI에 저장하는 것이 아니라 DB서버에 저장.
레시피 데이터 수정/읽기는 해당 HMI에서만 조작이 가능하고
DB서버에 연결 시 cMT 뷰어를 통해 어디서든 레시피 데이터를 업데이트/읽기 가능
4. 고급 모드:
•사용자가 HMI 또는 cMT 뷰어를 통해 생산 기록 확인
•데이터를 필터링 하거나 간단한 연산 진행 가능
SQL 쿼리 – 기본 명령
1. SQL ─ ─ ─ CRUD (기본명령) :
•Create(생성), Read(읽기), Update(갱신), Delete(삭제)
2. SQL 명령 형식
•추가 INSERT
INSERT INTO table_name
VALUES (value1, value2, value3,....)
•읽기 SELECT (query)
SELECT column_name(s)
FROM table_name
3. SQL 명령 형식
•업데이트 UPDATE
UPDATE table_name
SET column1=value, column2=value,...
WHERE some_column=some_value
•삭제 DELECT
DELECT FROM table_name
WHERE some_column=some_value
4. 추가, 읽기, 업데이트, 삭제 기본 명령(CRUD) 만 사용 가능
5. 데이터 샘플링/이벤트 이력은 자동으로 생성하지만 DB, 테이블, 행은 우선 MySQL에서 설정해야 함
SQL 쿼리 – 심플 모드
-
초기 절차는 데이터 샘플링/알람(이벤트) 목록 생성하는 것과 동일함
-
테이블 생성
-
심플 모드에서 prime key와 AI(auto increment)는 반드시 체크
-
EBPRO에서 데이터 베이스, 테이블 이름, 행 주소 설정 후 [서버에서 가져오기] 클릭
-
[서버에서 가져오기]가 완료되면 테이블 행에 행 주소의 내용이 입력됨
-
[명령] 탭에서 제어 주소를 설정하면 제어 주소의 값에 따라 SQL 명령 실행 가능
-
[SQL 쿼리 표시] 오브젝트를 클릭하여 테이블 선택
-
[SQL 쿼리 표시] 오브젝트와 [SQL 쿼리 표시] 오브젝트에서 설정한 테이블 주소 및 제어 주소를 통해 해당 기능을 모두 사용 가능
SQL 쿼리 – 고급 모드
-
[SQL 쿼리] » [일반] 탭의 우측 상단의 [고급모드] 선택 (체크 후 체크 해제 불가)
-
[명령] 탭에서 제어 주소 설정 후 [새로 만들기] 클릭
-
명령 ID 설정(제어 주소 값 입력) 및 사용자 정의 SQL 명령 사용
-
SQL 명령의 특성에 따라 [인수]와 [내보내기]를 사용
-
[인수]는 HMI 주소로부터 SQL 명령 쓰기
-
[내보내기]는 HMI 주소에 SQL 명령 결과의 값을 표시
SQL 쿼리 – 고급 모드 (1)
-
[쿼리] 탭에서 SQL 명령(읽기)을 추가
-
[내보내기] 주소를 LW-101~104로 설정하면 각각의 주소가 테이블의 각 행에 값을 표시
SQL 쿼리 – 고급 모드 (2)
-
[쿼리] 탭에서 SQL 명령(새로 만들기)을 추가
- 인수를 사용하려면 SQL 명령에서 인수 개수를 추가해야 하며 형식은 {${1}) 입니다. 예를 들어, 3개의 인수를 추가해야 하는 경우에는 (${1}, ${2}, ${3})를 입력해야 합니다.
- [인수] 탭에서 [새로 만들기]를 하여 주소를 LW-201~203으로 설정합니다.
- 이는 다른 테이블에 인수를 입력하는데 사용되는 주소입니다.
- 이 탭에서 SQL 명령어와 동일한 개수의 인수를 추가해야 합니다.
SQL 쿼리 – 고급 모드
-
SQL 고급 명령 형식
- 읽기 SELECT (query) + 조건
SELECT column_name(s)
FROM table_name WHERE `table_name` > (${1})
- 평균값 읽기 SELECT (query)
SELECT avg(`column_name`)
FROM table_name
- 최대값 읽기 SELECT (query)
SELECT max(`column_name`)
FROM table_name
SQL 쿼리 – 적용 예제
[생산라인] 온라인 레시피 DB
[제어실] 온라인 레시피 DB + 생산 기록
[사무실] 생산 기록 데이터 관리
SQL 명령 사용 - 보충 설명
SQL 쿼리 명령 사용하기
-
목적: 데이터 베이스에서 원하는 데이터 검색
-
의미: SQL 쿼리 명령 SELECT 사용
-
예를 들어, 이 데이터에서 5월 하순의 데이터 중 업무 시간 내의 주중 데이터를 원할 경우 아래의 SQL 명령을 실행해야 합니다.
SELECT
FROM_UNIXTIME(ROUND(`time@timestamp`,0)-8*3600) as 'Sample Time' ,
ROUND(data_format_0,2) as ‘Temperature (°C)’,
ROUND(data_format_0*9/5+32,0) as ‘Temperature (°F)’,
ROUND(data_format_1,1) as Humidity,
CONVERT(data_format_3 using ASCII) as Mode
FROM plantfactory.`cmt-4b3d_status_data`
WHERE
HOUR(FROM_UNIXTIME(`time@timestamp`-8*3600)) between 9 and 17 and
FROM_UNIXTIME(`time@timestamp`-8*3600) between ‘2016-05-16’ and ‘2016-05-31’;
-
명령을 실행하면 결과가 다음과 같이 표시됩니다.
-
버튼을 클릭하면 이미 엑셀 스프레드 시트에 추가되어 있습니다.
SELECT COLUMN_A, COLUMN_B, COLUMN_C…, ARITHEMETICS
FROM DATABASE.TABLE
WHERE CONDITION_A AND/OR CONDITION_B…
GROUP BY
HAVING
ORDER BY
LIMIT
연습: 데이터 샘플링
-
목표: 데이터 베이스에서 데이터 샘플링 중 원하는 데이터 검색
1. 심플하게 시작해 보자!
목표는…
모든 데이터 샘플링 기록 가져오기!
-
모든 데이터 샘플링 기록 가져오기
데이터 베이스에 포함된 원본 원시 데이터를 제공합니다.
그러나 이는 예상보다 유용하지 않습니다…
그리고 몇 가지의 단점이 있습니다.
참조
- 시간은 어떻게 되는 건가요?
- cMT는 현재 시간(time@timestamp)을 Unix Epoch Time(1970-01-01 이후 초 단위의 경과시간)으로 기록합니다.
- 이는 My SQL (FROM_UNIXTIME) 함수 기능으로 변환할 수 있습니다.
- 시간 대를 염두에 두고 시간이 GMT로 반환되는지, 현지 시간이 반환되는지 확인합니다.
- HMI는 현재 시간을 표준 시간으로 취급하고 해당 값을 Unix timestamp로 기록합니다.
- 예를 들어 여기서 다루는 데이터 베이스는 HMI가 현지 시간으로 설정된 타이페이(+8)로 기록되었습니다. FROM_UNIXTIME() 함수로 보내기 전에 8시간 오프셋을 해야 합니다.
- 시스템 및 응용 프로그램에 따라 날짜, 시간이 현지 시간 또는 GMT로 반환될 수 있습니다. 사용자는 이 부분을 확인하여야 합니다.
연습: 데이터 샘플링
목표: 데이터 베이스에서 데이터 샘플링 중 원하는 데이터 검색
2. 결과를 유용하게 해보자!
목표는…
모든 데이터 샘플링 기록 가져와서 이력 표시와 유사한 방식으로 표시하기!
- 1.data_index 필드를 삭제합니다.
-
2. Timestamp(unix time)은 알아볼 수 없으니 알아볼 수 있는 형태로 변환합니다.
FROM_UNIXTIME(`time@timestamp`)
-
또한 밀리 초 정보를 삭제합니다.
FROM_UNIXTIME(ROUND(`time@timestamp`,0))
-
3. 열 헤더, data_format_0, data_format_1 등 정확히 알아볼 수 없습니다. 또한 값에 소수점이 너무 많습니다.
-
4. 값의 소수점 너무 많습니다.
Temp/Humidity 데이터에는 소수점 이하 두 자리면 충분합니다..
-
5. BLOB를 나눕니다. 여기서는 바이너리 형식으로 저장된 STRING 데이터입니다.
-
요약:
SELECT
FROM_UNIXTIME(ROUND(`time@timestamp`,0)-8*3600) as 'Sample Time' ,
ROUND(data_format_0,2) as Temperature, ROUND(data_format_1,1) as Humidity, CONVERT(data_format_3 using ASCII) as Mode
FROM plantfactory.`cmt-4b3d_status_data`;
SELECT
FROM_UNIXTIME(ROUND(`time@timestamp`,0)-8*3600) as 'Sample Time' ,
ROUND(data_format_0,2) as Temperature,
ROUND(data_format_1,1) as Humidity,
CONVERT(data_format_3 using ASCII) as Mode
FROM plantfactory.`cmt-4b3d_status_data`;
참조
-
바로 변환 및 산술 실행
SELECT
FROM_UNIXTIME(ROUND(`time@timestamp`,0)-8*3600) as 'Sample Time' ,
ROUND(data_format_0,2) as ‘Temperature (°C)’,
ROUND(data_format_0*9/5+32,0) as ‘Temperature (°F)’,
ROUND(data_format_1,1) as Humidity,
CONVERT(data_format_3 using ASCII) as Mode
FROM plantfactory.`cmt-4b3d_status_data`;
연습: 데이터 샘플링
-
목표: 데이터 베이스에서 데이터 샘플링 중 원하는 데이터 검색
-
3. 이미 거의 완성되었지만 마지막 한 가지가 남았습니다.
5월 하순의 데이터 중 업무 시간 내의 주중 데이터만 보고 싶을 경우…
우리는 WHERE 절을 사용해야 합니다.
-
우선 순위가 지정된 데이터가 알맞는지 다시 한번 확인합니다.
연습: 이벤트 로그
-
목표: HMI에 나타나는 이벤트 로그를 다시 작성하기
-
모든 이벤트 정보 및 발생 시간? 방법은?
-
이벤트 테이블 확인
SELECT * from plantfactory.`cmt-4b3d_event`;
-
cmt-4b3d_event에는 발생한 이벤트에 대한 모든 관련 데이터가 포함됩니다
-
이벤트 테이블 확인
SELECT * from plantfactory.`cmt-4b3d_event_log`;
-
cmt-4b3d_event_log에는 각 이벤트에 대한 정보가 포함됩니다.
SELECT FROM_UNIXTIME(ROUND(`trigger_time@timestamp`,0)-8*3600) as 'Trigger Time', language1, WATCH1
FROM `cmt-4b3d_event` AS E, `cmt-4b3d_event_log` AS EL;
SELECT FROM_UNIXTIME(ROUND(`trigger_time@timestamp`,0)-8*3600) as 'Trigger Time', language1, WATCH1
FROM `cmt-4b3d_event` AS E, `cmt-4b3d_event_log` AS EL
WHERE E.event_log_index = EL.event_log_index;
- 이벤트 발생 횟수를 계산합니다. 발생한 이벤트 수에 따라 총 개수를 추적합니다.
-
SELECT TIME, EVENT
-
FROM TABLE_DATA, TABLE_FORMAT
-
WHERE
-
ORDER BY
끝~
고생하셨습니다!!
웨인텍 터치패널 및 HMI 통신 게이트웨이 데이터관리 솔루션 문의는
이지뷰코리아 공식 대리점
알비솔루션
T : 02-3667-8027
Mail : bsk0916@naver.com
[알비솔루션 - cMT Gateway시리즈] 이지뷰 HMI 최강 솔루션기업 알비솔루션입니다.
이지뷰/웨인텍 공식 대리점
rbsolution.modoo.at
댓글