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

ClickHouse におけるネストされたデータ構造の概要

ネストされたデータ構造は、セルの中にあるテーブルのようなものです。ネストされたデータ構造のパラメータ (カラム名と型) は、CREATE TABLE クエリと同じ方法で指定します。テーブルの各行は、ネストされたデータ構造内の任意の数の行に対応できます。

カラム名にはドットを使用しないでください

ドットを含むカラム名、共通のドット付きプレフィックスを持つカラム、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ステートメントでは、Goalsというネストされたデータ構造を宣言しており、コンバージョン、つまり達成された目標に関するデータが含まれています。 visitsテーブルの各行は、0 件以上のコンバージョンに対応します。

flatten_nested設定を0にすると (デフォルトはflatten_nested=1) 、任意のレベルのネストをサポートできます。

通常、ネストされたデータ構造を扱う場合、そのカラムはドット区切りのカラム名で指定します。 これらのカラムは、対応する型の配列を構成します。 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 カラムのフィルタリング

Nested 構造の各カラムは Array として格納されるため、WHERE 句で参照すると、個々の要素ではなく各行の配列全体が返されます。ネストされたカラムをスカラー値と直接比較することはできないため、代わりに array functions を使用する必要があります。

たとえば、次のクエリは単に結果が 0 行になるのではなく、例外を発生させます。これは、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,
     [], [], [], [], [], []);

1 つの行内にあるネストされたすべてのサブカラム配列は、同じ長さでなければなりません。長さが一致しない場合はエラーになります：

-- 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 にする、またはその逆) 。