Update tabel streaming dengan pengambilan data perubahan

Pengambilan data perubahan BigQuery (CDC) akan memperbarui tabel BigQuery dengan memproses dan menerapkan perubahan yang di-streaming pada data yang ada. Sinkronisasi ini dilakukan melalui operasi baris upsert and delete yang di-streaming secara real time oleh BigQuery Storage Write API, yang harus Anda ketahui sebelum melanjutkan.

Sebelum memulai

Berikan peran Identity and Access Management (IAM) yang memberi pengguna izin yang diperlukan untuk melakukan setiap tugas dalam dokumen ini, dan pastikan alur kerja Anda memenuhi setiap prasyarat.

Izin yang diperlukan

Untuk mendapatkan izin yang Anda perlukan guna menggunakan Storage Write API, minta administrator untuk memberi Anda peran IAM (roles/bigquery.dataEditor) Editor Data BigQuery Untuk mengetahui informasi selengkapnya tentang cara memberikan peran, lihat Mengelola akses.

Peran bawaan ini berisi izin bigquery.tables.updateData, yang diperlukan untuk menggunakan Storage Write API.

Anda mungkin juga bisa mendapatkan izin ini dengan peran khusus atau peran bawaan lainnya.

Untuk mengetahui informasi lebih lanjut tentang peran dan izin IAM di BigQuery, baca Pengantar IAM.

Prasyarat

Untuk menggunakan BigQuery CDC, alur kerja Anda harus memenuhi kondisi berikut:

  • Anda harus menggunakan Storage Write API di aliran default.
  • Anda harus mendeklarasikan kunci utama untuk tabel tujuan di BigQuery. Kunci utama gabungan yang berisi hingga 16 kolom didukung.
  • Resource komputasi BigQuery yang memadai harus tersedia untuk menjalankan operasi baris CDC. Perlu diketahui bahwa jika operasi modifikasi baris CDC gagal, Anda mungkin secara tidak sengaja menyimpan data yang ingin dihapus. Untuk informasi selengkapnya, lihat Pertimbangan data yang dihapus.

Menentukan perubahan pada data yang ada

Di BigQuery CDC, kolom semu _CHANGE_TYPE akan menunjukkan jenis perubahan yang akan diproses untuk setiap baris. Untuk menggunakan CDC, tetapkan _CHANGE_TYPE saat Anda melakukan streaming modifikasi baris menggunakan Storage Write API. Kolom semu _CHANGE_TYPE hanya menerima nilai UPSERT dan DELETE. Tabel dianggap kompatibel dengan CDC, sedangkan Storage Write API melakukan streaming modifikasi baris ke tabel dengan cara ini.

Contoh dengan nilai UPSERT dan DELETE

Pertimbangkan tabel berikut di BigQuery:

ID Nama Gaji
100 Tagihan 2.000
101 Lucy 3.000
102 Ethan 5.000

Modifikasi baris berikut di-streaming oleh Storage Write API:

ID Nama Gaji _CHANGE_TYPE
100 DELETE
101 Lucy 8.000 UPSERT
105 Maks 6.000 UPSERT

Tabel yang diupdate kini menjadi sebagai berikut:

ID Nama Gaji
101 Lucy 8.000
102 Ethan 5.000
105 Maks 6.000

Mengelola penghentian tabel yang tidak berlaku

Secara default, setiap kali Anda menjalankan kueri, BigQuery akan menampilkan hasil terbaru. Untuk memberikan hasil terbaru saat membuat kueri tabel yang mendukung CDC, BigQuery harus menerapkan setiap modifikasi baris yang di-streaming hingga waktu mulai kueri, sehingga versi tabel terbaru dikueri. Menerapkan perubahan baris ini pada waktu proses kueri akan meningkatkan latensi dan biaya kueri. Namun, jika Anda tidak memerlukan hasil kueri yang sepenuhnya terbaru, Anda dapat mengurangi biaya dan latensi pada kueri dengan menetapkan opsi max_staleness di tabel Anda. Jika opsi ini disetel, BigQuery akan menerapkan perubahan baris setidaknya sekali dalam interval yang ditentukan oleh nilai max_staleness, sehingga Anda dapat menjalankan kueri tanpa menunggu update diterapkan, dengan mengorbankan beberapa data yang tidak berlaku.

Perilaku ini sangat berguna untuk dasbor dan laporan yang tidak terlalu mementingkan keaktualan data. Opsi ini juga berguna untuk pengelolaan biaya dengan memberi Anda lebih banyak kontrol atas seberapa sering BigQuery menerapkan modifikasi baris.

Tabel kueri dengan kumpulan opsi max_staleness

Saat Anda membuat kueri pada tabel dengan kumpulan opsi max_staleness, BigQuery akan menampilkan hasil berdasarkan nilai max_staleness dan waktu saat penerapan tugas terakhir terjadi, yang diwakili oleh stempel waktu upsert_stream_apply_watermark.

Perhatikan contoh berikut, saat tabel memiliki opsi max_staleness yang ditetapkan ke 10 menit, dan penerapan tugas terbaru terjadi di T20:

Waktu proses kueri terjadi dalam interval waktu maksimum untuk data yang tidak berlaku.

Jika Anda membuat kueri tabel di T25, maka versi tabel saat ini akan berstatus 5 menit tidak berlaku, yang kurang dari interval max_staleness 10 menit. Dalam hal ini, BigQuery akan menampilkan versi tabel di T20, yang berarti data yang ditampilkan juga sudah 5 menit tidak berlaku.

Saat Anda menetapkan opsi max_staleness di tabel, BigQuery akan menerapkan modifikasi baris yang tertunda setidaknya sekali dalam interval max_staleness. Namun, dalam beberapa kasus, BigQuery mungkin tidak menyelesaikan proses penerapan modifikasi baris tertunda ini dalam interval.

Misalnya, jika Anda membuat kueri tabel di T35, dan proses penerapan modifikasi baris yang tertunda belum selesai, maka versi tabel saat ini sudah 15 menit tidak berlaku, yang lebih besar daripada interval max_staleness 10 menit. Dalam hal ini, pada waktu proses kueri, BigQuery akan menerapkan semua modifikasi baris antara T20 dan T35, yang berarti data benar-benar terbaru, dengan mengorbankan beberapa latensi kueri tambahan. Hal ini dianggap sebagai tugas penggabungan runtime.

Waktu proses kueri terjadi di luar interval waktu maksimum untuk data yang tidak berlaku.

Nilai max_staleness tabel umumnya harus lebih tinggi dari dua nilai berikut:

  • Kehabisan data maksimum yang dapat ditoleransi untuk alur kerja Anda.
  • Dua kali waktu maksimum yang diperlukan untuk menerapkan perubahan yang telah di-upsert ke tabel Anda, ditambah beberapa buffer tambahan.

Guna menghitung waktu yang diperlukan untuk menerapkan perubahan yang telah diupdate dan dimasukkan ke tabel yang ada, gunakan kueri SQL berikut untuk menentukan durasi persentil ke-95 tugas yang diterapkan di latar belakang, ditambah buffer tujuh menit untuk memungkinkan konversi penyimpanan yang dioptimalkan untuk penulisan BigQuery (buffer streaming).

SELECT
  project_id,
  destination_table.dataset_id,
  destination_table.table_id,
  APPROX_QUANTILES((TIMESTAMP_DIFF(end_time, creation_time,MILLISECOND)/1000), 100)[OFFSET(95)] AS p95_background_apply_duration_in_seconds,
  CEILING(APPROX_QUANTILES((TIMESTAMP_DIFF(end_time, creation_time,MILLISECOND)/1000), 100)[OFFSET(95)]*2/60)+7 AS recommended_max_staleness_with_buffer_in_minutes
FROM `region-us`.INFORMATION_SCHEMA.JOBS AS job
WHERE
  project_id = 'PROJECT_ID'
  AND DATE(creation_time) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND CURRENT_DATE()
  AND job_id LIKE "%cdc_background%"
GROUP BY 1,2,3;

Ganti PROJECT_ID dengan ID project yang berisi tabel BigQuery yang sedang dimodifikasi oleh BigQuery CDC.

Durasi penerapan tugas di latar belakang dipengaruhi oleh beberapa faktor, termasuk jumlah dan kompleksitas operasi CDC yang dikeluarkan dalam interval yang tidak berlaku, ukuran tabel, dan ketersediaan resource BigQuery. Untuk informasi selengkapnya tentang ketersediaan resource, lihat Mengukur dan memantau pemesanan LATAR BELAKANG.

Membuat tabel dengan opsi max_staleness

Untuk membuat tabel dengan opsi max_staleness, gunakan pernyataan CREATE TABLE. Contoh berikut membuat tabel employees dengan batas max_staleness selama 10 menit:

CREATE TABLE employees (
  id INT64 PRIMARY KEY NOT ENFORCED,
  name STRING)
  CLUSTER BY
    id
  OPTIONS (
    max_staleness = INTERVAL 10 MINUTE);

Mengubah opsi max_staleness untuk tabel yang sudah ada

Untuk menambahkan atau mengubah batas max_staleness dalam tabel yang ada, gunakan pernyataan ALTER TABLE. Contoh berikut mengubah batas max_staleness pada tabel employees menjadi 15 menit:

ALTER TABLE employees
SET OPTIONS (
  max_staleness = INTERVAL 15 MINUTE);

Menentukan nilai max_staleness tabel saat ini

Untuk menentukan nilai max_staleness tabel saat ini, buat kueri tampilan INFORMATION_SCHEMA.TABLE_OPTIONS. Contoh berikut akan memeriksa nilai max_staleness tabel mytable saat ini:

SELECT
  option_name,
  option_value
FROM
  DATASET_NAME.INFORMATION_SCHEMA.TABLE_OPTIONS
WHERE
  option_name = 'max_staleness'
  AND table_name = 'TABLE_NAME';

Ganti kode berikut:

  • DATASET_NAME: nama set data tempat tabel yang mendukung CDC.
  • TABLE_NAME: nama tabel yang mendukung CDC.

Hasilnya menunjukkan bahwa nilai max_staleness adalah 10 menit:

+---------------------+--------------+
| Row |  option_name  | option_value |
+---------------------+--------------+
|  1  | max_staleness | 0-0 0 0:10:0 |
+---------------------+--------------+

Memantau progres operasi upsert tabel

Untuk memantau status tabel dan memeriksa kapan modifikasi baris terakhir diterapkan, buat kueri tampilan INFORMATION_SCHEMA.TABLES untuk mendapatkan stempel waktu upsert_stream_apply_watermark.

Contoh berikut memeriksa nilai upsert_stream_apply_watermark tabel mytable:

SELECT upsert_stream_apply_watermark
FROM DATASET_NAME.INFORMATION_SCHEMA.TABLES
WHERE table_name = 'TABLE_NAME';

Ganti kode berikut:

  • DATASET_NAME: nama set data tempat tabel yang mendukung CDC.
  • TABLE_NAME: nama tabel yang mendukung CDC.

Hasilnya serupa dengan berikut ini:

[{
 "upsert_stream_apply_watermark": "2022-09-15T04:17:19.909Z"
}]

Operasi upsert dilakukan oleh akun layanan bigquery-adminbot@system.gserviceaccount.com dan muncul dalam histori tugas project yang berisi tabel yang mendukung CDC.

Mengonfigurasi pemesanan BigQuery untuk digunakan dengan CDC

Anda dapat menggunakan pemesanan BigQuery guna mengalokasikan resource compute BigQuery khusus untuk operasi modifikasi baris CDC. Dengan pemesanan, Anda dapat menetapkan batas biaya untuk melakukan operasi ini. Pendekatan ini sangat berguna untuk alur kerja dengan operasi CDC yang sering dilakukan terhadap tabel besar, yang akan menimbulkan biaya sesuai permintaan yang tinggi karena banyaknya byte yang diproses saat melakukan setiap operasi.

Tugas BigQuery CDC yang menerapkan modifikasi baris tertunda dalam interval max_staleness dianggap sebagai tugas latar belakang dan memanfaatkan jenis penetapan BACKGROUND, bukan jenis tugas QUERY. Sebaliknya, kueri di luar interval max_staleness yang memerlukan modifikasi baris untuk diterapkan saat waktu proses kueri memanfaatkan jenis penetapan QUERY. Tugas latar belakang BigQuery CDC yang dilakukan tanpa pemanfaatan penetapan BACKGROUND harga sesuai permintaan. Pertimbangan ini penting saat merancang strategi pengelolaan workload untuk BigQuery CDC.

Untuk mengonfigurasi pemesanan BigQuery yang akan digunakan dengan CDC, mulailah dengan membeli komitmen kapasitas dan mengonfigurasi pemesanan di region tempat tabel BigQuery Anda berada. Untuk panduan ukuran pemesanan Anda, lihat Mengukur dan memantau BACKGROUND pemesanan. Setelah membuat pemesanan, assign project BigQuery ke pemesanan, dan tetapkan opsi job_type ke BACKGROUND dengan menjalankan pernyataan CREATE ASSIGNMENT berikut:

CREATE ASSIGNMENT
  `ADMIN_PROJECT_ID.region-LOCATION.RESERVATION_NAME.ASSIGNMENT_ID`
OPTIONS (
  assignee = 'projects/PROJECT_ID',
  job_type = 'BACKGROUND');

Ganti kode berikut:

  • ADMIN_PROJECT_ID: ID project administrasi yang memiliki pemesanan.
  • LOCATION: lokasi pemesanan.
  • RESERVATION_NAME: nama pemesanan.
  • ASSIGNMENT_ID: ID tugas. ID harus unik untuk project dan lokasi, diawali dan diakhiri dengan huruf kecil atau angka, serta hanya berisi huruf kecil, angka, dan tanda hubung.
  • PROJECT_ID: ID project yang berisi tabel BigQuery yang diubah oleh BigQuery CDC. Project ini ditetapkan ke pemesanan.

Mengukur dan memantau pemesanan BACKGROUND

Pemesanan akan menentukan jumlah resource compute yang tersedia untuk menjalankan operasi compute BigQuery. Meremehkan pemesanan dapat meningkatkan waktu pemrosesan operasi modifikasi baris CDC. Untuk menentukan ukuran pemesanan secara akurat, pantau pemakaian slot historis untuk project yang menjalankan operasi CDC dengan membuat kueri tampilan INFORMATION_SCHEMA.JOBS_TIMELINE:

SELECT
  period_start,
  SUM(period_slot_ms) / (1000 * 60) AS slots_used
FROM
  REGION.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT
WHERE
  DATE(job_creation_time) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  AND CURRENT_DATE()
  AND job_id LIKE '%cdc_background%'
GROUP BY
  period_start
ORDER BY
  period_start DESC;

Ganti REGION dengan nama region tempat project Anda berada. Misalnya, region-us.

Pertimbangan data yang dihapus

  • Operasi BigQuery CDC memanfaatkan resource compute BigQuery. Jika operasi CDC dikonfigurasi untuk menggunakan penagihan sesuai permintaan, operasi CDC akan dilakukan secara rutin menggunakan resource BigQuery internal. Jika operasi CDC dikonfigurasi dengan pemesanan BACKGROUND, operasi CDC tunduk pada ketersediaan resource pemesanan yang dikonfigurasi. Jika tidak tersedia cukup resource dalam pemesanan yang dikonfigurasi, pemrosesan operasi CDC, termasuk penghapusan, mungkin memerlukan waktu lebih lama daripada yang diperkirakan.
  • Operasi DELETE CDC dianggap akan diterapkan hanya jika stempel waktu upsert_stream_apply_watermark telah melewati stempel waktu saat Storage Write API mengalirkan operasi. Setelah operasi diterapkan, proses penghapusan data Google Cloud standar akan dimulai. Untuk informasi selengkapnya tentang stempel waktu upsert_stream_apply_watermark, lihat Memantau progres operasi upsert tabel.

Batasan

  • BigQuery CDC tidak melakukan penerapan kunci, sehingga kunci utama Anda harus unik.
  • Kunci utama tidak boleh lebih dari 16 kolom.
  • Tabel yang mendukung CDC tidak mendukung hal berikut:
  • Tabel yang mendukung CDC dan menjalankan tugas penggabungan runtime karena nilai max_staleness tabel terlalu rendah tidak dapat mendukung hal berikut:
  • Operasi ekspor BigQuery pada tabel yang mendukung CDC tidak mengekspor modifikasi baris yang baru-baru ini di-stream dan belum diterapkan oleh tugas latar belakang. Untuk mengekspor tabel lengkap, gunakan pernyataan EXPORT DATA.
  • Jika kueri Anda memicu penggabungan runtime pada tabel berpartisi, maka seluruh tabel akan dipindai, terlepas dari apakah kueri dibatasi untuk subset partisi tersebut atau tidak.
  • Jika Anda menggunakan edisi Standar, reservasi BACKGROUND tidak tersedia, sehingga penerapan modifikasi baris yang tertunda menggunakan model harga on demand. Namun, Anda dapat membuat kueri tabel yang mendukung CDC, apa pun edisi Anda.

Harga CDC BigQuery

BigQuery CDC menggunakan Storage Write API untuk penyerapan data, penyimpanan BigQuery untuk penyimpanan data, dan komputasi BigQuery untuk operasi modifikasi baris, yang semuanya dikenai biaya. Untuk mengetahui informasi harga, lihat harga BigQuery.

Perkirakan biaya CDC BigQuery

Selain praktik terbaik estimasi biaya BigQuery umum, memperkirakan biaya CDC BigQuery mungkin penting untuk alur kerja yang memiliki data dalam jumlah besar, konfigurasi max_staleness yang rendah, atau data yang sering berubah.

Harga penyerapan data BigQuery dan Harga penyimpanan BigQuery dihitung langsung berdasarkan jumlah data yang Anda serap dan simpan. Namun, harga komputasi BigQuery mungkin lebih sulit untuk diperkirakan, karena berkaitan dengan konsumsi resource komputasi yang digunakan untuk menjalankan tugas BigQuery CDC.

Tugas CDC BigQuery dibagi menjadi tiga kategori:

  • Tugas melamar di latar belakang: tugas yang berjalan di latar belakang pada interval reguler yang ditentukan oleh nilai max_staleness tabel. Tugas ini menerapkan modifikasi baris yang di-streaming yang baru-baru ini di-streaming ke tabel yang mendukung CDC.
  • Tugas kueri: Kueri GoogleSQL yang berjalan dalam jendela max_staleness dan hanya dibaca dari tabel dasar pengukuran CDC.
  • Tugas penggabungan runtime: tugas yang dipicu oleh kueri GoogleSQL ad hoc yang berjalan di luar periode max_staleness. Tugas ini harus melakukan penggabungan langsung pada tabel dasar pengukuran CDC dan modifikasi baris yang baru di-streaming saat runtime kueri.

Ketiga jenis tugas CDC BigQuery memanfaatkan pengelompokan BigQuery, tetapi hanya tugas kueri yang memanfaatkan partisi BigQuery. Tugas penerapan latar belakang dan tugas penggabungan runtime tidak dapat menggunakan partisi karena, saat menerapkan modifikasi baris yang baru-baru ini di-streaming, tidak ada jaminan untuk partisi tabel mana pembaruan dan yang baru di-streaming akan diterapkan. Dengan kata lain, tabel dasar pengukuran lengkap dibaca selama tugas melamar pekerjaan di latar belakang dan tugas penggabungan runtime. Memahami jumlah data yang sedang dibaca untuk melakukan operasi CDC sangat membantu dalam memperkirakan biaya total.

Jika jumlah data yang dibaca dari dasar pengukuran tabel tinggi, pertimbangkan untuk menggunakan model harga kapasitas BigQuery, yang tidak didasarkan pada jumlah data yang diproses.

Praktik terbaik biaya CDC BigQuery

Selain praktik terbaik biaya BigQuery umum, gunakan teknik berikut untuk mengoptimalkan biaya operasi BigQuery CDC:

Langkah selanjutnya