Масштабирование clickhouse, управление миграциями и отправка запросов из php в кластер

Formats

There are several formats that Clickhouse support. This is used for retrieving and inserting data.
Default format is set to JSON.
When you perform simple select / insert queries data is encoded into JSON and transferred between client and Clickhouse.
This does not work for Stream / Closure queries/writes.
When performing any query/write you can change format of communication by passing a class name as the last parameter.

use ClickhouseClient\Client\Format;

# select using default JSON format
$client->query('SELECT * FROM system.numbers LIMIT 5');
# select using TabSeparated format
$client->query('SELECT * FROM system.numbers LIMIT 5', Format\TabSeparatedFormat::class);

# insert usin JSON format
$client->writeRows('INSERT INTO myTable',
    ,
        ,
        
    ]
);
# insert usin TabSeparated format
$client->writeRows('INSERT INTO myTable',
    ,
        ,
        
    ], 
    Format\TabSeparatedFormat::class
);

# create client with differrent default format
$client = new Client($config, Format\TabSeparatedFormat::class);
# create client without default format (which would result in errors in some cases)
$client = new Client($config, null);

7: Настройка брандмауэра (опционально)

Если вы намерены использовать ClickHouse только локально с приложениями, работающими в рамках одного сервера, или если на вашем сервере не включен брандмауэр, вам не нужен этот раздел. Однако если вы будете подключаться к серверу базы данных ClickHouse удаленно, лучше выполнить его.

В настоящее время на вашем сервере включен брандмауэр, который блокирует доступ к вашему внешнему IP-адресу по всем портам. Выполните следующие два шага, чтобы разрешить удаленный доступ:

• Измените конфигурацию ClickHouse и разрешите ему прослушивать все интерфейсы.
• Добавьте правило брандмауэра, разрешающее входящие подключения к порту 8123 (это HTTP-порт, на котором работает сервер ClickHouse).

Если вы находитесь в командной строке базы данных, выйдите из нее, набрав CTRL+D.

Откройте в редакторе файл конфигурации:

Раскомментируйте строку <!— <listen_host>0.0.0.0</listen_host> —>:

Сохраните и закройте файл.

Чтобы обновить конфигурацию, перезапустите сервис:

Вы не увидите вывод этой команды. Сервер ClickHouse прослушивает порт 8123 для HTTP-соединений и порт 9000 для соединений от клиента clickhouse-client. Разрешите доступ к обоим портам IP-адресу вашего второго сервера с помощью следующей команды:

Вы увидите для обеих команд следующий вывод, который показывает, что доступ к обоим портам включен:

ClickHouse теперь будет доступен для сервера, IP-адрес которого вы добавили. При необходимости можно добавить дополнительные IP-адреса, например, адрес вашего локального компьютера.

Чтобы убедиться, что вы можете подключиться к серверу ClickHouse с удаленного компьютера, сначала выполните действия, описанные в разделе 1 этого руководства, на втором сервере и убедитесь, что на нем установлен клиент clickhouse-client.

Войдите на второй сервер, запустите сеанс клиента:

Следующий вывод сообщает, что вы успешно подключились к серверу:

Вы включили удаленный доступ к серверу базы данных ClickHouse, изменив правила брандмауэра.

Наш графический клиент для ClickHouse: возможности и особенности

• Просмотр списка баз данных и таблиц
• Просмотр содержимого таблицы
• Подсветка функций ClickHouse, названий таблиц и полей
• Автодополнение для названий таблиц, колонок и встроенных функций
• Выполнение выделенного / текущего / нескольких запросов в редакторе
• Автоматическое определение типа запроса: CREATE TABLE / INSERT / SELECT
• Удобная вставка значений словарей
• Темы оформления для редактора запросов, темы оформления для всего редактора (светлая и темная)
• Горячие клавиши

Клиент написан полностью на JavaScript, без использования server side.

Вы можете спокойно использовать наш последний опубликованный билд.

