Niniejszy wpis powstał przy rozwiązywaniu konkretnego problemu. Jest tu opis polecenia LOAD DATA INFILE (import danych z pliku) oraz sposób wykonania konwersji daty podczas importu danych, co może komuś się przydać (niekoniecznie przy walce z takim samym problemem).
Opis problemu: Potrzebujemy wyciągnąć wszystkie próby logowania z wykorzystaniem programu OTC Terminal konkretnego dnia, albo o konkretnej godzinie każdego dnia zdefiniowanego użytkownika.
Rozeznanie terenu: Wspomniany program tworzy log w następującym formacie:
4. BS2 LOGIN ERROR 25.03.2009 07:02:08 Password expired.
4. BS2 CHPWD OK 25.03.2009 07:02:16 Password for user 'BS2' successfully changed.
0. BS2 LOGIN OK 30.03.2009 06:54:25
1. BS2 LOGIN OK 30.03.2009 06:54:29
0. BS2 LOGOUT OK 30.03.2009 06:54:29
1. BS2 LOGOUT OK 30.03.2009 06:54:42
10. BS2 LOGOUT ERROR 24.04.2009 11:47:58 Login limit (session)
6. BS2 LOGIN ERROR 21.05.2009 14:32:57 No such name
Nie wnikam co oznacza pierwsza kolumna w logach, kolejne to: nazwa użytkownika, akcja, data oraz godzina, a także dodatkowa informacja dla akcji. Nie interesuje nas zarówno pierwsza, jak i ostatnia kolumna. Plik tekstowy ma kolumny o stałej szerokości (tutaj tego nie widać).
Do realizacji zadania można użyć awk, sed, grep, find (Windows) lub innych podobnych narzędzi w połączeniu z wyrażeniami regularnymi, ale można też skorzystać z bazy danych, która będzie przechowywać wspomniane informacje w konkretnym formacie, a samo przeszukiwanie będzie wygodniejsze, szybsze i bardziej elastyczne.
Realizacja: Za pomocą arkusza kalkulacyjnego przekształciłem logi w format CSV (najszybsze, co przyszło mi do głowy). Wyrzuciłem niepotrzebne kolumny. Utworzyłem tabelę w MySQL, która może wciągnąć te dane:
CREATE TABLE `trmlog` (
`user` char(3) NOT NULL,
`action` varchar(24) DEFAULT NULL,
`date` date DEFAULT NULL,
`time` time DEFAULT NULL,
KEY `useridx` (`user`),
KEY `dateidx` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Data i godzina są osobno. Jako, że data ma postać DD.MM.YYYY, a chcemy YYYY-MM-DD, stworzyłem funkcję, która może to skonwertować (nie wiem czy MySQL w locie coś może sam zrobić):
delimiter //
CREATE FUNCTION dateconv (date CHAR(10)) RETURNS DATE
BEGIN
RETURN CONCAT(SUBSTRING(date, 7), "-", SUBSTRING(date, 4, 2), "-", SUBSTRING(date, 1, 2));
END//
delimiter ;
I ostatecznie zaimportowałem dane:
LOAD DATA INFILE 'E:\\temp\\termlog\\log.csv' INTO TABLE trmlog FIELDS TERMINATED BY ';' (user, action, @var1, time) SET date = dateconv(@var1);
Uwagi wymaga proces konwersji kolumny z datą. Domyślnie LOAD DATA INFILE zakłada, że każde pole w pliku CSV odpowiada jednej kolumnie w tabeli. Jeśli chcemy zaimportować dane tylko do niektórych kolumn, musimy podać ich nazwy (czy sam ich porządek). Jeśli natomiast chcemy je w jakiś sposób przekształcić, używamy klauzuli SET, która może przybrać różne formy. Po przykłady odsyłam do dokumentacji MySQL. U mnie wygląda to tak, że do kolumny date wciągamy dane, które wyrzuci nam (nasza własna) funkcja dateconv(). Może nie do końca intuicyjne, ale nie tak trudne.
Dalej obrabiamy to jak chcemy w SQL.
Oczywiście nie musimy używać MySQL, a jeśli już użyliśmy, zawsze możemy migrować do innej bazy:
Tagi: MySQL, OTC, OTC Terminal