Script for fast loading log files

[matyaskopp]

Loading script is slow when inserting line by line. Old static log files can be loaded faster with COPY command.

Script should settings

• load (service_id, regex) from database
• get SELECT max(file_id)+1 FROM log_files;

convert access-file.log to:

access-file.sql

INSERT 
INTO log_files(file_id, file_name, first_line_checksum, last_read_line_checksum, lines_read, lines_valid, tail) 
VALUES(ID,'access-file.log',FIRST_CHECK,LAST_CHECK,LREAD,LVALID,FALSE);

COPY log_file_entries(file_id, service_id, line_number, line_checksum, remote_addr, remote_user, time_local, method, request, protocol, status, body_bytes_sent, http_referer, http_user_agent, unit) 
FROM 'access-file.dump'
WITH 
   DELIMITER AS '\t';

access-file.dump

TAB      SEPARATED      VALUES

to handle

• be careful about file_id, check if it does not exist during import.
• aggregations are not done