1. Aleksey Barabanov Avatar Aleksey Barabanov
  2. Untitled project
  3. configs

Wiki

Clone wiki

configs / db / clickhouse

View History

Clickhouse

Основные операции

Timezone

В основном конфиге необходимо прописать верную таймзону - иначе в ту же графану графики будут приходить со смещением 

<timezone>Europe/Samara</timezone>

Создание БД

CREATE DATABASE test;

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

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

use test;
CREATE TABLE x500.products (
    `id` UInt32,
    `receipt_id` UInt32,
    `bk_id` UInt16,
    `dv_id` UInt16,
    `open_datetime` DateTime,
    `op_type` String,
    `deleted` UInt16,
    `base_1c` UInt16,
    `product_code` UInt32,
    `price` Float32,
    `amount` Float32,
    `full_price` Float32,
    `full_price_w_discount` Float32,
    `tax_group_code` Float32,
    `tax_code` Float32,
    `tax_sum` Float32,
    `product_amount` UInt16,
    `doc_sum` Float32
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(open_datetime)
ORDER BY (bk_id, op_type)
В запросах избегайте SELECT * так как для колоночной БД это весьма тяжело и используйте WHERE по ключу партиционирования + желательно ключу сортировки (ORDER BY/PRIMARY KEY) 
SELECT uniq(receipt_id) 
FROM x500.sales_part 
WHERE 
open_datetime >'2019-12-04 00:00:00' AND 
open_datetime <'2019-12-05 00:00:00' AND 
bk_id=412

Перепартиционирование

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

CREATE TABLE IF NOT EXISTS sales_part AS sales ENGINE = MergeTree() PARTITION BY toYYYYMM(open_datetime) ORDER BY (bk_id,op_type);
INSERT INTO sales_part SELECT * FROM sales;

Посмотреть список партиций по таблице: 

SELECT partition, name, active FROM system.parts  WHERE table = 'sales_part'
Запустить ручной мерж определенной партиции по таблице: 
OPTIMIZE TABLE sales_part PARTITION 201810

Копирование партиций

Бывает необходимость в подготовке данных без удаления их из продовых таблиц. Для этого создаем новую таблицу с точно такой же структурой и PARTITION/ORDER BY, наливаем данные за необходимый период (партицию) и копируем партицию из новой таблицы с предподготовленными данными в продовую: (в примере копируем партицию 202001 из таблицы receipts_part в таблицу receipts, данные будут заменены, в исходной таблице данные не изменятся) 

ALTER TABLE receipts REPLACE PARTITION 202001 FROM receipts_part

Размеры

Размеры таблиц

SELECT table, round(sum(bytes) / 1024/1024/1024, 2) as size_gb
FROM system.parts
WHERE active
GROUP BY table
ORDER BY size_gb DESC

Размеры колонок

SELECT database, table, name, 
round(sum(data_compressed_bytes) / 1024/1024/1024, 2) as size_gb,
round(sum(data_uncompressed_bytes) / 1024/1024/1024, 2) as size_uncompressed_gb
FROM system.columns
GROUP BY database,table,name
HAVING size_gb>0.1
ORDER BY size_gb DESC

MySQL

Подключение БД MySQL

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

CREATE DATABASE IF NOT EXISTS hl_receipts
ENGINE = MySQL('mysql-server:3306', 'databasename', 'user', 'pass')

Внимание, INNER JOIN работает очень плохо, лучше не пробовать.

Запрос доливает в таблицу clickhouse (x500.sqles_part) все данные из мускуля (hl_receipts.hl_receipts) более свежие чем последний ID (даташтамп нужен для ускорения - чтобы использовались не все партиции для запроса) 

INSERT INTO
    x500.sales_part
SELECT
    *
FROM
    hl_receipts.hl_receipts
WHERE
    id > (
        SELECT
            id
        FROM
            x500.sales_part
        WHERE
            open_datetime > '2019-12-04 00:00:00'
        ORDER BY
            id DESC
        LIMIT
            1
    )
Или из консоли (можно сделать кронжобу) 
clickhouse-client -h clickhouse1 -t -q "INSERT INTO x500.sales_part SELECT * FROM hl_receipts.hl_receipts WHERE id > (SELECT id FROM x500.sales_part WHERE open_datetime >'2019-12-04 00:00:00' ORDER BY id DESC LIMIT 1)"

Перелив через csv

Самый неудобный но надежный вариант (структура данных должна соответствовать, конечно)
В мускуле: 

SELECT
    p.id,
    receipt_id,
    r.bk_id,
    r.dv_id,
    open_datetime,
    op_type,
    deleted,
    p.base_1c,
    product_code,
    price,
    amount,
    full_price,
    full_price_w_discount,
    tax_group_code,
    tax_code,
    tax_sum,
    product_amount,
    doc_sum
FROM
    hl_receipts r
    INNER JOIN hl_receipts_products p ON (r.id = p.receipt_id)
WHERE
    deleted = 0 INTO OUTFILE '/var/lib/mysql/file.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
переносим ближе к кликхаус серверу... 
cat file.csv | clickhouse-client --database=x500 --query="INSERT INTO products FORMAT CSV";

Словари

Можно подключить таблицу из MySQL для получения словарей, в дальнейшем можно преобразовывать на лету айдишники в названия, например. Кликхаус кеширует эти данные, не заваливает мускуль запросами.

Словарь это xml файл в корне папки с конфигами сервера с суффиксом _dictionaries.xml (подтягивается на лету). В один файл можно записать несколько словарей.
Например /etc/clickhouse-server/blabla_dictionaries.xml

<yandex>
    <dictionary>
        <name>bks</name>
            <structure>
                <id>
                    <name>bk_id</name>
                </id>
                <attribute>
                    <name>bk_name</name>
                    <type>String</type>
                    <null_value>-</null_value>
                </attribute>
                <attribute>
                    <name>bk_city</name>
                    <type>UInt64</type>
                    <null_value>-</null_value>
                </attribute>
            </structure>
        <layout>
            <hashed />
        </layout>
        <lifetime>
            <min>1</min>
            <max>360</max>
        </lifetime>
        <source>
            <mysql>
                <port>3306</port>
                <user>user</user>
                <password>pass</password>
                <replica>
                    <host>mysql-server</host>
                    <priority>1</priority>
                </replica>
                <db>databasename</db>
                <table>tablename</table>
            </mysql>
        </source>
    </dictionary>
    <dictionary>
        <name>citys</name>
            <structure>
                <id>
                    <name>ct_id</name>
                </id>
                <attribute>
                    <name>ct_name</name>
                    <type>String</type>
                    <null_value>-</null_value>
                </attribute>
            </structure>
        <layout>
            <hashed />
        </layout>
        <lifetime>
            <min>1</min>
            <max>360</max>
        </lifetime>
        <source>
            <mysql>
                <port>3306</port>
                <user>user</user>
                <password>pass</password>
                <replica>
                    <host>mysql-server</host>
                    <priority>1</priority>
                </replica>
                <db>databasename</db>
                <table>tablename</table>
            </mysql>
        </source>
    </dictionary>
</yandex>
Пример запроса используюещго словари: 
SELECT
sum(doc_sum) , dictGetUInt64('bks', 'bk_city', toUInt64(bk_id)) city,dictGetString('citys', 'ct_name', toUInt64(city))
FROM
    x500.sales_part
WHERE 
    open_datetime >'2019-12-05 00:00:00' 
    AND open_datetime <'2019-12-06 00:00:00'
    AND op_type = 'sale'
    AND deleted=0
GROUP BY city

Посмотреть действующие словари (втч ошибки загрузки): 

select * from system.dictionaries\G

Кейсы

Мониторинг nginx

Необходим кликхаус сервер и загружалка логов в него. В загружалку монтируем папку с логами нгинкса и настраиваем.

docker-compose: 

version: '2'

services:
  loader:
    image: mintance/nginx-clickhouse
    restart: always
    volumes:
    - /var/log/nginx/:/logs
    - ./config:/config
  clickhouse:
    image: yandex/clickhouse-server:19.16
    restart: always
    ports:
    - 8123:8123
    - 9000:9000
    - 9009:9009
    volumes:
    - ./clickhouse:/var/lib/clickhouse
    - ./clk:/etc/clickhouse-server

в кликхаусе создаем бд и таблицу для данных:
(обратите внимание на материализированные поля - они расчитываются при вставке, удобны для различных агрегаций в графане) 

CREATE DATABASE IF NOT EXISTS metrics
CREATE TABLE metrics.nginx (
    `RemoteAddr` String,
    `Host` String,
    `Scheme` String,
    `TimeLocal` DateTime,
    `Request` String,
    `RequestMethod` String DEFAULT '-',
    `NrmlzdRequest` String MATERIALIZED replaceRegexpOne(replaceRegexpOne(Request,'\\?.*$',''),'[0-9]*$',''),
    `Routing` String MATERIALIZED NrmlzdRequest,Request,extract(concat(Request,'/'),'^/(.*?)/'),
    `Status` UInt16,
    `BytesSent` UInt32,
    `HttpReferer` String,
    `HttpUserAgent` String,
    `BodyBytesSent` UInt32,
    `RequestLength` UInt32,
    `RequestTime` Float32,
    `UpstreamResponseTime` Float32
) ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(TimeLocal)
ORDER BY (Status, RequestMethod)
настраиваем nginx-clickhouse в файле ./config/config.yml: 
# Settings
settings:
  interval: 10 # in seconds
  log_path: /logs/access.log # path to logfile
  seek_from_end: true # при первом запуске можно выставить в false чтобы загрузить весь лог, потом необходимо поставить true чтобы избежать дублирования записей
# ClickHouse credentials
clickhouse:
 db: metrics
 table: nginx
 host: clickhouse
 port: 8123
 credentials:
  user: default
  password: 
 columns:
   RemoteAddr: remote_addr
   Host: host
   Scheme: scheme
   TimeLocal: time_local
   Request: request
   RequestMethod: request_method
   Status: status
   HttpReferer: http_referer
   HttpUserAgent: http_user_agent
   BodyBytesSent: body_bytes_sent
   RequestLength: request_length
   RequestTime: request_time
   UpstreamResponseTime: upstream_response_time
# NGINX
nginx:
  log_type: main
  log_format: $remote_addr [$time_local] "$host" $scheme "$request_method $request $protocol" $status $body_bytes_sent "$http_referer" "$http_user_agent" $request_length $request_time $upstream_response_time
формат лога в nginx должен быть соответственно: (если формат отличается - значит надо вносить изменения в настройки, создавать таблицу в клике согласно ваших полей) 
    log_format custom '$remote_addr [$time_local] "$host" $scheme "$request" $status $body_bytes_sent "$http_referer" "$http_user_agent" $request_length $request_time $upstream_response_time';
Ну и пример дашборда для графаны
Примеры запросов для графаны:
Перцентиль по обобщенным ссылкам, с исключениемЖ 
SELECT
    $timeSeries as t,
    quantile(0.95)(UpstreamResponseTime) urt95,
    Routing
FROM $table
WHERE $timeFilter AND Routing NOT IN ('reports')
GROUP BY t,Routing
ORDER BY t,urt95 DESC
Общий перцентиль 95 
SELECT
    $timeSeries as t,
    quantile(0.95)(UpstreamResponseTime) urt95
FROM $table
WHERE $timeFilter
GROUP BY t
ORDER BY t
500: 
SELECT
    $timeSeries as t,
    count() cnt,
    NrmlzdRequest
FROM $table
WHERE $timeFilter AND Status>=500
GROUP BY t,NrmlzdRequest
ORDER BY t,cnt DESC

Разное

Преобразование даташтампа в строку дата-час (2019121112) 

SELECT substring(toString(toYYYYMMDDhhmmss(toDateTime('2019-12-11 12:20:12'))),1,10)

Updated