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()
, danCURRENT_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
, danREPLACE
, Spanner tidak memungkinkan Anda menentukan kolom kunci yang dihasilkan. UntukUPDATE
, Anda dapat secara opsional menentukan kolom kunci yang dihasilkan. UntukDELETE
, Anda harus menentukan kolom kunci sepenuhnya termasuk kunci yang dihasilkan. - DML: Anda tidak dapat menulis secara eksplisit ke kunci yang dihasilkan dalam pernyataan
INSERT
atauUPDATE
. - 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
(
=
) atauIN
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.
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;
Buat indeks di kolom baru ini, dan nonaktifkan pengindeksan nilai
NULL
dengan kata kunciNULL_FILTERED
di GoogleSQL atau predikatIS 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;
Untuk mengambil
Id
danAge
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;
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 klausaWHERE
- 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.