Halaman ini diterjemahkan oleh Cloud Translation API.

Mengubah data dengan bahasa manipulasi data (DML)

Bahasa manipulasi data (DML) BigQuery memungkinkan Anda memperbarui, 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 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 dilakukan oleh pernyataan tersebut 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 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 penyalinan.

Atau, baris yang baru saja ditulis menggunakan Storage Write API dapat diubah menggunakan pernyataan UPDATE, DELETE, atau MERGE. Untuk mengetahui informasi selengkapnya, lihat Menggunakan bahasa manipulasi data (DML) dengan data yang baru 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 pernyataan INSERT.

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 membiarkan 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 sebagai gantinya. Untuk mengetahui informasi selengkapnya tentang pernyataan TRUNCATE, lihat pernyataan TRUNCATE.

Pernyataan `UPDATE`

Gunakan pernyataan UPDATE untuk memperbarui 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 lainnya. Untuk mengetahui informasi dan contoh selengkapnya tentang pernyataan MERGE, lihat Pernyataan MERGE.

Tugas serentak

BigQuery mengelola serentak 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, serentak pernyataan INSERT yang menulis ke tabel dibatasi hingga 10. Pernyataan INSERT tambahan ditambahkan ke antrean PENDING. Hingga 100 pernyataan INSERT dapat diantrekan terhadap tabel kapan saja. Setelah 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.

Konkurensi DML UPDATE, DELETE, MERGE

Pernyataan DML UPDATE, DELETE, dan MERGE disebut pernyataan DML yang mengubah data. Jika Anda mengirimkan satu atau beberapa pernyataan DML bermutasi pada tabel saat tugas DML bermutasi lainnya di tabel tersebut masih berjalan (atau tertunda), BigQuery akan menjalankan hingga 2 tugas secara serentak, lalu hingga 20 tugas akan diantrekan sebagai PENDING. Saat pekerjaan yang sebelumnya berjalan selesai, pekerjaan tertunda berikutnya akan dikeluarkan dari antrean dan dijalankan. Pernyataan DML bermutasi yang diantrekan 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: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 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 bersamaan selama pernyataan tersebut 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 dijalankan.

DML terperinci

DML terperinci adalah peningkatan performa yang dirancang untuk mengoptimalkan eksekusi pernyataan UPDATE, DELETE, dan MERGE (juga dikenal sebagai pernyataan DML yang bermutasi). Tanpa DML terperinci diaktifkan, 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 konsumsi slot secara keseluruhan.

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

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, pernyataan DML yang mengubah data 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 dalam sebagian besar situasi, biaya ini diperkirakan tidak signifikan dibandingkan dengan ukuran tabel itu sendiri.

Operasi DML terperinci memproses data yang dihapus secara offline. Tugas pemrosesan data yang dihapus ini akan menimbulkan biaya komputasi BigQuery tambahan.

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

Tugas pemrosesan data yang dihapus secara offline dan terperinci dianggap sebagai tugas latar belakang dan menggunakan jenis penetapan BACKGROUND, bukan jenis penetapan QUERY. Project yang melakukan operasi DML terperinci tanpa proses penetapan BACKGROUND menghapus data menggunakan harga sesuai permintaan.

Untuk project yang dikonfigurasi untuk menggunakan harga komputasi sesuai permintaan, pernyataan DML terperinci tidak akan mengurangi byte yang dipindai.

Untuk menemukan tugas pemrosesan data yang dihapus DML terperinci offline:

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

Pertimbangan data yang dihapus

Project yang melakukan operasi DML terperinci menggunakan harga sesuai permintaan secara rutin menghapus data yang diproses menggunakan resource BigQuery internal.

Project yang melakukan operasi DML terperinci dengan proses penetapan BACKGROUND menghapus data menggunakan slot, dan tunduk pada ketersediaan resource pemesanan yang dikonfigurasi. Jika tidak tersedia cukup resource dalam reservasi 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 diaktifkan. Sebagai gantinya, 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 dengan DML terperinci diaktifkan setelah menjalankan pernyataan UPDATE, DELETE, atau MERGE.
Anda tidak dapat mengaktifkan DML terperinci pada tabel dalam 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 terbatas pada partisi tertentu dalam tabel.
Hindari memartisi tabel jika jumlah data di setiap partisi kecil dan setiap update mengubah sebagian besar partisi.
Jika Anda sering memperbarui baris dengan satu atau beberapa kolom yang berada dalam rentang nilai yang sempit, pertimbangkan untuk menggunakan tabel yang dikelompokkan. Pengelompokan memastikan bahwa perubahan terbatas pada set 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 mengkueri data yang berada di Cloud SQL.

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

Langkah berikutnya

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

Mengubah data dengan bahasa manipulasi data (DML)

Batasan

Pernyataan DML

Pernyataan INSERT

Pernyataan DELETE

Pernyataan TRUNCATE

Pernyataan UPDATE

Pernyataan MERGE

Tugas serentak

Konkurensi DML INSERT

Konkurensi DML UPDATE, DELETE, MERGE

Konflik pernyataan DML

DML terperinci

Mengaktifkan DML terperinci

Harga

Pertimbangan data yang dihapus

Batasan

Praktik terbaik

Langkah berikutnya

Pernyataan `INSERT`

Pernyataan `DELETE`

Pernyataan `TRUNCATE`

Pernyataan `UPDATE`

Pernyataan `MERGE`