Transformasi data dengan bahasa manipulasi data ({i>data manipulation language, <i}DML)
Bahasa manipulasi data (DML) BigQuery dapat digunakan untuk memperbarui, menyisipkan, dan menghapus data dari tabel BigQuery.
Anda dapat mengeksekusi pernyataan DML seperti halnya pernyataan SELECT
, dengan kondisi berikut:
- Anda harus menggunakan GoogleSQL. Untuk mengaktifkan GoogleSQL, lihat Beralih 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.
Batasan
Setiap pernyataan DML memulai transaksi implisit, yang berarti perubahan yang dibuat oleh pernyataan akan otomatis di-commit di akhir setiap pernyataan DML yang berhasil.
Baris yang baru saja ditulis menggunakan metode streaming
tabledata.insertall
tidak dapat diubah dengan bahasa manipulasi data (DML), seperti pernyataanUPDATE
,DELETE
,MERGE
, atauTRUNCATE
. Penulisan terbaru adalah operasi yang terjadi dalam 30 menit terakhir. Semua baris lain dalam tabel tetap dapat diubah menggunakan pernyataanUPDATE
,DELETE
,MERGE
, atauTRUNCATE
. Data yang di-streaming dapat memerlukan waktu hingga 90 menit agar tersedia untuk operasi penyalinan.Atau, baris yang baru saja ditulis menggunakan Storage Write API dapat diubah menggunakan pernyataan
UPDATE
,DELETE
, atauMERGE
(Pratinjau). Untuk mengetahui informasi selengkapnya, lihat Menggunakan bahasa manipulasi data (DML) dengan data yang baru saja di-streaming.Subkueri yang terkait dalam
when_clause
,search_condition
,merge_update_clause
, ataumerge_insert_clause
tidak didukung untuk pernyataanMERGE
.Kueri yang berisi pernyataan DML tidak dapat menggunakan tabel karakter pengganti sebagai target kueri. Misalnya, tabel karakter pengganti dapat digunakan dalam klausa
FROM
dari kueriUPDATE
, tetapi tabel karakter pengganti tidak dapat digunakan sebagai target operasiUPDATE
.
Tugas serentak
BigQuery mengelola konkurensi pernyataan DML yang menambahkan, mengubah, atau menghapus baris dalam tabel.
SISIPKAN konkurensi DML
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
. Maksimal 100 pernyataan INSERT
dapat diantrekan ke satu tabel pada waktu tertentu. Saat pernyataan INSERT
selesai, pernyataan INSERT
berikutnya akan dihapus dari antrean dan dijalankan.
UPDATE, DELETE, DELETE DML serentak
Pernyataan DML UPDATE
, DELETE
, dan MERGE
disebut pernyataan DML bermutasi. Jika Anda mengirimkan satu atau beberapa pernyataan DML yang bermutasi di tabel, sementara tugas DML lain yang bermutasi masih berjalan (atau tertunda), BigQuery akan menjalankan hingga 2 pernyataan DML secara bersamaan, setelah itu hingga 20 diantrekan sebagai PENDING
. Setelah tugas yang sebelumnya berjalan selesai, tugas
yang tertunda berikutnya akan dikeluarkan dari antrean dan dijalankan. Pernyataan DML yang bermutasi dalam antrean berbagi antrean per tabel dengan panjang maksimum 20. Pernyataan tambahan setelah 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:TABLE, limit is 20.
Tugas DML prioritas interaktif yang diantrekan lebih dari 6 jam akan gagal dengan pesan error berikut:
DML statement has been queued for too long
Konflik pernyataan DML
Mengubah pernyataan DML yang berjalan serentak pada tabel menyebabkan konflik pernyataan DML ketika 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 bertentangan dengan pernyataan DML lainnya yang berjalan serentak.Pernyataan DML
MERGE
tidak bertentangan dengan pernyataan DML lain yang berjalan secara serentak selama pernyataan tersebut hanya menyisipkan baris dan tidak menghapus atau memperbarui baris yang ada. Ini dapat mencakup pernyataanMERGE
dengan klausaUPDATE
atauDELETE
, selama klausa tersebut tidak dipanggil saat kueri berjalan.
Harga
Untuk mengetahui informasi tentang harga DML, lihat Harga Bahasa Manipulasi Data di halaman Harga.
Praktik terbaik
Untuk performa terbaik, Google merekomendasikan pola berikut:
Jangan mengirimkan pembaruan 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 satu baris.
Jika pembaruan atau penghapusan umumnya terjadi pada data lama, atau dalam rentang tanggal tertentu, pertimbangkan untuk mempartisi tabel Anda. 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 memperbarui baris yang memiliki satu atau beberapa kolom yang berada dalam rentang nilai yang sempit, pertimbangkan untuk menggunakan tabel yang dikelompokkan. Pengelompokan 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);
Dalam kasus ini, pertimbangkan untuk mengelompokkan pada kolom
id
.Jika Anda memerlukan fungsi OLTP, pertimbangkan untuk menggunakan kueri gabungan Cloud SQL, yang memungkinkan BigQuery untuk mengkueri data yang berada di Cloud SQL.
Untuk praktik terbaik mengoptimalkan performa kueri, lihat Pengantar cara mengoptimalkan performa kueri.
Langkah selanjutnya
- Untuk mengetahui informasi dan contoh sintaksis DML, lihat sintaksis DML.
- Untuk informasi tentang penggunaan pernyataan DML dalam kueri terjadwal, lihat Menjadwalkan kueri.