Mengupdate data tabel berpartisi menggunakan DML

Halaman ini menyediakan ringkasan dukungan bahasa pengolahan data (DML) untuk tabel berpartisi.

Untuk mengetahui informasi selengkapnya tentang DML, lihat:

Tabel yang digunakan dalam contoh

Definisi skema JSON berikut mewakili tabel yang digunakan dalam contoh di halaman ini.

mytable: tabel berpartisi berdasarkan waktu penyerapan 

    [
      {"name": "field1", "type": "INTEGER"},
      {"name": "field2", "type": "STRING"}
    ]

mytable2: tabel standar (tidak dipartisi) 

    [
      {"name": "id", "type": "INTEGER"},
      {"name": "ts", "type": "TIMESTAMP"}
    ]

mycolumntable: tabel berpartisi yang dipartisi menggunakan kolom TIMESTAMP ts 

    [
      {"name": "field1", "type": "INTEGER"},
      {"name": "field2", "type": "STRING"}
      {"name": "field3", "type": "BOOLEAN"}
      {"name": "ts", "type": "TIMESTAMP"}
    ]

Pada contoh tempat COLUMN_ID muncul, ganti dengan nama kolom yang ingin Anda operasikan.

Menyisipkan data

Anda menggunakan pernyataan INSERT DML untuk menambahkan baris ke tabel berpartisi.

Menyisipkan data ke tabel berpartisi berdasarkan waktu penyerapan

Saat menggunakan pernyataan DML untuk menambahkan baris ke tabel berpartisi berdasarkan waktu penyerapan, Anda dapat menentukan partisi yang akan ditambahi baris. Anda mereferensikan partisi menggunakan kolom semu _PARTITIONTIME.

Misalnya, pernyataan INSERT berikut menambahkan baris ke partisi mytable 1 Mei 2017 — “2017-05-01”.

INSERT INTO
  project_id.dataset.mytable (_PARTITIONTIME,
    field1,
    field2)
SELECT
  TIMESTAMP("2017-05-01"),
  1,
  "one"

Hanya stempel waktu yang sesuai dengan batas tanggal persis yang dapat digunakan. Misalnya, pernyataan DML berikut menampilkan error:

INSERT INTO
  project_id.dataset.mytable (_PARTITIONTIME,
    field1,
    field2)
SELECT
  TIMESTAMP("2017-05-01 21:30:00"),
  1,
  "one"

Menyisipkan data ke tabel berpartisi

Menyisipkan data ke tabel berpartisi menggunakan DML sama dengan memasukkan data ke dalam tabel yang tidak dipartisi.

Misalnya, pernyataan INSERT berikut menambahkan baris ke tabel berpartisi mycolumntable dengan memilih data dari mytable2 (tabel yang tidak dipartisi).

INSERT INTO
  project_id.dataset.mycolumntable (ts,
    field1)
SELECT
  ts,
  id
FROM
  project_id.dataset.mytable2

Menghapus data

Anda menggunakan pernyataan DELETE DML untuk menghapus baris dari tabel berpartisi.

Menghapus data di tabel berpartisi berdasarkan waktu penyerapan

Pernyataan DELETE berikut akan menghapus semua baris dari partisi 1 Juni 2017 ("2017-06-01") dari mytable dengan field1 sama dengan 21. Anda mereferensikan partisi menggunakan kolom semu _PARTITIONTIME.

DELETE
  project_id.dataset.mytable
WHERE
  field1 = 21
  AND _PARTITIONTIME = "2017-06-01"

Menghapus data dalam tabel berpartisi

Menghapus data dalam tabel berpartisi menggunakan DML sama dengan menghapus data dari tabel yang tidak dipartisi.

Misalnya, pernyataan DELETE berikut akan menghapus semua baris dari partisi 1 Juni 2017 ("2017-06-01") dari mycolumntable dengan field1 sama dengan 21.

DELETE
  project_id.dataset.mycolumntable
WHERE
  field1 = 21
  AND DATE(ts) = "2017-06-01"

Menggunakan DML DELETE untuk menghapus partisi

Jika pernyataan DELETE yang memenuhi syarat mencakup semua baris dalam partisi, BigQuery akan menghapus seluruh partisi. Penghapusan ini dilakukan tanpa memindai byte atau menghabiskan slot. Contoh pernyataan DELETE berikut mencakup seluruh partisi filter di kolom pseudo _PARTITIONDATE:

DELETE mydataset.mytable
WHERE _PARTITIONDATE IN ('2076-10-07', '2076-03-06');

Diskualifikasi umum

Kueri dengan karakteristik berikut mungkin tidak mendapatkan manfaat dari pengoptimalan:

Kelayakan untuk pengoptimalan dapat bervariasi bergantung pada jenis partisi, metadata penyimpanan yang mendasarinya, dan predikat filter. Sebagai praktik terbaik, lakukan uji coba untuk memverifikasi bahwa kueri menghasilkan 0 byte yang diproses.

Transaksi multi-pernyataan

Pengoptimalan ini berfungsi dalam transaksi multi-pernyataan. Contoh kueri berikut mengganti partisi dengan data dari tabel lain dalam satu transaksi, tanpa memindai partisi untuk pernyataan DELETE.

DECLARE REPLACE_DAY DATE;
BEGIN TRANSACTION;

-- find the partition which we want to replace
SET REPLACE_DAY = (SELECT MAX(d) FROM mydataset.mytable_staging);

-- delete the entire partition from mytable
DELETE FROM mydataset.mytable WHERE part_col = REPLACE_DAY;

-- insert the new data into the same partition in mytable
INSERT INTO mydataset.mytable
SELECT * FROM mydataset.mytable_staging WHERE part_col = REPLACE_DAY;

COMMIT TRANSACTION;

Mengupdate data

Anda menggunakan pernyataan UPDATE untuk mengupdate baris dalam tabel berpartisi.

Mengupdate data di tabel berpartisi berdasarkan waktu penyerapan

Pernyataan UPDATE berikut akan memindahkan baris dari satu partisi ke partisi lainnya. Baris dalam partisi 1 Mei 2017 (“2017-05-01”) dari mytable dengan field1 sama dengan 21 akan dipindahkan ke partisi 1 Juni 2017 (“2017-06-01”).

UPDATE
  project_id.dataset.mytable
SET
  _PARTITIONTIME = "2017-06-01"
WHERE
  _PARTITIONTIME = "2017-05-01"
  AND field1 = 21

Mengupdate data dalam tabel berpartisi

Mengupdate data dalam tabel berpartisi menggunakan DML sama dengan mengupdate data dari tabel yang tidak dipartisi. Misalnya, pernyataan UPDATE berikut akan memindahkan baris dari satu partisi ke partisi lainnya. Baris dalam partisi 1 Mei 2017 (“2017-05-01”) dari mytable dengan field1 sama dengan 21 dipindahkan ke partisi 1 Juni 2017 (“2017-06-01”).

UPDATE
  project_id.dataset.mycolumntable
SET
  ts = "2017-06-01"
WHERE
  DATE(ts) = "2017-05-01"
  AND field1 = 21

DML dalam tabel berpartisi per jam, bulanan, dan tahunan

Anda dapat menggunakan pernyataan DML untuk memodifikasi tabel berpartisi per jam, bulanan, atau tahunan. Berikan rentang jam, bulan, atau tahun dari tanggal/stempel waktu/datetime yang relevan, seperti pada contoh berikut untuk tabel berpartisi bulanan:

    bq query --nouse_legacy_sql 'DELETE FROM my_dataset.my_table WHERE
    TIMESTAMP_TRUNC(ts_column, MONTH) = "2020-01-01 00:00:00";'

Atau contoh lain untuk tabel berpartisi dengan kolom DATETIME:

    bq query --nouse_legacy_sql 'DELETE FROM my_dataset.my_table WHERE
    dt_column BETWEEN DATETIME("2020-01-01")
    AND DATETIME("2020-05-01");'

Menggunakan pernyataan MERGE

Anda menggunakan pernyataan MERGE DML untuk menggabungkan operasi INSERT, UPDATE, dan DELETE untuk tabel berpartisi menjadi satu pernyataan dan menjalankannya secara atomik.

Pruning partisi saat menggunakan pernyataan MERGE

Saat Anda menjalankan pernyataan MERGE terhadap tabel berpartisi, Anda dapat membatasi partisi mana yang dipindai dengan menyertakan kolom partisi dalam filter subkueri, filter search_condition, atau filter merge_condition. Pruning dapat terjadi saat memindai tabel sumber atau tabel target, atau keduanya.

Setiap contoh di bawah menjalankan kueri tabel berpartisi berdasarkan waktu penyerapan menggunakan kolom semu _PARTITIONTIME sebagai filter.

Menggunakan subkueri untuk memfilter data sumber

Dalam pernyataan MERGE berikut, subkueri dalam klausa USING memfilter pada kolom semu _PARTITIONTIME di tabel sumber.

MERGE dataset.target T
USING (SELECT * FROM dataset.source WHERE _PARTITIONTIME = '2018-01-01') S
ON T.COLUMN_ID = S.COLUMN_ID
WHEN MATCHED THEN
  DELETE

Dengan melihat rencana eksekusi kueri, subkueri akan berjalan terlebih dahulu. Hanya baris di partisi '2018-01-01' dalam tabel sumber yang dipindai. Berikut adalah tahap yang relevan dalam rencana kueri:

READ $10:name, $11:_PARTITIONTIME
FROM temp.source
WHERE equal($11, 1514764800.000000000)

Menggunakan filter di search_condition dari when_clause

Jika search_condition berisi filter, maka pengoptimal kueri akan mencoba memangkas partisi. Misalnya, dalam pernyataan MERGE berikut, setiap klausa WHEN MATCHED dan WHEN NOT MATCHED berisi filter pada kolom semu _PARTITIONTIME.

MERGE dataset.target T
USING dataset.source S
ON T.COLUMN_ID = S.COLUMN_ID
WHEN MATCHED AND T._PARTITIONTIME = '2018-01-01' THEN
  UPDATE SET COLUMN_ID = S.COLUMN_ID
WHEN MATCHED AND T._PARTITIONTIME = '2018-01-02' THEN
  UPDATE SET COLUMN_ID = S.COLUMN_ID + 10
WHEN NOT MATCHED BY SOURCE AND T._PARTITIONTIME = '2018-01-03' THEN
  DELETE

Selama tahap join, hanya partisi berikut yang dipindai dalam tabel target: '2018-01-01', '2018-01-02', dan '2018-01-03' — yang merupakan gabungan semua filter search_condition.

Dari rencana eksekusi kueri:

READ
$1:COLUMN_ID, $2:_PARTITIONTIME, $3:$file_temp_id, $4:$row_temp_id
FROM temp.target
WHERE or(equal($2, 1514764800.000000000), equal($2, 1514851200.000000000), equal($2, 1514937600.000000000))

Namun, dalam contoh berikut, klausa WHEN NOT MATCHED BY SOURCE tidak memiliki ekspresi filter:

MERGE dataset.target T
USING dataset.source S
ON T.COLUMN_ID = S.COLUMN_ID
WHEN MATCHED AND T._PARTITIONTIME = '2018-01-01' THEN
  UPDATE SET COLUMN_ID = S.COLUMN_ID
WHEN NOT MATCHED BY SOURCE THEN
  UPDATE SET COLUMN_ID = COLUMN_ID + 1

Kueri ini harus memindai seluruh tabel target untuk menghitung klausa WHEN NOT MATCHED BY SOURCE. Akibatnya, tidak ada partisi yang dipangkas.

Menggunakan predikat salah (false) konstan dalam merge_condition

Jika Anda menggunakan klausa WHEN NOT MATCHED dan WHEN NOT MATCHED BY SOURCE secara bersamaan, maka BigQuery biasanya akan melakukan outer join penuh, yang tidak dapat dipangkas. Namun, jika kondisi penggabungan menggunakan predikat false yang konstan, BigQuery dapat menggunakan kondisi filter untuk pruning partisi. Untuk mengetahui informasi selengkapnya tentang penggunaan predikat false yang onstan, lihat deskripsi klausa merge_condition dalam dokumentasi pernyataan MERGE.

Contoh berikut hanya memindai partisi '2018-01-01' dalam tabel target dan sumber.

MERGE dataset.target T
USING dataset.source S
ON FALSE
WHEN NOT MATCHED AND _PARTITIONTIME = '2018-01-01' THEN
  INSERT(COLUMN_ID) VALUES(COLUMN_ID)
WHEN NOT MATCHED BY SOURCE AND _PARTITIONTIME = '2018-01-01' THEN
  DELETE

Menggunakan filter di merge_condition

Pengoptimal kueri akan mencoba menggunakan filter dalam merge_condition untuk memangkas partisi. Pengoptimal kueri mungkin dapat atau tidak dapat mendorong predikat ke tahap pemindaian tabel, bergantung pada jenis gabungan.

Pada contoh berikut, merge_condition digunakan sebagai predikat untuk menggabungkan tabel sumber dan target. Pengoptimal kueri dapat mendorong predikat ini ke bawah saat memindai kedua tabel. Akibatnya, kueri hanya memindai partisi '2018-01-01' di tabel target dan sumber.

MERGE dataset.target T
USING dataset.source S
ON T.COLUMN_ID = S.COLUMN_ID AND
  T._PARTITIONTIME = '2018-01-01' AND
  S._PARTITIONTIME = '2018-01-01'
WHEN MATCHED THEN
  UPDATE SET COLUMN_ID = NEW_VALUE

Pada contoh berikutnya, merge_condition tidak berisi predikat untuk tabel sumber, sehingga tidak ada pruning partisi yang dapat dilakukan pada tabel sumber. Pernyataan tersebut berisi predikat untuk tabel target, tetapi pernyataan tersebut menggunakan klausa WHEN NOT MATCHED BY SOURCE, bukan klausa WHEN MATCHED. Itu berarti kueri harus memindai seluruh tabel target untuk menemukan baris yang tidak cocok.

MERGE dataset.target T
USING dataset.source S
ON T.COLUMN_ID = S.COLUMN_ID AND T._PARTITIONTIME = '2018-01-01'
WHEN NOT MATCHED BY SOURCE THEN
  UPDATE SET COLUMN_ID = NEW_VALUE

Batasan

Untuk mengetahui informasi tentang batasan DML, lihat Batasan di halaman Referensi DML.

Kuota

Untuk mengetahui informasi tentang informasi kuota DML, lihat Pernyataan DML di halaman Kuota dan batas.

Harga

Untuk mengetahui informasi tentang harga DML, lihat Harga DML untuk tabel berpartisi.

Keamanan tabel

Untuk mengontrol akses ke tabel di BigQuery, lihat Pengantar kontrol akses tabel.

Langkah selanjutnya