В csv файле меньше колонок чем ожидается найдено 5 колонок ожидается 9
CSV является стандартом де-факто для связи между собой разнородных систем, для передачи и обработки объемных данных с «жесткой», табличной структурой. Во многих скриптовых языках программирования есть встроенные средства разбора и генерации, он хорошо понятен как программистам, так и рядовым пользователям, а проблемы с самими данными в нем хорошо обнаруживаются, как говорится, на глаз.
История этого формата насчитывает не менее 30 лет. Но даже сейчас, в эпоху повального использования XML, для выгрузки и загрузки больших объемов данных по-прежнему используют CSV. И, несмотря на то, что сам формат довольно неплохо описан в RFC, каждый его понимает по-своему.
В этой статье я попробую обобщить существующие знания об этом формате, указать на типичные ошибки, а также проиллюстрировать описанные проблемы на примере кривой реализации импорта-экспорта в Microsoft Office 2007. Также покажу, как обходить эти проблемы (в т.ч. автоматическое преобразование типов Excel-ом в DATETIME и NUMBER) при открытии .csv.
Начнем с того, что форматом CSV на самом деле называют три разных текстовых формата, отличающихся символами-разделителями: собственно сам CSV (comma-separated values — значения, разделенные запятыми), TSV (tab-separated values — значения, разделенные табуляциями) и SCSV (semicolon separated values — значения, разделенные точкой с запятой). В жизни все три могут называться одним CSV, символ-разделитель в лучшем случае выбирается при экспорте или импорте, а чаще его просто «зашивают» внутрь кода. Это создает массу проблем в попытке разобраться.
Как иллюстрацию возьмем казалось бы тривиальную задачу: импортировать в Microsoft Outlook данные из таблицы в Microsoft Excel.
В Microsoft Excel есть средства экспорта в CSV, а в Microsoft Outlook — соответствующие средства импорта. Что могло быть проще — сделал файлик, «скормил» почтовой программе и — дело сделано? Как бы не так.
Создадим в Excel тестовую табличку:
… и попробуем экспортировать ее в три текстовых формата:
«Текст Unicode» | Кодировка — UTF-16, разделители — табуляция, переводы строк — 0×0D, 0×0A, объем файла — 222 байт |
«CSV (разделители — запятые)» | Кодировка — Windows-1251, разделители — точка с запятой (не запятая!), во второй строке значение телефонов не взято в кавычки, несмотря на запятую, зато взято в кавычки значение «01;02», что правильно. Переводы строк — 0×0D, 0×0A. Объем файла — 110 байт |
«Текстовые файлы (с разделителями табуляции)» | Кодировка — Windows-1251, разделители — табуляция, переводы строк — 0×0D, 0×0A. Значение «01;02» помещено в кавычки (без особой нужды). Объем файла — 110 байт |
Какой вывод мы делаем из этого. То, что здесь Microsoft называет «CSV (разделители — запятые)», на самом деле является форматом с разделителями «точка с запятой». Формат у Microsoft — строго Windows-1251. Поэтому, если у вас в Excel есть Unicode-символы, они на выходе в CSV отобразятся в вопросительные знаки. Также то, что переводами строк является всегда пара символов, то, что Microsoft тупо берет в кавычки все, где видит точку с запятой. Также то, что если у вас нет Unicode-символов вообще, то можно сэкономить на объеме файла. Также то, что Unicode поддерживается только UTF-16, а не UTF-8, что было бы сильно логичнее.
Теперь посмотрим, как на это смотрит Outlook. Попробуем импортировать эти файлы из него, указав такие же источники данных. Outlook 2007: Файл -> Импорт и экспорт… -> Импорт из другой программы или файла. Далее выбираем формат данных: «Значения, разделенные запятыми (Windows)» и «Значения, разделенные табуляцией (Windows)».
«Значения, разделенные табуляцией(Windows)» | Скармливаем аутлуку файл tsv, с разделенными табуляцией значениями и. — чтобы вы думали. Outlook склеивает поля и табуляцию не замечает. Заменяем в файле табуляцию на запятые и, как видим, поля уже разбирает, молодец. |
«Значения, разделенные запятыми (Windows)» | А вот аутлук как раз понимает все верно. Comma — это запятая. Поэтому ожидает в качестве разделителя запятую. А у нас после экселя — точка с запятой. В итоге аутлук распознает все неверно. |
Два майкрософтовских продукта не понимают друг друга, у них напрочь отсутствует возможность передать через текстовый файл структурированные данные. Для того, чтобы все заработало, требуются «пляски с бубном» программиста.
Мы помним, что Microsoft Excel умеет работать с текстовыми файлами, импортировать данные из CSV, но в версии 2007 он делает это очень странно. Например, если просто открыть файл через меню, то он откроется без какого-либо распознавания формата, просто как текстовый файл, целиком помещенный в первую колонку. В случае, если сделать дабл-клик на CSV, Excel получает другую команду и импортирует CSV как надо, не задавая лишних вопросов. Третий вариант — вставка файла на текущий лист. В этом интерфейсе можно настраивать разделители, сразу же смотреть, что получилось. Но одно но: работает это плохо. Например, Excel при этом не понимает закавыченных переводов строк внутри полей.
Более того, одна и та же функция сохранения в CSV, вызванная через интерфейс и через макрос, работает по-разному. Вариант с макросом не смотрит в региональные настройки вообще.
Стандарта CSV как такового, к сожалению, нет, но, между тем, существует т.н. memo. Это RFC 4180 года, в котором описано все довольно толково. За неимением ничего большего, правильно придерживаться хотя бы RFC. Но для совместимости с Excel следует учесть его собенности.
Вот краткая выжимка рекомендаций RFC 4180 и мои комментарии в квадратных скобках:
- между строками — перевод строки CRLF [на мой взгляд, им не стоило ограничивать двумя байтами, т.е. как CRLF (0×0D, 0×0A), так и CR 0×0D]
- разделители — запятые, в конце строки не должно быть запятой,
- в последней строке CRLF не обязателен,
- первая строка может быть строкой заголовка (никак не помечается при этом)
- пробелы, окружающие запятую-разделитель, игнорируются.
- если значение содержит в себе CRLF, CR, LF (символы-разделители строк), двойную кавычку или запятую (символ-разделитель полей), то заключение значения в кавычки обязательно. В противном случае — допустимо.
- т.е. допустимы переводы строк внутри поля. Но такие значения полей должны быть обязательно закавычены,
- если внутри закавыченной части встречаются двойные кавычки, то используется специфический квотинг кавычек в CSV — их дублирование.
Вот в нотации ABNF описание формата:
Также при реализации формата нужно помнить, что поскольку здесь нет указателей на число и тип колонок, поскольку нет требования обязательно размещать заголовок, здесь есть условности, о которых необходимо не забывать:
- строковое значение из цифр, не заключенное в кавычки может быть воспринято программой как числовое, из-за чего может быть потеряна информация, например, лидирующие нули,
- количество значений в каждой строке может отличаться и необходимо правильно обрабатывать эту ситуацию. В одних ситуациях нужно предупредить пользователя, в других — создавать дополнительные колонки и заполнять их пустыми значениями. Можно определиться, что количество колонок задается заголовком, а можно добавлять их динамически, по мере импорта CSV,
- Квотить кавычки через «слэш» не по стандарту, делать так не надо.
- Поскольку типизации полей нет, нет и требования к ним. Разделители целой и дробной частей в разных странах разные, и это приводит к тому, что один и тот же CSV, сгенрированный приложением, в одном экселе «понимается», в другом — нет. Потому что Microsoft Office ориентируется на региональные настройки Windows, а там может быть что угодно. В России там указано, что разделитель — запятая,
- Если CSV открывать не через меню «Данные», а напрямую, то Excel лишних вопросов не задает, и делает как ему кажется правильным. Например, поле со значением 1.24 он понимает по умолчанию как «24 января»
- Эксель убивает ведующие нули и приводит типы даже тогда, когда значение указано в кавычках. Делать так не надо, это ошибка. Но чтобы обойти эту проблему экселя, можно сделать небольшой «хак» — значение начать со знака «равно», после чего поставить в кавычках то, что необходимо передать без изменения формата.
- У экселя есть спецсимвол «равно», который в CSV рассматривается как идентификатор формулы. То есть, если в CSV встретится =2+3, он сложит два и три и результат впишет в ячейку. По стандарту он это делать не должен.
Пример валидного CSV, который можно использовать для тестов:
точно такой же SCSV:
Первый файлик, который реально COMMA-SEPARATED, будучи сохраненным в .csv, Excel-ом не воспринимается вообще.
Второй файлик, который по логике SCSV, экселом воспринимается и выходит вот что:
- Учлись пробелы, окружающие разделители
- Последний столбец вообще толком не распознался, несмотря на то, что данные в кавычках. Исключение составляет строка с «Петровым» — там корректно распозналось 1,24.
- В поле индекс Excel «опустил» ведущие нули.
- в самом правом поле последней строки пробелы перед кавычками перестали указывать на спецсимвол
Если же воспользоваться функционалом импорта (Данные -> Из файла) и обозвать при импорте все поля текстовыми, то будет следующая картина:
С приведением типов сработало, но зато теперь не обрабатываются нормально переводы строк и осталась проблема с ведущими нулями, кавычками и лишними пробелами. Да и пользователям так открывать CSV крайне неудобно.
Есть эффективный способ, как заставить Excel не приводить типы, когда это нам не нужно. Но это будет CSV «специально для Excel». Делается это помещением знака «=» перед кавычками везде, где потенциально может возникнуть проблема с типами. Заодно убираем лишние пробелы.
И вот что случаеся, если мы открываем этот файлик в экселе:
Я пытаюсь получить ".csv " файл в базу данных SQL с phpMyAdmin. Однако всякий раз, когда я импортирую его, я получаю ошибку: недопустимое количество столбцов во входных данных CSV в строке 1. Я провел весь день, играя вокруг с различными вариантами, чтобы попытаться заставить его работать, но безрезультатно. В моем ровно 47 столбцов .CSV-файл. Я создал 47 столбцов в моей таблице SQL. Однако имена не совсем совпадают с именами в файле. Когда я импортирую, он продолжает давать мне эту ошибку. Любая помощь был бы очень признателен! ~ Carpetfizz Одна вещь, которую я думал, может вызвать проблему, заключалась в том, что первый столбец ничего не назван в моем документе excel. Может ли это вызвать проблему?
EDIT 12: 30AM: phpMyAdmin уже последняя версия доступна, через (apt-get install phpmyadmin) (phpmyadmin уже последняя версия)
здесь - это .CSV-файл, если это поможет.
Если ваша таблица БД уже существует, и вы не хотите включать все столбцы таблицы в свой CSV-файл, то при запуске PHP Admin Import вам нужно будет заполнить поле имена столбцов в параметрах формата для CSV, показанных здесь внизу следующего скриншота.
- Выберите файл CSV
- установите формат в CSV
- заполните поле имена столбцов именами столбцов в вашем CSV
- если ваш CSV-файл имеет имена столбцов, перечисленные в строке 1, Установите "пропустить это количество запросов (для SQL) или строк (для других форматов), начиная с первого" до 1
исправлено! Я в основном просто выбрал "импорт", даже не делая таблицу сам. phpMyAdmin создал для меня таблицу со всеми правильными именами столбцов из исходного документа.
Я получил ту же ошибку при импорте .csv-файл с использованием phpMyAdmin.
решением моей проблемы было то, что мой компьютер сохранил .csv-файл с; (с запятой) в качестве разделителя вместо, (запятые).
в параметрах формата вы можете выбрать "столбцы разделены:" и выбрать ; вместо, (запятая).
чтобы посмотреть, в чем ваш компьютер хранит файл, откройте .CSV-файл в текстовом редакторе.
имел ту же проблему и сделал два изменения: (a) не переписал существующие данные (не идеально, если это ваше намерение, но вы можете запустить запрос на удаление заранее), и (b) подсчитал столбцы и обнаружил, что csv имеет пустой столбец, поэтому всегда платит, чтобы вернуться к исходной работе, даже если все "кажется" правильным.
вам нужно будет пропустить первую строку (где определены имена столбцов), и вам нужно будет проверить, какой "символ" разделяет ячейки (обычно это , , но в моем случае был ; )
Ниже приведена картина моего импорта:
ваше решение, похоже, предполагает, что вы хотите создать совершенно новую таблицу.
однако, если вы хотите добавить содержимое в уже существующую таблицу, найдите структуру таблицы и обратите внимание на количество столбцов (столбец id, если у вас есть один все еще подсчитывает ->, даже если это может быть автоматическое приращение/уникальный)
Итак, если таблица выглядит так id имя возраст секс
убедитесь, что ваша таблица excel выглядит так A1 id B1 имя C1 возраст D1 пол
и теперь они оба имеют 4 колонки.
также прямо под частичным импортом, рядом с пропуском количества запросов. увеличьте число, чтобы пропустить соответствующую строку. выбор 1 автоматически пропустит первую строку. Для тех, у кого могут быть заголовки в файлах excel
Если таблица уже была создана, и Вы были достаточно ленивы, чтобы не указывать столбцы в полях ввода имен, то все, что вам нужно сделать, это выбрать пустые столбцы справа от содержимого файла и удалить их.
У меня была аналогичная проблема с phpmyAdmin. Количество столбцов в импортируемом файле соответствует столбцам в целевой таблице базы данных. Я попытался импортировать файлы в обоих .csv и .формат ods безрезультатно, получая множество ошибок, включая один аргумент, что количество столбцов было неправильным.
Как .csv и .файлы ods были созданы с помощью LibreOffice 5.204. Основываясь на небольшом опыте с проблемами импорта в прошлые годы, я решил переделать файлы с помощью gnumeric электронная таблица, экспорт .СОД в соответствии со стандартом "строгого" формата. Вуаля! Больше нет проблем с импортом. Хотя у меня не было времени исследовать проблему дальше, я подозреваю, что что-то изменилось во внутренней структуре вывода файла LibreOffice.
при возникновении ошибок с входными файлами любого типа,проблемы с кодировкой общие.
простым решением может быть открытие нового файла, копирование вставки в него текста CSV, а затем сохранение его как нового файла.
последний столбец моей базы данных (это столбец F в электронной таблице) не используется и поэтому пуст. Когда я импортировал файл CSV excel, я получил ошибку "количество столбцов".
Это потому, что excel сохранял только столбцы, которые я использую. А-Е
добавление 0 в первую строку в F решило проблему, затем я удалил ее после успешной загрузки.
надеюсь, это поможет и сэкономит кому-то еще время и потерю волос:)
самая тупая вещь, которая когда-либо исправит эту ошибку в Microsoft Excel (при условии, что у вас действительно есть все остальное):
выберите данные и нажмите "границы все" в Excel (ставит визуальные границы вокруг ваших данных) перед сохранением CSV. Звучит бессмысленно? Я полностью согласен! Однако он исправит эту ошибку. Я использую этот трюк, по крайней мере, три раза в неделю.
У меня есть два файла CSV, и у обоих есть 2 общих столбца (имя компьютера и дата). В одном CSV-файле 5 столбцов, а во втором CSV-файле - 22 столбца.
Я хочу сравнить данные двух столбцов (имя компьютера и дату) со вторым файлом CSV, и если он совпадает, добавьте еще один столбец в CSV 1 (5 столбцов) и вставьте данные из другого столбца.
Я хочу сравнить оба столбца как в файле CSV, так и в случае совпадения значений, затем объединить столбец значений в первом файле CSV.
Я попытался начать создавать скрипт, но не знаю, как это можно сделать в PowerShell.
Это не сценарий, который я хочу создать, потому что он не соответствует моим требованиям.
Обновлять
alert.csv содержит 22 столбца, а Threats.csv - 5 столбцов. Я хочу сравнить угрозуs.csv с alert.csv.
Когда я запускаю сценарий ниже, я получаю такой выходной файл.
Вывод в файл данных
Я пробовал несколько способов решить проблему, но это не решается. $ угроза | Select-Object "Имя компьютера" | Export-CSV "d: \ ps \ dangerousss.csv" -NoTypeInformation
Вы на 100% уверены, что это CSV, а не книга Excel (* .XLS)? Можете ли вы открыть его в Блокноте и прочитать содержимое? Если это книга Excel, с которой можно работать, и мы можем это реализовать, но это намного больше кода и совсем другой процесс.
Привет, приятель, я исправил проблему, связанную с проблемой версии PowerShell. Я добавил код одной строки, чтобы запустить его в версии 1.0. Я обновлю команду, как только приду в офис. Спасибо за вашу помощь.
Я думаю, вы слишком усложняете ситуацию. Попробуй это:
Пройдя через это, он сначала импортирует файл alert.csv в набор данных $ alert. Затем делает то же самое для угроз.csv и $ угроза. Затем он проходит через каждую запись в наборе данных $ alert и ищет совпадающие записи в $ угрозе, где имя компьютера и время создания равны. Он сохраняет поле «Значение» из совпадающей записи в $ угрозе, добавляет это значение (если есть) к текущей записи в $ alert и переходит к следующей записи в $ alert. После анализа всех записей в $ alert он экспортирует объединенный набор данных в Combined.csv.
Дайте мне знать, если у вас возникнут проблемы или вопросы.
Спасибо за вашу помощь. Возникает один вопрос: можно ли вставить столбец в 1-й файл csv вместо создания нескольких файлов, потому что 1-й csv содержит 3 других столбца, и я тоже хочу это значение.
Конечно, но у вас уже должны быть эти значения в новом файле. Если вы хотите заменить существующий файл, вы просто измените имя файла, в который вы выводите. Итак, в последней строке Export-Csv ".\Combined.csv" -NoTypeInformation становится Export-Csv ".\alert.csv" -NoTypeInformation
@TheMadTechnician: проблема еще не решена. Я не получаю желаемого результата. Что я хочу добавить еще один столбец «значение» в существующий файл угроза.csv. Угроза.csv имеет 5 столбцов после столбца добавленного значения, всего 6 столбцов. На данный момент я не получаю никаких данных, я получаю только данные мусора.
ИЗМЕНИТЬ 12:30: phpMyAdmin уже является последней доступной версией через (apt-get install phpmyadmin) (phpmyadmin уже является последней версией)
Здесь находится файл .csv, если это поможет.
Зафиксированный! Я просто выбрал «Импорт», даже не создав сам таблицу. phpMyAdmin создал для меня таблицу со всеми правильными именами столбцов из исходного документа.
Я также столкнулся с этой проблемой в phpMyAdmin. Использование LOAD_DATA вместо LOAD решило проблему .
С id в качестве первичного ключа AUTO_INCREMENT. Записи были такие:
Кодировка таблицы была utf8_general_ci , а файл был закодирован в utf-8 .
ЗАГРУЗИТЬ также не удалось установить вручную значение для столбца id .
Самая глупая вещь, которая исправит эту ошибку в Microsoft Excel (при условии, что все остальное у вас правильно):
Выберите данные и нажмите «Границы всех» в Excel (визуальные границы вокруг данных) перед сохранением CSV. Звучит бессмысленно? Я полностью согласен! Однако это исправит эту ошибку. Я использую этот трюк не реже трех раз в неделю.
Последний столбец моей базы данных (это столбец F в электронной таблице) не используется и поэтому пуст. Когда я импортировал CSV-файл Excel, я получил ошибку «счетчик столбцов».
Это потому, что Excel сохранял только те столбцы, которые я использую. A-E
Добавление 0 к первой строке в F решило проблему, затем я удалил ее после успешной загрузки.
Надеюсь, это поможет и сэкономит кому-то время и избавит от выпадения волос :)
У меня была аналогичная проблема с phpmyAdmin. Количество столбцов в импортируемом файле соответствует столбцам в таблице целевой базы данных. Я безуспешно пытался импортировать файлы в формате .csv и .ods, получая множество ошибок, в том числе одну, утверждающую, что количество столбцов было неправильным.
Файлы .csv и .ods были созданы с помощью LibreOffice 5.204. Основываясь на небольшом опыте решения проблем с импортом в прошлые годы, я решил переделать файлы с помощью электронной таблицы gnumeric, экспортируя .ods в соответствии со стандартом «строгого» формата. Вуаля! Больше никаких проблем с импортом. Хотя у меня не было времени на дальнейшее изучение проблемы, я подозреваю, что что-то изменилось во внутренней структуре файлового вывода LibreOffice.
Кажется, ваше решение предполагает, что вы хотите создать совершенно новую таблицу.
Однако, если вы хотите добавить контент в уже существующую таблицу, найдите структуру таблицы и обратите внимание на количество столбцов (столбец id, если он у вас есть, все еще считается->, даже если он может быть автоматически увеличивающимся / уникальным)
Итак, если ваша таблица выглядит так: id Имя Возраст Пол
Убедитесь, что ваша таблица Excel выглядит как A1 id B1 Имя C1 Возраст D1 Пол
И теперь у них обоих по 4 столбца.
Также прямо под частичным импортом, рядом с пропустить количество запросов . увеличьте число, чтобы пропустить соответствующую строку. выбор 1 автоматически пропустит первую строку. Для тех, у кого могут быть заголовки в файлах Excel
Если таблица уже была создана, и вы были достаточно ленивы, чтобы не указывать столбцы во вводе имен полей, то все, что вам нужно сделать, это выбрать пустые столбцы справа от содержимого файла и удалить их.
Была та же проблема и внесены два изменения: (а) не перезаписывал существующие данные (не идеально, если это ваше намерение, но вы можете заранее выполнить запрос на удаление), и (б) подсчитал столбцы и обнаружил, что csv имеет пустой столбец, поэтому всегда стоит вернуться к исходной работе, даже если все «кажется» выглядит правильным.
Вам нужно будет пропустить первую строку (где определены имена столбцов), и вам нужно будет проверить, какой «символ» разделяет ячейки (обычно это , , но в моем случае был ; )
Ниже приведена фотография моего импорта:
При обнаружении ошибок с входными файлами любого типа обычно возникают проблемы с кодировкой .
Простое решение может заключаться в том, чтобы открыть новый файл, скопировать, вставив в него текст CSV, а затем сохранить его как новый файл.
Что вы хотите сделать, так это изменить "Поля, заканчивающиеся на" с ";" на "," и затем убедитесь, что выбрано "Использовать ЛОКАЛЬНОЕ ключевое слово".
У меня такая же ошибка при импорте файла .csv с помощью phpMyAdmin.
Решение моей проблемы заключалось в том, что мой компьютер сохранил файл .csv с; (точка с запятой) в качестве разделителя вместо (запятые).
Однако в параметрах, связанных с форматом, вы можете выбрать «разделенные столбцы:» и выбрать; вместо (запятая).
Чтобы увидеть, в чем ваш компьютер хранит файл, откройте файл .csv в текстовом редакторе.
Если ваша таблица БД уже существует, и вы НЕ хотите включать все столбцы таблицы в свой CSV-файл, тогда, когда вы запустите PHP Admin Import, вам нужно будет заполнить поле «Имена столбцов» в параметрах, зависящих от формата для CSV - показано здесь, внизу следующего снимка экрана.
В последнее время в курилках часто возникали дискуссии на тему сравнения производительности различных форматов хранения данных в Apache Hadoop — включая CSV, JSON, Apache Avro и Apache Parquet. Большинство участников сразу отметают текстовые форматы как очевидных аутсайдеров, оставляя главную интригу состязанию между Avro и Parquet.
Господствующие мнения представляли собой неподтвержденные слухи о том, что один формат выглядит "лучше" при работе со всем датасетом, а второй "лучше" справляется с запросами к подмножеству столбцов.
Как любой уважающий себя инженер, я подумал, что было бы неплохо провести полноценные performance-тесты, чтобы наконец проверить, на чьей стороне правда. Результат сравнения — под катом.
Примечание переводчика:
Изначально статья задумывалась как вольный перевод текста Дона Дрейка (@dondrake) для Cloudera Engineering Blog об опыте сравнения Apache Avro и Apache Parquet при использовании Apache Spark. Однако в процессе перевода я углубился в детали и нашел в тестах массу спорных моментов. Я добавил к статье подзаголовок, а текст снабдил комментариями со злорадным указанием неточностей.
Методология теста
Я выбрал Apache Spark 1.6 в качестве рабочей лошадки для тестов. Spark поддерживает Parquet из коробки, поддержка Avro и CSV подключается отдельно. Все операции проводились на кластере CDH 5.5.x из 100+ машин.
Мне было интересно замерить производительность форматов на различных видах процессинга — загрузки, простых запросов, нетривиальных запросов, обработки целого датасета, а также объем используемого дискового пространства.
Я запускал тесты через spark-shell с одной и той же конфигурацией для обоих датасетов (отличие было только в числе executor'ов). Режим шелла :paste спас мне жизнь, позволив копировать Scala-код прямо в REPL, не беспокоясь о многострочных командах, которые могут смутить интерпретатор.
Я брал время выполнения запроса с вкладки "Job" в Spark Web UI. Каждый тест я повторил трижды, а затем вычислил среднее время. Запросы к узкому датасету выполнялись на относительно нагруженном кластере, в то время, как запросы к широкому датасету выполнялись в моменты полного простоя кластера. Так получилось не специально, скорее, это совпадение.
Использование различных окружений между тестами (в т.ч. разное число воркеров и разную загруженность кластера) делает невозможным сравнение абсолютных значений.
Использование загруженного кластера само по себе негативно влияет на воспроизводимость результатов замеров при повторном запуске — им банально нельзя верить.
Трехкратное повторение эксперимента выглядит статистически несерьезным — доверительный интервал оценки будет очень большим. Впрочем, автор о доверительных интервалах даже не упоминает.
Тест "узкого" датасета
Для начала, я оценил время, за которое можно записать на диск узкий датасет в формате Avro или Parquet. Считал только эффективное время на запись, уже после того, как данные прочитаны в датафрейм. Получилась разница в пределах статистической погрешности. Таким образом, производительность записи узкого датасета для обоих форматов примерно одинакова.
Время сериализации получилось неправдоподобно большим, даже с учетом возможных накладных расходов на сеть и прочее — ведь на один воркер приходится менее 20 МБ выходных данных.
Выглядит так, как будто автор неправильно отделял время на чтение и процессинг и время на запись. В этом случае, вполне может получиться, что большая часть этого времени — это чтение 4х-гигабайтового CSV файла, возможно, даже в один поток. А на все остальное уходит 5-10 секунд.
Время записи на диск узкого датасета, в секундах (чем меньше, тем лучше):
После этого я посмотрел, сколько времени занимает простой подсчет числа строк в узком датасете. Avro и Parquet отработали одинаково быстро[^fast-row-count]. Для сравнения и чтобы запугать читателей, я посчитал также время подсчета несжатого CSV.
Файлы Parquet содержат в метаданных число объектов в блоке. При таком соотношении объема данных на воркер каждому достается не больше одного блока Parquet'а. Таким образом, для подсчета достаточно каждому прочесть по одной чиселке, а потом сделать общий reduce для получения итоговой суммы.
Для Avro задача значительно сложнее — блоки Avro также содержат число объектов в блоке, однако сами блоки значительно меньше (64 КБ по умолчанию), а файл содержит множество блоков. Теоретически, время подсчета всех объектов в avro-файле должно быть больше. На практике, для таких маленьких файлов разницу можно и не заметить.
Для подсчета числа строк в CSV-файле необходимо этот файл полностью прочитать, как и в случае с Avro. Если правильно шардировать 4 ГБ файл, на каждый воркер придется по 80 МБ данных, что можно прочесть за несколько секунд. Однако ж, процесс чтения у автора занимает 45 секунд, что свидетельствует в пользу того, что файл недостаточно распараллелен.
Время подсчета числа строк в узком датасете, в секундах (чем меньше, тем лучше):
Запрос для Parquet:
Запрос для Avro query:
Для запроса с группировкой Parquet оказался в 2,6 раз быстрее Avro:
Далее, я решил выполнить преобразование .map() на DataFrame е, чтобы сымитировать процессинг всего датасета. Я выбрал преобразование, которое считает число столбцов в строке и собирает все их уникальные значения.
Операция .distinct() существенно усложняет задачу. Для простоты можно считать, что она добавляет reduce-фазу к процессу, что само по себе означает, что измеряется уже не только .map() для всего датасета, но и оверхед на обмен данными между воркерами.
Это не совсем та задача, которая будет выполняться при реальной обработке данных, но тем не менее, она форсит порцессинг всего датасета. И вновь Parquet оказывается почти в 2 раза быстрее Avro:
Последнее, что обязательно нужно сделать — это сравнить размеры датасета на диске. График показывает размер в байтах. Avro был сконфигурирован на использование кодека сжатия Snappy, а для Parquet использовались дефолтные настройки.
Датасет в Parquet оказался меньше Avro на 25%.
Использовать настройки сжатия по умолчанию и даже не заглядывать в них — очень плохая практика.
Тем не менее, Parquet по умолчанию использует gzip. Gzip сжимает заметно сильнее Snappy. Вдруг разница в размерах обусловлена исключительно разными кодеками? Для корректного сравнения нужно посчитать размеры датасетов при использовании одинакового сжатия или вообще без оного.
Также для честности стоит отметить, что обычно текстовый файл можно сжать в разы. Допускаю, что агрессивно gzip-ованный исходный CSV-файл займет не больше 1,5 ГБ. Так что преимущество бинарных форматов будет не таким драматическим.
Тестовый датасет
Я подумал, что для тестов будет правильным использовать реальные данные и настоящие запросы. В этом случае можно ожидать, что производительность в продакшен окружении будет вести себя аналогично тестовой. Другими словами, для теста не обойтись подсчетом строк на суррогатных данных.
Выбор "реальных данных" и "реальных запросов" для теста представляется крайне спорной идеей, т.к. у всех разные реальные данные и запросы. Для решения этой проблемы синтезируют типовые тесты производительности хранилищ, например TPC Benchmarks.
Я покопался в датасетах, с которыми недавно работал, и нашел там два отлично подходящих для теста. Первый из них, назовем его "узкий", состоит из всего трех колонок и содержит 82,3 млн строк, что в CSV занимает 3,9 ГБ.
Как будет видно ниже, из этого получится 750-1000 МБ сериализованных данных, а обрабатываться это будет в 50 воркеров. Каждому воркеру достанется 15-20 МБ данных. Скорее всего, инициализация воркера займет больше времени, чем чтение и обработка данных.
Второй, назовем его "широкий", содержит 103 колонки и 694 млн строк, что дает CSV файл размером 194 ГБ. Я думаю, такой подход позволит оценить, какой формат работает лучше с файлами большого и маленького размера.
"Широкий" датасет не только в 30 раз шире, но и в 8 раз длиннее. И в 49 раз больше по исходному размеру. Датасеты правильнее называть "маленьким" и "большим".
Кроме того, судя по отношению размеров, похоже, что в датасетах представлены колонки разных типов. В этой работе различия в типах данных вообще игнорируются. Меж тем, это ключевой аспект формата хранения.
Тест "широкого" датасета
Я выполнил аналогичные операции на большом "широком" датасете. Напомню, этот датасет содержит 103 колонки и 694 миллиона строк, что выливается в 194 ГБ несжатого CSV файла.
А я, забегая вперед, сообщу, что это выливается в 5 ГБ Parquet и 17 ГБ Avro. Что при 500 воркерах даёт нам нагрузку на воркер в 100 МБ для Parquet или 340 МБ для Avro. По компактности хранения выиграл, конечно, Parquet. Но в файлах Avro получилось больше блоков, а значит, скорость их обработки можно увеличить, нарастив число воркеров. Так что, если загрузить кластер не в потолок и динамически рассчитывать число воркеров, можно добиться лучшей производительности Avro, чем в этих тестах.
Вначале я замерил время на сохранение широкого датасета в обоих форматах. Parquet каждый раз был быстрее Avro:
В подсчете числа строк Parquet наголову разбил Avro, выдавая результат быстрее, чем за 3 секунды:
Parquet по умолчанию использует размер блока в 128 МБ, что больше, чем средний объем данных на воркер. Таким образом, при работе с Parquet продолжает действовать трюк из "узкого" датасета, когда для вычисления числа строк в датасете достаточно прочесть одну чиселку из метаданных.
Для Avro-файлов приходится делать полное чтение датасета, интерпретируя только метаданные каждого блока и пропуская (не десериализуя) сами данные. Это выливается в "настоящую" работу диска. Для CSV ситуация еще хуже — там приходится еще и парсить каждый байт.
Для более сложных GROUP BY запросов Parquet вновь выходит в лидеры:
Вот здесь стоит вспомнить, что есть возможность запустить в 3,4 раза больше воркеров для Avro. Сохранит ли Parquet тогда лидерство?
И даже для .map() преобразований всего датасета Parquet вновь побеждает с убедительным отрывом:
И здесь также стоит помнить, что есть возможность запустить в 3,4 раза больше воркеров для Avro. И какую долю во времени работы операции занимает .distinct() , а какую — собственно чтение с диска?
Последний тест, тест эффективности утилизации дискового пространства, показал впечатляющие результаты для обоих участников. Parquet смог сжать исходные 194 ГБ в 4.7 ГБ, обеспечив грандиозное сжатие выше 97%. Avro также показал впечатляющий результат, сжав данные до 16.9 ГБ (91% сжатия). Поапплодируем обоим участникам:
Заключение
В итоге, Parquet продемонстрировал как минимум не худшую производительность на каждом тесте. При увеличении объема данных его преимущество стало очевидным. Своими хорошими результатами Parquet частично обязан лучшей эффективности сжатия, ведь Avro приходилось читать в 3,5 раза больше, чем Parquet. И Avro не показал той высокой производительности при чтении всего датасета, что приписывала ему молва.
Когда приходится выбирать формат хранения данных в Hadoop, нужно учитывать множество факторов, таких как интеграцию со сторонними приложениями, эволюцию схемы, поддержку специфических типов данных… Но если вы ставите производительность во главу угла, то тесты выше убедительно показывают, что Parquet — ваш лучший выбор.
И от себя добавлю. Это вполне годный замер производительности форматов. Он подтверждается многочисленными разрозненными наблюдениями из промышленного опыта нашей команды. Тем не менее, методология тестирования местами искажает замеры посторонними действиями (чтение CSV, GROUP BY', '.distinct() , . ), а местами совсем игнорирует важные вопросы (сжатие, форматы данных, . ). Я осознаю, что весьма непросто сделать каноничный тест с "дистиллированными" метриками. Но от блога Cloudera я ожидал именно этого.
Препроцессинг данных
При чтении узкого датасета из CSV я не выводил схемы, но мне пришлось сконвертировать колонку типа String в Timestamp . Я не включал время на это преобразование в результат, т.к. оно не относится к форматам хранения. При работе с широким датасетом я использовал вывод схемы, однако время на это я так же не учитывал.
Под выводом схемы (в оригинале — infer schema) имеется ввиду неявное преобразование из RDD в DataFrame с помощью Reflection.
В процессе тестирования я был удивлен, узнав, что нельзя сохранить Avro файл с колонкой типа Timestamp. Фактически, Avro версий 1.7.x в принципе не поддерживает ни Date , ни Timestamp .
Avro 1.8 поддерживает логические типы Date , Timestamp и их производные. По сути, они являются лишь оберткой над int или long .
Читайте также: