sqlite를 이용해서 자료 분석

SQLite는 DBMS처럼 별도의 설치를 할 필요도 없고 sqlite3.exe 파일만 있으면 실행이 가능한 DB이다.

자주 사용하는 관용 패턴이 있어서 정리를 해본다.

주의. Mac OS X에서는 줄바꿈 문자을 0x0A (LF; Line Feed)를 사용한다. (텍스트 편집기에서 직접 타입한 경우) 그런데 Microsoft Excel:mac 2011에서 셀을 복사할 경우 줄바꿈 문자가 LF가 아닌 0x0D로 복사가 됨을 확인했다. sqlite에서는 줄바꿈 문자를  CR이나 LF만 인식한다. 따라서 맥에서 작업할 경우에 주의가 필요하다. (.import시 데이터 하나만 들어가는 문제가 발생) 관련 이슈는 링크 참고

Idiom1. IP의 개수를 빈도별로 측정

DB에 데이터가 있다면 select count(col) 과 group by로 가능하다. 데이터에 넣기 위한 작업이 포인트다.

자신의 웹서버에 접속되어 있는 IP의 개수를 카운팅하여 많은 순서대로 알고 싶을 때를 예로 들었다.

1. RAW 데이터를 획득

 - netstat 명령과 파일로 리다이렉션으로 이용해 파일로 만들었다.

   netstat -noa | find ":80" > list-1st.txt

 

2. RAW 데이터 1차 가공

 - 패턴을 찾아서 정규식으로 짜르면 쉽게 가공이 되낟.

 - 주로 Editplus의 정규식으로 찾아 바꾸기를 이용한다.

   찾을 말: :[0-9]+   / 바꿀 말: (빈문자열)

3. SQLite 실행 (sqlite3.exe가 PATH에 걸려있다고 가정)

 > sqlite3

4. 테이블 생성

 sqlite> create table test (ip);

5. DB 입력 (만약 csv 같은 형태라면 .mode csv 로 모드 전환을 해준다)

 sqlite> .import ips.txt test

6. 표시형식 지정 및 파일로 출력

 sqlite> .header on

 sqlite> .mode column

 sqlite> .width 20

 sqlite> .output out.txt

7. SELECT 명령

 sqlite> select ip, count(ip) as cnt from test group by ip order by cnt desc;

8. 화면으로 스트림 재변경

 sqlite> .output stdout

out.txt 라는 파일이 생성된다. 참고로 Unix 스타일로 줄바꿈 문자가 되어 있기 때문에 Editplus 같은 프로그램으로 여는 것이 좋다.

배치 스크립트

sqlite3

create table test (ip);

.import ips.txt test

.header on

.mode column

.width 20

.output out.txt

select ip, count(ip) as cnt from test group by ip order by cnt desc;

.output stdout