Fungsi yang ditentukan pengguna di legacy SQL

Dokumen ini menjelaskan cara menggunakan fungsi JavaScript yang ditentukan pengguna dalam sintaksis kueri legacy SQL. Sintaksis kueri pilihan untuk BigQuery adalah GoogleSQL. Untuk mengetahui informasi tentang fungsi yang ditentukan pengguna (UDF) di GoogleSQL, lihat Fungsi GoogleSQL yang ditentukan pengguna.

Legacy SQL BigQuery mendukung fungsi yang ditentukan pengguna (UDF) (UDF) yang ditulis dalam JavaScript. UDF mirip dengan fungsi "Map" di MapReduce: UDF mengambil satu baris sebagai input dan menghasilkan nol baris atau beberapa baris sebagai output. Output tersebut dapat berpotensi memiliki skema yang berbeda dengan inputnya.

Untuk mengetahui informasi tentang fungsi yang ditentukan pengguna (UDF) di GoogleSQL, lihat fungsi yang ditentukan pengguna (UDF) di GoogleSQL.

Contoh UDF

// UDF definition
function urlDecode(row, emit) {
  emit({title: decodeHelper(row.title),
        requests: row.num_requests});
}

// Helper function with error handling
function decodeHelper(s) {
  try {
    return decodeURI(s);
  } catch (ex) {
    return s;
  }
}

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  urlDecode  // The function reference
);

Kembali ke atas

Struktur UDF

function name(row, emit) {
  emit(<output data>);
}

UDF BigQuery beroperasi pada tiap-tiap baris pada tabel atau hasil kueri subpilihan. UDF memiliki dua parameter formal:

  • row: baris input.
  • emit: hook yang digunakan oleh BigQuery untuk mengumpulkan data output. Fungsi emit mengambil satu parameter: objek JavaScript yang merepresentasikan satu baris data output. Fungsi emit dapat dipanggil lebih dari sekali, misalnya dalam satu loop, untuk menghasilkan beberapa baris data.

Contoh kode berikut menunjukkan UDF dasar.

function urlDecode(row, emit) {
  emit({title: decodeURI(row.title),
        requests: row.num_requests});
}

Mendaftarkan UDF

Anda harus mendaftarkan nama untuk fungsi Anda agar fungsi tersebut dapat dipanggil dari BigQuery SQL. Nama yang terdaftar tidak harus sama dengan nama yang Anda gunakan untuk fungsi di JavaScript.

bigquery.defineFunction(
  '<UDF name>',  // Name used to call the function from SQL

  ['<col1>', '<col2>'],  // Input column names

  // JSON representation of the output schema
  [<output schema>],

  // UDF definition or reference
  <UDF definition or reference>
);

Kolom input

Nama kolom input harus cocok dengan nama (atau alias, jika ada) kolom dalam tabel atau subkueri input.

Untuk kolom input yang berupa kumpulan data, Anda harus menentukan—dalam daftar kolom input—kolom leaf yang ingin diakses dari kumpulan data.

Misalnya, jika Anda memiliki data yang menyimpan nama dan usia seseorang:

person RECORD REPEATED
  name STRING OPTIONAL
  age INTEGER OPTIONAL

Penentu input untuk nama dan usia adalah:

['person.name', 'person.age']

Penggunaan ['person'] tanpa nama atau usia akan mengakibatkan error.

Output yang dihasilkan akan cocok dengan skema. Anda akan memiliki array objek JavaScript, dengan setiap objek memiliki properti "nama" dan "usia". Contoh:

[ {name: 'alice', age: 23}, {name: 'bob', age: 64}, ... ]

Skema output

Anda harus memberi BigQuery skema atau struktur kumpulan yang dihasilkan UDF, yang direpresentasikan sebagai JSON. Skema dapat berisi jenis data BigQuery yang didukung termasuk kumpulan data bertingkat. Penentu jenis yang didukung adalah:

  • boolean
  • float
  • bilangan bulat
  • kumpulan data
  • string
  • stempel waktu

Contoh kode berikut menunjukkan sintaksis untuk kumpulan data dalam skema output. Setiap kolom output memerlukan atribut name dan type. Kolom bertingkat juga harus berisi atribut fields.

[{name: 'foo_bar', type: 'record', fields:
  [{name: 'a', type: 'string'},
   {name: 'b', type: 'integer'},
   {name: 'c', type: 'boolean'}]
}]

Setiap kolom dapat berisi atribut mode opsional, yang mendukung nilai berikut:

  • nullable : nilai ini tersedia secara default dan dapat dihilangkan.
  • wajib : jika ditentukan, kolom yang dimaksud harus ditetapkan ke sebuah nilai dan tidak boleh tidak ditetapkan.
  • berulang : jika ditetapkan, kolom yang diberikan harus berupa array.

