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

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

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

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

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

💽Установка

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

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

deb http://repo.yandex.ru/clickhouse/trusty stable main

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

sudo apt-key adv --keyserver keyserver.ubuntu.com --recv E0C56BD4    # optional
sudo apt-get update

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

sudo apt-get install clickhouse-client clickhouse-server-common

Запуск:

sudo service clickhouse-server start

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

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

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

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

clickhouse-client

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

SELECT 1;

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

🐍ClickHouse и Python

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

pip install clickhouse-driver

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

from clickhouse_driver.client import Client

client = Client('127.0.0.1')
print(client.execute('select 1'))

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

📈ClickHouse и цены

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

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

-- промежуточная таблица --
CREATE TABLE minutes_tmp
(
symbol String,
dt DateTime,
high Float64, 
low Float64, 
open Float64,
close Float64,
volume UInt64,
last_volume UInt32
) ENGINE = Log;

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

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

time clickhouse-client --query="INSERT INTO minutes_tmp FORMAT CSV" < SPY.csv

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

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

SELECT * FROM minutes_tmp LIMIT 10

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

-- основная таблица --
CREATE TABLE minutes
ENGINE = ReplacingMergeTree(day, (symbol, dt), 8192)
AS SELECT DISTINCT
toDate(dt) AS day,
symbol,
dt,
open,
high,
low,
close,
volume
FROM minutes_tmp;

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

INSERT INTO minutes 
SELECT DISTINCT 
toDate(dt) AS day, symbol, dt, open, high, low, close, volume 
FROM minutes_tmp
WHERE (symbol, dt) NOT IN (SELECT (symbol, dt) FROM minutes);

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

SELECT formatReadableSize(sum(bytes)) FROM system.parts
WHERE table = 'minutes' AND active

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

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

SELECT * FROM minutes WHERE symbol = 'AAPL' LIMIT 10

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

client = Client('127.0.0.1')
print(client.execute("SELECT * FROM minutes WHERE symbol = 'AAPL' LIMIT 10"))

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

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

-- to day
SELECT 
uniq(dt), count(), 
symbol, 
day, 
argMin(open, dt) as open, 
max(high) as high, 
min(low) as low, 
argMax(close, dt) as close, 
sum(volume) as volume 
FROM minutes 
GROUP BY day, symbol 
ORDER BY day desc 
LIMIT 10

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

-- to week
SELECT 
uniq(dt), count(), 
symbol, 
toMonday(day) as monday, 
argMin(open, dt) as open, 
max(high) as high, 
min(low) as low, 
argMax(close, dt) as close, 
sum(volume) as volume 
FROM minutes 
GROUP BY monday, symbol 
ORDER BY monday desc 
LIMIT 10

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

SHOW DATABASES

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

SHOW TABLES FROM default

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

SELECT formatReadableSize(sum(bytes)) FROM system.parts 
WHERE table = 'minutes' AND active

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

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

🏁Вывод

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

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

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

Александр Румянцев
Автор Quantrum.me
Telegram-канал: https://t.me/quantrums

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

☝Хотите торговать криптовалютой?🎓Обращайтесь к профессионалам👍.