Auto Increment

[GoTLiuM InSPiRiT]

Добрый день! 

Возник вопрос: есть ли возможность использования AutoIncrement. В документации на этот счет ничего нет.

[man...@gmail.com]

Добрый день.

Autoincrement нет.
Реализация autoincrement возможна, но необходимости в этом не было.

[Ruslan]

А каким образом Вы работаете с деревьями в Яндекс.Метрике? Обычно используются NS/CT/AL/MP. 

Если нет Autoincrement-а, то можно хранить как MP и то в String Path. 

Например у Яндекс.Метрики есть UTM деревья. Ведь не хранятся они в Array как Вложенные структуры.

Если и хранятся, то возникает вопрос: Оптимально ли использование вложенных структур и в каком виде оно хранится на диске (колонки в отдельном файле?). На сколько будет оптимальным использование ARRAY JOIN с GROUP BY в этом случае? 

Очень не хватает EXPLAIN-а и точное понимание того, как все работает. Допустим при использовании MP String Path - возможно лучше кешировать используя sipHash64, и группировка по UInt64 будет менее затратной по памяти. Да и не будет ли коллизии в случае использования алгоритма sipHash64.

[man...@gmail.com]

UTM метки (а также другие метки) храним в виде отдельных столбцов:

    UTMSource String,
    UTMMedium String,
    UTMCampaign String,
    UTMContent String,
    UTMTerm String

В отчёте пользователь может сам выбирать, в каком порядке из них строить дерево.
Для построения отчёта, делаем GROUP BY по всем нужным меткам.
В результате запроса, по сути, получается не дерево, а набор вершин, который ещё нужно упорядочить в дерево. Это делается на стороне приложения.

Другие деревья, например, регионы, поисковые системы, товарные категории и т. п. храним так:
Один столбец RegionID (SearchEngineID...), в котором расположено "листовое" значение.
Дерево потом получаем с помощью функций: regionHierarchy или dictGetHierarchy и arrayJoin.
Либо получаем отдельные элементы с помощью regionIn, regionTo..., dictIsIn и т. п.

Например, для выдачи всего дерева, используем в качестве ключа arrayJoin(regionHierarchy(RegionID)).
А для выдачи дерева из страны, области, города, используем в качестве ключей regionToCountry(RegionID), regionToArea(RegionID), regionToCity(RegionID).
Насколько я помню, в Метрике сейчас используется только второй вариант.

Храня листовое значение и применяя функцию ...Hierarchy, вы получаете решение похожее на Materialized Path, но без хранения в таблице этого Path. Получение Path налету по словарю весьма эффективно и отменяет необходимость хранить весь Path в таблице.

Есть случаи, когда одной строчке таблицы соответствует сразу много вершин дерева.
Например, это "параметры визитов". Параметры визитов - произвольный JSON, который передаётся вместе с событием.
Мы раскладываем их во вложенную структуру данных из 10 столбцов:

ParsedParams Nested
(
   Value1 String,
   Value2 String,
   ...
   Value10 String
)

Где 10 - максимальная глубина дерева.
То есть, мы рассматриваем JSON как набор всех его путей глубины не более 10.

Оптимально ли использование вложенных структур и в каком виде оно хранится на диске (колонки в отдельном файле?).

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

ParsedParams.Value1 Array(String),
ParsedParams.Value2 Array(String),
...
ParsedParams.Value10 Array(String)

Соответственно, отдельные её элементы хранятся отдельно.

На сколько будет оптимальным использование ARRAY JOIN с GROUP BY в этом случае?

 ARRAY JOIN работает хорошо, пока массивы маленькие. Например, десятки элементов - нормально, сотни - плохо, тысячи - лучше введите ограничение и режте при записи.

Очень не хватает EXPLAIN-а и точное понимание того, как все работает.

 Да, подробного EXPLAIN-а нет. При выполненнии запроса, сервер выводит в лог конвейер выполнения, но без особых деталей.

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

Группировка по sipHash64 менее затратна по памяти, если только вы не достаёте сами строки.
Пример:

SELECT UTMSourceHash, count() FROM table GROUP BY UTMSourceHash
- менее затратно, чем
SELECT UTMSource, count() FROM table GROUP BY UTMSource
Но
SELECT UTMSourceHash, count(), any(UTMSource) FROM table GROUP BY UTMSourceHash
более затратно и менее эффективно.

Использование sipHash64 более эффективно по времени, только если у вас значение sipHash64 уже лежит в одном из столбцов таблицы.
А если оно вычисляется налету, то это (как правило) менее эффективно, чем GROUP BY по строке.

Пример:

SELECT UTMSourceHash, count() FROM table GROUP BY UTMSourceHash
- более эффективно, чем:
SELECT UTMSource, count() FROM table GROUP BY UTMSource
- но в свою очередь, последнее как правило более эффективно, чем:
SELECT sipHash64(UTMSource) AS UTMSourceHash, count() FROM table GROUP BY UTMSourceHash

Да и не будет ли коллизии в случае использования алгоритма sipHash64.

На достаточно большом множестве данных коллизии будут - надо иметь это ввиду.

Если у вас есть несколько миллиардов строк, то в этом множестве есть какая-нибудь коллизия с вероятностью примерно 1/2.
Если брать конкретную строку (например, URL главной страницы сайта), то вероятность того, что она имеет коллизию с какой-то другой строкой множества, уже намного меньше - около 1 на миллиард.

[Ruslan]

Спасибо за достаточно развернутый ответ. Хотелось бы немного дополнить вопросы примерами, 

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

задать еще парочку вопросов по индексам, оптимизаторе и тд.

1. Структуры данных. Пример:

-- таблица
CREATE TABLE MetricsStruct
(
  EventDate Date,
  CounterId UInt64,
  Users UInt32,
  Views UInt32,
  Utm Nested (
    Level UInt64, -- допустимо от 0-4
    Name String
  )
) ENGINE = MergeTree(EventDate, (CounterId, EventDate), 8192);


-- данные
INSERT INTO MetricsStruct VALUES (toDate(now()), 12345, 1, 1, [0, 1, 2, 3, 4], ['direct', 'cpc', 'phone', 'discount', 'buy_phone']);
INSERT INTO MetricsStruct VALUES (yesterday(), 12345, 1, 1, [0, 1, 2], ['direct', 'cpc', 'phone']);
INSERT INTO MetricsStruct VALUES (yesterday(), 54321, 1, 1, [0, 1, 2], ['direct', 'cpc', 'phone']);


-- получаем суммарные значения utm_source для CounterId по диапазону дат
SELECT
        Utm.Name,
        sum(Users),
        sum(Views)
    FROM MetricsStruct
    ARRAY JOIN Utm AS Utm
    WHERE CounterId = 12345 AND EventDate BETWEEN yesterday() AND toDate(now()) AND Utm.Level = 0
    GROUP BY Utm.Name;

Вопросы: 

1.1. Как отработает WHERE? EventDate находится в индексе, тогда как Utm.Level - нет. И мало того, оно находится во вложенной структуре.

Может лучше преобразовать запрос так и это будет более оптимально:

 PREWHERE CounterId = 12345 AND EventDate BETWEEN yesterday() AND toDate(now())
     WHERE Utm.Level = 0

1.1.1 В результате оптимизатор сначала отфильтрует данные с индекса, и после произведет фильтрацию по вложенной структуре,

что положительно скажется на производительности, тогда как в обычном случае не используя PREWHERE,

придется пройтись по всем данным проверяя и дату и значение структуры. Или все таки оптимизатор понимает, что сначала нужно выполнить фильтрацию по индексным полям и сам решит?

1.2. Возвращаясь к Вашему ответу: Если в таблице MetricsStruct будет более 3 млрд записей, а во вложенной структуре Utm каждой записи будет 5 значений (utm_source/utm_medium/utm_campaign/utm_content/utm_term) для 2-х колонок (Name/Level) - то это не оптимально, или же Вы имели ввиду кол-во колонок в самой структуре?

2. Возможно стоит переделать таблицу и это будет более оптимально в результате:

-- таблица
CREATE TABLE MetricsPath
(
  EventDate Date,
  CounterId UInt64,
  Users UInt32,
  Views UInt32,
  Path UInt64, -- sipHash64(/utm_source/utm_medium/utm_campaign/utm_content/utm_term)
  Name String
) ENGINE = MergeTree(EventDate, (CounterId, EventDate, Path), 8192);


-- данные
INSERT INTO MetricsPath VALUES (toDate(now()), 12345, 1, 1, sipHash64('/'), 'direct');
INSERT INTO MetricsPath VALUES (toDate(now()), 12345, 1, 1, sipHash64('/direct'), 'cpc');
INSERT INTO MetricsPath VALUES (toDate(now()), 12345, 1, 1, sipHash64('/direct/cpc'), 'phone');
INSERT INTO MetricsPath VALUES (toDate(now()), 12345, 1, 1, sipHash64('/direct/cpc/phone'), 'discount');
INSERT INTO MetricsPath VALUES (toDate(now()), 12345, 1, 1, sipHash64('/direct/cpc/phone/discount'), 'buy_phone');
INSERT INTO MetricsPath VALUES (toDate(now()), 12345, 1, 1, sipHash64('/'), 'direct');
INSERT INTO MetricsPath VALUES (toDate(now()), 12345, 1, 1, sipHash64('/direct'), 'cpc');
INSERT INTO MetricsPath VALUES (toDate(now()), 12345, 1, 1, sipHash64('/direct/cpc'), 'phone');


-- запрос
SELECT
        Path,
        sum(Users),
        sum(Views)
    FROM MetricsPath
    WHERE CounterId = 12345 AND EventDate BETWEEN yesterday() AND toDate(now()) AND Path = sipHash64('/')
    GROUP BY Path;

2.1 при таком подходе кол-во строк по сравнению с таблицей с вложенной структурой возрастет в 4-5 раз, а значит и потребление дискового пространства.

2.2 мы не используем JOIN, а значит меньше данных выгружаем в ОЗУ, меньшее кол-во открытых файлов дескрипторов, более эффективное планирование запроса по сравнению с вложенными структурами и более быстрый результат?

2.3 Действительно ли UInt64 Path будет в индексе? И он столь же эффективен как CounterId/EventDate при использовании в секции WHERE?

2.4 Path в этом случае UInt64, а значит он будет занимать меньше места и группировка по нему будет более эффективной, чем в случае String?

3. По коллизиям sipHash64. 

3.1 Если у нас есть CounterId и в среднем данных для каждого CounterId - 300к, а не миллиарды, то возникновение коллизии будет крайне мала? (Нужно конечно же проверять этот момент)

3.2 Почему именно sipHash64? Можно выбрать к примеру MD5/SHA-1, но если их хранить как String - то памяти они при группировке, хранении и выборке будут жрать куда больше чем UInt64, и менее эффективными при выборках, когда посчитанное заранее значение лежит в таблице.

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

Если бы я точно знал как обрабатываются запросы в 1-м и во 2-м случаях, возможно я бы даже объединил их, пожертвовав к примеру файловыми дескрипторами и занимаемом местом на HDD. Потому и уточняю, как работает БД и как правильно работать с ней. Был бы очень признательным если бы Вы прояснили все моменты. И даже подсказали более правильный путь на текущих примерах.

5. Является ли хранение дерева UTM в Яндекс.Метрике в качестве String и группировка по ним столь оптимальными, исходя из вопроса по Int/String? 

SELECT UTMSourceHash, count() FROM table GROUP BY UTMSourceHash

5.1 Например хранить строковое значение отдельно, группировать по UInt64, и после уже выбрать значение словаря по sipHash64 с отдельной таблицы/БД/CSV, как в случае с регионами или как-либо еще. 

5.2 Тут лишь вопрос: функция получения данных со словаря выполняется после получения результата, или в момент получения каждого значения и заменяет int на str и группировка происходит уже по str, а не по int. 

6. Планируется ли добавить EXPLAIN?

[man...@gmail.com]

1. Сначала стоит понять, мы храним неагрегированные или агрегированные данные.
Судя по столбцам Users, Views, у вас агрегированные (или частично-агрегированные) данные.

Удобнее всего ClickHouse работает с неагрегированными данными - логами событий (в вашем случае - просмотров страниц).
Тогда у вас в таблице будут столбцы UserID, EventTime, а не Users, Views.

Как хранить метки. Дерево меток ведь имеет очень ограниченную глубину и заранее известно, какие могут быть метки.
Зачем тогда вложенная структура? Достаточно иметь несколько столбцов: UTMSource, UTMMedium и т. п.
Типы - String, если на каждую строчку таблицы мы запоминаем одну метку или Array(String), если несколько.

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

Ещё мелочь: в запросе INSERT у вас написаны выражения типа toDate(now()). Для примера это Ок, но не используйте это при вставке большого объёма данных. Поддержка выражений в INSERT VALUES добавлена в качестве исключения и работает менее эффективно.

1.1. Оптимизатор запросов всегда смотрит на индекс и всегда фильтрует по индексу в самом начале. Поэтому, класть выражения, использующие индекс в PREWHERE не имеет смысла. Кстати, PREWHERE можно вообще не использовать, так как в системе есть эвристика по автоматическому переносу условий в PREWHERE (наверное, её наличие не документировано). Она работает по-умолчанию всегда, когда в запросе не указано PREWHERE явно.

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

1.2. Возвращаясь к Вашему ответу: Если в таблице MetricsStruct будет более 3 млрд записей, а во вложенной структуре Utm каждой записи будет 5 значений (utm_source/utm_medium/utm_campaign/utm_content/utm_term) для 2-х колонок (Name/Level) - то это не оптимально, или же Вы имели ввиду кол-во колонок в самой структуре?

Это будет нормально. Неоптимально будет, если много записей будет в одном значении - в одном массиве в одной строке. Например, 10 млрд. строк, в каждой массив по 5 значений - Ок. 10 млрд. строк, в каждой из которых массив в среднем по 100 значений а иногда по 100 000 значений - плохо.


2. Не стоит материализовывать дерево при записи в таблицу.
Во первых, вы ограничиваете себя одним отчётом. Вы записываете таблицу только для того, чтобы реализовать отчёт по меткам.
Гораздо лучше записывать в таблицу лог, содержащий всевозможные свойства, по которым вы сможете строить отчёты - не только метки.

2.3 Действительно ли UInt64 Path будет в индексе? И он столь же эффективен как CounterId/EventDate при использовании в секции WHERE?

Если вы указали его в индексе, он там будет. Когда вы указали его после CounterId, EventDate - значит индекс будет работать эффективнее, когда выбираются данные для одного или нескольких CounterId и для небольшого количества EventDate. Подробнее, как устроен индекс, отвечено здесь:
https://groups.google.com/forum/#!topic/clickhouse/eUDrOLxV-lE

2.4 Path в этом случае UInt64, а значит он будет занимать меньше места и группировка по нему будет более эффективной, чем в случае String?

Как правило, это так. Но иногда разница будет незаметна.

3. По коллизиям sipHash64.
3.1 Если у нас есть CounterId и в среднем данных для каждого CounterId - 300к, а не миллиарды, то возникновение коллизии будет крайне мала? (Нужно конечно же проверять этот момент)

Да.

3.2 Почему именно sipHash64? Можно выбрать к примеру MD5/SHA-1, но если их хранить как String - то памяти они при группировке, хранении и выборке будут жрать куда больше чем UInt64, и менее эффективными при выборках, когда посчитанное заранее значение лежит в таблице.

SipHash в несколько эффективнее MD5 и SHA-1. Качество SipHash лучше MD5.
Кроме sipHash64 есть также функция sipHash128, которая может использоваться вместо MD5, когда нужно 128 бит.
Она возвращает FixedString(16). Да, конечно это менее эффективно (в два раза), чем хранить только 64 бита, и ещё и несколько менее удобно.
Если хранить более-менее упорядочены по этому значению, то в результате сжатия, разница в сжатых данных будет менее чем в два раза.

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

Зависит от того, что такое очень быстрые выборки.
Для 300 000 строк на CounterId, всё будет работать где-то за 5-20 мс., если удастся обойтись без каких-нибудь граблей.

Был бы очень признательным если бы Вы прояснили все моменты. И даже подсказали более правильный путь на текущих примерах.

С удовольствием проясню все моменты, по мере того, как удастся выделять на это время.

5. Является ли хранение дерева UTM в Яндекс.Метрике в качестве String и группировка по ним столь оптимальными, исходя из вопроса по Int/String?

5.1 Например хранить строковое значение отдельно, группировать по UInt64, и после уже выбрать значение словаря по sipHash64 с отдельной таблицы/БД/CSV, как в случае с регионами или как-либо еще.

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

В ClickHouse GROUP BY хорошо оптимизированы для разных случаев ключей агрегации - и для чисел и для строк и для чисел со строками и т. п.
Конечно, GROUP BY по строковому полю работает менее оптимально хотя бы по фундаментальным соображениям. Но он всё-равно пытается работать настолько оптимально, насколько это возможно :)
Попробуйте загрузить существенное количество данных в таблицу и проверьте сами. Также можете использовать тестовые данные, как здесь:  https://clickhouse.yandex/tutorial.html
Там, в основном, в запросах, делается как раз GROUP BY по строкам.

5.2 Тут лишь вопрос: функция получения данных со словаря выполняется после получения результата, или в момент получения каждого значения и заменяет int на str и группировка происходит уже по str, а не по int.

Это определяется тем, на каком этапе выполнения запроса, понадобилась эта функция.
Если значение понадобилось до GROUP BY, то и преобразование будет делаться до GROUP BY.
Также есть исключение - атрибут словаря можно обозначить как "инъективным" - смотрите здесь:
https://clickhouse.yandex/reference_ru.html#%D0%92%D0%BD%D0%B5%D1%88%D0%BD%D0%B8%D0%B5%20%D1%81%D0%BB%D0%BE%D0%B2%D0%B0%D1%80%D0%B8

6. Планируется ли добавить EXPLAIN?

Сейчас этого нет в планах.

[Ruslan]

спасибо за столь развернутый ответ. 

среда, 29 июня 2016 г., 23:21:21 UTC+5 пользователь man...@gmail.com написал: