محرك الجدول PostgreSQL

يتيح محرك PostgreSQL تنفيذ استعلامات SELECT وINSERT على البيانات المخزنة على خادم PostgreSQL بعيد.

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 type1 [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 type2 [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = PostgreSQL({host:port, database, table, user, password[, schema, [, on_conflict]] | named_collection[, option=value [,..]]})
SETTINGS
    [ postgresql_connection_pool_size=16, ]
    [ postgresql_connection_pool_wait_timeout=5000, ]
    [ postgresql_connection_pool_retries=2, ]
    [ postgresql_connection_pool_auto_close_connection=false, ]
    [ postgresql_connection_attempt_timeout=2 ]
;

راجع وصفًا مفصلًا لاستعلام CREATE TABLE.

يمكن أن تختلف بنية الجدول عن بنية جدول PostgreSQL الأصلي:

• يجب أن تكون أسماء الأعمدة مطابقة لما هي عليه في جدول PostgreSQL الأصلي، ولكن يمكنك استخدام بعض هذه الأعمدة فقط وبأي ترتيب.
• قد تختلف أنواع الأعمدة عن تلك الموجودة في جدول PostgreSQL الأصلي. يحاول ClickHouse cast القيم إلى أنواع بيانات ClickHouse.
• يحدّد الإعداد external_table_functions_use_nulls كيفية التعامل مع الأعمدة Nullable. القيمة الافتراضية: 1. إذا كانت القيمة 0، فلن تُنشئ دالة الجدول أعمدة Nullable، وستُدرج القيم الافتراضية بدلًا من قيم NULL. وينطبق هذا أيضًا على قيم NULL داخل المصفوفات.

معلمات المحرك

• host:port — عنوان خادم PostgreSQL.
• database — اسم قاعدة البيانات البعيدة.
• table — اسم الجدول البعيد، أو استعلام يُمرَّر إلى PostgreSQL كما هو (راجع تمرير استعلام بدلًا من اسم جدول).
• user — مستخدم PostgreSQL.
• password — كلمة مرور المستخدم.
• schema — مخطط الجدول غير الافتراضي. اختياري.
• on_conflict — استراتيجية حل التعارض. مثال: ON CONFLICT DO NOTHING. اختياري. ملاحظة: ستؤدي إضافة هذا الخيار إلى جعل الإدراج أقل كفاءة.

يُنصح باستخدام المجموعات المسماة (المتاحة منذ الإصدار 21.11) في بيئة الإنتاج. إليك مثالًا:

<named_collections>
    <postgres_creds>
        <host>localhost</host>
        <port>5432</port>
        <user>postgres</user>
        <password>****</password>
        <schema>schema1</schema>
    </postgres_creds>
</named_collections>

يمكن تجاوز بعض المعلمات باستخدام وسيطات المفتاح-القيمة:

SELECT * FROM postgresql(postgres_creds, table='table1');

تُمرَّر معلمات TLS/SSL إلى libpq، ويمكن ضبطها كمفاتيح في مجموعة مُسمّاة أو كوسيطات مفتاح-قيمة لاحقة: sslmode (disable أو allow أو prefer أو require أو verify-ca أو verify-full)، بالإضافة إلى الشهادات والمفتاح، بإحدى صيغتين. عند عدم تعيينها، تُستخدم القيم الافتراضية لـ libpq (sslmode=prefer).

• sslrootcert (شهادة CA، أو القيمة الخاصة system) وsslcert (شهادة العميل) وsslkey (المفتاح الخاص للعميل) هي مسارات لملفات محلية على الخادم. لا يمكن تحديدها إلا ضمن مجموعة مُسمّاة معرّفة في ملف تهيئة الخادم، ولا يمكن تجاوزها في استعلام، إذ يفتح الخادم الملفات بصلاحياته الخاصة.
• تقبل sslrootcert_pem وsslcert_pem وsslkey_pem المحتوى الحرفي للملف المقابل بدلاً من مساره. ويمكن تحديدها في أي مكان — في استعلام، أو في مجموعة مُسمّاة أُنشئت باستخدام SQL، أو كتجاوز لمجموعة مُسمّاة — وتُحجب في السجلات واستعلامات SHOW كما تُحجب كلمة المرور.

على سبيل المثال، لفرض اتصال مشفّر والتحقق من شهادة الخادم:

<named_collections>
    <postgres_creds>
        <host>localhost</host>
        <port>5432</port>
        <user>postgres</user>
        <password>****</password>
        <sslmode>verify-full</sslmode>
        <sslrootcert>/etc/clickhouse-server/postgresql-ca.crt</sslrootcert>
    </postgres_creds>
</named_collections>

الأمر نفسه دون استخدام ملف إعدادات، مع تمرير محتوى الشهادة في الاستعلام:

CREATE TABLE postgres_table (id UInt64, value String)
ENGINE = PostgreSQL('localhost:5432', 'database', 'table', 'user', 'password',
                    sslmode = 'verify-full', sslrootcert_pem = '-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----');

يمكن تهيئة مجمّع الاتصالات الذي يستخدمه محرك الجدول PostgreSQL (ودالة الجدول postgresql) لكل جدول على حدة باستخدام عبارة SETTINGS. وعند عدم تحديد إعداد معيّن، تُستخدم تلقائيًا قيمة إعداد postgresql_* المقابل على مستوى الاستعلام.

حجم مجمّع الاتصالات (إذا كانت جميع الاتصالات قيد الاستخدام، ينتظر الاستعلام حتى يصبح أحد الاتصالات متاحًا). يجب أن تكون القيمة غير صفرية.

القيمة الافتراضية: 16.

مهلة انتظار push/pop لمجمع الاتصالات، بالمللي ثانية، عندما يكون المجمع فارغًا. تعني القيمة 0 أنه يتم الحظر عند فراغ المجمع.

القيمة الافتراضية: 5000.

عدد مرات إعادة المحاولة لعمليتَي push/pop في مجمع الاتصالات.

القيمة الافتراضية: 2.

أغلق الاتصال قبل إعادته إلى مجمّع الاتصالات.

القيمة الافتراضية: false.

مهلة الاتصال، بالثواني، لمحاولة واحدة للاتصال بنقطة نهاية PostgreSQL. تُمرَّر القيمة كمعلمة connect_timeout في عنوان URL الخاص بالاتصال.

القيمة الافتراضية: 2.

مثال:

CREATE TABLE pg_table
(
    `float_nullable` Nullable(Float32),
    `str` String,
    `int_id` Int32
)
ENGINE = PostgreSQL('localhost:5432', 'public', 'test', 'postgres_user', 'postgres_password')
SETTINGS postgresql_connection_pool_size = 32, postgresql_connection_pool_auto_close_connection = 1;

تُنفَّذ استعلامات SELECT على جانب PostgreSQL بصيغة COPY (SELECT ...) TO STDOUT داخل معاملة PostgreSQL للقراءة فقط، مع تنفيذ commit بعد كل استعلام SELECT.

تُنفَّذ عبارات WHERE البسيطة مثل =, !=, >, >=, <, <=, وIN على خادم PostgreSQL.

تُنفَّذ جميع عمليات JOIN، وعمليات التجميع، والفرز، وشروط IN [ array ]، وقيد أخذ العينات الخاص بـ LIMIT في ClickHouse فقط بعد اكتمال الاستعلام إلى PostgreSQL.

بدلًا من اسم جدول، يمكن أن تكون الوسيطة table استعلام SELECT يُمرَّر إلى PostgreSQL كما هو. ويُستدل على بنية الجدول من نتيجة الاستعلام. ويمكن كتابة الاستعلام إما كاستعلام فرعي، أو تغليفه داخل الدالة query:

CREATE TABLE pg_table ENGINE = PostgreSQL('localhost:5432', 'test', (SELECT a, b FROM t1 JOIN t2 USING (id) WHERE a > 0), 'user', 'password');
CREATE TABLE pg_table ENGINE = PostgreSQL('localhost:5432', 'test', query('SELECT a, b FROM t1 JOIN t2 USING (id) WHERE a > 0'), 'user', 'password');

يُعد هذا مفيدًا لدفع تنفيذ عمليات JOIN وعمليات التجميع أو أي معالجة أخرى إلى PostgreSQL. هذا الجدول للقراءة فقط: لا يُسمح بتنفيذ INSERT فيه. وتدعم دالة الجدول postgresql الصياغة نفسها.

تُنفَّذ استعلامات INSERT على جانب PostgreSQL بصيغة COPY \"table_name\" (field1, field2, ... fieldN) FROM STDIN داخل معاملة PostgreSQL مع auto-commit بعد كل تعليمة INSERT.

تُحوَّل أنواع Array في PostgreSQL إلى مصفوفات في ClickHouse.

يدعم عدة نسخ متماثلة يجب إدراجها باستخدام |. على سبيل المثال:

CREATE TABLE test_replicas (id UInt32, name String) ENGINE = PostgreSQL(`postgres{2|3|4}:5432`, 'clickhouse', 'test_replicas', 'postgres', 'mysecretpassword');

يتم دعم تحديد أولوية النسخ المتماثلة لمصدر قاموس PostgreSQL. وكلما زادت القيمة في map، انخفضت الأولوية. أعلى أولوية هي 0.

في المثال أدناه، تتمتع النسخة المتماثلة example01-1 بأعلى أولوية:

<postgresql>
    <port>5432</port>
    <user>clickhouse</user>
    <password>qwerty</password>
    <replica>
        <host>example01-1</host>
        <priority>1</priority>
    </replica>
    <replica>
        <host>example01-2</host>
        <priority>2</priority>
    </replica>
    <db>db_name</db>
    <table>table_name</table>
    <where>id=10</where>
    <invalidate_query>SQL_QUERY</invalidate_query>
</postgresql>
</source>

postgres=# CREATE TABLE "public"."test" (
"int_id" SERIAL,
"int_nullable" INT NULL DEFAULT NULL,
"float" FLOAT NOT NULL,
"str" VARCHAR(100) NOT NULL DEFAULT '',
"float_nullable" FLOAT NULL DEFAULT NULL,
PRIMARY KEY (int_id));

CREATE TABLE

postgres=# INSERT INTO test (int_id, str, "float") VALUES (1,'test',2);
INSERT 0 1

postgresql> SELECT * FROM test;
int_id | int_nullable | float | str  | float_nullable
--------+--------------+-------+------+----------------
       1 |              |     2 | test |
(1 row)

يستخدم هذا المثال محرك جدول PostgreSQL لربط جدول ClickHouse بجدول PostgreSQL واستخدام عبارتي SELECT وINSERT على قاعدة بيانات PostgreSQL:

CREATE TABLE default.postgresql_table
(
    `float_nullable` Nullable(Float32),
    `str` String,
    `int_id` Int32
)
ENGINE = PostgreSQL('localhost:5432', 'public', 'test', 'postgres_user', 'postgres_password');

تقوم دالة الجدول postgresql بنسخ البيانات من PostgreSQL إلى ClickHouse، ويُستخدم ذلك غالبًا لتحسين أداء الاستعلامات على هذه البيانات عبر الاستعلام عنها أو إجراء التحليلات في ClickHouse بدلًا من PostgreSQL، كما يمكن استخدامه أيضًا لترحيل البيانات من PostgreSQL إلى ClickHouse. وبما أننا سننسخ البيانات من PostgreSQL إلى ClickHouse، فسنستخدم محرك جدول MergeTree في ClickHouse ونسميه postgresql_copy:

CREATE TABLE default.postgresql_copy
(
    `float_nullable` Nullable(Float32),
    `str` String,
    `int_id` Int32
)
ENGINE = MergeTree
ORDER BY (int_id);

INSERT INTO default.postgresql_copy
SELECT * FROM postgresql('localhost:5432', 'public', 'test', 'postgres_user', 'postgres_password');

إذا كنت ستُجري بعد الإدراج الأولي مزامنةً مستمرة بين جدول PostgreSQL وجدول ClickHouse، فيمكنك استخدام عبارة WHERE في ClickHouse لإدراج البيانات التي أُضيفت إلى PostgreSQL فقط استنادًا إلى طابع زمني أو معرّف تسلسلي فريد.

ويتطلّب ذلك تتبّع الحد الأقصى للمعرّف أو الطابع الزمني الذي أُضيف سابقًا، كما في المثال التالي:

SELECT max(`int_id`) AS maxIntID FROM default.postgresql_copy;

ثم إدراج القيم من جدول PostgreSQL التي تتجاوز الحد الأقصى

INSERT INTO default.postgresql_copy
SELECT * FROM postgresql('localhost:5432', 'public', 'test', 'postgres_user', 'postgres_password')
WHERE int_id > (SELECT max(int_id) FROM default.postgresql_copy);

SELECT * FROM postgresql_copy WHERE str IN ('test');

┌─float_nullable─┬─str──┬─int_id─┐
│           ᴺᵁᴸᴸ │ test │      1 │
└────────────────┴──────┴────────┘

postgres=# CREATE SCHEMA "nice.schema";

postgres=# CREATE TABLE "nice.schema"."nice.table" (a integer);

postgres=# INSERT INTO "nice.schema"."nice.table" SELECT i FROM generate_series(0, 99) as t(i)

CREATE TABLE pg_table_schema_with_dots (a UInt32)
        ENGINE PostgreSQL('localhost:5432', 'clickhouse', 'nice.table', 'postgrsql_user', 'password', 'nice.schema');

انظر أيضًا

• دالة الجدول postgresql
• استخدام PostgreSQL كمصدر للقاموس

• مدونة: ClickHouse و PostgreSQL - ثنائي مثالي في عالم البيانات - الجزء 1
• مدونة: ClickHouse و PostgreSQL - ثنائي مثالي في عالم البيانات - الجزء 2