יצירת תמונות מצב של טבלאות באמצעות שאילתה מתוזמנת

במאמר הזה מוסבר איך ליצור תמונות מצב חודשיות של טבלה באמצעות חשבון שירות שמריץ שאילתת DDL מתוזמנת. בדוגמה הבאה מוסבר איך עושים את זה:

  1. בפרויקט PROJECT, יוצרים חשבון שירות בשם snapshot-bot.
  2. נותנים לחשבון השירות snapshot-bot את ההרשאות שהוא צריך כדי ליצור תמונות מצב של הטבלה TABLE שנמצאת במערך הנתונים DATASET, ולאחסן את תמונות המצב של הטבלה במערך הנתונים BACKUP.
  3. תכתוב שאילתה שיוצרת תמונות מצב חודשיות של הטבלה TABLE וממקמת אותן במערך הנתונים BACKUP. אי אפשר להחליף תמונת מצב קיימת של טבלה, ולכן השמות של תמונות המצב של הטבלה חייבים להיות ייחודיים. כדי לעשות את זה, האפליקציה מוסיפה את התאריך הנוכחי לשמות של תמונות המצב של הטבלה. לדוגמה, TABLE_20220521. התוקף של תמונות המצב של הטבלה יפוג אחרי 40 ימים.
  4. מתזמנים את חשבון השירות snapshot-bot להפעלת השאילתה ביום הראשון של כל חודש.

המסמך הזה מיועד למשתמשים שמכירים את BigQuery ואת תמונות המצב של טבלאות ב-BigQuery.

הרשאות ותפקידים

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

הרשאות

כדי לעבוד עם חשבון שירות, צריך את ההרשאות הבאות:

הרשאה משאב סוג המשאב
iam.serviceAccounts.* PROJECT פרויקט

כדי לתזמן שאילתה, נדרשת ההרשאה הבאה:

הרשאה משאב סוג המשאב
bigquery.jobs.create PROJECT פרויקט

תפקידים

אלה התפקידים המוגדרים מראש שכוללים את ההרשאות שנדרשות כדי לעבוד עם חשבון שירות:

תפקיד משאב סוג המשאב
אחת מהפעולות הבאות:

roles/iam.serviceAccountAdmin
roles/editor
roles/owner 		PROJECT פרויקט

התפקידים המוגדרים מראש ב-BigQuery שמספקים את ההרשאות שנדרשות לתזמון שאילתה הם:

תפקיד משאב סוג המשאב
אחת מהאפשרויות הבאות:

