Posting

Machbase의 최신 소식을 지금 만나보세요

[MACHBASE 기초] CSV로 데이터 추출

마크베이스를 운영하다보면 다른 시스템에서 활용하고자 특정 조건의 데이터를 추출하여 달라는 요구사항이 발생한다.
이 때 마크베이스에서는 다양한 방법으로 저장된 데이터를 외부 파일로 export 할 수 있다.
일반적으로 많이 사용하는 CSV 형식으로 데이터를 추출하여 저장하는 방법에 대해서 알아본다.
마크베이스에 저장된 모든 데이터에 대한 추출이 아니라 특정 조건의 부분 데이터를 추출하는 방법에 대한 설명이다.
데이터 추출 범위가 큰 경우 대량 데이터에 대한 조회가 발생할 가능성도 있다. 
따라서 CPU, Memory, Disk I/O 등 하드웨어 자원이 부족한 경우에는 실제 운영 서비스에 영향을 줄 수 있으므로 
이러한 배치성 작업을 수행할 때는 유휴시간에 작업을 진행하거나 유휴시간을 확보하기 어려운 경우에는 
시스템 자원에 대한 모니터링을 병행하면서 검색 범위에 대한 조건을 조정할 필요가 있다.
검색 조건을 축소하면 작업을 한번에 완료하는 것이 아니라 여러 번 나눠서 수행해야하는 번거러움은 있지만 
시스템 부하를 적게 하여 본 서비스에 영향을 최소화 할 수 있다.

마크베이스 Tag 튜토리얼 데이터셋을 기반으로 데이터를 입력하고 데이터를 추출하여 저장하는 방법을 아래에 순서대로 설명한다.

데이터셋 준비

마크베이스가 제공하는 github에 보면 다양한 사례의 repositories가 있는데 그중에서 Tag Tutorial 4번 사례를 이용하여 데이터를 준비하도록 한다.

1) 아래 github 의 파일을 로컬 서버에 다운로드 받는다. 
https://github.com/MACHBASE/TagTutorial/tree/master/edu_4_house_sensor

2) 아래 순서로 진행해서 데이터를 입력한다.

– 원본 데이터 파일을 저장할 테이블을 생성한다.

$ machsql -s localhost -u sys -p manager -f 1_create_table.sql

– TAG 테이블을 생성한다.

$ machsql -s localhost -u sys -p manager -f 1_create_tag.sql

– 태그 메타 데이터를 입력한다.

$ machsql -s localhost -u sys -p manager -f 2_meta.sql

– 원본 압축 데이터의 압축을 해제한다.

$ unzip energydata_complete.zip

– CSV 데이터를 원본 테이블에 입력한다.

$ sh 3_load.sh

– 원본 테이블에 저장된 데이터를 TAG 테이블에 변환하여 입력한다.

$ machsql -s localhost -u sys -p manager -f 4_to_tag.sql

– 입력된 데이터의 값을 확인한다.

Mach> select count(*) from tag;
count(*)
-----------------------
552580

Mach> select min(time), max(time) from tag;
min(time) max(time)
-------------------------------------------------------------------
2016-01-11 17:00:00 000:000:000 2016-05-27 18:00:00 000:000:000

Mach> select * from _tag_meta limit 10;
_ID NAME
----------------------------------------------------------
1 ETAG_APPLIANCES
2 ETAG_LIGHTS
3 ETAG_T1
4 ETAG_RH_1
5 ETAG_T2
6 ETAG_RH_2
7 ETAG_T3
8 ETAG_RH_3
9 ETAG_T4
10 ETAG_RH_4

입력된 데이터중에서 ETAG_T1, ETAG_T2, ETAG_T3 3개 센서에 대한 2016년 4월 한달 동안의 원본 데이터를 추출하여
CSV 파일로 저장하는 3가지 방법에 대해서 설명한다. 
machsql을machsql을 이용하는 방법, save data 명령어를 이용하는 방법 그리고 machloader를 이용하는 방법이 있다.

