Александр Румянцев
Александр Румянцев личный блог
15 июля 2018, 20:43

Как быстро из PostgreSQL и ClickHouse получить в Python длинные истории цен

Разбил много ☕кружек в поисках решения для ️быстрого получения длинных историй цен для большого количества активов в Python. Ещё имел смелость желать работать с ценами в numpy-массивах, а лучше сразу в pandas.

Стандартные подходы в лоб работали разочаровывающе, что приводило к выполнению запроса к БД в течение 30 секунд и более. Не желая мириться, я нашёл несколько решений, которые полностью меня удовлетворили.
Как быстро из PostgreSQL и ClickHouse получить в Python длинные истории цен

Ноги растут из объектной природы Python. Ведь у него даже целые числа являются объектами, что крайне отрицательно влияет на скорость работы. Менять язык я категорически не хотел.

Первым решением была группировка истории цен силами PostgreSQL, что приводило к незначительной просадке производительности на стороне БД, но ускоряло задачу примерно в ~3 раза. Подробнее метод описан здесь.

Итогом появилось понимание, что в Python надо каким-то образом получить весь набор данных одним куском, хотя бы строкой. И разобрать по numpy-массивам или сразу в pandas.

Решение в лоб для PostgreSQL

 

Делаем группировку данных в sql-запрос. Пример:

Код доступен на Quantrum.me.

Разобрать данные проще простого:

Код доступен на Quantrum.me.

Производительность на ~1.7 млн. строк:

%timeit get_prices_fast(is_adj=False)  # 11.9s

 

Готовые пакеты Python


Python хорош своим сообществом, которое сталкивается со схожими проблемами. Для нашей цели подойдут следующие:

  • odo — создан для оптимизации скорости передачи данных из одного источника в другой. Полностью на Python. С PostgreSQL взаимодействует через SQLAlchemy.
  • warp_prism — C-расширение, используемое проектом Quantopian для получения данных из PostgreSQL. В основе заложен функционал odo.

Оба пакета используют возможность PostgreSQL копировать данные в CSV:

Код доступен на Quantrum.me.

На выходе данные разбираются в pandas.DataFrame() или numpy.ndarray().

Так как warp_prism написан на C, он имеет существенное преимущество по скорости парсинга данных. Но одновременно с этим имеет существенный недостаток — ограниченную поддержку типов данных. То есть он парсит int, float, date и str, но не numeric. У odo подобных ограничений нет.

Для использования необходимо описать структуру таблицы и запрос с помощью пакета sqlalchemy:

Код доступен на Quantrum.me.

Тесты скорости:

%timeit odo(query, pd.DataFrame, bind=engine)  # 13.8s
%timeit warp_prism.to_dataframe(query, bind=engine)  # 8.4s
%timeit warp_prism.to_arrays(query, bind=engine)  # 8.0s

warp_prism.to_arrays() — подготовка python-словаря с numpy-массивами.

Что можно сделать с ️ClickHouse?


PostgreSQL всем хорош, кроме аппетита с размеру хранилища и необходимости настройки шардинга для больших таблиц. ClickHouse сам шардирует, хранит всё компактно, а работает молниеносно. Для примера таблица на PostgreSQL размером ~5Gb в ClickHouse умещается в ~1Gb. Использование ClickHouse для хранения цен описано здесь.

К моему огорчению odo не помог, хоть для sqlalchemy есть расширение clickhouse. Воспоминания о скорости работы clickhouse в консоли меня навели на идею обращения к БД через создание отдельного процесса. Я знаю, что это долго и ресурсозатратно, но результаты оказались выше всяких похвал.

Код доступен на Quantrum.me.

Результат:

%timeit ch_pandas(cmd)  # 1.6s

 

Запрос к HTTP-порту ️ClickHouse


Результаты чуть ухудшились при обращении непосредственно к порту 8123, где отвечает БД:

import urllib
%timeit pd.io.parsers.read_csv(...)
# 1.9s

Но не обошлось без ложки дёгтя.

 

Ложка дёгтя с ClickHouse


БД впечатлила на больших выборках, но на маленьких результаты разочаровали. В ~20 раз хуже odo. Но это издержки на дополнительный обвес с запуском процесса или обращением по HTTP.

Результаты:

Как быстро из PostgreSQL и ClickHouse получить в Python длинные истории цен

Заключение

Данной статьёй погоня за ускорением взаимодействия между Python и базами данных закончена. Для PostgreSQL при стандартных полях и необходимости универсального доступа к ценам лучшим способом является использование пакета warp_prism от Quantopian. При необходимости хранить большие объёмы истории и высокой частоте запросов большого количества строк идеально подойдёт ClickHouse.

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

Александр Румянцев
Автор на Quantrum.me
Telegram-канал (ссылка может не работать): @quantiki

Интересуетесь алготрейдингом на Python? Присоединяйтесь к команде.

15 Комментариев
  • scooter
    15 июля 2018, 23:37
    Хорошая тема. Нужная
  • Алексей Кобрин
    15 июля 2018, 23:40
    Вы ресурсом не ошиблись?
      • TradingKit
        16 июля 2018, 09:53
        Александр Румянцев, Не слушайте таких людей, им бы про пенсионный фонд и Украину тут читать. Пишите еще, интересно, плюсую.
  • Андрей К
    16 июля 2018, 00:37
    я правильно понял, что вы на питоне обошли скорость sql запросов?
  • Андрей К
    16 июля 2018, 00:43
    Еще раз перечитал. Мне кажется архитектура БД была не правильна выстроена. Не настроены прально индексы, составные индексы.

    Сделать SELECT, ну пусть с группировкой, на 1.7млн записей на правильно выстроенной бд — это дело секунды, максимум двух практически на любой БД.
      • Андрей К
        16 июля 2018, 00:47
        Александр Румянцев, понятно, буду знать =), наверное плохо прочитал
  • _landy
    16 июля 2018, 09:59
    redis еще попробуйте
  • Валентин
    16 июля 2018, 11:46
    Не очень разбираюсь, но может сразу в pandas читать через pandas.read_sql_query? Или там тоже с производительностью не очень?
  • Брахман Пилорама
    02 июля 2023, 20:43
    Забросили тему? Клик с тех пор очень шагнул вперед.

Активные форумы
Что сейчас обсуждают

Старый дизайн
Старый
дизайн