Cluster

$testCluster = new Tinderbox\Clickhouse\Cluster('cluster-name', ,
    'server-2' => new Tinderbox\Clickhouse\Server('127.0.0.1', '8124', 'default', 'user', 'pass')
]);

$anotherCluster = new Tinderbox\Clickhouse\Cluster('cluster-name', ,
    new Tinderbox\Clickhouse\Server('127.0.0.1', '8126', 'default', 'user', 'pass')
]);

$serverProvider = (new Tinderbox\Clickhouse\ServerProvider())->addCluster($testCluster)->addCluster($anotherCluster);

$client = (new Tinderbox\Clickhouse\Client($serverProvider));

Before execute any query on cluster, you should provide cluster name and client will run all queries on specified cluster.

By default client will use random server in given list of servers or in specified cluster. If you want to perform request on specified server you should use
method on client and then run query. Client will remember hostname for next queries:

$client->using('server-2')->select('select * from table');

Интерфейсы ClickHouse

По умолчанию у ClickHouse есть несколько интерфейсов, через которые можно получать доступ к данным.

ClickHouse предоставляет два сетевых интерфейса (оба могут быть дополнительно обернуты в TLS для дополнительной безопасности):

• HTTP, который задокументирован и прост для использования напрямую;
• Native TCP, который имеет меньше накладных расходов.

В большинстве случаев рекомендуется использовать подходящий инструмент или библиотеку, а не напрямую взаимодействовать с ClickHouse по сути. Официально поддерживаемые Яндексом:

• Консольный клиент;
• JDBC-драйвер;
• ODBC-драйвер;
• C++ клиентская библиотека.

Существует также широкий спектр сторонних библиотек для работы с ClickHouse:

• Клиентские библиотеки;
• Библиотеки для интеграции;
• Визуальные интерфейсы.

На первом этапе наиболее часто вам скорее всего придется работать с HTTP-интерфейс

HTTP-интерфейс

HTTP интерфейс позволяет использовать ClickHouse на любой платформе, из любого языка программирования. У нас он используется для работы из Java и Perl, а также из shell-скриптов. В других отделах, HTTP интерфейс используется из Perl, Python и Go. HTTP интерфейс более ограничен по сравнению с родным интерфейсом, но является более совместимым.

По умолчанию, clickhouse-server слушает HTTP на порту 8123 (это можно изменить в конфиге). Если запросить GET / без параметров, то вернётся строка заданная с помощью настройки . Значение по умолчанию «Ok.» (с переводом строки на конце).

$ curl 'http://localhost:8123/'
Ok.

В скриптах проверки доступности вы можете использовать GET /ping без параметров. Если сервер доступен всегда возвращается «Ok.» (с переводом строки на конце).

$ curl 'http://localhost:8123/ping'
Ok.

Запрос отправляется в виде URL параметра с именем query. Или как тело запроса при использовании метода POST. Или начало запроса в URL параметре query, а продолжение POST-ом (зачем это нужно, будет объяснено ниже). Размер URL ограничен 16KB, это следует учитывать при отправке больших запросов.

В случае успеха, вам вернётся код ответа 200 и результат обработки запроса в теле ответа. В случае ошибки, вам вернётся код ответа 500 и текст с описанием ошибки в теле ответа.

При использовании метода GET, выставляется настройка readonly. То есть, для запросов, модифицирующие данные, можно использовать только метод POST. Сам запрос при этом можно отправлять как в теле POST-а, так и в параметре URL.

По умолчанию, данные возвращаются в формате TabSeparated (подробнее смотри раздел «Форматы»). Можно попросить любой другой формат — с помощью секции FORMAT запроса.

http://localhost:8123/?query=SELECT 1 FORMAT Pretty

Более подробнее можно почитать на страницу документации

Important usage notes

No multiple queries

ClickHouse engine does not support parsing multiple queries per on roundtrip. Please split your queries into separately executed commands.

Always use NextResult

Although you may think that would not be used due to aforementioned lack of multiple query support that’s completely wrong! You must always use
as ClickHouse protocol and engine may and will return multiple resultsets per query and sometime result schemas may differ (definetly in regard to field
ordering if query doesn’t explicitly specify it).

Hidden bulk-insert functionality

If you read ClickHouse documentation it stongly advices you to insert records in bulk (1000+ per request). This driver can do bulk inserts. To do so you have to use special
insert syntax:

INSERT INTO some_table (col1, col2, col3) VALUES @bulk

And after that you must add parameted named with its castable to each item of it must be too. Empty lists are not allowed.
Alternatively you may pass implementation as a ‘s value to speed up processing and use less memory inside clickhouse driver.
This may be used conviniently with the following syntax:

CREATE TABLE test (date Date, time DateTime, str String, int UInt16) ENGINE=MergeTree(date,(time,str,int), 8192)

class MyPersistableObject:IEnumerable{
	public string MyStringField;
	public DateTime MyDateField;
	public int MyIntField;

	//Count and order of returns must match column order in SQL INSERT
	public IEnumerator GetEnumerator(){
		yield return MyDateField;
		yield return MyDateField;
		yield return MyStringField;
		yield return (ushort)MyIntField;
	}
}

//... somewhere elsewhere ...
var list=new List<MyPersistableObject>();

// fill the list to insert
list.Add(new MyPersistableObject());

var command=connection.CreateCommand();
command.CommandText="INSERT INTO test (date,time,str,int) VALUES @bulk";
command.Parameters.Add(new ClickHouseParameter{
	ParameterName="bulk",
	Value=list
});
command.ExecuteNonQuery();

INSERT

Добавление данных.

Базовый формат запроса:

В запросе можно указать список столбцов для вставки . В этом случае, в остальные столбцы записываются:

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

Если strict_insert_defaults=1, то столбцы, для которых не определены , необходимо перечислить в запросе.

В INSERT можно передавать данные любого , который поддерживает ClickHouse. Для этого формат необходимо указать в запросе в явном виде:

Например, следующий формат запроса идентичен базовому варианту INSERT … VALUES:

ClickHouse отсекает все пробелы и один перенос строки (если он есть) перед данными

Рекомендуем при формировании запроса переносить данные на новую строку после операторов запроса (это важно, если данные начинаются с пробелов)

Пример:

С помощью консольного клиента или HTTP интерфейса можно вставлять данные отдельно от запроса. Как это сделать, читайте в разделе «».

Ограничения (constraints)

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

Вставка результатов

Соответствие столбцов определяется их позицией в секции SELECT. При этом, их имена в выражении SELECT и в таблице для INSERT, могут отличаться. При необходимости выполняется приведение типов данных, эквивалентное соответствующему оператору CAST.

Все форматы данных кроме Values не позволяют использовать в качестве значений выражения, такие как , и подобные. Формат Values позволяет ограниченно использовать выражения, но это не рекомендуется, так как в этом случае для их выполнения используется неэффективный вариант кода.

Не поддерживаются другие запросы на модификацию части данных: , , , , , . Вы можете удалять старые данные с помощью запроса .

Для табличной функции input() после секции должна следовать секция .

Замечания о производительности

сортирует входящие данные по первичному ключу и разбивает их на партиции по ключу партиционирования. Если вы вставляете данные в несколько партиций одновременно, то это может значительно снизить производительность запроса . Чтобы избежать этого:

• Добавляйте данные достаточно большими пачками. Например, по 100 000 строк.
• Группируйте данные по ключу партиционирования самостоятельно перед загрузкой в ClickHouse.

Снижения производительности не будет, если:

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

Использование

Для использования пользователь должен иметь привилегию . Пользователь может выдавать привилегии только внутри области действий назначенных ему самому привилегий.

Например, администратор выдал привилегию пользователю :

Это означает, что пользователю разрешено выполнять:

• .
• .
• .

не может выполнить или . После обработки данных запросов ClickHouse ничего не вернет — даже или . Единственное исключение — если таблица содержит только столбцы и . В таком случае ClickHouse вернет все данные.

Также у есть привилегия . может выдать другим пользователям привилегии той же или меньшей области действий из тех, которые есть у него.

При присвоении привилегий допускается использовать астериск () вместо имени таблицы или базы данных. Например, запрос позволит пользователю выполнять над всеми таблицам в базе данных . Также вы можете опускать имя базы данных. В таком случае привилегии позволят совершать операции над текущей базой данных. Например, запрос выдаст привилегию на выполнение над всеми таблицами в текущей базе данных; — только над таблицей в текущей базе данных.

Доступ к базе данных разрешен всегда (данная база данных используется при обработке запросов).

Вы можете присвоить несколько привилегий нескольким пользователям в одном запросе. Запрос позволит пользователям и выполнять и над всеми таблицами всех баз данных на сервере.

Подключение к серверу ClickHouse, создание БД и таблицы

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

Сначала рассмотрим создание базы данных и таблицы с помощью нашего графического клиента. Подключаемся через графический клиент к серверу ClickHouse и выполняем запрос на создание новой базы данных и новой таблицы:

Поясним некоторые параметры этого запроса:

• — это движок таблицы. Также существуют , , , и другие.
• Первый параметр указывает на имя столбца типа Date, содержащего дату.
• — кортеж, определяющий первичный ключ таблицы (индекс).

В большинстве запросов на чтение планируется указывать, по какому сайту нам нужны данные, поэтому первым в индексе используется .

Теперь попробуем создать подключение к серверу ClickHouse, базу данных и таблицу через наш драйвер PHP. Для этого сначала установим драйвер.

Установку стабильной сборки драйвера можно выполнить через :

либо клонировать драйвер из основной (master) ветки Git-репозитория:

Более подробная информация по установке драйвера доступна в , которая также содержит описание функций драйвера и ChangeLog.

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

Обращаем внимание, что запросы в драйвере разделены на следующие:

• запись
• вставку данных
• чтение

Операции вставки и чтения данных могут выполняться параллельно.

Запросы на запись и вставку данных не содержат ответа, выполняется только проверка, что ответ сервера был положительным. Запросы на чтение ответ содержат (исключением является прямая запись ответа в файл).

Поддержка collation

Для сортировки по значениям типа String есть возможность указать collation (сравнение). Пример: — для сортировки по поисковой фразе, по возрастанию, с учётом турецкого алфавита, регистронезависимо, при допущении, что строки в кодировке UTF-8. может быть указан или не указан для каждого выражения в ORDER BY независимо. Если есть или , то указывается после них. При использовании сортировка всегда регистронезависима.

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

Использование внешних данных для обработки запроса

Допустим, что нам нужно посчитать, сколько уникальных пользователей просмотрело за сутки статьи X, где в X перечислено несколько идентификаторов статей. Это можно сделать так:

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

Рассмотрим эту возможность ClickHouse на примере. Создадим CSV-файл , в котором перечислим все нужные для запроса , и попросим ClickHouse создать временную таблицу , содержащую одну колонку:

Тогда содержимое CSV-файла можно использовать на сервере:

См. этот пример на GitHub.

Также функцией поддерживаются файлы в форматах TabSeparated и TabSeparatedWithNames.

Что дальше

На этом мы, пожалуй, завершим первую часть нашего рассказа о ClickHouse.

Много полезной информации о ClickHouse вы можете узнать в .

Мы планируем сделать цикл материалов, посвященных нашему опыту работы с ClickHouse.
В планах — следующие темы.

Часть 2:

• Подключение к кластеру ClickHouse из PHP
• Отправка запросов в кластер, реализация миграций на PHP

Часть 3:

• Использование словарей из MySQL в ClickHouse
• Движки таблиц: CollapsingMergeTree, SummingMergeTree, MaterializedView

Часть 4:

• Примеры запросов в ClickHouse на открытых данных СМИ2
• Семплирование данных в ClickHouse

1: Установка ClickHouse

Подключитесь к своему серверу по SSH:

Yandex поддерживает репозиторий APT с последней версией ClickHouse. Добавьте GPG-ключ репозитория, чтобы получить возможность загрузить надежные, валидные пакеты ClickHouse.

Команда вернет:

Этот вывод подтверждает, что пакеты загружены успешно.

Добавьте репозиторий в список APT:

Эта команда также передает вывод echo команде sudo tee, чтобы этот вывод был помещен в файл, принадлежащий пользователю root.

Теперь запустите apt-get update для обновления индекса пакетов:

Теперь пакеты clickhouse-server и clickhouse-client доступны для установки.

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

Поиск по подстрокам

Скорее всего, вы знаете много разных алгоритмов поиска подстроки в строке. Мы расскажем о тех, что используются в ClickHouse. Сначала введём пару определений:

1. haystack — строка, в которой мы ищем; типично длина обозначается n.
2. needle — строка или регулярное выражение, по которому мы ищем; длина будет обозначаться m.

После изучения большого количества алгоритмов могу сказать, что есть 2 (максимум 3) вида алгоритмов поиска подстрок. Первый — создание в том или ином виде суффиксных структур. Второй вид — алгоритмы, основанные на сравнении памяти. Ещё есть алгоритм Рабина — Карпа, который использует хэши, но он достаточно уникален в своём роде. Самого быстрого алгоритма не существует, всё зависит от размера алфавита, длины needle, haystack и частоты вхождения.

Почитать про разные алгоритмы можно здесь. А вот наиболее популярные алгоритмы:

1. Кнута — Морриса — Пратта,
2. Бойера — Мура,
3. Бойера — Мура — Хорспула,
4. Рабина — Карпа,
5. Двусторонний (используется в glibc под названием «memmem»),
6. BNDM.

Список можно продолжать. Мы в ClickHouse честно всё попробовали, но в итоге остановились на более экстраординарном варианте.

Алгоритм Волницкого

Алгоритм был опубликован в блоге программиста Леонида Волницкого в конце 2010 года. Он чем-то напоминает алгоритм Бойера — Мура — Хорспула, только улучшенную версию.

Если m < 4, то применяется стандартный алгоритм поиска. Сохраним все биграммы (2 идущих подряд байта) needle с конца в хэш-таблицу с открытой адресацией размера |Sigma|2 элементов (на практике это 216 элементов), где оффсеты данной биграммы будут значениями, а сама биграмма — хэшом и индексом одновременно. Изначальная позиция будет на позиции m — 2 от начала haystack. Походим по haystack с шагом m — 1, посмотрим на очередную биграмму с этой позиции в haystack и рассмотрим все значения по биграмме в хэш-таблице. Затем будем сравнивать два куска памяти обычным алгоритмом сравнения. Хвост, который останется, обработаем этим же алгоритмом.

Шаг m — 1 выбран таким образом, что если есть вхождение needle в haystack, то мы обязательно рассматриваем биграмму этого вхождения — тем самым гарантируя, что вернём позицию вхождения в haystack. Первое вхождение гарантируется тем, что в хэш-таблицу по биграмме мы добавим индексы с конца. Это значит, что когда пойдём слева направо, то сначала будем рассматривать биграммы с конца строки (возможно, изначально рассматривая совершенно ненужные биграммы), потом — ближе к началу.

Рассмотрим пример. Пусть строка haystack будет и needle равна . Хэш-таблица будет .

Видим биграмму . В needle она есть, подставляем в равенство:

Не совпало. После в хэш-таблице нет никаких записей, шагаем с шагом 3:

Биграммы в хэш-таблице нет, идём дальше:

