עבודה עם נהלים מאוחסנים של SQL

תהליך מאוחסן הוא אוסף של הצהרות שאפשר לקרוא להן משאילתות אחרות או מתהליכים מאוחסנים אחרים. פרוצדורה יכולה לקבל ארגומנטים של קלט ולהחזיר ערכים כפלט. נותנים שם לפרוצדורה ומאחסנים אותה במערך נתונים ב-BigQuery. משתמשים רבים יכולים לגשת לנתונים בכמה מערכי נתונים או לשנות אותם באמצעות פרוצדורה מאוחסנת. היא יכולה להכיל גם שאילתה עם כמה הצהרות.

חלק מהתהליכים המאוחסנים מוטמעים ב-BigQuery ואין צורך ליצור אותם. הן נקראות פרוצדורות מערכת, ואפשר לקרוא עליהן עוד בהפניה לפרוצדורות מערכת.

הפרוצדורות המאוחסנות תומכות בהצהרות של שפה פרוצדורלית, שמאפשרות לבצע פעולות כמו הגדרת משתנים והטמעה של זרימת בקרה. מידע נוסף על הצהרות בשפה פרוצדורלית זמין בהפניה לשפה פרוצדורלית.

יצירת תהליך מאוחסן

כדי ליצור פרוצדורה מאוחסנת, בוחרים אחת מהאפשרויות הבאות:

SQL

כדי ליצור פרוצדורה, משתמשים בהצהרה CREATE PROCEDURE.

בדוגמה הבאה, procedure_name מייצג את הפרוצדורה, והגוף של הפרוצדורה מופיע בין ההצהרות BEGIN ו-END:

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

בדוגמה הבאה מוצגת פרוצדורה שמכילה שאילתה עם כמה הצהרות. השאילתה עם כמה הצהרות מגדירה משתנה, מריצה הצהרת INSERT ומציגה את התוצאה כמחרוזת טקסט מעוצבת.

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

בדוגמה הקודמת, שם הפרוצדורה הוא mydataset.create_customer, והגוף של הפרוצדורה מופיע בין ההצהרות BEGIN ו-END.

כדי להפעיל את הפרוצדורה, משתמשים בהצהרה CALL:

CALL mydataset.create_customer();

Terraform

משתמשים במשאב google_bigquery_routine.

הערה: כדי ליצור אובייקטים של BigQuery באמצעות Terraform, צריך להפעיל את Cloud Resource Manager API.

כדי לבצע אימות ב-BigQuery, צריך להגדיר את Application Default Credentials. מידע נוסף זמין במאמר הגדרת אימות לספריות לקוח.

בדוגמה הבאה נוצרת פרוצדורה מאוחסנת בשם 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`;"
}

כדי להחיל את ההגדרות של Terraform בפרויקט ב- Google Cloud , מבצעים את השלבים בקטעים הבאים.

הכנת Cloud Shell

1. מפעילים את Cloud Shell.

2. מגדירים את פרויקט ברירת המחדל שבו רוצים להחיל את ההגדרות של Terraform. Google Cloud

   תצטרכו להריץ את הפקודה הזו רק פעם אחת לכל פרויקט, ותוכלו לעשות זאת בכל ספרייה.

   export GOOGLE_CLOUD_PROJECT=PROJECT_ID

   אם תגדירו ערכים ספציפיים בקובץ התצורה של Terraform, הם יבטלו את ערכי ברירת המחדל של משתני הסביבה.

הכנת הספרייה

לכל קובץ תצורה של Terraform צריכה להיות ספרייה משלו (שנקראת גם מודול ברמה הבסיסית).

1. יוצרים ספרייה חדשה ב-Cloud Shell ובה יוצרים קובץ חדש. שם הקובץ חייב לכלול את הסיומת .tf, למשל main.tf. במדריך הזה, הקובץ נקרא main.tf.

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

2. אם אתם עוקבים אחרי המדריך, תוכלו להעתיק את הקוד לדוגמה בכל קטע או שלב.

   מעתיקים את הקוד לדוגמה בקובץ main.tf החדש שיצרתם.

   לחלופין, אפשר גם להעתיק את הקוד מ-GitHub. כדאי לעשות את זה כשקטע הקוד של Terraform הוא חלק מפתרון מקצה לקצה.

3. בודקים את הפרמטרים לדוגמה ומשנים אותם בהתאם לסביבה שלכם.
4. שומרים את השינויים.
5. מפעילים את Terraform. צריך לעשות זאת רק פעם אחת לכל ספרייה.

   terraform init

   אופציונלי: תוכלו לכלול את האפשרות -upgrade, כדי להשתמש בגרסה העדכנית ביותר של הספק של Google:

   terraform init -upgrade

החלה של השינויים

1. בודקים את ההגדרות ומוודאים שהמשאבים שמערכת Terraform תיצור או תעדכן תואמים לציפיות שלכם:

   terraform plan

   מתקנים את ההגדרות לפי הצורך.

2. מריצים את הפקודה הבאה ומזינים yes בהודעה שמופיעה, כדי להחיל את הגדרות Terraform:

   terraform apply

   ממתינים עד שב-Terraform תוצג ההודעה "Apply complete!‎".

3. פותחים את Google Cloud הפרויקט כדי לראות את התוצאות. במסוף Google Cloud , נכנסים למשאבים בממשק המשתמש כדי לוודא שהם נוצרו או עודכנו ב-Terraform.

הערה: בדרך כלל הנחת העבודה בדוגמאות של Terraform היא שממשקי ה-API הנדרשים מופעלים ב Google Cloud פרויקט.

העברת ערך באמצעות פרמטר קלט

יכול להיות שלפרוצדורה יש פרמטרים של קלט. פרמטר קלט מאפשר קלט עבור פרוצדורה, אבל לא מאפשר פלט.

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

העברת ערך החוצה באמצעות פרמטר פלט

לפרוצדורה יכולים להיות פרמטרים של פלט. פרמטר פלט מחזיר ערך מהפרוצדורה, אבל לא מאפשר קלט לפרוצדורה. כדי ליצור פרמטר פלט, משתמשים במילת המפתח OUT לפני שם הפרמטר.

לדוגמה, הגרסה הזו של הפרוצדורה מחזירה את מספר הלקוח החדש באמצעות הפרמטר 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

כדי להפעיל את הפרוצדורה הזו, צריך להשתמש במשתנה כדי לקבל את ערך הפלט:

--- 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;

העברת ערך פנימה והחוצה באמצעות פרמטר קלט/פלט

לפרוצדורה יכולים להיות גם פרמטרים של קלט/פלט. פרמטר קלט/פלט מחזיר ערך מהפרוצדורה וגם מקבל קלט לפרוצדורה. כדי ליצור פרמטר קלט/פלט, משתמשים במילת המפתח INOUT לפני שם הפרמטר. מידע נוסף זמין במאמר בנושא מצב ארגומנט.

מתן הרשאה לתרחישים

אפשר להגדיר הרשאות לתהליכים מאוחסנים כתרחישים. בעזרת שגרות מורשות אפשר לשתף תוצאות של שאילתות עם משתמשים או קבוצות ספציפיים בלי לתת להם גישה לטבלאות הבסיסיות שמהן נוצרו התוצאות. לדוגמה, שגרה מורשית יכולה לחשב צבירה של נתונים או לחפש ערך בטבלה ולהשתמש בו בחישוב.

שגרות מורשות יכולות ליצור, להסיר ולשנות טבלאות, וגם להפעיל פרוצדורות מאוחסנות אחרות בטבלה הבסיסית.

מידע נוסף זמין במאמר בנושא שגרה מאושרת.

התקשרות לתהליך מאוחסן

כדי להפעיל פרוצדורה מאוחסנת אחרי שיוצרים אותה, משתמשים בהצהרה CALL. לדוגמה, ההצהרה הבאה מפעילה את הפרוצדורה המאוחסנת create_customer:

CALL mydataset.create_customer();

הערה: קריאה לפרוצדורה מאוחסנת במקום לכלול את הצהרות ה-SQL של הפרוצדורה ישירות בשאילתה, מוסיפה תקורה קטנה לביצועים.

הפעלת פרוצדורה של מערכת

כדי להתקשר לפרוצדורה מובנית של המערכת, משתמשים בהצהרה CALL. לדוגמה, ההצהרה הבאה מפעילה את הפרוצדורה של המערכת BQ.REFRESH_MATERIALIZED_VIEW:

CALL BQ.REFRESH_MATERIALIZED_VIEW;