Menentukan kolom identitas

Dokumen ini menjelaskan cara membuat dan menggunakan kolom identitas, terkadang disebut sebagai kolom auto-increment, yang digunakan untuk membuat dan mempertahankan kunci utama pada tabel Anda. Saat Anda menyisipkan baris ke dalam tabel yang memiliki kolom identitas, BigQuery akan membuat nilai bilangan bulat unik untuk kolom tersebut.

Ringkasan

Kolom identitas adalah kolom INT64 yang diisi dengan nilai unik yang dibuat sistem.

Kasus penggunaan utama untuk kolom identitas adalah membuat kunci utama. Anda juga dapat membuat kunci utama menggunakan GENERATE_UUID fungsi untuk membuat string unik, tetapi kolom identitas umumnya lebih disukai karena alasan berikut:

  • Nilai bilangan bulat memerlukan lebih sedikit ruang penyimpanan daripada nilai string.
  • Menggunakan bilangan bulat untuk gabungan tabel lebih efisien daripada menggunakan string.

Nilai untuk kolom identitas dibuat berdasarkan nilai awal yang menentukan nilai pertama, dan nilai inkremen yang menentukan perbedaan minimum antara nilai yang dibuat secara berurutan.

Nilai kolom identitas yang dibuat memiliki properti berikut:

  • Unik. Nilai yang dibuat secara otomatis bersifat unik dalam tabel.
  • Diurutkan secara longgar. Nilai yang dibuat tidak dijamin dalam urutan yang meningkat atau menurun secara ketat.
  • Jarang. Nilai yang dibuat tidak dijamin berurutan. Beberapa nilai mungkin dilewati, tetapi nilai dalam kolom identitas selalu berbeda dengan kelipatan inkremen yang Anda tentukan.

Batasan

  • Tabel dapat memiliki maksimal satu kolom identitas.
  • Anda dapat membaca dari tabel dengan kolom identitas menggunakan SQL lama, tetapi Anda tidak dapat menulis ke tabel dengan kolom identitas menggunakan SQL lama.
  • Anda tidak dapat menggunakan pengelompokan atau partisi pada kolom identitas.

  • Operasi salin tabel berikut tidak didukung jika tabel sumber atau tujuan memiliki kolom identitas:

    • Salin tabel dengan disposisi tulis WRITE_APPEND atau WRITE_TRUNCATE
    • Salin tabel multi-sumber

  • Streaming data menggunakan Storage Write API (gRPC) atau metode tabledata.insertAll API tidak didukung untuk tabel dengan kolom identitas.

Membuat kolom identitas

Anda dapat membuat kolom identitas saat membuat tabel baru menggunakan pernyataan DDL CREATE TABLE. Gunakan klausa GENERATED AS IDENTITY untuk menetapkan kolom INT64 sebagai kolom identitas. Tabel dapat memiliki maksimal satu kolom identitas. Anda dapat menentukan salah satu mode pembuatan berikut yang menentukan apakah Anda dapat menyisipkan nilai secara manual ke dalam kolom identitas:

  • GENERATED ALWAYS AS IDENTITY: nilai selalu dibuat oleh sistem. Anda tidak dapat memberikan nilai Anda sendiri saat menyisipkan atau memperbarui data di kolom ini. Jika Anda tidak menentukan ALWAYS atau BY DEFAULT, ALWAYS akan digunakan.

  • GENERATED BY DEFAULT AS IDENTITY: Anda dapat menyisipkan atau mengubah nilai di kolom identitas. BigQuery tidak menerapkan keunikan nilai yang Anda sisipkan atau ubah.

    Jika Anda menghapus kolom atau memberikan NULL saat menyisipkan data, BigQuery akan otomatis membuat nilai untuk Anda. Kolom identitas tidak boleh berisi nilai NULL. Jika ingin menggunakan nilai yang dibuat dalam pernyataan INSERT, MERGE, atau UPDATE, Anda dapat menggunakan kata kunci DEFAULT atau NULL.

Contoh berikut membuat tabel mydataset.id_table dengan identitas kolom id yang dimulai dari 0 dan bertambah 5:

CREATE TABLE mydataset.id_table (
  id INT64 GENERATED ALWAYS AS IDENTITY(START WITH 0 INCREMENT BY 5),
  data STRING
);

Menambahkan properti kolom identitas ke kolom

Untuk mengubah kolom yang ada agar menghasilkan nilai identitas, gunakan pernyataan DDL ALTER TABLE ALTER COLUMN SET GENERATED. Pernyataan ini mengubah kolom INT64 yang ada menjadi kolom identitas. Pernyataan ini tidak mengisi ulang nilai untuk baris yang ada di kolom identitas.

Menggunakan pernyataan DML dengan kolom identitas

Anda dapat menggunakan pernyataan DML seperti INSERT, MERGE, dan UPDATE dengan kolom identitas. Bagian berikut menggunakan tabel mydataset.mytable yang memiliki kolom identitas bernama id dan kolom string bernama data:

CREATE OR REPLACE TABLE mydataset.mytable (
  id INT64 GENERATED BY DEFAULT AS IDENTITY(START WITH 100 INCREMENT BY 10),
  data STRING
);

Menyisipkan data

Saat menyisipkan data ke dalam tabel dengan kolom identitas, Anda dapat menghapus kolom identitas dari daftar kolom untuk membuat nilai untuknya. Pernyataan INSERT berikut menghapus kolom id, BigQuery akan membuat nilai untuknya:

INSERT mydataset.mytable (data) VALUES ('A'), ('B'), ('C');

Hasilnya mirip dengan berikut ini, meskipun urutan penetapan nilai yang dibuat ke baris dapat bervariasi:

+-----+------+
| id  | data |
+-----+------+
| 110 | A    |
| 120 | B    |
| 100 | C    |
+-----+------+

Jika kolom identitas ditentukan dengan GENERATED BY DEFAULT AS IDENTITY, Anda dapat menentukan nilai Anda sendiri untuk kolom tersebut. Anda juga dapat menggunakan kata kunci DEFAULT atau NULL agar BigQuery membuat nilai.

Pernyataan INSERT berikut memberikan nilai untuk satu baris, dan menggunakan DEFAULT atau NULL untuk membuat nilai bagi dua baris lainnya:

INSERT mydataset.mytable (id, data)
VALUES (155, 'D'), (DEFAULT, 'E'), (NULL, 'F');

Hasilnya mirip dengan berikut ini:

+-----+------+
| id  | data |
+-----+------+
| 110 | A    |
| 120 | B    |
| 100 | C    |
| 155 | D    |
| 140 | E    |
| 130 | F    |
+-----+------+

Jika kolom identitas ditentukan dengan GENERATED ALWAYS AS IDENTITY, Anda hanya dapat menggunakan kata kunci DEFAULT agar BigQuery membuat nilai. Anda tidak dapat memberikan nilai Anda sendiri atau menggunakan NULL.

Menggabungkan data

Anda dapat menggunakan pernyataan MERGE untuk menggabungkan data ke dalam tabel dengan kolom identitas. Jika kolom identitas Anda menggunakan mode pembuatan GENERATED BY DEFAULT AS IDENTITY, Anda dapat menggunakan kata kunci DEFAULT atau NULL untuk membuat nilai saat Anda menyisipkan atau memperbarui data sebagai bagian dari pernyataan MERGE.

Contoh berikut menggabungkan mydataset.source_table ke dalam mydataset.mytable, menyisipkan baris baru jika tidak ada kecocokan pada kolom data, dan memperbarui kolom id ke nilai baru yang dibuat jika ada kecocokan:

CREATE OR REPLACE TABLE mydataset.source_table(data STRING)
AS SELECT * FROM UNNEST(['A', 'C', 'G']);

MERGE mydataset.mytable T
USING mydataset.source_table S
ON T.data = S.data
WHEN MATCHED THEN
  UPDATE SET id = DEFAULT
WHEN NOT MATCHED THEN
  INSERT(data)
  VALUES(S.data);

Hasilnya mirip dengan berikut ini:

+-----+------+
| id  | data |
+-----+------+
| 160 | A    |
| 120 | B    |
| 150 | C    |
| 155 | D    |
| 140 | E    |
| 130 | F    |
| 170 | G    |
+-----+------+

Jika kolom identitas Anda menggunakan mode pembuatan GENERATED ALWAYS AS IDENTITY, Anda tidak dapat menyertakan kolom identitas dalam klausa pembaruan gabungan. Untuk menggunakan klausa penyisipan gabungan, Anda dapat menghapus kolom identitas dari daftar kolom atau menggunakan kata kunci DEFAULT.

Memperbarui data

Anda dapat menggunakan pernyataan UPDATE untuk memperbarui nilai di kolom identitas yang menggunakan mode pembuatan GENERATED BY DEFAULT AS IDENTITY. Anda dapat menggunakan kata kunci DEFAULT atau NULL untuk membuat nilai baru.

Contoh berikut memperbarui semua nilai di kolom id ke nilai yang baru dibuat:

UPDATE mydataset.mytable
SET id = NULL
WHERE TRUE;

Hasilnya mirip dengan berikut ini:

+-----+------+
| id  | data |
+-----+------+
| 190 | A    |
| 210 | B    |
| 240 | C    |
| 230 | D    |
| 180 | E    |
| 200 | F    |
| 220 | G    |
+-----+------+

Jika kolom identitas Anda menggunakan mode pembuatan GENERATED ALWAYS AS IDENTITY, Anda tidak dapat memperbarui kolom identitas.

Menambahkan ke tabel

Anda dapat menggunakan perintah bq query dengan flag --append_table untuk menambahkan hasil kueri ke tabel tujuan yang memiliki kolom identitas. Jika kueri menghapus kolom identitas, nilai akan dibuat untuknya.

Contoh berikut hanya menambahkan data untuk kolom data ke mydataset.mytable:

bq query \
    --nouse_legacy_sql \
    --append_table \
    --destination_table=mydataset.mytable \
    'SELECT "H" AS data'

Baris baru dengan nilai id yang dibuat ditambahkan ke mydataset.mytable.

Memuat data

Anda dapat memuat data ke dalam tabel dengan kolom identitas menggunakan bq load perintah atau LOAD DATA pernyataan. Jika kolom identitas dihapus dari data atau skema sumber, nilai akan dibuat untuknya. Jika kolom identitas adalah GENERATED ALWAYS AS IDENTITY, kolom tersebut harus dihapus.

Contoh berikut memuat data dari file CSV data.csv ke mydataset.mytable. File hanya berisi data untuk kolom data:

"X"
"Y"

Perintah bq load berikut memuat data.csv ke mydataset.mytable, menghapus baris header, dan hanya menentukan kolom data dalam skema:

bq load --source_format=CSV --skip_leading_rows=0 \
mydataset.mytable data.csv data:STRING

Tugas pemuatan membuat nilai id untuk baris baru.

Menghapus properti kolom identitas

Anda dapat menghapus properti identitas dari kolom menggunakan pernyataan DDL ALTER TABLE ALTER COLUMN DROP GENERATED.

Contoh berikut menghapus properti kolom identitas dari kolom id di mydataset.mytable:

ALTER TABLE mydataset.mytable
ALTER COLUMN id DROP GENERATED;

Melihat informasi tentang kolom identitas

Untuk melihat konfigurasi kolom identitas untuk kolom, buat kueri tampilan INFORMATION_SCHEMA.COLUMNS.

Contoh berikut menunjukkan informasi kolom identitas untuk kolom di mydataset.mytable:

SELECT
  column_name,
  is_identity,
  identity_generation,
  identity_start,
  identity_increment
FROM
  mydataset.INFORMATION_SCHEMA.COLUMNS
WHERE
  table_name = 'mytable';

Hasilnya mirip dengan berikut ini:

+-------------+-------------+---------------------+----------------+--------------------+
| column_name | is_identity | identity_generation | identity_start | identity_increment |
+-------------+-------------+---------------------+----------------+--------------------+
| id          | YES         | BY DEFAULT          | 100            | 10                 |
| data        | NO          | NULL                | NULL           | NULL               |
+-------------+-------------+---------------------+----------------+--------------------+

Atau, Anda dapat membuat kueri kolom ddl dari tampilan INFORMATION_SCHEMA.TABLES untuk melihat definisi kolom identitas dalam pernyataan DDL CREATE TABLE untuk tabel.

Langkah berikutnya