שאילתות מאוחדות ב-Spanner

כאנליסטים של נתונים, אתם יכולים להריץ שאילתות על נתונים ב-Spanner מ-BigQuery באמצעות שאילתות מאוחדות.

פדרציית BigQuery Spanner מאפשרת ל-BigQuery להריץ שאילתות על נתונים שנמצאים ב-Spanner בזמן אמת, בלי להעתיק או להעביר את הנתונים.

יש שתי דרכים לשאילתת נתונים ב-Spanner:

  • יוצרים מערך נתונים חיצוני של Spanner.
  • משתמשים בפונקציה EXTERNAL_QUERY.

הסבר על תפקידים והרשאות

כשמריצים שאילתה ב-Spanner מ-BigQuery, יש שני סוגים שונים של תפקידים שמנהלים את הגישה ברמות שונות.

  • תפקידי IAM: התפקידים האלה קובעים את הגישה לGoogle Cloud משאבים, כולל מסדי נתונים ומופעים של Spanner. הן קובעות לאילו גורמים יש גישה לשירות Spanner ואילו פעולות הם יכולים לבצע ברמת המופע או מסד הנתונים, כמו התחברות, קריאת נתונים או ניהול. אתם יכולים לנהל את התפקידים בניהול הזהויות והרשאות הגישה (IAM) דרך מסוף IAM או דרך Google Cloud CLI. לדוגמה, roles/bigquery.connectionUser ו-roles/spanner.databaseReader. מידע נוסף זמין במאמרים תפקידי IAM ב-Spanner והענקת הרשאות.

  • תפקידים במסד נתונים של Spanner: התפקידים האלה מוגדרים במסד נתונים של Spanner באמצעות הצהרות DDL כמו CREATE ROLE ו-GRANT. הם שולטים בגישה פרטנית לאובייקטים ספציפיים של סכימה, כמו טבלאות, עמודות ותצוגות, בתוך מסד הנתונים. האפשרות הזו היא חלק מבקרת גישה פרטנית (FGAC). משתמשים בתפקיד במסד נתונים אם הארגון מיישם FGAC כדי לנהל הרשאות בתוך מסד הנתונים.

איך בודקים אם אתם משתמשים ב-FGAC

כדי לדעת אילו הרשאות לבקש, צריך לבדוק אם אתם משתמשים ב-FGAC. כדי לעשות את זה, צריך לשאול את האדמין של מסד הנתונים ב-Spanner אם הגישה שלכם למסד הנתונים ב-Spanner מנוהלת באמצעות בקרת גישה מדויקת.

סביר להניח שאתם משתמשים ב-FGAC אם האדמין נותן לחשבון שלכם הרשאות על ידי הקצאת תפקיד ספציפי במסד נתונים של Spanner (לדוגמה, על ידי הקצאת תפקיד IAM‏ roles/spanner.databaseRoleUser בחשבון שלכם במשאב של תפקיד במסד נתונים). במקרה כזה, צריך לדעת את השם של תפקידי מסד הנתונים שבהם אפשר להשתמש. צריך להגדיר את החיבור ל-BigQuery כך שישתמש באחד מתפקידי מסד הנתונים האלה.

סביר להניח שאתם לא משתמשים ב-FGAC אם האדמין נותן לחשבון שלכם תפקידי IAM רחבים יותר ברמת מסד הנתונים, כמו roles/spanner.databaseReader. במקרה כזה, לא צריך להשתמש בתפקיד ספציפי במסד הנתונים כשמתחברים.

השוואה בין בקשות לתפקיד

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

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

  • הרשאת ה-IAM‏ spanner.databases.useRoleBasedAccess, שלרוב מוענקת דרך התפקיד roles/spanner.fineGrainedAccessUser.
  • הרשאה להשתמש בתפקיד הספציפי במסד הנתונים, שניתנת באמצעות התפקיד roles/spanner.databaseRoleUser עם תנאי IAM.

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

שימוש במערכי נתונים חיצוניים

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

אם יוצרים מערך נתונים חיצוני, לא צריך ליצור חיבור כדי לשלוח שאילתות לנתוני Spanner.

שימוש בפונקציה EXTERNAL_QUERY

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

