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.

Catatan: Persistent UDF aman untuk dipanggil jika dibagikan antarpemilik. UDF tidak dapat mengubah data, berkomunikasi dengan sistem eksternal, atau mengirim log ke Kemampuan Observasi Google Cloud atau aplikasi serupa.

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, 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 [-2^53, 2^53]. 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 [-2^53, 2^53] 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

1. Buka halaman BigQuery di Konsol Google Cloud.

   Buka BigQuery

2. Di panel Explorer, luaskan project dan set data Anda, lalu pilih fungsi.

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

4. 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 informasi selengkapnya tentang cara menjalankan kueri, lihat Menjalankan kueri interaktif.

Membuat rutinitas penyamaran kustom

Catatan: Fitur ini mungkin tidak tersedia saat menggunakan reservasi yang dibuat dengan edisi BigQuery tertentu. Untuk mengetahui informasi selengkapnya fitur mana saja yang diaktifkan di setiap edisi, lihat Pengantar edisi BigQuery.

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, opsi data_governance_type harus ditetapkan ke DATA_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
• Rutinitas penyamaran kustom dapat menerima tanpa input atau satu input dalam jenis data BigQuery, kecuali GEOGRAPHY dan STRUCT. GEOGRAPHY dan STRUCT 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 ke DATA_MASKING, Anda tidak dapat mengubah data_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, dan Node, 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.