Записки программиста, обо всем и ни о чем. Но, наверное, больше профессионального.

2012-01-13

Using PostgreSQL

Кстати, сегодня ведь ч0рная пятница! 2012-01-13 friday. Какой кошмар.

Итак, продолжим. Предыдущий пост был посвящен установке и первичной настройке СУБД PostgreSQL (вместе с PostGIS, которая пока никак не задействована, потом пригодится). В том посте я обещал нарисовать пример использования свежеустановленной БД по задаче «загрузить список файлов в таблицу и найти нужные файлы».

Приступим.

В наличии файл (размер под гигабайт, более 4 000 000 строк) вида CSV.
Пример данных, первые две строки из файла:
'mode';'ino';'dev';'nlink';'uid';'gid';'size';'atime';'mtime';'ctime';'fullName'
'33206';'0';'0';'0';'0';'0';'2924';'2012-01-12 03:17:04';'2011-12-20 17:38:39';'2011-12-20 17:38:39';'c:\arcgisserver\arcgiscache\topo500\Layers\conf.xml'

Для меня интерес представляет параметр mtime, то есть время последней модификации. Мне надо найти файлы, изменение которых проходило после некоей даты, которую еще надо определить исходя из нечеткой логики типа «в последнюю неделю старого года мы немного файлов изменяли, когда точно — не помню».

Файл был получен прогоном скрипта, где ключевые строки такие:
#!/usr/bin/env python
...
csvWriter = csv.writer(fileObj,
 delimiter=';', quotechar="'", quoting=csv.QUOTE_ALL, lineterminator='\n')
...
 for (path, dirs, files) in os.walk(path):
  for fn in files:
   fullName = os.path.join(path, fn)
   (mode, ino, dev, nlink, uid, gid, size, atime, mtime, ctime) = ('', '', '', '', '', '', '', '', '', '')
   try:
    (mode, ino, dev, nlink, uid, gid, size, atime, mtime, ctime) = os.stat(fullName)
   except:
    log.error('os.stat failed', exc_info=True)
   csvWriter.writerow((mode, ino, dev, nlink, uid, gid, size,
    strFtime(atime), strFtime(mtime), strFtime(ctime), fullName))

Глядя на содержимое файла, я состряпал такую таблицу (в pgAdmin есть вполне приятный GUI создания таблиц)
CREATE TABLE flist
(
   fmode integer, 
   fino integer, 
   fdev integer, 
   fnlink integer, 
   fuid integer, 
   fgid integer, 
   fsize integer, 
   fatime character varying(20), 
   fmtime character varying(20), 
   fctime character varying(20), 
   "fullName" character varying(255), 
   CONSTRAINT flist_pk PRIMARY KEY ("fullName")
) 
WITH (
  OIDS = FALSE
);
ALTER TABLE flist OWNER TO postgres;
видно, что с именем «fullName» я погорячился — теперь придется всю дорогу использовать форму записи имени с кавычками. Еще видно, что индексы я делать пока не стал, из соображений скорости загрузки. Правда сделал первичный ключ (без него как-то неуютно даже).

Теперь надо загрузить файл CSV в эту таблицу. Есть сведения, что с этим легко справится встроенная в PostgreSQL команда COPY. Но есть закавыка — если я на клиенте, где мне расположить файл? Я попробовал по разному и в итоге, лично мне проще оказалось выполнить команду в консоли сервера, подмонтировав файл в дерево каталогов сервера.

Сначала я тупо скопировал файл на сервер
postgres@postgis:~$ rsync v@sqz2:vshare/t/topo500/current/listfiles.csv ~/
получился файл /var/lib/postgresql/listfiles.csv
что многое говорит о профиле пользователя postgres на сервере.
А потом я подумал (бывает, сначала делаешь, потом думаешь) и решил сделать лучше — подключить Samba, ибо часто нужно использовать виндовые шари.

Получилось так:
от рута:
   86  aptitude install cifs-utils
   92  mkdir -p /home/v/mnt/vshare
   94  chown -R v /home/v/mnt
   98  mount -t cifs //sqz2/vshare /home/v/mnt/vshare -o username=v,uid=postgres
postgres@postgis:~$ less /home/v/mnt/vshare/t/topo500/current/listfiles.csv
Что и требовалось - файл доступен для пользователя postgres на сервере БД.

