Как обрабатывать большие наборы данных

Шаг 1. Переместите данные в базу данных, а не в электронную таблицу.

Самый важный шаг: перестаньте пытаться открыть большие данные в электронной таблице. Вместо этого поместите его в базу данных. SQLite (через бесплатное приложение Браузер БД) не имеет практического ограничения на количество строк. Тот же файл, который привел к сбою Excel, становится таблицей, к которой можно обратиться за считанные секунды.

Это именно то создать, импортировать, проверить рабочий процесс из Руководство по базе данных SQL. Шаги идентичны для большого файла. Просто это займет больше времени, и именно к этому вас готовит остальная часть этого руководства.

Шаг 2. Ожидайте, что загрузка будет заархивирована (с дополнительными функциями, которые вам не нужны)

Большие общедоступные наборы данных почти никогда не загружаются в виде одного чистого CSV. Они приходят как .zip (часто называют чем-то общим, например archive.zip), который содержит несколько файлов, объединенных вместе. В нашем примере в застежка-молнияархиве содержалось четыре файла, а нам нужен был только один.

1. Щелкните правой кнопкой мыши загруженный застежка-молнияархив и выберите Извлечь все.
2. Откройте извлеченную папку и используйте только тот файл, который вам действительно нужен (для нас, recommendations.csv). Остальное игнорируйте.

Шаг 3. Импорт выполняется медленно, а файл растет. Это нормально.

CSV-файл размером 2 ГБ не импортируется мгновенно. Дайте этому время. Людей удивляют две вещи, и обе ожидаемы:

• Импорт занимает минуты, а не секунды. Браузер БД может выглядеть зависшим во время работы. Пусть это закончится.
• Твой .db файл вырастает до нескольких гигабайт. CSV-файл размером 2 ГБ может создать базу данных объемом несколько ГБ. Прежде чем приступить к работе, убедитесь, что у вас есть свободное место на диске: исходный файл CSV вместе с базой данных легко может занимать от 4 до 6 ГБ.

Импортируйте файл точно так же, как в руководстве по SQL (назовите таблицу, оканчивающуюся на _raw, отметьте «имена столбцов в первой строке»), затем нажмите Ctrl+S чтобы сохранить.

Шаг 4. Создайте индекс (шаг, который ускоряет большие запросы)

Представьте себе телефонную книгу и представьте одни и те же имена в том порядке, в котором люди переезжали в город. Оба содержат одну и ту же информацию. Только один позволяет найти имя, не читая каждую страницу. Держите эту картинку, потому что это именно то, что описано в следующем абзаце.

Ан индекс — это ярлык поиска в столбце. Без него каждый раз, когда вы выполняете поиск или присоединяетесь к этому столбцу, база данных считывает все 41 миллион строк сверху вниз. С одним он переходит прямо к нужным строкам. На маленьком столике этого никогда не заметишь. По большому счету, это разница между мгновенным и непригодным для использования.

Создайте индекс один раз по столбцу, к которому вы будете присоединяться или фильтровать по большинству (обычно идентификатор):

CREATE INDEX IF NOT EXISTS idx_rec_appid ON recommendations(app_id);

Ловушка: функция клавиши соединения отключает индекс.

Одно уточнение, которое раздражает даже людей, разбирающихся в индексах. Индекс на title помогает присоединиться ON a.title = b.title — но в тот момент, когда вы обертываете ключ в функцию, например ON LOWER(a.title) = LOWER(b.title), простой индекс не используется. Такая переноска является стандартным приемом для сопоставления имен без учета регистра. Индекс сортировал исходные значения, а не строчные. База данных молча возвращается к сравнению каждой строки с каждой строкой. И снова реальные цифры: объединение 13 000 строк против 33 000 — небольшие таблицы по меркам этого руководства! — это 427 миллионов сравнений без индекса, длительностью более 30 минут. Исправление представляет собой индекс выражения, построенный именно на том выражении, которое использует соединение:

CREATE INDEX IF NOT EXISTS idx_songs_lower
  ON artist_songs (LOWER(primary_artist), LOWER(title));

Тот же запрос потом: секунды. Модернизированное практическое правило: индексируйте не только столбец, по которому выполняется поиск объединений, но и точное выражение они ищут по.

Шаг 5. Сначала напишите запрос к образцу

Вы не хотите ждать 41 миллион строк каждый раз, когда исправляете опечатку в своем запросе. Разработайте небольшой фрагмент, а затем запустите готовый запрос для всего.

• добавлять LIMIT 1000 пока вы пишете и тестируете, результаты возвращаются мгновенно.
• Или оставьте небольшой образец CSV (несколько тысяч строк) в виде отдельной таблицы для построения запросов.

Как только запрос в образце будет правильным, удалите LIMIT и запустить его по-настоящему.

Шаг 6. Агрегируйте заранее и никогда не выбрасывайте на экран миллионы необработанных строк.

Если попросить инструмент отобразить 41 миллион строк, он заморозится. В любом случае вам почти никогда не нужны необработанные строки, вам нужен краткое содержание. Использовать GROUP BY чтобы свернуть данные в одну строку для каждой интересующей вас вещи:

SELECT app_id, COUNT(*) AS num_reviews, AVG(hours) AS avg_hours
FROM recommendations
GROUP BY app_id;

Это превращает 41 миллион строк обзора в одну аккуратную строку за игру. Достичь COUNT, AVG, SUM, и GROUP BY прежде чем ты когда-нибудь потянешься за SELECT *.