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

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

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

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

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

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

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

דחיפות 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 נתמכת רק ב-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'

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