Дока формируется на основе получения личных шишек, консультаций и редактированной копипасты с ClickHouse не тормозит. Дополнения и помощь в переводе приветствуются.
CH вешается на два порта:
- 8123 (http-протокол),
- 9000 (бинарный протокол).
Если их перепутать, никуда вы не подключитесь. Поэтому не путайте порты и настройки ssh-туннелей.
основной здесь:
/etc/clickhouse-server/config.xml
главные опции там -
- listener (какой интерфейс слушать, для закрытого aws можно 0.0.0.0)
- <path>/var/lib/clickhouse/</path> путь к данным
в /etc/metrika.xml (по умолчанию называется так) можно создать переопределяющие настройки:
- <yandex><zookeeper><node id='1'><host>172.31.16.254</host><port>2181</port></node></zookeeper></yandex>
- для сборки с помощью ./release нужно отключить флаг -pie в debian/rules, иначе distcc в текущей реализации собирает только на локалхосте
С настройками по умолчанию, ZooKeeper является бомбой замедленного действия: Сервер ZooKeeper не будет удалять файлы со старыми снепшоты и логами при использовании конфигурации по умолчанию (см. autopurge), это является ответственностью оператора.
Эту бомбу нужно обезвредить < https://clickhouse.yandex/docs/ru/operations/tips.html
[Andrey]
какой engine использовать для хранения данных, не привязанных к дате? Например, есть список доменов с определёнными характеристиками (~100 млн). Надо получать запросами различную статистику по этим данным. MergeTree требует EventDate.
[Stanislav Vlasov] c MergeTree можно использовать функцию для партиционирования, по-умолчанию - от даты. Где-то в недрах док есть пример по созданию таблиц с кастомным ключом.
[Alexey Milovidov] MergeTree вообще не требует партиционирования. Можно использовать без партиционирования - не указывать PARTITION BY:
https://clickhouse.yandex/docs/ru/single/#proizvolnyi-klyuch-particzionirovaniya
3.1 список: см.: clone_libs.sh
на данном этапе испытываем clickhouse_driver, нативный интерфейс python/c/clickhouse.
поскольку CH все еще бывает довольно нестабилен при неправильных запросов (см. раздел 4), использовать ORM пока нет смысла.
кроме того, поскольку мы непланируем использовать clickhouse напрямую в существующих проектах, ORM неактуальны.
3.2 удобство использования
3.3 бенчмарки
кафка - самый частый вариант, см. 6.2
proxysql - для реализации механизма очереди запросов - например "Если число соединений между серверами будет больше чем max_distributed_connections, выкинется исключение или запросы встанут в очередь?" С дефолтным значением 1024 и если у вас в кластере меньше 1024 шардов, вы в этот лимит никогда не упрётесь. Значение можете проверить запросом select * from system.settings where name = 'max_distributed_connections'.
балансировщик и прокси chproxy
В КХ нельзя вставлять данные маленькими порциями. Чем больше порция - тем лучше. При высокочастотной записи по одной записи КХ начинает отставать, потом зависает. Это вызвано внутренними структурами данных КХ.
Пишите хоть по одной строке, но не часто, либо:
[Tima Ber]
- не вставлять меньше чем 1000 строк. Ни 10, ни 20, не меньше 1к.
- вставлять не часто, не чаще 1 раза в секунду, в один поток.
- Лучше увеличить размер батча, чем иметь больше одного потока вставки.
[Alex More]: используйте input_format_skip_unknown_fields=1 чтобы игнорировать поля, которые есть в json, но нет в схеме
curl -H "Content-Type: application/json" -X POST -d 'insert into table_name FORMAT JSONEachRow {"create_date": "12345", "ololo":"ololo"}' http://default:psw@localhost:8123/?input_format_skip_unknown_fields=1
Vadim Metikov, [26.02.18 13:59] кто-нибудь прореживал так данные? партишионинг по ГГГГММ более всего подходит
DB::Exception: Element of set in IN or VALUES is not a constant expression
[Milovidov] Cвязано с тем, что данные для вставки записаны не в точно ожидаемом формате. Тогда ClickHouse пытается интерпретировать указанное значение как произвольное выражение и затем преобразовать его к нужному типу данных. Чтобы увидеть более явно, где возникает проблема, выставите настройку input_format_values_interpret_expressions в ноль.
[Time Ber] Данные могут поехать, когда в исходных данных нет значения, а коде формирования батча не учитывается это и не проставляются дефолтные значения для каждого из типов. Например в данных нет даты, тогда в строку для КХ нужно ставить '0000-00-00', иначе при вставке столбцы "поедут"
Nikita Tokarchuk, [21.02.18 18:46] В чем может быть плюс своего решения против Buffer таблицы? Цель — буферизировать запросы на вставку, идущие в реалтайме один за другим.
Документация предлагает два решения — делать буфер в приложении которое делает insert, либо использовать Buffer таблицу
[Andrey @rhenix]: Buffer лежит в оперативке + расход на коннекты от разных инсертов. Поэтому в некоторых случаях буферизация на аппликухе куда лучше
Если нужно чтобы данные собирались и интервально сбрасывались, то нет смысла изобретать велосипед, Buffer для того и живет
Для переноса данных достаточно остановить сервер и перенести директорию с данными (например /var/lib/clickhouse) с одной машины на другую любым удобным способом - scp/rsync/mount hdd/ssd :-)
Соответственно, и при переустановке самого кликхауза данные подхватятся автоматически.
[Salim Murtazaliev] Если таблица назначения является реплицируемой, то при записи в таблицу Buffer будут потеряны некоторые ожидаемые свойства реплицируемых таблиц. Из-за произвольного изменения порядка строк и размеров блоков данных, перестаёт работать дедупликация данных, в результате чего исчезает возможность надёжной exactly once записи в реплицируемые таблицы.
counter_id находится в составе primary key:
запрос 1:
SELECT * FROM vkad.ponylog WHERE counter_id between {lim} and {skip} ORDER BY counter_id
без лимита по памяти на запрос (max_memory_usage в /etc/clichkouse-server/user.xml) - по дефолту 10 гб - уходит в своп с лимитом 2 гб. - падает на ~10-м запросе. работает медленно
запрос 2:
SELECT * FROM vkad.ponylog ORDER BY counter_id LIMIT {lim}, {skip}
не падает по памяти, но очень быстро замедляется до неприемлемых значений, при этом нет сортировки.
запрос 3:
SELECT * FROM vkad.ponylog WHERE counter_id between {start} and {end} ORDER BY counter_id
летает отлично, быстро, сортировка работает.
поскольку нормального explain нет, выглядит это так, что в первом запросе кликхауз пожирает всю доступную память, видимо многократно запихивая индекс в память. руководство о таком предупреждает, но, конечно, нам это не подходит
-
с помощью функций: в отличие от mysql нельзя сделать запрос в духе
select extract(desc, '\\[.*?]') from TBL group by 1(сгруппировать по первой колонке) нужно делать так:SELECT extract(desc, '\\[.*?]') FROM bigdb.ponylog GROUP BY extract(desc, '\\[.*?]')
string = # точное совпадение
SELECT count(*)
FROM bigdb.ponylog
WHERE desc = '[Pony Updater] Update from VK:<br/>Status: archived - paused<br/>'
LIMIT 1
Row 1:
──────
count(): 20190018
1 rows in set. Elapsed: 0.782 sec. Processed 38.96 million rows, 3.93 GB (49.80 million rows/s., 5.03 GB/s.)
string like '%pattern%' #частичное совпадение
SELECT count(*)
FROM bigdb.ponylog
WHERE desc LIKE '% Updater%'
┌──count()─┐
│ 20617731 │
└──────────┘
1 rows in set. Elapsed: 0.832 sec. Processed 38.96 million rows, 3.93 GB (46.81 million rows/s., 4.73 GB/s.)
string with regexp/group by regexp #поиск по регулярному выражению, с группировкой по нему же
SELECT
count(*),
extract(desc, '\\[.*?]')
FROM bigdb.ponylog
GROUP BY extract(desc, '\\[.*?]');
┌──count()─┬─extract(desc, \'\\\\[.*?]\')─┐
│ 1078529 │ │
│ 556649 │ [StatusChanger] │
Для чего удобно использовать внешние словари (например c MySQL) [shegloff]
В кликхаус копируем например BigDataBannerShows.LogsAggregated в where как раз идут условия на всякие user_options в UserDictionary.UserInfo, status в Partners.Site и так далее вот эти таблицы - чтобы не копировать в кликхаус их постоянно, удобно подключить как внешние словари они раз в 5 мин обновляются и хорошо
Кликхауз напрямую в MySQL за ними не ходит, а забирает к себе: он их выселекчивает раз в 5 минут и держит у себя в оперативке, и обращение идет в оперативку уже
словари не обязательно целиком хранятся в оперативе, есть возможность держать кэш ограниченного рзамера, а полноценный словарь будет при этом находиться в том же мускуле [Yuran aka yourock88] cловарь 1 раз загружается в память, тогда как при джоине вы это делаете на каждый запрос
Как в подзапросе сделать условие на колонку из внешней таблицы? Это вообще возможно?
- Через словарь и getDict. [Vasilij Abrosimov]
Если делать дурные запросы, то при джойнах КХ может тянуть в память одну из таблиц целиком, что может вас убить.
дурной запрос:
ANY INNER JOIN urls2 USING id
ограничим условие с подзапросом и where:
ANY INNER JOIN (SELECT id, url FROM urls2 WHERE id = url_id) USING id[Tima Ber] илиANY INNER JOIN (SELECT id, count() AS shows FROM urls2 WHERE day = today) USING (id)[Shegloff]
вывести предыдущее значение строки event - runningDifference(event) [Владимир Мюге]
подскажите а есть ли способ при выборке count() с группировкой по дате получить 0 для пустых дней, вместо пропущенных строк в результате? вместо
20180102 1
20180104 1
получить
20180102 1
20180103 0
20180104 1
[Alexey Sheglov] Немного наркомании
:) select day, count() from test.huest group by day
┌────────day─┬─count()─┐
│ 2018-02-21 │ 1 │
│ 2018-03-03 │ 1 │
└────────────┴─────────┘
:) select day, sum(c) as count from (select day, count() as c from test.huest group by day) ALL RIGHT JOIN (select day, c from (select toDate('1970-01-01') + number as day, 0 as c from system.numbers limit 30000) where day between (select min(day) from test.huest) and (select max(day) from test.huest)) USING(day) group by day
┌────────day─┬─count─┐
│ 2018-02-21 │ 1 │
│ 2018-02-22 │ 0 │
│ 2018-02-23 │ 0 │
│ 2018-02-24 │ 0 │
│ 2018-02-25 │ 0 │
│ 2018-02-26 │ 0 │
│ 2018-02-27 │ 0 │
│ 2018-02-28 │ 0 │
│ 2018-03-01 │ 0 │
│ 2018-03-02 │ 0 │
│ 2018-03-03 │ 1 │
[Gennadiy Alekseev / @alekseevgena] У меня есть два массива во вложенном запросе, в результате я хочу получить элементы первого массива, которые не содержатся во втором?
[Natalya]:
`SELECT [3, 4, 8] as mas, arrayFilter(x -> x NOT IN (1,2,3,4), mas) AS res`
[Alexey Sheglov / @Shegloff]:
еще такой изврат есть: SELECT arrayJoin(one_arr) AS res, any(two_arr) AS two_arr FROM ( SELECT [1, 2, 3, 4, 5] AS one_arr, [3, 4, 5, 6, 7, 8] AS two_arr ) GROUP BY res HAVING has(two_arr, res) = 0
┌─res─┬─two_arr───────┐
│ 1 │ [3,4,5,6,7,8] │
│ 2 │ [3,4,5,6,7,8] │
└─────┴───────────────┘
не массив, а столбец преобразовать в строку (аналог concat/group_concat)/поворот + concat колонки [Qq]
А
1
2
3
to: 1,2,3
[Nikolai Kochetov]
Для колонок: можно сделать groupArray(), потом джойн на результат
Для массива - arrayStringConcat()
использование zk для нескольких приложений одновременно (например, кафка+clickhouse)
tldr: при работе с кафкой надо создать две таблицы (одна типа кафака, другая, допустим, mergetree) и 1 materialised view, данные принимаются в кафку, а MV в фоне укладывает их в нужную таблицу
использование кафки через командную строку (для тестов):
проблемы при укладывании данных из MV в таблицу: в логах StorageKafka (fromkafka): EOF reached for partition 0 offset 59651
проверить stream_flush_interval_ms
[Milovidov]
Вставка всегда 1 к 1. Если не было сообщения об ошибке - то есть, если запрос INSERT SELECT выполнился успешно - все данные будут вставлены.
Какие могут быть случаи, когда возникают похожие ошибки:
-
вставка текстового дампа, в котором есть строки с переводом строки, экранированные как
abc\\ def. Тогда количество строк, отображаемое wc -l, будет фактически больше реального. -
вставка в Distributed таблицу, которая развозит данные асинхронно.
-
вставка в ReplicatedMergeTree полностью повторяющихся блоков данных, которые дедуплицируются;
-
вставка в таблицу, которая меняет количество строк при мерже, типа Collapsing, Replacing...
-
вставка в Distributed таблицу с неправильной конфигурацией кластера - например, internal_replication = 1 при использовании не-Replicated таблиц; или когда перепутали шарды и реплики;
-
вставка в Replicated таблицу и чтение данных с отстающих реплик;
-
вставка в Buffer таблицу и чтение из обычной таблиц
Alex [@Player_a], [20.02.18 01:37] Есть база с collapsingmergetree таблицей, размером в 100 Гб. Появилась необходимость поднять реплику и возник вопрос - а как собственно происходит синхронизация? Как на реплику отправить 100гигабайт, которые уже есть на мастере?
[Alexey Milovidov], [20.02.18 01:41] Если у вас таблица уже имеет тип ReplicatedCollapsingMergeTree, то всё просто: добавление новой реплики делается запросом CREATE TABLE на новом сервере. В параметрах указывается тот же путь таблицы в ZK, но другой идентификатор реплики. После создания, реплика синхронизируется сама - идёт и скачивает все нужные данные. Если таблица просто CollapsingMergeTree (не Replicated) - то посмотрите раздел документации "Преобразование MergeTree в ReplicatedMergeTree".
В логи зукипера почти нет смысла смотреть, лучше в кликхаусные (там будет что-то вроде pulling logs to queue).
Проще посмотреть select * from system.replicas WHERE database='db' AND table='table' FORMAT Vertical, там интересные поля last_queue_update, active_replicas, absolute_delay.
BTW Если в удобоваримом виде хочется посмотреть, что происходит внутри, то можно включить <part_log> в конфиге и смотреть в таблицу system.part_log.
[Vitaliy Lyudvichenko]
ошибка Exception: Could not find a column of minimum size in MergeTree, part /var/lib/clickhouse//data/public/events/20180124_20180124_14437_14442_1/ такого куска нет на этой реплике. Я сделал detach partition 201801 и attach partition 201801, при аттаче потерянный кусочек скачался с другой реплики и стало хорошо
можете на проблемной реплике вообще удалить все данные после detach из папки detached, после attach проблемная скачает всю партицию
[Vladislav Denisov]
Feb 15 16:05:59 cdn2 docker[15331]: 2018.02.15 13:05:59.045028 [ 11 ] <Debug> local.clickstream (StorageReplicatedMergeTree): Part 20180101_20180131_0_267872_24 (state Deleting) should be deleted after previous attempt before fetch
Заметил расхождения в данных, полностью удалил партицию и перелил с зеркальной ноды через scp, теперь весь лог в таких ошибках. Можно как-то вылечить?
[Alexey Sheglov]:
-
detach partition- на обеих репликах партиция перенесется в папку detached -
problem_node:
$ rm -rf /path/to/clickhouse_data/detached/* -
attach partition, и проблемная нода скачает партицию со здоровой
[Vadim Metikov]
Пришлось вывести сервер из кластера КХ . При заведении создаю реплицируемые таблицы и говрит, что реплика уже есть: Code: 253. DB::Exception: Received from localhost:9000, 127.0.0.1. DB::Exception: Replica /clickhouse/tables/01/graphite_tree/replicas/r1 already exists.. добавлю как 4я реплика, как удалить старую r1 ?
[Max Pavlov]: Нужно удалить данные о реплике в зукипере. Это делается автоматом если на старой реплике запустить drop database или drop table, там где есть реплицируемые таблицы Иначе нужно добавлять новую реплику с другим номером
после этого зачистить zookeepr:
[zk: localhost:2181(CONNECTED) 9] delete /clickhouse/tables/01/graphite/replicas/r1/
(либо через gui типа zooinspector)
один из способов без использование distributed tables:
Добавляем сервера и всё. Данные попадают в кафку, от туда мы пишем в шарды КХ, distributed таблицы на запись не используем. Если нужно больше места или уперлись в то что памяти на объем данных сервера не хватает добавляем шард. Соответственно после добавления шарда данные начинают размазываться немного по другому, а т.к. старые данные переодически удаляются то и по размеру шарды постепенно выравниваются [Kirill Shvakov]
[Jen Zolotarev]: Хочу протестировать производительность Clickhouse с кастомным ключом партиционирования в сравнении с дефолтным (два одинаковых селекта в разные таблицы, но с одинаковыми данными), сейчас застрял на том, что БД кеширует данные для запроса, и поэтому результаты скорости выполнения селекта получаются смазанные. Как почистить этот кеш? БД перезагружал, другие запросы выполнял, но эффекта не дало.
[Kirill Shvakov]: Там кэш ОС, у КХ нет кэша, сделайте так:
SET min_bytes_to_use_direct_io=1, merge_tree_uniform_read_distribution=0
[Ilyas Guseynov]: Можно ли как-то выставить distributed_group_by_no_merge в единичку для read-only юзеров? Есть запрос с группировкой и шардинг гарантирующий размещение этих групп только на конкретных нодах. Кажется логичным применять эту настройку, но для read-only оно ругается "Cannot execute SET query in readonly mode"
[Kirill Shvakov]: readonly в 2 выставить надо (или просто в профиле пользователя указать) - тогда можно делать SET и создавать временные таблицы