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;

تعديل النوع لعمود فرعي ممكن ويؤدي إلى mutation (إعادة كتابة البيانات):

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 أو العكس).