Bekerja dengan prosedur tersimpan SQL

Prosedur tersimpan adalah kumpulan pernyataan yang dapat dipanggil dari kueri lain atau prosedur tersimpan lainnya. Prosedur dapat mengambil argumen input dan menampilkan nilai sebagai output. Anda memberi nama dan menyimpan prosedur dalam set data BigQuery. Prosedur tersimpan dapat mengakses atau memodifikasi data di beberapa set data oleh banyak pengguna. Prosedur ini juga dapat berisi kueri multi-pernyataan.

Beberapa prosedur tersimpan telah diintegrasikan ke dalam BigQuery dan tidak perlu dibuat. Ini disebut dengan prosedur sistem dan Anda dapat mempelajarinya lebih lanjut di Referensi prosedur sistem.

Prosedur tersimpan mendukung pernyataan bahasa prosedur, yang memungkinkan Anda melakukan berbagai hal seperti menentukan variabel dan menerapkan alur kontrol. Anda dapat mempelajari pernyataan bahasa prosedur lebih lanjut dalam Referensi bahasa prosedur.

Membuat prosedur tersimpan

Pilih salah satu opsi berikut untuk membuat prosedur tersimpan:

SQL

Untuk membuat prosedur, gunakan pernyataan CREATE PROCEDURE.

Dalam contoh konseptual berikut, procedure_name mewakili prosedur dan isi prosedur yang muncul di antara pernyataan BEGIN dan END:

CREATE PROCEDURE dataset_name.procedure_name()
BEGIN
-- statements here
END

Contoh berikut menunjukkan prosedur yang berisi kueri multi-pernyataan. Kueri multi-pernyataan menetapkan variabel, menjalankan pernyataan INSERT, dan menampilkan hasilnya sebagai string teks berformat.

CREATE OR REPLACE PROCEDURE mydataset.create_customer()
BEGIN
  DECLARE id STRING;
  SET id = GENERATE_UUID();
  INSERT INTO mydataset.customers (customer_id)
    VALUES(id);
  SELECT FORMAT("Created customer %s", id);
END

Pada contoh sebelumnya, nama prosedurnya adalah mydataset.create_customer, dan isi prosedur muncul di antara pernyataan BEGIN dan END.

Untuk memanggil prosedur, gunakan pernyataan CALL:

CALL mydataset.create_customer();

Terraform

Gunakan resource google_bigquery_routine.

Catatan: Untuk membuat objek BigQuery menggunakan Terraform, Anda harus mengaktifkan Cloud Resource Manager API.

Untuk melakukan autentikasi ke BigQuery, siapkan Kredensial Default Aplikasi. Untuk mengetahui informasi selengkapnya, lihat Menyiapkan autentikasi untuk library klien.

Contoh berikut membuat prosedur tersimpan bernama my_stored_procedure:

# Creates a SQL stored procedure.

# Create a dataset to contain the stored procedure.
resource "google_bigquery_dataset" "my_dataset" {
  dataset_id = "my_dataset"
}

# Create a stored procedure.
resource "google_bigquery_routine" "my_stored_procedure" {
  dataset_id      = google_bigquery_dataset.my_dataset.dataset_id
  routine_id      = "my_stored_procedure"
  routine_type    = "PROCEDURE"
  language        = "SQL"
  definition_body = "SELECT * FROM `bigquery-public-data.ml_datasets.penguins`;"
}

Untuk menerapkan konfigurasi Terraform di project, selesaikan langkah-langkah di bagian berikut. Google Cloud

Menyiapkan Cloud Shell

1. Luncurkan Cloud Shell.

2. Tetapkan project Google Cloud default tempat Anda ingin menerapkan konfigurasi Terraform.

   Anda hanya perlu menjalankan perintah ini sekali per project, dan dapat dijalankan di direktori mana pun.

   export GOOGLE_CLOUD_PROJECT=PROJECT_ID

   Variabel lingkungan akan diganti jika Anda menetapkan nilai eksplisit dalam file konfigurasi Terraform.

Menyiapkan direktori

Setiap file konfigurasi Terraform harus memiliki direktorinya sendiri (juga disebut modul root).

1. Di Cloud Shell, buat direktori dan file baru di dalam direktori tersebut. Nama file harus memiliki ekstensi .tf—misalnya main.tf. Dalam tutorial ini, file ini disebut sebagai main.tf.

   mkdir DIRECTORY && cd DIRECTORY && touch main.tf

