Posting

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

[MACHBASE R] 천만건 데이터 분석

마크베이스(Machbase) 기반  천만건 데이터 실전 R 분석 예제

빅데이터 분석과 R의 한계

알려진 많은 R의 장점에도 불구하고, 빅데이터 분석에는 어렵다고  알려졌는데, 그 이유는 메모리 기반의 분석 구조 때문이다.

R 프로그램은 일반적으로 데스크탑 혹은 서버라고 하더라도 해당 단일 시스템이 가진 메모리의 최대치만을 활용할 수 있기 때문에 데이터량이 수백 만건이 넘어가는 경우에는 메모리 부족으로 처리가 불가능한 상황이 종종 발생한다.

그래서, 많은 사람들이 이러한 메모리 부족 문제를 해결하기 위해 데이터에 대한 선처리(preprocessing) 작업을 미리 하게 된다.

그러나, 컴퓨터에 익숙치 않거나, 프로그래밍에 미숙한 분석가들의 한계는 이러한 선처리를 위해 파이썬과 같은 프로그래밍 언어를 배우기도 힘들 뿐만 아니라, 파이썬과 같은 인터프리터 언어가 가진 성능의 한계로 인해서 쉽지 않은 상황이다.

R 분석과 마크베이스 활용

마크베이스의 가장 큰 장점이라고 한다면 데이터 로딩과 재처리의 성능이 매우 탁월하다는 것이다.

실시간 데이터 처리에 강점이 있을 뿐만 아니라,이런 배치 분석에 있어서도 사용자의 시간과 비용을 낮출 것으로 예상되는데,

이 블로그에서는 분석 과정을  몇 가지 관점에서 보도록 한다.

  1. 대량의 빅데이터를 얼마나 빠르게 로딩할 수 있을까?
  2. 데이터 선처리(변환)를 얼마나 빠르게 할 수 있는가?
  3. R의 메모리 한계를 넘어설 수 있는 정도의 분석이 가능한가?

어쨌든 프로그래밍 언어를 쓰지 않고, 단순히 SQL 만으로 위의 목표를 달성할 수 있다면 그것 또한 멋진 일이 아닌가 생각된다.

수행 환경

여기서도 리눅스의 우분투 14.04 환경에서 연동을 해 보는 것으로 한다.

그러나, 다른 환경(윈도우 포함) 에서도 크게 어려움은 없을 것으로 생각된다.

우선 해당 리눅스 시스템에 마크베이스가 설치되어 있고, ODBC 및 R도 함께 설치된 것을 가정한다.

만일 그렇지 않다면, 관련 블로그를 참고해서 설치하기 바란다.

마크베이스(Machbase) 우분투(Ubuntu) 설치기

마크베이스(Machbase) 를 리눅스에서 ODBC를 연동해 보자.

마크베이스(Machbase) 와 R과 연동하기 (리눅스 환경)

분석 예제 : 1년간의 습도 데이터 분석 (천만건)

목표: 어느 특정 지역에서 1년간 측정된 습도(humidity)의 분포를 확인하고, 가장 습도가 높은 날과 시간을 찾는 것!

그러나, 이 분석에는 기존의 R 혹은 엑셀로 분석하기 위해서는 정말 쉽지 않은 몇 가지 난제가  존재한다.

  1. 데이터 량의 크기 문제 : 빅데이터 
    1. 1초에 한번씩 측정을 했다고 가정하면 전체 데이터의 갯수가 약 3천1백만건이 넘어간다. 
    2. 물론, 센서 데이터의 크기가 값이라서 작을 수 있지만, 만일 센서의 종류가 여러 개라면 그 데이터 크기 또한 기하급수적으로 늘어난다.
    3. 만일 1년이 아니라, 30년치를 분석해야 한다면? (아무리 적어도 4억건 이상의 데이터이다)
    4. 일반 데스크탑 컴퓨터에서 분석은 언감생심일 것이다.
  2. 데이터 품질 보장을 위한 변환 문제
    1. 천만건이 넘어가게 되면, 텍스트 파일의 크기도 수백메가에서 기가급으로 변한다.
    2. 그런데, 받은 데이터가 온전하다는 보장이 없다.
    3. 경험상으로도 숫자가 올 곳에 문자가 오기도하고, 데이터가 빠지기도 하고, 센서의 값이 범위를 아예 넘어가기도 한다.
    4. 이를 위해서는 프로그래밍을 통해서 정련을 해야 하는데, 매번 파이썬과 같은 언어로 프로그래밍을 하는 것이 과연 바른 접근인가?
  3. 데이터 로딩 문제
    1. 다행히 데이터를 변환해서 데이터 건수가 줄면 다행이지만, 여전히  수천만건의 데이터가 남아 있다면 어떻게 R에 로깅해야 할 것인지?
    2. 엑셀의 경우는 기본적으로 백만건 이상의 한 쉬트에 로딩할 수 없다.
    3. R의 경우에도 앞에서 언급했듯이 메모리 상에 모두 로딩해야 하고, 더 큰 문제는 로딩하면서 원시 데이터의 크기보다 더 커진다는 것이다.
    4. 그래서, 해당 시스템의 메모리 한계가 문제가 종종 된다.

