يوضح هذا المثال كيفية إنشاء عرض مادي، ثم كيفية إلحاق عرض مادي ثانٍ بالأول على نحو متسلسل. في هذه الصفحة، سترى كيفية تنفيذ ذلك، والإمكانات المتاحة، والقيود. ويمكن تلبية حالات استخدام مختلفة عبر إنشاء عرض مادي يستخدم عرضًا ماديًا ثانيًا بوصفه المصدر.
مثال:
سنستخدم مجموعة بيانات وهمية تتضمن عدد المشاهدات لكل ساعة لمجموعة من أسماء النطاقات.
هدفنا
- نحتاج إلى البيانات مجمّعة حسب الشهر لكل اسم نطاق،
- ونحتاج أيضًا إلى البيانات مجمّعة حسب السنة لكل اسم نطاق.
يمكنك اختيار أحد هذه الخيارات:
- كتابة استعلامات تقرأ البيانات وتجمعها أثناء طلب SELECT
- تجهيز البيانات وقت الإدخال بتنسيق جديد
- تجهيز البيانات وقت الإدخال وفق تجميع محدد.
يتيح لك تجهيز البيانات باستخدام العروض المادية تقليل كمية البيانات والحسابات التي يحتاج ClickHouse إلى تنفيذها، مما يجعل طلبات SELECT أسرع.
الجدول المصدر للعروض المادية
Null.
CREATE DATABASE IF NOT EXISTS analytics;
CREATE TABLE analytics.hourly_data
(
`domain_name` String,
`event_time` DateTime,
`count_views` UInt64
)
ENGINE = Null
يمكنك إنشاء عرض مادي على جدول Null. لذا ستؤثر البيانات المكتوبة إلى الجدول في العرض، لكن البيانات الخام الأصلية ستظل تُهمَل.
الجدول المُجمَّع الشهري والعرض المادي
Target. وفي هذا المثال، سيكون analytics.monthly_aggregated_data، وسنخزّن فيه مجموع مرات المشاهدة حسب الشهر واسم النطاق.
CREATE TABLE analytics.monthly_aggregated_data
(
`domain_name` String,
`month` Date,
`sumCountViews` AggregateFunction(sum, UInt64)
)
ENGINE = AggregatingMergeTree
ORDER BY (domain_name, month)
CREATE MATERIALIZED VIEW analytics.monthly_aggregated_data_mv
TO analytics.monthly_aggregated_data
AS
SELECT
toDate(toStartOfMonth(event_time)) AS month,
domain_name,
sumState(count_views) AS sumCountViews
FROM analytics.hourly_data
GROUP BY
domain_name,
month
الجدول السنوي المجمّع والعرض المادي
monthly_aggregated_data.
سنُنشئ أولًا جدول هدف جديدًا سيخزّن إجمالي المشاهدات المجمّعة سنويًا لكل اسم نطاق.
CREATE TABLE analytics.year_aggregated_data
(
`domain_name` String,
`year` UInt16,
`sumCountViews` UInt64
)
ENGINE = SummingMergeTree()
ORDER BY (domain_name, year)
FROM الجدول monthly_aggregated_data، وهذا يعني أن تدفّق البيانات سيكون على النحو التالي:
- تصل البيانات إلى الجدول
hourly_data.
- سيُمرِّر ClickHouse البيانات المستلمة إلى جدول العرض المادي الأول
monthly_aggregated_data،
- أخيرًا، ستُمرَّر البيانات المستلمة في الخطوة 2 إلى
year_aggregated_data.
CREATE MATERIALIZED VIEW analytics.year_aggregated_data_mv
TO analytics.year_aggregated_data
AS
SELECT
toYear(toStartOfYear(month)) AS year,
domain_name,
sumMerge(sumCountViews) AS sumCountViews
FROM analytics.monthly_aggregated_data
GROUP BY
domain_name,
year
من المفاهيم الخاطئة الشائعة عند العمل مع العروض المادية أن البيانات تُقرأ من الجدول. لكن هذه ليست الطريقة التي تعمل بها العروض المادية؛ فالبيانات التي تُمرَّر هي block الذي تم insert له، وليست النتيجة النهائية في جدولك.لنتخيّل في هذا المثال أن الـ engine المستخدم في monthly_aggregated_data هو CollapsingMergeTree. عندها لن تكون البيانات المُمرَّرة إلى الـ العرض المادي الثانية year_aggregated_data_mv هي النتيجة النهائية للجدول بعد collapsing، بل سيُمرَّر block البيانات بالحقول المعرّفة كما في SELECT ... GROUP BY.إذا كنت تستخدم CollapsingMergeTree أو ReplacingMergeTree أو حتى SummingMergeTree، وتخطط لإنشاء عرض مادي متسلسلة، فعليك فهم القيود الموضّحة هنا.
INSERT INTO analytics.hourly_data (domain_name, event_time, count_views)
VALUES ('clickhouse.com', '2019-01-01 10:00:00', 1),
('clickhouse.com', '2019-02-02 00:00:00', 2),
('clickhouse.com', '2019-02-01 00:00:00', 3),
('clickhouse.com', '2020-01-01 00:00:00', 6);
SELECT على محتويات analytics.hourly_data، فسترى ما يلي لأن محرك الجدول هو Null، ولكن جرت معالجة البيانات.
SELECT * FROM analytics.hourly_data
Ok.
0 rows in set. Elapsed: 0.002 sec.
sumCountViews، فسترى التمثيل الثنائي (في بعض الطرفيات)، لأن القيمة لا تُخزَّن كرقم بل كنوع AggregateFunction.
وللحصول على النتيجة النهائية لعملية التجميع، ينبغي استخدام اللاحقة -Merge.
يمكنك رؤية المحارف الخاصة المخزَّنة في AggregateFunction باستخدام هذا الاستعلام:
SELECT sumCountViews FROM analytics.monthly_aggregated_data
┌─sumCountViews─┐
│ │
│ │
│ │
└───────────────┘
3 rows in set. Elapsed: 0.003 sec.
Merge للحصول على قيمة sumCountViews:
SELECT
sumMerge(sumCountViews) AS sumCountViews
FROM analytics.monthly_aggregated_data;
┌─sumCountViews─┐
│ 12 │
└───────────────┘
1 row in set. Elapsed: 0.003 sec.
AggregatingMergeTree عرّفنا AggregateFunction على أنها sum، لذا يمكننا استخدام sumMerge. وعندما نستخدم الدالة avg في AggregateFunction، فسنستخدم avgMerge، وهكذا.
SELECT
month,
domain_name,
sumMerge(sumCountViews) AS sumCountViews
FROM analytics.monthly_aggregated_data
GROUP BY
domain_name,
month
monthly_aggregated_data، يمكننا الحصول على البيانات المجمّعة حسب الشهر لكل اسم نطاق:
SELECT
month,
domain_name,
sumMerge(sumCountViews) AS sumCountViews
FROM analytics.monthly_aggregated_data
GROUP BY
domain_name,
month
┌──────month─┬─domain_name────┬─sumCountViews─┐
│ 2020-01-01 │ clickhouse.com │ 6 │
│ 2019-01-01 │ clickhouse.com │ 1 │
│ 2019-02-01 │ clickhouse.com │ 5 │
└────────────┴────────────────┴───────────────┘
3 rows in set. Elapsed: 0.004 sec.
SELECT
year,
domain_name,
sum(sumCountViews)
FROM analytics.year_aggregated_data
GROUP BY
domain_name,
year
┌─year─┬─domain_name────┬─sum(sumCountViews)─┐
│ 2019 │ clickhouse.com │ 6 │
│ 2020 │ clickhouse.com │ 6 │
└──────┴────────────────┴────────────────────┘
2 rows in set. Elapsed: 0.004 sec.
دمج عدة جداول مصدر في جدول هدف واحد
UNION ALL.
أولًا، أنشئ جدولَي مصدر يمثلان مجموعات مختلفة من المقاييس:
CREATE TABLE analytics.impressions
(
`event_time` DateTime,
`domain_name` String
) ENGINE = MergeTree ORDER BY (domain_name, event_time)
;
CREATE TABLE analytics.clicks
(
`event_time` DateTime,
`domain_name` String
) ENGINE = MergeTree ORDER BY (domain_name, event_time)
;
Target باستخدام مجموعة المقاييس الموحَّدة:
CREATE TABLE analytics.daily_overview
(
`on_date` Date,
`domain_name` String,
`impressions` SimpleAggregateFunction(sum, UInt64),
`clicks` SimpleAggregateFunction(sum, UInt64)
) ENGINE = AggregatingMergeTree ORDER BY (on_date, domain_name)
Target نفسه. لا تحتاج إلى تضمين الأعمدة المفقودة صراحةً:
CREATE MATERIALIZED VIEW analytics.daily_impressions_mv
TO analytics.daily_overview
AS
SELECT
toDate(event_time) AS on_date,
domain_name,
count() AS impressions,
0 clicks ---<<<--- if you omit this, it will be the same 0
FROM
analytics.impressions
GROUP BY
toDate(event_time) AS on_date,
domain_name
;
CREATE MATERIALIZED VIEW analytics.daily_clicks_mv
TO analytics.daily_overview
AS
SELECT
toDate(event_time) AS on_date,
domain_name,
count() AS clicks,
0 impressions ---<<<--- if you omit this, it will be the same 0
FROM
analytics.clicks
GROUP BY
toDate(event_time) AS on_date,
domain_name
;
Target:
INSERT INTO analytics.impressions (domain_name, event_time)
VALUES ('clickhouse.com', '2019-01-01 00:00:00'),
('clickhouse.com', '2019-01-01 12:00:00'),
('clickhouse.com', '2019-02-01 00:00:00'),
('clickhouse.com', '2019-03-01 00:00:00')
;
INSERT INTO analytics.clicks (domain_name, event_time)
VALUES ('clickhouse.com', '2019-01-01 00:00:00'),
('clickhouse.com', '2019-01-01 12:00:00'),
('clickhouse.com', '2019-03-01 00:00:00')
;
Target:
SELECT
on_date,
domain_name,
sum(impressions) AS impressions,
sum(clicks) AS clicks
FROM
analytics.daily_overview
GROUP BY
on_date,
domain_name
;
┌────on_date─┬─domain_name────┬─impressions─┬─clicks─┐
│ 2019-01-01 │ clickhouse.com │ 2 │ 2 │
│ 2019-03-01 │ clickhouse.com │ 1 │ 1 │
│ 2019-02-01 │ clickhouse.com │ 1 │ 0 │
└────────────┴────────────────┴─────────────┴────────┘
3 rows in set. Elapsed: 0.018 sec.