Machsql

먼저 machsql 유틸리티를 이용하는 방법이 있다. machsql은 SQL 쿼리문을 작성한 스크립트 파일을 옵션으로 받아서 실행할 수가 있는데
그 결과를 외부 파일로 저장하는 것도 가능하다. 아래 순서대로 진행하면 된다.

1) 추출하고자 하는 검색 조건의 SQL 구문을 작성하여 파일로 저장한다.
아래 쿼리문을 sel.sql 로 저장한다.

SELECT * FROM tag
WHERE name IN ( 'ETAG_T1', 'ETAG_T2', 'ETAG_T3')
AND time >= TO_DATE('2016-04-01 00:00:00') AND TIME < TO_DATE('2016-05-01 00:00:00');

2) 다음과 같이 -f 옵션과 -o 옵션을 이용하여 데이터를 csv 파일로 추출하여 저장한다.

$ machsql -s localhost -u sys -p manager -f sel.sql -o machsql_output.csv
=================================================================
 Machbase Client Query Utility
 Release Version 6.0.5.official
 Copyright 2014 MACHBASE Corporation or its subsidiaries.
 All Rights Reserved.
=================================================================
MACHBASE_CONNECT_MODE=INET, PORT=5656
Type 'help' to display a list of available commands.
Mach> SELECT * FROM tag
WHERE name IN ( 'ETAG_T1', 'ETAG_T2', 'ETAG_T3')
AND time >= TO_DATE('2016-04-01 00:00:00') AND TIME < TO_DATE('2016-05-01 00:00:00');

Total : 12960 record(s) saved.

생성된 파일의 내용을 확인하면 다음과 같다. 
즉 문자열 칼럼은 따옴표("")로 감싸줘 있고 날짜형 컬럼은 나노초까지 표현하고 있다.

$ head -n 10 machsql_output.csv
"ETAG_T1",2016-04-01 00:00:00 000:000:000,22.39
"ETAG_T1",2016-04-01 00:10:00 000:000:000,22.39
"ETAG_T1",2016-04-01 00:20:00 000:000:000,22.3567
"ETAG_T1",2016-04-01 00:30:00 000:000:000,22.29
"ETAG_T1",2016-04-01 00:40:00 000:000:000,22.29
"ETAG_T1",2016-04-01 00:50:00 000:000:000,22.29
"ETAG_T1",2016-04-01 01:00:00 000:000:000,22.23
"ETAG_T1",2016-04-01 01:10:00 000:000:000,22.2
"ETAG_T1",2016-04-01 01:20:00 000:000:000,22.1333
"ETAG_T1",2016-04-01 01:30:00 000:000:000,22.1

3) 데이터 출력 형식을 원하는 포맷으로 지정하고자 할 때는 마크베이스에서 지원하는 함수를 이용하여 
쿼리문을 작성하여 수행하면 된다. 아래는 날짜형 칼럼을 초단위까지 형식으로 지정하여 출력한다.
아래 쿼리문으로 sel2.sql 로 저장하고 실행하면 machsql_output2.csv 파일이 생성된다.

SELECT name, to_char(time,'YYYY-MM-DD HH24:MI:SS') time, value FROM tag
WHERE name IN ( 'ETAG_T1', 'ETAG_T2', 'ETAG_T3')
AND time >= TO_DATE('2016-04-01 00:00:00') AND TIME < TO_DATE('2016-05-01 00:00:00');
 
machsql -s localhost -u sys -p manager -f sel2.sql -o machsql_output2.csv
=================================================================
 Machbase Client Query Utility
 Release Version 6.0.5.official
 Copyright 2014 MACHBASE Corporation or its subsidiaries.
 All Rights Reserved.