그래서, 일반적인 R로 분석이 쉽지 않은 예제를 준비했다.

데이터 및 스크립트 다운로드

해당 데이터 및 관련 소스는 다음과 같은 GitHub 에서 받을 수 있고, 이후의 내용은 그 소스를 참조해서 진행하겠다. https://github.com/sjkim1971/Machbase_R_Analytics

위 사이트에서 zip 파일로 다운로드를 받고, 압축을 풀면 다음과 같은 파일이 존재한다.

sjkim2@sjkim-Precision-T1700:~/work/Machbase_R$ unzip Machbase_R_Analytics-master.zip
Archive: Machbase_R_Analytics-master.zip
402f7991b2572996f277e8548f1d09ea7201cafa
creating: Machbase_R_Analytics-master/
sjkim2@sjkim-Precision-T1700:~/work/Machbase_R$ cd Machbase_R_Analytics-master
sjkim2@sjkim-Precision-T1700:~/work/Machbase_R/Machbase_R_Analytics-master$ ls -al
-rw-rw-r-- 1 sjkim2 sjkim2 984 8월 12 10:05 README.md
-rw-rw-r-- 1 sjkim2 sjkim2 10300 8월 12 10:05 calendarHeatmap.R
-rw-rw-r-- 1 sjkim2 sjkim2 250 8월 12 10:05 cleansing.sql
-rw-rw-r-- 1 sjkim2 sjkim2 209 8월 12 10:05 create_table.sql
-rw-rw-r-- 1 sjkim2 sjkim2 1682 8월 12 10:05 humidity.R
-rw-rw-r-- 1 sjkim2 sjkim2 26214400 8월 12 10:05 humidity1.split
-rw-rw-r-- 1 sjkim2 sjkim2 21973348 8월 12 10:05 humidity2.split
-rw-rw-r-- 1 sjkim2 sjkim2 76 8월 12 10:05 merge.sh
-rw-rw-r-- 1 sjkim2 sjkim2 188 8월 12 10:05 run.sh

원시 데이터 복원 및 준비 완료압축이 풀렸다!

그러나, 위와 파일을 보면, CSV 파일은 없고, 확장자가 split 이라고 된, 크 파일이 두개 존재한다.

이 파일을 합쳐서 원시 데이터 CSV 파일을 복원하자.

merge.sh를 수행하면 되는데, 다음과 같다.

$ cat merge.sh
cat humidity1.split humidity2.split >humidity.csv.gz
sjkim2@sjkim-Precision-T1700:~/work/Machbase_R/Machbase_R_Analytics-master$ sh merge.sh
sjkim2@sjkim-Precision-T1700:~/work/Machbase_R/Machbase_R_Analytics-master$ ls -al
합계 303596
drwxrwxr-x 2 sjkim2 sjkim2 4096 8월 12 10:15 .
drwxrwxr-x 3 sjkim2 sjkim2 4096 8월 12 10:12 ..
-rw-rw-r-- 1 sjkim2 sjkim2 984 8월 12 10:05 README.md
-rw-rw-r-- 1 sjkim2 sjkim2 10300 8월 12 10:05 calendarHeatmap.R
-rw-rw-r-- 1 sjkim2 sjkim2 250 8월 12 10:05 cleansing.sql
-rw-rw-r-- 1 sjkim2 sjkim2 209 8월 12 10:05 create_table.sql
-rw-rw-r-- 1 sjkim2 sjkim2 1682 8월 12 10:05 humidity.R
-rw-rw-r-- 1 sjkim2 sjkim2 262645209 8월 12 10:15 humidity.csv
-rw-rw-r-- 1 sjkim2 sjkim2 26214400 8월 12 10:05 humidity1.split
-rw-rw-r-- 1 sjkim2 sjkim2 21973348 8월 12 10:05 humidity2.split
-rw-rw-r-- 1 sjkim2 sjkim2 76 8월 12 10:05 merge.sh
-rw-rw-r-- 1 sjkim2 sjkim2 188 8월 12 10:05 run.sh
sjkim2@sjkim-Precision-T1700:~/work/Machbase_R/Machbase_R_Analytics-master$


이제 분석에 들어갈 모든 준비가 다 되었다!위에서 humidity.csv 가 복원된 것을 볼 수 있다.

$ ls humidity.csv
humidity.csv

원시 데이터 파일 훓어 보기

파일 정보

원시 데이터 파일 humidity.csv의 크기는 약 262M 이고, 라인수는 10,101,739이며, 약 천만 라인이 넘는다.

wc humidity.csv
 10101739  10101739 262645209 humidity.csv

아래는 실제 파일의 앞부분 30라인 정도를 표시한 것이다. (이 데이터에는 실제로 여러가지 노이즈를 포함시켰다) CSV 포맷으로서 컬럼은 좌측으로부터 연도, 월, 일, 시, 분, 초, 센서값 (습도 0 ~ 100) 으로 되어 있다.

$ head -n 30 humidity.csv
2016,01,01,00,00,01,40.74
2016,01,01,00,00,07,41.12
2016,01,01,00,00,15,41.26
2016,01,01,00,00,15,12340
2016,01,01,00,00,19,40.61
2016,01,01,00,00,19,41.15
2016,01,01,00,00,19,thisisnotnumber
2016,01,01,00,,,
2016,01,01,00,00,22,9999999
2016,01,01,00,00,22,41.63
2016,01,01,00,00,27,-1927
2016,01,01,00,00,35,41.29
2016,01,01,00,00,43,41.21
2016,01,01,00,00,47,41.78
2016,01,01,00,00,50,41.30
2016,01,01,00,00,50,41.84
2016,01,01,00,,
2016,01,01,00,00,51,41.58
2016,01,01,00,00,51,41.96
2016,01,01,00,00,56,43.07
2016,01,01,00,00,59,41.67
2016,01,01,00,01,03,41.19
2016,01,01,00,01,05,40.39
2016,01,01,00,01,05,40.93
2016,01,01,00,01,07,40.85
2016,01,01,00,01,09,41.09
2016,01,01,00,01,09,41.72
2016,01,01,00,01,09,-9999
2016,01,01,00,01,18,41.33
2016,01,01,00,01,24,40.52

데이터 노이즈 종류

위의 CSV 파일은 그대로 사용할 수 없는데, 그 이유는 원시 데이터에 다양한 형태의 노이즈가 포함되어 있기 때문이다.

이러한 노이즈는 원시 파일 생성시에 다양한 이유로 추가된 것이며, 반드시 분석 과정에서 제거되거나, 연산의 대상에서 빠져야 한다.

이 작업이 사실 분석의 80% 이상의 시간과 비용을 잡아먹는 주 원인이다.

  • 데이터 타입 오류
    • 위의 2번과 같은 형태인데, 실제 숫자형이 기록되어야 하는 센서 값 부분에 문자열이 기록되어 있다.
    • 이러한 경우 해당 데이터가  제거되어야 함은 물론이다.
  • 데이터 범위 오류
    • 위의 1, 4, 5, 7 의 경우인데, 습도는 0에서 100사이의 값이 들어가야 하나, 음수 혹은 100보다 큰 값이 기록되어 있다.
    • 데이터 타입은 올바르지만, 논리적인 오류로서  제거되어야 하는 오류 데이터이다.
  • 데이터 누락 
    • 3번과 같은 경우인데, 해당 필드의 값이 아예 기록되지 않은 경우이다.
    • 이 경우 해당 값을 특정 값으로 채우든지 아니면, 분석 대상에서 제거되어야 한다.
  • CSV 포맷 오류
    • 6번과 같은 경우인데, CSV 포맷을 지키지 않는 경우이다.
    • 이 경우에는 최초의 파일 생성시에 오류가 추가되거나, 합성 과정에서의 버그로 인한 것들일 것이다. 물론, 분석 대상에서 제거되어야 한다.

이 파일을 보는 것만으로도 답답하다…일단 천만개의 레코드이므로 MS 엑셀로 로딩이 안될 뿐만 아니라, R로 로딩하기에도 만만치 않고, 얼마나 시간이 걸릴지 고민스럽다.

또한, 중간 중간에 노이즈 값이 포함되어 있어서 간단하게 제거하는 것도 쉬워보이지 않는다.

물론 현재 이 파일의 크기가 262M 이므로 R로 로딩해서 어찌어찌 할 수 있을 것이라고 생각할 수 있지만, 이 파일의 크기가 20G라면 어찌할 것인가?

원시 데이터 파일 클랜징 하기 (노이즈 제거 및 정리)

클랜징 단계 정리

