Membuat dan mengelola kolom yang dibuat

Kolom yang dihasilkan adalah kolom yang selalu dihitung dari kolom lain dalam baris. Kolom ini dapat membuat kueri menjadi 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 penyambungan dari FirstName dan LastName

GoogleSQL

CREATE TABLE Users (
Id STRING(20) NOT NULL,
FirstName STRING(50),
LastName STRING(50),
Age INT64 NOT NULL,
FullName STRING(100) AS (ARRAY_TO_STRING([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)
);

Nilai FullName dihitung saat baris baru disisipkan atau saat FirstName dan/atau LastName diperbarui untuk baris yang ada. Nilai yang dihitung disimpan bersama dengan kolom lain dari tabel. SQL dalam tanda kurung disebut ekspresi pembuatan.

• 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 tidak boleh berisi fungsi non-deterministik seperti PENDING_COMMIT_TIMESTAMP(), CURRENT_DATE(), dan CURRENT_TIMESTAMP().

  • Anda tidak dapat mengubah ekspresi kolom yang dibuat.

• Atribut STORED yang mengikuti ekspresi menyimpan hasil ekspresi bersama dengan kolom tabel lainnya. Update berikutnya pada kolom yang direferensikan akan menyebabkan Spanner mengevaluasi ulang dan menyimpan ekspresi.

• Spanner tidak mengizinkan kolom yang dibuat kecuali jika menggunakan atribut STORED.

• Penulisan langsung ke kolom yang dihasilkan tidak diizinkan.

• Opsi kolom allow_commit_timestamp tidak diizinkan pada kolom yang dibuat atau kolom apa pun yang membuat referensi kolom.

• Anda tidak dapat mengubah jenis data pada kolom yang dihasilkan, atau untuk kolom mana pun yang direferensikan oleh kolom yang dihasilkan.

• Anda tidak dapat menghapus kolom yang merujuk kolom yang dihasilkan.

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

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

  • Kunci utama yang dihasilkan dapat merujuk, paling banyak, 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 dibuat:

  • API Baca: Anda harus menentukan kolom kunci sepenuhnya, termasuk kolom kunci yang dihasilkan.
  • Mutation API: Untuk INSERT, INSERT_OR_UPDATE, dan REPLACE, Spanner tidak memungkinkan Anda menentukan kolom kunci yang dihasilkan. Untuk UPDATE, Anda dapat secara opsional menentukan kolom kunci yang dihasilkan. 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 direferensikan. Untuk mengetahui informasi selengkapnya dan contohnya, 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;

Hal ini setara dengan pernyataan berikut, yang tidak menggunakan kolom yang dihasilkan yang disimpan.

GoogleSQL

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

PostgreSQL

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

Selain menyederhanakan kueri dan menghemat biaya evaluasi ekspresi pada waktu kueri, kolom yang dihasilkan juga dapat diindeks atau digunakan sebagai kunci asing.

Membuat indeks pada kolom yang dihasilkan

Untuk membantu pencarian di 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 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;

Menambahkan kolom dihasilkan yang disimpan ke tabel yang ada adalah operasi yang berjalan lama untuk mengisi ulang nilai kolom. Selama pengisian ulang, kolom yang dihasilkan yang disimpan tidak dapat dibaca atau dibuat kuerinya. Status pengisian ulang tercermin dalam INFORMATION_SCHEMA.

Membuat indeks sebagian menggunakan kolom yang dihasilkan

Bagaimana jika kita hanya ingin menanyakan 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)) STORED;
   

   PostgreSQL

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

2. Buat indeks di 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 tidak mencakup semua orang yang berusia 18 tahun ke bawah.

   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 usia yang berbeda, misalnya, untuk mengambil semua pengguna berusia di atas 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;
   

Menghapus kolom yang dibuat

Pernyataan DDL berikut menghapus kolom yang dihasilkan dari tabel Users.

ALTER TABLE Users
DROP COLUMN Initials;

Mengubah ekspresi kolom yang dihasilkan

Modifikasi ekspresi kolom yang dihasilkan tidak diizinkan. Sebagai gantinya, Anda harus melepaskan kolom yang ada dan membuat kolom baru yang dihasilkan dengan ekspresi baru.

Membuat kunci utama di kolom yang dihasilkan

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

Contoh berikut menunjukkan pernyataan DDL yang membuat tabel UserInfoLog dengan kolom yang dihasilkan ShardId. Nilai kolom ShardId bergantung pada kolom lain. Nilai ini diperoleh dengan menggunakan 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 utama. Pada contoh sebelumnya, ini berarti menyediakan ShardId dan UserId. Namun, Spanner terkadang dapat menyimpulkan nilai kolom kunci utama yang dibuat jika bergantung pada satu kolom lain dan jika nilai kolom yang menjadi tempatnya bergantung sepenuhnya ditentukan. Hal ini berlaku jika kolom yang dirujuk oleh kolom kunci utama yang dihasilkan memenuhi salah satu kondisi berikut:

• Itu sama dengan nilai konstanta atau parameter terikat dalam klausa WHERE, atau
• Metode ini mendapatkan nilainya yang ditetapkan oleh operator IN dalam klausa WHERE
• Ia mendapatkan nilainya 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?

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

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 pembacaan 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 tidak memengaruhi performa operasi baca atau kueri. Namun, hal ini dapat memengaruhi performa operasi tulis (pernyataan `DML` dan `Mutasi`) karena overhead mengevaluasi ekspresi kolom dari kolom yang dihasilkan jika operasi tulis mengubah salah satu kolom yang dirujuk dalam ekspresi kolom yang dihasilkan. Karena overhead bervariasi bergantung pada workload tulis untuk karakteristik aplikasi, desain skema, dan set data, sebaiknya ukur aplikasi Anda sebelum menggunakan kolom yang dihasilkan.

Langkah selanjutnya

• Pelajari SCHEMA INFORMASI Spanner lebih lanjut.

• Lihat detail selengkapnya tentang kolom yang dihasilkan dalam detail parameter CREATE TABLE.