Техники по апдейту данных

[Дмитрий Карташов]

Здравствуйте.

Я пишу в базу информацию по кликам, также я в последствии записываю ряд postclick событий. Я знаю, что отсутствует реализация UPDATE/DELETE, но мне хотелось бы изменять (удалять и создавать заново) информацию по кликам. Существуют ли какие-то методы (техники), чтобы изменять (удалять) данные для построения аналитических отчетов без JOIN`ов.

Спасибо.

[Дмитрий Карташов]

И еще вопрос по вложенным структурам данных. 

Я как то могу вставить данные во вложенную структуру уже имеющейся записи? Т.е. я создал запись:

CREATE TABLE nested_test
(
    created_at Date,
 id Int64,
    nest Nested(
 x Int64,
 )
)

INSERT INTO nested_test(created_at,id) VALUES ('2014-01-01',1);

Через какое-то время мне нужно добавить данные в nest.x, где nest.x соответствует id=1. Для примера:

INSERT INTO nested_test(nest.x) VALUES ([1,2,3]) WHERE id=1;

[Александр Гордиенко]

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

То есть, имея запись:

   id     date         fld1 fld2 fld3    click_id

   1   2014-01-01   0   0   0            1

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

   2   2014-01-01   1   2   0            1

   3   2014-01-01   0   0   7            1

При выборке данных по полю даты с функцией агрегации по полям 1, 2 и 3 и ограничением множества по click_id=1 мы получим результирующую выборку вида:

   1   2   7

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

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

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

   4   2014-01-01   0   0  -7            1

Тогда в выборке увидим следующее:

   1   2   0

Прошу прощения, если получилось скомкано.

[ds]

Скорее всего так и есть.

Правда добавлять во вложенные структуры было в разы удобнее. Может еще не докуметировали эту возможность.

Было бы интересно услышать мнения разработчиков базы.

[man...@gmail.com]

Как сказал Александр (спасибо!), один из способов состоит в том, чтобы превратить обновления данных в добавление новых строчек лога.
То есть, вы записываете в таблицу неизменяемый лог добавлений/обновлений данных, и стараетесь организовать его таким образом, чтобы результирующие отчёты было получить несложно.

В ClickHouse есть (весьма специфическая) поддержка "схлопывания" записей во время фоновых слияний, для таких логов обновлений.
Например, https://clickhouse.yandex/reference_ru.html#CollapsingMergeTree
Есть ещё ReplacingMergeTree (не документирован), который позволяет при слиянии заменять строки с одинаковым ключом на последнюю версию. Могу рассказать подробнее.

Но я рекомендую сначала попытаться ограничиться неизменяемым логом.
Это нормально. Когда произошёл клик, вы сначала записываете в лог строчку. Когда после клика произошла конверсия, вы записываете ещё одну строчку.

[man...@gmail.com]

Возможности добавления во вложенные структуры нет.

[Дмитрий Карташов]

Спасибо за ответ.

Хотелось бы подробнее узнать про ReplacingMergeTree.

[ds]

Могли бы выложить примеры с CollapsingMergeTree и SummingMergeTree? Не происходит удаление удаление записей или суммирование соответственно. Заранее спасибо.

[Denis Kuznetsov]

Если верить документации, склейка кусков (суммирование или замена) выполняются не сразу, а спустя какое-то время. Но есть несколько способов исправить ситуацию:

1. Добавить в запрос final
2. Сделать optimize table

:) create table counterSum(dt Date, idCounter UInt32, value UInt32) engine = SummingMergeTree(dt, (dt, idCounter), 8192);
:) insert into counterSum values ('2016-01-01', 1, 10), ('2016-01-01', 1, 20), ('2016-01-01', 1, 30);
:) select * from counterSum;

:) select * from counterSum;

SELECT *
FROM counterSum

┌─────────dt─┬─idCounter─┬─value─┐
│ 2016-01-01 │         1 │    10 │
│ 2016-01-01 │         1 │    20 │
│ 2016-01-01 │         1 │    30 │
└────────────┴───────────┴───────┘

3 rows in set. Elapsed: 0.002 sec.

:) select * from counterSum final;

SELECT *
FROM counterSum
FINAL

┌─────────dt─┬─idCounter─┬─value─┐
│ 2016-01-01 │         1 │    60 │
└────────────┴───────────┴───────┘

1 rows in set. Elapsed: 0.002 sec.

:) select idCounter, sum(value) from counterSum group by idCounter;

SELECT
    idCounter,
    sum(value)
FROM counterSum
GROUP BY idCounter

┌─idCounter─┬─sum(value)─┐
│         1 │         60 │
└───────────┴────────────┘

1 rows in set. Elapsed: 0.003 sec.

:) optimize table counterSum;

OPTIMIZE TABLE counterSum

Ok.

0 rows in set. Elapsed: 0.002 sec.

:) select * from counterSum;

SELECT *
FROM counterSum

┌─────────dt─┬─idCounter─┬─value─┐
│ 2016-01-01 │         1 │    60 │
└────────────┴───────────┴───────┘

1 rows in set. Elapsed: 0.002 sec.

[man...@gmail.com]

ReplacingMergeTree:

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

В качестве последнего, необязательного параметра, движок таблицы принимает имя столбца с версией.
Этот столбец должен иметь тип из семейства UInt или Date/DateTime.
Если такой столбец задан, то при мердже, для каждого первичного ключа, выбирается одна строка с максимальным значением версии, а если строк с максимальным значением версии больше одной - то последняя из них по порядку вставки.

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

Пример использования: https://github.com/yandex/ClickHouse/blob/master/dbms/tests/queries/0_stateless/00325_replacing_merge_tree.sql

[Дмитрий Карташов]

Спасибо большое за помощь.

[Ivan Ladelschikov]

Для добавления во вложенные структуры подошел бы (пока что, воображаемый) движок ConcatenatingMergeTree, который будет указанные array(nested) значения склеивать в одно для одинаковых значений первичного ключа:

CREATE TABLE test.concatenating (d Date, k UInt64, p Nested(y Int32, z Int32), arr Array(String)) ENGINE = ConcatenatingMergeTree(d, k, 8192, (p, arr));
INSERT INTO test.concatenating VALUES ('2000-01-01', 1, [1,2],[10,20],['uno']), ('2000-01-01', 1, [100],[200],['dos', 'tres']), ('2000-01-01', 1, [],[],['quarto']);
SELECT * FROM test.concatenating FINAL;

'2000-01-01', 1, [1,2,100], [10,20,200], ['uno','dos', 'tres', 'quarto']


Есть ли какая-нибудь информация у разработчиков по реализации подобного поведения?

[man...@gmail.com]

Есть ещё AggregatingMergeTree, который умеет выполнять агрегатные функции при слияниях, и есть агрегатная функция groupArray.
Но я не рекомендую, по нескольким причинам:

1. Длинные массивы будут плохо работать. Всё написано из предположения, что одно значение, одна строчка - это что-то маленькое. Например, поэтому, при выполнении запроса, данные обрабатываются пачками. Если в строках расположены длинные массивы, то пачки станут большими, будут съедать много оперативки и не будут помещаться даже в L3-кэш.

2. Работа с AggregatingMergeTree идейно намного сложнее, чем с MergeTree. Будет неудобно.

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

https://clickhouse.yandex/reference_ru.html#%D0%9A%D0%BE%D0%BC%D0%B1%D0%B8%D0%BD%D0%B0%D1%82%D0%BE%D1%80%20-Array.%20%D0%90%D0%B3%D1%80%D0%B5%D0%B3%D0%B0%D1%82%D0%BD%D1%8B%D0%B5%20%D1%84%D1%83%D0%BD%D0%BA%D1%86%D0%B8%D0%B8%20%D0%B4%D0%BB%D1%8F%20%D0%B0%D1%80%D0%B3%D1%83%D0%BC%D0%B5%D0%BD%D1%82%D0%BE%D0%B2-%D0%BC%D0%B0%D1%81%D1%81%D0%B8%D0%B2%D0%BE%D0%B2

Или размножить данные по массивам с помощью arrayJoin.