Lima Tips Optimisasi Performa Query di BigQuery
Seringkali, kita menghadapi situasi di mana query yang kita buat sebenarnya sederhana, namun tidak sesuai dengan harapan, query berjalan dengan lambat, dengan mengonsumsi resource yang besar, dan akhirnya memperlambat query lain yang sedang berjalan di waktu yang sama.
Berikut beberapa tips untuk mengoptimalkan query sehingga query berjalan lebih cepat dan menggunakan resource lebih rendah
*data yang digunakan pada tips query ini berasal dari public data set yang disediakan oleh BigQuery. Seluruh query di page ini bisa dicopy dan dijalankan di masing masing console
- Hindari penggunaan select *
Penggunan select * berarti meminta BigQuery untuk melakukan scan pada seluruh tabel, hal tersebut akan membuat query semakin lambat jika komponen kolom dalam tabel semakin banyak.
Contoh — not optimize.
Select * from `bigquery-public-data.austin_311.311_service_requests`
Hasil:
elapsed time: 7 Sec.
slot time consumed: 50 Sec.
bytes shuffled: 1.22 GB.Contoh — optimize.
SELECT unique_key,
complaint_description,
source,
status,
status_change_date,
created_date,
FROM `bigquery-public-data.austin_311.311_service_requests`
Hasil:
elapsed time: 4 Sec.
slot time consumed: 16 Sec.
bytes shuffled: 359 MB.Berdasarkan hasil di atas, query dengan Select * lebih banyak mengambil resource dan waktu.
2. Early filtering as possible
Melakukan filtrasi merupakan salah satu cara untuk mengoptimisasi query. Perintah seperti Distinct, Inner Join, Where, Group By, sebisa mungkin dilakukan agar data yang akan diproses merupakan data-data yang dibutuhkan saja.
Contoh — not optimize.
WITH
cte_city AS (
SELECT
city,
Source
FROM
`bigquery-public-data.austin_311.311_service_requests`
),
cte_status AS (
SELECT
city,
status,
FROM
`bigquery-public-data.austin_311.311_service_requests`
)
SELECT
r.city,
r.source,
COUNT(DISTINCT c.status) AS status_count
FROM
cte_city r
LEFT JOIN
cte_status c ON r.city = c.city
GROUP BY
1,2
Hasil:
elapsed time: 3 min.
slot time consumed: 1 Hour 41 min.
bytes shuffled: — MBContoh — optimize
WITH
cte_city AS (
SELECT
city,
Source
FROM
`bigquery-public-data.austin_311.311_service_requests`
),
cte_status AS (
SELECT
city,
count(distinct status) status_count,
FROM
`bigquery-public-data.austin_311.311_service_requests`
group by 1
)
SELECT
r.city,
r.source,
c.status_count
FROM
cte_city r
LEFT JOIN
cte_status c ON r.city = c.city
Hasil:
elapsed time: 1 Sec.
slot time consumed: 13 Sec.
bytes shuffled: 82.47 MB.Pada contoh query yang tidak teroptimisasi, terlihat query berjalan sangat lambat, bahkan tidak selesai. Sedangkan query yang terpotimisasi berjalan dengan sangat cepat dan memakan resource yang jauh lebih kecil. Hal ini disebabkan oleh filtering query untuk memastikan tabel yang akan digabungkan memiliki data yang unique atau sudah tidak ada duplikat di dalamnya.
Sehingga ketika join dilakuakan, BigQuery akan lebih cepat melakukan scan tabel dari 1 tabel ke tabel lainnya.
3. Partition dan cluster
Partition dan clustering digunakan pada tabel yang ukuran data nya lebih dari 1 GB. Penggunaan kedua fungsi ini cukup signifikan, di mana partition akan membantu merekonstruksi data ke bagian-bagian yang diinginkan, misalnya berdasarkan tanggal. Sedangkan clustering membantu mengelompokan data berdasarkan bagian-bagian yang diinginkan. Gambaran partition dan clustering
a. Partition
CREATE TABLE `my-project-1412-362516.database.promo_partition`
PARTITION BY date AS
SELECT date, event, page FROM `my-project-1412-362516.database.promo`
Ketika menjalankan query dari tabel yang sudah dipartisi, BigQuery akan melakukan filterasi hanya pada data yang spesifik yang sudah dipartisi saja tanpa melakukan full scan pada seluruh tabel.
Hal yang sama terjadi untuk kasus clustering, berikut contoh query yang akan digunakan dalam proses clustering.
b. Cluster
CREATE TABLE
`my-project-1412-362516.database.country_shipped_clusters`
CLUSTER BY Country AS
SELECT order_date, country, status
FROM `my-project-1412-362516.database.country_shipped`
CREATE TABLE `my-project-1412-362516.database.country_shipped_clusters`
CLUSTER BY country, status AS
SELECT order_date, country, status
FROM `my-project-1412-362516.database.country_shipped`
Tabel yang telah di kelompokan (cluster) akan mengurutkan data ke dalam blok berdasarkan kolom yang kita pilih, dan kemudian melacak data melalui indeks cluster. Selama proses query, indeks cluster menunjuk ke blok-blok yang berisi data( ), sehingga memungkinkan BigQuery untuk melewati blok-blok yang tidak relevan. Proses melewati blok yang tidak relevan saat pemindaian dikenal sebagai pemangkasan blok. Dengan melakukan hal tersebut, resource yang digunakan BigQuery lebih sedikit, sehingga query yang mengambil data dari tabel-tabel yang telah dilakukan partition dan cluster akan berjalan lebih cepat.
c. Partition and Cluster
Partition dan cluster dapat digabungkan untuk lebih mengoptimalkan performa query untuk tabel yang akan digunakan. Untuk membuat tabel dengan partition dan cluster, dapat dilihat pada query di bawah ini:
CREATE TABLE
`my-project-1412-362516.database.country_shipped_partition_clusters`
partition by order_date cluster by country, status as
Select order_date, country, status
from `my-project-1412-362516.database.country_shipped`
Ada beberapa hal yang perlu diperhatikan untuk membuat partition dan cluster pada BigQuery, beberapa hal yang perlu diperhatikan bisa di lihat pada resource ini
4. Join menggunakan int atau string?
Operasi join memetakan satu tabel ke tabel lain dengan membandingkan kunci join mereka. Jika kunci join tersebut berasal dari tipe data yang sulit untuk dibandingkan, maka query menjadi lambat dan mengeluarkan biaya yang lebih tinggi.
Pertanyaannya adalah — tipe data mana yang sulit untuk dibandingkan, dan mengapa?
Salah satu perbedaannya adalah dalam ukuran storage. Setiap tipe data dialokasikan sejumlah byte yang spesifik dalam database. untuk BigQuery, INT64 selalu menggunakan 8 byte ruang penyimpanan terlepas dari Panjang value nya, tetapi STRING dapat menggunakan storage bervariasi tergantung pada value nya. Dengan asumsi string umumnya menggunakan 2 + 4 * jumlah karakter byte penyimpanan, atau dengan kata lain, jauh lebih banyak daripada int64.
Selain itu, int64 memiliki kelebihan lain dibandingkan join dengan string, karena dalam string memiliki karakter yang kompleks sehingga BigQuery memerlukan waktu untuk memetakan satu nilai ke nilai yang lain.
Contoh — join with string
WITH details as (
SELECT unique_key,clearance_status
FROM `bigquery-public-data.austin_crime.crime`
)
,incidents as (
SELECT unique_key,address
FROM bigquery-public-data.austin_incidents.incidents_2016
)
SELECT a.unique_key, b.address, a.clearance_status FROM details a
JOIN incidents b ON
CAST (a.unique_key as string) = CAST (b.unique_key as string)
Hasil:
elapsed time: 1 Sec.
slot time consumed: 6 Sec.
bytes shuffled: 10.27 MB.Contoh — join with int64
WITH details as (
SELECT unique_key,clearance_status
FROM `bigquery-public-data.austin_crime.crime`
)
,incidents as (
SELECT unique_key,address
FROM bigquery-public-data.austin_incidents.incidents_2016
)
SELECT a.unique_key, b.address, a.clearance_status FROM details a
JOIN incidents b ON
a.unique_key =b.unique_key
Hasil:
elapsed time: 1 Sec.
slot time consumed: 457 ms.
bytes shuffled: 4.93 MB.Dengan menggunakan int64 menggantikan string, penggunaan resource turun dari 6 detik ke 457 millisecond, begitupun byte shuffled yang mengalami penurunan dari 10.27 MB ke 4.93 MB.
5. Optimize anti join
Anti-join adalah operator JOIN dengan klausa eksklusif (where not in, where not exist, dll) yang menghapus baris jika terdapat kecocokan dengan tabel kedua.
Namun yang manakah lebih baik untuk menjalankan query dari beberapa metode ini:
· Left Join
· Not In
· Not Exist
· Except Distinct
Kita akan membedakan anti join dengan tipe string dan int, untuk melihat performa anti join mana yang baik untuk kedua tipe data tersebut
Berikut hasil anti join dengan tipe data int64
WITH details AS (
SELECT unique_key AS unique_id,clearance_status
FROM `bigquery-public-data.austin_crime.crime`
)
,incidents AS (
SELECT unique_key AS unique_id,address
FROM bigquery-public-data.austin_incidents.incidents_2016
)
#LEFT JOIN
SELECT a.unique_id FROM details a
LEFT JOIN incidents b
ON a.unique_id = b.unique_id
WHERE b.unique_id is null
#NOT EXIST
SELECT a.unique_id from details a
WHERE NOT EXISTS
(SELECT b.unique_id FROM incidents b WHERE a.unique_id = b.unique_id)
#NOT IN
SELECT a.unique_id FROM details a
WHERE a.unique_id NOT IN(
SELECT b.unique_id
FROM incidents b)
#EXCEPT DISTINCT
SELECT a.unique_id FROM details a
EXCEPT DISTINCT
SELECT b.unique_id
FROM incidents b
Berikut hasil anti join dengan tipe data string
WITH details AS (
SELECT CAST(unique_key AS string) unique_id ,clearance_status
FROM `bigquery-public-data.austin_crime.crime`
)
,incidents AS (
SELECT CAST(unique_key AS string) unique_id,address
FROM bigquery-public-data.austin_incidents.incidents_2016
)
#LEFT JOIN
SELECT a.unique_id FROM details a
LEFT JOIN incidents b
ON a.unique_id = b.unique_id
WHERE b.unique_id is null
#NOT EXIST
SELECT a.unique_id from details a
WHERE NOT EXISTS
(SELECT b.unique_id FROM incidents b WHERE a.unique_id = b.unique_id)
#NOT IN
SELECT a.unique_id FROM details a
WHERE a.unique_id NOT IN(
SELECT b.unique_id
FROM incidents b)
#EXCEPT DISTINCT
SELECT a.unique_id FROM details a
EXCEPT DISTINCT
SELECT b.unique_id
FROM incidents b
Dari kedua tipe di atas yang menggunakan beberapa method anti-join, seluruh durasi waktu yang dibutuhkan sama, namun terlihat untuk int64 Left Join dan Not Exist memiliki performa yang lebih baik dibandingkan dua lainnya dimana penggunaan resource lebih rendah.
Berbeda dengan tipe string, metode yang memiliki performan baik adalah not in dan except distinct.
Penutup
Artikel ini hanya berisikan sebagian kecil dari banyaknya tips & tricks untuk mengoptimisasi BigQuery. Harapannya artikel ini dapat memberikan manfaat bagi para pembaca. Saran dan kritik terbuka jika ada langkah-langkah atau definisi yang saya lewatkan.
Thank you and good luck, never stop learning~
Refrensi:
