Membuat snapshot tabel dengan kueri terjadwal

Dokumen ini menjelaskan cara membuat snapshot bulanan dari satu tabel menggunakan akun layanan yang menjalankan kueri DDL terjadwal. Dokumen ini akan memandu Anda melalui contoh berikut:

  1. Di project PROJECT, buat akun layanan bernama snapshot-bot.
  2. Beri akun layanan snapshot-bot izin yang diperlukan untuk mengambil snapshot tabel dari tabel TABLE, yang terletak di set data DATASET, dan simpan snapshot tabelnya dalam set data BACKUP.
  3. Tulis kueri yang membuat snapshot bulanan dari tabel TABLE tabel dan menempatkannya di set data BACKUP. Karena Anda tidak dapat menimpa snapshot tabel yang ada, snapshot tabel tersebut harus memiliki nama yang unik. Untuk mencapai hal ini, kueri menambahkan tanggal saat ini ke nama snapshot tabel; misalnya, TABLE_20220521. Snapshot tabel akan habis masa berlakunya setelah 40 hari.
  4. Jadwalkan akun layanan snapshot-bot untuk menjalankan kueri pada hari pertama setiap bulan.

Dokumen ini ditujukan untuk pengguna yang sudah memahami BigQuery dan snapshot Tabel BigQuery.

Izin dan peran

Bagian ini menjelaskan Izin Identity and Access Management (IAM) Anda perlu membuat akun layanan dan menjadwalkan kueri, dan peran IAM yang telah ditetapkan yang memberikan izin akses tersebut.

Izin

Untuk menggunakan akun layanan, Anda memerlukan izin berikut:

Izin Resource Jenis aset
iam.serviceAccounts.* PROJECT Project

Untuk menjadwalkan kueri, Anda memerlukan izin berikut:

Izin Resource Jenis aset
bigquery.jobs.create PROJECT Project

Peran

Peran yang telah ditetapkan sebelumnya dan memberikan izin yang diperlukan agar dapat bekerja dengan akun layanan adalah sebagai berikut:

Peran Resource Jenis aset
Apa pun dari yang berikut ini:

roles/iam.serviceAccountAdmin
roles/editor
roles/owner 		PROJECT Project

Peran BigQuery yang telah ditentukan dan memberikan izin yang diperlukan untuk menjadwalkan kueri adalah sebagai berikut:

Peran Resource Jenis aset
Apa pun dari yang berikut ini:

roles/bigquery.user
roles/bigquery.jobuser
roles/bigquery.admin 		PROJECT Project

Buat akun layanan snapshot-bot:

Ikuti langkah-langkah berikut untuk membuat snapshot-bot akun layanan dan memberinya izin yang diperlukan untuk menjalankan kueri di project PROJECT:

Konsol

  1. Di Konsol Google Cloud, buka halaman Akun layanan:

    Buka Akun Layanan

  2. Pilih project PROJECT.

  3. Buat akun layanan snapshot-bot:

    1. Klik Create service account.

    2. Dalam kolom Nama akun layanan, masukkan snapshot-bot.

    3. Klik Create and Continue.

  4. Berikan izin yang diperlukan akun layanan untuk menjalankan tugas BigQuery:

    1. Di bagian Berikan akun layanan ini akses ke project pilih peran BigQuery User.

    2. Klik Done.

BigQuery membuat akun layanan dengan alamat email snapshot-bot@PROJECT.iam.gserviceaccount.com.

Untuk memverifikasi bahwa BigQuery membuat akun layanan dengan izin yang Anda tentukan, ikuti langkah-langkah berikut:

Konsol

Pastikan BigQuery telah membuat akun layanan:

  1. Di Konsol Google Cloud, buka halaman Akun layanan:

    Buka Akun Layanan

  2. Pilih project PROJECT.

  3. Klik snapshot-bot@PROJECT.iam.gserviceaccount.com.

  4. Verifikasi bahwa pesan Status akun layanan menunjukkan bahwa akun layanan Anda aktif.

Pastikan BigQuery telah memberi akun layanan Anda izin yang diperlukan untuk menjalankan kueri:

  1. Di Konsol Google Cloud, buka halaman Mengelola resource:

    Buka Mengelola Resource

  2. Klik PROJECT.

  3. Klik Tampilkan Panel Info.

  4. Di tab Izin, luaskan node BigQuery User.

  5. Pastikan akun layanan snapshot-bot Anda tercantum.

Memberikan izin ke akun layanan

Bagian ini menjelaskan cara memberikan izin yang diperlukan akun layanan snapshot-bot untuk membuat snapshot tabel dari tabel DATASET.TABLE dalam set data BACKUP.

Izin untuk mengambil snapshot tabel dasar

Untuk memberi akun layanan snapshot-bot izin yang diperlukan untuk mengambil snapshot tabel DATASET.TABLE, ikuti langkah-langkah berikut:

Konsol

  1. Di Konsol Google Cloud, buka halaman BigQuery.

    Buka BigQuery

  2. Di panel Explorer, luaskan node project PROJECT.

  3. Luaskan node set data DATASET.

  4. Pilih tabel TABLE.

  5. Klik Bagikan. Panel Bagikan akan terbuka.

  6. Klik Tambahkan Akun Utama. Panel Berikan akses akan terbuka.

  7. Di Akun utama baru, masukkan alamat email akun layanan: snapshot-bot@PROJECT.iam.gserviceaccount.com.

  8. Dari dropdown Pilih peran, pilih peran BigQuery Data Editor.

  9. Klik Simpan.

  10. Di panel Berbagi, luaskan node Editor Data BigQuery dan verifikasi bahwa akun layanan snapshot-bot@PROJECT.iam.gserviceaccount.com telah tercantum.

  11. Klik Close.

bq

  1. Di Konsol Google Cloud, aktifkan Cloud Shell:

    Aktifkan Cloud Shell

  2. Masukkan perintah: bq add-iam-policy-binding berikut:

    bq add-iam-policy-binding \
--member=serviceAccount:snapshot-bot@PROJECT.iam.gserviceaccount.com \
--role=roles/bigquery.dataEditor DATASET.TABLE

BigQuery mengonfirmasi bahwa binding kebijakan baru telah ditambahkan.

Izin untuk membuat tabel di set data tujuan

Berikan izin yang diperlukan akun layanan snapshot-bot untuk membuat snapshot tabel di set data BACKUP sebagai berikut:

Konsol

  1. Di Konsol Google Cloud, buka halaman BigQuery.

    Buka BigQuery

  2. Di panel Explorer, luaskan node project PROJECT.

  3. Klik menu untuk node set data BACKUP, lalu pilih Buka.

  4. Klik Bagikan Set Data. Panel izin Set Data akan terbuka.

  5. Di kolom Tambahkan anggota, masukkan alamat email akun layanan: snapshot-bot@PROJECT.iam.gserviceaccount.com.

  6. Dari dropdown Pilih peran, pilih peran BigQuery Data Owner.

  7. Klik Tambahkan.

  8. Di panel Izin set data, pastikan akun layanan snapshot-bot@PROJECT.iam.gserviceaccount.com tercantum di node BigQuery Data Owner.

  9. Klik Done.

Akun layanan snapshot-bot Anda kini memiliki peran IAM berikut untuk resource sebagai berikut:

Peran Resource Jenis aset Tujuan
Editor Data BigQuery PROJECT:DATASET.TABLE Tabel Mengambil snapshot tabel TABLE.
BigQuery Data Owner PROJECT:BACKUP Set data Membuat dan menghapus snapshot tabel di set data BACKUP.
BigQuery User PROJECT Project Jalankan kueri terjadwal yang membuat snapshot tabel.

Peran ini memberikan izin yang diperlukan akun layanan snapshot-bot untuk menjalankan kueri yang membuat snapshot tabel DATASET.TABLE dan menempatkan snapshot tabel di set data BACKUP.

Menulis kueri multi-pernyataan

Bagian ini menjelaskan cara menulis kueri multi-pernyataan yang membuat snapshot tabel dari tabel DATASET.TABLE dengan menggunakan pernyataan DDL CREATE SNAPSHOT TABLE. Snapshot disimpan dalam set data BACKUP dan habis masa berlakunya setelah satu hari.

-- Declare variables
DECLARE snapshot_name STRING;
DECLARE expiration TIMESTAMP;
DECLARE query STRING;

-- Set variables
SET expiration = DATE_ADD(current_timestamp(), INTERVAL 1 DAY);
SET snapshot_name = CONCAT(
                      "BACKUP.TABLE_",
                      FORMAT_DATETIME('%Y%m%d', current_date()));

-- Construct the query to create the snapshot
SET query = CONCAT(
              "CREATE SNAPSHOT TABLE ",
              snapshot_name,
              " CLONE mydataset.mytable OPTIONS(expiration_timestamp = TIMESTAMP '",
              expiration,
              "');");

-- Run the query
EXECUTE IMMEDIATE query;

Menjadwalkan kueri bulanan

Jadwalkan kueri Anda agar berjalan pada pukul 05.00 pada hari pertama setiap bulan sebagai berikut:

bq

  1. Di Konsol Google Cloud, aktifkan Cloud Shell:

    Aktifkan Cloud Shell

  2. Masukkan perintah bq query berikut:

    bq query --use_legacy_sql=false --display_name="Monthly snapshots of the TABLE table" \
