...
로그 파일의 첫번째 필드가 날짜인데, 이 값을 _arrival_time 칼럼에 컬럼에 입력하도록 옵션을 지정한다.
| Code Block | ||
|---|---|---|
| ||
[machbase@localhost csvload]$ csvimport -t sample_table -d sample_data.csv -a -F "_arrival_time YYYY-MM-DD HH24:MI:SS"
-----------------------------------------------------------------
Machbase Data Import/Export Utility.
Release Version 5.1.9.community
Copyright 2014, MACHBASE Corporation or its subsidiaries.
All Rights Reserved.
-----------------------------------------------------------------
NLS : US7ASCII EXECUTE MODE : IMPORT
TARGET TABLE : sample_table DATA FILE : sample_data.csv
IMPORT_MODE : APPEND FILED TERM : ,
ROW TERM :
ENCLOSURE : "
ESCAPE : " ARRIVAL_TIME : TRUE
ENCODING : NONE HEADER : FALSE
CREATE TABLE : FALSE
Progress bar Imported records Error records
1000000 0
Import time : 0 hour 0 min 5.728 sec
Load success count : 1000000
Load fail count : 0
[machbase@localhost csvload]$ |
...
인덱스 생성 및 조회
생성된 sample_table 칼럼 컬럼 중에서 varchar 형인 eventlog 칼럼에 컬럼에 대해서 keyword 인덱스를 생성하고 텍스트 검색을 해본다.
...
| Code Block | ||
|---|---|---|
| ||
-- _arrival_time 칼럼에컬럼에 입력된 최대,최소값을 확인한다. Mach> SELECT MIN(_ARRIVAL_TIME), MAX(_ARRIVAL_TIME) FROM SAMPLE_TABLE; MIN(_ARRIVAL_TIME) MAX(_ARRIVAL_TIME) ------------------------------------------------------------------- 2015-05-20 06:00:00 000:000:000 2015-05-20 06:40:10 000:000:000 [1] row(s) selected. -- DATE_TRUNC() 함수를 이용하여 분당 건수를 구한다. Mach> SELECT DATE_TRUNC('minute', _ARRIVAL_TIME) as TIME, COUNT(*) as COUNT FROM SAMPLE_TABLE GROUP BY TIME ORDER BY TIME; TIME COUNT -------------------------------------------------------- 2015-05-20 06:00:00 000:000:000 32001 2015-05-20 06:01:00 000:000:000 28000 2015-05-20 06:02:00 000:000:000 24000 2015-05-20 06:03:00 000:000:000 32000 2015-05-20 06:04:00 000:000:000 16000 2015-05-20 06:05:00 000:000:000 16000 2015-05-20 06:06:00 000:000:000 32000 2015-05-20 06:07:00 000:000:000 32000 2015-05-20 06:08:00 000:000:000 20000 2015-05-20 06:09:00 000:000:000 24000 2015-05-20 06:10:00 000:000:000 20000 2015-05-20 06:11:00 000:000:000 20000 2015-05-20 06:12:00 000:000:000 24000 2015-05-20 06:13:00 000:000:000 20000 2015-05-20 06:14:00 000:000:000 32000 2015-05-20 06:15:00 000:000:000 24000 2015-05-20 06:16:00 000:000:000 32000 2015-05-20 06:17:00 000:000:000 28000 2015-05-20 06:18:00 000:000:000 32000 2015-05-20 06:19:00 000:000:000 12000 2015-05-20 06:20:00 000:000:000 24000 2015-05-20 06:21:00 000:000:000 28000 2015-05-20 06:22:00 000:000:000 28000 2015-05-20 06:23:00 000:000:000 24000 2015-05-20 06:24:00 000:000:000 28000 2015-05-20 06:25:00 000:000:000 28000 2015-05-20 06:26:00 000:000:000 32000 2015-05-20 06:27:00 000:000:000 20000 2015-05-20 06:28:00 000:000:000 20000 2015-05-20 06:29:00 000:000:000 20000 2015-05-20 06:30:00 000:000:000 28000 2015-05-20 06:31:00 000:000:000 32000 2015-05-20 06:32:00 000:000:000 32000 2015-05-20 06:33:00 000:000:000 28000 2015-05-20 06:34:00 000:000:000 20000 2015-05-20 06:35:00 000:000:000 24000 2015-05-20 06:36:00 000:000:000 24000 2015-05-20 06:37:00 000:000:000 16000 2015-05-20 06:38:00 000:000:000 24000 2015-05-20 06:39:00 000:000:000 16000 2015-05-20 06:40:00 000:000:000 3999 [41] row(s) selected. -- DURATION 구문을 이용하여 특정시각 기준 1분 이전 시간 범위를 지정하여 조회한다. Mach> SELECT MIN(_ARRIVAL_TIME), MAX(_ARRIVAL_TIME), COUNT(*) as COUNT FROM SAMPLE_TABLE DURATION 1 MINUTE BEFORE TO_DATE('2015-05-20 06:30:00'); MIN(_ARRIVAL_TIME) MAX(_ARRIVAL_TIME) COUNT ----------------------------------------------------------------------------------------- 2015-05-20 06:29:05 000:000:000 2015-05-20 06:29:45 000:000:000 20000 [1] row(s) selected. -- DURATION 구문을 이용하여 특정시각 기준 1분 이후 시간 범위를 지정하여 조회한다. Mach> SELECT MIN(_ARRIVAL_TIME), MAX(_ARRIVAL_TIME), COUNT(*) as COUNT FROM SAMPLE_TABLE DURATION 1 MINUTE AFTER TO_DATE('2015-05-20 06:30:00'); MIN(_ARRIVAL_TIME) MAX(_ARRIVAL_TIME) COUNT ----------------------------------------------------------------------------------------- 2015-05-20 06:30:04 000:000:000 2015-05-20 06:30:57 000:000:000 28000 [1] row(s) selected. -- DURATION 구문을 이용하여 FROM ~ TO 시간 범위를 지정하여 조회한다. Mach> SELECT MIN(_ARRIVAL_TIME), MAX(_ARRIVAL_TIME), COUNT(*) as COUNT FROM SAMPLE_TABLE DURATION FROM TO_DATE('2015-05-20 06:20:00') TO TO_DATE('2015-05-20 06:30:00'); MIN(_ARRIVAL_TIME) MAX(_ARRIVAL_TIME) COUNT ----------------------------------------------------------------------------------------- 2015-05-20 06:20:03 000:000:000 2015-05-20 06:29:45 000:000:000 252000 [1] row(s) selected. |
...