Membuat dan mengelola kolom yang dibuat

Kolom yang dihasilkan adalah kolom yang selalu dihitung dari kolom lain dalam baris. Kolom ini dapat membuat kueri lebih sederhana, menghemat biaya evaluasi ekspresi pada waktu kueri, dan dapat diindeks atau digunakan sebagai kunci asing. Dalam artikel ini, kami akan menjelaskan cara mengelola jenis kolom ini di database Anda.

Menambahkan kolom yang dihasilkan ke tabel baru

Dalam cuplikan CREATE TABLE berikut, kita membuat tabel untuk menyimpan informasi tentang pengguna. Kita memiliki kolom untuk FirstName dan LastName serta menentukan kolom yang dihasilkan untuk FullName, yang merupakan penggabungan FirstName dan LastName. SQL dalam tanda kurung disebut ekspresi pembuatan.

Kolom yang dihasilkan dapat ditandai sebagai STORED untuk menghemat biaya evaluasi ekspresi pada waktu kueri. Akibatnya, nilai FullName hanya dihitung saat baris baru disisipkan atau saat FirstName atau LastName diperbarui untuk baris yang ada. Nilai yang dihitung disimpan bersama dengan kolom lain dalam tabel.

GoogleSQL 

CREATE TABLE Users (
Id STRING(20) NOT NULL,
FirstName STRING(50),
LastName STRING(50),
Age INT64 NOT NULL,
FullName STRING(100) AS (FirstName || ' ' || LastName) STORED
) PRIMARY KEY (Id);

PostgreSQL 

CREATE TABLE users (
id VARCHAR(20) NOT NULL,
firstname VARCHAR(50),
lastname VARCHAR(50),
age BIGINT NOT NULL,
fullname VARCHAR(100) GENERATED ALWAYS AS (firstname || ' ' || lastname) STORED,
PRIMARY KEY(id)
);

Anda dapat membuat kolom yang dihasilkan dan tidak disimpan dengan menghapus atribut STORED di DDL. Jenis kolom yang dihasilkan ini dievaluasi pada waktu kueri dan dapat mempermudah kueri. Di PostgreSQL, Anda dapat membuat kolom yang dihasilkan dan tidak disimpan menggunakan atribut VIRTUAL.

GoogleSQL 

FullName STRING(MAX) AS (CONCAT(FirstName, " ", LastName))

PostgreSQL 

fullname text GENERATED ALWAYS AS (firstname || ' ' || lastname) VIRTUAL

  • expression dapat berupa ekspresi SQL valid yang dapat ditetapkan ke jenis data kolom dengan batasan berikut.

    • Ekspresi hanya dapat mereferensikan kolom dalam tabel yang sama.

    • Ekspresi tidak boleh berisi subkueri.

    • Ekspresi dengan fungsi non-deterministik seperti PENDING_COMMIT_TIMESTAMP(), CURRENT_DATE(), dan CURRENT_TIMESTAMP() tidak dapat dibuat menjadi kolom yang dihasilkan STORED atau kolom yang dihasilkan yang diindeks.

    • Anda tidak dapat mengubah ekspresi STORED atau kolom yang dihasilkan dan diindeks.

  • Atribut STORED yang mengikuti ekspresi menyimpan hasil ekspresi bersama dengan kolom lain dalam tabel. Pembaruan berikutnya pada kolom yang dirujuk akan menyebabkan Spanner mengevaluasi ulang dan menyimpan ekspresi.

  • Kolom yang dihasilkan yang bukan STORED tidak dapat ditandai sebagai NOT NULL.

  • Penulisan langsung ke kolom yang dihasilkan tidak diizinkan.

  • Opsi kolom allow_commit_timestamp tidak diizinkan pada kolom yang dihasilkan atau kolom apa pun yang mereferensikan kolom yang dihasilkan.

  • Untuk STORED atau kolom yang dihasilkan yang diindeks, Anda tidak dapat mengubah jenis data kolom, atau kolom apa pun yang dirujuk oleh kolom yang dihasilkan.

  • Anda tidak dapat menghapus kolom yang direferensikan oleh kolom yang dihasilkan.

  • Anda dapat menggunakan kolom yang dihasilkan sebagai kunci utama dengan batasan tambahan berikut:

    • Kunci utama yang dihasilkan tidak dapat mereferensikan kolom lain yang dihasilkan.

    • Kunci utama yang dihasilkan dapat mereferensikan, maksimal, satu kolom non-kunci.

    • Kunci utama yang dihasilkan tidak dapat bergantung pada kolom non-kunci dengan klausa DEFAULT.

  • Aturan berikut berlaku saat menggunakan kolom kunci yang dihasilkan:

    • API Baca: Anda harus menentukan kolom kunci sepenuhnya, termasuk kolom kunci yang dihasilkan.
    • Mutation API: Untuk INSERT, INSERT_OR_UPDATE, dan REPLACE, Spanner tidak mengizinkan Anda menentukan kolom kunci yang dihasilkan. Untuk UPDATE, Anda dapat menentukan kolom kunci yang dihasilkan secara opsional. Untuk DELETE, Anda harus menentukan kolom kunci sepenuhnya, termasuk kunci yang dihasilkan.
    • DML: Anda tidak dapat menulis secara eksplisit ke kunci yang dihasilkan dalam pernyataan INSERT atau UPDATE.
    • Kueri: Secara umum, sebaiknya gunakan kolom kunci yang dihasilkan sebagai filter dalam kueri Anda. Secara opsional, jika ekspresi untuk kolom kunci yang dihasilkan hanya menggunakan satu kolom sebagai referensi, kueri dapat menerapkan kondisi kesetaraan (=) atau IN ke kolom yang dirujuk. Untuk mengetahui informasi selengkapnya dan contoh, lihat Membuat kunci unik yang berasal dari kolom nilai.

Kolom yang dihasilkan dapat dikueri seperti kolom lainnya, seperti yang ditunjukkan dalam contoh berikut.

GoogleSQL 

SELECT Id, FullName
FROM Users;

PostgreSQL 

SELECT id, fullname
FROM users;

Kueri yang menggunakan Fullname setara dengan kueri dengan ekspresi yang dihasilkan. Oleh karena itu, kolom yang dihasilkan dapat membuat kueri lebih sederhana.

GoogleSQL 

SELECT Id, ARRAY_TO_STRING([FirstName, LastName], " ") as FullName
FROM Users;

PostgreSQL 

SELECT id, firstname || ' ' || lastname as fullname
FROM users;

Membuat indeks pada kolom yang dibuat

Anda juga dapat mengindeks atau menggunakan kolom yang dihasilkan sebagai kunci asing.

Untuk membantu pencarian pada kolom yang dihasilkan FullName, kita dapat membuat indeks sekunder seperti yang ditunjukkan dalam cuplikan berikut.

GoogleSQL 

CREATE INDEX UsersByFullName ON Users (FullName);

PostgreSQL 

CREATE INDEX UserByFullName ON users (fullname);

Menambahkan kolom yang dihasilkan ke tabel yang ada

Dengan menggunakan pernyataan ALTER TABLE berikut, kita dapat menambahkan kolom yang dihasilkan ke tabel Users untuk membuat dan menyimpan inisial pengguna.

GoogleSQL 

ALTER TABLE Users ADD COLUMN Initials STRING(2)
AS (ARRAY_TO_STRING([SUBSTR(FirstName, 0, 1), SUBSTR(LastName, 0, 1)], "")) STORED;

PostgreSQL 

ALTER TABLE users ADD COLUMN initials VARCHAR(2)
GENERATED ALWAYS AS (SUBSTR(firstname, 0, 1) || SUBSTR(lastname, 0, 1)) STORED;

Jika Anda menambahkan kolom yang dihasilkan dan disimpan ke tabel yang ada, operasi yang berjalan lama untuk mengisi ulang nilai kolom akan dimulai. Selama pengisian ulang, kolom yang dihasilkan dan disimpan tidak dapat dibaca atau dikueri. Status pengisian ulang ditampilkan dalam tabel INFORMATION_SCHEMA.

Membuat indeks parsial menggunakan kolom yang dihasilkan

Bagaimana jika kita hanya ingin membuat kueri pengguna yang berusia di atas 18 tahun? Pemindaian penuh tabel tidak akan efisien, jadi kita menggunakan indeks parsial.

  1. Gunakan pernyataan berikut untuk menambahkan kolom lain yang dihasilkan yang menampilkan usia pengguna jika mereka berusia di atas 18 tahun, dan menampilkan NULL jika tidak.

    GoogleSQL 

    ALTER TABLE Users ADD COLUMN AgeAbove18 INT64
AS (IF(Age > 18, Age, NULL));

    PostgreSQL 

    ALTER TABLE Users ADD COLUMN AgeAbove18 BIGINT
GENERATED ALWAYS AS (nullif( Age , least( 18, Age) )) VIRTUAL;

  2. Buat indeks pada kolom baru ini, dan nonaktifkan pengindeksan nilai NULL dengan kata kunci NULL_FILTERED di GoogleSQL atau predikat IS NOT NULL di PostgreSQL. Indeks parsial ini lebih kecil dan lebih efisien daripada indeks normal karena mengecualikan semua orang yang berusia 18 tahun atau lebih muda.

    GoogleSQL 

    CREATE NULL_FILTERED INDEX UsersAbove18ByAge
ON Users (AgeAbove18);

    PostgreSQL 

    CREATE INDEX UsersAbove18ByAge ON users (AgeAbove18)
WHERE AgeAbove18 IS NOT NULL;

  3. Untuk mengambil Id dan Age dari semua pengguna yang berusia di atas 18 tahun, jalankan kueri berikut.

    GoogleSQL 

    SELECT Id, Age
FROM Users@{FORCE_INDEX=UsersAbove18ByAge}
WHERE AgeAbove18 IS NOT NULL;

    PostgreSQL 

    SELECT Id, Age
FROM users /*@ FORCE_INDEX = UsersAbove18ByAge */
WHERE AgeAbove18 IS NOT NULL;

  4. Untuk memfilter berdasarkan usia yang berbeda, misalnya, untuk mengambil semua pengguna yang berusia lebih dari 21 tahun, gunakan indeks dan filter yang sama pada kolom yang dihasilkan sebagai berikut:

    GoogleSQL 

    SELECT Id, Age
FROM Users@{FORCE_INDEX=UsersAbove18ByAge}
WHERE AgeAbove18 > 21;

    PostgreSQL 

    SELECT Id, Age
FROM users /*@ FORCE_INDEX = UsersAbove18ByAge */
WHERE AgeAbove18 > 21;

    Kolom yang dihasilkan dan diindeks dapat menghemat biaya evaluasi ekspresi pada waktu kueri dan menghindari penyimpanan nilai dua kali (di tabel dasar dan indeks) dibandingkan dengan kolom yang dihasilkan STORED.

Menghapus kolom yang dihasilkan

Pernyataan DDL berikut menghapus kolom yang dihasilkan dari tabel Users:

GoogleSQL 

  ALTER TABLE Users DROP COLUMN Initials;

PostgreSQL 

  ALTER TABLE users DROP COLUMN initials;

Mengubah ekspresi kolom yang dihasilkan

GoogleSQL 

ALTER TABLE Users ALTER COLUMN FullName STRING(100)
AS (ARRAY_TO_STRING(ARRAY_TO_STRING([LastName, FirstName ], " ")));

PostgreSQL 

ALTER TABLE users ADD COLUMN Initials VARCHAR(2)
GENERATED ALWAYS AS (lastname || ' ' || firstname) VIRTUAL;

Memperbarui ekspresi kolom yang dihasilkan STORED atau kolom yang dihasilkan yang tidak disimpan dan diindeks tidak diizinkan.

Membuat kunci utama pada kolom yang dihasilkan

Di Spanner, Anda dapat menggunakan kolom yang dihasilkan STORED di kunci utama.

Contoh berikut menunjukkan pernyataan DDL yang membuat tabel UserInfoLog dengan kolom yang dihasilkan ShardId. Nilai kolom ShardId bergantung pada kolom lain. Kolom ini berasal dari penggunaan fungsi MOD pada kolom UserId. ShardId dideklarasikan sebagai bagian dari kunci utama.

GoogleSQL 

CREATE TABLE UserInfoLog (
  ShardId INT64 NOT NULL
  AS (MOD(UserId, 2048)) STORED,
  UserId INT64 NOT NULL,
  FullName STRING(1024) NOT NULL,
) PRIMARY KEY (ShardId, UserId);

PostgreSQL 

CREATE TABLE UserInfoLog (
  ShardId BIGINT GENERATED ALWAYS
  AS (MOD(UserId, '2048'::BIGINT)) STORED NOT NULL,
  UserId BIGINT NOT NULL,
  FullName VARCHAR(1024) NOT NULL,
  PRIMARY KEY(ShardId, UserId));

Biasanya, untuk mengakses baris tertentu secara efisien, Anda perlu menentukan semua kolom kunci. Dalam contoh sebelumnya, ini berarti menyediakan ShardId dan UserId. Namun, Spanner terkadang dapat menyimpulkan nilai kolom kunci utama yang dihasilkan jika bergantung pada satu kolom lain dan jika nilai kolom yang menjadi dependensinya telah ditentukan sepenuhnya. Hal ini benar jika kolom yang direferensikan oleh kolom kunci utama yang dihasilkan memenuhi salah satu kondisi berikut:

  • Sama dengan nilai konstan atau parameter terikat dalam klausa WHERE, atau
  • Nilainya ditetapkan oleh operator IN dalam klausa WHERE
  • Nilainya diperoleh dari kondisi equi-join

Misalnya, untuk kueri berikut:

GoogleSQL 

SELECT * FROM UserInfoLog
AS T WHERE T.UserId=1;

PostgreSQL 

SELECT * FROM UserInfoLog
AS T WHERE T.UserId=1;

Spanner dapat menyimpulkan nilai ShardId dari UserId yang disediakan. Kueri sebelumnya setara dengan kueri berikut setelah pengoptimalan kueri:

GoogleSQL 

SELECT * FROM UserInfoLog
AS T WHERE T.ShardId = MOD(1, 2048)
AND T.UserId=1;

PostgreSQL 

SELECT * FROM UserInfoLog
AS T WHERE T.ShardId = MOD(1, 2048)
AND T.UserId=1;

Contoh berikutnya menunjukkan cara membuat tabel Students dan menggunakan ekspresi yang mengambil kolom id dari kolom JSON StudentInfo dan menggunakannya sebagai kunci utama:

GoogleSQL 

CREATE TABLE Students (
  StudentId INT64 NOT NULL
  AS (CAST(JSON_VALUE(StudentInfo, "$.id") AS INT64)) STORED,
  StudentInfo JSON NOT NULL,
) PRIMARY KEY (StudentId);

PostgreSQL 

CREATE TABLE Students (
  StudentId BIGINT GENERATED ALWAYS
  AS (((StudentInfo ->> 'id'::TEXT))::BIGINT) STORED NOT NULL,
  StudentInfo JSONB NOT NULL,
  PRIMARY KEY(StudentId));

Melihat properti kolom yang dihasilkan

INFORMATION_SCHEMA Spanner berisi informasi tentang kolom yang dihasilkan di database Anda. Berikut adalah beberapa contoh pertanyaan yang dapat Anda jawab saat membuat kueri skema informasi.

Kolom yang dihasilkan apa yang ditentukan dalam database saya?

GoogleSQL 

SELECT c.TABLE_NAME, c.COLUMN_NAME, C.IS_STORED
FROM INFORMATION_SCHEMA.COLUMNS as c
WHERE c.GENERATION_EXPRESSION IS NOT NULL;

PostgreSQL 

SELECT c.TABLE_NAME, c.COLUMN_NAME, C.IS_STORED
FROM INFORMATION_SCHEMA.COLUMNS as c
WHERE c.GENERATION_EXPRESSION IS NOT NULL;

IS_STORED adalah YES untuk kolom yang dihasilkan dan disimpan, NO untuk kolom yang dihasilkan dan tidak disimpan, atau NULL untuk kolom yang tidak dihasilkan.

Apa status kolom yang dihasilkan saat ini di tabel Users?

Jika telah menambahkan kolom yang dihasilkan ke tabel yang ada, Anda dapat meneruskan SPANNER_STATE dalam kueri untuk mengetahui status kolom saat ini. SPANNER_STATE menampilkan nilai berikut:

  • COMMITTED: Kolom dapat digunakan sepenuhnya.
  • WRITE_ONLY: Kolom sedang diisi ulang. Tidak ada operasi baca yang diizinkan.

Gunakan kueri berikut untuk menemukan status kolom:

GoogleSQL 

SELECT c.TABLE_NAME, c.COLUMN_NAME, c.SPANNER_STATE
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE c.TABLE_NAME="Users" AND c.GENERATION_EXPRESSION IS NOT NULL;

PostgreSQL 

SELECT c.TABLE_NAME, c.COLUMN_NAME, c.SPANNER_STATE
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE c.TABLE_NAME='users' AND c.GENERATION_EXPRESSION IS NOT NULL;

Catatan: Kolom yang dihasilkan dan tidak disimpan hanya dapat diakses menggunakan kueri SQL. Namun, jika diindeks, Anda dapat menggunakan API baca untuk mengakses nilai dari indeks.

Performa

Kolom yang dihasilkan STORED tidak memengaruhi performa operasi baca atau kueri. Namun, kolom yang dihasilkan yang tidak disimpan dan digunakan dalam kueri dapat memengaruhi performanya karena overhead evaluasi ekspresi kolom yang dihasilkan.

Performa operasi tulis (pernyataan DML dan mutasi) terpengaruh saat menggunakan kolom yang dihasilkan STORED atau kolom yang dihasilkan yang diindeks. Overhead ini disebabkan oleh evaluasi ekspresi kolom yang dihasilkan saat operasi tulis menyisipkan atau mengubah kolom yang dirujuk dalam ekspresi kolom yang dihasilkan. Karena overhead bervariasi bergantung pada beban kerja tulis untuk aplikasi, desain skema, dan karakteristik set data, sebaiknya Anda menjalankan benchmark aplikasi sebelum menggunakan kolom yang dihasilkan.

Langkah selanjutnya