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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

אם אתם משתמשים בחיבור שלא מצוין בו תפקיד במסד הנתונים, צריכות להיות לכם הרשאות של תפקידי 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 יכולה להוביל לצריכת CPU נמוכה משמעותית, ובמקרים מסוימים גם לחביון נמוך יותר של השאילתות.

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

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

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

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

הפעלת 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 במספר אזורים: Spanner region us-central1
  • ‫BigQuery EU במספר אזורים: Spanner region europe-west1

לדוגמה:

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

פתרון בעיות

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

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

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