Биграмма в needle есть, смотрим на оффсет и находим вхождение:

У алгоритма много плюсов. Во-первых, можно не выделять память на куче, а 64 КБ на стеке не являются сейчас чем-то заоблачным. Во-вторых, 216 — отличное число для взятия по модулю для процессора; это просто инструкции movzwl (или как мы шутим, «мовзвл») и семейство.

В среднем этот алгоритм проявил себя лучше всех. Данные мы взяли из Яндекс.Метрики, запросы почти реальные. Скорость на один поток, больше лучше, KMP: алгоритм Кнута — Морриса — Пратта, BM: Бойера — Мура, BMH: Бойера — Мура — Хорспула.

Чтобы не быть голословным, алгоритм может работать квадратичное время:

Он используется в функции , а также выступает оптимизацией для поиска по регулярным выражениям.

Прочти это перед использованием

Нет поддержки нескольких запросов

Движок ClickHouse не умеет обрабатывать несколько SQL запросов за один вызов . Запросы надо разбивать на отдельные команды.

Всегда используй NextResult

В связи с вышесказаным может показаться что не нужен, но это совершенно не так. Использование обязательно, поскольку протокол и движок ClickHouse может и будет возвращать несколько наборов данных на один запрос, и, хуже того, схемы этих наборов могут различаться (по крайней мере может быть перепутан порядок полей, если запрос не имеет явного указания порядка).

Секретная функция групповой вставки

В документации ClickHouse указано, что вставлять данные лучше пачками 100+ записей. Для этого предусмотрен специальный синтаксис:

INSERT INTO some_table (col1, col2, col3) VALUES @bulk

Для этой команды надо задать параметр со значением приводимым к , каждый из элементов которого, в свою очередь, тоже должен быть .
Кроме того, в качестве значения параметра передать объект реализующий — это уменьшит использование памяти и процессора внутри драйвера clickhouse.
Это удобно при использовании такого синтаксиса:

CREATE TABLE test (date Date, time DateTime, str String, int UInt16) ENGINE=MergeTree(date,(time,str,int), 8192)

class MyPersistableObject:IEnumerable{
	public string MyStringField;
	public DateTime MyDateField;
	public int MyIntField;

	//Количество и порядок return должны соответствовать количеству и порядку полей в SQL INSERT
	public IEnumerator GetEnumerator(){
		yield return MyDateField;
		yield return MyDateField;
		yield return MyStringField;
		yield return (ushort)MyIntField;
	}
}

//... где-то ещё ...
var list=new List<MyPersistableObject>();

// заполнение списка вставляемых объектов
list.Add(new MyPersistableObject());

var command=connection.CreateCommand();
command.CommandText="INSERT INTO test (date,time,str,int) VALUES @bulk";
command.Parameters.Add(new ClickHouseParameter{
	ParameterName="bulk",
	Value=list
});
command.ExecuteNonQuery();

Configurations

use ClickhouseClient\Client\Config;

$config = new Config(
    // basic connection information
    ,
    // settings
    ,
    // credentials
    
);

You can pass additional settings in 2nd parameter, along with database name.

You do not need do define all of this in case you are using default configurations.

For example if you need to create a client object with readonly access, pass «readonly» parameter.

$config = new Config(
    // basic connection information - set to default
    [],
    // settings
    
    // credentials - set to defauult
    []
);

For example if we would like to set a 5 second connection timeout, we would create a following config:

use ClickhouseClient\Client\Config;

$config = new Config(
    // basic connection information - set to default
    [],
    // settings
    
    // credentials - set to defauult
    [],
    // additional CURL options
    
);

You can define credentials or settings after creating the config object

use ClickhouseClient\Client\Config;

$config = new Config(
    // basic connection information
    
);
$config->setUser('user');
$config->setPassword('password');
$config->change('database', 'new-db');

Functionality for changing basic connection information and curl settings is not implemented, because changing those settings should both conceptually and technically be considered a new client.