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

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

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

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

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

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

User Configuration Error: Field is ambiguous

טקסט של הודעת השגיאה: שגיאה בהגדרת המשתמש

הסיבה: בטבלאות מאוחדות (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 אחת באמצעות ביטויי טבלה נפוצים (CTE או משפטי WITH):

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

הסיבה: כש-Data Studio מריץ משימת שאילתה, BigQuery כותב את תוצאות השאילתה בטבלה זמנית בפרויקט לחיוב. אם אילוץ מדיניות הארגון constraints/gcp.restrictNonCmekServices נאכף ב-BigQuery בפרויקט לחיוב, BigQuery דורש מפתח הצפנה בניהול הלקוח (CMEK) לכל הטבלאות החדשות, כולל טבלאות זמניות של תוצאות שאילתות. מכיוון שהמחבר לא מעביר מפתח Cloud Key Management Service בבקשות של משימות שאילתה, השאילתות נכשלות עם User Configuration Error: CONDITION_NOT_MET, אלא אם מוגדר מפתח Cloud KMS כברירת מחדל לפרויקט.

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

מגדירים מפתח Cloud KMS כברירת מחדל לפרויקט ב-BigQuery, כדי ש-BigQuery יצפין באופן אוטומטי טבלאות זמניות של תוצאות שאילתות:

  1. מקצים את התפקיד Cloud KMS CryptoKey Encrypter/Decrypter‏ (roles/cloudkms.cryptoKeyEncrypterDecrypter) במפתח Cloud KMS לחשבון השירות של הצפנת BigQuery בפרויקט לחיוב.
  2. הגדירו מפתח Cloud KMS כברירת מחדל לפרויקט (region-LOCATION.default_kms_key_name) בפרויקט לחיוב, במיקום של מערך הנתונים שאתם שולחים אליו שאילתה. לדוגמה: 
    ALTER PROJECT PROJECT_ID
SET OPTIONS (
  `region-LOCATION.default_kms_key_name` = 'projects/KMS_PROJECT_ID/locations/LOCATION/keyRings/KEY_RING/cryptoKeys/KEY'
);

לחלופין, אפשר לבקש מהאדמין של הארגון להחריג את הפרויקט לחיוב מהמדיניות constraints/gcp.restrictNonCmekServices.

סוג הנתונים 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` כשדה מסוג **תאריך ושעה**.
  • עיצוב: במאפייני התרשים של הדוח, משנים את השדה **Display Format** ל-**Hour**, ל-**Minute** או לפורמט זמן בהתאמה אישית (`h:mm:ss`) כדי להציג רק את החלק של השעה (מידע נוסף).

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

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

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

שגיאות ב-VPC Service Controls

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

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

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

Service Control Failure

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

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

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

  1. כדי לפתור את הבעיה בבטחה, צרו עותק של הדוח שבו הבעיה מופיעה.
  2. בכלי לעריכת דוחות של העותק, עוברים אל משאב > ניהול מקורות נתונים שנוספו.
  3. בודקים כל מקור נתונים מוטמע של **BigQuery** או **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, צריך להגדיר את מקור הנתונים כך שישתמש בפרטי כניסה של חשבון שירות או ליצור רמת גישה שמבוססת על זהות: