Nested(name1 Type1, Name2 Type2, ...)

Обзор вложенных структур данных в ClickHouse

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

Избегайте точек в именах столбцов

Имена столбцов, содержащие точки, столбцы с общим префиксом до точки и столбцы типа Array могут интерпретироваться как часть развёрнутой структуры Nested, если flatten_nested = 1 (значение по умолчанию). Это может привести к неожиданной проверке длины массивов при вставке и ограничениям на переименование.

По возможности не используйте точки в именах столбцов. Вместо точек используйте символ подчёркивания (_) или другой разделитель, если только вам не нужна именно семантика Nested.

Пример:

CREATE TABLE test.visits(
  CounterID UInt32,
  StartDate Date,
  Sign Int8,
  IsNew UInt8,
  VisitID UInt64,
  UserID UInt64,
  Goals Nested(
    ID UInt32,
    Serial UInt32,
    EventTime DateTime,
    Price Int64,
    OrderID String,
    CurrencyID UInt32
  )
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY (StartDate, intHash32(UserID), (CounterID, StartDate, intHash32(UserID), VisitID));

INSERT INTO test.visits
(CounterID, StartDate, Sign, IsNew, VisitID, UserID, Goals.ID, Goals.Serial, Goals.EventTime, Goals.Price, Goals.OrderID, Goals.CurrencyID)
VALUES
    (101500, '2014-03-17', 1, 1, 1001, 100001, [1073752, 591325, 591325], [1, 2, 3], ['2014-03-17 16:38:10', '2014-03-17 16:38:48', '2014-03-17 16:42:27'], [0, 0, 0], ['', '', ''], [0, 0, 0]),
    (101500, '2014-03-17', 1, 0, 1002, 100002, [1073752], [1], ['2014-03-17 00:28:25'], [0], [''], [0]),
    (101500, '2014-03-17', 1, 0, 1003, 100003, [1073752], [1], ['2014-03-17 10:46:20'], [0], [''], [0]),
    (101500, '2014-03-17', 1, 1, 1004, 100004, [1073752, 591325, 591325, 591325], [1, 2, 3, 4], ['2014-03-17 13:59:20', '2014-03-17 22:17:55', '2014-03-17 22:18:07', '2014-03-17 22:18:51'], [0, 0, 0, 0], ['', '', '', ''], [0, 0, 0, 0]),
    (101500, '2014-03-17', 1, 0, 1005, 100005, [], [], [], [], [], []),
    (101500, '2014-03-17', 1, 0, 1006, 100006, [1073752, 591325, 591325], [1, 2, 3], ['2014-03-17 11:37:06', '2014-03-17 14:07:47', '2014-03-17 14:36:21'], [0, 0, 0], ['', '', ''], [0, 0, 0]),
    (101500, '2014-03-17', 1, 0, 1007, 100007, [], [], [], [], [], []),
    (101500, '2014-03-17', 1, 0, 1008, 100008, [], [], [], [], [], []),
    (101500, '2014-03-17', 1, 1, 1009, 100009, [591325, 1073752], [1, 2], ['2014-03-17 00:46:05', '2014-03-17 00:46:05'], [0, 0], ['', ''], [0, 0]),
    (101500, '2014-03-17', 1, 1, 1010, 100010, [1073752, 591325, 591325, 591325], [1, 2, 3, 4], ['2014-03-17 13:28:33', '2014-03-17 13:30:26', '2014-03-17 18:51:21', '2014-03-17 18:51:45'], [0, 0, 0, 0], ['', '', '', ''], [0, 0, 0, 0]);

Приведённый выше DDL-оператор CREATE TABLE объявляет вложенную структуру данных Goals, которая содержит данные о конверсиях, то есть о достигнутых целях. Каждая строка в таблице ‘visits’ может соответствовать нулю, одной или нескольким конверсиям.

Если настройка flatten_nested установлена в 0 (по умолчанию flatten_nested=1), поддерживаются произвольные уровни вложенности.

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

Например:

SELECT
    Goals.ID,
    Goals.EventTime
FROM test.visits
WHERE CounterID = 101500 AND length(Goals.ID) < 5
ORDER BY VisitID
LIMIT 10

    ┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
    ┃ Goals.ID                       ┃ Goals.EventTime                                                                           ┃
    ┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
 1. │ [1073752,591325,591325]        │ ['2014-03-17 16:38:10','2014-03-17 16:38:48','2014-03-17 16:42:27']                       │
    ├────────────────────────────────┼───────────────────────────────────────────────────────────────────────────────────────────┤
 2. │ [1073752]                      │ ['2014-03-17 00:28:25']                                                                   │
    ├────────────────────────────────┼───────────────────────────────────────────────────────────────────────────────────────────┤
 3. │ [1073752]                      │ ['2014-03-17 10:46:20']                                                                   │
    ├────────────────────────────────┼───────────────────────────────────────────────────────────────────────────────────────────┤
 4. │ [1073752,591325,591325,591325] │ ['2014-03-17 13:59:20','2014-03-17 22:17:55','2014-03-17 22:18:07','2014-03-17 22:18:51'] │
    ├────────────────────────────────┼───────────────────────────────────────────────────────────────────────────────────────────┤
 5. │ []                             │ []                                                                                        │
    ├────────────────────────────────┼───────────────────────────────────────────────────────────────────────────────────────────┤
 6. │ [1073752,591325,591325]        │ ['2014-03-17 11:37:06','2014-03-17 14:07:47','2014-03-17 14:36:21']                       │
    ├────────────────────────────────┼───────────────────────────────────────────────────────────────────────────────────────────┤
 7. │ []                             │ []                                                                                        │
    ├────────────────────────────────┼───────────────────────────────────────────────────────────────────────────────────────────┤
 8. │ []                             │ []                                                                                        │
    ├────────────────────────────────┼───────────────────────────────────────────────────────────────────────────────────────────┤
 9. │ [591325,1073752]               │ ['2014-03-17 00:46:05','2014-03-17 00:46:05']                                             │
    ├────────────────────────────────┼───────────────────────────────────────────────────────────────────────────────────────────┤
10. │ [1073752,591325,591325,591325] │ ['2014-03-17 13:28:33','2014-03-17 13:30:26','2014-03-17 18:51:21','2014-03-17 18:51:45'] │
    └────────────────────────────────┴───────────────────────────────────────────────────────────────────────────────────────────┘

Проще всего представить вложенную структуру данных как набор из нескольких массивов столбцов одинаковой длины.

​

Фильтрация столбцов Nested в WHERE

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

Например, этот запрос не просто незаметно возвращает ноль строк — он вызывает исключение, потому что Goals.ID имеет тип Array(UInt32), а equals(Array(UInt32), UInt32) не является допустимым сравнением:

-- WRONG: compares the entire Array to a scalar
SELECT * FROM test.visits
WHERE Goals.ID = 591325;

Code: 43. DB::Exception: Illegal types of arguments (`Array(UInt32)`, `UInt32`)
of function `equals`. (ILLEGAL_TYPE_OF_ARGUMENT)

Используйте has, чтобы проверить, есть ли в массиве определённое значение:

-- Find visits that have at least one goal with ID 591325
SELECT CounterID, VisitID, Goals.ID
FROM test.visits
WHERE has(Goals.ID, 591325);

Используйте arrayExists, если условие сложнее:

-- Find visits that have at least one goal with ID greater than 1000000
SELECT CounterID, VisitID, Goals.ID
FROM test.visits
WHERE arrayExists(id -> id > 1000000, Goals.ID);

Вы можете фильтровать массивы по длине с помощью length или исключать пустые массивы с помощью notEmpty:

-- Visits with at least 3 goals
SELECT CounterID, VisitID, Goals.ID
FROM test.visits
WHERE length(Goals.ID) >= 3;

-- Visits with at least one goal (non-empty array)
SELECT CounterID, VisitID, Goals.ID
FROM test.visits
WHERE notEmpty(Goals.ID);

Чтобы фильтровать по отдельным элементам вложенной структуры, а не по целым строкам, сначала используйте ARRAY JOIN, чтобы развернуть массивы. После ARRAY JOIN каждый элемент становится отдельной строкой, поэтому предложение WHERE применяется к скалярным значениям. Дополнительную информацию см. в разделе предложение ARRAY JOIN. Пример:

SELECT
    Goal.ID,
    Goal.EventTime
FROM test.visits
ARRAY JOIN Goals AS Goal
WHERE CounterID = 101500 AND length(Goals.ID) < 5
ORDER BY VisitID, Goal.Serial
LIMIT 10

    ┏━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┓
    ┃ Goal.ID ┃      Goal.EventTime ┃
    ┡━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━┩
 1. │ 1073752 │ 2014-03-17 16:38:10 │
    ├─────────┼─────────────────────┤
 2. │  591325 │ 2014-03-17 16:38:48 │
    ├─────────┼─────────────────────┤
 3. │  591325 │ 2014-03-17 16:42:27 │
    ├─────────┼─────────────────────┤
 4. │ 1073752 │ 2014-03-17 00:28:25 │
    ├─────────┼─────────────────────┤
 5. │ 1073752 │ 2014-03-17 10:46:20 │
    ├─────────┼─────────────────────┤
 6. │ 1073752 │ 2014-03-17 13:59:20 │
    ├─────────┼─────────────────────┤
 7. │  591325 │ 2014-03-17 22:17:55 │
    ├─────────┼─────────────────────┤
 8. │  591325 │ 2014-03-17 22:18:07 │
    ├─────────┼─────────────────────┤
 9. │  591325 │ 2014-03-17 22:18:51 │
    ├─────────┼─────────────────────┤
10. │ 1073752 │ 2014-03-17 11:37:06 │
    └─────────┴─────────────────────┘

Нельзя выполнить SELECT для всей вложенной структуры данных целиком. Можно только явно перечислить отдельные столбцы, входящие в её состав.

​

Вставка данных

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

Каждый вложенный подстолбец указывается в списке столбцов в точечной нотации (Goals.ID, Goals.Serial, …), а соответствующие значения задаются как массивы:

INSERT INTO test.visits
    (CounterID, StartDate, Sign, IsNew, VisitID, UserID,
     Goals.ID, Goals.Serial, Goals.EventTime, Goals.Price, Goals.OrderID, Goals.CurrencyID)
VALUES
    -- A visit with two goals: each nested sub-column gets an array of length 2
    (101500, '2014-03-18', 1, 1, 2001, 200001,
     [1073752, 591325], [1, 2],
     ['2014-03-18 10:00:00', '2014-03-18 10:05:00'],
     [100, 200], ['order_a', 'order_b'], [1, 2]),
    -- A visit with no goals: all nested sub-columns get empty arrays
    (101500, '2014-03-18', 1, 0, 2002, 200002,
     [], [], [], [], [], []);

Все массивы вложенных подстолбцов в пределах одной строки должны иметь одинаковую длину. Если длины не совпадают, возникает ошибка:

-- ERROR: Goals.ID has 2 elements, but Goals.Serial has 1
INSERT INTO test.visits
    (CounterID, StartDate, Sign, IsNew, VisitID, UserID,
     Goals.ID, Goals.Serial, Goals.EventTime, Goals.Price, Goals.OrderID, Goals.CurrencyID)
VALUES
    (101500, '2014-03-18', 1, 1, 2003, 200003,
     [1073752, 591325], [1],
     ['2014-03-18 12:00:00'], [0], [''], [0]);

Для запроса DESCRIBE столбцы вложенной структуры данных также перечисляются отдельно.

​

Ограничения ALTER

Запросы ALTER для вложенных структур данных имеют следующие ограничения:

Добавление подстолбцов работает как обычно. Вы можете добавить новый подстолбец в существующую структуру Nested:

ALTER TABLE test.visits ADD COLUMN Goals.Revenue Float64;

Подстолбцы можно удалять и по отдельности:

ALTER TABLE test.visits DROP COLUMN Goals.Revenue;

Изменение типа подстолбца поддерживается и запускает мутацию (перезапись данных):

ALTER TABLE test.visits MODIFY COLUMN Goals.Price Int32;

Переименование имеет ограничения. Вы можете переименовать подстолбец в рамках одной и той же вложенной структуры:

-- OK: stays within the Goals structure
ALTER TABLE test.visits RENAME COLUMN Goals.Price TO Goals.Amount;

Однако вы не можете:

• Переименовать всю вложенную структуру целиком (например, Goals в Conversions).
• Переместить подстолбец в другую вложенную структуру (например, Goals.ID в OtherNested.ID).
• Переместить подстолбец из вложенной структуры наружу или, наоборот, внутрь неё (например, Goals.ID в GoalID и наоборот).