Mengubah data dengan bahasa pengolahan data (DML)

Bahasa pengolahan data (DML) BigQuery memungkinkan Anda mengupdate, memasukkan, dan menghapus data dari tabel BigQuery.

Anda dapat menjalankan pernyataan DML seperti halnya pernyataan SELECT, dengan kondisi berikut:

  • Anda harus menggunakan GoogleSQL. Untuk mengaktifkan GoogleSQL, lihat Mengalihkan dialek SQL.
  • Anda tidak dapat menentukan tabel tujuan untuk kueri.

Untuk mengetahui informasi selengkapnya tentang cara menghitung jumlah byte yang diproses oleh pernyataan DML, lihat Penghitungan ukuran kueri sesuai permintaan.

Batasan

  • Setiap pernyataan DML memulai transaksi implisit, yang berarti bahwa perubahan yang dibuat oleh pernyataan tersebut otomatis di-commit pada akhir setiap pernyataan DML yang berhasil.

  • Baris yang baru-baru ini ditulis menggunakan BigQuery Storage Write API (REST) tidak dapat diubah dengan bahasa pengolahan data (DML), seperti pernyataan UPDATE, DELETE, MERGE, atau TRUNCATE. Penulisan terbaru adalah penulisan yang terjadi dalam 30 menit terakhir. Semua baris lainnya dalam tabel tetap dapat diubah menggunakan pernyataan UPDATE, DELETE, MERGE, atau TRUNCATE. Data yang di-streaming dapat memerlukan waktu hingga 90 menit agar tersedia untuk operasi salin.

    Atau, baris yang baru-baru ini ditulis menggunakan Storage Write API (gRPC) dapat diubah menggunakan pernyataan UPDATE, DELETE, atau MERGE. Untuk mengetahui informasi selengkapnya, lihat Menggunakan bahasa pengolahan data (DML) dengan data yang baru-baru ini di-streaming.

  • Subkueri berkorelasi dalam when_clause, search_condition, merge_update_clause, atau merge_insert_clause tidak didukung untuk pernyataan MERGE.

  • Kueri yang berisi pernyataan DML tidak dapat menggunakan tabel karakter pengganti sebagai target kueri. Misalnya, tabel karakter pengganti dapat digunakan dalam klausa FROM dari kueri UPDATE, tetapi tabel karakter pengganti tidak dapat digunakan sebagai target operasi UPDATE.

Pernyataan DML

Bagian berikut menjelaskan berbagai jenis pernyataan DML dan cara menggunakannya.

Pernyataan INSERT

Gunakan pernyataan INSERT untuk menambahkan baris baru ke tabel yang ada. Contoh berikut menyisipkan baris baru ke dalam tabel dataset.Inventory dengan nilai yang ditentukan secara eksplisit.

INSERT dataset.Inventory (product, quantity)
VALUES('whole milk', 10),
      ('almond milk', 20),
      ('coffee beans', 30),
      ('sugar', 0),
      ('matcha', 20),
      ('oat milk', 30),
      ('chai', 5)

/+-------------------+----------+
 |      product      | quantity |
 +-------------------+----------+
 | almond milk       |       20 |
 | chai              |        5 |
 | coffee beans      |       30 |
 | matcha            |       20 |
 | oat milk          |       30 |
 | sugar             |        0 |
 | whole milk        |       10 |
 +-------------------+----------+/

Untuk mengetahui informasi selengkapnya tentang pernyataan INSERT, lihat INSERT pernyataan.

Pernyataan DELETE

Gunakan pernyataan DELETE untuk menghapus baris dalam tabel. Contoh berikut menghapus semua baris dalam tabel dataset.Inventory yang memiliki nilai quantity 0.

DELETE dataset.Inventory
WHERE quantity = 0

/+-------------------+----------+
 |      product      | quantity |
 +-------------------+----------+
 | almond milk       |       20 |
 | chai              |        5 |
 | coffee beans      |       30 |
 | matcha            |       20 |
 | oat milk          |       30 |
 | whole milk        |       10 |
 +-------------------+----------+/