=================================================================
MACHBASE_CONNECT_MODE=INET, PORT=5656
Type 'help' to display a list of available commands.
Mach> SELECT name, to_char(time,'YYYY-MM-DD HH24:MI:SS') time, value FROM tag
WHERE name IN ( 'ETAG_T1', 'ETAG_T2', 'ETAG_T3')
AND time >= TO_DATE('2016-04-01 00:00:00') AND TIME < TO_DATE('2016-05-01 00:00:00');

Total : 12960 record(s) saved.

파일의 내용을 확인하면 날짜형 칼럼이 초단위까지만 출력한다.

$ head -n 10 machsql_output2.csv
"ETAG_T1","2016-04-01 00:00:00",22.39
"ETAG_T1","2016-04-01 00:10:00",22.39
"ETAG_T1","2016-04-01 00:20:00",22.3567
"ETAG_T1","2016-04-01 00:30:00",22.29
"ETAG_T1","2016-04-01 00:40:00",22.29
"ETAG_T1","2016-04-01 00:50:00",22.29
"ETAG_T1","2016-04-01 01:00:00",22.23
"ETAG_T1","2016-04-01 01:10:00",22.2
"ETAG_T1","2016-04-01 01:20:00",22.1333
"ETAG_T1","2016-04-01 01:30:00",22.1

Save Data

save data는 machsql 상에서 동작하는 쿼리문으로 질의 결과를 csv 데이터 파일로 바로 저장한다.
기본적으로 csv 포맷으로 동작하지만 필요에 따라 저장되는 필드의 구분자를 지정할 수 있고, 
칼럼명으로 csv 헤더를 생성할 수 있으며,출력 데이터 파일의 인코딩 포맷을 지정할 수도 있다.

상대 경로를 지정하는 경우 결과 파일은 $MACHBASE_HOME 에 파일이 생성된다.
절대 경로로 지정하면 해당 경로에 결과 파일이 생성된다.
또한 save data는 모든 데이터 칼럼을 문자열로 변형해서 결과를 출력한다.
날짜형 데이터를 unixtimestamp 로 변환해서 출력한다.

자세한 사항은 아래 메뉴얼을 참조하면 된다.
http://krdoc.machbase.com/display/MANUAL6/SELECT#SELECT-SAVEDATA

1) machsql 에 접속해서 아래 SQL 구문을 실행한다.

Mach> save data into 'save_output.csv' as select * from tag WHERE name IN ( 'ETAG_T1', 'ETAG_T2', 'ETAG_T3') 
and time >= TO_DATE('2016-04-01 00:00:00') AND TIME < TO_DATE('2016-05-01 00:00:00');

12960 row(s) saved.

아래 결과를 보면 모든 칼럼이 쌍따옴표로 감싸줘 있고, 날짜형은 unixtimestamp값으로 변환되어서 출력되었다.
또한 파일명만 지정하였기 때문에 해당 파일이 $MACHBASE_HOME 에 생성되었다.

$ head -n 10 $MACHBASE_HOME/save_output.csv
"ETAG_T1","1459436400000000000","22.390000"
"ETAG_T1","1459437000000000000","22.390000"
"ETAG_T1","1459437600000000000","22.356667"
"ETAG_T1","1459438200000000000","22.290000"
"ETAG_T1","1459438800000000000","22.290000"
"ETAG_T1","1459439400000000000","22.290000"
"ETAG_T1","1459440000000000000","22.230000"
"ETAG_T1","1459440600000000000","22.200000"
"ETAG_T1","1459441200000000000","22.133333"
"ETAG_T1","1459441800000000000","22.100000"

2) 쌍따옴표를 제외하고 출력하려면 아래와 같이  fields enclosed by  구문을 추가하여 추출하면 된다.

Mach> save data into 'save_output2.csv' fields enclosed by '' as select name,to_char(time,'YYYY-MM-DD HH24:MI:SS') time, value
 from tag WHERE name IN ( 'ETAG_T1', 'ETAG_T2', 'ETAG_T3') and time >= TO_DATE('2016-04-01 00:00:00') 
AND TIME < TO_DATE('2016-05-01 00:00:00');
12960 row(s) saved.
$ head -n 10 $MACHBASE_HOME/save_output2.csv
ETAG_T1,2016-04-01 00:00:00,22.390000
ETAG_T1,2016-04-01 00:10:00,22.390000
ETAG_T1,2016-04-01 00:20:00,22.356667
ETAG_T1,2016-04-01 00:30:00,22.290000
ETAG_T1,2016-04-01 00:40:00,22.290000
ETAG_T1,2016-04-01 00:50:00,22.290000
ETAG_T1,2016-04-01 01:00:00,22.230000
ETAG_T1,2016-04-01 01:10:00,22.200000
ETAG_T1,2016-04-01 01:20:00,22.133333
ETAG_T1,2016-04-01 01:30:00,22.100000

Machloader

machloader는 기본적으로 특정 테이블의 전체 데이터를 export할 때 사용하는 툴이다.
하지만, 검색 조건을 지정하여 해당 데이터만 export 하는 방법이 있다.
아래와 같은 순서로 진행하면 된다.

자세한 사용법은 아래 메뉴얼을 참조하면 된다.
http://krdoc.machbase.com/display/MANUAL6/MACHLOADER

1) 데이터 스키마 파일 생성
export할 대상 테이블의 스키마를 기반으로 저장할 데이터 파일의 스키마를 설정하는 파일을 먼저 생성한다.
아래와 같이 테이블명과 스키마 파일을 지정하면 파일이 생성된다.

$ machloader -c -t tag -f tag.fmt
-----------------------------------------------------------------
 Machbase Data Import/Export Utility.
 Release Version 6.0.5.official
 Copyright 2014, MACHBASE Corporation or its subsidiaries.
 All Rights Reserved.
-----------------------------------------------------------------
NLS : US7ASCII EXECUTE MODE : SCHEMA
TARGET TABLE : tag SCHMEA FILE : tag.fmt
ARRIVAL_TIME : FALSE

Table schema file is created.[tag.fmt]

생성된 tag.fmt 파일을 vi 등 편집기로 아래와 같이 수정하고 저장한다.
요점은 DATEFOMRMAT 으로 TIME칼럼의 날짜 형식을 지정하는 것이고
DOWNLOAD CONDITION 으로 WHERE절의 검색 조건을 지정하는 것이다.

table tag
{
NAME varchar (32);
TIME datetime;
VALUE double;
}
DATEFORMAT TIME "YYYY-MM-DD HH24:MI:SS"
DOWNLOAD CONDITION "name IN ( 'ETAG_T1', 'ETAG_T2', 'ETAG_T3') and time >= TO_DATE('2016-04-01 00:00:00') 
AND TIME < TO_DATE('2016-05-01 00:00:00')"

2) 기본 옵션 사용법
    추가 옵션없이 Machloader 를 이용하여 데이터를 추출하는 방법은 아래와 같이 진행하면 된다.

$ machloader -o -f tag.fmt -d machloader_output.csv
-----------------------------------------------------------------
 Machbase Data Import/Export Utility.
 Release Version 6.0.5.official
 Copyright 2014, MACHBASE Corporation or its subsidiaries.
 All Rights Reserved.
-----------------------------------------------------------------
NLS : US7ASCII EXECUTE MODE : EXPORT
SCHMEA FILE : tag.fmt DATA FILE : machloader_output.csv
FIELD TERM : , ROW TERM : \n
ENCLOSURE : " ESCAPE : \
ARRIVAL_TIME : FALSE ENCODING : NONE
HEADER : FALSE CREATE TABLE : FALSE

==> 대량의 데이터인 경우 결과 건수를 조회하는데 시간이 걸리므로 아래 Progress bar가 나타나기 까지 시간이 걸릴 수도 있다.

Progress bar Exported records Total records
 12960 12960