이렇게 노이즈가 많은 데이터를 어떻게든 조작을 하기 위해서는 마크베이스를 활용해서  다음과 같은 단계를 거쳐서 작업을 해 보자.

  1. 텍스트 기반의 더미 테이블 데이터 로딩
    1. 이 의미는 가능한 한 노이즈가 있는 원본 데이터를 그대로 마크베이스 내부에 로딩하는 것이다.
    2. 물론, 숫자형과 문자형이 뒤섞여 있고, 데이터가 틀릴 수 있지만, 모든 데이터를 텍스트로 가정하면 로딩 가능한 모든 데이터는 일단 로딩이 된다.
    3. 이렇게 로딩이 되었다는 의미는 SQL을 통해서 어떤 식으로든 우리가 변환을 할 수 있는 강력한 환경을 보유했다는 의미이기도 하다.
    4. 이 과정에서 CSV 포맷이 아닌 정말 비정상적인 레코드가 걸러지게 된다. (6번 타입 에러)
  2. 데이터 타입 기반의 데이터 재구성
    1. 1번을 통해 원시 데이터의 형태나 특성을 파악했으면, 원래의 데이터 타입에 맞도록 변환을 하는 과정이다.
    2. 1번에서는 무조건 텍스트 타입이었지만, 이 과정에서는 숫자형 혹은 날짜형으로 타입을 지정하고, 연산이 가능하도록 변환하는 것이다.
    3. 이 과정에서는 원래 자신의 데이터 타입이 아닌 것들(예를 들면 숫자형 필드에 문자형이 온 것들)이 걸리지게 된다. (2번 타입 에러)
  3. 논리적 데이터 재구성
    1. 2번을 통해 데이터 타입이 제대로 입력이 되었다면, 이제 논리적으로 해당 데이터의 범위가 올바른지 판단할 때다.
    2. 이번 예제에서 볼 수 있듯이 습도는 0과 100사이에 있는데 이 값을 벗어난 것은 오류이므로 불필요하다.
    3. 결과적으로 이 과정에서 1, 4, 5, 7 타입의 에러가 걸리지게 되는 것이다.
  4. 마무리 및 R 연동 
    1. 여기에서는 거의 완전한 데이터가 별도의 테이블로 존재하는 단계이다.
    2. 이제부터는 강력한 SQL을 통해 데이터를 여러가지로 분류, 변환, 추출, CSV 생성 등이 가능한 단계이다.
    3. 이 단계에서 R과 연동을 통해 분석을 하면 된다.

더미 테이블 생성

앞에서 언급한 바와 같이 원시 데이터를 마크베이스로 모두 로딩해 보자.

다행히 마크베이스에서는 자동적으로 CSV를 파악해서 테이블 생성 및  로딩까지 해 주는 도구인 csvimport를  제공하고 있다.

csvimport에서 -d는 원시 CSV 파일, -t 는 대상 테이블인데 -C 옵션을 주면, 알아서 컬럼까지 추가해서 모두 텍스트 형태로 로딩해 준다.

-b 옵션은 로딩시에 발생한 에러를 로깅해서 사용자에게 알려주는 것이다.수행 결과는 아래와 같다.

sjkim2@myhost:~/SensorAnalyticsR$ csvimport -d humidity.csv -t humidity_dummy -C -b bad.log
-----------------------------------------------------------------
Machbase Data Import/Export Utility.
Release Version 3.5.0.826b8f2.community
Copyright 2014, InfiniFlux Corporation or its subsidiaries.
All Rights Reserved.
-----------------------------------------------------------------
NLS : US7ASCII EXECUTE MODE : IMPORT
TARGET TABLE : humidity_dummy DATA FILE : humidity.csv
BAD FILE : bad.log IMPORT_MODE : APPEND
FILED TERM : , ROW TERM : \n
ENCLOSURE : " ARRIVAL_TIME : FALSE
ENCODING : NONE HEADER : FALSE
CREATE TABLE : TRUE
Progress bar Imported records Error records
10101738 1
Import time : 0 hour 0 min 7.937 sec
Load success count : 10101738
Load fail count : 1

그럼 어떤 라인이 에러가 났는지 보자. 위 결과를 보면, 총 10101738 건 중에서 1건이 에러가 났고, 나머지는 모두 약 8초만에 로딩이 되었다.

sjkim2@myhost:~/SensorAnalyticsR$ cat bad.log
Errors occurred in row 17.
1 token : '2016'
2 token : '01'
3 token : '01'
4 token : '00'
5 token : ''
6 token : ''
Error: Data and schema have different field count.(6 != 7)
2016,01,01,00,,

그럼 테이블을 확인해 보면 17라인은 위에서 노이즈 중에 CSV 포맷에 맞지 않는 노이즈였고, 이 단계에서 성공적으로 제거된 것을 확인할 수 있다.

sjkim2@myhost:~/SensorAnalyticsR$ machsql
Mach> select count(*) from humidity_dummy;
count(*)
-----------------------
10101738
[1] row(s) selected.
Elapsed time: 0.001
 Mach> desc humidity_dummy;
[ COLUMN ]
----------------------------------------------------------------
NAME TYPE LENGTH
----------------------------------------------------------------
C0 varchar 32767
C1 varchar 32767
C2 varchar 32767
C3 varchar 32767
C4 varchar 32767
C5 varchar 32767
C6 varchar 32767 
Mach> select * from humidity_dummy limit 1;
C0
------------------------------------------------------------------------------------
C1
------------------------------------------------------------------------------------
C2
------------------------------------------------------------------------------------
C3
------------------------------------------------------------------------------------
C4
------------------------------------------------------------------------------------
C5
------------------------------------------------------------------------------------
C6
------------------------------------------------------------------------------------
2016
12
31
23
59
59
52.34
[1] row(s) selected.
Elapsed time: 0.001


실제 로딩 건수가 10101738 이고, 스키마는 컬럼이 C0 부터 C6 까지 자동으로 VARCHAR(32K)로 생성된 것을 알 수 있다.

또한, 데이터도 잘 들어간 것을 확인할 수 있다.

더미 데이터 검토

그 전에 실제 마크베이스에 어떤 형태의 데이터가 들어가 있는지 확인하는 작업을 해 보자.

NULL 데이터 확인

입력된 데이터 중에 NULL이 있는지 확인해 본다.

컬럼중에 NULL이 있다는 것은 CSV 포맷은 맞지만, 해당 데이터가 빠졌다는 의미이므로 이 데이터는 나중에 제거되어야 하기 때문이다.

아래와 같은 SQL로 확인하면,

Mach> select * from humidity_dummy where c0 is null or c1 is null or c2 is null or c3 is null or c4 is null or c5 is null or c6 is null;
2016
01
01
00
NULL
NULL
NULL
[1] row(s) selected.
Elapsed time: 15.787



16초 정도만에 천만건 중에서 2016년 1월 1일 0시 에 이후의 데이터가 없는 CSV가 입력된 것을 알 수 있다.

즉 숫자형 컬럼에 문자가 들어있는 것들말이다. 모든 컬럼이 사실상 숫자형 타입이므로 이 중에서 문자형이 있는지 검색하는 쿼리는 다음과 같다.

SELECT 해당컬럼 from HUMIDITITY_DUMMY WHERE 해당컬럼 is not NULL and TO_NUMBER_SAFE(해당컬럼) is NULL;

핵심은 TO_NUMBER_SAFE() 함수인데, 입력된 컬럼이 숫자일 경우에는 숫자를 리턴하고, 숫자로 변경될 수 없는 값이 입력되면 NULL이 리턴된다.어떤 의미인가 하면, 입력된 해당 컬럼의 텍스트 중에서 NULL이 아닌 것들 중에서 숫자가 아닌 값을 찾는 것이다.

따라서, 아래의 쿼리를 수행하면, 해당 에러 컬럼을 찾을 수 있다.

Mach> select * from humidity_dummy where c6 is not null and to_number_safe(c6) is NULL;
2016
01
01
00
00
19
thisisnotnumber
[1] row(s) selected.
Elapsed time: 4.357


찾았다. 2016년 1월 1일 0시 0분 19초 입력된 값이 숫자가 아니라는 것을 발견했다.

이것 역시 위와 유사하게 수행을 하되,  변환된 값의 범위가 넘어가는 것을 찾으면, 다음과 같다.

Mach> select c6 from humidity_dummy where c6 is not null and to_number_safe(c6) > 99 or to_number_safe(c6) < 0;
c6
------------------------------------------------------------------------------------
1938473
-33
491831.20
-192
1023
-9999
-1927
9999999
12340
[9] row(s) selected.
Elapsed time: 6.693

전체 천만건 중에서 9건이 데이터 범위를 벗어나는 논리적 오류가 발생했다는 것을 알 수 있다.

올바른 데이터 개수 확인

위와 같이 에러를 확인했으므로, 정확한 데이터 범위를 가지고, NULL 아닌 데이터 개수를 세어보자.

Mach> select count(*) from humidity_dummy where c6 is not NULL and c6 is not null
and to_number_safe(c6) < 100 and to_number_safe(c6) >= 0;
count(*)
-----------------------
10101727
[1] row(s) selected.
Elapsed time: 2.255
데이터 타입갯수비고
CSV 레코드 갯수10,101,739 건wc -l
csvimport시 에러1건bad.log 확인
값이 없이 입력된 것1건SQL로 확인
텍스트 오류로 입력된 것1건SQL로 확인
데이터 범위에러9건SQL로 확인
올바른 대상 데이터 레코드 갯수10,101,739 - 11= 10101727 

 이제 우리에게 필요한 데이터는 10101727 건이 되겠다.

