يُعد إلغاء تطبيع البيانات في ClickHouse أسلوبًا يعتمد على استخدام الجداول المسطّحة للمساعدة في تقليل زمن استجابة الاستعلام إلى الحد الأدنى من خلال تجنّب عمليات JOIN.
مقارنة المخططات المُطبَّعة مقابل المخططات منزوعة التطبيع
JOIN، وذلك عبر تخزين جميع الإحصاءات أو الصفوف المرتبطة داخل صف أصلي على هيئة أعمدة وكائنات متداخلة. على سبيل المثال، في مخطط نموذجي لمدونة، يمكننا تخزين جميع Comments باعتبارها Array من الكائنات ضمن المنشورات المقابلة لها.
بشكل عام، نوصي بإلغاء التطبيع في الحالات التالية:
- أزِل إلغاء التطبيع عن الجداول التي نادرًا ما تتغير، أو التي يمكن فيها تحمّل تأخير قبل إتاحة البيانات للاستعلامات التحليلية، أي عندما يمكن إعادة تحميل البيانات بالكامل على شكل دفعة.
- تجنّب إلغاء التطبيع في العلاقات متعدد-إلى-متعدد، لأن ذلك قد يؤدي إلى الحاجة إلى تحديث عدد كبير من الصفوف إذا تغيّر صف واحد في المصدر.
- تجنّب إلغاء التطبيع في العلاقات عالية الكاردينالية. فإذا كان كل صف في جدول ما يرتبط بآلاف الإدخالات في جدول آخر، فسيتعيّن تمثيلها على شكل
Array — إما من نوع بدائي أو Tuple. وبوجه عام، لا يُنصح عادةً بالمصفوفات التي تحتوي على أكثر من 1000 Tuple.
- بدلًا من إلغاء التطبيع لجميع الأعمدة على هيئة كائنات متداخلة، فكّر في إلغاء التطبيع لإحصائية واحدة فقط باستخدام العروض المادية (انظر أدناه).
ليس من الضروري إلغاء التطبيع لكل المعلومات، بل فقط للمعلومات الأساسية التي تحتاج إلى الوصول إليها بشكل متكرر.
يمكن تنفيذ عملية إلغاء التطبيع إما في ClickHouse أو في المصدر الأصلي، مثلًا باستخدام Apache Flink.
تجنّب إلغاء التطبيع للبيانات التي تُحدَّث بشكل متكرر
- تشغيل عبارات JOIN الصحيحة عند تغيّر صف في جدول. ومن المثالي ألّا يؤدي ذلك إلى تحديث جميع الكائنات المرتبطة بعملية JOIN، بل فقط تلك التي تأثرت. ويتطلب تعديل عمليات JOIN لتطبيق عامل تصفية على الصفوف الصحيحة بكفاءة، وتحقيق ذلك في ظل معدل نقل مرتفع، أدوات خارجية أو جهدًا هندسيًا.
- يجب إدارة تحديثات الصفوف في ClickHouse بعناية، مما يضيف مزيدًا من التعقيد.
لذلك، تكون عملية التحديث على شكل دفعات أكثر شيوعًا، حيث يُعاد تحميل جميع الكائنات التي خضعت لإلغاء التطبيع بشكل دوري.
حالات عملية لإلغاء التطبيع
Posts جرى بالفعل إلغاء تطبيعه بإحصاءات مثل AnswerCount وCommentCount — إذ تُوفَّر البيانات المصدر بهذا الشكل. لكن عمليًا، قد نرغب في تطبيع هذه المعلومات، لأنها على الأرجح ستكون عرضة لتغييرات متكررة. كما أن كثيرًا من هذه الأعمدة متاح أيضًا عبر جداول أخرى؛ فعلى سبيل المثال، تتوفر تعليقات المنشور من خلال العمود PostId والجدول Comments. ولأغراض هذا المثال، نفترض أن المنشورات يُعاد تحميلها ضمن عملية مجمّعة.
وسنقتصر هنا على إلغاء تطبيع الجداول الأخرى إلى Posts، لأننا نعدّه جدولنا الرئيسي لأغراض التحليلات. وقد يكون إلغاء التطبيع في الاتجاه المعاكس مناسبًا أيضًا لبعض الاستعلامات، مع انطباق الاعتبارات نفسها المذكورة أعلاه.
في كل مثال من الأمثلة التالية، افترض وجود استعلام يتطلب استخدام الجدولين معًا في عملية join.
تُمثَّل Votes المرتبطة بـ Posts في جداول منفصلة. ويظهر أدناه المخطط المُحسَّن لذلك، إلى جانب أمر insert المستخدم لتحميل البيانات:
CREATE TABLE votes
(
`Id` UInt32,
`PostId` Int32,
`VoteTypeId` UInt8,
`CreationDate` DateTime64(3, 'UTC'),
`UserId` Int32,
`BountyAmount` UInt8
)
ENGINE = MergeTree
ORDER BY (VoteTypeId, CreationDate, PostId)
INSERT INTO votes SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/votes/*.parquet')
0 rows in set. Elapsed: 26.272 sec. Processed 238.98 million rows, 2.13 GB (9.10 million rows/s., 80.97 MB/s.)
SELECT round(avg(c)) AS avg_votes_per_hr, round(avg(posts)) AS avg_posts_per_hr
FROM
(
SELECT
toStartOfHour(CreationDate) AS hr,
count() AS c,
uniq(PostId) AS posts
FROM votes
GROUP BY hr
)
┌─avg_votes_per_hr─┬─avg_posts_per_hr─┐
│ 41759 │ 33322 │
└──────────────────┴──────────────────┘
SELECT PostId, concat('https://stackoverflow.com/questions/', PostId) AS url, count() AS c
FROM votes
GROUP BY PostId
ORDER BY c DESC
LIMIT 5
┌───PostId─┬─url──────────────────────────────────────────┬─────c─┐
│ 11227902 │ https://stackoverflow.com/questions/11227902 │ 35123 │
│ 927386 │ https://stackoverflow.com/questions/927386 │ 29090 │
│ 11227809 │ https://stackoverflow.com/questions/11227809 │ 27475 │
│ 927358 │ https://stackoverflow.com/questions/927358 │ 26409 │
│ 2003515 │ https://stackoverflow.com/questions/2003515 │ 25899 │
└──────────┴──────────────────────────────────────────────┴───────┘
Score حاليًا إحصائية من هذا النوع، أي إجمالي الأصوات المؤيدة مطروحًا منه الأصوات المعارضة. ومن الناحية المثالية، نحتاج فقط إلى استرجاع هذه الإحصاءات في وقت الاستعلام من خلال عملية بحث بسيطة (راجع القواميس).
لننظر الآن إلى Users وBadges:
نُدرج البيانات أولًا باستخدام الأمر التالي:
CREATE TABLE users
(
`Id` Int32,
`Reputation` LowCardinality(String),
`CreationDate` DateTime64(3, 'UTC') CODEC(Delta(8), ZSTD(1)),
`DisplayName` String,
`LastAccessDate` DateTime64(3, 'UTC'),
`AboutMe` String,
`Views` UInt32,
`UpVotes` UInt32,
`DownVotes` UInt32,
`WebsiteUrl` String,
`Location` LowCardinality(String),
`AccountId` Int32
)
ENGINE = MergeTree
ORDER BY (Id, CreationDate)
CREATE TABLE badges
(
`Id` UInt32,
`UserId` Int32,
`Name` LowCardinality(String),
`Date` DateTime64(3, 'UTC'),
`Class` Enum8('Gold' = 1, 'Silver' = 2, 'Bronze' = 3),
`TagBased` Bool
)
ENGINE = MergeTree
ORDER BY UserId
INSERT INTO users SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/users.parquet')
0 rows in set. Elapsed: 26.229 sec. Processed 22.48 million rows, 1.36 GB (857.21 thousand rows/s., 51.99 MB/s.)
INSERT INTO badges SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/badges.parquet')
0 rows in set. Elapsed: 18.126 sec. Processed 51.29 million rows, 797.05 MB (2.83 million rows/s., 43.97 MB/s.)
SELECT UserId, count() AS c FROM badges GROUP BY UserId ORDER BY c DESC LIMIT 5
┌─UserId─┬─────c─┐
│ 22656 │ 19334 │
│ 6309 │ 10516 │
│ 100297 │ 7848 │
│ 157882 │ 7574 │
│ 29407 │ 6512 │
└────────┴───────┘
قد نرغب في إلغاء تطبيع إحصاءات من جدول Badges إلى جدول Users، مثل عدد الشارات. ونتناول مثالًا كهذا عند استخدام القواميس لهذه المجموعة من البيانات وقت الإدراج.
يربط PostLinks بين عناصر Posts التي يعتبرها المستخدمون ذات صلة أو مكررة. يوضّح الاستعلام التالي البنية وأمر التحميل:
CREATE TABLE postlinks
(
`Id` UInt64,
`CreationDate` DateTime64(3, 'UTC'),
`PostId` Int32,
`RelatedPostId` Int32,
`LinkTypeId` Enum('Linked' = 1, 'Duplicate' = 3)
)
ENGINE = MergeTree
ORDER BY (PostId, RelatedPostId)
INSERT INTO postlinks SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/postlinks.parquet')
0 rows in set. Elapsed: 4.726 sec. Processed 6.55 million rows, 129.70 MB (1.39 million rows/s., 27.44 MB/s.)
SELECT PostId, count() AS c
FROM postlinks
GROUP BY PostId
ORDER BY c DESC LIMIT 5
┌───PostId─┬───c─┐
│ 22937618 │ 125 │
│ 9549780 │ 120 │
│ 3737139 │ 109 │
│ 18050071 │ 103 │
│ 25889234 │ 82 │
└──────────┴─────┘
SELECT
round(avg(c)) AS avg_votes_per_hr,
round(avg(posts)) AS avg_posts_per_hr
FROM
(
SELECT
toStartOfHour(CreationDate) AS hr,
count() AS c,
uniq(PostId) AS posts
FROM postlinks
GROUP BY hr
)
┌─avg_votes_per_hr─┬─avg_posts_per_hr─┐
│ 54 │ 44 │
└──────────────────┴──────────────────┘
CREATE TABLE posts_with_duplicate_count
(
`Id` Int32 CODEC(Delta(4), ZSTD(1)),
... -other columns
`DuplicatePosts` UInt16
) ENGINE = MergeTree
ORDER BY (PostTypeId, toDate(CreationDate), CommentCount)
INSERT INTO SELECT لإجراء JOIN بين إحصائية التكرار وبيانات posts لدينا.
INSERT INTO posts_with_duplicate_count SELECT
posts.*,
DuplicatePosts
FROM posts AS posts
LEFT JOIN
(
SELECT PostId, countIf(LinkTypeId = 'Duplicate') AS DuplicatePosts
FROM postlinks
GROUP BY PostId
) AS postlinks ON posts.Id = postlinks.PostId
استغلال الأنواع المعقدة للعلاقات من واحد إلى متعدد
- Named Tuples - تتيح تمثيل بنية مرتبطة كمجموعة من الأعمدة.
- Array(Tuple) أو Nested - مصفوفة من Named Tuples، وتُعرف أيضًا باسم Nested، بحيث يمثّل كل إدخال كائنًا. وينطبق ذلك على العلاقات من واحد إلى متعدد.
كمثال، نوضح أدناه كيفية إجراء إلغاء التطبيع لـ PostLinks ضمن Posts.
يمكن أن يحتوي كل منشور على عدد من الروابط إلى منشورات أخرى كما هو موضح سابقًا في مخطط PostLinks. وباستخدام النوع Nested، يمكننا تمثيل هذه المنشورات المرتبطة والمكررة كما يلي:
SET flatten_nested=0
CREATE TABLE posts_with_links
(
`Id` Int32 CODEC(Delta(4), ZSTD(1)),
... -other columns
`LinkedPosts` Nested(CreationDate DateTime64(3, 'UTC'), PostId Int32),
`DuplicatePosts` Nested(CreationDate DateTime64(3, 'UTC'), PostId Int32),
) ENGINE = MergeTree
ORDER BY (PostTypeId, toDate(CreationDate), CommentCount)
لاحظ استخدام الإعداد flatten_nested=0. نوصي بتعطيل تسطيح البيانات المتداخلة.
يمكننا تنفيذ عملية إلغاء التطبيع هذه باستخدام استعلام INSERT INTO SELECT مع OUTER JOIN:
INSERT INTO posts_with_links
SELECT
posts.*,
arrayMap(p -> (p.1, p.2), arrayFilter(p -> p.3 = 'Linked' AND p.2 != 0, Related)) AS LinkedPosts,
arrayMap(p -> (p.1, p.2), arrayFilter(p -> p.3 = 'Duplicate' AND p.2 != 0, Related)) AS DuplicatePosts
FROM posts
LEFT JOIN (
SELECT
PostId,
groupArray((CreationDate, RelatedPostId, LinkTypeId)) AS Related
FROM postlinks
GROUP BY PostId
) AS postlinks ON posts.Id = postlinks.PostId
0 rows in set. Elapsed: 155.372 sec. Processed 66.37 million rows, 76.33 GB (427.18 thousand rows/s., 491.25 MB/s.)
Peak memory usage: 6.98 GiB.
لاحظ الزمن هنا. لقد تمكّنا من إلغاء تطبيع 66 مليون صف خلال نحو دقيقتين. وكما سنرى لاحقًا، فهذه عملية يمكننا جدولة تشغيلها.
لاحظ استخدام دوال groupArray لطيّ PostLinks إلى مصفوفة واحدة لكل PostId قبل الربط. ثم تُرشَّح هذه المصفوفة إلى قائمتين فرعيتين: LinkedPosts وDuplicatePosts، مع استبعاد أي نتائج فارغة ناتجة عن الربط الخارجي أيضًا.
يمكننا اختيار بعض الصفوف للاطّلاع على بنيتنا الجديدة بعد إلغاء التطبيع:
SELECT LinkedPosts, DuplicatePosts
FROM posts_with_links
WHERE (length(LinkedPosts) > 2) AND (length(DuplicatePosts) > 0)
LIMIT 1
FORMAT Vertical
Row 1:
──────
LinkedPosts: [('2017-04-11 11:53:09.583',3404508),('2017-04-11 11:49:07.680',3922739),('2017-04-11 11:48:33.353',33058004)]
DuplicatePosts: [('2017-04-11 12:18:37.260',3922739),('2017-04-11 12:18:37.260',33058004)]
تنظيم وجدولة إلغاء التطبيع
INSERT INTO SELECT. وهذا مناسب لعمليات التحويل الدُفعية الدورية.
لدى المستخدمين عدة خيارات لتنسيق ذلك في ClickHouse، بافتراض أن عملية تحميل دُفعية دورية مقبولة:
- العروض المادية القابلة للتحديث - يمكن استخدام العروض المادية القابلة للتحديث لجدولة استعلام بشكل دوري مع إرسال النتائج إلى الجدول الهدف. وعند تنفيذ الاستعلام، يضمن العرض تحديث الجدول الهدف ذريًا. ويوفر ذلك وسيلة أصلية في ClickHouse لجدولة هذا العمل.
- أدوات خارجية - استخدام أدوات مثل dbt وAirflow لجدولة التحويل بشكل دوري. ويضمن تكامل ClickHouse مع dbt تنفيذ ذلك ذريًا، مع إنشاء إصدار جديد من الجدول الهدف ثم تبديله ذريًا مع الإصدار الذي يستقبل الاستعلامات (عبر الأمر EXCHANGE).
قد ترغب أيضًا في تنفيذ ذلك خارج ClickHouse، قبل الإدراج، باستخدام تقنيات التدفّق مثل Apache Flink. أو يمكن استخدام العروض المادية التزايدية لتنفيذ هذه العملية عند إدراج البيانات. آخر تعديل في ٢٩ يونيو ٢٠٢٦
