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 berdasarkansql_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, semua referensi ke entity BigQuery, seperti tabel atau tampilan, harus menyertakan project ID, kecuali jika 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 sederhana 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
Buka halaman BigQuery di Konsol Google Cloud.
Di panel Explorer, luaskan project dan set data Anda, lalu pilih fungsi.
Di panel Details, klik
Edit Routine Details untuk mengedit teks deskripsi.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
:
Di konsol Google Cloud, buka halaman BigQuery.
Di editor kueri, masukkan pernyataan berikut:
CREATE OR REPLACE FUNCTION mydataset.my_function(...) AS ( ... ) OPTIONS ( description = 'DESCRIPTION' );
Klik
Run.
Untuk informasi selengkapnya tentang cara menjalankan kueri, lihat Menjalankan kueri interaktif.
Membuat rutinitas penyamaran kustom
Anda dapat membuat UDF untuk digunakan dengan rutinitas penyamaran kustom. Rutinitas penyamaran kustom harus memenuhi persyaratan berikut:
- Rutinitas penyamaran kustom harus berupa UDF SQL.
- Dalam fungsi
OPTIONS
, opsidata_governance_type
harus ditetapkan keDATA_MASKING
. - Rutinitas masking kustom mendukung fungsi berikut:
- Fungsi string
REGEXP_REPLACE
- Fungsi hash
FARM_FINGERPINT
- Fungsi hash
MD5
- Fungsi hash
SHA1
- Fungsi hash
SHA256
- Fungsi hash
SHA512
- Fungsi konversi
CAST
- Fungsi string
CONCAT
- Fungsi string
- Rutinitas penyamaran kustom dapat menerima tanpa input atau satu input dalam jenis data BigQuery, kecuali
GEOGRAPHY
danSTRUCT
.GEOGRAPHY
danSTRUCT
tidak didukung untuk rutinitas penyamaran kustom. - Parameter UDF SQL bertemplate tidak didukung.
- Saat input diberikan, jenis data input dan output harus sama.
- Jenis output harus disediakan.
- Tidak ada UDF, subkueri, tabel, atau tampilan lain yang dapat direferensikan dalam isi definisi.
- Setelah membuat rutinitas penyamaran, rutinitas tidak dapat diubah menjadi fungsi standar. Artinya, jika opsi
data_governance_type
disetel keDATA_MASKING
, Anda tidak dapat mengubahdata_governance_type
menggunakan pernyataan DDL atau panggilan API.
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 hash berikut 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 konstan:
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) );
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 ini, lalu memperluas set data persistent_udfs
bertingkat dalam project tersebut.
Jika ingin berkontribusi pada UDF dalam repositori ini, lihat Memberikan UDF untuk mengetahui petunjuknya.
Batasan
Batasan berikut berlaku untuk fungsi yang ditentukan fungsi yang ditentukan pengguna persisten (persistent UDF) dan fungsi sementara:
- Objek DOM
Window
,Document
, danNode
, serta fungsi yang memerlukan objek tersebut, tidak didukung. - Fungsi JavaScript yang mengandalkan kode native dapat gagal, misalnya, jika melakukan 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.