ClickHouse и Python для хранения истории цен

Продолжая поиски быстрой базы данных для хранения цен я попробовал применить для своих нужд ClickHouse от Яндекса. Это open-source колоночная база данных для хранения и обработки временных рядов в реальном времени.

У ClickHouse огромный список ограничений, к которым мы не привыкли работая с реляционными базами данных. Но кто нас остановит?

Так же попробуем подружить ClickHouse с Python🐍.

Документация доступна здесь.

💽Установка

Установка и запуск доступны любому. Всё любезно расписано в документации. Устанавливаем из пакетов.

В файл [code]/etc/apt/sources.list.d/clickhouse.list[/code] добавим:

[code commandline]

[/code]

Затем выполним команды:

[code commandline]

[/code]

Теперь можно установить ClickHouse:

[code commandline]

[/code]

Запуск:

[code commandline]

[/code]

Настройки лежат здесь: [code]/etc/clickhouse-server/[/code]

Логи лежат здесь: [code]/var/log/clickhouse-server/[/code]

🔌Подключение

Подключиться можно клиентом с помощью команды:

[code commandline]

[/code]

Вас поприветствует сервер, где вы можете выполнить простую команду:

[code sql]

[/code]

На что получите простой ожидаемый ответ.

🐍ClickHouse и Python

Есть несколько пакетов. Мы будем использовать первый попавшийся и установим его командой:

[code bash]

[/code]

Создадим простой скрипт [code]click.py[/code]:

[code python]

[/code]

И запустим его командой: [code]python click.py[/code]

📈ClickHouse и цены

База данных создавалась для Яндекс.Метрики и не планировалось использовать её в качестве хранилища истории цен. Она не умеет поддерживать уникальность записей по ключу, но имеет много другого. Так что уникальность мы будем поддерживать самостоятельно.

Для воплощения нашей мечты нам придётся пользоваться промежуточной таблицей, чтобы силами запросов контролировать уникальность. Создадим таблицу для загрузки данных.

[code sql]

[/code]

Разработчики не рекомендуют использовать тип Float64, но мы пойдём своим путём и набьём немного шишек.

У меня в распоряжении цены в CSV-файлах, так что я рассмотрю именно этот кейс. Обратите внимание на последовательность полей в созданной таблице, у меня такая структура файлов. Загрузим данные из CSV-файлов командой:

[code sql]

[/code]

Здесь нет проверки дубликатов. Так что, сколько раз запустим, столько повторений получим. Помним об этом и учитываем. Так же помним, что мы используем движок Log, он быстрый, но не устойчивый к сбоям. Соответственно, не храним там ничего ценного.

Проверим загруженные данные командой:

[code sql]

[/code]

Настало время сложить данные в основную таблицу. Используем движок ReplacingMergeTree, который поддерживает ключ и умеет удалять дубликаты. Дубликаты удаляет непредсказуемо и может вернуть две строки с одинаковым ключом. Ключом сделаем поля symbol и dt. Для движков *MergeTree в ключе обязательно должно находится поле с типом Date. Команда:

[code sql]

[/code]

Дубликаты промежуточной таблицы исключим командой DISTINCT. А при повторном добавлении данных исключим дубликаты дополнительным условием:

[code sql]

[/code]

Теперь можно посмотреть занимаемое пространство:

[code sql]

[/code]

Общий объём загруженных CSV файлов в 64Мб уложился примерно в ~20Мб. Вот она, сила колоночных баз данных.

Проверим данные:

[code sql]

[/code]

Также можно убедится в успешной работе python-скрипта. Float работает предсказуемо и от него будет разумно отказаться в пользу Int при хранении цен:

[code python]

[/code]

🛠Общие команды

Я загружал минутную историю цен. Для получения дневных цен подойдёт запрос:

[code sql]

[/code]

А для получения недельных:

[code sql]

[/code]

Посмотреть базы данных можно командой:

[code sql]

[/code]

Посмотреть таблицы можно командой:

[code sql]

[/code]

Размер *MergeTree таблиц:

[code sql]

[/code]

🎁Веб-интерфейс для ClickHouse

Красивый, наглядный, удобный и открытый: https://tabix.io/

🏁Вывод

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

В остальном, это просто мечта!

💬В комментариях задавайте вопросы. Расскажите, как вы храните цены сохраняя удобство обработки и скорость доступа.

Александр Румянцев
Автор на Quantrum.me
Telegram-канал📣: @quantiki

Экспертиза торговых стратегий и алгоритмов, оптимизация среднесрочных портфелей.
☝Хотите торговать криптовалютой?🎓Учитесь у профессионалов👍.