중첩 데이터 구조
중첩 데이터 구조 (Nested Data Structures)
셀 안에 들어 있는 테이블처럼 동작하는 데이터 구조예요. 컬럼 이름과 타입 같은 매개변수는 CREATE TABLE 쿼리와 같은 방식으로 지정해요. 각 테이블 행은 중첩 데이터 구조의 임의의 행 수에 대응할 수 있어요.
출처: 문서
본문
중첩 데이터 구조는 셀 안에 있는 테이블과 같아요. 중첩 데이터 구조의 매개변수(컬럼 이름과 타입)는 CREATE TABLE 쿼리와 같은 방식으로 지정돼요. 각 테이블 행은 중첩 데이터 구조의 임의의 행 수에 대응할 수 있어요.
컬럼 이름에 점(dot) 사용 피하기 (Avoid using dots in column names)
점을 포함한 컬럼 이름, 공통 점 접두사를 공유하는 컬럼, Array 타입 컬럼은 flatten_nested = 1(기본값)일 때 각각 평면화된 Nested 구조의 일부로 해석될 수 있어요. 이는 insert 시 예상 밖의 배열 길이 검증과 이름 변경 제한을 일으킬 수 있어요. 가능하면 컬럼 이름에 점을 사용하지 마세요. 의도적으로 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]);
위 CREATE TABLE DDL 문은 변환(conversions) 또는 도달한 목표(goals)에 대한 데이터를 담는 Goals 중첩 데이터 구조를 선언해요. 'visits' 테이블의 각 행은 0개 이상의 변환에 대응해요. 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 컬럼 필터링 (Filtering Nested columns in WHERE)
Nested 구조의 각 컬럼은 Array로 저장되므로, WHERE 절에서 이를 참조하면 각 행에 대해 개별 요소가 아니라 전체 배열을 얻게 돼요. 중첩 컬럼을 스칼라 값과 직접 비교할 수 없으므로 배열 함수(array functions)를 대신 사용해야 해요. 예를 들어 다음 쿼리는 조용히 행을 반환하지 않는 게 아니라 예외를 일으켜요. 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를 수행할 수 없어요. 그 일부인 개별 컬럼만 명시적으로 나열할 수 있어요.
데이터 삽입 (Inserting data)
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 limitations)
중첩 데이터 구조에 대한 ALTER 쿼리에는 다음 제한이 있어요.
서브컬럼 추가(Adding sub-columns) 는 정상적으로 동작해요. 기존 Nested 구조에 새 서브컬럼을 추가할 수 있어요.
ALTER TABLE test.visits ADD COLUMN Goals.Revenue Float64;
서브컬럼 제거(Dropping sub-columns) 는 개별 서브컬럼에 대해 동작해요.
ALTER TABLE test.visits DROP COLUMN Goals.Revenue;
서브컬럼 타입 수정(Modifying the type) 은 동작하며 뮤테이션(데이터 다시 쓰기)을 일으켜요.
ALTER TABLE test.visits MODIFY COLUMN Goals.Price Int32;
이름 변경(Renaming) 에는 제한이 있어요. 같은 중첩 구조 안에서 서브컬럼 이름을 바꿀 수 있어요.
-- 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로 또는 그 반대로).