Baris yang diteruskan ke fungsi emit() harus cocok dengan jenis data skema output. Kolom yang direpresentasikan dalam skema output yang dihilangkan dalam fungsi emit akan menghasilkan output sebagai null.

Definisi atau referensi UDF

Kalau mau, Anda dapat menentukan UDF secara inline di bigquery.defineFunction. Contoh:

bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  // The UDF
  function(row, emit) {
    emit({title: decodeURI(row.title),
          requests: row.num_requests});
  }
);

Jika tidak, Anda dapat menentukan UDF secara terpisah, dan meneruskan referensi ke fungsi di bigquery.defineFunction. Contoh:

// The UDF
function urlDecode(row, emit) {
  emit({title: decodeURI(row.title),
        requests: row.num_requests});
}

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  urlDecode  // The function reference
);

Penanganan error

Jika pengecualian atau error ditampilkan selama pemrosesan UDF, seluruh kueri akan gagal. Anda dapat menggunakan blok try-catch untuk menangani error. Contoh:

// The UDF
function urlDecode(row, emit) {
  emit({title: decodeHelper(row.title),
        requests: row.num_requests});
}

// Helper function with error handling
function decodeHelper(s) {
  try {
    return decodeURI(s);
  } catch (ex) {
    return s;
  }
}

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  urlDecode  // The function reference
);

Menjalankan kueri dengan UDF

Anda dapat menggunakan UDF pada legacy SQL dengan alat command line bq atau BigQuery API. Konsol Google Cloud tidak mendukung UDF dalam legacy SQL.

Menggunakan alat command line bq

Untuk menjalankan kueri yang berisi satu atau beberapa UDF, tentukan flag --udf_resource pada alat command line bq dari Google Cloud CLI. Nilai flag ini dapat berupa URI (gs://...) Cloud Storage atau jalur ke file lokal. Untuk menentukan beberapa file resource UDF, ulangi flag ini.

Gunakan sintaksis berikut untuk menjalankan kueri dengan UDF:

bq query --udf_resource=<file_path_or_URI> <sql_query>

Contoh berikut menjalankan kueri yang menggunakan UDF yang disimpan dalam file lokal dan kueri SQL yang juga disimpan dalam file lokal.

Membuat UDF

Anda dapat menyimpan UDF di Cloud Storage atau sebagai file teks lokal. Misalnya, untuk menyimpan UDF urlDecode berikut, buat file bernama urldecode.js dan tempelkan kode JavaScript berikut ke dalam file tersebut sebelum menyimpan file.

// UDF definition
function urlDecode(row, emit) {
  emit({title: decodeHelper(row.title),
        requests: row.num_requests});
}

// Helper function with error handling
function decodeHelper(s) {
  try {
    return decodeURI(s);
  } catch (ex) {
    return s;
  }
}

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  urlDecode  // The function reference
);

Membuat kueri

Anda juga dapat menyimpan kueri dalam file agar command line tidak terlalu panjang. Misalnya, Anda dapat membuat file lokal bernama query.sql dan menempelkan pernyataan BigQuery berikut ke dalam file tersebut.

#legacySQL
SELECT requests, title
FROM
  urlDecode(
    SELECT
      title, sum(requests) AS num_requests
    FROM
      [fh-bigquery:wikipedia.pagecounts_201504]
    WHERE language = 'fr'
    GROUP EACH BY title
  )
WHERE title LIKE '%ç%'
ORDER BY requests DESC
LIMIT 100

Setelah menyimpan file, Anda dapat mereferensikan file tersebut pada command line.

Menjalankan kueri

Setelah menentukan UDF dan kueri dalam file terpisah, Anda dapat mereferensikan uDF dan kueri pada command line. Misalnya, perintah berikut menjalankan kueri yang Anda simpan sebagai file bernama query.sql dan mereferensikan UDF yang Anda buat.

$ bq query --udf_resource=urldecode.js "$(cat query.sql)"

Menggunakan BigQuery API

configuration.query

Kueri yang menggunakan UDF harus berisi elemen userDefinedFunctionResources yang menyediakan kode, atau lokasi untuk membuat kode resource, yang akan digunakan dalam kueri. Kode yang disediakan harus menyertakan pemanggilan fungsi pendaftaran untuk setiap UDF yang direferensikan oleh kueri.

Resource kode

Konfigurasi kueri Anda dapat mencakup blob kode JavaScript, serta referensi ke file sumber JavaScript yang tersimpan di Cloud Storage.

Blob kode JavaScript inline diisi di bagian inlineCode pada elemen userDefinedFunctionResource. Namun, kode yang akan digunakan kembali atau direferensikan di beberapa kueri harus dipertahankan di Cloud Storage dan direferensikan sebagai resource eksternal.