roles/bigquery.user
roles/bigquery.jobuser
roles/bigquery.admin` 		PROJECT פרויקט

יצירת חשבון שירות snapshot-bot

כדי ליצור את snapshot-bot חשבון השירות ולהעניק לו את ההרשאות שהוא צריך כדי להריץ שאילתות בפרויקט PROJECT, פועלים לפי השלבים הבאים:

המסוף

  1. במסוף Google Cloud , נכנסים לדף Service accounts:

    כניסה לדף Service accounts

  2. בוחרים את הפרויקט PROJECT.

  3. יוצרים את חשבון השירות snapshot-bot:

    1. לוחצים על יצירת חשבון שירות.

    2. בשדה שם חשבון השירות, מזינים snapshot-bot.

    3. לוחצים על יצירה והמשך.

  4. נותנים לחשבון השירות את ההרשאות שהוא צריך כדי להריץ משימות של BigQuery:

    1. בקטע Grant this service account access to project (הענקת גישה של חשבון השירות הזה לפרויקט), בוחרים בתפקיד BigQuery User (משתמש ב-BigQuery).

    2. לוחצים על סיום.

מערכת BigQuery יוצרת את חשבון השירות עם כתובת האימייל snapshot-bot@PROJECT.iam.gserviceaccount.com.

כדי לוודא ש-BigQuery יצר את חשבון השירות עם ההרשאות שציינתם, פועלים לפי השלבים הבאים:

המסוף

מוודאים ש-BigQuery יצר את חשבון השירות:

  1. במסוף Google Cloud , נכנסים לדף Service accounts:

    לדף Service accounts

  2. בוחרים את הפרויקט PROJECT.

  3. לוחצים על snapshot-bot@PROJECT.iam.gserviceaccount.com.

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

מוודאים ש-BigQuery העניק לחשבון השירות שלכם את ההרשאה שנדרשת להרצת שאילתות:

  1. במסוף Google Cloud , נכנסים לדף Manage resources:

    מעבר לניהול משאבים

  2. לחץ על PROJECT.

  3. לוחצים על הצגת חלונית המידע.

  4. בכרטיסייה הרשאות, מרחיבים את הצומת משתמש BigQuery.

  5. מוודאים שחשבון השירות snapshot-bot מופיע ברשימה.

מתן הרשאות לחשבון השירות

בקטע הזה מוסבר איך להעניק לחשבון השירות snapshot-bot את ההרשאות שדרושות לו כדי ליצור תמונות מצב של הטבלה DATASET.TABLE במערך הנתונים BACKUP.

הרשאה לצילום תמונות מצב של טבלת הבסיס

כדי לתת לחשבון השירות snapshot-bot את ההרשאות שהוא צריך כדי לצלם תמונות מצב של הטבלה DATASET.TABLE, מבצעים את השלבים הבאים:

המסוף

  1. במסוף Google Cloud , פותחים את הדף BigQuery.

    כניסה ל-BigQuery

  2. בחלונית הימנית, לוחצים על כלי הניתוחים:

    כפתור מודגש לחלונית הסייר.

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

  3. בחלונית Explorer, מרחיבים את צומת הפרויקט PROJECT.

  4. לוחצים על Datasets (מערכי נתונים) ואז על מערך הנתונים DATASET.

  5. לוחצים על סקירה כללית > טבלאות ואז על הטבלה TABLE.

  6. לוחצים על שיתוף. תיפתח החלונית שיתוף.

  7. לוחצים על Add Principal. נפתחת החלונית הענקת גישה.

  8. בקטע New principals (חשבונות משתמשים חדשים), מזינים את כתובת האימייל של חשבון השירות: snapshot-bot@PROJECT.iam.gserviceaccount.com.

  9. בתפריט הנפתח Select a role (בחירת תפקיד), בוחרים בתפקיד BigQuery Data Editor (עריכת נתונים ב-BigQuery).

  10. לוחצים על Save.

  11. בחלונית Share (שיתוף), מרחיבים את הצומת BigQuery Data Editor (עורך נתונים ב-BigQuery) ומוודאים שחשבון השירות snapshot-bot@PROJECT.iam.gserviceaccount.com מופיע ברשימה.

  12. לוחצים על Close.

BQ

  1. במסוף Google Cloud , מפעילים את Cloud Shell:

    הפעלת Cloud Shell

  2. מזינים את הפקודה הבאה: bq add-iam-policy-binding 

    bq add-iam-policy-binding \
--member=serviceAccount:snapshot-bot@PROJECT.iam.gserviceaccount.com \
--role=roles/bigquery.dataEditor DATASET.TABLE

מערכת BigQuery מאשרת שהוסף קישור מדיניות חדש.

הרשאה ליצור טבלאות במערך הנתונים של היעד

נותנים לחשבון השירות snapshot-bot את ההרשאות שהוא צריך כדי ליצור תמונות מצב של טבלאות במערך הנתונים BACKUP באופן הבא:

המסוף

  1. במסוף Google Cloud , עוברים לדף BigQuery.

    כניסה ל-BigQuery

  2. בחלונית הימנית, לוחצים על כלי הניתוחים:

    כפתור מודגש לחלונית הסייר.

  3. בחלונית Explorer, מרחיבים את צומת הפרויקט PROJECT.

  4. לוחצים על Datasets (מערכי נתונים) ואז על מערך הנתונים BACKUP.

  5. לוחצים על שיתוף > ניהול הרשאות. נפתחת חלונית ההרשאות של מערך הנתונים.

  6. לוחצים על Add principal. בשדה New principals, מזינים את כתובת האימייל של חשבון השירות: snapshot-bot@PROJECT.iam.gserviceaccount.com.

  7. בתפריט הנפתח Select a role (בחירת תפקיד), בוחרים את התפקיד BigQuery Data Owner (הבעלים של נתוני BigQuery).

  8. לוחצים על Save.

  9. בחלונית ההרשאות של מערך הנתונים, מוודאים שחשבון השירות snapshot-bot@PROJECT.iam.gserviceaccount.com מופיע בצומת BigQuery Data Owner.

  10. לוחצים על Close.

לחשבון השירות snapshot-bot יש עכשיו את תפקידי ה-IAM הבאים למשאבים הבאים:

תפקיד משאב סוג המשאב מטרה
עריכה של נתוני BigQuery PROJECT:DATASET.TABLE טבלה תצלם תמונות מצב של הטבלה TABLE.
בעלים של נתונים ב-BigQuery PROJECT:BACKUP קבוצת הנתונים יצירה ומחיקה של קובצי snapshot של טבלאות במערך הנתונים BACKUP.
משתמש BigQuery PROJECT פרויקט מריצים את השאילתה המתוזמנת שיוצרת את תמונות המצב של הטבלה.

התפקידים האלה מספקים את ההרשאות שחשבון השירות snapshot-bot צריך כדי להריץ שאילתות שיוצרות תמונות מצב של הטבלה DATASET.TABLE וממקמות את תמונות המצב של הטבלה במערך הנתונים BACKUP.

כתיבת שאילתה עם כמה הצהרות

בקטע הזה מוסבר איך לכתוב שאילתה עם כמה הצהרות שיוצרת תמונת מצב של הטבלה DATASET.TABLE באמצעות הצהרת DDL‏ CREATE SNAPSHOT TABLE. התמונה נשמרת במערך הנתונים BACKUP והתוקף שלה פג אחרי יום אחד.

-- Declare variables
DECLARE snapshot_name STRING;
DECLARE expiration TIMESTAMP;
DECLARE query STRING;

-- Set variables
SET expiration = DATE_ADD(current_timestamp(), INTERVAL 1 DAY);
SET snapshot_name = CONCAT(
                      "BACKUP.TABLE_",
                      FORMAT_DATETIME('%Y%m%d', current_date()));

-- Construct the query to create the snapshot
SET query = CONCAT(
              "CREATE SNAPSHOT TABLE ",
              snapshot_name,
              " CLONE mydataset.mytable OPTIONS(expiration_timestamp = TIMESTAMP '",
              expiration,
              "');");

-- Run the query
EXECUTE IMMEDIATE query;

תזמון השאילתה החודשית

כדי לתזמן את השאילתה כך שתפעל בשעה 5:00 בבוקר ביום הראשון של כל חודש:

BQ

  1. במסוף Google Cloud , מפעילים את Cloud Shell:

    הפעלת Cloud Shell

  2. מזינים את הפקודה הבאה: bq query

    bq query --use_legacy_sql=false --display_name="Monthly snapshots of the TABLE table" \
--location="us" --schedule="1 of month 05:00" \
--project_id=PROJECT \
'DECLARE snapshot_name STRING;
DECLARE expiration TIMESTAMP;
DECLARE query STRING;
SET expiration = DATE_ADD(@run_time, INTERVAL 40 DAY);
SET snapshot_name = CONCAT("BACKUP.TABLE_",
  FORMAT_DATETIME("%Y%m%d", @run_date));
SET query = CONCAT("CREATE SNAPSHOT TABLE ", snapshot_name,
  " CLONE PROJECT.DATASET.TABLE OPTIONS(expiration_timestamp=TIMESTAMP \"",
  expiration, "\");");
EXECUTE IMMEDIATE query;'

  3. המערכת של BigQuery מתזמנת את השאילתה.

השאילתה עם כמה הצהרות בפקודה של כלי שורת הפקודה של BigQuery שונה מהשאילתה שהפעלתם במסוף Google Cloud באופן הבא:

  • השאילתה בכלי שורת הפקודה של BigQuery משתמשת ב-@run_date במקום ב-current_date(). בשאילתה מתוזמנת, הפרמטר @run_date מכיל את התאריך הנוכחי. אבל בשאילתה אינטראקטיבית, הפרמטר @run_date לא נתמך. אפשר להשתמש ב-current_date() במקום ב-@run_date כדי לבדוק שאילתה אינטראקטיבית לפני שמגדירים לה תזמון.
  • השאילתה בכלי שורת הפקודה של BigQuery משתמשת ב-@run_time במקום ב-current_timestamp(), ומסיבה דומה – הפרמטר @run_time לא אפשרי בשאילתות אינטראקטיביות, אבל אפשר להשתמש ב-current_timestamp() במקום ב-@run_time כדי לבדוק את השאילתה האינטראקטיבית.
  • בשאילתה של כלי שורת הפקודה של BigQuery נעשה שימוש בלוכסן ובמירכאות \" במקום בגרשיים ', כי הגרשיים משמשים להוספת השאילתה.

הגדרת חשבון השירות להרצת השאילתה המתוזמנת

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

  1. מריצים את הפקודה bq ls כדי לקבל את הזהות של עבודת השאילתה המתוזמנת:

    bq ls --transfer_config=true --transfer_location=us

    הפלט אמור להיראות כך:

    name displayName dataSourceId state
    projects/12345/locations/us/transferConfigs/12345 Monthly snapshots of the TABLE table scheduled_query RUNNING

  2. מריצים את הפקודה bq update הבאה באמצעות המזהה בשדה name:

    bq update --transfer_config --update_credentials \
--service_account_name=snapshot-bot@PROJECT.iam.gserviceaccount.com \
projects/12345/locations/us/transferConfigs/12345

ב-Cloud Shell מוצג אישור שהשאילתה המתוזמנת עודכנה בהצלחה.

בדיקת העבודה

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

הצגת השאילתה המתוזמנת

כדי לוודא ש-BigQuery תזמן את השאילתה של תמונות המצב החודשיות של הטבלה, צריך לבצע את השלבים הבאים:

המסוף

  1. במסוף Google Cloud , עוברים לדף Scheduled queries:

    מעבר לשאילתות מתוזמנות

  2. לוחצים על תמונות מצב חודשיות של הטבלה TABLE.

  3. לוחצים על הגדרה.

  4. מוודאים שמחרוזת השאילתה מכילה את השאילתה, ושהשאילתה מתוזמנת לפעול ביום הראשון של כל חודש.

הצגת היסטוריית ההרצה של השאילתה המתוזמנת

אחרי שהשאילתה המתוזמנת תרוץ, תוכלו לראות אם היא רצה בהצלחה באופן הבא:

המסוף

  1. במסוף Google Cloud , עוברים לדף Scheduled queries:

    מעבר לשאילתות מתוזמנות

  2. לוחצים על תיאור השאילתה, Monthly snapshots of the TABLE table (תמונות מצב חודשיות של הטבלה TABLE).

  3. לוחצים על היסטוריית ההרצה.

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

צפייה בתמונות המצב של הטבלה

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

המסוף

  1. במסוף Google Cloud , עוברים לדף BigQuery:

    כניסה ל-BigQuery

  2. בחלונית הימנית, לוחצים על כלי הניתוחים:

    כפתור מודגש לחלונית הסייר.

  3. בחלונית Explorer, פותחים את מערך הנתונים BACKUP ומוודאים שנוצרו תמונות המצב TABLE_YYYYMMDD, כאשר YYYYMMDD הוא היום הראשון בכל חודש.

    לדוגמה:

    • TABLE_20220601
    • TABLE_20220701
    • TABLE_20220801

המאמרים הבאים