Menetapkan Kolom Event Time untuk Tabel di Hologres - Hologres

Untuk dataset berukuran besar yang sering menjalankan operasi UPDATE berdasarkan primary key atau kueri dengan kondisi range filter, pertimbangkan untuk menetapkan event time column (segment key). Sistem mengurutkan file data berdasarkan rentang event time column sebelum menggabungkannya, sehingga mengurangi tumpang tindih antarfile. Hal ini memungkinkan mesin kueri memangkas lebih banyak file dan meningkatkan efisiensi kueri. Konfigurasi event_time_column yang tepat dapat meningkatkan kecepatan kueri dan kinerja secara keseluruhan. Topik ini menjelaskan cara menetapkan properti event_time_column untuk tabel di Hologres.

Kolom event time

Properti event_time_column sebelumnya dikenal sebagai segment key. Mulai Hologres V0.9 dan versi selanjutnya, nama properti bawaannya adalah event_time_column, sedangkan segment key tetap dipertahankan demi kompatibilitas mundur.

Event time column terutama digunakan dalam skenario berikut:

Kueri yang mencakup range filter atau equality condition.
Operasi UPDATE berdasarkan primary key.

Untuk menetapkan properti event_time_column, Anda harus menentukannya saat membuat tabel. Gunakan sintaks berikut:

-- Sintaks yang didukung di Hologres V2.1 dan versi selanjutnya
CREATE TABLE <table_name> (...) WITH (event_time_column = '[<columnName>[,...]]');
-- Sintaks yang didukung di semua versi
BEGIN;
CREATE TABLE <table_name> (...);
call set_table_property('<table_name>', 'event_time_column', '[<columnName> [,...]]');
COMMIT;

Parameter:

Parameter	Deskripsi
table_name	Nama tabel.
columnName	Kolom yang akan digunakan sebagai `event time column`.

Rekomendasi

Event time column paling cocok untuk kolom dengan nilai yang bersifat monoton naik atau turun, seperti kolom timestamp. Kolom ini ideal untuk data berbasis waktu, misalnya log dan data traffic, karena konfigurasi yang tepat dapat meningkatkan kinerja secara signifikan. Jika event time column sama sekali tidak terurut, file hasil penggabungan tidak akan memiliki rentang nilai yang berbeda, sehingga manfaat pemangkasan file menjadi hilang.
Jika tabel tidak memiliki kolom yang jelas bersifat monoton naik atau turun, pertimbangkan untuk menambahkan kolom seperti update_time dan menulis waktu saat ini ke kolom tersebut untuk setiap operasi UPSERT.
Event time column mengikuti leftmost matching principle. Menetapkan beberapa kolom dapat membatasi pola kueri yang dipercepat. Oleh karena itu, kami merekomendasikan untuk menentukan tidak lebih dari dua kolom dalam kebanyakan kasus.

Batasan

Event time column harus berupa satu kolom atau kombinasi kolom yang didefinisikan sebagai NOT NULL. Anda boleh menghilangkan properti event_time_column, tetapi tidak boleh menetapkannya ke kolom yang mengizinkan nilai null. Di Hologres V1.3.20 hingga V1.3.27, kolom nullable sempat didukung untuk properti event_time_column. Mulai Hologres V1.3.28, dukungan ini dihentikan karena event time column yang nullable dapat mengompromikan integritas data. Jika Anda memiliki kebutuhan bisnis yang kuat untuk menetapkan kolom nullable sebagai event time column, Anda dapat menambahkan pernyataan berikut sebelum pernyataan SQL Anda:

set hg_experimental_enable_nullable_segment_key = true;

Anda dapat menggunakan pernyataan SQL berikut untuk memeriksa apakah database saat ini berisi event time column (atau segment key) yang nullable:

WITH t_base AS (
    SELECT
        *
    FROM
        hologres.hg_table_info
    WHERE
        collect_time::date = CURRENT_DATE
),
t1 AS (
    SELECT
        db_name,
        schema_name,
        table_name,
        jsonb_array_elements(table_meta::jsonb -> 'columns') cols
    FROM
        t_base
),
t2 AS (
    SELECT
        db_name,
        schema_name,
        table_name,
        cols ->> 'name' col_name
    FROM
        t1
    WHERE
        cols -> 'nullable' = 'true'::jsonb
),
t3 AS (
    SELECT
        db_name,
        schema_name,
        table_name,
        regexp_replace(regexp_split_to_table(table_meta::jsonb ->> 'segment_key', ','), ':asc|:desc$', '') segment_key_col
    FROM
        t_base
    WHERE
        table_meta::jsonb -> 'segment_key' IS NOT NULL
)
SELECT
    CURRENT_DATE,
    t3.db_name,
    t3.schema_name,
    t3.table_name,
    jsonb_build_object('nullable_segment_key_column', string_agg(t3.segment_key_col, ',')) as nullable_segment_key_column
