Установка и использование ClickHouse на Debian 10

Автор выбрал фонд Free and Open Source Fund для получения пожертвования в рамках программы Write for DOnations.

Введение

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

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

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

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

Предварительные требования

• Один Debian 10 с пользователем с возможностями sudo без полномочий root и настройкой брандмауэра. Вы можете использовать руководство по начальной настройке сервера для создания пользователя и настройки брандмауэра.
• (Необязательно) Вспомогательный Debian 10 с пользователем с возможностями sudo без полномочий root и настройкой брандмауэра. Вы можете использовать руководство по начальной настройке сервера.

Шаг 1 — Установка ClickHouse

В этом разделе мы установим серверные и клиентские программы ClickHouse, используя apt.

Сначала SSH на ваш сервер, запустив:

ssh sammy@your_server_ip

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

sudo apt install dirmngr

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

sudo apt-key adv --keyserver keyserver.ubuntu.com --recv E0C56BD4

Вы увидите примерно следующий результат:

Output
Executing: /tmp/apt-key-gpghome.JkkcKnBAFY/gpg.1.sh --keyserver keyserver.ubuntu.com --recv E0C56BD4

gpg: key C8F1E19FE0C56BD4: public key "ClickHouse Repository Key <milovidov@yandex-team.ru>" imported
gpg: Total number processed: 1
gpg:               imported: 1

Результат подтверждает, что проверка выполнена и добавлен ключ.

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

echo "deb http://repo.yandex.ru/clickhouse/deb/stable/ main/" | sudo tee /etc/apt/sources.list.d/clickhouse.list

Здесь вы передали вывод echo на sudo tee, чтобы этот вывод мог распечатать файл, принадлежащий root.

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

sudo apt update

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

Начиная с версии ClickHouse 19.3.6, определенные конфигурации OpenSSL 1.1.1, например MinProtocol и CipherVersion, читаются некорректно. Чтобы обойти эту несовместимость, измените файл конфигурации OpenSSL и закомментируйте строку ssl_conf = ssl_sect в /etc/ssl/openssl.cnf​​​.

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

sudo nano /etc/ssl/openssl.cnf

Затем закомментируйте строку, содержащую ssl_conf = ssl_sect​​​1​​​, как в следующем файле:

...

tsa_name		= yes	# Must the TSA name be included in the reply?
				# (optional, default: no)
ess_cert_id_chain	= no	# Must the ESS cert id chain be included?
				# (optional, default: no)
ess_cert_id_alg		= sha1	# algorithm to compute certificate
				# identifier (optional, default: sha1)
[default_conf]
#ssl_conf = ssl_sect

[ssl_sect]
...

Теперь после корректировки конфигурации OpenSSL вы готовы установить пакеты сервера и клиента ClickHouse. Установите их с помощью:

sudo apt install clickhouse-server clickhouse-client

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

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

Шаг 2 — Запуск службы

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

Запустите службу clickhouse-server, выполнив:

sudo service clickhouse-server start

Предыдущая команда не будет отображать никакие результаты. Чтобы убедиться, что служба работает успешно, выполните:

sudo service clickhouse-server status

Результат будет выглядеть примерно так:

Output
● clickhouse-server.service - ClickHouse Server (analytic DBMS for big data)
   Loaded: loaded (/etc/systemd/system/clickhouse-server.service; enabled; vendor preset: enabled)
   Active: active (running) since Sat 2018-12-22 07:23:20 UTC; 1h 9min ago
 Main PID: 27101 (ClickHouse-serv)
    Tasks: 34 (limit: 1152)
   CGroup: /system.slice/ClickHouse-server.service
           └─27101 /usr/bin/ClickHouse-server --config=/etc/ClickHouse-server/config.xml

Результат означает, что сервер работает.

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

Шаг 3 — Создание баз данных и таблиц

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

clickhouse-client --password

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

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

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

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

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

Теперь, когда вы находитесь в командной строке ClickHouse, создайте базу данных test​​​​​, выполнив:

CREATE DATABASE test;

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

Output
CREATE DATABASE test

Ok.

0 rows in set. Elapsed: 0.003 sec.

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

Синтаксис для создания таблиц в ClickHouse соответствует этому примеру структуры:

CREATE TABLE table_name
(
    column_name1 column_type [options],
    column_name2 column_type [options],
    ...
) ENGINE = engine

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

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

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

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

• Date: используется для хранения дат, соответствующих формату ГГГГ-ММ-ДД.

• DateTime: используется для хранения дат в сочетании со временем и соответствует формату ГГГГ-ММ-ДД ЧЧ-ММ-СС.

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

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

USE test;

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

Output
USE test

Ok.

0 rows in set. Elapsed: 0.001 sec.

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

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

CREATE TABLE visits (
 id UInt64,
 duration Float64,
 url String,
 created DateTime
) ENGINE = MergeTree() 
PRIMARY KEY id 
ORDER BY id;

Вот разбивка того, что делает команда. Вы создаете таблицу с именем visits​​, которая содержит четыре столбца:

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

• duration: столбец float используется для хранения продолжительности каждого посещения в секундах. Столбцы float могут хранить десятичные значения, такие как 12,50.

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

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

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

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

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

При выполнении оператора create вы увидите следующий вывод:

Output
CREATE TABLE visits
(
    id UInt64,
    duration Float64,
    url String,
    created DateTime
)
ENGINE = MergeTree()
PRIMARY KEY id
ORDER BY id

Ok.

0 rows in set. Elapsed: 0.010 sec.

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

Шаг 4 — Установка, обновление и удаление данных и столбцов

На этом этапе вы будете использовать вашу таблицу visits для вставки, обновления и удаления данных. Следующая команда является примером синтаксиса для вставки строк в таблицу ClickHouse:

INSERT INTO table_name VALUES (column_1_value, column_2_value, ....);

Теперь вставьте несколько строк примеров данных о посещении сайта в таблицу visits, выполнив каждое из следующих операторов:

INSERT INTO visits VALUES (1, 10.5, 'http://example.com', '2019-01-01 00:01:01');

INSERT INTO visits VALUES (2, 40.2, 'http://example1.com', '2019-01-03 10:01:01');

INSERT INTO visits VALUES (3, 13, 'http://example2.com', '2019-01-03 12:01:01');

INSERT INTO visits VALUES (4, 2, 'http://example3.com', '2019-01-04 02:01:01');

Вы увидите следующий вывод, повторяющийся для каждого оператора вставки.

Output
INSERT INTO visits VALUES

Ok.

1 rows in set. Elapsed: 0.004 sec.

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

Теперь вы добавите дополнительный столбец в таблицу visits. При добавлении или удалении столбцов из существующих таблиц ClickHouse поддерживает синтаксис ALTER.

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

ALTER TABLE table_name ADD COLUMN column_name column_type;

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

ALTER TABLE visits ADD COLUMN location String;

Результат будет выглядеть примерно так:

Output
ALTER TABLE visits
    ADD COLUMN
    location String


Ok.

0 rows in set. Elapsed: 0.014 sec.

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

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

Следующий синтаксис является примером массового обновления строк:

ALTER TABLE table_name UPDATE  column_1 = value_1, column_2 = value_2 ...  WHERE  filter_conditions;

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

ALTER TABLE visits UPDATE url = 'http://example2.com' WHERE duration < 15;

Результат массового обновления будет выглядеть следующим образом:

Output
ALTER TABLE visits
    UPDATE url = 'http://example2.com' WHERE duration < 15


Ok.

0 rows in set. Elapsed: 0.003 sec.

Результат показывает, что ваш запрос на обновление выполнен успешно. 0 rows in set​​​ в выводе означает, что запрос не вернул ни одной строки; это станет причиной каких-либо запросов на обновление и удаление.

Пример синтаксиса массового удаления строк аналогичен обновлению строк и имеет следующую структуру:

ALTER TABLE table_name DELETE WHERE filter_conditions;

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

ALTER TABLE visits DELETE WHERE duration < 5;

Результат оператора массового удаления будет выглядеть следующим образом:

Output
ALTER TABLE visits
    DELETE WHERE duration < 5


Ok.

0 rows in set. Elapsed: 0.003 sec.

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

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

