ALTER TABLE ... PROJECTION

تتناول هذه الصفحة ماهية الإسقاطات، وكيفية استخدامها، والخيارات المختلفة لإدارتها.

تخزّن الإسقاطات البيانات بصيغة تُحسّن تنفيذ الاستعلامات، وتكون هذه الميزة مفيدة في الحالات التالية:

• تشغيل استعلامات على عمود ليس جزءًا من المفتاح الأساسي
• إجراء تجميع مسبق للأعمدة، مما يقلل كلاً من العمليات الحسابية وIO

يمكنك تعريف إسقاط واحد أو أكثر لجدول، وأثناء تحليل الاستعلام سيختار ClickHouse الإسقاط الذي يتطلب مسح أقل قدر من البيانات، وذلك من دون تعديل الاستعلام الذي قدّمه المستخدم.

يمكنك الاطلاع على مزيد من التفاصيل التقنية حول كيفية عمل الإسقاطات داخليًا في هذه الصفحة.

إنشاء الجدول:

CREATE TABLE visits_order
(
   `user_id` UInt64,
   `user_name` String,
   `pages_visited` Nullable(Float64),
   `user_agent` String
)
ENGINE = MergeTree()
PRIMARY KEY user_agent

باستخدام ALTER TABLE، يمكننا إضافة الإسقاط إلى جدولٍ موجود:

ALTER TABLE visits_order ADD PROJECTION user_name_projection (
    SELECT *
    ORDER BY user_name
)

ALTER TABLE visits_order MATERIALIZE PROJECTION user_name_projection

إدخال البيانات:

INSERT INTO visits_order SELECT
    number,
    'test',
    1.5 * (number / 2),
    'Android'
FROM numbers(1, 100);

سيتيح لنا الإسقاط إجراء تصفية حسب user_name بسرعة، حتى لو لم يكن user_name مُعرَّفًا في الجدول الأصلي بوصفه PRIMARY_KEY. أثناء تنفيذ الاستعلام، يحدّد ClickHouse أن مقدارًا أقل من البيانات سيُعالَج إذا استُخدم الإسقاط، لأن البيانات مرتبة حسب user_name.

SELECT
    *
FROM visits_order
WHERE user_name='test'
LIMIT 2

للتحقق من أن استعلامًا يستخدم الإسقاط، يمكننا مراجعة جدول system.query_log. في الحقل projections يظهر اسم الإسقاط المستخدم، أو يكون الحقل فارغًا إذا لم يُستخدم أي إسقاط:

SELECT query, projections FROM system.query_log WHERE query_id='<query_id>'

أنشئ الجدول باستخدام الإسقاط projection_visits_by_user:

CREATE TABLE visits
(
   `user_id` UInt64,
   `user_name` String,
   `pages_visited` Nullable(Float64),
   `user_agent` String,
   PROJECTION projection_visits_by_user
   (
       SELECT
           user_agent,
           sum(pages_visited)
       GROUP BY user_id, user_agent
   )
)
ENGINE = MergeTree()
ORDER BY user_agent

أدرِج البيانات:

INSERT INTO visits SELECT
    number,
    'test',
    1.5 * (number / 2),
    'Android'
FROM numbers(1, 100);

INSERT INTO visits SELECT
    number,
    'test',
    1. * (number / 2),
   'IOS'
FROM numbers(100, 500);

نفّذ أول استعلام باستخدام GROUP BY مع الحقل user_agent. لن يستخدم هذا الاستعلام الـ إسقاط المعرّف لأن التجميع المسبق لا يتطابق.

SELECT
    user_agent,
    count(DISTINCT user_id)
FROM visits
GROUP BY user_agent

لاستخدام الإسقاط، يمكنك تنفيذ استعلامات تحدد بعض حقول التجميع المسبق وGROUP BY أو جميعها:

SELECT
    user_agent
FROM visits
WHERE user_id > 50 AND user_id < 150
GROUP BY user_agent

SELECT
    user_agent,
    sum(pages_visited)
FROM visits
GROUP BY user_agent

كما ذُكر سابقًا، يمكنك مراجعة جدول system.query_log لمعرفة ما إذا كان قد استُخدم أي إسقاط. ويعرض الحقل projections اسم الـ إسقاط المستخدم. وسيكون فارغًا إذا لم يُستخدم أي إسقاط:

SELECT query, projections FROM system.query_log WHERE query_id='<query_id>'

إنشاء فهرس إسقاط:

CREATE TABLE events
(
    `event_time` DateTime,
    `event_id` UInt64,
    `user_id` UInt64,
    `huge_string` String,
    PROJECTION order_by_user_id INDEX user_id TYPE basic
)
ENGINE = MergeTree()
ORDER BY (event_id);

CREATE TABLE events
(
    `event_time` DateTime,
    `event_id` UInt64,
    `user_id` UInt64,
    `huge_string` String,
    PROJECTION order_by_user_id
    (
        SELECT
            _part_offset
        ORDER BY user_id
    )
)
ENGINE = MergeTree()
ORDER BY (event_id);

إدراج بعض البيانات النموذجية:

INSERT INTO events SELECT * FROM generateRandom() LIMIT 100000;

يحتفظ الحقل _part_offset بقيمته عبر عمليات الدمج والتعديلات، مما يجعله مفيدًا للفهرسة الثانوية. ويمكننا الاستفادة من ذلك في الاستعلامات:

SELECT
    count()
FROM events
WHERE _part_starting_offset + _part_offset IN (
    SELECT _part_starting_offset + _part_offset
    FROM events
    WHERE user_id = 42
)
SETTINGS enable_shared_storage_snapshot_in_query = 1

يمكن أن تتضمن الـ إسقاطات عبارة WHERE لتخزين مجموعة فرعية فقط من الصفوف. ويكون ذلك مفيدًا عندما تُصفّي الاستعلامات كثيرًا باستخدام شرط معروف، إذ لا تُخزِّن الـ إسقاط إلا الصفوف المطابقة، مما يقلل التخزين ويحسّن أداء الاستعلامات.

إنشاء جدول وإضافة إسقاط مُرشَّحة:

CREATE TABLE events
(
    `event_type` String,
    `time` DateTime,
    `message` String
)
ENGINE = MergeTree()
ORDER BY time;

ALTER TABLE events ADD PROJECTION proj_pageview (
    SELECT event_type, time, message
    WHERE event_type = 'pageview'
    ORDER BY time
);

ALTER TABLE events MATERIALIZE PROJECTION proj_pageview;

إدخال البيانات:

INSERT INTO events VALUES
    ('pageview', '2024-01-01', 'homepage'),
    ('click', '2024-01-02', 'button'),
    ('pageview', '2024-01-03', 'about');

عندما تستلزم عبارة WHERE الخاصة بالاستعلام عبارة WHERE الخاصة بالإسقاط (أي إن كل شرط في عامل تصفية الإسقاط موجود أيضًا في عامل تصفية الاستعلام)، يمكن للمُحسِّن استخدام الإسقاط تلقائيًا إذا حدّد أن ذلك مفيد:

-- This query implies the projection's WHERE, so the projection may be used:
SELECT time, message FROM events WHERE event_type = 'pageview';

-- A stricter query also implies the projection's WHERE:
SELECT time, message FROM events WHERE event_type = 'pageview' AND time > '2024-01-01';

-- This query does NOT imply the projection, so the base table is scanned:
SELECT time, message FROM events WHERE event_type = 'click';

فحص الاستلزام متحفّظ — إذ يعتمد على المطابقة الدقيقة لأجزاء الاقتران في الصيغة المعيارية للتعبير. وقد تفوته بعض فرص التحسين الصحيحة (مثل استلزام النطاقات)، لكنه لن يُنتج أبدًا نتائج غير صحيحة.

تتوفّر العمليات التالية على الإسقاطات:

استخدم تعليمة SQL التالية لإضافة وصف لإسقاط إلى البيانات الوصفية لجدول:

-- Normal projection (supports WHERE)
ALTER TABLE [db.]name [ON CLUSTER cluster] ADD PROJECTION [IF NOT EXISTS] name ( SELECT <COLUMN LIST EXPR> [WHERE <expr>] [ORDER BY] ) [WITH SETTINGS ( setting_name1 = setting_value1, setting_name2 = setting_value2, ...)]

-- Aggregate projection (supports WHERE)
ALTER TABLE [db.]name [ON CLUSTER cluster] ADD PROJECTION [IF NOT EXISTS] name ( SELECT <COLUMN LIST EXPR> [WHERE <expr>] [GROUP BY] ) [WITH SETTINGS ( setting_name1 = setting_value1, setting_name2 = setting_value2, ...)]

تُعرِّف WITH SETTINGS إعدادات على مستوى الإسقاط، والتي تُخصِّص كيفية تخزين البيانات في الإسقاط (على سبيل المثال، index_granularity أو index_granularity_bytes). وتقابل هذه الإعدادات مباشرةً إعدادات جدول MergeTree، لكنها تنطبق على هذا الإسقاط فقط.

مثال:

ALTER TABLE t
ADD PROJECTION p (
    SELECT x ORDER BY x
) WITH SETTINGS (
    index_granularity = 4096,
    index_granularity_bytes = 1048576
);

تحلّ إعدادات الإسقاط محل إعدادات الجدول المطبَّقة فعليًا على الإسقاط، مع مراعاة قواعد التحقق (على سبيل المثال، سيُرفض أي تجاوز غير صالح أو غير متوافق).

استخدم العبارة التالية لتعديل عبارة WITH SETTINGS في إسقاط موجود دون إعادة بناء بياناته:

ALTER TABLE [db.]name [ON CLUSTER cluster] MODIFY PROJECTION [IF EXISTS] name ( SELECT <COLUMN LIST EXPR> [WHERE <expr>] [GROUP BY] [ORDER BY] ) WITH SETTINGS ( setting_name1 = setting_value1, setting_name2 = setting_value2, ...)

بالنسبة إلى فهرس إسقاط، أعد تعريف INDEX بدلًا من استعلام SELECT:

ALTER TABLE [db.]name [ON CLUSTER cluster] MODIFY PROJECTION [IF EXISTS] name INDEX <index_expr> TYPE <index_type> WITH SETTINGS ( setting_name1 = setting_value1, setting_name2 = setting_value2, ...)

تعيد التعليمة ذكر تعريف الإسقاط كاملًا، لكن يجوز أن تختلف عن التعريف الحالي عبارة WITH SETTINGS فقط. يجب أن يظل استعلام الإسقاط نفسه (أو، بالنسبة إلى فهرس الإسقاط، تعبير الفهرس ونوعه) دون تغيير، لأن أجزاء الإسقاط الحالية تخزّن بيانات مُنشأة منه؛ ولتغييره، استخدم DROP PROJECTION ثم ADD PROJECTION.

لا تغيّر التعليمة سوى البيانات الوصفية للجدول ولا تعيد كتابة أي بيانات: تحتفظ أجزاء الإسقاط الحالية بالإعدادات التي كُتبت بها، بينما تستخدم أجزاء الإسقاط التي تُنشئها عمليات الإدراج والدمج المستقبلية الإعدادات الجديدة. لإعادة بناء الأجزاء الحالية بالإعدادات الجديدة، شغّل MATERIALIZE PROJECTION.

مثال:

ALTER TABLE t
MODIFY PROJECTION p (
    SELECT x ORDER BY x
) WITH SETTINGS (
    index_granularity = 128
);

استخدم التعليمة التالية لإزالة وصف الإسقاط من البيانات الوصفية للجداول وحذف ملفات الإسقاط من القرص. يُنفَّذ هذا على شكل mutation.

ALTER TABLE [db.]name [ON CLUSTER cluster] DROP PROJECTION [IF EXISTS] name

استخدم التعليمة أدناه لإعادة بناء الإسقاط name في القسم partition_name. يُنفَّذ ذلك على هيئة mutation.

ALTER TABLE [db.]table [ON CLUSTER cluster] MATERIALIZE PROJECTION [IF EXISTS] name [IN PARTITION partition_name]

استخدم التعليمة أدناه لحذف ملفات الإسقاط من القرص من دون إزالة التعريف. يُنفَّذ ذلك على شكل mutation.

ALTER TABLE [db.]table [ON CLUSTER cluster] CLEAR PROJECTION [IF EXISTS] name [IN PARTITION partition_name]

تُعد الأوامر ADD وMODIFY وDROP وCLEAR خفيفة الوزن، بمعنى أنها لا تغيّر سوى البيانات الوصفية أو تزيل الملفات. بالإضافة إلى ذلك، فهي تدعم النسخ المتماثل وتُزامِن البيانات الوصفية للإسقاط عبر ClickHouse Keeper أو ZooKeeper.

عند تنفيذ استعلام، يختار ClickHouse بين القراءة من الجدول الأصلي أو من أحد الإسقاطات التابعة له. ويُتخذ قرار القراءة من الجدول الأصلي أو من أحد إسقاطاته بشكل منفصل لكل جزء بيانات. يهدف ClickHouse عمومًا إلى قراءة أقل قدر ممكن من البيانات، ويستخدم عدة أساليب لتحديد أفضل جزء للقراءة منه، مثل أخذ عيّنة من المفتاح الأساسي للجزء. وفي بعض الحالات، لا تكون لأجزاء الجدول المصدر أجزاء إسقاط مقابلة. وقد يحدث هذا، على سبيل المثال، لأن إنشاء إسقاط لجدول في SQL يكون "lazy" افتراضيًا، أي إنه يؤثر فقط في البيانات المُدرجة حديثًا ويُبقي الأجزاء الموجودة كما هي.

وبما أن أحد الإسقاطات يحتوي بالفعل على قيم مجمّعة ومحسوبة مسبقًا، يحاول ClickHouse القراءة من أجزاء الإسقاط المقابلة لتجنّب تنفيذ التجميع مرة أخرى أثناء تشغيل الاستعلام. وإذا كان جزء معيّن يفتقر إلى جزء الإسقاط المقابل، فسيعود تنفيذ الاستعلام إلى الجزء الأصلي.

لكن ماذا يحدث إذا تغيّرت الصفوف في الجدول الأصلي بطريقة غير بسيطة نتيجة عمليات الدمج غير البسيطة في الخلفية لأجزاء البيانات؟ على سبيل المثال، افترض أن الجدول مخزّن باستخدام محرك الجدول ReplacingMergeTree. إذا جرى اكتشاف الصف نفسه في عدة أجزاء إدخال أثناء الدمج، فلن يُحتفَظ إلا بأحدث إصدار من الصف (من الجزء المُدرَج أخيرًا)، بينما ستُهمَل جميع الإصدارات الأقدم.

وبالمثل، إذا كان الجدول مخزّنًا باستخدام محرك الجدول AggregatingMergeTree، فقد تؤدي عملية الدمج إلى طيّ الصفوف المتطابقة في أجزاء الإدخال (استنادًا إلى قيم المفتاح الأساسي) في صف واحد لتحديث حالات التجميع الجزئية.

قبل ClickHouse v24.8، كانت أجزاء الإسقاط إما تفقد التزامن مع البيانات الأساسية بصمت، أو أن بعض العمليات مثل التحديثات وعمليات الحذف لم يكن ممكنًا تشغيلها مطلقًا لأن قاعدة البيانات كانت تطرح استثناءً تلقائيًا إذا كان للجدول إسقاطات.

اعتبارًا من v24.8، يتحكم إعداد جديد على مستوى الجدول deduplicate_merge_projection_mode في السلوك عند حدوث عمليات الدمج غير البسيطة المذكورة أعلاه في أجزاء الجدول الأصلي.

تُعد Delete mutations مثالًا آخر على عمليات دمج الأجزاء التي تُسقط صفوفًا من أجزاء الجدول الأصلي. واعتبارًا من v24.7، يتوفر أيضًا إعداد للتحكم في السلوك فيما يتعلق بـ delete mutations التي تُفعَّل بواسطة lightweight deletes: lightweight_mutation_projection_mode.

فيما يلي القيم الممكنة لكل من deduplicate_merge_projection_mode وlightweight_mutation_projection_mode:

• throw (الافتراضي): يُطرَح استثناء، مما يمنع أجزاء الإسقاط من فقدان التزامن.
• drop: تُسقَط أجزاء جدول الإسقاط المتأثرة. وستعود الاستعلامات إلى جزء الجدول الأصلي بالنسبة إلى أجزاء الإسقاط المتأثرة.
• rebuild: يُعاد بناء جزء الإسقاط المتأثر ليظل متسقًا مع البيانات الموجودة في جزء الجدول الأصلي.

لا يمكن استخدام عمود ALIAS ضمن عبارة ORDER BY الخاصة بالإسقاط. على سبيل المثال:

CREATE TABLE t
(
    id UInt64,
    a UInt32,
    ab_sum UInt64 ALIAS a + 1,
    PROJECTION p (SELECT a ORDER BY ab_sum)
)
ENGINE = MergeTree ORDER BY id;
-- Fails with UNKNOWN_IDENTIFIER

أعمدة ALIAS لا تُخزَّن فعليًا، بل تُحتسَب أثناء تنفيذ الاستعلام، لذا لا تكون متاحة ضمن مسار كتابة جزء الإسقاط عند تقييم تعبير الترتيب.

بدلًا من ذلك، استخدم أعمدة MATERIALIZED أو ضمِّن التعبير مباشرةً:

-- using MATERIALIZED column
CREATE TABLE t
(
    id UInt64,
    a UInt32,
    ab_sum UInt64 MATERIALIZED a + 1,
    PROJECTION p (SELECT a ORDER BY ab_sum)
)
ENGINE = MergeTree ORDER BY id;

-- using an inline expression
CREATE TABLE t
(
    id UInt64,
    a UInt32,
    PROJECTION p (SELECT a ORDER BY a + 1)
)
ENGINE = MergeTree ORDER BY id;

• "التحكم في الإسقاطات أثناء عمليات الدمج" (مقالة مدونة)
• "الإسقاطات" (دليل)
• "العروض المادية مقابل الإسقاطات"