Недавно у меня возникла потребность собирать некоторую аналитику на среднем количестве данных (20-30 млн. в сутки). И нужно было выбрать в чем хранить такие объемы, первый вариант у меня был Postgresql, но он делал аггреции не недостаточной скоростью и я решил попробовать Clickhouse от Yandex, так как она как раз разрабатывалась под эти цели.
Начало работы.
Clickhouse устанавливается довольно просто, а также имеет превосходную документацию.
Основное что надо знать для работы следующее:
- в данной субд нет обновления
- записываемые данные должны быть по максимуму денормализованы, так как хотя есть join но работают они в большинстве медленно и надо понимать когда и как их использовать
- вставлять данные необходимо большими пачками иначе из-за внутренних особенностей вставка будет очень медленной.
- работа со строками (а также различными json) не оптимальна и лучше использовать более специфичные типы данных под заданные цели
- на одну таблицу может быть только один индекс, который может состоять из нескольких колонок (составной индекс, колонки слева-направо). При использовании этих колонок в запросе WHERE база оптимизирует выборку с помощью индекса.
- нет полноценного ACID
- для справочников в clickhouse рекомендуется использовать внешние словари, а не хранить их в таблицах. Внешние словари хранятся в памяти, поэтому они lдешевы и не сильно влияют на время выполнения запроса.
Для начала работы нужно создать таблицу, это делается с помощью команды create table:
CREATE TABLE IF NOT EXISTS test
(
client UInt64,
packet_id UInt64,
navigation_date DateTime,
sensors Nested(
port Int8,
raw_value Float64,
value Float64
)
) ENGINE = MergeTree PARTITION BY toYYYYMMDD(navigation_date)
ORDER BY (navigation_date, client)
TTL navigation_date + INTERVAL 6 MONTH
SETTINGS index_granularity = 8192
В данном случае создается таблица с движком MergeTree. Подробнее что это такое можно прочитать в документации.
В разделе PARTITION BY задается ключ партиционирования, т. е. поля по которым будут разбиватся данные для внутреннего хранения.
В разделе ORDER BY задает сортировку, порядок полей в которой является индексом.
TTL задает срок хранения данных.
Так же интересны тип поля Nested этот тип содержит вложенную таблицу, но хранится она как столбцы массивов. Подробнее можно почитать тут. Для себя я сделал вывод что ее можно использовать для ранения json данных с одним уровнем вложенности.
Загрузка данных.
Чтобы загрузить данные можно воспользоваться утилитой clickhouse-client.
Например из csv файла данные можно загрузить следующим образом:
clickhouse-client --format_csv_delimiter=";" --query="INSERT INTO test FORMAT CSV" < /var/lib/clickhouse/backup.csv
В данном случае мы импортируем данные из csv файла. Кроме csv есть много форматов для импорта/экспорта данных.
Примеры запросов и их особенности.
Вставка записи с полем типа Nested:
INSERT INTO test VALUES (1, 1, '2017-08-17',['1','2'],[100,50],[66.2,30.2])
Как видно из примера в каждое поле вложенной таблицы вставляется массив.
Следующий пример покажет , как можно сделать select запрос к данной таблице:
select client,
packet_id,
navigation_date,
sensor.port
from test
array join sensor
where dateDiff('hour', navigation_date, now()) <= 4
and dut.value < 0
order by client, packet_id
Данный запрос покажет все датчики в которых было получено отрицательное значение за последние 4 часа. Для того чтобы эти датчики выводились отдельными строками, (а не массивами в поле у пакета) используется ARRAY JOIN.
Еще один интересный запрос поможет отследить изменения датчика в определенном кол-ве пакетов за последние 4 часа:
select client,
nph_request_id,
navigation_date,
end_date,
port,
neighbor(packet_id, 1) - packet_id
from (
select *, neighbor(navigation_date, 1) end_date
from (
select client,
navigation_date,
packet_id,
sensor.port port,
sensor.raw_value val
from test
array join dut
where dateDiff('hour', navigation_date, now()) <= 4
and speed > 0
and sensor.raw_value > 0
order by client, navigation_date, dut.port
)
where neighbor(val, -1) != neighbor(val, 1)
and neighbor(port, 1) = port
)
where navigation_date < end_date
and neighbor(packet_id, 1) - packet_id > 5
В данном запросе используется больше число подзапросов, это связано с использование функции смещения neighbor. При работе с этой функцией нужно быть внимательным с сортировкой, так как смещение может зависеть от вложенных запросов, и, в некоторых ситуация, запросы могу дать неожиданный результат.
Как я понял это происходит из-за того что Clickhouse работает с пачками данных при выборке, а данное смещение считается он начала пачки, и получается результат которого не ожидаешь.
Заключение
Итого после небольшого кол-ва работы с данной СУБД в статье я постарался описать те ньюансы с которыми я столкнулся при практическом использовании.
По скорости агрегирования Clickhouse работает быстрее на указанных объемах данных, чем PostgreSQL, что в целом ожидаемое.
В целом данное решение мне понравилось и я буду расширять его использование в своих проектах.