Собственно импорт довольно прост:
postgres@postgis:~$ psql
postgres=# copy flist(fmode, fino, fdev, fnlink, fuid, fgid, fsize, fatime, fmtime, fctime, "fullName") 
from '/home/v/mnt/vshare/t/topo500/current/listfiles.csv' with DELIMITER as ';' CSV header quote as '''';
COPY 4260931
последняя строка показывает сколько строк скопировано. Число правильное. Жаль, время я тогда не засек, а тулза его не показывает. По ощущениям, данные заливаются раза в два дольше, чем если просто переписывать файл по сети. Возможно, удалив первичный ключ получилось бы быстрее.

Дальнейшая работа с таблицей целиком проходит на клиенте, pgAdmin query tool в моем случае.

Создать индекс на поле fmtime. Опять же, через GUI создания/редактирования таблиц
-- DROP INDEX flist_mod_idx;
CREATE INDEX flist_mod_idx
  ON flist
  USING btree
  (fmtime COLLATE pg_catalog."default" );
в том GUI довольно много опций, надо бы в них поподробнее разобраться. А сейчас все опции дефолтные.

Запрос на выборку
SELECT 
  flist."fullName"
--  , flist.fctime
--  , flist.fmtime
FROM 
  public.flist
WHERE 
  flist.fmtime > '2011-12-23 21:53:37'
ORDER BY
  flist.fmtime ASC
-- limit 1000
;
Total query runtime: 7962 ms.
70066 rows retrieved.
изначально я его сделал через GUI графического построителя запросов, из любопытства. Оказалось очень даже удобно. Только опции ограничения количества записей не нашел как в запрос добавить. Пришлось руками поправлять.

Тыща строк в секунду. Как я понял, погоняв немного разные варианты запросов, тормозом можно считать сетевой ввод/вывод. Ибо, при последующих прогонах одного и того же запроса дисковая активность практически нулевая, как и загруженность процессора (это я про сервер, хотя клиент тоже не загружен).

Ну и получив нужную мне выборку, в том же pgAdmin query tool я сохранил ее в файл, через меню File — Export. Очень удобно.

Задача решена, всем спасибо, все свободны.

Комментариев нет:

Отправить комментарий

Архив блога

Ярлыки

linux (241) python (191) citation (185) web-develop (170) gov.ru (157) video (123) бытовуха (112) sysadm (100) GIS (97) Zope(Plone) (88) Book (81) programming (81) бурчалки (81) грабли (77) development (73) Fun (72) windsurfing (72) Microsoft (64) hiload (62) opensource (58) internet provider (57) security (57) опыт (55) movie (52) Wisdom (51) ML (47) language (45) hardware (44) JS (41) curse (40) driving (40) money (40) DBMS (38) bigdata (38) ArcGIS (34) history (31) PDA (30) howto (30) holyday (29) Google (27) Oracle (27) virtbox (27) health (26) vacation (24) AI (23) Autodesk (23) SQL (23) Java (22) humor (22) knowledge (22) translate (20) CSS (19) cheatsheet (19) hack (19) tourism (19) Apache (16) Manager (15) web-browser (15) Никонов (15) happiness (14) music (14) todo (14) PHP (13) course (13) functional programming (13) weapon (13) HTTP. Apache (12) SSH (12) frameworks (12) hero (12) im (12) settings (12) HTML (11) SciTE (11) crypto (11) game (11) map (11) scala (11) HTTPD (9) ODF (9) купи/продай (9) benchmark (8) documentation (8) 3D (7) CS (7) DNS (7) NoSQL (7) Photo (7) cloud (7) django (7) gun (7) matroska (7) telephony (7) Microsoft Office (6) VCS (6) bluetooth (6) pidgin (6) proxy (6) Donald Knuth (5) ETL (5) NVIDIA (5) REST (5) bash (5) flash (5) keyboard (5) price (5) samba (5) CGI (4) LISP (4) RoR (4) cache (4) display (4) holywar (4) nginx (4) pistol (4) xml (4) Лебедев (4) IDE (3) IE8 (3) J2EE (3) NTFS (3) RDP (3) USA (3) holiday (3) mount (3) spark (3) Гоблин (3) кухня (3) урюк (3) AMQP (2) ERP (2) IE7 (2) NAS (2) Naudoc (2) PDF (2) address (2) air (2) british (2) coffee (2) font (2) ftp (2) messaging (2) notify (2) sharepoint (2) ssl/tls (2) stardict (2) tests (2) tunnel (2) udev (2) APT (1) CRUD (1) Canyonlands (1) Cyprus (1) DVDShrink (1) Jabber (1) K9Copy (1) Matlab (1) Palanga (1) Portugal (1) VBA (1) WD My Book (1) autoit (1) bike (1) cannabis (1) chat (1) concurrent (1) dbf (1) ext4 (1) idioten (1) krusader (1) license (1) mindmap (1) pneumatic weapon (1) quiz (1) regexp (1) robot (1) science (1) serialization (1) tie (1) vim (1) Науру (1) крысы (1) налоги (1) пианино (1)

Google+ Followers