Code: Select all
CREATE TABLE public.apache_log
(
eip char(15),
uip char(15),
datetime timestamp,
verb varchar(8),
dt varchar(256),
df varchar(256),
url varchar(256),
http_version varchar(36),
ref_url varchar(256),
status int,
cl int,
bytes int,
dur int,
cache int,
ua varchar(256)
);
Code: Select all
daniel@synapse:~$ cat /tmp/apache.log
213.4.28.244 - 95.123.101.114 - - [05/Jun/2013:08:27:47] "GET /X/0/crossdomain.xml HTTP/1.1 http://democdn.biz.tm/player-5.1.897.swf" 200 307 307 0 0 "Mozilla/5.0 (Windows NT 5.1; rv:21.0) Gecko/20100101 Firefox/21.0"
Code: Select all
copy apache_log
(
record filler varchar(65000),
eip as split_part(record,' ', 1),
uip as split_part(record,' - ', 2),
datetime as to_timestamp(regexp_substr(record, '\[([^]]+)]', 1,1,'',1), 'DD/Mon/YYYY:HH:MI:SS'),
verb as regexp_substr(record, '"([^\s]+)',1,1,'',1),
dt as regexp_substr(record, '"\w+\s(/[^/]+)',1,1,'',1),
df as regexp_substr(record, '"\w+\s(/[^/]+)(/[^/]+)',1,1,'',2),
url as regexp_substr(record, '"\w+\s(/[^/]+)(/[^/]+)(/[^/]+)\s',1,1,'',3),
http_version as regexp_substr(record, '"\w+\s[^\s]+\s([^\s]+)',1,1,'',1),
ref_url as regexp_substr(record, 'http://[^"]+'),
status as regexp_substr(record, '(?<=\s)\d+(?=\s)', 1,1)::int,
cl as regexp_substr(record, '(?<=\s)\d+(?=\s)', 1,2)::int,
bytes as regexp_substr(record, '(?<=\s)\d+(?=\s)', 1,3)::int,
dur as regexp_substr(record, '(?<=\s)\d+(?=\s)', 1,4)::int,
cache as regexp_substr(record, '(?<=\s)\d+(?=\s)', 1,5)::int,
ua as regexp_substr(record, '"[^"]+"$')
)
from '/tmp/apache.log' direct abort on error;
Code: Select all
daniel=> \e
Rows Loaded
-------------
1
(1 row)
daniel=> \x
Expanded display is on.
daniel=> select * from apache_log ;
-[ RECORD 1 ]+--------------------------------------------------------------------
eip | 213.4.28.244
uip | 95.123.101.114
datetime | 2013-06-05 08:27:47
verb | GET
dt | /X
df | /0
url | /crossdomain.xml
http_version | HTTP/1.1
ref_url | http://democdn.biz.tm/player-5.1.897.swf
status | 200
cl | 307
bytes | 307
dur | 0
cache | 0
ua | "Mozilla/5.0 (Windows NT 5.1; rv:21.0) Gecko/20100101 Firefox/21.0"