정규 데이터 타입 테이블 생성

테이블 생성

이제 모든 데이터에 대한 확인이 끝났으므로,  텍스트형 데이터를 숫자형으로 바꾸어서 입력하면 두번째 클랜징이 될 것이다.

그래서, 해당 테이블 스키마를 다음과 같이 만들자. 소스코드에 create_table.sql을 활용하고 내용은 다음과 같다.

$ cat create_table.sql
DROP TABLE HUMIDITY;

CREATE TABLE humidity
(
    s_year    SHORT,
    s_month   SHORT,
    s_day     SHORT,
    s_hour    SHORT,
    s_minute  SHORT,
    s_second  SHORT,
    s_value   FLOAT
);

아래와 같이 데이터가 로딩될 테이블을 생성하자.

테이블이 생성되었다.아래와 같이 데이터가 로딩될 테이블을 생성하자.

$ machsql -f create_table.sql -s 127.0.0.1 -u sys -p manager
=================================================================
Machbase Client Query Utility
Release Version 3.5.0.826b8f2.community
Copyright 2014 InfiniFlux Corporation or its subsidiaries.
All Rights Reserved.
=================================================================
MACHBASE_CONNECT_MODE=INET, PORT=5656
Type 'help' to display a list of available commands.
Mach> DROP TABLE HUMIDITY;
Dropped successfully.
Elapsed time: 0.041
Mach> CREATE TABLE humidity
(
s_year SHORT,
s_month SHORT,
s_day SHORT,
s_hour SHORT,
s_minute SHORT,
s_second SHORT,
s_value FLOAT
);
Created successfully.
Elapsed time: 0.090

데이터 변환

다음은 더미 테이블로부터 데이터를 가져오는 과정이다.

앞에서 검증된 데이터를 아래와 같이 입력하자.

변환은 마크베이스에서 제공하는 INSERT INTO .. SELECT 구문을 활용하면 아주 쉽게 변환된다.

Mach> insert into humidity select to_number(c0), to_number(c1), to_number(c2),
to_number(c3), to_number(c4), to_number(c5), to_number(c6)
from humidity_dummy
where c6 is not NULL and c6 is not null and
to_number_safe(c6) < 100 and to_number_safe(c6) >= 0;
10101727 row(s) inserted.
Elapsed time: 27.162


이제 할 일은 온전한 데이터가 들어가 있는 humidity 테이블의 데이터를 R과 연동을 통해 분석하는 것만 남았다.아..모든 데이터 클랜징과 변환이 끝났다.

클랜징 데이터과 R 분석

R 수행 및 관련 모듈 로딩

이제 R을 수행하고, 하나씩 데이터를 분석해 보도록 하자.

이해의 도움을 위해 몇개의 그림을 그릴 수 있도록 하기 위해 부가 R 모듈을 로딩하자.

$ R
R version 3.4.1 (2017-06-30) -- "Single Candle"
...........
> library('RODBC')
> library('lattice')
> library('grid')
> library('chron')
> source("./calendarHeatmap.R");



그 중에 적절한 것을 소스코드에 넣었기 때문에 위와 같이 수행해서 직접 로딩하도록 한다.위에서 calendarHeadmap.R 은 1년의 캘린더의 데이터를 히트맵으로 보여주는 R 소스코드이다.

이제 모든 준비가 끝났다.

ODBC 접속 및 캘린더 히트맵 보기

아래와 같이 마크베이스로 접속하자.

> conn <- odbcConnect('machbase', believeNRows=FALSE, case="toupper");
> sqlTables(conn)
TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS
1 IFLUX SYS HUMIDITY TABLE
2 IFLUX SYS HUMIDITY_DUMMY TABLE
>

두개의 테이블이 존재하는 것을 확인할 수 있다.에러가 없다면 성공적으로 접속한 것이고, 테이블이 있는지 확인해 보자.

우리는 기특한 HUMIDITY 테이블을 사용할 것이다.

캘린더 히트맵 함수

히트맵 함수의 프로토타입은 다음과 같다.

calendarHeat(dates=dat$s_date, values=dat$s_max, color="g2r", varname="Humidity Max Value")

그러므로, 테이블 HUMIDITY에서 이러한 구조로 데이터를 뽑기만 하면, 이 함수를 잘 사용할 수 있을 것이다.첫번째 인자 dates는 "연-월-일" 구조의 텍스트이고, 두번째 인자 values는 실수형 숫자이며, color는 색깔, varname은 타이틀이다.

