Чтение больших объемов данных в Python/Postgresql

Стек рассматриваемых технологий: Postgresql 9.3, Python 2.7 с установленным модулем «psycopg2».

Проблема


Как часто в вашей практике приходилось сталкиваться с задачей обработки таблиц большого объема (более 10 млн. записей)? Думаю вы согласитесь, что данная задача является довольно ресурсоемкой как в плане времени обработки, так и задействованных ресурсов системы. Сегодня я постараюсь показать альтернативный способ решения задачи.

Предложение:


В СУБД Postgresql есть прекрасный оператор для работы с большими объемами информации, а именно «COPY». Применение данного оператора позволяет нам читать и записывать огромные объемы информации в таблицу. В данной статье мы будем рассматривать режим чтения.

Согласно документации оператора «COPY» нам доступны несколько режимов чтения в файл либо в поток STDOUT, а также различные форматы, в том числе и «csv». Как раз его мы и постараемся использовать с максимальной пользой.

Подготовка:


В качестве «подопытного кролика» мы создадим таблицу с 1 млн. записей и напишем небольшой скрипт, отражающий суть метода. Sql файл можно найти в моем репозитории git (ссылку можно найти внизу статьи).

Также не забудьте установить расширение psycopg2!

Реализация:


Для выборки данных мы будем использовать чудесную функцию «copy_expert», которая позволяет нам исполнять «COPY» запросы из Python клиента.

query = """
    SELECT * from big_data inner join big_data as t1 USING(fname)
"""
output = StringIO()
self.cursor.copy_expert("COPY (%s) TO STDOUT (FORMAT 'csv', HEADER true)" % query, output)
data = output.getvalue()
output.close()
result = list()
for item in getResults(data):
    # do whatever we need
    item = {k: None if v == "" else v for k, v in item.items()}
    result.append(item)

Пояснения к коду:

  1. В запросе делаем объединение на себя, для его усложнения (замечено, что преимущество в скорости прямо пропорционально сложности запроса);
  2. В качестве буфера используем объект «StringIO», куда мы будем записывать данные из курсора;
  3. Парсить строку будем генератором «getResults»;
  4. Для удобства интерпретации я преобразую все пустые строки в тип «None», т.к. после использования «COPY» мы получаем строковые значения;
  5. Хочу отметить, что формат я будем использовать «csv» с лидирующей строкой заголовков, почему именно так поймете, немного позже.

Код генератора:

def getResults(stream):
    """
    get result generator
    """
    f = StringIO(stream)
    result = csv.DictReader(f, restkey=None)
    for item in result:
        yield item
    f.close()

Пояснения:

  1. Как видно из листинга, опять же используем уже знакомый буфер «StringIO»;
  2. Для преобразования строки «csv» в словарь (dictionary) используем метод «DictReader» родной библиотеки csv. По умолчанию, данный метод принимает первую строку за список полей словаря.

Вот, собственно, и все, что нам требуется!

Моя конфигурация: MacBook Air 2013 Processor: 1,3 GHz Intel Core i5, Ram: 4 ГБ 1600 МГц DDR3, SSD.

PS:
Хочу отметить, что данный подход к ускорению чтения не всегда работает, а именно, если у вас довольно простая таблица из 3-5 полей, ощутимой разницы вы не заметите (по крайней мере до 1 млн.). Однако данный метод показывает просто сумасшедший прирост в скорости, при сложных запросах ускорение достигает до 10-20 раз! Также очень сильно влияет конфигурация железа, на котором исполняется скрипт.

Весь код можно найти в git репозитории https://github.com/drizgolovicha/python_bulk_read.

Буду рад замечаниям и предложениям по оптимизации!

Спасибо что дочитали до конца.

UPD:
Результаты замеров выборка (14к) записей:
  1. Прямой SELECT, Where условие по неиндексированному полю — 21,4с
  2. COPY предыдущего запроса — 13,1с
  3. Выборка того же SELECT, но из materialized view с индексом по полю — 12,6с
  4. COPY materialized view — 1.8с
Поделиться публикацией

