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 pernyataan UPDATE, DELETE, MERGE, atau TRUNCATE. Penulisan 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 agar tersedia untuk operasi penyalinan.

    Atau, baris yang baru saja ditulis menggunakan Storage Write API dapat diubah menggunakan pernyataan UPDATE, DELETE, atau MERGE (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, 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.

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 pernyataan MERGE dengan klausa UPDATE atau DELETE, 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.