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

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

  1. בפרויקט PROJECT, יוצרים חשבון שירות בשם snapshot-bot.
  2. נותנים לחשבון השירות snapshot-bot את ההרשאות שנדרשות כדי ליצור תמונות מצב של הטבלה TABLE שנמצאת במערך הנתונים DATASET, ולאחסן את תמונות המצב של הטבלה במערך הנתונים BACKUP.
  3. כותבים שאילתה שיוצרת תמונות מצב חודשיות של הטבלה TABLE וממקמת אותן במערך הנתונים BACKUP. אי אפשר להחליף תמונת מצב קיימת של טבלה, ולכן לכל תמונת מצב של טבלה צריך להיות שם ייחודי. כדי לעשות את זה, השאילתה מוסיפה את התאריך הנוכחי לשמות של תמונות המצב של הטבלאות, לדוגמה: TABLE_20220521. התוקף של קובצי ה-snapshot של הטבלאות פג אחרי 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. במסוף Cloud de Confiance , נכנסים לדף Service accounts:

    כניסה לדף Service accounts

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

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

    1. לוחצים על Create service account.

    2. בשדה Service account name, מזינים snapshot-bot.

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

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

    1. בקטע Grant this service account access to project, בוחרים את התפקיד BigQuery User.

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

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

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

המסוף

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

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

    לדף Service accounts

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

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

  4. מוודאים שבהודעה Service account status כתוב שחשבון השירות פעיל.

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

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

    כניסה לדף Manage Resources

  2. לחץ על PROJECT.

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

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

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

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

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

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

כדי לתת לחשבון השירות snapshot-bot את ההרשאות שנדרשות לו כדי ליצור קובצי Snapshot של הטבלה DATASET.TABLE:

המסוף

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

    כניסה ל-BigQuery

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

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

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

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

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

  5. לוחצים על Overview > Tables, ואז על הטבלה TABLE.

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

  7. לוחצים על Add Principal. החלונית Grant access נפתחת.

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

  9. בתפריט הנפתח Select a role, בוחרים את התפקיד BigQuery Data Editor.

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

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

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

BQ

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

    הפעלת Cloud Shell

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

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

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

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

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

המסוף

  1. במסוף Cloud de Confiance , נכנסים לדף BigQuery.

    כניסה ל-BigQuery

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

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

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

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

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

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

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

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

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

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

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

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

התפקידים האלה מספקים את ההרשאות שנדרשות לחשבון השירות 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. מפעילים את Cloud Shell במסוף Cloud de Confiance :

    הפעלת 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 שונה מהשאילתה שהרצתם במסוף Cloud de Confiance , באופן הבא:

  • השאילתה של כלי שורת הפקודה של 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.s3ns.iam.gserviceaccount.com \
projects/12345/locations/us/transferConfigs/12345

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

בדיקת העבודה שלכם

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

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

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

המסוף

  1. במסוף Cloud de Confiance , נכנסים לדף Scheduled queries:

    מעבר אל Scheduled queries

  2. לוחצים על Monthly snapshots of the TABLE table.

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

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

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

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

המסוף

  1. במסוף Cloud de Confiance , נכנסים לדף Scheduled queries:

    מעבר לדף Scheduled queries

  2. לוחצים על תיאור השאילתה, Monthly snapshots of the TABLE table.

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

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

הצגת תמונות המצב של הטבלה

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

המסוף

  1. במסוף Cloud de Confiance , נכנסים לדף BigQuery:

    כניסה ל-BigQuery

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

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

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

    לדוגמה:

    • TABLE_20220601
    • TABLE_20220701
    • TABLE_20220801

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