Untuk menghapus semua baris dalam tabel, gunakan pernyataan TRUNCATE TABLE. Untuk mengetahui informasi selengkapnya tentang pernyataan DELETE, lihat pernyataan DELETE.

Pernyataan TRUNCATE

Gunakan pernyataan TRUNCATE untuk menghapus semua baris dari tabel, tetapi biarkan metadata tabel tetap utuh, termasuk skema, deskripsi, dan label tabel. Contoh berikut menghapus semua baris dari tabel dataset.Inventory.

TRUNCATE dataset.Inventory

Untuk menghapus baris tertentu dalam tabel, gunakan pernyataan DELETE. Untuk mengetahui informasi selengkapnya tentang pernyataan TRUNCATE, lihat pernyataan TRUNCATE.

Pernyataan UPDATE

Gunakan pernyataan UPDATE untuk mengupdate baris yang ada dalam tabel. Pernyataan UPDATE juga harus menyertakan kata kunci WHERE untuk menentukan kondisi. Contoh berikut mengurangi nilai quantity baris sebesar 10 untuk produk yang berisi string milk.

UPDATE dataset.Inventory
SET quantity = quantity - 10,
WHERE product LIKE '%milk%'

/+-------------------+----------+
 |      product      | quantity |
 +-------------------+----------+
 | almond milk       |       10 |
 | chai              |        5 |
 | coffee beans      |       30 |
 | matcha            |       20 |
 | oat milk          |       20 |
 | whole milk        |        0 |
 +-------------------+----------+/

Pernyataan UPDATE juga dapat menyertakan klausa FROM untuk menyertakan tabel gabungan. Untuk mengetahui informasi selengkapnya tentang pernyataan UPDATE, lihat pernyataan UPDATE.

Pernyataan MERGE

Pernyataan MERGE menggabungkan operasi INSERT, UPDATE, dan DELETE menjadi satu pernyataan dan menjalankan operasi secara atomik untuk menggabungkan data dari satu tabel ke tabel lain. Untuk mengetahui informasi selengkapnya dan contoh tentang MERGE pernyataan, lihat MERGE pernyataan.

Tugas serentak

BigQuery mengelola konkurensi pernyataan DML yang menambahkan, mengubah, atau menghapus baris dalam tabel.

Konkurensi DML INSERT

Selama periode 24 jam, 1.500 pernyataan INSERT pertama akan langsung dijalankan setelah dikirimkan. Setelah batas ini tercapai, konkurensi pernyataan INSERT yang menulis ke tabel akan dibatasi hingga 10. Pernyataan INSERT tambahan akan ditambahkan ke antrean PENDING. Hingga 100 pernyataan INSERT dapat diantrekan terhadap tabel pada waktu tertentu. Saat pernyataan INSERT selesai, pernyataan INSERT berikutnya akan dihapus dari antrean dan dijalankan.

Jika Anda harus menjalankan pernyataan DML INSERT lebih sering, pertimbangkan untuk melakukan streaming data ke tabel menggunakan Storage Write API (gRPC).

Konkurensi DML UPDATE, DELETE, MERGE

Pernyataan DML UPDATE, DELETE, dan MERGE disebut pernyataan DML bermutasi. Jika Anda mengirimkan satu atau beberapa pernyataan DML bermutasi pada tabel saat tugas DML bermutasi lainnya masih berjalan (atau tertunda), BigQuery akan menjalankan hingga 2 pernyataan secara serentak, setelah itu hingga 20 pernyataan akan diantrekan sebagai PENDING. Saat tugas yang sebelumnya berjalan selesai, tugas tertunda berikutnya akan dihapus dari antrean dan dijalankan. Pernyataan DML bermutasi yang diantrekan berbagi antrean per tabel dengan panjang maksimum 20. Pernyataan tambahan yang melebihi panjang antrean maksimum untuk setiap tabel akan gagal dengan pesan error: Resources exceeded during query execution: Too many DML statements outstanding against table PROJECT_ID:DATASET.TABLE, limit is 20.

Tugas DML prioritas interaktif yang diantrekan selama lebih dari 7 jam akan gagal dengan pesan error berikut:

DML statement has been queued for too long

Konflik pernyataan DML

Pernyataan DML bermutasi yang berjalan serentak pada tabel menyebabkan konflik pernyataan DML saat pernyataan mencoba mengubah partisi yang sama. Pernyataan akan berhasil selama tidak mengubah partisi yang sama. BigQuery mencoba menjalankan kembali pernyataan yang gagal hingga tiga kali.

  • Pernyataan DML INSERT yang menyisipkan baris ke tabel tidak akan berkonflik dengan pernyataan DML lain yang berjalan serentak.

  • Pernyataan DML MERGE tidak berkonflik dengan pernyataan DML lain yang berjalan serentak selama pernyataan hanya menyisipkan baris dan tidak menghapus atau mengupdate baris yang ada. Hal ini dapat mencakup pernyataan MERGE dengan klausa UPDATE atau DELETE, selama klausa tersebut tidak dipanggil saat kueri berjalan.

DML terperinci

DML terperinci adalah peningkatan performa yang dirancang untuk mengoptimalkan eksekusi pernyataan UPDATE, DELETE, dan MERGE (juga dikenal sebagai pernyataan DML bermutasi).

Pertimbangan performa

Tanpa DML terperinci yang diaktifkan, mutasi DML dilakukan di tingkat grup file, yang dapat menyebabkan penulisan ulang data yang tidak efisien, terutama untuk mutasi yang jarang. Hal ini dapat menyebabkan penggunaan slot tambahan dan waktu eksekusi yang lebih lama.

DML terperinci adalah peningkatan performa yang dirancang untuk mengoptimalkan pernyataan DML bermutasi ini dengan memperkenalkan pendekatan yang lebih terperinci yang bertujuan untuk mengurangi jumlah data yang perlu ditulis ulang di tingkat grup file. Pendekatan ini dapat mengurangi waktu pemrosesan, I/O, dan slot yang digunakan untuk tugas DML bermutasi secara signifikan.

Ada beberapa pertimbangan performa yang perlu diperhatikan saat menggunakan DML terperinci:

  • Operasi DML terperinci memproses data yang dihapus dengan pendekatan hybrid yang mendistribusikan biaya penulisan ulang di berbagai mutasi tabel. Setiap operasi DML dapat memproses sebagian data yang dihapus, lalu mengalihkan pemrosesan data yang dihapus yang tersisa ke proses pembersihan sampah memori latar belakang. Untuk mengetahui informasi selengkapnya, lihat pertimbangan data yang dihapus.
  • Tabel dengan operasi DML bermutasi yang sering mungkin mengalami peningkatan latensi untuk kueri SELECT dan tugas DML berikutnya. Untuk mengevaluasi dampak pengaktifan fitur ini, lakukan benchmark performa urutan operasi DML yang realistis dan pembacaan berikutnya.
  • Mengaktifkan DML terperinci tidak akan mengurangi jumlah byte yang dipindai dari pernyataan DML bermutasi itu sendiri.

Mengaktifkan DML terperinci

Untuk mengaktifkan DML terperinci, tetapkan opsi tabel enable_fine_grained_mutationske TRUE saat Anda menjalankan CREATE TABLE atau ALTER TABLE pernyataan DDL.

Untuk membuat tabel baru dengan DML terperinci, gunakan pernyataan CREATE TABLE:

CREATE TABLE mydataset.mytable (
  product STRING,
  inventory INT64)
OPTIONS(enable_fine_grained_mutations = TRUE);

Untuk mengubah tabel yang ada dengan DML terperinci, gunakan pernyataan ALTER TABLE:

ALTER TABLE mydataset.mytable
SET OPTIONS(enable_fine_grained_mutations = TRUE);

Untuk mengubah semua tabel yang ada dalam set data dengan DML terperinci, gunakan pernyataan ALTER TABLE: 

FOR record IN
 (SELECT CONCAT(table_schema, '.', table_name) AS table_path
 FROM mydataset.INFORMATION_SCHEMA.TABLES)
DO
 EXECUTE IMMEDIATE
   "ALTER TABLE " || record.table_path || " SET OPTIONS(enable_fine_grained_mutations = TRUE)";
END FOR;

Setelah opsi enable_fine_grained_mutations ditetapkan ke TRUE, DML bermutasi pernyataan akan dijalankan dengan kemampuan DML terperinci yang diaktifkan dan menggunakan sintaksis pernyataan DML yang ada.

Untuk menentukan apakah tabel telah diaktifkan dengan DML terperinci, buat kueri tampilan INFORMATION_SCHEMA.TABLES. Contoh berikut memeriksa tabel mana dalam set data yang telah diaktifkan dengan fitur ini:

SELECT
  table_schema AS datasetId,
  table_name AS tableId,
  is_fine_grained_mutations_enabled
FROM
  DATASET_NAME.INFORMATION_SCHEMA.TABLES;

Ganti DATASET_NAME dengan nama set data tempat untuk memeriksa apakah ada tabel yang mengaktifkan DML terperinci.

Menonaktifkan DML terperinci

Untuk menonaktifkan DML terperinci dari tabel yang ada, gunakan pernyataan ALTER TABLE.

ALTER TABLE mydataset.mytable
SET OPTIONS(enable_fine_grained_mutations = FALSE);

Saat menonaktifkan DML terperinci, mungkin perlu waktu beberapa saat agar semua data yang dihapus dapat diproses sepenuhnya, lihat Pertimbangan data yang dihapus. Akibatnya, batasan DML terperinci mungkin tetap ada hingga hal ini terjadi.

Harga

Mengaktifkan DML terperinci untuk tabel dapat dikenai biaya tambahan. Biaya ini mencakup hal berikut:

  • Biaya penyimpanan BigQuery untuk menyimpan metadata mutasi tambahan yang terkait dengan operasi DML terperinci Biaya penyimpanan sebenarnya bergantung pada jumlah data yang diubah, tetapi untuk sebagian besar situasi, biaya ini diperkirakan tidak signifikan dibandingkan dengan ukuran tabel itu sendiri.
  • Biaya komputasi BigQuery untuk memproses data yang dihapus menggunakan tugas pembersihan sampah memori yang dialihkan, dan SELECT berikutnya yang memproses metadata penghapusan tambahan yang belum dibersihkan.

Anda dapat menggunakan pemesanan BigQuery guna mengalokasikan resource komputasi BigQuery khusus untuk memproses tugas data yang dihapus yang dialihkan. Dengan pemesanan, Anda dapat menetapkan batas biaya untuk melakukan operasi ini. Pendekatan ini sangat berguna, dan sering direkomendasikan, untuk tabel yang sangat besar dengan operasi DML bermutasi terperinci yang sering, yang akan menimbulkan biaya sesuai permintaan yang tinggi karena banyaknya byte yang diproses saat melakukan setiap tugas pemrosesan data yang dihapus yang dialihkan.

Tugas pemrosesan data yang dihapus yang dialihkan DML terperinci dianggap sebagai tugas latar belakang dan memerlukan penggunaan jenis penetapan pemesanan BACKGROUND, bukan jenis penetapan pemesanan QUERY. Project yang melakukan operasi DML terperinci tanpa penetapan BACKGROUND menggunakan harga sesuai permintaan untuk memproses tugas data yang dihapus yang dialihkan.

Operasi Harga sesuai permintaan Harga berdasarkan kapasitas
Pernyataan DML bermutasi Gunakan ukuran DML standar untuk menentukan penghitungan byte yang dipindai sesuai permintaan.

Mengaktifkan DML terperinci tidak akan mengurangi jumlah byte yang dipindai dari pernyataan DML itu sendiri.

 Gunakan slot yang ditetapkan dengan jenis QUERY pada waktu proses pernyataan.
Tugas pemrosesan data yang dihapus yang dialihkan Gunakan ukuran DML standar untuk menentukan penghitungan byte yang dipindai sesuai permintaan saat tugas pemrosesan data yang dihapus dijalankan. Gunakan slot yang ditetapkan dengan jenis BACKGROUND saat tugas pemrosesan data yang dihapus dijalankan.

Pertimbangan data yang dihapus

Operasi DML terperinci menggunakan pendekatan hybrid untuk mengelola data yang dihapus, menggabungkan pemrosesan inline dengan pembersihan sampah memori yang dialihkan untuk mendistribusikan biaya penulisan ulang dan mengoptimalkan performa di beberapa pernyataan DML bermutasi yang dikeluarkan terhadap tabel.

Selama eksekusi pernyataan DML bermutasi, BigQuery mencoba melakukan sebagian pembersihan sampah memori yang relevan dari pernyataan DML sebelumnya secara inline. Data yang dihapus yang tidak ditangani secara inline akan dialihkan ke proses latar belakang untuk pembersihan nanti.

Project yang melakukan operasi DML terperinci dengan penetapan BACKGROUND memproses tugas pembersihan sampah memori yang dialihkan menggunakan slot. Pemrosesan data yang dihapus tunduk pada ketersediaan resource pemesanan yang dikonfigurasi. Jika tidak tersedia cukup resource dalam pemesanan yang dikonfigurasi, pemrosesan operasi pembersihan sampah memori yang dialihkan mungkin memerlukan waktu lebih lama daripada yang diperkirakan.

Project yang melakukan operasi DML terperinci dengan menggunakan harga sesuai permintaan, atau tanpa penetapan BACKGROUND memproses tugas pembersihan sampah memori yang dialihkan menggunakan resource BigQuery internal dan dikenai biaya dengan tarif harga sesuai permintaan. Untuk mengetahui informasi selengkapnya, lihat Harga.

Waktu tugas pembersihan sampah memori yang dialihkan ditentukan oleh frekuensi aktivitas DML pada tabel dan ketersediaan resource, jika menggunakan penetapan BACKGROUND:

  • Untuk tabel dengan operasi DML bermutasi berkelanjutan, setiap DML memproses sebagian workload pembersihan sampah memori untuk memastikan performa baca dan tulis yang konsisten, dan sebagai hasilnya, pembersihan sampah memori diproses secara rutin saat DML berikutnya dijalankan.
  • Jika tidak ada aktivitas DML berikutnya yang terjadi pada tabel, pembersihan sampah memori yang dialihkan akan otomatis dipicu setelah data yang dihapus mencapai usia 5 hari.
  • Dalam kasus yang jarang terjadi, pemrosesan data yang dihapus sepenuhnya mungkin memerlukan waktu lebih lama.

Untuk mengidentifikasi tugas pemrosesan data yang dihapus yang dialihkan DML terperinci, buat kueri INFORMATION_SCHEMA.JOBS tampilan: 

SELECT
  *
FROM
  region-us.INFORMATION_SCHEMA.JOBS
WHERE
  job_id LIKE "%fine_grained_mutation_garbage_collection%"

Batasan

Tabel yang diaktifkan dengan DML terperinci tunduk pada batasan berikut:

  • Untuk tabel besar dengan partisi yang sering diubah yang melebihi 2 TB, DML terperinci tidak direkomendasikan. Tabel ini mungkin mengalami tekanan memori tambahan untuk kueri berikutnya, yang dapat menyebabkan latensi baca atau error kueri tambahan.
  • Hanya satu pernyataan DML bermutasi yang dapat dijalankan dalam satu waktu pada tabel yang mengaktifkan DML terperinci. Tugas berikutnya diantrekan sebagai PENDING. Untuk mengetahui informasi selengkapnya tentang perilaku konkurensi DML bermutasi, lihat Konkurensi DML UPDATE, DELETE, MERGE.
  • Tabel yang diaktifkan dengan DML terperinci tidak dapat menghapus partisi satu per satu atau menimpa. Untuk menghapus atau mengganti data dalam partisi, Anda harus menggunakan pernyataan DML bermutasi, seperti DELETE, UPDATE, MERGE, atau TRUNCATE.
  • Anda tidak dapat menggunakan metode tabledata.list untuk membaca konten dari tabel yang mengaktifkan DML terperinci. Sebagai gantinya, buat kueri tabel dengan pernyataan SELECT untuk membaca rekaman tabel.
  • Tabel yang diaktifkan dengan DML terperinci tidak dapat dilihat pratinjaunya menggunakan konsol BigQuery.
  • Anda tidak dapat menyalin tabel dengan DML terperinci diaktifkan setelah menjalankan pernyataan UPDATE, DELETE, atau MERGE.
  • Anda tidak dapat membuat snapshot tabel atau clone tabel dari tabel yang mengaktifkan DML terperinci setelah menjalankan pernyataan UPDATE, DELETE, atau MERGE.
  • Anda tidak dapat mengganti nama tabel yang mengaktifkan DML terperinci setelah menjalankan pernyataan UPDATE, DELETE, atau MERGE.
  • Replikasi lintas-region tidak didukung untuk tabel yang mengaktifkan DML terperinci. Meskipun sistem memblokir pembuatan set data yang direplikasi yang sudah berisi tabel tersebut, sistem tidak memblokir pengaktifan DML terperinci pada tabel dalam set data yang direplikasi yang ada. Namun, tindakan ini dapat menyebabkan hasil kueri yang salah di sekunder untuk tabel yang mengaktifkan DML terperinci.
  • Pernyataan DML yang dijalankan dalam transaksi multi-pernyataan tidak dioptimalkan dengan DML terperinci.
  • Anda tidak dapat mengaktifkan DML terperinci pada tabel sementara yang dibuat dengan pernyataan CREATE TEMP TABLE.
  • Metadata yang ditampilkan dalam tampilan INFORMATION_SCHEMA.TABLE_STORAGE dan tampilan INFORMATION_SCHEMA.PARTITIONS dapat sementara menyertakan data yang baru-baru ini dihapus menggunakan DML terperinci hingga tugas pembersihan sampah memori latar belakang selesai.

Praktik terbaik

Untuk performa terbaik, Google merekomendasikan pola berikut:

  • Hindari mengirimkan sejumlah besar update atau penyisipan baris individual. Sebagai gantinya, kelompokkan operasi DML jika memungkinkan. Untuk mengetahui informasi selengkapnya, lihat Pernyataan DML yang memperbarui atau menyisipkan baris tunggal.

  • Jika update atau penghapusan umumnya terjadi pada data lama, atau dalam rentang tanggal tertentu, pertimbangkan untuk mempartisi tabel Anda. Pembuatan partisi memastikan bahwa perubahan terbatas pada partisi tertentu dalam tabel.

  • Hindari mempartisi tabel jika jumlah data di setiap partisi kecil dan setiap update mengubah sebagian besar partisi.

  • Jika Anda sering mengupdate baris tempat satu atau beberapa kolom berada dalam rentang nilai yang sempit, pertimbangkan untuk menggunakan tabel bercluster. Clustering memastikan bahwa perubahan terbatas pada kumpulan blok tertentu, sehingga mengurangi jumlah data yang perlu dibaca dan ditulis. Berikut adalah contoh pernyataan UPDATE yang memfilter rentang nilai kolom:

    UPDATE mydataset.mytable
SET string_col = 'some string'
WHERE id BETWEEN 54 AND 75;

    Berikut adalah contoh serupa yang memfilter daftar kecil nilai kolom:

    UPDATE mydataset.mytable
SET string_col = 'some string'
WHERE id IN (54, 57, 60);

    Pertimbangkan untuk melakukan clustering pada kolom id dalam kasus ini.

  • Jika Anda memerlukan fungsi OLTP, pertimbangkan untuk menggunakan kueri gabungan Cloud SQL, yang memungkinkan BigQuery membuat kueri data yang berada di Cloud SQL.

  • Untuk mengatasi dan mencegah error kuota Too many DML statements outstanding against table, ikuti panduan untuk error ini di halaman Pemecahan Masalah BigQuery.

Untuk mengetahui praktik terbaik guna mengoptimalkan performa kueri, lihat Pengantar cara mengoptimalkan performa kueri.

Langkah berikutnya