Mengubah data dengan bahasa manipulasi data (DML)

GoogleSQL adalah nama baru untuk Google Standard SQL. Nama baru dengan kehebatan dialek SQL yang masih sama.

Bahasa manipulasi data (DML) BigQuery memungkinkan Anda mengupdate, menyisipkan, 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 daftar pernyataan DML BigQuery dan contoh cara menggunakannya, lihat Pernyataan bahasa manipulasi data di GoogleSQL. Untuk mengetahui informasi selengkapnya tentang cara menghitung jumlah byte yang diproses oleh pernyataan DML, lihat Penghitungan ukuran kueri sesuai permintaan.

Tugas serentak

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

Catatan: Pernyataan DML tunduk pada batas kapasitas seperti kecepatan maksimum penulisan tabel. Anda mungkin mencapai batas kapasitas jika mengirimkan tugas dalam jumlah besar ke tabel sekaligus. Tarif ini tidak membatasi jumlah total pernyataan DML yang dapat dijalankan. Jika Anda mendapatkan pesan error yang menyatakan bahwa Anda telah melampaui batas kapasitas, coba lagi operasi menggunakan backoff eksponensial di antara percobaan ulang.

Konkurensi DML INSERT

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

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

Konkurensi DML UPDATE, DELETE, MERGE

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

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

DML statement has been queued for too long

Konflik pernyataan DML

Memutasi pernyataan DML yang berjalan secara serentak pada tabel menyebabkan konflik pernyataan DML saat pernyataan mencoba memutasi partisi yang sama. Pernyataan berhasil selama tidak mengubah partisi yang sama. BigQuery mencoba menjalankan ulang pernyataan yang gagal hingga tiga kali.

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

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

DML terperinci

Pratinjau

Fitur ini tunduk pada "Persyaratan Penawaran Pra-GA" di bagian Persyaratan Layanan Umum dalam Persyaratan Khusus Layanan. Anda dapat memproses data pribadi untuk fitur ini sebagaimana dijelaskan dalam Adendum Pemrosesan Data Cloud, dengan tunduk pada kewajiban dan batasan yang dijelaskan dalam perjanjian tempat Anda mengakses Google Cloud. Fitur pra-GA tersedia "sebagaimana adanya" dan mungkin memiliki dukungan terbatas. Untuk mengetahui informasi selengkapnya, lihat deskripsi tahap peluncuran.

Catatan: Untuk memberikan masukan atau meminta dukungan terkait fitur ini, kirim email ke [e-mail].

DML terperinci adalah peningkatan performa yang dirancang untuk mengoptimalkan eksekusi pernyataan UPDATE, DELETE, dan MERGE (juga dikenal sebagai pernyataan DML yang bermutasi). Tanpa mengaktifkan DML terperinci, mutasi dilakukan di tingkat grup file, yang dapat menyebabkan penulisan ulang data yang tidak efisien. DML terperinci memperkenalkan pendekatan yang lebih terperinci yang bertujuan untuk mengurangi jumlah data yang perlu ditulis ulang, dan untuk mengurangi penggunaan slot secara keseluruhan.

Untuk menyatakan minat dalam mendaftarkan project ke pratinjau DML terperinci, isi formulir pendaftaran DML terperinci BigQuery. Project didaftarkan secara selektif berdasarkan evaluasi workload Anda.

Mengaktifkan DML terperinci

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

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);

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

Untuk menonaktifkan DML terperinci pada tabel, tetapkan enable_fine_grained_mutations ke FALSE menggunakan pernyataan DDL ALTER TABLE.

Harga

Mengaktifkan DML terperinci untuk tabel dapat menimbulkan biaya penyimpanan BigQuery tambahan untuk menyimpan metadata mutasi tambahan yang terkait dengan operasi DML terperinci. Biaya sebenarnya bergantung pada jumlah data yang diubah, tetapi untuk sebagian besar situasi, biaya ini diperkirakan tidak signifikan jika dibandingkan dengan ukuran tabel itu sendiri.

Project yang dikonfigurasi untuk menggunakan reservations menggunakan slot untuk memproses pernyataan DML terperinci, termasuk pemrosesan latar belakang metadata tabel atau mutasi.

Pertimbangan data yang dihapus

Operasi DML terperinci memproses data yang dihapus secara offline.

Project yang melakukan operasi DML terperinci tanpa proses penetapan BACKGROUND akan menghapus data menggunakan harga on-demand. Dalam hal ini, pemrosesan data yang dihapus dilakukan secara rutin menggunakan resource BigQuery internal.

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

Batasan

Tabel yang diaktifkan dengan DML terperinci tunduk pada batasan berikut:

• Anda tidak dapat menggunakan metode tabledata.list untuk membaca konten dari tabel dengan DML terperinci yang diaktifkan. Sebagai gantinya, gunakan Storage Read API untuk membaca data tabel menggunakan API.
• Anda tidak dapat membuat snapshot tabel atau clone tabel dari tabel dengan DML terperinci yang diaktifkan.
• Anda tidak dapat mengaktifkan DML terperinci pada tabel di set data yang direplikasi, dan Anda tidak dapat mereplikasi set data yang berisi tabel dengan DML terperinci yang diaktifkan.
• Pernyataan DML yang dieksekusi dalam transaksi multi-pernyataan tidak dioptimalkan dengan DML terperinci.

Praktik terbaik

Untuk performa terbaik, Google merekomendasikan pola berikut:

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

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

• Hindari membuat partisi tabel jika jumlah data di setiap partisi kecil dan setiap pembaruan mengubah sebagian besar partisi.

• Jika Anda sering memperbarui baris dengan satu atau beberapa kolom yang berada dalam rentang nilai yang sempit, sebaiknya gunakan tabel yang dikelompokkan. Clustering memastikan bahwa perubahan dibatasi 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 pengelompokan 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 mengetahui praktik terbaik guna mengoptimalkan performa kueri, lihat Pengantar cara mengoptimalkan performa kueri.

Batasan

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

• Baris yang baru-baru ini ditulis menggunakan metode streaming tabledata.insertall tidak dapat diubah dengan bahasa pengolahan data (DML), seperti pernyataan UPDATE, DELETE, MERGE, atau TRUNCATE. Operasi tulis terbaru adalah operasi yang terjadi dalam 30 menit terakhir. Semua baris lain dalam tabel tetap dapat diubah menggunakan pernyataan UPDATE, DELETE, MERGE, atau TRUNCATE. Data yang di-streaming dapat memerlukan waktu hingga 90 menit untuk tersedia bagi operasi penyalinan.

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

• Subkueri yang 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.

Langkah selanjutnya

• Untuk informasi dan contoh sintaksis DML, lihat Sintaksis DML.
• Untuk informasi tentang penggunaan pernyataan DML dalam kueri terjadwal, lihat Menjadwalkan kueri.