Membuat dan mengelola kolom yang dibuat

Kolom yang dihasilkan adalah kolom yang selalu dihitung dari kolom lain dalam baris. Kolom ini dapat menyederhanakan kueri, menghemat biaya evaluasi ekspresi pada waktu kueri, dan dapat diindeks atau digunakan sebagai kunci asing. Halaman ini menjelaskan cara mengelola jenis kolom ini di database untuk database dialek GoogleSQL dan database dialek PostgreSQL.

Menambahkan kolom yang dibuat 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 gabungan dari 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 menghilangkan atribut STORED dalam DDL. Jenis kolom yang dibuat ini dievaluasi pada waktu kueri dan dapat menyederhanakan kueri. Di PostgreSQL, Anda dapat membuat kolom yang dihasilkan tidak tersimpan 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 dijadikan kolom yang dihasilkan STORED atau kolom yang dihasilkan yang diindeks.

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

  • Untuk database dialek GoogleSQL, kolom yang dihasilkan dan tidak disimpan dengan jenis STRING atau BYTES harus memiliki panjang MAX.

  • Untuk database dialek PostgreSQL, kolom virtual atau tidak disimpan yang dihasilkan dengan jenis VARCHAR harus memiliki panjang MAX.

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

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

  • Penulisan langsung ke kolom yang dihasilkan tidak diizinkan.

  • Opsi kolom allow_commit_timestamp tidak diizinkan di kolom yang dibuat atau kolom apa pun yang direferensikan oleh kolom yang dibuat.

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

  • Anda tidak dapat melepaskan kolom yang dirujuk oleh kolom yang dihasilkan.

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

    • Kunci utama yang dibuat tidak dapat merujuk ke kolom lain yang dibuat.

    • Kunci utama yang dihasilkan dapat mereferensikan 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:

    • Read API: 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 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 dibuat 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 dan contoh selengkapnya, 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 dibuat dapat menyederhanakan kueri.

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 dibuat sebagai kunci asing.

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 dapat menambahkan kolom yang dibuat 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 disimpan dan dibuat ke tabel yang ada, operasi 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 dicerminkan 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 tabel secara penuh akan tidak efisien, jadi kita menggunakan indeks parsial.

  1. Gunakan pernyataan berikut untuk menambahkan kolom yang dihasilkan lainnya 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 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 di atas 21 tahun, gunakan indeks yang sama dan filter 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 (dalam 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 dibuat

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;

Ekspresi kolom yang dihasilkan STORED atau kolom yang dihasilkan non-tersimpan dan diindeks tidak boleh diperbarui.

Membuat kunci utama pada kolom yang dihasilkan

Di Spanner, Anda dapat menggunakan kolom yang dibuat 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 harus menentukan semua kolom kunci. Dalam contoh sebelumnya, ini berarti memberikan 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 bergantung padanya ditentukan sepenuhnya. Hal ini benar jika kolom yang dirujuk oleh kolom kunci utama yang dibuat memenuhi salah satu kondisi berikut:

  • Sama dengan nilai konstanta atau parameter terikat dalam klausa WHERE, atau
  • Nilainya ditetapkan oleh operator IN dalam klausa WHERE
  • Nilai ini 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 diberikan. 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 (INT64(StudentInfo.id)) STORED,
  StudentInfo JSON NOT NULL,
) PRIMARY KEY (StudentId);

PostgreSQL 

CREATE TABLE Students (
  StudentId BIGINT GENERATED ALWAYS
  AS ((StudentInfo ->> 'id')::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 turunan 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 virtual tersimpan, NO untuk kolom virtual tidak tersimpan, atau NULL untuk kolom non-virtual.

Apa status kolom yang dihasilkan saat ini dalam tabel Users?

Jika Anda telah menambahkan kolom yang dibuat ke tabel yang ada, Anda mungkin ingin 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 dibuat 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 virtual yang tidak disimpan yang digunakan dalam kueri dapat memengaruhi performa kueri karena overhead evaluasi ekspresi kolom virtual.

Performa operasi tulis (pernyataan DML dan mutasi) terpengaruh saat menggunakan kolom yang dihasilkan STORED atau kolom yang dihasilkan yang diindeks. Overhead terjadi karena evaluasi ekspresi kolom yang dihasilkan saat operasi tulis menyisipkan atau mengubah salah satu kolom yang dirujuk dalam ekspresi kolom yang dihasilkan. Karena overhead bervariasi bergantung pada beban kerja penulisan untuk aplikasi, desain skema, dan karakteristik set data, sebaiknya Anda melakukan tolok ukur aplikasi sebelum menggunakan kolom yang dihasilkan.

Langkah berikutnya