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

שאילתות מאוחדות מאפשרות לשלוח הצהרת שאילתה למסד נתונים חיצוני (כמו AlloyDB,‏ Spanner,‏ Cloud SQL,‏ Oracle,‏ MySQL או PostgreSQL) ולקבל את התוצאה כטבלה זמנית. שאילתות מאוחדות משתמשות ב-BigQuery Connection API כדי ליצור חיבור למסד הנתונים החיצוני. בשאילתה, משתמשים בפונקציה EXTERNAL_QUERY כדי לשלוח הצהרת שאילתה למסד הנתונים החיצוני, באמצעות הניב של SQL של מסד הנתונים הזה. התוצאות מומרות לסוגי נתונים של GoogleSQL.

מאגרי נתונים נתמכים

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

תהליך עבודה

  • מזהים את הפרויקט ב- Google Cloud שכולל את מקור הנתונים שרוצים לשלוח אליו שאילתה.
  • משתמש ב-bigquery.admin יוצר משאב חיבור ב-BigQuery.
  • המשתמש עם הרשאת האדמין נותן למשתמש ב' הרשאה להשתמש במשאב החיבור.
    • אם האדמין ומשתמש ב' הם אותו אדם, אין צורך לתת הרשאה.
  • משתמש ב' כותב שאילתה ב-BigQuery עם פונקציית ה-SQL החדשה EXTERNAL_QUERY.

חלופות לשאילתות מאוחדות: טבלאות וקבוצות נתונים חיצוניות

אפשרות נוספת לשליחת שאילתות למסדי נתונים תפעוליים כמו Bigtable,‏ Spanner,‏ Cloud Storage,‏ Google Drive ו-Salesforce Data Cloud, היא להשתמש בטבלאות ובמערכי נתונים חיצוניים. טבלאות ומערכי נתונים חיצוניים מאפשרים לכם לראות טבלאות ואת הסכימות שלהן, ולשאול אותן שאלות בלי להשתמש בפונקציית SQL של EXTERNAL_QUERY. אתם לא צריכים להחזיר את הנתונים ל-BigQuery, ואתם יכולים להשתמש בתחביר של BigQuery במקום לכתוב בדיאלקט הספציפי של מסד הנתונים ב-SQL.

אזורים נתמכים

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

‫AlloyDB ו-Cloud SQL

שאילתות מאוחדות נתמכות רק באזורים שתומכים גם במקור הנתונים החיצוני וגם ב-BigQuery.

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

אזורים יחידים

אזור יחיד ב-BigQuery יכול לשלוח שאילתות רק למשאב באותו אזור.

לדוגמה, אם מערך הנתונים נמצא ב-us-east4, אתם יכולים לשלוח שאילתות למכונות של Cloud SQL או למכונות של AlloyDB שנמצאות ב-us-east4. מיקום העיבוד של השאילתה הוא האזור היחיד ב-BigQuery.

מספר אזורים

