Hologres:Kolom tergenerasi

Skenario

• Mengotomatiskan perhitungan untuk bidang yang diperlukan, sehingga menghilangkan kebutuhan menerapkan logika perhitungan secara manual.

• Menjamin konsistensi data dengan mencegah ketidakkonsistenan akibat kesalahan manusia atau logika kode yang cacat.

• Meningkatkan performa kueri. Untuk kueri berfrekuensi tinggi, membaca dari kolom tergenerasi yang disimpan memiliki efisiensi setara dengan membaca dari kolom biasa.

• Menyederhanakan logika bisnis dengan mengurangi kompleksitas SQL untuk operasi transformasi data umum dan tetap.

Penggunaan kolom tergenerasi yang tepat sesuai kebutuhan bisnis Anda secara signifikan meningkatkan efisiensi pengembangan dan menjamin keandalan data.

Sintaksis

Deklarasikan kolom tergenerasi menggunakan klausa GENERATED ALWAYS AS dan tentukan bahwa kolom tersebut merupakan kolom tergenerasi yang disimpan dengan menambahkan kata kunci STORED.

• Buat tabel yang berisi kolom tergenerasi.

  CREATE TABLE generated_col_t (
      [...,]
      col1 INT,
      col2 INT GENERATED ALWAYS AS (col1 + 1) STORED
  );

• Buat tabel partisi logis yang berisi kolom tergenerasi dan gunakan kolom tergenerasi tersebut sebagai kunci partisi.

  CREATE TABLE generated_col_logical_part (
      a TEXT,
      b INT,
      ts TIMESTAMP NOT NULL,
      d TIMESTAMP GENERATED ALWAYS AS (date_trunc('day', ts)) STORED NOT NULL
  )
  LOGICAL PARTITION BY LIST(d);

Catatan

• Saat menggunakan CREATE TABLE:

  • Hanya fungsi atau ekspresi IMMUTABLE yang didukung saat mendefinisikan kolom tergenerasi. Fungsi non-IMMUTABLE seperti CURRENT_DATE dan RANDOM tidak didukung.

  • Saat mendefinisikan kolom tergenerasi, ekspresinya tidak boleh mereferensikan kolom tergenerasi lain, dan Anda tidak dapat menetapkan kata kunci default untuknya.

  • Saat membuat tabel partisi, Anda dapat menetapkan kunci partisi tabel partisi logis ke kolom tergenerasi. Namun, Anda tidak dapat menetapkan kunci partisi tabel partisi fisik ke kolom tergenerasi. Kolom biasa apa pun dalam tabel partisi tetap dapat menjadi kolom tergenerasi.

  • Anda tidak dapat mendefinisikan kolom tergenerasi saat membuat tabel eksternal menggunakan CREATE FOREIGN TABLE.

  • Anda dapat mengonfigurasi kolom tergenerasi sebagai berbagai jenis indeks Hologres, termasuk primary key, distribution key, segment key, cluster index, bitmap index, dan kolom dengan dictionary encoding.

• Saat menggunakan ALTER TABLE:

  • Kolom tergenerasi tidak mendukung operasi ADD.

  • Anda dapat menghapus kolom tergenerasi, tetapi sebelum menghapusnya, pastikan semua kolom yang direferensikannya tetap ada.

  • Anda tidak dapat memodifikasi tipe data kolom tergenerasi atau kolom yang direferensikannya. Untuk melakukannya, gunakan fitur REBUILD. Untuk informasi selengkapnya, lihat REBUILD.

  • Anda dapat mengganti nama kolom tergenerasi.

• Saat menggunakan DML atau DQL:

  • Saat menulis atau memperbarui data di tabel yang memiliki kolom tergenerasi, Anda dapat mengabaikan kolom tersebut atau menggunakan kata kunci default. Nilai pada kolom tergenerasi tidak dapat ditetapkan secara langsung.

  • Saat memperbarui data, Anda tidak dapat memperbarui kolom tergenerasi atau kolom yang direferensikannya jika salah satunya dikonfigurasi sebagai distribution key.

  • Jika primary key tabel mencakup kolom tergenerasi, maka primary key tersebut juga harus mencakup semua kolom yang direferensikan oleh kolom tergenerasi tersebut agar mendukung pembaruan data menggunakan Fixed Plan.

  • Saat melakukan pembaruan kolom parsial menggunakan Fixed Plan, jika kolom tergenerasi mereferensikan beberapa kolom biasa, Anda tidak dapat memperbarui hanya sebagian dari kolom yang direferensikan tersebut.

  • Operasi lain mendukung tabel dengan kolom tergenerasi, termasuk operasi baca dan tulis yang dieksekusi oleh mesin HQE, operasi baca dan tulis yang dieksekusi oleh Fixed Plan, serta operasi Copy.

• Operasi lainnya:

  • Anda dapat menggunakan CREATE TABLE LIKE dengan tabel sumber yang berisi kolom tergenerasi. Namun, untuk mempertahankan properti kolom tergenerasi tersebut, Anda harus mengaktifkan parameter hg_experimental_enable_create_table_like_properties.

  • Saat menggunakan CREATE TABLE AS, tabel sumber dengan kolom tergenerasi tidak didukung.

  • Untuk memodifikasi parameter tabel yang berisi kolom tergenerasi, gunakan sintaksis REBUILD. Ini mencakup migrasi tabel ke kelompok tabel lain. Untuk informasi selengkapnya, lihat REBUILD. Sintaksis HG_MOVE_TABLE_TO_TABLE_GROUP tidak didukung untuk migrasi kelompok tabel.

  • Untuk melakukan operasi INSERT OVERWRITE pada tabel dengan kolom tergenerasi, Anda harus menggunakan sintaksis INSERT OVERWRITE asli yang didukung di Hologres V3.1. Sintaksis lama hg_insert_overwrite tidak lagi didukung. Untuk informasi selengkapnya mengenai sintaksis tersebut, lihat INSERT OVERWRITE.

Contoh

1. Buat tabel dengan kolom tergenerasi.

   CREATE TABLE generated_col_t (
       id INT PRIMARY KEY,
       col1 INT,
       col2 INT GENERATED ALWAYS AS (col1 + 1) STORED
   );

2. Impor data.

   • Anda dapat mengimpor data ke semua kolom non-tergenerasi. Contoh berikut menunjukkan caranya:

     INSERT INTO generated_col_t VALUES (1, 1);
     INSERT INTO generated_col_t(id, col1) VALUES (2, 2);

     Kueri SELECT * FROM generated_col_t; menghasilkan output berikut.

     id	col1	col2
     1	1	2
     2	2	3

   • Anda dapat menggunakan kata kunci default untuk kolom tergenerasi saat mengimpor data. Contoh berikut menunjukkan cara penggunaannya:

     INSERT INTO generated_col_t VALUES (3, 3, default);
     INSERT INTO generated_col_t(id, col1, col2) VALUES (4, 4, default);

     Kueri SELECT * FROM generated_col_t; menghasilkan output berikut.

     id	col1	col2
     4	4	5
     2	2	3
     3	3	4
     1	1	2

   • Anda tidak dapat mengimpor data dengan menentukan nilai untuk kolom tergenerasi. Contoh berikut menunjukkan hal ini:

     INSERT INTO generated_col_t VALUES (5, 5, 6);
     INSERT INTO generated_col_t(id, col1, col2) VALUES (6, 6, 7);

     Hasil berikut dikembalikan.

     

3. Perbarui data.

   • Anda dapat memperbarui data di kolom non-tergenerasi. Contoh berikut menunjukkan caranya:

     UPDATE generated_col_t SET col1 = 2 WHERE id = 1;

     Kueri SELECT * FROM generated_col_t; menghasilkan output berikut.

     id	col1	col2
     2	2	3
     3	3	4
     4	4	5
     1	2	3     -- Data di kolom ini telah berubah

   • Anda dapat menggunakan kata kunci default untuk kolom tergenerasi saat memperbarui data. Contoh berikut menunjukkan cara penggunaannya:

     UPDATE generated_col_t SET col1 = 3, col2 = default WHERE id = 2;

     Kueri SELECT * FROM generated_col_t; menghasilkan keluaran berikut.

     id	col1	col2
     3	3	4
     2	3	4    -- Data di kolom ini telah berubah
     4	4	5
     1	2	3

   • Anda tidak dapat memperbarui data dengan menentukan nilai untuk kolom tergenerasi. Contoh berikut menunjukkan hal ini:

     UPDATE generated_col_t SET col2 = 4 WHERE id = 3;

     Hasil berikut dikembalikan.

     

Anda dapat menggunakan kueri SQL berikut untuk memverifikasi apakah suatu fungsi bersifat IMMUTABLE untuk tipe parameter tertentu. Sebagai contoh, fungsi TO_CHAR bersifat IMMUTABLE hanya jika input-nya bertipe TIMESTAMP WITH TIME ZONE. Oleh karena itu, pastikan tipe parameter sesuai saat menggunakan fungsi ini dalam kolom tergenerasi.

SELECT n.nspname AS "Schema",
  p.proname AS "Name",
  pg_catalog.pg_get_function_result(p.oid) AS "Result data type",
  pg_catalog.pg_get_function_arguments(p.oid) AS "Argument data types",
 CASE p.prokind
  WHEN 'a' THEN 'agg'
  WHEN 'w' THEN 'window'
  WHEN 'p' THEN 'proc'
  ELSE 'func'
 END AS "Type",
 CASE
  WHEN p.provolatile = 'i' THEN 'immutable'
  WHEN p.provolatile = 's' THEN 'stable'
  WHEN p.provolatile = 'v' THEN 'volatile'
 END AS "Volatility",
 CASE
  WHEN p.proparallel = 'r' THEN 'restricted'
  WHEN p.proparallel = 's' THEN 'safe'
  WHEN p.proparallel = 'u' THEN 'unsafe'
 END AS "Parallel",
 pg_catalog.pg_get_userbyid(p.proowner) AS "Owner",
 CASE WHEN prosecdef THEN 'definer' ELSE 'invoker' END AS "Security",
 pg_catalog.array_to_string(p.proacl, E'\n') AS "Access privileges",
 l.lanname AS "Language",
 p.prosrc AS "Source code",
 pg_catalog.obj_description(p.oid, 'pg_proc') AS "Description"
FROM pg_catalog.pg_proc p
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
     LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang
-- Target function
WHERE p.proname OPERATOR(pg_catalog.~) '^(TO_CHAR)$' COLLATE pg_catalog.default
  AND pg_catalog.pg_function_is_visible(p.oid)
ORDER BY 1, 2, 4;