Hologres:CREATE TABLE

Lihat skema tabel

Jalankan kueri berikut untuk mengambil pernyataan Data Definition Language (DDL) tabel:

SELECT hg_dump_script('public.dwd_trade_orders');

Masukkan data

Hologres kompatibel dengan sintaks standar Data Manipulation Language (DML). Pernyataan berikut memasukkan 10 baris data sampel:

INSERT INTO public.dwd_trade_orders
    (order_id, shop_id, user_id, order_amount, payment, payment_type, is_delivered, dt, order_time)
VALUES
    (50001, 101, 'U678', 299.00, 280.00, 1, true,  '20231101', '2023-11-01 10:00:01+08'),
    (50002, 102, 'U992',  59.00,  59.00, 2, false, '20231101', '2023-11-01 10:05:12+08'),
    (50003, 101, 'U441', 150.00, 145.00, 1, true,  '20231101', '2023-11-01 10:10:45+08'),
    (50004, 105, 'U219', 888.00, 888.00, 3, true,  '20231101', '2023-11-01 10:20:11+08'),
    (50005, 102, 'U883',  35.00,  30.00, 1, false, '20231101', '2023-11-01 10:32:00+08'),
    (50006, 110, 'U007', 120.50, 120.50, 2, true,  '20231101', '2023-11-01 10:45:33+08'),
    (50007, 101, 'U321', 210.00, 210.00, 1, true,  '20231101', '2023-11-01 11:02:19+08'),
    (50008, 108, 'U556',  45.00,  45.00, 2, false, '20231101', '2023-11-01 11:15:04+08'),
    (50009, 101, 'U112', 300.00, 290.00, 3, true,  '20231101', '2023-11-01 11:25:55+08'),
    (50010, 105, 'U449',  99.90,  99.90, 1, true,  '20231101', '2023-11-01 11:40:22+08');

Kueri data

-- Hitung total jumlah transaksi per toko, diurutkan secara menurun.
SELECT
    shop_id,
    COUNT(1)     AS total_orders,
    SUM(payment) AS total_payment
FROM public.dwd_trade_orders
GROUP BY shop_id
ORDER BY total_payment DESC;

Output yang diharapkan:

shop_id  total_orders  total_payment
105      2             987.90
101      4             925.00
110      1             120.50
102      1              59.00
108      1              45.00

Sintaks CREATE TABLE

Hologres mendukung dua sintaks untuk mendefinisikan properti dan komentar tabel:

Sintaks standar (direkomendasikan untuk Hologres V2.1 dan versi lebih baru)

Gunakan kata kunci WITH untuk mendefinisikan properti secara inline. Sintaks ini lebih ringkas dan menawarkan kinerja lebih baik.

BEGIN;

CREATE TABLE [IF NOT EXISTS] [schema_name.]table_name (
    {
        column_name column_type [column_constraints, [...]]
        | table_constraints
        [,...]
    }
)
[WITH (
    property = 'value'
    [, ...]
)];

[COMMENT ON COLUMN <[schema_name.]tablename.column> IS '<value>';]
[COMMENT ON TABLE  <[schema_name.]tablename>        IS '<value>';]

COMMIT;

Sintaks kompatibel (didukung di semua versi)

Gunakan CALL set_table_property untuk mengatur properti dan COMMENT untuk menambahkan komentar. Semua pernyataan harus berada dalam blok transaksi BEGIN...COMMIT yang sama dengan CREATE TABLE.

BEGIN;

CREATE TABLE [IF NOT EXISTS] [schema_name.]table_name (
    {
        column_name column_type [column_constraints, [...]]
        | table_constraints
        [,...]
    }
);

CALL set_table_property('[schema_name.]<table_name>', '<property>', '<value>');
COMMENT ON COLUMN <[schema_name.]tablename.column> IS '<value>';
COMMENT ON TABLE  <[schema_name.]tablename>        IS '<value>';

COMMIT;

Properti tabel

Properti dikelompokkan menjadi tiga kategori berdasarkan fungsinya. Properti yang ditandai tidak dapat dimodifikasi tidak dapat diubah setelah tabel dibuat—Anda harus membuat ulang tabel tersebut.

CALL set_table_property('table_name', 'dictionary_encoding_columns', '[columnName{:[on|off|auto]}[,...]]');

CALL set_table_property('table_name', 'time_to_live_in_seconds', '<non_negative_literal>');