SQL 만들기

데이터베이스를 잘 모르는 사람은 조금 힘겨울 수 있으나, 5분만 집중하면 금방 이해할 수 있을 정도로 간단하다.

만들 쿼리는 하나이지만, 내부적으로는 크게 두 부분으로 나뉘어진다.

첫번째 부분은 HUMIDITY 테이블의 데이터로부터 일자별 통계 정보를 생성하는 부분이고 다음과 같다.

SELECT S_YEAR,
S_MONTH,
S_DAY,
MIN(S_VALUE) s_min,
MAX(S_VALUE) s_max,
AVG(S_VALUE) s_avg
FROM humidity
GROUP BY S_YEAR, S_MONTH, S_DAY
ORDER BY S_YEAR, S_MONTH, S_DAY;


GROUP BY는 연월일 순서대로 그룹을 만들라는 의미이고, ORDER BY는 연월일 순서대로 소팅을 하라는 의미이다.이 쿼리는 결과값이 연, 월, 일과 그 날의 최소, 최고, 평균 습도를 1월 1일부터 12월 31일까지 순서대로 출력하는 함수다.

즉, 총 일년 최대 366일의 결과 레코드가 생성되는 것이다.

한번 수행해 보면, 다음과 같이 쭉..출력된다.

Mach> SELECT S_YEAR, S_MONTH, S_DAY, MIN(S_VALUE) s_min, MAX(S_VALUE) s_max, AVG(S_VALUE) s_avg FROM humidity GROUP BY S_YEAR, S_MONTH, S_DAY ORDER BY S_YEAR, S_MONTH, S_DAY;
S_YEAR S_MONTH S_DAY s_min s_max s_avg
-------------------------------------------------------------------------------------------------------------------------------
2016 1 1 39.6 59.89 43.8559
2016 1 2 39.47 61.99 44.224
2016 1 3 39.66 61.76 44.0558
2016 1 4 39.61 58.26 43.6778
2016 1 5 39.54 63.42 44.2637
................
2016 12 25 39.71 61.53 43.8556
2016 12 26 39.71 56.72 43.971
2016 12 27 39.77 59.62 43.9969
2016 12 28 39.59 55.45 45.9118
2016 12 29 42.94 55.58 49.0696
2016 12 30 42.9 55.18 49.0812
2016 12 31 42.88 55.47 49.0785
[366] row(s) selected.
Elapsed time: 3.041
두번째 부분의 쿼리는 히트맵 입력에 맞도록 위의 결과 값을 스트링으로 변환하는 것이다.
SELECT TO_CHAR(s_year)||'-'||
LPAD(TO_CHAR(s_month),2,'0')||'-'||
LPAD(TO_CHAR(s_day),2,'0') AS s_date,
s_min,
s_max,
s_avg
FROM (위의 SQL 쿼리...)

위에서 || 표시는 스트링을 서로 연결하는 표시이고, TO_CHAR()는 숫자를 스트링으로 변환하는 것이고, LPAD는 스트링 위치를 조정하는 함수이다.두번째 부분의 쿼리는 히트맵 입력에 맞도록 위의 결과 값을 스트링으로 변환하는 것이다.

이 결과물은    [ "2016-03-01", 최소, 최고, 평균 ]  이라는 레코드를 생성하는 간단한 쿼리이다.

이 둘을 합치면 다음과 같은 쿼리로 완성되며, 천만건에 대해서 우아하게 히트맵의 입력될 형태로 변환해 준다.

당연하지만, 이러한 SQL을 활용하면 얼마든지 다양한 형태의 데이터로 조작할 수 있음은 분명하다.

SELECT TO_CHAR(s_year)||'-'||
LPAD(TO_CHAR(s_month),2,'0')||'-'||
LPAD(TO_CHAR(s_day),2,'0') AS s_date,
s_min,
s_max,
s_avg
FROM (
SELECT S_YEAR,
S_MONTH,
S_DAY,
MIN(S_VALUE) s_min,
MAX(S_VALUE) s_max,
AVG(S_VALUE) s_avg
FROM humidity
GROUP BY S_YEAR, S_MONTH, S_DAY
ORDER BY S_YEAR, S_MONTH, S_DAY
)



이제 모든 준비가 되었다.R에서 SQL을 통해 히트맵 결과 값 얻기

R에서 다음과 같은 명령어를 수행하면 히트맵에 넣을 수 있는 입력 데이터를 얻을 수 있다.