Комментарии 15

    +4
    Моя конфигурация
    А сколько попугаев за сколько скрипт отработал с 1 млн. записей?
      +1
      Поддерживаю, и насколько это быстрее традиционного метода?
        0
        Данный конкретный пример выдает что то вроде 44 сек. vs 32 cек. Однако, на работе приходится иметь дело с менее объемными выборками, но более сложные запросы (работа без индексов, таблица содержит порядка 100 полей). Вот там прирост просто потрясает от 50 сек. при использовании стандартного "SELECT" до 1-2 сек. с использованием вышеприведенной техники.
        0
        Хотелось бы уточнить, правильно ли я Вас понял?
        Вы выгружаете данные с запроса в CSV файл и потом для работы с этими данными просто читаете этот файл, так?
        Если так, то почему бы не использовать какую-нибудь временную таблицу, в которую можно сбросить эти данные, поработать с ними и потом удалить. Там же можно и индексы и любые плюшки реляционных БД.
          0
          Данные просто представлены в csv формате (по сути обычная строка). Можно использовать различные подходы. Из за специфики моей работы, я не могу менять структуру таблицы или даже дабовлять индексы, поэтому приходится добиваться прироста производительности альтернативными методами.
            +1
            Все равно я за то, чтобы использовать временную таблицу с выбранными данными. Хоть на локальной машине разверните тот же Postgresql, а Ваши дальнейшие алгоритмы работать с этим будут наверняка быстрее, например, если требуется дополнительная фильтрация этих данных или поиск.
            Не покидает чувство, что я неправильно понимаю ваши мотивы использовать именно CSV.
              0
              Тут вот в чем дело, действительно для ускорения выборки по 2м таблицам я использую materizlized view что по сути является временной таблицей. Но даже так, приведенный выше метод работает быстрее. Завтра доберусь до рабочей машины, постараюсь выложить конкретные цифры.
                0
                Результаты замеров выборка (14к) записей:
                1) Прямой select, Where условие по неиндексированному полю — 21,4с
                2) COPY предыдущего запроса — 13,1с
                3) Выборка того же select но из materialized view с индексом по полю — 12,6с
                4) COPY materialized view — 1.8с

                собственно как я и упоминал выше, скорость просто сумасшедшая
            +1
            PostgreSQL 9.5.2, Python 3.5.1 — может пора обновиться?
            зачем считывать миллионы записей я не увидел ни одной задачи и проблемы?
            наверное вы пытались сделать бэкапилку вручную или еще что-то типа того, а может статистику посчитать за 20 лет
              0
              Вы правы, я работаю с фин. данными за 30 лет. Конфигурация является стандартом компании, я и сам бы рад обновиться, но не имею возможности.
                0
                как я вас понимаю, такое ощущение порой, что над софтом поработал мастер бандажа и у вас нет выбора, как придумывать обходные пути )
              0
              Любопытный способ. Было бы приятно увидеть результаты синтетических тестов SELECT с условиями на примере трех ситуаций:

              Простой SELECT из большой таблицы
              Тот же SELECT из большой таблицы, разбитой на партиции с грамотно построенными индексами и использованием constraint exclusion
              Тот же SELECT, обернутый в COPY по вашей методике.

              В рамках статьи ожидал также объяснения, почему этот метод работает. Как в вашем случае строится план запроса? В моем понимании при таком сценарии все равно должен сначала выполняться внутренний селект, результат которого будет отправлен в копи. КМК, ускорение может быть разве что психологическое — в том случае, когда результат вложенного селекта будет отправляться в поток вывода пачками, по мере выполнения запросами.
              И еще. Как выглядит ваш postgresql.conf? Размер оперативной памяти ни о чем не говорит в отрыве от размера таблицы и размера work_mem и shared_buffers.
                0
                Не могли бы вы пояснить, каким образом грамотно построенные индексы и партиционирование могли бы ускорить процесс при выполнении схожего приведённому в примере запроса?
                Я не специалист в Postgresql, но есть подозрение, что в приведённом примере индексы могут только ухудшить время выполнения запроса, а партиционирование скорее всего повлияет никак.
                В других случаях — возможно, но для приведённых примеров с математической точки зрения и то и другое выглядит бесполезно. Что я пропускаю?
                  0
                  Я тоже не специалист в Postgresql, всего лишь энтузиаст с небольшим опытом работы с этой СУБД в личном проекте-печочнице. Мой проект предполагал работу с достаточно крупными таблицами-хранилищами, содержащими порядка 10^8 строк. Я столкнулся с тем, что даже простые селекты с условием на индексированное при помощи кластеризованного btree-индекса поле работали очень медленно. Это при том, что результат селекта предположительно должен был возвращать не более миллиона строк. Понятное дело, сложные запросы, включающие inner join тоже работали медленно.
                  Анализируя ситуацию, я убедился, что на больших таблицах идет раздувание древовидного индекса, когда его размеры становятся сравнимы с размерами самих данных. Одной из хороших практик в таких ситуациях является дробление таблицы на партиции, когда есть пустая мастер-таблица, задающая структуру данных, и набор наследниц, собственно хранящих информацию. Обычно дробят данные по полю (или комбинации полей), по которым обычно задаются условия запроса.
                  К примеру, есть таблица, в которую постоянно добавляются данные. Каждая запись промаркирована отметкой времени timestamp. Если предполагается делать запросы, извлекающие данные для конкретного дня, разумно дробить таблицу именно по этому условию. Один день — одна партиция.
                  В этой схеме зачастую на каждую партицию вешается check constraint, дабы при исполнении запроса отсекать партиции, в которых заведомо нет интересующих данных.
                  Такое решение сильно снижает общий размер таблицы с индексами, с которой будет работать СУБД во время исполнения одного или пачки похожих запросов. Я предполагаю, что это может положительно повлиять на производительность, т.к. вся таблица, ожидающая присоединения, может быть закеширована в RAM. Поэтому, в частности, я попросил уточнить сайзинг и настройки work_mem и shared_buffers.
                  Безусловно, в своих предположениях я могу ошибаться, т.к. я достоверно не знаю, что находится у постгреса под капотом. Да и не СУБДист я. Поэтому было бы действительно интересно увидеть в рамках статьи анализ и некие best practices. Но, как я понял из комментариев, у автора не было возможности менять структуру хранения и вообще была иная задача.
                  Кстати, насколько я помню, наличие индексов в постгрес приводит к замедлению только на маленьких таблицах. Да и то, если планировщик их по какой-то причине использует.
                0
                А вот такая штука не быстрее будет? Было бы интересно посмотреть на сравнение: http://blog.cloudera.com/blog/2016/03/feather-a-fast-on-disk-format-for-data-frames-for-r-and-python-powered-by-apache-arrow/

                Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                Самое читаемое