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

בדף הזה מוסבר איך להשתמש בשאילתות מאוחדות, ומפורטות הנחיות לשאילתות של נתונים מ-Spanner, מ-AlloyDB ומ-Cloud SQL מתוך BigQuery.

שאילתות מאוחדות מאפשרות לשלוח הצהרת שאילתה למסדי נתונים של AlloyDB,‏ Spanner או Cloud SQL ולקבל את התוצאה כטבלה זמנית. שאילתות מאוחדות משתמשות ב-BigQuery Connection API כדי ליצור חיבור ל-AlloyDB, ל-Spanner או ל-Cloud SQL. בשאילתה, משתמשים בפונקציה 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 במסד הנתונים של 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 שונה מהמיקום של מקור הנתונים החיצוני, מדובר בשאילתה חוצת-אזורים. אפשר להריץ עד ‎1 TB‎ של שאילתות חוצות-אזורים לכל פרויקט ביום. הנה דוגמה לשאילתה חוצת-אזורים.
    • מופע 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, אתם מחויבים לפי מספר המשבצות שבהן אתם משתמשים. מידע נוסף זמין במאמר בנושא תמחור של קיבולת מחשוב.

העברת פעולות SQL למטה

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

כשמשתמשים בפונקציה EXTERNAL_QUERY, המערכת מבצעת SQL pushdown על ידי כתיבה מחדש של השאילתה המקורית. בדוגמה הבאה, נעשה שימוש בפונקציה 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, השאילתה הבאה נשלחת ל-Cloud SQL:

SELECT *
FROM operations_table

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

עם SQL pushdowns, השאילתה הבאה נשלחת ל-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.
  • אין תמיכה ב-SQL pushdowns ב-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).

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

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

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

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

    • ערכים של PostgreSQL NUMERIC שנקראים מ-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).
  • כשמשתמשים במערכי נתונים חיצוניים, בנוסף:
    • Compute pushdown,
    • העברת Partial Aggregate למטה,
    • פונקציות String,
    • פונקציות מתמטיות,
    • פונקציות 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'

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

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

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