Total 12960 records exported
Export time : 0 hour 0 min 0.33 sec

결과 파일을 확인해보면 문자열은 쌍따옴표("")로 감싸져 있다.

$ head -n 10 machloader_output.csv
"ETAG_T1",2016-04-01 00:00:00,22.39
"ETAG_T1",2016-04-01 00:10:00,22.39
"ETAG_T1",2016-04-01 00:20:00,22.3567
"ETAG_T1",2016-04-01 00:30:00,22.29
"ETAG_T1",2016-04-01 00:40:00,22.29
"ETAG_T1",2016-04-01 00:50:00,22.29
"ETAG_T1",2016-04-01 01:00:00,22.23
"ETAG_T1",2016-04-01 01:10:00,22.2
"ETAG_T1",2016-04-01 01:20:00,22.1333
"ETAG_T1",2016-04-01 01:30:00,22.1

3) 추가 옵션 사용법

문자열 칼럼에 포함된 쌍따옴표를 제거하려면 enclosing character 옵션으로 처리하면 된다.

$ machloader -o -f tag.fmt -d machloader_output2.csv -e ''
-----------------------------------------------------------------
 Machbase Data Import/Export Utility.
 Release Version 6.0.5.official
 Copyright 2014, MACHBASE Corporation or its subsidiaries.
 All Rights Reserved.
-----------------------------------------------------------------
NLS : US7ASCII EXECUTE MODE : EXPORT
SCHMEA FILE : tag.fmt DATA FILE : machloader_output2.csv
FIELD TERM : , ROW TERM : \n
ENCLOSURE : ESCAPE : \
ARRIVAL_TIME : FALSE ENCODING : NONE
HEADER : FALSE CREATE TABLE : FALSE

Progress bar Exported records Total records
 12960 12960

Total 12960 records exported
Export time : 0 hour 0 min 0.30 sec

결과 파일을 확인하면 아래과 같이 문자열 칼럼에 쌍따옴표가 없이 출력되었다.

$ head -n 10 machloader_output2.csv
ETAG_T1,2016-04-01 00:00:00,22.39
ETAG_T1,2016-04-01 00:10:00,22.39
ETAG_T1,2016-04-01 00:20:00,22.3567
ETAG_T1,2016-04-01 00:30:00,22.29
ETAG_T1,2016-04-01 00:40:00,22.29
ETAG_T1,2016-04-01 00:50:00,22.29
ETAG_T1,2016-04-01 01:00:00,22.23
ETAG_T1,2016-04-01 01:10:00,22.2
ETAG_T1,2016-04-01 01:20:00,22.1333
ETAG_T1,2016-04-01 01:30:00,22.1

맺음말

지금까지 마크베이스에 저장된 데이터를 csv 형식의 외부 파일로 저장하는 3가지 방법에 대해서 알아보았다.
machsql을 이용하는 방법, save data 구문을 활용하는 방법 그리고 machloader를 이용하는 방법이 있다.
여기에서 설명하지는 않았지만 마크베이스가 제공하는 CLI, OBDC, JDBC, .NET 등 다양한 SDK를 이용하여
직접 export 어플리케이션을 개발하여 좀 더 자유자재로 원하는 데이터를 추출하여 저장할 수도 있다.

연관 포스트

C언어로 Binary data를 Machbase에 넣기

1.개요 Data를 다루다 보면 numeric, varchar 형 데이터뿐만 아니라 JPG, PNG, MP3와 같은 Binary data도 database에 저장해야 할 때가 존재한다. 그러나 일반 data들과는 달리 Binary

[MACHBASE 연동] Android studio에서 JDBC 연결하기

마크베이스 기술지원본부 이현민 1. 개요 수많은 데이터들이 많은 환경에서 생성되고 있는 오늘날, 우리 현대인들의 동반자인 스마트폰 또한 데이터생성의 주체로써 또는 전달자로서 알게 모르게 그 구실을