2. Jika mengikuti tutorial, Anda dapat menyalin kode contoh di setiap bagian atau langkah.

   Salin kode contoh ke dalam main.tf yang baru dibuat.

   Atau, salin kode dari GitHub. Tindakan ini direkomendasikan jika cuplikan Terraform adalah bagian dari solusi menyeluruh.

3. Tinjau dan ubah contoh parameter untuk diterapkan pada lingkungan Anda.
4. Simpan perubahan Anda.
5. Lakukan inisialisasi Terraform. Anda hanya perlu melakukan ini sekali per direktori.

   terraform init

   Secara opsional, untuk menggunakan versi penyedia Google terbaru, sertakan opsi -upgrade:

   terraform init -upgrade

Menerapkan perubahan

1. Tinjau konfigurasi dan pastikan resource yang akan dibuat atau diupdate oleh Terraform sesuai yang Anda inginkan:

   terraform plan

   Koreksi konfigurasi jika diperlukan.

2. Terapkan konfigurasi Terraform dengan menjalankan perintah berikut dan memasukkan yes pada prompt:

   terraform apply

   Tunggu hingga Terraform menampilkan pesan "Apply complete!".

3. Buka Google Cloud project Anda untuk melihat hasilnya. Di konsol Google Cloud , buka resource Anda di UI untuk memastikan bahwa Terraform telah membuat atau mengupdatenya.

Catatan: Contoh Terraform biasanya mengasumsikan bahwa API yang diperlukan sudah diaktifkan di project Google Cloud Anda.

Memasukkan nilai dengan parameter input

Prosedur dapat memiliki parameter input. Parameter input mengizinkan input untuk suatu prosedur, tetapi tidak mengizinkan output.

CREATE OR REPLACE PROCEDURE mydataset.create_customer(name STRING)
BEGIN
DECLARE id STRING;
SET id = GENERATE_UUID();
INSERT INTO mydataset.customers (customer_id, name)
  VALUES(id, name);
SELECT FORMAT("Created customer %s (%s)", id, name);
END

Mengeluarkan nilai dengan parameter output

Prosedur dapat memiliki parameter output. Parameter output menampilkan nilai dari prosedur, tetapi tidak mengizinkan input untuk prosedur. Untuk membuat parameter output, gunakan kata kunci OUT sebelum nama parameter.

Misalnya, versi prosedur ini menampilkan ID pelanggan baru melalui parameter id:

CREATE OR REPLACE PROCEDURE mydataset.create_customer(name STRING, OUT id STRING)
BEGIN
SET id = GENERATE_UUID();
INSERT INTO mydataset.customers (customer_id, name)
  VALUES(id, name);
SELECT FORMAT("Created customer %s (%s)", id, name);
END

Untuk memanggil prosedur ini, Anda harus menggunakan variabel untuk menerima nilai output:

--- Create a new customer record.
DECLARE id STRING;
CALL mydataset.create_customer("alice",id);

--- Display the record.
SELECT * FROM mydataset.customers
WHERE customer_id = id;

Memasukkan dan mengeluarkan nilai dengan parameter input/output

Prosedur juga dapat memiliki parameter input/output. Parameter input/output menampilkan nilai dari prosedur dan juga menerima input untuk prosedur tersebut. Untuk membuat parameter input/output, gunakan kata kunci INOUT sebelum nama parameter. Untuk mengetahui informasi selengkapnya, lihat Mode argumen.

Mengizinkan rutinitas

Anda dapat mengizinkan prosedur yang tersimpan 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.

Rutinitas yang diizinkan dapat membuat, menghapus, dan memanipulasi tabel, serta panggil prosedur lain yang tersimpan di tabel pokok.

Untuk informasi selengkapnya, lihat Rutinitas yang diizinkan.

Memanggil prosedur yang disimpan

Untuk memanggil prosedur tersimpan setelah dibuat, gunakan pernyataan CALL. Misalnya, pernyataan berikut memanggil prosedur tersimpan create_customer:

CALL mydataset.create_customer();

Catatan: Memanggil prosedur tersimpan, bukan menyertakan pernyataan SQL prosedur secara langsung dalam kueri Anda, akan menimbulkan sedikit overhead performa.

Memanggil prosedur sistem

Untuk memanggil prosedur sistem bawaan, gunakan pernyataan CALL. Misalnya, pernyataan berikut memanggil prosedur sistem BQ.REFRESH_MATERIALIZED_VIEW:

CALL BQ.REFRESH_MATERIALIZED_VIEW;