Как объединить большое количество строк, суммируя данные по одинаковым строкам?

Question

[Артем]

Здравствуйте!

Исходные данные:

50 csv-файлов в формате
документ;колонка1;колонка2;колонка3
Всего около миллиарда строк во всех файлах.
Документы могут повторяться как в пределах одного файла, так и между несколькими файлами (заранее количество одинаковых документов неизвестно).

Необходимо:

Объединить все файлы в один (или в одну БД), суммируя данные в колонке1, колонке2 и колонке3 для одинаковых документов.

Подскажите, пожалуйста, как это можно сделать за минимальное время?
Поставил теги SQL, так как думаю, что без какого-то SQL-сервера здесь не обойтись.

Answer 1

[Александр]

Наверное стоит начать разговор с языка программирования?)

Comments

[Артем]

Для реализации этой конкретной задачи язык программирования значения не имеет. Если это не ассемблер, конечно )

[Александр]

Так в чем вопрос тогда? Как написать парсер CSV файлов?

[Артем]

Написать парсер CSV-файлов нетрудно. Труднее сделать так, чтобы этот парсер прошелся по миллиарду строк, вычислил одинаковые и просуммировал данные по ним. Где хранить промежуточные результаты? В память они не поместятся (файлы занимают около 150 гб), поэтому придется делать это на жестком диске. Вот и интересуюсь готовыми решениями - возможно, до меня кто-то сталкивался с таким объемом данных и подскажет, как это сделать за минимальное время.

Представьте даже просто добавление в SQL-базу - нужно для каждого документа из 1 млрд сделать SELECT для того, чтобы узнать, есть он в базе или нет и, если есть, то просуммировать данные. 1 млрд SELECT'ов будут делаться вечность, причем чем больше база, тем дольше будет выполняться каждый SELECT.

[Александр]

Во-первых, не обязательно за один цикл обработать сразу все 50 файлов, можно по одному.
Данные конечно придется хранить в БД.
Для проверки существования не нужно делать SELECT, есть такая вещь как ON DUPLICATE KEY UPDATE, главное правильно сделать primary key для таблицы. Если операции с таблицами начнут занимать много времени из-за объема данных в них - нужно подумать в сторону шардирования.
Как-то примерно так.

[Артем]

Спасибо!

[Дмитрий Энтелис]

Зачем обрабатывать из языка, когда в любой современной субд есть нормальные встроенные механизмы?

Answer 2

[Дмитрий Энтелис]

Вашу задачу можно разбить на 2 части:
1. Вам нужно сделать импорт из CSV в SQL.
Если использовать mysql то есть замечательная команда dev.mysql.com/doc/refman/5.1/en/load-data.html
В postgresql тоже способ есть stackoverflow.com/questions/2987433/how-to-import-...

2. Объединить данные не сложно вообще.
Если Вам данные нужны больше 1 раза (а так и есть, то логично записать новые агрегированные данные в новую таблицу с аналогичной структурой)

insert into `table2` (doc,col1,col2,col3)
(
select doc, sum(col1),sum(col2),sum(col3) from `table1`
group by doc
)

Comments

[Артем]

Спасибо за ответ! Сколько по времени займет такой запрос при 1 мдрд записей в таблице? Сколько оперативки должно быть, чтобы не нарваться на её недостаток?

[Дмитрий Энтелис]

devspec: попробуйте :) я так понимаю что это разовое какое то действие у Вас, так что быстродействие разового запроса не сильно критично

[Артем]

В таком масштабе - разовое. Но не хотелось бы растягивать на месяцы )

[Дмитрий Энтелис]

devspec: не очень понятно сколько у вас занимает поле doc, но миллиард записей будет занимать где то ~10-20 gb на диске + индексы. Соответственно если памяти будет больше - все будет достаточно быстро. Меньше - дольше.

[Артем]

Поле doc занимает до 200 байт в utf8, сейчас миллиард записей в текстовых файлах занимает около 150 гб. Скорость особо не имеет значения - даже неделя - допустимо. Главное, чтобы запрос в принципе выполнился и не вывалился из-за недостатка памяти.

[Дмитрий Энтелис]

devspec: Хех, я надеялся что оно хотя бы байт 10-20)
Ну спробуйте загрузить, это делается одной простой командой же.
Если не проглотит - тогда уже думать извращения.

[Артем]

Ну то есть, с большой долей вероятности, запрос этот в память не поместится? ) Я уже подумываю разбить базу на 43 таблицы (33 буквы алфавита и 10 цифр) и попробовать в них загрузить документы по первому символу. Потом базы по идее можно будет объединить.

Answer 3

[mgyk]

Быстрее будет сделать LOAD DATA INFILE dev.mysql.com/doc/refman/5.1/en/load-data.html и импортировать сразу из CSV.
Можно попробовать engine=ARCHIVE если операция одноразовая. Будет больший шанс влезть в память при следующем GROUP BY