-- Atur mode penyimpanan saat pembuatan tabel:
CREATE TABLE <table_name> (...) WITH (storage_mode = 'hot');
CREATE TABLE <table_name> (...) WITH (storage_mode = 'cold');

-- Atur mode penyimpanan setelah pembuatan tabel:
CALL set_table_property('table_name', 'storage_mode', 'hot');
CALL set_table_property('table_name', 'storage_mode', 'cold');

Contoh: Tabel partisi untuk data time-series berskala besar

Saat volume data meningkat, tabel flat tunggal menjadi mahal untuk dipelihara—membersihkan data historis memerlukan pemindaian semua baris, dan kueri berbasis waktu melakukan pemindaian tabel penuh. Tabel partisi secara fisik mengisolasi data berdasarkan hari, memungkinkan penghapusan partisi instan untuk pembersihan dan pemangkasan partisi otomatis selama kueri.

Contoh ini meningkatkan tabel dwd_trade_orders dari bagian Panduan cepat ke struktur partisi. Definisi bidang dan pengaturan indeks diwariskan, tetapi kunci primer harus mencakup kunci partisi dt.

BEGIN;

-- Buat tabel induk partisi.
-- Kunci primer mencakup kunci bisnis (order_id) dan kunci partisi (dt).
CREATE TABLE IF NOT EXISTS public.dwd_trade_orders_partitioned (
    order_id      BIGINT NOT NULL,
    shop_id       INT NOT NULL,
    user_id       TEXT NOT NULL,
    order_amount  NUMERIC(12, 2) DEFAULT 0.00,
    payment       NUMERIC(12, 2) DEFAULT 0.00,
    payment_type  INT DEFAULT 0,
    is_delivered  BOOLEAN DEFAULT false,
    dt            TEXT NOT NULL,
    order_time    TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (order_id, dt)
)
PARTITION BY LIST (dt)
WITH (
    orientation       = 'column',
    distribution_key  = 'order_id',
    event_time_column = 'order_time',
    clustering_key    = 'order_time:asc'
);

COMMIT;

-- Buat tabel anak untuk menyediakan penyimpanan fisik untuk tanggal tertentu.
CREATE TABLE IF NOT EXISTS public.dwd_trade_orders_20231101
    PARTITION OF public.dwd_trade_orders_partitioned FOR VALUES IN ('20231101');

-- Masukkan data. Logika aplikasi identik dengan tabel flat.
-- Data secara otomatis diarahkan ke tabel anak yang sesuai.
INSERT INTO public.dwd_trade_orders_partitioned
    (order_id, shop_id, user_id, order_amount, payment, payment_type, is_delivered, dt, order_time)
VALUES
    (50001, 101, 'U678', 299.00, 280.00, 1, true,  '20231101', '2023-11-01 10:00:01+08'),
    (50002, 102, 'U992',  59.00,  59.00, 2, false, '20231101', '2023-11-01 10:05:12+08'),
    (50003, 101, 'U441', 150.00, 145.00, 1, true,  '20231101', '2023-11-01 10:10:45+08'),
    (50004, 105, 'U219', 888.00, 888.00, 3, true,  '20231101', '2023-11-01 10:20:11+08'),
    (50005, 102, 'U883',  35.00,  30.00, 1, false, '20231101', '2023-11-01 10:32:00+08'),
    (50006, 110, 'U007', 120.50, 120.50, 2, true,  '20231101', '2023-11-01 10:45:33+08'),
    (50007, 101, 'U321', 210.00, 210.00, 1, true,  '20231101', '2023-11-01 11:02:19+08'),
    (50008, 108, 'U556',  45.00,  45.00, 2, false, '20231101', '2023-11-01 11:15:04+08'),
    (50009, 101, 'U112', 300.00, 290.00, 3, true,  '20231101', '2023-11-01 11:25:55+08'),
    (50010, 105, 'U449',  99.90,  99.90, 1, true,  '20231101', '2023-11-01 11:40:22+08');

-- Kueri dengan filter partisi. Hologres hanya memindai tabel anak yang sesuai.
SELECT COUNT(*) FROM public.dwd_trade_orders_partitioned WHERE dt = '20231101';

-- Hapus tabel anak untuk mereklaim ruang dalam hitungan detik (jauh lebih cepat daripada DELETE).
-- DROP TABLE public.dwd_trade_orders_20231101;

Contoh: Analitik real-time untuk dataset besar (tabel fakta)

