Fungsi tabel

Fungsi tabel, yang juga disebut fungsi yang bernilai tabel (TVF), merupakan fungsi yang ditentukan pengguna dan menampilkan tabel. Anda bisa menggunakan fungsi tabel di mana pun Anda bisa menggunakan tabel. Fungsi tabel berperilaku mirip dengan tampilan, tetapi fungsi tabel dapat mengambil parameter.

Membuat fungsi tabel

Untuk membuat fungsi tabel, gunakan pernyataan CREATE TABLE FUNCTION. Fungsi tabel berisi kueri yang menghasilkan tabel. Fungsi ini menampilkan hasil kueri. Fungsi tabel berikut mengambil parameter INT64 dan menggunakan nilai ini di dalam klausa WHERE dalam kueri melalui set data publik yang disebut bigquery-public-data.usa_names.usa_1910_current .

CREATE OR REPLACE TABLE FUNCTION mydataset.names_by_year(y INT64)
AS (
  SELECT year, name, SUM(number) AS total
  FROM `bigquery-public-data.usa_names.usa_1910_current`
  WHERE year = y
  GROUP BY year, name
);

Untuk memfilter dengan cara lain, Anda dapat meneruskan beberapa parameter ke fungsi tabel. Fungsi tabel berikut memfilter data menurut tahun dan awalan nama:

CREATE OR REPLACE TABLE FUNCTION mydataset.names_by_year_and_prefix(
  y INT64, z STRING)
AS (
  SELECT year, name, SUM(number) AS total
  FROM `bigquery-public-data.usa_names.usa_1910_current`
  WHERE
    year = y
    AND STARTS_WITH(name, z)
  GROUP BY year, name
);

Parameter tabel

Anda dapat menetapkan parameter TVF sebagai tabel. Setelah nama parameter tabel, Anda harus menentukan skema tabel yang diperlukan secara eksplisit, dengan cara yang sama seperti saat Anda menentukan kolom struct. Argumen tabel yang Anda teruskan ke TVF dapat berisi kolom tambahan selain yang ditentukan dalam skema parameter, dan kolom dapat muncul dalam urutan apa pun.

Fungsi tabel berikut menampilkan tabel yang berisi total penjualan untuk item_name dari tabel orders:

CREATE TABLE FUNCTION mydataset.compute_sales (
  orders TABLE<sales INT64, item STRING>, item_name STRING)
AS (
  SELECT SUM(sales) AS total_sales, item
  FROM orders
  WHERE item = item_name
  GROUP BY item
);

Menggunakan jenis ANY TABLE di TVF memungkinkan Anda membuat fungsi generik yang menerima tabel dengan struktur apa pun. Tidak seperti tabel tetap—tempat Anda harus menentukan setiap kolom dan jenis data—jenis ANY TABLE bertindak sebagai placeholder, sehingga fungsi dapat digunakan kembali dengan set data yang berbeda. Namun, tabel yang Anda teruskan harus tetap berisi kolom tertentu yang digunakan di dalam fungsi. Misalnya, jika fungsi Anda memfilter berdasarkan created_date, tabel input Anda harus memiliki kolom tersebut, atau kueri akan gagal.

-- Simple pass-through (accepts any table, as used in your devstack)
CREATE OR REPLACE TABLE FUNCTION dataset_test.MyTVF(input_table ANY TABLE)
AS
SELECT * FROM input_table;

-- Generic filter (accepts any table, but implicitly requires a 'status' column)
CREATE TEMP TABLE FUNCTION FilterByStatus(input_table ANY TABLE, status_val STRING)
AS
SELECT * FROM input_table
WHERE status = status_val;

Nama parameter

Jika parameter fungsi tabel cocok dengan nama kolom tabel, parameter tersebut dapat membuat referensi yang ambigu. Dalam hal ini, BigQuery akan menafsirkan nama tersebut sebagai referensi ke kolom tabel, bukan parameter. Praktik yang direkomendasikan adalah menggunakan nama parameter yang berbeda dengan nama kolom tabel yang direferensikan.

