# data[].attributes.attachment_link.readable_text
The module supports creating lightweight "virtual environments", each with their own independent set of Python packages installed in their directories. A virtual environment is created on top of an existing Python installation, known as the virtual environment's "base" Python, and may optionally be isolated from the packages in the base environment, so only those explicitly installed in the virtual environment are available.

When used from within a virtual environment, common installation tools such as pip will install Python packages into a virtual environment without needing to be told to do so explicitly.

A virtual environment is (amongst other things):

Used to contain a specific Python interpreter and software libraries and binaries which are needed to support a project (library or application). These are by default isolated from software in other virtual environments and Python interpreters and libraries installed in the operating system.

Contained in a directory, conventionally either named or in the project directory, or under a container directory for lots of virtual environments, such as ~/.virtualenvs .

Not checked into source control systems such as Git.

Considered as disposable - it should be simple to delete and recreate it from scratch. You don't place any project code in the environment

Not considered as movable or copyable - you just recreate the same environment in the target location.

See PEP 405 for more background on Python virtual environments.

Availability : not Emscripten, not WASI.

This module does not work or is not available on WebAssembly platforms wasm32-emscripten and wasm32-wasi . See WebAssembly platforms for more information.

Creating virtual environments

Creation of virtual environments is done by executing the command :

Running this command creates the target directory (creating any parent directories that don't exist already) and places a file in it with a key pointing to the Python installation from which the command was run (a common name for the target directory is ). It also creates a (or on Windows) subdirectory containing a copy/symlink of the Python binary/binaries (as appropriate for the platform or arguments used at environment creation time). It also creates an (initially empty) lib/pythonX.Y/site-packages subdirectory (on Windows, this is Lib\site-packages ). If an existing directory is specified, it will be re-used.

On Windows, invoke the command as follows:

Alternatively, if you configured the and variables for your Python installation :

The command, if run with , will show the available options:

The created file also includes the include-system-site-packages key, set to if is run with the--system-site-packages option, otherwise.

Unless the --without-pip option is given, will be invoked to bootstrap into the virtual environment.

Multiple paths can be given to , in which case an identical virtual environment will be created, according to the given options, at each provided path.

How venvs work

When a Python interpreter is running from a virtual environment, and point to the directories of the virtual environment, whereas and point to those of the base Python used to create the environment. It is sufficient to check to determine if the current interpreter is running from a virtual environment.

A virtual environment may be "activated" using a script in its binary directory ( on POSIX; on Windows). This will prepend that directory to your , so that running python will invoke the environment's Python interpreter and you can run installed scripts without having to use their full path. The invocation of the activation script is platform-specific ( must be replaced by the path to the directory containing the virtual environment):

You don't specifically need to activate a virtual environment, as you can just specify the full path to that environment's Python interpreter when invoking Python. Furthermore, all scripts installed in the environment should be runnable without activating it.

In order to achieve this, scripts installed into virtual environments have a "shebang" line which points to the environment's Python interpreter, i.e. . This means that the script will run with that interpreter regardless of the value of . On Windows, "shebang" line processing is supported if you have the Python Launcher for Windows installed. Thus, double-clicking an installed script in a Windows Explorer window should run it with the correct interpreter without the environment needing to be activated or on the .

When a virtual environment has been activated, the VIRTUAL_ENV environment variable is set to the path of the environment. Since explicitly activating a virtual environment is not required to use it, VIRTUAL_ENV cannot be relied upon to determine whether a virtual environment is being used.

You can deactivate a virtual environment by typing deactivate in your shell. The exact mechanism is platform-specific and is an internal implementation detail (typically, a script or shell function will be used).

API

The high-level method described above makes use of a simple API which provides mechanisms for third-party virtual environment creators to customize environment creation according to their needs, the class.

The class accepts the following keyword arguments on instantiation:

system_site_packages - a Boolean value indicating that the system Python site-packages should be available to the environment (defaults to ).

- a Boolean value which, if true, will delete the contents of any existing target directory, before creating the environment.

- a Boolean value indicating whether to attempt to symlink the Python binary rather than copying.

- a Boolean value which, if true, will upgrade an existing environment with the running Python - for use when that Python has been upgraded in-place (defaults to ).

- a Boolean value which, if true, ensures pip is installed in the virtual environment. This uses with the --default-pip option.

- a String to be used after virtual environment is activated (defaults to which means directory name of the environment would be used). If the special string is provided, the basename of the current directory is used as the prompt.

upgrade_deps - Update the base venv modules to the latest on PyPI

Creators of third-party virtual environment tools will be free to use the provided class as a base class.

The returned env-builder is an object which has a method, :

Creates the environment directory and all necessary subdirectories that don't already exist, and returns a context object. This context object is just a holder for attributes (such as paths) for use by the other methods. If the is created with the arg , contents of the environment directory will be cleared and then all necessary subdirectories will be recreated.

The returned context object is a types.SimpleNamespace with the following attributes:

Changed in version 3.12: The attribute was added to the context, and the context object was documented.

Creates the configuration file in the environment.

Creates a copy or symlink to the Python executable in the environment. On POSIX systems, if a specific executable was used, symlinks to and will be created pointing to that executable, unless files with those names already exist.

Installs activation scripts appropriate to the platform into the virtual environment.

Upgrades the core venv dependency packages (currently and setuptools ) in the environment. This is done by shelling out to the executable in the environment.

A placeholder method which can be overridden in third party implementations to pre-install packages in the virtual environment or perform other post-creation steps.

Changed in version 3.7.2: Windows now uses redirector scripts for python[w].exe instead of copying the actual binaries. In 3.7.2 only does nothing unless running from a build in the source tree.

Changed in version 3.7.3: Windows copies the redirector scripts as part of instead of . This was not the case in 3.7.2. When using symlinks, the original executables will be linked.

In addition, provides this utility method that can be called from or in subclasses to assist in installing custom scripts into the virtual environment.

path is the path to a directory that should contain subdirectories "common", "posix", "nt", each containing scripts destined for the bin directory in the environment. The contents of "common" and the directory corresponding to are copied after some text replacement of placeholders:

The directories are allowed to exist (for when an existing environment is being upgraded).

There is also a module-level convenience function:

An example of extending EnvBuilder

The following script shows how to extend by implementing a subclass which installs setuptools and pip into a created virtual environment:

This script is also available for download online.

# data[].attributes.attachment_link.description
Source code: Lib/venv/ The venv module supports creating lightweight "virtual environments", each with their own independent set of Python packages installed in their site directories. A virtual en...

# data[].attributes.attachment_link.readable_text
Provides a table-like interface to select/insert files in Amazon S3 and Google Cloud Storage. This table function is similar to the hdfs function, but provides S3-specific features.
Syntax Arguments

path - Bucket url with path to file. Supports following wildcards in readonly mode: *, **, ?, {abc,def} and {N..M} where N, M - numbers, 'abc', 'def' - strings. For more information see here.

The GCS path is in this format as the endpoint for the Google XML API is different than the JSON API:

and not https://storage.cloud.google.com.

NOSIGN - If this keyword is provided in place of credentials, all the requests will not be signed.

structure - Structure of the table. Format 'column1_name column1_type, column2_name column2_type, ...'.

compression - Parameter is optional. Supported values: none, gzip/gz, brotli/br, xz/LZMA, zstd/zst. By default, it will autodetect compression by file extension.

Returned value
A table with the specified structure for reading or writing data in the specified file.
Examples
Selecting the first 5 rows from the table from S3 file https://datasets-documentation.s3.eu-west-3.amazonaws.com/aapl_stock.csv:

┌───────Date─┬────Open─┬────High─┬─────Low─┬───Close─┬───Volume─┬─OpenInt─┐ │ 1984-09-07 │ 0.42388 │ 0.42902 │ 0.41874 │ 0.42388 │ 23220030 │ 0 │ │ 1984-09-10 │ 0.42388 │ 0.42516 │ 0.41366 │ 0.42134 │ 18022532 │ 0 │ │ 1984-09-11 │ 0.42516 │ 0.43668 │ 0.42516 │ 0.42902 │ 42498199 │ 0 │ │ 1984-09-12 │ 0.42902 │ 0.43157 │ 0.41618 │ 0.41618 │ 37125801 │ 0 │ │ 1984-09-13 │ 0.43927 │ 0.44052 │ 0.43927 │ 0.43927 │ 57822062 │ 0 │ └────────────┴─────────┴─────────┴─────────┴─────────┴──────────┴─────────┘

Suppose that we have several files with following URIs on S3:

Count the amount of rows in files ending with numbers from 1 to 3:

Count the total amount of rows in all files in these two directories:

Count the total amount of rows in files named file-000.csv, file-001.csv, ... , file-999.csv:

Insert data into file test-data.csv.gz:

Insert data into file test-data.csv.gz from existing table:

Glob ** can be used for recursive directory traversal. Consider the below example, it will fetch all files from my-test-bucket-768 directory recursively:

The below get data from all test-data.csv.gz files from any folder inside my-test-bucket directory recursively:

If you specify PARTITION BY expression when inserting data into S3 table, a separate file is created for each partition value. Splitting the data into separate files helps to improve reading operations efficiency.
Examples
Using partition ID in a key creates separate files:

As a result, the data is written into three files: file_x.csv, file_y.csv, and file_z.csv.

Using partition ID in a bucket name creates files in different buckets:

As a result, the data is written into three files in different buckets: my_bucket_1/file.csv, my_bucket_10/file.csv, and my_bucket_20/file.csv.

ClickHouse tries to fetch credentials from many different types of sources. Sometimes, it can produce problems when accessing some buckets that are public causing the client to return 403 error code. This issue can be avoided by using NOSIGN keyword, forcing the client to ignore all the credentials, and not sign the requests.
See Also

# data[].attributes.attachment_link.description
Provides a table-like interface to select/insert files in Amazon S3 and Google Cloud Storage. This table function is similar to the hdfs function, but provides S3-specific features.

# data[].attributes.attachment_link.description
The Clickhouse plugin for dbt (data build tool). Contribute to ClickHouse/dbt-clickhouse development by creating an account on GitHub.

# data[].attributes.attachment_link.provider_name
Авторский сайт ИТ-консультанта

# data[].attributes.attachment_link.readable_text
ClickHouse - это мощная столбцовая база данных, написанная на C, которая генерирует аналитические и статистические отчеты в режиме реального времени с помощью операторов SQL!

Какие кейсы использования ClickHouse могут быть: сессионная и событийная аналитика, аналитика по бизнес-метрикам, генерация аналитических запросов по структурированным данным c минимальной задержкой, анализ операционных логов, анализ финансовых данных, Data Science, продуктовая аналитика и т.п.

Выполнение запросов в ClickHouse осуществляется значительно быстрее, чем в Big Data системах класса SQL-on-Hadoop (например, такие системы, как Apache Hive, Cloudera Impala, Presto и Spark) или при работе с данными в колоночных форматах, таких как Parquet или Kudu.

Файлы конфигурации сервера ClickHouse

Конфигурация сервера Clickhouse состоит из двух частей: настроек сервера ( config.xml) и настроек пользователей ( users.xml).

По умолчанию они хранятся в папке /etc/clickhouse-server/ в двух файлах config.xml и users.xml.

Рекомендуется никогда не изменять файлы vendor config и помещать ваши изменения в отдельные файлы .xml в подпапках. Этот способ проще в обслуживании и упрощает обновление Clickhouse.

/etc/clickhouse-server/users.d - подпапка для пользовательских настроек.

/etc/clickhouse-server/config.d - подпапка для настроек сервера.

/etc/clickhouse-server/conf.d - подпапка для любых (обоих) настроек.

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

Описание общих ключевых моментов по ClickHouse

Официальная документация - Configuration Files (En) & Конфигурационные файлы (Ru)
Конфигурационные файлы:

Основной файл конфигурации сервера - config.xml (в /etc/clickhouse-server/config.xml ) - все настройки сервера, такие как порт прослушивания, регистратор, удаленный доступ, настройка кластера (shards и replicas), системные настройки (часовой пояс, umask и др.), мониторинг, журналы запросов, словари, сжатие и так далее.
Настройки сервера: Server Configuration Parameters -> Server Settings (En)

Основной файл конфигурации пользователя - users.xml (в /etc/clickhouse-server/users.xml ), в котором указаны профили, пользователи, пароли, ACL, квоты и т.д. Он также поддерживает конфигурацию пользователя на основе SQL.
Доступные настройки и параметры пользователей: User Settings (En)

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

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

Каждый параметр конфигурации можно переопределить с помощью файлов конфигурации в config.d/ (о чем было указано выше).

users.xml - здесь описываются юзеры с паролями, также задается список сетей для пользователей, квоты, описываются профили пользователей. Можно установить на пользователя дефолтный фильтр на конкретную таблицу, задать readonly mode и т.д.

Порты ClickHouse по умолчанию:

8123 - порт HTTP - клиента (8443 - HTTPS). Клиент может подключаться к curl или wget или другим HTTP(S) - клиентам командной строки для управления и вставки данных в базы данных и таблицы.

9000 - это собственный клиентский порт TCP/IP (9440 - это порт с поддержкой TLS для этой службы) для управления и вставки данных в базы данных и таблицы.

9004 - это порт протокола MySQL. ClickHouse поддерживает проводной протокол MySQL.

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

Другие описания:

Существует каталог флагов , в котором файлы со специальными именами могут указывать ClickHouse для обработки команд. Например, создание пустого файла с именем: /var/lib/clickhouse/flags/force_restore_data даст указание ClickHouse начать процедуру восстановления сервера.

Хорошей практикой является создание резервной копии всего каталога конфигурации, несмотря на то, что основные файлы конфигурации не изменены и находятся в исходном состоянии.

Команды SQL, поддерживаемые сервером CickHouse : https://clickhouse.com/docs/en/sql-reference/ и https://clickhouse.com/docs/en/sql-reference/statements/

Базовым и фундаментальным типом таблицы является MergeTree , который предназначен для вставки очень большого количества данных в таблицу - https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree/

Имейте в виду, ClickHouse поддерживает синтаксис SQL и некоторые операторы SQL, но операторы UPDATE и DELETE не поддерживаются, только INSERT !
Основная идея ClickHouse - не изменять данные, а только добавлять!

Пакетные INSERT являются предпочтительным способом вставки данных! На самом деле в руководстве по ClickHouse есть рекомендация 1 INSERT в секунду.

Ряд описаний по параметрам запуска в Docker

Как правило, вы можете смонтировать следующие папки внутри вашего контейнера для сохранения постоянства:

/var/lib/clickhouse/ - основная папка, в которой ClickHouse хранит данные;

/var/log/clickhouse-server/ - logs;

/etc/clickhouse-server/config.d/*.xml - файлы с настройками конфигурации сервера;

/etc/clickhouse-server/users.d/*.xml - файлы с настройками использования;

/docker-entrypoint-initdb.d/ - папка со скриптами инициализации базы данных;

/var/lib/clickhouse/user_files/ - файлы со словарями (пользовательские csv files).

Основная терминология по ClickHouse

Партиционирование данных - разбивка данных 1 большой таблицы на множество частей по какому-то признаку (например по периоду - по месяцам).

Шардирование - это стратегия горизонтального масштабирования кластера, при которой части одной базы данных ClickHouse размещаются на разных шардах. Шард состоит из одного или нескольких хостов-реплик. Запрос на запись или чтение в шард может быть отправлен на любую его реплику, выделенного мастера нет. При вставке данных они будут скопированы с реплики, на которой был выполен INSERT-запрос, на другие реплики шарда в асинхронном режиме.

Репликация - синхронизация данных между хостами-репликами в рамках одной шарды (в рамках одного кластера).

ClickHouse-кластер (или шард) - это один или несколько хостов базы данных, между которыми настраивается репликация.

Apache Zookeeper is a centralized service for maintaining configuration information, naming, providing distributed synchronization, and providing group services.

Материальные вьюхи (Materialized View) - хранят данные, преобразованные соответствующим запросом SELECT. Материализованные представления в ClickHouse больше похожи на after insert триггеры. Если в запросе материализованного представления есть агрегирование, оно применяется только к вставляемому блоку записей. Любые изменения существующих данных исходной таблицы (например обновление, удаление, удаление раздела и т.д.) не изменяют материализованное представление.

Внешние словари - Существует возможность подключать собственные словари из различных источников данных. Источником данных для словаря может быть локальный текстовый/исполняемый файл, HTTP(s) ресурс или другая СУБД.

Как и где хранятся данные, куда их писать и откуда читать.

Какие запросы поддерживаются и каким образом.

Конкурентный доступ к данным.

Использование индексов, если есть.

Возможно ли многопоточное выполнение запроса.

Параметры репликации данных.

Clickhouse использует последовательные чтение и запись на диск.
Суть шардирования: взяли данные и разделили частями на разные шарды (на разные кластеры/серверы). Это нужно для хранения огромного числа данных. Заправшиваются данные с каждого шарда (запрос sql), затем результаты склеиваются и обрабатываются, после итоговый результат отдается юзеру.

Zookeeper - используется для хранения состояний репликации. В случае сбоя реплика пойдет в Zookeeper, получит инфу о данных, далее пойдет в соседнюю реплику и заберет нужный кусок данных. Потом, после обновления данных, реплика идет в Zookeeper и ставит пометку об обновлении.
Схема, поясняющая, что такое шард и реплика:
Репликация и шардирование настраивается на уровне таблиц. Т.е. можно сделать таблицы с разной конфигурацией (какие-то таблицы можно разнести на несколько шард, какие-то оставить на одной, аналогично настраивается реплицирование на уровне каждой отдельной таблицы).
Зачем нужна реплика:

Отказоустойчивость

Распараллеливание чтения

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

Установка Clickhouse из официального образа с hub.docker.com

Официальная инструкция как задеплоить образ находится здесь https://hub.docker.com/r/clickhouse/clickhouse-server/.

Запускаем команду:

Скачается официальный докер образ (но пока еще не запустится):

Далее необходимо запустить из образа контейнер:

Чтобы проверить, что у Вас запустился контейнер с Clickhouse - запустите команду:

Проверить работу Clickhouse-server можно перейдя по url :

Далее запускаем команду:

С помощью этой команды мы подключимся к Clickhouse через native client:

Клиент командной строки

ClickHouse предоставляет собственный клиент командной строки: clickhouse-client. Клиент поддерживает запуск с аргументами командной строки и с конфигурационными файлами.

Клиент устанавливается пакетом clickhouse-client и запускается командой clickhouse-client.

Установка на Ubuntu:
Запуск клиента для подключения к локальной базе ClickHouse (localhost, default port 9000):
Клиенты и серверы различных версий совместимы, однако если клиент старее сервера, то некоторые новые функции могут быть недоступны. Рекомендуется использовать одинаковые версии клиента и сервера.

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

В результате должны получить ответ:

Видео "Установка базы данных ClickHouse в виде контейнера Docker"

Установка ClickHouse с помощью docker-compose

Пример установки ClickHouse с помощью Docker Compose #1

Конфигурация ClickHouse в Docker Compose может выглядеть так:

В этом примере мы определяем контейнер ClickHouse, который будет доступен на портах 8123 и 9000 на хостовой машине. Мы также монтируем файл конфигурации config.xml, который может содержать пользовательские настройки и разрешения портов и IP.

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

Мы также определяем сеть clickhouse-network, чтобы установить соединение между контейнерами. Как видите, мы указываем подсеть 172.25.0.0/16, которую можно изменить в соответствии с вашими потребностями.

Пример установки ClickHouse с помощью Docker Compose #2

Во втором примере мы определяем контейнер ClickHouse, который будет доступен на портах 8123 и 9000 на хостовой машине. Также я монтирую каталоги config.d и users.d, которые содержат файлы конфигурации и настроек пользователей соответственно:

Пример файла config.d для ClickHouse, который можно использовать в качестве шаблона:

Этот файл содержит настройки для ClickHouse, такие как порты для HTTP- и Native-протоколов, максимальное время выполнения запроса, максимальное количество строк в результате запроса и т.д.

Обратите внимание на элементы <compression> и <dictionaries_config>, которые позволяют задать настройки сжатия данных и словарей соответственно. Также в этом примере используется элемент <users_config>, который указывает ClickHouse на путь к файлам с настройками пользователей.

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

Также вы можете создать файлы настроек пользователей в каталоге users.d. Например, вы можете создать файл myuser.xml с таким содержимым:

Теперь при запуске контейнера ClickHouse будут использоваться настройки из файлов в каталогах config.d и users.d

Установить dbeaver в Ubuntu можно через Ubuntu Software:

Выбираем коннектор к Clickhouse:

Настройки подключения с дефолтным юзером:

show databases - проверочный запрос к clickhouse:

ClickHouse имеет богатый набор функций для управления сетевыми подключениями для клиентов, а также для других серверов в кластере. Тем не менее, новым пользователям может быть сложно проработать возможные варианты, а опытным пользователям может быть сложно обеспечить полный доступ к развернутым системам для приложений и их надлежащую защиту.
Следующий кусок взят из официальной документации раздела "Интерфейсы":
ClickHouse предоставляет три сетевых интерфейса (они могут быть обернуты в TLS для дополнительной безопасности):

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

Native TCP, который имеет меньше накладных расходов;

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

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

Что такое http-интерфейс

HTTP интерфейс позволяет использовать ClickHouse на любой платформе, из любого языка программирования. HTTP интерфейс более ограничен по сравнению с родным интерфейсом, но является более совместимым. По умолчанию clickhouse-server слушает HTTP на порту 8123. Запрос отправляется в виде URL параметра с именем query. Или как тело запроса при использовании метода POST. Или начало запроса в URL параметре query, а продолжение POST-ом. Размер URL ограничен 16KB, это следует учитывать при отправке больших запросов.

Порт 8123 является конечной точкой интерфейса HTTP по умолчанию. Вы будете использовать этот порт, если используете команды curl для отправки запросов серверу. Кроме того, ряд библиотек, таких как JDBC-драйвер Yandex ClickHouse, скрытно используют HTTP-запросы, так что вы можете использовать http-интерфейс, даже не подозревая об этом.

Что такое Native TCP (Родной интерфейс)

Нативный протокол используется в клиенте командной строки, для взаимодействия между серверами во время обработки распределенных запросов, а также в других программах на C++. К сожалению, у родного протокола ClickHouse пока нет формальной спецификации.

Порт 9000 является конечной точкой Native TCP интерфейса (по-умолчанию). Он широко используется клиентами, как показано на следующих примерах.

Clickhouse-client, стандартный клиент командной строки для ClickHouse, использует собственный протокол TCP/IP.

Точно так же механизм ClickHouse Distributed использует собственный протокол TCP/IP для отправки подзапросов в базовые таблицы. В редких случаях он также используется реплицированными таблицами при отправке запросов ведущей реплике.

Наконец, драйверы, такие как драйвер Python clickhouse или драйвер Golang, общаются с ClickHouse, используя собственный протокол TCP/IP.

Что такое gRPC

ClickHouse поддерживает интерфейс gRPC. Это система удаленного вызова процедур с открытым исходным кодом, которая использует HTTP/2 и Protocol Buffers.

gRPC - мощный фреймворк для работы с удаленными вызовами процедур. RPC позволяют писать код так, как если бы он был запущен на локальном компьютере, даже если он может выполняться на другом компьютере.

Как правило, gRPC считается лучшей альтернативой протоколу REST для микросервисной архитектуры. Букву g в gRPC можно отнести к компании Google, которая изначально разработала эту технологию. gRPC создан для преодоления ограничений REST в связи с микросервисами.

gRPC - это новейшая структура, созданная на основе протокола RPC. Он использует свои преимущества и пытается исправить проблемы традиционного RPC. gRPC использует буферы протокола в качестве языка определения интерфейса для сериализации и связи вместо JSON/XML.

Буферы протокола могут описывать структуру данных, и на основе этого описания может быть сгенерирован код для генерации или анализа потока байтов, представляющего структурированные данные. По этой причине gRPC предпочтительнее для многоязычных веб-приложений (реализованных с использованием различных технологий). Формат двоичных данных позволяет облегчить общение. gRPC также можно использовать с другими форматами данных, но предпочтительным является буфер протокола.

Кроме того, gRPC построен на основе HTTP/2, который поддерживает двунаправленную связь наряду с традиционным запросом/ответом. gRPC допускает слабую связь между сервером и клиентом. На практике клиент открывает долговременное соединение с сервером gRPC, и новый поток HTTP/2 открывается для каждого вызова RPC.

В отличие от REST, который использует JSON (в основном), gRPC использует буферы протокола, которые являются лучшим способом кодирования данных. Поскольку JSON - это текстовый формат, он будет намного тяжелее, чем сжатые данные в формате protobuf.

Network Listener Configuration

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

https_port

Порт для зашифрованных запросов HTTPS

8443

interserver_http_port

Порт для незашифрованного трафика HTTP-репликации

9009

interserver_https_port

Порт для зашифрованного трафика репликации HTTPS

tcp_port

Порт для незашифрованных собственных запросов TCP/IP

9000

tcp_port_secure

Порт для зашифрованных TLS собственных запросов TCP/IP

9440

Идем в dbeaver и запускаем скрипты.
1. Создаем базу данных в Clickhouse 2. Проверяем, что база данных создалась: 3. Создаем таблицу в Clickhouse: 4. Вставляем первую строку данных: 5. Проверяем, что в Clickhouse данные вставились:
Далее попробуем вставить еще 1 строку с помощью разных вариантов.

Вариант 1 - clickhouse-driver (Python Driver with native interface)
Установка:
Краткое описание: clickhouse-driver работает через 9000 порт, поэтому он должен быть открыт и должен быть настроен listener. По умолчанию 9000 порт в образе докера открыт, но может не настроен listener. Делается это через config файл для clickhouse server - по-умолчанию лежит тут etc/clickhouse-server/config.xml.
Пример запроса Show Databases:
Пример Select запроса к ClickHouse

Вставка 1 команды Insert через Execute

Вставка dataframe pandas в ClickHouse

Подробности по настройкам можно почитать в документации в разделе NumPy/Pandas support >>>

Еще 1 пример вставки данных в ClickHouse с помощью библиотеки clickhouse-driver

Вариант 2 - sqlalchemy-clickhouse (ClickHouse dialect for SQLAlchemy)

https://github.com/cloudflare/sqlalchemy-clickhouse - этот коннектор рекомендуется для использования в superset. Строка подключения выглядит как:
Установка:
Пример полного скрипта Python для получения данных из ClickHouse:

Вставка данных в ClickHouse с помощью библиотеки sqlalchemy-clickhouse

Создадим таблицу, если ее еще не существует, используя функцию execute:

Пример полного скрипта Python для вставки данных в ClickHouse:

Вариант 3 - pandahouse (Pandas interface for Clickhouse database)

Github проект Pandahouse - https://github.com/kszucs/pandahouse
Установка:
Для того чтобы получить данные из ClickHouse с помощью библиотеки pandahouse в Python, необходимо выполнить следующие шаги:

1. Установить библиотеку pandahouse с помощью pip: pip install pandahouse

2. Импортировать библиотеку и создать соединение с базой данных ClickHouse:

Выполнить запрос SELECT с помощью функции read_clickhouse и сохранить результат в объект DataFrame:

Пример полного скрипта Python для получения данных из ClickHouse:

Пример вставки данных в ClickHouse с помощью pandahouse

Вариант 4 - aiochclient (http(s) ClickHouse client for python 3.6+)

https://github.com/maximdanilchenko/aiochclient - Lightweight async http(s) ClickHouse client for python 3.6+ with types converting

туду

Вариант 5 - asynch (native TCP interface)

https://github.com/long2ice/asynch - An asyncio ClickHouse Python Driver with native (TCP) interface support.

туду

Условие:

Как создать таблицу в Clickhouse и записать 1 миллион строк данных с помощью Python. Нужно использовать библиотеку Pandas. Данные необходимо отправлять пакетно по 50 тысяч строк.

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

Мы создаем таблицу my_table в ClickHouse и отправляем данные пакетно по 50 тысяч строк с помощью цикла и команды INSERT INTO.

Обратите внимание на использование метода iterrows() для преобразования DataFrame в список кортежей, который может быть отправлен в ClickHouse.

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

В этом примере мы загружаем данные из CSV-файла в Pandas, определяем столбцы и их типы в соответствии с DataFrame и создаем таблицу my_table в ClickHouse.

Затем мы отправляем данные пакетно по 100 тысяч строк с помощью цикла и команды INSERT INTO.

В этом примере мы создаем клиент для подключения к ClickHouse, выполняем запрос SELECT * FROM my_table и получаем результаты с помощью метода execute() клиента. Затем мы создаем DataFrame из результатов с помощью конструктора pd.DataFrame() и задаем имена столбцов DataFrame с помощью команды DESCRIBE my_table. После этого мы можем использовать полученный DataFrame для дальнейшей обработки данных.

Обратите внимание, что результаты запроса представляют собой список кортежей. Каждый кортеж представляет собой одну строку данных из таблицы ClickHouse. Мы преобразовали этот список в DataFrame с помощью конструктора pd.DataFrame(), передав ему список кортежей и список имен столбцов.

# data[].attributes.attachment_link.title
Как записать данные в Clickhouse с помощью Python

# data[].attributes.attachment_link.description
Как записать данные в Clickhouse с помощью Python. Установка Clickhouse с помощью docker и docker-compose. ClickHouse Python Driver

# data[].attributes.attachment_link.description
The HTTP interface lets you use ClickHouse on any platform from any programming language in a form of REST API. The HTTP interface is more limited than the native interface, but it has better language support.

# data[].attributes.body
https://sidmid.ru/%D1%83%D1%81%D1%82%D0%B0%D0%BD%D0%BE%D0%B2%D0%BA%D0%B0-clickhouse/

# data[].attributes.attachment_link.readable_text
Thank you for reading this post, don't forget to subscribe!

1.Установка
2.Запуск
3.Создание базы данных и таблиц
4.Вставка обновление удаление столбцов и данных
5.Запрос данных
6.Удаление таблиц и данных
7.Настройка firewall

ClickHouse - это аналитическая колоночная база данных с открытым исходным кодом, разработанная компанией Yandex для и работы с big data. Благодаря обработке запросов в режиме реального времени ClickHouse подходит для приложений, которым нужно получать аналитические данные за доли секунды. Язык запросов ClickHouse - это диалект SQL, который предоставляет возможности декларативных запросов.

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

Системы OLAP(Online Analytics Processing, интерактивная аналитическая обработка) позволяют организовывать большие объемы данных и выполнять сложные запросы. Они способны управлять петабайтами данных и быстро возвращать результаты запроса. поэтому системы OLAP полезны для работы в таких областях, как наука о данных и бизнес-аналитика.

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

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

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

yum install -y pygpgme yum-utils

pygpgme - это пакет для управления GPG-подписями, а yum-utils позволяет управлять исходным кодом.

Altinity, консалтинговая фирма ClickHouse, поддерживает YUM-репозиторий с последней версией ClickHouse. Вы можете добавить этот репозиторий и безопасно загрузить проверенные пакеты ClickHouse. Чтобы проверить содержимое пакета, просмотрите исходники, из которых он собирается, в этом проекте Github.

Создайте файл для данных о репозитории:
cat /etc/yum.repos.d/altinity_clickhouse.repo
Добавьте следующие строки в файл:

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

2: Запуск сервиса ClickHouse

Пакет clickhouse-server, который вы установили в предыдущем разделе, создает сервис systemd. Он выполняет такие действия, как запуск, остановка и перезапуск сервера базы данных. systemd - это система инициализации Linux для управления сервисами. В этом разделе мы запустим сервис ClickHouse и убедимся, что он работает правильно.

Запустите сервис clickhouse-server:
service clickhouse-server start
Вы должны увидеть:
Start clickhouse-server service: Path to data directory in /etc/clickhouse-server/config.xml: /var/lib/clickhouse/
DONE
Эта команда не возвращает вывод. Чтобы убедиться, что сервис работает, введите:
service clickhouse-server status
clickhouse-server service is running
Такой вывод показывает, что сервис был запущен успешно.

Теперь вы сможете использовать программу CLI clickhouse-client для подключения к серверу.

3: Создание баз данных и таблиц

В ClickHouse вы можете создавать и удалять базы данных, запуская SQL-операторы непосредственно в интерактивной командной строке. Операторы состоят из команд, составленных по определенному синтаксису, которые сообщают серверу БД, какую операцию нужно выполнить (вместе с необходимыми данными). Для создания базы данных используется синтаксис CREATE DATABASE table_name. Чтобы создать базу данных, сначала нужно запустить сеанс клиента, выполнив следующую команду:
clickhouse-client --multiline
Эта команда откроет доступ к клиентской командной строке, в которой вы можете запускать SQL-операторы ClickHouse для выполнения следующих действий:

Создание, обновление, удаление баз данных, таблиц, индексов, разделов и видов.

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

Флаг -multiline позволяет вводить запросы, которые занимают несколько строк.

Сейчас клиент ClickHouse готов к вставке данных. Попробуйте создать базу данных и таблицу. В этом мануале для примера мы создадим базу данных test, а внутри нее - таблицу по имени visits, которая отслеживает продолжительность посещений веб-сайта.

Находясь в командной строке ClickHouse, создайте БД test:
CREATE DATABASE test;
Вы увидите следующий вывод, который показывает, что вы создали базу данных:

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

Синтаксис для создания таблиц в ClickHouse выглядит так:

Значения table_name и column_name могут быть любыми действительными идентификаторами ASCII. ClickHouse поддерживает широкий спектр типов столбцов; вот некоторые из самых популярных:

UInt64: используется для хранения целочисленных значений в диапазоне от 0 до 18446744073709551615.

Float64: используется для хранения чисел с плавающей точкой, таких как 2039.23, 10.5 и т. п.

String: используется для хранения строк символов переменной длины. Не требует атрибутов максимальной длины, поскольку может хранить произвольную длину.

Date: хранит даты в формате YYYY-MM-DD.

DateTime: хранит даты и время в формате YYYY-MM-DD HH:MM:SS.

После определения столбца нужно указать движок, используемый в таблице. В ClickHouse движки определяют физическую структуру базовых данных, возможности запросов к таблице, режимы одновременного доступа и поддержку индексов. В разных ситуациях применяются разные типы движков. Наиболее часто используемый и широко применяемый тип - MergeTree.

Теперь вы знаете, как создаются таблицы. Давайте попробуем создать таблицу. Сначала выберите БД, в которую нужно добавить таблицу:

Следующий вывод показывает, что вы переключились в базу данных test из базы данных default:
USE test
Ok.
0 rows in set. Elapsed: 0.001 sec.
Все оставшиеся действия в этом руководстве будут выполняться в контексте этой базы данных.

Создайте таблицу visits с помощью этой команды:

Давайте рассмотрим команду подробнее. Вы создали таблицу visits, которая состоит из 4 столбцов:

id: столбец для первичного ключа. Как и в других системах РСУБД, столбец первичного ключа в ClickHouse идентифицирует строку; каждая строка должна иметь для этого столбца уникальное значение.

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

url: строковый столбец, в котором хранится посещенный URL-адрес, например http://example.com.

created: столбец даты и времени, который отслеживает время посещения.

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

Если вы намереваетесь использовать ClickHouse для архивации данных, которые редко запрашиваются, или для хранения временных данных, вы можете использовать семейство Log.

После определения столбцов вы определите другие параметры уровня таблицы. Опция PRIMARY KEY устанавливает id в качестве столбца первичного ключа, а ORDER BY будет хранить значения, отсортированные по столбцу id. Первичный ключ уникальным образом идентифицирует строку и используется для быстрого доступа к одной строке и эффективного размещения строк.

Запустив оператор для создания таблицы, вы увидите такой вывод:

В этом разделе вы создали базу данных и таблицу для отслеживания данных о посещениях веб-сайта. Дальше вы узнаете, как вставлять данные в таблицу, обновлять существующие данные и удалять их.

4: Вставка, обновление и удаление данных и столбцов

Теперь можно использовать таблицу visits для вставки, обновления и удаления данных. Следующий синтаксис используется для вставки строк в таблицу ClickHouse:
INSERT INTO table_name VALUES (column_1_value, column_2_value, ....);
Чтобы добавить тестовые данные в таблицу visits, введите следующие операторы:

Вы увидите следующий вывод для каждого оператора вставки. Это значит, что данные добавлены успешно.

Вы увидите следующий вывод для каждого оператора вставки. Это значит, что данные добавлены успешно.
INSERT INTO visits VALUES
Ok.
1 rows in set. Elapsed: 0.004 sec.
Теперь нужно добавить в таблицу дополнительный столбец. При добавлении или удалении столбцов из существующих таблиц ClickHouse поддерживает синтаксис ALTER.

Базовый синтаксис для добавления столбца в таблицу выглядит следующим образом:
ALTER TABLE table_name ADD COLUMN column_name column_type;
Добавьте в таблицу столбец location, в котором будет храниться расположение посещений веб-сайта:
ALTER TABLE visits ADD COLUMN location String;
Вы увидите такой вывод:

Вывод показывает, что вы успешно добавили столбец location.

Начиная с версии 19.3.6, ClickHouse не поддерживает обновление и удаление отдельных строк данных из-за ограничений реализации. Однако ClickHouse поддерживает массовые обновления и удаления и имеет для этих операций особый синтаксис SQL, чтобы подчеркнуть их нестандартное использование.

Следующий синтаксис - пример массового обновления строк:
ALTER TABLE table_name UPDATE column_1 = value_1, column_2 = value_2 ... WHERE filter_conditions;
Запустите следующий оператор, чтобы обновить столбец url всех строк, длительность которых не превышает 15.
ALTER TABLE visits UPDATE url = 'http://example2.com' WHERE duration < 15;
Вы получите такой вывод:

Вывод показывает, что ваш запрос на обновление завершен успешно. Строка "0 rows in set" сообщает, что запрос не вернул ни одной строки; это всегда происходит для любых запросов на обновление и удаление.

Синтаксис массового удаления строк аналогичен обновлению и имеет следующую структуру:
ALTER TABLE table_name DELETE WHERE filter_conditions;
Для примера используйте следующий оператор, чтобы удалить все строки, duration которых менее 5:

Выходные данные подтверждают, что вы удалили строки, чье значение duration менее 5.

Чтобы удалить столбцы из таблицы, нужно использовать такой синтаксис:
ALTER TABLE table_name DROP COLUMN column_name;
Удалите ранее добавленный столбец location, выполнив:
ALTER TABLE visits DROP COLUMN location;
Вывод DROP COLUMN, подтверждающий, что вы удалили столбец, будет выглядеть следующим образом:

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

5: Запрос данных

Язык запросов ClickHouse представляет собой пользовательский диалект SQL с расширениями и функциями для аналитических рабочих нагрузок. На этом этапе вы научитесь запускать запросы на выборку и агрегацию, чтобы получить данные и результаты из тестовой таблицы visits.

Запросы выбора позволяют извлекать строки и столбцы данных, отфильтрованных по указанным вами условиям, а также таким параметрам, как количество возвращаемых строк. Вы можете выбрать строки и столбцы данных, используя синтаксис SELECT. Основной синтаксис для запросов SELECT:
SELECT func_1(column_1), func_2(column_2) FROM table_name WHERE filter_conditions row_options;
Выполните следующий оператор, чтобы извлечь значения url и duration для строк, url которых http://example.com.

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

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

count: возвращает количество строк, соответствующих указанным условиям.

sum: возвращает сумму значений выбранных столбцов.

avg: возвращает среднее значение для выбранных столбцов

Некоторые характерные для ClickHouse агрегатные функции включают в себя:

uniq: возвращает приблизительное количество различных значений аргумента.

topK: возвращает массив наиболее часто встречающихся значений определенного столбца с помощью алгоритма аппроксимации.

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

Теперь давайте вычислим два основных URL, выполнив:

6: Удаление таблиц и баз данных

В этом разделе вы узнаете, как удалить свою таблицу visits и базу данных test.

Синтаксис для удаления таблиц выглядит так:
DROP TABLE table_name;
Чтобы удалить таблицу visits, запустите следующий оператор:
DROP TABLE visits;
Вы увидите следующий вывод, если таблица удалена успешно:
DROP TABLE visits
Ok.
0 rows in set. Elapsed: 0.005 sec.
Вы можете удалить базу данных, используя синтаксис DROP database table_name. Чтобы удалить базу данных, введите:
DROP DATABASE test;
Вы увидите следующий вывод, если БД удалена успешно:
DROP DATABASE test
Ok.
0 rows in set. Elapsed: 0.003 sec.
Итак, вы удалили таблицу и базу данных. Теперь, когда вы научились выполнять базовые операции с экземпляром ClickHouse, давайте включим удаленный доступ к вашему серверу баз данных.

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

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

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

Измените конфигурацию ClickHouse и разрешите ему прослушивать все интерфейсы.

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

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

Откройте в редакторе файл:
sudo vi /etc/clickhouse-server/config.xml
Раскомментируйте строку <!- <listen_host>0.0.0.0</listen_host> ->:

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

Чтобы обновить конфигурацию, перезапустите сервис:
sudo service clickhouse-server restart
Вы увидите такой вывод этой команды.
Stop clickhouse-server service:
DONE
Start clickhouse-server service: Path to data directory in /etc/clickhouse-server/config.xml: /var/lib/clickhouse/
DONE
Добавьте IP удаленной машины в зону public:
sudo firewall-cmd --permanent --zone=public --add-source=second_server_ip/32
Сервер ClickHouse прослушивает порт 8123 для HTTP-соединений и порт 9000 для соединений от клиента clickhouse-client. Разрешите доступ к обоим портам IP-адресу вашего второго сервера с помощью следующей команды:
sudo firewall-cmd --permanent --zone=public --add-port=8123/tcp
sudo firewall-cmd --permanent --zone=public --add-port=9000/tcp
Вы увидите для обеих команд следующий вывод, который показывает, что доступ к обоим портам включен:

Перезапустите брандмауэр:
sudo firewall-cmd --reload
Команда тоже должна вернуть success.

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

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

Войдите на второй сервер, запустите сеанс клиента:
clickhouse-client --host your_server_ip --multiline
Следующий вывод сообщает, что вы успешно подключились к серверу:

ClickHouse client version 19.4.3.
Connecting to your_server_ip:9000 as user default.
Connected to ClickHouse server version 19.4.3 revision 54416.
hostname 🙂

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

# data[].attributes.attachment_link.description
Thank you for reading this post, don't forget to subscribe! 1.Установка 2.Запуск 3.Создание базы дан­ных и таблиц 4.Вставка обнов­ле­ние уда­ле­ние столб­цов и данных 5.Запрос дан­ных 6.Удаление таб­лиц и данных 7.Настройка firewall ClickHouse - это ана­ли­ти­че­ская коло­ноч­ная база дан­ных с откры­тым исход­ным кодом, раз­ра­бо­тан­ная ком­па­ни­ей Yandex для OLAP и рабо­ты с big data.

# data[].attributes.body
https://clickhouse.com/docs/ru/getting-started/install

# data[].attributes.attachment_link.readable_text
ClickHouse может работать на любой операционной системе Linux, FreeBSD или Mac OS X с архитектурой процессора x86-64, AArch64 или PowerPC64LE.

Предварительно собранные пакеты компилируются для x86-64 и используют набор инструкций SSE 4.2, поэтому, если не указано иное, его поддержка в используемом процессоре, становится дополнительным требованием к системе. Вот команда, чтобы проверить, поддерживает ли текущий процессор SSE 4.2:

Чтобы запустить ClickHouse на процессорах, которые не поддерживают SSE 4.2, либо имеют архитектуру AArch64 или PowerPC64LE, необходимо самостоятельно собрать ClickHouse из исходного кода с соответствующими настройками конфигурации.

Яндекс рекомендует использовать официальные скомпилированные deb-пакеты для Debian или Ubuntu. Для установки пакетов выполните:

Чтобы использовать различные версии ClickHouse в зависимости от ваших потребностей, вы можете заменить stable на lts или testing.

Также вы можете вручную скачать и установить пакеты из репозитория.

clickhouse-common-static - устанавливает исполняемые файлы ClickHouse.

clickhouse-server - создаёт символические ссылки для clickhouse-server и устанавливает конфигурационные файлы.

clickhouse-client - создаёт символические ссылки для clickhouse-client и других клиентских инструментов и устанавливает конфигурационные файлы clickhouse-client.

clickhouse-common-static-dbg - устанавливает исполняемые файлы ClickHouse собранные с отладочной информацией.

Команда ClickHouse в Яндексе рекомендует использовать официальные предкомпилированные rpm-пакеты для CentOS, RedHat и всех остальных дистрибутивов Linux, основанных на rpm.

Сначала нужно подключить официальный репозиторий:

Для систем с пакетным менеджером zypper (openSUSE, SLES):

Далее любая команда yum install может быть заменена на zypper install. Чтобы указать желаемую версию, необходимо добавить -$VERSION в имени пакета, например clickhouse-client-22.2.2.22.

Для использования наиболее свежих версий нужно заменить stable на testing (рекомендуется для тестовых окружений). Также иногда доступен prestable.

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

Также есть возможность установить пакеты вручную, скачав отсюда: https://packages.clickhouse.com/rpm/stable.

Команда ClickHouse в Яндексе рекомендует использовать предкомпилированные бинарники из tgz-архивов для всех дистрибутивов, где невозможна установка deb- и rpm- пакетов.

Интересующую версию архивов можно скачать вручную с помощью curl или wget из репозитория https://packages.clickhouse.com/tgz/. После этого архивы нужно распаковать и воспользоваться скриптами установки. Пример установки самой свежей версии:

Для продуктивных окружений рекомендуется использовать последнюю stable-версию. Её номер также можно найти на github с на вкладке https://github.com/ClickHouse/ClickHouse/tags c постфиксом -stable.

Для запуска ClickHouse в Docker нужно следовать инструкции на Docker Hub. Внутри образов используются официальные deb-пакеты.

Для установки ClickHouse под Linux можно использовать единый переносимый бинарный файл из последнего коммита ветки master: [https://builds.clickhouse.com/master/amd64/clickhouse].

Для других операционных систем и архитектуры AArch64 сборки ClickHouse предоставляются в виде кросс-компилированного бинарного файла из последнего коммита ветки master (с задержкой в несколько часов).

macOS - curl -O 'https://builds.clickhouse.com/master/macos/clickhouse' && chmod a+x ./clickhouse

FreeBSD - curl -O 'https://builds.clickhouse.com/master/freebsd/clickhouse' && chmod a+x ./clickhouse

AArch64 - curl -O 'https://builds.clickhouse.com/master/aarch64/clickhouse' && chmod a+x ./clickhouse

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

Чтобы установить ClickHouse в рамках всей системы (с необходимыми конфигурационными файлами, настройками пользователей и т. д.), выполните sudo ./clickhouse install. Затем выполните команды clickhouse start (чтобы запустить сервер) и clickhouse-client (чтобы подключиться к нему).

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

Для компиляции ClickHouse вручную используйте инструкцию для Linux или Mac OS X.

Можно скомпилировать пакеты и установить их, либо использовать программы без установки пакетов. Также при ручой сборке можно отключить необходимость поддержки набора инструкций SSE 4.2 или собрать под процессоры архитектуры AArch64.

Для работы собранного вручную сервера необходимо создать каталоги для данных и метаданных, а также сделать сменить их владельца на желаемого пользователя ( chown). Пути к этим каталогам могут быть изменены в конфигурационном файле сервера (src/programs/server/config.xml), по умолчанию используются следующие:

На Gentoo для установки ClickHouse из исходного кода можно использовать просто emerge clickhouse.

Для запуска сервера в режиме демона, выполните:

Смотрите журналы в каталоге /var/log/clickhouse-server/.

Если сервер не стартует, проверьте корректность конфигурации в файле /etc/clickhouse-server/config.xml

Также можно запустить сервер вручную из консоли:

При этом журнал будет выводиться в консоль, что удобно для разработки. Если конфигурационный файл лежит в текущем каталоге, то указывать параметр --config-file не требуется, по умолчанию будет использован файл ./config.xml.

После запуска сервера соединиться с ним можно с помощью клиента командной строки:

По умолчанию он соединяется с localhost:9000 от имени пользователя default без пароля. Также клиент может быть использован для соединения с удалённым сервером с помощью аргумента --host.

Терминал должен использовать кодировку UTF-8.

Более подробная информация о клиенте располагается в разделе "Клиент командной строки".

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

# data[].attributes.body
https://clickhouse.com/docs/en/integrations/dbt

# data[].attributes.attachment_link.readable_text
dbt (data build tool) enables analytics engineers to transform data in their warehouses by simply writing select statements. dbt handles materializing these select statements into objects in the database in the form of tables and views - performing the T of Extract Load and Transform (ELT). Users can create a model defined by a SELECT statement.

Within dbt, these models can be cross-referenced and layered to allow the construction of higher-level concepts. The boilerplate SQL required to connect models is automatically generated. Furthermore, dbt identifies dependencies between models and ensures they are created in the appropriate order using a directed acyclic graph (DAG).

Dbt is compatible with ClickHouse through a ClickHouse-supported plugin. We describe the process for connecting ClickHouse with a simple example based on a publicly available IMDB dataset. We additionally highlight some of the limitations of the current connector.

dbt introduces the concept of a model. This is defined as a SQL statement, potentially joining many tables. A model can be "materialized" in a number of ways. A materialization represents a build strategy for the model's select query. The code behind a materialization is boilerplate SQL that wraps your SELECT query in a statement in order to create a new or update an existing relation.

dbt provides 4 types of materialization:

view (default): The model is built as a view in the database.

table: The model is built as a table in the database.

ephemeral: The model is not directly built in the database but is instead pulled into dependent models as common table expressions.

incremental: The model is initially materialized as a table, and in subsequent runs, dbt inserts new rows and updates changed rows in the table.

Additional syntax and clauses define how these models should be updated if their underlying data changes. dbt generally recommends starting with the view materialization until performance becomes a concern. The table materialization provides a query time performance improvement by capturing the results of the model's query as a table at the expense of increased storage. The incremental approach builds on this further to allow subsequent updates to the underlying data to be captured in the target table.

The current plugin for ClickHouse supports the view, table,, ephemeral and incremental materializations. The plugin also supports dbt snapshots and seeds which we explore in this guide.

For the following guides, we assume you have a ClickHouse instance available.

We assume the use of the dbt CLI for the following examples. Users may also wish to consider dbt Cloud, which offers a web-based Integrated Development Environment (IDE) allowing users to edit and run projects.

dbt offers a number of options for CLI installation. Follow the instructions described here. At this stage install dbt-core only. We recommend the use of pip.
Important: The following is tested under python 3.9.
Install the dbt ClickHouse plugin:

dbt excels when modeling highly relational data. For the purposes of example, we provide a small IMDB dataset with the following relational schema. This dataset originates from the relational dataset repository. This is trivial relative to common schemas used with dbt but represents a manageable sample:

We use a subset of these tables as shown.

Create the following tables:

We use the s3 function to read the source data from public endpoints to insert data. Run the following commands to populate the tables:

The execution of these may vary depending on your bandwidth, but each should only take a few seconds to complete. Execute the following query to compute a summary of each actor, ordered by the most movie appearances, and to confirm the data was loaded successfully:

The response should look like:

|id |name |num_movies|avg_rank |unique_genres|uniq_directors|updated_at | |45332 |Mel Blanc |832 |6.175853582979779 |18 |84 |2022-04-26 14:01:45| |621468|Bess Flowers|659 |5.57727638854796 |19 |293 |2022-04-26 14:01:46| |372839|Lee Phelps |527 |5.032976449684617 |18 |261 |2022-04-26 14:01:46| |283127|Tom London |525 |2.8721716524875673|17 |203 |2022-04-26 14:01:46| |356804|Bud Osborne |515 |2.0389507108727773|15 |149 |2022-04-26 14:01:46|

In the later guides, we will convert this query into a model - materializing it in ClickHouse as a dbt view and table.

1. cd into your project folder:

2. At this point, you will need the text editor of your choice. In the examples below, we use the popular VSCode. Opening the IMDB directory, you should see a collection of yml and sql files:

3. Update your dbt_project.yml file to specify our first model - actor_summary and set profile to clickhouse_imdb.

When using the view materialization, a model is rebuilt as a view on each run, via a CREATE VIEW AS statement in ClickHouse. This doesn't require any additional storage of data but will be slower to query than table materializations.

1. From the imdb folder, delete the directory models/example:

2. Create a new file in the actors within the models folder. Here we create files that each represent an actor model:

3. Create the files schema.yml and actor_summary.sql in the models/actors folder.

The file schema.yml defines our tables. These will subsequently be available for use in macros. Edit models/actors/schema.yml to contain this content:

The actors_summary.sql defines our actual model. Note in the config function we also request the model be materialized as a view in ClickHouse. Our tables are referenced from the schema.yml file via the function source e.g. source('imdb', 'movies') refers to the movies table in the imdb database. Edit models/actors/actors_summary.sql to contain this content:

Note how we include the column updated_at in our final actor_summary. We use this later for incremental materializations.

4. From the imdb directory execute the command dbt run.

5. dbt will represent the model as a view in ClickHouse as requested. We can now query this view directly. This view will have been created in the imdb_dbt database - this is determined by the schema parameter in the file ~/.dbt/profiles.yml under the clickhouse_imdb profile.

Querying this view, we can replicate the results of our earlier query with a simpler syntax:

In the previous example, our model was materialized as a view. While this might offer sufficient performance for some queries, more complex SELECTs or frequently executed queries may be better materialized as a table. This materialization is useful for models that will be queried by BI tools to ensure users have a faster experience. This effectively causes the query results to be stored as a new table, with the associated storage overheads - effectively, an INSERT TO SELECT is executed. Note that this table will be reconstructed each time i.e., it is not incremental. Large result sets may therefore result in long execution times - see dbt Limitations.

1. Modify the file actors_summary.sql such that the materialized parameter is set to table. Notice how ORDER BY is defined, and notice we use the MergeTree table engine:

2. From the imdb directory execute the command dbt run. This execution may take a little longer to execute - around 10s on most machines.

3. Confirm the creation of the table imdb_dbt.actor_summary:

You should the table with the appropriate data types:

4. Confirm the results from this table are consistent with previous responses. Notice an appreciable improvement in the response time now that the model is a table:

Feel free to issue other queries against this model. For example, which actors have the highest ranking movies with more than 5 appearances?

The previous example created a table to materialize the model. This table will be reconstructed for each dbt execution. This may be infeasible and extremely costly for larger result sets or complex transformations. To address this challenge and reduce the build time, dbt offers Incremental materializations. This allows dbt to insert or update records into a table since the last execution, making it appropriate for event-style data. Under the hood a temporary table is created with all the updated records and then all the untouched records as well as the updated records are inserted into a new target table. This results in similar limitations for large result sets as for the table model.

To overcome these limitations for large sets, the plugin supports 'inserts_only' mode, where all the updates are inserted into the target table without creating a temporary table (more about it below).

To illustrate this example, we will add the actor "Clicky McClickHouse", who will appear in an incredible 910 movies - ensuring he has appeared in more films than even Mel Blanc.

1. First, we modify our model to be of type incremental. This addition requires:

1. unique_key - To ensure the plugin can uniquely identify rows, we must provide a unique_key - in this case, the id field from our query will suffice. This ensures we will have no row duplicates in our materialized table. For more details on uniqueness constraints, see here.

2. Incremental filter - We also need to tell dbt how it should identify which rows have changed on an incremental run. This is achieved by providing a delta expression. Typically this involves a timestamp for event data; hence our updated_at timestamp field. This column, which defaults to the value of now() when rows are inserted, allows new roles to be identified. Additionally, we need to identify the alternative case where new actors are added. Using the {{this}} variable, to denote the existing materialized table, this gives us the expression where id > (select max(id) from {{ this }}) or updated_at > (select max(updated_at) from {{this}}). We embed this inside the {% if is_incremental() %} condition, ensuring it is only used on incremental runs and not when the table is first constructed. For more details on filtering rows for incremental models, see this discussion in the dbt docs.

Update the file actor_summary.sql as follows:

Note that our model will only respond to updates and additions to the roles and actors tables. To respond to all tables, users would be encouraged to split this model into multiple sub-models - each with their own incremental criteria. These models can in turn be referenced and connected. For further details on cross-referencing models see here.

2. We will now add data to our model to illustrate an incremental update. Add our actor "Clicky McClickHouse" to the actors table:

3. Let's have Clicky star in 910 random movies:

We can identify the statements executed to achieve the above incremental update by querying ClickHouse's query log.

Adjust the above query to the period of execution. We leave result inspection to the user but highlight the general strategy used by the plugin to perform incremental updates:

1. The plugin creates a temporary table actor_sumary__dbt_tmp. Rows that have changed are streamed into this table.

2. A new table, actor_summary_new, is created. The rows from the old table are, in turn, streamed from the old to new, with a check to make sure row ids do not exist in the temporary table. This effectively handles updates and duplicates.

3. The results from the temporary table are streamed into the new actor_summary table:

4. Finally, the new table is exchanged atomically with the old version via an EXCHANGE TABLES statement. The old and temporary tables are in turn dropped.

This is visualized below:

This strategy may encounter challenges on very large models. For further details see Limitations.

To overcome the limitations of large datasets in incremental models, the plugin uses the dbt configuration parameter incremental_strategy. This can be set to the value append. When set, updated rows are inserted directly into the target table (a.k.a imdb_dbt.actor_summary) and no temporary table is created. Note: Append only mode requires your data to be immutable or for duplicates to be acceptable. If you want an incremental table model that supports altered rows don't use this mode!

To illustrate this mode, we will add another new actor and re-execute dbt run with incremental_strategy='append'.

1. Configure append only mode in actor_summary.sql:

2. Let's add another famous actor - Danny DeBito

3. Let's star Danny in 920 random movies.

Note how much faster that incremental was compared to the insertion of Clicky.

Checking again the query_log table reveals the differences between the 2 incremental runs:

In this run, only the new rows are added straight to imdb_dbt.actor_summary table and there is no table creation involved.

Historically ClickHouse has had only limited support for updates and deletes, in the form of asynchronous Mutations. These can be extremely IO-intensive and should generally be avoided.

ClickHouse 22.8 introduced Lightweight deletes. These are currently experimental but offer a more performant means of deleting data.

This mode can be configured for a model via the incremental_strategy parameter i.e.

This strategy operates directly on the target model's table, so if there is an issue during the operation, the data in the incremental model is likely to be in an invalid state - there is no atomic update.

In summary, this approach:

1. The plugin creates a temporary table actor_sumary__dbt_tmp. Rows that have changed are streamed into this table.

2. A DELETE is issued against the current actor_summary table. Rows are deleted by id from actor_sumary__dbt_tmp

3. The rows from actor_sumary__dbt_tmp are inserted into actor_summary using an INSERT INTO actor_summary SELECT * FROM actor_sumary__dbt_tmp.

This process is shown below:

dbt snapshots allow a record to be made of changes to a mutable model over time. This in turn allows point-in-time queries on models, where analysts can "look back in time" at the previous state of a model. This is achieved using type-2 Slowly Changing Dimensions where from and to date columns record when a row was valid. This functionality is supported by the ClickHouse plugin and is demonstrated below.

This example assumes you have completed Creating an Incremental Table Model. Make sure your actor_summary.sql doesn't set inserts_only=True. Your models/actor_summary.sql should look like this:

Create a file actor_summary in the snapshots directory.

A few observations regarding this content:

The select query defines the results you wish to snapshot over time. The function ref is used to reference our previously created actor_summary model.

We require a timestamp column to indicate record changes. Our updated_at column (see Creating an Incremental Table Model) can be used here. The parameter strategy indicates our use of a timestamp to denote updates, with the parameter updated_at specifying the column to use. If this is not present in your model you can alternatively use the check strategy. This is significantly more inefficient and requires the user to specify a list of columns to compare. dbt compares the current and historical values of these columns, recording any changes (or doing nothing if identical).

Note how a table actor_summary_snapshot has been created in the snapshots db (determined by the target_schema parameter).

1. Sampling this data you will see how dbt has included the columns dbt_valid_from and dbt_valid_to. The latter has values set to null. Subsequent runs will update this.

2. Make our favorite actor Clicky McClickHouse appear in another 10 films.

3. If we now query our snapshot, notice we have 2 rows for Clicky McClickHouse. Our previous entry now has a dbt_valid_to value. Our new value is recorded with the same value in the dbt_valid_from column, and a dbt_valid_to value of null. If we did have new rows, these would also be appended to the snapshot.

For further details on dbt snapshots see here.

dbt provides the ability to load data from CSV files. This capability is not suited to loading large exports of a database and is more designed for small files typically used for code tables and dictionaries, e.g. mapping country codes to country names. For a simple example, we generate and then upload a list of genre codes using the seed functionality.

1. We generate a list of genre codes from our existing dataset. From the dbt directory, use the clickhouse-client to create a file seeds/genre_codes.csv:

2. Execute the dbt seed command. This will create a new table genre_codes in our database imdb_dbt (as defined by our schema configuration) with the rows from our csv file.

3. Confirm these have been loaded:

The current ClickHouse plugin for dbt has several limitations users should be aware of:

1. The plugin currently materializes models as tables using an INSERT TO SELECT. This effectively means data duplication. Very large datasets (PB) can result in extremely long run times, making some models unviable. Aim to minimize the number of rows returned by any query, utilizing GROUP BY where possible. Prefer models which summarize data over those which simply perform a transform whilst maintaining row counts of the source.

2. To use Distributed tables to represent a model, users must create the underlying replicated tables on each node manually. The Distributed table can, in turn, be created on top of these. The plugin does not manage cluster creation.

3. When dbt creates a relation (table/view) in a database, it usually creates it as: {{ database }}.{{ schema }}.{{ table/view id }}. ClickHouse has no notion of schemas. The plugin therefore uses {{schema}}.{{ table/view id }}, where schema is the ClickHouse database.

Further Information

The previous guides only touch the surface of dbt functionality. Users are recommended to read the excellent dbt documentation.

Additional configuration for the plugin is described here.

# data[].attributes.attachment_link.title
Integrating dbt and ClickHouse | ClickHouse Docs

# data[].attributes.attachment_link.description
Users can transform and model their data in ClickHouse using dbt

# data[].attributes.body
https://docs.getdbt.com/reference/resource-configs/clickhouse-configs

# data[].attributes.attachment_link.title
ClickHouse configurations | dbt Developer Hub

# data[].attributes.attachment_link.description
Read this guide to understand ClickHouse configurations in dbt.

# data[].attributes.body
https://pypi.org/project/dbt-clickhouse/0.21.1/

# data[].attributes.attachment_link.description
The Clickhouse plugin for dbt (data build tool)

# data[].attributes.body
https://medium.com/@vargascypher/clickhouse-dbt-how-to-accompany-dbt-run-and-test-results-a210baed7bec

# data[].attributes.attachment_link.readable_text
What's Data Observability ?
As Andrew Magnusson Director of Global Customer Engineering StrongDM say's:

"Data observability is the ability to understand, diagnose, and manage data health across multiple IT tools throughout the data lifecycle. A data observability platform helps organizations to discover, triage, and resolve real-time data issues using telemetry data like logs, metrics, and traces."

Observability goes beyond monitoring by allowing organizations to improve security by tracking data movement across disparate applications, servers, and tools.
DBT Observability
Keeping the data observability concepts in mind, we see that dbt can be a perfect tool for implement data quality process, build tests and lineages, but it's hard to monitoring especially if you use dbt-core only.

In this great post implementing this concept ('dbt observability') , our friend Or Avidov show us an easy way on how to catch and save dbt run an tests result on snowflake.

Now i'll show how we can implement this with Clickhouse, changing some settings.
STEP BY STEP
The execution of a resource in dbt generates a Result object. This file contains information about a completed invocation of dbt, timing and status info for each model run or test that was executed. At the end of an invocation, dbt records these objects in dbt artifacts.

Luckily, dbt exposes most of the important information of these artifacts and as Or Avidov show to us, we can extract Metadata about command results from the resultsvariable (accessible only during an on-run-end hook).

I will keep the steps really simple since Or Avidov already describes it perfectly in his article.

Step 1 : Create an empty table that will be used for storing the results.

We used some different settings like TTL to prevent this table from getting too big and map completed_at and message fields.

{{
 config(
 materialized = 'incremental',
 incremental_strategy='append',
 order_by = 'result_id',
 engine = 'MergeTree()',
 post_hook = "ALTER TABLE {{this}} MODIFY TTL completed_at + INTERVAL 3 MONTH"
 )
}}

with empty_table as (
 select
 '' as result_id,
 '' as invocation_id,
 '' as unique_id,
 toDateTime(now()) as completed_at,
 '' as database_name,
 '' as schema_name,
 '' as name,
 '' as resource_type,
 '' as status,
 0.0 as execution_time,
 0 as rows_affected,
 '' as message
)

select * from empty_table
-- This is a filter so we will create an empty table
where 1 = 0

Step 2: Implement a dbt macro that will parse the run results

In this step, we add 2 more fileds, completed_at from datetime.now() Jinja module and message from result object.

{% macro parse_dbt_results(results) %}
 -- Create a list of parsed results
 {%- set parsed_results = [] %}
 -- Flatten results and add to list
 {% for run_result in results %}
 -- Convert the run result object to a simple dictionary
 {% set run_result_dict = run_result.to_dict() %}
 -- Get the underlying dbt graph node that was executed
 {% set node = run_result_dict.get('node') %}
 {% set rows_affected = run_result_dict.get('adapter_response', {}).get('rows_affected', 0) %}
 {%- if not rows_affected -%}
 {% set rows_affected = 0 %}
 {%- endif -%}
 -- Get the datetime at the end of execution
 {% set now = modules.datetime.datetime.utcnow() %}
 {% set parsed_result_dict = {
 'result_id': invocation_id ~ '.' ~ node.get('unique_id'),
 'invocation_id': invocation_id,
 'unique_id': node.get('unique_id'),
 'completed_at': now.strftime("%Y-%m-%d %H:%M:%S") ,
 'database_name': node.get('schema'),
 'schema_name': node.get('schema'),
 'name': node.get('name'),
 'resource_type': node.get('resource_type'),
 'status': run_result_dict.get('status'),
 'execution_time': run_result_dict.get('execution_time'),
 'rows_affected': rows_affected,
 'message': run_result_dict.get('message')
 }%}
 {% do parsed_results.append(parsed_result_dict) %}
 {% endfor %}
 {{ return(parsed_results) }}
{% endmacro %}

Step 3: Create an on-run-end hook to insert the results and add the hook to dbt_project.yml

{% macro log_dbt_results(results) %}
 {%- if execute -%}
 {%- set parsed_results = parse_dbt_results(results) -%}
 {%- if parsed_results | length > 0 -%}
 {% set insert_dbt_results_query -%}
 insert into {{ source('insights','dbt_results') }}
 (
 result_id,
 invocation_id,
 unique_id,
 completed_at,
 database_name,
 schema_name,
 name,
 resource_type,
 status,
 execution_time,
 rows_affected,
 message
 ) values
 {%- for parsed_result_dict in parsed_results -%}
 (
 '{{ parsed_result_dict.get('result_id') }}',
 '{{ parsed_result_dict.get('invocation_id') }}',
 '{{ parsed_result_dict.get('unique_id') }}',
 '{{ parsed_result_dict.get('completed_at') }}',
 '{{ parsed_result_dict.get('database_name') }}',
 '{{ parsed_result_dict.get('schema_name') }}',
 '{{ parsed_result_dict.get('name') }}',
 '{{ parsed_result_dict.get('resource_type') }}',
 '{{ parsed_result_dict.get('status') }}',
 {{ parsed_result_dict.get('execution_time') }},
 {{ parsed_result_dict.get('rows_affected') }},
 '{{ parsed_result_dict.get('message') }}'
 ) {{- "," if not loop.last else "" -}}
 {%- endfor -%}
 {%- endset -%}
 {%- do run_query(insert_dbt_results_query) -%}
 {%- endif -%}
 {%- endif -%}
 -- This macro is called from an on-run-end hook and therefore must return a query txt to run. Returning an empty string will do the trick
 {{ return ('') }}
{% endmacro %}

Now you can add the hook at the end of your dbt_project:

on-run-end:
 - "{{ log_dbt_results(results) }}"

With this, on every dbt command invocation, this macro will be called at the end of the execution and automatically save the parsed results.

# data[].attributes.attachment_link.title
Clickhouse + dbt: How to accompany dbt run and test results

# data[].attributes.attachment_link.description
What's Data Observability ? As Andrew Magnusson Director of Global Customer Engineering StrongDM say's: Observability goes beyond monitoring by allowing organizations to improve security by tracking...

# data[].attributes.body
https://www.youtube.com/watch?v=16VBGdNuHv8

# data[].attributes.attachment_link.title
Using dbt to Transform Data in ClickHouse

# data[].attributes.body
https://github.com/ClickHouse/dbt-clickhouse/blob/main/examples/taxis/dbt_project.yml

# data[].attributes.attachment_link.description
The Clickhouse plugin for dbt (data build tool). Contribute to ClickHouse/dbt-clickhouse development by creating an account on GitHub.

# data[].attributes.body
https://vc.ru/services/607637-keys-hoff-kak-biznesu-poluchit-maksimum-ot-dannyh-dlya-produktovoy-analitiki-i-digital-marketinga

# data[].attributes.attachment_link.readable_text
Рассказываем, как компании распутать десятки терабайт данных, реагировать на ошибки в них в 3 раза быстрее и сократить время на подготовку аналитических отчётов до 20 минут.

Hoff - большая сеть гипермаркетов мебели и товаров для дома, сайт hoff.ru и мобильное приложение используют более 113 миллионов клиентов. Чтобы проводить продуктовый и маркетинговый анализ, мы собираем много информации. В локальном хранилище у нас находятся ключевые данные, такие как продажи, остатки и движения товаров. А вот в облачном Data Warehouse (DWH) мы храним информацию о событиях и действия клиентов на сайте и в приложениях, о просмотрах товаров, конверсиях и спросе в Интернете. За 5 лет накопилось 93 ТБ сырых данных из более чем 10 различных источников. На основе этих данных мы строим аналитику, всего сотрудники используют 155 различных дашбордов и формируют 42 отчёта.

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

Для решения проблемы мы обратились в e-сommerce агентство Aero. В процессе обсуждения проекта выяснилось, что устранить противоречия в данных и ускорить работу с хранилищем DWH можно только с помощью полного рефакторинга. Кроме того, возникла необходимость миграции хранилища на российскую платформу, поэтому совместно с Aero мы решили полностью перестроить архитектуру: создать масштабируемое DWH с едиными принципами хранения для всех данных и системой алертинга для контроля качества.

Как "распутать" 93 ТБ данных

Мы сформировали команду из специалистов Hoff, Hoff Tech и Aero. Это был сложный этап работы. 93 ТБ данных использовались для создания дашбордов и отчётов, которые содержали 1938 расчётных полей. Команда провела реверс-инжиниринг: начиная с дашбордов отслеживали путь данных к источникам, выявляя последовательности и правила преобразования.

В начале проекта общая схема потоков данных выглядела так:

Упорядочить эти потоки данных удалось благодаря последовательной отработке скриптов и разбивки данных по источникам. Итоговый упорядоченный граф:

В результате мы определили, что почти треть накопленной за 5 лет информации устарела или не используется. Мы совместно решали, какие из этих данных не нужно переносить в новое хранилище.

Кроме того, выяснилось, что еженедельно из Power BI запрашивается 29 ТБ данных для построения аналитики. Изначально наше DWH было развёрнуто на облачной платформе, которая предусматривала оплату за каждое обращение к данным. При такой схеме использования затраты на эти запросы составляли порядка 3000 долларов в месяц.

Оптимизация расходов и опенсорс-продукты

Aero предложили использовать опенсорс-продукты, что позволило бы избежать платы за каждый запрос данных и при этом разворачивать DWH на любой инфраструктуре. Мы хотели локализовать хранилище, поэтому новую облачную платформу нужно было найти в России. Выбрали Yandex Cloud - платформа предоставляет управляемые сервисы аналитической СУБД ClickHouse и массивно-параллельной СУБД Greenplum, которые хорошо подошли для проекта.

Clickhouse и Greenplum - это бесплатные опенсорс-продукты, над которыми работает большое сообщество. Они, как правило, содержат меньше дефектов, быстрее обновляются и не зависят от политики какой-либо компании. Архитектуру, использующую эти продукты, можно быстро масштабировать или перенести на любую инфраструктуру без потери данных и пересборки хранилища с нуля.

Clickhouse хорошо работает с сырыми данными: за счёт высокого сжатия хранилище занимает меньше места, но при этом даже единичные ad hoc запросы выполняются оперативно. Благодаря распределённой обработке, в Greenplum можно быстро изменять, фильтровать и связывать данные.

Многослойная архитектура, визуализация данных и алертинг

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

Для её реализации мы решили послойно развернуть хранилище DWH в разных системах управления.

В старом хранилище аналитики могли работать только с сырыми данными. В новом DWH эту данные мы перенесли в ClickHouse вместе с коннекторами, что позволяет хранить все сырые данные из источников в одной СУБД.

Для ядра DWH мы выбрали Greenplum. Data Vault 2.0 Greenplum предполагает хранение только высоконормализованных данных. Поэтому предварительно мы обрабатываем сырые данные, поступающие из ClickHouse, с помощью фреймворка DBT. Такой подход позволяет избежать повторов данных и противоречий, можно доверять информации в хранилище на 100%, не переживая об актуальности и достоверности. Данные автоматически обновляются по заданному расписанию с помощью настроенных пайплайнов в Airflow. Благодаря чётко выстроенным процессам и описанным регламентам, наши сотрудники могут поддерживать эту систему самостоятельно.

Ещё один важный этап работы - создание аналитических витрин данных. Это части хранилища в виде агрегированных таблиц, которые содержат данные по разным направлениям деятельности Hoff. Это ключевые метрики, которые можно комбинировать различными способами для дальнейшего анализа. На основе витрин данных строится визуализация в Yandex DataLens. Одна витрина данных может быть источником для множества дашбордов. В старом хранилище мы работали только с сырыми данными, поэтому дашборды и отчёты каждый отдел компании строил исходя из собственной экспертизы. Теперь эти отчёты создаются из предобработанной по единому стандарту информации.

Кроме того, для нашего DWH Aero создали с нуля систему алертинга. Теперь актуальность отчётов определяется с помощью трехцветной системы маркировки, это позволяет избежать попадание устаревших данных в аналитику. При обнаружении ошибок на всех этапах загрузки и обработки данных работает система оповещений, что позволяет избежать работы с неконсистентной или неверной информацией.

За 1 год нам с Aero удалось полностью пересобрать облачный DWH. После анализа и оптимизации данных мы сократили их объём с 93 ТБ до 51 ТБ. При этом в новое хранилище ежедневно поступает 15 ГБ сырых данных, а из них в Data Vault Greenplum попадает 3 ГБ высоконормализованных данных. Работа с предварительно подготовленной информацией позволила сократить время на подготовку аналитического отчёта до 20 минут.

Созданная многоуровневая архитектура и использование управляемых облачных сервисов позволяют легко масштабировать проект. При регулярно растущем объёме данных DWH можно трансформировать в Data Lake без потери качества структуры.

Объем рынка онлайн-торговли в России ежегодно растет. Появляются новые игроки, увеличивается конкуренция и в борьбе за внимание потребителя выигрывает тот, кто умеет работать с данными, развивает продуктовую аналитику и маркетинг. Это позволяет предлагать наиболее релевантные товары, предугадывать интересы и тренды, эффективнее распоряжаться бюджетом

Появилась единая методология очистки и подготовки данных - теперь вероятность разночтений сведена к минимуму. При появлении нового источника достаточно прописать новый алгоритм, после чего сырые данные будут автоматически приводиться в стандартный вид и попадать в Data Valut. Разработанная система алертинга позволяет оперативно оповещать аналитиков - скорость реагирования на ошибки увеличилась в 3 раза. Всё это позволяет поддерживать достоверность информации.

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

Подписывайтесь на блог Yandex Cloud, чтобы узнавать еще больше новостей и историй об IT и бизнесе.

Другие истории наших партнеров и клиентов, которые активно читают наши подписчики:

# data[].attributes.attachment_link.title
Кейс Hoff: как бизнесу получить максимум от данных для продуктовой аналитики и digital-маркетинга - Сервисы на vc.ru

# data[].attributes.attachment_link.description
Рассказываем, как компании распутать десятки терабайт данных, реагировать на ошибки в них в 3 раза быстрее и сократить время на подготовку аналитических отчётов до 20 минут.

# data[].attributes.body
https://clickhouse.com/blog/building-a-data-warehouse-with-clickhouse

# data[].attributes.attachment_link.title
How we built the Internal Data Warehouse at ClickHouse

# data[].attributes.attachment_link.description
Read about how we built our own internal data warehouse with ClickHouse and only open-source technology.

# data[].attributes.body
https://clickhouse.com/blog/user-defined-functions-clickhouse-udfs

# data[].attributes.attachment_link.readable_text
This post continues our bite-sized series, where we highlight cool queries and share interesting tips and tricks related to ClickHouse.
Interested in trying UDFs in ClickHouse Cloud? Get started instantly with $300 free credit for 30 days.
Today, we focus on a query from the analysis of the ClickHouse repository using the Git data captured by the git-import tool distributed with ClickHouse and presented in a previous post in this series. This query uses a powerful feature of ClickHouse: SQL User-defined functions, which we've recently made available in ClickHouse Cloud!

For this post, we'll create a query that shows the entire commit history of a file in the ClickHouse repository. This query will utilize a UDF to allow for renames in the file. Our final solution is included in the documentation, and we welcome improvements.

All examples in this post can be reproduced in our play.clickhouse.com environment (see the git_clickhouse database). Alternatively, if you want to dive deeper into this dataset, ClickHouse Cloud is a great starting point and now supports UDFs - spin up a cluster using a free trial, load the data, let us deal with the infrastructure, and get querying!

User-defined functions (UDF) allow users to extend the behavior of ClickHouse, by creating lambda expressions that can utilize SQL constructs and functions. These functions can then be used like any in-built function in a query.

To create our UDF, we use the CREATE FUNCTION <name> syntax and specify our method signature as a lambda expression. In it its simplest form, this might look like the following, which returns the string odd or even depending on the parity of the number:

CREATE FUNCTION parity_str AS (n) -> if(n % 2, 'odd', 'even'); SELECT number, parity_str(number) FROM numbers(5) ┌─number─┬─if(modulo(number, 2), 'odd', 'even')─┐ │ 0 │ even │ │ 1 │ odd │ │ 2 │ even │ │ 3 │ odd │ │ 4 │ even │ └────────┴──────────────────────────────────────┘



✎

This is deliberately simple. As we'll demonstrate, these can get considerably more complex.

Our git-import generates data for several tables from the git commit history of a repository. One of these, file_changes, contains a row for every file changed in a commit. A commit that modifies more than one file will therefore generate multiple rows, allowing us to construct the history of a file with a simple SELECT statement. For example, below, we look at the recent commits to our ReplicatedMergeTree:

SELECT time, substring(commit_hash, 1, 11) AS commit, change_type, author, lines_added AS added, lines_deleted AS deleted FROM git.file_changes WHERE path = 'src/Storages/StorageReplicatedMergeTree.cpp' ORDER BY time DESC LIMIT 5 ┌────────────────time─┬─commit──────┬─change_type─┬─author─────────────┬─added─┬─deleted─┐ │ 2022-10-30 16:30:51 │ c68ab231f91 │ Modify │ Alexander Tokmakov │ 13 │ 10 │ │ 2022-10-23 16:24:20 │ b40d9200d20 │ Modify │ Anton Popov │ 28 │ 30 │ │ 2022-10-23 01:23:15 │ 56e5daba0c9 │ Modify │ Anton Popov │ 28 │ 44 │ │ 2022-10-21 13:35:37 │ 851f556d65a │ Modify │ Igor Nikonov │ 3 │ 2 │ │ 2022-10-21 13:02:52 │ 13d31eefbc3 │ Modify │ Igor Nikonov │ 4 │ 4 │ └─────────────────────┴─────────────┴─────────────┴────────────────────┴───────┴─────────┘ 5 rows in set. Elapsed: 0.011 sec. Processed 3.91 thousand rows, 704.14 KB (350.59 thousand rows/s., 63.19 MB/s.)



✎

This approach works until a commit renames a file. A rename is denoted by the change_type having the value Rename. As shown below, the path column holds the new name of the file (also used in subsequent modification commits), while the original name is referenced in the old_path column for this row only.

SELECT time, substring(commit_hash, 1, 11) AS commit, change_type, path, old_path FROM git.file_changes WHERE path = 'src/Storages/StorageReplicatedMergeTree.cpp' ORDER BY time ASC LIMIT 2 FORMAT Vertical Row 1: ────── time: 2020-04-03 16:14:31 commit: 06446b4f08a change_type: Rename path: src/Storages/StorageReplicatedMergeTree.cpp old_path: dbms/Storages/StorageReplicatedMergeTree.cpp Row 2: ────── time: 2020-04-07 16:28:29 commit: 82a87bc0d2d change_type: Modify path: src/Storages/StorageReplicatedMergeTree.cpp old_path: 2 rows in set. Elapsed: 0.009 sec. Processed 122.88 thousand rows, 7.93 MB (13.46 million rows/s., 867.97 MB/s.)



✎

While we could potentially modify our query to match the old_path and path fields as shown below, this will only allow for one rename. This problem might typically be solved by recursive CTEs, which are not supported in ClickHouse.

path = 'src/Storages/StorageReplicatedMergeTree.cpp' OR old_path = 'src/Storages/StorageReplicatedMergeTree.cpp`

Ideally, we want to recurse through the complete rename hierarchy and collect the full list of files. We could then simply modify our restriction to be path IN (set of filenames). For this we will use a UDF.

Note: this problem could also be solved at data insertion time. We could modify the original git-import tool to add an authoritative file id on all commits, giving us a consistent field value across all commits. Humor us and assume we can't do this :)

In our case, we need a recursive behavior where the function calls itself - each time passing the previous path name and finding the next rename event.

Unfortunately, recursion is not currently supported in ClickHouse UDFs. We can work around this limitation by specifying the function N times, where each function calls another. While this limits us to a rename depth of N, it should be sufficient for our use case. Below we create our first function to show the general structure:

CREATE FUNCTION file_path_history AS n -> if(empty(n), [], arrayConcat([n], file_path_history_01(( SELECT if(empty(old_path), NULL, old_path) FROM git.file_changes WHERE (path = n) AND ((change_type = 'Rename') OR (change_type = 'Add')) LIMIT 1 ))))

Our function file_path_history accepts the file's name of interest as a parameter n - likely the current known path on the first call. This path is then concatenated to the current result using the arrayConcat function, in addition to the result of a UDF call to the next level via file_path_history_01 (we haven't defined this yet). To this function, we pass the previous filename via the query:

SELECT if(empty(old_path), Null, old_path) FROM git.file_changes WHERE path = n AND (change_type = 'Rename' OR change_type = 'Add') LIMIT 1 ))))

The following function, file_path_history_01, is very similar, except it will receive the old path of the original file specified by the user. It, in turn, finds the previous path for this file, invoking file_path_history_02. This artificial recursion continues until either we reach the maximum depth (i.e., the file has been renamed more than five times) or no result is returned from a SELECT (effectively a Null).

Our complete function definitions look like this. Note our final function is different and provides a base case:

CREATE FUNCTION file_path_history AS (n) -> if(empty(n), [], arrayConcat([n], file_path_history_01((SELECT if(empty(old_path), Null, old_path) FROM git.file_changes WHERE path = n AND (change_type = 'Rename' OR change_type = 'Add') LIMIT 1)))); CREATE FUNCTION file_path_history_01 AS (n) -> if(isNull(n), [], arrayConcat([n], file_path_history_02((SELECT if(empty(old_path), Null, old_path) FROM git.file_changes WHERE path = n AND (change_type = 'Rename' OR change_type = 'Add') LIMIT 1)))); CREATE FUNCTION file_path_history_02 AS (n) -> if(isNull(n), [], arrayConcat([n], file_path_history_03((SELECT if(empty(old_path), Null, old_path) FROM git.file_changes WHERE path = n AND (change_type = 'Rename' OR change_type = 'Add') LIMIT 1)))); CREATE FUNCTION file_path_history_03 AS (n) -> if(isNull(n), [], arrayConcat([n], file_path_history_04((SELECT if(empty(old_path), Null, old_path) FROM git.file_changes WHERE path = n AND (change_type = 'Rename' OR change_type = 'Add') LIMIT 1)))); CREATE FUNCTION file_path_history_04 AS (n) -> if(isNull(n), [], arrayConcat([n], file_path_history_05((SELECT if(empty(old_path), Null, old_path) FROM git.file_changes WHERE path = n AND (change_type = 'Rename' OR change_type = 'Add') LIMIT 1)))); CREATE FUNCTION file_path_history_05 AS (n) -> if(isNull(n), [], [n]);

We can now use our function file_path_history like any other. Below we get the full path history of our Replicated Merge Tree.

SELECT file_path_history('src/Storages/StorageReplicatedMergeTree.cpp') AS paths FORMAT Vertical Row 1: ────── paths: ['src/Storages/StorageReplicatedMergeTree.cpp', 'dbms/Storages/StorageReplicatedMergeTree.cpp', 'dbms/src/Storages/StorageReplicatedMergeTree.cpp'] 1 row in set. Elapsed: 0.041 sec. Processed 286.72 thousand rows, 20.11 MB (6.99 million rows/s., 490.36 MB/s.)



✎

We can also now use our function in our original query to get the full commit history of a file and solve our original question. Below we modify our query slightly to return two commits per filename.

SELECT time, substring(commit_hash, 1, 11) AS commit, path FROM git.file_changes WHERE path IN file_path_history('src/Storages/StorageReplicatedMergeTree.cpp') ORDER BY time DESC LIMIT 2 BY path ┌────────────────time─┬─commit──────┬─path─────────────────────────────────────────────┐ │ 2022-10-30 16:30:51 │ c68ab231f91 │ src/Storages/StorageReplicatedMergeTree.cpp │ │ 2022-10-23 16:24:20 │ b40d9200d20 │ src/Storages/StorageReplicatedMergeTree.cpp │ │ 2020-04-03 15:21:24 │ 38a50f44d34 │ dbms/Storages/StorageReplicatedMergeTree.cpp │ │ 2020-04-02 17:11:10 │ 5b133dd1ce7 │ dbms/Storages/StorageReplicatedMergeTree.cpp │ │ 2020-04-01 19:21:27 │ 1d5a77c1132 │ dbms/src/Storages/StorageReplicatedMergeTree.cpp │ │ 2020-04-01 13:43:09 │ 46322370c00 │ dbms/src/Storages/StorageReplicatedMergeTree.cpp │ └─────────────────────┴─────────────┴──────────────────────────────────────────────────┘ 6 rows in set. Elapsed: 0.079 sec. Processed 552.77 thousand rows, 47.69 MB (6.96 million rows/s., 600.46 MB/s.)



✎

In this post we've demonstrated how User Defined functions can be used to extend the behavior of ClickHouse to solve otherwise challenging queries. Now available in ClickHouse Cloud, we'd love to hear about your own usage of UDFs!

# data[].attributes.attachment_link.title
User-defined functions in ClickHouse Cloud

# data[].attributes.attachment_link.description
Unleash the potential of ClickHouse Cloud with User Defined Functions. Delve into our article & harness the limitless capabilities of UDFs in ClickHouse Cloud.

# data[].attributes.body
https://clickhouse.com/docs/en/sql-reference/functions/udf

# data[].attributes.attachment_link.readable_text
ClickHouse can call any external executable program or script to process data.

The configuration of executable user defined functions can be located in one or more xml-files. The path to the configuration is specified in the user_defined_executable_functions_config parameter.

A function configuration contains the following settings:

name - a function name.

command - script name to execute or command if execute_direct is false.

argument - argument description with the type, and optional name of an argument. Each argument is described in a separate setting. Specifying name is necessary if argument names are part of serialization for user defined function format like Native or JSONEachRow. Default argument name value is c + argument_number.

format - a format in which arguments are passed to the command.

return_type - the type of a returned value.

return_name - name of returned value. Specifying return name is necessary if return name is part of serialization for user defined function format like Native or JSONEachRow. Optional. Default value is result.

type - an executable type. If type is set to executable then single command is started. If it is set to executable_pool then a pool of commands is created.

max_command_execution_time - maximum execution time in seconds for processing block of data. This setting is valid for executable_pool commands only. Optional. Default value is 10.

command_termination_timeout - time in seconds during which a command should finish after its pipe is closed. After that time SIGTERM is sent to the process executing the command. Optional. Default value is 10.

command_read_timeout - timeout for reading data from command stdout in milliseconds. Default value 10000. Optional parameter.

command_write_timeout - timeout for writing data to command stdin in milliseconds. Default value 10000. Optional parameter.

pool_size - the size of a command pool. Optional. Default value is 16.

send_chunk_header - controls whether to send row count before sending a chunk of data to process. Optional. Default value is false.

execute_direct - If execute_direct = 1, then command will be searched inside user_scripts folder specified by user_scripts_path. Additional script arguments can be specified using whitespace separator. Example: script_name arg1 arg2. If execute_direct = 0, command is passed as argument for bin/sh -c. Default value is 1. Optional parameter.

lifetime - the reload interval of a function in seconds. If it is set to 0 then the function is not reloaded. Default value is 0. Optional parameter.

The command must read arguments from STDIN and must output the result to STDOUT. The command must process arguments iteratively. That is after processing a chunk of arguments it must wait for the next chunk.
Example
Query:

Result:

Creating test_function_sum manually specifying execute_direct to 0 using XML configuration. File test_function.xml (/etc/clickhouse-server/test_function.xml with default path settings).

Query:

Result:

Creating test_function_sum_json with named arguments and format JSONEachRow using XML configuration. File test_function.xml (/etc/clickhouse-server/test_function.xml with default path settings).

Query:

Result:

Executable user defined functions can take constant parameters configured in command setting (works only for user defined functions with executable type). It also requires the execute_direct option (to ensure no shell argument expansion vulnerability). File test_function_parameter_python.xml (/etc/clickhouse-server/test_function_parameter_python.xml with default path settings).

Query:

Result:

Some functions might throw an exception if the data is invalid. In this case, the query is canceled and an error text is returned to the client. For distributed processing, when an exception occurs on one of the servers, the other servers also attempt to abort the query.

For distributed query processing, as many stages of query processing as possible are performed on remote servers, and the rest of the stages (merging intermediate results and everything after that) are performed on the requestor server.

This means that functions can be performed on different servers. For example, in the query SELECT f(sum(g(x))) FROM distributed_table GROUP BY h(y),

if a distributed_table has at least two shards, the functions 'g' and 'h' are performed on remote servers, and the function 'f' is performed on the requestor server.

if a distributed_table has only one shard, all the 'f', 'g', and 'h' functions are performed on this shard's server.

If a function in a query is performed on the requestor server, but you need to perform it on remote servers, you can wrap it in an 'any' aggregate function or add it to a key in GROUP BY.

Custom functions from lambda expressions can be created using the CREATE FUNCTION statement. To delete these functions use the DROP FUNCTION statement.

# data[].attributes.attachment_link.title
UDFs User Defined Functions | ClickHouse Docs

# data[].attributes.body
https://clickhouse.com/docs/en/sql-reference/statements/explain

# data[].attributes.attachment_link.description
Shows the execution plan of a statement.

# data[].attributes.body
https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#primary-keys-and-indexes-in-queries

# data[].attributes.attachment_link.readable_text
The MergeTree engine and other engines of this family (*MergeTree) are the most robust ClickHouse table engines.

Engines in the MergeTree family are designed for inserting a very large amount of data into a table. The data is quickly written to the table part by part, then rules are applied for merging the parts in the background. This method is much more efficient than continually rewriting the data in storage during insert.

Main features:

Stores data sorted by primary key.

This allows you to create a small sparse index that helps find data faster.

Partitions can be used if the partitioning key is specified.

ClickHouse supports certain operations with partitions that are more efficient than general operations on the same data with the same result. ClickHouse also automatically cuts off the partition data where the partitioning key is specified in the query.

Data replication support.

The family of ReplicatedMergeTree tables provides data replication. For more information, see Data replication.

Data sampling support.

If necessary, you can set the data sampling method in the table.

For a description of parameters, see the CREATE query description.

ENGINE - Name and parameters of the engine. ENGINE = MergeTree(). The MergeTree engine does not have parameters.

ORDER BY - The sorting key.

A tuple of column names or arbitrary expressions. Example: ORDER BY (CounterID, EventDate).

ClickHouse uses the sorting key as a primary key if the primary key is not defined explicitly by the PRIMARY KEY clause.

Use the ORDER BY tuple() syntax, if you do not need sorting. See Selecting the Primary Key.

PARTITION BY - The partitioning key. Optional. In most cases, you don't need a partition key, and if you do need to partition, generally you do not need a partition key more granular than by month. Partitioning does not speed up queries (in contrast to the ORDER BY expression). You should never use too granular partitioning. Don't partition your data by client identifiers or names (instead, make client identifier or name the first column in the ORDER BY expression).

For partitioning by month, use the toYYYYMM(date_column) expression, where date_column is a column with a date of the type Date. The partition names here have the "YYYYMM" format.

PRIMARY KEY - The primary key if it differs from the sorting key. Optional.

By default the primary key is the same as the sorting key (which is specified by the ORDER BY clause). Thus in most cases it is unnecessary to specify a separate PRIMARY KEY clause.

SAMPLE BY - An expression for sampling. Optional.

If a sampling expression is used, the primary key must contain it. The result of a sampling expression must be an unsigned integer. Example: SAMPLE BY intHash32(UserID) ORDER BY (CounterID, EventDate, intHash32(UserID)).

TTL - A list of rules specifying storage duration of rows and defining logic of automatic parts movement between disks and volumes. Optional.

Expression must have one Date or DateTime column as a result. Example:

For more details, see TTL for columns and tables

Additional parameters that control the behavior of the MergeTree (optional):

index_granularity - Maximum number of data rows between the marks of an index. Default value: 8192. See Data Storage.

index_granularity_bytes - Maximum size of data granules in bytes. Default value: 10Mb. To restrict the granule size only by number of rows, set to 0 (not recommended). See Data Storage.

min_index_granularity_bytes - Min allowed size of data granules in bytes. Default value: 1024b. To provide a safeguard against accidentally creating tables with very low index_granularity_bytes. See Data Storage.

use_minimalistic_part_header_in_zookeeper - Storage method of the data parts headers in ZooKeeper. If use_minimalistic_part_header_in_zookeeper=1, then ZooKeeper stores less data. For more information, see the setting description in "Server configuration parameters".

merge_with_ttl_timeout - Minimum delay in seconds before repeating a merge with delete TTL. Default value: 14400 seconds (4 hours).

merge_with_recompression_ttl_timeout - Minimum delay in seconds before repeating a merge with recompression TTL. Default value: 14400 seconds (4 hours).

try_fetch_recompressed_part_timeout - Timeout (in seconds) before starting merge with recompression. During this time ClickHouse tries to fetch recompressed part from replica which assigned this merge with recompression. Default value: 7200 seconds (2 hours).

write_final_mark - Enables or disables writing the final index mark at the end of data part (after the last byte). Default value: 1. Don't turn it off.

merge_max_block_size - Maximum number of rows in block for merge operations. Default value: 8192.

storage_policy - Storage policy. See Using Multiple Block Devices for Data Storage.

min_bytes_for_wide_part, min_rows_for_wide_part - Minimum number of bytes/rows in a data part that can be stored in Wide format. You can set one, both or none of these settings. See Data Storage.

max_parts_in_total - Maximum number of parts in all partitions.

max_compress_block_size - Maximum size of blocks of uncompressed data before compressing for writing to a table. You can also specify this setting in the global settings (see max_compress_block_size setting). The value specified when table is created overrides the global value for this setting.

min_compress_block_size - Minimum size of blocks of uncompressed data required for compression when writing the next mark. You can also specify this setting in the global settings (see min_compress_block_size setting). The value specified when table is created overrides the global value for this setting.

max_partitions_to_read - Limits the maximum number of partitions that can be accessed in one query. You can also specify setting max_partitions_to_read in the global setting.
Example of Sections Setting
In the example, we set partitioning by month.

We also set an expression for sampling as a hash by the user ID. This allows you to pseudorandomize the data in the table for each CounterID and EventDate. If you define a SAMPLE clause when selecting the data, ClickHouse will return an evenly pseudorandom data sample for a subset of users.

The index_granularity setting can be omitted because 8192 is the default value.

Deprecated Method for Creating a Table

Do not use this method in new projects. If possible, switch old projects to the method described above.

MergeTree() Parameters

date-column - The name of a column of the Date type. ClickHouse automatically creates partitions by month based on this column. The partition names are in the "YYYYMM" format.

sampling_expression - An expression for sampling.

(primary, key) - Primary key. Type: Tuple()

index_granularity - The granularity of an index. The number of data rows between the "marks" of an index. The value 8192 is appropriate for most tasks.

Example
The MergeTree engine is configured in the same way as in the example above for the main engine configuration method.

A table consists of data parts sorted by primary key.

When data is inserted in a table, separate data parts are created and each of them is lexicographically sorted by primary key. For example, if the primary key is (CounterID, Date), the data in the part is sorted by CounterID, and within each CounterID, it is ordered by Date.

Data belonging to different partitions are separated into different parts. In the background, ClickHouse merges data parts for more efficient storage. Parts belonging to different partitions are not merged. The merge mechanism does not guarantee that all rows with the same primary key will be in the same data part.

Each data part is logically divided into granules. A granule is the smallest indivisible data set that ClickHouse reads when selecting data. ClickHouse does not split rows or values, so each granule always contains an integer number of rows. The first row of a granule is marked with the value of the primary key for the row. For each data part, ClickHouse creates an index file that stores the marks. For each column, whether it's in the primary key or not, ClickHouse also stores the same marks. These marks let you find data directly in column files.

The granule size is restricted by the index_granularity and index_granularity_bytes settings of the table engine. The number of rows in a granule lays in the [1, index_granularity] range, depending on the size of the rows. The size of a granule can exceed index_granularity_bytes if the size of a single row is greater than the value of the setting. In this case, the size of the granule equals the size of the row.

Take the (CounterID, Date) primary key as an example. In this case, the sorting and index can be illustrated as follows:

 CounterID: [aaaaaaaaaaaaaaaaaabbbbcdeeeeeeeeeeeeefgggggggghhhhhhhhhiiiiiiiiikllllllll] Date: [1111111222222233331233211111222222333211111112122222223111112223311122333] a,1 a,2 a,3 b,3 e,2 e,3 g,1 h,2 i,1 i,3 l,3 Marks numbers: 0 1 2 3 4 5 6 7 8 9 10

If the data query specifies:

Date = 3, the server reads the data in the range of marks [1, 10].

The examples above show that it is always more effective to use an index than a full scan.

A sparse index allows extra data to be read. When reading a single range of the primary key, up to index_granularity * 2 extra rows in each data block can be read.

Sparse indexes allow you to work with a very large number of table rows, because in most cases, such indexes fit in the computer's RAM.

ClickHouse does not require a unique primary key. You can insert multiple rows with the same primary key.

You can use Nullable-typed expressions in the PRIMARY KEY and ORDER BY clauses but it is strongly discouraged. To allow this feature, turn on the allow_nullable_key setting. The NULLS_LAST principle applies for NULL values in the ORDER BY clause.

The number of columns in the primary key is not explicitly limited. Depending on the data structure, you can include more or fewer columns in the primary key. This may:

Improve the performance of an index.

If the primary key is (a, b), then adding another column c will improve the performance if the following conditions are met:

There are queries with a condition on column c.

Long data ranges (several times longer than the index_granularity) with identical values for (a, b) are common. In other words, when adding another column allows you to skip quite long data ranges.

Improve data compression.

ClickHouse sorts data by primary key, so the higher the consistency, the better the compression.

Provide additional logic when merging data parts in the CollapsingMergeTree and SummingMergeTree engines.

In this case it makes sense to specify the sorting key that is different from the primary key.

A long primary key will negatively affect the insert performance and memory consumption, but extra columns in the primary key do not affect ClickHouse performance during SELECT queries.

You can create a table without a primary key using the ORDER BY tuple() syntax. In this case, ClickHouse stores data in the order of inserting. If you want to save data order when inserting data by INSERT ... SELECT queries, set max_insert_threads = 1.

To select data in the initial order, use single-threaded SELECT queries.

It is possible to specify a primary key (an expression with values that are written in the index file for each mark) that is different from the sorting key (an expression for sorting the rows in data parts). In this case the primary key expression tuple must be a prefix of the sorting key expression tuple.

This feature is helpful when using the SummingMergeTree and AggregatingMergeTree table engines. In a common case when using these engines, the table has two types of columns: dimensions and measures. Typical queries aggregate values of measure columns with arbitrary GROUP BY and filtering by dimensions. Because SummingMergeTree and AggregatingMergeTree aggregate rows with the same value of the sorting key, it is natural to add all dimensions to it. As a result, the key expression consists of a long list of columns and this list must be frequently updated with newly added dimensions.

In this case it makes sense to leave only a few columns in the primary key that will provide efficient range scans and add the remaining dimension columns to the sorting key tuple.

ALTER of the sorting key is a lightweight operation because when a new column is simultaneously added to the table and to the sorting key, existing data parts do not need to be changed. Since the old sorting key is a prefix of the new sorting key and there is no data in the newly added column, the data is sorted by both the old and new sorting keys at the moment of table modification.

For SELECT queries, ClickHouse analyzes whether an index can be used. An index can be used if the WHERE/PREWHERE clause has an expression (as one of the conjunction elements, or entirely) that represents an equality or inequality comparison operation, or if it has IN or LIKE with a fixed prefix on columns or expressions that are in the primary key or partitioning key, or on certain partially repetitive functions of these columns, or logical relationships of these expressions.

Thus, it is possible to quickly run queries on one or many ranges of the primary key. In this example, queries will be fast when run for a specific tracking tag, for a specific tag and date range, for a specific tag and date, for multiple tags with a date range, and so on.

Let's look at the engine configured as follows:

In this case, in queries:

ClickHouse will use the primary key index to trim improper data and the monthly partitioning key to trim partitions that are in improper date ranges.

The queries above show that the index is used even for complex expressions. Reading from the table is organized so that using the index can't be slower than a full scan.

In the example below, the index can't be used.

To check whether ClickHouse can use the index when running a query, use the settings force_index_by_date and force_primary_key.

The key for partitioning by month allows reading only those data blocks which contain dates from the proper range. In this case, the data block may contain data for many dates (up to an entire month). Within a block, data is sorted by primary key, which might not contain the date as the first column. Because of this, using a query with only a date condition that does not specify the primary key prefix will cause more data to be read than for a single date.

Consider, for example, the days of the month. They form a monotonic sequence for one month, but not monotonic for more extended periods. This is a partially-monotonic sequence. If a user creates the table with partially-monotonic primary key, ClickHouse creates a sparse index as usual. When a user selects data from this kind of table, ClickHouse analyzes the query conditions. If the user wants to get data between two marks of the index and both these marks fall within one month, ClickHouse can use the index in this particular case because it can calculate the distance between the parameters of a query and index marks.

ClickHouse cannot use an index if the values of the primary key in the query parameter range do not represent a monotonic sequence. In this case, ClickHouse uses the full scan method.

ClickHouse uses this logic not only for days of the month sequences, but for any primary key that represents a partially-monotonic sequence.

The index declaration is in the columns section of the CREATE query.

For tables from the *MergeTree family, data skipping indices can be specified.

These indices aggregate some information about the specified expression on blocks, which consist of granularity_value granules (the size of the granule is specified using the index_granularity setting in the table engine). Then these aggregates are used in SELECT queries for reducing the amount of data to read from the disk by skipping big blocks of data where the where query cannot be satisfied.

The GRANULARITY clause can be omitted, the default value of granularity_value is 1.
Example
Indices from the example can be used by ClickHouse to reduce the amount of data to read from disk in the following queries:

Data skipping indexes can also be created on composite columns:

Stores extremes of the specified expression (if the expression is tuple, then it stores extremes for each element of tuple), uses stored info for skipping blocks of data like the primary key.

Syntax: minmax

Stores unique values of the specified expression (no more than max_rows rows, max_rows=0 means "no limits"). Uses the values to check if the WHERE expression is not satisfiable on a block of data.

Syntax: set(max_rows)

Stores a Bloom filter for the specified columns. An optional false_positive parameter with possible values between 0 and 1 specifies the probability of receiving a false positive response from the filter. Default value: 0.025. Supported data types: Int*, UInt*, Float*, Enum, Date, DateTime, String, FixedString, Array, LowCardinality, Nullable, UUID and Map. For the Map data type, the client can specify if the index should be created for keys or values using mapKeys or mapValues function.

Syntax: bloom_filter([false_positive])

Stores a Bloom filter that contains all n-grams from a block of data. Only works with datatypes: String, FixedString and Map. Can be used for optimization of EQUALS, LIKE and IN expressions.

Syntax: ngrambf_v1(n, size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)

n - ngram size,

size_of_bloom_filter_in_bytes - Bloom filter size in bytes (you can use large values here, for example, 256 or 512, because it can be compressed well).

number_of_hash_functions - The number of hash functions used in the Bloom filter.

random_seed - The seed for Bloom filter hash functions.

Users can create UDF to estimate the parameters set of ngrambf_v1. Query statements are as follows:

To use those functions,we need to specify two parameter at least. For example, if there 4300 ngrams in the granule and we expect false positives to be less than 0.0001. The other parameters can be estimated by executing following queries:

Of course, you can also use those functions to estimate parameters by other conditions. The functions refer to the content here.

The same as ngrambf_v1, but stores tokens instead of ngrams. Tokens are sequences separated by non-alphanumeric characters.

Syntax: tokenbf_v1(size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)

Experimental indexes to support approximate nearest neighbor (ANN) search. See here for details.

An experimental inverted index to support full-text search. See here for details.

Conditions in the WHERE clause contains calls of the functions that operate with columns. If the column is a part of an index, ClickHouse tries to use this index when performing the functions. ClickHouse supports different subsets of functions for using indexes.

Indexes of type set can be utilized by all functions. The other index types are supported as follows:

Functions with a constant argument that is less than ngram size can't be used by ngrambf_v1 for query optimization.

(*) For hasTokenCaseInsensitive and hasTokenCaseInsensitiveOrNull to be effective, the tokenbf_v1 index must be created on lowercased data, for example INDEX idx (lower(str_col)) TYPE tokenbf_v1(512, 3, 0).

For example:

Projections are like materialized views but defined in part-level. It provides consistency guarantees along with automatic usage in queries.

Projections are not supported in the SELECT statements with the FINAL modifier.

A projection query is what defines a projection. It implicitly selects data from the parent table. Syntax

Projections can be modified or dropped with the ALTER statement.

Projections are stored inside the part directory. It's similar to an index but contains a subdirectory that stores an anonymous MergeTree table's part. The table is induced by the definition query of the projection. If there is a GROUP BY clause, the underlying storage engine becomes AggregatingMergeTree, and all aggregate functions are converted to AggregateFunction. If there is an ORDER BY clause, the MergeTree table uses it as its primary key expression. During the merge process the projection part is merged via its storage's merge routine. The checksum of the parent table's part is combined with the projection's part. Other maintenance jobs are similar to skip indices.

1. Check if the projection can be used to answer the given query, that is, it generates the same answer as querying the base table.

2. Select the best feasible match, which contains the least granules to read.

3. The query pipeline which uses projections will be different from the one that uses the original parts. If the projection is absent in some parts, we can add the pipeline to "project" it on the fly.

For concurrent table access, we use multi-versioning. In other words, when a table is simultaneously read and updated, data is read from a set of parts that is current at the time of the query. There are no lengthy locks. Inserts do not get in the way of read operations.

Reading from a table is automatically parallelized.

Determines the lifetime of values.

The TTL clause can be set for the whole table and for each individual column. Table-level TTL can also specify the logic of automatic moving data between disks and volumes, or recompressing parts where all the data has been expired.

Expressions must evaluate to Date or DateTime data type.
Syntax
Setting time-to-live for a column:

To define interval, use time interval operators, for example:

When the values in the column expire, ClickHouse replaces them with the default values for the column data type. If all the column values in the data part expire, ClickHouse deletes this column from the data part in a filesystem.

The TTL clause can't be used for key columns.
Examples
Table can have an expression for removal of expired rows, and multiple expressions for automatic move of parts between disks or volumes. When rows in the table expire, ClickHouse deletes all corresponding rows. For parts moving or recompressing, all rows of a part must satisfy the TTL expression criteria.

Type of TTL rule may follow each TTL expression. It affects an action which is to be done once the expression is satisfied (reaches current time):

DELETE - delete expired rows (default action);

RECOMPRESS codec_name - recompress data part with the codec_name;

TO DISK 'aaa' - move part to the disk aaa;

TO VOLUME 'bbb' - move part to the disk bbb;

GROUP BY - aggregate expired rows.

DELETE action can be used together with WHERE clause to delete only some of the expired rows based on a filtering condition:

GROUP BY expression must be a prefix of the table primary key.

If a column is not part of the GROUP BY expression and is not set explicitly in the SET clause, in result row it contains an occasional value from the grouped rows (as if aggregate function any is applied to it).
Examples
Creating a table, where the rows are expired after one month. The expired rows where dates are Mondays are deleted:

Creating a table, where expired rows are aggregated. In result rows x contains the maximum value across the grouped rows, y - the minimum value, and d - any occasional value from grouped rows.

Data with an expired TTL is removed when ClickHouse merges data parts.

When ClickHouse detects that data is expired, it performs an off-schedule merge. To control the frequency of such merges, you can set merge_with_ttl_timeout. If the value is too low, it will perform many off-schedule merges that may consume a lot of resources.

If you perform the SELECT query between merges, you may get expired data. To avoid it, use the OPTIMIZE query before SELECT.
See Also
In addition to local block devices, ClickHouse supports these storage types:

MergeTree family table engines can store data on multiple block devices. For example, it can be useful when the data of a certain table are implicitly split into "hot" and "cold". The most recent data is regularly requested but requires only a small amount of space. On the contrary, the fat-tailed historical data is requested rarely. If several disks are available, the "hot" data may be located on fast disks (for example, NVMe SSDs or in memory), while the "cold" data - on relatively slow ones (for example, HDD).

Data part is the minimum movable unit for MergeTree-engine tables. The data belonging to one part are stored on one disk. Data parts can be moved between disks in the background (according to user settings) as well as by means of the ALTER queries.

Disk - Block device mounted to the filesystem.

Default disk - Disk that stores the path specified in the path server setting.

Volume - Ordered set of equal disks (similar to JBOD).

Storage policy - Set of volumes and the rules for moving data between them.

The names given to the described entities can be found in the system tables, system.storage_policies and system.disks. To apply one of the configured storage policies for a table, use the storage_policy setting of MergeTree-engine family tables.

Disks, volumes and storage policies should be declared inside the <storage_configuration> tag either in a file in the config.d directory.

Configuration structure:

Tags:

<disk_name_N> - Disk name. Names must be different for all disks.

keep_free_space_bytes - the amount of free disk space to be reserved.

The order of the disk definition is not important.

Storage policies configuration markup:

Tags:

policy_name_N - Policy name. Policy names must be unique.

volume_name_N - Volume name. Volume names must be unique.

disk - a disk within a volume.

max_data_part_size_bytes - the maximum size of a part that can be stored on any of the volume's disks. If the a size of a merged part estimated to be bigger than max_data_part_size_bytes then this part will be written to a next volume. Basically this feature allows to keep new/small parts on a hot (SSD) volume and move them to a cold (HDD) volume when they reach large size. Do not use this setting if your policy has only one volume.

move_factor - when the amount of available space gets lower than this factor, data automatically starts to move on the next volume if any (by default, 0.1). ClickHouse sorts existing parts by size from largest to smallest (in descending order) and selects parts with the total size that is sufficient to meet the move_factor condition. If the total size of all parts is insufficient, all parts will be moved.

prefer_not_to_merge - Disables merging of data parts on this volume. When this setting is enabled, merging data on this volume is not allowed. This allows controlling how ClickHouse works with slow disks.

perform_ttl_move_on_insert - Disables TTL move on data part INSERT. By default (if enabled) if we insert a data part that already expired by the TTL move rule it immediately goes to a volume/disk declared in move rule. This can significantly slowdown insert in case if destination volume/disk is slow (e.g. S3). If disabled then already expired data part is written into a default volume and then right after moved to TTL volume.

Configuration examples:

In given example, the hdd_in_order policy implements the round-robin approach. Thus this policy defines only one volume ( single), the data parts are stored on all its disks in circular order. Such policy can be quite useful if there are several similar disks are mounted to the system, but RAID is not configured. Keep in mind that each individual disk drive is not reliable and you might want to compensate it with replication factor of 3 or more.

If there are different kinds of disks available in the system, moving_from_ssd_to_hdd policy can be used instead. The volume hot consists of an SSD disk ( fast_ssd), and the maximum size of a part that can be stored on this volume is 1GB. All the parts with the size larger than 1GB will be stored directly on the cold volume, which contains an HDD disk disk1. Also, once the disk fast_ssd gets filled by more than 80%, data will be transferred to the disk1 by a background process.

The order of volume enumeration within a storage policy is important. Once a volume is overfilled, data are moved to the next one. The order of disk enumeration is important as well because data are stored on them in turns.

When creating a table, one can apply one of the configured storage policies to it:

The default storage policy implies using only one volume, which consists of only one disk given in <path>. You could change storage policy after table creation with [ALTER TABLE ... MODIFY SETTING] query, new policy should include all old disks and volumes with same names.

The number of threads performing background moves of data parts can be changed by background_move_pool_size setting.

This example query shows how to attach a table stored at a URL and configure the remote storage within the query. The web storage is not configured in the ClickHouse configuration files; all the settings are in the CREATE/ATTACH query.

In this ATTACH TABLE query the UUID provided matches the directory name of the data, and the endpoint is the URL for the raw GitHub content.

This example query builds on the above dynamic disk configuration and shows how to use a local disk to cache data from a table stored at a URL. Neither the cache disk nor the web storage is configured in the ClickHouse configuration files; both are configured in the CREATE/ATTACH query settings.

In the settings highlighted below notice that the disk of type=web is nested within the disk of type=cache.

In the case of MergeTree tables, data is getting to disk in different ways:

In all these cases except for mutations and partition freezing, a part is stored on a volume and a disk according to the given storage policy:

1. The first volume (in the order of definition) that has enough disk space for storing a part (unreserved_space > current_part_size) and allows for storing parts of a given size (max_data_part_size_bytes > current_part_size) is chosen.

2. Within this volume, that disk is chosen that follows the one, which was used for storing the previous chunk of data, and that has free space more than the part size (unreserved_space - keep_free_space_bytes > current_part_size).

Under the hood, mutations and partition freezing make use of hard links. Hard links between different disks are not supported, therefore in such cases the resulting parts are stored on the same disks as the initial ones.

In the background, parts are moved between volumes on the basis of the amount of free space ( move_factor parameter) according to the order the volumes are declared in the configuration file. Data is never transferred from the last one and into the first one. One may use system tables system.part_log (field type = MOVE_PART) and system.parts (fields path and disk) to monitor background moves. Also, the detailed information can be found in server logs.

User can force moving a part or a partition from one volume to another using the query ALTER TABLE ... MOVE PART|PARTITION ... TO VOLUME|DISK ..., all the restrictions for background operations are taken into account. The query initiates a move on its own and does not wait for background operations to be completed. User will get an error message if not enough free space is available or if any of the required conditions are not met.

Moving data does not interfere with data replication. Therefore, different storage policies can be specified for the same table on different replicas.

After the completion of background merges and mutations, old parts are removed only after a certain amount of time ( old_parts_lifetime). During this time, they are not moved to other volumes or disks. Therefore, until the parts are finally removed, they are still taken into account for evaluation of the occupied disk space.

User can assign new big parts to different disks of a JBOD volume in a balanced way using the min_bytes_to_rebalance_partition_over_jbod setting.

MergeTree family table engines can store data to S3 using a disk with type s3.

Configuration markup:

Required parameters:

endpoint - S3 endpoint URL in path or virtual hosted styles. Endpoint URL should contain a bucket and root path to store data.

access_key_id - S3 access key id.

secret_access_key - S3 secret access key.

Optional parameters:

region - S3 region name.

support_batch_delete - This controls the check to see if batch deletes are supported. Set this to false when using Google Cloud Storage (GCS) as GCS does not support batch deletes and preventing the checks will prevent error messages in the logs.

use_environment_credentials - Reads AWS credentials from the Environment variables AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY and AWS_SESSION_TOKEN if they exist. Default value is false.

expiration_window_seconds - Grace period for checking if expiration-based credentials have expired. Optional, default value is 120.

connect_timeout_ms - Socket connect timeout in milliseconds. Default value is 10 seconds.

request_timeout_ms - Request timeout in milliseconds. Default value is 5 seconds.

retry_attempts - Number of retry attempts in case of failed request. Default value is 10.

single_read_retries - Number of retry attempts in case of connection drop during read. Default value is 4.

min_bytes_for_seek - Minimal number of bytes to use seek operation instead of sequential read. Default value is 1 Mb.

metadata_path - Path on local FS to store metadata files for S3. Default value is /var/lib/clickhouse/disks/<disk_name>/.

skip_access_check - If true, disk access checks will not be performed on disk start-up. Default value is false.

header - Adds specified HTTP header to a request to given endpoint. Optional, can be specified multiple times.

server_side_encryption_customer_key_base64 - If specified, required headers for accessing S3 objects with SSE-C encryption will be set.

server_side_encryption_kms_key_id - If specified, required headers for accessing S3 objects with SSE-KMS encryption will be set. If an empty string is specified, the AWS managed S3 key will be used. Optional.

server_side_encryption_kms_encryption_context - If specified alongside server_side_encryption_kms_key_id, the given encryption context header for SSE-KMS will be set. Optional.

server_side_encryption_kms_bucket_key_enabled - If specified alongside server_side_encryption_kms_key_id, the header to enable S3 bucket keys for SSE-KMS will be set. Optional, can be true or false, defaults to nothing (matches the bucket-level setting).

s3_max_put_rps - Maximum PUT requests per second rate before throttling. Default value is 0 (unlimited).

s3_max_get_rps - Maximum GET requests per second rate before throttling. Default value is 0 (unlimited).

This is the cache configuration from above:

These parameters define the cache layer:

type - If a disk is of type cache it caches mark and index files in memory.

disk - The name of the disk that will be cached.

Cache parameters:

path - The path where metadata for the cache is stored.

max_size - The size (amount of disk space) that the cache can grow to.

S3 disk can be configured as main or cold storage:

In case of cold option a data can be moved to S3 if local disk free size will be smaller than move_factor * disk_size or by TTL move rule.

MergeTree family table engines can store data to Azure Blob Storage using a disk with type azure_blob_storage.

As of February 2022, this feature is still a fresh addition, so expect that some Azure Blob Storage functionalities might be unimplemented.

Configuration markup:

Connection parameters:

storage_account_url - Required, Azure Blob Storage account URL, like http://account.blob.core.windows.net or http://azurite1:10000/devstoreaccount1.

container_name - Target container name, defaults to default-container.

container_already_exists - If set to false, a new container container_name is created in the storage account, if set to true, disk connects to the container directly, and if left unset, disk connects to the account, checks if the container container_name exists, and creates it if it doesn't exist yet.

Authentication parameters (the disk will try all available methods and Managed Identity Credential):

connection_string - For authentication using a connection string.

account_name and account_key - For authentication using Shared Key.

Limit parameters (mainly for internal usage):

s3_max_single_part_upload_size - Limits the size of a single block upload to Blob Storage.

min_bytes_for_seek - Limits the size of a seekable region.

max_single_read_retries - Limits the number of attempts to read a chunk of data from Blob Storage.

max_single_download_retries - Limits the number of attempts to download a readable buffer from Blob Storage.

thread_pool_size - Limits the number of threads with which IDiskRemote is instantiated.

s3_max_inflight_parts_for_one_file - Limits the number of put requests that can be run concurrently for one object.

Other parameters:

metadata_path - Path on local FS to store metadata files for Blob Storage. Default value is /var/lib/clickhouse/disks/<disk_name>/.

cache_enabled - Allows to cache mark and index files on local FS. Default value is true.

cache_path - Path on local FS where to store cached mark and index files. Default value is /var/lib/clickhouse/disks/<disk_name>/cache/.

skip_access_check - If true, disk access checks will not be performed on disk start-up. Default value is false.

Examples of working configurations can be found in integration tests directory (see e.g. test_merge_tree_azure_blob_storage or test_azure_blob_storage_zero_copy_replication).

:::note Zero-copy replication is not ready for production Zero-copy replication is disabled by default in ClickHouse version 22.8 and higher. This feature is not recommended for production use. :::

In this sample configuration:

the disk is of type hdfs

the data is hosted at hdfs://hdfs1:9000/clickhouse/

Web storage can be used for read-only purposes. An example use is for hosting sample data, or for migrating data.

In this sample configuration:

the disk is of type web

the data is hosted at http://nginx:80/test1/

a cache on local storage is used

_part - Name of a part.

_part_index - Sequential index of the part in the query result.

_partition_id - Name of a partition.

_part_uuid - Unique part identifier (if enabled MergeTree setting assign_part_uuids).

_partition_value - Values (a tuple) of a partition by expression.

_sample_factor - Sample factor (from the query).

# data[].attributes.attachment_link.description
The MergeTree engine and other engines of this family (*MergeTree) are the most robust ClickHouse table engines.

# data[].attributes.body
https://datasphere.yandex.ru/

# data[].attributes.attachment_link.description
Сервис для ML-разработки, объединяющий наиболее востребованные инструменты и ресурсы для машинного обучения – от эксперимента до запуска готовой модели в эксплуатацию.

# data[].attributes.body
https://github.com/adform/stream-loader

# data[].attributes.attachment_link.title
GitHub - adform/stream-loader: Components for building stream loaders from Kafka to arbitrary storages

# data[].attributes.attachment_link.description
Components for building stream loaders from Kafka to arbitrary storages - GitHub - adform/stream-loader: Components for building stream loaders from Kafka to arbitrary storages

# data[].attributes.body
https://clickhouse.com/blog/storing-traces-and-spans-open-telemetry-in-clickhouse

# data[].attributes.attachment_link.readable_text
Here at ClickHouse, we consider Observability to be just another real-time analytics problem. As a high-performance real-time analytics database, ClickHouse is used for many use cases, including real-time analytics for time series data. Its diversity of use cases has helped drive a huge range of analytical functions, which assist in querying most data types. These query features and high compression rates have increasingly led users to utilize ClickHouse to store Observability data. This data takes three common forms: logs, metrics, and traces. In this blog, the second in an Observability series, we explore how trace data can be collected, stored, and queried in ClickHouse.

We have focused this post on using OpenTelemetry to collect trace data for storage in ClickHouse. When combined with Grafana, and recent developments in the ClickHouse plugin, traces are easily visualized and can be combined with logs and metrics to obtain a deep understanding of your system behavior and performance when detecting and diagnosing issues.

We have attempted to ensure that any examples can be reproduced, and while this post focuses on data collection and visualization basics, we have included some tips on schema optimization. For example purposes, we have forked the official OpenTelemetry Demo, adding support for ClickHouse and including an OOTB Grafana dashboard for visualizing traces.

Telemetry is data emitted from a system about its behavior. This data can take the form of logs, metrics, and traces. A trace records the paths taken by requests (made by an application or end-user) as they propagate through multi-service architectures such as microservice and serverless applications. A single trace consists of multiple spans, each a unit of work or operation. The span provides details of an operation, principally the time it took, in addition to other metadata and related log messages. These spans are hierarchy related as a tree, with the first span relating the root and covering the entire trace from start to finish. Beneath this root and each subsequent span, child operations are captured. As we navigate the tree, we can see the child operations and steps that make up the above level. This gives us ever-increasing context as to the work performed by the original request. This is visualized below:

These traces, when combined with metrics and logs, can be critical in obtaining insights into the behavior of a system for the detection and resolution of issues.

The OpenTelemetry project is a vendor-neutral open-source framework consisting of SDKs, APIs, and components that allow the ingesting, transforming, and sending of Observability data to a backend. More specifically, this consists of several main components:

A set of specifications and conventions of how metrics, logs, and traces should be collected and stored. This includes recommendations for language-specific agents and a full specification for the OpenTelemetry Line Protocol (OTLP) based on protobuf. This allows data to be transmitted between services by providing a full description of the client-server interface and the message format.

Language-specific libraries and SDKs for instrumenting, generating, collecting, and exporting observability data. This is particularly relevant to the collection of trace data.

An OTEL Collector written in Golang provides a vendor-agnostic implementation of receiving, processing and exporting Observability data. The OTEL Collector provides a centralized processing gateway by supporting several input formats, such as Prometheus and OTLP, and a wide range of export targets, including ClickHouse.

In summary, OpenTelemetry standardizes the collection of logs, metrics, and traces. Importantly, it is not responsible for storing this data in an Observability backend - this is where ClickHouse comes in!

Trace data is typically represented as a single table, with each span a row, and thus can be considered just another real-time analytics problem. As well as providing high compression for this data type, ClickHouse provides a rich SQL interface with additional analytical functions that trivialize querying traces. When combined with Grafana, users have a highly cost-efficient way of storing and visualizing traces. While other stores may offer similar compression levels, ClickHouse is unique in combining this low latency querying as the world's fastest analytical database. In fact, these characteristics led to ClickHouse becoming a preferred backend for many commercial observability solutions like: Signoz.io, Highlight.io, qryn, BetterStack, or homegrown large-scale observability platforms like Uber, Cloudflare, or Gitlab.

Instrumentation libraries are provided for the most popular languages. These provide both automatic instrumentation of code, where an application/services framework is exploited to capture the most common metric and trace data, and manual instrumentation techniques. While automatic instrumentation is typically sufficient, the latter allows users to instrument-specific parts of their code, potentially in more detail - capturing application-specific metrics and trace information.

For the purposes of this blog, we are only interested in the capture of trace information. The OpenTelemetry demo application consists of a microservices architecture with many dependent services, each in a different language, to provide a reference for implementors. The simple example below shows the instrumentation of a Python Flask API to collect trace data:

A detailed guide on each library is well beyond the scope of this blog, and we encourage users to read the relevant documentation for their language.

The OTEL collector accepts data from Observability sources, such as trace data from an instrumentation library, processes this data, and exports it to the target backend. The OTEL Collector can also provide a centralized processing gateway by supporting several input formats, such as Prometheus and OTLP, and a wide range of export targets, including ClickHouse.

The Collector uses the concept of pipelines. These can be of type logs, metrics, or traces and consist of a receiver, processor, and exporter.

The receiver in this architecture acts as the input for OTEL data. This can be either via a pull or push model. While this can occur over a number of protocols, trace data from instrumentation libraries will be pushed via OTLP using either gRPC or HTTP. Processors subsequently run on this data providing filtering, batching, and enrichment capabilities. Finally, an exporter sends the data to a backend destination via either push or pull. In our case, we will push the data to ClickHouse.

Note that while more commonly used as a gateway/aggregator, handling tasks such as batching and retries, the collector can also be deployed as an agent itself - this is useful for log collection, as described in our previous post. OTLP represents the OpenTelemetry data standard for communication between gateway and agent instances, which can occur over gRPC or HTTP. For the purposes of trace collection, the collector is simply deployed as a gateway, however, as shown below:

Note that more advanced architectures are possible for higher load environments. We recommend this excellent video discussing possible options.

ClickHouse is supported in the OTEL exporter through a community contribution, with support for logs, traces, and metrics. Communication with ClickHouse occurs over the optimized native format and protocol via the official Go client. Before using the OpenTelemetry Collector, users should consider the following points:

The ClickHouse data model and schema that the agent uses are hard coded. As of the time of writing, there is no ability to change the types or codecs used. Mitigate this by creating the table before deploying the connector, thus enforcing your schema.

The exporter is not distributed with the core OTEL distribution but rather as an extension through the contrib image. Practically this means using the correct Docker image in any HELM chart. For leaner deployments, users can build a custom collector image with only the required components.

As of version 0.74, users should pre-create the database in ClickHouse before deployment if not set to the value of default (as used in the demo fork).

The exporter is in alpha, and users should adhere to the advice provided by OpenTelemetry.

OpenTelemetry provides a demo application giving a practical example of the implementation of OpenTelemetry. This is a distributed micro-services architecture that powers a web store selling telescopes. This e-commerce use-case is useful in creating an opportunity for a wide range of simple, understandable services, e.g., recommendations, payments, and currency conversion. The storefront is subjected to a load generator, which causes each instrumented service to generate logs, traces, and metrics. As well as providing a realistic example for practitioners to learn how to instrument in their preferred language, this demo also allows vendors to show off their OpenTelemetry integration with their Observability backend. In this spirit, we have forked this application and made the necessary changes to store trace data in ClickHouse.

Note the breadth of languages used in the above architecture and the number of components handling operations such as payments and recommendations. Users are recommended to check the code for the service in their preferred language. Due to the presence of a collector as a gateway, no changes have been made to any instrumentation code. This architectural separation is one of the clear benefits of OpenTelemetry - backends can be changed with just a change of the target exporter in the collector.

The demo uses a Docker container for each service. The demo can be deployed using docker compose and the steps outlined in the official documentation, substituting the ClickHouse fork for the original repository.

git clone https://github.com/ClickHouse/opentelemetry-demo.git cd opentelemetry-demo/ docker compose up --no-build

We have modified the docker-compose file to include a ClickHouse instance in which data will be stored, available to the other services as clickhouse.

The demo can easily be deployed in Kubernetes using the official instructions. We recommend copying the values file and modifying the collector configuration. A sample values file, which sends all spans to a ClickHouse Cloud instance, can be found here. This can be downloaded and deployed with a modified helm command, i.e.,

helm install -f values.yaml my-otel-demo open-telemetry/opentelemetry-demo

In this post, we focus on exporting traces only. While logs and metrics can also be stored in ClickHouse, we use the default configuration for simplicity. Logs are not enabled by default, and metrics are sent to Prometheus.

To send trace data to ClickHouse, we must add a custom OTEL Collector configuration via the file otel-config-extras.yaml. This will be merged with the main configuration, overriding any existing declarations. The additional configuration is shown below:

The main changes here are configuring ClickHouse as an exporter. A few key settings here:

The endpoint setting specifies the ClickHouse host and port. Note communication occurs over TCP (port 9000). For secure connections, this should be 9440 with a secure=true parameter, e.g., 'clickhouse://&lt;username>:&lt;password>@&lt;host>:9440?secure=true'. Alternatively, use the dsn parameter. Note we connect to host clickhouse here. This is the clickhouse container, added to the local deployment docker image. Feel free to modify this path, e.g., to point to a ClickHouse Cloud cluster.

ttl_days - This controls data retention in ClickHouse via the TTL feature. See the section "Schema" below.

Our traces pipeline utilizes the OTLP receiver to receive trace data from the instrumentation libraries. This pipeline then passes this data to two processors:

A batch processor is responsible for ensuring INSERTs occur at most every 5s or when the batch size reaches 100k. This ensures inserts are batched efficiently.

A spanmetrics processor. This aggregates request, error, and metrics from trace data, forwarding it to the metrics pipeline. We will utilize this in a later post on metrics.

Once deployed, we can confirm trace data is being sent to ClickHouse using a simple SELECT on the table otel_traces. This represents the main data to which all spans are sent. Note we access the container using the clickhouse-client (it should be exposed on the host on the default 9000 port).

Each row represents a span, some of which are also root spans. There are some key fields, which with a basic understanding, will allow us to construct useful queries. A full description of trace metadata is available here:

TraceId - The Trace Id represents the trace that the Span is a part of.

SpanId - A Span's unique Id

ParentSpanId - The span id of the Span's parent span. This allows a trace call history to be constructed. This will be empty for root spans.

SpanName - the name of the operation

SpanKind - When a span is created, its Kind is either a Client, Server, Internal, Producer, or Consumer. This Kind hints to the tracing backend as to how the trace should be assembled. It effectively describes the relationship of the Span to its children and parents.

ServiceName - the name of the service, e.g., Adservice, from which the Span originates.

ResourceAttributes - key-value pairs that contain metadata that you can use to annotate a Span to carry information about the operation it is tracking. This might, for example, contain Kubernetes information, e.g., pod name or values concerning the host. Note our schema forces keys and values to be both String with a Map type.

SpanAttributes - additional span level attributes, e.g., thread.id.

Duration - duration of the Span in nanoseconds.

StatusCode - Either UNSET, OK, or ERROR. The latter will be set when there is a known error in the application code, such as an exception.

- While possibly inappropriate for dashboard overviews, these can interest the application developer. This can be thought of as a structured annotation on a Span, typically used to denote a meaningful, singular point during the Span's duration, e.g., when a page becomes interactive. The Events.Timestamp, Events.Name, and Events.Attributes can be used to reconstruct the full event - note this relies on array positions.

- These imply a casual relationship to another span. For example, these might be asynchronous operations executed as a result of this specific operation. A processing job that is queued due to a request operation might be an appropriate span link. Here the developer might link the last Span from the first trace to the first Span in the second trace to causally associate them. In the ClickHouse schema, we again rely on Array types and associating the positions of the columns Links.TraceId, Links.SpanId, and Links.Attributes.

Note that the collector is opinionated on the schema, including enforcing specific codecs. While these represent sensible choices for the general case, they prevent users from tuning the configuration to their needs via collector configuration. Users wishing to modify the codecs or the ORDER BY key, e.g., fit user-specific access patterns, should pre-create the table in advance.

Other than TTL (see below) there are some important observations regarding this schema:

The ORDER BY clause in our schema determines how our data is sorted and stored on disk. This will also control the construction of our sparse index and, most importantly, directly impact our compression levels and query performance. The current clause of (ServiceName, SpanName, toUnixTimestamp(Timestamp), TraceId) sorts the data in order of right to left and optimizes for queries which first filter by ServiceName. Filter restrictions by later-ordered columns will become increasingly ineffective. If your access patterns differ due to differences in your diagnosis workflows, you might modify this order and the columns used. When doing this, consider best practices to ensure the key is optimally exploited.

PARTITION BY - this clause causes a physical separation of the data on disk. While useful for the efficient deletion of data (see TTL below), it can potentially positively and negatively impact query performance. Based on the partition expression toDate(Timestamp), which creates a partition by day, queries that target the most recent data, e.g., the last 24 hours, will benefit. Queries over many partitions/days (only likely if you expand your retention beyond the default of 3 days) will conversely potentially be negatively impacted. If you expand your data retention period to months or years or have access patterns that need to target a wider time range, consider using a different expression, e.g., partition by week, if you have a TTL of a year.

Map - the Map type is used extensively in the above schema for attributes. This has been selected as the keys here are dynamic and application specific. The Map type's flexibility here is useful but at some cost. Accessing a map key requires the entire column to be read and loaded. Accessing the key of a map will therefore incur a greater cost than if the key was an explicit column at the root - especially if the map is large with many keys. The difference in performance here will depend on the size of the map but can be considerable. To address this, users should materialize frequently queried map key/value pairs to columns on the root. These materialized columns will, in turn, be populated at INSERT time from the corresponding map value and be available for fast access. We show an example below where we materialize the key host.name from the Map column ResourceAttributes to the root column Host:

Alternatively, this can be applied retrospectively once data has been inserted, and your access patterns are identified:

ALTER TABLE otel_traces ADD COLUMN HostName String MATERIALIZED ResourceAttributes['host.name']; ALTER TABLE otel_traces MATERIALIZE COLUMN HostName;

This process requires a mutation which can be I/O intensive and should be scheduled with caution. The Map type additionally requires the values to be the same type - String in our case. This loss of type information can require casts at query time. Furthermore, users should know the required syntax to access map keys - see "Querying Traces".

Bloom filters - To compensate for the restrictive ORDER BY key, the schema creates several data-skipping bloom indices. These are designed to speed up queries that either filter by trace id or the maps or keys of our attributes. Generally, secondary indices are effective when a strong correlation between the primary key and the targeted column/expression exists, or a value is very sparse in the data. This ensures that when applying a filter matching this expression, granules on disk that have a reasonable chance of not containing the target value can be skipped. For our specific schema, our TraceId should be very sparse and correlated with the ServiceName of the primary key. Likewise, our attribute keys and values will be correlated with the ServiceName and SpanName columns of the primary key. Generally, we consider these to be good candidates for bloom filters. The TraceId index is highly effective, but the others have not been tested under real-world workloads, so could potentially be a premature optimization until evidence suggests otherwise. We will evaluate the scalability of this model in a future post, stay tuned!

Via the collector parameter ttl_days, the user is able to control the expiration of data through ClickHouse's TTL functionality. This value is reflected in the expression TTL toDateTime(Timestamp) + toIntervalDay(3), which defaults to 3. Data older than this will be deleted based on an asynchronous background process. For more details on TTL, see here.

The schemas above use PARTITION BY to assist TTL. Specifically, this allows a day's worth of data to be efficiently deleted when combined with the parameter ttl_only_drop_parts=1. As noted above, this may positively and negatively impact queries.

In addition to the main table, the ClickHouse exporter creates a m aterialized view. A materialized view is a special trigger that stores the result of a SELECT query on data as it is inserted into a target table. This target table can summarize data (using an aggregation) in a format optimized for specific queries. In the exporter's case, the following view is created:

This specific materialized view is running a GROUP BY TraceId and identifying the max and min timestamp per id. This executes on every block of data (potentially millions of rows) inserted into the table otel_traces. This summarized data is, in turn, inserted into a target table otel_traces_trace_id_ts. Below we show several rows from this table and its schema:

As shown, the target table otel_traces_trace_id_ts uses (TraceId,toUnixTimestamp(Start)) for its ORDER BY key. This thus allows users to quickly identify a specific trace's time range.

We explore the value of this materialized view in "Querying Traces" below but have found its value limited in speeding up wider queries performing TraceId lookups. However, it does provide an excellent getting started example from which users can take inspiration.

Users may wish to extend or modify this materialized view. For example, an array of ServiceName could be added to the materialized views aggregation and target table to allow fast identification of a traces service. This can be achieved by pre-creating the table and materialized view before deploying the collector or alternatively modifying the view and table post-creation. Users can also attach new materialized views to the main table to address other access pattern requirements. See our recent blog for further details.

Finally, the above capability could also be implemented using projections. While these don't provide all of the capabilities of Materialized views, they are directly included in the table definition. Unlike Materialized Views, projections are updated atomically and kept consistent with the main table, with ClickHouse automatically choosing the optimal version at query time.

The docs for the exporter provide some excellent getting-started queries. Users needing further inspiration can refer to the queries in the dashboard we present below. A few important concepts when querying for traces:

TraceId look-ups on the main otel_traces table can potentially be expensive, despite the bloom filter. Should you need to drill down on a specific trace, the otel_traces_trace_id_ts table can potentially be used to identify the time range of the trace - as noted above. This time range then be applied as an additional filter to the otel_traces table, which includes the timestamp in the ORDER BY key. The query can be further optimized if the ServiceName is applied as a filter to the query (although this will limit to spans from a specific service). Consider the two query variants below and their respective timings, both of which return the spans associated with a trace.

Using only the otel_traces table:

When exploiting our otel_traces_trace_id_ts table and using the resulting times to apply a filter:

Our data volume here is small (around 200m spans and 125GB of data), so absolute timings and differences are low. While we expect these differences to widen on larger datasets, our testing suggests this materialized view provides only moderate speedups (note the small difference in rows read) - unsurprising as the Timestamp column is the third key in the otel_traces table ORDER BY and can thus, at best, be used for a generic exclusion search. The otel_traces table is also already significantly benefiting from the bloom filter. A full EXPLAIN of the differences in these queries shows a small difference in the number of granules read post-filtering. For this reason, we consider the use of this materialized view to be an unnecessary optimization in most cases when balanced against the increase in query complexity, although some users may find it useful for performance-critical scenarios. In later posts, we will explore the possibility of using projections to accelerate lookups by TraceId.

The collector utilizes the Map data type for attributes. Users can use a map notation to access the nested keys in addition to specialized ClickHouse map functions if filtering or selecting these columns. As noted earlier, if you frequently access these keys, we recommend materializing them as an explicit column on the table. Below we query spans from a specific host, grouping by the hour and the language of the service. We compute percentiles of span duration for each bucket - useful in any issue diagnosis.

Identifying the available map keys for querying can be challenging - especially if application developers have added custom metadata. Using an aggregator combinator function, the following query identifies the keys within the ResourceAttributes column. Adapt to other columns, e.g., SpanAttributes, as required.

We recommend Grafana for visualizing and exploring trace data using the official ClickHouse plugin. Previous posts and videos have explored this plugin in depth. Recently we have enhanced the plugin to allow visualization of traces using the Trace Panel. This is supported as both a visualization and as a component in Explore. This panel has strict naming and type requirements for columns which, unfortunately, is not aligned with the OTEL specification at the time of writing. The following query produces the appropriate response for a trace to be rendered in the Trace visualization:

Using variables and data links in Grafana, users can produce complex workflows where visualizations can be filtered interactivity. The following dashboard contains several visualizations:

A overview of service request volume as a stacked bar

99th percentile of latency of each service as a multi-line

Error rates per service as a bar

A list of traces aggregated by traceId - the service is here the first in the span chain.

A Trace Panel that populates when we filter to a specific trace.

This dashboard gives us some basic diagnostic capabilities around errors and performance. The OpenTelemetry demo has existing scenarios where the user can enable specific issues on the service. One of these scenarios involves a memory leak in the recommendation service. Without metrics, we can't complete the entire issue resolution flow but can identify problematic traces. We show this below:

This dashboard is now packaged with the plugin, and available with the demo.

The above queries can be quite complex. For example, notice how we are forced to utilize an arrayMap function to ensure the attributes are correctly structured. We could defer this work to a materialized or default column at query time, thus simplifying the query. However, this will still require significant SQL. This can be especially tedious when visualizing a trace in the Explore view.

To simplify query syntax, ClickHouse offers parameterized views. Parametrized views are similar to normal views but can be created with parameters that are not resolved immediately. These views can be used with table functions, which specify the name of the view as the function name and the parameter values as its arguments. This can dramatically reduce the end user's required syntax in ClickHouse. Below we create a view that accepts a trace id and returns the results required for the Trace View. Although support for CTEs in parametized views was recently added, but below we use the simpler query from earlier:

To run this view, we simply pass a trace id e.g.,

This can significantly reduce the complexity of querying for traces. Below we use the Explore view to query for a specific trace. Notice the need to set the Format value to Trace to cause rendering of the trace:

Parametrized views are best employed for common workloads where users perform common tasks that require ad-hoc analysis, such as inspecting a specific trace.

One of the benefits of ClickHouse for storing trace data is its high compression. Using the query below, we can see we achieve compression rates of 9x-10x on the trace data generated by this demo. This dataset was generated by running the demo while subjected to 2000 virtual users for 24 hours using the load generator service provided. We have made this dataset available for public use. This can be inserted using the steps here. For hosting this data, we recommend a development service in ClickHouse Cloud (16GB, two cores), which is more than sufficient for a dataset of this size.

We explore schema optimization and how this compression rate can be further improved in a later blog post in this series.

The current ClickHouse Exporter is in alpha. This state reflects the relative recency of its release and maturity. While we plan to invest in this exporter, we have identified a number of challenges and possible improvements:

Schema - The schema contains a number of optimizations that potentially be premature. The use of bloom filters may be unnecessary for some workloads. As shown below, these consume space (around 1% of the total data size).

We have found the filter on the TraceId column to be effective and a worthy addition to the schema. This seems to negate most of the benefit of the materialized view, which adds questionable value for the additional query and maintenance complexity. However, it provides an excellent example of how materialized views can potentially be applied to accelerate queries. We have insufficient evidence to testify to the value of other bloom filters and would recommend users experiment.

High memory - We have found the OTEL collector to be very memory intensive. In the earlier configuration, we use the batch processor to send data to ClickHouse after 5s or when the batch reaches 100,000 rows. While this optimizes for ClickHouse inserts and adheres to best practices, it can be very memory intensive under high load - especially when collecting logs. This can be mitigated by reducing the flush time and/or batch size. Be aware that this requires tuning as it may cause e xcessive parts to accumulate in ClickHouse. Alternatively, users may wish to use asynchronous inserts to send data. This will reduce the batch size but is only supported over HTTP in the exporter. To activate, use the connection_params in the exporter config, e.g.,

Note that this will not be as efficient for inserts to ClickHouse.

End-to-end delivery - We currently see no support for end-to-end delivery guarantees. i.e., the application SDKs will consider a trace sent once the Collector receives it. If the OTEL Collector crashes, batches currently in memory will be lost. This can be mitigated by reducing the batch size (see above). However, users may also wish to consider alternative architectures involving Kafka (see receiver and exporter) or equivalent persistent queues if higher delivery guarantees are required. We have not explored the recent persistent queues feature, which is in alpha but promises to improve resilience.

Scaling - The above deployment only utilizes a single collector. In a high-volume production environment, it is likely users would need to deploy multiple collectors behind a load balancer. The OTEL collector supports this using a Trace Id/Service Name load-balancing exporter. This ensures spans from the same trace are forwarded to the same collector. Note that we have also made no effort to tune agents or measure their resource overhead - something we recommend users research or do before production deployment. We plan to explore these topics in later posts.

Sampling - Our current implementation results in all data being stored in ClickHouse. While ClickHouse offers fantastic compression, we appreciate that users will wish to employ sampling techniques. This allows only a subset of traces to be stored, thus reducing hardware requirements. Be aware this complicates scaling. We will address this in a later post.

This blog post shows how traces can easily be collected and stored in ClickHouse using OpenTelemetry. We have forked the OpenTelemetry demo to support ClickHouse, touched on queries and visualization techniques using Grafana, and highlighted some approaches to reducing query complexity and some of the future work for the project. For further reading, we encourage users to explore topics beyond this post, such as how the OTEL collector can be deployed at scale, handling backpressure, and delivery guarantees. We will explore these topics in a later post and add metrics data to our ClickHouse instance before also exploring how schemas can be optimized, and data managed with lifecycle features.

# data[].attributes.attachment_link.title
Building an Observability Solution with ClickHouse - Part 2 - Traces

# data[].attributes.attachment_link.description
Continuing our blog series on building an Observability solution with ClickHouse we focus on storing traces in ClickHouse with OpenTelemetry.

# data[].attributes.body
https://housepower.github.io/clickhouse_sinker/guide/run.html#example

# data[].attributes.attachment_link.readable_text
Requirements

Note: Ensure clickhouse-server and kafka work before running clickhouse_sinker.

Configs

There are two ways to get config: a local single config, or Nacos.

Read more detail descriptions of config in here

Example

Let's follow up a piece of the systest script.

Run as a daemon

On systemd managed Linux OSs such as RHEL, Debian and their variants, it's doable to run clickhouse_sinker as a system service to achieve auto-restart, coredump management etc.

Create /etc/systemd/system/sinker_metric.service

[Unit] Description=ck-sink-metric Requires=network-online.target After=network-online.target [Service] Type=simple User=eoi LimitCORE=infinity Environment="GOTRACEBACK=crash" ExecStart=/data02/app/sinker/sinker/clickhouse_sinker --local-cfg-file=/data02/app/sinker/sinker/ck-sink-metric.json --log-paths=/data02/app/sinker/sinker/logs/sinker_metric.log Restart=on-failure RestartSec=3s StartLimitInterval=0 [Install] WantedBy=multi-user.target

Note:

Change pathes in ExecStart as necessary.

User=eoi means to run service as non-root for security reason.

LimitCORE=infinity for service is equivalent to ulimit -c unlimited for non-service.

env GOTRACEBACK=crash is required for Go applications to dump core. Refers to https://pkg.go.dev/runtime.

Modify /etc/sysctl.conf
kernel.core_pattern = |/usr/lib/systemd/systemd-coredump %p %u %g %s %t
Run sysctl -p.

Modify etc/systemd/coredump.conf

[Coredump] ProcessSizeMax=50G ExternalSizeMax=50G

Manage clickhouse-sinker service

To start, systemctl start sinker_metric

To stop, systemctl stop sinker_metric

To view status, systemctl status sinker_metric

Manage coredumps with coredumpctl

Coredumps are stored under /var/lib/systemd/coredump. Refers to core(5), systemd.exec(5), systemd-coredump(8), coredump.conf(5).

# data[].attributes.attachment_link.description
clickhouse_sinker a tool to sink the data into ClickHouse

# data[].attributes.body
https://clickhouse.com/docs/en/operations/opentelemetry

# data[].attributes.attachment_link.readable_text
OpenTelemetry is an open standard for collecting traces and metrics from the distributed application. ClickHouse has some support for OpenTelemetry.

ClickHouse accepts trace context HTTP headers, as described by the W3C recommendation. It also accepts trace context over a native protocol that is used for communication between ClickHouse servers or between the client and server. For manual testing, trace context headers conforming to the Trace Context recommendation can be supplied to clickhouse-client using --opentelemetry-traceparent and --opentelemetry-tracestate flags.

If no parent trace context is supplied or the provided trace context does not comply with W3C standard above, ClickHouse can start a new trace, with probability controlled by the opentelemetry_start_trace_probability setting.

The trace context is propagated to downstream services in the following cases:

ClickHouse creates trace spans for each query and some of the query execution stages, such as query planning or distributed queries.

To be useful, the tracing information has to be exported to a monitoring system that supports OpenTelemetry, such as Jaeger or Prometheus. ClickHouse avoids a dependency on a particular monitoring system, instead only providing the tracing data through a system table. OpenTelemetry trace span information required by the standard is stored in the system.opentelemetry_span_log table.

The table must be enabled in the server configuration, see the opentelemetry_span_log element in the default config file config.xml. It is enabled by default.

The tags or attributes are saved as two parallel arrays, containing the keys and values. Use ARRAY JOIN to work with them.

At the moment, there is no ready tool that can export the tracing data from ClickHouse to a monitoring system.

For testing, it is possible to setup the export using a materialized view with the URL engine over the system.opentelemetry_span_log table, which would push the arriving log data to an HTTP endpoint of a trace collector. For example, to push the minimal span data to a Zipkin instance running at http://localhost:9411, in Zipkin v2 JSON format:

In case of any errors, the part of the log data for which the error has occurred will be silently lost. Check the server log for error messages if the data does not arrive.

# data[].attributes.attachment_link.title
[experimental] Tracing ClickHouse with OpenTelemetry | ClickHouse Docs

# data[].attributes.attachment_link.description
OpenTelemetry is an open standard for collecting traces and metrics from the distributed application. ClickHouse has some support for OpenTelemetry.

# data[].attributes.body
https://clickhouse.com/docs/en/interfaces/third-party/integrations

# data[].attributes.attachment_link.title
Integration Libraries from Third-party Developers | ClickHouse Docs

# data[].attributes.attachment_link.description
ClickHouse, Inc. does not maintain the tools and libraries listed below and haven't done extensive testing to ensure their quality.

# data[].attributes.headline
Understanding ClickHouse Data Skipping Indexes

# data[].attributes.subject
Understanding ClickHouse Data Skipping Indexes

# data[].attributes.body
https://clickhouse.com/docs/en/optimize/skipping-indexes

# data[].attributes.attachment_link.readable_text
Many factors affect ClickHouse query performance. The critical element in most scenarios is whether ClickHouse can use the primary key when evaluating the query WHERE clause condition. Accordingly, selecting a primary key that applies to the most common query patterns is essential for effective table design.

Nevertheless, no matter how carefully tuned the primary key, there will inevitably be query use cases that can not efficiently use it. Users commonly rely on ClickHouse for time series type data, but they often wish to analyze that same data according to other business dimensions, such as customer id, website URL, or product number. In that case, query performance can be considerably worse because a full scan of each column value may be required to apply the WHERE clause condition. While ClickHouse is still relatively fast in those circumstances, evaluating millions or billions of individual values will cause "non-indexed" queries to execute much more slowly than those based on the primary key.

In a traditional relational database, one approach to this problem is to attach one or more "secondary" indexes to a table. This is a b-tree structure that permits the database to find all matching rows on disk in O(log(n)) time instead of O(n) time (a table scan), where n is the number of rows. However, this type of secondary index will not work for ClickHouse (or other column-oriented databases) because there are no individual rows on the disk to add to the index.

Instead, ClickHouse provides a different type of index, which in specific circumstances can significantly improve query speed. These structures are labeled "Skip" indexes because they enable ClickHouse to skip reading significant chunks of data that are guaranteed to have no matching values.

Users can only employ Data Skipping Indexes on the MergeTree family of tables. Each data skipping has four primary arguments:

Index name. The index name is used to create the index file in each partition. Also, it is required as a parameter when dropping or materializing the index.

Index expression. The index expression is used to calculate the set of values stored in the index. It can be a combination of columns, simple operators, and/or a subset of functions determined by the index type.

TYPE. The type of index controls the calculation that determines if it is possible to skip reading and evaluating each index block.

GRANULARITY. Each indexed block consists of GRANULARITY granules. For example, if the granularity of the primary table index is 8192 rows, and the index granularity is 4, each indexed "block" will be 32768 rows.

When a user creates a data skipping index, there will be two additional files in each data part directory for the table.

skpidx{index_name}.idx, which contains the ordered expression values

skpidx{index_name}.mrk2, which contains the corresponding offsets into the associated data column files.

If some portion of the WHERE clause filtering condition matches the skip index expression when executing a query and reading the relevant column files, ClickHouse will use the index file data to determine whether each relevant block of data must be processed or can be bypassed (assuming that the block has not already been excluded by applying the primary key). To use a very simplified example, consider the following table loaded with predictable data.

When executing a simple query that does not use the primary key, all 100 million entries in the my_value column are scanned:

Now add a very basic skip index:

Normally skip indexes are only applied on newly inserted data, so just adding the index won't affect the above query.

To index already existing data, use this statement:

Rerun the query with the newly created index:

Instead of processing 100 million rows of 800 megabytes, ClickHouse has only read and analyzed 32768 rows of 360 kilobytes -- four granules of 8192 rows each.

In a more visual form, this is how the 4096 rows with a my_value of 125 were read and selected, and how the following rows were skipped without reading from disk:

Users can access detailed information about skip index usage by enabling the trace when executing queries. From clickhouse-client, set the send_logs_level:

This will provide useful debugging information when trying to tune query SQL and table indexes. From the above above example, the debug log shows that the skip index dropped all but two granules:

This lightweight index type requires no parameters. It stores the minimum and maximum values of the index expression for each block (if the expression is a tuple, it separately stores the values for each member of the element of the tuple). This type is ideal for columns that tend to be loosely sorted by value. This index type is usually the least expensive to apply during query processing.

This type of index only works correctly with a scalar or tuple expression -- the index will never be applied to expressions that return an array or map data type.

This lightweight index type accepts a single parameter of the max_size of the value set per block (0 permits an unlimited number of discrete values). This set contains all values in the block (or is empty if the number of values exceeds the max_size). This index type works well with columns with low cardinality within each set of granules (essentially, "clumped together") but higher cardinality overall.

The cost, performance, and effectiveness of this index is dependent on the cardinality within blocks. If each block contains a large number of unique values, either evaluating the query condition against a large index set will be very expensive, or the index will not be applied because the index is empty due to exceeding max_size.

A Bloom filter is a data structure that allows space-efficient testing of set membership at the cost of a slight chance of false positives. A false positive is not a significant concern in the case of skip indexes because the only disadvantage is reading a few unnecessary blocks. However, the potential for false positives does mean that the indexed expression should be expected to be true, otherwise valid data may be skipped.

Because Bloom filters can more efficiently handle testing for a large number of discrete values, they can be appropriate for conditional expressions that produce more values to test. In particular, a Bloom filter index can be applied to arrays, where every value of the array is tested, and to maps, by converting either the keys or values to an array using the mapKeys or mapValues function.

There are three Data Skipping Index types based on Bloom filters:

The basic bloom_filter which takes a single optional parameter of the allowed "false positive" rate between 0 and 1 (if unspecified, .025 is used).

The specialized tokenbf_v1. It takes three parameters, all related to tuning the bloom filter used: (1) the size of the filter in bytes (larger filters have fewer false positives, at some cost in storage), (2) number of hash functions applied (again, more hash filters reduce false positives), and (3) the seed for the bloom filter hash functions. See the calculator here for more detail on how these parameters affect bloom filter functionality. This index works only with String, FixedString, and Map datatypes. The input expression is split into character sequences separated by non-alphanumeric characters. For example, a column value of This is a candidate for a "full text" search will contain the tokens This is a candidate for full text search. It is intended for use in LIKE, EQUALS, IN, hasToken() and similar searches for words and other values within longer strings. For example, one possible use might be searching for a small number of class names or line numbers in a column of free form application log lines.

The specialized ngrambf_v1. This index functions the same as the token index. It takes one additional parameter before the Bloom filter settings, the size of the ngrams to index. An ngram is a character string of length n of any characters, so the string A short string with an ngram size of 4 would be indexed as:

This index can also be useful for text searches, particularly languages without word breaks, such as Chinese.

The core purpose of data-skipping indexes is to limit the amount of data analyzed by popular queries. Given the analytic nature of ClickHouse data, the pattern of those queries in most cases includes functional expressions. Accordingly, skip indexes must interact correctly with common functions to be efficient. This can happen either when:

data is inserted and the index is defined as a functional expression (with the result of the expression stored in the index files), or

the query is processed and the expression is applied to the stored index values to determine whether to exclude the block.

Each type of skip index works on a subset of available ClickHouse functions appropriate to the index implementation listed here. In general, set indexes and Bloom filter based indexes (another type of set index) are both unordered and therefore do not work with ranges. In contrast, minmax indexes work particularly well with ranges since determining whether ranges intersect is very fast. The efficacy of partial match functions LIKE, startsWith, endsWith, and hasToken depend on the index type used, the index expression, and the particular shape of the data.

There are two available settings that apply to skip indexes.

use_skip_indexes (0 or 1, default 1). Not all queries can efficiently use skip indexes. If a particular filtering condition is likely to include most granules, applying the data skipping index incurs an unnecessary, and sometimes significant, cost. Set the value to 0 for queries that are unlikely to benefit from any skip indexes.

force_data_skipping_indices (comma separated list of index names). This setting can be used to prevent some kinds of inefficient queries. In circumstances where querying a table is too expensive unless a skip index is used, using this setting with one or more index names will return an exception for any query that does not use the listed index. This would prevent poorly written queries from consuming server resources.

Skip indexes are not intuitive, especially for users accustomed to secondary row-based indexes from the RDMS realm or inverted indexes from document stores. To get any benefit, applying a ClickHouse data skipping index must avoid enough granule reads to offset the cost of calculating the index. Critically, if a value occurs even once in an indexed block, it means the entire block must be read into memory and evaluated, and the index cost has been needlessly incurred.

Consider the following data distribution:

Assume the primary/order by key is timestamp, and there is an index on visitor_id. Consider the following query:

SELECT timestamp, url FROM table WHERE visitor_id = 1001

A traditional secondary index would be very advantageous with this kind of data distribution. Instead of reading all 32768 rows to find the 5 rows with the requested visitor_id, the secondary index would include just five row locations, and only those five rows would be read from disk. The exact opposite is true for a ClickHouse data skipping index. All 32768 values in the visitor_id column will be tested regardless of the type of skip index.

Accordingly, the natural impulse to try to speed up ClickHouse queries by simply adding an index to key columns is often incorrect. This advanced functionality should only be used after investigating other alternatives, such as modifying the primary key (see How to Pick a Primary Key), using projections, or using materialized views. Even when a data skipping index is appropriate, careful tuning both the index and the table will often be necessary.

In most cases a useful skip index requires a strong correlation between the primary key and the targeted, non-primary column/expression. If there is no correlation (as in the above diagram), the chances of the filtering condition being met by at least one of the rows in the block of several thousand values is high and few blocks will be skipped. In constrast, if a range of values for the primary key (like time of day) is strongly associated with the values in the potential index column (such as television viewer ages), then a minmax type of index is likely to be beneficial. Note that it may be possible to increase this correlation when inserting data, either by including additional columns in the sorting/ORDER BY key, or batching inserts in a way that values associated with the primary key are grouped on insert. For example, all of the events for a particular site_id could be grouped and inserted together by the ingest process, even if the primary key is a timestamp containing events from a large number of sites. This will result in many granules that contains only a few site ids, so many blocks could be skipped when searching by a specific site_id value.

Another good candidate for a skip index is for high cardinality expressions where any one value is relatively sparse in the data. One example might be an observability platform that tracks error codes in API requests. Certain error codes, while rare in the data, might be particularly important for searches. A set skip index on the error_code column would allow bypassing the vast majority of blocks that don't contain errors and therefore significantly improve error focused queries.

Finally, the key best practice is to test, test, test. Again, unlike b-tree secondary indexes or inverted indexes for searching documents, data skipping index behavior is not easily predictable. Adding them to a table incurs a meangingful cost both on data ingest and on queries that for any number of reasons don't benefit from the index. They should always be tested on real world type of data, and testing should include variations of the type, granularity size and other parameters. Testing will often reveal patterns and pitfalls that aren't obvious from thought experiments alone.

# data[].attributes.attachment_link.title
Understanding ClickHouse Data Skipping Indexes | ClickHouse Docs

# data[].attributes.attachment_link.description
Skip indexes enable ClickHouse to skip reading significant chunks of data that are guaranteed to have no matching values.

# data[].attributes.body
https://clickhouse.com/docs/en/optimize/sparse-primary-indexes

# data[].attributes.attachment_link.readable_text
In this guide we are going to do a deep dive into ClickHouse indexing. We will illustrate and discuss in detail:

You can optionally execute all ClickHouse SQL statements and queries given in this guide by yourself on your own machine. For installation of ClickHouse and getting started instructions, see the Quick Start.

Throughout this guide we will use a sample anonymized web traffic data set.

We will use a subset of 8.87 million rows (events) from the sample data set.

The uncompressed data size is 8.87 million events and about 700 MB. This compresses to 200 mb when stored in ClickHouse.

In our subset, each row contains three columns that indicate an internet user (UserID column) who clicked on a URL (URL column) at a specific time (EventTime column).

With these three columns we can already formulate some typical web analytics queries such as:

"What are the top 10 most clicked urls for a specific user?"

"What are the top 10 users that most frequently clicked a specific URL?"

"What are the most popular times (e.g. days of the week) at which a user clicks on a specific URL?"

All runtime numbers given in this document are based on running ClickHouse 22.2.1 locally on a MacBook Pro with the Apple M1 Pro chip and 16GB of RAM.

In order to see how a query is executed over our data set without a primary key, we create a table (with a MergeTree table engine) by executing the following SQL DDL statement:

Next insert a subset of the hits data set into the table with the following SQL insert statement. This uses the URL table function in order to load a subset of the full dataset hosted remotely at clickhouse.com:

The response is:

0 rows in set. Elapsed: 145.993 sec. Processed 8.87 million rows, 18.40 GB (60.78 thousand rows/s., 126.06 MB/s.)

ClickHouse client's result output shows us that the statement above inserted 8.87 million rows into the table.

Lastly, in order to simplify the discussions later on in this guide and to make the diagrams and results reproducible, we optimize the table using the FINAL keyword:

Now we execute our first web analytics query. The following is calculating the top 10 most clicked urls for the internet user with the UserID 749927693:

The response is:

ClickHouse client's result output indicates that ClickHouse executed a full table scan! Each single row of the 8.87 million rows of our table was streamed into ClickHouse. That doesn't scale.

To make this (way) more efficient and (much) faster, we need to use a table with a appropriate primary key. This will allow ClickHouse to automatically (based on the primary key's column(s)) create a sparse primary index which can then be used to significantly speed up the execution of our example query.

In traditional relational database management systems, the primary index would contain one entry per table row. For our data set this would result in the primary index - often a B(+)-Tree data structure - containing 8.87 million entries. Such an index allows the fast location of specific rows, resulting in high efficiency for lookup queries and point updates. Searching an entry in a B(+)-Tree data structure has average time complexity of O(log2 n). For a table of 8.87 million rows, this means 23 steps are required to locate any index entry. This capability comes at a cost: additional disk and memory overheads and higher insertion costs when adding new rows to the table and entries to the index (and also sometimes rebalancing of the B-Tree).

Considering the challenges associated with B-Tree indexes, table engines in ClickHouse utilise a different approach. The ClickHouse MergeTree Engine Family has been designed and optimized to handle massive data volumes. These tables are designed to receive millions of row inserts per second and store very large (100s of Petabytes) volumes of data. Data is quickly written to a table part by part, with rules applied for merging the parts in the background. In ClickHouse each part has its own primary index. When parts are merged, then the merged part's primary indexes are also merged. At the very large scale that ClickHouse is designed for, it is paramount to be very disk and memory efficient. Therefore, instead of indexing every row, the primary index for a part has one index entry (known as a 'mark') per group of rows (called 'granule') - this technique is called sparse index.

Sparse indexing is possible because ClickHouse is storing the rows for a part on disk ordered by the primary key column(s). Instead of directly locating single rows (like a B-Tree based index), the sparse primary index allows it to quickly (via a binary search over index entries) identify groups of rows that could possibly match the query. The located groups of potentially matching rows (granules) are then in parallel streamed into the ClickHouse engine in order to find the matches. This index design allows for the primary index to be small (it can, and must, completely fit into the main memory), whilst still significantly speeding up query execution times: especially for range queries that are typical in data analytics use cases.

The following illustrates in detail how ClickHouse is building and using its sparse primary index. Later on in the article, we will discuss some best practices for choosing, removing, and ordering the table columns that are used to build the index (primary key columns).

Create a table that has a compound primary key with key columns UserID and URL:

DDL Statement Details

In order to simplify the discussions later on in this guide, as well as make the diagrams and results reproducible, the DDL statement

specifies a compound sorting key for the table via an `ORDER BY` clause

explicitly controls how many index entries the primary index will have through the settings:

`index_granularity: explicitly set to its default value of 8192. This means that for each group of 8192 rows, the primary index will have one index entry, e.g. if the table contains 16384 rows then the index will have two index entries.

`index_granularity_bytes`: set to 0 in order to disable adaptive index granularity. Adaptive index granularity means that ClickHouse automatically creates one index entry for a group of n rows if either of these are true:

if n is less than 8192 and the size of the combined row data for that n rows is larger than or equal to 10 MB (the default value for index_granularity_bytes) or

if the combined row data size for n rows is less than 10 MB but n is 8192.

The primary key in the DDL statement above causes the creation of the primary index based on the two specified key columns.

Next insert the data:

The response looks like:

0 rows in set. Elapsed: 149.432 sec. Processed 8.87 million rows, 18.40 GB (59.38 thousand rows/s., 123.16 MB/s.)

And optimize the table:

We can use the following query to obtain metadata about our table:

The response is:

The output of the ClickHouse client shows:

The table's data is stored in wide format in a specific directory on disk meaning that there will be one data file (and one mark file) per table column inside that directory.

The table has 8.87 million rows.

The uncompressed data size of all rows together is 733.28 MB.

The compressed size on disk of all rows together is 206.94 MB.

The table has a primary index with 1083 entries (called 'marks') and the size of the index is 96.93 KB.

In total, the table's data and mark files and primary index file together take 207.07 MB on disk.

Our table that we created above has

The inserted rows are stored on disk in lexicographical order (ascending) by the primary key columns (and the additional EventTime column from the sorting key).

ClickHouse is a column-oriented database management system. As shown in the diagram below

for the on disk representation, there is a single data file (*.bin) per table column where all the values for that column are stored in a compressed format, and

the 8.87 million rows are stored on disk in lexicographic ascending order by the primary key columns (and the additional sort key columns) i.e. in this case

UserID.bin, URL.bin, and EventTime.bin are the data files on disk where the values of the `UserID`, `URL`, and `EventTime` columns are stored.

For data processing purposes, a table's column values are logically divided into granules. A granule is the smallest indivisible data set that is streamed into ClickHouse for data processing. This means that instead of reading individual rows, ClickHouse is always reading (in a streaming fashion and in parallel) a whole group (granule) of rows.

The following diagram shows how the (column values of) 8.87 million rows of our table are organized into 1083 granules, as a result of the table's DDL statement containing the setting index_granularity (set to its default value of 8192).

The first (based on physical order on disk) 8192 rows (their column values) logically belong to granule 0, then the next 8192 rows (their column values) belong to granule 1 and so on.

The primary index has one entry per granule​

The primary index is created based on the granules shown in the diagram above. This index is an uncompressed flat array file (primary.idx), containing so-called numerical index marks starting at 0.

The diagram below shows that the index stores the primary key column values (the values marked in orange in the diagram above) for each first row for each granule. Or in other words: the primary index stores the primary key column values from each 8192nd row of the table (based on the physical row order defined by the primary key columns). For example

the first index entry ('mark 0' in the diagram below) is storing the key column values of the first row of granule 0 from the diagram above,

the second index entry ('mark 1' in the diagram below) is storing the key column values of the first row of granule 1 from the diagram above, and so on.

In total the index has 1083 entries for our table with 8.87 million rows and 1083 granules:

Inspecting the content of the primary index

On a self-managed ClickHouse cluster we can use the file table function for inspecting the content of the primary index of our example table.

For that we first need to copy the primary index file into the user_files_path of a node from the running cluster:

Step 1: Get part-path that contains the primary index file

returns /Users/tomschreiber/Clickhouse/store/85f/85f4ee68-6e28-4f08-98b1-7d8affa1d88c/all_1_9_4 on the test machine.

Step 2: Get user_files_path
The default user_files_path on Linux is `/var/lib/clickhouse/user_files/`
and on Linux you can check if it got changed: $ grep user_files_path /etc/clickhouse-server/config.xml

On the test machine the path is /Users/tomschreiber/Clickhouse/user_files/

Step 3: Copy the primary index file into the user_files_path
` cp /Users/tomschreiber/Clickhouse/store/85f/85f4ee68-6e28-4f08-98b1-7d8affa1d88c/all_1_9_4/primary.idx /Users/tomschreiber/Clickhouse/user_files/primary-hits_UserID_URL.idx `

Now we can inspect the content of the primary index via SQL:

Get amount of entries

FROM file('primary-hits_UserID_URL.idx', 'RowBinary', 'UserID UInt32, URL String'); `

returns `1083`

Get first two index marks
` SELECT UserID, URL
FROM file('primary-hits_UserID_URL.idx', 'RowBinary', 'UserID UInt32, URL String')
LIMIT 0, 2; `

returns
` 240923, http://showtopics.html%3...
4073710, http://mk.ru&pos=3_0 `

Get last index mark
` SELECT UserID, URL
FROM file('primary-hits_UserID_URL.idx', 'RowBinary', 'UserID UInt32, URL String')
LIMIT 1082, 1; `

returns
` 4292714039 │ http://sosyal-mansetleri... `

This matches exactly our diagram of the primary index content for our example table:

The primary key entries are called index marks because each index entry is marking the start of a specific data range. Specifically for the example table:

UserID index marks:
The stored UserID values in the primary index are sorted in ascending order.
'mark 1' in the diagram above thus indicates that the UserID values of all table rows in granule 1, and in all following granules, are guaranteed to be greater than or equal to 4.073.710.

As we will see later, this global order enables ClickHouse to use a binary search algorithm over the index marks for the first key column when a query is filtering on the first column of the primary key.

URL index marks:
The quite similar cardinality of the primary key columns UserID and URL means that the index marks for all key columns after the first column in general only indicate a data range as long as the predecessor key column value stays the same for all table rows within at least the current granule.
For example, because the UserID values of mark 0 and mark 1 are different in the diagram above, ClickHouse can't assume that all URL values of all table rows in granule 0 are larger or equal to'http://showtopics.html%3...'. However, if the UserID values of mark 0 and mark 1 would be the same in the diagram above (meaning that the UserID value stays the same for all table rows within the granule 0), the ClickHouse could assume that all URL values of all table rows in granule 0 are larger or equal to 'http://showtopics.html%3...'.

We will discuss the consequences of this on query execution performance in more detail later.

We can now execute our queries with support from the primary index.

The following calculates the top 10 most clicked urls for the UserID 749927693.

The response is:

The output for the ClickHouse client is now showing that instead of doing a full table scan, only 8.19 thousand rows were streamed into ClickHouse.

If trace logging is enabled then the ClickHouse server log file shows that ClickHouse was running a binary search over the 1083 UserID index marks, in order to identify granules that possibly can contain rows with a UserID column value of 749927693. This requires 19 steps with an average time complexity of O(log2 n):

We can see in the trace log above, that one mark out of the 1083 existing marks satisfied the query.

Trace Log Details

Mark 176 was identified (the 'found left boundary mark' is inclusive, the 'found right boundary mark' is exclusive), and therefore all 8192 rows from granule 176 (which starts at row 1.441.792 - we will see that later on in this guide) are then streamed into ClickHouse in order to find the actual rows with a UserID column value of 749927693.

We can also reproduce this by using the EXPLAIN clause in our example query:

The response looks like:

The client output is showing that one out of the 1083 granules was selected as possibly containing rows with a UserID column value of 749927693.

As discussed above, ClickHouse is using its sparse primary index for quickly (via binary search) selecting granules that could possibly contain rows that match a query.

This is the first stage (granule selection) of ClickHouse query execution.

In the second stage (data reading), ClickHouse is locating the selected granules in order to stream all their rows into the ClickHouse engine in order to find the rows that are actually matching the query.

We discuss that second stage in more detail in the following section.

The following diagram illustrates a part of the primary index file for our table.

As discussed above, via a binary search over the index's 1083 UserID marks, mark 176 was identified. Its corresponding granule 176 can therefore possibly contain rows with a UserID column value of 749.927.693.

Granule Selection Details

The diagram above shows that mark 176 is the first index entry where both the minimum UserID value of the associated granule 176 is smaller than 749.927.693, and the minimum UserID value of granule 177 for the next mark (mark 177) is greater than this value. Therefore only the corresponding granule 176 for mark 176 can possibly contain rows with a UserID column value of 749.927.693.

In order to confirm (or not) that some row(s) in granule 176 contain a UserID column value of 749.927.693, all 8192 rows belonging to this granule need to be streamed into ClickHouse.

To achieve this, ClickHouse needs to know the physical location of granule 176.

In ClickHouse the physical locations of all granules for our table are stored in mark files. Similar to data files, there is one mark file per table column.

The following diagram shows the three mark files UserID.mrk, URL.mrk, and EventTime.mrk that store the physical locations of the granules for the table's UserID, URL, and EventTime columns.

We have discussed how the primary index is a flat uncompressed array file (primary.idx), containing index marks that are numbered starting at 0.

Similarly, a mark file is also a flat uncompressed array file (*.mrk) containing marks that are numbered starting at 0.

Once ClickHouse has identified and selected the index mark for a granule that can possibly contain matching rows for a query, a positional array lookup can be performed in the mark files in order to obtain the physical locations of the granule.

Each mark file entry for a specific column is storing two locations in the form of offsets:

The first offset ('block_offset' in the diagram above) is locating the block in the compressed column data file that contains the compressed version of the selected granule. This compressed block potentially contains a few compressed granules. The located compressed file block is uncompressed into the main memory on read.

The second offset ('granule_offset' in the diagram above) from the mark-file provides the location of the granule within the uncompressed block data.

All the 8192 rows belonging to the located uncompressed granule are then streamed into ClickHouse for further processing.

The following diagram and the text below illustrate how for our example query ClickHouse locates granule 176 in the UserID.bin data file.

We discussed earlier in this guide that ClickHouse selected the primary index mark 176 and therefore granule 176 as possibly containing matching rows for our query.

ClickHouse now uses the selected mark number (176) from the index for a positional array lookup in the UserID.mrk mark file in order to get the two offsets for locating granule 176.

As shown, the first offset is locating the compressed file block within the UserID.bin data file that in turn contains the compressed version of granule 176.

Once the located file block is uncompressed into the main memory, the second offset from the mark file can be used to locate granule 176 within the uncompressed data.

ClickHouse needs to locate (and stream all values from) granule 176 from both the UserID.bin data file and the URL.bin data file in order to execute our example query (top 10 most clicked URLs for the internet user with the UserID 749.927.693).

The diagram above shows how ClickHouse is locating the granule for the UserID.bin data file.

In parallel, ClickHouse is doing the same for granule 176 for the URL.bin data file. The two respective granules are aligned and streamed into the ClickHouse engine for further processing i.e. aggregating and counting the URL values per group for all rows where the UserID is 749.927.693, before finally outputting the 10 largest URL groups in descending count order.

When a query is filtering on a column that is part of a compound key and is the first key column, then ClickHouse is running the binary search algorithm over the key column's index marks.

But what happens when a query is filtering on a column that is part of a compound key, but is not the first key column?

We use a query that calculates the top 10 users that have most frequently clicked on the URL "http://public_search":

The response is:

The client output indicates that ClickHouse almost executed a full table scan despite the URL column being part of the compound primary key! ClickHouse reads 8.81 million rows from the 8.87 million rows of the table.

If trace_logging is enabled then the ClickHouse server log file shows that ClickHouse used a generic exclusion search over the 1083 URL index marks in order to identify those granules that possibly can contain rows with a URL column value of "http://public_search":

We can see in the sample trace log above, that 1076 (via the marks) out of 1083 granules were selected as possibly containing rows with a matching URL value.

This results in 8.81 million rows being streamed into the ClickHouse engine (in parallel by using 10 streams), in order to identify the rows that are actually contain the URL value "http://public_search".

However, as we will see later only 39 granules out of that selected 1076 granules actually contain matching rows.

Whilst the primary index based on the compound primary key (UserID, URL) was very useful for speeding up queries filtering for rows with a specific UserID value, the index is not providing significant help with speeding up the query that filters for rows with a specific URL value.

The reason for this is that the URL column is not the first key column and therefore ClickHouse is using a generic exclusion search algorithm (instead of binary search) over the URL column's index marks, and the effectiveness of that algorithm is dependant on the cardinality difference between the URL column and it's predecessor key column UserID.

In order to illustrate that, we give some details about how the generic exclusion search works.

The following is illustrating how the ClickHouse generic exclusion search algorithm works when granules are selected via a secondary column where the predecessor key column has a low(er) or high(er) cardinality.

As an example for both cases we will assume:

a query that is searching for rows with URL value = "W3".

an abstract version of our hits table with simplified values for UserID and URL.

the same compound primary key (UserID, URL) for the index. This means rows are first ordered by UserID values. Rows with the same UserID value are then ordered by URL.

a granule size of two i.e. each granule contains two rows.

We have marked the key column values for the first table rows for each granule in orange in the diagrams below..
Predecessor key column has low(er) cardinality
Suppose UserID had low cardinality. In this case it would be likely that the same UserID value is spread over multiple table rows and granules and therefore index marks. For index marks with the same UserID, the URL values for the index marks are sorted in ascending order (because the table rows are ordered first by UserID and then by URL). This allows efficient filtering as described below:

There are three different scenarios for the granule selection process for our abstract sample data in the diagram above:

1. Index mark 0 for which the URL value is smaller than W3 and for which the URL value of the directly succeeding index mark is also smaller than W3 can be excluded because mark 0, and 1 have the same UserID value. Note that this exclusion-precondition ensures that granule 0 is completely composed of U1 UserID values so that ClickHouse can assume that also the maximum URL value in granule 0 is smaller than W3 and exclude the granule.

2. Index mark 1 for which the URL value is smaller (or equal) than W3 and for which the URL value of the directly succeeding index mark is greater (or equal) than W3 is selected because it means that granule 1 can possibly contain rows with URL W3.

3. Index marks 2 and 3 for which the URL value is greater than W3 can be excluded, since index marks of a primary index store the key column values for the first table row for each granule and the table rows are sorted on disk by the key column values, therefore granule 2 and 3 can't possibly contain URL value W3.

Predecessor key column has high(er) cardinality
When the UserID has high cardinality then it is unlikely that the same UserID value is spread over multiple table rows and granules. This means the URL values for the index marks are not monotonically increasing:

As we can see in the diagram above, all shown marks whose URL values are smaller than W3 are getting selected for streaming its associated granule's rows into the ClickHouse engine.

This is because whilst all index marks in the diagram fall into scenario 1 described above, they do not satisfy the mentioned exclusion-precondition that the directly succeeding index mark has the same UserID value as the current mark and thus can't be excluded.

For example, consider index mark 0 for which the URL value is smaller than W3 and for which the URL value of the directly succeeding index mark is also smaller than W3. This can not be excluded because the directly succeeding index mark 1 does not have the same UserID value as the current mark 0.

This ultimately prevents ClickHouse from making assumptions about the maximum URL value in granule 0. Instead it has to assume that granule 0 potentially contains rows with URL value W3 and is forced to select mark 0.

The same scenario is true for mark 1, 2, and 3.

In our sample data set both key columns (UserID, URL) have similar high cardinality, and, as explained, the generic exclusion search algorithm is not very effective when the predecessor key column of the URL column has a high(er) or similar cardinality.

Because of the similarly high cardinality of UserID and URL, our query filtering on URL also wouldn't benefit much from creating a secondary data skipping index on the URL column of our table with compound primary key (UserID, URL).

For example this two statements create and populate a minmax data skipping index on the URL column of our table:

ClickHouse now created an additional index that is storing - per group of 4 consecutive granules (note the GRANULARITY 4 clause in the ALTER TABLE statement above) - the minimum and maximum URL value:

The first index entry ('mark 0' in the diagram above) is storing the minimum and maximum URL values for the rows belonging to the first 4 granules of our table.

The second index entry ('mark 1') is storing the minimum and maximum URL values for the rows belonging to the next 4 granules of our table, and so on.

(ClickHouse also created a special mark file for to the data skipping index for locating the groups of granules associated with the index marks.)

Because of the similarly high cardinality of UserID and URL, this secondary data skipping index can't help with excluding granules from being selected when our query filtering on URL is executed.

The specific URL value that the query is looking for (i.e. 'http://public_search') very likely is between the minimum and maximum value stored by the index for each group of granules resulting in ClickHouse being forced to select the group of granules (because they might contain row(s) matching the query).

As a consequence, if we want to significantly speed up our sample query that filters for rows with a specific URL then we need to use a primary index optimized to that query.

If in addition we want to keep the good performance of our sample query that filters for rows with a specific UserID then we need to use multiple primary indexes.

The following is showing ways for achieving that.

If we want to significantly speed up both of our sample queries - the one that filters for rows with a specific UserID and the one that filters for rows with a specific URL - then we need to use multiple primary indexes by using one of these three options:

Creating a second table with a different primary key.

Creating a materialized view on our existing table.

Adding a projection to our existing table.

All three options will effectively duplicate our sample data into a additional table in order to reorganize the table primary index and row sort order.

However, the three options differ in how transparent that additional table is to the user with respect to the routing of queries and insert statements.

When creating a second table with a different primary key then queries must be explicitly send to the table version best suited for the query, and new data must be inserted explicitly into both tables in order to keep the tables in sync:

With a materialized view the additional table is implicitly created and data is automatically kept in sync between both tables:

And the projection is the most transparent option because next to automatically keeping the implicitly created (and hidden) additional table in sync with data changes, ClickHouse will automatically choose the most effective table version for queries:

In the following we discuss this three options for creating and using multiple primary indexes in more detail and with real examples.

We are creating a new additional table where we switch the order of the key columns (compared to our original table) in the primary key:

Insert all 8.87 million rows from our original table into the additional table:

The response looks like:

0 rows in set. Elapsed: 2.898 sec. Processed 8.87 million rows, 838.84 MB (3.06 million rows/s., 289.46 MB/s.)

And finally optimize the table:

Because we switched the order of the columns in the primary key, the inserted rows are now stored on disk in a different lexicographical order (compared to our original table) and therefore also the 1083 granules of that table are containing different values than before:

This is the resulting primary key:

That can now be used to significantly speed up the execution of our example query filtering on the URL column in order to calculate the top 10 users that most frequently clicked on the URL "http://public_search":

The response is:

Now, instead of almost doing a full table scan, ClickHouse executed that query much more effectively.

With the primary index from the original table where UserID was the first, and URL the second key column, ClickHouse used a generic exclusion search over the index marks for executing that query and that was not very effective because of the similarly high cardinality of UserID and URL.

With URL as the first column in the primary index, ClickHouse is now running binary search over the index marks. The corresponding trace log in the ClickHouse server log file confirms that:

ClickHouse selected only 39 index marks, instead of 1076 when generic exclusion search was used.

Note that the additional table is optimized for speeding up the execution of our example query filtering on URLs.

Similar to the bad performance of that query with our original table, our example query filtering on UserIDs will not run very effectively with the new additional table, because UserID is now the second key column in the primary index of that table and therefore ClickHouse will use generic exclusion search for granule selection, which is not very effective for similarly high cardinality of UserID and URL. Open the details box for specifics.

We now have two tables. Optimized for speeding up queries filtering on UserIDs, and speeding up queries filtering on URLs, respectively:

Create a materialized view on our existing table.

The response looks like:

0 rows in set. Elapsed: 2.935 sec. Processed 8.87 million rows, 838.84 MB (3.02 million rows/s., 285.84 MB/s.)

The implicitly created table (and it's primary index) backing the materialized view can now be used to significantly speed up the execution of our example query filtering on the URL column:

The response is:

Because effectively the implicitly created table (and it's primary index) backing the materialized view is identical to the secondary table that we created explicitly, the query is executed in the same effective way as with the explicitly created table.

The corresponding trace log in the ClickHouse server log file confirms that ClickHouse is running binary search over the index marks:

Create a projection on our existing table:

And materialize the projection:

The hidden table (and it's primary index) created by the projection can now be (implicitly) used to significantly speed up the execution of our example query filtering on the URL column. Note that the query is syntactically targeting the source table of the projection.

The response is:

Because effectively the hidden table (and it's primary index) created by the projection is identical to the secondary table that we created explicitly, the query is executed in the same effective way as with the explicitly created table.

The corresponding trace log in the ClickHouse server log file confirms that ClickHouse is running binary search over the index marks:

The primary index of our table with compound primary key (UserID, URL) was very useful for speeding up a query filtering on UserID. But that index is not providing significant help with speeding up a query filtering on URL, despite the URL column being part of the compound primary key.

And vice versa: The primary index of our table with compound primary key (URL, UserID) was speeding up a query filtering on URL, but didn't provide much support for a query filtering on UserID.

Because of the similarly high cardinality of the primary key columns UserID and URL, a query that filters on the second key column doesn't benefit much from the second key column being in the index.

Therefore it makes sense to remove the second key column from the primary index (resulting in less memory consumption of the index) and to use multiple primary indexes instead.

However if the key columns in a compound primary key have big differences in cardinality, then it is beneficial for queries to order the primary key columns by cardinality in ascending order.

The higher the cardinality difference between the key columns is, the more the order of those columns in the key matters. We will demonstrate that in the next section.

In a compound primary key the order of the key columns can significantly influence both:

the efficiency of the filtering on secondary key columns in queries, and

the compression ratio for the table's data files.

In order to demonstrate that, we will use a version of our web traffic sample data set where each row contains three columns that indicate whether or not the access by an internet 'user' ( UserID column) to a URL ( URL column) got marked as bot traffic ( IsRobot column).

We will use a compound primary key containing all three aforementioned columns that could be used to speed up typical web analytics queries that calculate

how much (percentage of) traffic to a specific URL is from bots or

how confident we are that a specific user is (not) a bot (what percentage of traffic from that user is (not) assumed to be bot traffic)

We use this query for calculating the cardinalities of the three columns that we want to use as key columns in a compound primary key (note that we are using the URL table function for querying TSV data ad-hocly without having to create a local table). Run this query in clickhouse client:

The response is:

We can see that there is a big difference between the cardinalities, especially between the URL and IsRobot columns, and therefore the order of these columns in a compound primary key is significant for both the efficient speed up of queries filtering on that columns and for achieving optimal compression ratios for the table's column data files.

In order to demonstrate that we are creating two table versions for our bot traffic analysis data:

a table hits_URL_UserID_IsRobot with the compound primary key (URL, UserID, IsRobot) where we order the key columns by cardinality in descending order

a table hits_IsRobot_UserID_URL with the compound primary key (IsRobot, UserID, URL) where we order the key columns by cardinality in ascending order

Create the table hits_URL_UserID_IsRobot with the compound primary key (URL, UserID, IsRobot):

And populate it with 8.87 million rows:

This is the response:

0 rows in set. Elapsed: 104.729 sec. Processed 8.87 million rows, 15.88 GB (84.73 thousand rows/s., 151.64 MB/s.)

Next, create the table hits_IsRobot_UserID_URL with the compound primary key (IsRobot, UserID, URL):

And populate it with the same 8.87 million rows that we used to populate the previous table:

The response is:

0 rows in set. Elapsed: 95.959 sec. Processed 8.87 million rows, 15.88 GB (92.48 thousand rows/s., 165.50 MB/s.)

When a query is filtering on at least one column that is part of a compound key, and is the first key column, then ClickHouse is running the binary search algorithm over the key column's index marks.

When a query is filtering (only) on a column that is part of a compound key, but is not the first key column, then ClickHouse is using the generic exclusion search algorithm over the key column's index marks.

For the second case the ordering of the key columns in the compound primary key is significant for the effectiveness of the generic exclusion search algorithm.

This is a query that is filtering on the UserID column of the table where we ordered the key columns (URL, UserID, IsRobot) by cardinality in descending order:

The response is:

This is the same query on the table where we ordered the key columns (IsRobot, UserID, URL) by cardinality in ascending order:

The response is:

We can see that the query execution is significantly more effective and faster on the table where we ordered the key columns by cardinality in ascending order.

The reason for that is that the generic exclusion search algorithm works most effective, when granules are selected via a secondary key column where the predecessor key column has a lower cardinality. We illustrated that in detail in a previous section of this guide.

This query compares the compression ratio of the UserID column between the two tables that we created above:

This is the response:

We can see that the compression ratio for the UserID column is significantly higher for the table where we ordered the key columns (IsRobot, UserID, URL) by cardinality in ascending order.

Although in both tables exactly the same data is stored (we inserted the same 8.87 million rows into both tables), the order of the key columns in the compound primary key has a significant influence on how much disk space the compressed data in the table's column data files requires:

in the table hits_URL_UserID_IsRobot with the compound primary key (URL, UserID, IsRobot) where we order the key columns by cardinality in descending order, the UserID.bin data file takes 11.24 MiB of disk space

in the table hits_IsRobot_UserID_URL with the compound primary key (IsRobot, UserID, URL) where we order the key columns by cardinality in ascending order, the UserID.bin data file takes only 877.47 KiB of disk space

Having a good compression ratio for the data of a table's column on disk not only saves space on disk, but also makes queries (especially analytical ones) that require the reading of data from that column faster, as less i/o is required for moving the column's data from disk to the main memory (the operating system's file cache).

In the following we illustrate why it's beneficial for the compression ratio of a table's columns to order the primary key columns by cardinality in ascending order.

The diagram below sketches the on-disk order of rows for a primary key where the key columns are ordered by cardinality in ascending order:

We discussed that the table's row data is stored on disk ordered by primary key columns.

In the diagram above, the table's rows (their column values on disk) are first ordered by their cl value, and rows that have the same cl value are ordered by their ch value. And because the first key column cl has low cardinality, it is likely that there are rows with the same cl value. And because of that it is also likely that ch values are ordered (locally - for rows with the same cl value).

If in a column, similar data is placed close to each other, for example via sorting, then that data will be compressed better. In general, a compression algorithm benefits from the run length of data (the more data it sees the better for compression) and locality (the more similar the data is, the better the compression ratio is).

In contrast to the diagram above, the diagram below sketches the on-disk order of rows for a primary key where the key columns are ordered by cardinality in descending order:

Now the table's rows are first ordered by their ch value, and rows that have the same ch value are ordered by their cl value. But because the first key column ch has high cardinality, it is unlikely that there are rows with the same ch value. And because of that is is also unlikely that cl values are ordered (locally - for rows with the same ch value).

Therefore the cl values are most likely in random order and therefore have a bad locality and compression ration, respectively.

For both the efficient filtering on secondary key columns in queries and the compression ratio of a table's column data files it is beneficial to order the columns in a primary key by their cardinality in ascending order.

Although in general it is not the best use case for ClickHouse, sometimes applications built on top of ClickHouse require to identify single rows of a ClickHouse table.

An intuitive solution for that might be to use a UUID column with a unique value per row and for fast retrieval of rows to use that column as a primary key column.

For the fastest retrieval, the UUID column would need to be the first key column.

We discussed that because a ClickHouse table's row data is stored on disk ordered by primary key column(s), having a very high cardinality column (like a UUID column) in a primary key or in a compound primary key before columns with lower cardinality is detrimental for the compression ratio of other table columns.

A compromise between fastest retrieval and optimal data compression is to use a compound primary key where the UUID is the last key column, after low(er) cardinality key columns that are used to ensure a good compression ratio for some of the table's columns.

One concrete example is a the plaintext paste service https://pastila.nl that Alexey Milovidov developed and blogged about.

On every change to the text-area, the data is saved automatically into a ClickHouse table row (one row per change).

And one way to identify and retrieve (a specific version of) the pasted content is to use a hash of the content as the UUID for the table row that contains the content.

The following diagram shows

the insert order of rows when the content changes (for example because of keystrokes typing the text into the text-area) and

the on-disk order of the data from the inserted rows when the PRIMARY KEY (hash) is used:

Because the hash column is used as the primary key column

specific rows can be retrieved very quickly, but

the table's rows (their column data) are stored on disk ordered ascending by (the unique and random) hash values. Therefore also the content column's values are stored in random order with no data locality resulting in a suboptimal compression ratio for the content column data file.

In order to significantly improve the compression ratio for the content column while still achieving fast retrieval of specific rows, pastila.nl is using two hashes (and a compound primary key) for identifying a specific row:

The following diagram shows

the insert order of rows when the content changes (for example because of keystrokes typing the text into the text-area) and

the on-disk order of the data from the inserted rows when the compound PRIMARY KEY (fingerprint, hash) is used:

Now the rows on disk are first ordered by fingerprint, and for rows with the same fingerprint value, their hash value determines the final order.

Because data that differs only in small changes is getting the same fingerprint value, similar data is now stored on disk close to each other in the content column. And that is very good for the compression ratio of the content column, as a compression algorithm in general benefits from data locality (the more similar the data is the better the compression ratio is).

The compromise is that two fields ( fingerprint and hash) are required for the retrieval of a specific row in order to optimally utilise the primary index that results from the compound PRIMARY KEY (fingerprint, hash).

# data[].attributes.attachment_link.title
A Practical Introduction to Primary Indexes in ClickHouse | ClickHouse Docs

# data[].attributes.attachment_link.description
In this guide we are going to do a deep dive into ClickHouse indexing.

# data[].attributes.headline
Yandex DataLens: воркбуки и новая ролевая модель

# data[].attributes.subject
Yandex DataLens: воркбуки и новая ролевая модель

# data[].attributes.body
https://www.youtube.com/watch?v=gy25zbXdk9E

# data[].attributes.attachment_link.title
Yandex DataLens: воркбуки и новая ролевая модель

# data[].attributes.body
https://clickhouse.com/docs/en/sql-reference/statements/create/table#temporary-tables

# data[].attributes.attachment_link.readable_text
Creates a new table. This query can have various syntax forms depending on a use case.

By default, tables are created only on the current server. Distributed DDL queries are implemented as ON CLUSTER clause, which is described separately.

Creates a table named table_name in the db database or the current database if db is not set, with the structure specified in brackets and the engine engine. The structure of the table is a list of column descriptions, secondary indexes and constraints . If primary key is supported by the engine, it will be indicated as parameter for the table engine.

A column description is name type in the simplest case. Example: RegionID UInt32.

Expressions can also be defined for default values (see below).

If necessary, primary key can be specified, with one or more key expressions.

Comments can be added for columns and for the table.

Creates a table with the same structure as another table. You can specify a different engine for the table. If the engine is not specified, the same engine will be used as for the db2.name2 table.

Creates a table with the same result as that of the table function specified. The created table will also work in the same way as the corresponding table function that was specified.

Creates a table with a structure like the result of the SELECT query, with the engine engine, and fills it with data from SELECT. Also you can explicitly specify columns description.

If the table already exists and IF NOT EXISTS is specified, the query won't do anything.

There can be other clauses after the ENGINE clause in the query. See detailed documentation on how to create tables in the descriptions of table engines.
Example
Query:

Result:

NULL and NOT NULL modifiers after data type in column definition allow or do not allow it to be Nullable.

If the type is not Nullable and if NULL is specified, it will be treated as Nullable; if NOT NULL is specified, then no. For example, INT NULL is the same as Nullable(INT). If the type is Nullable and NULL or NOT NULL modifiers are specified, the exception will be thrown.

See also data_type_default_nullable setting.

The column description can specify a default value expression in the form of DEFAULT expr, MATERIALIZED expr, or ALIAS expr. Example: URLDomain String DEFAULT domain(URL).

The expression expr is optional. If it is omitted, the column type must be specified explicitly and the default value will be 0 for numeric columns, '' (the empty string) for string columns, [] (the empty array) for array columns, 1970-01-01 for date columns, or NULL for nullable columns.

The column type of a default value column can be omitted in which case it is inferred from expr's type. For example the type of column EventDate DEFAULT toDate(EventTime) will be date.

If both a data type and a default value expression are specified, an implicit type casting function inserted which converts the expression to the specified type. Example: Hits UInt32 DEFAULT 0 is internally represented as Hits UInt32 DEFAULT toUInt32(0).

A default value expression expr may reference arbitrary table columns and constants. ClickHouse checks that changes of the table structure do not introduce loops in the expression calculation. For INSERT, it checks that expressions are resolvable - that all columns they can be calculated from have been passed.
DEFAULT expr
Normal default value. If the value of such a column is not specified in an INSERT query, it is computed from expr.

Example:
MATERIALIZED expr
Materialized expression. Values of such columns are always calculated, they cannot be specified in INSERT queries.

Also, default value columns of this type are not included in the result of SELECT *. This is to preserve the invariant that the result of a SELECT * can always be inserted back into the table using INSERT. This behavior can be disabled with setting asterisk_include_materialized_columns.

Example:
EPHEMERAL [expr]
Ephemeral column. Columns of this type are not stored in the table and it is not possible to SELECT from them. The only purpose of ephemeral columns is to build default value expressions of other columns from them.

An insert without explicitly specified columns will skip columns of this type. This is to preserve the invariant that the result of a SELECT * can always be inserted back into the table using INSERT.

Example:

Calculated columns (synonym). Column of this type are not stored in the table and it is not possible to INSERT values into them.

When SELECT queries explicitly reference columns of this type, the value is computed at query time from expr. By default, SELECT * excludes ALIAS columns. This behavior can be disabled with setting asteriks_include_alias_columns.

When using the ALTER query to add new columns, old data for these columns is not written. Instead, when reading old data that does not have values for the new columns, expressions are computed on the fly by default. However, if running the expressions requires different columns that are not indicated in the query, these columns will additionally be read, but only for the blocks of data that need it.

If you add a new column to a table but later change its default expression, the values used for old data will change (for data where values were not stored on the disk). Note that when running background merges, data for columns that are missing in one of the merging parts is written to the merged part.

It is not possible to set default values for elements in nested data structures.

You can define a primary key when creating a table. Primary key can be specified in two ways:

Along with columns descriptions constraints could be defined:

boolean_expr_1 could by any boolean expression. If constraints are defined for the table, each of them will be checked for every row in INSERT query. If any constraint is not satisfied - server will raise an exception with constraint name and checking expression.

Adding large amount of constraints can negatively affect performance of big INSERT queries.

Defines storage time for values. Can be specified only for MergeTree-family tables. For the detailed description, see TTL for columns and tables.

By default, ClickHouse applies lz4 compression in the self-managed version, and zstd in ClickHouse Cloud.

For MergeTree-engine family you can change the default compression method in the compression section of a server configuration.

You can also define the compression method for each individual column in the CREATE TABLE query.

The Default codec can be specified to reference default compression which may depend on different settings (and properties of data) in runtime. Example: value UInt64 CODEC(Default) - the same as lack of codec specification.

Also you can remove current CODEC from the column and use default compression from config.xml:

Codecs can be combined in a pipeline, for example, CODEC(Delta, Default).

Compression is supported for the following table engines:

MergeTree family. Supports column compression codecs and selecting the default compression method by compression settings.

Log family. Uses the lz4 compression method by default and supports column compression codecs.

Set. Only supported the default compression.

Join. Only supported the default compression.

ClickHouse supports general purpose codecs and specialized codecs.

NONE - No compression.

LZ4 - Lossless data compression algorithm used by default. Applies LZ4 fast compression.

LZ4HC[(level)] - LZ4 HC (high compression) algorithm with configurable level. Default level: 9. Setting level <= 0 applies the default level. Possible levels: [1, 12]. Recommended level range: [4, 9].

ZSTD[(level)] - ZSTD compression algorithm with configurable level. Possible levels: [1, 22]. Default value: 1.

High compression levels are useful for asymmetric scenarios, like compress once, decompress repeatedly. Higher levels mean better compression and higher CPU usage.

DEFLATE_QPL - Deflate compression algorithm implemented by Intel® Query Processing Library. Some limitations apply:

DEFLATE_QPL is disabled by default and can only be used after setting configuration parameter enable_deflate_qpl_codec = 1.

DEFLATE_QPL requires a ClickHouse build compiled with SSE 4.2 instructions (by default, this is the case). Refer to Build Clickhouse with DEFLATE_QPL for more details.

DEFLATE_QPL works best if the system has a Intel® IAA (In-Memory Analytics Accelerator) offloading device. Refer to Accelerator Configuration and Benchmark with DEFLATE_QPL for more details.

DEFLATE_QPL-compressed data can only be transferred between ClickHouse nodes compiled with SSE 4.2 enabled.

These codecs are designed to make compression more effective by using specific features of data. Some of these codecs do not compress data themself. Instead, they prepare the data for a common purpose codec, which compresses it better than without this preparation.

Delta(delta_bytes) - Compression approach in which raw values are replaced by the difference of two neighboring values, except for the first value that stays unchanged. Up to delta_bytes are used for storing delta values, so delta_bytes is the maximum size of raw values. Possible delta_bytes values: 1, 2, 4, 8. The default value for delta_bytes is sizeof(type) if equal to 1, 2, 4, or 8. In all other cases, it's 1.

DoubleDelta(bytes_size) - Calculates delta of deltas and writes it in compact binary form. Possible bytes_size values: 1, 2, 4, 8, the default value is sizeof(type) if equal to 1, 2, 4, or 8. In all other cases, it's 1. Optimal compression rates are achieved for monotonic sequences with a constant stride, such as time series data. Can be used with any fixed-width type. Implements the algorithm used in Gorilla TSDB, extending it to support 64-bit types. Uses 1 extra bit for 32-bit deltas: 5-bit prefixes instead of 4-bit prefixes. For additional information, see Compressing Time Stamps in Gorilla: A Fast, Scalable, In-Memory Time Series Database.

Gorilla(bytes_size) - Calculates XOR between current and previous floating point value and writes it in compact binary form. The smaller the difference between consecutive values is, i.e. the slower the values of the series changes, the better the compression rate. Implements the algorithm used in Gorilla TSDB, extending it to support 64-bit types. Possible bytes_size values: 1, 2, 4, 8, the default value is sizeof(type) if equal to 1, 2, 4, or 8. In all other cases, it's 1. For additional information, see section 4.1 in Gorilla: A Fast, Scalable, In-Memory Time Series Database.

FPC(level, float_size) - Repeatedly predicts the next floating point value in the sequence using the better of two predictors, then XORs the actual with the predicted value, and leading-zero compresses the result. Similar to Gorilla, this is efficient when storing a series of floating point values that change slowly. For 64-bit values (double), FPC is faster than Gorilla, for 32-bit values your mileage may vary. Possible level values: 1-28, the default value is 12. Possible float_size values: 4, 8, the default value is sizeof(type) if type is Float. In all other cases, it's 4. For a detailed description of the algorithm see High Throughput Compression of Double-Precision Floating-Point Data.

T64 - Compression approach that crops unused high bits of values in integer data types (including Enum, Date and DateTime). At each step of its algorithm, codec takes a block of 64 values, puts them into 64x64 bit matrix, transposes it, crops the unused bits of values and returns the rest as a sequence. Unused bits are the bits, that do not differ between maximum and minimum values in the whole data part for which the compression is used.

DoubleDelta and Gorilla codecs are used in Gorilla TSDB as the components of its compressing algorithm. Gorilla approach is effective in scenarios when there is a sequence of slowly changing values with their timestamps. Timestamps are effectively compressed by the DoubleDelta codec, and values are effectively compressed by the Gorilla codec. For example, to get an effectively stored table, you can create it in the following configuration:

These codecs don't actually compress data, but instead encrypt data on disk. These are only available when an encryption key is specified by encryption settings. Note that encryption only makes sense at the end of codec pipelines, because encrypted data usually can't be compressed in any meaningful way.

Encryption codecs:

CODEC('AES-128-GCM-SIV') - Encrypts data with AES-128 in RFC 8452 GCM-SIV mode.

CODEC('AES-256-GCM-SIV') - Encrypts data with AES-256 in GCM-SIV mode.

These codecs use a fixed nonce and encryption is therefore deterministic. This makes it compatible with deduplicating engines such as ReplicatedMergeTree but has a weakness: when the same data block is encrypted twice, the resulting ciphertext will be exactly the same so an adversary who can read the disk can see this equivalence (although only the equivalence, without getting its content).
Example Example
ClickHouse supports temporary tables which have the following characteristics:

Temporary tables disappear when the session ends, including if the connection is lost.

A temporary table uses the Memory table engine when engine is not specified and it may use any table engine except Replicated and KeeperMap engines.

The DB can't be specified for a temporary table. It is created outside of databases.

Impossible to create a temporary table with distributed DDL query on all cluster servers (by using ON CLUSTER): this table exists only in the current session.

If a temporary table has the same name as another one and a query specifies the table name without specifying the DB, the temporary table will be used.

For distributed query processing, temporary tables used in a query are passed to remote servers.

To create a temporary table, use the following syntax:

In most cases, temporary tables are not created manually, but when using external data for a query, or for distributed (GLOBAL) IN. For more information, see the appropriate sections

It's possible to use tables with ENGINE = Memory instead of temporary tables.

'REPLACE' query allows you to update the table atomically.

If you need to delete some data from a table, you can create a new table and fill it with a SELECT statement that does not retrieve unwanted data, then drop the old table and rename the new one:

Instead of above, you can use the following:

All syntax forms for CREATE query also work for this query. REPLACE for a non-existent table will cause an error.

Consider the table:

Using REPLACE query to clear all data:

Using REPLACE query to change table structure:

You can add a comment to the table when you creating it.
Syntax Example
Query:

Result:

# data[].attributes.attachment_link.description
Creates a new table. This query can have various syntax forms depending on a use case.

# data[].attributes.body
https://clickhouse.com/docs/en/engines/database-engines/postgresql

# data[].attributes.attachment_link.description
Allows to connect to databases on a remote PostgreSQL server. Supports read and write operations (SELECT and INSERT queries) to exchange data between ClickHouse and PostgreSQL.

# data[].attributes.body
https://clickhouse.com/docs/en/engines/table-engines/special/join

# data[].attributes.attachment_link.readable_text
Optional prepared data structure for usage in JOIN operations.

See the detailed description of the CREATE TABLE query.

join_strictness - JOIN strictness.

k1[, k2, ...] - Key columns from the USING clause that the JOIN operation is made with.

Enter join_strictness and join_type parameters without quotes, for example, Join(ANY, LEFT, col1). They must match the JOIN operation that the table will be used for. If the parameters do not match, ClickHouse does not throw an exception and may return incorrect data.

Join table data is always located in the RAM. When inserting rows into a table, ClickHouse writes data blocks to the directory on the disk so that they can be restored when the server restarts.

If the server restarts incorrectly, the data block on the disk might get lost or damaged. In this case, you may need to manually delete the file with damaged data.

You can use INSERT queries to add data to the Join-engine tables. If the table was created with the ANY strictness, data for duplicate keys are ignored. With the ALL strictness, all rows are added.

Main use-cases for Join-engine tables are following:

Place the table to the right side in a JOIN clause.

Call the joinGet function, which lets you extract data from the table the same way as from a dictionary.

ALTER DELETE queries for Join-engine tables are implemented as mutations. DELETE mutation reads filtered data and overwrites data of memory and disk.

When creating a table, the following settings are applied:
join_use_nulls max_rows_in_join max_bytes_in_join join_overflow_mode join_any_take_last_row
Disables persistency for the Join and Set table engines.

Reduces the I/O overhead. Suitable for scenarios that pursue performance and do not require persistence.

Possible values:

Default value: 1.

The Join-engine tables can't be used in GLOBAL JOIN operations.

The Join-engine allows to specify join_use_nulls setting in the CREATE TABLE statement. SELECT query should have the same join_use_nulls value.

Creating the left-side table:

Creating the right-side Join table:

Joining the tables:

As an alternative, you can retrieve data from the Join table, specifying the join key value:

Deleting a row from the Join table:

# data[].attributes.attachment_link.description
Optional prepared data structure for usage in JOIN operations.

# data[].attributes.body
https://clickhouse.com/docs/en/sql-reference/dictionaries

# data[].attributes.attachment_link.readable_text
DELETE FROM removes rows from the table [db.]table that match the expression expr. The deleted rows are marked as deleted immediately and will be automatically filtered out of all subsequent queries. Cleanup of data happens asynchronously in the background. This feature is only available for the MergeTree table engine family.

For example, the following query deletes all rows from the hits table where the Title column contains the text hello:

Lightweight deletes are asynchronous by default. Set mutations_sync equal to 1 to wait for one replica to process the statement, and set mutations_sync to 2 to wait for all replicas.

The idea behind Lightweight Delete is that when a DELETE FROM table ... query is executed ClickHouse only saves a mask where each row is marked as either "existing" or as "deleted". Those "deleted" rows become invisible for subsequent queries, but physically the rows are removed only later by subsequent merges. Writing this mask is usually much more lightweight than what is done by ALTER table DELETE ... query.

The mask is implemented as a hidden _row_exists system column that stores True for all visible rows and False for deleted ones. This column is only present in a part if some rows in this part were deleted. In other words, the column is not persisted when it has all values equal to True.

When the column is present SELECT ... FROM table WHERE condition query internally is extended by an additional predicate on _row_exists and becomes similar to

At execution time the column _row_exists is read to figure out which rows are not visible and if there are many deleted rows it can figure out which granules can be fully skipped when reading the rest of the columns.

DELETE FROM table WHERE condition is translated into ALTER table UPDATE _row_exists = 0 WHERE condition mutation. Internally this mutation is executed in 2 steps:

1. SELECT count() FROM table WHERE condition for each individual part to figure out if the part is affected.

2. Mutate affected parts, and make hardlinks for unaffected parts. Mutating a part in fact only writes _row_exists column and just hardlinks all other columns' files in the case of Wide parts. But for Compact parts, all columns are rewritten because they all are stored together in one file.

So if we compare Lightweight Delete to ALTER DELETE in the first step they both do the same thing to figure out which parts are affected, but in the second step ALTER DELETE does much more work because it reads and rewrites all columns' files for the affected parts.

With the described implementation now we can see what can negatively affect 'DELETE FROM' execution time:

Heavy WHERE condition in DELETE query

Mutations queue filled with other mutations, because all mutations on a table are executed sequentially

Table having a very large number of data parts

Having a lot of data in Compact parts-in a Compact part, all columns are stored in one file.

# data[].attributes.attachment_link.description
DELETE FROM removes rows from the table [db.]table that match the expression expr. The deleted rows are marked as deleted immediately and will be automatically filtered out of all subsequent queries. Cleanup of data happens asynchronously in the background. This feature is only available for the MergeTree table engine family.

# data[].attributes.body
https://clickhouse.com/docs/en/guides/developer/lightweght-delete

# data[].attributes.attachment_link.readable_text
DELETE FROM removes rows from the table [db.]table that match the expression expr. The deleted rows are marked as deleted immediately and will be automatically filtered out of all subsequent queries. Cleanup of data happens asynchronously in the background. This feature is only available for the MergeTree table engine family.

For example, the following query deletes all rows from the hits table where the Title column contains the text hello:

Lightweight deletes are asynchronous by default. Set mutations_sync equal to 1 to wait for one replica to process the statement, and set mutations_sync to 2 to wait for all replicas.

The idea behind Lightweight Delete is that when a DELETE FROM table ... query is executed ClickHouse only saves a mask where each row is marked as either "existing" or as "deleted". Those "deleted" rows become invisible for subsequent queries, but physically the rows are removed only later by subsequent merges. Writing this mask is usually much more lightweight than what is done by ALTER table DELETE ... query.

The mask is implemented as a hidden _row_exists system column that stores True for all visible rows and False for deleted ones. This column is only present in a part if some rows in this part were deleted. In other words, the column is not persisted when it has all values equal to True.

When the column is present SELECT ... FROM table WHERE condition query internally is extended by an additional predicate on _row_exists and becomes similar to

At execution time the column _row_exists is read to figure out which rows are not visible and if there are many deleted rows it can figure out which granules can be fully skipped when reading the rest of the columns.

DELETE FROM table WHERE condition is translated into ALTER table UPDATE _row_exists = 0 WHERE condition mutation. Internally this mutation is executed in 2 steps:

1. SELECT count() FROM table WHERE condition for each individual part to figure out if the part is affected.

2. Mutate affected parts, and make hardlinks for unaffected parts. Mutating a part in fact only writes _row_exists column and just hardlinks all other columns' files in the case of Wide parts. But for Compact parts, all columns are rewritten because they all are stored together in one file.

So if we compare Lightweight Delete to ALTER DELETE in the first step they both do the same thing to figure out which parts are affected, but in the second step ALTER DELETE does much more work because it reads and rewrites all columns' files for the affected parts.

With the described implementation now we can see what can negatively affect 'DELETE FROM' execution time:

Heavy WHERE condition in DELETE query

Mutations queue filled with other mutations, because all mutations on a table are executed sequentially

Table having a very large number of data parts

Having a lot of data in Compact parts-in a Compact part, all columns are stored in one file.

# data[].attributes.attachment_link.description
DELETE FROM removes rows from the table [db.]table that match the expression expr. The deleted rows are marked as deleted immediately and will be automatically filtered out of all subsequent queries. Cleanup of data happens asynchronously in the background. This feature is only available for the MergeTree table engine family.

# data[].attributes.body
https://clickhouse.com/blog/handling-updates-and-deletes-in-clickhouse

# data[].attributes.attachment_link.readable_text
As the world's fastest database for real-time analytics, many ClickHouse workloads involve large amounts of data that is written once and not frequently modified (for example, telemetry events generated by IOT devices or customer clicks generated by an e-commerce website). While these are typically immutable, additional data sets critical to providing context during analytics (e.g., lookup tables with information based on device or customer ID) may require modifications.

Historically there have been multiple approaches to updating and deleting data in ClickHouse, depending on your goals and performance requirements. The rest of this article describes each approach and its trade-offs, as well as some recent developments with Lightweight Deletes that address some common challenges. We recommend best practices and highlight some important considerations when considering an approach.

Before proceeding, determine whether updates are the best way to solve your problem. For example, for infrequently changed data, versioning the data may be a better option. ClickHouse is the number one analytics database in terms of storage efficiency and query performance, so just saving multiple versions of data instead of updating may work better in many cases.

Lightweight Deletes represent the preferred and most efficient way to remove data from ClickHouse. Exposed via the DELETE FROM table syntax, users can specify a condition to remove specific rows as shown below:

This operation is asynchronous by default unless the setting mutations_sync is set to 1 (see below). When executing the delete, ClickHouse saves a mask for each row, indicating whether it is deleted in a _row_exists column. Subsequent queries, in turn, exclude those deleted rows as shown below.

Internally, ClickHouse orders data into parts, each containing column data files and indices. Regular merge cycles are responsible for combining (merging) and rewriting these parts. This ensures the number of files does not continue to grow as more data is inserted, keeping queries fast. These merges consider lightweight deletes, excluding those rows marked for deletion in the newly formed parts.

Released in 22.8, and still experimental as of the time of writing, lightweight deletes are on track to become production ready in one of the next releases. Until this time, the use of lightweight deletes requires setting allow_experimental_lightweight_delete=true.

Users should be aware that by relying on normal background merge cycles, rows will only be eventually removed from the disk. While excluded from search results, the rows will reside on disk until their part is merged. The time taken for this to happen is not deterministic. This has a few implications:

Space savings will not be as immediate as issuing a deletion through mutations - see below. Consider using mutations if space savings are critical, e.g., insufficient disk space.

With no guarantees for removal, users with compliance requirements may wish to use mutations to ensure data is deleted.

The cost of a lightweight delete operation depends on the number of matching rows in the WHERE clause and the current number of data parts. This operation will be most efficient when matching a small number of rows. Users should also be aware that Lightweight Deletes perform best on Wide parts, where the column data files are stored separately, vs. Compact parts, where a single file is used for all column data. The former allows the mask _row_exists to be stored as a separate file, thereby allowing it to be written independently of the other columns. Generally, compact parts will be formed after inserts. Once parts exceed a certain size (e.g., due to merging), the wide format is used. For most workloads, this shouldn't be a concern.

Finally, note that Lightweight Delete uses the same mutation queue and background threads we describe below. We recommend the documentation here for further details on the internal implementation.

The easiest way to update data in the ClickHouse table is to use ALTER...UPDATE statement.

This query will update col1 on the table table using a given filter.

Unlike some databases, ClickHouse's ALTER UPDATE statement is asynchronous by default. This means that the update occurs in the background and you will not get an instant effect on the table. This process of updating tables is called mutations.

An important note here is that updating data is a heavy query because ClickHouse has to do a lot of work to optimize storage and processing. A mutation operation forces all data parts containing those rows to be deleted to be re-written, with the target rows excluded when forming the new part. This can cause considerable I/O and cluster overhead, so use this with caution or consider the alternatives discussed below.

Like updates, deletes are also possible with mutations and offer an alternative to Lightweight Deletes. In most cases, Lightweight Deletes are more appropriate for data removal due to the mutation cost of rewriting all columns. More specifically, unlike Lightweight Deletes, all columns are rewritten vs. just a _row_exists mask column.

However, given the "eventual deletion of data from disk" property of Lightweight Deletes, users may prefer this mutation-based approach to achieve guaranteed disk space savings. Furthermore, this approach can be appropriate when users need to guarantee data removal from the disk, e.g., due to compliance reasons.

In this query, all rows with col2 value of 3 are deleted. Similar to other mutations, deletes are also asynchronous by default. This can be made synchronous using the same mutations_sync setting described above.

Since mutations run asynchronously, monitoring can be performed via the system.mutations table. This allows users to need to check their progress for a specific mutation on a table.

If the value of is_done is 0 for a specific mutation, it's still being executed. Mutations are executed for each table part where mutated parts become available instantly:

For users requiring synchronous updates, the mutations_sync parameter can be set to a value of 1 (or 2 if we also want to wait till all replicas are also updated):

Now our update query will wait for the mutation to complete:

Note how it took 1 second for this query to finish while ClickHouse waited for the background mutation to complete. Note this parameter also applies to Lightweight Deletes.

In some cases, users need to update the values of an entire column. Initially, users may try to achieve this by using the ALTER TABLE query without a WHERE clause. This, however, fails, as shown below:

ClickHouse won't let you update an entire table because updates are heavy. One way to force ClickHouse to accept this operation is with an always-true filter:

However, a more optimal approach is to create a new column with the new value as the default and then switch the old and new columns. For example:

We use the default value of the col1_new column to specify what we want to use as the updated value. This is safe and much more efficient since we skip the heavy mutation operation here.

Sometimes, we need to delete or update rows based on relations; thus, we have to join tables. This is best achieved in ClickHouse using the Join table engine and joinGet function. Suppose we have two tables - one with all pageviews and the other with all logins tracked:

The difference between the tables is that the logins table only stores a single event per session. Suppose at some point in time, we decided to add the session_id column to the logins table:

We now need to update the logins.session_id column with corresponding values from the pageviews table using a JOIN on user_id and time:

First, we need to create and populate a special table:

This table will allow us to use the joinGet function to fetch values based on JOIN while executing an update query:

And we can see, the logins table was updated accordingly with the JOIN:

Because we have altered the logins table by adding the session_id column, we can DROP the pageviews_join table once the changes are done (check the system.mutations table to be sure before dropping):

The same approach can be used to delete data with lightweight or mutation based deletes.

If we have to delete big blocks of data, users can partition a table so that partitions can be dropped as required. This is a lightweight operation. Suppose we have the following table:

CREATE TABLE hits ( `project` String, `url` String, `time` DateTime, `hits` UInt32 ) ENGINE = MergeTree PARTITION BY project ORDER BY (project, path, time)

Having this table partitioned by the project column allows us to delete rows with a specific project value by dropping an entire partition. Let's delete everything with project = c:

Here, c is the project column value we want to delete:

The list of available partitions can be found in the system.parts table:

We can also move partitions between tables (e.g., if we want to move data to a trash table instead of deleting it) using the DETACH and ATTACH statements.

When setting up partitioning in your DDL, be aware of the common pitfall of partitioning by a column or expression with high cardinality. This can cause many parts to be created, leading to performance issues.

In cases of time-series data, we might want to delete outdated data regularly. ClickHouse has the TTL feature for this exact use case. This requires configuring a table and specifying which data we want to be dropped and when. Suppose we want to delete data older than one month from our hits table:

Here we ask ClickHouse to delete all rows that have a time column value more than one month ago from the current time. TTL can also be set on columns to reset their values to defaults after a period of time. This operation can be made more efficient by partitioning by date, rounded to an appropriate unit of time, e.g., days. ClickHouse will, in turn, automatically drop data in the most efficient way when executing a TTL rule. Again, tables should not be partitioned by time columns of high cardinality (e.g. millisecond granularity) to avoid high part counts. Typically partitioning by day or month is sufficient for most TTL operations.

If we have to update individual rows frequently, we can use the CollapsingMergeTree engine to efficiently manage data updates.

Suppose we have a table with article stats to track each article's reading depth. We want a single row that shows how deeply each user has read each article. The challenge here is that we have to update the actual reading progress while the user reads an article. Let's create a table for our data:

CREATE TABLE article_reads ( `user_id` UInt32, `article_id` UInt32, `read_to` UInt8, `read_start` DateTime, `read_end` DateTime, `sign` Int8 ) ENGINE = CollapsingMergeTree(sign) ORDER BY (read_start, article_id, user_id)

The special sign column is used for CollapsingMergeTree as a way to tell ClickHouse we want to update the specific row. If we insert -1 for the sign column, the whole row is going to be deleted. If we insert a row with sign = 1, ClickHouse will keep the row. Rows to update are identified based on the sorting key used in the ORDER BY () DDL statement when creating the table:

To satisfy the deduplication condition on the sorting key, we have to insert the same value for the read_start, article_id, user_id columns to update a row. For example, when a user starts reading an article, we insert the following row:

We now have a single row in the table:

A minute later, when the user reaches 70% of our article, we insert the following 2 rows:

The first row with sign=-1 is used to tell ClickHouse it should delete the existing row (based on values in the ORDER BY tuple - read_start, article_id and user_id columns). The second inserted row (with sign=1) is the new row with the read_to column set to the new value of 70.

Since data updates are happening in the background, with eventual consistency of results, we should filter on the sign column to get correct results:

The CollapsingMergreTree engine will now take care of removing canceled rows from storage efficiently in the background, so we don't have to delete them manually. You can find further examples of using the CollapsingMergeTree engine here.

For more sophisticated cases, we might want to use versioning based on the ReplacingMergeTree engine. This engine implements an efficient way of doing what's known as UPSERT in other DBMSs, by using a special version column to track which rows should be removed. If multiple rows with the same sorting key are present, only the one with the largest version is kept in storage while others are removed:

For our previous example with articles reading, we can use the following structure:

CREATE TABLE article_reads ( `user_id` UInt32, `article_id` UInt32, `read_to` UInt8, `read_time` DateTime, `version` Int32 ) ENGINE = ReplacingMergeTree(version) ORDER BY (article_id, user_id)

Note the special version numeric column, which will be used by the ReplacingMergeTree engine to mark rows for removal. Let's simulate a user reading an article over time from 0 to 80%:

Here we increase the version column value as reading progress is tracked. The process of row removal is also executed in the background through normal merge cycles, so we need to filter based on the latest version at query time:

Or we can use LIMIT 1 BY to get the list of rows with the latest version:

SELECT user_id, article_id, read_to FROM article_reads ORDER BY version DESC LIMIT 1 BY user_id, article_id ┌─user_id─┬─article_id─┬─read_to─┐ │ 1 │ 12 │ 80 │ └─────────┴────────────┴─────────┘

And again, we do not have to care about removal of old versions - this is done automatically by ClickHouse in the background.

Updating and deleting data in analytical environments can be challenging and impact database performance dramatically. To address this ClickHouse provides multiple powerful ways to update and delete data efficiently for different cases:

Lightweight Deletes via the DELETE FROM syntax for removing data from ClickHouse. This is the most efficient way to delete data, provided that immediate disk space savings are not required, and users can tolerate deleted data "existing" on disk.

Mutation-based deletes via ALTER...DELETE in cases where immediate disk space savings are required. For example, compliance requirements require guarantees around removing data from disk.

Mutation-based updates using ALTER...UPDATE in case of irregular and non-frequent changes

Using TTLs for regular removal of (outdated) data based on date/time,

Using CollapsingMergeTree to update or remove individual rows frequently.

Using ReplacingMergeTree to implement upsert (insert/update) based on versioning.

Dropping partitions when removing large blocks of data regularly.

Creating new columns (and dropping old ones) might also be a more efficient way to update entire tables.

# data[].attributes.attachment_link.title
Handling Updates and Deletes in ClickHouse

# data[].attributes.attachment_link.description
With the recent addition of Lightweight Deletes, read about the latest best practices for handling updates and deletes in ClickHouse.

# data[].attributes.body
https://clickhouse.com/docs/en/guides/developer/transactional

# data[].attributes.attachment_link.title
Transactional (ACID) support | ClickHouse Docs

# data[].attributes.attachment_link.description
Case 1: INSERT into one partition, of one table, of the MergeTree* family

# data[].attributes.body
https://clickhouse.com/docs/en/sql-reference/statements/alter#alter-mutations

# data[].attributes.attachment_link.description
Most ALTER TABLE queries modify table settings or data:

# data[].attributes.body
https://clickhouse.com/docs/en/sql-reference/statements/create/view#live-view

# data[].attributes.attachment_link.readable_text
Creates a new view. Views can be normal, materialized, live, and window (live view and window view are experimental features).

Syntax:

Normal views do not store any data. They just perform a read from another table on each access. In other words, a normal view is nothing more than a saved query. When reading from a view, this saved query is used as a subquery in the FROM clause.

As an example, assume you've created a view:

and written a query:

This query is fully equivalent to using the subquery:

Parametrized views are similar to normal views, but can be created with parameters which are not resolved immediately. These views can be used with table functions, which specify the name of the view as function name and the parameter values as its arguments.

The above creates a view for table which can be used as table function by substituting parameters as shown below.

Materialized views store data transformed by the corresponding SELECT query.

When creating a materialized view without TO [db].[table], you must specify ENGINE - the table engine for storing data.

When creating a materialized view with TO [db].[table], you must not use POPULATE.

A materialized view is implemented as follows: when inserting data to the table specified in SELECT, part of the inserted data is converted by this SELECT query, and the result is inserted in the view.

If you specify POPULATE, the existing table data is inserted into the view when creating it, as if making a CREATE TABLE ... AS SELECT ... . Otherwise, the query contains only the data inserted in the table after creating the view. We do not recommend using POPULATE, since data inserted in the table during the view creation will not be inserted in it.

A SELECT query can contain DISTINCT, GROUP BY, ORDER BY, LIMIT. Note that the corresponding conversions are performed independently on each block of inserted data. For example, if GROUP BY is set, data is aggregated during insertion, but only within a single packet of inserted data. The data won't be further aggregated. The exception is when using an ENGINE that independently performs data aggregation, such as SummingMergeTree.

The execution of ALTER queries on materialized views has limitations, for example, you can not update the SELECT query, so this might be inconvenient. If the materialized view uses the construction TO [db.]name, you can DETACH the view, run ALTER for the target table, and then ATTACH the previously detached ( DETACH) view.

Note that materialized view is influenced by optimize_on_insert setting. The data is merged before the insertion into a view.

Views look the same as normal tables. For example, they are listed in the result of the SHOW TABLES query.

To delete a view, use DROP VIEW. Although DROP TABLE works for VIEWs as well.

Live views store result of the corresponding SELECT query and are updated any time the result of the query changes. Query result as well as partial result needed to combine with new data are stored in memory providing increased performance for repeated queries. Live views can provide push notifications when query result changes using the WATCH query.

Live views are triggered by insert into the innermost table specified in the query.

Live views work similarly to how a query in a distributed table works. But instead of combining partial results from different servers they combine partial result from current data with partial result from the new data. When a live view query includes a subquery then the cached partial result is only stored for the innermost subquery.

You can monitor changes in the LIVE VIEW query result using WATCH query.
Example:
Watch a live view while doing a parallel insert into the source table.

Or add EVENTS clause to just get change events.
Example:
You can execute SELECT query on a live view in the same way as for any regular view or a table. If the query result is cached it will return the result immediately without running the stored query on the underlying tables.

You can force live view refresh using the ALTER LIVE VIEW [db.]table_name REFRESH statement.

When a live view is created with a WITH REFRESH clause then it will be automatically refreshed after the specified number of seconds elapse since the last refresh or trigger.

If the refresh value is not specified then the value specified by the periodic_live_view_refresh setting is used.
Example:
└─────────────────────┴──────────┘ └─────────────────────┴──────────┘ └─────────────────────┴──────────┘

Code: 60. DB::Exception: Received from localhost:9000. DB::Exception: Table default.lv does not exist..

Most common uses of live view tables include:

Providing push notifications for query result changes to avoid polling.

Caching results of most frequent queries to provide immediate query results.

Watching for table changes and triggering a follow-up select queries.

Watching metrics from system tables using periodic refresh.

See Also
Window view can aggregate data by time window and output the results when the window is ready to fire. It stores the partial aggregation results in an inner(or specified) table to reduce latency and can push the processing result to a specified table or push notifications using the WATCH query.

Creating a window view is similar to creating MATERIALIZED VIEW. Window view needs an inner storage engine to store intermediate data. The inner storage can be specified by using INNER ENGINE clause, the window view will use AggregatingMergeTree as the default inner engine.

When creating a window view without TO [db].[table], you must specify ENGINE - the table engine for storing data.

Time window functions are used to get the lower and upper window bound of records. The window view needs to be used with a time window function.

Window view supports processing time and event time process.

Processing time allows window view to produce results based on the local machine's time and is used by default. It is the most straightforward notion of time but does not provide determinism. The processing time attribute can be defined by setting the time_attr of the time window function to a table column or using the function now(). The following query creates a window view with processing time.

Event time is the time that each individual event occurred on its producing device. This time is typically embedded within the records when it is generated. Event time processing allows for consistent results even in case of out-of-order events or late events. Window view supports event time processing by using WATERMARK syntax.

Window view provides three watermark strategies:

STRICTLY_ASCENDING: Emits a watermark of the maximum observed timestamp so far. Rows that have a timestamp smaller to the max timestamp are not late.

ASCENDING: Emits a watermark of the maximum observed timestamp so far minus 1. Rows that have a timestamp equal and smaller to the max timestamp are not late.

BOUNDED: WATERMARK=INTERVAL. Emits watermarks, which are the maximum observed timestamp minus the specified delay.

The following queries are examples of creating a window view with WATERMARK:

By default, the window will be fired when the watermark comes, and elements that arrived behind the watermark will be dropped. Window view supports late event processing by setting ALLOWED_LATENESS=INTERVAL. An example of lateness handling is:

Note that elements emitted by a late firing should be treated as updated results of a previous computation. Instead of firing at the end of windows, the window view will fire immediately when the late event arrives. Thus, it will result in multiple outputs for the same window. Users need to take these duplicated results into account or deduplicate them.

You can modify SELECT query that was specified in the window view by using ALTER TABLE ... MODIFY QUERY statement. The data structure resulting in a new SELECT query should be the same as the original SELECT query when with or without TO [db.]name clause. Note that the data in the current window will be lost because the intermediate state cannot be reused.

Window view supports the WATCH query to monitoring changes, or use TO syntax to output the results to a table.

WATCH query acts similar as in LIVE VIEW. A LIMIT can be specified to set the number of updates to receive before terminating the query. The EVENTS clause can be used to obtain a short form of the WATCH query where instead of the query result you will just get the latest query watermark.

window_view_clean_interval: The clean interval of window view in seconds to free outdated data. The system will retain the windows that have not been fully triggered according to the system time or WATERMARK configuration, and the other data will be deleted.

window_view_heartbeat_interval: The heartbeat interval in seconds to indicate the watch query is alive.

wait_for_window_view_fire_signal_timeout: Timeout for waiting for window view fire signal in event time processing.

Suppose we need to count the number of click logs per 10 seconds in a log table called data, and its table structure is:

First, we create a window view with tumble window of 10 seconds interval:

Then, we use the WATCH query to get the results.

When logs are inserted into table data,

The WATCH query should print the results as follows:

Alternatively, we can attach the output to another table using TO syntax.

Additional examples can be found among stateful tests of ClickHouse (they are named *window_view* there).

The window view is useful in the following scenarios:

Monitoring: Aggregate and calculate the metrics logs by time, and output the results to a target table. The dashboard can use the target table as a source table.

Analyzing: Automatically aggregate and preprocess data in the time window. This can be useful when analyzing a large number of logs. The preprocessing eliminates repeated calculations in multiple queries and reduces query latency.

# data[].attributes.attachment_link.description
Creates a new view. Views can be normal, materialized, live, and window (live view and window view are experimental features).

# data[].attributes.body
https://clickhouse.com/docs/en/guides/developer/cascading-materialized-views

# data[].attributes.attachment_link.readable_text
This example demonstrates how to create a Materialized View, and then how to cascade a second Materialized View on to the first. In this page, you will see how to do it, many of the possibilities, and the limitations. Different use cases can be answered by creating a Materialized view using a second Materialized view as the source.

Example:

We will use a fake dataset with the number of views per hour for a group of domain names.

Our Goal

1. We need the data aggregated by month for each domain name,

2. We also need the data aggregated by year for each domain name.

You could choose one of these options:

Write queries that will read and aggregate the data during the SELECT request

Prepare the data at the ingest time to a new format

Prepare the data at the time of ingest to a specific aggregation.

Preparing the data using Materialized views will allow you to limit the amount of data and calculation ClickHouse needs to do, making your SELECT requests faster.

Create the source table, because our goals involve reporting on the aggregated data and not the individual rows, we can parse it, pass the information on to the Materialized Views, and discard the actual incoming data. This meets our goals and saves on storage so we will use the Null table engine.

For the first Materialized View, we need to create the Target table, for this example, it will be analytics.monthly_aggregated_data and we will store the sum of the views by month and domain name.

The Materialized View that will forward the data on the target table will look like this:

Now we will create the second Materialized view that will be linked to our previous target table monthly_aggregated_data.

First, we will create a new target table that will store the sum of views aggregated by year for each domain name.

This step defines the cascade. The FROM statement will use the monthly_aggregated_data table, this means the data flow will be:

1. The data comes to the hourly_data table.

2. ClickHouse will forward the data received to the first Materialized View monthly_aggregated_data table,

3. Finally, the data received in step 2 will be forwarded to the year_aggregated_data.

Now is the time to test our cascade materialized view by inserting some data:

If you SELECT the contents of analytics.hourly_data you will see the following because the table engine is Null, but the data was processed.

We have used a small dataset to be sure we can follow and compare the result with what we are expecting, once your flow is correct with a small data set, you could just move to a large amount of data.

If you try to query the target table by selecting the sumCountViews field, you will see the binary representation (in some terminals), as the value is not stored as a number but as an AggregateFunction type. To get the final result of the aggregation you should use the-Merge suffix.

You can see the special characters stored in AggregateFunction with this query:

Instead, let's try using the Merge suffix to get the sumCountViews value:

In the AggregatingMergeTree we have defined the AggregateFunction as sum, so we can use the sumMerge. When we use the function avg on the AggregateFunction, we will use avgMerge, and so forth.

Now we can review that the Materialized Views answer the goal we have defined.

Now that we have the data stored in the target table monthly_aggregated_data we can get the data aggregated by month for each domain name:

The data aggregated by year for each domain name:

# data[].attributes.attachment_link.description
HowTo use multiple materialized views from a source table.

# data[].attributes.body
https://clickhouse.com/docs/en/sql-reference/statements/select/join#processing-of-empty-or-null-cells

# data[].attributes.attachment_link.readable_text
Join produces a new table by combining columns from one or multiple tables by using values common to each. It is a common operation in databases with SQL support, which corresponds to relational algebra join. The special case of one table join is often referred to as "self-join".
Syntax
Expressions from ON clause and columns from USING clause are called "join keys". Unless otherwise stated, join produces a Cartesian product from rows with matching "join keys", which might produce results with much more rows than the source tables.

All standard SQL JOIN types are supported:

INNER JOIN, only matching rows are returned.

LEFT OUTER JOIN, non-matching rows from left table are returned in addition to matching rows.

RIGHT OUTER JOIN, non-matching rows from right table are returned in addition to matching rows.

FULL OUTER JOIN, non-matching rows from both tables are returned in addition to matching rows.

CROSS JOIN, produces cartesian product of whole tables, "join keys" are not specified.

JOIN without specified type implies INNER. Keyword OUTER can be safely omitted. Alternative syntax for CROSS JOIN is specifying multiple tables in FROM clause separated by commas.

Additional join types available in ClickHouse:

LEFT SEMI JOIN and RIGHT SEMI JOIN, a whitelist on "join keys", without producing a cartesian product.

LEFT ANTI JOIN and RIGHT ANTI JOIN, a blacklist on "join keys", without producing a cartesian product.

LEFT ANY JOIN, RIGHT ANY JOIN and INNER ANY JOIN, partially (for opposite side of LEFT and RIGHT) or completely (for INNER and FULL) disables the cartesian product for standard JOIN types.

ASOF JOIN and LEFT ASOF JOIN, joining sequences with a non-exact match. ASOF JOIN usage is described below.

The default join type can be overridden using join_default_strictness setting.

The behavior of ClickHouse server for ANY JOIN operations depends on the any_join_distinct_right_table_keys setting.
See also
Use the cross_to_inner_join_rewrite setting to define the behavior when ClickHouse fails to rewrite a CROSS JOIN as an INNER JOIN. The default value is 1, which allows the join to continue but it will be slower. Set cross_to_inner_join_rewrite to 0 if you want an error to be thrown, and set it to 2 to not run the cross joins but instead force a rewrite of all comma/cross joins. If the rewriting fails when the value is 2, you will receive an error message stating "Please, try to simplify WHERE section".

An ON section can contain several conditions combined using the AND and OR operators. Conditions specifying join keys must refer both left and right tables and must use the equality operator. Other conditions may use other logical operators but they must refer either the left or the right table of a query.

Rows are joined if the whole complex condition is met. If the conditions are not met, still rows may be included in the result depending on the JOIN type. Note that if the same conditions are placed in a WHERE section and they are not met, then rows are always filtered out from the result.

The OR operator inside the ON clause works using the hash join algorithm - for each OR argument with join keys for JOIN, a separate hash table is created, so memory consumption and query execution time grow linearly with an increase in the number of expressions OR of the ON clause.
Example
Consider table_1 and table_2:

Query with one join key condition and an additional condition for table_2:

Note that the result contains the row with the name C and the empty text column. It is included into the result because an OUTER type of a join is used.

Query with INNER type of a join and multiple conditions:

Result:

Query with INNER type of a join and condition with OR:

Result:

Query with INNER type of a join and conditions with OR and AND:

Result:

ASOF JOIN is useful when you need to join records that have no exact match.

Algorithm requires the special column in tables. This column:

Syntax ASOF JOIN ... ON:

You can use any number of equality conditions and exactly one closest match condition. For example, SELECT count() FROM table_1 ASOF LEFT JOIN table_2 ON table_1.a == table_2.b AND table_2.t <= table_1.t.

Syntax ASOF JOIN ... USING:

ASOF JOIN uses equi_columnX for joining on equality and asof_column for joining on the closest match with the table_1.asof_column >= table_2.asof_column condition. The asof_column column is always the last one in the USING clause.

For example, consider the following tables:

ASOF JOIN can take the timestamp of a user event from table_1 and find an event in table_2 where the timestamp is closest to the timestamp of the event from table_1 corresponding to the closest match condition. Equal timestamp values are the closest if available. Here, the user_id column can be used for joining on equality and the ev_time column can be used for joining on the closest match. In our example, event_1_1 can be joined with event_2_1 and event_1_2 can be joined with event_2_3, but event_2_2 can't be joined.

There are two ways to execute join involving distributed tables:

When using a normal JOIN, the query is sent to remote servers. Subqueries are run on each of them in order to make the right table, and the join is performed with this table. In other words, the right table is formed on each server separately.

When using GLOBAL ... JOIN, first the requestor server runs a subquery to calculate the right table. This temporary table is passed to each remote server, and queries are run on them using the temporary data that was transmitted.

Be careful when using GLOBAL. For more information, see the Distributed subqueries section.

INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN queries support the implicit type conversion for "join keys". However the query can not be executed, if join keys from the left and the right tables cannot be converted to a single type (for example, there is no data type that can hold all values from both UInt64 and Int64, or String and Int32).
Example
Consider the table t_1:

and the table t_2:

The query

returns the set:

While joining tables, the empty cells may appear. The setting join_use_nulls define how ClickHouse fills these cells.

If the JOIN keys are Nullable fields, the rows where at least one of the keys has the value NULL are not joined.

The columns specified in USING must have the same names in both subqueries, and the other columns must be named differently. You can use aliases to change the names of columns in subqueries.

The USING clause specifies one or more columns to join, which establishes the equality of these columns. The list of columns is set without brackets. More complex join conditions are not supported.

For multiple JOIN clauses in a single SELECT query:

Taking all the columns via * is available only if tables are joined, not subqueries.

The PREWHERE clause is not available.

Arbitrary expressions cannot be used in ON, WHERE, and GROUP BY clauses, but you can define an expression in a SELECT clause and then use it in these clauses via an alias.

When running a JOIN, there is no optimization of the order of execution in relation to other stages of the query. The join (a search in the right table) is run before filtering in WHERE and before aggregation.

Each time a query is run with the same JOIN, the subquery is run again because the result is not cached. To avoid this, use the special Join table engine, which is a prepared array for joining that is always in RAM.

In some cases, it is more efficient to use IN instead of JOIN.

If you need a JOIN for joining with dimension tables (these are relatively small tables that contain dimension properties, such as names for advertising campaigns), a JOIN might not be very convenient due to the fact that the right table is re-accessed for every query. For such cases, there is a "dictionaries" feature that you should use instead of JOIN. For more information, see the Dictionaries section.

By default, ClickHouse uses the hash join algorithm. ClickHouse takes the right_table and creates a hash table for it in RAM. If join_algorithm = 'auto' is enabled, then after some threshold of memory consumption, ClickHouse falls back to merge join algorithm. For JOIN algorithms description see the join_algorithm setting.

If you need to restrict JOIN operation memory consumption use the following settings:

When any of these limits is reached, ClickHouse acts as the join_overflow_mode setting instructs.

Example:

# data[].attributes.attachment_link.description
Join produces a new table by combining columns from one or multiple tables by using values common to each. It is a common operation in databases with SQL support, which corresponds to relational algebra join. The special case of one table join is often referred to as "self-join".