--location="us" --schedule="1 of month 05:00" \
--project_id=PROJECT \
'DECLARE snapshot_name STRING;
DECLARE expiration TIMESTAMP;
DECLARE query STRING;
SET expiration = DATE_ADD(@run_time, INTERVAL 40 DAY);
SET snapshot_name = CONCAT("BACKUP.TABLE_",
  FORMAT_DATETIME("%Y%m%d", @run_date));
SET query = CONCAT("CREATE SNAPSHOT TABLE ", snapshot_name,
  " CLONE PROJECT.DATASET.TABLE OPTIONS(expiration_timestamp=TIMESTAMP \"",
  expiration, "\");");
EXECUTE IMMEDIATE query;'

  3. BigQuery menjadwalkan kueri.

Kueri multi-pernyataan dalam perintah alat command line bq berbeda dengan kueri yang Anda jalankan di Konsol Google Cloud sebagai berikut:

  • Kueri alat command line bq menggunakan @run_date, bukan current_date(). Dalam kueri terjadwal, parameter @run_date berisi tanggal saat ini. Namun, dalam kueri interaktif, parameter @run_date tidak didukung. Anda dapat menggunakan current_date() bukan @run_date untuk menguji kueri interaktif sebelum menjadwalkannya.
  • Kueri alat command line bq menggunakan @run_time, bukan current_timestamp(), untuk alasan serupa—parameter @run_time tidak didukung dalam kueri interaktif, tetapi current_timestamp() dapat digunakan sebagai pengganti @run_time untuk menguji kueri interaktif.
  • Kueri alat command line bq menggunakan garis miring dan tanda kutip ganda \", bukan tanda kutip tunggal ' karena tanda kutip tunggal digunakan untuk mencakup kueri.

Mengonfigurasi akun layanan untuk menjalankan kueri terjadwal

Kueri saat ini dijadwalkan untuk dijalankan menggunakan kredensial Anda. Perbarui kueri terjadwal Anda agar berjalan dengan kredensial akun layanan snapshot-bot sebagai berikut:

  1. Jalankan perintah bq ls untuk mendapatkan identitas tugas kueri terjadwal:

    bq ls --transfer_config=true --transfer_location=us

    Outputnya terlihat mirip dengan berikut ini:

    name displayName dataSourceId state
    projects/12345/locations/us/transferConfigs/12345 Monthly snapshots of the TABLE table scheduled_query RUNNING

  2. Dengan menggunakan ID di kolom name, jalankan perintah bq update berikut:

    bq update --transfer_config --update_credentials \
--service_account_name=snapshot-bot@PROJECT.iam.gserviceaccount.com \
projects/12345/locations/us/transferConfigs/12345

Cloud Shell mengonfirmasi bahwa kueri terjadwal telah berhasil diperbarui.

Periksa hasil kerja Anda:

Bagian ini menjelaskan cara memverifikasi bahwa kueri Anda dijadwalkan dengan benar, Cara melihat apakah ada error saat kueri dijalankan, dan cara memverifikasi bahwa snapshot bulanan sedang dibuat.

Melihat kueri terjadwal

Untuk memverifikasi bahwa BigQuery telah menjadwalkan kueri snapshot tabel bulanan Anda, ikuti langkah-langkah berikut:

Konsol

  1. Di Konsol Google Cloud, buka halaman Kueri terjadwal:

    Buka Kueri terjadwal

  2. Klik Snapshot bulanan tabel TABLE.

  3. Klik Konfigurasi.

  4. Pastikan String kueri berisi kueri Anda, dan kueri tersebut dijadwalkan untuk berjalan pada hari pertama setiap bulan.

Melihat histori pengoperasian kueri terjadwal

Setelah kueri terjadwal berjalan, Anda dapat melihat apakah kueri terjadwal berhasil dijalankan sebagai berikut:

Konsol

  1. Di Konsol Google Cloud, buka halaman Kueri terjadwal:

    Buka Kueri terjadwal

  2. Klik deskripsi kueri, Snapshot bulanan tabel TABLE.

  3. Klik Histori dijalankan.

Anda dapat melihat tanggal dan waktu saat kueri dijalankan, apakah kueri berhasil dijalankan, dan jika tidak, error apa yang terjadi. Untuk melihat detail selengkapnya tentang operasi tertentu, klik barisnya dalam tabel Histori dijalankan. Panel Detail operasi akan menampilkan detail tambahan.

Melihat snapshot tabel

Untuk memastikan snapshot tabel dibuat, ikuti langkah-langkah berikut:

Konsol

  1. Di Konsol Google Cloud, buka halaman BigQuery:

    Buka BigQuery

  2. Di panel Penjelajah, buka set data BACKUP dan verifikasi bahwa snapshot TABLE_YYYYMMDD telah dibuat, dengan YYYYMMDD adalah hari pertama setiap bulan.

    Contoh:

    • TABLE_20220601
    • TABLE_20220701
    • TABLE_20220801

Langkah berikutnya