במערך נתונים של BigQuery שפרוס במספר אזורים, אפשר להריץ שאילתות בכל אזור של מקור נתונים שנמצא באותו אזור גיאוגרפי גדול (ארה"ב, האיחוד האירופי). מיקומים בכמה אזורים לא זמינים למכונות של Cloud SQL, כי הם משמשים רק לגיבויים.

  • שאילתה שפועלת במספר אזורים בארה"ב ב-BigQuery יכולה להריץ שאילתות בכל אזור יחיד באזור הגיאוגרפי של ארה"ב, כמו us-central1,‏ us-east4 או us-west2.

  • שאילתה שפועלת במספר אזורים באיחוד האירופי ב-BigQuery יכולה לשלוח שאילתות לכל אזור יחיד במדינות החברות באיחוד האירופי, כמו europe-north1 או europe-west3.

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

ביצועי השאילתה משתנים בהתאם לקרבה בין מערך הנתונים לבין מקור הנתונים החיצוני. לדוגמה, שאילתה מאוחדת בין מערך נתונים במספר אזורים בארה"ב לבין מכונה של Cloud SQL ב-us-central1 תתבצע במהירות. לעומת זאת, אם תריצו את אותה שאילתה בין המיקום במספר אזורים בארה"ב לבין מכונה של Cloud SQL ב-us-east4, יכול להיות שהביצועים יהיו איטיים יותר.

המיקום שבו מתבצע עיבוד השאילתה הוא המיקום במספר אזורים, US או EU.

Spanner

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

‫Oracle,‏ MySQL ו-PostgreSQL

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

אם אתם מנתבים תנועה דרך רשתות פרטיות באמצעות מחברי רשת של Private Service Connect, מחבר הרשת והחיבור ל-BigQuery צריכים להיות באותו אזור.

מיפוי סוגי נתונים

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

מכסות ומגבלות

  • שאילתות מאוחדות בין אזורים. אם מיקום עיבוד השאילתות ב-BigQuery ומיקום מקור הנתונים החיצוני שונים, זו שאילתה חוצת-אזורים. אפשר להריץ שאילתות חוצות-אזורים בנפח של עד 1TB לכל פרויקט ביום. הדוגמה הבאה היא של שאילתה חוצת-אזורים.
    • המכונה של Cloud SQL נמצאת ב-us-west1, והחיבור ל-BigQuery מבוסס על מספר אזורים בארה"ב. מיקום עיבוד השאילתות ב-BigQuery הוא US.
  • Quota. המשתמשים צריכים לשלוט במכסת השאילתות במקור הנתונים החיצוני. אין הגדרת מכסה נוספת לשאילתות מאוחדות. כדי לבודד את עומס העבודה, מומלץ לשלוח שאילתות רק לרפליקה לקריאה בלבד של מסד הנתונים.
  • מספר הבייטים המקסימלי המותר לחיוב. השדה הזה לא נתמך בשאילתות מאוחדות. אי אפשר לחשב את הבייטים לחיוב לפני שמריצים בפועל את השאילתות המאוחדות.
  • מספר החיבורים. שאילתה מאוחדת יכולה לכלול עד 10 חיבורים ייחודיים.
  • ‫Cloud SQL MySQL ו-PostgreSQL. בכפוף למכסות ולמגבלות.

מגבלות

שאילתות מאוחדות כפופות למגבלות הבאות:

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

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

  • שאילתות מאוחדות הן לקריאה בלבד. השאילתה החיצונית שמופעלת במסד הנתונים של המקור חייבת להיות לקריאה בלבד. לכן אין תמיכה בהצהרות DML או DDL.

  • סוגי נתונים לא נתמכים. אם השאילתה החיצונית מכילה סוג נתונים שלא נתמך ב-BigQuery, השאילתה תיכשל מיד. אתם יכולים להמיר את סוג הנתונים שלא נתמך לסוג נתונים אחר שכן נתמך.

  • מפתחות הצפנה בניהול הלקוח (CMEK). מגדירים את ה-CMEK בנפרד ל-BigQuery ולמקורות נתונים חיצוניים. אם מגדירים את מסד הנתונים של המקור לשימוש ב-CMEK אבל לא ב-BigQuery, הטבלה הזמנית שמכילה את התוצאות של שאילתה מאוחדת מוצפנת באמצעות Google-owned and Google-managed encryption key.

תמחור

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

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

דחיפות SQL

שאילתות מאוחדות כפופות לטכניקת האופטימיזציה שנקראת SQL pushdowns. הן משפרות את הביצועים של שאילתה על ידי העברת פעולות כמו סינון למקור הנתונים החיצוני, במקום לבצע אותן ב-BigQuery. צמצום כמות הנתונים שמועברים ממקור הנתונים החיצוני יכול לקצר את זמן הרצת השאילתה ולהוזיל את העלויות. העברת שאילתות SQL כוללת גיזום עמודות (סעיפי SELECT) והעברת מסננים (סעיפי WHERE).

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

SELECT COUNT(*)
FROM (
  SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID", "select * from operations_table")
  )
WHERE a = 'Y' AND b NOT IN ('COMPLETE','CANCELLED');

מחליפים את CONNECTION_ID במזהה של החיבור ל-BigQuery.

בלי דחיפות SQL, השאילתה הבאה נשלחת ל-Cloud SQL:

SELECT *
FROM operations_table

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

עם דחיפות SQL, השאילתה הבאה נשלחת ל-Cloud SQL:

SELECT `a`, `b`
FROM (
  SELECT * FROM operations_table) t
WHERE ((`a` = 'Y') AND (NOT `b` IN ('COMPLETE', 'CANCELLED')))

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

העברת שאילתות SQL מיושמת גם כשמריצים שאילתות מאוחדות עם מערכי נתונים חיצוניים של Spanner.

אפשר לבדוק את דחיפות השאילתות שהוחלו (אם יש כאלה) בתוכנית לביצוע שאילתה.

מגבלות

לדחיפות SQL יש מגבלות שונות, בהתאם למקור הנתונים החיצוני ולדרך שבה שולחים שאילתות לנתונים.

מגבלות על איחוד שאילתות כשמשתמשים ב-EXTERNAL_QUERY

  • העברת שאילתות SQL מתבצעת רק בשאילתות מאוחדות בפורמט SELECT * FROM T.
  • יש תמיכה רק בגיזום עמודות ובדחיפת מסננים. באופן ספציפי, אין תמיכה ב-pushdown של חישוב, איחוד, הגבלה, סידור לפי וצבירה.
  • במסננים שמועברים למסד הנתונים, הליטרלים חייבים להיות מאחד מהסוגים הבאים: BOOL,‏ INT64,‏ FLOAT64,‏ STRING,‏ DATE,‏ DATETIME,‏ TIMESTAMP. אין תמיכה בערכים מילוליים שהם מבנים.
  • העברת פונקציות SQL חלה רק על פונקציות שנתמכות גם ב-BigQuery וגם במסד נתונים של יעד.
  • העברת שאילתות SQL נתמכת רק ב-AlloyDB, ב-Cloud SQL וב-Spanner. במסדי נתונים אחרים, השאילתה נשלחת ללא שינויים.

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

  • יש תמיכה בגיזום עמודות, בסינון, בחישוב ובדחיפה של צבירה חלקית. באופן ספציפי, אין תמיכה ב-pushdown של join, ‏ limit ו-order by.
  • במסננים שמועברים למסד הנתונים, הליטרלים חייבים להיות אחד מהסוגים הבאים: BOOL,‏ INT64,‏ FLOAT64,‏ STRING,‏ DATE,‏ DATETIME,‏ TIMESTAMP,‏ BYTE או מערכים. אין תמיכה בערכים מילוליים שהם מבנים.
  • העברת פונקציות SQL חלה רק על פונקציות שנתמכות גם ב-BigQuery וגם ב-Spanner.

פונקציות נתמכות לפי מקור נתונים

אלה פונקציות ה-SQL שנתמכות לפי מקור נתונים:

‫Cloud SQL MySQL

  • אופרטורים לוגיים: AND, OR, NOT.
  • אופרטורים להשוואה: =, >, >=, <, <=, <>, IN, BETWEEN, IS NULL.
  • אופרטורים אריתמטיים: +,‏ -,‏ * (רק ל-INT64 ול-FLOAT64).

‫Cloud SQL PostgreSQL ו-AlloyDB

  • אופרטורים לוגיים: AND, OR, NOT.
  • אופרטורים להשוואה: =, >, >=, <, <=, <>, IN, BETWEEN, IS NULL.
  • אופרטורים אריתמטיים: +, -, *, / (רק לסוגים INT64, FLOAT64 ו-DATE, חוץ מחיסור של DATE).

‫Spanner – ניב PostgreSQL

  • אופרטורים לוגיים: AND, OR, NOT.
  • אופרטורים להשוואה: =, >, >=, <, <=, <>, IN, BETWEEN, IS NULL.
  • אופרטורים אריתמטיים: +, -, *, / (רק בשביל INT64, FLOAT64, NUMERIC).

  • כשמשתמשים במערכי נתונים חיצוניים, צריך גם:

    • העברת חישוב למקור הנתונים
    • דחיפה למטה של Partial Aggregate
    • פונקציות מחרוזת
    • פונקציות מתמטיות
    • פונקציות Cast
    • פונקציות מערך

  • כשמריצים שאילתות, צריך לצפות לסמנטיקה של GoogleSQL ולא לסמנטיקה של PostgreSQL. לדוגמה:

    • ערכי NULL ממוינים קודם בסדר עולה כברירת מחדל, בניגוד ל-PostgreSQL שבו הם ממוינים אחרונים כברירת מחדל.

    • ערכי NUMERIC של PostgreSQL שנקראים מ-Spanner מטופלים בהתאם למיפוי הסוגים של Spanner ל-BigQuery. לדוגמה, אם בעמודה מספרית יש את הערך 1.1234567891, השאילתה הבאה תחזיר 0 שורות: 

      SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID", "SELECT * from
operations_table where numeric_col = 1.123456789")
       אבל ההצהרה הבאה מחזירה שורה אחת על סמך הסמנטיקה של GoogleSQL: 
      SELECT * from operations_table where numeric_col = 1.123456789

    • הנורמליזציה של אובייקט JSON מתנהגת אחרת. ב-Spanner JSON, המפתחות ממוינים באופן לקסיקוגרפי בלבד, אבל ב-PostgreSQL PG JSONB הם ממוינים קודם לפי אורך המפתח, ואז באופן לקסיקוגרפי עם אורך מפתח שווה ערך.

‫Spanner – ניב GoogleSQL

  • אופרטורים לוגיים: AND, OR, NOT.
  • אופרטורים להשוואה: =, >, >=, <, <=, <>, IN, BETWEEN, IS NULL.
  • אופרטורים אריתמטיים: +, -, *, / (רק בשביל INT64, FLOAT64, NUMERIC).
  • אופרטורים אריתמטיים בטוחים: SAFE_ADD, SAFE_SUBTRACT, SAFE_MULTIPLY, SAFE_DIVIDE (רק ל-INT64, FLOAT64, NUMERIC).
  • כשמשתמשים במערכי נתונים חיצוניים, צריך גם:
    • העברת חישובים,
    • דחיפה של Partial Aggregate,
    • פונקציות מחרוזת,
    • פונקציות מתמטיות,
    • פונקציות Cast,
    • פונקציות מערך.

עבודה עם השוואות במקורות נתונים חיצוניים

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

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

SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID", "select * from operations_table where flag = 'Y'")

מחליפים את CONNECTION_ID במזהה של החיבור ל-BigQuery.

השאילתה הקודמת מחזירה שורות שבהן הערך של flag הוא y או Y, כי השאילתה מופעלת במקור הנתונים החיצוני.

עם זאת, בשאילתות מאוחדות עם מקורות נתונים של Cloud SQL,‏ SAP Datasphere,‏ AlloyDB,‏ Oracle,‏ MySQL או PostgreSQL, אם מוסיפים מסנן לשאילתה הראשית, השאילתה מבוצעת בצד של BigQuery עם ברירת המחדל של ההשוואה. השאילתה הבאה:

SELECT * FROM
  (
    SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID", "select * from operations_table")
  )
WHERE flag = 'Y'

בגלל ההשוואה שתלויה באותיות רישיות ומוגדרת כברירת מחדל ב-BigQuery, השאילתה הקודמת מחזירה רק שורות שבהן הדגל הוא Y ומסננת שורות שבהן הדגל הוא y. כדי שהסעיף WHERE לא יהיה תלוי-רישיות, צריך לציין את ה-collation בשאילתה:

SELECT * FROM
  (
    SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID", "select * from operations_table")
  )
WHERE COLLATE(flag, 'und:ci') = 'Y'

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