לפני שמתחילים

  • מוודאים שהאדמין שלכם ב-BigQuery יצר חיבור ל-Spanner ושיתף אותו איתכם. בחירת החיבור הנכון
  • כדי לקבל את ההרשאות שנדרשות להפעלת שאילתה במופע Spanner, צריך לבקש מהאדמין להקצות לכם את תפקיד ה-IAM 'משתמש בחיבור BigQuery' (roles/bigquery.connectionUser) בחיבור. אתם צריכים גם הרשאות מתאימות במסד הנתונים של Spanner, בהתאם לשאלה אם אתם משתמשים ב-FGAC.
    • אם אתם משתמשים בבקרת גישה פרטנית:
      • צריך להקצות את תפקידי ה-IAM שנדרשים כדי להשתמש ב-FGAC. התפקידים האלה הם בדרך כלל roles/spanner.fineGrainedAccessUser ו-roles/spanner.databaseRoleUser. התפקידים האלה משמשים עם תנאי שמציין את תפקיד מסד הנתונים.
      • למשתמש שמוגדר בחיבור ל-Spanner צריכה להיות הרשאת SELECT לכל האובייקטים בסכימה שאליהם מתייחסות השאילתות. אדמין מסד הנתונים מעניק הרשאות באמצעות הצהרת ה-DDL‏ GRANT (או ההצהרה המקבילה ב-PostgreSQL).
    • אם אתם לא משתמשים בבקרת גישה ברמת דיוק גבוהה, אתם צריכים את תפקיד ה-IAM של קורא מסד נתונים ב-Spanner ‏ (roles/spanner.databaseReader) במסד הנתונים.

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

בחירת החיבור המתאים

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

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

שאילתת נתונים

כדי לשלוח שאילתה מאוחדת ל-Spanner משאילתת GoogleSQL, משתמשים בפונקציה EXTERNAL_QUERY.

מנסחים את השאילתה ב-Spanner ב-GoogleSQL או ב-PostgreSQL, בהתאם לניב שצוין של מסד הנתונים.

בדוגמה הבאה מורצת שאילתה מאוחדת במסד נתונים של Spanner בשם orders, והתוצאות מצורפות לטבלה ב-BigQuery בשם mydataset.customers:

SELECT c.customer_id, c.name, rq.first_order_date
FROM mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'my-project.us.example-db',
  '''SELECT customer_id, MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY customer_id''') AS rq
  ON rq.customer_id = c.customer_id
GROUP BY c.customer_id, c.name, rq.first_order_date;

Spanner Data Boost

‫Data Boost היא תכונה מנוהלת לחלוטין, ללא שרת (serverless), שמספקת משאבי מחשוב עצמאיים לעומסי עבודה נתמכים ב-Spanner. התכונה Data Boost מאפשרת להריץ שאילתות ניתוח ולייצא נתונים עם השפעה מינימלית על עומסי העבודה הקיימים במופע Spanner שהוקצה. בעזרת Data Boost אפשר להריץ שאילתות מאוחדות עם יכולת חישוב עצמאית, בנפרד מהמופעים שהוקצו לכם, כדי למנוע השפעה על עומסי עבודה קיימים ב-Spanner. Data Boost שימושית במיוחד כשמריצים שאילתות מורכבות אד-הוק, או כשרוצים לעבד כמויות גדולות של נתונים בלי להשפיע על עומס העבודה הקיים ב-Spanner. הפעלת שאילתות מאוחדות באמצעות Data Boost יכולה להוביל לצריכת מעבד נמוכה משמעותית, ובמקרים מסוימים, לזמן אחזור נמוך יותר של שאילתות.

לפני שמתחילים

כדי לקבל את ההרשאה שנדרשת להפעלת הגישה ל-Data Boost, צריך לבקש מהאדמין להקצות לכם את תפקיד ה-IAM‏ Cloud Spanner Database Reader with DataBoost (roles/spanner.databaseReaderWithDataBoost) במסד הנתונים של Spanner. כדי לקרוא הסבר על מתן תפקידים, ראו איך מנהלים את הגישה ברמת הפרויקט, התיקייה והארגון.

התפקיד המוגדר מראש הזה כולל את ההרשאה spanner.databases.useDataBoost, שנדרשת כדי להפעיל גישה ל-Data Boost.

יכול להיות שתוכלו לקבל את ההרשאה הזו גם בתפקידים בהתאמה אישית או בתפקידים אחרים שמוגדרים מראש.

תפקיד ה-IAM‏ roles/spanner.databaseReaderWithDataBoost מעניק הרשאה להשתמש ב-Data Boost. התפקיד הזה נדרש בנוסף להרשאות הבסיסיות שנדרשות לקריאת נתונים, כמו roles/spanner.databaseReader למשתמשים שלא משתמשים ב-FGAC או ההרשאות המתאימות של בקרת גישה ברמת גרנולריות גבוהה.

השימוש במערכי נתונים חיצוניים ב-Spanner תמיד מתבצע באמצעות Data Boost, ולכן נדרשת הרשאת spanner.databases.useDataBoost.

הפעלת Data Boost

כשמשתמשים במערכי נתונים חיצוניים, Data Boost מופעל תמיד ואין צורך להפעיל אותו ידנית.

אם רוצים להשתמש ב-Data Boost בשאילתות EXTERNAL_QUERY, צריך להפעיל אותו כשיוצרים חיבור שמשמש את השאילתה.

קריאת נתונים במקביל

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

כדי לראות את תוכנית הביצוע של שאילתת Spanner, אפשר לעיין במאמר הסבר על אופן הביצוע של שאילתות ב-Spanner.

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

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

ניהול העדיפות של ביצוע שאילתות

כשמריצים שאילתות מאוחדות עם פונקציית EXTERNAL_QUERY, אפשר להקצות עדיפות (high,‏ medium או low) לשאילתות נפרדות על ידי ציון האפשרות query_execution_priority:

SELECT *
FROM EXTERNAL_QUERY(
  'my-project.us.example-db',
  '''SELECT customer_id, MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY customer_id''',
  '{"query_execution_priority":"high"}');

ערך ברירת המחדל של העדיפות הוא medium.

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

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

הצגת סכימת טבלה ב-Spanner

אם אתם משתמשים במערכי נתונים חיצוניים, הטבלאות שלכם ב-Spanner גלויות ישירות ב-BigQuery Studio, ואתם יכולים לראות את הסכימות שלהן.

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

מסד נתונים של Google SQL 

SELECT *
FROM EXTERNAL_QUERY(
  'my-project.us.example-db',
  '''SELECT t.column_name, t.spanner_type, t.is_nullable
    FROM information_schema.columns AS t
    WHERE
      t.table_catalog = ''
      AND t.table_schema = ''
     AND t.table_name = 'MyTable'
    ORDER BY t.ordinal_position
  ''');

מסד נתונים של PostgreSQL 

SELECT * from EXTERNAL_QUERY(
'my-project.us.postgresql-example-db',
'''SELECT t.column_name, t.data_type, t.is_nullable
   FROM information_schema.columns AS t
   WHERE t.table_schema = 'public' AND t.table_name = 'MyTable'
   ORDER BY t.ordinal_position''');

מידע נוסף זמין במאמרי העזרה של Spanner בנושא סכימת המידע הבאה:

תמחור

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

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

תחויבו על העברת הנתונים בהתאם למק"טים הבאים:

  • Network Intra-region Cross-Zone Data Transfer Out
  • Network Inter-Region Data Transfer Out to the Same Continent
  • Network Inter-Region Data Transfer Out to a Different Continent

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

במקרים של הגדרות של BigQuery במספר אזורים (US או EU), עלויות העברת הנתונים מ-Spanner נקבעות באופן הבא:

  • ‫BigQuery US multi-region: אזור Spanner‏ us-central1
  • ‫BigQuery EU multi-region: אזור Spanner‏ europe-west1

לדוגמה:

  • ‫BigQuery (US multi-region) ו-Spanner (us-central1): יש עלויות על העברת נתונים באותו אזור.
  • ‫BigQuery (US multi-region) ו-Spanner (us-west4): יש עלויות על העברת נתונים בין אזורים באותה יבשת.

פתרון בעיות

בקטע הזה מוסבר איך לפתור בעיות שעלולות לקרות כששולחים שאילתה מאוחדת ל-Spanner.

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

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