Menggunakan fungsi tabel

Anda dapat memanggil fungsi tabel dalam konteks apa pun jika tabel tersebut valid. Contoh berikut memanggil fungsi mydataset.names_by_year dalam klausa FROM pernyataan SELECT:

SELECT * FROM mydataset.names_by_year(1950)
  ORDER BY total DESC
  LIMIT 5

Hasilnya akan terlihat seperti berikut:

+------+--------+-------+
| year |  name  | total |
+------+--------+-------+
| 1950 | James  | 86447 |
| 1950 | Robert | 83717 |
| 1950 | Linda  | 80498 |
| 1950 | John   | 79561 |
| 1950 | Mary   | 65546 |
+------+--------+-------+

Anda dapat menggabungkan output dari fungsi tabel dengan tabel lain:

SELECT *
  FROM `bigquery-public-data.samples.shakespeare` AS s
  JOIN mydataset.names_by_year(1950) AS n
  ON n.name = s.word

Anda juga dapat menggunakan fungsi tabel di a subkueri:

SELECT ARRAY(
  SELECT name FROM mydataset.names_by_year(1950)
  ORDER BY total DESC
  LIMIT 5)

Saat memanggil fungsi tabel yang memiliki parameter tabel, Anda harus menggunakan kata kunci TABLE sebelum nama argumen tabel. Argumen tabel dapat memiliki kolom yang tidak tercantum dalam skema parameter tabel:

CREATE TABLE FUNCTION mydataset.compute_sales (
  orders TABLE<sales INT64, item STRING>, item_name STRING)
AS (
  SELECT SUM(sales) AS total_sales, item
  FROM orders
  WHERE item = item_name
  GROUP BY item
);

WITH my_orders AS (
    SELECT 1 AS sales, "apple" AS item, 0.99 AS price
    UNION ALL
    SELECT 2, "banana", 0.49
    UNION ALL
    SELECT 5, "apple", 0.99)
SELECT *
FROM mydataset.compute_sales(TABLE my_orders, "apple");

/*-------------+-------+
 | total_sales | item  |
 +-------------+-------+
 | 6           | apple |
 +-------------+-------*/

Menggunakan variabel sistem dengan TVF

Variabel sistem @@session_id dan @@location didukung dengan TVF. Anda dapat menyertakan variabel sistem ini di mana saja dalam pernyataan pembuatan fungsi untuk menampilkan ID sesi atau lokasi kueri saat ini. Semua variabel sistem lainnya tidak didukung.

Mencantumkan fungsi tabel

Fungsi tabel adalah jenis rutinitas. Untuk mencantumkan semua rutinitas dalam set data, lihat Mencantumkan rutinitas.

Menghapus fungsi tabel

Untuk menghapus fungsi tabel, gunakan pernyataan DROP TABLE FUNCTION:

DROP TABLE FUNCTION mydataset.names_by_year

Memberi otorisasi kepada rutinitas

Anda dapat mengizinkan fungsi tabel sebagai rutinitas. Rutinitas yang diotorisasi memungkinkan Anda membagikan hasil kueri kepada pengguna atau grup tertentu tanpa memberi mereka akses ke tabel dasar yang membuat 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 diotorisasi.

Batasan

• Isi kueri harus berupa pernyataan SELECT dan tidak dapat mengubah apa pun. Misalnya, pernyataan bahasa definisi data (DDL) dan bahasa manipulasi data (DML) tidak diizinkan dalam fungsi tabel. Jika Anda memerlukan efek samping, sebaiknya tulis prosedur.

• Fungsi tabel harus disimpan di lokasi yang sama dengan tabel yang dirujuknya.

Kuota

Untuk mengetahui informasi selengkapnya tentang kuota dan batas fungsi tabel, lihat Kuota dan batas.