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

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

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 pushdowns, והן משתנות בהתאם למקור הנתונים החיצוני ולאופן שבו שולחים שאילתות לנתונים.

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

  • העברת שאילתות SQL מתבצעת רק בשאילתות מאוחדות מהצורה SELECT * FROM T.
  • יש תמיכה רק בגיזום עמודות ובדחיפת מסננים. בפרט, אין תמיכה בפעולות הבאות: compute,‏ join,‏ limit,‏ order by ו-aggregation pushdowns.
  • במקרים של העברת סינון למקור הנתונים, הערכים המילוליים צריכים להיות אחד מהסוגים הבאים: 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).

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

    • העברת חישובים למטה (pushdown)
    • Partial Aggregate pushdown
    • פונקציות מחרוזת
    • פונקציות מתמטיות
    • פונקציות 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 pushdown,
    • פונקציות String,
    • פונקציות מתמטיות,
    • פונקציות Cast,
    • פונקציות מערך.

עבודה עם כללי מיון במקורות נתונים חיצוניים

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

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

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 ללא תלוי-רישיות, מציינים את אוסף הכללים (collation) בשאילתה:

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

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