Установка Clickhouse

Произвести установку сервера Clickhouse на выделенном сервере согласно официальной документации в соответствии с выбранной ОС.

Произвести конфигурацию сервера Clickhouse:

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

<clickhouse>
    <logger>
        <level>information</level>
        <log>/var/log/clickhouse-server/clickhouse-server.log</log>
        <errorlog>/var/log/clickhouse-server/clickhouse-server.err.log</errorlog>
        <size>10M</size>
        <count>7</count>
    </logger>
    <display_name>clickhouse</display_name>
    <listen_host>0.0.0.0</listen_host>
    <http_port>8123</http_port>
    <tcp_port>9000</tcp_port>
    <interserver_http_port>9009</interserver_http_port>
</clickhouse>

Произвести настройку пользователей в Clickhouse. Для этого создать конфигурационный файл со следующим содержимым, указав в теге пароль для администратора 'pult':

sudo nano /etc/clickhouse-server/users.d/pult.xml

<clickhouse>
    <users>
        <default>     
            <access_management>1</access_management>
            <named_collection_control>1</named_collection_control>
            <show_named_collections>1</show_named_collections>
            <show_named_collections_secrets>1</show_named_collections_secrets>
        </default>
        <!-- Создание нового пользователя pult в качестве администратора БД -->
        <pult>
            <password>changeme</password>
            <grants>
                <query>GRANT admin</query>
            </grants>
        </pult>
    </users>
    <!-- Создание роли admin -->
    <roles>
    <admin>
        <grants>
            <query>GRANT ALL ON pult.* WITH GRANT OPTION;</query>
        </grants>
    </admin>
    </roles>
    <!-- Настройка стандартного профиля default для корректного форматирования данных в JSON (необходимо для интеграции с Grafana) -->
    <profiles>
        <default>
            <input_format_json_try_infer_numbers_from_strings>1</input_format_json_try_infer_numbers_from_strings>
            <output_format_json_quote_64bit_floats>1</output_format_json_quote_64bit_floats>
        </default>
    </profiles>
</clickhouse>

Перезапустить службу Clickhouse

sudo systemctl restart clickhouse-server

Создать БД pult в Clickhouse через консоль с помощью клиента clickhouse-client, указав пользователя и заданный пароль для подключения к Clickhouse:

clickhouse-client --user pult --password changeme --multiquery "CREATE DATABASE IF NOT EXISTS pult; \
CREATE TABLE IF NOT EXISTS pult.history (itemid UInt64, clock UInt64 CODEC(Delta, LZ4), ns UInt64 CODEC(T64, LZ4), value Float64) ENGINE = MergeTree() PARTITION BY toYYYYMM(CAST(clock as date)) ORDER BY (itemid, clock) TTL (CAST(clock as date)) + INTERVAL 6 MONTH; \
CREATE TABLE IF NOT EXISTS pult.history_str (itemid UInt64, clock UInt64 CODEC(Delta, LZ4), ns UInt64 CODEC(T64, LZ4), value String) ENGINE = MergeTree() PARTITION BY toYYYYMM(CAST(clock as date)) ORDER BY (itemid, clock) TTL (CAST(clock as date)) + INTERVAL 6 MONTH; \
CREATE TABLE IF NOT EXISTS pult.history_uint (itemid UInt64, clock UInt64 CODEC(Delta, LZ4), ns UInt64 CODEC(T64, LZ4), value UInt64) ENGINE = MergeTree() PARTITION BY toYYYYMM(CAST(clock as date)) ORDER BY (itemid, clock) TTL (CAST(clock as date)) + INTERVAL 6 MONTH; \
CREATE TABLE IF NOT EXISTS pult.history_text (itemid UInt64, clock UInt64 CODEC(Delta, LZ4), ns UInt64 CODEC(T64, LZ4), value String) ENGINE = MergeTree() PARTITION BY toYYYYMM(CAST(clock as date)) ORDER BY (itemid, clock) TTL (CAST(clock as date)) + INTERVAL 6 MONTH; \
CREATE TABLE IF NOT EXISTS pult.history_log (itemid UInt64, clock UInt64 CODEC(Delta, LZ4), ns UInt64 CODEC(T64, LZ4), timestamp UInt64 CODEC(Delta, LZ4), logeventid UInt64, source String, severity UInt8, value String) ENGINE = MergeTree() PARTITION BY toYYYYMM(CAST(clock as date)) ORDER BY (itemid, clock) TTL (CAST(clock as date)) + INTERVAL 6 MONTH; \
CREATE MATERIALIZED VIEW IF NOT EXISTS pult.trends (itemid UInt64, clock DateTime('Europe/Moscow'), num AggregateFunction(count), value_min AggregateFunction(min, Float64), value_avg AggregateFunction(avg, Float64), value_max AggregateFunction(max, Float64)) ENGINE = AggregatingMergeTree PARTITION BY toYYYYMM(clock) ORDER BY (itemid, toStartOfHour(toDateTime(clock), 'Europe/Moscow')) SETTINGS index_granularity = 8192 AS SELECT itemid, toStartOfHour(toDateTime(clock), 'Europe/Moscow') AS clock, countState(*) AS num, minState(value) AS value_min, avgState(value) AS value_avg, maxState(value) AS value_max FROM pult.history GROUP BY itemid, clock; \
CREATE MATERIALIZED VIEW IF NOT EXISTS pult.trends_uint (itemid UInt64, clock DateTime('Europe/Moscow'), num AggregateFunction(count), value_min AggregateFunction(min, UInt64), value_avg AggregateFunction(avg, UInt64), value_max AggregateFunction(max, UInt64)) ENGINE = AggregatingMergeTree PARTITION BY toYYYYMM(clock) ORDER BY (itemid, toStartOfInterval(toDateTime(clock, 'Europe/Moscow'), toIntervalHour(1))) TTL toStartOfInterval(toDateTime(clock, 'Europe/Moscow'), toIntervalHour(1)) + toIntervalMonth(12) SETTINGS index_granularity = 8192 AS SELECT itemid, toStartOfInterval(toDateTime(clock, 'Europe/Moscow'), toIntervalHour(1)) AS clock, countState(*) AS num, minState(value) AS value_min, avgState(value) AS value_avg, maxState(value) AS value_max FROM pult.history_uint GROUP BY itemid, clock; \ 
CREATE TABLE IF NOT EXISTS pult.history_bin (itemid UInt64, clock UInt64 CODEC(Delta, LZ4), ns UInt64 CODEC(T64, LZ4), value String) ENGINE = MergeTree() PARTITION BY toYYYYMM(CAST(clock as date)) ORDER BY (itemid, clock) TTL (CAST(clock as date)) + INTERVAL 6 MONTH;"