פתרון בעיות במחבר BigQuery ומגבלות שלו

כשמחברים את Data Studio ל-BigQuery, יכול להיות שתיתקלו בפסק זמן, במגבלות על תחביר SQL, במגבלות על מכסות או בשגיאות של VPC Service Controls. במדריך הזה מתוארות בעיות נפוצות במחבר BigQuery. מרחיבים את הקטע שלבים לפתרון הבעיה כדי לבדוק את הבעיה ולפתור אותה.

שגיאות בתחביר של שאילתות ו-SQL

יש מגבלות ספציפיות על שאילתות SQL בהתאמה אישית ב-Data Studio. אם השאילתה חורגת מהמגבלות האלה, יכולות להתרחש שגיאות.

Field is ambiguous שגיאה בהצטרפות

אם השאילתה המותאמת אישית מכילה שמות עמודות כפולים, השגיאה הבאה תוצג בתרשימים:

User Configuration Error: Field is ambiguous

הטקסט של הודעת השגיאה: User Configuration Error (שגיאה בהגדרת המשתמש)

הסיבה: בטבלאות מאוחדות (JOIN) לא יכולות להיות עמודות עם שמות זהים. לדוגמה, אם מצטרפים לשתי טבלאות עם סכימות זהות בשדה Criteria_ID, הטבלה הווירטואלית שמתקבלת תכלול עמודות כפולות (Criteria_ID, Parent_ID, Name), מה שיגרום לשגיאת עמימות.

השלבים לפתרון הבעיה

כדי שכל שמות העמודות יהיו ייחודיים, משתמשים במילת המפתח AS או בסעיף EXCEPT:

אפשרות 1: שינוי השם של שדות כפולים באופן מפורש באמצעות כינויים

SELECT *
FROM (
  SELECT
    Criteria_ID AS Criteria_ID_1,
    Parent_ID AS Parent_ID_1,
    Name AS NAME_1
  FROM
    `project.dataset.table_1` ) AS table_1
LEFT JOIN (
  SELECT
    Criteria_ID AS Criteria_ID_2,
    Parent_ID AS Parent_ID_2,
    Name AS NAME_2
  FROM
    `project.dataset.table_2` ) AS table_2
ON
  table_1.Criteria_ID_1 = table_2.Criteria_ID_2;

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

אם אתם צריכים לשנות את השם של מספר קטן של שדות ולהשאיר את השאר, אתם יכולים להשתמש באפשרות EXCEPT:

SELECT * EXCEPT (city), city AS city_1 FROM `project.dataset.table_1`

שגיאת תחביר בשאילתת SQL בהתאמה אישית (כמה הצהרות)

שאילתת SQL בהתאמה אישית תיכשל אם היא מכילה משתנים או כמה הצהרות (DECLARE, SET).

הסיבה:‏ Data Studio מריץ את ה-SQL בתוך שאילתת SELECT חיצונית (SELECT * FROM (<your_custom_sql>)). לכן, השאילתה צריכה להיות הצהרת SELECT יחידה.

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

DECLARE cost_per_tb_in_dollar FLOAT64 DEFAULT 4.2;
SELECT total_bytes_billed / (1024 * 1024) * cost_per_tb_in_dollar / (1024 * 1024) FROM `billing_table`;

השלבים לפתרון הבעיה

לשלב חישובים בהצהרה אחת של SELECT באמצעות ביטויי טבלה נפוצים (CTEs או WITH clauses):

WITH constants AS (
  SELECT 4.2 AS cost_per_tb_in_dollar
)
SELECT
  total_bytes_billed / (1024 * 1024) * c.cost_per_tb_in_dollar / (1024 * 1024) AS cost
FROM `billing_table`, constants AS c;

ביצועים וזמנים קצובים לתפוגה של שאילתות

אם הפעלת שאילתות נמשכת יותר משלוש עד חמש דקות, מתרחש פסק זמן ב-Data Studio לפני קבלת התוצאות, ומוצגת השגיאה: HTTP 504 Gateway timeout.

HTTP 504 Gateway timeout או שגיאות בשאילתות שפועלות במשך יותר מדי זמן

יכול להיות ששאילתות בהתאמה אישית או צבירות מורכבות של תרשימים ב-Data Studio יסתיימו אחרי שלוש עד חמש דקות, ויחזירו שגיאה HTTP 504 Gateway timeout.

השלבים לפתרון הבעיה

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

  • הפעלת BigQuery Storage Read API: מפעילים את BigQuery Storage Read API כדי להגדיל את קצב העברת הנתונים.
  • לפשט את השאילתה: מסירים פקודות `JOIN` מיותרות, מקבצים נתונים לפי תקופות זמן רחבות יותר ובוחרים רק את העמודות הנדרשות.
  • שימוש ב-BigQuery BI Engine: אפשר להזמין קיבולת באמצעות BigQuery BI Engine כדי לקבל ביצועים מהירים מאוד.
  • שימוש בתצוגות מפורטות של מסד נתונים: שומרים את ה-SQL המותאם אישית כתצוגה מפורטת של BigQuery או כתצוגה מפורטת מגובה בחומר, ומחברים את Data Studio ישירות לתצוגה המפורטת הזו.
  • ביצוע צבירה מראש לטבלת דיווח: אפשר להשתמש בשאילתות מתוזמנות ב-BigQuery כדי לכתוב רשומות סיכום לטבלה נפרדת, ולהריץ שאילתות על טבלת הסיכום.

