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
- Untuk ringkasan tabel berpartisi, lihat Pengantar tabel berpartisi.
- Untuk mempelajari lebih lanjut cara membuat tabel berpartisi, lihat Membuat tabel berpartisi.