Как импортировать TSV или CSV в SQLite (когда grep уже не справляется)

Дано: большой текстовый файл с табличкой (колонки разделены табуляцией, TSV), 940 МБ.
Нужно: находить строчки с определённым значением и брать значение другой колонки, несколько сот раз в день.
Решение: конвертировать TSV-файл в базу данных SQLite и пользоваться базой данных. В этой заметке я расскажу как быстро импортировать TSV или CSV файл в SQLite.

Обычный способ, используем awk или grep:

$ time awk '/190711127/ { print $2, $7; }' datafile.tsv
6407305 190711127

real 2m21.116s
user 2m9.340s
sys 0m1.596s


Долго. С grep немного быстрее:

$ time grep 190711127 datafile.tsv | awk '{ print $2, $7; }'
6407305 190711127

real 0m33.283s
user 0m0.952s
sys 0m1.024s


Лучше, но всё равно долго. Можно вырезать заранее только нужные колонки и работать с файлом поменьше:

$ awk -v OFS='\t' '{ print $2, $7;}' datafile.tsv > onlytwo.tsv
$ du -sh onlytwo.tsv
194M onlytwo.tsv
$ time grep 190711127 onlytwo.tsv
6407305 190711127

real 0m7.686s
user 0m0.156s
sys 0m0.228s


Файл 200 МБ. 7 секунд на каждый поиск по нему, для нескольких сот запросов в день это слишком долго. Так что обычный grep здесь уже не справляется. Примерно те же проблемы при работе с файлом из скрипта на python: грузить целиком в память нехорошо, а читать каждый раз по строчке с диска — долго.

Было решено поместить данные из файла в базу данных SQLite, благо конфигурировать её не надо, с таким объёмом данных она справляется отлично, а использовать её можно из любых скриптов.

Первая мысль была сгенерировать SQL-скрипт, который создаст табличку и вставит в неё все значения. Скрипт сгенерировал. Запустил sqlite3 imported.db < onlytwo_insert.sql и стал ждать. Дело шло неспешно (минут за 20 я успел погуглить и прочитать документацию). Оказалось, для быстрого импорта данных в SQLite есть команда .import. Импорт сделал так:

$ sqlite3 imported.db
SQLite version 3.5.3
Enter ".help" for instructions
sqlite> create table a_b ( a integer, b integer ) ;
sqlite> .separator \t
sqlite> .import 'onlytwo.tsv' a_b
sqlite> create index a_idx a_b (a);
sqlite> create index b_idx a_b (b);
sqlite> .timer on
sqlite> select * from a_b where b = 190711127;
6407305|190711127
CPU Time: user 0.000000 sys 0.000000

Теперь после создания индексов поиск с помощью SQL-запроса занимает пренебрежимые в большинстве ситуаций доли секунды. Хочу заметить, что для импорта данных разделённых не табуляцией, а каким-то другим символом (запятой в CSV, пробелом, ещё чем-то) достаточно просто указать другой .separator. В отличие от выполнения SQL-запроса на вставку, .import работает быстро, но ошибки несоответствия типов игнорируются (данные из файла встравляются в базу как строки, что может потребовать выполнить потом ручной update заменяющий что-нибудь на честный null, например).

Полученную базу можно использовать в том числе и в bash-скриптах, примерно вот так:

$ time sqlite3 imported.db 'select * from a_b where b = 190711128;'
6405752|190711128

real 0m0.003s
user 0m0.000s
sys 0m0.004s

Размер базы с двумя колонками примерно равен размеру соответствующего текстового файла (197 МБ), но после создания двух индексов весит уже 534 МБ. Зато работает быстро.