> dat <- sqlQuery(conn, "SELECT TO_CHAR(s_year)||'-'||LPAD(TO_CHAR(s_month),2,'0')||'-'||LPAD(TO_CHAR(s_day),2,'0') AS s_date, s_min, s_max, s_avg FROM (SELECT S_YEAR, S_MONTH, S_DAY, MIN(S_VALUE) s_min, MAX(S_VALUE) s_max, AVG(S_VALUE) s_avg FROM humidity GROUP BY S_YEAR, S_MONTH, S_DAY ORDER BY S_YEAR, S_MONTH, S_DAY)")
> View(dat)

dat 내부에 어떻게 데이터가 들어가 있는지 보기 위해 View()함수를 호출했다. 이제 히트맵을 볼 수 있는 만반의 준비가 되었다.

캘린더 히트맵 보기

이제 히트맵을 수행해 보자.

하루 중  최고 습도를 보인 날에 대한 히트맵

> calendarHeat(dates=dat$s_date, values=dat$s_max, color="g2r", varname="Humidity Max Value")

보니 8월 28일이 가장 높은 습도를 보인 날이었다. 아마도 소나기나 홍수 혹은 장마가 있었던  듯 하다.

하루 중  최저 습도를 보인 날에 대한 히트맵

> calendarHeat(dates=dat$s_date, values=dat$s_min, color="g2r", varname="Humidity Min Value")

12월 마지막 날에 최저 습도치가 가장 높았다..겨울에 전반적으로 습도가 높은 지역 인듯.

최저 습도는 전체적으로 큰 차이가 없는 듯 한 지역이다.

하루 평균 습도에 대한 히트맵

12월 마지막 날에 최저 습도치가 가장 높았다..겨울에 전반적으로 습도가 높은 지역 인듯.

최저 습도는 전체적으로 큰 차이가 없는 듯 한 지역이다.

하루 평균 습도에 대한 히트맵

> calendarHeat(dates=dat$s_date, values=dat$s_avg, color="g2r", varname="Humidity Average")

12월 말에 평균 습도가 가장 높은 날이 있다. 남반구의 어느 도시가 아닐까..하는 생각을 해 본다.

8월 28일 시간별 습도 분포 보기

이번에는 특정 날에 대한 시간별 습도 분포를 보도록 하자.

최고를 기록한 8월 28일 결과는 다음과 같다.

HUMIDITY 테이블에서 8월 28일의 데이터중에서 시간, 분, 초, 습도값 을 모두 얻어와서 hourmax에 저장한다.

> hourmax <- sqlQuery(conn, "SELECT S_HOUR, S_MINUTE, S_SECOND, S_VALUE FROM humidity WHERE S_MONTH = 8 and S_DAY = 28")
> View(hourmax)

hourmax의 값은...

이고 시계열로 plot 을 하면 다음과 같이 예쁘게 나온다.

> plot(hourmax$S_HOUR, hourmax$S_VALUE, col="blue", main="2016-08-28 humidity per hour", xlab="hour", ylab="value")
2016년 8월 28일 12시에서 13시에 최고 습도를 기록한 것으로 나온다.

그럼, 그날 12시에서 13시까지의 세부 데이터를 보자.

> minmax <- sqlQuery(conn, "SELECT S_MINUTE, S_SECOND, S_VALUE FROM humidity WHERE S_MONTH = 8 and S_DAY = 28 and S_HOUR = 12")
> View(minmax)

실제 데이터는 아래와 같다.이 데이터를 가지고 시계열 데이터를 보자.

> plot(minmax$S_MINUTE, minmax$S_VALUE, col="#de5347", main="2016-08-28 12 ~ 13 humidity per minute", xlab="minute", ylab="value")

결론적으로 2016년 전체 데이터를 보면, 8월 28일 오후 12시 25분경에 그해 최고의 습도를 기록한 것으로 나온다.

아래는 마지막으로 2016년 1년 전체의 일별 전체 습도 평균을 시계열로 나타낸 것이다.

> plot(dat$s_date, dat$s_avg, col="#de5347", main="2016", xlab="day", ylab="humidity")

출력된 그림은 아래와 같다.

마치면서

천만건짜리 분석용 CSV 데이터를 활용해서 다양하게 데이터를 조작, 입력, 분석해 보았다.

실제로 일반적인 PC에서  모든 스크립트를 자동으로 수행하면, 천만건의 데이터를 로딩, 클랜징, 분석 하는데 1분도 채 걸리지 않는다.

본 블로그에서 설명한 대로  마크베이스와 R을 잘 활용하여  데이터 분석에 있어서 큰 도움이 되었으면 한다.

Contact the Machbase team with your questions!
info@machbase.com

연관 포스트

C언어로 Binary data를 Machbase에 넣기

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

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

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