ALTER TABLE table_name DROP COLUMN column_name;

Удалите столбец location, который вы добавили ранее, выполнив следующее:

ALTER TABLE visits DROP COLUMN location;

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

Output
ALTER TABLE visits
    DROP COLUMN
    location String


Ok.

0 rows in set. Elapsed: 0.010 sec.

Теперь, когда вы успешно вставили, обновили и удалили строки и столбцы в своей таблице visits​​​​​​, мы перейдем к запросу данных в следующем шаге.

Шаг 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 +.

SELECT url, duration FROM visits WHERE url = 'http://example2.com' LIMIT 2;

Результат будет выглядеть следующим образом:

Output
SELECT
    url,
    duration
FROM visits
WHERE url = 'http://example2.com'
LIMIT 2

┌─url─────────────────┬─duration─┐
│ http://example2.com │     10.5 │
└─────────────────────┴──────────┘
┌─url─────────────────┬─duration─┐
│ http://example2.com │       13 │
└─────────────────────┴──────────┘

2 rows in set. Elapsed: 0.013 sec.

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

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

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

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

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

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

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

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

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

SELECT SUM(duration) FROM visits;

Вы увидите примерно следующий результат:

Output
SELECT SUM(duration)
FROM visits

┌─SUM(duration)─┐
│          63.7 │
└───────────────┘

1 rows in set. Elapsed: 0.010 sec.

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

SELECT topK(2)(url) FROM visits;

Вы увидите примерно следующий результат:

Output
SELECT topK(2)(url)
FROM visits

┌─topK(2)(url)──────────────────────────────────┐
│ ['http://example2.com','http://example1.com'] │
└───────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.010 sec.

Мы успешно запросили таблицу visits и на следующем шаге выполним удаление таблиц и баз данных.

Шаг 6 — Удаление таблиц и баз данных

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

Синтаксис для удаления таблиц соответствует данному примеру:

DROP TABLE table_name;

Для удаления таблицы visits выполните следующее выражение:

DROP TABLE visits;

Вы увидите следующий вывод, свидетельствующий о том, что вы успешно удалили таблицу:

output
DROP TABLE visits

Ok.

0 rows in set. Elapsed: 0.005 sec.

Вы можете удалить базы данных, используя синтаксис DROP database table_name​​​1​​​. Для удаления базы данных test выполните следующий оператор:

DROP DATABASE test;

Полученный результат показывает, что вы успешно удалили базу данных.

Output
DROP DATABASE test

Ok.

0 rows in set. Elapsed: 0.003 sec.

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

Шаг 7 — Настройка правил брандмауэра (необязательно)

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

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

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

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

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

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

sudo nano /etc/clickhouse-server/config.xml

Затем раскомментируйте строку, содержащую <!-- <listen_host>0.0.0.0</listen_host> -->, как в следующем файле:

...
 <interserver_http_host>example.yandex.ru</interserver_http_host>
    -->

    <!-- Listen specified host. use :: (wildcard IPv6 address), if you want to accept connections both with IPv4 and IPv6 from everywhere. -->
    <!-- <listen_host>::</listen_host> -->
    <!-- Same for hosts with disabled ipv6: -->
    <listen_host>0.0.0.0</listen_host>

    <!-- Default values - try listen localhost on ipv4 and ipv6: -->
    <!--
    <listen_host>::1</listen_host>
    <listen_host>127.0.0.1</listen_host>
    -->
...

Сохраните файл и закройте его. Для применения новой конфигурации перезапустите службу, выполнив:

sudo service clickhouse-server restart

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

sudo ufw allow from second_server_ip/32 to any port 8123  

sudo ufw allow from second_server_ip/32 to any port 9000  

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

Output
Rule added

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

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

Теперь, когда вы вошли на второй сервер, запустите сеанс клиента, выполнив:

clickhouse-client --host your_server_ip --password

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

Output
ClickHouse client version 19.13.3.26 (official build).
Password for user (default):
Connecting to your_server_ip:9000 as user default.
Connected to ClickHouse server version 19.13.3 revision 54425.

hostname :)

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

Заключение

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

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