Skenario: Ringkasan dashboard real-time dengan volume data besar, di mana kebutuhan utamanya adalah agregasi cepat—misalnya, menghitung Gross Merchandise Volume (GMV) dan jumlah pesanan.

Penyimpanan berorientasi kolom unggul di sini: rasio kompresi tinggi mengurangi I/O, dan pemindaian kolom hanya membaca kolom yang diperlukan untuk agregasi.

BEGIN;

CREATE TABLE IF NOT EXISTS public.dwd_order_summary (
    order_id    BIGINT PRIMARY KEY,
    category_id INT NOT NULL,
    gmv         NUMERIC(15, 2),
    order_time  TIMESTAMPTZ NOT NULL
) WITH (
    orientation       = 'column',           -- Terbaik untuk agregasi skala besar; kompresi tinggi, pemindaian kolom efisien
    distribution_key  = 'order_id',         -- Distribusi merata; memungkinkan join lokal dengan tabel pesanan lain
    event_time_column = 'order_time',       -- Memungkinkan pemotongan segmen tingkat file untuk filter rentang waktu
    clustering_key    = 'order_time:asc'    -- Mengurangi I/O disk untuk kueri "jam terakhir" atau "hari tertentu"
);

COMMENT ON TABLE  public.dwd_order_summary            IS 'Tabel fakta untuk detail ringkasan pesanan.';
COMMENT ON COLUMN public.dwd_order_summary.order_id   IS 'ID pesanan unik.';
COMMENT ON COLUMN public.dwd_order_summary.category_id IS 'ID kategori.';
COMMENT ON COLUMN public.dwd_order_summary.gmv        IS 'Gross merchandise volume.';
COMMENT ON COLUMN public.dwd_order_summary.order_time IS 'Waktu saat pesanan dilakukan.';

COMMIT;

Contoh: Kueri titik konkurensi tinggi (tabel dimensi)

Skenario: Ambil profil pengguna berdasarkan user_id dalam milidetik, di bawah Queries Per Second (QPS) tinggi.

Penyimpanan berorientasi baris dioptimalkan untuk pencarian berbasis kunci primer. Semua data baris disimpan secara berurutan, sehingga kueri titik sangat cepat tanpa memindai kolom yang tidak relevan.

BEGIN;

CREATE TABLE IF NOT EXISTS public.dim_user_persona (
    user_id       TEXT PRIMARY KEY,
    user_level    INT,
    persona_jsonb JSONB
) WITH (
    orientation = 'row'
    -- Untuk tabel berorientasi baris, kunci primer secara otomatis digunakan sebagai kunci distribusi
    -- dan kunci pengelompokan. Tidak diperlukan pengaturan tambahan.
);

COMMENT ON TABLE  public.dim_user_persona              IS 'Tabel dimensi untuk profil pengguna.';
COMMENT ON COLUMN public.dim_user_persona.user_id      IS 'ID pengguna unik.';
COMMENT ON COLUMN public.dim_user_persona.user_level   IS 'Level pengguna.';
COMMENT ON COLUMN public.dim_user_persona.persona_jsonb IS 'Fitur profil pengguna dalam format JSON.';

COMMIT;

Contoh: Beban kerja hibrida (penyimpanan hibrida baris-kolom)

Skenario: Sistem logistik dan purna jual yang membutuhkan analitik (merangkum status logistik) dan kueri titik (mengambil detail pesanan berdasarkan order_id).

Penyimpanan hibrida baris-kolom menggabungkan kinerja kueri titik tingkat milidetik dari penyimpanan berorientasi baris dengan agregasi efisien dari penyimpanan berorientasi kolom.

BEGIN;

CREATE TABLE IF NOT EXISTS public.ads_shipping_info (
    order_id         BIGINT PRIMARY KEY,
    shipping_status  INT,
    receiver_address TEXT,
    update_time      TIMESTAMPTZ
) WITH (
    orientation      = 'row,column',      -- Mendukung kueri titik dan agregasi
    distribution_key = 'order_id',        -- Mengontrol distribusi data di seluruh shard
    bitmap_columns   = 'shipping_status'  -- Mempercepat kueri filter "status = X" pada kolom dengan kardinalitas rendah
);

COMMENT ON TABLE  public.ads_shipping_info                  IS 'Tabel aplikasi untuk kueri status logistik.';
COMMENT ON COLUMN public.ads_shipping_info.order_id         IS 'ID pesanan.';
COMMENT ON COLUMN public.ads_shipping_info.shipping_status  IS 'Status logistik (1: Akan dikirim, 2: Dalam perjalanan, 3: Terkirim).';
COMMENT ON COLUMN public.ads_shipping_info.receiver_address IS 'Alamat pengiriman.';

COMMIT;

Batasan kunci primer

• Composite primary keys: Beberapa bidang dapat membentuk kunci primer. Semua bidang harus NOT NULL dan harus dideklarasikan dalam satu pernyataan.

  BEGIN;
  CREATE TABLE public.test (
      id TEXT NOT NULL,
      ds TEXT NOT NULL,
      PRIMARY KEY (id, ds)
  );
  CALL SET_TABLE_PROPERTY('public.test', 'orientation', 'column');
  COMMIT;

• Tipe yang tidak didukung: FLOAT, DOUBLE, NUMERIC, ARRAY, JSON, DATE, dan tipe kompleks lainnya tidak dapat digunakan sebagai kolom kunci primer.

• Tidak dapat dimodifikasi: Kunci primer tidak dapat diubah setelah pembuatan tabel. Buat ulang tabel jika Anda memerlukan kunci primer yang berbeda.

• Persyaratan penyimpanan: Tabel berorientasi baris dan hibrida baris-kolom harus memiliki kunci primer. Kunci primer opsional untuk tabel berorientasi kolom.

Dukungan kendala

Aturan penamaan dan escaping

• Nama kolom tidak boleh diawali dengan hg_.

• Nama skema tidak boleh diawali dengan holo_, hg_, atau pg_.

• Nama tabel tidak boleh melebihi 127 byte.

• Apit nama dengan tanda kutip ganda ("") ketika nama tersebut merupakan kata kunci SQL, kata tercadang, bidang sistem (seperti ctid), pengidentifikasi case-sensitive, nama dengan karakter khusus, atau nama yang diawali angka.

Sintaks untuk nama kolom escaped di Hologres V2.0 dan versi lebih baru:

-- Kolom escaped tunggal
BEGIN;
CREATE TABLE tbl (c1 INT NOT NULL);
CALL set_table_property('tbl', 'clustering_key', '"c1":asc');
COMMIT;

-- Beberapa kolom, termasuk yang huruf kapital (V2.1 dan versi lebih baru)
BEGIN;
CREATE TABLE tbl ("C1" INT NOT NULL, c2 TEXT NOT NULL) WITH (clustering_key = '"C1",c2');
COMMIT;

-- Beberapa kolom, termasuk yang huruf kapital (V2.0 dan versi lebih baru)
BEGIN;
CREATE TABLE tbl ("C1" INT NOT NULL, c2 TEXT NOT NULL);
CALL set_table_property('tbl', 'clustering_key', '"C1",c2');
COMMIT;

Sintaks untuk nama kolom escaped di versi sebelum Hologres V2.0:

BEGIN;
CREATE TABLE tbl (c1 INT NOT NULL);
CALL set_table_property('tbl', 'clustering_key', '"c1:asc"');
COMMIT;

-- Beberapa kolom, termasuk yang huruf kapital
BEGIN;
CREATE TABLE tbl ("C1" INT NOT NULL, c2 TEXT NOT NULL);
CALL set_table_property('tbl', 'clustering_key', '"C1,c2"');
COMMIT;

Untuk beralih ke sintaks parser lama di Hologres V2.0 saat diperlukan:

-- Aktifkan sintaks lama di tingkat session.
SET hg_disable_parse_holo_property = on;

-- Aktifkan sintaks lama di tingkat database.
ALTER DATABASE <db_name> SET hg_disable_parse_holo_property = on;

Perilaku IF NOT EXISTS

Batasan modifikasi

Setelah tabel dibuat, hal-hal berikut tidak dapat diubah:

• Tipe data (sebelum Hologres V3.0)

• Urutan kolom

• Kendala nullability (NOT NULL ↔ nullable)

• Properti tata letak penyimpanan: orientation, distribution_key, clustering_key, event_time_column

Hal-hal berikut dapat diubah setelah pembuatan tabel:

• bitmap_columns dan dictionary_encoding_columns — melalui ALTER TABLE

• Tipe data: beberapa tipe di V3.0 dan versi lebih baru; semua tipe melalui REBUILD di V3.1 dan versi lebih baru — lihat Ubah tipe data dan REBUILD