FROM
    t2,
    t3
WHERE
    t3.db_name = t2.db_name
    AND t3.schema_name = t2.schema_name
    AND t3.table_name = t2.table_name
    AND t2.col_name = t3.segment_key_col
GROUP BY
    t3.db_name,
    t3.schema_name,
    t3.table_name;

Anda tidak dapat mengubah properti event_time_column setelah tabel dibuat. Untuk mengubahnya, Anda harus membuat ulang tabel tersebut.
Anda tidak dapat menetapkan event time column untuk row-oriented table.
Untuk column-oriented table, jika Anda tidak secara eksplisit menetapkan event time column, Hologres secara bawaan menggunakan kolom pertama bertipe data TIMESTAMP atau TIMESTAMPTZ yang didefinisikan sebagai NOT NULL sebagai event time column. Jika tidak ada kolom semacam itu, kolom pertama bertipe data DATE yang didefinisikan sebagai NOT NULL akan digunakan. Di versi sebelum Hologres V0.9, tidak ada event time column bawaan yang ditetapkan.
Anda tidak dapat menggunakan tipe data kompleks seperti DECIMAL, NUMERIC, FLOAT, DOUBLE, ARRAY, JSON, JSONB, BIT, MONEY, atau tipe data kompleks lainnya untuk event time column.

Cara kerja

Gambar berikut menunjukkan proses penulisan data dalam satu shard: 分段键技术原理

Dalam satu shard, data pertama kali ditulis ke in-memory table dalam mode append-only untuk memaksimalkan kinerja penulisan. In-memory table memiliki ukuran tetap. Saat in-memory table penuh, sistem secara asinkron menyimpan datanya ke file.
Untuk mengoptimalkan kinerja penulisan, data ditulis dalam mode append-only, yang menyebabkan jumlah file meningkat seiring waktu. Sistem secara berkala menggabungkan file-file ini di latar belakang. Jika Anda menetapkan event time column (atau segment key), sistem mengurutkan file berdasarkan rentang event time column, lalu menggabungkan file-file dengan rentang yang berdekatan. Proses ini mengurangi tumpang tindih antarfile, sehingga pengoptimal kueri dapat memangkas lebih banyak file dan meningkatkan efisiensi kueri.
File diurutkan berdasarkan event time column. Oleh karena itu, event time column juga mengikuti leftmost matching principle. Misalnya, jika Anda menetapkan kolom a, b, dan c sebagai event time column, kueri dapat memperoleh manfaat dari pengaturan ini jika kondisi filter-nya berada pada a,b,c atau a,b. Jika kondisi filter berada pada a,c, hanya kolom a yang mendapat manfaat. Jika kondisi filter berada pada b,c, kueri tidak dapat memperoleh manfaat dari event time column.

Seperti dijelaskan, event time column dapat mempercepat skenario berikut:

Kueri yang mencakup range filter atau equality condition.

Jika kolom yang dikueri ditetapkan sebagai event time column, Hologres membandingkan kondisi rentang dalam kueri dengan statistik tingkat file (min/maks) kolom tersebut. Hal ini memungkinkan Hologres untuk dengan cepat memangkas file yang tidak relevan dan mempercepat kueri.
Operasi UPDATE berdasarkan primary key.

Perintah UPDATE di Hologres diimplementasikan sebagai kombinasi perintah DELETE dan INSERT. Dalam skenario yang melibatkan UPDATE berbasis primary key atau INSERT ON CONFLICT (UPSERT), primary key pertama-tama digunakan untuk menemukan segment key dari data lama di tabel target. Segment key ini kemudian digunakan untuk menemukan file yang berisi data lama, lalu data tersebut akhirnya ditandai sebagai DELETE. Jika segment key dikonfigurasi dengan tepat, file yang berisi data lama dapat ditemukan dengan cepat, sehingga meningkatkan kinerja penulisan. Sebaliknya, jika tabel berorientasi kolom tidak memiliki segment key yang dikonfigurasi, segment key dikonfigurasi dengan bidang yang tidak sesuai, atau bidang segment key tidak memiliki korelasi kuat dengan waktu selama penulisan data (misalnya, data sebagian besar tidak terurut), sejumlah besar file harus dipindai untuk menemukan data lama. Proses ini tidak hanya menyebabkan banyak operasi I/O, tetapi juga menghabiskan sumber daya CPU yang signifikan, yang berdampak negatif pada kinerja penulisan dan beban seluruh instans.

Contoh

Buat satu event time column saat membuat tabel.

Sintaks yang didukung di Hologres V2.1 dan versi selanjutnya:

CREATE TABLE tbl_segment_test (
    a int NOT NULL,
    b timestamptz NOT NULL
)
WITH (
    event_time_column = 'b'
);
INSERT INTO tbl_segment_test values
(1,'2022-09-05 10:23:54+08'),
(2,'2022-09-05 10:24:54+08'),
(3,'2022-09-05 10:25:54+08'),
(4,'2022-09-05 10:26:54+08');
EXPLAIN SELECT * FROM tbl_segment_test WHERE b > '2022-09-05 10:24:54+08';

Sintaks yang didukung di semua versi:

BEGIN;
CREATE TABLE tbl_segment_test (
    a int NOT NULL,
    b timestamptz NOT NULL
    );
CALL set_table_property('tbl_segment_test', 'event_time_column', 'b');
COMMIT;
INSERT INTO tbl_segment_test VALUES 
(1,'2022-09-05 10:23:54+08'),
(2,'2022-09-05 10:24:54+08'),
(3,'2022-09-05 10:25:54+08'),
(4,'2022-09-05 10:26:54+08');
EXPLAIN SELECT * FROM tbl_segment_test WHERE b > '2022-09-05 10:24:54+08';

Anda dapat memeriksa execution plan dengan menjalankan pernyataan EXPLAIN. Jika Segment Filter muncul dalam rencana tersebut, berarti kueri menggunakan event time column. Rencana berikut mencakup Segment Filter, yang menunjukkan bahwa event time column aktif.

QUERY PLAN
Gather  (cost=0.00..1.10 rows=1 width=12)
  -> Exchange (Gather Exchange)  (cost=0.00..1.10 rows=1 width=12)
    -> Decode  (cost=0.00..1.10 rows=1 width=12)
      -> Index Scan using holo_index:[1] on tbl_segment_test  (cost=0.00..1.00 rows=1 width=12)
            Segment Filter: (b > '2022-09-05 10:24:54+08'::timestamp with time zone)
Optimizer: HQO version 1.3.0

Buat beberapa event time column saat membuat tabel.

Sintaks yang didukung di Hologres V2.1 dan versi selanjutnya:

CREATE TABLE tbl_segment_test_2 (
    a int NOT NULL,
    b timestamptz NOT NULL
)
WITH (
    event_time_column = 'a,b'
);
INSERT INTO tbl_segment_test_2 VALUES 
(1,'2022-09-05 10:23:54+08'),
(2,'2022-09-05 10:24:54+08'),
(3,'2022-09-05 10:25:54+08'),
(4,'2022-09-05 10:26:54+08')
;
-- Kueri tidak mengenai event time column.
SELECT * FROM tbl_segment_test_2 WHERE b > '2022-09-05 10:24:54+08';
-- Kueri mengenai event time column.
SELECT * FROM tbl_segment_test_2 WHERE a = 3 and b > '2022-09-05 10:24:54+08';
SELECT * FROM tbl_segment_test_2 WHERE a > 3 and b < '2022-09-05 10:26:54+08';
SELECT * FROM tbl_segment_test_2 WHERE a > 3 and b > '2022-09-05 10:24:54+08';

Sintaks yang didukung di semua versi:

BEGIN;
CREATE TABLE tbl_segment_test_2 (
    a int NOT NULL,
    b timestamptz NOT NULL
    );
CALL set_table_property('tbl_segment_test_2', 'event_time_column', 'a,b');
COMMIT;
INSERT INTO tbl_segment_test_2 VALUES 
(1,'2022-09-05 10:23:54+08'),
(2,'2022-09-05 10:24:54+08'),
(3,'2022-09-05 10:25:54+08'),
(4,'2022-09-05 10:26:54+08')
;
-- Kueri tidak mengenai event time column.
SELECT * FROM tbl_segment_test_2 WHERE b > '2022-09-05 10:24:54+08';
-- Kueri mengenai event time column.
SELECT * FROM tbl_segment_test_2 WHERE a = 3 and b > '2022-09-05 10:24:54+08';
SELECT * FROM tbl_segment_test_2 WHERE a > 3 and b < '2022-09-05 10:26:54+08';
SELECT * FROM tbl_segment_test_2 WHERE a > 3 and b > '2022-09-05 10:24:54+08';

Topik terkait

Untuk panduan memilih properti tabel yang tepat sesuai pola kueri Anda, lihat Panduan pembuatan dan penyetelan tabel berdasarkan skenario.
Untuk pernyataan DDL terkait tabel internal Hologres, lihat: