УСТАНОВКА И РАБОТА С CLICKHOUSE В UBUNTU 18.04

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

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

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

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

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

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

ssh host@your_server_ip

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

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

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

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-get update для обновления индекса пакетов:

sudo apt-get update

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

sudo apt-get install -y clickhouse-server clickhouse-client

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

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

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

Запустите сервис clickhouse-server:

sudo service clickhouse-server start

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

sudo service clickhouse-server status

Вы должны увидеть:

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

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

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

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

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

clickhouse-client

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

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

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

Находясь в командной строке ClickHouse, создайте БД test:

CREATE DATABASE test;

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

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: хранит даты в формате YYYY-MM-DD.
DateTime: хранит даты и время в формате YYYY-MM-DD HH:MM:SS.

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

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

USE test;

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

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, которая состоит из 4 столбцов:

id: столбец для первичного ключа. Как и в других системах РСУБД, столбец первичного ключа в ClickHouse идентифицирует строку; каждая строка должна иметь для этого столбца уникальное значение.
duration: столбец с плавающей запятой, используемый для хранения продолжительности каждого посещения в секундах. В столбцах с плавающей запятой можно хранить десятичные значения, например 12.50.
url: строковый столбец, в котором хранится посещенный URL-адрес, например http://example.com.
created: столбец даты и времени, который отслеживает время посещения.

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

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

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

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

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');

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

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;

Вы увидите такой вывод:

ALTER TABLE visits ADD COLUMN location String Ok. 0 rows in set. Elapsed: 0.014 sec.

Вывод показывает, что вы успешно добавили столбец 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;

Вы получите такой вывод:

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; ALTER TABLE visits DELETE WHERE duration < 5 Ok. 0 rows in set. Elapsed: 0.003 sec.

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

Чтобы удалить столбцы из таблицы, нужно использовать такой синтаксис:

ALTER TABLE table_name DROP COLUMN column_name;

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

ALTER TABLE visits DROP COLUMN location;

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

ALTER TABLE visits DROP COLUMN location String Ok. 0 rows in set. Elapsed: 0.010 sec.

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

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; 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: возвращает массив наиболее часто встречающихся значений определенного столбца с помощью алгоритма аппроксимации.

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

SELECT SUM(duration) FROM visits; SELECT SUM(duration) FROM visits ┌─SUM(duration)─┐ │ 63.7 │ └───────────────┘ 1 rows in set. Elapsed: 0.010 sec.

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

SELECT topK(2)(url) FROM visits; SELECT topK(2)(url) FROM visits ┌─topK(2)(url)──────────────────────────────────┐ │ ['http://example2.com','http://example1.com'] │ └───────────────────────────────────────────────┘ 1 rows in set. Elapsed: 0.010 sec.

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 nano /etc/clickhouse-server/config.xml

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

... <interserver_http_host>example.yandex.ru</interserver_http_host> -->    <listen_host>0.0.0.0</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

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

Rule added

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

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

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

clickhouse-client --host your_server_ip

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

ClickHouse client version 19.3.6. Connecting to your_server_ip:9000 as user default. Connected to ClickHouse server version 19.3.6 revision 54415. hostname :)

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

Заключение

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