Fungsi yang ditentukan pengguna (UDF)

Fungsi yang ditentukan pengguna (UDF) memungkinkan Anda membuat fungsi menggunakan ekspresi SQL atau kode JavaScript. UDF menerima kolom input, melakukan tindakan pada input, dan menampilkan hasil tindakan tersebut sebagai nilai.

Anda dapat menentukan UDF sebagai persistent atau temporary. Anda dapat menggunakan kembali persistent UDF di beberapa kueri, sedangkan temporary UDF hanya ada dalam cakupan satu kueri.

Untuk membuat UDF, gunakan pernyataan CREATE FUNCTION. Untuk menghapus fungsi yang ditentukan pengguna persisten (persistent UDF), gunakan pernyataan DROP FUNCTION. Temporary UDF berakhir segera setelah kueri selesai. Pernyataan DROP FUNCTION hanya didukung untuk temporary UDF dalam kueri multi-pernyataan dan prosedur.

Untuk mengetahui informasi tentang UDF di SQL lama, lihat Fungsi yang ditentukan pengguna (UDF) di SQL lama.

UDF SQL

Contoh berikut membuat temporary SQL UDF bernama AddFourAndDivide dan memanggil UDF dari dalam pernyataan SELECT:

CREATE TEMP FUNCTION AddFourAndDivide(x INT64, y INT64)
RETURNS FLOAT64
AS (
  (x + 4) / y
);

SELECT
  val, AddFourAndDivide(val, 2)
FROM
  UNNEST([2,3,5,8]) AS val;

Contoh ini menghasilkan output berikut:

+-----+-----+
| val | f0_ |
+-----+-----+
|   2 | 3.0 |
|   3 | 3.5 |
|   5 | 4.5 |
|   8 | 6.0 |
+-----+-----+

Contoh berikutnya membuat fungsi yang sama seperti persistent UDF:

CREATE FUNCTION mydataset.AddFourAndDivide(x INT64, y INT64)
RETURNS FLOAT64
AS (
  (x + 4) / y
);

Karena UDF ini bersifat persisten, Anda harus menentukan set data untuk fungsi tersebut (dalam contoh ini mydataset). Setelah menjalankan pernyataan CREATE FUNCTION, Anda dapat memanggil fungsi dari kueri:

SELECT
  val, mydataset.AddFourAndDivide(val, 2)
FROM
  UNNEST([2,3,5,8,12]) AS val;

Parameter UDF SQL bertemplate

Parameter dengan jenis yang sama dengan ANY TYPE dapat cocok dengan lebih dari satu jenis argumen saat fungsi dipanggil.

  • Jika lebih dari satu parameter memiliki jenis ANY TYPE, BigQuery tidak akan menerapkan hubungan jenis apa pun antara argumen ini.
  • Jenis nilai yang ditampilkan fungsi tidak boleh berupa ANY TYPE. Jenis nilai tersebut harus dihilangkan, yang berarti akan otomatis ditentukan berdasarkan sql_expression, atau jenis eksplisit.
  • Meneruskan argumen fungsi dari jenis yang tidak kompatibel dengan definisi fungsi akan menimbulkan error pada waktu panggilan.

Contoh berikut menunjukkan UDF SQL yang menggunakan parameter bertemplate.

CREATE TEMP FUNCTION addFourAndDivideAny(x ANY TYPE, y ANY TYPE)
AS (
  (x + 4) / y
);

SELECT
  addFourAndDivideAny(3, 4) AS integer_input,
  addFourAndDivideAny(1.59, 3.14) AS floating_point_input;

Contoh ini menghasilkan output berikut:

+----------------+-----------------------+
| integer_input  |  floating_point_input |
+----------------+-----------------------+
| 1.75           | 1.7802547770700636    |
+----------------+-----------------------+

Contoh berikutnya menggunakan parameter bertemplate untuk menampilkan elemen terakhir array dari jenis apa pun:

CREATE TEMP FUNCTION lastArrayElement(arr ANY TYPE)
AS (
  arr[ORDINAL(ARRAY_LENGTH(arr))]
);

SELECT
  lastArrayElement(x) AS last_element
FROM (
  SELECT [2,3,5,8,13] AS x
);

Contoh ini menghasilkan output berikut:

+--------------+
| last_element |
+--------------+
| 13           |
+--------------+

Subkueri skalar

UDF SQL dapat menampilkan nilai subkueri skalar. Subkueri skalar harus memilih satu kolom.

Contoh berikut menunjukkan UDF SQL yang menggunakan subkueri skalar untuk menghitung jumlah pengguna dengan usia tertentu dalam tabel pengguna:

CREATE TEMP TABLE users
AS (
  SELECT
    1 AS id, 10 AS age
  UNION ALL
  SELECT
    2 AS id, 30 AS age
  UNION ALL
  SELECT
    3 AS id, 10 AS age
);

CREATE TEMP FUNCTION countUserByAge(userAge INT64)
AS (
  (SELECT COUNT(1) FROM users WHERE age = userAge)
);

SELECT
  countUserByAge(10) AS count_user_age_10,
  countUserByAge(20) AS count_user_age_20,
  countUserByAge(30) AS count_user_age_30;

Contoh ini menghasilkan output berikut:

+-------------------+-------------------+-------------------+
| count_user_age_10 | count_user_age_20 | count_user_age_30 |
+-------------------+-------------------+-------------------+
|                 2 |                 0 |                 1 |
+-------------------+-------------------+-------------------+

Project default dalam ekspresi SQL

Dalam isi UDF SQL, setiap referensi ke entity BigQuery, seperti tabel atau tampilan, harus menyertakan project ID, kecuali entity tersebut berada dalam project yang sama yang berisi UDF.

Misalnya, perhatikan pernyataan berikut:

CREATE FUNCTION project1.mydataset.myfunction()
AS (
  (SELECT COUNT(*) FROM mydataset.mytable)
);

Jika Anda menjalankan pernyataan ini dari project1 dan mydataset.mytable ada di project1, pernyataan akan berhasil. Namun, jika Anda menjalankan pernyataan ini dari project yang berbeda, pernyataan tersebut akan gagal. Untuk memperbaiki error tersebut, sertakan project ID dalam referensi tabel:

CREATE FUNCTION project1.mydataset.myfunction()
AS (
  (SELECT COUNT(*) FROM project1.mydataset.mytable)
);

Anda juga dapat mereferensikan entity dalam project atau set data yang berbeda dengan set data tempat Anda membuat fungsi:

CREATE FUNCTION project1.mydataset.myfunction()
AS (
  (SELECT COUNT(*) FROM project2.another_dataset.another_table)
);

UDF JavaScript

UDF JavaScript memungkinkan Anda memanggil kode yang ditulis dalam JavaScript dari kueri SQL. UDF JavaScript biasanya menggunakan lebih banyak resource slot dibandingkan dengan kueri SQL standar sehingga menurunkan performa tugas. Jika fungsi dapat dinyatakan dalam SQL, menjalankan kode sebagai tugas kueri SQL standar sering kali akan lebih optimal.

Contoh berikut menampilkan UDF JavaScript. Kode JavaScript dikutip dalam string mentah.

CREATE TEMP FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r"""
  return x*y;
""";

WITH numbers AS
  (SELECT 1 AS x, 5 as y
  UNION ALL
  SELECT 2 AS x, 10 as y
  UNION ALL
  SELECT 3 as x, 15 as y)
SELECT x, y, multiplyInputs(x, y) AS product
FROM numbers;

Contoh ini menghasilkan output berikut:

+-----+-----+--------------+
| x   | y   | product      |
+-----+-----+--------------+
| 1   | 5   | 5            |
| 2   | 10  | 20           |
| 3   | 15  | 45           |
+-----+-----+--------------+

Contoh berikutnya menjumlahkan nilai dari semua kolom bernama foo di string JSON tertentu.

CREATE TEMP FUNCTION SumFieldsNamedFoo(json_row STRING)
RETURNS FLOAT64
LANGUAGE js
AS r"""
  function SumFoo(obj) {
    var sum = 0;
    for (var field in obj) {
      if (obj.hasOwnProperty(field) && obj[field] != null) {
        if (typeof obj[field] == "object") {
          sum += SumFoo(obj[field]);
        } else if (field == "foo") {
          sum += obj[field];
        }
      }
    }
    return sum;
  }
  var row = JSON.parse(json_row);
  return SumFoo(row);
""";

WITH Input AS (
  SELECT
    STRUCT(1 AS foo, 2 AS bar, STRUCT('foo' AS x, 3.14 AS foo) AS baz) AS s,
    10 AS foo
  UNION ALL
  SELECT
    NULL,
    4 AS foo
  UNION ALL
  SELECT
    STRUCT(NULL, 2 AS bar, STRUCT('fizz' AS x, 1.59 AS foo) AS baz) AS s,
    NULL AS foo
)
SELECT
  TO_JSON_STRING(t) AS json_row,
  SumFieldsNamedFoo(TO_JSON_STRING(t)) AS foo_sum
FROM Input AS t;

Contoh tersebut menghasilkan output berikut:

+---------------------------------------------------------------------+---------+
| json_row                                                            | foo_sum |
+---------------------------------------------------------------------+---------+
| {"s":{"foo":1,"bar":2,"baz":{"x":"foo","foo":3.14}},"foo":10}       | 14.14   |
| {"s":null,"foo":4}                                                  | 4       |
| {"s":{"foo":null,"bar":2,"baz":{"x":"fizz","foo":1.59}},"foo":null} | 1.59    |
+---------------------------------------------------------------------+---------+

Jenis data UDF JavaScript yang didukung

Beberapa jenis SQL memiliki pemetaan langsung ke jenis JavaScript, tetapi yang lainnya tidak. BigQuery merepresentasikan jenis dengan cara berikut:

Jenis data BigQuery Jenis data JavaScript
ARRAY ARRAY
BOOL BOOLEAN
BYTES base64-encoded STRING
FLOAT64 NUMBER
NUMERIC, BIGNUMERIC Jika nilai NUMERIC atau BIGNUMERIC dapat direpresentasikan secara persis sebagai nilai floating point IEEE 754 dan tidak memiliki bagian pecahan, nilai tersebut akan dienkode sebagai Angka. Nilai ini berada dalam rentang [-253, 253]. Atau, nilai ini akan dienkode sebagai string.
STRING STRING
STRUCT OBJECT dengan setiap kolom STRUCT merupakan kolom bernama
TIMESTAMP DATE dengan kolom mikrodetik yang berisi sebagian kecil microsecond stempel waktu
DATE DATE
JSON

JSON OBJECTS, ARRAYS, and VALUES dikonversi menjadi JavaScript OBJECTS, ARRAYS, and VALUES. yang setara

JavaScript tidak mendukung nilai INT64. Hanya angka JSON dalam rentang [-253, 253] yang dikonversi secara tepat. Jika tidak, nilai numerik akan dibulatkan, yang dapat mengakibatkan hilangnya presisi.

Karena JavaScript tidak mendukung jenis bilangan bulat 64-bit, INT64 tidak didukung sebagai jenis input untuk UDF JavaScript. Sebagai gantinya, gunakan FLOAT64 untuk merepresentasikan nilai bilangan bulat sebagai angka, atau STRING untuk merepresentasikan nilai bilangan bulat sebagai string.

BigQuery mendukung INT64 sebagai jenis nilai yang ditampilkan di UDF JavaScript. Dalam hal ini, isi fungsi JavaScript dapat menampilkan Nomor JavaScript atau String. Lalu, BigQuery mengonversi salah satu jenis ini menjadi INT64.

Jika nilai hasil UDF JavaScript adalah Promise, BigQuery akan menunggu Promise hingga Promise diselesaikan. Jika Promise ditetapkan ke status terpenuhi, BigQuery akan menampilkan hasilnya. Jika Promise ditetapkan ke status ditolak, BigQuery akan menampilkan error.

Aturan tanda kutip

Anda harus menutup kode JavaScript dalam tanda kutip. Untuk cuplikan kode satu baris, Anda dapat menggunakan string kutipan standar:

CREATE TEMP FUNCTION plusOne(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS "return x+1;";

SELECT val, plusOne(val) AS result
FROM UNNEST([1, 2, 3, 4, 5]) AS val;

Contoh ini menghasilkan output berikut:

+-----------+-----------+
| val       | result    |
+-----------+-----------+
| 1         | 2.0       |
| 2         | 3.0       |
| 3         | 4.0       |
| 4         | 5.0       |
| 5         | 6.0       |
+-----------+-----------+

Jika cuplikan berisi tanda kutip, atau terdiri dari beberapa baris, gunakan blok tiga tanda kutip:

CREATE TEMP FUNCTION customGreeting(a STRING)
RETURNS STRING
LANGUAGE js
AS r"""
  var d = new Date();
  if (d.getHours() < 12) {
    return 'Good Morning, ' + a + '!';
  } else {
    return 'Good Evening, ' + a + '!';
  }
""";

SELECT customGreeting(names) AS everyone
FROM UNNEST(['Hannah', 'Max', 'Jakob']) AS names;

Contoh ini menghasilkan output berikut:

+-----------------------+
| everyone              |
+-----------------------+
| Good Morning, Hannah! |
| Good Morning, Max!    |
| Good Morning, Jakob!  |
+-----------------------+

Menyertakan library JavaScript

Anda dapat memperluas UDF JavaScript menggunakan bagian OPTIONS. Bagian ini memungkinkan Anda menentukan library kode eksternal untuk UDF.

CREATE TEMP FUNCTION myFunc(a FLOAT64, b STRING)
RETURNS STRING
LANGUAGE js
  OPTIONS (
    library=['gs://my-bucket/path/to/lib1.js', 'gs://my-bucket/path/to/lib2.js'])
AS r"""
  // Assumes 'doInterestingStuff' is defined in one of the library files.
  return doInterestingStuff(a, b);
""";

SELECT myFunc(3.14, 'foo');

Pada contoh sebelumnya, kode dalam lib1.js dan lib2.js tersedia untuk kode apa pun di bagian [external_code] pada UDF.

Praktik terbaik untuk UDF JavaScript

Filter input Anda terlebih dahulu

Jika input Anda dapat difilter sebelum diteruskan ke UDF JavaScript, kueri Anda mungkin akan lebih cepat dan lebih murah.

Menghindari status yang dapat berubah secara persisten

Jangan menyimpan atau mengakses status yang dapat berubah di seluruh panggilan UDF JavaScript. Misalnya, hindari pola berikut:

-- Avoid this pattern
CREATE FUNCTION temp.mutable()
RETURNS INT64
LANGUAGE js
AS r"""
  var i = 0; // Mutable state
  function dontDoThis() {
    return ++i;
  }
  return dontDoThis()
""";

Menggunakan memori secara efisien

Lingkungan pemrosesan JavaScript menyediakan memori yang terbatas per kueri. Kueri UDF JavaScript yang mengakumulasi terlalu banyak status lokal mungkin gagal karena kehabisan memori.

Mengizinkan rutinitas

Anda dapat mengizinkan UDF sebagai rutinitas. Rutinitas yang diizinkan memungkinkan Anda membagikan hasil kueri kepada pengguna atau grup tertentu tanpa memberi mereka akses ke tabel pokok yang menampilkan hasil tersebut. Misalnya, rutinitas yang diizinkan dapat menghitung agregasi atas data atau mencari nilai tabel dan menggunakan nilai tersebut dalam komputasi. Untuk mengetahui informasi selengkapnya, lihat Rutinitas yang diizinkan.

Menambahkan deskripsi ke UDF

Untuk menambahkan deskripsi ke UDF, ikuti langkah-langkah berikut:

Konsol

  1. Buka halaman BigQuery di konsol Google Cloud .

    Buka BigQuery

  2. Di panel kiri, klik Explorer:

    Tombol yang ditandai untuk panel Explorer.

    Jika Anda tidak melihat panel kiri, klik Luaskan panel kiri untuk membuka panel.

  3. Di panel Explorer, luaskan project Anda dan klik Set data.

  4. Klik set data Anda. Anda juga dapat menggunakan fitur penelusuran atau filter untuk menemukan set data Anda.

  5. Klik tab Rutinitas, lalu pilih fungsi.

  6. Di panel detail, klik Edit Routine Details untuk mengedit teks deskripsi.

  7. Pada dialog, masukkan deskripsi dalam kotak atau edit deskripsi yang ada. Klik Save untuk menyimpan teks deskripsi baru.

SQL

Untuk memperbarui deskripsi fungsi, buat ulang fungsi menggunakan pernyataan DDL CREATE FUNCTION dan tetapkan kolom description dalam daftar OPTIONS:

  1. Di konsol Google Cloud , buka halaman BigQuery.

    Buka BigQuery

  2. Di editor kueri, masukkan pernyataan berikut:

    CREATE OR REPLACE FUNCTION mydataset.my_function(...)
AS (
  ...
) OPTIONS (
  description = 'DESCRIPTION'
);

  3. Klik Run.

Untuk mengetahui informasi selengkapnya tentang cara menjalankan kueri, lihat artikel Menjalankan kueri interaktif.

Membuat rutinitas penyamaran kustom

Anda dapat membuat UDF untuk digunakan dengan rutinitas penyamaran kustom. Anda harus membuat set data khusus dan menyiapkan izin IAM yang tepat untuk mengelola UDF penyamaran. Rutinitas penyamaran kustom harus memenuhi persyaratan berikut:

Misalnya, rutinitas penyamaran yang mengganti nomor jaminan sosial pengguna dengan XXX-XX-XXXX mungkin akan terlihat sebagai berikut:

  CREATE OR REPLACE FUNCTION SSN_Mask(ssn STRING) RETURNS STRING
  OPTIONS (data_governance_type="DATA_MASKING") AS (
  SAFE.REGEXP_REPLACE(ssn, '[0-9]', 'X') # 123-45-6789 -> XXX-XX-XXXX
  );

Contoh berikut melakukan hashing dengan salt yang disediakan pengguna, menggunakan fungsi SHA256:

CREATE OR REPLACE FUNCTION `project.dataset.masking_routine1`(
  ssn STRING)
RETURNS STRING OPTIONS (data_governance_type = 'DATA_MASKING')
AS (
  CAST(SHA256(CONCAT(ssn, 'salt')) AS STRING format 'HEX')
);

Contoh berikut menyamarkan kolom DATETIME dengan nilai konstanta:

CREATE OR REPLACE FUNCTION `project.dataset.masking_routine2`(
  column DATETIME)
RETURNS DATETIME OPTIONS (data_governance_type = 'DATA_MASKING')
AS (
  SAFE_CAST('2023-09-07' AS DATETIME)
);

Sebagai praktik terbaik, gunakan SAFE awalan jika memungkinkan untuk menghindari pemaparan data mentah melalui pesan error.

Setelah Anda membuat rutinitas penyamaran kustom, rutinitas tersebut akan tersedia sebagai aturan penyamaran di bagian Membuat kebijakan data.

Fungsi yang dikontribusikan komunitas

UDF yang dikontribusikan komunitas tersedia di set data publik bigquery-public-data.persistent_udfs dan repositori GitHub bigquery-utils open source. Anda dapat melihat semua UDF komunitas di konsol Google Cloud dengan membintangi project bigquery-public-data di panel Explorer, lalu meluaskan set data persistent_udfs bertingkat dalam project tersebut.

Mengizinkan akses ke fungsi yang dikontribusikan komunitas dalam perimeter Kontrol Layanan VPC

Untuk project yang Kontrol Layanan VPC-nya diaktifkan dan BigQuery adalah layanan yang dilindungi, Anda harus menentukan aturan traffic keluar ke project bigquery-public-data (Project ID: 1057666841514).

Aturan ini harus mengaktifkan operasi berikut:

  • bigquery.routines.get (untuk menggunakan rutinitas)
  • bigquery.tables.getData (untuk membuat kueri tabel BigQuery)

Kode berikut menunjukkan contoh konfigurasi YAML:

  - egressFrom:
      identityType: ANY_IDENTITY
    egressTo:
      operations:
      - serviceName: 'bigquery.googleapis.com'
        methodSelectors:
        - permission: 'bigquery.routines.get'
        - permission: 'bigquery.tables.getData'
      resources:
      - projects/1057666841514 # bigquery-public-data

Jika ingin berkontribusi pada UDF dalam repositori ini, lihat Memberikan UDF untuk mengetahui petunjuknya.

Akses terpadu ke rutinitas di beberapa wilayah

Untuk menggunakan UDF dalam kueri di beberapa region, UDF harus tersedia di setiap region tempat kueri yang berisi UDF dijalankan. Oleh karena itu, Anda harus membuat dan mengelola UDF di region mana pun tempat Anda dapat menggunakan UDF dalam kueri. Meskipun tabel Anda identik, Anda harus mempertahankan 2 versi fungsi. Misalnya, jika Anda menyimpan data penjualan di multi-region EU dan US, Anda harus mempertahankan versi fungsi di setiap region. Contoh:

Kueri di multi-region EU: 

  SELECT 
    id,
    europe_dataset.my_function(value)
  FROM
    sales;

Kueri di multi-region US: 

  SELECT 
    id,
    us_dataset.my_function(value)
  FROM
    sales;

Selain itu, saat definisi fungsi berubah, Anda harus memperbaruinya di semua region.

Untuk membuat UDF Anda tidak bergantung pada region, Anda dapat menggunakan replikasi set data lintas region:

  1. Buat set data khusus baru, misalnya my_utils, untuk menyimpan semua UDF yang diperlukan. Perlu diingat bahwa setiap tabel yang ditambahkan ke set data ini akan direplikasi, yang akan meningkatkan biaya. Namun, mereplikasi UDF dan prosedur tidak menimbulkan biaya tambahan.
  2. Tambahkan semua UDF Anda ke set data baru. Hal ini juga dapat mencakup UDF komunitas seperti bqutil yang disalin dari GitHub.
  3. Aktifkan replikasi set data. Konfigurasi set data ini agar direplikasi ke semua region tempat Anda perlu menjalankan kueri yang memanggil UDF ini. Tindakan ini akan menyalin fungsi Anda ke wilayah tersebut dan menyinkronkannya.

Saat Anda menjalankan kueri, BigQuery akan otomatis menggunakan UDF versi lokal dari replika set data lokal tanpa Anda menentukan region tempat fungsi ditentukan, sehingga kueri Anda dapat digunakan di berbagai lokasi. Contoh:

  SELECT 
    id,
    my_utils.my_function(value)
  FROM
    sales;

Batasan

Batasan berikut berlaku untuk fungsi yang ditentukan fungsi yang ditentukan pengguna persisten (persistent UDF) dan fungsi sementara:

  • Objek DOM Window, Document, dan Node, serta fungsi yang memerlukan objek tersebut, tidak didukung.
  • Fungsi JavaScript beroperasi dalam lingkungan sandbox, dan fungsi yang mengandalkan kode sistem yang mendasarinya mungkin gagal karena panggilan sistem yang dibatasi.
  • Waktu tunggu UDF JavaScript dapat habis dan membuat kueri Anda tidak selesai. Waktu tunggu bisa paling singkat 5 menit, tetapi dapat bervariasi bergantung pada beberapa faktor, termasuk berapa banyak waktu CPU yang digunakan fungsi Anda serta seberapa besar input dan output Anda ke fungsi JavaScript.
  • Operasi bitwise dalam JavaScript hanya menangani 32 bit yang paling signifikan.
  • UDF tunduk kepada batas kapasitas dan batas kuota tertentu. Untuk mengetahui informasi selengkapnya, lihat Batas UDF.

Batasan berikut berlaku untuk fungsi yang ditentukan pengguna persisten:

  • Setiap set data hanya dapat berisi satu persistent UDF dengan nama yang sama. Namun, Anda dapat membuat UDF yang namanya sama dengan nama tabel dalam set data yang sama.
  • Saat mereferensikan persistent UDF dari persistent UDF lainnya atau tampilan logis, Anda harus memenuhi syarat nama dengan set data. Contoh:
    CREATE FUNCTION mydataset.referringFunction() AS (mydataset.referencedFunction());

Batasan berikut berlaku untuk fungsi yang ditentukan pengguna sementara (temporary UDF).

  • Saat membuat temporary UDF, function_name tidak boleh berisi titik.
  • Tabel virtual dan persistent UDF tidak dapat mereferensikan temporary UDF.