Untuk mereferensikan file sumber JavaScript dari Cloud Storage, tetapkan bagian resourceURI dari elemen userDefinedFunctionResource ke URI gs:// file.

Konfigurasi kueri dapat berisi beberapa elemen userDefinedFunctionResource. Setiap elemen dapat berisi bagian inlineCode atau resourceUri.

Contoh

Contoh JSON berikut menunjukkan permintaan kueri yang mereferensikan dua resource UDF: satu blob kode inline, dan satu file lib.js untuk dibaca dari Cloud Storage. Dalam contoh ini, myFunc dan pemanggilan pendaftaran untuk myFunc disediakan oleh lib.js.

{
  "configuration": {
    "query": {
      "userDefinedFunctionResources": [
        {
          "inlineCode": "var someCode = 'here';"
        },
        {
          "resourceUri": "gs://some-bucket/js/lib.js"
        }
      ],
      "query": "select a from myFunc(T);"
    }
  }
}

Kembali ke atas

Praktik terbaik

Mengembangkan UDF Anda

Anda bisa menggunakan alat pengujian UDF untuk menguji dan men-debug UDF tanpa menghabiskan tagihan BigQuery.

Memfilter input terlebih dahulu

Jika input Anda dapat difilter dengan mudah sebelum diteruskan ke UDF, kueri Anda kemungkinan akan lebih cepat dan lebih terjangkau.

Dalam contoh menjalankan kueri, subkueri diteruskan sebagai input ke urlDecode, bukan tabel penuh. Tabel [fh-bigquery:wikipedia.pagecounts_201504] memiliki sekitar 5,6 miliar baris, dan jika kita menjalankan UDF di seluruh tabel, framework JavaScript perlu memproses lebih dari 21 baris lebih banyak daripada dengan subkueri yang difilter.

Menghindari status persisten yang dapat berubah

Jangan menyimpan atau mengakses status yang dapat berubah pada panggilan UDF. Contoh kode berikut menjelaskan skenario ini:

// myCode.js
var numRows = 0;

function dontDoThis(r, emit) {
  emit({rowCount: ++numRows});
}

// The query.
SELECT max(rowCount) FROM dontDoThis(t);

Contoh di atas tidak akan berperilaku sebagaimana mestinya, karena BigQuery melakukan sharding kueri di banyak node. Setiap node memiliki lingkungan pemrosesan JavaScript mandiri yang mengakumulasi nilai terpisah untuk numRows.

Menggunakan memori secara efisien

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

Meluaskan kueri tertentu

Anda harus mencantumkan kolom yang dipilih dari UDF secara eksplisit. SELECT * FROM <UDF name>(...) tidak didukung.

Untuk memeriksa struktur data baris input, Anda dapat menggunakan JSON.stringify() untuk menghasilkan kolom output string:

bigquery.defineFunction(
  'examineInputFormat',
  ['some', 'input', 'columns'],
  [{name: 'input', type: 'string'}],
  function(r, emit) {
    emit({input: JSON.stringify(r)});
  }
);

Kembali ke atas

Batas

  • Jumlah data yang dihasilkan UDF saat memproses satu baris harus sekitar 5 MB atau lebih sedikit.
  • Setiap pengguna dibatasi untuk menjalankan sekitar 6 kueri UDF dalam project tertentu secara bersamaan. Jika Anda menerima pesan error bahwa Anda melebihi batas kueri serentak, tunggu beberapa menit, lalu coba lagi.
  • UDF dapat berada pada waktu tunggu habis dan mencegah kueri Anda selesai. Waktu tunggu bisa paling singkat 5 menit, tetapi dapat bervariasi tergantung beberapa faktor, termasuk jumlah waktu yang digunakan CPU pengguna yang dipakai oleh fungsi Anda serta besarnya input dan output Anda ke fungsi JS.
  • Tugas kueri dapat memiliki maksimal 50 resource UDF (blob kode inline atau file eksternal).
  • Setiap blob kode inline dibatasi ukuran maksimumnya hingga 32 KB. Untuk menggunakan resource kode yang lebih besar, simpan kode Anda di Cloud Storage dan referensikan sebagai resource eksternal.
  • Setiap resource kode eksternal dibatasi ukuran maksimumnya hingga 1 MB.
  • Ukuran kumulatif semua resource kode eksternal dibatasi maksimum 5 MB.

Kembali ke atas

Batasan

  • Objek DOM Window, Document, dan Node, serta fungsi yang memerlukan objek tersebut, tidak didukung.
  • Fungsi JavaScript yang mengandalkan kode native tidak didukung.
  • Operasi bitwise dalam JavaScript hanya menangani 32 bit yang paling signifikan.
  • Karena sifatnya yang non-deterministik, kueri yang memanggil fungsi yang ditentukan pengguna (UDF) tidak dapat menggunakan hasil yang di-cache.

Kembali ke atas