מכסות ומגבלות על טבלאות

כשמערך הנתונים מכיל אלפי טבלאות או מחזיר מיליוני רשומות, יכולות להתרחש שגיאות שקשורות למגבלות:

הקפאה של הרספונסיביות של ממשק המשתמש בערכת נתונים עם יותר מ-5,000 טבלאות

כשמנסים להתחבר למערך נתונים ב-BigQuery באמצעות רשימת בחירת הטבלאות ב-Data Studio, ממשק המשתמש קופא או לא מגיב.

הגורם: המחבר תומך בעד 5,000 טבלאות לכל מערך נתונים. אם מערך נתונים מכיל יותר מ-5,000 טבלאות או תצוגות, רשימת בחירת הטבלאות תפסיק להגיב ותקפא.

השלבים לפתרון הבעיה

אפשר להשתמש באחת מהחלופות הבאות כדי להתחבר בלי לטעון את רשימת הטבלאות:

  • חיבור באמצעות שאילתה בהתאמה אישית: בוחרים באפשרות CUSTOM QUERY (שאילתה בהתאמה אישית) וכותבים הצהרת `SELECT` פחות מורכבת: 
    SELECT * FROM `your_project.your_dataset.your_table`
  • להתחבר ישירות מ-BigQuery: במסוף BigQuery, מאתרים את הטבלה, לוחצים על ייצוא או על ניתוח נתונים ובוחרים באפשרות פתיחה באמצעות Looker Studio.
  • פיצול או ארגון מחדש של מערך הנתונים: מעבירים טבלאות דיווח למערכי נתונים קטנים יותר שמוקדשים לדיווח ומכילים פחות מ-5,000 טבלאות.

מגבלת החזרת השורות היא 2 מיליון

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

הסיבה: המחבר מחזיר מקסימום 2 מיליון שורות לכל שאילתת תרשים. אם שאילתה חורגת מ-2 מיליון רשומות, התרשימים חותכים את הנתונים ומציגים אזהרה.

השלבים לפתרון הבעיה

כדי למנוע חיתוך של נתונים:

  • החלת מסנני תאריכים ברמת הדוח כדי לצמצם את נפח השאילתות.
  • שליחת שאילתות לטבלאות שמחולקות למחיצות לפי תאריך (DATE, ‏ DATETIME, ‏ TIMESTAMP) שדורשות מסנני מחיצות (מידע נוסף).
  • כדאי לקבץ מאפיינים עם קרדינליות גבוהה ב-BigQuery לפני שמציגים אותם ב-Data Studio.

MEDIAN וגם שונות של PERCENTILE

כשמחשבים חציון מדויק (MEDIAN) או אחוזון (PERCENTILE) בתרשימים שמחוברים ל-BigQuery, יכול להיות שהתוצאה תהיה שונה מעט מחישובים זהים שמבוצעים במסדי נתונים אחרים של SQL או בייצוא של קובץ CSV.

הסיבה: בשאילתות של BigQuery, הפונקציות MEDIAN ו-PERCENTILE משתמשות בפונקציית הצבירה המשוערת APPROX_QUANTILES. התהליך הזה מעבד במהירות מערכי נתונים בסדר גודל של פטה-בייט, אבל התוצאות המשוערות עשויות להיות שונות מעט מהחישובים המדויקים שמתבצעים בייצוא של קובצי CSV או במסדי נתונים אחרים של SQL.

שגיאות שקשורות לסוג הנתונים ולהצפנה

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

שגיאת הצפנה CONDITION_NOT_MET (CMEK)

כשמריצים שאילתה על מערך נתונים, התרשים נכשל ומוחזרת השגיאה הבאה:

User Configuration Error: CONDITION_NOT_MET

הסיבה: המחבר לא תומך במפתחות הצפנה בניהול הלקוח (CMEK). אם מדיניות הארגון מחייבת הצפנת CMEK לשאילתות או לאחסון זמני (Organization Policy Service), התרשים יציג את הסמל User Configuration Error: CONDITION_NOT_MET.

השלבים לפתרון הבעיה

עובדים עם האדמין בארגון כדי להחריג את פרויקט הדיווח ממדיניות ה-CMEK, או מייצאים את נתוני הדיווח למערך נתונים הכפוף לסטנדרט Google-owned and Google-managed encryption keys.

סוג הנתונים TIME לא נתמך

כשמתחברים לטבלת BigQuery שמכילה עמודות עם סוג הנתונים TIME (למשל, 23:59:59), ‏ Data Studio ממיר את השדה ל-TEXT, מה שמונע מיון או צבירה על בסיס זמן.

