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

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

Не используйте точки в именах столбцов

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

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

Пример:

CREATE TABLE test.visits(
  CounterID UInt32,
  StartDate Date,
  Sign Int8,
  IsNew UInt8,
  VisitID UInt64,
  UserID UInt64,
--highlight-start
  Goals Nested(
    ID UInt32,
    Serial UInt32,
    EventTime DateTime,
    Price Int64,
    OrderID String,
    CurrencyID UInt32
  )
--highlight-end
)
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'] │
    └────────────────────────────────┴───────────────────────────────────────────────────────────────────────────────────────────┘

Совет

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

Фильтрация вложенных столбцов в 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).