Praktik terbaik untuk fungsi

Dokumen ini menjelaskan cara mengoptimalkan kueri yang menggunakan fungsi SQL.

Mengoptimalkan perbandingan string

Praktik terbaik: Jika memungkinkan, gunakan LIKE, bukan REGEXP_CONTAINS.

Di BigQuery, Anda dapat menggunakan fungsi REGEXP_CONTAINS atau operator LIKE untuk membandingkan string. REGEXP_CONTAINS menyediakan lebih banyak fungsionalitas, tetapi juga memiliki waktu eksekusi yang lebih lambat. Penggunaan LIKE dan bukan REGEXP_CONTAINS akan lebih cepat, terutama jika Anda tidak memerlukan kekuatan penuh dari ekspresi reguler yang disediakan REGEXP_CONTAINS, misalnya pencocokan karakter pengganti.

Pertimbangkan penggunaan fungsi REGEXP_CONTAINS berikut:

SELECT
  dim1
FROM
  `dataset.table1`
WHERE
  REGEXP_CONTAINS(dim1, '.*test.*');

Anda dapat mengoptimalkan kueri ini sebagai berikut:

SELECT
  dim1
FROM
  `dataset.table`
WHERE
  dim1 LIKE '%test%';

Mengoptimalkan fungsi agregasi

Praktik terbaik: Jika kasus penggunaan Anda mendukungnya, gunakan fungsi agregasi perkiraan.

Jika fungsi agregasi SQL yang Anda gunakan memiliki fungsi perkiraan yang setara, fungsi perkiraan akan menghasilkan performa kueri yang lebih cepat. Misalnya, gunakan APPROX_COUNT_DISTINCT, bukan menggunakan COUNT(DISTINCT). Untuk mengetahui informasi selengkapnya, lihat fungsi perkiraan agregasi.

Anda juga dapat menggunakan fungsi HyperLogLog++ untuk melakukan perkiraan (termasuk agregasi kustom). Untuk mengetahui informasi selengkapnya, lihat Fungsi HyperLogLog++ dalam referensi GoogleSQL.

Pertimbangkan penggunaan fungsi COUNT berikut:

SELECT
  dim1,
  COUNT(DISTINCT dim2)
FROM
  `dataset.table`
GROUP BY 1;

Anda dapat mengoptimalkan kueri ini sebagai berikut:

SELECT
  dim1,
  APPROX_COUNT_DISTINCT(dim2)
FROM
  `dataset.table`
GROUP BY 1;

Mengoptimalkan fungsi kuantil

Praktik terbaik: Jika memungkinkan, gunakan APPROX_QUANTILE, bukan NTILE.

Menjalankan kueri yang berisi fungsi NTILE dapat gagal dengan error Resources exceeded jika ada banyak elemen ke ORDER BY dalam satu partisi, yang menyebabkan volume data meningkat. Jendela analisis tidak dipartisi, sehingga komputasi NTILE memerlukan ORDER BY global untuk semua baris dalam tabel agar diproses oleh satu pekerja/slot.

Coba gunakan APPROX_QUANTILES. Fungsi ini memungkinkan kueri berjalan lebih efisien karena tidak memerlukan ORDER BY global untuk semua baris dalam tabel.

Pertimbangkan penggunaan fungsi NTILE berikut:

SELECT
  individual_id,
  NTILE(nbuckets) OVER (ORDER BY sales desc) AS sales_third
FROM
  `dataset.table`;

Anda dapat mengoptimalkan kueri ini sebagai berikut:

WITH QuantInfo AS (
  SELECT
    o, qval
  FROM UNNEST((
     SELECT APPROX_QUANTILES(sales, nbuckets)
     FROM `dataset.table`
    )) AS qval
  WITH offset o
  WHERE o > 0
)
SELECT
  individual_id,
  (SELECT
     (nbuckets + 1) - MIN(o)
   FROM QuantInfo
   WHERE sales <= QuantInfo.qval
  ) AS sales_third
FROM `dataset.table`;

Versi yang dioptimalkan memberikan hasil yang serupa, tetapi tidak identik dengan kueri asli, karena APPROX_QUANTILES:

1. Memberikan perkiraan agregasi.
2. Menempatkan nilai sisa (sisa jumlah baris yang dibagi dengan bucket) dengan cara yang berbeda.

Mengoptimalkan UDF

Praktik terbaik: Gunakan UDF SQL untuk penghitungan sederhana karena pengoptimal kueri dapat menerapkan pengoptimalan pada definisi UDF SQL. Gunakan UDF JavaScript untuk penghitungan kompleks yang tidak didukung oleh SQL UDF.

Memanggil UDF JavaScript memerlukan pembuatan instance subproses. Memulai proses ini dan menjalankan UDF akan berdampak langsung pada performa kueri. Jika memungkinkan, gunakan UDF native (SQL) sebagai gantinya.

Persistent UDF

Lebih baik membuat fungsi SQL dan JavaScript buatan pengguna yang persisten dalam set data BigQuery terpusat yang dapat dipanggil di seluruh kueri dan dalam tampilan logis, daripada membuat dan memanggil UDF dalam kode setiap saat singkat ini. Membuat library logika bisnis di seluruh organisasi dalam set data bersama akan membantu mengoptimalkan performa dan menggunakan lebih sedikit resource.

Contoh berikut menunjukkan cara UDF sementara dipanggil dalam kueri:

CREATE TEMP FUNCTION addFourAndDivide(x INT64, y INT64) AS ((x + 4) / y);

WITH numbers AS
  (SELECT 1 as val
  UNION ALL
  SELECT 3 as val
  UNION ALL
  SELECT 4 as val
  UNION ALL
  SELECT 5 as val)
SELECT val, addFourAndDivide(val, 2) AS result
FROM numbers;

Anda dapat mengoptimalkan kueri ini dengan mengganti UDF sementara dengan UDF persisten:

WITH numbers AS
  (SELECT 1 as val
  UNION ALL
  SELECT 3 as val
  UNION ALL
  SELECT 4 as val
  UNION ALL
  SELECT 5 as val)
SELECT val, `your_project.your_dataset.addFourAndDivide`(val, 2) AS result
FROM numbers;