מבוא לשאילתות לכמה מסדי נתונים

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

באמצעות שאילתות לכמה מסדי נתונים, אתם יכולים לנתח בזמן אמת נתונים תפעוליים שמאוחסנים במאגרי הנתונים Spanner, AlloyDB, Cloud SQL ו-SAP Datasphere, בלי להעתיק או להעביר את הנתונים ל-BigQuery.

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

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

תהליך עבודה

  • מזהים את הפרויקט ב- 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 בכל אזור נתמך של Spanner, מאזור יחיד או ממספר אזורים של BigQuery. למידע נוסף, קראו על שאילתות חוצות-אזורים.

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

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

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

  • שאילתות לכמה מסדי נתונים באזורים שונים. אם מיקום עיבוד השאילתות ב-BigQuery שונה מהמיקום של מקור הנתונים החיצוני, זו שאילתה חוצת-אזורים. אפשר להריץ שאילתות חוצות-אזורים בנפח של עד 1TB לכל פרויקט ביום. הדוגמה הבאה היא של שאילתה חוצת-אזורים.
    • המכונה של Cloud SQL נמצאת ב-us-west1, והחיבור ל-BigQuery מבוסס על מספר אזורים בארה"ב. מיקום עיבוד השאילתות ב-BigQuery הוא US.
  • Quota. המשתמשים צריכים לשלוט במכסת השאילתות במקור הנתונים החיצוני, כמו Cloud SQL או AlloyDB. אין הגדרת מכסה נוספת לשאילתות לכמה מסדי נתונים. כדי לבודד את עומס העבודה, מומלץ לשלוח שאילתות רק לרפליקה לקריאה של מסד הנתונים.
  • מספר הבייטים המקסימלי המותר לחיוב. השדה הזה לא נתמך בשאילתות לכמה מסדי נתונים. אי אפשר לחשב את הבייטים לחיוב לפני שמריצים בפועל את השאילתות לכמה מסדי נתונים.
  • מספר החיבורים. שאילתה לכמה מסדי נתונים יכולה לכלול עד 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, אתם מחויבים על סמך מספר ה-slots שבהם אתם משתמשים. מידע נוסף זמין במאמר תמחור של קיבולת מחשוב.

דחיפות 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.

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

מגבלות

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

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

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

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

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

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

אלה פונקציות ה-SQL שנתמכות לפי מקור נתונים. אין תמיכה בפונקציות ב-SAP Datasphere.

‫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, אם מוסיפים מסנן לשאילתה הראשית, השאילתה מבוצעת בצד של 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'

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