Mengkueri tabel berpartisi

Dokumen ini menjelaskan beberapa pertimbangan khusus untuk mengkueri tabel berpartisi di BigQuery.

Untuk mengetahui informasi umum tentang cara menjalankan kueri di BigQuery, lihat Menjalankan kueri interaktif dan batch.

Ringkasan

Jika kueri menggunakan filter yang memenuhi syarat pada nilai kolom partisi, BigQuery dapat memindai partisi yang cocok dengan filter dan melewati partisi yang tersisa. Proses ini disebut pruning partisi.

Pruning partisi adalah mekanisme yang digunakan BigQuery untuk menghilangkan partisi yang tidak diperlukan dari pemindaian input. Partisi yang di-pruning tidak disertakan saat menghitung byte yang dipindai oleh kueri. Secara umum, pruning partisi membantu mengurangi biaya kueri.

Perilaku pruning bervariasi untuk berbagai jenis partisi, sehingga Anda dapat melihat perbedaan byte yang diproses saat mengkueri tabel yang dipartisi secara berbeda, tetapi ternyata identik. Untuk memperkirakan jumlah byte yang akan diproses, jalankan uji coba.

Mengkueri tabel berpartisi kolom unit waktu

Untuk melakukan pruning partisi saat Anda mengkueri tabel berpartisi berdasarkan kolom unit waktu, sertakan filter pada kolom partisi.

Pada contoh berikut, asumsikan bahwa dataset.table dipartisi pada kolom transaction_date. Contoh kueri ini akan melakukan pruning tanggal sebelum 2016-01-01.

SELECT * FROM dataset.table
WHERE transaction_date >= '2016-01-01'

Mengkueri tabel berpartisi berdasarkan waktu penyerapan

Tabel berpartisi berdasarkan waktu penyerapan berisi kolom semu bernama _PARTITIONTIME, yang merupakan kolom partisi. Nilai kolom adalah waktu penyerapan UTC untuk setiap baris, yang dipotong sesuai batas partisi (seperti per jam atau harian), sebagai nilai TIMESTAMP.

Misalnya, jika Anda menambahkan data pada 15 April 2021, 08:15:00 UTC, kolom _PARTITIONTIME untuk baris tersebut berisi nilai berikut:

  • Tabel berpartisi per jam: TIMESTAMP("2021-04-15 08:00:00")
  • Tabel berpartisi harian: TIMESTAMP("2021-04-15")
  • Tabel berpartisi bulanan: TIMESTAMP("2021-04-01")
  • Tabel berpartisi tahunan: TIMESTAMP("2021-01-01")

Jika perincian partisi adalah harian, tabel juga berisi kolom semu bernama _PARTITIONDATE. Nilai ini sama dengan _PARTITIONTIME yang dipotong menjadi nilai DATE.

Kedua nama kolom semu ini telah dipesan. Anda tidak dapat membuat kolom dengan nama mana pun di tabel Anda.

Untuk melakukan pruning partisi, filter salah satu kolom tersebut. Misalnya, kueri berikut hanya memindai partisi antara tanggal 1 Januari 2016 dan 2 Januari 2016:

SELECT
  column
FROM
  dataset.table
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP('2016-01-01') AND TIMESTAMP('2016-01-02')

Untuk memilih kolom semu _PARTITIONTIME, Anda harus menggunakan alias. Misalnya, kueri berikut memilih _PARTITIONTIME dengan menetapkan alias pt ke kolom semu:

SELECT
  _PARTITIONTIME AS pt, column
FROM
  dataset.table

Untuk tabel berpartisi harian, Anda dapat memilih kolom semu _PARTITIONDATE dengan cara yang sama:

SELECT
  _PARTITIONDATE AS pd, column
FROM
  dataset.table

Kolom semu _PARTITIONTIME dan _PARTITIONDATE tidak ditampilkan oleh pernyataan SELECT *. Anda harus memilihnya secara eksplisit:

SELECT
  _PARTITIONTIME AS pt, *
FROM
  dataset.table

Menangani zona waktu dalam tabel berpartisi berdasarkan waktu penyerapan

Nilai _PARTITIONTIME didasarkan pada tanggal UTC saat kolom diisi. Jika Anda ingin mengkueri data berdasarkan zona waktu selain UTC, pilih salah satu opsi berikut:

  • Sesuaikan untuk perbedaan zona waktu dalam kueri SQL Anda.
  • Gunakan dekorator partisi untuk memuat data ke partisi waktu penyerapan tertentu, berdasarkan zona waktu yang berbeda dengan UTC.

Performa yang lebih baik dengan kolom semu

Untuk meningkatkan performa kueri, gunakan kolom semu _PARTITIONTIME saja di sisi kiri perbandingan.

Misalnya, dua kueri berikut adalah setara. Bergantung pada ukuran tabel, kueri kedua mungkin berperforma lebih baik karena menempatkan _PARTITIONTIME sendiri di sisi kiri operator >. Kedua kueri memproses jumlah data yang sama.

-- Might be slower.
SELECT
  field1
FROM
  dataset.table1
WHERE
  TIMESTAMP_ADD(_PARTITIONTIME, INTERVAL 5 DAY) > TIMESTAMP("2016-04-15");

-- Often performs better.
SELECT
  field1
FROM
  dataset.table1
WHERE
  _PARTITIONTIME > TIMESTAMP_SUB(TIMESTAMP('2016-04-15'), INTERVAL 5 DAY);

Untuk membatasi partisi yang dipindai dalam kueri, gunakan ekspresi konstan dalam filter Anda. Kueri berikut membatasi partisi yang di-pruning berdasarkan kondisi filter pertama dalam klausa WHERE. Namun, kondisi filter kedua tidak membatasi partisi yang dipindai, karena menggunakan nilai tabel yang dinamis.

SELECT
  column
FROM
  dataset.table2
WHERE
  -- This filter condition limits the scanned partitions:
  _PARTITIONTIME BETWEEN TIMESTAMP('2017-01-01') AND TIMESTAMP('2017-03-01')
  -- This one doesn't, because it uses dynamic table values:
  AND _PARTITIONTIME = (SELECT MAX(timestamp) from dataset.table1)

Untuk membatasi partisi yang dipindai, jangan sertakan kolom lain dalam filter _PARTITIONTIME. Misalnya, kueri berikut tidak membatasi partisi yang dipindai, karena field1 adalah kolom dalam tabel.

-- Scans all partitions of table2. No pruning.
SELECT
  field1
FROM
  dataset.table2
WHERE
  _PARTITIONTIME + field1 = TIMESTAMP('2016-03-28');

Jika Anda sering membuat kueri untuk rentang waktu tertentu, pertimbangkan untuk membuat tampilan yang memfilter kolom semu _PARTITIONTIME. Misalnya, pernyataan berikut membuat tampilan yang hanya menyertakan data tujuh hari terakhir dari tabel bernama dataset.partitioned_table:

-- This view provides pruning.
CREATE VIEW dataset.past_week AS
  SELECT *
  FROM
    dataset.partitioned_table
  WHERE _PARTITIONTIME BETWEEN
    TIMESTAMP_TRUNC(TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 7 * 24 HOUR), DAY)
    AND TIMESTAMP_TRUNC(CURRENT_TIMESTAMP, DAY);

Untuk informasi tentang membuat tampilan, lihat Membuat tampilan.

Mengkueri tabel berpartisi berdasarkan rentang bilangan bulat

Untuk melakukan pruning partisi saat Anda mengkueri tabel berpartisi berdasarkan rentang bilangan bulat, sertakan filter pada kolom partisi bilangan bulat.

Pada contoh berikut, asumsikan bahwa dataset.table adalah tabel berpartisi berdasarkan rentang bilangan bulat dengan spesifikasi partisi customer_id:0:100:10. Contoh kueri memindai tiga partisi yang dimulai dengan 30, 40, dan 50.

SELECT * FROM dataset.table
WHERE customer_id BETWEEN 30 AND 50

+-------------+-------+
| customer_id | value |
+-------------+-------+
|          40 |    41 |
|          45 |    46 |
|          30 |    31 |
|          35 |    36 |
|          50 |    51 |
+-------------+-------+

Pruning partisi tidak didukung untuk fungsi pada kolom berpartisi berdasarkan rentang bilangan bulat. Misalnya, kueri berikut memindai seluruh tabel.

SELECT * FROM dataset.table
WHERE customer_id + 1 BETWEEN 30 AND 50

Menggunakan SQL lama untuk mengkueri tabel berpartisi berdasarkan rentang bilangan bulat

Anda tidak dapat menggunakan SQL lama untuk mengkueri di seluruh tabel berpartisi berdasarkan rentang bilangan bulat. Sebagai gantinya, kueri akan menampilkan error seperti berikut:

Querying tables partitioned on a field is not supported in Legacy SQL

Namun, SQL lama mendukung penggunaan dekorator tabel untuk menangani partisi tertentu dalam tabel berpartisi berdasarkan rentang bilangan bulat. Kunci untuk menangani partisi rentang adalah awal dari rentang.

Contoh berikut mengkueri partisi rentang yang dimulai dengan 30:

SELECT * FROM dataset.table$30

Mengkueri data di penyimpanan yang dioptimalkan untuk tulis

Partisi __UNPARTITIONED__ menyimpan data yang di-streaming ke tabel berpartisi untuk sementara saat berada di penyimpanan yang dioptimalkan untuk tulis. Data yang di-streaming langsung ke partisi tertentu dari tabel berpartisi tidak menggunakan partisi __UNPARTITIONED__. Sebaliknya, data akan di-streaming langsung ke partisi.

Data di penyimpanan yang dioptimalkan untuk tulis memiliki nilai NULL dalam kolom _PARTITIONTIME dan _PARTITIONDATE.

Untuk mengkueri data dalam partisi __UNPARTITIONED__, gunakan kolom semu _PARTITIONTIME dengan nilai NULL. Contoh:

SELECT
  column
FROM dataset.table
WHERE
  _PARTITIONTIME IS NULL

Untuk informasi selengkapnya, lihat Streaming ke tabel berpartisi.

Praktik terbaik untuk pruning partisi

Menggunakan ekspresi filter konstan

Untuk membatasi partisi yang dipindai dalam kueri, gunakan ekspresi konstan dalam filter Anda. Jika Anda menggunakan ekspresi dinamis dalam filter kueri, BigQuery harus memindai semua partisi.

Misalnya, kueri berikut melakukan pruning partisi karena filter tersebut berisi ekspresi konstan:

SELECT
  t1.name,
  t2.category
FROM
  table1 AS t1
INNER JOIN
  table2 AS t2
ON t1.id_field = t2.field2
WHERE
  t1.ts = CURRENT_TIMESTAMP()

Namun, kueri berikut tidak melakukan pruning partisi, karena filter, WHERE t1.ts = (SELECT timestamp from table where key = 2), bukan ekspresi konstan; hal itu bergantung pada nilai dinamis kolom timestamp dan key:

SELECT
  t1.name,
  t2.category
FROM
  table1 AS t1
INNER JOIN
  table2 AS t2
ON
  t1.id_field = t2.field2
WHERE
  t1.ts = (SELECT timestamp from table3 where key = 2)

Memisahkan kolom partisi dalam filter

Memisahkan kolom partisi saat mengekspresikan filter. Filter yang memerlukan data dari beberapa kolom untuk dihitung tidak akan melakukan pruning partisi. Misalnya, kueri dengan perbandingan tanggal menggunakan kolom partisi dan kolom kedua, atau kueri yang berisi beberapa penyambungan kolom tidak akan melakukan pruning partisi.

Misalnya, filter berikut tidak melakukan pruning partisi karena memerlukan komputasi berdasarkan kolom ts partisi dan kolom kedua ts2:

WHERE TIMESTAMP_ADD(ts, INTERVAL 6 HOUR) > ts2

Mewajibkan filter partisi dalam kueri

Saat membuat tabel berpartisi, Anda dapat mewajibkan penggunaan filter predikat dengan mengaktifkan opsi Wajibkan filter partisi. Jika opsi ini diterapkan, upaya untuk mengkueri tabel berpartisi tanpa menentukan klausa WHERE akan menghasilkan error berikut:

Cannot query over table 'project_id.dataset.table' without a filter that can be used for partition elimination.

Setidaknya harus ada satu predikat yang hanya merujuk ke kolom partisi agar filter dianggap memenuhi syarat untuk penghapusan partisi. Misalnya, untuk tabel yang dipartisi pada kolom partition_id dengan kolom tambahan f dalam skemanya, kedua klausa WHERE berikut memenuhi persyaratan:

WHERE partition_id = "20221231"
WHERE partition_id = "20221231" AND f = "20221130"

Namun, WHERE (partition_id = "20221231" OR f = "20221130") tidak cukup.

Untuk tabel berpartisi berdasarkan waktu penyerapan, gunakan kolom semu _PARTITIONTIME atau _PARTITIONDATE.

Untuk informasi selengkapnya tentang menambahkan opsi Wajibkan filter partisi saat membuat tabel berpartisi, lihat Membuat tabel berpartisi. Anda juga dapat memperbarui setelan ini di tabel yang sudah ada.

Langkah selanjutnya