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

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

Bagian ini menjelaskan praktik terbaik untuk menulis kueri yang menggunakan penghapusan partisi untuk mengoptimalkan performa kueri dan mengurangi biaya.

Menggunakan ekspresi filter konstan

Untuk membatasi partisi yang dipindai dalam kueri, filter kolom partisi menggunakan ekspresi konstan, bukan ekspresi dinamis.

Kueri berikut melakukan pruning partisi:

SELECT
  t1.name, t1.quantity
FROM
  table1 AS t1
WHERE
  t1.ts = CURRENT_TIMESTAMP()

Sebagai perbandingan, kueri berikut tidak melakukan pruning partisi, karena predikat, WHERE t1.ts = (SELECT timestamp FROM table3 WHERE key = 2), bukan ekspresi konstan. Kueri ini membandingkan kolom partisi dengan nilai dinamis, yang mencegah penghapusan partisi.

SELECT
  t1.name, t1.quantity
FROM
  table1 AS t1
WHERE
  t1.ts = (SELECT timestamp FROM table3 WHERE key = 2)

Selain itu, kueri dengan predikat berikut tidak melakukan pruning partisi karena memerlukan komputasi berdasarkan kolom tabel kedua yang tidak konstan ts2 atau duration:

WHERE ts >= ts2

WHERE ts < CURRENT_TIMESTAMP() - duration

Mengisolasi kolom partisi atau menggunakan fungsi yang didukung

Untuk memangkas partisi, kondisi filter harus disusun agar BigQuery dapat menentukan partisi mana yang akan dipindai tanpa membaca data tabel. Untuk melakukannya, pisahkan kolom partisi di satu sisi operator perbandingan, atau bungkus kolom hanya dalam fungsi bawaan yang didukung. Anda dapat menggunakan uji coba untuk memverifikasi apakah pemangkasan partisi didukung pada kueri tertentu.

Fungsi bawaan berikut pada kolom partisi mendukung penghapusan partisi, jika argumen tambahannya konstan:

Fungsi lain dan operasi matematika yang kompleks akan memerlukan pemindaian tabel penuh.

Contoh

Kueri berikut menunjukkan contoh predikat yang mendukung penghapusan partisi.

SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE datehour = '2025-03-30 12:00:00';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE datehour >= '2025-03-30'
  AND datehour < TIMESTAMP_ADD('2025-03-30', INTERVAL 1 DAY);
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE DATE(datehour) = '2025-03-30';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE EXTRACT(DATE FROM datehour) = '2025-03-30';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE CAST(datehour AS DATE) = '2025-03-30';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE datehour >= '2025-01-01' AND datehour < '2025-02-01';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE TIMESTAMP_TRUNC(datehour, MONTH) >= '2025-04-01'
  AND TIMESTAMP_TRUNC(datehour, MONTH) < '2025-07-01';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE TIMESTAMP_DIFF(datehour, '2025-01-01', DAY) < 1;
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE TIMESTAMP_ADD(datehour, INTERVAL 1 DAY) < '2025-01-03';
 
SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE TIMESTAMP_SUB(datehour, INTERVAL 1 DAY) < '2025-01-01';

Kueri berikut melewati semua partisi karena predikat tidak cocok dengan baris mana pun.

SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE EXTRACT(YEAR FROM datehour) = 1900;

Kueri berikut memilih hari pertama setiap bulan dalam tabel, dan mendukung penghapusan partisi. 

SELECT COUNT(*) FROM bigquery-public-data.wikipedia.pageviews_2025
WHERE DATE(datehour) IN UNNEST(GENERATE_DATE_ARRAY(
  DATE_TRUNC(CURRENT_DATE(), YEAR),
  DATE(DATE_TRUNC(CURRENT_DATE(), YEAR) + INTERVAL 1 YEAR - INTERVAL 1 DAY),
  INTERVAL 1 MONTH
))

Kueri dengan predikat berikut tidak memangkas partisi karena memanipulasi kolom partisi dengan fungsi yang tidak didukung:

WHERE FORMAT_DATE('%Y-%m-%d %H', ts) = '2025-03-28 20';

WHERE EXTRACT(MONTH FROM ts) = 3 AND EXTRACT(HOUR FROM ts) = 20

Demikian pula, kueri dengan predikat berikut tidak memangkas partisi karena memanipulasi kolom partisi dengan operasi aritmatika: 

WHERE ts + INTERVAL 1 DAY > CURRENT_TIMESTAMP()

Untuk mengaktifkan pruning partisi, Anda harus menulis ulang ekspresi dengan mengisolasi kolom partisi ts dari fungsi atau operasi aritmatika yang tidak didukung. Untuk rentang waktu, gunakan >= dan < untuk mengambil rentang yang tepat. Untuk aritmatika, pindahkan operasi ke sisi perbandingan yang lain.

Kueri berikut memungkinkan penghapusan partisi dengan mengisolasi kolom partisi ts untuk rentang waktu:

WHERE ts >= '2025-03-28 20:00:00' AND ts < '2025-03-28 21:00:00'

Kueri berikut memungkinkan penghapusan partisi dengan mengisolasi kolom partisi dari operasi aritmatika:

WHERE ts > CURRENT_TIMESTAMP() - INTERVAL 1 DAY

Memfilter beberapa kolom

Predikat pada kolom partisi dalam kueri tidak membatasi hal lain yang dapat Anda filter. Anda dapat menyertakan predikat pada kolom lain dalam klausa WHERE yang sama, dan pemangkasan partisi akan tetap terjadi selama kondisi yang mengevaluasi kolom partisi mengikuti praktik terbaik. Perhatikan bahwa AND penting dalam contoh berikut. Jika AND diubah menjadi OR, pemangkasan partisi tidak akan berfungsi, karena meskipun partisi tidak cocok dengan predikat pada kolom partisi, partisi tersebut tetap tidak dapat dipangkas. Data dalam partisi ini dengan meter_id = 1234 tetap memenuhi syarat untuk kueri.

Perhatikan bahwa predikat tidak perlu ditulis dalam urutan tertentu. Dalam contoh kueri berikut, dengan asumsi partisi pada kolom ts, pemangkasan partisi tetap terjadi terlepas dari penempatan predikat.

WHERE meter_id = 1234
  AND ts >= '2025-03-28 20:00:00' AND ts < '2025-03-28 21:00:00'

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.

Persyaratan ini juga berlaku untuk kueri pada tampilan dan tampilan terwujud yang mereferensikan tabel yang dipartisi.

Setidaknya harus ada satu predikat yang hanya merujuk ke kolom partisi agar filter dianggap memenuhi syarat untuk penghapusan partisi. 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, berikut ini tidak cukup, dan akan menghasilkan error:

WHERE partition_id = "20221231" OR f = "20221130"

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 berikutnya