הסיבה: כלי Data Studio לא תומך באופן מובנה בסוג הנתונים TIME של BigQuery (לדוגמה, 23:59:59). המחבר ממיר עמודות TIME למחרוזות TEXT בזמן ההטמעה, ולכן אי אפשר למיין לפי זמן.

השלבים לפתרון הבעיה

כדי להמיר עמודות מסוג TIME לאובייקטים מסוג DATETIME, אפשר להשתמש באחת מהשיטות הבאות:

פתרון עקיף 1: שימוש בשאילתת SQL מותאמת אישית

משלבים את השדה `TIME` עם תאריך בסיס (`1970-01-01`) ישירות ב-SQL:

SELECT
  *,
  -- Combine a dummy date (1970-01-01) with your TIME field
  DATETIME(DATE "1970-01-01", your_time_field) AS time_as_datetime
FROM
  `your_project.your_dataset.your_table`
  • תוצאה: המערכת של Data Studio קולטת את הערך `time_as_datetime` כשדה מסוג **תאריך ושעה**.
  • עיצוב: במאפייני תרשים הדוח, משנים את **פורמט התצוגה** של השדה ל **שעה**, **דקה** או פורמט זמן מותאם אישית (`h:mm:ss`) כדי להציג רק את חלק הזמן (מידע נוסף).

פתרון עקיף 2: יצירת שדה מחושב ב-Data Studio

אם לא משנים את שאילתת ה-SQL, יוצרים שדה מחושב בתוך מקור הנתונים:

PARSE_DATETIME("%H:%M:%S", CAST(your_time_field AS TEXT))
  • תוצאה: הפונקציה PARSE_DATETIME מנתחת את מחרוזת הטקסט לאובייקט **Date & Time** (תאריך ושעה), ומשתמשת בתאריך הקלנדרי 1 בינואר 1970 כברירת מחדל (מידע נוסף).

שגיאות ב-VPC Service Controls

פתרון בעיות שקשורות לשגיאות של דחיית גישה ולהגבלות על תהליכים ברקע כשעובדים בגבולות גזרה לשירותים.

Service Control Failure כשצופים בדוחות בלי VPN

כשצופים בדוח מחוץ ל-VPN או לרשת של הארגון, חלק מהתרשימים או כולם לא מוצגים ומופיעה השגיאה הבאה:

Service Control Failure

הסיבה: המחבר מעביר את כתובת ה-IP של הצופה בדוח אל BigQuery כדי לאמת את רמות הגישה מבוססות ה-IP של VPC Service Controls. כשמעתיקים דוח, יכול להיות שמקורות נתונים מותאמים אישית של SQL מדור קודם או 'מקורות רפאים' בעותק יפנו לפרויקט חיוב שמוגן בתוך גבול גזרה לשירות, גם אם מערך הנתונים הראשי נמצא מחוץ לגבול הגזרה.

השלבים לפתרון הבעיה

מזהים ומסירים מהדוח פרויקטים לחיוב שמוגבלים להיקף ההרשאה ומוסתרים, או מקצים אותם מחדש:

  1. כדי לפתור את הבעיה בצורה בטוחה, יוצרים עותק של הדוח המושפע.
  2. בכלי לעריכת דוחות של העותק, עוברים אל מקור > ניהול מקורות נתונים שנוספו.
  3. בודקים כל מקור נתונים מוטמע מסוג **BigQuery** או **Custom SQL** שמצורף לדוח.
  4. עורכים כל חיבור SQL מותאם אישית כדי לוודא שפרויקט החיוב מוגדר. אם מקור נתונים כלשהו מצביע על פרויקט לחיוב שמוגן על ידי גבולות גזרה של VPC Service Controls, צריך לעדכן אותו כך שישתמש בפרויקט לחיוב שלא מוגן או למחוק את מקור הנתונים אם הוא כבר לא בשימוש.

שליחת הודעת אימייל מתוזמנת או התראות על תרשימים נכשלות מאחורי VPC Service Controls

כשמפעילים תכונות אוטומטיות ברקע (כמו שליחת הודעת אימייל מתוזמנת או התראות על תרשימים) בתרשים שמקושר למערך נתונים ב-BigQuery שמוגן על ידי VPC Service Controls, הודעת האימייל המתוזמנת נשלחת בלי תוכן הדוח או קבצים מצורפים, או שההתראה לא מופעלת (VPC Service Controls unexpected field in error map).

הסיבה: תכונות אוטומטיות שפועלות ברקע (כמו שליחת אימייל מתוזמנת או התראות על תרשימים) פועלות כמשימות ברקע ללא כתובת IP של משתמש קצה, ולכן VPC Service Controls ‏ (VPC-SC) חוסם אותן כשמתבצעת הערכה של רמות גישה מבוססות-IP ‏ (VPC Service Controls unexpected field in error map).

השלבים לפתרון הבעיה

כדי להשתמש בתכונות אוטומטיות שפועלות ברקע מאחורי מתחם של VPC Service Controls, צריך להגדיר את מקור הנתונים כך שישתמש בפרטי כניסה של חשבון שירות או ליצור רמת גישה מבוססת-זהות: